[GENERAL] pgsql gives ExecutePlan error after empty UPDATE?
Hello! I am using PostgreSQL 7.0.2 on FreeBSD 3.4. I am trying to update a table with the results of a (possibly empty) selection: zorro_4330=# create table foo(bar int4) \g CREATE zorro_4330=# update foo set bar = count(*) from foo \g ERROR: ExecutePlan: (junk) `ctid' is NULL! In this example, there are no rows in table "foo" to update. Why is postgres returning this cryptic error instead of just UPDATE 0? If I add a sample row, the same query works fine: zorro_4330=# insert into foo values (1) \g INSERT 50688 1 zorro_4330=# update foo set bar = count(*) from foo \g UPDATE 1 If I change my query to update no rows, I get the error again: zorro_4330=# update foo set bar = count(*) from foo where bar 0 \g ERROR: ExecutePlan: (junk) `ctid' is NULL! Postgres seems to be reporting an error because the update matched no rows, but this a perfectly legitimate database operation. I have not been able to find any documentation on "ExecutePlan" or why it thinks there is a problem. Please let me know if you have any suggestions or workarounds for this problem, or if there is somewhere more appropriate to discuss this. Thanks! David - David C Mudie DigitalDeck Inc [EMAIL PROTECTED] San Mateo CA 94402 http://www.digitaldeck.com
Re: [GENERAL] pgsql gives ExecutePlan error after empty UPDATE?
David C Mudie [EMAIL PROTECTED] writes: zorro_4330=# update foo set bar = count(*) from foo \g ERROR: ExecutePlan: (junk) `ctid' is NULL! Postgres seems to be reporting an error because the update matched no rows, but this a perfectly legitimate database operation. Actually, it's flat-out illegal according to SQL92: thou shalt not use an aggregate in UPDATE, quoth the standard. Try it with the count() in a sub-select, which is legal SQL: update foo set bar = (select count(*) from foo); There is a thread going on in pghackers right now about whether it makes sense to allow aggregates outside sub-selects in UPDATE, and if so what it should mean exactly. regards, tom lane
