Thread: pg_dump: Sorted output, referential integrity statements

Hi,

While daily using pg_dump for a while the need for the following
features grew significantly.
I finally came to the point of implementing them myself ;-)

- pg_dump outputs the data unsorted but to manage the data in a version
control system you need it consistently sorted. So a flag to sort by
either primary key or left to right would be of great value. (--sorted
?)

- pg_dump outputs referential constraints as 3 triggers (near to two
different tables) per constraint. A mode which outputs the original
statement (alter table ... add constraint) would be more sql standard
conformant, portable and readable. But ... you might get into trouble if
the referenced table creation command is output later.

If we call this switch --sql-standard it might also prefer the short
(standard compliant) form for index creation [create index X on Y(Z,W)]
and some other things.

So, I'm kindly asking for your opinion regarding this two features.
Does anybody plan to implement them? Do you have reasons against?
  Christof

On Thu, 6 Dec 2001, Christof Petig wrote:

> - pg_dump outputs referential constraints as 3 triggers (near to two
> different tables) per constraint. A mode which outputs the original
> statement (alter table ... add constraint) would be more sql standard
> conformant, portable and readable. But ... you might get into trouble if
> the referenced table creation command is output later.

There's some interesting timing things with this.  Pretty much the
alter statements have to be after the creates for all the tables at least
due to recursive constraints.  When you're using insert statements (-d)
since the restore doesn't appear to be in a transaction, all the data
needs to have been loaded as well (again due to recursive constraints).
In fact, there's *no* guarantee that even with a transaction that a
restore of the current database state statement by statement will succeed
since the user may have done odd things to insert the data.
If the data's already there, the alter table is going to check each row
for validity which can be kinda slow right now on big restores, we'd
probably need to make a better check.

At 12:05 6/12/01 +0100, Christof Petig wrote:
>
>- pg_dump outputs the data unsorted

Not quite correct; it outputs them in an order that is designed to improve
the chances of dependencies being satisfied, and improve the performance of
a full restore (a modified OID order).

> but to manage the data in a version
>control system you need it consistently sorted. So a flag to sort by
>either primary key or left to right would be of great value. (--sorted
>?)

Not really very generalizable when you consider user defined types,
triggers etc.


>- pg_dump outputs referential constraints as 3 triggers (near to two
>different tables) per constraint. A mode which outputs the original
>statement (alter table ... add constraint) would be more sql standard

Abosolutely; with time we are moving pg_dump to use standard SQL.


>So, I'm kindly asking for your opinion regarding this two features.
>Does anybody plan to implement them?

No plans for the first one, but sorting by ('object-type', 'object-name')
would be close to trivial, if there is much interest/support for it.

The second (SQL conformance) is high on my list; a few people (Chris &
Stephen?) have been working hard to implement 'alter table add/etc
constraint'. When this is stable, we will move pg_dump in that direction.
But as of 7.1, there were still wrinkles in the the implementation that
meant it was unsuitable for pg_dump. Not sure about the status in 7.2.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Stephan Szabo wrote:

Since nobody answered concerning the sort issue, I guess
- nobody is planning or implementing this
- nobody disagrees this might be handy to have


> On Thu, 6 Dec 2001, Christof Petig wrote:
>
> > - pg_dump outputs referential constraints as 3 triggers (near to two
> > different tables) per constraint. A mode which outputs the original
> > statement (alter table ... add constraint) would be more sql standard
> > conformant, portable and readable. But ... you might get into trouble if
> > the referenced table creation command is output later.
>
> There's some interesting timing things with this.  Pretty much the
> alter statements have to be after the creates for all the tables at least
> due to recursive constraints.  When you're using insert statements (-d)
> since the restore doesn't appear to be in a transaction, all the data
> needs to have been loaded as well (again due to recursive constraints).
> In fact, there's *no* guarantee that even with a transaction that a
> restore of the current database state statement by statement will succeed
> since the user may have done odd things to insert the data.
> If the data's already there, the alter table is going to check each row
> for validity which can be kinda slow right now on big restores, we'd
> probably need to make a better check.

The propose was mainly made to make the output more readable if you dump a
single table (per pg_dump call). This would also use portable sql commands so
it's easier to migrate data (given that you also specify -D).

Yours  Christof

Philip Warner wrote:

Ah, yes. Now I remember it was you improving pg_dump.

