Thread: ERROR: row is too big: size 9856, maximum size 8136

On Wed, Jan 19, 2005 at 03:50:30PM -0500, Joel Fradkin wrote:

> I get ERROR:  row is too big: size 9856, maximum size 8136 when inserting a
> view?

Could you post the smallest possible self-contained example that
demonstrates this behavior?  What version of PostgreSQL are you
using?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

I am enclosing a text file if this is not the correct manner let me know
whats best way its not a lot of lines.

ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu,
compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2

ERROR:  row is too big: size 9856, maximum size 8136

Joel Fradkin
Wazagua, LLC
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, LLC. All rights reserved. WAZAGUA, LLCThis email message is for the use of the intended recipient(s)
andmay
 
contain confidential and privileged information.  Any unauthorized review,
use, disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.


-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org] 
Sent: Wednesday, January 19, 2005 10:01 PM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] ERROR: row is too big: size 9856, maximum size 8136

On Wed, Jan 19, 2005 at 03:50:30PM -0500, Joel Fradkin wrote:

> I get ERROR:  row is too big: size 9856, maximum size 8136 when inserting
a
> view?

Could you post the smallest possible self-contained example that
demonstrates this behavior?  What version of PostgreSQL are you
using?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Thu, Jan 20, 2005 at 08:56:12AM -0500, Joel Fradkin wrote:

> I am enclosing a text file if this is not the correct manner let me know
> whats best way its not a lot of lines.

The file you attached contains a view definition but it doesn't
show the underlying tables, nor the statement that resulted in the
error.  By "self-contained example" I mean enough statements that
somebody could copy them into an empty database and reproduce the
problem.

The error "row is too big: size 9856, maximum size 8136" gives a
clue at what's wrong but I'm not sure what circumstances cause it,
because the TOAST mechanism allows rows to be larger than a page.
This is just a guess, but maybe the error means that the non-TOASTABLE
data is exceeding the page size.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Sorry, that was the statement that caused the error.
I was creating a view that exists in the MSSQL land.
It actually joins a few tables. I can put a create statement for all the
tables used in and then create the view and re send the txt file with those.
I am reloading the LINUX from scratch at the moment, but as soon as I get
back up (be tomorrow probably as it takes over night to load the data from
the MSSQL server) I will email with all the pertinent information.

I am re-loading to hopefully get rid of the pg_user error I was getting (I
went to su postgres and created my data base that way after creating a
postgres user as root). My friend said to not create any users just start
the data base up (Fedora core 3) and use pgadmin to create the database.

I was following a how to convert I got off the archives, so I must of messed
something up.

Again thank you for the information. If it is non TOAST (sorry not sure what
that means; I am guessing like not part of a text field data) field sizes
adding up to more the 8k is there some way to produce the data set, or is
this a limit of Postgres in general. If I can not have all the data needed
in a recordset I might have to re-think using postgres is this a limit of
mysql also? I hate to think I have to consider staying on MSSQL as it is not
in our budget.

Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, IncThis email message is for the use of the intended recipient(s)
andmay 
contain confidential and privileged information.  Any unauthorized review,
use, disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Thursday, January 20, 2005 11:33 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] ERROR: row is too big: size 9856, maximum size 8136

On Thu, Jan 20, 2005 at 08:56:12AM -0500, Joel Fradkin wrote:

> I am enclosing a text file if this is not the correct manner let me know
> whats best way its not a lot of lines.

The file you attached contains a view definition but it doesn't
show the underlying tables, nor the statement that resulted in the
error.  By "self-contained example" I mean enough statements that
somebody could copy them into an empty database and reproduce the
problem.

The error "row is too big: size 9856, maximum size 8136" gives a
clue at what's wrong but I'm not sure what circumstances cause it,
because the TOAST mechanism allows rows to be larger than a page.
This is just a guess, but maybe the error means that the non-TOASTABLE
data is exceeding the page size.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Joel Fradkin wrote:
> Sorry, that was the statement that caused the error.
> I was creating a view that exists in the MSSQL land.
> It actually joins a few tables. I can put a create statement for all the
> tables used in and then create the view and re send the txt file with those.
> I am reloading the LINUX from scratch at the moment, but as soon as I get
> back up (be tomorrow probably as it takes over night to load the data from
> the MSSQL server) I will email with all the pertinent information.
> 
> I am re-loading to hopefully get rid of the pg_user error I was getting (I
> went to su postgres and created my data base that way after creating a
> postgres user as root). My friend said to not create any users just start
> the data base up (Fedora core 3) and use pgadmin to create the database.

