Обсуждение: Asynchronous execution support for Custom Scan

Поиск
Список
Период
Сортировка

Asynchronous execution support for Custom Scan

От
Kazutaka Onishi
Дата:
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,

Вложения

Re: Asynchronous execution support for Custom Scan

От
Kazutaka Onishi
Дата:
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,

Вложения

Re: Asynchronous execution support for Custom Scan

От
Etsuro Fujita
Дата:
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



Re: Asynchronous execution support for Custom Scan

От
Kohei KaiGai
Дата:
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>



Re: Asynchronous execution support for Custom Scan

От
Etsuro Fujita
Дата:
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



Re: Asynchronous execution support for Custom Scan

От
Kazutaka Onishi
Дата:
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



Re: Asynchronous execution support for Custom Scan

От
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



Re: Asynchronous execution support for Custom Scan

От
Kazutaka Onishi
Дата:
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



Re: Asynchronous execution support for Custom Scan

От
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



Re: Asynchronous execution support for Custom Scan

От
Ronan Dunklau
Дата:
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





Re: Asynchronous execution support for Custom Scan

От
Kazutaka Onishi
Дата:
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
>
>

Вложения

Re: Asynchronous execution support for Custom Scan

От
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.

Best regards,

--
Ronan Dunklau







Re: Asynchronous execution support for Custom Scan

От
Kohei KaiGai
Дата:
> > 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>



Re: Asynchronous execution support for Custom Scan

От
vignesh C
Дата:
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



Re: Asynchronous execution support for Custom Scan

От
vignesh C
Дата:
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