> At 12:05 6/12/01 +0100, Christof Petig wrote:
> >
> >- pg_dump outputs the data unsorted
>
> Not quite correct; it outputs them in an order that is designed to improve
> the chances of dependencies being satisfied, and improve the performance of
> a full restore (a modified OID order).

That's perfect - unless you want to diff two pg_dumps

> > but to manage the data in a version
> >control system you need it consistently sorted. So a flag to sort by
> >either primary key or left to right would be of great value. (--sorted
> >?)
>
> Not really very generalizable when you consider user defined types,
> triggers etc.

Hmmm. But if we have a primary key on columns (A,B,C) and request the data
'order by A,B,C' this should be portable, shouldn't it?
If we don't have a primary key simply ordering by 1,2,3,...n should also work.
Or am I missing something?

> >- pg_dump outputs referential constraints as 3 triggers (near to two
> >different tables) per constraint. A mode which outputs the original
> >statement (alter table ... add constraint) would be more sql standard
>
> Abosolutely; with time we are moving pg_dump to use standard SQL.

Great news.

> >So, I'm kindly asking for your opinion regarding this two features.
> >Does anybody plan to implement them?
>
> No plans for the first one, but sorting by ('object-type', 'object-name')
> would be close to trivial, if there is much interest/support for it.

I don't understand what you mean by 'sorting by object-type/name', can you
give me an example. Simply adding an (optional) order by clause was the one I
intended.

> The second (SQL conformance) is high on my list; a few people (Chris &
> Stephen?) have been working hard to implement 'alter table add/etc
> constraint'. When this is stable, we will move pg_dump in that direction.
> But as of 7.1, there were still wrinkles in the the implementation that
> meant it was unsuitable for pg_dump. Not sure about the status in 7.2.

Oh, I was targeting 7.2. I can not surely tell about 7.2, but have seen cvs
logs implementing similar things.
   Christof

On Fri, Dec 07, 2001 at 03:16:26PM +0100, Christof Petig wrote:
> Philip Warner wrote:
> 
> Ah, yes. Now I remember it was you improving pg_dump.
> 
> > At 12:05 6/12/01 +0100, Christof Petig wrote:
> > >
> > >- pg_dump outputs the data unsorted
> >
> > Not quite correct; it outputs them in an order that is designed to improve
> > the chances of dependencies being satisfied, and improve the performance of
> > a full restore (a modified OID order).
> 
> That's perfect - unless you want to diff two pg_dumps

I've ran into this myself. However, I've never wanted to diff a full dump,
usually just schema comparisions - I usually _know_ which database has
the current data, I just want to be sure I can move it over. For schema
comparisions, it's easy enough to generate a 'diffable' file that reflects
the schema, something like:

select relname||'.'||attname from pg_class c, pg_attribute a
where attrelid=c.oid and attnum >0 and relname !~ '^pg' order by
relname,attname;

Hmm, I do see that sometimes it'd be nice to do a full diff, really. The
'oid order' was a nice hack to avoid having to do a full dependency
analysis on db objects, but they're not stable. I think with oids
going away as much as possible, anyway, we're probably going to have
to bite the bullet and do dependencies, one way or another. There are
a number of features that are often requested that all boil down to
knowing dependencies: dropping the auto-generated sequence for a serial,
along with the table - reparsing various functions/views/etc. when the
underlying tables are modified, etc.

Ross

At 15:16 7/12/01 +0100, Christof Petig wrote:
>>
>> Not really very generalizable when you consider user defined types,
>> triggers etc.
>
>Hmmm. But if we have a primary key on columns (A,B,C) and request the data
>'order by A,B,C' this should be portable, shouldn't it?
>If we don't have a primary key simply ordering by 1,2,3,...n should also
work.
>Or am I missing something?

My mistake; I thought you wanted to compare metadata. Sorting data by PK
seems like a reasonable thing to do.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

At 10:02 8/12/01 +1100, Philip Warner wrote:
>At 15:16 7/12/01 +0100, Christof Petig wrote:
>>>
>>> Not really very generalizable when you consider user defined types,
>>> triggers etc.
>>
>>Hmmm. But if we have a primary key on columns (A,B,C) and request the data
>>'order by A,B,C' this should be portable, shouldn't it?
>>If we don't have a primary key simply ordering by 1,2,3,...n should also
>work.
>>Or am I missing something?
>
>My mistake; I thought you wanted to compare metadata. Sorting data by PK
>seems like a reasonable thing to do.
>

