Thread: Asynchronous execution support for Custom Scan
Hello, I suggest supporting asynchronous execution for Custom Scan. Since v14, PostgreSQL supports asynchronous execution for Foreign Scan. This patch enables asynchronous execution by applying the process for Foreign Scan to Custom Scan . The patch is divided into 2 parts, source and documents(sgml). Regards,
Attachment
v1 patch occurs gcc warnings, I fixed it. 2022年8月13日(土) 22:42 Kazutaka Onishi <onishi@heterodb.com>: > > Hello, > > I suggest supporting asynchronous execution for Custom Scan. > Since v14, PostgreSQL supports asynchronous execution for Foreign Scan. > This patch enables asynchronous execution by applying the process for > Foreign Scan to Custom Scan . > > The patch is divided into 2 parts, source and documents(sgml). > > > Regards,
Attachment
Hi Onishi-san, On Sun, Aug 14, 2022 at 12:59 PM Kazutaka Onishi <onishi@heterodb.com> wrote: > v1 patch occurs gcc warnings, I fixed it. Thanks for working on this! I'd like to review this (though, I'm not sure I can have time for it in the next commitfet), but I don't think we can review this without any example. Could you provide it? I think a simple example is better for ease of review. Best regards, Etsuro Fujita
I internally suggested him to expand the ctidscan module for the PoC purpose. https://github.com/kaigai/ctidscan Even though it does not have asynchronous capability actually, but suitable to ensure API works and small enough for reviewing. Best regards, 2022年8月22日(月) 17:55 Etsuro Fujita <etsuro.fujita@gmail.com>: > > Hi Onishi-san, > > On Sun, Aug 14, 2022 at 12:59 PM Kazutaka Onishi <onishi@heterodb.com> wrote: > > v1 patch occurs gcc warnings, I fixed it. > > Thanks for working on this! > > I'd like to review this (though, I'm not sure I can have time for it > in the next commitfet), but I don't think we can review this without > any example. Could you provide it? I think a simple example is > better for ease of review. > > Best regards, > Etsuro Fujita -- HeteroDB, Inc / The PG-Strom Project KaiGai Kohei <kaigai@heterodb.com>
Hi KaiGai-san, On Tue, Aug 23, 2022 at 6:26 PM Kohei KaiGai <kaigai@heterodb.com> wrote: > I internally suggested him to expand the ctidscan module for the PoC purpose. > https://github.com/kaigai/ctidscan > > Even though it does not have asynchronous capability actually, but > suitable to ensure > API works and small enough for reviewing. Seems like a good idea. Thanks! Best regards, Etsuro Fujita
Hi, Fujii-san, The asynchronous version "ctidscan" plugin is ready. Please check this. https://github.com/0-kaz/ctidscan/tree/async_sample I've confirmed this works correctly by running SQL shown below. The query plan shows 2 custom scan works asynchronously. postgres=# LOAD 'ctidscan'; LOAD postgres=# EXPLAIN ANALYZE SELECT * FROM t1 WHERE ctid BETWEEN '(2,1)'::tid AND '(3,10)'::tid UNION SELECT * FROM (SELECT * FROM t1 WHERE ctid BETWEEN '(2,115)'::tid AND '(3,10)'::tid); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=3.55..5.10 rows=155 width=36) (actual time=0.633..0.646 rows=130 loops=1) Group Key: t1.a, t1.b Batches: 1 Memory Usage: 48kB -> Append (cost=0.01..2.77 rows=155 width=36) (actual time=0.035..0.590 rows=146 loops=1) -> Async Custom Scan (ctidscan) on t1 (cost=0.01..1.00 rows=134 width=37) (actual time=0.009..0.129 rows=130 loops=1) Filter: ((ctid >= '(2,1)'::tid) AND (ctid <= '(3,10)'::tid)) Rows Removed by Filter: 30 ctid quals: ((ctid >= '(2,1)'::tid) AND (ctid <= '(3,10)'::tid)) -> Async Custom Scan (ctidscan) on t1 t1_1 (cost=0.01..1.00 rows=21 width=37) (actual time=0.003..0.025 rows=16 loops=1) Filter: ((ctid >= '(2,115)'::tid) AND (ctid <= '(3,10)'::tid)) Rows Removed by Filter: 144 ctid quals: ((ctid >= '(2,115)'::tid) AND (ctid <= '(3,10)'::tid)) Planning Time: 0.314 ms Execution Time: 0.762 ms (14 rows) Regards, 2022年8月26日(金) 17:18 Etsuro Fujita <etsuro.fujita@gmail.com>: > > Hi KaiGai-san, > > On Tue, Aug 23, 2022 at 6:26 PM Kohei KaiGai <kaigai@heterodb.com> wrote: > > I internally suggested him to expand the ctidscan module for the PoC purpose. > > https://github.com/kaigai/ctidscan > > > > Even though it does not have asynchronous capability actually, but > > suitable to ensure > > API works and small enough for reviewing. > > Seems like a good idea. > > Thanks! > > Best regards, > Etsuro Fujita
On Fri, Sep 2, 2022 at 10:43 PM Kazutaka Onishi <onishi@heterodb.com> wrote: > The asynchronous version "ctidscan" plugin is ready. Thanks for that! I looked at the extended version quickly. IIUC, it uses the proposed APIs, but actually executes ctidscans *synchronously*, so it does not improve performance. Right? Anyway, that version seems to be useful for testing that the proposed APIs works well. So I'll review the proposed patches with it. I'm not Fujii-san, though. :-) Best regards, Etsuro Fujita
Fujita-san, I'm sorry for my error on your name... > IIUC, it uses the proposed > APIs, but actually executes ctidscans *synchronously*, so it does not > improve performance. Right? Exactly. The actual CustomScan that supports asynchronous execution will start processing in CustomScanAsyncRequest, configure to detect completion via file descriptor in CustomScanAsyncConfigureWait, and receive the result in CustomScanAsyncNotify. > So I'll review the proposed patches with it. Thank you! 2022年9月5日(月) 15:27 Etsuro Fujita <etsuro.fujita@gmail.com>: > > On Fri, Sep 2, 2022 at 10:43 PM Kazutaka Onishi <onishi@heterodb.com> wrote: > > The asynchronous version "ctidscan" plugin is ready. > > Thanks for that! > > I looked at the extended version quickly. IIUC, it uses the proposed > APIs, but actually executes ctidscans *synchronously*, so it does not > improve performance. Right? > > Anyway, that version seems to be useful for testing that the proposed > APIs works well. So I'll review the proposed patches with it. I'm > not Fujii-san, though. :-) > > Best regards, > Etsuro Fujita
On Mon, Sep 5, 2022 at 10:32 PM Kazutaka Onishi <onishi@heterodb.com> wrote: > I'm sorry for my error on your name... No problem. > > IIUC, it uses the proposed > > APIs, but actually executes ctidscans *synchronously*, so it does not > > improve performance. Right? > Exactly. > The actual CustomScan that supports asynchronous execution will > start processing in CustomScanAsyncRequest, > configure to detect completion via file descriptor in > CustomScanAsyncConfigureWait, > and receive the result in CustomScanAsyncNotify. Ok, thanks! Best regards, Etsuro Fujita
Le mardi 6 septembre 2022, 11:29:55 CET Etsuro Fujita a écrit : > On Mon, Sep 5, 2022 at 10:32 PM Kazutaka Onishi <onishi@heterodb.com> wrote: > > I'm sorry for my error on your name... > > No problem. > > > > IIUC, it uses the proposed > > > > > > APIs, but actually executes ctidscans *synchronously*, so it does not > > > improve performance. Right? > > > > Exactly. > > The actual CustomScan that supports asynchronous execution will > > start processing in CustomScanAsyncRequest, > > configure to detect completion via file descriptor in > > CustomScanAsyncConfigureWait, > > and receive the result in CustomScanAsyncNotify. > > Ok, thanks! Thanks for this patch, seems like a useful addition to the CustomScan API. Just to nitpick: there are extraneous tabs in createplan.c on a blank line. Sorry for the digression, but I know your ctidscan module had been proposed for inclusion in contrib a long time ago, and I wonder if the rationale for not including it could have changed. We still don't have tests which cover CustomScan, and I can think of at least a few use cases where this customscan is helpful and not merely testing code. One of those use case is when performing migrations on a table, and one wants to update the whole table by filling a new column with a computed value. You obviously don't want to do it in a single transaction, so you end up batching updates using an index looking for null values. If you want to do this, it's much faster to update rows in a range of block, performing a first series of batch updating all such block ranges, and then finally update the ones we missed transactionally (inserted in a block we already processed while in the middle of the batch, or in new blocks resulting from a relation extension). Best regards, -- Ronan Dunklau
Thank you for your comment. I've removed the tabs. > I can think of at least a few use cases where this customscan is helpful and not merely testing code. IIUC, we already can use ctid in the where clause on the latest PostgreSQL, can't we? 2022年11月22日(火) 18:07 Ronan Dunklau <ronan.dunklau@aiven.io>: > > Le mardi 6 septembre 2022, 11:29:55 CET Etsuro Fujita a écrit : > > On Mon, Sep 5, 2022 at 10:32 PM Kazutaka Onishi <onishi@heterodb.com> wrote: > > > I'm sorry for my error on your name... > > > > No problem. > > > > > > IIUC, it uses the proposed > > > > > > > > APIs, but actually executes ctidscans *synchronously*, so it does not > > > > improve performance. Right? > > > > > > Exactly. > > > The actual CustomScan that supports asynchronous execution will > > > start processing in CustomScanAsyncRequest, > > > configure to detect completion via file descriptor in > > > CustomScanAsyncConfigureWait, > > > and receive the result in CustomScanAsyncNotify. > > > > Ok, thanks! > > Thanks for this patch, seems like a useful addition to the CustomScan API. > Just to nitpick: there are extraneous tabs in createplan.c on a blank line. > > Sorry for the digression, but I know your ctidscan module had been proposed > for inclusion in contrib a long time ago, and I wonder if the rationale for > not including it could have changed. We still don't have tests which cover > CustomScan, and I can think of at least a few use cases where this customscan > is helpful and not merely testing code. > > One of those use case is when performing migrations on a table, and one wants > to update the whole table by filling a new column with a computed value. You > obviously don't want to do it in a single transaction, so you end up batching > updates using an index looking for null values. If you want to do this, it's > much faster to update rows in a range of block, performing a first series of > batch updating all such block ranges, and then finally update the ones we > missed transactionally (inserted in a block we already processed while in the > middle of the batch, or in new blocks resulting from a relation extension). > > Best regards, > > -- > Ronan Dunklau > >
Attachment
> IIUC, we already can use ctid in the where clause on the latest > PostgreSQL, can't we? Oh, sorry, I missed the TidRangeScan. My apologies for the noise. Best regards, -- Ronan Dunklau
> > IIUC, we already can use ctid in the where clause on the latest > > PostgreSQL, can't we? > > Oh, sorry, I missed the TidRangeScan. My apologies for the noise. > I made the ctidscan extension when we developed CustomScan API towards v9.5 or v9.6, IIRC. It would make sense just an example of CustomScan API (e.g, PG-Strom code is too large and complicated to learn about this API), however, makes no sense from the standpoint of the functionality. Best regards, 2022年12月1日(木) 22:27 Ronan Dunklau <ronan.dunklau@aiven.io>: > > > IIUC, we already can use ctid in the where clause on the latest > > PostgreSQL, can't we? > > Oh, sorry, I missed the TidRangeScan. My apologies for the noise. > > Best regards, > > -- > Ronan Dunklau > > > > -- HeteroDB, Inc / The PG-Strom Project KaiGai Kohei <kaigai@heterodb.com>
On Fri, 2 Dec 2022 at 05:05, Kohei KaiGai <kaigai@heterodb.com> wrote: > > > > IIUC, we already can use ctid in the where clause on the latest > > > PostgreSQL, can't we? > > > > Oh, sorry, I missed the TidRangeScan. My apologies for the noise. > > > I made the ctidscan extension when we developed CustomScan API > towards v9.5 or v9.6, IIRC. It would make sense just an example of > CustomScan API (e.g, PG-Strom code is too large and complicated > to learn about this API), however, makes no sense from the standpoint > of the functionality. This patch has been moving from one CF to another CF without any discussion happening. It has been more than one year since any activity in this thread. I don't see there is much interest in this patch. I prefer to return this patch in this CF unless someone is interested in the patch and takes it forward. Regards, Vignesh
On Sun, 14 Jan 2024 at 11:21, vignesh C <vignesh21@gmail.com> wrote: > > On Fri, 2 Dec 2022 at 05:05, Kohei KaiGai <kaigai@heterodb.com> wrote: > > > > > > IIUC, we already can use ctid in the where clause on the latest > > > > PostgreSQL, can't we? > > > > > > Oh, sorry, I missed the TidRangeScan. My apologies for the noise. > > > > > I made the ctidscan extension when we developed CustomScan API > > towards v9.5 or v9.6, IIRC. It would make sense just an example of > > CustomScan API (e.g, PG-Strom code is too large and complicated > > to learn about this API), however, makes no sense from the standpoint > > of the functionality. > > This patch has been moving from one CF to another CF without any > discussion happening. It has been more than one year since any > activity in this thread. I don't see there is much interest in this > patch. I prefer to return this patch in this CF unless someone is > interested in the patch and takes it forward. Since the author or no one else showed interest in taking it forward and the patch had no activity for more than 1 year, I have changed the status to RWF. Feel free to add a new CF entry when someone is planning to work on this. Regards, Vignesh