CODEWITHSUNDEEP
sqlpostgresqllockingpostgresql-9.3
MKo
MKo

Reputation: 5048

privileged queries in PostgreSQL 9.3

There are 2 users in DB(PostgreSQL 9.3). 1) The X user do a lot of SELECT/INSERT queries. 2) The Y user do a query which need "ACCESS EXCLUSIVE" lock.

Is there a way to grand the Y user a privilege so the queries from Y user will 1) stop the queries of X users. 2) finish the query of Y user 3) restart the queries of X user

Upvotes: 0

Views: 57

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325181

No, there isn't. PostgreSQL doesn't currently support aborting conflicting transactions on lock requests, though it'd be a nice-to-have. You can have a periodic task query pg_stat_activity and pg_locks looking for queries waiting on an AccessExclusiveLock and pg_terminate_backend any conflicting workers, but it's a bit heavy handed.

Nor can PostgreSQL re-run aborted queries, the application is expected to do that its self. Consider that the query might be the fourth in a transaction; it'd be completely wrong for Pg to just re-run the last query in the transaction. It must abort the whole transaction. And it doesn't know if it's even correct to re-run it. So the application must take care of this.

Upvotes: 2

Related Questions