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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org