Trouble incrementing a column

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Trouble incrementing a column

Blake McBride-2
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?

Thanks!

Blake McBride
Reply | Threaded
Open this post in threaded view
|

Re: Trouble incrementing a column

Ron-2
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.


Reply | Threaded
Open this post in threaded view
|

Re: Trouble incrementing a column

Tom Lane-2
In reply to this post by Blake McBride-2
Blake McBride <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Trouble incrementing a column

Jeff Janes
On Sat, Nov 23, 2019 at 4:47 PM Tom Lane <[hidden email]> 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


bench_dii.sh (968 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Trouble incrementing a column

Tom Lane-2
Jeff Janes <[hidden email]> writes:
> On Sat, Nov 23, 2019 at 4:47 PM Tom Lane <[hidden email]> 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