Postgres Pro
Downloads
Home   >   mailing lists

Thread: computing and updating the size of a table with large objects

computing and updating the size of a table with large objects

From
"Marco Bizzarri"
Date:
Hi all.

I've a table with large objects, here is the definition:


PAFlow-emmebi=# \d documenti
           Tabella "public.documenti"
 Colonna |         Tipo          | Modificatori
---------+-----------------------+--------------
 id_doc  | character varying(50) | not null
 file    | oid                   |
 kind    | integer               | not null
 size    | bigint                |
Indici:
    "documenti_pkey" chiave primaria, btree (id_doc, kind)

(sorry for the mixed language output)

I need to populate the size attribute with the size of the large object in file.

My first attempt was:

SELECT  lo_lseek(lo_open(file, 262144), 0, 2) AS size FROM documenti ;

This opens the largeobject, and passes the result to the lo_lseek,
which goes up to the end of the largeobject, and thus computes the
size.

Now I could prepare an update which does the job. My doubt is that I
could use all the resources in the update (documenti table is quite
large).

I thought I could use something like this:

SELECT  lo_lseek(lo_open(file, 262144), 0, 2) AS size, lo_close(0)
FROM documenti ;

which is quite nasty, and relies on side effects happening in the
proper order, but uses just one file descriptor for all the query.

Does anyone has any other suggestion?

Regards
Marco

--
Marco Bizzarri
http://iliveinpisa.blogspot.com/

Re: computing and updating the size of a table with large objects

From
"Daniel Verite"
Date:
Marco Bizzarri wrote:

>            Tabella "public.documenti"
>  Colonna |         Tipo          | Modificatori
> ---------+-----------------------+--------------
>  id_doc  | character varying(50) | not null
>  file    | oid                   |
>  kind    | integer               | not null
>  size    | bigint                |
> Indici:
>     "documenti_pkey" chiave primaria, btree (id_doc, kind)
>
> (sorry for the mixed language output)
>
> I need to populate the size attribute with the size of the large object in file.

You can get the sizes from pg_largeobject, this way:

SELECT id_doc, sum(length(data)) as filesize
FROM documenti, pg_largeobject
WHERE documenti.file = pg_largeobject.loid
GROUP BY id_doc;

--
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

Re: computing and updating the size of a table with large objects

From
"Daniel Verite"
Date:
    Marco Bizzarri wrote:

>            Tabella "public.documenti"
>  Colonna |         Tipo          | Modificatori
> ---------+-----------------------+--------------
>  id_doc  | character varying(50) | not null
>  file    | oid                   |
>  kind    | integer               | not null
>  size    | bigint                |
> Indici:
>     "documenti_pkey" chiave primaria, btree (id_doc, kind)
>
> (sorry for the mixed language output)
>
> I need to populate the size attribute with the size of the large object in file.

You can get the sizes from pg_largeobject, this way:

SELECT id_doc, sum(length(data)) as filesize
FROM documenti, pg_largeobject
WHERE documenti.file = pg_largeobject.loid
GROUP BY id_doc;

--
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

Re: computing and updating the size of a table with large objects

From
"Marco Bizzarri"
Date:
On 8/31/07, Daniel Verite <daniel@manitou-mail.org> wrote:
>
> You can get the sizes from pg_largeobject, this way:
>
> SELECT id_doc, sum(length(data)) as filesize
> FROM documenti, pg_largeobject
> WHERE documenti.file = pg_largeobject.loid
> GROUP BY id_doc;
>
> --
>  Daniel
>  PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
>

Thanks a lot for the suggestion: I didn't think to use the
pg_largeobject: much cleaner, now :-)

Regards
Marco
--
Marco Bizzarri
http://iliveinpisa.blogspot.com/