Thread: Automated analyze process fails with custom function, which works perfect as regular user (8.4.2).

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

On Mon, Aug 9, 2010 at 3:59 AM, Patric de Waha <lists@p-dw.com> wrote:
> =A0Hello,
> =A0 =A0I found something weird in the logs.
> =A0 =A0Apparently the automated analyze process has some
> =A0 =A0problems with custom functions.
>
> =A0 =A0Using my regular database user for this db, i get no problems
> =A0 =A0using the functions which fail for the automated analyze process.
> =A0 =A0Can this be a search_path problem?

Maybe you should do ALTER FUNCTION name SET search_path =3D 'the right
search path' and see if that helps.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company