Thread: postgres_fdw has insufficient support for large object

On Mon, May 23, 2022 at 7:16 AM Saladin <jiaoshuntian@highgo.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@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.

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

Dilip Kumar <dilipbalaut@gmail.com> writes:
> 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'));

For this example to "work", lo_import() would have to somehow know
that its result would get inserted into some foreign table and
then go create the large object on that table's server instead
of locally.

This is unlikely to happen, for about ten different reasons that
you should have no trouble understanding if you stop to think
about it.

            regards, tom lane

On Mon, May 23, 2022 at 10:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Dilip Kumar <dilipbalaut@gmail.com> writes:
> > 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'));
>
> For this example to "work", lo_import() would have to somehow know
> that its result would get inserted into some foreign table and
> then go create the large object on that table's server instead
> of locally.

Yeah that makes sense.  The lo_import() is just running as an
independent function to import the object into pg_largeobject and
return the Oid so definitely it has no business to know where that Oid
will be stored :)


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

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.

"=?gb18030?B?U2FsYWRpbg==?=" <jiaoshuntian@highgo.com> writes:
> 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.

The big picture here is that Postgres is a hodgepodge of features
that were developed at different times and with different quality
standards, over a period that's now approaching forty years.
Some of these features interoperate better than others.  Large
objects, in particular, are largely a mess with a lot of issues
such as not having a well-defined garbage collection mechanism.
They do not interoperate well with foreign tables, or several
other things, and you will not find anybody excited about putting
effort into fixing that.  We're unlikely to remove large objects
altogether, because some people use them successfully and we're not
about breaking cases that work today.  But they're fundamentally
incompatible with use in foreign tables in the way you expect,
and that is not likely to get fixed.

            regards, tom lane

On Mon, May 23, 2022 at 1:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The big picture here is that Postgres is a hodgepodge of features
> that were developed at different times and with different quality
> standards, over a period that's now approaching forty years.
> Some of these features interoperate better than others.  Large
> objects, in particular, are largely a mess with a lot of issues
> such as not having a well-defined garbage collection mechanism.
> They do not interoperate well with foreign tables, or several
> other things, and you will not find anybody excited about putting
> effort into fixing that.  We're unlikely to remove large objects
> altogether, because some people use them successfully and we're not
> about breaking cases that work today.

We could possibly have a category of such features and label them
"obsolete", where we don't threaten to remove them someday (i.e.
"deprecated"), but we are not going to improve them in any meaningful
way, and users would be warned about using them in new projects if
better alternatives are available.

-- 
John Naylor
EDB: http://www.enterprisedb.com

On Mon, May 23, 2022 at 2:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The big picture here is that Postgres is a hodgepodge of features
> that were developed at different times and with different quality
> standards, over a period that's now approaching forty years.
> Some of these features interoperate better than others.  Large
> objects, in particular, are largely a mess with a lot of issues
> such as not having a well-defined garbage collection mechanism.

Well, in one sense, the garbage mechanism is pretty well-defined:
objects get removed when you explicitly remove them. Given that
PostgreSQL has no idea that the value you store in your OID column has
any relationship with the large object that is identified by that OID,
I don't see how it could work any other way. The problem isn't really
that the behavior is unreasonable or even badly-designed. The real
issue is that it's not what people want.

I used to think that what people wanted was something like TOAST.
After all, large objects can be a lot bigger than toasted values, and
that size limitation might be a problem for some people. But then I
realized that there's a pretty important behavioral difference: when
you fetch a row that contains an OID that happens to identify a large
object, you can look at the rest of the row and then decide whether or
not you want to fetch the large object. If you just use a regular
column, with a data type of text or bytea, and store really big values
in there, you don't have that option: the server sends you all the
data whether you want it or not. Similarly, on the storage side, you
can't send the value to the server a chunk at a time, which means you
have to buffer the whole value in memory on the client side first,
which might be inconvenient.

I don't think that allowing larger toasted values would actually be
that hard. We couldn't do it with varlena, but we could introduce a
new negative typlen that corresponds to some new representation that
permits larger values. That would require sorting out various places
where we randomly limit things to 1GB, but I think that's pretty
doable. However, I'm not sure that would really solve any problem,
because who wants to malloc(1TB) in your application, and then
probably again in libpq, to schlep that value to the server -- and
then do the same thing in reverse when you get the value back? Without
some notion of certain values that are accessed via streaming rather
than monolithically, I can't really imagine getting to a satisfying
place.

I realize I've drifted away from the original topic a bit. I just
think it's interesting to think about what a better mechanism might
look like.

-- 
Robert Haas
EDB: http://www.enterprisedb.com