Обсуждение: Trouble incrementing a column

On 11/23/19 3:28 PM, Blake McBride wrote:
> Greetings,
>
> I am using PostgreSQL 10.10.  I am having trouble incrementing a column 
> for reasons I can't see.  It's probably some basic SQL thing.  Your help 
> is appreciated.
>
> create table my_table (
>     listid char(36) not null,
>     seq smallint not null,
>     item varchar(4096),
>     primary key (listid, seq)
> );
>
> insert into my_table (listid, seq) values ('abc', 1);
> insert into my_table (listid, seq) values ('abc', 2);
>
> -- the following works some of the time
> update my_table set seq=seq+1;
>
> -- the following doe not work for reasons I do not know
> update my_table set seq=seq+1 where listid='abc';
>
> What I get is a duplicate primary key.  I wouldn't think I'd get that 
> because I'd think the whole thing is done in a transaction so that 
> duplicate checks wouldn't be done till the end (essentially).
>
> Is there a clean way to do this?

A deferrable constraint might solve the problem.

https://www.commandprompt.com/blog/postgres_deferred_primary_keys/


https://www.postgresql.org/docs/9.6/sql-altertable.html

ALTER TABLE ... ALTER CONSTRAINT ... DEFERRABLE INITIALLY DEFERRED;



-- 
Angular momentum makes the world go 'round.

Blake McBride <blake1024@gmail.com> writes:
> I am using PostgreSQL 10.10.  I am having trouble incrementing a column for
> reasons I can't see.  It's probably some basic SQL thing.  Your help is
> appreciated.

> create table my_table (
>     listid char(36) not null,
>     seq smallint not null,
>     item varchar(4096),
>     primary key (listid, seq)
> );

> insert into my_table (listid, seq) values ('abc', 1);
> insert into my_table (listid, seq) values ('abc', 2);

> -- the following works some of the time
> update my_table set seq=seq+1;

> -- the following doe not work for reasons I do not know
> update my_table set seq=seq+1 where listid='abc';

> What I get is a duplicate primary key.  I wouldn't think I'd get that
> because I'd think the whole thing is done in a transaction so that
> duplicate checks wouldn't be done till the end (essentially).

Postgres only treats primary/unique keys that way if you explicitly
mark the constraint as DEFERRABLE.  Otherwise, the uniqueness check is
made immediately as each row is updated, so it's very order-dependent
as to whether something like the above will work.

Note that you pay a fairly substantial performance penalty for deferring
the check, which is why it isn't the default, even though the SQL spec
says it ought to be.

This is documented in some obscure place [ ... looks around ... ]
ah, see "Non-Deferred Uniqueness Constraints" under Compatibility
in the CREATE TABLE reference page.

            regards, tom lane

Jeff Janes <jeff.janes@gmail.com> writes:
> On Sat, Nov 23, 2019 at 4:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Note that you pay a fairly substantial performance penalty for deferring
>> the check, which is why it isn't the default, even though the SQL spec
>> says it ought to be.

> Do you know what the worst case scenario is for the performance of
> deferring the check to the end of the statement (with deferred initially
> immediate)?  Upon testing, I get a penalty of 2 to 5%, which seems pretty
> small, but I might not be testing the most adverse situation.  See attached.

Hm, I would have expected more, though not factor-of-10 or anything
like that.  But that's just vague recollection from when we put in
the feature.  I'm not surprised if the numbers have moved since.

> The main "cost" that prevents from using DII routinely is that they can't
> receive foreign key constraints.

Yeah, that's an issue.

            regards, tom lane