pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pg_dumpall renders ALTER TABLE for a view? 3+ messages / 2 participants [nested] [flat]
* pg_dumpall renders ALTER TABLE for a view? @ 2022-12-15 16:54 Michel Pelletier <pelletier.michel@gmail.com> 2022-12-15 17:15 ` Re: pg_dumpall renders ALTER TABLE for a view? Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: Michel Pelletier @ 2022-12-15 16:54 UTC (permalink / raw) To: pgsql-general 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_dumpall renders ALTER TABLE for a view? 2022-12-15 16:54 pg_dumpall renders ALTER TABLE for a view? Michel Pelletier <pelletier.michel@gmail.com> @ 2022-12-15 17:15 ` Tom Lane <tgl@sss.pgh.pa.us> 2022-12-15 17:31 ` Re: pg_dumpall renders ALTER TABLE for a view? Michel Pelletier <pelletier.michel@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2022-12-15 17:15 UTC (permalink / raw) To: Michel Pelletier <pelletier.michel@gmail.com>; +Cc: pgsql-general 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_dumpall renders ALTER TABLE for a view? 2022-12-15 16:54 pg_dumpall renders ALTER TABLE for a view? Michel Pelletier <pelletier.michel@gmail.com> 2022-12-15 17:15 ` Re: pg_dumpall renders ALTER TABLE for a view? Tom Lane <tgl@sss.pgh.pa.us> @ 2022-12-15 17:31 ` Michel Pelletier <pelletier.michel@gmail.com> 0 siblings, 0 replies; 3+ messages in thread From: Michel Pelletier @ 2022-12-15 17:31 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-12-15 17:31 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-12-15 16:54 pg_dumpall renders ALTER TABLE for a view? Michel Pelletier <pelletier.michel@gmail.com> 2022-12-15 17:15 ` Tom Lane <tgl@sss.pgh.pa.us> 2022-12-15 17:31 ` Michel Pelletier <pelletier.michel@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox