IWETHEY v. 0.3.0 | TODO
1,095 registered users | 1 active user | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New PostgreSQL FDW to pg_largeobject and pg_largeobject_metadata on another host?
Has anyone ever tried that? I've got an application I need to use the client side lo_import via a perl script. Trouble is I'm connecting to a database in one network (call it host1) zone but need the blob data in a database in another network zone (call it host2). I *might* be able to do this by first loading the blob into host1 and then doing something like the below ($OrigLOBId is from the script calling lo_import on host1, connection is to host1):

INSERT INTO foreign_table.pg_largeobject_metadata (lomowner, lomacl)
SELECT lomowner, lomacl
FROM pg_largeobject_metadata
WHERE oid = $OrigLOBId
RETURNING oid AS foreign_oid;

INSERT INTO foreign_table.pg_largeobject (loid, pageno, data)
SELECT foreign, pageno, data
FROM pg_largeobject
WHERE loid = $OrigLOBId;

SELECT lo_unlink($OrigLOBId);

I think this would work, but I don't really like it. Creating a r/w fdw for pg_largeobject and pg_largeobject_metadata strikes me as an extremely unwise idea.

Thoughts?
bcnu,
Mikem

It's mourning in America again.
New postgres_fdw seems to be the method of choice
Haven't had to go this route, so can't say anything definite, but postgres_fdw is the module to use according to the docs. It should do under the covers what you're trying to accomplish manually.
New Thanks, that's what I was going to use.
That would be what I used to create the foreign table foreign_table.pg_largeobject_metadata. In then end it turned out I didn't need to attempt that. I'm pretty sure it would have worked, but was still uncomfortable with it. We make pretty extensive use of postgres_fdw for a variety of things (persisting sequence values across database hosts for instance: create a view as select nextval('your_seq') on host1, use postgres_fdw on host2 to create a foreign table for that view, etc.), but I'd never used it for non-user tables/views before and was just personally uncomfortable doing that. Not for any reason I could articulate, mind you, it just didn't "feel" right.

Thanks for the input!
bcnu,
Mikem

It's mourning in America again.
     PostgreSQL FDW to pg_largeobject and pg_largeobject_metadata on another host? - (mmoffitt) - (2)
         postgres_fdw seems to be the method of choice - (scoenye) - (1)
             Thanks, that's what I was going to use. - (mmoffitt)

GPLed, for your pleasure.
75 ms