PostgreSQL: selecting large objects (e.g., text) non programmatically (with a tool)

Question

We are using PostgreSQL with JPA which maps large strings to columns with type TEXT.

While programmatically we are able to read and write the data, pgAdmin and psql just show me the object ID when I select the data.

Is there a way/tool to quickly select the data without having to use some API (e.g., JDBC)?

An example:

doi=> \d+ xmlsnippet;
                           Table "doi.xmlsnippet"
    Column    |          Type          | Modifiers | Storage  | Description 
--------------+------------------------+-----------+----------+-------------
 dbid         | bigint                 | not null  | plain    | 
 version      | bigint                 | not null  | plain    | 
 datasnippet  | text                   |           | extended | 
 doi          | character varying(255) |           | extended | 
 doipool_dbid | bigint                 |           | plain    | 

The column datasnippet contains some large strings (with XML code)

When I select it, I get

doi=> select * from xmlsnippet;
 dbid  | version | datasnippet |           doi            | doipool_dbid 
-------+---------+-------------+--------------------------+--------------
    43 |       0 | 282878      | 10.3929/ethz-a-000077127 |           13
    44 |       0 | 282879      | 10.3929/ethz-a-000085677 |           13
    45 |       0 | 282880      | 10.3929/ethz-a-000085786 |           13
    46 |       0 | 282881      | 10.3929/ethz-a-000087642 |           13
    47 |       0 | 282882      | 10.3929/ethz-a-000088898 |           13

                   ^^^^^^^

Edit: if I perform the same query using JDBC I get the expected content (the text I stored in the column)

Answer 1

Perhaps one of "the usual suspects" (i.e. JPA / Hibernate / PostgreSQL JDBC driver) mapped the column into the "Large Object" system of PostgreSQL.

A quick test in the psql shell:

db=> \lo_export 282878 /tmp/x.txt
lo_export

would export the stuff referenced by the first id from your example into the file /tmp/x.txt. Examine it with an editor. Tell us whether that's your data or not.

If this kind of mapping really happened, then you have a maintenance problem - large object must be deleted by hand and have some other intrinsic shortcomings. But that's another story.