Thread: Column Sizes
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
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
"Brett Maton" <brett.maton@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
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" <brett.maton@itaccounting.co.uk> wrote in message news:botsl3$tsp$1@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 > >
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" <brett.maton@itaccounting.co.uk> wrote in message > news:botsl3$tsp$1@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
Adam Ruth <aruth@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
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