It used to be that if you had a pg_dump file without ACL checks, you
could load it as an unprivileged user.  Now you get a ton of complaints
about those handy little "update pg_class" commands.

I suppose this is only a cosmetic issue, but we're going to get
questions/complaints about it... is there any way to avoid needing
those UPDATEs?

			regards, tom lane

Mon, 05 Mar 2001 20:00:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
pg_dump scripts are no longer ordinary-user friendly
At 20:00 5/03/01 -0500, Tom Lane wrote:
>I suppose this is only a cosmetic issue, but we're going to get
>questions/complaints about it... is there any way to avoid needing
>those UPDATEs?

I definitely prefer it to match the old behaviour, and since by default we
put triggers at the end, we could go back to the old model of only updating
pg_class in a data-only dump/restore.

This only has a problem if the user reorders the restore to put triggers at
the start, and then I suspect they may want them enabled anyway. If anybody
can see another case where this will be a problem, speak up...


----------------------------------------------------------------
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   |/

Tue, 06 Mar 2001 13:07:03 +1100
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump scripts are no longer ordinary-user friendly
Philip Warner <pjw@rhyme.com.au> writes:
> ... we could go back to the old model of only updating
> pg_class in a data-only dump/restore.

Works for me ...

			regards, tom lane

Mon, 05 Mar 2001 21:37:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: pg_dump scripts are no longer ordinary-user friendly 
At 21:37 5/03/01 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> ... we could go back to the old model of only updating
>> pg_class in a data-only dump/restore.
>
>Works for me ...
>

Should we have an option to turn off this feature entirely?



----------------------------------------------------------------
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   |/

Tue, 06 Mar 2001 14:23:14 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Re: pg_dump scripts are no longer ordinary-user
  friendly
Philip Warner <pjw@rhyme.com.au> writes:
> At 21:37 5/03/01 -0500, Tom Lane wrote:
>> Philip Warner <pjw@rhyme.com.au> writes:
>>> ... we could go back to the old model of only updating
>>> pg_class in a data-only dump/restore.
>> 
>> Works for me ...

> Should we have an option to turn off this feature entirely?

Now that you mention it, is it a feature at all?  Or a bug?  ISTM poor
form for a data-only restore to assume it may turn off all pre-existing
triggers.

			regards, tom lane

Mon, 05 Mar 2001 22:26:35 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: pg_dump scripts are no longer ordinary-user friendly
At 22:26 5/03/01 -0500, Tom Lane wrote:
>
>> Should we have an option to turn off this feature entirely?
>
>Now that you mention it, is it a feature at all?  Or a bug?  ISTM poor
>form for a data-only restore to assume it may turn off all pre-existing
>triggers.

Do you recall any of the history - why was it added in the first place? I
vaguely recall something about doing a schema restore then data restore. In
this case, you need to disable triggers, but maybe that should be an option
only. ie. default to no messing with pg_class, but if the user requests it,
output code to disable triggers. 

The only thing that worries me in this, is that we are changing the
behaviour from 7.0.

----------------------------------------------------------------
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   |/

Tue, 06 Mar 2001 14:32:15 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Re: pg_dump scripts are no longer ordinary-user
  friendly
Philip Warner <pjw@rhyme.com.au> writes:
> At 22:26 5/03/01 -0500, Tom Lane wrote:
>> Now that you mention it, is it a feature at all?  Or a bug?  ISTM poor
>> form for a data-only restore to assume it may turn off all pre-existing
>> triggers.

> Do you recall any of the history - why was it added in the first place?

No, I don't recall.  It might be worth digging in the archives.

> I vaguely recall something about doing a schema restore then data
> restore. In this case, you need to disable triggers, but maybe that
> should be an option only. ie. default to no messing with pg_class, but
> if the user requests it, output code to disable triggers.

Well, mumble.  I guess the question is what are the triggers going to
*do*?  If they are going to cross-check against tables that may not be
restored yet, then you have a problem if you don't turn them off.  OTOH
it's easy to imagine that this may allow you to load inconsistent data.
'Tis a puzzlement.

For now, I'd be happy if the normal case of a simple restore doesn't
generate warnings.  Improving on that probably takes more thought and
risk than we should be putting in at the end of beta.

			regards, tom lane

Mon, 05 Mar 2001 22:40:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: pg_dump scripts are no longer ordinary-user friendly
On Mon, 5 Mar 2001, Tom Lane wrote:

> Philip Warner <pjw@rhyme.com.au> writes:
> > At 21:37 5/03/01 -0500, Tom Lane wrote:
> >> Philip Warner <pjw@rhyme.com.au> writes:
> >>> ... we could go back to the old model of only updating
> >>> pg_class in a data-only dump/restore.
> >> 
> >> Works for me ...
> 
> > Should we have an option to turn off this feature entirely?
> 
> Now that you mention it, is it a feature at all?  Or a bug?  ISTM poor
> form for a data-only restore to assume it may turn off all pre-existing
> triggers.

The problem is that in general if you do a schema dump and data dump
separately (which was the case that was put in for really), you're already
screwed if you've got triggers that alter or check other data unless
you do manual work for restore.

If you've got a trigger that logs changes, you don't want to log the 
reinserted data if you're also restoring the data for the log table.
You can't not restore the log table if it logs modifications because
you'll lose the modification data.

If you're doing any triggers that are doing anything like fk (say you
want to do something other than direct comparisons) you run into the
issue of having the data not be there.

If you're doing an insert/update trigger that sets a modification date,
you probably don't want to blow away the modification dates on a restore.

I don't think turning off triggers is a good idea, but I'm not certain
that turning them on always will actually be better for the average user.  
I think an option is a good idea though.



Mon, 5 Mar 2001 19:51:49 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Re: pg_dump scripts are no longer ordinary-user friendly
At 22:40 5/03/01 -0500, Tom Lane wrote:
>
>For now, I'd be happy if the normal case of a simple restore doesn't
>generate warnings. 

I'll commit the changes shortly.


>Improving on that probably takes more thought and
>risk than we should be putting in at the end of beta.

Agreed.


----------------------------------------------------------------
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   |/

Tue, 06 Mar 2001 14:56:24 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Re: pg_dump scripts are no longer ordinary-user
  friendly
On Mon, 5 Mar 2001, Tom Lane wrote:

> Philip Warner <pjw@rhyme.com.au> writes:
> > At 22:26 5/03/01 -0500, Tom Lane wrote:
> >> Now that you mention it, is it a feature at all?  Or a bug?  ISTM poor
> >> form for a data-only restore to assume it may turn off all pre-existing
> >> triggers.
> 
> > Do you recall any of the history - why was it added in the first place?
> 
> No, I don't recall.  It might be worth digging in the archives.

Foreign key constraints with data following the full constraint definition
if the data was in the wrong order.  

Unfortunately it does allow invalid data to be loaded, but for circular
cases I'm not sure how you can do this safely.  I guess for fk, if all the
data loading was in a single transaction and you did something to override
the normal deferrable-ness of the constraint and forced the constraints to
be deferred, it would check at the end of the full load.  This still
breaks for multiple dump files per table and for other random user
triggers that are unsafe on restore though.




Mon, 5 Mar 2001 20:04:11 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Re: pg_dump scripts are no longer ordinary-user friendly