postgres_fdw has insufficient support for large object

From: Saladin <jiaoshuntian(at)highgo(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: postgres_fdw has insufficient support for large object
Date: 2022-05-23 01:45:40
Message-ID: tencent_D2EC2EB1604EA4F3AAF9B2F9EBB3EBBDEC05@qq.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

PostgreSQL version:PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Platform information:Linux version 3.10.0-1127.el7.x86_64
(mockbuild(at)kbuilder(dot)bsys(dot)centos(dot)org) (gcc version 4.8.5 20150623 (Red Hat
4.8.5-39) (GCC) ) #1 SMP Tue Mar 31 23:36:51 UTC 2020

I created two tables for testing. One is remote table in database A and the
other is foreign table in database B.
Then i use INSERT statements with lo_import function to add data to remote
table.

The output i have got.
The result is remote table,pg_largeobject in database
A,pg_largeobject_metadata in database A have correct data.
But,i don't find correct data in pg_largeobject and pg_largeobject_metadata
in database B.

My operation steps are as follows:
&nbsp;&nbsp;&nbsp; Both database A and database B:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; create extension postgres_fdw;
select * from pg_largeobject_metadata ;--check if exists any rows
select * from pg_largeobject;
&nbsp;&nbsp;&nbsp; database A:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CREATE TABLE oid_table (id INT NOT NULL, oid_1 oid, oid_2 oid);
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; insert into oid_table values
(1,lo_import('/home/highgo/pictures/bird.jpg'),lo_import('/home/highgo/pictures/pig.jpg'));--Two
ordinary files on the machine
select * from oid_table;
&nbsp;&nbsp;&nbsp; database B:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CREATE server srv_postgres_cn_0 FOREIGN data wrapper postgres_fdw
options(host '127.0.0.1', port '9000', dbname 'postgres');
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CREATE USER mapping FOR highgo server srv_postgres_cn_0 options(user
'highgo', password '123456');
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CREATE FOREIGN TABLE oid_table_ft (id INT NOT NULL, oid_1 oid, oid_2
oid) server srv_postgres_cn_0 options(schema_name 'public', table_name
'oid_table');
select * from oid_table_ft;
select lo_export(oid_1,'/usr/local/pgsql/out.jpg') from oid_table_ft where
id=1;--the result is "ERROR:&nbsp; large object xxx does not exist"

comments :
my default databse is "postgres" and default user is "highgo" and I don't
think these will have an impact on this problem.

The output i expected:
pg_largeobject_metadata and pg_largeobject in both database A and database
B should have rows.Shouldn't only in database A.So, i can use large object
functions
to operate large_objectin remote table or foreign table.

Please forgive me, English is not my mother tongue. If you have any doubts
about my description, please contact me, and I will reply to you at the
first time. Thank you sincerely and look forward to your reply.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Shinoda, Noriyoshi (PN Japan FSIP) 2022-05-23 02:00:18 PG15 beta1 fix pg_database view document
Previous Message Kyotaro Horiguchi 2022-05-23 01:32:40 Re: Add --{no-,}bypassrls flags to createuser