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
"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.