Dropping and creating a trigger
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
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.
Adding LEFT JOIN to a query has increased execution time 10 times
Good evening, On a CentOS 7.6 server (Intel Core i7-6700, 64 GB DDR4 RAM, RAID1 SSD) I run a backend written in PL/pgSQL and Java for a mobile and desktop word game with the following Linux packages: postgresql10-server-10.6-1PGDG.rhel7.x86_64 pgbouncer-1.9.0-1.rhel7.x86_64 postgresql-jdbc-42.2.5-1.rhel7.noarch Here are the only modified settings in postgresql.conf: max_connections = 120 # (change requires restart) work_mem = 8MB # min 64kB maintenance_work_mem = 128MB # min 1MB 90% of the backend source code are JSON-emitting stored functions and there is one function which is the main core of the game and is a SELECT query over 7 tables. It is called for every Websocket-connected client and delivers a JSON list of active games for the player. Until recently the query needed 1-2 seconds for completion, but after I have added a LEFT JOIN with the following table, the query takes 7-10 seconds for completion and makes the game unpleasant to play: # \d words_geoip; Table "public.words_geoip" Column | Type | Collation | Nullable | Default +--+---+--+- block | inet | | not null | lat| double precision | | | lng| double precision | | | Indexes: "words_geoip_pkey" PRIMARY KEY, btree (block) # select * from words_geoip order by random() limit 5; block | lat|lng ---+--+--- 217.72.221.128/25 |48.26 |11.434 71.183.37.0/24| 40.9357 | -72.9809 190.174.132.0/22 | -34.6033 | -58.3817 24.72.74.128/25 | 50.5061 | -104.6752 73.155.238.0/23 | 29.5075 | -95.0895 (5 rows) # select count(*) from words_geoip; count - 3073410 (1 row) Here is the SELECT query (I have removed the stored function and ROW_TO_JSON around it for better readability and have commented the 3 new lines out): SELECT g.gid, EXTRACT(EPOCH FROM g.created)::int AS created, EXTRACT(EPOCH FROM g.finished)::int AS finished, g.letters AS letters, g.values AS values, g.bid AS bid, CARDINALITY(g.pile) AS pilelen, m.tiles AS tiles, m.score AS score, CASE WHEN g.player1 = 5 THEN g.player1 ELSE g.player2 END AS player1, CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END AS player2, CASE WHEN g.player1 = 5 THEN g.score1 ELSE g.score2 END AS score1, CASE WHEN g.player1 = 5 THEN g.score2 ELSE g.score1 END AS score2, CASE WHEN g.player1 = 5 THEN g.state1 ELSE g.state2 END AS state1, CASE WHEN g.player1 = 5 THEN g.hint1 ELSE g.hint2 END AS hint1, CASE WHEN g.player1 = 5 THEN g.chat1 ELSE g.chat2 END AS chat1, u1.elo AS elo1, u2.elo AS elo2, -- i2.lat AS lat2, -- i2.lng AS lng2, s1.given AS given1, s2.given AS given2, s1.photo AS photo1, s2.photo AS photo2, EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played1 ELSE g.played2 END)::int AS played1, EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played2 ELSE g.played1 END)::int AS played2, ARRAY_TO_STRING(CASE WHEN g.player1 = 5 THEN g.hand1 ELSE g.hand2 END, '') AS hand1, CASE WHEN g.finished IS NOT NULL THEN NULL WHEN g.player2 IS NULL THEN NULL WHEN g.player1 = 5 AND g.played1 < g.played2 THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int WHEN g.player2 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1) THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int ELSE NULL END AS left1, CASE WHEN g.finished IS NOT NULL THEN NULL WHEN g.player2 IS NULL THEN NULL WHEN g.player1 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1) THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int WHEN g.player2 = 5 AND g.played1 < g.played2 THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int ELSE NULL END AS left2 FROM words_games g LEFT JOIN words_moves m ON m.gid = g.gid AND NOT EXISTS (SELECT 1 FROM words_moves m2 WHERE m2.gid = m.gid AND m2.played > m.played) LEFT JOIN words_users u1 ON u1.uid = 5 LEFT JOIN words_users u2 ON u2.uid = (CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END) -- LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = 5 THEN u2.ip ELSE u1.ip END) << i2.block LEFT JOIN words_social s1 ON s1.uid = 5 AND N
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.kla...@aklaver.com
Re: Adding LEFT JOIN to a query has increased execution time 10 times
> "AF" == Alexander Farber writes: AF> Here are the only modified settings in postgresql.conf: AF> max_connections = 120 # (change requires restart) AF> work_mem = 8MB # min 64kB AF> maintenance_work_mem = 128MB # min 1MB AF> 90% of the backend source code are JSON-emitting stored functions AF> and there is one function which is the main core of the game and is AF> a SELECT query over 7 tables. AF> It is called for every Websocket-connected client and delivers a AF> JSON list of active games for the player. AF> Until recently the query needed 1-2 seconds for completion, That seems slow in itself, even before adding the extra join - the explain suggests that you're both short on indexes and you're getting pretty bad plans, possibly due to exceeding join_collapse_limit. (You might try increasing that in your config, along with from_collapse_limit; the default values are a legacy of the days when CPUs were much slower and planning time more of an issue.) AF> but after I have added a LEFT JOIN with the following table, the AF> query takes 7-10 seconds for completion and makes the game AF> unpleasant to play: AF> # \d words_geoip; AF> Table "public.words_geoip" AF> Column | Type | Collation | Nullable | Default AF> +--+---+--+- AF> block | inet | | not null | AF> lat| double precision | | | AF> lng| double precision | | | AF> Indexes: AF> "words_geoip_pkey" PRIMARY KEY, btree (block) And here's yet another missing index, resulting in your query having to process and discard 27 million rows in the course of generating a result of only 9 rows: Join Filter: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END << i2.block) Rows Removed by Join Filter: 27660682 (you probably wanted <<= rather than << as that comparison, if there's any chance your geoip table might have entries for single IPs) Fortunately, this being pg10, you can use either of these indexes: CREATE INDEX ON words_geoip USING gist (block inet_ops); or CREATE INDEX ON words_geoip USING spgist (block); As for the rest of the query, here are places you could probably work on: AF> LEFT JOIN words_moves m ON m.gid = g.gid AF> AND NOT EXISTS (SELECT 1 AF> FROM words_moves m2 AF> WHERE m2.gid = m.gid AF> AND m2.played > m.played) Whar you're asking for here is that the words_moves row that you're joining not have a matching row with a larger "played" value. You can do this far more efficiently with a lateral join, given the right index. AF> LEFT JOIN words_social s1 ON s1.uid = 5 AF> AND NOT EXISTS (SELECT 1 AF> FROM words_social s AF> WHERE s1.uid = s.uid AF> AND s.stamp > s1.stamp) AF> LEFT JOIN words_social s2 ON s2.uid = (CASE WHEN g.player1 = 5 THEN AF> g.player2 ELSE g.player1 END) AF> AND NOT EXISTS (SELECT 1 AF> FROM words_social s AF> WHERE s2.uid = s.uid AF> AND s.stamp > s2.stamp) Similar considerations apply to both of the above. AF> WHERE 5 IN (g.player1, g.player2) AF> AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 AF> day'); This WHERE clause could be written as WHERE 5 IN (g.player1, g.player2) AND coalesce(g.finished,'infinity') > (current_timestamp - interval '1 day') and you could then create the following indexes, CREATE INDEX ON words_games (player1, coalesce(finished,'infinity')); CREATE INDEX ON words_games (player2, coalesce(finished,'infinity')); which should get you a BitmapOr plan for that condition. AF> I have also asked my question at [dba.stack] If you ask questions like this on the IRC channel (#postgresql on chat.freenode.net - see http://freenode.net for info or web-based client access), you can usually get feedback in real time (I rarely answer performance questions in email because getting responses just takes too long). You may have to be patient. -- Andrew (irc:RhodiumToad)
Re: Adding LEFT JOIN to a query has increased execution time 10 times
Oh ok, so it is not as simple as eliminating all "Seq Scan" occurrences... Thank you for replying Andrew - On Sat, Jan 5, 2019 at 9:18 PM Andrew Gierth wrote: That seems slow in itself, even before adding the extra join - the > explain suggests that you're both short on indexes and you're getting > pretty bad plans, possibly due to exceeding join_collapse_limit. > > (You might try increasing that in your config, along with > from_collapse_limit; the default values are a legacy of the days when > CPUs were much slower and planning time more of an issue.) > > AF> but after I have added a LEFT JOIN with the following table, the > AF> query takes 7-10 seconds for completion and makes the game > AF> unpleasant to play: > > AF> # \d words_geoip; > AF> Table "public.words_geoip" > AF> Column | Type | Collation | Nullable | Default > AF> +--+---+--+- > AF> block | inet | | not null | > AF> lat| double precision | | | > AF> lng| double precision | | | > AF> Indexes: > AF> "words_geoip_pkey" PRIMARY KEY, btree (block) > > And here's yet another missing index, resulting in your query having to > process and discard 27 million rows in the course of generating a result > of only 9 rows: > > Join Filter: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END << > i2.block) > Rows Removed by Join Filter: 27660682 > > (you probably wanted <<= rather than << as that comparison, if there's > any chance your geoip table might have entries for single IPs) > > Fortunately, this being pg10, you can use either of these indexes: > > CREATE INDEX ON words_geoip USING gist (block inet_ops); > > or > > CREATE INDEX ON words_geoip USING spgist (block); > > As for the rest of the query, here are places you could probably > work on: > > AF> LEFT JOIN words_moves m ON m.gid = g.gid > AF> AND NOT EXISTS (SELECT 1 > AF> FROM words_moves m2 > AF> WHERE m2.gid = m.gid > AF> AND m2.played > m.played) > > Whar you're asking for here is that the words_moves row that you're > joining not have a matching row with a larger "played" value. You can do > this far more efficiently with a lateral join, given the right index. > > AF> LEFT JOIN words_social s1 ON s1.uid = 5 > AF> AND NOT EXISTS (SELECT 1 > AF> FROM words_social s > AF> WHERE s1.uid = s.uid > AF> AND s.stamp > s1.stamp) > AF> LEFT JOIN words_social s2 ON s2.uid = (CASE WHEN g.player1 = 5 > THEN > AF> g.player2 ELSE g.player1 END) > AF> AND NOT EXISTS (SELECT 1 > AF> FROM words_social s > AF> WHERE s2.uid = s.uid > AF> AND s.stamp > s2.stamp) > > Similar considerations apply to both of the above. > > AF> WHERE 5 IN (g.player1, g.player2) > AF> AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - > INTERVAL '1 > AF> day'); > > This WHERE clause could be written as > > WHERE 5 IN (g.player1, g.player2) > AND coalesce(g.finished,'infinity') > (current_timestamp - interval '1 > day') > > and you could then create the following indexes, > > CREATE INDEX ON words_games (player1, coalesce(finished,'infinity')); > CREATE INDEX ON words_games (player2, coalesce(finished,'infinity')); > > which should get you a BitmapOr plan for that condition. > > AF> I have also asked my question at [dba.stack] > > If you ask questions like this on the IRC channel (#postgresql on > chat.freenode.net - see http://freenode.net for info or web-based client > access), you can usually get feedback in real time (I rarely answer > performance questions in email because getting responses just takes too > long). You may have to be patient. > > I will try to digest your information and to follow up... Thanks again For IRC I am unfortunately too tired right now (evening in Germany) Regards Alex
Re: Dropping and creating a trigger
Hi!
On Sat, Jan 5, 2019 at 9:35 AM Adrian Klaver 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('\nCREATE TRIGGER $1~\nAFTER INSERT OR UPDATE OR DELETE ON $2~\nFOR EACH ROW EXECUTE PROCEDURE $3~()\n ', [trigger_name, table, _this2.trigger_func]);
+return db.none('\nBEGIN; DROP TRIGGER IF EXISTS "' + trigger_name + '" ON "' + table + '"; CREATE TRIGGER "' + trigger_name + '"\nAFTER INSERT OR UPDATE OR DELETE ON "' + table + '"\nFOR EACH ROW EXECUTE PROCEDURE "' + _this2.trigger_func + '"(); COMMIT;\n ');
case 5:
_context4.next = 9;
