order of row processing affects updates

[Paramveer.Singh]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Marius Andreiana]

On Sun, 2004-09-19 at 08:37 -0500, Pa************* @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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Jim J]

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.

[Greg Stark]

Pa************* @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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org