Thread: Firing an event trigger for ADD COLUMN?
I'm writing a script that "reverses" ddl operations in an "up" script by capturing event triggers and generating a "down" script for reverting. I've got it working great for tables, indexes, etc, but it seems ADD COLUMN doesn't sent an event, here's the code i'm using, pasted straight from the documentation:
And here's the results I get, I get an event for dropping a column, but not adding one:
postgres=# create table foo (bar int);
NOTICE: CREATE TABLE created object: table public public.foo
CREATE TABLE
postgres=# alter table foo add column baz int;
NOTICE: ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=# alter table foo drop column bar;
NOTICE: ALTER TABLE dropped object: table column public.<NULL> public.foo.bar
NOTICE: ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=#
NOTICE: CREATE TABLE created object: table public public.foo
CREATE TABLE
postgres=# alter table foo add column baz int;
NOTICE: ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=# alter table foo drop column bar;
NOTICE: ALTER TABLE dropped object: table column public.<NULL> public.foo.bar
NOTICE: ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=#
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.
Thank you,
-Michel
> 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.
This is the expected behavior since pg_event_trigger_ddl_commands function does not return object_name unlike the pg_event_trigger_dropped_objects.
Logging the statement through log_statement=ddl or higher is the only way I see to capture the details of alter statement.
On Tue, May 12, 2020 at 4:57 AM Michel Pelletier <pelletier.michel@gmail.com> wrote:
I'm writing a script that "reverses" ddl operations in an "up" script by capturing event triggers and generating a "down" script for reverting. I've got it working great for tables, indexes, etc, but it seems ADD COLUMN doesn't sent an event, here's the code i'm using, pasted straight from the documentation:And here's the results I get, I get an event for dropping a column, but not adding one:postgres=# create table foo (bar int);
NOTICE: CREATE TABLE created object: table public public.foo
CREATE TABLE
postgres=# alter table foo add column baz int;
NOTICE: ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=# alter table foo drop column bar;
NOTICE: ALTER TABLE dropped object: table column public.<NULL> public.foo.bar
NOTICE: ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=#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.Thank you,-Michel
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@alvh.no-ip.org 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. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2020-May-11, Alvaro Herrera wrote: > https://www.postgresql.org/message-id/20150409161419.GC4369@alvh.no-ip.org (Please don't expect this code to compile anymore.) -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, May 11, 2020 at 5:23 PM Alvaro Herrera <alvherre@2ndquadrant.com> 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@alvh.no-ip.org
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