Re: Multiple multithreaded insert

Hi,

Personally where possible I would always internal features of any DB engine. These are pre-compiled, tested for performance and are ingrained into the system. So they can naturally be expected to be slightly faster than even triggers. In the case of your question, why not use serial data types.

As to the threads, in any OS, unsynchronised threads can’t guarantee sequence of operations.

Hope this helps.

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of ????? ????????
Sent: 14 October 2016 14:13
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: [GENERAL] 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