Hi!
On Sat, Jan 5, 2019 at 9:35 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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
--- node_modules/pg-table-observer/dist/PgTableObserver.js.orig 2019-01-05 14:11:33.303140087 -0800
+++ node_modules/pg-table-observer/dist/PgTableObserver.js 2019-01-05 14:19:24.817530060 -0800
@@ -373,7 +373,7 @@
trigger_name = channel + '_' + table;
_context4.prev = 2;
_context4.next = 5;
- return db.none('\n CREATE TRIGGER $1~\n AFTER INSERT OR UPDATE OR DELETE ON $2~\n FOR EACH ROW EXECUTE PROCEDURE $3~()\n ', [trigger_name, table, _this2.trigger_func]);
+ return db.none('\n BEGIN; DROP TRIGGER IF EXISTS "' + trigger_name + '" ON "' + table + '"; CREATE TRIGGER "' + trigger_name + '"\n AFTER INSERT OR UPDATE OR DELETE ON "' + table + '"\n FOR EACH ROW EXECUTE PROCEDURE "' + _this2.trigger_func + '"(); COMMIT;\n ');
case 5:
_context4.next = 9;
