Re: Logical locking beyond pg_advisory

/Would be nice if you explain more about what kind of problem you want
to solve./

There are two main questions "to solve"
a) generally speaking, I like to get some form of "exclusive access" to
the row before updating or deleting. None of the optimistic / pesimistic
automatic variants of concurrency management glad me. Nor, of course,
the "versioning" variants.
b) some of the tables I´m working on have a "number" column (_not the
PK_) which sometimes come from material, external sources, but sometimes
must be assigned by the system. This could be solved two main ways
b.1) Use a trigger to get the famous "max(n) + 1". At least in one of
the cases, the number automatically generated must fall into different
ranges conditioned by the value of another column, and the ranges vary
between versions of the database.
b.2) "Lock the entire table", get programmatically the next number for
the correct range, assign it and free the table lock.

Of course, and beforehand, all database manipulations are done thru
applications.
Till yesterday, I was working with the "advisory" family. Having a
bigint as the only "lock identifier" I was working with a hash of the
table name XORed with the id of the row or zero for the entire table.
(All my tables have an autosequential integer id as PK).
Even if I found a very robust hash algorithm for the table name, I
cannot discard some collision once the id was xored. I tested five or
six table names, along 20000 ids every one, without collision. But...
Of course, I need the "full table lock" for inserts. So, it´s a very
separated concern with updates and deletions. But...

TIA

On 17/09/2018 03:19 , Fabrízio de Royes Mello wrote:
>
> Em dom, 16 de set de 2018 às 17:53, marcelo <marcelo(dot)nicolet(at)gmail(dot)com
> <mailto:marcelo(dot)nicolet(at)gmail(dot)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

---
El software de antivirus Avast ha analizado este correo electrónico en busca de virus.
https://www.avast.com/antivirus