Re: Assertion with aborted UPDATE in subtransaction

ng the script will lead to the the assertion: TRAP: failed Assert("HEAP_XMAX_IS_LOCKED_ONLY(infomask_lock_old_tuple)"), File: "/usr/local/postgresql-17.4/debug-build/../src/backend/access/heap/heapam.c", Line: 3766, PID: 15604 After analysis with Luc Vlaming, we believe that

Re: Reducing planning time on tables with many indexes

in that query is acquired. We attempted a fix by pre-filtering > out all indexes that anyways cannot be used with a certain query, without > taking the index locks (credits to Luc Vlaming for idea and > implementation). The patch does so by caching the columns present in ever

Re: Lazy JIT IR code generation to increase JIT speed with partitions

nvest more in making it cheap(er) to emit modules, I'm all for that. Kudos to David for fixing the caching in that sense :) @Andres if there's any other things we ought to fix to make this cheap (enough) compared to the previous code I'd love to know your thoughts. Best, Luc Vl

Re: "could not find pathkey item to sort" for TPC-DS queries 94-96

On 15-04-2021 04:01, James Coleman wrote: On Wed, Apr 14, 2021 at 5:42 PM James Coleman wrote: On Mon, Apr 12, 2021 at 8:37 AM Tomas Vondra wrote: On 4/12/21 2:24 PM, Luc Vlaming wrote: Hi, When trying to run on master (but afaik also PG-13) TPC-DS queries 94, 95 and 96 on a SF10 I get

potential deadlock in parallel hashjoin grow-buckets-barrier and blocking nodes?

