Re: postgres_fdw has insufficient support for large object

On Mon, May 23, 2022 at 7:16 AM Saladin <jiaoshuntian(at)highgo(dot)com> wrote:
>
> 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：
> Both database A and database B:
> create extension postgres_fdw;
> select * from pg_largeobject_metadata ;--check if exists any rows
> select * from pg_largeobject;
> database A:
> CREATE TABLE oid_table (id INT NOT NULL, oid_1 oid, oid_2 oid);
> 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;
> database B:
> CREATE server srv_postgres_cn_0 FOREIGN data wrapper postgres_fdw
> options(host '127.0.0.1', port '9000', dbname 'postgres');
> CREATE USER mapping FOR highgo server srv_postgres_cn_0 options(user
> 'highgo', password '123456');
> 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: 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.

I don't think that the local pg_largeobject should maintain the
foreign server's data, instead that the export should fetch the data
from the remote's pg_largeobject table. Then I just checked inserting
into the foriegn from your test as shown below[1] and I noticed that
the insert is also importing the large object into the local
pg_largeobject instead of the remote server's pg_large object, which
clearly seems broken to me. Basically, the actual row is inserted on
the remote server and the large object w.r.t. the same row is imported
in local pg_largeobject.

insert into oid_table_ft values(1,lo_import('/home/highgo/pictures/bird.jpg'));

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com