I'm trying to create a system in Postgres where each client can create its own subscriptions via listen notify triggers. I.e. the client will listen to a channel, and then create a trigger which runs notify on that channel when its conditions have been met. The issue is that I want Postgres to clean up properly in case of improper client termination (e.g. the remote client process dies). To be more specific, I want that trigger which is calling the notify to be removed as there is no longer a listener anyways. How can I accomplish this?

I've thought about having a table to map triggers to client ids and then using that to remove triggers where the client is gone, but it seems like a not so great solution.

CodePudding user response：

That's not how it works. CREATE TRIGGER requires that you either own the table or have the TRIGGER privilege on it (which nobody in their right mind will give you, because it enables you to run arbitrary code in their name). Moreover, CREATE TRIGGER requires an SHARE ROW EXCLUSIVE lock on the table and DROP TRIGGER requires an ACCESS EXCLUSIVE lock, which can be disruptive.

Create a single trigger and keep that around.

CodePudding user response：

I found an answer to this in another question: Drop trigger/function at end of session in PostgreSQL?

In reasonably recent Postgres versions you can create a function in pg_temp schema:

create function pg_temp.get_true() returns boolean language sql as $$ select true; $$;
select pg_temp.get_true();

This is the schema in which temporary tables are created. All its contents, including your function, will be deleted on end of session.

You can also create triggers using temporary functions on tables. I've just tested this and it works as expected:

create function pg_temp.ignore_writes() returns trigger language plpgsql as $$
    begin
        return NULL;
    end;
$$;
create table test (id int);
create trigger test_ignore_writes
    before insert, update, delete on test
    for each row execute procedure pg_temp.ignore_writes();

Because this trigger function always returns NULL and is before [event] it should make any writes to this table to be ignored. And indeed:

insert into test values(1);
select count(*) from test;
 count
-------
     0

But after logout and login this function and the trigger would not be present anymore, so writes would work:

insert into test values(1);
select count(*) from test;
 count
-------
     1

But you should be aware that this is somewhat hackish — not often used and might not be very thoroughly tested.