Re: Logging of matching pg_hba.conf entry during auth skips trust auth, potential security issue
On Mon, Aug 21, 2023 at 04:44:33PM -0700, Jacob Champion wrote: > On Mon, Aug 21, 2023 at 4:22 PM Michael Paquier wrote: >> There are additionally two more comments in the SSL tests that could >> be removed, I guess. Here's a v4, with Robert's latest suggestion >> added. > > LGTM. Okay. Hearing nothing else, I have gone ahead and applied v4. -- Michael signature.asc Description: PGP signature
Re: PSQL error: total cell count of XXX exceeded
Thank you David. >From the code logic, I don't think this check is meant to check the limit: If it enters the double-loop (cont.nrows * cont.ncolumns) in printQuery(), the check should be always false (except overflow happened). So, if want to check the limit, we could have done this check before the double-loop: just checking PGresult and reports error earlier. > I wouldn’t be adverse to an improved error message, and possibly documenting > said limit. Agreed with you, current error message may even report a negative value, it's very confusing for user. It's better to introduce a limit here. Or using a bigger integer type (e.g. long) for them, but it's also have the theoretical upbound. Thanks. From: David G. Johnston Sent: Saturday, August 26, 2023 12:09 To: Hongxu Ma Cc: PostgreSQL Hackers Subject: Re: PSQL error: total cell count of XXX exceeded On Friday, August 25, 2023, Hongxu Ma mailto:inte...@outlook.com>> wrote: When I tried to select a big amount of rows, psql complains a error "Cannot add cell to table content: total cell count of 905032704 exceeded." We should use long for ncolumns and nrows and give a more obvious error message here. Any thoughts? or some other hidden reasons? 9 millions cells seems more than realistic a limit for a psql query result output. In any case it isn’t a bug, the code demonstrates that fact by producing an explicit error. I wouldn’t be adverse to an improved error message, and possibly documenting said limit. David J.
Re: RFC: Logging plan of the running query
On Fri, Aug 25, 2023 at 7:43 AM James Coleman wrote: > > On Thu, Aug 17, 2023 at 10:02 AM torikoshia > wrote: > > > > On 2023-06-16 01:34, James Coleman wrote: > > > Attached is v28 > > > which sets ProcessLogQueryPlanInterruptActive to false in errfinish > > > when necessary. Once built with those two patches I'm simply running > > > `make check`. > > > > With v28-0001 and v28-0002 patch, I confirmed backend processes consume > > huge > > amount of memory and under some environments they were terminated by OOM > > killer. > > > > This was because memory was allocated from existing memory contexts and > > they > > were not freed after ProcessLogQueryPlanInterrupt(). > > Updated the patch to use dedicated memory context for > > ProcessLogQueryPlanInterrupt(). > > > > Applying attached patch and v28-0002 patch, `make check` successfully > > completed after 20min and 50GB of logs on my environment. > > > > >>> On 2023-06-15 01:48, James Coleman wrote: > > >>> > The tests have been running since last night, but have been apparently > > >>> > hung now for many hours. > > > > I don't know if this has anything to do with the hung you faced, but I > > thought > > it might be possible that the large amount of memory usage resulted in > > swapping, which caused a significant delay in processing. > > Ah, yes, I think that could be a possible explanation. I was delaying > on this thread because I wasn't comfortable with having caused an > issue once (even if I couldn't easily reproduce) without at least some > theory as to the cause (and a fix). > > > If possible, I would be very grateful if you could try to reproduce this > > with > > the v29 patch. > > I'll kick off some testing. > I don't have time to investigate what's happening here, but 24 hours later the first "make check" is still running, and at first glance it seems to have the same behavior I'd seen that first time. The test output is to this point: # parallel group (5 tests): index_including create_view index_including_gist create_index create_index_spgist ok 66+ create_index26365 ms ok 67+ create_index_spgist 27675 ms ok 68+ create_view 1235 ms ok 69+ index_including 1102 ms ok 70+ index_including_gist 1633 ms # parallel group (16 tests): create_aggregate create_cast errors roleattributes drop_if_exists hash_func typed_table create_am infinite_recurse and it hasn't progressed past that point since at least ~16 hours ago (the first several hours of the run I wasn't monitoring it). I haven't connected up gdb yet, and won't be able to until maybe tomorrow, but here's the ps output for postgres processes that are running: admin3213249 0.0 0.0 196824 20552 ?Ss Aug25 0:00 /home/admin/postgresql-test/bin/postgres -D /home/admin/postgresql-test-data admin3213250 0.0 0.0 196964 7284 ?Ss Aug25 0:00 postgres: checkpointer admin3213251 0.0 0.0 196956 4276 ?Ss Aug25 0:00 postgres: background writer admin3213253 0.0 0.0 196956 8600 ?Ss Aug25 0:00 postgres: walwriter admin3213254 0.0 0.0 198424 7316 ?Ss Aug25 0:00 postgres: autovacuum launcher admin3213255 0.0 0.0 198412 5488 ?Ss Aug25 0:00 postgres: logical replication launcher admin3237967 0.0 0.0 2484 516 pts/4S+ Aug25 0:00 /bin/sh -c echo "# +++ regress check in src/test/regress +++" && PATH="/home/admin/postgres/tmp_install/home/admin/postgresql-test/bin:/home/admin/postgres/src/test/regress:$PATH" LD_LIBRARY_PATH="/home/admin/postgres/tmp_install/home/admin/postgresql-test/lib" INITDB_TEMPLATE='/home/admin/postgres'/tmp_install/initdb-template ../../../src/test/regress/pg_regress --temp-instance=./tmp_check --inputdir=. --bindir= --dlpath=. --max-concurrent-tests=20 --schedule=./parallel_schedule admin3237973 0.0 0.0 197880 20688 pts/4S+ Aug25 0:00 postgres -D /home/admin/postgres/src/test/regress/tmp_check/data -F -c listen_addresses= -k /tmp/pg_regress-7mmGUa admin3237976 0.0 0.1 198332 44608 ?Ss Aug25 0:00 postgres: checkpointer admin3237977 0.0 0.0 198032 4640 ?Ss Aug25 0:00 postgres: background writer admin3237979 0.0 0.0 197880 8580 ?Ss Aug25 0:00 postgres: walwriter admin3237980 0.0 0.0 199484 7608 ?Ss Aug25 0:00 postgres: autovacuum launcher admin3237981 0.0 0.0 199460 5488 ?Ss Aug25 0:00 postgres: logical replication launcher admin3243644 0.0 0.2 252400 74656 ?Ss Aug25 0:01 postgres: admin regression [local] SELECT waiting admin3243645 0.0 0.1 205480 33992 ?Ss Aug25 0:00 postgres: admin regression [local] SELECT waiting admin3243654 99.9 0.0 203156 31504 ?Rs Aug25 1437:49 postgres: admin regression [local] VACUUM admin3243655 0.0 0.1 212036 3
Re: broken master regress tests
Hi
pá 25. 8. 2023 v 9:12 odesílatel Pavel Stehule
napsal:
>
>
> pá 25. 8. 2023 v 8:22 odesílatel Matthias van de Meent <
> boekewurm+postg...@gmail.com> napsal:
>
>>
>>
>> On Fri, 25 Aug 2023, 05:54 Pavel Stehule,
>> wrote:
>>
>>> Hi
>>>
>>> today build is broken on my Fedora 39
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>> make[2]: Opouští se adresář
>>> „/home/pavel/src/postgresql.master/src/bin/initdb“
>>> make -C pg_amcheck check
>>> make[2]: Vstupuje se do adresáře
>>> „/home/pavel/src/postgresql.master/src/bin/pg_amcheck“
>>> [...]
>>> # ERROR: could not open file "base/16736/16781": Adresář nebo
>>> soubor neexistuje
>>> # '
>>> # doesn't match '(?^:could not open file ".*": No such file or
>>> directory)'
>>>
>>
>> It looks like the error message matcher doesn't account for the localized
>> version of "No such file or directory", might that be the issue?
>>
>
> yes
>
> LANG=C maje check-world
>
>
>
I tried to fix this issue, but there is some strange
regress tests are initialized with
<-->delete $ENV{LANGUAGE};
<-->delete $ENV{LC_ALL};
<-->$ENV{LC_MESSAGES} = 'C';
so the environment should be correct
I checked this setting before
<-->IPC::Run::run($cmd, '>', \$stdout, '2>', \$stderr);
and it looks correct. But the tests fails
Only when I use `LC_MESSAGES=C make check` the tests are ok
My environment has only `LANG=cs_CZ.UTF-8`
So it looks so IPC::Run::run is ignore parent environment
>
> Regards
>
> Pavel
>
>>
>> Kind regards,
>>
>> Matthias van de Meent
>> Neon (https://neon.tech)
>>
>
Fwd: Invitation to Test and Contribute to Pgperffarm
-- Forwarded message - From: Anil Date: Sat, Aug 26, 2023 at 9:37 PM Subject: Invitation to Test and Contribute to Pgperffarm To: Hello Pgperffarm Community, I'm Anil, a GSoC'23 contributor to Pgperffarm. I'm excited to share our progress as the program approaches its culmination. You can view the latest benchmark results at http://140.211.168.145/ Now, I'm inviting you to be a part of our effort. Could you test and run the benchmark on your system? Let me know, and I'll give you a unique machine ID for seamless contributions. Access the repository at https://github.com/PGPerfFarm/pgperffarm I want you to know that your involvement matters greatly. Please feel free to reach out if you have any questions or to get started. If you encounter any bug, please report it. Best, Anil GSoC'23 Contributor, Pgperffarm
Re: Format list of catalog files in makefile vertically
On 2023-Aug-25, Peter Eisentraut wrote: > I propose to reformat the catalog lists in src/backend/catalog/Makefile to > be more vertical, one per line. This makes it easier to keep that list in > sync with src/include/catalog/meson.build, and visually compare both lists. > Also, it's easier to read and edit in general. +1 > In passing, I'd also copy over some relevant comments from the makefile to > meson.build. For the hypothetical future when we delete the makefiles, > these comments seem worth keeping. (For fun, I tested whether the comments > are still true, and yes, the order still matters.) Sure. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "La experiencia nos dice que el hombre peló millones de veces las patatas, pero era forzoso admitir la posibilidad de que en un caso entre millones, las patatas pelarían al hombre" (Ijon Tichy)
Re: [PATCH] Add XMLText function (SQL/XML X038)
On 2023-Aug-25, Chapman Flack wrote: > On 2023-08-25 10:49, Vik Fearing wrote: > > I do not think this should be addressed in this patch because > > there are quite a lot of functions that need to handle this. > > Indeed, as described in [0], we still largely provide the SQL/XML:2003 > notion of a single XML datatype, not the distinguishable XML(DOCUMENT), > XML(CONTENT), XML(SEQUENCE) types from :2006 and later, which has a > number of adverse consequences for developers[1], and that wiki page > proposed a couple possible ways forward[2]. Sadly, all the projects seem to have been pretty much abandoned in the meantime. Zorba has been dead for 9 years, xqilla for 6. Even XQC, the API they claim to implement, is dead. It sounds unlikely that there is *any* way forward here. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "El Maquinismo fue proscrito so pena de cosquilleo hasta la muerte" (Ijon Tichy en Viajes, Stanislaw Lem)
Re: Error in calculating length of encoded base64 string
On 2023-Jun-09, Tom Lane wrote: > Gurjeet Singh writes: > > On Thu, Jun 8, 2023 at 7:35 AM Tom Lane wrote: > >> This bug is very ancient, dating to commit 79d78bb26 which > >> added encode.c. (The other instances were presumably copied > >> from there.) Still, it doesn't quite seem worth back-patching. > > > Is it worth investing time in trying to unify these 3 occurrences of > > base64 length (and possibly other relevant) code to one place? If yes, > > I can volunteer for it. > > I wondered about that too. It seems really silly that we made > a copy in src/common and did not replace the others with calls > to that. I looked into this. It turns out that there is a difference in newline handling in the other routines compared to what was added for SCRAM, which doesn't have any (and complains if you supply them). Peter E did suggest to unify them at the time: https://www.postgresql.org/message-id/947b9aff-8fdb-dbf5-a99c-0ffd4523a73f%402ndquadrant.com We could add a boolean "whitespace" flag to both of src/common/base64.c's pg_b64_encode() and pg_b64_decode(); with that I think it could serve the three places that need it. -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/
Re: [PATCH] Add XMLText function (SQL/XML X038)
On 2023-08-26 13:02, Alvaro Herrera wrote: Sadly, all the projects seem to have been pretty much abandoned in the meantime. Zorba has been dead for 9 years, xqilla for 6. Even XQC, the API they claim to implement, is dead. Sounds like bad news for the "XQC as integration point" proposal, anyway. Saxon 11.6 came out two days ago[0], supporting XPath/XQuery 3.1 etc. (12.3 came out last month, but 12 isn't considered the 'stable' release yet. It's working toward XSLT/XPath/XQuery 4.0.) Regards, -Chap [0] https://blog.saxonica.com/announcements/2023/08/saxon-11.6.html
Re: [PATCH] Add XMLText function (SQL/XML X038)
Hi so 26. 8. 2023 v 21:23 odesílatel Chapman Flack napsal: > On 2023-08-26 13:02, Alvaro Herrera wrote: > > Sadly, all the projects seem to have been pretty much abandoned in the > > meantime. Zorba has been dead for 9 years, xqilla for 6. Even XQC, > > the > > API they claim to implement, is dead. > > Sounds like bad news for the "XQC as integration point" proposal, > anyway. > > Saxon 11.6 came out two days ago[0], supporting XPath/XQuery 3.1 etc. > (12.3 came out last month, but 12 isn't considered the 'stable' > release yet. It's working toward XSLT/XPath/XQuery 4.0.) > Saxon can be an interesting library, but nobody knows if integration with Postgres is possible. Their C implementation is Java compiled/executed by GraalV. Regards Pavel > Regards, > -Chap > > [0] https://blog.saxonica.com/announcements/2023/08/saxon-11.6.html > > >
Re: [PATCH] Add XMLText function (SQL/XML X038)
On 2023-08-26 16:00, Pavel Stehule wrote: Saxon can be an interesting library, but nobody knows if integration with Postgres is possible. Their C implementation is Java compiled/executed by GraalV. Indeed, such an integration would probably not be in core. Of the two possible-ways-forward described on that wiki page, the one that didn't rely on the defunct XQC was one involving query rewriting. Have the parser understand the SQL/XML customized syntax, and define a set of ordinary functions it will be rewritten into. (This idea is bolstered somewhat by the fact that many things in SQL/XML, XMLTABLE for example, are /defined in the standard/ in terms of query rewriting into calls on simpler functions.) Then let there be an extension, or ideally someday a choice of extensions, supplying those functions. As to whether running Saxon in a Postgres extension is possible, that's been an example that ships with PL/Java since 1.5.1 five years ago. It's too bad the other projects have stalled; it's good to have more than one ready option. But Saxon shows no sign of going away. Perhaps the act of devising a standardized rewriting of queries onto a standardized set of loadable functions could be of interest to other DBMS projects as well. It's hard to imagine another DBMS not being in the same boat (if it isn't from a rich commercial firm that happens to have a modern XQuery implementation in-house). Maybe having that set of functions specified, with the prospect that more than one DBMS might be interested in a project implementing them, even inspires someone to go look at the xqilla or zorba repos to see how far they got, and pick up the baton, and then there could be more than one option. Regards, -Chap
Re: broken master regress tests
On Sun, Aug 27, 2023 at 3:03 AM Pavel Stehule wrote:
> So it looks so IPC::Run::run is ignore parent environment
I guess the new initdb template captures lc_messages in
postgresql.conf, when it runs earlier? I guess if you put
$node->append_conf('postgresql.conf', 'lc_messages=C'); into
src/bin/pg_amcheck/t/003_check.pl then it will work. I'm not sure
what the correct fix should be, ie if the template mechanism should
notice this difference and not use the template, or if tests that
depend on the message locale should explicitly say so with
lc_messages=C or similar (why is this the only one?), or ...
Re: Extract numeric filed in JSONB more effectively
On 2023-08-22 08:16, Chapman Flack wrote: On 2023-08-22 01:54, Andy Fan wrote: After we label it, we will get error like this: select (a->'a')::int4 from m; ERROR: cannot display a value of type internal Without looking in depth right now, I would double-check what relabel node is being applied at the result. The idea, of course, was to relabel the result as the expected result as I suspected, looking at v10-0003, there's this: + fexpr = (FuncExpr *)makeRelabelType((Expr *) fexpr, INTERNALOID, + 0, InvalidOid, COERCE_IMPLICIT_CAST); compared to the example I had sent earlier: On 2023-08-18 17:02, Chapman Flack wrote: expr = (Expr *)makeRelabelType((Expr *)fexpr, targetOid, -1, InvalidOid, COERCE_IMPLICIT_CAST); The key difference: this is the label going on the result type of the function we are swapping in. The function already has return type declared internal; we want to relabel it as returning the type identified by targetOid. A relabel node to type internal is the reverse of what's needed (and also superfluous, as the function's return type is internal already). Two more minor differences: (1) the node you get from makeRelabelType is an Expr, but not really a FuncExpr. Casting it to FuncExpr is a bit bogus. Also, the third argument to makeRelabelType is a typmod, and I believe the "not-modified" typmod is -1, not 0. In the example I had sent earlier, there were two relabel nodes, serving different purposes. In one, we have a function that wants internal for an argument, but we've made a Const with type OIDOID, so we want to say "this is internal, the thing the function wants." The situation is reversed at the return type of the function: the function declares its return type internal, but the surrounding query is expecting an int4 (or whatever targetOid identifies), so any relabel node there needs to say it's an int4 (or whatever). So, that approach involves two relabelings. In the one idea for restructuring that I suggested earlier, that's reduced: the ..._int function produces a JsonbValue (typed internal) and the selected ..._finish function expects that, so those types match and no relabeling is called for. And with the correct ..._finish function selected at rewrite time, it already has the same return type the surrounding query expects, so no relabeling is called for there either. However, simply to ensure the ..._int function cannot be casually called, it needs an internal parameter, even an unused one, and the rewriter must supply a value for that, which may call for one relabel node. On 2023-08-21 06:50, Andy Fan wrote: I'm not very excited with this manner, reasons are: a). It will have to emit more steps in ExprState->steps which will be harmful for execution. The overhead is something I'm not willing to afford. I would be open to a performance comparison, but offhand I am not sure whether the overhead of another step or two in an ExprState is appreciably more than some of the overhead in the present patch, such as the every-time-through fcinfo initialization buried in DirectFunctionCall1 where you don't necessarily see it. I bet the fcinfo in an ExprState step gets set up once, and just has new argument values slammed into it each time through. (Also, I know very little about how the JIT compiler is used in PG, but I suspect that a step you bury inside your function is a step it may not get to see.) b). this manner requires more *internal*, which is kind of similar to "void *" in C. I'm not sure in what sense you mean "more". The present patch has to deal with two places where some other type must be relabeled internal or something internal relabeled to another type. The approach I suggested does involve two families of function, one returning internal (a JsonbValue) and one expecting internal (a JsonbValue), where the rewriter would compose one over the other, no relabeling needed. There's also an internal parameter needed for whatever returns internal, and that's just the protocol for how such things are done. To me, that seems like a fairly principled use of the type. I would not underestimate the benefit of reducing the code duplication and keeping the patch as clear as possible. The key contributions of the patch are getting a numeric or boolean efficiently out of the JSON operation. Getting from numeric to int or float are things the system already does well. A patch that focuses on what it contributes, and avoids redoing things the system already can do--unless the duplication can be shown to have a strong performance benefit--is easier to review and probably to get integrated. Regards, -Chap
Re: broken master regress tests
Hi
so 26. 8. 2023 v 23:52 odesílatel Thomas Munro
napsal:
> On Sun, Aug 27, 2023 at 3:03 AM Pavel Stehule
> wrote:
> > So it looks so IPC::Run::run is ignore parent environment
>
> I guess the new initdb template captures lc_messages in
> postgresql.conf, when it runs earlier? I guess if you put
> $node->append_conf('postgresql.conf', 'lc_messages=C'); into
> src/bin/pg_amcheck/t/003_check.pl then it will work. I'm not sure
> what the correct fix should be, ie if the template mechanism should
> notice this difference and not use the template, or if tests that
> depend on the message locale should explicitly say so with
> lc_messages=C or similar (why is this the only one?), or ...
>
diff --git a/src/bin/pg_amcheck/t/003_check.pl b/src/bin/pg_amcheck/t/
003_check.pl
index d577cffa30..ba7c713adc 100644
--- a/src/bin/pg_amcheck/t/003_check.pl
+++ b/src/bin/pg_amcheck/t/003_check.pl
@@ -122,6 +122,7 @@ sub perform_all_corruptions()
$node = PostgreSQL::Test::Cluster->new('test');
$node->init;
$node->append_conf('postgresql.conf', 'autovacuum=off');
+$node->append_conf('postgresql.conf', 'lc_messages=C');
$node->start;
$port = $node->port;
it fixes this issue
Regards
Pavel
Re: pg_upgrade fails with in-place tablespace
Could you please provide more thoughts about this issue? pg_upgrade is the final issue of in-place tablespace and we are on the cusp of success. -- Best regards, Rui Zhao
Re: [PATCH] Add XMLText function (SQL/XML X038)
so 26. 8. 2023 v 22:47 odesílatel Chapman Flack napsal: > On 2023-08-26 16:00, Pavel Stehule wrote: > > Saxon can be an interesting library, but nobody knows if integration > > with > > Postgres is possible. Their C implementation is Java compiled/executed > > by GraalV. > > Indeed, such an integration would probably not be in core. > > Of the two possible-ways-forward described on that wiki page, the one > that didn't rely on the defunct XQC was one involving query rewriting. > Have the parser understand the SQL/XML customized syntax, and define > a set of ordinary functions it will be rewritten into. (This idea is > bolstered somewhat by the fact that many things in SQL/XML, XMLTABLE > for example, are /defined in the standard/ in terms of query rewriting > into calls on simpler functions.) > > Then let there be an extension, or ideally someday a choice of > extensions, supplying those functions. > > As to whether running Saxon in a Postgres extension is possible, that's > been an example that ships with PL/Java since 1.5.1 five years ago. > The most simple "solution" can be the introduction of some new hooks there. Then you can write an extension that will call PL/Java functions > > It's too bad the other projects have stalled; it's good to have more > than one ready option. But Saxon shows no sign of going away. > > Perhaps the act of devising a standardized rewriting of queries > onto a standardized set of loadable functions could be of interest > to other DBMS projects as well. It's hard to imagine another DBMS > not being in the same boat (if it isn't from a rich commercial firm > that happens to have a modern XQuery implementation in-house). > > Maybe having that set of functions specified, with the prospect > that more than one DBMS might be interested in a project > implementing them, even inspires someone to go look at the > xqilla or zorba repos to see how far they got, and pick up > the baton, and then there could be more than one option. > Another possibility is revitalization of libxml2. There was an extension http://www.explain.com.au/libx/ But the code is not available to download too, but extending libxml2 is feasible. I am not sure how valuable this work can be. Probably whoever really needs it uses some Java based solution already. Regards Pavel > Regards, > -Chap >
