Thread: question about upper limit on TEXT size
Hi, all. I'm working with a database record that includes one field of type text. It's my understanding there is no upper limit on this data type's size. However, I'm running into size related problems with this. It could be due to the interface I'm using (my programs are in C, and I'm using the libpq-fe.h interface (version 1.80 of 2001/11/08). On the PGresult var I'm getting back, the PQresultErrorMessage is telling me ERROR: Tuple is too big: size 54776, max size 8128 Is this a limit imposed by the C interface? Or something else? I tried the same INSERT command causing the failure at the psql commandline interface and got a core dump. If I shorten up the TEXT field to something on one line or so, the command goes okay (so it's not something else in the format of that command). Thoughts, comments? This is very annoying, as I do need to be able to put in very large amounts of text into that field now and then. Thanks, --Cindy -- ctmoore@uci.edu
Hi all. I am trying to do the following in plpgsql. Can someone advise create table test ( "Test Column" varchar(10) ); In the plpgsql function I am trying to update this column. update test set "Test Column" = ''test data''; The function fails with Error: Error while executing the query; ERROR: unterminated " in name "Test (State:S1000, Native Code: 1) It works fine if the column name is "TestColumn" but not "Test Column". Thanks Andrew
Cindy <ctmoore@uci.edu> writes:
> Hi, all. I'm working with a database record that includes one field
> of type text. It's my understanding there is no upper limit on this
> data type's size. However, I'm running into size related problems
> with this. It could be due to the interface I'm using (my programs
> are in C, and I'm using the libpq-fe.h interface (version 1.80 of
> 2001/11/08). On the PGresult var I'm getting back, the
> PQresultErrorMessage is telling me ERROR: Tuple is too big: size
> 54776, max size 8128
<blink> What Postgres version are you using? If it's recent (7.1
or later), could we see the full schema of your table?
regards, tom lane
"Andrew Bartley" <abartley@evolvosystems.com> writes:
> In the plpgsql function I am trying to update this column.
> update test
> set "Test Column" = ''test data'';
> The function fails with
> Error: Error while executing the query;
plpgsql doesn't cope with embedded spaces in identifiers, even
when double-quoted :-(
This is fixed as of a couple days ago in development sources, but
until 7.3 comes out you'll have to avoid such names. Sorry.
regards, tom lane
Tom Lane writes:
>Cindy <ctmoore@uci.edu> writes:
>> Hi, all. I'm working with a database record that includes one field
>> of type text. It's my understanding there is no upper limit on this
>> data type's size. However, I'm running into size related problems
>> with this. It could be due to the interface I'm using (my programs
>> are in C, and I'm using the libpq-fe.h interface (version 1.80 of
>> 2001/11/08). On the PGresult var I'm getting back, the
>> PQresultErrorMessage is telling me ERROR: Tuple is too big: size
>> 54776, max size 8128
>
><blink> What Postgres version are you using? If it's recent (7.1
>or later), could we see the full schema of your table?
7.2, on Solaris:
search_info=# CREATE TABLE searches (
search_info(# id SERIAL,
search_info(# uid VARCHAR(20),
search_info(# search_name VARCHAR(50),
search_info(# search_type INT,
search_info(# date_saved TIMESTAMP,
search_info(# date_lastupdated TIMESTAMP,
search_info(# parameters TEXT,
search_info(# temporary BOOLEAN
search_info(# );
<snip>
search_info=# \d searches
Table "searches"
Attribute | Type | Modifier
------------------+-------------+-----------------------------------------------
----
id | integer | not null default nextval('searches_id_seq'::te
xt)
uid | varchar(20) |
search_name | varchar(50) |
search_type | integer |
date_saved | timestamp |
date_lastupdated | timestamp |
parameters | text |
temporary | boolean |
Index: searches_id_key
I could attach (or send separately) the exact INSERT command that generates
the error message if you like. (I"m refraining from blasting a 54K file out
to the list, though :). It certainly doesn't seem to matter what's already
in the database, and I can successfully run the same INSERT command if I set
the parameters field to "" rather than its longer form.
Thanks,
--Cindy
--
ctmoore@uci.edu
Cindy <ctmoore@uci.edu> writes:
> Tom Lane writes:
>>> Cindy <ctmoore@uci.edu> writes:
>>>> PQresultErrorMessage is telling me ERROR: Tuple is too big: size
>>>> 54776, max size 8128
>>>
>>> <blink> What Postgres version are you using? If it's recent (7.1
>>> or later), could we see the full schema of your table?
> 7.2, on Solaris:
Hmph --- that certainly shouldn't have any trouble with a measly 50K
text value. I wonder if you've somehow managed to turn off TOASTing
on this column? Could we see the output of
select * from pg_class where relname = 'searches';
and also the output of
select attname,attstorage from pg_attribute where attrelid =
(select oid from pg_class where relname = 'searches');
Do you see the same failure if you try to INSERT the data into
a freshly-created table, using the exact CREATE TABLE command you
just showed us?
> I could attach (or send separately) the exact INSERT command that generates
> the error message if you like.
If you want to send it to me (not the whole list), I'll be sure to try
it. I'll bet a nickel that it'll work for me though ... there's
something odd about this, and I'm not sure what.
regards, tom lane
Tom Lane writes:
>Cindy <ctmoore@uci.edu> writes:
>> 7.2, on Solaris:
>
>Hmph --- that certainly shouldn't have any trouble with a measly 50K
>text value.
My thought, too :*)
>I wonder if you've somehow managed to turn off TOASTing
>on this column? Could we see the output of
> select * from pg_class where relname = 'searches';
relname | reltype | relowner | relam | relpages | reltuples | rellongrelid | r
elhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | reluke
ys | relfkeys | relrefs | relhaspkey | relhasrules | relacl
----------+---------+----------+-------+----------+-----------+--------------+--
-----------+-------------+---------+----------+-----------+-------------+-------
---+----------+---------+------------+-------------+-------------------
searches | 0 | 3895 | 0 | 10 | 1000 | 0 | t
| f | r | 8 | 0 | 0 |
0 | 0 | 0 | f | f | {"=","wwwd=arwR"}
(1 row)
(sorry for the poor formatting...that's pretty much how it appears
on my terminal)
>and also the output of
> select attname,attstorage from pg_attribute where attrelid =
> (select oid from pg_class where relname = 'searches');
attname | attstorage
------------------+------------
cmax | p
cmin | p
ctid | p
date_lastupdated | p
date_saved | p
id | p
oid | p
parameters | p
search_name | p
search_type | p
temporary | p
uid | p
xmax | p
xmin | p
(14 rows)
>Do you see the same failure if you try to INSERT the data into
>a freshly-created table, using the exact CREATE TABLE command you
>just showed us?
That's exactly what I did prior to sending out mail to the list...I dropped
the table and re-created it.
--Cindy
--
ctmoore@uci.edu
Cindy <ctmoore@uci.edu> writes:
> 7.2, on Solaris:
>>> on this column? Could we see the output of
>>> select * from pg_class where relname = 'searches';
> relname | reltype | relowner | relam | relpages | reltuples | rellongrelid | r
> elhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | reluke
> ys | relfkeys | relrefs | relhaspkey | relhasrules | relacl
Um, well, I wasn't expecting to see the answer in the column headers...
but if that's the schema of pg_class then you are *not* talking to a 7.2
server. It's evidently 7.0.something. Try "select version()" to
find out for sure. But in any case, you need a server update ---
pre-7.1 Postgres didn't have support for rows wider than 8K.
regards, tom lane