Thread: vacuumlo fails in the presence of a index on expression - demo sql included

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.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> CREATE FUNCTION get_level(varchar) RETURNS int LANGUAGE 'sql' IMMUTABLE STRICT
> SECURITY INVOKER AS 'SELECT id FROM level WHERE abbreviation = $1';

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