Re: [GSOC] questions about idea "rewrite pg_dump as library" - Mailing list pgsql-hackers

From Joel Jacobson
Subject Re: [GSOC] questions about idea "rewrite pg_dump as library"
Date
Msg-id CAASwCXc-4FU5raOcrGr0Cp-msqAtoeQJuP07pft-YkBJUn1puw@mail.gmail.com
Whole thread Raw
In response to Re: [GSOC] questions about idea "rewrite pg_dump as library"  (Hannu Krosing <hannu@2ndQuadrant.com>)
List pgsql-hackers
On Fri, Apr 12, 2013 at 1:07 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
I was just thinking of moving the queries the pg_dump currently
uses into UDF-s, which do _not_ use catalog cache, but will use
the same SQL to query catalogs as pg_dump currently does
using whatever snapshot mode is currently set .

the pg_dump will need to still have the same queries for older
versions of postgresql but for new versions pg_dump  can become
catalog-agnostic.

and I think that we can retire pg_dump support for older
postgresql versions the same way we drop support for
older versions of postgresql itself. 
 
main things I see would be

 * get_list_of_objects(object_type, pattern or namelist)
 * get_sql_def_for_object(object_type, object_name)
 * sort_by_dependency(list of [obj_type, obj_name])

from this you could easily construct most uses, especially if
sort_by_dependency(list of [obj_type, obj_name])
would be smart enough to break circular dependencies, like
turning to tables with mutual FK-s into tabledefs without
FKs + separate constraints.


+1

This is an excellent idea. This would allow doing all kinds of crazy things outside of the scope of pg_dump.

2 years ago I was working on a system to version control the schema, inside the database.
Don't know if it's a good idea or not, but one thing which bugged me a lot was the lack of pg_get_[object type]def(oid) functions for all different object types.
It also turned out to be quite complicated to do the pg_depend topological sort yourself. I managed eventually, but it was running to slow because I had to pass the entire content of pg_depend to a plperl function I wrote.

With this in place I would be motivated enough to resume my old project, which is still online at https://github.com/gluefinance/pov if anyone is interested.

Is it really necessary to write all the missing pg_get_[object type]def(oid) functions in C? I think it would be quite easy to put them together using pure SQL, you wouldn't even need PL/pgSQL.

This old view I once wrote manage to produce working create and drop statements for most object types using SQL only:

It would also be nice with functions which returned the proper command to DROP an object. I need it in this project in order to do schema modifications where objects have to be dropped/recreated in a particular order to not break dependencies. Perhaps there are other use cases out there.


pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: doc hdparm also support SATA
Next
From: Stephen Woodbridge
Date:
Subject: Need help with TRAP: FailedAssertion("!(context != CurrentMemoryContext)"