Re: Dropping and creating a trigger

Hi!

On Sat, Jan 5, 2019 at 9:35 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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