To make the dump diff-able, we probably need to sort the metadata by type &
name as well.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

> > > but to manage the data in a version
> > >control system you need it consistently sorted. So a flag to sort by
> > >either primary key or left to right would be of great value. (--sorted
> > >?)
> >
> > Not really very generalizable when you consider user defined types,
> > triggers etc.
>
> Hmmm. But if we have a primary key on columns (A,B,C) and request the data
> 'order by A,B,C' this should be portable, shouldn't it?
> If we don't have a primary key simply ordering by 1,2,3,...n
> should also work.
> Or am I missing something?

I can see how ordering a dump by the primary key would be a neat way of
'clustering' your data after a restore, however I have qualms about the
scalability of such a scheme.  What if someone has a 100GB table?  They may
have arranged things so that they never get a sort from it or something, or
it might take ages.  However I guess if it's an optional parameter it might
be neat.

My feeling is that it won't happen unless you actually code it into a patch
that makes it a parameter to pg_dump.  Having an actual patch is a great way
of getting something you want done ;)

Alternatively, have you tried just writing a PERL script (or some clever sed
script) that will just sort the COPY FROM sections...?

Chris

Christopher Kings-Lynne wrote:

> > > > but to manage the data in a version
> > > >control system you need it consistently sorted. So a flag to sort by
> > > >either primary key or left to right would be of great value. (--sorted
> > > >?)
> > >
> > > Not really very generalizable when you consider user defined types,
> > > triggers etc.
> >
> > Hmmm. But if we have a primary key on columns (A,B,C) and request the data
> > 'order by A,B,C' this should be portable, shouldn't it?
> > If we don't have a primary key simply ordering by 1,2,3,...n
> > should also work.
> > Or am I missing something?
>
> I can see how ordering a dump by the primary key would be a neat way of
> 'clustering' your data after a restore, however I have qualms about the
> scalability of such a scheme.  What if someone has a 100GB table?  They may
> have arranged things so that they never get a sort from it or something, or
> it might take ages.  However I guess if it's an optional parameter it might
> be neat.
>
> My feeling is that it won't happen unless you actually code it into a patch
> that makes it a parameter to pg_dump.  Having an actual patch is a great way
> of getting something you want done ;)
>
> Alternatively, have you tried just writing a PERL script (or some clever sed
> script) that will just sort the COPY FROM sections...?

That's beyond my perl skills. And I believe sed to be not the right tool. (hmm,
perhaps split (at 'COPY FROM' and at '\.'), then sort, then cat ... many
(perhaps big) temporary files, let the db do the hard work)

But making a patch to pg_dump is a matter of (say) up to 4 hours.
I'll do it since you seem to like it and nobody started doing it so far.

Christof

> But making a patch to pg_dump is a matter of (say) up to 4 hours.
> I'll do it since you seem to like it and nobody started doing it so far.

Well, I'm in no way a major developer, so even if I do like it, I don't know
what the chances are of it making its way into the tree.

Chris

Christopher Kings-Lynne wrote:

> > But making a patch to pg_dump is a matter of (say) up to 4 hours.
> > I'll do it since you seem to like it and nobody started doing it so far.
>
> Well, I'm in no way a major developer, so even if I do like it, I don't know
> what the chances are of it making its way into the tree.

If I stop using C++ comments '//', the chance might grew better ;-) [I
apologize again]

Since Philip also likes it ...
I would say it's a good feature to have and up to now most of my patches went
into the tree. So the chances are not that bad (though definitely not for 7.2).
   Christof

> The second (SQL conformance) is high on my list; a few people (Chris &
> Stephen?) have been working hard to implement 'alter table add/etc
> constraint'. When this is stable, we will move pg_dump in that direction.
> But as of 7.1, there were still wrinkles in the the implementation that
> meant it was unsuitable for pg_dump. Not sure about the status in 7.2.

Well, the biggest thing I see on using alter table add constraint for
foreign keys is the expense involved if you do it after the tables are
populated. I chose the theoretical cleanliness of checking each row
using the code we had over the speed of doing a special check for the
alter table case, although I'm considering reversing that for 7.3 to make
the alter table more reasonable and make it possible for you to consider
doing it.

At 13:34 7/12/01 -0800, Stephan Szabo wrote:
>
>Well, the biggest thing I see on using alter table add constraint for
>foreign keys is the expense involved if you do it after the tables are
>populated.

Is it really worse than loading the tables with the constraint in place?


