Re: Large Objects: Sizeof and Deleting Unlinked LOs

[Harald Fuchs <hari.fuchs@xxxxxxxxx>]

In article <4B72AEB3.4000809@xxxxxxxxxxxxx>,
Howard Cole <howardnews@xxxxxxxxxxxxx> writes:

> Is there an SQL function to determine the size of a large object?

I'm using a pgsql helper function for that:


  CREATE FUNCTION lo_size(oid oid) RETURNS integer
      LANGUAGE plpgsql
      AS $$
  DECLARE
    fd int;
    res int;
    i int;
  BEGIN
    fd = lo_open(oid, 262144); -- INV_READ
    IF fd < 0 THEN
      RAISE EXCEPTION 'lo_open returns %', fd;
    END IF;
    res = lo_lseek(fd, 0, 2); -- SEEK_END
    IF res < 0 THEN
      RAISE EXCEPTION 'lo_lseek returns %', res;
    END IF;
    i = lo_close(fd);
    IF i < 0 THEN
      RAISE EXCEPTION 'lo_close returns %', i;
    END IF;
    RETURN res;
  END;
  $$;

> Also, can I safely delete all the large objects in
> pg_catalog.pg_largeobject? For example:

> select lo_unlink(loid) from (select distinct loid from
> pg_catalog.pg_largeobject) as loids where loid not in (select my_oid
> from my_only_table_that_uses_large_objects)

Did you have a look at contrib/lo?


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general