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
(mockbu...@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.
