Re: Dropping and creating a trigger

On 1/5/19 1:59 AM, Mitar wrote:
> Hi!
>
> I am seeing such errors in logs:
>
> ERROR: trigger "myapp_assignments" for relation "assignments" already exists
> STATEMENT:
> BEGIN TRANSACTION;
> DROP TRIGGER IF EXISTS "myapp_assignments" ON "assignments";
> CREATE TRIGGER "myapp_assignments"
> AFTER INSERT OR UPDATE OR DELETE ON "assignments"
> FOR EACH ROW EXECUTE PROCEDURE "tblobs_myapp"();
> COMMIT;
>
> How is this possible? If I am inside a transaction, this should work, no?

Works here:

select version();
version

------------------------------------------------------------------------------------

PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.5, 64-bit

test=# begin;
BEGIN

test=# drop trigger if exists test_trigger on trigger_test;
DROP TRIGGER

test=# create trigger test_trigger BEFORE INSERT ON trigger_test FOR
EACH ROW WHEN (new.id > 10) EXECUTE PROCEDURE trigger_test()

test-# ;

NOTICE: caught CREATE TRIGGER event on 'test_trigger on
public.trigger_test'

CREATE TRIGGER

test=# commit ;

COMMIT

So:

1) Postgres version?

2) Is this one of your 'temporary' trigger/function combos?

>
>
> Mitar
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com