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@kbuilder.bsys.centos.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.
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.
