Обсуждение: Version 16.x search_path behavior change?

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

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