Re: Multiple multithreaded insert

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Арсен Арутюнян <arutar(at)bk(dot)ru>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Multiple multithreaded insert
Date: 2016-10-14 14:59:53
Message-ID: CAOR=d=1kxAz5EaE_7_-v-7vibAh+mfcx79xA7Jws9bAW+PV7rw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Oct 14, 2016 at 7:12 AM, Арсен Арутюнян <arutar(at)bk(dot)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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message FarjadFarid(ChkNet) 2016-10-14 15:01:46 Re: Multiple multithreaded insert
Previous Message Albe Laurenz 2016-10-14 14:27:56 Re: journaled FS and and WAL