>I chose the theoretical cleanliness of checking each row
>using the code we had over the speed of doing a special check for the
>alter table case,

Out of curiosity - what was the difference?

By the sounds of it, we may get 'alter table' in pg_dump by 7.3 or 7.4.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

On Tue, 11 Dec 2001, Philip Warner wrote:

> At 13:34 7/12/01 -0800, Stephan Szabo wrote:
> >
> >Well, the biggest thing I see on using alter table add constraint for
> >foreign keys is the expense involved if you do it after the tables are
> >populated.
>
> Is it really worse than loading the tables with the constraint in place?

I'd say its better than while loading, but currently the check isn't
performed at all I think, because the create constraint trigger
statements are after data load and they don't check the data at all.
At least that's how I remember it, I could be wrong.

> >I chose the theoretical cleanliness of checking each row
> >using the code we had over the speed of doing a special check for the
> >alter table case,
>
> Out of curiosity - what was the difference?

The check could be performed in a single statment on the fktable with
a not exists (limit 1). I've sort of hoped that the optimizer would
be able to potentially pick a better plan than run the subselect once
for every row in the fktable. :)  But at the time, I wasn't comfortable
with mucking with the triggers themselves, and that would have meant
having two things that each had a copy of the fk check logic.

> By the sounds of it, we may get 'alter table' in pg_dump by 7.3 or 7.4.

That'd be cool. :)

Christof Petig wrote:

> Christopher Kings-Lynne wrote:
>
> > > But making a patch to pg_dump is a matter of (say) up to 4 hours.
> > > I'll do it since you seem to like it and nobody started doing it so far.
> >
> > Well, I'm in no way a major developer, so even if I do like it, I don't know
> > what the chances are of it making its way into the tree.
>
> If I stop using C++ comments '//', the chance might grew better ;-) [I
> apologize again]
>
> Since Philip also likes it ...
> I would say it's a good feature to have.

Here's the patch. It's not as efficient as it might be (if dumpTable_order_by had
indinfo around) but it works. I'm not clear about quoting when using sorted output
in 'COPY' style. So if anybody has good test cases around (tables with strange
characters), please check it.

Also I don't know whether the sorting behaviour is sensible when it comes to
inheritance. Can someone using inheritance please check it.

If you like the patch I'll provide documentation patches.

-----

This patch implements:
   -T alias '--sort'  which sorts by primary key / the columns in output order

   Yours
      Christof

Stephan Szabo wrote:
>
> On Tue, 11 Dec 2001, Philip Warner wrote:
>
> > At 13:34 7/12/01 -0800, Stephan Szabo wrote:
> > >
> > >Well, the biggest thing I see on using alter table add constraint for
> > >foreign keys is the expense involved if you do it after the tables are
> > >populated.
> >
> > Is it really worse than loading the tables with the constraint in place?
>
> I'd say its better than while loading, but currently the check isn't
> performed at all I think, because the create constraint trigger
> statements are after data load and they don't check the data at all.
> At least that's how I remember it, I could be wrong.
   You're  not.  This  discussion came up a couple of times, and   the answer is allways the same.
   We don't want to define  the  constraints  with  ALTER  TABLE   because this means checking data on restore that
doesn'tneed   to be checked at all (in theory).  If he has  a  crash  of  a   critical system and restores from a dump,
Ibet the farm that   he wants it FAST.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Tue, 11 Dec 2001, Jan Wieck wrote:

> Stephan Szabo wrote:
> >
> > On Tue, 11 Dec 2001, Philip Warner wrote:
> >
> > > At 13:34 7/12/01 -0800, Stephan Szabo wrote:
> > > >
> > > >Well, the biggest thing I see on using alter table add constraint for
> > > >foreign keys is the expense involved if you do it after the tables are
> > > >populated.
> > >
> > > Is it really worse than loading the tables with the constraint in place?
> >
> > I'd say its better than while loading, but currently the check isn't
> > performed at all I think, because the create constraint trigger
> > statements are after data load and they don't check the data at all.
> > At least that's how I remember it, I could be wrong.
>
>     You're  not.  This  discussion came up a couple of times, and
>     the answer is allways the same.
>
>     We don't want to define  the  constraints  with  ALTER  TABLE
>     because this means checking data on restore that doesn't need
>     to be checked at all (in theory).  If he has  a  crash  of  a
>     critical system and restores from a dump, I bet the farm that
>     he wants it FAST.

