Thread: pg_dump --split patch

Joel Jacobson <joel@gluefinance.com> writes:
> Dear fellow hackers,
> Problem: A normal diff of two slightly different schema dump files (pg_dump
> -s), will not produce a user-friendly diff, as you get all changes in the
> same file.

> Solution: I propose a new option to pg_dump, --split, which dumps each
> object to a separate file in a user friendly directory structure:

Um ... how does that solve the claimed problem exactly?

> [-f filename] : main dump file, imports each splitted part using \i
> [-f filename]-split/[desc]/[tag]/[oid].sql : dump of the oid

This particular choice seems remarkably *un* friendly, since two dumps
from different DBs will inevitably not share the same OIDs, making it
practically impossible to compare them even if they are logically
identical.  But even without the choice to use OIDs in the filenames
I'm unconvinced that file-per-object is a good idea in any way shape or
form.
        regards, tom lane

Joel Jacobson <joel@gluefinance.com> writes:
> 2010/12/28 Tom Lane <tgl@sss.pgh.pa.us>
>> Joel Jacobson <joel@gluefinance.com> writes:
>>> Solution: I propose a new option to pg_dump, --split, which dumps each
>>> object to a separate file in a user friendly directory structure:
>> 
>> Um ... how does that solve the claimed problem exactly?

> Because then you can do,
> $ diff -r <old schema dump dir> <new schema dump dir>,
> instead of,
> $ diff <old entire schema dump> <new entire schema dump>
> which will nicely reveal each individual object modified, as opposed to a
> huge global diff of everything

That has at least as many failure modes as the other representation.
        regards, tom lane

On 12/28/2010 11:59 AM, Joel Jacobson wrote:
> 2010/12/28 Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>
>
>
>     That has at least as many failure modes as the other representation.
>
>
> I don't follow, what do you mean with "failure modes"? The oid in the 
> filename? I suggested to use a sequence instead but you didn't comment 
> on that. Are there any other failure modes which could cause a diff -r 
> between two different databases to break?
>
> (This might be a bad idea for some other reason, but I noticed a few 
> other users requesting the same feature when I googled "pg_dump split".)
>
>

A better approach to the problem might be to have a tool which did a 
comparison of structures rather than a textual comparison of dumps. For 
extra credit, such a tool might even try to produce a sync script for 
you ...

Of course, that task might involve more effort than you want to devote 
to it.

cheers

andrew

Joel Jacobson <joel@gluefinance.com> writes:
> 2010/12/28 Tom Lane <tgl@sss.pgh.pa.us>
>> That has at least as many failure modes as the other representation.

> I don't follow, what do you mean with "failure modes"? The oid in the
> filename? I suggested to use a sequence instead but you didn't comment on
> that. Are there any other failure modes which could cause a diff -r between
> two different databases to break?

AFAIK the primary failure modes for diff'ing text dumps are

(1) randomly different ordering of objects from one dump to another.
Your initial proposal would avoid that problem as long as the object
OIDs didn't change, but since it falls down completely across a dump and
reload, or delete and recreate, I can't really see that it's a step
forward.  Using a sequence number generated by pg_dump doesn't change
this at all --- the sequence would be just as unpredictable.

(2) randomly different ordering of rows within a table.  Your patch
didn't address that, unless I misunderstood quite a bit.

I think the correct fix for (1) is to improve pg_dump's method for
sorting objects.  It's not that bad now, but it does have issues with
random ordering of similarly-named objects.  IIRC Peter Eisentraut
proposed something for this last winter but it seemed a mite too ugly,
and he got beaten down to just this:

commit 1acc06a1f4ae752793d2199d8d462a6708c8acc2
Author: Peter Eisentraut <peter_e@gmx.net>
Date:   Mon Feb 15 19:59:47 2010 +0000
   When sorting functions in pg_dump, break ties (same name) by number of argum
ents

Maybe you can do better, but I'd suggest going back to reread the
discussion that preceded that patch.

> (This might be a bad idea for some other reason, but I noticed a few other
> users requesting the same feature when I googled "pg_dump split".)

AFAIR what those folk really wanted was a selective dump with more
selectivity knobs than exist now.  I don't think their lives would be
improved by having to root through a twisty little maze of numbered
files to find the object they wanted.
        regards, tom lane

On Tue, Dec 28, 2010 at 11:59 AM, Joel Jacobson <joel@gluefinance.com> wrote:

> I don't follow, what do you mean with "failure modes"? The oid in the
> filename? I suggested to use a sequence instead but you didn't comment on
> that. Are there any other failure modes which could cause a diff -r between
> two different databases to break?

Both OID and sequence mean that your likely to get a diff which is
nothing more than complete files removed from 1 side and added to the
othe rside with different names (i.e. oid's don't match, or an
added/removed object changes all following sequence assingments).

If you're going to try and split, I really think the only usefull
filename has to be similar to something like:   <schema>/<type>/<name>/<part>

If you want to use "diff", you pretty much have to make sure that the
*path* will be identical for similary named objects, irrespective of
anything else in the database.  And path has to be encoding aware.

And you want names that glob well, so for instance, you could exclude
*.data (or a schema) from the diff.

a.

--
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

Sent from my iPhone

On 28 dec 2010, at 21:45, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:

