Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500

Hello Tom and Richard, 17.11.2023 22:42, Tom Lane wrote: OK. I pushed the patch after a bit more review: we can simplify things some more by using the subroot->parse querytree for all tests. After the previous refactoring, it wasn't buying us anything to do some initial tests with the raw quer

Re: verify predefined LWLocks have entries in wait_event_names.txt

Hi, On Fri, Jan 05, 2024 at 11:46:20AM -0600, Nathan Bossart wrote: > On Fri, Jan 05, 2024 at 10:42:03AM -0600, Nathan Bossart wrote: > > On Fri, Jan 05, 2024 at 07:39:39AM +, Bertrand Drouvot wrote: > >> + die "lists of predefined LWLocks in lwlocknames.txt and > >> wait_event_names.tx

Re: Multidimensional Histograms

Hi Tomas, I am sorry I didn't look into the code carefully. Indeed Postgres uses Equi-Depth Histogram: delta = (nvals - 1) / (num_hist - 1); Regards, Alexander Cheshev On Sat, 6 Jan 2024 at 01:00, Alexander Cheshev wrote: > > Hi Tomas, > > > Another reason was that the algorithm described in

Re: Exposing the lock manager's WaitForLockers() to SQL

Simplified the code and docs, and rewrote the example with more prose instead of PL/pgSQL, which unfortunately made it longer, although it could be truncated. Not really sure what's best... v5-0001-Refactor-GetLockConflicts-into-more-general-GetLo.patch Description: Binary data v5-0003-Add-WAIT

Re: Exposing the lock manager's WaitForLockers() to SQL

On Sat, 2024-01-06 at 02:57 -0800, Will Mortensen wrote: > Simplified the code and docs, and rewrote the example with more prose > instead of PL/pgSQL, which unfortunately made it longer, although it > could be truncated. Not really sure what's best... I thought about this idea, and I have some do

Re: introduce dynamic shared memory registry

On Wed, Jan 3, 2024 at 4:19 AM Nathan Bossart wrote: > > Here's a new version of the patch set with Bharath's feedback addressed. Thanks. The v4 patches look good to me except for a few minor comments. I've marked it as RfC in CF. 1. Update all the copyright to the new year. A run of src/tools/c

Re: Shared detoast Datum proposal

On Mon, 1 Jan 2024 at 19:26, Andy Fan wrote: > > > Andy Fan writes: > > > > > Some Known issues: > > -- > > > > 1. Currently only Scan & Join nodes are considered for this feature. > > 2. JIT is not adapted for this purpose yet. > > JIT is adapted for this feature in v2. Any feedb

Re: [PATCH] pgbench log file headers

On Tue, 21 Nov 2023 at 09:52, Adam Hendel wrote: > > Hello, > > On Mon, Nov 13, 2023 at 6:01 PM Andres Freund wrote: >> >> Hi, >> >> On 2023-11-13 11:55:07 -0600, Adam Hendel wrote: >> > Currently, pgbench will log individual transactions to a logfile when the >> > `--log` parameter flag is provi

Re: POC: Extension for adding distributed tracing - pg_tracing

On Thu, 7 Dec 2023 at 20:06, Anthonin Bonnefoy wrote: > > Hi, > > Thanks for the review! > > > ``` > > +-- Worker can take some additional time to end and report their spans > > +SELECT pg_sleep(0.2); > > + pg_sleep > > +-- > > + > > +(1 row) > > ``` > > > > Pretty sure this will fail on b

Re: pg_stat_statements and "IN" conditions

