Thread: 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

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