tracyhenry/Kyrix

kyrix keeps transaction open to DB - blocks admin DDL commands

asah opened this issue · 10 comments

asah commented

I created a test index and then DROP INDEX blocked (!!) because of a lock created by Kyrix.

Killing the Kyrix server immediately allowed the DROP INDEX to proceed, i.e. presumably the connection dropped which (presumably) dropped the lock...

kyrix=# SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '1 minutes';
pid | duration | query
| state
------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------+---------------------
2646 | 00:06:04.103491 | drop index gist2;
| active

2531 | 00:45:00.495962 | COPY (SELECT id, x, y, cx, cy, minx, miny, maxx, maxy FROM bbox_dots_toplayer0_102820 bbox_dots_toplayer0 WHERE ((geom OPERATOR(pg_catalog.&&) '(6992,6727),(4992,4727)'::box) AND
(NOT (geom OPERATOR(pg_catalog.&&) '(6678,6590),(4678,4590)'::box)))) TO STDOUT | idle in transaction
2530 | 00:45:00.497046 | COPY (SELECT id, x, y, cx, cy, minx, miny, maxx, maxy FROM bbox_dots_toplayer0_102813 bbox_dots_toplayer0 WHERE ((geom OPERATOR(pg_catalog.&&) '(6992,6727),(4992,4727)'::box) AND
(NOT (geom OPERATOR(pg_catalog.&&) '(6678,6590),(4678,4590)'::box)))) TO STDOUT | idle in transaction
2532 | 00:45:00.494433 | COPY (SELECT id, x, y, cx, cy, minx, miny, maxx, maxy FROM bbox_dots_toplayer0_102827 bbox_dots_toplayer0 WHERE ((geom OPERATOR(pg_catalog.&&) '(6992,6727),(4992,4727)'::box) AND
(NOT (geom OPERATOR(pg_catalog.&&) '(6678,6590),(4678,4590)'::box)))) TO STDOUT | idle in transaction
2533 | 00:45:00.493996 | COPY (SELECT id, x, y, cx, cy, minx, miny, maxx, maxy FROM bbox_dots_toplayer0_102834 bbox_dots_toplayer0 WHERE ((geom OPERATOR(pg_catalog.&&) '(6992,6727),(4992,4727)'::box) AND
(NOT (geom OPERATOR(pg_catalog.&&) '(6678,6590),(4678,4590)'::box)))) TO STDOUT | idle in transaction

tracyhenry commented

I need more information to reproduce the issue:

  • was indexing finished?
  • what table was the index created on?
asah commented
tracyhenry commented

this will be a one-liner fix -- I just did not close the connection.

On the other hand, why do you want to modify the MV tables? It doesn't quite make sense to drop the tables/indexes when the indexing/REST server is running, right? Stuff in the kyrix DB is generated by kyrix, rather than raw data in user DBs.

asah commented
tracyhenry commented

Turns out you block if you CREATE a new index then try to DROP it (create proceeds, drop blocks). There's lots of counterintuitive picking behavior like this. (via cellphone, random spelling ahead)
…
On Tue, Apr 16, 2019, 5:38 PM Wenbo Tao @.***> wrote: this will be a one-liner fix -- I just did not close the connection. On the other hand, why do you want to modify the MV tables? It doesn't quite make sense to drop the tables/indexes when the indexing/REST server is running, right? Stuff in the kyrix DB is generated by kyrix, rather than raw data in user DBs. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#69 (comment)>, or mute the thread https://github.com/notifications/unsubscribe-auth/AAIi4vnpB8n93s_FWFhUT2yVXkwaM9qzks5vhkLPgaJpZM4czgjK .

this should be expected given that I did not close the connection... the locks are on existing tables/indexes. But again, I don't see why you want to drop existing tables/indexes. Can you explain a bit?

tracyhenry commented

sorry, I can't actually reproduce the issue... If you run the code from master, you can drop whatever index/table you want after indexing is done. The transaction is committed at the end of createMV of the indexers, so locks should already be released.

Did you test your the native indexer or existing indexers in master?

asah commented
tracyhenry commented

Can you try on distribute_citus (via cellphone, random spelling ahead)
…
On Tue, Apr 16, 2019, 6:42 PM Wenbo Tao @.***> wrote: sorry, I can't actually reproduce the issue... If you run the code from master, you can drop whatever index/table you want after indexing is done. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#69 (comment)>, or mute the thread https://github.com/notifications/unsubscribe-auth/AAIi4qMfeqdGe-LEwvhsXXtKfw_7zVwMks5vhlHGgaJpZM4czgjK .

do you still have this issue? I can't check right now because an exception would always be raised in getBboxCoordinates() when I just try to index the nba app on distribute_citus

asah commented
tracyhenry commented

now I found that the runtime queries kept the transaction open...
and you're right we don't need transactions. Will make a fix.