The problem I see with suffixing a sequence id to the objects with name
collision is that one day the dump may name myfunc(int) as myfunc.sql and
after an overloaded version is created, say myfunc(char, int), then the same
myfunc(int) may be dumped in myfunc-2.sql, which again is non-deterministic.


I agree, good point!
Perhaps abbreviations are to prefer, e.g., myfunc_i, myfunc_i_c, etc to
reduce the need of truncating filenames.



Also, it is a project policy that we do not introduce new features in back
branches, so spending time on an 8.4.6 patch may not be the best use of your
time.


My company is using 8.4 and needs this feature, so I'll have to patch it
anyway :)


Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

On 12/28/2010 04:44 PM, Joel Jacobson wrote:
>
>
>
>
>> The problem I see with suffixing a sequence id to the objects with 
>> name collision is that one day the dump may name myfunc(int) as 
>> myfunc.sql and after an overloaded version is created, say 
>> myfunc(char, int), then the same myfunc(int) may be dumped in 
>> myfunc-2.sql, which again is non-deterministic.
>
> I agree, good point!
> Perhaps abbreviations are to prefer, e.g., myfunc_i, myfunc_i_c, etc 
> to reduce the need of truncating filenames.
>
>
>

I think that's just horrible. Does the i stand for integer or inet? And 
it will get *really* ugly for type names with spaces in them ...


cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> On 12/28/2010 04:44 PM, Joel Jacobson wrote:
>> Perhaps abbreviations are to prefer, e.g., myfunc_i, myfunc_i_c, etc 
>> to reduce the need of truncating filenames.

> I think that's just horrible. Does the i stand for integer or inet? And 
> it will get *really* ugly for type names with spaces in them ...

You think spaces are bad, try slashes ;-)

Not to mention the need for including schemas in typenames sometimes.
I think you're going to have a real problem trying to fully describe a
function's signature in a file name of reasonable max length.
        regards, tom lane

David Wilson <david.t.wilson@gmail.com> writes:
> On Tue, Dec 28, 2010 at 2:39 PM, Joel Jacobson <joel@gluefinance.com> wrote:
>> I didn't include the arguments in the file name, as it would lead to very
>> long file names unless truncated, and since the problem is very limited, I
>> think we shouldn't include it. It's cleaner with just the name part of the
>> tag in the file name.

> Why not place all overloads of a function within the same file? Then,
> assuming you order them deterministically within that file, we sidestep the
> file naming issue and maintain useful diff capabilities, since a diff of the
> function's file will show additions or removals of various overloaded
> versions.

If you've solved the deterministic-ordering problem, then this entire
patch is quite useless.  You can just run a normal dump and diff it.
        regards, tom lane

On 12/28/2010 08:18 PM, Joel Jacobson wrote:
> 2010/12/29 Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>
>
>
>     If you've solved the deterministic-ordering problem, then this entire
>     patch is quite useless.  You can just run a normal dump and diff it.
>
>
> No, that's only half true.
>
> Diff will do a good job minimizing the "size" of the diff output, yes, 
> but such a diff is still quite useless if you want to quickly grasp 
> the context of the change.
>
> If you have a hundreds of functions, just looking at the changed 
> source code is not enough to figure out which functions were modified, 
> unless you have the brain power to memorize every single line of code 
> and are able to figure out the function name just by looking at the 
> old and new line of codes.
>
>

try:
 diff -F '^CREATE' ...

cheers

andrew

The problem I see with suffixing a sequence id to the objects with name collision is that one day the dump may name myfunc(int) as myfunc.sql and after an overloaded version is created, say myfunc(char, int), then the same myfunc(int) may be dumped in myfunc-2.sql, which again is non-deterministic.

Also, it is a project policy that we do not introduce new features in back branches, so spending time on an 8.4.6 patch may not be the best use of your time.

Regards,

--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

On Wed, Dec 29, 2010 at 2:27 AM, Joel Jacobson <joel@gluefinance.com> wrote:

<description of split stuff>

So, how different (or not) is this to the "directory" format that was
coming out of the desire of a parallel pg_dump?

a.

--
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

On Wed, Dec 29, 2010 at 9:11 AM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> On Wed, Dec 29, 2010 at 8:31 AM, Joel Jacobson <joel@gluefinance.com> wrote:
>>
>>
>> 2010/12/29 Aidan Van Dyk <aidan@highrise.ca>
>>>
>>> On Wed, Dec 29, 2010 at 2:27 AM, Joel Jacobson <joel@gluefinance.com>
>>> wrote:
>>>
>>> <description of split stuff>
>>>
>>> So, how different (or not) is this to the "directory" format that was
>>> coming out of the desire of a parallel pg_dump?
>>
>> Not sure what format you are referring to? Custom, tar or plain text?
>> I noticed there are two undocumented formats as well, "append" and "file".
>> I tried both of these undocumented formats, but it did not procude any
>> directory structure of the dumped objects.
>> Could you please explain how to use the "directory format" is such a
>> format already exists?
>> I can't find it in the documentation nor the source code of HEAD.
>
> It is still being discussed as a patch to pg_dump. Google for "directory
> archive format for pg_dump", specifically in archives.postgresql.org.

Specifically:
Message-ID: <AANLkTimUELTXwRSQDQNwxik_k1y3YcH1u-9NgHZqpi9e@mail.gmail.com>

