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
Re: Version 16.x search_path behavior change?
Dennis 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
Re: Unable to start postgresql-14
Was this backup taken before the data directory was deleted? Yes. I restored it from a 1-day old backup, but unfortunately, I couldn't apply the logs. If so how was the backup done? It was taken with a customized script that uses pg_dump. Does this mean you recreated the data directory from some source and then ran pg_resetwal? I recreated it from the backup Do you mean you did a pg_dumpall from the Postgres instance running on the restored data directory? Yes. And it failed On Sat, Dec 23, 2023 at 5:35 PM Adrian Klaver wrote: > On 12/23/23 10:05, Johnathan Tiamoh wrote: > > More information needed: > > > > 1) Exact Postgres version e.g 14.x > > > > Postgresql Version 14. 10 > > > > 2) Is it community version or fork? > > > > Community version > > > > 3) What was the command you used to start the cluster? > > > > systemctl start postgresql-14 > > > > 4) Do you have replication/WAL archiving set up? > > > > Yes. > > > > > > Just to add. The data directory was accidentally deleted. At the time of > > deletion. The secondary was broken. > > So that is the cause of the issue. > > > I restore from backup and it couldn't start. > > Was this backup taken before the data directory was deleted? > > If so how was the backup done? > > > > > I ended up doing a pg_resetwal to start it and ended up with lots of > > data corruption. > > Does this mean you recreated the data directory from some source and > then ran pg_resetwal? > > > > > I have try a pg_dumpall to restore in a new server and it has equally > > failed. > > Do you mean you did a pg_dumpall from the Postgres instance running on > the restored data directory? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Unable to start postgresql-14
On 12/24/23 08:03, Johnathan Tiamoh wrote: Was this backup taken before the data directory was deleted? Yes. I restored it from a 1-day old backup, but unfortunately, I couldn't apply the logs. If the backup was done using pg_dump as mentioned below why where you applying logs? You need to provide more information about what the customized script does and what logs you are using? If so how was the backup done? It was taken with a customized script that uses pg_dump. Does this mean you recreated the data directory from some source and then ran pg_resetwal? I recreated it from the backup Do you mean you did a pg_dumpall from the Postgres instance running on the restored data directory? Yes. And it failed -- Adrian Klaver adrian.kla...@aklaver.com
Re: Unable to start postgresql-14
On Sun, Dec 24, 2023 at 11:04 AM Johnathan Tiamoh wrote: > > If so how was the backup done? > It was taken with a customized script that uses pg_dump. > That's your problem: pg_dump is a logical backup. All the WAL records are now completely invalid. If you want PITR, read https://www.postgresql.org/docs/current/continuous-archiving.html *and then use a dedicated PITR program*.like (alphabetically) barman or pgbackrest. I like pgbackrest. It's always worked for me, and the developers respond quickly to issues raised on github. Stephen also monitors this list.
Re: Changing a schema's name with function1 calling function2
On 2023-12-23 08:31:39 -0800, Adrian Klaver wrote: > On 12/23/23 08:12, Wilma Wantren wrote: > > I had already feared that such a variable does not exist (because I > > had not found it). I think that's a pity, because I suspect that in > > at least 90% of the cases where a function needs a search_path, this > > variable would be the value of the search_path, so that in 90% of > > the cases no change to the search_path would be necessary after > > renaming the schema. > > I would say the issue is with this from your previous post: > > > "Therefore it would be great if there was a variable which I could set in a > search_path (like the variable "$user") which denotes the function's schema > and which is only evaluated when the function is executed, i.e. the variable > would be the value of the function's search_path in the function's meta > data." > > search_path can be set(just off the top of head): > > postgresql.conf and it's include files > ALTER DATABASE > ALTER FUNCTION > ALTER ROLE > ALTER SYSTEM > At any point by a user/script/function/etc. > > Even if such a variable existed you be chasing down the 'correct' version of > search_path that had it. Basically where you are now. I think you misunderstood Wilma. What she is asking for is a "keyword" or "magic variable" (or whatever you want to call it) which you can specify in CREATE|ALTER FUNCTION ... SET SEARCH_PATH = ..., which refers to the schema the function is (being) created in. So if you create the function with create function foo (...) set search_path to __function_schema__ $$ ... $$ (using the "dunder" convention (from some other programming languages) to denote the magic variable/keyword) the search path would be set to whatever schema was first in the search_path when the function was created. If you create it with create function my_schema.foo (...) set search_path to __function_schema__ $$ ... $$ it would be set to "my_schema". And if you changed the schema with alter function foo set schema my_new_schema it would be changed to "my_new_schema". Personally I think that effect should be easy enough to create in your deployment or migration scripts but of course that assumes that you have such scripts. If you are doing your deployments manually (especially by cloning a template as described by Wilma) I can see how that feature would make things easier and/or reduce the risk of errors. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Changing a schema's name with function1 calling function2
On 12/24/23 13:43, Peter J. Holzer wrote: On 2023-12-23 08:31:39 -0800, Adrian Klaver wrote: I think you misunderstood Wilma. What she is asking for is a "keyword" or "magic variable" (or whatever you want to call it) which you can specify in CREATE|ALTER FUNCTION ... SET SEARCH_PATH = ..., which refers to the schema the function is (being) created in. Maybe, but I don't think so. From one of Wilma's previous post: "Therefore it would be great if there was a variable which I could set in a search_path (like the variable "$user") which denotes the function's schema and which is only evaluated when the function is executed, i.e. the variable would be the value of the function's search_path in the function's meta data. This variable, e.g. "$function_schema" would still denote the correct schema after renaming the schema." It seems to be pretty tied to search_path. Now there is, from: https://www.postgresql.org/docs/current/catalog-pg-proc.html proconfig text[] Function's local settings for run-time configuration variables Though that is part of the function definition not the code inside the function as you show below. Also you would still need to determine what was the appropriate schema before creating/altering the setting for the function. There is also: pronamespace oid (references pg_namespace.oid) The OID of the namespace that contains this function which could be construed as '... the function meta data.', though its not strictly search_path. I could see that being part of some dynamic SQL to set the search_path. So if you create the function with create function foo (...) set search_path to __function_schema__ $$ ... $$ (using the "dunder" convention (from some other programming languages) to denote the magic variable/keyword) the search path would be set to whatever schema was first in the search_path when the function was created. That assumes the first schema is the correct one. Not something that could be counted on given search_path can be set any number of ways at any time. If you create it with create function my_schema.foo (...) set search_path to __function_schema__ $$ ... $$ it would be set to "my_schema". And if you changed the schema with alter function foo set schema my_new_schema it would be changed to "my_new_schema". Personally I think that effect should be easy enough to create in your deployment or migration scripts but of course that assumes that you have such scripts. If you are doing your deployments manually (especially by cloning a template as described by Wilma) I can see how that feature would make things easier and/or reduce the risk of errors. hp -- Adrian Klaver adrian.kla...@aklaver.com
