[BUG?] Missing OID column from pl/pgsql record

From: "Saul, Jean Paolo" <paolo(dot)saul(at)verizonconnect(dot)com>
To: PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: [BUG?] Missing OID column from pl/pgsql record
Date: 2019-07-16 01:41:08
Message-ID: CA+73ANes0gK9vgmq4=-fD5OzCEGxK2c4iAQjwwo3CP0uK0XbOg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

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

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

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

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Prakash Ramakrishnan 2019-07-16 01:47:14 Re: perl issue
Previous Message PG Bug reporting form 2019-07-15 20:17:13 BUG #15907: JSON Select Issue