Postgres Pro
Downloads
Home   >   mailing lists

Thread: [BUG?] UPDATE with RETURNING tableoid

[BUG?] UPDATE with RETURNING tableoid

From
KaiGai Kohei
Date:
I found a strange behavior on the recent v8.4devel with/without
SE-PostgreSQL patch set. Can you reproduce the following behavior?

When I use UPDATE statement with RETURNING clause which contains
references to "tableoid" system column, it returns InvalidOid.
(The correct valus is 16384 in this case.)
However, RETURNING clause with INSERT/DELETE statement works well.

----------
postgres=# CREATE TABLE t1 (a int, b text);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
INSERT 0 3
postgres=# SELECT tableoid, * FROM t1;tableoid | a |  b
----------+---+-----   16384 | 1 | aaa   16384 | 2 | bbb   16384 | 3 | ccc
(3 rows)

postgres=# BEGIN;
BEGIN
postgres=# UPDATE t1 SET b = 'abc' RETURNING tableoid, *;tableoid | a |  b
----------+---+-----       0 | 1 | abc       0 | 2 | abc       0 | 3 | abc
(3 rows)

UPDATE 3
postgres=# ABORT; BEGIN;
ROLLBACK
BEGIN
postgres=# INSERT INTO t1 VALUES (4, 'ddd') RETURNING tableoid, *;tableoid | a |  b
----------+---+-----   16384 | 4 | ddd
(1 row)

INSERT 0 1
postgres=# ABORT; BEGIN;
ROLLBACK
BEGIN
postgres=# DELETE FROM t1 RETURNING tableoid, *;tableoid | a |  b
----------+---+-----   16384 | 1 | aaa   16384 | 2 | bbb   16384 | 3 | ccc
(3 rows)

DELETE 3

-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

Re: [BUG?] UPDATE with RETURNING tableoid

From
Tom Lane
Date:
KaiGai Kohei <kaigai@ak.jp.nec.com> writes:
> I found a strange behavior on the recent v8.4devel with/without
> SE-PostgreSQL patch set. Can you reproduce the following behavior?

> When I use UPDATE statement with RETURNING clause which contains
> references to "tableoid" system column, it returns InvalidOid.
> (The correct valus is 16384 in this case.)
> However, RETURNING clause with INSERT/DELETE statement works well.

Confirmed here, all the way back to 8.2.  I concur it's a bug.
        regards, tom lane

Re: [BUG?] UPDATE with RETURNING tableoid

From
KaiGai Kohei
Date:
Tom Lane wrote:
> KaiGai Kohei <kaigai@ak.jp.nec.com> writes:
>> I found a strange behavior on the recent v8.4devel with/without
>> SE-PostgreSQL patch set. Can you reproduce the following behavior?
> 
>> When I use UPDATE statement with RETURNING clause which contains
>> references to "tableoid" system column, it returns InvalidOid.
>> (The correct valus is 16384 in this case.)
>> However, RETURNING clause with INSERT/DELETE statement works well.
> 
> Confirmed here, all the way back to 8.2.  I concur it's a bug.

http://git.postgresql.org/?p=postgresql.git;a=commitdiff;h=ab7c31031eb4c9fa73d92c19d55a8c7f8bbf4196

Thanks, I confirmed the update eliminates the bug.
-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>