Thread: pg_dump scripts are no longer ordinary-user friendly
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
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 |/
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
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 |/
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
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 |/
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
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.
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 |/
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.