Обсуждение: order of row processing affects updates

Hi all!
consider the following table

table a (id int primary key)
and a particular instance of it:
      id
------------------------
      5
      6

now update a set id = id +1;
fails if the executor processes row with 5 first.
This means that the query will succeed sometimes and fail at other times
(when it processes 6 first).
Are there any workarounds to this?
Basically what we would like to do would be something like doing constraint
validations only at the end of execution of an update query, instead of
after every row update.

to see why sometimes this query might work, run the following commands:

create table a (id int primary key);
insert into a values (6);
insert into a values (5);
update a set id = id +1;

basically we would like to see uniformity in execution. Either the query
should always fail or always succeed.
Are there any standards on this?

any info will be helpful
paraM

On Sun, 2004-09-19 at 08:37 -0500, Paramveer.Singh@trilogy.com wrote:
> table a (id int primary key)
> and a particular instance of it:
>       id
> ------------------------
>       5
>       6
>
> now update a set id = id +1;
> fails if the executor processes row with 5 first.
> Basically what we would like to do would be something like doing constraint
> validations only at the end of execution of an update query, instead of
> after every row update.
does it fail even if it's in a
BEGIN
UPDATE ..
COMMIT
?

--
Marius Andreiana
Galuna - Solutii Linux in Romania
http://www.galuna.ro

On Sun, 19 Sep 2004 08:37:10 -0500, Paramveer.Singh wrote:

> Hi all!
> consider the following table
>
> table a (id int primary key)
> and a particular instance of it:
>       id
> ------------------------
>       5
>       6
>
> now update a set id = id +1;
> fails if the executor processes row with 5 first.
> This means that the query will succeed sometimes and fail at other times
> (when it processes 6 first).
> Are there any workarounds to this?
> Basically what we would like to do would be something like doing constraint
> validations only at the end of execution of an update query, instead of
> after every row update.
>
> to see why sometimes this query might work, run the following commands:
>
> create table a (id int primary key);
> insert into a values (6);
> insert into a values (5);
> update a set id = id +1;
>
> basically we would like to see uniformity in execution. Either the query
> should always fail or always succeed.
> Are there any standards on this?
>

What is the logic for changing the PK?

One option is a SELECT INTO statement.  The id can be incremented during
the statement.

Paramveer.Singh@trilogy.com writes:

> Hi all!
> consider the following table
>
> table a (id int primary key)
> and a particular instance of it:
>       id
> ------------------------
>       5
>       6
>
> now update a set id = id +1;
> fails if the executor processes row with 5 first.

Well the correct way to make this always work would be to make the unique
constraint deferrable and set constraints to be deferred. However Postgres
doesn't support deferring unique constraints.

I don't think there's any practical way to guarantee the ordering of the
update. You could cluster the table on the unique index which would guarantee
it will fail. But clustering is a slow operation and it would have to be done
before every update like this.

To make it work I think the usual work-around is to update the ids to be in a
different range, and then update them to the final values. Something like:

BEGIN;
UPDATE a SET id = -id;
UPDATE a SET id = -id + 1;
COMMIT;


--
greg

Paramveer.Singh@trilogy.com writes:
> Can someone tell me why postgres does not support deferring unique
> constraints?

Because no one's implemented it.  The existing unique-check code is
associated with insertion of a new index entry, and it's nontrivial
to refactor it into a deferrable operation.  There is a TODO item
for this, but don't hold your breath ...

            regards, tom lane