Postgres concurrency issue

Question

I wrote the following trigger to guarantee that the field 'filesequence' on the insert receives always the maximum value + 1, for one stakeholder.

CREATE OR REPLACE FUNCTION update_filesequence()
        RETURNS TRIGGER AS '
  DECLARE
    lastSequence file.filesequence%TYPE;

  BEGIN
    IF (NEW.filesequence IS NULL) THEN

      PERFORM ''SELECT id FROM stakeholder WHERE id = NEW.stakeholder FOR UPDATE'';

      SELECT max(filesequence) INTO lastSequence FROM file WHERE stakeholder = NEW.stakeholder;

      IF (lastSequence IS NULL) THEN
        lastSequence = 0;
      END IF;

      lastSequence = lastSequence + 1;

      NEW.filesequence = lastSequence;
    END IF;
    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER file_update_filesequence BEFORE INSERT
  ON file FOR EACH ROW EXECUTE PROCEDURE
  update_filesequence();

But I have repeated 'filesequence' on the database:

select id, filesequence, stakeholder from file where stakeholder=5273;
id      filesequence    stakeholder
6773    5               5273
6774    5               5273

By my undertanding, the SELECT... FOR UPDATE would LOCK two transactions on the same stakeholder, and then the second one would read the new 'filesequence'. But it is not working.

I made some tests on PgAdmin, executing the following:

BEGIN;
select id from stakeholder where id = 5273 FOR UPDATE;

And it realy LOCKED other records being inserted to the same stakeholder. Then it seems that the LOCK is working.

But when I run the application with concurrent uploads, I see then repeating.

Someone could help me in finding what is the issue with my trigger?

Thanks, Douglas.

In plpgsql, doesn't that first BEGIN mark the start of a block, not the start of a transaction? — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Nov 7, 2013 at 11:22
A personal question: where did you learn to use PERFORM like this? I saw a lot of people doing that, and I don't know why. (it is wrong, see my answer)... — MatheusOl
– MatheusOl, Commented Nov 7, 2013 at 12:19
@MikeSherrill'Catcall', that BEGIN does not start a transaction, but on PostgreSQL a function is always called inside a transaction. — MatheusOl
– MatheusOl, Commented Nov 7, 2013 at 12:22
@MatheusOl I tryed to use SELECT and ignore the result, and received an exception on Java with the sugestion of using PERFORM. And I used it without undertanding the correct usage. — DHansen
– DHansen, Commented Nov 7, 2013 at 13:39

MatheusOl · Accepted Answer · 2013-11-07 12:18:45Z

1

Your idea is right. To get an autoincrement based on another field (let's say it designate to a group) you cannot use a sequence, then you have to lock the rows of that group before incrementing it.

The logic of your trigger function does that. But you have a misunderstood about the PERFORM operation. It supposed to be put instead of the SELECT keyword, so it does not receive an string as parameter. It means that when you do:

PERFORM 'SELECT id FROM stakeholder WHERE id = NEW.stakeholder FOR UPDATE';

The PL/pgSQL is actually executing:

SELECT 'SELECT id FROM stakeholder WHERE id = NEW.stakeholder FOR UPDATE';

And ignoring the result.

What you have to do on this line is:

PERFORM id FROM stakeholder WHERE id = NEW.stakeholder FOR UPDATE;

That is it, only change this line and you are done.

answered Nov 7, 2013 at 12:18

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres concurrency issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related