From: | Patric de Waha <lists(at)p-dw(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Automated analyze process fails with custom function, which works perfect as regular user (8.4.2). |
Date: | 2010-08-09 07:59:35 |
Message-ID: | 4C5FB567.80709@p-dw.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hello,
I found something weird in the logs.
Apparently the automated analyze process has some
problems with custom functions.
Using my regular database user for this db, i get no problems
using the functions which fail for the automated analyze process.
Can this be a search_path problem?
Extract from log:
------------------------------------------
ERROR: function defined(thirdparty.hstore, text) does not exist at
character 9
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT defined( $1 , $2 )
CONTEXT: PL/pgSQL function "hstorextract" line 2 at IF
automatic analyze of table "blade.directory.tbldirectory_18410"
------------------------------------------
The "defined" function here is the one shipped by the hstore module.
I imported all contrib modules, into a schema called "thirdparty".
The function hstorextract is defined as:
------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "directory"."hstorextract" (
"inp" "thirdparty"."hstore",
"key" text
)
RETURNS text AS
$body$
BEGIN
IF defined(inp, key) THEN
return inp->key;
ELSE
return null;
END IF;
END;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
--------------------------------------------------------------------
tbldirecotry_18410 is a child of the tbldirectory parent table, intended
for data partitioning.
-- tbldirectory_18410 relation definition same as tbldirectory
---------------
From here on I post the defintions: Column | Type
-------------------+--------------------------------+----------------------------------
ficlassinstance | bigint | not null
dtregistered | timestamp without time zone | not null default
now()
dtapproved | boolean | not null default true
firelated | integer[] | not null default
'{}'::integer[]
dtproperties | hstore | not null default
''::hstore
fiklass | integer | not null
dttags | hstore | not null default
''::hstore
dtratings | hstore | not null default
''::hstore
dtvisible | boolean | not null default true
dtspoint | spoint |
dtcheckinspoint | spoint |
dtcheckints | timestamp(0) without time zone |
dtcheckinlocation | bigint |
---------------------------------------------------------------------------------------
I have an index on this table which is as follows, (might play a role):
CREATE INDEX "idx_136_v1_7" ON "directory"."tbldirectory_18410"
USING btree (((hstorextract(dtproperties,
'sphericalProvider'::text))::bigint))
WITH (fillfactor = 70)
WHERE defined(dtproperties, 'sphericalProvider'::text);
------------------------------------------------------------------------
Postgresql Version: 8.4.2..
Thanks in advance,
Patric de Waha
From | Date | Subject | |
---|---|---|---|
Next Message | Itagaki Takahiro | 2010-08-09 09:41:12 | BUG #5608: array_agg() consumes too much memory |
Previous Message | Rene Novotny | 2010-08-09 07:30:41 | Re: BUG #5601: cannot create language plperl; |