question about upper limit on TEXT size

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