Re: [patch] bit XOR aggregate functions

Hi all, Thanks for your reviews. I've updated my patch to the current master and added a documentation line suggesting using the new function as a checksum. Best regards, Alex On 04/03/2021 17:14, Ibrar Ahmed wrote: On Wed, Mar 3, 2021 at 7:30 PM Peter Eisentraut

[patch] bit XOR aggregate functions

Hi, I personally use it as a checksum for a large unordered set, where performance and simplicity is prioritized over collision resilience. Maybe there are other ways to use them. Best, Alex diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1ab31a9056..f33358f8db 100644 --- a/

don't allocate HashAgg hash tables when running explain only

Hi, I got somewhat scared when my explain took a few seconds to complete and used a few gigs of RAM. To reproduce try the following: discard temp; create temp table a as select to_timestamp(generate_series(1, 7000)) i; analyze a; set work_mem to '3GB'; explain select distinct a1.i - a2.i from

Re: Improve planner cost estimations for alternative subplans

Hi Tom, sorry for the delay, I experimented with adding a number-of-evaluations parameter to cost_qual_eval, and found that the majority of call sites do have something realistic they can pass. The attached very-much-WIP patch shows my results so far. There's a lot of loose ends: I like the i

Re: Improve planner cost estimations for alternative subplans

Hi Melanie, Sorry for the delay. I've just started looking at this patch today, but I was wondering if you might include a test case which minimally reproduces the original problem you had. I could reproduce it with an easier generated data set, please see attached. However, to be honest with

Improve planner cost estimations for alternative subplans

Hello, Currently, in case of alternative subplans that do hashed vs per-row lookups, the per-row estimate is used when planning the rest of the query. It's also coded in not quite an explicit way. In [1] we found a situation where it leads to a suboptimal plan, as it bloats the overall cost in

Re: control max length of parameter values logged

Pushed with a bit of editorialization. Great, and thanks for the fixes! Best, Alex

Re: control max length of parameter values logged

Hi, Please see the new version attached. +If greater than zero, bind parameter values reported in non-error +statement-logging messages are trimmed to no more than this many bytes. +If this value is specified without units, it is taken as bytes. +Zero disables log

Re: control max length of parameter values logged

Hi, The privilege argument seems irrelevant to me. We already decided that the plan is (a) SUSET for non-error statement logging purposes and (b) USERSET for logging caused by errors, and that would have to apply to length limits as well as enable/disable ability. Otherwise a user could pretty

Re: control max length of parameter values logged

Hi, +If greater than zero, bind parameter values reported in non-error +statement-logging messages are trimmed to no more than this many bytes. Can I suggest to say: "Limit bind parameter values reported by non-error statement-logging messages to this many bytes". Or, "The maxi

Re: control max length of parameter values logged

Also agreed. It's been like it is for a long time with not that many complaints, so the case for changing the default behavior seems a bit weak. Barring other opinions, I think we have consensus here on what to do. Alexey, will you update your patch? Sorry for the delay, please could you ha

Re: Less-silly selectivity for JSONB matching operators

On 31/03/2020 18:53, Tom Lane wrote: Renamed "matchsel" to "matchingsel" etc, added DEFAULT_MATCHING_SEL, rebased over commit 911e70207. Since that commit already created new versions of the relevant contrib modules, I think we can just redefine what those versions contain, rather than making ye

Re: Less-silly selectivity for JSONB matching operators

I was wondering about DEFAULT_MATCHING_SEL. The difference in purpose from DEFAULT_MATCH_SEL wouldn't be too obvious, but then it probably wouldn't be anyway. Fine with me, especially if both new functions are renamed accordingly. Best, Alex

Re: Less-silly selectivity for JSONB matching operators

So that leaves us with needing to find a better name for this new one. Any ideas? I'm thinking of something wide like opersel, operjoinsel, DEFAULT_OPER_SEL or maybe even genericsel, genericjoinsel, DEFAULT_GENERIC_SEL Best, Alex

