Thread: Multiple multithreaded insert

Hi, everyone!

I have a table:

create table testpr(id serial,priority integer,unique(priority) DEFERRABLE, primary key(id));

and a trigger which, when added to this table, automatically sets priority as the maximum value +1

CREATE OR REPLACE FUNCTION PriorityCheck() RETURNS trigger AS $$

BEGIN

    NEW.priority := (SELECT coalesce(max(priority),0)+1 from testpr);

    RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER InsertTrigger BEFORE INSERT on testpr for EACH ROW

EXECUTE PROCEDURE PriorityCheck();

Will the priority be serial, when there is multithreaded addition of values to the table?

Which is:

Thread 1  insert into testpr(priority) values (1), (1), (1), (1), (1), (1), (1);

Thread 2  insert into testpr(priority) values (2), (2), (2), (2), (2), (2), (2);

The result (priority):

Thread 1: (1) (2) (3) (4) (5) (6) (7)

Thread 2: (8) (9) (10) (11) (12) (13) (14)

-- Arsen Arutyunyan

On Fri, Oct 14, 2016 at 7:12 AM, Арсен Арутюнян <arutar@bk.ru> wrote:
> Hi, everyone!
>
> I have a table:
>
> create table testpr(id serial,priority integer,unique(priority) DEFERRABLE,
> primary key(id));
>

This:

> and a trigger which, when added to this table, automatically sets priority
> as the maximum value +1

Leads to THIS:

> The result (priority):
>
> Thread 1: (1) (2) (3) (4) (5) (6) (7)
>
> Thread 2: (8) (9) (10) (11) (12) (13) (14)

If you have to have monotonically increasing priorities with no gaps,
that's the price you pay, unless you can pre-allocate them or
something.

Basically max(id)+1 is a db anti-pattern.