Horton

Thread

  1. Proposed patch: make pg_dump --data-only consider FK constraints

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-09-07T18:06:40Z

    Okay, I got tired of seeing people complain about foreign-key constraint
violations in data-only dumps.  While it's true that the problem can't
be solved in the general case (because of potentially circular
references), we could certainly make pg_dump at least *try* to order the
tables according to foreign key relationships.  It turns out not to even
require a whole lot of new code.  Accordingly I propose the attached
patch.  It will order the tables safely if it can, and otherwise
complain like this:

pg_dump: WARNING: circular foreign-key constraints among these table(s):
pg_dump:   master
pg_dump:   child
pg_dump: You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.

Comments?

			regards, tom lane

  2. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2008-09-07T18:50:56Z

    Tom Lane wrote:
> Okay, I got tired of seeing people complain about foreign-key constraint
> violations in data-only dumps.  While it's true that the problem can't
> be solved in the general case (because of potentially circular
> references), we could certainly make pg_dump at least *try* to order the
> tables according to foreign key relationships.  It turns out not to even
> require a whole lot of new code.  Accordingly I propose the attached
> patch.  It will order the tables safely if it can, and otherwise
> complain like this:

+1

> pg_dump: WARNING: circular foreign-key constraints among these table(s):
> pg_dump:   master
> pg_dump:   child
> pg_dump: You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.

WARNING feels a bit too strong. I realize that that message isn't going 
to the postmaster's log, bloating it, but if a user does that regularly, 
always disabling triggers as instructed, or there is in fact never 
circular references in the data with a particular schema, seeing that 
big fat warning every time is going to become a bit tiresome. Perhaps 
"NOTE: ..." ?

How about printing that notice at the top of the dump file as well? Most 
people probably don't look at the dump files, but if someone needs to 
deal with a data-only dumps that contain circular constraints, and also 
those that don't, it would be invaluable information.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

  3. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    David Fetter <david@fetter.org> — 2008-09-07T18:52:40Z

    On Sun, Sep 07, 2008 at 02:06:40PM -0400, Tom Lane wrote:
> Okay, I got tired of seeing people complain about foreign-key
> constraint violations in data-only dumps.

Isn't this something solved in the more general case by having
pre-data, data, and post-data dump options?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

  4. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    Alvaro Herrera <alvherre@commandprompt.com> — 2008-09-07T19:04:48Z

    Heikki Linnakangas wrote:
> Tom Lane wrote:

>> pg_dump: WARNING: circular foreign-key constraints among these table(s):
>> pg_dump:   master
>> pg_dump:   child
>> pg_dump: You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
>
> WARNING feels a bit too strong. I realize that that message isn't going  
> to the postmaster's log, bloating it, but if a user does that regularly,  
> always disabling triggers as instructed, or there is in fact never  
> circular references in the data with a particular schema, seeing that  
> big fat warning every time is going to become a bit tiresome. Perhaps  
> "NOTE: ..." ?

But the warning is only going to be emitted if there are actual circular
FK constraints, so it seems OK.

> How about printing that notice at the top of the dump file as well? Most  
> people probably don't look at the dump files, but if someone needs to  
> deal with a data-only dumps that contain circular constraints, and also  
> those that don't, it would be invaluable information.

I assume that this trick will only work at restore time only for custom
or tar dumps.  A text-only dump would produce the warning to stderr at
dump time, no?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

  5. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-09-07T19:06:26Z

    David Fetter <david@fetter.org> writes:
> On Sun, Sep 07, 2008 at 02:06:40PM -0400, Tom Lane wrote:
>> Okay, I got tired of seeing people complain about foreign-key
>> constraint violations in data-only dumps.

> Isn't this something solved in the more general case by having
> pre-data, data, and post-data dump options?

No, not unless you expect that that patch will somehow forbid people
from using data-only dumps.

			regards, tom lane

  6. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-09-07T19:10:53Z

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Tom Lane wrote:
>> pg_dump: WARNING: circular foreign-key constraints among these table(s):
>> pg_dump:   master
>> pg_dump:   child
>> pg_dump: You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.

> WARNING feels a bit too strong. I realize that that message isn't going 
> to the postmaster's log, bloating it, but if a user does that regularly, 
> always disabling triggers as instructed, or there is in fact never 
> circular references in the data with a particular schema, seeing that 
> big fat warning every time is going to become a bit tiresome. Perhaps 
> "NOTE: ..." ?

