PostgreSQL Version Control Feature Coverage

In a comment to one of my early posts about version control, Peter Eisentraut stated he was “somewhat discouraged because [seeing how fast PostgreSQL develops and adds new features,] I don’t see how a tool like apgdiff can keep up and stay useful unless a lot more resources are put into it.” At the time, the first release of Pyrseas (0.1.0) was well over a month away. Currently, Pyrseas 0.3.0 is nearing release . This seems like a good time to summarize where Pyrseas stands vis-à-vis the list of PostgreSQL data definition features:

DDL Feature 0.1 0.2 0.3
AGGREGATE  X
CAST
CONSTRAINT TRIGGER
CONVERSION
DOMAIN  X
FOREIGN DATA WRAPPER
FUNCTION  X  X
GROUP
INDEX  X  X  X
LANGUAGE  X  X
OPERATOR
OPERATOR CLASS
OPERATOR FAMILY
ROLE
RULE X
SCHEMA X X X
SEQUENCE X X X
SERVER
TABLE  X  X  X
 - CHECK CONSTRAINT  X  X  X
 - PRIMARY KEY  X  X  X
 - FOREIGN KEY  X  X  X
 - UNIQUE CONSTRAINT  X  X  X
 - INHERIT  X  X
TABLESPACE
TEXT SEARCH CONFIGURATION
TEXT SEARCH DICTIONARY
TEXT SEARCH PARSER
TEXT SEARCH TEMPLATE
TRIGGER  X
TYPE  X
USER
USER MAPPING
VIEW  X  X
COMMENT  X  X

Note that PosgreSQL 9.1 will add EXTENSION to the list above. Note also that although the links in the matrix go to the corresponding CREATE object documentation page, Pyrseas supports CREATE, CREATE OR REPLACE, DROP and ALTER, as applicable. A final note: an “X” in a particular release doesn’t imply that the feature is supported in all its syntactic glory.

Caveats aside, progress has been made. Pyrseas 0.1.0 supported most of the basic relational database objects and concepts present in the SQL Standard, and usually covered by generic version control tools. Pyrseas 0.3.0 will support the features showcased in the Pagila sample database, which I hope is somewhat representative of the average PostgreSQL database (in terms of DDL features, of course).

The goal for the 0.4.0 release will be to cover the remaining features excluding Text Search, SQL/MED and ROLE/GROUP, which will be tackled in 0.5.0 or later. Is there a feature you or your organization is interested in? Let me know in the comments.

About these ads

6 thoughts on “PostgreSQL Version Control Feature Coverage”

    1. I believe the Pyrseas utilities (dbtoyaml and yamltodb) are most useful in development, where two or more DBAs/programmers make changes to their databases in a more or less “distributed” manner which they share with others just like they do the application code via a DVCS (or even a centralized VCS for that matter). In that environment, database object ownership and permissions are (probably) not too relevant (I’m assuming each DBA/programmer has his/her own database with objects owner by him or her). That is why I’m leaving GROUP/ROLE/USER for last.

      I see your point about deployment and I’ll consider GRANT and REVOKE when working on the latter group.

  1. PgPirdie is doing something similar but is also far away from a really usable tool. At the moment (for my open source project) I use PgComparer. At the moment this is the best tool I have found but is not open source :(

    It is realy painful while developing and delpoing doing all these tasks, so a tool like this would be for sure of interest.

    1. I guess you mean PgBirdie, which is available at Google Code. That tool, written in C#, only runs on Windows (as does EMS DB Comparer for PostgreSQL, which I assume is what you mean by PgComparer–although there is also a pgCompare) whereas Pyrseas, written in Python, runs just about everywhere. Also, Pyrseas 0.4 has just been released and includes support for even more PostgreSQL features (see http://pyrseas.wordpress.com/2011/09/19/version-control-for-postgis/) than those listed above.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s