heads up on large text fields.

[Rob Sargent <robjsargent@xxxxxxxxx>]

Playing around with files-in-text-field.  I can happily slam a 10M file into a text field in a table defined as

gtdb=# \d gt.ld
                    Table "gt.ld"
    Column    | Type | Collation | Nullable | Default
--------------+------+-----------+----------+---------
 id           | uuid |           | not null |
 name         | text |           |          |
 markerset_id | uuid |           | not null |
 ld           | text |           |          |
Indexes:
    "ld_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "ld_markerset_id_fkey" FOREIGN KEY (markerset_id) REFERENCES base.markerset(id)

gtdb=# select id, length(ld), substring(ld,  300000, 100) from gt.ld;
                  id                  | length  |          substring          
--------------------------------------+---------+------------------------------
 28f8dc94-c9d1-4c45-b504-fda585b497f8 | 6742760 |                             +
                                      |         | 3 2     rs1858447|5852230|10+
                                      |         |  0.500000  0.500000         +
                                      |         | 3 2     rs1567706|5853767|10+
                                      |         |  0.500000  0.500000
(1 row)

And I can regenerate the file using java (with jOOQ) in respectable time.

However, I get into deep dodo when I try redirecting psql output such as

select ld from gt.ld\g /tmp/regen.file

"/tmp/regen.file" gets very large, very fast and I have to pg_terminate_backend.  Tried this three times, once using "\o test.blob" instead.

h009357:loader$ ls -ltr
total 2048
-rwxr-xr-x 1 u0138544 camplab 10002460672 Sep 21 15:49 test.blob

Frankly, I'm suspicious of that ls (it's an smb mount of 25T partition) but that's what's in the emacs shell buffer!
The re-direct isn't a must-have, but was hoping that would be an easy way to get a file back.

Have I simply gone too far with text type?

h009357:share$ psql --version
psql (PostgreSQL) 10.5 (Ubuntu 10.5-0ubuntu0.18.04)

postgres=# select version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)