On Tue, 31 Oct 2023 at 14:36, Dmitry Dolgov <9erthali...@gmail.com> wrote: > > > On Fri, Oct 27, 2023 at 05:02:44PM +0200, Dmitry Dolgov wrote: > > > On Thu, Oct 26, 2023 at 09:08:42AM +0900, Michael Paquier wrote: > > > typedef struct ArrayExpr > > > { > > > + pg_node_attr(custom_query_jumble)

Re: Emit fewer vacuum records by reaping removable tuples during pruning

Patch 0001 in the attached set addresses the following review feedback: - pronto_reap renamed to no_indexes - reduce the number of callers of heap_prune_record_unused() by calling it from heap_prune_record_dead() when appropriate - add unlikely hint to no_indexes test I've also dropped the patc

Re: weird GROUPING SETS and ORDER BY behaviour

On Fri, 5 Jan 2024 at 18:34, Zhang Mingli wrote: > > On Jan 6, 2024 at 01:38 +0800, Geoff Winkless , wrote: > > > Am I missing some reason why the first set isn't sorted as I'd hoped? > > > Woo, it’s a complex order by, I try to understand your example. > And I think the order is right, what’s you

Re: btree: downlink right separator/HIKEY optimization

On Wed, 1 Nov 2023 at 03:38, Matthias van de Meent wrote: > > (now really to -hackers) > Hi, > > Over at [0] I'd implemented an optimization that allows us to skip > calling _bt_compare in _bt_moveright in many common cases. This patch, > when stacked on top of the prefix truncation patch, improve

Re: Bytea PL/Perl transform

On Fri, 21 Jul 2023 at 02:59, Ivan Panchenko wrote: > > Friday, 14 July 2023, 23:27 +03:00 от Tom Lane : > > =?UTF-8?B?SXZhbiBQYW5jaGVua28=?= writes: > > Четверг, 6 июля 2023, 14:48 +03:00 от Peter Eisentraut < > > pe...@eisentraut.org >: > >> If the transform deals with a built-in type, then th

Re: Emit fewer vacuum records by reaping removable tuples during pruning

On Fri, Jan 5, 2024 at 12:23 PM Robert Haas wrote: > > As I think we chatted about before, I eventually would like the option to > > remove index entries for a tuple during on-access pruning, for OLTP > > workloads. I.e. before removing the tuple, construct the corresponding index > > tuple, use i

Re: verify predefined LWLocks have entries in wait_event_names.txt

On Sat, Jan 06, 2024 at 09:03:52AM +, Bertrand Drouvot wrote: > Sorry, I missed this in my first review, but instead of: > > - input: files('../../backend/storage/lmgr/lwlocknames.txt'), > + input: [files('../../backend/storage/lmgr/lwlocknames.txt'), > files('../../backend/utils/activity/w

Re: weird GROUPING SETS and ORDER BY behaviour

On Sat, Jan 6, 2024 at 8:38 AM Geoff Winkless wrote: > On Fri, 5 Jan 2024 at 18:34, Zhang Mingli wrote: > > > > On Jan 6, 2024 at 01:38 +0800, Geoff Winkless , > wrote: > > > > > > Am I missing some reason why the first set isn't sorted as I'd hoped? > > > > > > Woo, it’s a complex order by, I t

Re: Emit fewer vacuum records by reaping removable tuples during pruning

On Fri, Jan 5, 2024 at 12:57 PM Andres Freund wrote: > > I will be astonished if you can make this work well enough to avoid > > huge regressions in plausible cases. There are plenty of cases where > > we do a very thorough job opportunistically removing index tuples. > > These days the AM is ofte

Re: introduce dynamic shared memory registry

On Sat, Jan 06, 2024 at 07:34:15PM +0530, Bharath Rupireddy wrote: > 1. Update all the copyright to the new year. A run of > src/tools/copyright.pl on the source tree will take care of it at some > point, but still it's good if we can update while we are here. Done. > 2. Typo: missing "an" before

Re: weird GROUPING SETS and ORDER BY behaviour

Hi, Zhang Mingli www.hashdata.xyz On Jan 6, 2024 at 23:38 +0800, Geoff Winkless , wrote: > > I was hoping to see > > gp_n | gp_conc | n | concat > --+-+--+ > 1 | 0 | NULL | n1x5 > 1 | 0 | NULL | n2x4 > 1 | 0 | NULL | n3x3 > 1 | 0 | NULL | n4x2 > 1 | 0 | NULL | n5x1 > 0 | 1

Re: Password leakage avoidance

On 1/2/24 07:23, Sehrope Sarkuni wrote: 1. There's two sets of defaults, the client program's default and the server's default. Need to pick one for each implemented function. They don't need to be the same across the board. Is there a concrete recommendation here? 2. Password encoding should

Re: weird GROUPING SETS and ORDER BY behaviour

On Sat, 6 Jan 2024 at 16:22, David G. Johnston wrote: > On Sat, Jan 6, 2024 at 8:38 AM Geoff Winkless wrote: >> because when gp_conc is 0, it should be ordering by the concat() value. > > Something does seem off here with the interaction between grouping sets and > order by. > I'm inclined to be

Re: abi-compliance-checker

On Wed, 1 Nov 2023 at 16:43, Peter Eisentraut wrote: > > Here is an updated version of this patch. It doesn't have any new > functionality, just a rebase and some minor adjustments. > > I have split up the one patch into several ones, which could be > considered incrementally, namely: > > v3-0001

Re: Unified File API

On Thu, 29 Jun 2023 at 13:20, John Morris wrote: > > Background > > == > > PostgreSQL has an amazing variety of routines for accessing files. Consider > just the “open file” routines. >PathNameOpenFile, OpenTemporaryFile, BasicOpenFile, open, fopen, > BufFileCreateFileSet, > >Buf

Re: Password leakage avoidance

On 12/24/23 10:14, Joe Conway wrote: On 12/23/23 11:00, Tom Lane wrote: Joe Conway writes: The attached patch set moves the guts of \password from psql into the libpq client side -- PQchangePassword() (patch 0001). Haven't really read the patch, just looked at the docs, but here's a bit of b

Re: Password leakage avoidance

On Sat, Jan 6, 2024 at 11:53 AM Joe Conway wrote: > On 1/2/24 07:23, Sehrope Sarkuni wrote: > > 1. There's two sets of defaults, the client program's default and the > > server's default. Need to pick one for each implemented function. They > > don't need to be the same across the board. > > Is t

Re: Password leakage avoidance

On Sat, Jan 6, 2024 at 12:39 PM Joe Conway wrote: > The only code specific comments were Tom's above, which have been > addressed. If there are no serious objections I plan to commit this > relatively soon. > One more thing that we do in pgjdbc is to zero out the input password args so that they

Re: Password leakage avoidance

Scratch that, rather than memset(...) should be explicit_bzero(...) so it doesn't get optimized out. Same idea though. Regards, -- Sehrope Sarkuni Founder & CEO | JackDB, Inc. | https://www.jackdb.com/ >

Re: Password leakage avoidance

On 1/6/24 13:16, Sehrope Sarkuni wrote: On Sat, Jan 6, 2024 at 12:39 PM Joe Conway > wrote: The only code specific comments were Tom's above, which have been addressed. If there are no serious objections I plan to commit this relatively soon. One more thi

Re: weird GROUPING SETS and ORDER BY behaviour

"David G. Johnston" writes: > Something does seem off here with the interaction between grouping sets and > order by. Yeah. I think Geoff is correct to identify the use of subqueries in the grouping sets as the triggering factor. We can get some insight by explicitly printing the ordering value

Re: Password leakage avoidance

Joe Conway writes: > The only code specific comments were Tom's above, which have been > addressed. If there are no serious objections I plan to commit this > relatively soon. I had not actually read this patchset before, but now I have, and I have a few minor suggestions: * The API comment fo

Re: Wrong results with grouping sets

Richard Guo writes: > For a variable-free expression, if it contains volatile functions, SRFs, > aggregates, or window functions, it would not be treated as a member of > EC that is redundant (see get_eclass_for_sort_expr()). That means it > would not be removed from the pathkeys list, so we do n

Re: Fix bogus Asserts in calc_non_nestloop_required_outer

Robert Haas writes: > On Fri, Jan 5, 2024 at 4:36 PM Tom Lane wrote: >> I don't think I believe this code change, let alone any of the >> explanations for it. The point of these Asserts is to be sure that >> we don't form an alleged parameterization set that includes any rels >> that are include

Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500

Alexander Lakhin writes: > Please look at the following query: > CREATE TABLE t(i int); > INSERT INTO t VALUES (1); > VACUUM ANALYZE t; > WITH ir AS (INSERT INTO t VALUES (2) RETURNING i) > SELECT * FROM ir WHERE i = 2; > which produces ERROR:  no relation entry for relid 1 > starting from f7816

Re: weird GROUPING SETS and ORDER BY behaviour

On Sat, 6 Jan 2024, 19:49 Tom Lane, wrote: > "David G. Johnston" writes: > > Something does seem off here with the interaction between grouping sets > and > > order by. > > Yeah. I think Geoff is correct to identify the use of subqueries in > the grouping sets as the triggering factor. [snip]

Re: POC: Extension for adding distributed tracing - pg_tracing

On Thu, Jan 4, 2024, 16:36 Anthonin Bonnefoy wrote: > > I used GUCs to set the `opentelemtery_trace_id` and `opentelemetry_span_id`. > > These can be sent as protocol-level messages by the client driver if the > > client driver has native trace integration enabled, in which case the user > > doesn

Re: Multidimensional Histograms

On 1/6/24 01:00, Alexander Cheshev wrote: > Hi Tomas, > >> Another reason was that the algorithm described in the two papers you >> reference (1988 paper by DeWitt and the evaluation by Carlson and >> Sutherland from ~2010) is simple but has pretty obvious weaknesses. It >> processes the column

Re: Shared detoast Datum proposal

Hi, vignesh C writes: > On Mon, 1 Jan 2024 at 19:26, Andy Fan wrote: >> >> >> Andy Fan writes: >> >> > >> > Some Known issues: >> > -- >> > >> > 1. Currently only Scan & Join nodes are considered for this feature. >> > 2. JIT is not adapted for this purpose yet. >> >> JIT is a

Re: Adding facility for injection points (or probe points?) for more advanced tests

On Fri, Jan 05, 2024 at 10:28:47AM -0600, Nathan Bossart wrote: > +1 Extra note for this thread: it is possible to add a SQL test case for problems like what's been reported on this thread when facing a partial write failure: https://www.postgresql.org/message-id/18259-6e256429825dd...@postgresql.

Re: Support prepared statement invalidation when result types change

On Mon, 18 Sept 2023 at 18:01, Jelte Fennema-Nio wrote: > > @Euler thanks for the review. I addressed the feedback. > > On Fri, 15 Sept 2023 at 01:41, Andy Fan wrote: > > What if a client has *cached* an old version of RowDescription > > and the server changed it to something new and sent resultd

Re: Support "Right Semi Join" plan shapes

On Wed, 1 Nov 2023 at 11:25, Richard Guo wrote: > > > On Thu, Aug 10, 2023 at 3:24 PM Richard Guo wrote: >> >> The cfbot reminds that this patch does not apply any more, so rebase it >> to v2. > > > Attached is another rebase over the latest master. Any feedback is > appreciated. One of the tes

Re: [PATCH] Support % wildcard in extension upgrade filenames

On Mon, 7 Aug 2023 at 19:25, Sandro Santilli wrote: > > On Tue, Aug 01, 2023 at 08:24:15PM +0200, Daniel Gustafsson wrote: > > > On 28 Jun 2023, at 10:29, Daniel Gustafsson wrote: > > > > > >> On 31 May 2023, at 21:07, Sandro Santilli wrote: > > >> On Thu, Apr 27, 2023 at 12:49:57PM +0200, Sandr

Re: Add a perl function in Cluster.pm to generate WAL

On Fri, Jan 05, 2024 at 11:00:00PM +0300, Alexander Lakhin wrote: > Your suspicion was proved right. After > git show c161ab74f src/test/recovery/t/035_standby_logical_decoding.pl  | git > apply -R > 20 iterations with 20 tests in parallel performed successfully for me > (twice). > > So it looks

Re: the s_lock_stuck on perform_spin_delay

Hi, > On 2024-01-05 14:19:23 -0500, Robert Haas wrote: >> On Fri, Jan 5, 2024 at 2:11 PM Andres Freund wrote: >> > I see it fairly regularly. Including finding several related bugs that >> > lead to >> > stuck systems last year (signal handlers are a menace). >> >> In that case, I think this

Re: Extract numeric filed in JSONB more effectively

Hi, > hi. > you don't need to change src/include/catalog/catversion.h > as mentioned in https://wiki.postgresql.org/wiki/Committing_checklist > Otherwise, cfbot will fail many times. Thanks for the wiki. I checked the wiki and search "catversion", the only message I got is: "Consider the need