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:
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.
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 |