> AFAIK, that applies to parallel dumps of data (may help in --schema-only
> dumps too), and what you are trying is for schema.

Right, but one of the things it does is break the dump in to parts,
and put them in a directory/file organization.

Both are doing it for different reasons, but doing pretty much the
same thing.  But can the layout/organization of Joachim's patch can be
made "human friendly" in the vein of Joel's vision?

a.

--
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

Aidan Van Dyk <aidan@highrise.ca> writes:
> On Wed, Dec 29, 2010 at 9:11 AM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
>> AFAIK, that applies to parallel dumps of data (may help in --schema-only
>> dumps too), and what you are trying is for schema.

> Right, but one of the things it does is break the dump in to parts,
> and put them in a directory/file organization.

> Both are doing it for different reasons, but doing pretty much the
> same thing.  But can the layout/organization of Joachim's patch can be
> made "human friendly" in the vein of Joel's vision?

I think they're fundamentally different things, because the previously
proposed patch is an extension of the machine-readable archive format,
and has to remain so because of the expectation that people will want
to use parallel restore with it.  Joel is arguing for a split-up of
the text dump format.
        regards, tom lane

Joel Jacobson <joel@gluefinance.com> writes:
> Solution: I propose a new option to pg_dump, --split, which dumps each
> object to a separate file in a user friendly directory structure:

Please have a look at getddl:
 https://github.com/dimitri/getddl

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On tis, 2010-12-28 at 12:33 -0500, Tom Lane wrote:
> (2) randomly different ordering of rows within a table.  Your patch
> didn't address that, unless I misunderstood quite a bit.

This issue here is just comparing schemas, so that part is a separate
problem for someone else.

> I think the correct fix for (1) is to improve pg_dump's method for
> sorting objects.  It's not that bad now, but it does have issues with
> random ordering of similarly-named objects.  IIRC Peter Eisentraut
> proposed something for this last winter but it seemed a mite too ugly,
> and he got beaten down to just this:
> 
> commit 1acc06a1f4ae752793d2199d8d462a6708c8acc2
> Author: Peter Eisentraut <peter_e@gmx.net>
> Date:   Mon Feb 15 19:59:47 2010 +0000
> 
>     When sorting functions in pg_dump, break ties (same name) by
> number of arguments

Yes, that was addressing the same underlying problem.  Frankly, I have
been thinking split files a lot before and since then.  If the files
were appropriately named, it would remove a lot of problems compared to
diffing one even perfectly sorted big dump file.

On tis, 2010-12-28 at 20:51 -0500, Andrew Dunstan wrote:
> try:
> 
>   diff -F '^CREATE' ...

This works about 67% of the time and still doesn't actually tell at a
glance what changed.  It will only tell you what the change you are
currently looking at probably belongs to.

On Mon, Jan 3, 2011 at 7:11 AM, Dmitry Koterov <dmitry@koterov.ru> wrote:
> To me, this is a wonderful feature, thanks! I think many people would be
> happy if this patch woud be included to the mainstream (and it is quite
> short and simple).
> About name ordering - I think that the problem exists for objects:
> 1. Stored functions.
> 2. Foreign keys/triggers (objects which has owning objects).
> It is wonderful that you store all functions with the same name to the same
> file. To order them within this file we may simply compare the first
> definition line lexicographically (or - first line which differs one
> function definition from another).
> Foreign key/triggers ordering problem is described by me at
> http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg114586.html
> The big problem is with triggers: many triggers may have the same name, but
> be bound to different tables. It would be great to include these triggers to
> table's definition or, at least, have separated files for each trigger+table
> pair.

Hmm... this sounds awfully specific to your particular environment.
My triggers never have the same names...  and on the flip side, I
don't see why someone couldn't want one function per file rather than
all same-named functions in one file.

I think the problem with this patch is that different people are
likely to want slightly different things, and there may not be any
single format that pleases everyone, and supporting too many variants
will become confusing for users and hard for us to maintain.  We're
going to need to agree on something that won't be perfect for
everyone, but will hopefully be a sufficient improvement for enough
people to be worth doing.

