Thread: [BUG?] Missing OID column from pl/pgsql record
Hi All,
We recently upgraded to PostgreSQL 11. We have discovered an undocumented (or well-hidden) new behavior in pl/pgSQL.
SELECT * INTO record variable does not include the OID column any more. All other system columns are still stored in the record variable. We tested this using a table WITH(OIDS).
PostgreSQL 9.5.18:
postgres=# create table test(id oid) with (oids);
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
DO
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
DO
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
PostgreSQL 9.6.14:
postgres=# create table test(id oid) with (oids);
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
DO
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
DO
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
PostgreSQL 10.9:
postgres=# create table test(id oid) with (oids);
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
DO
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
DO
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
PostgreSQL 11.4:
postgres=# create table test(id oid) with (oids);
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
ERROR: record "_x" has no field "oid"
CONTEXT: SQL statement "SELECT _x.oid"
PL/pgSQL function inline_code_block line 1 at assignment
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
ERROR: record "_x" has no field "oid"
CONTEXT: SQL statement "SELECT _x.oid"
PL/pgSQL function inline_code_block line 1 at assignment
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
The PG11 documentation for pl/pgsql states that variable assignment to a record type executes a select statement, which PG11 seems to be doing as intended.
Was including the OID column in assignment a feature in previous versions?
Kind Regards,
Paolo Saul
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL
VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.
"Saul, Jean Paolo" <paolo.saul@verizonconnect.com> writes:
> We recently upgraded to PostgreSQL 11. We have discovered an undocumented
> (or well-hidden) new behavior in pl/pgSQL.
> SELECT * INTO record variable does not include the OID column any more. All
> other system columns are still stored in the record variable.
Hmmm. TBH I think it's quite an accidental implementation artifact that
any of the underlying table's system columns get transmitted into the
record variable. It seems to happen only if you write exactly "select *",
or the table's exact column list, which probably means that it's tied
to the "physical tlist" optimization that the planner applies in such
cases. And that likely means it'll stop happening if there are any
dropped columns, to mention just one non-obvious problem case.
Between that and the fact that OID system columns are going away
entirely in v12, it's hard to muster any enthusiasm to mess with this.
I'd suggest explicitly selecting the OID, as you did in your non-failing
example.
regards, tom lane
Hi Tom,
First off, thanks for the reply and all the hard work the PostgreSQL team does.
Yes we are aware that OID is being demoted on PG12.
The fix on our end is trivial, just unexpected. It's a bit more obvious now why there would not be a release note info for this.
From your answer, as I understand, transmitting the system columns implicitly was an unintended feature in the first place, and we should not have relied on this.
Kind regards,
Paolo Saul
On Tue, 16 Jul 2019 at 14:50, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Saul, Jean Paolo" <paolo.saul@verizonconnect.com> writes:
> We recently upgraded to PostgreSQL 11. We have discovered an undocumented
> (or well-hidden) new behavior in pl/pgSQL.
> SELECT * INTO record variable does not include the OID column any more. All
> other system columns are still stored in the record variable.
Hmmm. TBH I think it's quite an accidental implementation artifact that
any of the underlying table's system columns get transmitted into the
record variable. It seems to happen only if you write exactly "select *",
or the table's exact column list, which probably means that it's tied
to the "physical tlist" optimization that the planner applies in such
cases. And that likely means it'll stop happening if there are any
dropped columns, to mention just one non-obvious problem case.
Between that and the fact that OID system columns are going away
entirely in v12, it's hard to muster any enthusiasm to mess with this.
I'd suggest explicitly selecting the OID, as you did in your non-failing
example.
regards, tom lane
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL
VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.