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

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?

Paul

On Thu, Jan 20, 2022 at 3:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

On Sun, Jan 23, 2022 at 7:54 AM Paul van der Linden <
paul.doskabouter@gmail.com> wrote:

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?

At present, yes. The system tooling enforces a nearly search_path-less
execution environment (you basically only get pg_catalog and pg_temp). The
only other possible solution is to somehow get the extension installed into
pg_catalog.

This is basically a security trade-off since the goal is to avoid having
the insecure public schema in the search_path. I'm sure that if we tried
we could come up with and implement one or more ideas to make situations
like this less painful (e.g., allow a DBA to mark a schema as privileged
and then it gets added alongside the pg_catalog schema). Some options may
not be as simple as adding a new command line option to pg_dump/pg_restore
to enforce a custom search_path, even one that includes public, thus giving
some measure of control to the DBA. We still haven't done that (though I
suppose if we solved this problem in a more systematic way the need for
such a pg_dump option very well might go away, it's basically the same
problem).

David J.

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

Thanks, works perfectly!

On Sun, Jan 23, 2022 at 4:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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, 24 Jan 2022, 3:22 pm Dominique Devienne, <ddevienne@gmail.com>
wrote:

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

This might help.

Alter user <user name> SET search_path TO myschema,public;

No need to set search_path every time.

Regards,
Ganesh Korde.

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).

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.

David J.

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

On Mon, Jan 24, 2022 at 9:25 AM Dominique Devienne <ddevienne@gmail.com>
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?

There is no way to achieve that.

Objects do not know what schema they are in; and the search_path that is
present when the database initiates the SQL transaction itself (e.g.,
pg_dump, create index) provides only pg_catalog.

In short, search_path is a convenience for interactive users and external
applications. Never rely upon it including specific schemas - or any
schema at all (besides pg_catalog). Only if you set the search_path
yourself can the code that you write depend upon the search_path.

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.

I rambled a bit in that email...but I did say "Sometimes limitations cannot
be avoided...". I then proceeded to show you an alternative...way of
thinking of the problem that would work with this limitation.

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?

All sessions have a search_path...but if you schema-qualify everything
(except what is in pg_catalog) that doesn't really matter. But if the
schema-qualified name is "my_custom_schema" then, no, omitting the
schema-qualification and relying on search_path means you will get the
identical result when my_custom_schema is in the search_path but will get
an error when it is not. Both alternatives still include a search_path.

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...

Attaching a set search_path and schema-qualifying all object references are
equally "schema-specific". It isn't a catch-22, its two options for
interacting with a system that is designed to be schema-specific.

That is a lot of theory and generalities but I hope it helps clear things
up.

David J.

PS: Does INVOKER vs DEFINER affect name resolution?

Permissions and search_path resolution are independent.