Hi, Whilst trying to debug a deadlock in some tpc-ds query I noticed something that could cause problems in the hashjoin implementation and cause potentially deadlocks (if my analysis is right). Whilst building the inner hash table, the whole time the grow barriers are attached (the PHJ_BUIL

interaction between csps with dummy tlists and set_customscan_references

Hi, Whilst developing a CSP that potentially sits (directly) above e.g. any union or anything with a dummy tlist we observed some problems as the set_customscan_references cannot handle any dummy tlists and will give invalid varno errors. I was wondering how we can fix this, and I was wonderi

"could not find pathkey item to sort" for TPC-DS queries 94-96

Hi, When trying to run on master (but afaik also PG-13) TPC-DS queries 94, 95 and 96 on a SF10 I get the error "could not find pathkey item to sort". When I disable enable_gathermerge the problem goes away and then the plan for query 94 looks like below. I tried figuring out what the problem i

Re: Lazy JIT IR code generation to increase JIT speed with partitions

On 18-01-2021 08:47, Luc Vlaming wrote: Hi everyone, Andres, On 03-01-2021 11:05, Luc Vlaming wrote: On 30-12-2020 14:23, Luc Vlaming wrote: On 30-12-2020 02:57, Andres Freund wrote: Hi, Great to see work in this area! I would like this topic to somehow progress and was wondering what

Re: allow partial union-all and improve parallel subquery costing

Hi David, On 15-03-2021 14:09, David Steele wrote: Hi Luc, On 12/30/20 8:54 AM, Luc Vlaming wrote: Created a commitfest entry assuming this is the right thing to do so that someone can potentially pick it up during the commitfest. Providing an updated patch based on latest master. Looks

join plan with unexpected var clauses

Hi, At a customer we came across a curious plan (see attached testcase). Given the testcase we see that the outer semi join tries to join the outer with the inner table id columns, even though the middle table id column is also there. Is this expected behavior? The reason i'm asking is two-f

Re: New Table Access Methods for Multi and Single Inserts

On 17-01-2021 00:04, Jeff Davis wrote: If we agree on removing heap_multi_insert_v2 API and embed that logic inside heap_insert_v2, then we can do this - pass the required information and the functions ExecInsertIndexTuples and ExecARInsertTriggers as callbacks so that, whether or not heap_inse

Re: Lazy JIT IR code generation to increase JIT speed with partitions

Hi everyone, Andres, On 03-01-2021 11:05, Luc Vlaming wrote: On 30-12-2020 14:23, Luc Vlaming wrote: On 30-12-2020 02:57, Andres Freund wrote: Hi, Great to see work in this area! I would like this topic to somehow progress and was wondering what other benchmarks / tests would be needed to

Re: Parallel Inserts in CREATE TABLE AS

On 06-01-2021 09:32, Bharath Rupireddy wrote: On Tue, Jan 5, 2021 at 1:25 PM Luc Vlaming wrote: wrt v18-0002patch: It looks like this introduces a state machine that goes like: - starts at CTAS_PARALLEL_INS_UNDEF - possibly moves to CTAS_PARALLEL_INS_SELECT

Re: New Table Access Methods for Multi and Single Inserts

On 06-01-2021 14:06, Bharath Rupireddy wrote: On Wed, Jan 6, 2021 at 12:56 PM Luc Vlaming wrote: The main reason for me for wanting a single API is that I would like the decision of using single or multi inserts to move to inside the tableam. For e.g. a heap insert we might want to put the

Re: New Table Access Methods for Multi and Single Inserts

On 05-01-2021 11:06, Bharath Rupireddy wrote: On Mon, Jan 4, 2021 at 1:29 PM Luc Vlaming <mailto:l...@swarm64.com>> wrote: >  > table AM patch [2] be reviewed further? > As to the patches themselves: > > I think the API is a huge step forward! I assume that we want to

Re: New Table Access Methods for Multi and Single Inserts

On 05-01-2021 22:28, Jeff Davis wrote: On Mon, 2021-01-04 at 08:59 +0100, Luc Vlaming wrote: Reason I'm asking is that I quite liked the heap_insert_begin parameter is_multi, which could even be turned into a "expected_rowcount" of the amount of rows expected to be commited in

Re: Parallel Inserts in CREATE TABLE AS

On 05-01-2021 13:57, Bharath Rupireddy wrote: On Tue, Jan 5, 2021 at 1:00 PM Luc Vlaming wrote: Reviewing further v20-0001: I would still opt for moving the code for the parallel worker into a separate function, and then setting rStartup of the dest receiver to that function in

Re: Parallel Inserts in CREATE TABLE AS

On 05-01-2021 11:32, Dilip Kumar wrote: On Tue, Jan 5, 2021 at 12:43 PM Luc Vlaming wrote: On 04-01-2021 14:32, Bharath Rupireddy wrote: On Mon, Jan 4, 2021 at 4:22 PM Luc Vlaming mailto:l...@swarm64.com>> wrote: > Sorry it took so long to get back to reviewing this. Thank

Re: Parallel Inserts in CREATE TABLE AS

On 04-01-2021 14:53, Bharath Rupireddy wrote: On Mon, Jan 4, 2021 at 5:44 PM Luc Vlaming wrote: On 04-01-2021 12:16, Hou, Zhijie wrote: wrt v18-0002patch: It looks like this introduces a state machine that goes like: - starts at CTAS_PARALLEL_INS_UNDEF - possibly moves

Re: Parallel Inserts in CREATE TABLE AS

On 05-01-2021 04:59, Bharath Rupireddy wrote: On Mon, Jan 4, 2021 at 7:02 PM Bharath Rupireddy wrote: + if (IS_PARALLEL_CTAS_DEST(gstate->dest) && + ((DR_intorel *) gstate->dest)->into->rel && +

Re: Parallel Inserts in CREATE TABLE AS

On 04-01-2021 14:32, Bharath Rupireddy wrote: On Mon, Jan 4, 2021 at 4:22 PM Luc Vlaming <mailto:l...@swarm64.com>> wrote: > Sorry it took so long to get back to reviewing this. Thanks for the comments. > wrt v18-0001patch: > > +               /* > +          

Re: Parallel Inserts in CREATE TABLE AS

On 04-01-2021 12:16, Hou, Zhijie wrote: Hi wrt v18-0002patch: It looks like this introduces a state machine that goes like: - starts at CTAS_PARALLEL_INS_UNDEF - possibly moves to CTAS_PARALLEL_INS_SELECT - CTAS_PARALLEL_INS_TUP_COST_CAN_IGN can be added - if both were add

Re: Parallel Inserts in CREATE TABLE AS

On 30-12-2020 04:55, Bharath Rupireddy wrote: On Wed, Dec 30, 2020 at 5:22 AM Zhihong Yu wrote: w.r.t. v17-0004-Enable-CTAS-Parallel-Inserts-For-Append.patch + * Push the dest receiver to Gather node when it is either at the top of the + * plan or under top Append node unless it does not have

Re: Consider Parallelism While Planning For REFRESH MATERIALIZED VIEW

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:not tested passes according to http://cfbot.cputube.org/ The new status of this

Re: Consider Parallelism While Planning For REFRESH MATERIALIZED VIEW

On 30-12-2020 04:49, Bharath Rupireddy wrote: On Wed, Dec 30, 2020 at 8:03 AM Hou, Zhijie wrote: Yeah without explain analyze we can not show whether the parallelism is picked in the test cases. What we could do is that we can add a plain RMV test case in write_parallel.sql after CMV so that at

Re: New Table Access Methods for Multi and Single Inserts

On 28-12-2020 13:48, Bharath Rupireddy wrote: On Fri, Dec 25, 2020 at 8:10 AM Justin Pryzby wrote: On Thu, Dec 24, 2020 at 05:48:42AM +0530, Bharath Rupireddy wrote: I'm not posting the updated 0002 to 0004 patches, I plan to do so after a couple of reviews happen on the design of the APIs in

Re: Lazy JIT IR code generation to increase JIT speed with partitions

On 30-12-2020 14:23, Luc Vlaming wrote: On 30-12-2020 02:57, Andres Freund wrote: Hi, Great to see work in this area! On 2020-12-28 09:44:26 +0100, Luc Vlaming wrote: I would like to propose a small patch to the JIT machinery which makes the IR code generation lazy. The reason for postponing

Re: faster ETL / bulk data load for heap tables

On 01-01-2021 19:55, Zhihong Yu wrote: Hi, Luc: Happy New Year. Looking at BufferAllocExtend() in v1-0002-WIP-buffer-alloc-specialized-for-relation-extensi.patch. it seems there is duplicate code with the existing BufferAlloc(). It would be good if some refactoring is done by extracting comm

Re: faster ETL / bulk data load for heap tables

On 02-01-2021 08:36, Amit Kapila wrote: On Fri, Jan 1, 2021 at 7:37 PM Luc Vlaming wrote: Hi, In an effort to speed up bulk data loading/transforming I noticed that considerable time is spent in the relation extension lock. We already do extend the relation in bulk when there is a

faster ETL / bulk data load for heap tables

https://www.postgresql.org/message-id/flat/CA%2BhUKG%2BHf_R_ih1pkBMTWn%3DSTyKMOM2Ks47Y_UqqfU1wRc1VvA%40mail.gmail.com#7a53ad72331e423ba3c6a50e6dc1259f >From b08074c5141967a8e6e805fd9c15c1a0450af2f7 Mon Sep 17 00:00:00 2001 From: Luc Vlaming Date: Thu, 31 Dec 2020 09:11:54 +0100 Subject: [PATCH v1 1/2] WIP: lo

Re: allow partial union-all and improve parallel subquery costing

On 23-10-2020 07:51, Luc Vlaming wrote: On 14.10.20 09:38, Luc Vlaming wrote: Hi, It seems I ran the wrong make checks to verify everything is correct (make check instead of make installcheck-world) and this uncovered another regress test change. I also noticed the statistics are sometimes

Re: Lazy JIT IR code generation to increase JIT speed with partitions

On 30-12-2020 02:57, Andres Freund wrote: Hi, Great to see work in this area! On 2020-12-28 09:44:26 +0100, Luc Vlaming wrote: I would like to propose a small patch to the JIT machinery which makes the IR code generation lazy. The reason for postponing the generation of the IR code is that

Lazy JIT IR code generation to increase JIT speed with partitions

: ~530ms Thoughts? With Regards, Luc Vlaming Swarm64 jit_partitions.sql Description: application/sql >From 5dd5df7e29bb3262b8f7f02c4fd3896bb34c3133 Mon Sep 17 00:00:00 2001 From: Luc Vlaming Date: Mon, 28 Dec 2020 09:01:32 +0100 Subject: [PATCH v1] generate JIT IR code lazily --- src/back

Re: Parallel Inserts in CREATE TABLE AS

On 25-11-2020 03:40, Bharath Rupireddy wrote: On Tue, Nov 24, 2020 at 4:43 PM Hou, Zhijie wrote: I'm very interested in this feature, and I'm looking at the patch, here are some comments. Thanks for the review. How about the following style: if(TupIsNull(outerTupleSlot)

Re: Parallel plans and "union all" subquery

On 27-11-2020 04:14, Greg Nancarrow wrote: On Thu, Nov 26, 2020 at 6:11 PM Luc Vlaming wrote: If interesting I can make a draft of what this would look like if this makes it easier to discuss? Sure, that would help clarify it. Okay. I will try to build an example but this will take a few

Re: Multi Inserts in CREATE TABLE AS - revived patch

On 26-11-2020 14:45, Bharath Rupireddy wrote: On Thu, Nov 26, 2020 at 5:34 PM Luc Vlaming wrote: On 26-11-2020 12:36, Bharath Rupireddy wrote: Few things: IIUC Andres mentioned similar kinds of APIs earlier in [1]. [1] - https://www.postgresql.org/message-id/20200924024128.kyk3r5g7dnu3fxxx

Re: Multi Inserts in CREATE TABLE AS - revived patch

On 26-11-2020 12:36, Bharath Rupireddy wrote: Few things: IIUC Andres mentioned similar kinds of APIs earlier in [1]. [1] - https://www.postgresql.org/message-id/20200924024128.kyk3r5g7dnu3fxxx%40alap3.anarazel.de

Re: Parallel plans and "union all" subquery

On 25-11-2020 14:54, Greg Nancarrow wrote: On Wed, Nov 25, 2020 at 6:43 PM Luc Vlaming <mailto:l...@swarm64.com>> wrote: > > > You're completely right, sorry for my error. I was too quick on assuming > my patch would work for this specific case too; I should

Re: Multi Inserts in CREATE TABLE AS - revived patch

On 26-11-2020 07:31, Bharath Rupireddy wrote: On Thu, Nov 26, 2020 at 9:55 AM Michael Paquier wrote: +inline Size +GetTupleSize(TupleTableSlot *slot, Size maxsize) +{ + Size sz = 0; + HeapTuple tuple = NULL; + + if (TTS_IS_HEAPTUPLE(slot)) + tuple = ((HeapTupleTableSlot *) slot)->t

Re: Multi Inserts in CREATE TABLE AS - revived patch

On 23-11-2020 11:23, Bharath Rupireddy wrote: On Mon, Nov 23, 2020 at 3:26 PM Heikki Linnakangas wrote: On 23/11/2020 11:15, Bharath Rupireddy wrote: Attaching v2 patch, rebased on the latest master 17958972. I just broke this again with commit c532d15ddd to split up copy.c. Here's another

Re: Parallel plans and "union all" subquery

On 24-11-2020 01:44, Greg Nancarrow wrote: On Tue, Nov 24, 2020 at 2:34 AM Luc Vlaming wrote: Hi, For this problem there is a patch I created, which is registered under https://commitfest.postgresql.org/30/2787/ that should fix this without any workarounds. Maybe someone can take a look at

Re: Parallel plans and "union all" subquery

On 23-11-2020 13:17, Phil Florent wrote: Hi Greg, The implicit conversion was the cause of the non parallel plan, thanks for the explanation and the workarounds. It can cause a huge difference in terms of performance, I will give the information to our developers. Regards, Phil -

Re: should INSERT SELECT use a BulkInsertState?

On 30.10.20 05:51, Justin Pryzby wrote: On Thu, Oct 22, 2020 at 01:29:53PM +0100, Simon Riggs wrote: On Fri, 16 Oct 2020 at 22:05, Justin Pryzby wrote: I made this conditional on BEGIN BULK/SET bulk, so I'll solicit comments on that. I think it would be better if this was self-tuning. So t

Re: allow partial union-all and improve parallel subquery costing

On 14.10.20 09:38, Luc Vlaming wrote: Hi, It seems I ran the wrong make checks to verify everything is correct (make check instead of make installcheck-world) and this uncovered another regress test change. I also noticed the statistics are sometimes giving different row count results so I

Re: Parallel Inserts in CREATE TABLE AS

On 16.10.20 08:23, Bharath Rupireddy wrote: On Fri, Oct 16, 2020 at 11:33 AM Luc Vlaming wrote: Really looking forward to this ending up in postgres as I think it's a very nice improvement. Whilst reviewing your patch I was wondering: is there a reason you did not introduce a batch inse

Re: Parallel Inserts in CREATE TABLE AS

On 14.10.20 11:16, Bharath Rupireddy wrote: On Tue, Oct 6, 2020 at 10:58 AM Amit Kapila wrote: Yes we do a bunch of catalog changes related to the created new table. We will have both the txn id and command id assigned when catalogue changes are being made. But, right after the table is creat

Re: allow partial union-all and improve parallel subquery costing

make sure the regress output is stable. Updated patch attached which now successfully runs installcheck-world for v13 and master. Kind regards, Luc From: Luc Vlaming Sent: Tuesday, October 13, 2020 10:57 AM To: pgsql-hackers Subject: allow partial union-all

Re: Use list_delete_xxxcell O(1) instead of list_delete_ptr O(N) in some places

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: not tested Spec compliant: not tested Documentation:not tested Patch applies cleanly on master & 13 and installcheck-world runs on 13 &

allow partial union-all and improve parallel subquery costing

: Luc Vlaming Date: Tue, 13 Oct 2020 09:35:33 +0200 Subject: [PATCH v1] Allow partial UNION ALL; improve parallel subquery costing --- src/backend/optimizer/path/costsize.c | 11 ++ src/backend/optimizer/prep/prepunion.c | 4 +++ src/test/regress/expected/union.out| 50