Column Sizes

[Brett Maton]

Hi NG,

How do I find out the size of a column ?

I am retrieving large objects from the pg_largeobject table and creating a
files, I would like to know the length of the data column so that I can
implement buffering instead of writing hundreds of small chunks to disk.

Any help would be appreciated.

Thanks in advance,

Brett

[Adam Ruth]

On Nov 12, 2003, at 11:04 AM, Brett Maton wrote:

Hi NG,

How do I find out the size of a column ?

I am retrieving large objects from the pg_largeobject table and
creating a
files, I would like to know the length of the data column so that I can
implement buffering instead of writing hundreds of small chunks to
disk.

Any help would be appreciated.

Thanks in advance,

Brett

I always check for the size this way:

select sum(length(data)) from pg_largeobject where loid = xxx;

Or if you want to know the size of an individual page:

select pageno, length(data) from pg_largeobject where loid = xxx order
by pageno;

Hope this help,

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

[Doug McNaught]

"Brett Maton" <br*********@itaccounting.co.uk> writes:

Hi NG,

How do I find out the size of a column ?

I am retrieving large objects from the pg_largeobject table and creating a
files, I would like to know the length of the data column so that I can
implement buffering instead of writing hundreds of small chunks to disk.

Funny, there doesn't seem to be an lo_size() function--I was sure
there was one. I'm not sure if there's a way to find out the size of
a large object without reading it all.

If you just do lo_read() in a loop with a reasonably large buffer it
should be fairly efficient.

-Doug

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

[Brett Maton]

Thanks for the answers guys,

I ran a quick query lastnight select length(data) from pg_largeobject
where loid = xxxx which prompted my post.

The query returned 'n' rows of 2048 bytes and an incomplete "page" of say
236, as I wasn't really thinking about it that much I assumed for some
reason that the data column was limited to a maximum of 2048 bytes by the
database. I was hoping to select this value (2048) from a query
(like \d tablename describes the columns and sizes) to build a buffer
either a) large enough or b) sensibly proportioned.

Although my question may not be relevant to this particular issue there
are times that I would like to now the size of a particular column, and I
believe
that it's possible to get this value from the database using a query and
pg_catalog etc, just not sure how to achieve this.

Thanks again,

Brett
"Brett Maton" <br*********@itaccounting.co.uk> wrote in message
news:bo**********@news.hub.org...

Hi NG,

How do I find out the size of a column ?

I am retrieving large objects from the pg_largeobject table and creating a files, I would like to know the length of the data column so that I can
implement buffering instead of writing hundreds of small chunks to disk.

Any help would be appreciated.

Thanks in advance,

Brett

[Adam Ruth]

On Nov 13, 2003, at 2:47 AM, Brett Maton wrote:

Thanks for the answers guys,

I ran a quick query lastnight select length(data) from pg_largeobject
where loid = xxxx which prompted my post.

The query returned 'n' rows of 2048 bytes and an incomplete "page"
of say
236, as I wasn't really thinking about it that much I assumed for some
reason that the data column was limited to a maximum of 2048 bytes
by the
database. I was hoping to select this value (2048) from a query
(like \d tablename describes the columns and sizes) to build a buffer
either a) large enough or b) sensibly proportioned.

Although my question may not be relevant to this particular issue
there
are times that I would like to now the size of a particular column,
and I
believe
that it's possible to get this value from the database using a query
and
pg_catalog etc, just not sure how to achieve this.

Thanks again,

Brett
"Brett Maton" <br*********@itaccounting.co.uk> wrote in message
news:bo**********@news.hub.org...

Hi NG,

How do I find out the size of a column ?

I am retrieving large objects from the pg_largeobject table and
creating

a

files, I would like to know the length of the data column so that I
can
implement buffering instead of writing hundreds of small chunks to
disk.

Any help would be appreciated.

Thanks in advance,

Bret

The column type for pg_largeobject.data is bytea with no length
specified. That means, unfortunately, that there's no way to get the
size of the column from the catalog. It does seem that the large
object functions do split the data into 2k chunks, but it would
probably be a simple thing to check the source to determine if this is
always true. But it could always change in a future version.

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

[Tom Lane]

Adam Ruth <ar***@intercation.com> writes:

... It does seem that the large
object functions do split the data into 2k chunks, but it would
probably be a simple thing to check the source to determine if this is
always true. But it could always change in a future version.

My recollection is that the large object chunk size is defined as BLCKSZ/4,
so you'd get 2K with the default 8K block size, but it could also be
4K or 8K with the other likely choices for block size.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Adam Ruth]

On Nov 12, 2003, at 11:04 AM, Brett Maton wrote:

Hi NG,

How do I find out the size of a column ?

I am retrieving large objects from the pg_largeobject table and
creating a
files, I would like to know the length of the data column so that I can
implement buffering instead of writing hundreds of small chunks to
disk.

Any help would be appreciated.

Thanks in advance,

Brett

I always check for the size this way:

select sum(length(data)) from pg_largeobject where loid = xxx;

Or if you want to know the size of an individual page:

select pageno, length(data) from pg_largeobject where loid = xxx order
by pageno;

Hope this help,

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