RE: WIP: System Versioned Temporal Table
hi, i tested the temporal patch ( https://commitfest.postgresql.org/26/2316/ ) with the current 14devel applied ontop of ef3d461 without any conflicts. i build with no special options passed to ./configure and noticed, that the postgresql-client-13 from the debian repositories crashes with the \d command to reproduce the issue: CREATE TABLE test ( id int PRIMARY KEY generated ALWAYS AS IDENTITY, name text NOT NULL, start_timestamp timestamp with time zone GENERATED ALWAYS AS ROW START, end_timestamp timestamp with time zone GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (start_timestamp, end_timestamp) ); \d test it failes after outputting the table informations with this backtrace: free(): invalid pointer [1]587783 abort (core dumped) psql -X -U easteregg -h localhost postgres (gdb) bt 50 #0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50 #1 0x7f21a62e0537 in __GI_abort () at abort.c:79 #2 0x7f21a6339768 in __libc_message (action=action@entry=do_abort, fmt=fmt@entry=0x7f21a6447e31 "%s\n") at ../sysdeps/posix/libc_fatal.c:155 #3 0x7f21a6340a5a in malloc_printerr (str=str@entry=0x7f21a644605e "free(): invalid pointer") at malloc.c:5347 #4 0x7f21a6341c14 in _int_free (av=, p=, have_lock=0) at malloc.c:4173 #5 0x55c9fa47b602 in printTableCleanup (content=content@entry=0x7ffece7e41c0) at ./build/../src/fe_utils/print.c:3250 #6 0x55c9fa444aa3 in describeOneTableDetails (schemaname=, schemaname@entry=0x55c9fbebfee6 "public", relationname=, oid=oid@entry=0x55c9fbebfee0 "16436", verbose=verbose@entry=false) at ./build/../src/bin/psql/describe.c:3337 #7 0x55c9fa4490c9 in describeTableDetails (pattern=pattern@entry=0x55c9fbebf540 "abk", verbose=verbose@entry=false, showSystem=) at ./build/../src/bin/psql/describe.c:1421 #8 0x55c9fa4372ff in exec_command_d (scan_state=scan_state@entry=0x55c9fbebd130, active_branch=active_branch@entry=true, cmd=cmd@entry=0x55c9fbebf430 "d") at ./build/../src/bin/psql/command.c:722 #9 0x55c9fa43ae2b in exec_command (previous_buf=0x55c9fbebd3a0, query_buf=0x55c9fbebd270, cstack=0x55c9fbebd250, scan_state=0x55c9fbebd130, cmd=0x55c9fbebf430 "d") at ./build/../src/bin/psql/command.c:317 #10 HandleSlashCmds (scan_state=scan_state@entry=0x55c9fbebd130, cstack=cstack@entry=0x55c9fbebd250, query_buf=0x55c9fbebd270, previous_buf=0x55c9fbebd3a0) at ./build/../src/bin/psql/command.c:220 #11 0x55c9fa4539e0 in MainLoop (source=0x7f21a6479980 <_IO_2_1_stdin_>) at ./build/../src/bin/psql/mainloop.c:502 #12 0x55c9fa433d64 in main (argc=, argv=0x7ffece7e47f8) at ./build/../src/bin/psql/startup.c:441 the client is this version: apt-cache policy postgresql-client-13 postgresql-client-13: Installed: 13.1-1.pgdg+2+b3 Candidate: 13.1-1.pgdg+2+b3 Version table: *** 13.1-1.pgdg+2+b3 100 100 http://apt.postgresql.org/pub/repos/apt sid-pgdg-testing/main amd64 Packages 100 /var/lib/dpkg/status the the 14devel version from my build or a selfcompiled REL_13_STABLE client will not crash. i was wondering if this might pose a security concern. i am a bit out of my depths here, but would be glad to help, if any informations are missing with kind regards, richard
Re: plpgsql variable assignment not supporting distinct anymore
> Probably the fix is not hard, but it is almost the same situation as the > UNION case. The result of your code is not deterministic > > If there are more different ti_resource_id then some values can be randomly > ignored - when hash agg is used. > > The safe fix should be > > _resource_id := (SELECT ti_resource_id >FROM tabk.resource_timeline > WHERE ti_a2_id = _ab2_id > AND ti_type = 'task'); > > and you get an exception if some values are ignored. Or if you want to > ignore some values, then you can write > > _resource_id := (SELECT MIN(ti_resource_id) -- or MAX >FROM tabk.resource_timeline > WHERE ti_a2_id = _ab2_id > AND ti_type = 'task'); > > Using DISTINCT is not a good solution. > in my usecase it was perfectly fine, because there is a constraint ensuring that here can never be more than on ti_resource_id at any given time for a given _ab2_id. also, whenever there would be more data ( for example if the constraint trigger would have a bug ) you will get an error like this: create table a ( t int ); insert into a values (1),(2); do $$ declare _t int; begin _t := distinct t from a; end $$; Query failed: ERROR: query "SELECT distinct t from a" returned more than one row CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment no doubt, that this piece of code might not look optimal at first glance, but i like my code to fail fast. because with the min() approach, you will not notice, that the constraint trigger is not doing its job, until you get other strange sideeffects down the road. richard
Re: plpgsql variable assignment not supporting distinct anymore
the code provided is just a little poc to get the error ( which i have not included with my first mail sorry. ) ERROR: syntax error at or near "DISTINCT" LINE 8: _test := DISTINCT a FROM ( VALUES ( (true), ( true ) ) )... the code in production looked like this: _resource_id := DISTINCT ti_resource_id FROM tabk.resource_timeline WHERE ti_a2_id = _ab2_id AND ti_type = 'task' ; this is backed up by a trigger function, that will ensure to every instance with the same ti_a2_id exists only one ti_resource_id, hence the query can never fail due to more than one row beeing returned. but this syntax is not supported anymore, which will break BC. up until PG 13, the assignment statement was just an implizit SELECT Query. Since Tom Lane didn't mentioned this change in the other thread, i figured the devteam might not be aware of this chance. i can refactor this line into _resource_id := ti_resource_id FROM tabk.resource_timeline WHERE ti_a2_id = _ab2_id AND ti_type = 'task' GROUP BY ti_resource_id ; but concerns about BC was already raised, although with UNION there might be far less people affected. with kind regards, richard
plpgsql variable assignment not supporting distinct anymore
hi, no noticed after the assignment with union ( https://www.postgresql.org/message-id/flat/20210105201257.f0d76aff%40mail.verfriemelt.org ), that the assignment with distinct is broken aswell. DO $$ DECLARE _test bool; BEGIN _test := DISTINCT a FROM ( VALUES ( (true), ( true ) ) )t(a); END $$; i would argue, that thats a way more common usecase than the union, which was merely bad code. tested with version 14~~devel~20210111.0540-1~299.gitce6a71f.pgdg110+1 from the apt repo with kind redards, richard
Re: plpgsql variable assignment with union is broken
to be clear, that was existing code within our codebase, it was used as a very bad alternative to an if/else clause. think of it as an tenery operator. thats just straight up bad code as tom said. but even if it is, its a change and i wanted to check, if its intended. so thank you for you time and consideration!
plpgsql variable assignment with union is broken
hi, i updated our ci pipeline to the latest 14-devel build from the postgresql apt repository: PostgreSQL 14devel (Debian 14~~devel~20210105.1140-1~285.gitbc43b7c.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-3) 10.2.1 20201224, 64-bit i found, that the behaviour of variable assignment in combination with union is not working anymore: DO $$ DECLARE t bool; begin t := a FROM ( SELECT true WHERE false ) t(a) UNION SELECT true AS a; END $$; before it worked with pg13 and 14-devel with this build: PostgreSQL 14devel (Debian 14~~devel~20201126.0540-1~210.gitf3a8f73.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.0-16) 10.2.0, 64-bit is this an intended change or is it a bug? with kind regards, richard
