Re: Schema version management

On Mon, May 21, 2012 at 5:03 AM, Joel Jacobson <joel@trustly.com> wrote:
>
> http://archives.postgresql.org/pgsql-hackers/2010-12/msg02301.php
> The initial feedback was on the usage of OIDs as file names.
> This was indeed a bad idea and was changed, see
> http://archives.postgresql.org/pgsql-hackers/2010-12/msg02318.php
> Gurjeet Singh pointed out the problem with functions sharing the same
> name but having different arguments.
> As of now, it's not certain they will always be dumped into the same files.
> This is a valid point, and needs to be solved in an elegant way.
> The arguments needs to be made part of the path somehow.

This is interesting at Jane Street we actually have a small tool
that "parses" the output of pg_dump.  (Well applies a set of regular
expressions plus a little bit guesswork).  We use this to do three things
all of which I would love to see supported by postgres tool chain proper:

1) split the output into one file per thing (basically as per this  thread) each file named
<type>_<name>_<running-integer>for use  with a VCS.  So if we have an overloaded function foo we end up with  several
function_foo_1.sqlfunction_foo_2.sql ...  The order of the  enumeration is just the order the functions occurred in the
pg_dump which seems to be stable and therefore good enough.
 

2) extract a patch.  You give the tool the name of one or more roots  (e.g. a table or set of tables you want to
modify). It finds all  things that depend on it (well sort of just turn the body of each  definition into a list of
wordsand a depends on b if the name of b  occurrs in a).  Do a topological sort (if there are cycles because  of the
hackdependency check break them but continue and produce a  warning). Output a file that first drops the definitions in
inverse dependency order and then recreates them (in dependency order).  The file starts with a begin but does NOT end
witha commit so you  are forced to enter it yourself.
 
  This tool is fantastic if you have a big set of plpgsql functions as  it is otherwise hard to make sure that you have
modifiedall places  when refactoring, changing a column, etc...
 

3) Find all leaves.  E.g. do the topsort on the whole pg_dump and list  the names of all things nothing depends on.
Thisis mostly useful if  you want to make sure that you are not accumulating cruft that isn't  used by anything.  Of
courseyou separately need a list or knowledge  about the entry points of your application(s).
 

Cheers,

Bene