agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Sebastien FLAESCH <sf@4js.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Get last generated serial sequence and set it up when explicit value is used
Date: Fri, 20 Nov 2020 11:23:24 +0100
Message-ID: <4279b691-5699-4e83-2337-e1a4f30553bd@4js.com> (raw)
In-Reply-To: <CAKFQuwY6oxAPp+QFsK2No5q6984rNWaBy-rQ3nRQJ2gf4_etPw@mail.gmail.com>
References: <fc65c19a-7701-13c8-b202-77bfc8fbf995@4js.com>
<CAKFQuwZeof4FQOuNH5hu7=ci+x+vAai3JfBQye15Dvs_nB7deg@mail.gmail.com>
<10622684-b74b-39d2-83d8-4ed2d8d52915@4js.com>
<CAKFQuwY6oxAPp+QFsK2No5q6984rNWaBy-rQ3nRQJ2gf4_etPw@mail.gmail.com>
On 11/20/20 9:49 AM, David G. Johnston wrote:
> On Friday, November 20, 2020, Sebastien FLAESCH <sf@4js.com <mailto:sf@4js.com>> wrote:
>
> Is there any way to avoid the error produced by currval()?
>
>
> No
>
> Ideally, currval() should return zero when no serial was produced yet.
>
>
> I’d accept null, zero is a valid value.
>
>
> Is it possible to write that in a simple SQL expression so it can be used in
> the RETURNING clause of my INSERTs ?
>
>
> Not that I can think of. Maybe as the docs suggest, just do an unconditional setval()? You might be able to combine that with a non-default
> isolation level (guessing here) to get close-enough behavior. You are fighting the existing design of the feature, looking for an in-between position
> of fast-and-concurrent (existing) and fully serialized (where this would be mostly trivial to implement). I don’t know of such a method.
>
> David J.
>
Thanks David for your comments.
I will give a chance to:
insert into table1 (name) values ('aaaa')
returning pkey, (select last_value from table1_pkey_seq);
Followed by a setval('seq',pkey,true), if pkey > last_value ...
In fact I wonder how PostgreSQL actually executes such statement.
To me, it should be an atomic operation so I guess the
(SELECT last_value FROM seq-name)
Should either return the new serial produced by this current INSERT,
or a new serial produced previously by the INSERT in another session,
when the current INSERT do not produce a new serial value.
But it should not return a new serial value that was produced
by another session between the actual local INSERT and the SELECT
last_val sub-query in the RETURNING clause...
Anyway, doing the setval(...pkey...) when pkey value is greater than
the last_value, should also be ok if a new last_value was produced
by another session in-between...
Does that make sense?
Seb
view thread (7+ messages) latest in thread
Message-ID: <4279b691-5699-4e83-2337-e1a4f30553bd@4js.com>
Permalink: ../4279b691-5699-4e83-2337-e1a4f30553bd@4js.com/
Also on: postgresql.org/message-id/4279b691-5699-4e83-2337-e1a4f30553bd@4js.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sf@4js.com, david.g.johnston@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: Get last generated serial sequence and set it up when explicit value is used
In-Reply-To: <4279b691-5699-4e83-2337-e1a4f30553bd@4js.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox