Thread: Cannot find hstore operator

Paul van der Linden <paul.doskabouter@gmail.com> writes:
> during maintenance I saw a lot of lines in my postgreslog saying:
> CONTEXT:  SQL function "line_function" during inlining
>         automatic analyze of table "osm.planet_osm_line"
> ERROR:  operator does not exist: public.hstore -> unknown at character 45

It sounds like line_function is careless about its search path
assumptions.  auto-analyze will run index expressions with the
search_path set to empty (i.e., only pg_catalog is accessible)
and hstore isn't normally installed in pg_catalog.

The easy fix would be to attach "SET search_path = public"
to that function, but I believe that destroys the ability to
inline it, which might be a performance problem for you.
Alternatively you could schema-qualify the operator name,
that is "foo OPERATOR(public.->) bar".

            regards, tom lane

Paul van der Linden <paul.doskabouter@gmail.com> writes:
> Thanks for the clarification, but giving up performance is a no-go for us.
> Also I have my concerns about shemaqualifying each and every use of the ->
> operator, there are really a lot of them in my functions and it would
> severely impact readability.
> Are these the only 2 solutions possible?

As of v14 you could use SQL-style function definitions, so that the
operator is parsed at function definition time instead of runtime.

            regards, tom lane

Hi. In https://www.mail-archive.com/pgsql-general@lists.postgresql.org/msg29321.html
I asked:

> On Tue, Jan 11, 2022 at 5:30 PM David G. Johnston <david.g.johns...@gmail.com> wrote:
> > On Tuesday, January 11, 2022, Dominique Devienne <ddevie...@gmail.com>> wrote:
> >> This means the template-schema name is part of the DDL for the schema,
> >> and a clone would need to use its own search-path, not the original.
> > This is your major limitation.  You are required to create new objects
> > from code and cannot leverage any kind of copy of existing objects.
>
> But how to avoid that limitation?
>
> Triggers in a schema should functions correctly, whether or not client
> sessions set the search_path, or use fully qualified object names.
> I was actually surprised that functions from the schema itself (where the
> trigger is defined), do "not bind more tightly" to the dot (.) schema,
> the "owner" schema of the trigger, compared to functions elsewhere.
>
> Perhaps there's something I'm missing around trigger and name resolution?

But didn't any answer at the time.

But Tom's answer to Paul's question seems to be related to my original
question, no?

On Sun, Jan 23, 2022 at 4:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Paul van der Linden <paul.doskabouter@gmail.com> writes:
> > Thanks for the clarification, but giving up performance is a no-go for us.
> > Also I have my concerns about shemaqualifying each and every use of the ->
> > operator, there are really a lot of them in my functions and it would
> > severely impact readability. Are these the only 2 solutions possible?
>
> As of v14 you could use SQL-style function definitions, so that the
> operator is parsed at function definition time instead of runtime.

After re-reading
https://www.postgresql.org/docs/14/sql-createfunction.html in light of
Tom's answer,
does that mean that our `SET search_path TO {0}, 'pg_temp'`
workaround, in the trigger below,
to not depend on the search_path at runtime:
```
CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf()
 RETURNS trigger
 LANGUAGE plpgsql
 SET search_path TO owner_schema_of_fn_tg_tab, 'pg_temp'
AS $function$
BEGIN
 UPDATE AttributeInput
 SET AppCreateDate = NEW.CreateDate
 WHERE Guid = NEW.Guid;
 RETURN NEW;
END;
$function$
```
can be re-written as below?
```
CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf()
 RETURNS trigger
 LANGUAGE sql
BEGIN ATOMIC
 UPDATE AttributeInput
 SET AppCreateDate = NEW.CreateDate
 WHERE Guid = NEW.Guid;
 RETURN NEW;
END;
```
As long as owner_schema_of_fn_tg_tab is first in the search_path at
function-creation time?
Or does the v14-specific trick Tom mentioned is not available to
trigger-returning functions?
I'm kinda afraid that RETUR NEW; is specific to plpgsql...

I'm still on v12, so cannot test v14 yet. We planned to move to v14,
for lz4 and built-in uuid-creation function,
but if we could get rid of the `SET search_path` workaround in our
trigger functions, that would be even more motivation.

I'd really like my DDL statements to NOT contain schema-specific names,
to ensure proper name resolution independent of the search_path and
completely "self-contained" in the schema itself.
Is there any way to achieve that, beside our current `SET search_path`
workaround?

Thanks, --DD

On Mon, Jan 24, 2022 at 11:19 AM Ganesh Korde <ganeshakorde@gmail.com> wrote:
> On Mon, 24 Jan 2022, 3:22 pm Dominique Devienne, <ddevienne@gmail.com> wrote:
>> Is there any way to achieve that, beside our current `SET search_path` workaround?

> This might help.
> Alter user <user name> SET search_path TO myschema,public;
> No need to set search_path every time.

Hi. Not really, no, I'm afraid.

I'm in charge and control the app's schemas, not the LOGIN USERs using
those schemas.
I.e. my triggers shouldn't have to rely on the session's search_path
at all, and nor how that search_path is set.
Also, the schema(s) to access are dynamic, and some clients don't set
a search_path at all.
My triggers shouldn't stop working when there's no search_path (i.e.
only pg_catalog and pg_temp are implicitly resolved).

After re-reading
https://www.postgresql.org/docs/14/sql-createfunction.html in light of
Tom's answer,
does that mean that our `SET search_path TO {0}, 'pg_temp'`
workaround, in the trigger below,
to not depend on the search_path at runtime:

On Mon, Jan 24, 2022 at 3:48 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Monday, January 24, 2022, Dominique Devienne <ddevienne@gmail.com> wrote:
>>
>> After re-reading
>> https://www.postgresql.org/docs/14/sql-createfunction.html in light of
>> Tom's answer,
>> does that mean that our `SET search_path TO {0}, 'pg_temp'`
>> workaround, in the trigger below,
>> to not depend on the search_path at runtime:
>>
>
> The general trigger documentation is kind enough to point out that the sql language cannot be used to write trigger
functions.

OK, failr enough...

But what about:
> I'd really like my DDL statements to NOT contain schema-specific names,
> to ensure proper name resolution independent of the search_path and
> completely "self-contained" in the schema itself.
> Is there any way to achieve that, beside our current `SET search_path` workaround?

Could I please get a definitive answer about this?

David, in the "clone-schema" thread, you kind of implied I shouldn't
have a set search_path in the triggers,
thus making my DDL schema-specific, but then when I ask about ways to
avoid thus, and have "static" resolution
of names in those trigger functions, I'm not getting alternatives.

Am I the only one to think that a session w/o a seach_path, which
fully qualifies table names,
should behaves exactly the same way than another session that has a
search_path and does not fully qualify table names?
Because that's the only reason I added a set search_path to our
trigger functions. The alternative being to fully-qualify
all object references in those trigger functions, making the DDL even
more "schema-specific". it feels like a catch-22...

I'm not trying to be difficult here... I'm trying to understand, FWIW. --DD

PS: Does INVOKER vs DEFINER affect name resolution? Does that apply to
trigger functions?

On 1/24/22 08:27, Dominique Devienne wrote:
> On Mon, Jan 24, 2022 at 3:48 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> On Monday, January 24, 2022, Dominique Devienne <ddevienne@gmail.com> wrote:
>>>
>>> After re-reading
>>> https://www.postgresql.org/docs/14/sql-createfunction.html in light of
>>> Tom's answer,
>>> does that mean that our `SET search_path TO {0}, 'pg_temp'`
>>> workaround, in the trigger below,
>>> to not depend on the search_path at runtime:
>>>
>>
>> The general trigger documentation is kind enough to point out that the sql language cannot be used to write trigger
functions.
> 
> OK, failr enough...
> 
> But what about:
>> I'd really like my DDL statements to NOT contain schema-specific names,
>> to ensure proper name resolution independent of the search_path and
>> completely "self-contained" in the schema itself.
>> Is there any way to achieve that, beside our current `SET search_path` workaround?
> 
> Could I please get a definitive answer about this?
> 
> David, in the "clone-schema" thread, you kind of implied I shouldn't
> have a set search_path in the triggers,
> thus making my DDL schema-specific, but then when I ask about ways to

If I am following your code and objects are schema specific so there is 
no way to avoid this.

> avoid thus, and have "static" resolution
> of names in those trigger functions, I'm not getting alternatives.
> 
> Am I the only one to think that a session w/o a seach_path, which
> fully qualifies table names,
> should behaves exactly the same way than another session that has a
> search_path and does not fully qualify table names?

Objects end up being found in a schema, either you specify that schema 
when using the object or the server walks the search_path to find the 
first schema where an object with the name exists.


> Because that's the only reason I added a set search_path to our
> trigger functions. The alternative being to fully-qualify
> all object references in those trigger functions, making the DDL even
> more "schema-specific". it feels like a catch-22...
> 
> I'm not trying to be difficult here... I'm trying to understand, FWIW. --DD
> 
> PS: Does INVOKER vs DEFINER affect name resolution? Does that apply to
> trigger functions?
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com