Re: Changing a schema's name with function1 calling function2
On 2023-12-24 14:27:19 -0800, Adrian Klaver wrote: > On 12/24/23 13:43, Peter J. Holzer 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." I interpreted that as meaning what I wrote above. So we'll have to wait for Wilma to clarify what she really meant. > 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. "Determining the appropriate schema" is what - AIUI - the requested magic variable is for. > > 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. Yes, I was sloppy there. What I actually meant was "the first schema where the user actually has permission to create a function" (and no doubt that isn't 100% correct either). I would expect that in a deployment situation that would be the first schema in the search_path, but you are of course correct that this is not necessarily the case. Anyway, that was only illustrative. The point of my examples was that no matter how the function is created, __function_schema__ always refers to the schema the function actually is in. 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: Re: Changing a schema's name with function1 calling function2
Great that you have discussed this further! And sorry that what I wrote is apparently still misleading. Peter understood me correctly, "__function_schema__ always refers to the schema the function actually is in". I define: 1. create function my_schema.function1... 2. alter function function1 set search_path to __function_schema__ The variable __function_schema__ is not evaluated, but is set "as it is" in the metadata of the function, which therefore reads: ... search_path: __function_schema__ ... Only when function1 is executed the variable is evaluated, namely "to the schema the function actually is in", in this case to 'my_schema'. If the schema is renamed, the search_path of function1 does not have to be changed, and is nevertheless evaluated correctly again when the function is executed, now to the new schema name. --- Ursprüngliche Nachricht --- Von: "Peter J. Holzer" Datum: 25.12.2023 15:37:26 An: Adrian Klaver Betreff: Re: Changing a schema's name with function1 calling function2 On 2023-12-24 14:27:19 -0800, Adrian Klaver wrote: > On 12/24/23 13:43, Peter J. Holzer 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." I interpreted that as meaning what I wrote above. So we'll have to wait for Wilma to clarify what she really meant. > 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. "Determining the appropriate schema" is what - AIUI - the requested magic variable is for. > > 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. Yes, I was sloppy there. What I actually meant was "the first schema where the user actually has permission to create a function" (and no doubt that isn't 100% correct either). I would expect that in a deployment situation that would be the first schema in the search_path, but you are of course correct that this is not necessarily the case. Anyway, that was only illustrative. The point of my examples was that no matter how the function is created, __function_schema__ always refers to the schema the function actually is in. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.eclipso.de
Re: Changing a schema's name with function1 calling function2
On 12/25/23 09:29, Wilma Wantren wrote:
Great that you have discussed this further! And sorry that what I wrote is
apparently still misleading.
Peter understood me correctly, "__function_schema__ always refers to the schema the
function actually is in".
I define:
1. create function my_schema.function1...
2. alter function function1 set search_path to __function_schema__
The variable __function_schema__ is not evaluated, but is set "as it is" in the
metadata of the function, which therefore reads:
...
search_path: __function_schema__
...
Only when function1 is executed the variable is evaluated, namely "to the schema the
function actually is in", in this case to 'my_schema'. If the schema is renamed, the
search_path of function1 does not have to be changed, and is nevertheless evaluated
correctly again when the function is executed, now to the new schema name.
Alright I see now.
A simple example of a partial solution:
CREATE OR REPLACE FUNCTION public.schema_test()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
fnc_name varchar := 'schema_test';
sch_name varchar;
cur_sch name;
BEGIN
SELECT INTO sch_name pronamespace::regnamespace from pg_proc where
proname = fnc_name;
RAISE NOTICE 'Schema is %', sch_name;
PERFORM set_config('search_path', sch_name, true);
select into cur_sch current_schema;
RAISE NOTICE 'search_path = %', cur_sch;
END;
Issues:
fnc_name would need to kept updated.
Overloaded function names would need to be accounted for.
Functions with same name in different schemas would need to be dealt with.
At this point I still the solution as being external to the database. In
other words making the change before the objects are loaded into a database.
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Changing a schema's name with function1 calling function2
> On Dec 25, 2023, at 10:44, Adrian Klaver wrote: > Functions with same name in different schemas would need to be dealt with. I think that's the primary use-case (at least, it would be for me), and I don't see a convenient way of doing that. Even a "get OID of current function" function would be useful here.
Query crash with 15.5 on debian bookworm/armv8
Hi, I've just updated my raspberry pi 3 from postgresql-13.3 on bullseye/armv6 to postgresq-15.5 on debian-bookworm/armv8. However after the upgrade, I experience reproducable crashes quering the following table: CREATE TABLE public.smartmeter ( leistungsfaktor real, momentanleistung integer, spannungl1 real, spannungl2 real, spannungl3 real, stroml1 real, stroml2 real, stroml3 real, wirkenergien real, wirkenergiep real, ts timestamp with time zone NOT NULL ); CREATE INDEX smartmeter_ts_idx ON public.smartmeter USING brin (ts); with the following query: SELECT floor(extract(epoch from ts)/60)*60 AS "time", AVG(spannungL1) as l1, AVG(spannungL2) as l2, AVG(spannungL3) as l3 FROM smartmeter WHERE ts BETWEEN '2023-12-01T13:01:30.514Z' AND '2023-12-25T19:01:30.514Z' GROUP BY time order by time; any ideas how to diagnose the issue further? is this a known problem? Thanks & best regards, Clemens Using host libthread_db library "/lib/aarch64-linux-gnu/libthread_db.so.1". 0x007ff6eb7fe0 in __GI_epoll_pwait (epfd=4, events=0xea2d20, maxevents=1, timeout=timeout@entry=-1, set=set@entry=0x0) at ../sysdeps/unix/sysv/linux/epoll_pwait.c:40 40 ../sysdeps/unix/sysv/linux/epoll_pwait.c: No such file or directory. (gdb) c Continuing. Program received signal SIGUSR1, User defined signal 1. 0x007ff6ea7f58 in __libc_pread64 (fd=25, buf=buf@entry=0x7feb754880, count=count@entry=8192, offset=offset@entry=16384) at ../sysdeps/unix/sysv/linux/pread64.c:25 25 ../sysdeps/unix/sysv/linux/pread64.c: No such file or directory. (gdb) c Continuing. Program received signal SIGSEGV, Segmentation fault. 0x007fe5e6a9f0 in ?? () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 (gdb) bt full #0 0x007fe5e6a9f0 in ?? () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #1 0x007fe59bb49c in llvm::raw_ostream::write(char const*, unsigned long) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #2 0x007fe6d71048 in llvm::MCContext::createTempSymbol(llvm::Twine const&, bool) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #3 0x007fe6d713f0 in llvm::MCContext::createTempSymbol() () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #4 0x007fe6d95c6c in llvm::MCObjectStreamer::emitCFIEndProcImpl(llvm::MCDwarfFrameInfo&) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #5 0x007fe619f4c0 in ?? () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #6 0x007fe6180b6c in llvm::AsmPrinter::emitFunctionBody() () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #7 0x007fe72a4ba4 in ?? () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #8 0x007fe5d3122c in llvm::MachineFunctionPass::runOnFunction(llvm::Function&) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #9 0x007fe5b14390 in llvm::FPPassManager::runOnFunction(llvm::Function&) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #10 0x007fe5b1af70 in llvm::FPPassManager::runOnModule(llvm::Module&) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #11 0x007fe5b14d98 in llvm::legacy::PassManagerImpl::run(llvm::Module&) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #12 0x007fe7187d70 in llvm::orc::SimpleCompiler::operator()(llvm::Module&) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #13 0x007fe71dc138 in ?? () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #14 0x007fe71dbf44 in llvm::orc::IRCompileLayer::emit(std::unique_ptr >, llvm::orc::ThreadSafeModule) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #15 0x007fe71dc634 in llvm::orc::IRTransformLayer::emit(std::unique_ptr >, llvm::orc::ThreadSafeModule) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #16 0x007fe71dc634 in llvm::orc::IRTransformLayer::emit(std::unique_ptr >, llvm::orc::ThreadSafeModule) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #17 0x007fe71e2648 in llvm::orc::BasicIRLayerMaterializationUnit::materialize(std::unique_ptr >) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #18 0x007fe7199c18 in llvm::orc::MaterializationTask::run() () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #19 0x007fe71a4ea0 in ?? () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #20 0x007fe719bad0 in llvm::orc::ExecutionSession::dispatchOutstandingMUs() () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 No symbol table info available. #21 0x007fe719ea84 in llvm::orc::ExecutionSession::OL_completeLookup(std::unique_ptr >, std::s
Re: Query crash with 15.5 on debian bookworm/armv8
On 12/25/23 13:01, Clemens Eisserer wrote: Hi, I've just updated my raspberry pi 3 from postgresql-13.3 on bullseye/armv6 to postgresq-15.5 on debian-bookworm/armv8. How did you upgrade? -- Adrian Klaver adrian.kla...@aklaver.com
Re: Query crash with 15.5 on debian bookworm/armv8
Hi Adrian, > How did you upgrade? A fresh install based on "Raspberry Pi OS Lite" image provided (based on debian bookworm) with pgdump_all & plsql -f.
Re: Query crash with 15.5 on debian bookworm/armv8
On 12/25/23 13:51, Clemens Eisserer wrote: Hi Adrian, How did you upgrade? A fresh install based on "Raspberry Pi OS Lite" image provided (based Does that install Postgres as part of the image or did you get it from somewhere else? on debian bookworm) with pgdump_all & plsql -f. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Query crash with 15.5 on debian bookworm/armv8
> Does that install Postgres as part of the image or did you get it from > somewhere else? I installed it via "apt-get install postgresql" and it downloaded postgresql-15_15.5-0+deb12u1_arm64.deb - which seems to be the current package shipped with debian bookworm for arm64: https://packages.debian.org/bookworm/arm64/postgresql-15/download best regards, Clemens
Re: Query crash with 15.5 on debian bookworm/armv8
On 12/25/23 13:51, Clemens Eisserer wrote: Hi Adrian, How did you upgrade? A fresh install based on "Raspberry Pi OS Lite" image provided (based on debian bookworm) with pgdump_all & plsql -f. Did you install the 32 or 64 bit version from here?: https://www.raspberrypi.com/software/operating-systems/ -- Adrian Klaver adrian.kla...@aklaver.com
Re: Query crash with 15.5 on debian bookworm/armv8
Clemens Eisserer writes: > Program received signal SIGSEGV, Segmentation fault. > 0x007fe5e6a9f0 in ?? () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 > (gdb) bt full > #0 0x007fe5e6a9f0 in ?? () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 > No symbol table info available. > #1 0x007fe59bb49c in llvm::raw_ostream::write(char const*, > unsigned long) () from /lib/aarch64-linux-gnu/libLLVM-14.so.1 > No symbol table info available. FWIW, since this crash is inside LLVM you could presumably dodge the bug by setting "jit" to off. As for an actual fix, perhaps a newer version of LLVM is needed? I don't see a problem testing this query on my RPI with Ubuntu 23.10 (LLVM 16). regards, tom lane
