Trigger (or something similar) on table rename?

On 02/15/2018 10:52 AM, Ken Tanzer wrote:

Hi.  I'm wondering about possibilities for taking action when a table is
renamed.

Specifically in this case, I'm using table_log, which when you use it on
a table creates a new table, sequence and index that is tied to the
table name.  Of course, if the oriignal table is renamed, the other
relations aren't.  So I rename table to table_old, and then create a new
version of table, but the table logging fails because of the
already-existing relations that table_log created.

I could of course rename them manually, or create a function to do it,
but that would still need to be manually invoked.  I haven't really used
listen/notify--I assume it could do this, but there would need to be
some kind of process actively listening?

Something like a trigger on the table rename would be ideal for my
purposes.  Anything like that possible?  Thanks!

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
(253) 245-3801

Subscribe to the mailing list
<mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Maybe?:

https://www.postgresql.org/docs/9.6/static/event-triggers.html

https://www.postgresql.org/docs/9.6/static/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER

--
Adrian Klaver
adrian.klaver@aklaver.com

Ken Tanzer <ken.tanzer@gmail.com> writes:

Something like a trigger on the table rename would be ideal for my
purposes. Anything like that possible? Thanks!

Recent PG versions have "event triggers" which would serve the purpose.
However, the infrastructure for them isn't very fully built out yet.
I'm not sure if you could identify a table rename without resorting to
writing some C code.

regards, tom lane

On 02/15/2018 10:52 AM, Ken Tanzer wrote:

Hi.  I'm wondering about possibilities for taking action when a table is
renamed.

I've looked into this a bit. Here is what I understand:

Since 9.3 Postgres has had "event triggers" which can run code on DDL
events
(https://www.postgresql.org/docs/current/static/event-triggers.html).
There are events like `ddl_command_start` and tags like `ALTER TABLE`.
So you could write a trigger that fires `ON ddl_command_start WHEN TAG
IN ('ALTER TABLE')`.

Unfortunately I don't think you can get the old/new table name from
inside the trigger function. If you need that, you might take a look at
this extension which adds some custom event triggers with ways of
getting that information:

https://github.com/CartoDB/pg_schema_triggers

If you are really adventurous you could even look at using the
ProcessUtility hook directly to do what you need.

I'm looking forward to seeing what others say here because I'd like to
know more myself!

Good luck!

--
Paul ~{:-)
pj@illuminatedcomputing.com

On Thu, Feb 15, 2018 at 11:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ken Tanzer <ken.tanzer@gmail.com> writes:

Something like a trigger on the table rename would be ideal for my
purposes. Anything like that possible? Thanks!

Recent PG versions have "event triggers" which would serve the purpose.
However, the infrastructure for them isn't very fully built out yet.
I'm not sure if you could identify a table rename without resorting to
writing some C code.

regards, tom lane

Hi, and thanks for the responses. As a follow-up, I see you can use
pg_event_trigger_ddl_commands() to get some info. One of the things it
returns is a pg_ddl_command ("A complete representation of the command, in
internal format. This cannot be output directly, but it can be passed to
other functions to obtain different pieces of information about the
command.")

Presumably the complete command would let you figure out it's a rename, and
the old and new tables. But I found this message (
https://postgrespro.com/list/thread-id/1561932) stating that a
pg_ddl_command could only be processed in C, not in a procedural language.
I'm wondering if that just hasn't been implemented yet and is likely to
change at some point, or if there is some kind of inherent limitation
involved.

Also, is there a link somewhere that does document the pg_ddl_command, in
case I did end up trying to work this in C?

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Ken Tanzer <ken.tanzer@gmail.com> writes:

Presumably the complete command would let you figure out it's a rename, and
the old and new tables. But I found this message (
https://postgrespro.com/list/thread-id/1561932) stating that a
pg_ddl_command could only be processed in C, not in a procedural language.
I'm wondering if that just hasn't been implemented yet and is likely to
change at some point, or if there is some kind of inherent limitation
involved.

That's basically the missing infrastructure I referred to. The parse tree
data structures are reasonably well-documented internally (look under
src/include/nodes/), but there's not a lot of mechanism in place for
displaying them to high-level code. We do have decent support for
reverse-compiling DML statements (select/insert/update/delete), but not
for utility commands which is what you're interested in.

There's no inherent reason we couldn't get there, it's just that it'd
be a lot of work to get to reasonable coverage, and probably a lot of
code to maintain going forward.

It looks like src/test/modules/test_ddl_deparse/ contains the beginnings
of a facility of this sort ... but it's only test code and doesn't
necessarily have anybody's blessing as to being a good basis for moving
forward.

regards, tom lane