Thread: Dropping and creating a trigger

Dropping and creating a trigger

From

Mitar

Date:

05 January 2019, 12:59:06

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?


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: Dropping and creating a trigger

From

Ron

Date:

05 January 2019, 13:16:06

On 1/5/19 3: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?

I'd think it should.  Have you run the commands manually, one at a time, 
from psql, and checking the table after the DROP TRIGGER, to verify that the 
trigger actually gets dropped?

-- 
Angular momentum makes the world go 'round.

Re: Dropping and creating a trigger

From

Adrian Klaver

Date:

05 January 2019, 20:35:40

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.klaver@aklaver.com

Re: Dropping and creating a trigger

From

Mitar

Date:

06 January 2019, 01:25:14

Hi!

On Sat, Jan 5, 2019 at 9:35 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> > How is this possible? If I am inside a transaction, this should work, no?
>
> Works here:

I thought so. This is being run in parallel multiple times by a
benchmarking tool I made. So it is not just done once, but many times
(50x) at almost the same time.

>   select version();
>                                        version

PostgreSQL 11.1 (Debian 11.1-1.pgdg90+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 6.3.0-
18+deb9u1) 6.3.0 20170516, 64-bit

In fact, using this Docker image [1] with two patches applied (the
ones I currently have in commitfest). I think they are unrelated to
this problem.

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

No. I was trying to fix this code of a package I found. [1] Which
currently does not work well because, again, if it runs multiple times
in parallel, then it happens that sometimes the same trigger tries to
be created twice in a row, failing the second time. So I tried to fix
it by wrapping it into a transaction, but then surprisingly didn't
work.

To reproduce this (if people are interested), I think, you could try:

- try using the Docker image [1]
- clone this benchmarking tool [2]
- after installing, modifying
node_modules/pg-table-observer/dist/PgTableObserver.js to try
BEGIN/COMMIT around the block, see attached patch
- maybe modify index.js to provide connection information to connect
to your PostgreSQL instance, CONN_STR variable
- run: node --experimental-worker --expose-gc index.js pg-query-observer
- ignore errors from the app, check PostgreSQL logs

[1] https://github.com/mitar/docker-postgres
[2] https://github.com/Richie765/pg-table-observer/blob/master/src/PgTableObserver.js#L199
[3] https://github.com/mitar/node-pg-reactivity-benchmark

(How can this thread be moved to bugs mailing list?)

Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Attachment

tx.patch