Re: New Table Access Methods for Multi and Single Inserts

On Wed, Apr 3, 2024 at 2:32 PM Bharath Rupireddy wrote: > > I too prefer the latter so that the caller doesn't have to have two > paths. The new API can just transparently fallback to single inserts. > I've implemented that in the attached v17 patch. I also tested the > default APIs manually, but

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Apr 3, 2024 at 1:10 AM Jeff Davis wrote: > > On Sun, 2024-03-31 at 21:18 +0530, Bharath Rupireddy wrote: > > if (table_modify_buffer_insert() is defined) > >table_modify_buffer_insert(...); > > else > > { > > myState->bistate = GetBulkInsertState(); > > table_tuple_insert(...); > >

Re: New Table Access Methods for Multi and Single Inserts

On Sun, 2024-03-31 at 21:18 +0530, Bharath Rupireddy wrote: > if (table_modify_buffer_insert() is defined) >    table_modify_buffer_insert(...); > else > { >   myState->bistate = GetBulkInsertState(); >   table_tuple_insert(...); > } We can't alloc/free the bulk insert state for every insert call.

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Mar 27, 2024 at 1:42 PM Jeff Davis wrote: > > On Wed, 2024-03-27 at 01:19 +0530, Bharath Rupireddy wrote: > > > > Similarly, with this new AM, the onus lies on the table AM > > implementers to provide an implementation for these new AMs even if > > they just do single inserts. > > Why not

Re: New Table Access Methods for Multi and Single Inserts

On Wed, 2024-03-27 at 01:19 +0530, Bharath Rupireddy wrote: > > Similarly, with this new AM, the onus lies on the table AM > implementers to provide an implementation for these new AMs even if > they just do single inserts. Why not fall back to using the plain tuple_insert? Surely some table AMs

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Mar 26, 2024 at 9:07 PM Jeff Davis wrote: > > On Tue, 2024-03-26 at 01:28 +0530, Bharath Rupireddy wrote: > > I'm thinking > > of dropping the COPY FROM patch using the new multi insert API for > > the > > following reasons: ... > > I agree with all of this. We do want COPY ... FROM suppor

Re: New Table Access Methods for Multi and Single Inserts

On Tue, 2024-03-26 at 01:28 +0530, Bharath Rupireddy wrote: > I'm thinking > of dropping the COPY FROM patch using the new multi insert API for > the > following reasons: ... I agree with all of this. We do want COPY ... FROM support, but there are some details to work out and we don't want to mak

Re: New Table Access Methods for Multi and Single Inserts

On Sat, Mar 23, 2024 at 5:47 AM Jeff Davis wrote: > > Comments: Thanks for looking into it. > * Do I understand correctly that CMV, RMV, and CTAS experience a > performance benefit, but COPY FROM does not? And is that because COPY > already used table_multi_insert, whereas CMV and RMV did not?

Re: New Table Access Methods for Multi and Single Inserts

On Thu, 2024-03-21 at 13:10 +0530, Bharath Rupireddy wrote: > I'm attaching the v13 patches using virtual tuple slots for buffered > tuples for multi inserts. Comments: * Do I understand correctly that CMV, RMV, and CTAS experience a performance benefit, but COPY FROM does not? And is that becaus

Re: New Table Access Methods for Multi and Single Inserts

On Thu, Mar 21, 2024 at 9:44 AM Bharath Rupireddy wrote: > > Yes, usingVirtualTupleTableSlot helps improve the performance a lot. > Below are results from my testing. Note that CMV, RMV, CTAS stand for > CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, CREATE TABLE AS > respectively. These com

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Mar 19, 2024 at 10:40 AM Masahiko Sawada wrote: > > I've not reviewed the patches in depth yet, but run performance tests > for CREATE MATERIALIZED VIEW. The test scenarios is: Thanks for looking into this. > Is there any reason why we copy a buffer-heap tuple to another > buffer-heap tu

Re: New Table Access Methods for Multi and Single Inserts

Hi, On Fri, Mar 8, 2024 at 7:37 PM Bharath Rupireddy wrote: > > On Sat, Mar 2, 2024 at 12:02 PM Bharath Rupireddy > wrote: > > > > On Mon, Jan 29, 2024 at 5:16 PM Bharath Rupireddy > > wrote: > > > > > > > Please find the attached v9 patch set. > > > > I've had to rebase the patches due to comm

Re: New Table Access Methods for Multi and Single Inserts

On Sat, Mar 2, 2024 at 12:02 PM Bharath Rupireddy wrote: > > On Mon, Jan 29, 2024 at 5:16 PM Bharath Rupireddy > wrote: > > > > > Please find the attached v9 patch set. > > I've had to rebase the patches due to commit 874d817, please find the > attached v11 patch set. Rebase needed. Please see t

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Jan 29, 2024 at 5:16 PM Bharath Rupireddy wrote: > > > Please find the attached v9 patch set. I've had to rebase the patches due to commit 874d817, please find the attached v11 patch set. -- Bharath Rupireddy PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: htt

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Jan 29, 2024 at 12:57 PM Bharath Rupireddy wrote: > > On Wed, Jan 17, 2024 at 10:57 PM Bharath Rupireddy > wrote: > > > > Thank you. I'm attaching v8 patch-set here which includes use of new > > insert TAMs for COPY FROM. With this, postgres not only will have the > > new TAM for inserts,

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Jan 17, 2024 at 10:57 PM Bharath Rupireddy wrote: > > Thank you. I'm attaching v8 patch-set here which includes use of new > insert TAMs for COPY FROM. With this, postgres not only will have the > new TAM for inserts, but they also can make the following commands > faster - CREATE TABLE AS

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Aug 1, 2023 at 10:32 PM Jacob Champion wrote: > > On Tue, Aug 1, 2023 at 9:31 AM Bharath Rupireddy > wrote: > > Thanks. Finally, I started to spend time on this. Just curious - may > > I know the discussion in/for which this patch is referenced? What was > > the motive? Is it captured so

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Aug 1, 2023 at 9:31 AM Bharath Rupireddy wrote: > Thanks. Finally, I started to spend time on this. Just curious - may > I know the discussion in/for which this patch is referenced? What was > the motive? Is it captured somewhere? It may not have been the only place, but we at least touc

Re: New Table Access Methods for Multi and Single Inserts

On Sun, Jun 4, 2023 at 4:08 AM Andres Freund wrote: > > Hi, > > This patch was referenced in a discussion at pgcon, so I thought I'd give it a > look, even though Bharat said that he won't have time to drive it forward... Thanks. Finally, I started to spend time on this. Just curious - may I kno

Re: New Table Access Methods for Multi and Single Inserts

On Sun, Jun 4, 2023 at 4:08 AM Andres Freund wrote: > > Hi, > > This patch was referenced in a discussion at pgcon, so I thought I'd give it a > look, even though Bharat said that he won't have time to drive it forward... Thanks. I'm glad to know that the feature was discussed at PGCon. If there

Re: New Table Access Methods for Multi and Single Inserts

Hi, This patch was referenced in a discussion at pgcon, so I thought I'd give it a look, even though Bharat said that he won't have time to drive it forward... On 2021-04-19 10:21:36 +0530, Bharath Rupireddy wrote: > diff --git a/src/backend/access/heap/heapam_handler.c > b/src/backend/access/h

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Oct 12, 2022 at 11:01 AM Michael Paquier wrote: > > This thread has been idle for 6 months now, so I have marked it as > returned with feedback as of what looks like a lack of activity. I > have looked at what's been proposed, and I am not really sure if the > direction taken is correct,

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Mar 07, 2022 at 05:09:23PM +0100, Matthias van de Meent wrote: > That's for the AM-internal flushing; yes. I was thinking about the AM > api for flushing that's used when finalizing the batched insert; i.e. > table_multi_insert_flush. > > Currently it assumes that all buffered tuples will

Re: New Table Access Methods for Multi and Single Inserts

On Sun, 6 Mar 2022 at 12:12, Bharath Rupireddy wrote: > > On Fri, Mar 4, 2022 at 8:07 PM Matthias van de Meent > wrote: > > > Another rebase due to conflicts in 0003. Attaching v6 for review. > > > > I recently touched the topic of multi_insert, and I remembered this > > patch. I had to dig a bit

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Mar 4, 2022 at 8:07 PM Matthias van de Meent wrote: > > Another rebase due to conflicts in 0003. Attaching v6 for review. > > I recently touched the topic of multi_insert, and I remembered this > patch. I had to dig a bit to find it, but as it's still open I've > added some comments: Than

Re: New Table Access Methods for Multi and Single Inserts

On Mon, 19 Apr 2021 at 06:52, Bharath Rupireddy wrote: > > On Mon, Apr 5, 2021 at 9:49 AM Bharath Rupireddy > wrote: > > > > On Wed, Mar 10, 2021 at 10:21 AM Bharath Rupireddy > > wrote: > > > Attaching the v4 patch set. Please review it further. > > > > Attaching v5 patch set after rebasing ont

Re: New Table Access Methods for Multi and Single Inserts

Single Inserts This patch introduces new table access methods for multi and single inserts. Also implements/rearranges the outside code for heap am into these new APIs. Main design goal of these new APIs is to give flexibility to tableam developers in implementing multi insert logic dependent on t

Re: New Table Access Methods for Multi and Single Inserts

rom 6518212583e24b017375512701d9fefa6de20e42 Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy Date: Wed, 10 Mar 2021 09:53:48 +0530 Subject: [PATCH v5 1/3] New Table AMs for Multi and Single Inserts This patch introduces new table access methods for multi and single inserts. Also implements/rearranges the outs

Re: New Table Access Methods for Multi and Single Inserts

Subject: [PATCH v4 1/3] New Table AMs for Multi and Single Inserts This patch introduces new table access methods for multi and single inserts. Also implements/rearranges the outside code for heap am into these new APIs. Main design goal of these new APIs is to give flexibility to tableam deve

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Mar 8, 2021 at 6:37 PM Dilip Kumar wrote: > > On Sat, Feb 20, 2021 at 11:15 AM Bharath Rupireddy > wrote: > > > > Please review the v3 patch set further. > > > > Below is the performance gain measured for CREATE TABLE AS with the > > new multi insert am propsed in this thread: > > > > cas

Re: New Table Access Methods for Multi and Single Inserts

On Sat, Feb 20, 2021 at 11:15 AM Bharath Rupireddy wrote: > > Please review the v3 patch set further. > > Below is the performance gain measured for CREATE TABLE AS with the > new multi insert am propsed in this thread: > > case 1 - 2 integer(of 4 bytes each) columns, 3 varchar(8), tuple size > 5

Re: New Table Access Methods for Multi and Single Inserts

Hi, bq. case 3 - 2 integer(of 4 bytes each) columns, tuple size 32 bytes Is there some other column(s) per row apart from the integer columns ? Since the 2 integer columns only occupy 8 bytes. I wonder where the other 32-8=24 bytes come from. Thanks On Fri, Feb 19, 2021 at 9:45 PM Bharath Rupire

Re: New Table Access Methods for Multi and Single Inserts

On Sat, Feb 20, 2021 at 12:53 PM Zhihong Yu wrote: > > Hi, > bq. case 3 - 2 integer(of 4 bytes each) columns, tuple size 32 bytes > > Is there some other column(s) per row apart from the integer columns ? Since > the 2 integer columns only occupy 8 bytes. I wonder where the other 32-8=24 > bytes

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Feb 17, 2021 at 12:46 PM Bharath Rupireddy wrote: > Hi, > > I addressed the following review comments and attaching v3 patch set. > > 1) ExecClearTuple happens before ExecCopySlot in heap_multi_insert_v2 > and this allowed us to remove clear_mi_slots flag from > TableInsertState. > 2) I re

