By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,661 Members | 1,330 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,661 IT Pros & Developers. It's quick & easy.

Column Sizes

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
"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

Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

P: n/a
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

Nov 12 '05 #6

P: n/a
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)

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.