Thread: question about upper limit on TEXT size

question about upper limit on TEXT size

From
Cindy
Date:
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

plpgsql question

From
"Andrew Bartley"
Date:
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


Re: question about upper limit on TEXT size

From
Tom Lane
Date:
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

Re: plpgsql question

From
Tom Lane
Date:
"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

Re: question about upper limit on TEXT size

From
Cindy
Date:
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

Re: question about upper limit on TEXT size

From
Tom Lane
Date:
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

Re: question about upper limit on TEXT size

From
Cindy
Date:
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

Re: question about upper limit on TEXT size

From
Tom Lane
Date:
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