Thread: bytea Issue - Reg

bytea Issue - Reg

From

sramay

Date:

03 June 2014, 21:47:55

Hi all,

I am having a specifc issue of bytea.   When we started storing more records
in the database on bytea field on version 9.1.x the data has gone to
pg_toast tables.   The strange observation is now after a table size of 700
gb it now all of a sudden reporting table not found on the tomcat logs but
table is very much there.

Where I am going wrong?




--
View this message in context: http://postgresql.1045698.n5.nabble.com/bytea-Issue-Reg-tp5805838.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: bytea Issue - Reg

From

Adrian Klaver

Date:

04 June 2014, 03:39:45

On 06/03/2014 01:25 AM, sramay wrote:
> Hi all,
>
> I am having a specifc issue of bytea.   When we started storing more records
> in the database on bytea field on version 9.1.x the data has gone to
> pg_toast tables.   The strange observation is now after a table size of 700
> gb it now all of a sudden reporting table not found on the tomcat logs but
> table is very much there.

Is the actual table reported missing or the TOAST table?

What is the exact error message?

>
> Where I am going wrong?
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com

Re: bytea Issue - Reg

From

sramay

Date:

04 June 2014, 16:47:32

Sir,

No it is reporting that base table is missing not the TOAST table.
Is Streaming Replication can cause this issue?.   Data is not being
aged which is much.   As the records add during this period on the base
table
which is hardly 300 MB ( 3million records) . the db size 700 GB.

Version I am using 9.1.8.  If I move to latest 9.3.x version will it help?

There are many TOAST Table, how no and growth of the file is controlled.

I always use the community version of it, source compiled.

Regards

Ramachandran S



--
View this message in context: http://postgresql.1045698.n5.nabble.com/bytea-Issue-Reg-tp5805838p5805952.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: bytea Issue - Reg

From

Adrian Klaver

Date:

05 June 2014, 04:14:05

On 06/03/2014 10:02 PM, sramay wrote:
> Sir,
>
> No it is reporting that base table is missing not the TOAST table.
> Is Streaming Replication can cause this issue?.

What is the exact error message Tomcat is reporting?

> Data is not being
> aged which is much.   As the records add during this period on the base
> table
> which is hardly 300 MB ( 3million records) . the db size 700 GB.

In your first post you said the table was 700 GB, now the database,
which is correct?

How are you getting the sizes you are reporting?

>
> Version I am using 9.1.8.  If I move to latest 9.3.x version will it help?

The issue has not been identified, so it is premature to think about
changing versions.

>
> There are many TOAST Table, how no and growth of the file is controlled.

This is expected:

http://www.postgresql.org/docs/9.3/interactive/storage-file-layout.html

When a table or index exceeds 1 GB, it is divided into gigabyte-sized
segments. The first segment's file name is the same as the filenode;
subsequent segments are named filenode.1, filenode.2, etc. This
arrangement avoids problems on platforms that have file size
limitations. (Actually, 1 GB is just the default segment size. The
segment size can be adjusted using the configuration option
--with-segsize when building PostgreSQL.) In principle, free space map
and visibility map forks could require multiple segments as well, though
this is unlikely to happen in practice.

>
> I always use the community version of it, source compiled.
>
> Regards
>
> Ramachandran S
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/bytea-Issue-Reg-tp5805838p5805952.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: bytea Issue - Reg

From

sramay

Date:

06 June 2014, 19:15:16

Sir,

The base table is having bytea and having records around 32 lakhs shows size
of 300 mb.
bytea field has attached documents size is not shown in the base table.

The message on  Tomcat is

--begin text --

4 Jun, 2014 3:29:07 PM org.apache.catalina.core.StandardWrapperValve invoke
INFO: WARN [TP-Processor24] JDBCExceptionReporter.logExceptions(100) | SQL
Error: 0, SQLState: 42P01
ERROR [TP-Processor24] JDBCExceptionReporter.logExceptions(101) | ERROR:
relation "public.file_attachments" does not exist
  Position: 238
org.hibernate.exception.SQLGrammarException: could not execute query
        at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
        at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.loader.Loader.doList(Loader.java:2231)
        at
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
        at org.hibernate.loader.Loader.list(Loader.java:2120)
        at
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
        at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
        at
org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:328)
        at
com.nic.edistrict.certificates.dao.impl.FileAttachmentsDaoImpl.getFileAttachments(FileAttachmentsDaoImpl.java:53)
        at

com.nic.edistrict.certificates.service.impl.FileAttachmentsManagerImpl.getFileAttachments(FileAttachmentsManagerImpl.java:28)
        at sun.reflect.GeneratedMethodAccessor1999.invoke(Unknown Source)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:28

--end text

Even though table is present and connections are opening for subsequently.
The message
appear at frequent intervals.   Is there any way to see the complete size of
a table
which has bytea filed like database
pg_size_pretty(pg_relation_size('file_attachments') shows
just 300 mb.

Any help is welcome.

Regards

Ramachandran s



--
View this message in context: http://postgresql.1045698.n5.nabble.com/bytea-Issue-Reg-tp5805838p5806146.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: bytea Issue - Reg

From

Alan Hodgson

Date:

06 June 2014, 21:00:43

On Wednesday, June 04, 2014 10:49:18 PM sramay wrote:
> relation "public.file_attachments" does not exist

.. is almost certainly not a size problem. What does your PostgreSQL log say?
I suspect your app is connecting to the wrong database.

Re: bytea Issue - Reg

From

Adrian Klaver

Date:

06 June 2014, 23:19:03

On 06/04/2014 10:49 PM, sramay wrote:
> Sir,
>
> The base table is having bytea and having records around 32 lakhs shows size
> of 300 mb.
> bytea field has attached documents size is not shown in the base table.
>
> The message on  Tomcat is
>
> --begin text --
>
> 4 Jun, 2014 3:29:07 PM org.apache.catalina.core.StandardWrapperValve invoke
> INFO: WARN [TP-Processor24] JDBCExceptionReporter.logExceptions(100) | SQL
> Error: 0, SQLState: 42P01
> ERROR [TP-Processor24] JDBCExceptionReporter.logExceptions(101) | ERROR:
> relation "public.file_attachments" does not exist
>    Position: 238
> org.hibernate.exception.SQLGrammarException: could not execute query
>          at
> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
>          at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
>          at org.hibernate.loader.Loader.doList(Loader.java:2231)
>          at
> org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
>          at org.hibernate.loader.Loader.list(Loader.java:2120)
>          at
> org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
>          at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
>          at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
>          at
> org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:328)
>          at
> com.nic.edistrict.certificates.dao.impl.FileAttachmentsDaoImpl.getFileAttachments(FileAttachmentsDaoImpl.java:53)
>          at
>
com.nic.edistrict.certificates.service.impl.FileAttachmentsManagerImpl.getFileAttachments(FileAttachmentsManagerImpl.java:28)
>          at sun.reflect.GeneratedMethodAccessor1999.invoke(Unknown Source)
>          at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>          at java.lang.reflect.Method.invoke(Method.java:597)
>          at
> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:28
>
> --end text


To add to Alan's comment about checking which database you are
connecting to, I have one:

In seems there are a couple of Java programs at work here, Hibernate and
the Spring Framework. I see something dealing with reflection and that
raises a flag.

Are you sure that the framework/Hibernate are doing the right thing?

>
> Even though table is present and connections are opening for subsequently.
> The message
> appear at frequent intervals.   Is there any way to see the complete size of
> a table
> which has bytea filed like database
> pg_size_pretty(pg_relation_size('file_attachments') shows
> just 300 mb.

http://www.postgresql.org/docs/9.1/interactive/functions-admin.html

pg_table_size accepts the OID or name of a table and returns the disk
space needed for that table, exclusive of indexes. (TOAST space, free
space map, and visibility map are included.)

>
> Any help is welcome.
>
> Regards
>
> Ramachandran s
>



--
Adrian Klaver
adrian.klaver@aklaver.com