DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=yxvSHfwCENZbvL7bRWn6tKPbg/KvlVctV0brpz61ulACtTrnuOIaQvPMbpfb831RqK6vz0/owiauKXC0ltPPqwk7YCKk2ItTgd8M/dQd7b3rGVrWlQTDrjuXRruDiQKI7uCOdmRcz7vzyw7WNmnPXCo+b8yS8oZ193CrpwjLhxo=;
References: <bnf617dql1hq15cd8vlqdnngdkejtechhg@4ax.com>
	<CAOWY8=ZMJO_GQjk-+MsqKecD-e_XBnXHmwaDDboF+4Yd+3JSTA@mail.gmail.com>
	<201107201808.49293.achill@matrix.gatewaynet.com>
Message-ID: <1311179972.35569.YahooMailNeo@web39704.mail.mud.yahoo.com>
Date: Wed, 20 Jul 2011 09:39:32 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Reply-To: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: Problem retrieving large records (bytea) data from a table
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <201107201808.49293.achill@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

PostgreSQL has to accumulate all the rows of a query before returning the r=
esult set to the client. =C2=A0It is probably spooling those several 400-45=
0 Mb docs, plus all the other attributes, to a temporary file prior to send=
ing the results back. =C2=A0If you have just three document stored in the d=
atabase you're looking at > 1 Gb for the spool file alone.=0A=0ARemember, s=
elect * is a convenience. =C2=A0You will probably get the same response tim=
e as before is you name the columns, except doc_data, in the select clause =
of your query. =C2=A0See the 'extended' attribute of doc_data? =C2=A0That m=
eans the bytea data is stored out-of-line from the other columns like id, c=
reate_date and by. =C2=A0 See=C2=A0http://www.postgresql.org/docs/9.0/inter=
active/storage-toast.html=C2=A0for the particulars of TOAST.=0A=0AIf you ne=
ed to remove the doc data from the table a quick way to do that would be to=
 either update the table and set doc_data to NULL, or use the "create table=
 as select" (CTAS) syntax and specify NULL as the value for doc_date, then =
drop the original table and rename the new one to doc_table. =C2=A0Note if =
you use the CTAS method you will have to alter the table afterwards to re-e=
stablish the not null and default attributes of each column. =C2=A0Don't fo=
rget to recreate the primary key, too.=0A=0AFinally, talk with the develope=
rs to see if the document data really needs to be in the database, or could=
 just be in a file outside of the database. =C2=A0If you need transactional=
 semantics (ACID properties) to manage the documents you may be stuck. =C2=
=A0If not, replace doc_data with doc_filename (or maybe file_n is that colu=
mn already) and move on from there.=0A=0AGood luck,=0A=0ABob Lunney=0A=0A=
=0A=0A=CE=A3=CF=84=CE=B9=CF=82 Wednesday 20 July 2011 17:31:45 =CE=BF/=CE=
=B7 pasman pasma=C5=84ski =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:=0A> You may=
 do a backup of this table. Then with ultraedit search your=0A> documents a=
nd remove them.=0A> =0A> 2011/7/5, jtkells@verizon.net <jtkells@verizon.net=
>:=0A> > I am having a hang condition every time I try to retrieve a large=
=0A> > records (bytea) data from=C2=A0 a table=0A> > The OS is a 5.11 snv_1=
34 i86pc i386 i86pc Solaris with 4GB memory=0A> > running Postgresql 8.4.3 =
with a standard postgresql.conf file (nothing=0A> > has been changed)=0A> >=
 I have the following table called doc_table=0A> >=C2=A0 =C2=A0 =C2=A0  Col=
umn=C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Type=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 Modifiers=C2=A0 =C2=A0  |=0A=
> > Storage=C2=A0 | Description=0A> > ------------------------+------------=
--------------------+---------------------------------------=0A> >=C2=A0 id=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0  | numeric=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null=C2=A0 =
=C2=A0 | main |=0A> >=C2=A0 file_n=C2=A0 =C2=A0 =C2=A0  | character varying=
(4000)=C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0  |=0A> > extended |=0A> >=C2=A0 create_date=C2=A0 | timestamp(6) withou=
t time zone | not null=0A> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0  default (clock_timestamp())=0A> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0  ::timestamp(0)without time zone=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain |=0A> >=C2=A0 desc=C2=A0 =C2=A0 =
=C2=A0 =C2=A0  | character varying(4000)=C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0  |=0A> > extended |=0A> >=C2=A0 doc_cc=
=C2=A0 =C2=A0 =C2=A0  | character varying(120)=C2=A0 =C2=A0 =C2=A0 =C2=A0  =
| not null=C2=A0 =C2=A0 |=0A> > extended |=0A> >=C2=A0 by=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0  | numeric=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null=C2=A0 =C2=A0 | main |=0A>=
 >=C2=A0 doc_data=C2=A0 =C2=A0  | bytea=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0  |=0A> > extended |=0A> >=C2=A0 mime_type_id | cha=
racter varying(16)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null=C2=A0 =C2=
=A0 |=0A> > extended |=0A> >=C2=A0 doc_src=C2=A0 =C2=A0 =C2=A0 | text=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0  |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0  |=0A> > extended |=
=0A> >=C2=A0 doc_stat=C2=A0 =C2=A0  | character varying(512)=C2=A0 =C2=A0 =
=C2=A0 =C2=A0  | not null=0A> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 default 'ACTIVE'::character varying=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0  |=0A> > extended |=0A> > Indexes:=0A> >=C2=A0 =C2=A0 "documents=
_pk" PRIMARY KEY, btree (document_id)=0A> >=0A> >=0A> > A while ago the som=
e developers inserted several records with a=0A> > document (stored in doc_=
Data) that was around 400 - 450 MB each. Now=0A> > when you do a select * (=
all) from this table you get a hang and the=0A> > system becomes unresponsi=
ve.=C2=A0 Prior to these inserts, a select * (all,=0A> > no where clause) w=
orked.=C2=A0 I'm also told a select * from doc_table=0A> > where id =3D xxx=
 still works.=C2=A0 I haven't seen any error message in the=0A> > postgresq=
l log files.=0A> > So I'm not sure how to find these bad records and why I =
am getting a=0A> > hang.=C2=A0 Since this postgresql is running with the de=
fault config files=0A> > could I be running out of a resource?=C2=A0 If so =
I'm not sure how to or=0A> > how much to add to these resources to fix this=
 problem since I have=0A> > very little memory on this system.=C2=A0 Does a=
nyone have any ideas why I=0A> > am getting a hang.=C2=A0 Thanks=0A> >=0A> =
> --=0A> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)=
=0A> > To make changes to your subscription:=0A> > http://www.postgresql.or=
g/mailpref/pgsql-admin=0A> >=0A> =0A> =0A> -- =0A> ------------=0A> pasman=
=0A> =0A=0A=0A=0A-- =0AAchilleas Mantzios=0A=0A-- =0ASent via pgsql-admin m=
ailing list (pgsql-admin@postgresql.org)=0ATo make changes to your subscrip=
tion:=0Ahttp://www.postgresql.org/mailpref/pgsql-admin=0A