Postgres Pro
Downloads
Home   >   mailing lists

Thread: Catching dangling LOBs?

Catching dangling LOBs?

From
Vitaly Belman
Date:
I have quite a large database with lobs being referenced from a few
different table. While I made triggers/store procedures to unlink the
lob once the item is deleted, I am afraid there might be a glitch
somewhere in the code that leaving the LOBs dangling with no use
(except wasting the HD).

Is there a way for me to catch these unused LOBs?

--
  ICQ: 1912453
  AIM: VitalyB1984
  MSN: tmdagent@hotmail.com
  Yahoo!: VitalyBe

Re: Catching dangling LOBs?

From
Tom Lane
Date:
Vitaly Belman <vitalyb@gmail.com> writes:
> Is there a way for me to catch these unused LOBs?

contrib/vacuumlo might help, IIRC.

            regards, tom lane

Re: Catching dangling LOBs?

From
Vitaly Belman
Date:
Thanks Tom, that's indeed what I needed.

However, I am having some problem with it. It goes:

----------------------
Checking edition_picture in public.editions
Checking book_picture in public.books
Failed to check book_picture in table public.books:
ERROR:  function bayes(real, integer, integer, numeric) does not exist
HINT:  No function matches the given name and argument types. You may need to ad
d explicit type casts.
CONTEXT:  SQL function "bayes_books" during inlining
----------------------

The only relation to "bayes_books" in this table is:
--------------
CREATE INDEX i_books_vote_rel_avg
  ON public.books
  USING btree
  (bayes_books(vote_avg, vote_count));
---------------
I tried running the function as it is and it seems to working just
fine. Anyone has an idea what might the vacuum do to trigger such an
error?

On 4/15/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Vitaly Belman <vitalyb@gmail.com> writes:
> > Is there a way for me to catch these unused LOBs?
>
> contrib/vacuumlo might help, IIRC.
>
>                         regards, tom lane
>


--
 ICQ: 1912453
 AIM: VitalyB1984
 MSN: tmdagent@hotmail.com
 Yahoo!: VitalyBe

Re: Catching dangling LOBs?

From
Tom Lane
Date:
Vitaly Belman <vitalyb@gmail.com> writes:
> Failed to check book_picture in table public.books:
> ERROR:  function bayes(real, integer, integer, numeric) does not exist
> HINT:  No function matches the given name and argument types. You may need to ad
> d explicit type casts.
> CONTEXT:  SQL function "bayes_books" during inlining

Can't help you with this when you didn't show us either function.

            regards, tom lane

Re: Catching dangling LOBs?

From
Vitaly Belman
Date:
My bad. Here we go, really simple:

-----------------------------------------

CREATE OR REPLACE FUNCTION public.bayes_books(float4, int4)
  RETURNS float8 AS
'select bayes($1, $2, 5, 3.9)'
  LANGUAGE 'sql' IMMUTABLE;
ALTER FUNCTION public.bayes_books(float4, int4) OWNER TO postgres;

CREATE OR REPLACE FUNCTION functions.bayes(float4, int4, int4, float4)
  RETURNS float8 AS
'select ($2 / ($2+$3::float4)) * $1 + ($3 / ($2+$3::float4)) * $4'
  LANGUAGE 'sql' IMMUTABLE;
ALTER FUNCTION functions.bayes(float4, int4, int4, float4) OWNER TO postgres;

-----------------------------------------

Thanks =)

On 4/16/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Vitaly Belman <vitalyb@gmail.com> writes:
> > Failed to check book_picture in table public.books:
> > ERROR:  function bayes(real, integer, integer, numeric) does not exist
> > HINT:  No function matches the given name and argument types. You may need to ad
> > d explicit type casts.
> > CONTEXT:  SQL function "bayes_books" during inlining
>
> Can't help you with this when you didn't show us either function.
>
>                         regards, tom lane
>


--
 ICQ: 1912453
 AIM: VitalyB1984
 MSN: tmdagent@hotmail.com
 Yahoo!: VitalyBe

Re: Catching dangling LOBs?

From
Tom Lane
Date:
Vitaly Belman <vitalyb@gmail.com> writes:
> My bad. Here we go, really simple:

> CREATE OR REPLACE FUNCTION public.bayes_books(float4, int4)
>   RETURNS float8 AS
> 'select bayes($1, $2, 5, 3.9)'
   ^^^^^^^^^^^^
>   LANGUAGE 'sql' IMMUTABLE;

> CREATE OR REPLACE FUNCTION functions.bayes(float4, int4, int4, float4)
                             ^^^^^^^^^^^^^^^
>   RETURNS float8 AS
> 'select ($2 / ($2+$3::float4)) * $1 + ($3 / ($2+$3::float4)) * $4'
>   LANGUAGE 'sql' IMMUTABLE;

I'll bet that the "functions" schema wasn't in your search path when you
did the VACUUM.  It would be wiser to write "functions.bayes(...)" in
the bayes_books function, so it wouldn't be context-dependent.

            regards, tom lane

Re: Catching dangling LOBs?

From
Vitaly Belman
Date:
That was the problem. Thanks =).

P.S Still a bit strange because "functions" IS in the search_path,
there is no reason for it not to find it from the vacuum.

On 4/16/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Vitaly Belman <vitalyb@gmail.com> writes:
> > My bad. Here we go, really simple:
>
> > CREATE OR REPLACE FUNCTION public.bayes_books(float4, int4)
> >   RETURNS float8 AS
> > 'select bayes($1, $2, 5, 3.9)'
>    ^^^^^^^^^^^^
> >   LANGUAGE 'sql' IMMUTABLE;
>
> > CREATE OR REPLACE FUNCTION functions.bayes(float4, int4, int4, float4)
>                              ^^^^^^^^^^^^^^^
> >   RETURNS float8 AS
> > 'select ($2 / ($2+$3::float4)) * $1 + ($3 / ($2+$3::float4)) * $4'
> >   LANGUAGE 'sql' IMMUTABLE;
>
> I'll bet that the "functions" schema wasn't in your search path when you
> did the VACUUM.  It would be wiser to write "functions.bayes(...)" in
> the bayes_books function, so it wouldn't be context-dependent.
>
>                         regards, tom lane
>


--
 ICQ: 1912453
 AIM: VitalyB1984
 MSN: tmdagent@hotmail.com
 Yahoo!: VitalyBe

Re: Catching dangling LOBs?

From
Tom Lane
Date:
Vitaly Belman <vitalyb@gmail.com> writes:
> P.S Still a bit strange because "functions" IS in the search_path,
> there is no reason for it not to find it from the vacuum.

I see this in vacuumlo.c:

    res = PQexec(conn, "SET search_path = pg_catalog");

            regards, tom lane