search

kaaveland / eugene

Careful With that Lock, Eugene
MIT License
32 stars 1 forks source link

Find queries with conflicts in `pg_stat_statements` #13

Open kaaveland opened 7 months ago

kaaveland commented 7 months ago

We should try to accept a dump (.csv.gz or .csv) of pg_stat_statements, or optionally just use pg_stat_statements in the connected database to see if we could pick up queries that would conflict with locks taken by the migration script.

kaaveland commented 7 months ago

Unless judiciously reset, pg_stat_statements is going to contain a lot of different stuff. All DDL commands, f. ex. It's probably only interesting to identify queries that run as part of normal application code, so we need some way to find those based on query text. One idea is to simply test if we can explain it. It is often (always?) a syntax error to explain alter statements and it works with "unusual" queries like refresh materialized view. Even though we know which locks a transaction will take, there's still a ways to go to test for conflict. We may be able to use this idea: https://github.com/kaaveland/eugene/issues/4 or we might have to try a more manual approach with pg_query.rs, or we can try to run the query itself in a transaction, assuming it doesn't have any placeholders that we can't automatically assign.

kaaveland commented 6 months ago

Per #4, it's pretty easy to detect which tables we need to lock at AccessShare or higher now, and which statements that are DML, so we can start working on this now.