On the specific issue of overloaded functions, I have a feeling that
the only feasible option is going to be to put them all in the same
file.  If you put them in different files, the names will either be
very long (because they'll have to include the argument types) or
fairly incomprehensible (if you did something like hash the argument
types and append 8 hex digits to the function name) or not all that
static (if you use OIDs; or if you number them sequentially, like
foo1.sql, foo2.sql, foo3.sql, then foo3.sql might end up as foo2.sql
on a system where there are only two variants of foo, making diff not
work very well).

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2011/1/3 Robert Haas <robertmhaas@gmail.com>:
> will become confusing for users and hard for us to maintain.  We're
> going to need to agree on something that won't be perfect for
> everyone, but will hopefully be a sufficient improvement for enough
> people to be worth doing.

Good point.
I think we can at least agree the "bare minimum" is splitting per
namespace, object type and name.

> On the specific issue of overloaded functions, I have a feeling that
> the only feasible option is going to be to put them all in the same
> file.  If you put them in different files, the names will either be
> very long (because they'll have to include the argument types) or
> fairly incomprehensible (if you did something like hash the argument
> types and append 8 hex digits to the function name) or not all that
> static (if you use OIDs; or if you number them sequentially, like
> foo1.sql, foo2.sql, foo3.sql, then foo3.sql might end up as foo2.sql
> on a system where there are only two variants of foo, making diff not
> work very well).

I agree.
Even if the overloaded functions are not written in the same order,
you will quickly and easily note "function(s) of this particular name
has been changed", which should narrow down your
mind-mapping-change-grasping-exercise quite a lot.

--
Best regards,

Joel Jacobson
Glue Finance

Robert Haas <robertmhaas@gmail.com> writes:
> On the specific issue of overloaded functions, I have a feeling that
> the only feasible option is going to be to put them all in the same
> file.  If you put them in different files, the names will either be
> very long (because they'll have to include the argument types) or
> fairly incomprehensible (if you did something like hash the argument
> types and append 8 hex digits to the function name) or not all that
> static (if you use OIDs; or if you number them sequentially, like
> foo1.sql, foo2.sql, foo3.sql, then foo3.sql might end up as foo2.sql
> on a system where there are only two variants of foo, making diff not
> work very well).

If you put all the variants in the same file, diff is *still* not going
to work very well.  At least not unless you solve the problems that keep
pg_dump from dumping objects in a consistent order ... and once you do
that, you don't need this patch.

> I think the problem with this patch is that different people are
> likely to want slightly different things, and there may not be any
> single format that pleases everyone, and supporting too many variants
> will become confusing for users and hard for us to maintain.

Yeah, that's exactly it.  I can think of some possible uses for
splitting up pg_dump output, but frankly "to ease diff-ing" is not
one of them.  For that problem, it's nothing but a crude kluge that
only sort-of helps.  If we're to get anywhere on this, we need a
better-defined problem statement that everyone can agree is worth
solving and is well solved with this particular approach.
        regards, tom lane

2011/1/3 Tom Lane <tgl@sss.pgh.pa.us>:
> pg_dump from dumping objects in a consistent order ... and once you do
> that, you don't need this patch.
> Yeah, that's exactly it.  I can think of some possible uses for
> splitting up pg_dump output, but frankly "to ease diff-ing" is not
> one of them.  For that problem, it's nothing but a crude kluge that
> only sort-of helps.  If we're to get anywhere on this, we need a
> better-defined problem statement that everyone can agree is worth
> solving and is well solved with this particular approach.

The problem statement is only partly diffing, I think the two major
other problems with one-single-giant-schema-file is:

1. Automatically version controlling your production database schema
using a file based approach is not possible.
Splitting the schema would mean it's very simple to setup a cronjob
which automatically commits the schema changes every night. You would
be able to follow the changes by simply looking at the vcs log,
instead of grepping the log files and trying to figure out what
changed.

2. Splitting is a single option which reduces the need for any other
imaginable exclude/include options, such as "only export these
functions" or "do not export views" etc. Tables are of course the most
common thing you want to include/exclude in a dump, but there are
quite a lot of different object types, however, no single object type
is "important enough" to motivate a new pg_dump option to allow a
exclusion/inclusion option, but I think the sum of the need for such
an option for all object types is greather than the extra complexity
introduced by a patch consisting of only a few lines of code.

--
Best regards,

Joel Jacobson
Glue Finance

On Mon, Jan 3, 2011 at 1:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On the specific issue of overloaded functions, I have a feeling that
>> the only feasible option is going to be to put them all in the same
>> file.  If you put them in different files, the names will either be
>> very long (because they'll have to include the argument types) or
>> fairly incomprehensible (if you did something like hash the argument
>> types and append 8 hex digits to the function name) or not all that
>> static (if you use OIDs; or if you number them sequentially, like
>> foo1.sql, foo2.sql, foo3.sql, then foo3.sql might end up as foo2.sql
>> on a system where there are only two variants of foo, making diff not
>> work very well).
>
> If you put all the variants in the same file, diff is *still* not going
> to work very well.  At least not unless you solve the problems that keep
> pg_dump from dumping objects in a consistent order ... and once you do
> that, you don't need this patch.

That's not really true.  It's a whole lot easier to look a diff of two
100-line files and then repeat that N times than to look at a single
diff of two N*100 line files.  I certainly spend enough of my
patch-review doing "git diff master <some particular source file>",
and then if what's going on isn't clear you can look at just that file
in more detail without worrying about every other source file in the
system.  And I have encountered this problem when comparing database
schemas (and sometimes data) also.  Yes, I've done that using diff.
Yes, it did suck.  Yes, I got it done before my boss fired me.

>> I think the problem with this patch is that different people are
>> likely to want slightly different things, and there may not be any
>> single format that pleases everyone, and supporting too many variants
>> will become confusing for users and hard for us to maintain.
>
> Yeah, that's exactly it.  I can think of some possible uses for
> splitting up pg_dump output, but frankly "to ease diff-ing" is not
> one of them.  For that problem, it's nothing but a crude kluge that
> only sort-of helps.  If we're to get anywhere on this, we need a
> better-defined problem statement that everyone can agree is worth
> solving and is well solved with this particular approach.

I have to admit I'm a bit unsold on the approach as well.  It seems
like you could write a short Perl script which would transform a text
format dump into the proposed format pretty easily, and if you did
that and published the script, then the next poor shmuck who had the
same problem could either use the script as-is or hack it up to meet
some slightly different set of requirements.  Or maybe you'd be better
off basing such a script on the custom or tar format instead, in order
to avoid the problem of misidentifying a line beginning with --- as a
comment when it's really part of a data item.  Or maybe even writing a
whole "schema diff" tool that would take two custom-format dumps as
inputs.

On the other hand, I can certainly think of times when even a pretty
dumb implementation of this would have saved me some time.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Jan 3, 2011 at 1:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, that's exactly it. �I can think of some possible uses for
>> splitting up pg_dump output, but frankly "to ease diff-ing" is not
>> one of them. �For that problem, it's nothing but a crude kluge that
>> only sort-of helps. �If we're to get anywhere on this, we need a
>> better-defined problem statement that everyone can agree is worth
>> solving and is well solved with this particular approach.

> I have to admit I'm a bit unsold on the approach as well.  It seems
> like you could write a short Perl script which would transform a text
> format dump into the proposed format pretty easily, and if you did
> that and published the script, then the next poor shmuck who had the
> same problem could either use the script as-is or hack it up to meet
> some slightly different set of requirements.  Or maybe you'd be better
> off basing such a script on the custom or tar format instead, in order
> to avoid the problem of misidentifying a line beginning with --- as a
> comment when it's really part of a data item.  Or maybe even writing a
> whole "schema diff" tool that would take two custom-format dumps as
> inputs.

> On the other hand, I can certainly think of times when even a pretty
> dumb implementation of this would have saved me some time.

The basic objection that I have to this patch is that it proposes to
institutionalize a pretty dumb implementation.  And, as you mentioned,
once it's in there it'll be more or less set in stone because we aren't
going to want to support umpteen variants.

I like the idea of a postprocessing script a lot better --- it seems
like it wouldn't get in the way of people making their own variants.
And as you say it'd likely be pretty trivial to do.
        regards, tom lane

> Robert Haas <robertmhaas@gmail.com> writes:
>> I have to admit I'm a bit unsold on the approach as well.  It seems
>> like you could write a short Perl script which would transform a text
>> format dump into the proposed format pretty easily, and if you did
>> that and published the script, then the next poor shmuck who had the
>> same problem could either use the script as-is or hack it up to meet
>> some slightly different set of requirements.  Or maybe you'd be better

That's actually what I first did a couple of months ago,
https://github.com/gluefinance/parse_schema/blob/master/parse_schema.pl

My major concern of parsing the schema file is I would never fully
trust the output from the script, even if the regex is extremely
paranoid and really strict, there is still a risk it contains a bug.

If you cannot trust the output from the schema parse script, it's not
safe to use it to do a partial restore of objects.
Let's say you want to restore only a few functions from your backup
schema. Without --split, you would need to restore them manually
somehow. With the --split option, you could simply restore them from
the indivudual files, at least functions where only the source code
has been modified and not the arguments.

2011/1/3 Tom Lane <tgl@sss.pgh.pa.us>:
> The basic objection that I have to this patch is that it proposes to
> institutionalize a pretty dumb implementation.  And, as you mentioned,

What's dumb about it? It's simple and it works.
Please give me an idea of what a smart implementation would be, and I
will see if I can rewrite the patch.

> once it's in there it'll be more or less set in stone because we aren't
> going to want to support umpteen variants.

Yes, that's why it should be a "bare minimum" solution to the problem,
which is a lot better than no solution at all.

What other variants than the discussion on the path name structure can
you think of?

> I like the idea of a postprocessing script a lot better --- it seems
> like it wouldn't get in the way of people making their own variants.
> And as you say it'd likely be pretty trivial to do.

I'm quite sure such a postprocessing script has been written hundreds
of times over the past years by different postgres users, not even
submitting a question to the mailing list, since it's a quite
managable task for anyone with some regex and scripting experience.

Why not just provide a simple "bare minimum" solution to the "problem"
and let the remaining x % of the users who have a very specific need
write their own specialized script if they need to? It would save a
lot of time for all future users who need to write their own script to
solve the problem or google until they find my script or any other
script solving the same problem. Multiple almost identical solutions
to exactly the same problem is kind of time waste.

--
Best regards,

Joel Jacobson
Glue Finance

On Mon, Jan 3, 2011 at 2:46 PM, Joel Jacobson <joel@gluefinance.com> wrote:
> My major concern of parsing the schema file is I would never fully
> trust the output from the script, even if the regex is extremely
> paranoid and really strict, there is still a risk it contains a bug.

That could possibly be resolved by using the custom or tar formats.

> If you cannot trust the output from the schema parse script, it's not
> safe to use it to do a partial restore of objects.
> Let's say you want to restore only a few functions from your backup
> schema.

I don't think this is the right solution to that problem; or at least,
it's not the solution I want personally.  I want something like
--function=glob (and yes I do want that same thing for every other
object type, too, but PARTICULARLY for functions).  This might be a
next-best solution to that problem, but I'm still holding out for the
home run.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> I have to admit I'm a bit unsold on the approach as well.  It seems
> like you could write a short Perl script which would transform a text
> format dump into the proposed format pretty easily, and if you did
> that and published the script, then the next poor shmuck who had the
> same problem could either use the script as-is or hack it up to meet
> some slightly different set of requirements.  Or maybe you'd be better
> off basing such a script on the custom or tar format instead, in order
> to avoid the problem of misidentifying a line beginning with --- as a
> comment when it's really part of a data item.  Or maybe even writing a
> whole "schema diff" tool that would take two custom-format dumps as
> inputs.
>
> On the other hand, I can certainly think of times when even a pretty
> dumb implementation of this would have saved me some time.

You mean like those:
 https://labs.omniti.com/labs/pgtreats/wiki/getddl https://github.com/dimitri/getddl

In this case hacking the perl version was impossible for me to hack on
so my version is in python, but still is somewhat of a fork.  I even
have an intermediate shell version somewhere that a colleague wrote, but
I much prefer maintaining python code.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On Mon, Jan 3, 2011 at 3:15 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
>> On the other hand, I can certainly think of times when even a pretty
>> dumb implementation of this would have saved me some time.
>
> You mean like those:
>
>  https://labs.omniti.com/labs/pgtreats/wiki/getddl
>  https://github.com/dimitri/getddl

That's not exactly what I was trying to do, but it's in the ballpark...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Sent from my iPhone

On 28 dec 2010, at 21:45, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:

The problem I see with suffixing a sequence id to the objects with name collision is that one day the dump may name myfunc(int) as myfunc.sql and after an overloaded version is created, say myfunc(char, int), then the same myfunc(int) may be dumped in myfunc-2.sql, which again is non-deterministic.

I agree, good point!

Perhaps abbreviations are to prefer, e.g., myfunc_i, myfunc_i_c, etc to reduce the need of truncating filenames.

Also, it is a project policy that we do not introduce new features in back branches, so spending time on an 8.4.6 patch may not be the best use of your time.

My company is using 8.4 and needs this feature, so I'll have to patch it anyway :)

On 28.12.2010 23:51, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.net>  writes:
>> On 12/28/2010 04:44 PM, Joel Jacobson wrote:
>>> Perhaps abbreviations are to prefer, e.g., myfunc_i, myfunc_i_c, etc
>>> to reduce the need of truncating filenames.
>> I think that's just horrible. Does the i stand for integer or inet? And
>> it will get *really* ugly for type names with spaces in them ...
> You think spaces are bad, try slashes ;-)
>
> Not to mention the need for including schemas in typenames sometimes.
> I think you're going to have a real problem trying to fully describe a
> function's signature in a file name of reasonable max length.
something like

funcname_<number-of-arguments>_<hash_of_argument_type_list>.sql

seems like a reasonable compromise - you can find the function you are 
looking for without too much searching, even when overloaded and the 
uniqueness is still guaranteed.

--------------------------------------------
Hannu Krosing
http://www.2ndQuadrant.com/books/

On 28.12.2010 17:00, Joel Jacobson wrote:
> Dear fellow hackers,
>
> Problem: A normal diff of two slightly different schema dump files 
> (pg_dump -s), will not produce a user-friendly diff, as you get all 
> changes in the same file.
>
Another Solution: I have used a python script for spliiting "dump -s" 
output into a directory structure for years

A structure that seems to work well is

1. <database_name>.load.sql - file containing \i for all other files
2. tree of
schema1\    +- <objtype1>\    |    + obj_of_type1_name1.sql    |    + ...    +-<objtype2>\ ...

That is, first have one directory per schema on top level and then one 
for each type of objects
inside have one file per object.
Some things which may have non-unique names, like functions need extra 
"uniquefication", like adding a underscore (or colon) separated list of 
argument types at the end.

Most times you want to  leave out the comment lines with OIDs so that 
you can diff the files against another version

------------------------------------------
Hannu Krosing
http://www.2ndQuadrant.com/books/

Joel Jacobson wrote:
> To understand a change to my database functions, I would start by 
> looking at the top-level, only focusing on the names of the functions 
> modified/added/removed.
> At this stage, you want as little information as possible about each 
> change, such as only the names of the functions.
> To do this, get a list of changes functions, you cannot compare two 
> full schema plain text dumps using diff, as it would only reveal the 
> lines changed, not the name of the functions, unless you are lucky to 
> get the name of the function within the (by default) 3 lines of copied 
> context.
>
> While you could increase the number of copied lines of context to a 
> value which would ensure you would see the name of the function in the 
> diff, that is not feasible if you want to quickly "get a picture" of 
> the code areas modified, since you would then need to read through 
> even more lines of diff output.

I can agree on some use cases you've outlined, where there's merit to 
the general idea of your patch.  But as an aside, you really should 
launch an investigation into some better diff tools if this is how 
you're doing this type of work.  Last week I reviewed 3K lines worth of 
changes from two versions of a 12K line schema dump I'd never seen 
before in a couple of hours using kdiff3.  I'd have killed myself before 
finishing if I had to do the same job with traditional diff as you're 
describing it here.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

On Mon, Jan 3, 2011 at 2:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Jan 3, 2011 at 1:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Yeah, that's exactly it.  I can think of some possible uses for
>>> splitting up pg_dump output, but frankly "to ease diff-ing" is not
>>> one of them.  For that problem, it's nothing but a crude kluge that
>>> only sort-of helps.  If we're to get anywhere on this, we need a
>>> better-defined problem statement that everyone can agree is worth
>>> solving and is well solved with this particular approach.
>
>> I have to admit I'm a bit unsold on the approach as well.  It seems
>> like you could write a short Perl script which would transform a text
>> format dump into the proposed format pretty easily, and if you did
>> that and published the script, then the next poor shmuck who had the
>> same problem could either use the script as-is or hack it up to meet
>> some slightly different set of requirements.  Or maybe you'd be better
>> off basing such a script on the custom or tar format instead, in order
>> to avoid the problem of misidentifying a line beginning with --- as a
>> comment when it's really part of a data item.  Or maybe even writing a
>> whole "schema diff" tool that would take two custom-format dumps as
>> inputs.
>
>> On the other hand, I can certainly think of times when even a pretty
>> dumb implementation of this would have saved me some time.
>
> The basic objection that I have to this patch is that it proposes to
> institutionalize a pretty dumb implementation.  And, as you mentioned,
> once it's in there it'll be more or less set in stone because we aren't
> going to want to support umpteen variants.
>
> I like the idea of a postprocessing script a lot better --- it seems
> like it wouldn't get in the way of people making their own variants.
> And as you say it'd likely be pretty trivial to do.

I notice that this patch is marked as "Needs Review" in the CommitFest
application, but I think it's fair to say that there's no consensus to
commit something along these lines.  Accordingly, I'm going to mark it
"Returned with Feedback".  There is clearly a need for better tooling
in this area, but I think there's a great deal of legitimate doubt
about whether this is the right solution to that problem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

Now that the (at least as far as I know) last ordering problem in
pg_dump has been solved [1], I'm going to attempt resurrecting this old
thread.

It seemed to me that the biggest objections to this patch in the old
discussions were directed at the implementation, which I have tried to
improve.  The attached patch implements the actual splitting in a new
backup format.

The general output scheme looks like this:
    schemaname/OBJECT_TYPES/object_name.sql,

but there are some exceptions.

Overloaded functions are dumped into the same file.  Object names are
encoded into the POSIX Portable Filename Character Set ([a-z0-9._-]) by
replacing any characters outside that set with an underscore.

Restoring the dump is supported through an index.sql file containing
statements which include (through \i) the actual object files in the
dump directory.

Any thoughts?  Objections on the idea or the implementation?


[1]:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7b583b20b1c95acb621c71251150beef958bb603


Regards,
Marko Tiikkaja

Hi,

Marko Tiikkaja <pgmail@joh.to> writes:
> The general output scheme looks like this:
>    schemaname/OBJECT_TYPES/object_name.sql,

I like this feature, I actually did have to code it myself in the past
and several other people did so, so we already have at least 3 copies of
`getddl` variants around. I really think this feature should be shipped
by default with PostgreSQL.

I don't much care for the all uppercase formating of object type
directories in your patch though.

> Overloaded functions are dumped into the same file.  Object names are
> encoded into the POSIX Portable Filename Character Set ([a-z0-9._-]) by
> replacing any characters outside that set with an underscore.

What happens if you have a table foo and another table "FoO"?

> Restoring the dump is supported through an index.sql file containing
> statements which include (through \i) the actual object files in the dump
> directory.

I think we should be using \ir now that we have that.

> Any thoughts?  Objections on the idea or the implementation?

As far as the implementation goes, someone with more experience on the
Archiver Handles should have a look. To me, it looks like you are trying
to shoehorn your feature in the current API and that doesn't feel good.

The holly grail here that we've been speaking about in the past would be
to separate out tooling and formats so that we have:
  pg_dump | pg_restore  pg_export | psql

In that case we would almost certainly need libpgdump to share the code,
and we maybe could implement a binary output option for pg_dump too
(yeah, last time it was proposed we ended up with bytea_output = 'hex').

That libpgdump idea basically means we won't have the --split feature in
9.3, and that's really bad, as we already are some releases late on
delivering that, in my opinion.

Maybe the pg_export and pg_dump tool could share code by just #include
magic rather than a full blown lib in a first incantation?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Hi,

On 16/11/2012 15:52, Dimitri Fontaine wrote:
> Marko Tiikkaja <pgmail@joh.to> writes:
>> The general output scheme looks like this:
>>     schemaname/OBJECT_TYPES/object_name.sql,
>
> I like this feature, I actually did have to code it myself in the past
> and several other people did so, so we already have at least 3 copies of
> `getddl` variants around. I really think this feature should be shipped
> by default with PostgreSQL.
>
> I don't much care for the all uppercase formating of object type
> directories in your patch though.

*shrug*  I have no real preference to one way or the other.

>> Overloaded functions are dumped into the same file.  Object names are
>> encoded into the POSIX Portable Filename Character Set ([a-z0-9._-]) by
>> replacing any characters outside that set with an underscore.
>
> What happens if you have a table foo and another table "FoO"?

They would go to the same file.  If you think there are technical issues 
behind that decision (e.g. the dump would not restore), I would like to 
hear an example case.

On the other hand, some people might find it preferrable to have them in 
different files (for example foo, foo.1, foo.2 etc).  Or some might 
prefer some other naming scheme.  One of the problems with this patch is 
exactly that people prefer different things, and providing switches for 
all of the different options people come up with would mean a lot of 
switches. :-(

>> Restoring the dump is supported through an index.sql file containing
>> statements which include (through \i) the actual object files in the dump
>> directory.
>
> I think we should be using \ir now that we have that.

Good point, will have to get that fixed.

>> Any thoughts?  Objections on the idea or the implementation?
>
> As far as the implementation goes, someone with more experience on the
> Archiver Handles should have a look. To me, it looks like you are trying
> to shoehorn your feature in the current API and that doesn't feel good.

It feels a bit icky to me too, but I didn't feel comfortable with 
putting in a lot of work to refactor the API because of how 
controversial this feature is.

> The holly grail here that we've been speaking about in the past would be
> to separate out tooling and formats so that we have:
>
>     pg_dump | pg_restore
>     pg_export | psql
>
> In that case we would almost certainly need libpgdump to share the code,
> and we maybe could implement a binary output option for pg_dump too
> (yeah, last time it was proposed we ended up with bytea_output = 'hex').

While I agree that this idea - when implemented - would be nicer in 
practically every way, I'm not sure I want to volunteer to do all the 
necessary work.

> That libpgdump idea basically means we won't have the --split feature in
> 9.3, and that's really bad, as we already are some releases late on
> delivering that, in my opinion.
>
> Maybe the pg_export and pg_dump tool could share code by just #include
> magic rather than a full blown lib in a first incantation?

That's one idea..


Regards,
Marko Tiikkaja

Marko Tiikkaja <pgmail@joh.to> writes:
>> What happens if you have a table foo and another table "FoO"?
>
> They would go to the same file.  If you think there are technical issues
> behind that decision (e.g. the dump would not restore), I would like to hear
> an example case.

I didn't try the patch itself yet so I wanted to hear that's something
you did actually try out, that's about it :) As soon as we're past the
initial agreement on this patch landing in (which I really want to see
happen), I'll devote some time on testing it.

> On the other hand, some people might find it preferrable to have them in
> different files (for example foo, foo.1, foo.2 etc).  Or some might prefer
> some other naming scheme.  One of the problems with this patch is exactly
> that people prefer different things, and providing switches for all of the
> different options people come up with would mean a lot of switches. :-(

I think this facility should provide something simple and useful, and
not something tasty. Database backups and exports are not meant to cater
with taste, they are meant to be easy to restore. In that very case, we
want to have a set of properly organized SQL files for doing partial
restores, right?

> It feels a bit icky to me too, but I didn't feel comfortable with putting in
> a lot of work to refactor the API because of how controversial this feature
> is.

+1

>>     pg_dump | pg_restore
>>     pg_export | psql
>
> While I agree that this idea - when implemented - would be nicer in
> practically every way, I'm not sure I want to volunteer to do all the
> necessary work.

What I think needs to happen now is a commiter's buy in that we want to
get there at some point and that your current patch is not painting us
into any corner now. So that we can accept it and have a documented path
forward.

Regards,
--
Dimitri Fontaine                                        06 63 07 10 78
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
>>>     pg_dump | pg_restore
>>>     pg_export | psql
>>
>> While I agree that this idea - when implemented - would be nicer in
>> practically every way, I'm not sure I want to volunteer to do all the
>> necessary work.
>
> What I think needs to happen now is a commiter's buy in that we want to
> get there at some point and that your current patch is not painting us
> into any corner now. So that we can accept it and have a documented path
> forward.

Just stumbled accross this message while reading some older threads
about the current topic:
 http://archives.postgresql.org/pgsql-hackers/2010-12/msg02496.php

Where Robert Treat said:
> I've both enjoyed reading this thread and seeing this wheel reinvented
> yet again, and wholeheartedly +1 the idea of building this directly
> into pg_dump. (The only thing better would be to make everything thing
> sql callable, but that's a problem for another day).

I know Andrew has been working on his "Retail DDL" project which is
basically a bunch of server-side functions that spits out SQL object
definitions. Andrew, were you able to make progress on that project?

On the other hand, pg_dump -Fs still is something I would like to have
as a complement to Andrew's facility.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On 11/19/2012 09:07 AM, Dimitri Fontaine wrote:
> Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
>>>>      pg_dump | pg_restore
>>>>      pg_export | psql
>>> While I agree that this idea - when implemented - would be nicer in
>>> practically every way, I'm not sure I want to volunteer to do all the
>>> necessary work.
>> What I think needs to happen now is a commiter's buy in that we want to
>> get there at some point and that your current patch is not painting us
>> into any corner now. So that we can accept it and have a documented path
>> forward.
> Just stumbled accross this message while reading some older threads
> about the current topic:
>
>    http://archives.postgresql.org/pgsql-hackers/2010-12/msg02496.php
>
> Where Robert Treat said:
>> I've both enjoyed reading this thread and seeing this wheel reinvented
>> yet again, and wholeheartedly +1 the idea of building this directly
>> into pg_dump. (The only thing better would be to make everything thing
>> sql callable, but that's a problem for another day).
> I know Andrew has been working on his "Retail DDL" project which is
> basically a bunch of server-side functions that spits out SQL object
> definitions. Andrew, were you able to make progress on that project?
>
> On the other hand, pg_dump -Fs still is something I would like to have
> as a complement to Andrew's facility.
>


No, sorry, it's on hold - I'm finding trouble finding time to work on it.

cheers

andrew

Marko Tiikkaja wrote:

> On 16/11/2012 15:52, Dimitri Fontaine wrote:
> >
> >What happens if you have a table foo and another table "FoO"?
>
> They would go to the same file.  If you think there are technical
> issues behind that decision (e.g. the dump would not restore), I
> would like to hear an example case.

create table foo (a int, b text);
create type bar as (stuff foo);
create table "FoO" (more bar);


--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services