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

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.

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

Cheers,

Jeff

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