Question 90159 in PostgreSQL Question posted in
The official documentation can be found here.

How do I create a temporary trigger in Postgres? – Postgresql

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search