There are probably RPMs for 8.0 available by now. Might be worth going 
to that from the start, rather than upgrading later.

> I was following a how to convert I got off the archives, so I must of messed
> something up.
> 
> Again thank you for the information. If it is non TOAST (sorry not sure what
> that means; I am guessing like not part of a text field data) field sizes
> adding up to more the 8k is there some way to produce the data set, or is
> this a limit of Postgres in general. If I can not have all the data needed
> in a recordset I might have to re-think using postgres is this a limit of
> mysql also? I hate to think I have to consider staying on MSSQL as it is not
> in our budget.

Well, that's a lot of non-text columns to breach 8kB. It is a definite 
limit, but you shouldn't see it until you have hundreds of columns. If 
you can post the table definitions along with the view definition, that 
should let people see if they can reproduce the problem.

--  Richard Huxton  Archonet Ltd

"Joel Fradkin" <jfradkin@wazagua.com> writes:
> Sorry, that was the statement that caused the error.

Hmm.  The error is associated with trying to store an oversized row.
And CREATE VIEW doesn't store any rows ... except into system catalogs.
So the only theory I can think of is that the pg_rewrite row for the
view is exceeding 8K.  Which can't happen, because no matter how
complicated the view definition rule is, the tuple toaster should have
sprung into action and pushed the rule text out-of-line.

Could we see the results of

select * from pg_class where relname = 'pg_rewrite';

select attname,atttypid::regtype,attstorage from pg_attribute where attrelid = 'pg_rewrite'::regclass and attnum > 0;

7.4 should certainly be configured to have a toast table for pg_rewrite,
but maybe something went wrong during initdb on your installation.
        regards, tom lane

Could very well be an install issue I was getting errors trying to see
template1. I am in the process of re-installing Linux and will let you know
if I still have the error what I get from the select you asked me to run.

I appreciate everyones help.

If anyone has an interest in the .net utility I wrote to pull the tables
schema and data let me know. I used SQLDMO to have the script text available
and then converted it to postgres syntax. I automated the creation and move
of the data including the text fields(it runs a little slow as it does a
read and write at a table row level, but this seemed the best way to get the
text fields to move over). The views and procedures I am afraid I will have
to use the list all views syntax and convert by hand as stuff like left and
datediff would be difficult to auto-convert. I did create a left and right
function but could see a performance hit for each use of function and feel
it will be better to just convert the SQL (the hit was only milisecs on
first number I guess the prepare part, but still might as well have it be as
fast as possible).

Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, IncThis email message is for the use of the intended recipient(s)
andmay 
contain confidential and privileged information.  Any unauthorized review,
use, disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, January 20, 2005 3:38 PM
To: Joel Fradkin
Cc: 'Michael Fuhr'; pgsql-sql@postgresql.org
Subject: Re: [SQL] ERROR: row is too big: size 9856, maximum size 8136

"Joel Fradkin" <jfradkin@wazagua.com> writes:
> Sorry, that was the statement that caused the error.

Hmm.  The error is associated with trying to store an oversized row.
And CREATE VIEW doesn't store any rows ... except into system catalogs.
So the only theory I can think of is that the pg_rewrite row for the
view is exceeding 8K.  Which can't happen, because no matter how
complicated the view definition rule is, the tuple toaster should have
sprung into action and pushed the rule text out-of-line.

Could we see the results of

select * from pg_class where relname = 'pg_rewrite';

select attname,atttypid::regtype,attstorage from pg_attribute where attrelid = 'pg_rewrite'::regclass and attnum > 0;

7.4 should certainly be configured to have a toast table for pg_rewrite,
but maybe something went wrong during initdb on your installation.
        regards, tom lane