Catching dangling LOBs?

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

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

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

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

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

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

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