Version 16.x search_path behavior change?

From: Dennis <dennis(at)iletsel(dot)nl>
To: pgsql-general(at)postgresql(dot)org
Subject: Version 16.x search_path behavior change?
Date: 2023-12-23 14:52:45
Message-ID: f2b90664-32e8-4e2a-906b-099fe8692739@iletsel.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wilma Wantren 2023-12-23 16:12:38 Re: Re: Changing a schema's name with function1 calling function2
Previous Message Johnathan Tiamoh 2023-12-23 06:48:57 Unable to start postgresql-14