Re: Less-silly selectivity for JSONB matching operators

Quickly tested like this: create table t(a jsonb); insert into t select jsonb_object( array[(random() * 10)::int::text], '{" "}') from generate_series(1, 10); insert into t select jsonb_object( array[(random() * 10)::int::text], array[(random() * 1000)::int::text]) from generate_series(1, 1

Re: Less-silly selectivity for JSONB matching operators

Hi Tom, The patches look entirely reasonable to me. The second one needs to be rebased. I like the idea of stubbing matchjoinsel for now, as well as being careful with operators that may correlate with sort orderings. The only little thing I can think of is hardcoding it as 2 * DEFAULT_EQ_SEL

Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING

Hello, Currently the documentation says that one can put "a list of table expressions" after FROM in UPDATE or after USING in DELETE. However, "table expression" is defined as a complex of FROM, WHERE, GROUP BY and HAVING clauses [1]. The thing one can list in the FROM clause in a comma-separat

Re: improve transparency of bitmap-only heap scans

I kinda suspect one of the ressons why this got so little attention is that it was never added to any CF. Thanks Tomas, I've created a CF entry https://commitfest.postgresql.org/27/2443/ Best, Alex

Re: improve transparency of bitmap-only heap scans

Hello, It took me a while to figure out what those names mean. "unfetched", as you call it on the code, may be more descriptive than "avoided" for the new label. However I think the other two are more confusing. It may be a good idea to change them together with this. It'll be sad if this pa

control max length of parameter values logged

Hello Patch ba79cb5 (for full discussion see [1]) introduces a feature to log bind parameter values on error, which greatly helps to reproduce errors artificially having only server log -- thanks everyone who reviewed and improved it! However, it cuts the values, as some of the reviewers were

Re: log bind parameter values on error

Patch file name is not great, changing it. On 30/11/2019 12:35, Alexey Bashtanov wrote: I've implemented it using error contexts, please could you have a look at the patch attached? diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 4ec13f3311..b3a0d27861 100644 ---

Re: log bind parameter values on error

Hi, I'm sorry for replying so late. I don't think those really are contradictions. You can continue to have an errdetail_params(), and but call it from the error context callback set up in the portal code ... Even leaving that aside, I'm *STRONGLY* against entangling elog.c with query execut

Re: log bind parameter values on error

Hi Anders and Alvaro, I must have missed this conversation branch when sending in v011. Sorry about that. > I think it might be worthwhile to cross-reference > log_min_error_statement, as log_parameters_on_error will only take effect when the > statement is logged due to log_min_error_statemen

Re: log bind parameter values on error

Hello Alvaro, I didn't like abusing testlibpq3.c for your new stuff, so I moved it off to a new file testlibpq5.c. I cleaned up a few other cosmetics things about this -- v10 attached. Thanks for doing this. I eventually noticed that this patch fails to initialize each param's textValue to N

Re: log bind parameter values on error

Please find the rebased patch attached. Tested like the following. Provided you're in the postgres checkout and you've run make in src/test/examples/ and connected to db=postgres: CREATE SCHEMA testlibpq3; SET search_path = testlibpq3; CREATE TABLE test1_(i int4, t text, b bytea); INSERT INTO

Re: log bind parameter values on error

On 05/04/2019 12:23, Peter Eisentraut wrote: On 2019-03-29 15:55, Alexey Bashtanov wrote: ERROR: value "62812" is out of range for type smallint STATEMENT: SELECT abalance FROM pgbench_accounts WHERE aid = $1; (In this case the error message contains the parameter value, so it&#x

Re: log bind parameter values on error

Hello and sorry for weeks of silence. Hello Anders and Peter, Thanks for your messages. Please see the new patch version attached. In my testing, I couldn't get this patch to do anything. Could you please share your testing steps? Sure. Provided you're in the postgres checkout and you've ru

Re: log bind parameter values on error

Hello Anders and Peter, Thanks for your messages. Please see the new patch version attached. > Have you analyzed how invasive it'd be to delay that till we actually > can safely start a [sub]transaction to do that logging? Probably too > expensive, but it seems like something that ought to be an

Re: log bind parameter values on error

Hi Peter, With your patch, with log_statement=all and log_parameters=on, you get the same, but with log_statement=all and log_parameters=off you get LOG: execute : SELECT abalance FROM pgbench_accounts WHERE aid = $1; DETAIL: parameters: $1 = UNKNOWN TYPE Thanks for spotting this, I've fixed

Re: log bind parameter values on error

please see attached sorry, some unintended changes sneaked in, please see the corrected patch diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index f64402a..924a76c 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6270,6 +6270,23 @@ log_line_prefix = '%m

Re: log bind parameter values on error

Hello Peter, Unlike SQL, parameters may spend much more memory, so I'd have them in portal memory context to make sure the memory is released earlier rather than later. Having them in the portal structure is different from having it in the portal memory context. Although there is perhaps value

Re: log bind parameter values on error

That sounds like a plausible approach. Have you done any performance measurements? No I haven't yet In your patch, I would organize the changes to the portal API a bit differently. Don't change the signature of CreatePortal(). okay Get rid of PortalSetCurrentTop() and PortalClearCurrent

Re: A case for UPDATE DISTINCT attribute

Hello Gajus, I have observed that the following pattern is repeating in our data management programs: UPDATE   event SET   fuid = ${fuid},   venue_id = ${venueId},   url = ${url} WHERE   id = ${id} AND   fuid IS != ${fuid} AND   venue_id IS != ${venueId} AND   url IS DISTINCT FROM ${url}; ..

log bind parameter values on error

Hello, I'd like to propose a patch to log bind parameter values not only when logging duration, but also on error (timeout in particular) or in whatever situation the statement normally gets logged. This mostly could be useful when the request originator doesn't log them either, so it's hard

Re: proposal: plpgsql pragma statement

You can use PERFORM as a workaround: PERFORM 'PRAGMA', 'cmdtype', 'CREATE'; There's some overhead when executing, but probably not too much. Thank you for tip, but I have not any idea, how it can work? Well, I thought you were for a comment-like thing that remains there when

Re: proposal: plpgsql pragma statement

Hello Pavel, The PRAGMA statement does nothing in runtime. It works only in compile time, and add a pair of key, value to next non pragma statement. This information can be used by some plpgsql extensions. What do you think about this proposal? You can use PERFORM as a workaround: PERFO

Re: Pull up sublink of type 'NOT NOT (expr)'

Hello Richard, Currently for quals in the form of "NOT NOT (SubLink)", this SubLink would not be considered when pulling up sublinks. For instance: gpadmin=# explain select * from a where NOT NOT (a.i in (select b.i from b));      QUERY PLAN --

Timetz comparison

Hello, Comparison of timetz values looks a bit weird to me, as '22:00+02'::timetz > '21:00+01'::timetz. I can see this behavior introduced by commit 2792374c in 2001 with the comment "timetz was just plain broken (some possible pairs of values were neither < nor = nor >)". The in-code commen

Re: [patch] pg_attribute.attndims turns to 0 when 'create table like/as'

Judging from the discussion in [1], attndims is deprecated. Good to know, thanks. Could you describe what you are trying to achieve with it? Mostly conformance with the documentation. Might be easier to fix the docs though.

[patch] pg_attribute.attndims turns to 0 when 'create table like/as'

As reported in [1], pg_attribute.attndims turns to 0 when 'create table like/as'. The patch attached is to fix it. Best Regards,   Alexey [1] https://www.postgresql.org/message-id/20150707072942.1186.98151%40wrigleys.postgresql.org diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes