Обсуждение: Freeze the inserted tuples during CTAS?

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

Freeze the inserted tuples during CTAS?

От
Paul Guo
Дата:
Here is the simple patch,

diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index dce882012e..0391699423 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -552,7 +552,7 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
    myState->rel = intoRelationDesc;
    myState->reladdr = intoRelationAddr;
    myState->output_cid = GetCurrentCommandId(true);
-   myState->ti_options = TABLE_INSERT_SKIP_FSM;
+   myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN;

MatView code already does this and COPY does this if specified. I’m not sure how
does the community think about this. Actually personally I expect more about the
all-visible setting due to TABLE_INSERT_FROZEN since I could easier use index only scan
if we create an index and table use CTAS, else people have to use index only scan
after vacuum. If people do not expect freeze could we at least introduce a option to
specify the visibility during inserting?

Regards,
Paul

Re: Freeze the inserted tuples during CTAS?

От
Darafei "Komяpa" Praliaskouski
Дата:
Hi,

I confirm that my analytic workflows often do the CTAS and VACUUM of the relation right after, before the index creation, to mark stuff as all-visible for IOS to work. Freezing and marking as visible will help.

On Wed, Jan 27, 2021 at 12:29 PM Paul Guo <guopa@vmware.com> wrote:
Here is the simple patch,

diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index dce882012e..0391699423 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -552,7 +552,7 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
    myState->rel = intoRelationDesc;
    myState->reladdr = intoRelationAddr;
    myState->output_cid = GetCurrentCommandId(true);
-   myState->ti_options = TABLE_INSERT_SKIP_FSM;
+   myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN;

MatView code already does this and COPY does this if specified. I’m not sure how
does the community think about this. Actually personally I expect more about the
all-visible setting due to TABLE_INSERT_FROZEN since I could easier use index only scan
if we create an index and table use CTAS, else people have to use index only scan
after vacuum. If people do not expect freeze could we at least introduce a option to
specify the visibility during inserting?

Regards,
Paul


--
Darafei "Komяpa" Praliaskouski
OSM BY Team - http://openstreetmap.by/

Re: Freeze the inserted tuples during CTAS?

От
Paul Guo
Дата:


On Jan 27, 2021, at 5:33 PM, Darafei Komяpa Praliaskouski <me@komzpa.net> wrote:

Hi,

I confirm that my analytic workflows often do the CTAS and VACUUM of the relation right after, before the index creation, to mark stuff as all-visible for IOS to work. Freezing and marking as visible will help.

Thanks for letting me know there is such a real case in production environment.
I attached the short patch. If no more other concerns, I will log the patch on commitfest.


-Paul


On Wed, Jan 27, 2021 at 12:29 PM Paul Guo <guopa@vmware.com> wrote:
Here is the simple patch,

diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index dce882012e..0391699423 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -552,7 +552,7 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
    myState->rel = intoRelationDesc;
    myState->reladdr = intoRelationAddr;
    myState->output_cid = GetCurrentCommandId(true);
-   myState->ti_options = TABLE_INSERT_SKIP_FSM;
+   myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN;

MatView code already does this and COPY does this if specified. I’m not sure how
does the community think about this. Actually personally I expect more about the
all-visible setting due to TABLE_INSERT_FROZEN since I could easier use index only scan
if we create an index and table use CTAS, else people have to use index only scan
after vacuum. If people do not expect freeze could we at least introduce a option to
specify the visibility during inserting?

Regards,
Paul


Вложения