pg_dumpall renders ALTER TABLE for a view?
Hello,
We found the root cause for an issue we encountered restoring a saved
database stored with pg_dumpall, but during this investigation we realized
that pg_dump/all renders `ALTER TABLE` statements for views, for example to
convey ownership.
I get that this is synonymous in most cases, except when there is an event
trigger for `ALTER TABLE`, it ends up firing the event trigger for the
views, and any event triggers expected to fire on `ALTER VIEW` statements
do not fire.
Is this something we can contribute a fix for, or is this some kind of
necessary b/w compat issue that must remain? Looking at the history of
ALTER VIEW it looks like it has existed since at least 9.0, so it seems
safe to me from a b/w compat standpoint to render the correct statement.
Thanks!
-Michel
Michel Pelletier <pelletier.michel@gmail.com> writes:
We found the root cause for an issue we encountered restoring a saved
database stored with pg_dumpall, but during this investigation we realized
that pg_dump/all renders `ALTER TABLE` statements for views, for example to
convey ownership.
I get that this is synonymous in most cases, except when there is an event
trigger for `ALTER TABLE`, it ends up firing the event trigger for the
views, and any event triggers expected to fire on `ALTER VIEW` statements
do not fire.
Is this something we can contribute a fix for, or is this some kind of
necessary b/w compat issue that must remain?
We're not likely to change the fact that you're allowed to write ALTER
TABLE for this, so if your event triggers get broken by doing that
you'd best fix the event triggers.
I don't have any great objection to making pg_dump emit the more modern
spelling (I think ... you'd need to look into pg_restore to make sure
it's not assuming something in this area). But doing that won't
really remove the hazard.
regards, tom lane
On Thu, Dec 15, 2022 at 9:15 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michel Pelletier <pelletier.michel@gmail.com> writes:
I get that this is synonymous in most cases, except when there is an
event
trigger for `ALTER TABLE`, it ends up firing the event trigger for the
views, and any event triggers expected to fire on `ALTER VIEW` statements
do not fire.Is this something we can contribute a fix for, or is this some kind of
necessary b/w compat issue that must remain?We're not likely to change the fact that you're allowed to write ALTER
TABLE for this, so if your event triggers get broken by doing that
you'd best fix the event triggers.
For sure, and we're working on fixing the triggers, I should have been more
clear that I'm not suggesting any changes to the server behavior, just to
the dump/restore process.
I don't have any great objection to making pg_dump emit the more modern
spelling (I think ... you'd need to look into pg_restore to make sure
it's not assuming something in this area). But doing that won't
really remove the hazard.
Agree it won't remove the hazard, but should reduce the kind of collateral
damage we encountered. Will examine pg_restore as you suggested to scope
the work better.
Thank you!
-Michel