Thread: BUG #16395: error when selecting generated column in a foreign table
BUG #16395: error when selecting generated column in a foreign table
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 16395 Logged by: Mario Leonel Email address: mario.ab.leonel@gmail.com PostgreSQL version: 12.2 Operating system: Ubuntu 18.04 Description: -- bd: postgres CREATE TABLE public.test ( id integer NOT NULL, field1 integer NOT NULL, field2 character varying COLLATE pg_catalog."default", CONSTRAINT test_pkey PRIMARY KEY (id) ); -- bd: test CREATE FOREIGN TABLE public.test( id integer NULL, field1 integer NULL, field2 character varying NULL COLLATE pg_catalog."default", field_calc integer generated always as (field1 * 2) stored ) SERVER fs_postgres; test=# SELECT id, field1, field2, field_calc FROM public.test; ERROR: column "field_calc" does not exist CONTEXT: remote SQL command: SELECT id, field1, field2, field_calc FROM public.test test=# SELECT id, field1, field2 FROM public.test; id | field1 | field2 ----+--------+-------- 1 | 2 | one 2 | 4 | two 3 | 6 | three 4 | 8 | four (4 rows)
On 2020-04-27 16:24, PG Bug reporting form wrote: > -- bd: postgres > CREATE TABLE public.test > ( > id integer NOT NULL, > field1 integer NOT NULL, > field2 character varying COLLATE pg_catalog."default", > CONSTRAINT test_pkey PRIMARY KEY (id) > ); > > -- bd: test > CREATE FOREIGN TABLE public.test( > id integer NULL, > field1 integer NULL, > field2 character varying NULL COLLATE pg_catalog."default", > field_calc integer generated always as (field1 * 2) stored > ) > SERVER fs_postgres; > > test=# SELECT id, field1, field2, field_calc FROM public.test; > ERROR: column "field_calc" does not exist > CONTEXT: remote SQL command: SELECT id, field1, field2, field_calc FROM > public.test Well, your public.test base table does not in fact have a "field_calc" column. You need to add that. Note that these are *stored* generated columns, so the value that is generated by the foreign table on insert is meant to be stored somewhere in the backing store of the foreign table. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #16395: error when selecting generated column in a foreign table
From
Peter Eisentraut
Date:
Here is a test case that shows that it works: create extension postgres_fdw; create server fs_postgres foreign data wrapper postgres_fdw options (dbname 'postgres'); create user mapping for current_user server fs_postgres; CREATE TABLE public.test ( id integer PRIMARY KEY, field1 integer NOT NULL, field2 character varying, field_calc integer ); CREATE FOREIGN TABLE public.test_ft ( id integer, field1 integer, field2 character varying, field_calc integer generated always as (field1 * 2) stored ) SERVER fs_postgres OPTIONS(table_name 'test'); insert into test_ft (id, field1, field2) values (1, 2, 'one'); select * from test_ft; id | field1 | field2 | field_calc ----+--------+--------+------------ 1 | 2 | one | 4 select * from test; id | field1 | field2 | field_calc ----+--------+--------+------------ 1 | 2 | one | 4 In your case, were you adding columns to existing tables? Depending on the order of actions, it might not work the way one might think. Can you reproduce your problem from a blank database? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services