Thread: 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        mudie@digitaldeck.com
                San Mateo CA 94402      http://www.digitaldeck.com

David C Mudie <mudie@digitaldeck.com> 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