Asynchronous execution support for Custom Scan

v1 patch occurs gcc warnings, I fixed it.

2022年8月13日(土) 22:42 Kazutaka Onishi <onishi@heterodb.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

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>:

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 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>:

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