Stored columns: Unexpected varattno in expression to be mapped

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Stored columns: Unexpected varattno in expression to be mapped

Thiede, Christoph

Hi all,


as a Postgres user for about half a year, I think I found a bug in psql (PostgreSQL) 12.2 (Debian 12.2-2.pgdg100+1).


Steps to reproduce:

Type the following commands into a PSQL shell:

postgres=# create table mytable (foo text generated always as (bar) stored, bar text);
CREATE TABLE
postgres=# create temporary table mytemp1 (like mytable);
CREATE TABLE
postgres=# create temporary table mytemp2 (like mytable including all);
ERROR:  unexpected varattno 2 in expression to be mapped

Considerations:
It looks as if the INCLUDING ALL logic, however it may work, assumes that stored columns can only refer to columns defined before. On the other hand, CREATE TABLE does not enforce this.
So from my naive perspective, either INCLUDING ALL should be fixed not to make this assumption, or CREATE TABLE & Co. should forbid the definition of such columns.

I would be happy if this bug could be fixed in a later release (provided that it has not already been).

Note: Please reply directly to my email address if you want to contact me, I did not subscribe to the bugs list.

Best regards,
Christoph
Reply | Threaded
Open this post in threaded view
|

Re: Stored columns: Unexpected varattno in expression to be mapped

David G Johnston
On Thu, Jul 16, 2020 at 8:59 AM Thiede, Christoph <[hidden email]> wrote:

as a Postgres user for about half a year, I think I found a bug in psql (PostgreSQL) 12.2 (Debian 12.2-2.pgdg100+1).


Steps to reproduce:

Type the following commands into a PSQL shell:

postgres=# create table mytable (foo text generated always as (bar) stored, bar text);
CREATE TABLE
postgres=# create temporary table mytemp1 (like mytable);
CREATE TABLE
postgres=# create temporary table mytemp2 (like mytable including all);
ERROR:  unexpected varattno 2 in expression to be mapped

I would be happy if this bug could be fixed in a later release (provided that it has not already been).

We have release notes that describe what gets fixed in each minor release (for you there is only one relevant one right now).  The notes for 12.3 seem promising but you could just install it and confirm it yourself.

https://www.postgresql.org/docs/12/release-12-3.html

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Stored columns: Unexpected varattno in expression to be mapped

Tom Lane-2
In reply to this post by Thiede, Christoph
"Thiede, Christoph" <[hidden email]> writes:
> as a Postgres user for about half a year, I think I found a bug in psql (PostgreSQL) 12.2 (Debian 12.2-2.pgdg100+1).

> Steps to reproduce:

> postgres=# create table mytable (foo text generated always as (bar) stored, bar text);
> CREATE TABLE
> postgres=# create temporary table mytemp1 (like mytable);
> CREATE TABLE
> postgres=# create temporary table mytemp2 (like mytable including all);
> ERROR:  unexpected varattno 2 in expression to be mapped

Thanks for the report!  Trying this locally, it seems to work in
either HEAD or v12 branch tip, so I think we fixed the bug already.
I recall some recent work around this area, so that conclusion
is not surprising.  What I don't recall is whether those fixes
were before or after 12.3.  You might find that updating to 12.3
fixes it; if not, it should be fixed in next month's 12.4 release.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Stored columns: Unexpected varattno in expression to be mapped

Michael Paquier-2
On Thu, Jul 16, 2020 at 12:10:35PM -0400, Tom Lane wrote:
> Thanks for the report!  Trying this locally, it seems to work in
> either HEAD or v12 branch tip, so I think we fixed the bug already.
> I recall some recent work around this area, so that conclusion
> is not surprising.  What I don't recall is whether those fixes
> were before or after 12.3.  You might find that updating to 12.3
> fixes it; if not, it should be fixed in next month's 12.4 release.

12.3 works properly, so you could just update to the latest minor
version already released.  Here is the commit:
commit: e92e4a2b68fe877677278c1300db1780956c984e
author: Peter Eisentraut <[hidden email]>
date: Thu, 9 Apr 2020 16:17:55 +0200
Fix CREATE TABLE LIKE INCLUDING GENERATED column order issue

And here is the related thread:
https://www.postgresql.org/message-id/16342-50008a509308f4ca@...
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

AW: Stored columns: Unexpected varattno in expression to be mapped

Thiede, Christoph

Hi all,


thanks for your help! We just upgraded our system to version 12.3 and I can confirm that the issue has been solved. Great!


Best,

Christoph


Von: Michael Paquier <[hidden email]>
Gesendet: Freitag, 17. Juli 2020 02:50:50
An: Tom Lane
Cc: Thiede, Christoph; [hidden email]
Betreff: Re: Stored columns: Unexpected varattno in expression to be mapped
 
On Thu, Jul 16, 2020 at 12:10:35PM -0400, Tom Lane wrote:
> Thanks for the report!  Trying this locally, it seems to work in
> either HEAD or v12 branch tip, so I think we fixed the bug already.
> I recall some recent work around this area, so that conclusion
> is not surprising.  What I don't recall is whether those fixes
> were before or after 12.3.  You might find that updating to 12.3
> fixes it; if not, it should be fixed in next month's 12.4 release.

12.3 works properly, so you could just update to the latest minor
version already released.  Here is the commit:
commit: e92e4a2b68fe877677278c1300db1780956c984e
author: Peter Eisentraut <[hidden email]>
date: Thu, 9 Apr 2020 16:17:55 +0200
Fix CREATE TABLE LIKE INCLUDING GENERATED column order issue

And here is the related thread:
https://www.postgresql.org/message-id/16342-50008a509308f4ca@...
--
Michael