Re: [HACKERS] generated columns

From: Erik Rijkers <er(at)xs4all(dot)nl>
To: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Sergei Kornilov <sk(at)zsrv(dot)org>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Jaime Casanova <jaime(dot)casanova(at)2ndquadrant(dot)com>
Subject: Re: [HACKERS] generated columns
Date: 2019-04-02 18:54:40
Message-ID: d873b54908ddc08e9649576fdf169d25@xs4all.nl
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 2019-04-02 15:36, Erik Rijkers wrote:
> On 2019-04-02 14:43, Peter Eisentraut wrote:
>> On 2019-04-01 10:52, Peter Eisentraut wrote:
>>> On 2019-03-31 05:49, Erik Rijkers wrote:
>>>> STORED in a
>>>> file_fdw foreign table still silently creates the column which then
>>>> turns out to be useless on SELECT, with an error like:
>>>>
>>>> "ERROR: column some_column_name is a generated column
>>>> DETAIL: Generated columns cannot be used in COPY."
>>>>
>>>> Maybe it'd be possible to get an error earlier, i.e., while trying
>>>> to
>>>> create such a useless column?
>>>
>>> I'll look into it.
>>
>> I've been trying to create a test case for file_fdw for this, but I'm
>> not getting your result. Can you send a complete test case?

attached is run_ft.sh which creates a text file: /tmp/pg_head.txt
then sets it up as a foreign table, and adds a generated column.

Then selects a succesful select, followed by a error-producing select.

Some selects are succesful but some fail. I'm not sure why it sometimes
fails (it's not just the explicitness of the generated-column-name like
I suggested earlier).

My output of run_ft.sh is below.

$ ./run_ft.sh
create schema if not exists "tmp";
CREATE SCHEMA
create server if not exists "tmpserver" foreign data wrapper file_fdw;
CREATE SERVER
drop foreign table if exists tmp.pg_head cascade;
DROP FOREIGN TABLE
create foreign table tmp.pg_head (
"Gene" text,
"Ratio H/L normalized Exp1" numeric
)
server tmpserver
options (
delimiter E'\t'
, format 'csv'
, header 'TRUE'
, filename '/tmp/pg_head.txt'
);
CREATE FOREIGN TABLE
alter foreign table tmp.pg_head
add column "Ratio H/L normalized Exp1 Log2 (Generated column)"
numeric generated always as (case when "Ratio H/L normalized Exp1" > 0
then log(2, "Ratio H/L normalized Exp1") else null end) stored
;
ALTER FOREIGN TABLE
-- this is OK (although the generated-column values are all empty/null)
select
"Gene"
, "Ratio H/L normalized Exp1"
, "Ratio H/L normalized Exp1 Log2 (Generated column)"
from tmp.pg_head
limit 3 ;
Gene | Ratio H/L normalized Exp1 | Ratio H/L normalized Exp1 Log2
(Generated column)
--------+---------------------------+---------------------------------------------------
Dhx9 | NaN |
Gapdh | 0.42288 |
Gm8797 | 0.81352 |
(3 rows)

-- but this fails
select
"Gene"
, "Ratio H/L normalized Exp1 Log2 (Generated column)"
from tmp.pg_head
limit 3 ;
ERROR: column "Ratio H/L normalized Exp1 Log2 (Generated column)" is a
generated column
DETAIL: Generated columns cannot be used in COPY.

Attachment Content-Type Size
run_ft.sh text/x-shellscript 1.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2019-04-02 19:15:31 Re: ToDo: show size of partitioned table
Previous Message Alvaro Herrera 2019-04-02 18:31:32 Re: monitoring CREATE INDEX [CONCURRENTLY]