Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
update pk set id = max(id) + 2;
I'm fairly sure this query is illegal per spec. There are ancient
discussions in the archives about whether aggregates in an UPDATE target
list can have a consistent interpretation or not. We never found one,
but never got around to disallowing it either. Maybe it's time. If you
try it with something like sum() you don't get a crash, but you do get
rather bizarre behavior.
Having said that, this may well expose a bug in the MAX-optimization
code that has consequences for more useful queries. I'll take a look
later today if no one beats me to it.
If you try the query on 8.0 and before you don't get a crash either, but
the result is unexpected. Try this version:
create table pk (id bigserial primary key, orig bigint);
insert into pk (id) values (DEFAULT);
insert into pk (id) values (DEFAULT);
insert into pk (id) values (DEFAULT);
update pk set orig = id;
select * from pk;
update pk set id = max(id) + 2;
select * from pk;
One could almost argue that crashing would be better ;-)
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
