On Mon, May 11, 2020 at 5:23 PM Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx> wrote:
On 2020-May-11, Michel Pelletier wrote:
> Is this asymmetry a bug? I realize these event trigger functions are
> typically written in C, but I'd like to keep this idea contained to a
> plpgsql function and cover as many reversible changes as I can.
Sadly, you cannot obtain all the necessary details using only plpgsql.
The only way is to compile a C extension. There's a very rough,
incomplete skeleton to show how to do this in
src/test/modules/test_ddl_deparse. A much more complete approach, which
was not completed, was used to produce JSON output from the C
structures -- see
https://www.postgresql.org/message-id/20150409161419.GC4369@xxxxxxxxxxxxxx
Ah thank you, test_ddl_deparse gives me a good idea what's going on now. Very interesting thread, thanks for your work on this.
Keep in mind ALTER TABLE is a very sophisticated; you can do things like
ALTER TABLE one ADD COLUMN two INT, DROP COLUMN three, ADD COLUMN four text;
(i.e. do several things in one command) so if you were to use a
simplistic approach, you might end up missing things.
This is a support feature for some users so they are encouraged to write down scripts. The idea being the tool would spit one out and they'll be advised to use it as a starting point for the actual post-hoc down script, so catching CREATE TABLE/INDEX is a good start. As this ddl deparsing feature evolves in the future, I can add more complex cases.
Thanks again!
-Michel
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
