5

I have a particular problem addressing the PostgreSQL advisory locking functions using the bigint variants.

Basically I want to create a 64 bit bigint value from a text type obtained with the PostgreSQL md5() function from an arbitrary text_input_context input.

The idea is to emulate / simulate a table row lock behavior1, where the text context is build from the full table name (schema + table name), and the table's keyfield values for a particularly selected row.
It might be acceptable in our application to lock even more rows due to collisions of these hash values, but ensuring to at least have that row is protected for updates from other sessions.
The lock / unlock would be ever straightforward and sequential to protect concurrent accesses of a specific table by design.

Originally the md5() function returns a 128 bit value represented as a hexadecimal text value of 32 bytes.

I want to convert this to a bigint value, which I can use with the PostgreSQL advisory lock functions.

When doing some research I found a way to just cut the 128 bit value output of the md5() function for it's 1st 16 bytes, and convert these to a 64 bit bigint value that can be passed to the PostgreSQL advisory lock functions2:

SELECT pg_catalog.pg_advisory_lock(('x' || 
             pg_catalog.left(pg_catalog.md5(text_input_context),16)::bit(64)::bigint)

I am well aware that using the md5() algorithm already looses information regarding the text_input_context, though I want to reduce that to a minimum, and even more when just using the 1st 16 bytes of the md5 hash value.

My question is:
Is that just a too naive approach, or can we have better implementations to compute a 64 bit value from a 128 bit hash value?

When researching I found the idea to additionally xor-ing the lower 16 byte part with the upper 16 byte part. But wouldn't that be even worse by means of distribution?


Regarding some comments advising to use SELECT ... FOR UPDATE, SELECT FOR UPDATE NOWAIT or SELECT FOR UPDATE SKIP LOCKED, please note that this won't help to solve my specific problem.

  1. The SELECT commands are done internally by some DB access component, where I have little to no chance to change the behavior how the component issues these statements against the database.

  2. What I need is to indicate that a particular table row is locked in the GUI, but still show all the information in the form.


PS.: May be this question better applies to SE Database Administrators, but in the end it's more about the algorithmic approach IMO.

PPS.: In case that matters anyhow, the applications are written in Delphi, using the Devart UniDAC components.


1)The problem occurs in the context, where we try to replace an ISAM (flat file) database system (ADS) with PostgreSQL. The row locking in the original system is implicit, when an application process (session) holds a cursor to a table row for updating.

2)Of course the text context value would be passed as a prepared query parameter, so I am aware about the dangers of SQL-injection, or other unexpected syntax issues.

12
  • Also I am pretty sure that asking at SE Code Review would complain about lacking context, thus I decided to ask here. Jan 17, 2020 at 20:32
  • I am even not so sure, that this is specific for the postgres tag, though I am mentioning PostgreSQL functions here. Jan 17, 2020 at 20:45
  • 1
    It would probably be better to simply use the record identifier. My assumption is you need to keep predictability based on the input so the key is easily calculated. Truncating the md5 to the high bits seems like you'll end up with a lot more key collisions for different strings. 64 bits is quite generous, but not if you are only using a fraction of that address space. Jan 17, 2020 at 21:10
  • @BerinLoritsch "the record identifier" We don't keep oid's with our tables unfortunately. We need to create contexts from the determined key-fields. Jan 17, 2020 at 22:12
  • @BerinLoritsch Not to mention that the record oid isn't unique regarding of all tables in a database and schema. Jan 17, 2020 at 22:22

1 Answer 1

0

That's a good approach.

SELECT pg_advisory_xact_lock(
  ('x' || md5(input_text))::bit(64)::bigint
);

(Whether you use a transaction or session-level lock depends on how it's invoked.)

It's unfortunate that PostgreSQL lacks a better hexadecimal or byte conversion to bigint, but performance-wise, the overall calculation is extremely fast and will not be the bottleneck.


P.S. While I think your solution is best for your situation, an alternative approach is

CREATE UNLOGGED TABLE lock (
  key text PRIMARY KEY
);

CREATE FUNCTION lock_delete() RETURNS trigger
LANGUAGE plpgsql AS $$
  BEGIN
    DELETE FROM lock;
    RETURN NULL;
  END;
$$;

CREATE TRIGGER delete AFTER INSERT ON lock
FOR EACH STATEMENT EXECUTE PROCEDURE lock_delete();

Then you can INSERT INTO lock VALUES (text_input). Records are deleted, but maintain a lock for the duration of the transaction.

This is slower than an advisory lock and imposes vacuum load, but avoids hash conflicts and does not limit the number of locks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.