Hi, I was looking at COPY FREEZE and I found that it's possible to run this command on a foreign table, This really does not make sense as this optimization cannot be applied to a remote table and it can give a user a false impression that it was.
"""
postgres=# begin;
BEGIN
postgres=*# create foreign table t1 (id int) server r1;
CREATE FOREIGN TABLE
postgres=*# copy t1 FROM '/tmp/copy_data' freeze;
COPY 999999
-- on the foreign server
postgres=# SELECT count(*), all_visible, all_frozen, pd_all_visible
FROM pg_visibility('t1'::regclass)
group by all_visible, all_frozen, pd_all_visible;
count | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
5 | f | f | f
(1 row)
"""
The other issue here is that one can only use COPY FREEZE
on a foreign table only if the foreign table is created in the
transaction. A truncate will not work, making the error
message wrong.
"""
postgres=# begin;
BEGIN
postgres=*# truncate table foreign_table_1;
TRUNCATE TABLE
postgres=*# copy foreign_table_1 FROM 'copy_data' freeze;
ERROR: cannot perform COPY FREEZE because the table was not created
or truncated in the current subtransaction
postgres=!#
"""
I think we should just block Foreign tables as we do with
partition tables. Attached patch does that.
I was also looking at why we block a parent from COPY FREEZE[1], but
the comments do not convince me this is a good idea. I think there
are good cases to allow this considering there is a common use case in
which a single
COPY command can load a large amount of data, making the overhead to check the
partitions worth the value of the FREEZE optimization. I will probably
start a separate thread for this.
Regards,
Sami Imseih
Amazon Web Services (AWS)
[1]
https://github.com/postgres/postgres/blob/master/src/backend/commands/copyfrom.c#L727-L735
v1-0001-Disallow-Foreign-Tables-with-COPY-FREEZE.patch
Description: Binary data
