Version 16.x search_path behavior change?

Started by Dennisover 2 years ago2 messagesgeneral
Jump to latest
#1Dennis
dennis@iletsel.nl

Hi,

Predating PostgreSQL's json functions, I had been using custom json
functions, which by now have been reduced to wrappers around the native
type, but still using their original signatures so as to not have to
change hundreds of stored procedures. One of these is unfortunately
called "json_object" which is also available as "pg_catalog.json_object".

This was managed, as in working up until version 15, by having these
json functions in a schema with higher search_path precedence.

The 16.x documentation still says the following:

----
However, you can explicitly place |pg_catalog| at the end of your search
path if you prefer to have user-defined names override built-in names.
----

This worked fine in PostgreSQL 15.5:

---
create schema myschema;
create function myschema.json_object(v text) returns json as $$
declare
begin
    return to_json(v);
end;
$$ language plpgsql stable;

set search_path = myschema, pg_catalog;
select json_object('hello');

 json_object
-------------
 "hello"
(1 row)

\df json_object
                            List of functions
   Schema   |    Name     | Result data type | Argument data types | Type
------------+-------------+------------------+---------------------+------
 myschema   | json_object | json             | v text | func
 pg_catalog | json_object | json             | text[] | func
 pg_catalog | json_object | json             | text[], text[] | func
---

But no longer in PostgreSQL 16.1:
---
create schema myschema;
create function myschema.json_object(v text) returns json as $$
declare
begin
    return to_json(v);
end;
$$ language plpgsql stable;

set search_path = myschema, pg_catalog;
select json_object('hello');

ERROR:  malformed array literal: "hello"
LINE 1: select json_object('hello');
                           ^
DETAIL:  Array value must start with "{" or dimension information.

# select myschema.json_object('hello');
 json_object
-------------
 "hello"

# \df json_object
                            List of functions
   Schema   |    Name     | Result data type | Argument data types | Type
------------+-------------+------------------+---------------------+------
 myschema   | json_object | json             | v text | func
 pg_catalog | json_object | json             | text[] | func
 pg_catalog | json_object | json             | text[], text[] | func

---

The most relevant changelog updates I could find mention:
"""
Add SQL/JSON constructors (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov,
Alexander Korotkov, Amit Langote)

The new functions JSON_ARRAY(), JSON_ARRAYAGG(), JSON_OBJECT(), and
JSON_OBJECTAGG() are part of the SQL standard.

"""

but I am not sure what this means in this case, as
pg_catalog.json_object(..) also existed earlier, where the above
search_path selectivity used to work.

Is this intentional? If so, is there a recommendation as to how to work
around this?

Thanks for any suggestions,

Dennis

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Dennis (#1)
Re: Version 16.x search_path behavior change?

Dennis <dennis@iletsel.nl> writes:

The 16.x documentation still says the following:

However, you can explicitly place |pg_catalog| at the end of your search
path if you prefer to have user-defined names override built-in names.

It does work that way, for ordinary names. JSON_OBJECT is special
because it has special calling syntax (thank you, SQL committee)
and the grammar productions for that always resolve it as the
built-in function. You could override things by writing explicitly
"myschema.json_object(...)" but I imagine you don't want to.

I'm not sure if this should be considered a bug or not. It looks
like we've gone out of our way to avoid capturing some other
comparable cases such as OVERLAY and SUBSTRING, if they aren't
actually written with special syntax. Don't know offhand if it's
practical to handle JSON_OBJECT similarly, or whether it'd really
make sense to have an exception for that.

regards, tom lane