From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: Problem retrieving large records (bytea) data from a table
Date: Wed, 20 Jul 2011 18:08:49 +0300
User-Agent: KMail/1.9.7
References: <bnf617dql1hq15cd8vlqdnngdkejtechhg@4ax.com>
	<CAOWY8=ZMJO_GQjk-+MsqKecD-e_XBnXHmwaDDboF+4Yd+3JSTA@mail.gmail.com>
In-Reply-To: 
 <CAOWY8=ZMJO_GQjk-+MsqKecD-e_XBnXHmwaDDboF+4Yd+3JSTA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201107201808.49293.achill@matrix.gatewaynet.com>

he could use smth like this to know the size like:
SELECT count(*),CASE WHEN length(doc_data)<50000000 THEN '<=3D50 MB' WHEN  =
length(doc_data)<100000000 THEN '<=3D100 MB' ELSE '>100MB' END from doc_tab=
le GROUP by 2;

and then based on the above, to do finer queries to find large data.

However, i dont know if cursor based queries (like the ones used by JDBC) s=
hould be affected by the size.
Normally they would not. However, querying by psql this way will definitely=
 be slow.


=CE=A3=CF=84=CE=B9=CF=82 Wednesday 20 July 2011 17:31:45 =CE=BF/=CE=B7 pasm=
an pasma=C5=84ski =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> You may do a backup of this table. Then with ultraedit search your
> documents and remove them.
>=20
> 2011/7/5, jtkells@verizon.net <jtkells@verizon.net>:
> > I am having a hang condition every time I try to retrieve a large
> > records (bytea) data from  a table
> > The OS is a 5.11 snv_134 i86pc i386 i86pc Solaris with 4GB memory
> > running Postgresql 8.4.3 with a standard postgresql.conf file (nothing
> > has been changed)
> > I have the following table called doc_table
> >       Column  |              Type              |  Modifiers     |
> > Storage  | Description
> > ------------------------+--------------------------------+-------------=
=2D-------------------------
> >  id           | numeric                        | not null    | main |
> >  file_n       | character varying(4000)        |             |
> > extended |
> >  create_date  | timestamp(6) without time zone | not null
> >                 default (clock_timestamp())
> >                 ::timestamp(0)without time zone              | plain |
> >  desc         | character varying(4000)        |             |
> > extended |
> >  doc_cc       | character varying(120)         | not null    |
> > extended |
> >  by           | numeric                        | not null    | main |
> >  doc_data     | bytea                          |             |
> > extended |
> >  mime_type_id | character varying(16)          | not null    |
> > extended |
> >  doc_src      | text                           |             |
> > extended |
> >  doc_stat     | character varying(512)         | not null
> >                default 'ACTIVE'::character varying           |
> > extended |
> > Indexes:
> >    "documents_pk" PRIMARY KEY, btree (document_id)
> >
> >
> > A while ago the some developers inserted several records with a
> > document (stored in doc_Data) that was around 400 - 450 MB each. Now
> > when you do a select * (all) from this table you get a hang and the
> > system becomes unresponsive.  Prior to these inserts, a select * (all,
> > no where clause) worked.  I'm also told a select * from doc_table
> > where id =3D xxx still works.  I haven't seen any error message in the
> > postgresql log files.
> > So I'm not sure how to find these bad records and why I am getting a
> > hang.  Since this postgresql is running with the default config files
> > could I be running out of a resource?  If so I'm not sure how to or
> > how much to add to these resources to fix this problem since I have
> > very little memory on this system.  Does anyone have any ideas why I
> > am getting a hang.  Thanks
> >
> > --
> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-admin
> >
>=20
>=20
> --=20
> ------------
> pasman
>=20


=2D-=20
Achilleas Mantzios