From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | heads up on large text fields. |
Date: | 2018-09-22 00:28:37 |
Message-ID: | b92e20ab-549d-bcb3-affe-df7f721f832a@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Ravi Krishna | 2018-09-22 00:46:57 | Re: New behavior with JDBC 42.2.5 |
Previous Message | Adrian Klaver | 2018-09-21 22:27:09 | Re: New behavior with JDBC 42.2.5 |