I doubt that very many people will ever see it at all, actually --- how
common are circular FK relationships?  And it does seem appropriate to
me for pg_dump to be noisy about the possibility of trouble at restore
time.  (Maybe the message should also suggest using a schema+data dump,
since that would be a solution at dump time?)

> How about printing that notice at the top of the dump file as well?

Hmm ... that might be feasible in plain text output, but I don't see
any easy way to get a similar effect in archive modes.

			regards, tom lane

  7. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-09-07T19:17:23Z

    Alvaro Herrera <alvherre@commandprompt.com> writes:
> Heikki Linnakangas wrote:
>> How about printing that notice at the top of the dump file as well?

> I assume that this trick will only work at restore time only for custom
> or tar dumps.  A text-only dump would produce the warning to stderr at
> dump time, no?

Yes, the warning (and the re-sorting) must happen at dump time.  Given a
data-only dump, pg_restore wouldn't even have the information needed to
do anything about this.

			regards, tom lane

  8. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    Greg Stark <stark@enterprisedb.com> — 2008-09-07T22:39:28Z

    Tom Lane <tgl@sss.pgh.pa.us> writes:

> I doubt that very many people will ever see it at all, actually --- how
> common are circular FK relationships?  And it does seem appropriate to
> me for pg_dump to be noisy about the possibility of trouble at restore
> time.  (Maybe the message should also suggest using a schema+data dump,
> since that would be a solution at dump time?)

I think they're surprisingly common actually. Most complex databases end up
with them one way or another. Either through a parent-child relationship or
from two different types of relationships (such as "user which owns this
directory" and "home directory of this user").

The other reason to think NOTICE might be better is that it's something which,
if it occurs once, will always occur for that database. So a sysadmin will
become inured to seeing WARNING on his backups. Are there any other warning
conditions which could occur spontaneously that this would mask?

One minor thought -- surely the main use case for data-only dumps is for
importing into another brand of database. In which case the message seems a
bit awkward -- it could talk generically about disabling or dropping the
constraints and then have a hint to indicate how to do that with Postgres.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

  9. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-09-07T23:21:04Z

    Gregory Stark <stark@enterprisedb.com> writes:
> The other reason to think NOTICE might be better is that it's something which,
> if it occurs once, will always occur for that database. So a sysadmin will
> become inured to seeing WARNING on his backups. Are there any other warning
> conditions which could occur spontaneously that this would mask?

[ shrug ... ]  Seems I'm outvoted, so NOTICE it will be.

> One minor thought -- surely the main use case for data-only dumps is for
> importing into another brand of database. In which case the message seems a
> bit awkward -- it could talk generically about disabling or dropping the
> constraints and then have a hint to indicate how to do that with Postgres.

I'm not convinced that data-only dumps are used mainly for that purpose.
In any case I don't want to turn this message into a paragraph;
mentioning all the things you might do about it in a Postgres context is
already making it longer than I would like...

			regards, tom lane

  10. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    Stephen Frost <sfrost@snowman.net> — 2008-09-07T23:33:47Z

    * Gregory Stark (stark@enterprisedb.com) wrote:
> The other reason to think NOTICE might be better is that it's something which,
> if it occurs once, will always occur for that database. So a sysadmin will
> become inured to seeing WARNING on his backups. Are there any other warning
> conditions which could occur spontaneously that this would mask?

Impartial on NOTICE vs. WARNING, it could go either way for me.

> One minor thought -- surely the main use case for data-only dumps is for
> importing into another brand of database. In which case the message seems a
> bit awkward -- it could talk generically about disabling or dropping the
> constraints and then have a hint to indicate how to do that with Postgres.

I have to disagree strongly with this.  We have multiple PG instances
and often have cause to copy between them using data-only pg_dump.  On
the other side, I've never used pg_dump (data-only or not) to generate
something to load data into a different database.

	Thanks,

		Stephen

  11. Re: Proposed patch: make pg_dump --data-only consider FK constraints

    Philip Warner <pjw@rhyme.com.au> — 2008-10-14T11:52:24Z

    Tom Lane wrote:
>> How about printing that notice at the top of the dump file as well?
>>     
>
> Hmm ... that might be feasible in plain text output, but I don't see
> any easy way to get a similar effect in archive modes.
>   

Just saw this, obviously very late, but from memory there is a TOC entry
type for comments or warnings that get output when the dump is used.