Em dom, 16 de set de 2018 às 17:53, marcelo <
marcelo.nicolet@gmail.com> escreveu:
>
> 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?
Would be nice if you explain more about what kind of problem you want to solve.
> b) What are the consequences of the large number of inserts and deletions
The first thing came to my mind with this approach is table bloat.
> c) Performance. In fact, pg_advisory* implies a network roundtrip, but (I think) no table operations.
Yeap... no table operations.
Regards,
--
Fabrízio de Royes Mello Timbira -
http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