Re: New Table Access Methods for Multi and Single Inserts

reddy. EnterpriseDB: http://www.enterprisedb.com From 49060fdc2c2a2e6caf1a489fcd16cafd0e1e20a3 Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy Date: Wed, 17 Feb 2021 11:06:35 +0530 Subject: [PATCH v3] New Table AMs for Multi and Single Inserts This patch introduces new table access methods for

Re: New Table Access Methods for Multi and Single Inserts

On Mon, 2021-01-18 at 08:58 +0100, Luc Vlaming wrote: > You mean how it could because of that the table modification API > uses > the table_tuple_insert_speculative ? Just wondering if you think if > it > generally cannot work or would like to see that path / more paths > integrated in to the pa

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: New Table Access Methods for Multi and Single Inserts

> 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_insert_v2 choses single or multi in

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 thre

Re: New Table Access Methods for Multi and Single Inserts

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 threshold at e.g. 100 > rows so that the overhe

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 > 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 have a > single-insert

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 the transaction (e

Re: New Table Access Methods for Multi and Single Inserts

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 the transaction (e.g. > single, > several, thou

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Jan 4, 2021 at 1:29 PM Luc Vlaming 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 have a > single-insert API like heap_insert_v2 so that we can encode the > knowledge that there will j

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: New Table Access Methods for Multi and Single Inserts

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 0001. > > > > Thoughts? > > Are you fami

Re: New Table Access Methods for Multi and Single Inserts

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 0001. > > Thoughts? Are you familiar with this work ? https://commitfest.postgresql.org/31/2717/

Re: New Table Access Methods for Multi and Single Inserts

ve not yet made the multi_insert_v2 API optional as suggested earlier. I will think more on this and update. 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 0001. Thoughts? With Regards, Bharath Rupireddy. En

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Dec 18, 2020 at 11:54:39AM -0600, Justin Pryzby wrote: > On Fri, Dec 18, 2020 at 07:39:14AM +0530, Bharath Rupireddy wrote: > > On Fri, Dec 18, 2020 at 2:14 AM Justin Pryzby wrote: > > > Are you thinking that TableInsertState would eventually have additional > > > attributes which would ap

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Dec 18, 2020 at 11:24 PM Justin Pryzby wrote: > On Fri, Dec 18, 2020 at 07:39:14AM +0530, Bharath Rupireddy wrote: > > On Fri, Dec 18, 2020 at 2:14 AM Justin Pryzby wrote: > > > Are you thinking that TableInsertState would eventually have additional > > > attributes which would apply to o

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Dec 18, 2020 at 07:39:14AM +0530, Bharath Rupireddy wrote: > On Fri, Dec 18, 2020 at 2:14 AM Justin Pryzby wrote: > > Are you thinking that TableInsertState would eventually have additional > > attributes which would apply to other tableams, but not to heap ? Is > > heap_insert_begin() re

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Dec 18, 2020 at 2:14 AM Justin Pryzby wrote: > On Thu, Dec 17, 2020 at 04:35:33PM +0530, Bharath Rupireddy wrote: > > > You made the v2 insert interface a requirement for all table AMs. > > > Should it be optional, and fall back to simple inserts if not implemented > > > ? > > > > I tried

Re: New Table Access Methods for Multi and Single Inserts

On Thu, Dec 17, 2020 at 04:35:33PM +0530, Bharath Rupireddy wrote: > > You made the v2 insert interface a requirement for all table AMs. > > Should it be optional, and fall back to simple inserts if not implemented ? > > I tried to implement the APIs mentioned by Andreas here in [1]. I just > used

Re: New Table Access Methods for Multi and Single Inserts

Thanks a lot for taking a look at the patches. On Thu, Dec 17, 2020 at 10:35 AM Justin Pryzby wrote: > Typos: > > + * 1) Specify is_multi as true, then multi insert state is allcoated. > => allocated > + * dropped, short-lived memory context is delted and mistate is freed up. > => deleted > + *

Re: New Table Access Methods for Multi and Single Inserts

Typos: + * 1) Specify is_multi as true, then multi insert state is allcoated. => allocated + * dropped, short-lived memory context is delted and mistate is freed up. => deleted + * 2) Currently, GetTupleSize() handles the existing heap, buffer, minmal and => minimal + /* Mulit insert state

Re: New Table Access Methods for Multi and Single Inserts

to show that these APIs work, avoids > a lot of duplicate code and makes life easier). Better commenting can > be added later. If these APIs and patches look okay, we can even > consider replacing them in other places such as nodeModifyTable.c and > so on. > > v1-0001-New-Table-AMs-

New Table Access Methods for Multi and Single Inserts

Multi-and-Single-Inserts.patch ---> introduces new table access methods for multi and single inserts. Also implements/rearranges the outside code for heap am into these new APIs. v1-0002-CTAS-and-REFRESH-Mat-View-With-New-Multi-Insert-Table-AM.patch ---> adds new multi insert table acces