L.S.

I don't expect that this is the intended behaviour:

db=# SELECT version();
version
---------------------------------------------------------------------
PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66


If you run these commands:

CREATE TABLE "level" ("id" int primary key, "abbreviation" text);
INSERT INTO "level" VALUES (1, 'ONE');
INSERT INTO "level" VALUES (2, 'TWO');
INSERT INTO "level" VALUES (3, 'THREE');

CREATE TABLE "base" ("id" int primary key, "sub_id" int, "level_id" int
references level(id), "image" oid);

CREATE FUNCTION get_level(varchar) RETURNS int LANGUAGE 'sql' IMMUTABLE STRICT
SECURITY INVOKER AS 'SELECT id FROM level WHERE abbreviation = $1';

CREATE INDEX base_idx ON base(sub_id, id) WHERE level_id = get_level('THREE');

Then vacuumlo will output:

# /usr/local/pgsql/bin/vacuumlo -v -n -U postgres db
Connected to db
Test run: no large objects will be removed!
Checking image in public.base
Failed to check image in table public.base:
ERROR: relation "level" does not exist
CONTEXT: SQL function "get_level" during startup


Omitting the index creation makes vacuumlo finish succesfully.





--
Best,




Frank.


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

Frank van Vugt <ftm.van.vugt (AT) foxi (DOT) nl> writes:
The bug is in this function: it's assuming that "level" will always be
in the current search path. Perhaps you want "FROM public.level".

Frankly, since this function is clearly *not* immutable, almost any
misbehavior of an index depending on it is going to be considered
not-a-bug...

regards, tom lane

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