I'd say as an optional parameter to dump, it's definately not a bad idea
(like the idea of a --sql or whatever) since the user has to explicitly
ask for it. I think for the rest of the cases it comes down to what people
want it to do.

At 10:34 11/12/01 -0500, Jan Wieck wrote:
>
>    We don't want to define  the  constraints  with  ALTER  TABLE
>    because this means checking data on restore that doesn't need
>    to be checked at all (in theory).  If he has  a  crash  of  a
>    critical system and restores from a dump, I bet the farm that
>    he wants it FAST.

This is just an argument for (a) using ALTER TABLE (since it will 
also prevent PK indexes being created, and make it FASTer), and 
(b) the ability to 'SET ALL CONSTRAINTS OFF' (or similar) to 
prevent the ALTER TABLE from forcing validation of the constraint.

The current situation of creating constraint triggers is IMO not 
acceptable in the long term.

There are also enough people who just restore one table to warrant
the ability for pg_dump to optionally run with constraints ON.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

If you're going to allow bypassing data integrity checks (great for
speed!) perhaps one should be introduced to quickly confirm the
integrity of the file itself?  A checksum on the first line will
validate the contents through the rest of the file.  It'll take a few
minutes to confirm a multi-GB sized file but in comparison to load
time it may be worthwhile to look into.

That way you can ensure it's the same as it was when it was dumped and
fsck or other accidental editing didn't remove the middle of it.
Intentional modifications won't be stopped but backups should be
treated the same as the database is security wise.

--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Philip Warner" <pjw@rhyme.com.au>
To: "Jan Wieck" <janwieck@yahoo.com>; "Stephan Szabo"
<sszabo@megazone23.bigpanda.com>
Cc: "Christof Petig" <christof@petig-baender.de>; "PostgreSQL Hackers"
<pgsql-hackers@postgresql.org>
Sent: Tuesday, December 11, 2001 10:03 PM
Subject: Re: [HACKERS] pg_dump: Sorted output, referential integrity


> At 10:34 11/12/01 -0500, Jan Wieck wrote:
> >
> >    We don't want to define  the  constraints  with  ALTER  TABLE
> >    because this means checking data on restore that doesn't need
> >    to be checked at all (in theory).  If he has  a  crash  of  a
> >    critical system and restores from a dump, I bet the farm that
> >    he wants it FAST.
>
> This is just an argument for (a) using ALTER TABLE (since it will
> also prevent PK indexes being created, and make it FASTer), and
> (b) the ability to 'SET ALL CONSTRAINTS OFF' (or similar) to
> prevent the ALTER TABLE from forcing validation of the constraint.
>
> The current situation of creating constraint triggers is IMO not
> acceptable in the long term.
>
> There are also enough people who just restore one table to warrant
> the ability for pg_dump to optionally run with constraints ON.
>
>
> ----------------------------------------------------------------
> Philip Warner                    |     __---_____
> Albatross Consulting Pty. Ltd.   |----/       -  \
> (A.B.N. 75 008 659 498)          |          /(@)   ______---_
> Tel: (+61) 0500 83 82 81         |                 _________  \
> Fax: (+61) 0500 83 82 82         |                 ___________ |
> Http://www.rhyme.com.au          |                /           \|
>                                  |    --________--
> PGP key available upon request,  |  /
> and from pgp5.ai.mit.edu:11371   |/
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Jan Wieck writes:

>     We don't want to define  the  constraints  with  ALTER  TABLE
>     because this means checking data on restore that doesn't need
>     to be checked at all (in theory).  If he has  a  crash  of  a
>     critical system and restores from a dump, I bet the farm that
>     he wants it FAST.

Um, if he has a *crash* of a *critical* system, doesn't he want his data
checked before he puts it back online?

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut wrote:
> Jan Wieck writes:
>
> >     We don't want to define  the  constraints  with  ALTER  TABLE
> >     because this means checking data on restore that doesn't need
> >     to be checked at all (in theory).  If he has  a  crash  of  a
> >     critical system and restores from a dump, I bet the farm that
> >     he wants it FAST.
>
> Um, if he has a *crash* of a *critical* system, doesn't he want his data
> checked before he puts it back online?
   The  data  came  (in  theory!!!)  from  an intact, consistent   database.  So the dump content is (theoretically)
knownto be   consistent, thus no check required.
 
   The  difference  between  theory and practice? There is none,   theoretically :-)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com