Knowledge-Database - ERROR: row is too big: size 9856, maximum size 8136

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

From:	Joel Fradkin
Subject:	ERROR: row is too big: size 9856, maximum size 8136
Date:	Wed, 19 Jan 2005 15:50:30 -0500

This is a multi-part message in MIME format.

------=_NextPart_000_0001_01C4FE3E.9CF536C0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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

Help

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, LLC
This email message is for the use of the intended recipient(s) and may
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.

------=_NextPart_000_0001_01C4FE3E.9CF536C0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

charset=3Dus-ascii">

style=3D'font-size:10.0pt;
font-family:Arial'>I get ERROR: row is too big: size 9856, maximum =
size 8136
when inserting a view?

style=3D'font-size:10.0pt;
font-family:Arial'>

style=3D'font-size:10.0pt;
font-family:Arial'>Help

style=3D'font-size:10.0pt;
font-family:Arial'>

style=3D'font-size:10.0pt;
font-family:Arial'>Joel Fradkin

style=3D'font-size:
12.0pt'>

style=3D'font-size:10.0pt;
font-family:Arial'>Wazagua, LLC

style=3D'font-size:10.0pt;font-family:
Arial'>2520 Trailmate Drface=3DArial>style=3D'font-size:10.0pt;font-family:Arial'>

style=3D'font-size:10.0pt;font-family:
Arial'>Sarasotastyle=3D'font-size:
10.0pt;font-family:Arial'>, face=3DArial> =
style=3D'font-size:10.0pt;font-family:Arial'>Floridasize=3D2
face=3DArial> =
size=3D2 face=3DArial>style=3D'font-size:10.0pt;font-family:Arial'>34243size=3D2 face=3DArial>style=3D'font-size:10.0pt;font-family:Arial'>

Tel. 941-753-7111 ext 305

style=3D'font-size:
12.0pt'>

style=3D'font-size:10.0pt;
font-family:Arial'>href=3D"mailto:jfradkin@wazagua.com">jfradkin@wazagua.com

www.wazagua.com

Powered by Wazagua

Providing you with the latest Web-based technology & advanced =
tools.

© 2004. WAZAGUA, LLC. All rights reserved. WAZAGUA, LLC

This email message is for the use of the intended recipient(s) and =
may
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.

style=3D'font-size:
12.0pt'>

style=3D'font-size:10.0pt;
font-family:Arial'>

style=3D'font-size:
12.0pt'>

------=_NextPart_000_0001_01C4FE3E.9CF536C0--

From:	Michael Fuhr
Subject:	Re: ERROR: row is too big: size 9856, maximum size 8136
Date:	Wed, 19 Jan 2005 20:01:13 -0700

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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

From:	Joel Fradkin
Subject:	Re: ERROR: row is too big: size 9856, maximum size 8136
Date:	Thu, 20 Jan 2005 08:56:12 -0500

This is a multi-part message in MIME format.

------=_NextPart_000_002A_01C4FECD.E54658C0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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, LLC
This email message is for the use of the intended recipient(s) and may
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/

------=_NextPart_000_002A_01C4FECD.E54658C0
Content-Type: text/plain;
name="rowtobigerror.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="rowtobigerror.txt"

CREATE OR REPLACE VIEW thenetworkcallbackview
as

select i.clientnum, i.incidentid, incidentnum, incidenttypeid, incident=
date, subjectid, subjecttypeid, locationid, suspectedactivity.value as desc=
ription, situation, policenotified, injuries,=20
injurednum, injuriesdescription, propertydamage, dama=
gelevelid, incidentreport, i.transfered, i.transftypeid, i.transfdate, i.tr=
ansfbyid, i.transfnum,=20
lastmodified, createdbyid,i.isdeleted, workflowstatus=
, cashloss, merchloss, totloss, markettypeid, typeofweaponid, alarmid, came=
raid,=20
escalated, transcount, orgcalldate_time, anoncaller,=
callernamefirst as firstname, callernamelast as lastname, callernamefirst =
|| ' ' || callernamelast as callernamelast, callertype, callertitle,=20
callerphone, callerext, callerbesttimetocall, callere=
mail, clientname, location, dba, address1, address2, city, state, zip, coun=
try, phone, ext,=20
abuselocation, casesource.value as hoiwincdknown, sup=
portdocsavailable, next24hours, nextwhen, nextwhere, howhotlineknown, inter=
viewernotes,=20
clientinstructions,
case=20
when i.statusid is null then 'none'
when i.statusid =3D 1 then 'open'
when i.statusid =3D 2 then 'closed'
end as status,i.assignedto,
cb.callbackdate, substring(cb.callbacknotes,1,20) as callbacknotes

from tblincident i
inner join tblincidentcallback cb on i.incidentnum =3D cb.incidentid and =
i.clientnum =3D cb.clientnum
left outer join tblcasesource casesource on i.inccasesourceid =3D casesourc=
e.id and (i.clientnum=3Dcasesource.clientnum)
left outer join tblsuspectedactivity suspectedactivity on i.incsuspectedact=
ivityid =3D suspectedactivity.id and (i.clientnum=3Dsuspectedactivity.clie=
ntnum);

------=_NextPart_000_002A_01C4FECD.E54658C0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

------=_NextPart_000_002A_01C4FECD.E54658C0--

From:	Michael Fuhr
Subject:	Re: ERROR: row is too big: size 9856, maximum size 8136
Date:	Thu, 20 Jan 2005 09:32:30 -0700

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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From:	Joel Fradkin
Subject:	Re: ERROR: row is too big: size 9856, maximum size 8136
Date:	Thu, 20 Jan 2005 12:20:26 -0500

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, Inc
This email message is for the use of the intended recipient(s) and may
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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

From:	Richard Huxton
Subject:	Re: ERROR: row is too big: size 9856, maximum size 8136
Date:	Thu, 20 Jan 2005 17:44:09 +0000

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

From:	Tom Lane
Subject:	Re: ERROR: row is too big: size 9856, maximum size 8136
Date:	Thu, 20 Jan 2005 15:38:07 -0500

"Joel Fradkin" 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

From:	Joel Fradkin
Subject:	Re: ERROR: row is too big: size 9856, maximum size 8136
Date:	Thu, 20 Jan 2005 16:08:49 -0500

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, Inc
This email message is for the use of the intended recipient(s) and may
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" 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster