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

Поиск
Список
Период
Сортировка
От Patric de Waha
Тема Automated analyze process fails with custom function, which works perfect as regular user (8.4.2).
Дата
Msg-id 4C5FB567.80709@p-dw.com
обсуждение исходный текст
Ответы Re: Automated analyze process fails with custom function, which works perfect as regular user (8.4.2).  (Robert Haas <robertmhaas@gmail.com>)
Список 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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "Rene Novotny"
Дата:
Сообщение: Re: BUG #5601: cannot create language plperl;
Следующее
От: "Itagaki Takahiro"
Дата:
Сообщение: BUG #5608: array_agg() consumes too much memory