Logical locking beyond pg_advisory - Mailing list pgsql-general
I need a mechanism of "logical locking" more ductile than the pg_advisory family.
I'm thinking of a table ("lock_table") that would be part of the database, with columns
* tablename varchar - name of the table "locked"
* rowid integer, - id of the row "locked"
* ownerid varchar, - identifier of the "user" who acquired the lock
* acquired timestamp - to be able to release "abandoned" locks after a certain time
and a group of functions
1) lock_table (tablename varchar, ownerid varchar) bool - get to lock over the entire table, setting rowid to zero
2) unlock_table (tablename varchar, ownerid varchar) bool - unlock the table, if the owner is the recorded one
3) locked_table (tablename varchar, ownerid varchar) bool - ask if the table is locked by some user other than the ownerid argument
4) lock_row (tablename varchar, rowid integer, ownerid varchar) bool - similar to pg_try_advisory_lock
5) unlock_row (tablename varchar, rowid integer, ownerid varchar) bool - similar to pg_advisory_unlock
6) unlock_all (ownerid varchar) bool - unlock all locks owned by ownerid
The timeout (default, maybe 15 minutes) is implicitly applied if the lock is taken by another user (there will be no notification).
Redundant locks are not queued, they simply return true, may be after an update of the acquired column.
Successful locks insert a new row, except the rare case of a timeout, which becomes an update (ownerid and acquired)
Unlock operations deletes the corresponding row
My question is double
a) What is the opinion on the project?
b) What are the consequences of the large number of inserts and deletions
c) Performance. In fact, pg_advisory* implies a network roundtrip, but (I think) no table operations.
TIA
I'm thinking of a table ("lock_table") that would be part of the database, with columns
* tablename varchar - name of the table "locked"
* rowid integer, - id of the row "locked"
* ownerid varchar, - identifier of the "user" who acquired the lock
* acquired timestamp - to be able to release "abandoned" locks after a certain time
and a group of functions
1) lock_table (tablename varchar, ownerid varchar) bool - get to lock over the entire table, setting rowid to zero
2) unlock_table (tablename varchar, ownerid varchar) bool - unlock the table, if the owner is the recorded one
3) locked_table (tablename varchar, ownerid varchar) bool - ask if the table is locked by some user other than the ownerid argument
4) lock_row (tablename varchar, rowid integer, ownerid varchar) bool - similar to pg_try_advisory_lock
5) unlock_row (tablename varchar, rowid integer, ownerid varchar) bool - similar to pg_advisory_unlock
6) unlock_all (ownerid varchar) bool - unlock all locks owned by ownerid
The timeout (default, maybe 15 minutes) is implicitly applied if the lock is taken by another user (there will be no notification).
Redundant locks are not queued, they simply return true, may be after an update of the acquired column.
Successful locks insert a new row, except the rare case of a timeout, which becomes an update (ownerid and acquired)
Unlock operations deletes the corresponding row
My question is double
a) What is the opinion on the project?
b) What are the consequences of the large number of inserts and deletions
c) Performance. In fact, pg_advisory* implies a network roundtrip, but (I think) no table operations.
TIA
pgsql-general by date: