Re: Need to calculate total size of a table along with BLOB data assosciated with it.

2015-09-18 14:43 GMT+02:00 girish R G peetle <giri(dot)anamika0(at)gmail(dot)com>:

> Got it. Thanks Laurenz.
> One thing is little confusing, if large objects don't belong to a table,
> then how does restriction of 8GB table size for TAR format is applicable if
> BLOB data is involved.
> Is it (Regular Table Data size) + (BLOB data held by OID stored in the
> table) ?
>
> If just OID of a large object is copied to a different table say 'Table2'.
> Then for 'Table2' as well should I calculate the total size as (Regular
> Table Data size) + (BLOB data held by OID stored in the table) ?
>
>
There's good chance your issue os with the pg_largeobjects table rather
than your user table.

>
> Thanks
> Girish
>
> On Fri, Sep 18, 2015 at 5:23 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
> wrote:
>
>> girish R G peetle wrote:
>> > PostgreSQL system function, pg_total_relation_size() doesn't include
>> BLOB data size that is associated
>> > with the table.
>> >
>> > TAR dump restore fails if the BLOB data size associated with a table is
>> more than 8GB. (Which is
>> > limitation of TAR format).
>> >
>> > I know we can switch to compressed dump format. But wanted to know if
>> there a way to know true size of
>> > a table.
>>
>> Not easily, because technically, large objects don't belong to a table.
>>
>> You'd have to write your own that scans the table and adds the size of
>> all referenced large objects.
>> It can probably be done in one SELECT using lo_lseek.
>>
>> Yours,
>> Laurenz Albe
>>
>
>

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com