pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michel Pelletier <pelletier.michel@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: pg_dumpall renders ALTER TABLE for a view?
Date: Thu, 15 Dec 2022 12:15:13 -0500
Message-ID: <3417783.1671124513@sss.pgh.pa.us> (raw)
In-Reply-To: <CACxu=vKVUTuPAy3qv5FPNr=zTdcJqZbzP7_9euJx4Owi_LMNrg@mail.gmail.com>
References: <CACxu=vKVUTuPAy3qv5FPNr=zTdcJqZbzP7_9euJx4Owi_LMNrg@mail.gmail.com>

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





view thread (3+ messages)  latest in thread

Message-ID: <3417783.1671124513@sss.pgh.pa.us>
Permalink:  ../3417783.1671124513@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3417783.1671124513@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, pelletier.michel@gmail.com
  Subject: Re: pg_dumpall renders ALTER TABLE for a view?
  In-Reply-To: <3417783.1671124513@sss.pgh.pa.us>
  References: <CACxu=vKVUTuPAy3qv5FPNr=zTdcJqZbzP7_9euJx4Owi_LMNrg@mail.gmail.com> <3417783.1671124513@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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