回复：回复：Re: Re: 回复：Re: Is it worth pushing conditions to sublink/subplan?

[曾文旌(义从)]

Fixed a bug found during testing.


Wenjing



 --原始邮件 --
发件人:曾文旌(义从) 
发送时间:Sun Dec 12 20:51:08 2021
收件人:Zhihong Yu 
抄送:Tomas Vondra , wjzeng , 
PostgreSQL Hackers , shawn wang 
, ggys...@gmail.com 
主题:回复：Re: Re: 回复：Re: Is it worth pushing conditions to sublink/subplan?




 --原始邮件 --
发件人:Zhihong Yu 
发送时间:Sun Dec 12 01:13:11 2021
收件人:曾文旌(义从) 
抄送:Tomas Vondra , wjzeng , 
PostgreSQL Hackers , shawn wang 
, ggys...@gmail.com 
主题:Re: Re: 回复：Re: Is it worth pushing conditions to sublink/subplan?



On Sat, Dec 11, 2021 at 7:31 AM 曾文旌(义从)  wrote:




 --原始邮件 --
发件人:Tomas Vondra 
发送时间:Wed Dec 8 11:26:35 2021
收件人:曾文旌(义从) , shawn wang 
, ggys...@gmail.com , PostgreSQL 
Hackers 
抄送:wjzeng 
主题:Re: 回复：Re: Is it worth pushing conditions to sublink/subplan?

Hi,

On 12/7/21 10:44, 曾文旌(义从) wrote:
> Hi Hackers
> 
> For my previous proposal, I developed a prototype and passed
> regression testing. It works similarly to subquery's qual pushdown.
> We know that sublink expands at the beginning of each level of
> query. At this stage, The query's conditions and equivalence classes
> are not processed. But after generate_base_implied_equalities the
> conditions are processed,  which is why qual can push down to 
> subquery but sublink not.
> 
> My POC implementation chose to delay the sublink expansion in the
> SELECT clause (targetList) and where clause. Specifically, it is
> delayed after generate_base_implied_equalities. Thus, the equivalent
> conditions already established in the Up level query can be easily
> obtained in the sublink expansion process (make_subplan).
> 
> For example, if the up level query has a.id = 10 and the sublink
> query has a.id = b.id, then we get b.id = 10 and push it down to the
> sublink quey. If b is a partitioned table and is partitioned by id,
> then a large number of unrelated subpartitions are pruned out, This
> optimizes a significant amount of Planner and SQL execution time, 
> especially if the partitioned table has a large number of
> subpartitions and is what I want.
> 
> Currently, There were two SQL failures in the regression test,
> because the expansion order of sublink was changed, which did not
> affect the execution result of SQL.
> 
> Look forward to your suggestions on this proposal.
> 

I took a quick look, and while I don't see / can't think of any problems
with delaying it until after generating implied equalities, there seems
to be a number of gaps.

Thank you for your attention.

1) Are there any regression tests exercising this modified behavior?
Maybe there are, but if the only changes are due to change in order of
targetlist entries, that doesn't seem like a clear proof.

It'd be good to add a couple tests exercising both the positive and
negative case (i.e. when we can and can't pushdown a qual).

I added several samples to the regress(qual_pushdown_to_sublink.sql). 
and I used the partition table to show the plan status of qual being pushed 
down into sublink.
Hopefully this will help you understand the details of this patch. Later, I 
will add more cases.
2) apparently, contrib/postgres_fdw does crash like this:

  #3  0x0077b412 in adjust_appendrel_attrs_mutator
  (node=0x13f7ea0, context=0x7fffc3351b30) at appendinfo.c:470
  470  Assert(!IsA(node, SubLink));
  (gdb) p node
  $1 = (Node *) 0x13f7ea0
  (gdb) p *node
  $2 = {type = T_SubLink}

  Backtrace attached.

For the patch attached in the last email, I passed all the tests under 
src/test/regress.
As you pointed out, there was a problem with regression under contrib(in 
contrib/postgres_fdw). 
This time I fixed it and the current patch (V2) can pass the check-world.


3) various parts of the patch really need at least some comments, like:

  - try_push_outer_qual_to_sublink_query really needs some docs

  - new stuff at the end of initsplan.c

Ok, I added some comments and will add more. If you have questions about any 
details,
please point them out directly.

4) generate_base_implied_equalities

   shouldn't this

if (ec->ec_processed)
;

   really be?

if (ec->ec_processed)
continue;

You are right. I fixed it.

5) I'm not sure why we need the new ec_processed flag.

I did this to eliminate duplicate equalities from the two 
generate_base_implied_equalities calls
1) I need the base equivalent expression generated after 
generate_base_implied_equalities,
which is used to pushdown qual to sublink(lazy_process_sublinks)
2) The expansion of sublink may result in an equivalent expression with 
parameters, such as a = $1,
which needs to deal with the equivalence classes again.
3) So, I added ec_processed and asked to process it again 
(generate_base_implied_equalities)
after the equivalence class changed (add_eq_memb

回复：Re: Re: 回复：Re: Is it worth pushing conditions to sublink/subplan?

[曾文旌(义从)]

 --原始邮件 --
发件人:Zhihong Yu 
发送时间:Sun Dec 12 01:13:11 2021
收件人:曾文旌(义从) 
抄送:Tomas Vondra , wjzeng , 
PostgreSQL Hackers , shawn wang 
, ggys...@gmail.com 
主题:Re: Re: 回复：Re: Is it worth pushing conditions to sublink/subplan?



On Sat, Dec 11, 2021 at 7:31 AM 曾文旌(义从)  wrote:




 --原始邮件 --
发件人:Tomas Vondra 
发送时间:Wed Dec 8 11:26:35 2021
收件人:曾文旌(义从) , shawn wang 
, ggys...@gmail.com , PostgreSQL 
Hackers 
抄送:wjzeng 
主题:Re: 回复：Re: Is it worth pushing conditions to sublink/subplan?

Hi,

On 12/7/21 10:44, 曾文旌(义从) wrote:
> Hi Hackers
> 
> For my previous proposal, I developed a prototype and passed
> regression testing. It works similarly to subquery's qual pushdown.
> We know that sublink expands at the beginning of each level of
> query. At this stage, The query's conditions and equivalence classes
> are not processed. But after generate_base_implied_equalities the
> conditions are processed,  which is why qual can push down to 
> subquery but sublink not.
> 
> My POC implementation chose to delay the sublink expansion in the
> SELECT clause (targetList) and where clause. Specifically, it is
> delayed after generate_base_implied_equalities. Thus, the equivalent
> conditions already established in the Up level query can be easily
> obtained in the sublink expansion process (make_subplan).
> 
> For example, if the up level query has a.id = 10 and the sublink
> query has a.id = b.id, then we get b.id = 10 and push it down to the
> sublink quey. If b is a partitioned table and is partitioned by id,
> then a large number of unrelated subpartitions are pruned out, This
> optimizes a significant amount of Planner and SQL execution time, 
> especially if the partitioned table has a large number of
> subpartitions and is what I want.
> 
> Currently, There were two SQL failures in the regression test,
> because the expansion order of sublink was changed, which did not
> affect the execution result of SQL.
> 
> Look forward to your suggestions on this proposal.
> 

I took a quick look, and while I don't see / can't think of any problems
with delaying it until after generating implied equalities, there seems
to be a number of gaps.

Thank you for your attention.

1) Are there any regression tests exercising this modified behavior?
Maybe there are, but if the only changes are due to change in order of
targetlist entries, that doesn't seem like a clear proof.

It'd be good to add a couple tests exercising both the positive and
negative case (i.e. when we can and can't pushdown a qual).

I added several samples to the regress(qual_pushdown_to_sublink.sql). 
and I used the partition table to show the plan status of qual being pushed 
down into sublink.
Hopefully this will help you understand the details of this patch. Later, I 
will add more cases.
2) apparently, contrib/postgres_fdw does crash like this:

  #3  0x0077b412 in adjust_appendrel_attrs_mutator
  (node=0x13f7ea0, context=0x7fffc3351b30) at appendinfo.c:470
  470  Assert(!IsA(node, SubLink));
  (gdb) p node
  $1 = (Node *) 0x13f7ea0
  (gdb) p *node
  $2 = {type = T_SubLink}

  Backtrace attached.

For the patch attached in the last email, I passed all the tests under 
src/test/regress.
As you pointed out, there was a problem with regression under contrib(in 
contrib/postgres_fdw). 
This time I fixed it and the current patch (V2) can pass the check-world.


3) various parts of the patch really need at least some comments, like:

  - try_push_outer_qual_to_sublink_query really needs some docs

  - new stuff at the end of initsplan.c

Ok, I added some comments and will add more. If you have questions about any 
details,
please point them out directly.

4) generate_base_implied_equalities

   shouldn't this

if (ec->ec_processed)
;

   really be?

if (ec->ec_processed)
continue;

You are right. I fixed it.

5) I'm not sure why we need the new ec_processed flag.

I did this to eliminate duplicate equalities from the two 
generate_base_implied_equalities calls
1) I need the base equivalent expression generated after 
generate_base_implied_equalities,
which is used to pushdown qual to sublink(lazy_process_sublinks)
2) The expansion of sublink may result in an equivalent expression with 
parameters, such as a = $1,
which needs to deal with the equivalence classes again.
3) So, I added ec_processed and asked to process it again 
(generate_base_implied_equalities)
after the equivalence class changed (add_eq_member/process_equivalence).

Maybe you have a better suggestion, please let me know.

6) So we now have lazy_process_sublink callback? Does that mean we
expand sublinks in two places - sometimes lazily, sometimes not?

Yes, not all sublink is delayed. Let me explain this:
1) I added a GUC switch enable_lazy_p

回复：Re: 回复：Re: Is it worth pushing conditions to sublink/subplan?

[曾文旌(义从)]

 --原始邮件 --
发件人:Tomas Vondra 
发送时间:Wed Dec 8 11:26:35 2021
收件人:曾文旌(义从) , shawn wang 
, ggys...@gmail.com , PostgreSQL 
Hackers 
抄送:wjzeng 
主题:Re: 回复：Re: Is it worth pushing conditions to sublink/subplan?

Hi,

On 12/7/21 10:44, 曾文旌(义从) wrote:
> Hi Hackers
> 
> For my previous proposal, I developed a prototype and passed
> regression testing. It works similarly to subquery's qual pushdown.
> We know that sublink expands at the beginning of each level of
> query. At this stage, The query's conditions and equivalence classes
> are not processed. But after generate_base_implied_equalities the
> conditions are processed,  which is why qual can push down to 
> subquery but sublink not.
> 
> My POC implementation chose to delay the sublink expansion in the
> SELECT clause (targetList) and where clause. Specifically, it is
> delayed after generate_base_implied_equalities. Thus, the equivalent
> conditions already established in the Up level query can be easily
> obtained in the sublink expansion process (make_subplan).
> 
> For example, if the up level query has a.id = 10 and the sublink
> query has a.id = b.id, then we get b.id = 10 and push it down to the
> sublink quey. If b is a partitioned table and is partitioned by id,
> then a large number of unrelated subpartitions are pruned out, This
> optimizes a significant amount of Planner and SQL execution time, 
> especially if the partitioned table has a large number of
> subpartitions and is what I want.
> 
> Currently, There were two SQL failures in the regression test,
> because the expansion order of sublink was changed, which did not
> affect the execution result of SQL.
> 
> Look forward to your suggestions on this proposal.
> 

I took a quick look, and while I don't see / can't think of any problems
with delaying it until after generating implied equalities, there seems
to be a number of gaps.

Thank you for your attention.

1) Are there any regression tests exercising this modified behavior?
Maybe there are, but if the only changes are due to change in order of
targetlist entries, that doesn't seem like a clear proof.

It'd be good to add a couple tests exercising both the positive and
negative case (i.e. when we can and can't pushdown a qual).

I added several samples to the regress(qual_pushdown_to_sublink.sql). 
and I used the partition table to show the plan status of qual being pushed 
down into sublink.
Hopefully this will help you understand the details of this patch. Later, I 
will add more cases.
2) apparently, contrib/postgres_fdw does crash like this:

  #3  0x0077b412 in adjust_appendrel_attrs_mutator
  (node=0x13f7ea0, context=0x7fffc3351b30) at appendinfo.c:470
  470  Assert(!IsA(node, SubLink));
  (gdb) p node
  $1 = (Node *) 0x13f7ea0
  (gdb) p *node
  $2 = {type = T_SubLink}

  Backtrace attached.

For the patch attached in the last email, I passed all the tests under 
src/test/regress.
As you pointed out, there was a problem with regression under contrib(in 
contrib/postgres_fdw). 
This time I fixed it and the current patch (V2) can pass the check-world.


3) various parts of the patch really need at least some comments, like:

  - try_push_outer_qual_to_sublink_query really needs some docs

  - new stuff at the end of initsplan.c

Ok, I added some comments and will add more. If you have questions about any 
details,
please point them out directly.

4) generate_base_implied_equalities

   shouldn't this

if (ec->ec_processed)
;

   really be?

if (ec->ec_processed)
continue;

You are right. I fixed it.

5) I'm not sure why we need the new ec_processed flag.

I did this to eliminate duplicate equalities from the two 
generate_base_implied_equalities calls
1) I need the base equivalent expression generated after 
generate_base_implied_equalities,
which is used to pushdown qual to sublink(lazy_process_sublinks)
2) The expansion of sublink may result in an equivalent expression with 
parameters, such as a = $1,
which needs to deal with the equivalence classes again.
3) So, I added ec_processed and asked to process it again 
(generate_base_implied_equalities)
after the equivalence class changed (add_eq_member/process_equivalence).

Maybe you have a better suggestion, please let me know.

6) So we now have lazy_process_sublink callback? Does that mean we
expand sublinks in two places - sometimes lazily, sometimes not?

Yes, not all sublink is delayed. Let me explain this:
1) I added a GUC switch enable_lazy_process_sublink. If it is turned off, all 
lazy process sublink will not happen,
qual pushdown to sublink depend on lazy procee sublink, which means no quals 
will be pushed down.
2) Even  if enable_lazy_process_sublink = true If Query in this level contains 
some complex features,
sublink in this level quer

回复：Re: Is it worth pushing conditions to sublink/subplan?

[曾文旌(义从)]

Hi Hackers

For my previous proposal, I developed a prototype and passed regression testing.
It works similarly to subquery's qual pushdown. We know that sublink expands
at the beginning of each level of query. At this stage, The query's conditions 
and
equivalence classes are not processed. But after 
generate_base_implied_equalities
the conditions are processed,  which is why qual can push down to subquery but 
sublink not.

My POC implementation chose to delay the sublink expansion in the SELECT clause 
(targetList)
and where clause. Specifically, it is delayed after 
generate_base_implied_equalities. Thus,
the equivalent conditions already established in the Up level query can be 
easily obtained
in the sublink expansion process (make_subplan). 

For example, if the up level query has a.id = 10 and the sublink query has a.id 
= b.id, then
we get b.id = 10 and push it down to the sublink quey. If b is a partitioned 
table and is
partitioned by id, then a large number of unrelated subpartitions are pruned 
out, 
This optimizes a significant amount of Planner and SQL execution time, 
especially
if the partitioned table has a large number of subpartitions and is what I want.

Currently, There were two SQL failures in the regression test, because the 
expansion
order of sublink was changed, which did not affect the execution result of SQL.

Look forward to your suggestions on this proposal.

Thanks

Wenjing




 --原始邮件 --
发件人:shawn wang 
发送时间:Wed Sep 1 10:54:50 2021
收件人:曾文旌(义从) 
抄送:PostgreSQL Hackers , wjzeng 

主题:Re: Is it worth pushing conditions to sublink/subplan?

I tested it the way you said and increased the number of sub-tables.
I created a hash partition table of 1000 sub-tables.
Test according to your first SQL, the optimizer cuts the unnecessary sub-tables 
well.
You can see the plan:

postgres=# explain analyze
postgres-# select temp.p1 from
postgres-# (
postgres(# select p1,p2 from test1.test1hashtable x where x.p1 = '1'
postgres(# union all
postgres(# (values('1','1'))
postgres(# ) temp,
postgres-# test1.test1hashtable y
postgres-# where y.p2 = temp.p2 and y.p1 = '1' and y.p1='1';
 QUERY PLAN 
-
 Nested Loop (cost=0.00..25.55 rows=1 width=32) (actual time=0.004..0.004 
rows=0 loops=1)
 Join Filter: (x.p2 = y.p2)
 -> Seq Scan on test1hashtable826 y (cost=0.00..12.75 rows=1 width=32) (actual 
time=0.002..0.002 rows=0 loops=1)
 Filter: (p1 = '1'::text)
 -> Append (cost=0.00..12.78 rows=2 width=64) (never executed)
 -> Seq Scan on test1hashtable826 x (cost=0.00..12.75 rows=1 width=64) (never 
executed)
 Filter: (p1 = '1'::text)
 -> Result (cost=0.00..0.01 rows=1 width=64) (never executed)
 Planning Time: 0.158 ms
 Execution Time: 0.022 ms
(10 rows)

But when the second one runs, the planning time reaches 13.942ms.
The plan:

postgres=# explain analyze
postgres-# select
postgres-# y.p1,
postgres-# (Select x.p2 from test1.test1hashtable x where y.p1 =x.p1 and 
y.p2=x.p2) as b
postgres-# from test1.test1hashtable y where p1 = '1' and p2 = '1';
 QUERY PLAN 
--
 Seq Scan on test1hashtable826 y (cost=0.00..13318.30 rows=1 width=64) (actual 
time=0.004..0.047 rows=0 loops=1)
 Filter: ((p1 = '1'::text) AND (p2 = '1'::text))
 SubPlan 1
 -> Append (cost=0.00..13305.00 rows=1000 width=32) (never executed)
 -> Seq Scan on test1hashtable1 x_1 (cost=0.00..13.30 rows=1 width=32) (never 
executed)
 Filter: ((y.p1 = p1) AND (y.p2 = p2))
 -> Seq Scan on test1hashtable1000 x_1000 (cost=0.00..13.30 rows=1 width=32) 
(never executed)
 Filter: ((y.p1 = p1) AND (y.p2 = p2))
 Planning Time: 13.942 ms
 Execution Time: 4.899 ms
(2006 rows)

This is a very worthwhile thing to do. In a relatively large business system, a 
large number of partition tables and high concurrency are often used. If the 
planning time is too long, this will greatly affect the business.

regards,
Shawn.
Wenjing  于2021年8月17日周二 上午10:31写道：




2021年8月16日 17:15，Wenjing  写道：
Hi Hackers,

Recently, a issue has been bothering me, This is about conditional push-down in 
SQL.
I use cases from regression testing as an example.
I found that the conditions  (B =1)  can be pushed down into the subquery, 
However, it cannot be pushed down to sublink/subplan.
If a sublink/subplan clause contains a partition table, it can be useful to get 
the conditions for pruning.

So, is it worth pushing conditions to sublink/subplan?
Anybody have any ideas?


regards,
Wenjing


example:
create table p (a int, b int, c int) partition by list (a);
create table p1 partition of p for values in (1);
create table p2 partition of p for values in (2);
create table q (a int, b int, c int) 

Re: [Proposal] Global temporary tables

[曾文旌]

> 2021年3月29日 16:37，Pavel Stehule  写道：
> 
> 
> 
> ne 28. 3. 2021 v 15:07 odesílatel Andrew Dunstan  > napsal:
> 
> On 3/17/21 7:59 AM, wenjing wrote:
> > ok
> >
> > The cause of the problem is that the name of the dependent function
> > (readNextTransactionID) has changed. I fixed it.
> >
> > This patch(V43) is base on 9fd2952cf4920d563e9cea51634c5b364d57f71a
> >
> > Wenjing
> >
> >
> 
> I have fixed this patch so that
> 
> a) it applies cleanly
> 
> b) it uses project best practice for catalog Oid assignment.
> 
> However, as noted elsewhere it fails the recovery TAP test.
> 
> I also note this:
> 
> 
> diff --git a/src/test/regress/parallel_schedule
> b/src/test/regress/parallel_schedule
> index 312c11a4bd..d44fa62f4e 100644
> --- a/src/test/regress/parallel_schedule
> +++ b/src/test/regress/parallel_schedule
> @@ -129,3 +129,10 @@ test: fast_default
>  
>  # run stats by itself because its delay may be insufficient under heavy
> load
>  test: stats
> +
> +# global temp table test
> +test: gtt_stats
> +test: gtt_function
> +test: gtt_prepare
> +test: gtt_parallel_1 gtt_parallel_2
> +test: gtt_clean
> 
> 
> Tests that need to run in parallel should use either the isolation
> tester framework (which is explicitly for testing things concurrently)
> or the TAP test framework.
> 
> Adding six test files to the regression test suite for this one feature
> is not a good idea. You should have one regression test script ideally,
> and it should be added as appropriate to both the parallel and serial
> schedules (and not at the end). Any further tests should be added using
> the other frameworks mentioned.
> 
> 
> * bad name of GTT-README - the convention is README.gtt
> 
> * Typo - "ofa" 
> 
> 2) Use beforeshmemexit to ensure that all files ofa session GTT are deleted 
> when
> the session exits. 
> 
> * Typo "nd" 
> 
> 3) GTT storage file cleanup during abnormal situations
> When a backend exits abnormally (such as oom kill), the startup process starts
> recovery before accepting client connection. The same startup process checks
> nd removes all GTT files before redo WAL.
> 
> * This comment is wrong
> 
>   /*
> + * Global temporary table is allowed to be dropped only when the
> + * current session is using it.
> + */
> + if (RELATION_IS_GLOBAL_TEMP(rel))
> + {
> + if (is_other_backend_use_gtt(RelationGetRelid(rel)))
> + ereport(ERROR,
> + (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST),
> + errmsg("cannot drop global temporary table %s when other backend attached 
> it.",
> + RelationGetRelationName(rel;
> + }
> 
> * same wrong comment
> 
>   /*
> + * Global temporary table is allowed to be dropped only when the
> + * current session is using it.
> + */
> + if (RELATION_IS_GLOBAL_TEMP(rel))
> + {
> + if (is_other_backend_use_gtt(RelationGetRelid(rel)))
> + ereport(ERROR,
> + (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST),
> + errmsg("cannot drop global temporary table %s when other backend attached 
> it.",
> + RelationGetRelationName(rel;
> + }
> 
> * typo "backand"
> 
> +/*
> + * Check if there are other backends using this GTT besides the current 
> backand.
> + */
> 
> There is not user's documentation
This is necessary, and I will make a separate document patch.


Wenjing.


> 
> Regards
> 
> Pavel
> 
>  
> 
> cheers
> 
> 
> andrew
> 
> 
> -- 
> 
> Andrew Dunstan
> EDB: https://www.enterprisedb.com 
> 



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2021年3月28日 21:07，Andrew Dunstan  写道：
> 
> 
> On 3/17/21 7:59 AM, wenjing wrote:
>> ok
>> 
>> The cause of the problem is that the name of the dependent function
>> (readNextTransactionID) has changed. I fixed it.
>> 
>> This patch(V43) is base on 9fd2952cf4920d563e9cea51634c5b364d57f71a
>> 
>> Wenjing
>> 
>> 
> 
> I have fixed this patch so that
> 
> a) it applies cleanly
> 
> b) it uses project best practice for catalog Oid assignment.
> 
> However, as noted elsewhere it fails the recovery TAP test.
> 
> I also note this:
> 
> 
> diff --git a/src/test/regress/parallel_schedule
> b/src/test/regress/parallel_schedule
> index 312c11a4bd..d44fa62f4e 100644
> --- a/src/test/regress/parallel_schedule
> +++ b/src/test/regress/parallel_schedule
> @@ -129,3 +129,10 @@ test: fast_default
>  
>  # run stats by itself because its delay may be insufficient under heavy
> load
>  test: stats
> +
> +# global temp table test
> +test: gtt_stats
> +test: gtt_function
> +test: gtt_prepare
> +test: gtt_parallel_1 gtt_parallel_2
> +test: gtt_clean
> 
> 
> Tests that need to run in parallel should use either the isolation
> tester framework (which is explicitly for testing things concurrently)
> or the TAP test framework.
> 
> Adding six test files to the regression test suite for this one feature
> is not a good idea. You should have one regression test script ideally,
> and it should be added as appropriate to both the parallel and serial
> schedules (and not at the end). Any further tests should be added using
> the other frameworks mentioned.
You're right, it doesn't look good.
I'll organize them and put them in place.


Wenjing.

> 
> 
> cheers
> 
> 
> andrew
> 
> 
> -- 
> 
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
> 
> 



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2021年3月28日 15:27，Pavel Stehule  写道：
> 
> Hi
> 
> st 17. 3. 2021 v 12:59 odesílatel wenjing  > napsal:
> ok
> 
> The cause of the problem is that the name of the dependent function 
> (readNextTransactionID) has changed. I fixed it.
> 
> This patch(V43) is base on 9fd2952cf4920d563e9cea51634c5b364d57f71a
> 
> Wenjing
> 
> I tested this patch and make check-world fails
> 
> make[2]: Vstupuje se do adresáře 
> „/home/pavel/src/postgresql.master/src/test/recovery“
> rm -rf '/home/pavel/src/postgresql.master/src/test/recovery'/tmp_check
> /usr/bin/mkdir -p 
> '/home/pavel/src/postgresql.master/src/test/recovery'/tmp_check
> cd . && TESTDIR='/home/pavel/src/postgresql.master/src/test/recovery' 
> PATH="/home/pavel/src/postgresql.master/tmp_install/usr/local/pgsql/master/bin:$PATH"
>  
> LD_LIBRARY_PATH="/home/pavel/src/postgresql.master/tmp_install/usr/local/pgsql/master/lib"
>   PGPORT='65432' 
> PG_REGRESS='/home/pavel/src/postgresql.master/src/test/recovery/../../../src/test/regress/pg_regress'
>  
> REGRESS_SHLIB='/home/pavel/src/postgresql.master/src/test/regress/regress.so' 
> /usr/bin/prove -I ../../../src/test/perl/ -I .  t/*.pl
> t/001_stream_rep.pl  .. ok 
> t/002_archiving.pl  ... ok   
> t/003_recovery_targets.pl   ok   
> t/004_timeline_switch.pl  . ok   
> t/005_replay_delay.pl   ok   
> t/006_logical_decoding.pl   ok   
>   
> t/007_sync_rep.pl   ok 
> t/008_fsm_truncation.pl  .. ok   
> t/009_twophase.pl   ok 
> t/010_logical_decoding_timelines.pl 
>  .. ok 
> t/011_crash_recovery.pl  .. ok   
> t/012_subtransactions.pl  . ok
>  
> t/013_crash_restart.pl  ... ok 
> t/014_unlogged_reinit.pl  . ok
>  
> t/015_promotion_pages.pl  . ok   
> t/016_min_consistency.pl  . ok   
> t/017_shm.pl  . skipped: SysV 
> shared memory not supported by this platform
> t/018_wal_optimize.pl   ok 
> t/019_replslot_limit.pl  .. ok 
> t/020_archive_status.pl  .. ok 
> t/021_row_visibility.pl  .. ok 
> t/022_crash_temp_files.pl   1/9 
> #   Failed test 'one temporary file'
> #   at t/022_crash_temp_files.pl  line 231.
> #  got: '0'
> # expected: '1'
> t/022_crash_temp_files.pl   9/9 
> # Looks like you failed 1 test of 9.
> t/022_crash_temp_files.pl   
> Dubious, test returned 1 (wstat 256, 0x100)
> Failed 1/9 subtests 
> t/023_pitr_prepared_xact.pl  .. ok 
>   
> 
> Test Summary Report
> ---
> t/022_crash_temp_files.pl   (Wstat: 
> 256 Tests: 9 Failed: 1)
>   Failed test:  8
>   Non-zero exit status: 1
> Files=23, Tests=259, 115 wallclock secs ( 0.21 usr  0.06 sys + 28.57 cusr 
> 18.01 csys = 46.85 CPU)
> Result: FAIL
> make[2]: *** [Makefile:19: check] Chyba 1
> make[2]: Opouští se adresář 
> „/home/pavel/src/postgresql.master/src/test/recovery“
> make[1]: *** [Makefile:49: check-recovery-recurse] Chyba 2
> make[1]: Opouští se adresář „/home/pavel/src/postgresql.master/src/test“
> make: *** [GNUmakefile:71: check-world-src/test-recurse] Chyba 2

This is because part of the logic of GTT is duplicated with the new commid  
cd91de0d17952b5763466cfa663e98318f26d357
that is commit by Tomas Vondra merge 11 days ago: "Remove Temporary Files after 
Backend Crash”.
The "Remove Temporary Files after Backend Crash” is exactly what GTT needs, or 
even better.
Therefore, I chose to delete the temporary file cleanup logic in the GTT path.

Let me update a new version.


Wenjing

> 
> Regards
> 
> Pavel



smime.p7s
Description: S/MIME cryptographic signature

Re: Proposal: Global Index

[曾文旌]

> 2021年1月12日 02:37，Robert Haas  写道：
> 
> On Mon, Jan 11, 2021 at 12:46 PM Bruce Momjian  wrote:
>>> For 1) The DETACH old child table can be finished immediately, global index 
>>> can be kept valid after DETACH is completed, and the cleanup of garbage 
>>> data in global index can be deferred to VACUUM.
>>> This is similar to the global index optimization done by Oracle12c.
>>> For 2) ATTACH new empty child table can also be completed immediately.
>>> If this is the case, many of the advantages of partitioned tables will be 
>>> retained, while the advantages of global indexes will be gained.
>> 
>> Yes, we can keep the index rows for the deleted partition and clean them
>> up later, but what is the advantage of partitioning then?  Just heap
>> deletion quickly?  Is that enough of a value?
> 
> I actually think the idea of lazily deleting the index entries is
> pretty good, but it won't work if the way the global index is
> implemented is by adding a tableoid column. Because then, I might
> detach a partition and later reattach it and the old index entries are
> still there but the table contents might have changed. Worse yet, the
> table might be dropped and the table OID reused for a completely
> unrelated table with completely unrelated contents, which could then
> be attached as a new partition.
> 
> One of the big selling points of global indexes is that they allow you
> to enforce uniqueness on a column unrelated to the partitioning
> column. Another is that you can look up a value by doing a single
> index scan on the global index rather than an index scan per
> partition. Those things are no less valuable for performing index
> deletion lazily.
> 
> However, there is a VACUUM amplification effect to worry about here
> which Wenjing seems not to be considering. Suppose I have a table
> which is not partitioned and it is 1TB in size with an index that is
> 128GB in size. To vacuum the table, I need to do 1TB + 128GB of I/O.
> Now, suppose I now partition the table into 1024 partitions each with
> its own local index. Each partition is 1GB in size and the index on
> each partition is 128MB in size. To vacuum an individual partition
> requires 1GB + 128MB of I/O, so to vacuum all the partitions requires
> the same amount of total I/O as before. But, now suppose that I have a
> single global index instead of a local index per partition. First, how
> big will that index be? It will not be 128GB, but somewhat bigger,
> because it needs extra space for every indexed tuple. Let's say 140GB.
> Furthermore, it will need to be vacuumed whenever any child is
> vacuumed, because it contains some index entries from every child. So
> the total I/O to vacuum all partitions is now 1GB * 1024 + 140GB *
> 1024 = 141TB, which is a heck of a lot worse than the 1.125TB I
> required with the unpartitioned table or the locally partitioned
> table.
Thank you for pointing this out.
It seems that some optimization can be done, but there is no good way
to completely eliminate the vacuum amplification effect of the global index.
Maybe we can only count on Zheap, which doesn't need to do Vaccum.



> 
> That's not necessarily a death sentence for every use case, but it's
> going to be pretty bad for tables that are big and heavily updated.
> 
> -- 
> Robert Haas
> EDB: http://www.enterprisedb.com



smime.p7s
Description: S/MIME cryptographic signature

Re: Proposal: Global Index

[曾文旌]

> 2021年1月7日 23:04，Robert Haas  写道：
> 
> On Thu, Jan 7, 2021 at 4:44 AM 曾文旌  wrote:
>> I've been following this topic for a long time. It's been a year since the 
>> last response.
>> It was clear that our customers wanted this feature as well, and a large 
>> number of them mentioned it.
>> 
>> So, I wish the whole feature to mature as soon as possible.
>> I summarized the scheme mentioned in the email and completed the POC 
>> patch(base on PG_13).
> 
> You need to summarize the basic design choices you've made here. Like,
> what did you do about the fact that TIDs have to be 6 bytes?

These are the basic choices, and most of them come from discussions in previous 
emails.

1 Definition of global index
Obviously, we need to expand Index address info(CTID) to include child table 
info in GlobalIndexTuple.

1.1 As mentioned in the previous email, Bruce suggested having the OID
instead of relfilenode as relfilenode can be duplicated across tablespaces. 
I agree with that.

1.2 And Heikki pointed me to include heap specific information using the 
INCLUDE keyword so that heap information
is stored with each index node as data.

So ,In POC stage, I choose use INCLUDE keyword to INCLUDE the tableoid of 
global index. This will add 4 bytes to each IndexTuple.

Considering that if a single partitioned table does not exceed 65535 child 
tables, perhaps two bytes for tracking which child table the data belongs to is 
sufficient.

2. Maintenance of global index by partition table DML.
The DML of each child table of the partitioned table needs to maintain the 
global index on the partitioned table.

3. Global index scan
Planner: 
Processes predicate on the primary partition, generating paths and plans for 
the global index.
The cost model of the global index may need to be considered. We need to make 
the global index or the local index selected in their respective advantageous 
scenarios.

Executer: 
The index scan get indextup, get the tableoid from indextup, and verify the 
visibility of the data in the child table.
If a child table is DETACH, then the index item of this table is ignored during 
the index scan until VACUUM finishes cleaning up the global index.

4. Vacuum partition table maintains global index.
Old data in the global index also needs to be cleaned up, and vaccum is 
suitable for it.
Each child table in VACUUM, while vacuuming its own index, also vacuums the 
global index on the partitioned table.

5. Other
The global index indexes all of the child tables, which makes the global index 
large and has many levels. 
Follow the technical route, The partitioned indexes are a further target.

This is my basic idea for implementing global index.
Looking forward to your feedback.

Thanks!

Wenjing

> 
> -- 
> Robert Haas
> EDB: http://www.enterprisedb.com



smime.p7s
Description: S/MIME cryptographic signature

Re: Proposal: Global Index

[曾文旌]

> 2021年1月8日 16:26，Julien Rouhaud  写道：
> 
> On Fri, Jan 8, 2021 at 4:02 PM 曾文旌  wrote:
>> 
>>> 2021年1月7日 22:16，Bruce Momjian  写道：
>>> 
>>> On Thu, Jan  7, 2021 at 05:44:01PM +0800, 曾文旌 wrote:
>>>> I've been following this topic for a long time. It's been a year since the 
>>>> last response.
>>>> It was clear that our customers wanted this feature as well, and a large 
>>>> number of them mentioned it.
>>>> 
>>>> So, I wish the whole feature to mature as soon as possible.
>>>> I summarized the scheme mentioned in the email and completed the POC 
>>>> patch(base on PG_13).
>>> 
>>> I think you need to address the items mentioned in this blog, and the
>>> email link it mentions:
>>> 
>>>  https://momjian.us/main/blogs/pgblog/2020.html#July_1_2020
>> 
>> Thank you for your reply.
>> I read your blog and it helped me a lot.
>> 
>> The blog mentions a specific problem: "A large global index might also 
>> reintroduce problems that prompted the creation of partitioning in the first 
>> place. "
>> I don't quite understand, could you give some specific information?
>> 
>> In addition you mentioned: "It is still unclear if these use-cases justify 
>> the architectural changes needed to enable global indexes."
>> Please also describe the problems you see, I will confirm each specific 
>> issue one by one.
> 
> One example is date partitioning.  People frequently need to store
> only the most recent data.  For instance doing a monthly partitioning
> and dropping the oldest partition every month once you hit the wanted
> retention is very efficient for that use case, as it should be almost
> instant (provided that you can acquire the necessary locks
> immediately).  But if you have a global index, you basically lose the
> advantage of partitioning as it'll require heavy changes on that
> index.
If the global index removes all the major benefits of partitioned tables, then 
it is not worth having it.

This is indeed a typical scenario for a partitioned table.
there are two basic operations
1) Monthly DETACH old child table
2) Monthly ATTACH new child table

For 1) The DETACH old child table can be finished immediately, global index can 
be kept valid after DETACH is completed, and the cleanup of garbage data in 
global index can be deferred to VACUUM.
This is similar to the global index optimization done by Oracle12c.
For 2) ATTACH new empty child table can also be completed immediately.
If this is the case, many of the advantages of partitioned tables will be 
retained, while the advantages of global indexes will be gained.



smime.p7s
Description: S/MIME cryptographic signature

Re: Proposal: Global Index

[曾文旌]

> 2021年1月7日 22:16，Bruce Momjian  写道：
> 
> On Thu, Jan  7, 2021 at 05:44:01PM +0800, 曾文旌 wrote:
>> I've been following this topic for a long time. It's been a year since the 
>> last response.
>> It was clear that our customers wanted this feature as well, and a large 
>> number of them mentioned it.
>> 
>> So, I wish the whole feature to mature as soon as possible.
>> I summarized the scheme mentioned in the email and completed the POC 
>> patch(base on PG_13).
> 
> I think you need to address the items mentioned in this blog, and the
> email link it mentions:
> 
>   https://momjian.us/main/blogs/pgblog/2020.html#July_1_2020

Thank you for your reply.
I read your blog and it helped me a lot.

The blog mentions a specific problem: "A large global index might also 
reintroduce problems that prompted the creation of partitioning in the first 
place. "
I don't quite understand, could you give some specific information?

In addition you mentioned: "It is still unclear if these use-cases justify the 
architectural changes needed to enable global indexes."
Please also describe the problems you see, I will confirm each specific issue 
one by one.


Thanks

Wenjing


> 
> I am not clear this is a feature we will want.  Yes, people ask for it,
> but if the experience will be bad for them and they will regret using
> it, I am not sure we want it.  Of course, if you code it up and we get
> a good user experience, we would want it --- I am just saying it is not
> clear right now.
> 
> -- 
>  Bruce Momjian  https://momjian.us
>  EnterpriseDB https://enterprisedb.com
> 
>  The usefulness of a cup is in its emptiness, Bruce Lee



smime.p7s
Description: S/MIME cryptographic signature

Re: Proposal: Global Index

[曾文旌]

I've been following this topic for a long time. It's been a year since the last response.It was clear that our customers wanted this feature as well, and a large number of them mentioned it.So, I wish the whole feature to mature as soon as possible.I summarized the scheme mentioned in the email and completed the POC patch(base on PG_13).Next, I encountered some difficulties when implementing the DDL of the partition table with global index, and I hope to get some help from the communityHere are some details what has been implemented1 Definition of global indexUsing the INCLUDE keyword to include the tableoid of the partitioned table.2. Maintenance of global index by partition table DML.Both INSERT and UPDATE of a partitioned table maintain global index3. Global index scanPlanner: Processes predicate conditions on the primary partition, generating paths and plans for the global index.Executer: index scan get indextup, get the tableoid from indextup, and verify the visibility of the data in the partition.4. Vacuum partition table maintains global indexEach partitioned table VACUUM cleans its own garbage data in the global index.After the above function point is completed, the global index can be used without partition table DDL.Demo:--Use pgbench to create the test partition tablepgbench -i -s 1000 --partitions=6 --partition-method=range—- create global index on bid, bid is not partition keyCREATE INDEX  idx_pgbench_accounts_bid on pgbench_accounts(bid) global;— check global index statusselect * , sum(alivetup) over()as sum_alivetup, sum(deadtup) over() as sum_deadtup from bt_get_global_index_status('idx_pgbench_accounts_bid');      relname       | alivetup | deadtup | sum_alivetup | sum_deadtup +--+-+--+- pgbench_accounts_1 | 1667 |       0 |    1 |           0 pgbench_accounts_2 | 1667 |       0 |    1 |           0 pgbench_accounts_3 | 1667 |       0 |    1 |           0 pgbench_accounts_4 | 1667 |       0 |    1 |           0 pgbench_accounts_5 | 1667 |       0 |    1 |           0 pgbench_accounts_6 | 1665 |       0 |    1 |           0(6 rows)— run pgbench for for a whilepgbench -M prepared  -j 32 -c 32 -T 60 -P1—- check global index, The index has bloatedpostgres=# select * , sum(alivetup) over()as sum_alivetup, sum(deadtup) over() as sum_deadtup from bt_get_global_index_status('idx_pgbench_accounts_bid');      relname       | alivetup | deadtup | sum_alivetup | sum_deadtup +--+-+--+- pgbench_accounts_1 | 16717733 |       0 |    100306102 |           0 pgbench_accounts_2 | 16717409 |       0 |    100306102 |           0 pgbench_accounts_3 | 16717540 |       0 |    100306102 |           0 pgbench_accounts_4 | 16717972 |       0 |    100306102 |           0 pgbench_accounts_5 | 16717578 |       0 |    100306102 |           0 pgbench_accounts_6 | 16717870 |       0 |    100306102 |           0(6 rows)—- vacuum partition tablevacuum pgbench_accounts;—- Garbage is collected, global index still looks correct and valid.postgres=# select * , sum(alivetup) over()as sum_alivetup, sum(deadtup) over() as sum_deadtup from bt_get_global_index_status('idx_pgbench_accounts_bid');      relname       | alivetup | deadtup | sum_alivetup | sum_deadtup +--+-+--+- pgbench_accounts_1 | 1667 |       0 |    1 |           0 pgbench_accounts_2 | 1667 |       0 |    1 |           0 pgbench_accounts_3 | 1667 |       0 |    1 |           0 pgbench_accounts_4 | 1667 |       0 |    1 |           0 pgbench_accounts_5 | 1667 |       0 |    1 |           0 pgbench_accounts_6 | 1665 |       0 |    1 |           0(6 rows)—-—- global index scan works wellpostgres=# select tableoid ,count(*) from pgbench_accounts where bid = 834 group by tableoid; tableoid | count --+---    16455 | 5    16458 | 5(2 rows)postgres=# explain select tableoid ,count(*) from pgbench_accounts where bid = 834 group by tableoid;                                                     QUERY PLAN                                                      HashAggregate  (cost=2945.23..2945.24 rows=1 width=12)   Group Key: pgbench_accounts.tableoid   ->  Global Index Scan using idx_pgbench_accounts_bid on pgbench_accounts  (cost=0.50..10.18 rows=587011 width=4)         Index Cond: (bid = 834)(4 rows)The following is how to implement DDL of global index. How to maintain global index of DDL of partitioned table.This seems to be more difficult than the previous work.I understand there are four main parts1 Build global index or reindex, especially in concurrent mode2 Detach partitionWould it be a good idea to make a flag to global 

Re: [Proposal] Global temporary tables

[曾文旌]

I found that the new Patch mail failed to register to Commitfest
https://commitfest.postgresql.org/28/2349/# 
<https://commitfest.postgresql.org/28/2349/#>
I don't know what's wrong and how to check it?
Could you help me figure it out?



> 2020年11月25日 14:19，Pavel Stehule  写道：
> 
> 
> 
> po 23. 11. 2020 v 10:27 odesílatel 曾文旌  <mailto:wenjing@alibaba-inc.com>> napsal:
> 
> 
>> 2020年11月21日 02:28，Pavel Stehule > <mailto:pavel.steh...@gmail.com>> 写道：
>> 
>> Hi
>> 
>> pá 11. 9. 2020 v 17:00 odesílatel 曾文旌 > <mailto:wenjing@alibaba-inc.com>> napsal:
>> I have written the README for the GTT, which contains the GTT requirements 
>> and design.
>> I found that compared to my first email a year ago, many GTT Limitations are 
>> now gone.
>> Now, I'm adding comments to some of the necessary functions.
>> 
>> There are problems with patching. Please, can you rebase your patch?
> Sure.
> I'm still working on sort code and comments.
> If you have any suggestions, please let me know.
> 
> It is broken again 
> 
> There is bad white space
> 
> +   /*
> +* For global temp table only
> +* use ShareUpdateExclusiveLock for ensure safety
> +*/
> +   {
> +   {
> +   "on_commit_delete_rows",
> +   "global temp table on commit options",
> +   RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
> +   ShareUpdateExclusiveLock
> +   },
> +   true
> +   },  <=
> /* list terminator */
> {{NULL}}
> 
> +7 OTHERS
> +Parallel query
> +Planner does not produce parallel query plans for SQL related to GTT. 
> Because <=
> +GTT private data cannot be accessed across processes.
> diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
> 
> 
> +/*
> + * Update global temp table relstats(relpage/reltuple/relallvisible) 
> <
> + * to local hashtable
> + */
> +void
> 
> +/*
> + * Search global temp table relstats(relpage/reltuple/relallvisible) 
> <==
> + * from lo
> 
> and there are lot of more places ...
> 
> I found other issue
> 
> postgres=# create global temp table foo(a int);
> CREATE TABLE
> postgres=# create index on foo(a);
> CREATE INDEX
> 
> close session and in new session
> 
> postgres=# reindex index foo_a_idx ;
> WARNING:  relcache reference leak: relation "foo" not closed
> REINDEX
> 
> Regards
> 
> Pavel
> 
> 
> 
> 
> Wenjing
> 
> 
>> 
>> Regards
>> 
>> Pavel
>> 
>> 
>> 
>> Wenjing
>> 
>> 
>> 
>> 
>> 
>> > 2020年7月31日 上午4:57，Robert Haas > > <mailto:robertmh...@gmail.com>> 写道：
>> > 
>> > On Thu, Jul 30, 2020 at 8:09 AM wenjing zeng > > <mailto:wjzeng2...@gmail.com>> wrote:
>> >> Please continue to review the code.
>> > 
>> > This patch is pretty light on comments. Many of the new functions have
>> > no header comments, for example. There are comments here and there in
>> > the body of the new functions that are added, and in places where
>> > existing code is changed there are comments here and there, but
>> > overall it's not a whole lot. There's no documentation and no README,
>> > either. Since this adds a new feature and a bunch of new SQL-callable
>> > functions that interact with that feature, the feature itself should
>> > be documented, along with its limitations and the new SQL-callable
>> > functions that interact with it. I think there should be either a
>> > lengthy comment in some suitable file, or maybe various comments in
>> > various files, or else a README file, that clearly sets out the major
>> > design principles behind the patch, and explaining also what that
>> > means in terms of features and limitations. Without that, it's really
>> > hard for anyone to jump into reviewing this code, and it will be hard
>> > for people who have to maintain it in the future to understand it,
>> > either. Or for users, for that matter.
>> > 
>> > -- 
>> > Robert Haas
>> > EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>
>> > The Enterprise PostgreSQL Company



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年7月31日 上午4:57，Robert Haas  写道：
> 
> On Thu, Jul 30, 2020 at 8:09 AM wenjing zeng  wrote:
>> Please continue to review the code.
> 
> This patch is pretty light on comments. Many of the new functions have
> no header comments, for example. There are comments here and there in
> the body of the new functions that are added, and in places where
> existing code is changed there are comments here and there, but
> overall it's not a whole lot. There's no documentation and no README,
> either. Since this adds a new feature and a bunch of new SQL-callable
> functions that interact with that feature, the feature itself should
> be documented, along with its limitations and the new SQL-callable
> functions that interact with it. I think there should be either a
> lengthy comment in some suitable file, or maybe various comments in
> various files, or else a README file, that clearly sets out the major
> design principles behind the patch, and explaining also what that
> means in terms of features and limitations. Without that, it's really
> hard for anyone to jump into reviewing this code, and it will be hard
> for people who have to maintain it in the future to understand it,
> either. Or for users, for that matter.
Your suggestion is to the point. I do lack a lot of comments, as is necessary.
I'll do this.


Wenjing



> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

Thank you very much for reviewing this patch.
This is very important to improve the GTT.

> 2020年8月3日 下午3:09，movead...@highgo.ca 写道：
> 
> 
> >Fixed in global_temporary_table_v29-pg13.patch
> >Please check.
> 
> I find this is the most latest mail with an attachment, so I test and reply on
> this thread, several points as below:
> 
> 1. I notice it produces new relfilenode when new session login and some
> data insert. But the relfilenode column in pg_class still the one when create
> the global temp table. I think you can try to show 0 in this area as what nail
> relation does. 
I think getting the GTT to have a default relfilenode looks closer to the 
existing implementation, and setting it to 0 requires extra work and has no 
clear benefit.
What do you think？
I'd like to know the reasons for your suggestion.

> 
> 2. The nail relations handle their relfilenodes by RelMapFile struct, and this
> patch use hash entry and relfilenode_list, maybe RelMapFile approach more
> understandable in my opinion. Sorry if I miss the real design for that.
We can see the STORAGE and statistics info for the GTT, including relfilenode, 
through view pg_gtt_relstats

postgres=# \d gtt
Table "public.gtt"
 Column |  Type   | Collation | Nullable | Default 
+-+---+--+-
 a  | integer |   |  | 
 b  | integer |   |  | 

postgres=# insert into gtt values(1,1);
INSERT 0 1
postgres=# select * from pg_gtt_relstats ;
 schemaname | tablename | relfilenode | relpages | reltuples | relallvisible | 
relfrozenxid | relminmxid 
+---+-+--+---+---+--+
 public | gtt   |   16384 |0 | 0 | 0 |  
532 |  1
(1 row)

postgres=# truncate gtt;
TRUNCATE TABLE
postgres=# select * from pg_gtt_relstats ;
 schemaname | tablename | relfilenode | relpages | reltuples | relallvisible | 
relfrozenxid | relminmxid 
+---+-+--+---+---+--+
 public | gtt   |   16387 |0 | 0 | 0 |  
533 |  1
(1 row)

> 
> 3. I get a wrong result of pg_relation_filepath() function for global temp 
> table,
> I think it's necessaryto keep this an correct output.

postgres=# select pg_relation_filepath(oid) from pg_class where relname = 'gtt';
 pg_relation_filepath 
--
 base/13835/t3_16384
(1 row)

I didn't find anything wrong. Could you please give me a demo.

> 
> 4. In gtt_search_by_relid() function, it has not handle the missing_ok 
> argument
> if gtt_storage_local_hash is null. There should be some comments if it's the 
> right
> code.
This is a problem that has been fixed in global_temporary_table_v34-pg13.patch.

> 
> 5. It's a long patch and hard to review, I think it will pretty good if it 
> can be
> divided into several subpatches with relatively independent subfunctions.
Thank you for your suggestion, and I am considering doing so, including adding 
comments.


Wenjing

> 
> Regards,
> Highgo Software (Canada/China/Pakistan) 
> URL : www.highgo.ca  
> EMAIL: mailto:movead(dot)li(at)highgo(dot)ca



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年6月9日 下午8:15，Prabhat Sahu  写道：
> 
> 
> 
> On Wed, Apr 29, 2020 at 8:52 AM 曾文旌  <mailto:wenjing@alibaba-inc.com>> wrote:
>> 2020年4月27日 下午9:48，Prabhat Sahu > <mailto:prabhat.s...@enterprisedb.com>> 写道：
>> 
>> Thanks Wenjing, for the fix patch for previous issues.
>> I have verified the issues, now those fix look good to me.
>> But the below error message is confusing(for gtt2).
>> 
>> postgres=# drop table gtt1;
>> ERROR:  cannot drop global temp table gtt1 when other backend attached it.
>> 
>> postgres=# drop table gtt2;
>> ERROR:  cannot drop index idx2 on global temp table gtt2 when other backend 
>> attached it.
>> 
>> I feel the above error message shown for "DROP TABLE gtt2;" is a bit 
>> confusing(looks similar to DROP INDEX gtt2;).
>> If possible, can we keep the error message simple as "ERROR:  cannot drop 
>> global temp table gtt2 when other backend attached it."?
>> I mean, without giving extra information for the index attached to that GTT.
> Fixed the error message to make the expression more accurate. In v33.
>  
> Thanks Wenjing. We verified your latest patch(gtt_v33) focusing on all 
> reported issues and they work fine. 
> Thanks.
> -- 

I'm very glad to hear such good news.
I am especially grateful for your professional work on GTT.
Please feel free to let me know if there is anything you think could be 
improved.


Thanks.


Wenjing

> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年4月29日 下午7:46，tushar  写道：
> 
> On 4/29/20 8:52 AM, 曾文旌 wrote:
>> Fixed the error message to make the expression more accurate. In v33.
> 
> Thanks wenjing
> 
> Please refer this scenario  , where getting an error while performing cluster 
> o/p
> 
> 1)
> 
> X terminal -
> 
> postgres=# create global temp table f(n int);
> CREATE TABLE
> 
> Y Terminal -
> 
> postgres=# create index index12 on f(n);
> CREATE INDEX
> postgres=# \q
> 
> X terminal -
> 
> postgres=# reindex index  index12;
> REINDEX
> postgres=#  cluster f using index12;
> ERROR:  cannot cluster on invalid index "index12"
> postgres=# drop index index12;
> DROP INDEX
> 
> if this is an expected  , could we try  to make the error message more 
> simpler, if possible.
> 
> Another issue  -
> 
> X terminal -
> 
> postgres=# create global temp table f11(n int);
> CREATE TABLE
> postgres=# create index ind1 on f11(n);
> CREATE INDEX
> postgres=# create index ind2 on f11(n);
> CREATE INDEX
> postgres=#
> 
> Y terminal -
> 
> postgres=# drop table f11;
> ERROR:  cannot drop index ind2 or global temporary table f11
> HINT:  Because the index is created on the global temporary table and other 
> backend attached it.
> postgres=#
> 
> it is only mentioning about ind2 index but what about ind1 and what if  - 
> they have lots of indexes ?
> i  think - we should not mix index information while dropping the table and 
> vice versa.
postgres=# drop index index12;
ERROR:  cannot drop index index12 or global temporary table f
HINT:  Because the index is created on the global temporary table and other 
backend attached it.

postgres=# drop table f;
ERROR:  cannot drop index index12 or global temporary table f
HINT:  Because the index is created on the global temporary table and other 
backend attached it.
postgres=#

Dropping an index on a GTT and dropping a GTT with an index can both trigger 
this message, so the message looks like this, and it feels like there's no 
better way to do it.



Wenjing



> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/
> The Enterprise PostgreSQL Company



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年4月24日 下午9:03，tushar  写道：
> 
> On 4/22/20 2:49 PM, 曾文旌 wrote:
>> 
>> I provide the TRUNCATE tablename DROP to clear the data in the GTT and 
>> delete the storage files.
>> This feature requires the current transaction to commit immediately after it 
>> finishes truncate.
>> 
> Thanks Wenjing , Please refer this scenario
> 
> postgres=# create global temp table testing (a int);
> CREATE TABLE
> postgres=# begin;
> BEGIN
> postgres=*# truncate testing;  -- working   [1]
> TRUNCATE TABLE
> postgres=*# truncate testing drop;
> ERROR:  Truncate global temporary table cannot run inside a transaction block 
>--that is throwing an error claiming something which i did  successfully 
> [1]
The truncate GTT drop was removed.
So the problem goes away.


Wenjing


> postgres=!#
> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/
> The Enterprise PostgreSQL Company



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年4月23日 下午3:43，Pavel Stehule  写道：
> 
> 
> 
> čt 23. 4. 2020 v 9:10 odesílatel 曾文旌  <mailto:wenjing@alibaba-inc.com>> napsal:
> 
> 
>> 2020年4月22日 下午10:50，Pavel Stehule > <mailto:pavel.steh...@gmail.com>> 写道：
>> 
>> 
>> 
>> st 22. 4. 2020 v 16:38 odesílatel Prabhat Sahu 
>> mailto:prabhat.s...@enterprisedb.com>> 
>> napsal:
>> 
>> 
>> On Wed, Apr 22, 2020 at 2:49 PM 曾文旌 > <mailto:wenjing@alibaba-inc.com>> wrote:
>>> 
>>> Although the implementation of GTT is different, I think so TRUNCATE on 
>>> Postgres (when it is really finalized) can remove session metadata of GTT 
>>> too (and reduce usage's counter). It is not critical feature, but I think 
>>> so it should not be hard to implement. From practical reason can be nice to 
>>> have a tool how to refresh GTT without a necessity to close session. 
>>> TRUNCATE can be this tool.
> Sorry, I don't quite understand what you mean, could you describe it in 
> detail? 
> In my opinion the TRUNCATE GTT cannot clean up data in other sessions, 
> especially clean up local buffers in other sessions.
> 
> It is about a possibility to force reset GTT to default empty state for all 
> sessions.
> 
> Maybe it is some what does your TRUNCATE DROP, but I don't think so this 
> design (TRUNCATE DROP) is good, because then user have to know implementation 
> detail.
> 
> I prefer some like TRUNCATE tab WITH OPTION (GLOBAL, FORCE) - "GLOBAL" .. 
> apply on all sessions, FORCE try to do without waiting on some global lock, 
> try to do immediately with possibility to cancel some statements and rollback 
> some session.
> 
> instead GLOBAL maybe we can use  "ALLSESSION", or "ALL SESSION" or some else
> 
> But I like possible terminology LOCAL x GLOBAL for GTT. What I mean? Some 
> statements like "TRUNCATE" can  works (by default) in "local" mode .. it has 
> impact to current session only. But sometimes can be executed in "global" 
> mode with effect on all sessions.
The TRUNCATE GTT GLOBAL like DROP GTT FORCE you mentioned that before.
I think this requires identifying sessions that have initialized the stored 
file and no actual data.
And Handling local buffers on other session and locks is also difficult.
It may be harder than dropping the GTT force, which can kill other sessions, 
but TRUNCATE GTT would prefer not to.
This doesn't seem to complete the basic conditions, it's not easy.
So, I want to put this feature in next releases, along with DROP GTT FORCE.
Also, in view of your comments, I roll back the feature of TRUNCATE GTT DROP.



Wenjing

> 
> 
> 
>> Yes, I think we need a way to delete the GTT local storage without closing 
>> the session.
>> 
>> I provide the TRUNCATE tablename DROP to clear the data in the GTT and 
>> delete the storage files.
>> This feature requires the current transaction to commit immediately after it 
>> finishes truncate.
>> 
>> Hi Wenjing,
>> Thanks for the patch(v30) for the new syntax support for (TRUNCATE 
>> table_name DROP) for deleting storage files after TRUNCATE on GTT.
>> 
>> This syntax looks strange, and I don't think so it solve anything in 
>> practical life, because without lock the table will be used in few seconds 
>> by other sessions.
> 
> If a dba wants to delete or modify a GTT, he can use locks to help him make 
> the change. 
> 
> postgres=# begin;
> BEGIN
> postgres=*# LOCK TABLE gtt2 IN ACCESS EXCLUSIVE MODE;
> postgres=*# select * from pg_gtt_attached_pids ;
> 
> Kill session or let session do TRUNCATE tablename DROP
> 
> postgres=*# drop table gtt2;
> DROP TABLE
> postgres=*# commit;
> COMMIT
> 
> yes, user can lock a tables. But I think so it is user friendly design. I 
> don't remember any statement in Postgres, where I have to use table locks 
> explicitly.
> 
> For builtin commands it should be done transparently (for user).
It can be improved ,like DROP GTT FORCE.

> 
> Regards
> 
> Pavel
>  
> 
>> 
>> This is same topic when we talked about ALTER - when and where the changes 
>> should be applied. 
>> 
>> The CLUSTER commands works only on session private data, so it should not to 
>> need some special lock or some special cleaning before.
>> 
>> Regards
>> 
>> Pavel
>>  
>>  
>> Please check below scenarios:
>> 
>> Case1:
>> -- session1:
>> postgres=# create global temporary table gtt2 (c1 integer) on commit 
>> preserve rows;
>> CREATE TABLE
>> postgres=

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年4月22日 下午10:50，Pavel Stehule  写道：
> 
> 
> 
> st 22. 4. 2020 v 16:38 odesílatel Prabhat Sahu  <mailto:prabhat.s...@enterprisedb.com>> napsal:
> 
> 
> On Wed, Apr 22, 2020 at 2:49 PM 曾文旌  <mailto:wenjing@alibaba-inc.com>> wrote:
>> 
>> Although the implementation of GTT is different, I think so TRUNCATE on 
>> Postgres (when it is really finalized) can remove session metadata of GTT 
>> too (and reduce usage's counter). It is not critical feature, but I think so 
>> it should not be hard to implement. From practical reason can be nice to 
>> have a tool how to refresh GTT without a necessity to close session. 
>> TRUNCATE can be this tool.
Sorry, I don't quite understand what you mean, could you describe it in detail? 
In my opinion the TRUNCATE GTT cannot clean up data in other sessions, 
especially clean up local buffers in other sessions.


> Yes, I think we need a way to delete the GTT local storage without closing 
> the session.
> 
> I provide the TRUNCATE tablename DROP to clear the data in the GTT and delete 
> the storage files.
> This feature requires the current transaction to commit immediately after it 
> finishes truncate.
> 
> Hi Wenjing,
> Thanks for the patch(v30) for the new syntax support for (TRUNCATE table_name 
> DROP) for deleting storage files after TRUNCATE on GTT.
> 
> This syntax looks strange, and I don't think so it solve anything in 
> practical life, because without lock the table will be used in few seconds by 
> other sessions.

If a dba wants to delete or modify a GTT, he can use locks to help him make the 
change.

postgres=# begin;
BEGIN
postgres=*# LOCK TABLE gtt2 IN ACCESS EXCLUSIVE MODE;
postgres=*# select * from pg_gtt_attached_pids ;

Kill session or let session do TRUNCATE tablename DROP

postgres=*# drop table gtt2;
DROP TABLE
postgres=*# commit;
COMMIT

> 
> This is same topic when we talked about ALTER - when and where the changes 
> should be applied. 
> 
> The CLUSTER commands works only on session private data, so it should not to 
> need some special lock or some special cleaning before.
> 
> Regards
> 
> Pavel
>  
>  
> Please check below scenarios:
> 
> Case1:
> -- session1:
> postgres=# create global temporary table gtt2 (c1 integer) on commit preserve 
> rows;
> CREATE TABLE
> postgres=# create index  idx1 on gtt2 (c1);
> CREATE INDEX
> postgres=# create index  idx2 on gtt2 (c1) where c1%2 =0;
> CREATE INDEX
> postgres=# 
> postgres=# CLUSTER gtt2 USING idx1;
> CLUSTER
> postgres=# CLUSTER gtt2 USING idx2;
> ERROR:  cannot cluster on partial index "idx2"
> 
> Case2:
> -- Session2:
> postgres=# CLUSTER gtt2 USING idx1;
> CLUSTER
> postgres=# CLUSTER gtt2 USING idx2;
> CLUSTER
> 
> postgres=# insert into gtt2 values(1);
> INSERT 0 1
> postgres=# CLUSTER gtt2 USING idx1;
> CLUSTER
> postgres=# CLUSTER gtt2 USING idx2;
> ERROR:  cannot cluster on partial index "idx2"
> 
> Case3:
> -- Session2:
> postgres=# TRUNCATE gtt2 DROP;
> TRUNCATE TABLE
> postgres=# CLUSTER gtt2 USING idx1;
> CLUSTER
> postgres=# CLUSTER gtt2 USING idx2;
> CLUSTER
> 
> In Case2, Case3 we can observe, with the absence of data in GTT, we are able 
> to "CLUSTER gtt2 USING idx2;" (having partial index)
> But why does the same query fail for Case1 (absence of data)?
> 
> Thanks,
> Prabhat Sahu
> 
>  
> 
> 
> Wenjing
> 
> 
>> 
>> Regards
>> 
>> Pavel
>> 
>> 
>> All in all, I think the current implementation is sufficient for dba to 
>> manage GTT.
>> 
>>> 2020年4月2日 下午4:45，Prabhat Sahu >> <mailto:prabhat.s...@enterprisedb.com>> 写道：
>>> 
>>> Hi All,
>>> 
>>> I have noted down few behavioral difference in our GTT implementation in PG 
>>> as compared to Oracle DB:
>>> As per my understanding, the behavior of DROP TABLE in case of "Normal 
>>> table and GTT" in Oracle DB are as below:
>>> Any tables(Normal table / GTT) without having data in a session, we will be 
>>> able to DROP from another session.
>>> For a completed transaction on a normal table having data, we will be able 
>>> to DROP from another session. If the transaction is not yet complete, and 
>>> we are trying to drop the table from another session, then we will get an 
>>> error. (working as expected)
>>> For a completed transaction on GTT with(on commit delete rows) (i.e. no 
>>> data in GTT) in a session, we will be able to DROP from another session.
>>> For a completed transaction on GTT with(on commit preserve rows

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年4月22日 下午10:38，Prabhat Sahu  写道：
> 
> 
> 
> On Wed, Apr 22, 2020 at 2:49 PM 曾文旌  <mailto:wenjing@alibaba-inc.com>> wrote:
>> 
>> Although the implementation of GTT is different, I think so TRUNCATE on 
>> Postgres (when it is really finalized) can remove session metadata of GTT 
>> too (and reduce usage's counter). It is not critical feature, but I think so 
>> it should not be hard to implement. From practical reason can be nice to 
>> have a tool how to refresh GTT without a necessity to close session. 
>> TRUNCATE can be this tool.
> Yes, I think we need a way to delete the GTT local storage without closing 
> the session.
> 
> I provide the TRUNCATE tablename DROP to clear the data in the GTT and delete 
> the storage files.
> This feature requires the current transaction to commit immediately after it 
> finishes truncate.
> 
> Hi Wenjing,
> Thanks for the patch(v30) for the new syntax support for (TRUNCATE table_name 
> DROP) for deleting storage files after TRUNCATE on GTT.
>  
> Please check below scenarios:
> 
> Case1:
> -- session1:
> postgres=# create global temporary table gtt2 (c1 integer) on commit preserve 
> rows;
> CREATE TABLE
> postgres=# create index  idx1 on gtt2 (c1);
> CREATE INDEX
> postgres=# create index  idx2 on gtt2 (c1) where c1%2 =0;
> CREATE INDEX
> postgres=# 
> postgres=# CLUSTER gtt2 USING idx1;
> CLUSTER
> postgres=# CLUSTER gtt2 USING idx2;
> ERROR:  cannot cluster on partial index "idx2"
> 
> Case2:
> -- Session2:
> postgres=# CLUSTER gtt2 USING idx1;
> CLUSTER
> postgres=# CLUSTER gtt2 USING idx2;
> CLUSTER
> 
> postgres=# insert into gtt2 values(1);
> INSERT 0 1
> postgres=# CLUSTER gtt2 USING idx1;
> CLUSTER
> postgres=# CLUSTER gtt2 USING idx2;
> ERROR:  cannot cluster on partial index "idx2"
> 
> Case3:
> -- Session2:
> postgres=# TRUNCATE gtt2 DROP;
> TRUNCATE TABLE
> postgres=# CLUSTER gtt2 USING idx1;
> CLUSTER
> postgres=# CLUSTER gtt2 USING idx2;
> CLUSTER
> 
> In Case2, Case3 we can observe, with the absence of data in GTT, we are able 
> to "CLUSTER gtt2 USING idx2;" (having partial index)
> But why does the same query fail for Case1 (absence of data)?
This is expected
Because TRUNCATE gtt2 DROP; The local storage file was deleted, so CLUSTER 
checked that there were no local files and ended the process.


Wenjing


> 
> Thanks,
> Prabhat Sahu
> 
>  
> 
> 
> Wenjing
> 
> 
>> 
>> Regards
>> 
>> Pavel
>> 
>> 
>> All in all, I think the current implementation is sufficient for dba to 
>> manage GTT.
>> 
>>> 2020年4月2日 下午4:45，Prabhat Sahu >> <mailto:prabhat.s...@enterprisedb.com>> 写道：
>>> 
>>> Hi All,
>>> 
>>> I have noted down few behavioral difference in our GTT implementation in PG 
>>> as compared to Oracle DB:
>>> As per my understanding, the behavior of DROP TABLE in case of "Normal 
>>> table and GTT" in Oracle DB are as below:
>>> Any tables(Normal table / GTT) without having data in a session, we will be 
>>> able to DROP from another session.
>>> For a completed transaction on a normal table having data, we will be able 
>>> to DROP from another session. If the transaction is not yet complete, and 
>>> we are trying to drop the table from another session, then we will get an 
>>> error. (working as expected)
>>> For a completed transaction on GTT with(on commit delete rows) (i.e. no 
>>> data in GTT) in a session, we will be able to DROP from another session.
>>> For a completed transaction on GTT with(on commit preserve rows) with data 
>>> in a session, we will not be able to DROP from any session(not even from 
>>> the session in which GTT is created), we need to truncate the table data 
>>> first from all the session(session1, session2) which is having data.
>>> 1. Any tables(Normal table / GTT) without having data in a session, we will 
>>> be able to DROP from another session.
>>> Session1:
>>> create table t1 (c1 integer);
>>> create global temporary table gtt1 (c1 integer) on commit delete rows;
>>> create global temporary table gtt2 (c1 integer) on commit preserve rows;
>>> 
>>> Session2:
>>> drop table t1;
>>> drop table gtt1;
>>> drop table gtt2;
>>> 
>>> -- Issue 1: But we are able to drop a simple table and failed to drop GTT 
>>> as below.
>>> postgres=# drop table t1;
>>> DROP TABLE
>>> postgres=# drop table gtt1;
>>&

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年4月17日 下午7:26，Prabhat Sahu  写道：
> 
> On Fri, Apr 17, 2020 at 2:44 PM 曾文旌  <mailto:wenjing@alibaba-inc.com>> wrote:
> 
> I improved the logic of the warning message so that when the gap between 
> relfrozenxid of GTT is small,
> it will no longer be alarmed message.
> 
> Hi Wenjing,
> Thanks for the patch(v26), I have verified the previous related issues, and 
> are working fine now.
> Please check the below scenario VACUUM from a non-super user.
> 
> -- Create user "test_gtt", connect it , create gtt, VACUUM gtt and VACUUM / 
> VACUUM FULL
> postgres=# CREATE USER test_gtt;
> CREATE ROLE
> postgres=# \c postgres test_gtt
> You are now connected to database "postgres" as user "test_gtt".
> postgres=> CREATE GLOBAL TEMPORARY TABLE gtt1(c1 int);
> CREATE TABLE
> 
> -- VACUUM gtt is working fine, whereas we are getting huge WARNING for VACUUM 
> / VACUUM FULL as below:
> postgres=> VACUUM gtt1 ;
> VACUUM
> postgres=> VACUUM;
> WARNING:  skipping "pg_statistic" --- only superuser or database owner can 
> vacuum it
> WARNING:  skipping "pg_type" --- only superuser or database owner can vacuum 
> it
> WARNING:  skipping "pg_toast_2600" --- only table or database owner can 
> vacuum it
> WARNING:  skipping "pg_toast_2600_index" --- only table or database owner can 
> vacuum it
> 
> ... ... 
> ... ... 
> 
> WARNING:  skipping "_pg_foreign_tables" --- only table or database owner can 
> vacuum it
> WARNING:  skipping "foreign_table_options" --- only table or database owner 
> can vacuum it
> WARNING:  skipping "user_mapping_options" --- only table or database owner 
> can vacuum it
> WARNING:  skipping "user_mappings" --- only table or database owner can 
> vacuum it
> VACUUM 
I think this is expected, and user test_gtt does not have permission to vacuum 
the system table.
This has nothing to do with GTT.


Wenjing

> 
> -- 
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年4月8日 下午6:34，tushar  写道：
> 
> On 4/7/20 2:27 PM, 曾文旌 wrote:
>> Vacuum full GTT, cluster GTT is already supported in 
>> global_temporary_table_v24-pg13.patch.
> Please refer this below scenario , where pg_upgrade is failing
> 1)Server is up and running (./pg_ctl -D data status)
> 2)Stop the server ( ./pg_ctl -D data stop)
> 3)Connect to server using single user mode ( ./postgres --single -D data 
> postgres) and create a global temp table
> [tushar@localhost bin]$ ./postgres --single -D data1233 postgres
> 
> PostgreSQL stand-alone backend 13devel
> backend> create global temp table t(n int);
> 
> --Press Ctl+D to exit
> 
> 4)Perform initdb ( ./initdb -D data123)
> 5.Run pg_upgrade
> [tushar@localhost bin]$ ./pg_upgrade -d data -D data123 -b . -B .
> --
> --
> --
> Restoring database schemas in the new cluster
>   postgres
> *failure*
> Consult the last few lines of "pg_upgrade_dump_13592.log" for
> the probable cause of the failure.
> Failure, exiting
> 
> log file content  -
> 
> [tushar@localhost bin]$ tail -20   pg_upgrade_dump_13592.log
> pg_restore: error: could not execute query: ERROR:  pg_type array OID value 
> not set when in binary upgrade mode
I found that the regular table also has this problem, I am very unfamiliar with 
this part, so I opened another email to consult this problem.

> Command was:
> -- For binary upgrade, must preserve pg_type oid
> SELECT 
> pg_catalog.binary_upgrade_set_next_pg_type_oid('13594'::pg_catalog.oid);
> 
> 
> -- For binary upgrade, must preserve pg_class oids
> SELECT 
> pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('13593'::pg_catalog.oid);
> 
> CREATE GLOBAL TEMPORARY TABLE "public"."t" (
> "n" integer
> )
> WITH ("on_commit_delete_rows"='false');
> 
> -- For binary upgrade, set heap's relfrozenxid and relminmxid
> UPDATE pg_catalog.pg_class
> SET relfrozenxid = '0', relminmxid = '0'
> WHERE oid = '"public"."t"'::pg_catalog.regclass;
> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/
> The Enterprise PostgreSQL Company



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年4月9日 下午7:46，tushar  写道：
> 
> On 4/7/20 2:27 PM, 曾文旌 wrote:
>> Vacuum full GTT, cluster GTT is already supported in 
>> global_temporary_table_v24-pg13.patch.
> 
> Hi Wenjing,
> 
> Please refer this scenario , where reindex   message is not coming next time 
> ( after reconnecting to database) for GTT
> 
> A)
> --normal table
> postgres=# create table nt(n int primary key);
> CREATE TABLE
> --GTT table
> postgres=# create global temp table gtt(n int primary key);
> CREATE TABLE
> B)
> --Reindex  , normal table
> postgres=# REINDEX (VERBOSE) TABLE  nt;
> INFO:  index "nt_pkey" was reindexed
> DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
> REINDEX
> --reindex GTT table
> postgres=# REINDEX (VERBOSE) TABLE  gtt;
> INFO:  index "gtt_pkey" was reindexed
> DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
> REINDEX
> C)
> --Reconnect  to database
> postgres=# \c
> You are now connected to database "postgres" as user "tushar".
> D) again perform step B)
> 
> postgres=# REINDEX (VERBOSE) TABLE  nt;
> INFO:  index "nt_pkey" was reindexed
> DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
> REINDEX
> postgres=# REINDEX (VERBOSE) TABLE  gtt;   <-- message  not coming
> REINDEX
Yes , Since the newly established connection is on the db, the GTT store file 
is not initialized, so there is no info message.

> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/
> The Enterprise PostgreSQL Company



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年4月7日 下午6:22，Prabhat Sahu  写道：
> 
> Thanks for review.
> This parameter should support all types of writing of the bool type like 
> parameter autovacuum_enabled.
> So I fixed in global_temporary_table_v24-pg13.patch.
> 
> Thank you Wenjing for the new patch with the fix and the "VACUUM FULL GTT" 
> support.
> I have verified the above issue now its resolved.
> 
> Please check the below findings on VACUUM FULL.
> 
> postgres=# create global temporary table  gtt(c1 int) on commit preserve rows;
> CREATE TABLE
> postgres=# vacuum FULL ;
> WARNING:  global temp table oldest FrozenXid is far in the past
> HINT:  please truncate them or kill those sessions that use them.
> VACUUM

This is expected,
This represents that the GTT FrozenXid is the oldest in the entire db, and dba 
should vacuum the GTT if he want to push the db datfrozenxid.
Also he can use function pg_list_gtt_relfrozenxids() to check which session has 
"too old” data and truncate them or kill the sessions.



> 
> -- 
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com 



smime.p7s
Description: S/MIME cryptographic signature

[bug] Wrong bool value parameter

[曾文旌]

Do we allow such a bool parameter value? This seems puzzling to me.postgres=# create table t1(c1 int) with(autovacuum_enabled ='tr');CREATE TABLEpostgres=# create table t2(c1 int) with(autovacuum_enabled ='fa');CREATE TABLEpostgres=# \d+ t1                                    Table "public.t1" Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description +-+---+--+-+-+--+- c1     | integer |           |          |         | plain   |              | Access method: heapOptions: autovacuum_enabled=trpostgres=# \d+ t2                                    Table "public.t2" Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description +-+---+--+-+-+--+- c1     | integer |           |          |         | plain   |              | Access method: heapOptions: autovacuum_enabled=faI am try to fix in bug_boolrelopt.patchWenjing

bug_boolrelopt.patch
Description: Binary data

Re: [Proposal] Global temporary tables

[曾文旌]

In my opinion
1 We are developing GTT according to the SQL standard, not Oracle.

2 The implementation differences you listed come from pg and oracle storage 
modules and DDL implementations.

2.1 issue 1 and issue 2
The creation of Normal table/GTT defines the catalog and initializes the data 
store file, in the case of the GTT, which initializes the store file for the 
current session. 
But in oracle It just looks like only defines the catalog.
This causes other sessions can not drop the GTT in PostgreSQL.
This is the reason for issue 1 and issue 2, I think it is reasonable.

2.2 issue 3
I thinking the logic of drop GTT is
When only the current session is using the GTT, it is safe to drop the GTT. 
because the GTT's definition and storage files can completely delete from db.
But, If multiple sessions are using this GTT, it is hard to drop GTT in session 
a, because remove the local buffer and data file of the GTT in other session is 
difficult.
I am not sure why oracle has this limitation.
So, issue 3 is reasonable.

2.3 TRUNCATE Normal table/GTT
TRUNCATE Normal table / GTT clean up the logical data but not unlink data store 
file. in the case of the GTT, which is the store file for the current session.
But in oracle,  It just looks like data store file was cleaned up.
PostgreSQL storage is obviously different from oracle, In other words, session 
is detached from storage.
This is the reason for issue4 I think it is reasonable.

All in all, I think the current implementation is sufficient for dba to manage 
GTT.

> 2020年4月2日 下午4:45，Prabhat Sahu  写道：
> 
> Hi All,
> 
> I have noted down few behavioral difference in our GTT implementation in PG 
> as compared to Oracle DB:
> As per my understanding, the behavior of DROP TABLE in case of "Normal table 
> and GTT" in Oracle DB are as below:
> Any tables(Normal table / GTT) without having data in a session, we will be 
> able to DROP from another session.
> For a completed transaction on a normal table having data, we will be able to 
> DROP from another session. If the transaction is not yet complete, and we are 
> trying to drop the table from another session, then we will get an error. 
> (working as expected)
> For a completed transaction on GTT with(on commit delete rows) (i.e. no data 
> in GTT) in a session, we will be able to DROP from another session.
> For a completed transaction on GTT with(on commit preserve rows) with data in 
> a session, we will not be able to DROP from any session(not even from the 
> session in which GTT is created), we need to truncate the table data first 
> from all the session(session1, session2) which is having data.
> 1. Any tables(Normal table / GTT) without having data in a session, we will 
> be able to DROP from another session.
> Session1:
> create table t1 (c1 integer);
> create global temporary table gtt1 (c1 integer) on commit delete rows;
> create global temporary table gtt2 (c1 integer) on commit preserve rows;
> 
> Session2:
> drop table t1;
> drop table gtt1;
> drop table gtt2;
> 
> -- Issue 1: But we are able to drop a simple table and failed to drop GTT as 
> below.
> postgres=# drop table t1;
> DROP TABLE
> postgres=# drop table gtt1;
> ERROR:  can not drop relation gtt1 when other backend attached this global 
> temp table
> postgres=# drop table gtt2;
> ERROR:  can not drop relation gtt2 when other backend attached this global 
> temp table
> 
> 3. For a completed transaction on GTT with(on commit delete rows) (i.e. no 
> data in GTT) in a session, we will be able to DROP from another session.
> Session1:
> create global temporary table gtt1 (c1 integer) on commit delete rows;
> 
> Session2:
> drop table gtt1;
> 
> -- Issue 2: But we are getting error for GTT with(on_commit_delete_rows) 
> without data.
> postgres=# drop table gtt1;
> ERROR:  can not drop relation gtt1 when other backend attached this global 
> temp table
> 
> 4. For a completed transaction on GTT with(on commit preserve rows) with data 
> in any session, we will not be able to DROP from any session(not even from 
> the session in which GTT is created)
> 
> Case1:
> create global temporary table gtt2 (c1 integer) on commit preserve rows;
> insert into gtt2 values(100);
> drop table gtt2;
> 
> SQL> drop table gtt2;
> drop table gtt2
>   *
> ERROR at line 1:
> ORA-14452: attempt to create, alter or drop an index on temporary table 
> already in use
> 
> -- Issue 3: But, we are able to drop the GTT(having data) which we have 
> created in the same session.
> postgres=# drop table gtt2;
> DROP TABLE
> 
> Case2: GTT with(on commit preserve rows) having data in both session1 and 
> session2
> Session1:
> create global temporary table gtt2 (c1 integer) on commit preserve rows;
> insert into gtt2 values(100);
> 
> Session2:
> insert into gtt2 values(200);
> 
> -- If we try to drop the table from any session we should get an error, it is 
> working fine.
> drop table gtt2;
> SQL> drop table gtt2;
> drop table gtt2
>   *
> ERROR at line 1:
> ORA-1

Re: [Proposal] Global temporary tables

[曾文旌]

> 2020年3月31日 下午9:59，Prabhat Sahu  写道：
> 
> Hi Wenjing,
> Thanks for the new patch.
> I saw with the patch(gtt_v23.patch), we are supporting the new concept 
> "global temporary sequence"(i.e. session-specific sequence), is this 
> intentional?
It was supported in earlier versions,
This causes the sequence built into the GTT to automatically become a "global 
temp sequence",
Such as create global temp table (a serial);
Like GTT, the global temp sequnce is used individually for each session.

Recently, I added the global temp sequence syntax so that it can be created 
independently.
The purpose of this is to enable such sequence built into the GTT to support 
pg_dump and pg_restore.


Wenjing


> 
> postgres=# create global temporary sequence gt_seq;
> CREATE SEQUENCE
> postgres=# create sequence seq;
> CREATE SEQUENCE
> postgres=# \d+
>   List of relations
>  Schema |  Name  |   Type   | Owner | Persistence |Size| Description 
> ++--+---+-++-
>  public | gt_seq | sequence | edb   | session | 8192 bytes | 
>  public | seq| sequence | edb   | permanent   | 8192 bytes | 
> (2 rows)
> 
> postgres=# select nextval('gt_seq'), nextval('seq');
>  nextval | nextval 
> -+-
>1 |   1
> (1 row)
> 
> postgres=# select nextval('gt_seq'), nextval('seq');
>  nextval | nextval 
> -+-
>2 |   2
> (1 row)
> 
> -- Exit and re-connect to psql prompt:
> postgres=# \q
> [edb@localhost bin]$ ./psql postgres 
> psql (13devel)
> Type "help" for help.
> 
> postgres=# select nextval('gt_seq'), nextval('seq');
>  nextval | nextval 
> -+-
>1 |   3
> (1 row)
> 
> postgres=# select nextval('gt_seq'), nextval('seq');
>  nextval | nextval 
> -+-
>2 |   4
> (1 row)
> 
> On Tue, Mar 31, 2020 at 9:46 AM 曾文旌  <mailto:wenjing@alibaba-inc.com>> wrote:
> 
> 
>> 2020年3月27日 下午5:21，tushar > <mailto:tushar.ah...@enterprisedb.com>> 写道：
>> 
>> On 3/27/20 10:55 AM, 曾文旌 wrote:
>>>> Hi Wenjing,
>>>> This patch(gtt_v21_pg13.patch) is not applicable on PG HEAD, I hope you 
>>>> have prepared the patch on top of some previous commit. 
>>>> Could you please rebase the patch which we can apply on HEAD ?
>>> Yes, It looks like the built-in functions are in conflict with new code.
>>> 
>>> 
>> This error message looks wrong  to me-
>> 
>> postgres=# reindex table concurrently t ;
>> ERROR:  cannot create indexes on global temporary tables using concurrent 
>> mode
>> postgres=# 
>> 
>> Better message would be-
>> 
>> ERROR:  cannot reindex global temporary tables concurrently
>> 
> I found that the local temp table automatically disables concurrency mode.
> so, I made some improvements, The reindex GTT behaves the same as the local 
> temp table.
> 
> 
> Wenjing
> 
> 
> 
>> 
>> -- 
>> regards,tushar
>> EnterpriseDB  https://www.enterprisedb.com/ <https://www.enterprisedb.com/>
>> The Enterprise PostgreSQL Company
> 
> 
> 
> -- 
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>



smime.p7s
Description: S/MIME cryptographic signature

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年3月11日 下午3:52，Prabhat Sahu  写道：
> 
> On Mon, Mar 9, 2020 at 10:02 PM 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> wrote:
> 
> 
> Fixed in global_temporary_table_v18-pg13.patch.
> Hi Wenjing,
> Thanks for the patch. I have verified the previous issues with 
> "gtt_v18_pg13.patch" and those are resolved.
> Please find below case:
> 
> postgres=# create sequence seq;
> CREATE SEQUENCE
> 
> postgres=# CREATE GLOBAL TEMPORARY TABLE gtt1(c1 int PRIMARY KEY) ON COMMIT 
> DELETE ROWS;
> CREATE TABLE
> 
> postgres=# CREATE GLOBAL TEMPORARY TABLE gtt2(c1 int PRIMARY KEY) ON COMMIT 
> PRESERVE ROWS;
> CREATE TABLE
> 
> postgres=# alter table gtt1 add c2 int default nextval('seq');
> ERROR:  cannot reindex global temporary tables
> 
> postgres=# alter table gtt2 add c2 int default nextval('seq');
> ERROR:  cannot reindex global temporary tables
reindex GTT is already supported

Please check global_temporary_table_v20-pg13.patch


Wenjing



> 
> Note: We are getting this error if we have a key column(PK/UNIQUE) in a GTT, 
> and trying to add a column with a default sequence into it.
> 
> -- 
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年3月16日 下午5:31，Prabhat Sahu  写道：
> 
> 
> 
> On Mon, Mar 16, 2020 at 1:30 PM Konstantin Knizhnik 
> mailto:k.knizh...@postgrespro.ru>> wrote:
> 
> It seems to be expected behavior: GTT data is private to the session and 
> postgres_fdw establish its own session where content of the table is empty.
> But if you insert some data in f_gtt1, then you will be able to select this 
> data from it because of connection cache in postgres_fdw.
> 
> Thanks for the explanation.
> I am able to insert and select the value from f_gtt1.
> 
>  postgres=# insert into f_gtt1 values (1,'gtt_c21');
> INSERT 0 1
> postgres=# select * from f_gtt1;
>  c1 |   c2
> +-
>   1 | gtt_c21
> (1 row)
> 
> I have one more doubt,
> As you told above "GTT data is private to the session and postgres_fdw 
> establish its own session where content of the table is empty."
> Please check the below scenario, 
> we can select data from the "root GTT" and "foreign GTT partitioned table" 
> but we are unable to select data from "GTT partitioned table"
postgres=# select pg_backend_pid();
 pg_backend_pid 

 119135
(1 row)

postgres=# select * from pg_gtt_attached_pids;
 schemaname | tablename | relid |  pid   
+---+---+
 public | gtt2_p1   | 73845 | 119135
 public | gtt2_p1   | 73845 |  51482
(2 rows)


postgres=# select datid,datname,pid,application_name,query from 
pg_stat_activity where usename = ‘wenjing';
 datid | datname  |  pid   | application_name | 
   query 
---+--++--+--
 13589 | postgres | 119135 | psql | select 
datid,datname,pid,application_name,query from pg_stat_activity where usename = 
'wenjing';
 13589 | postgres |  51482 | postgres_fdw | COMMIT TRANSACTION
(2 rows)

This can be explained
The postgre_fdw connection has not been disconnected, and it produced data in 
another session.
In other words, gtt2_p1 is empty in session 119135, but not in session 51482.


> 
> postgres=# create global temporary table gtt2 (c1 integer, c2 integer) 
> partition by range(c1);
> CREATE TABLE
> postgres=# create global temporary table gtt2_p1 (c1 integer, c2 integer);
> CREATE TABLE
> postgres=# create foreign table f_gtt2_p1 (c1 integer, c2 integer) server fdw 
> options (table_name 'gtt2_p1');
> CREATE FOREIGN TABLE
> postgres=# alter table gtt2 attach partition f_gtt2_p1 for values from 
> (minvalue) to (10);
> ALTER TABLE
> postgres=# insert into gtt2 select i,i from generate_series(1,5,2)i;
> INSERT 0 3
> postgres=# select * from gtt2;
>  c1 | c2 
> +
>   1 |  1
>   3 |  3
>   5 |  5
> (3 rows)
> 
> postgres=# select * from gtt2_p1;
>  c1 | c2 
> +
> (0 rows)
> 
> postgres=# select * from f_gtt2_p1;
>  c1 | c2 
> +
>   1 |  1
>   3 |  3
>   5 |  5
> (3 rows)
> 
> Is this an expected behavior?
> 
> -- 
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年3月16日 下午5:04，Pavel Stehule  写道：
> 
> 
> 
> po 16. 3. 2020 v 9:58 odesílatel tushar  <mailto:tushar.ah...@enterprisedb.com>> napsal:
> Hi Wenjing,
> 
> I have created a global table on X session but i am not able to drop from Y 
> session ?
> 
> X session - ( connect to psql terminal )
> postgres=# create global temp table foo(n int);
> CREATE TABLE
> postgres=# select * from foo;
>  n 
> ---
> (0 rows)
> 
> 
> Y session - ( connect to psql terminal )
> postgres=# drop table foo;
> ERROR:  can not drop relation foo when other backend attached this global 
> temp table
> 
> Table has been created  so i think - user should be able to drop from another 
> session as well without exit from X session. 
> 
> By the original design GTT was not modifiable until is used by any session. 
> Now, you cannot to drop normal table when this table is used.
> 
> It is hard to say what is most correct behave and design, but for this 
> moment, I think so protecting table against drop while it is used by other 
> session is the best behave.
> 
> Maybe for next release we can introduce DROP TABLE x (FORCE) - like we have 
> for DROP DATABASE. This behave is very similar.
I agree with that.


Wenjing

> 
> Pavel
> 
> 
> regards,
> 
> On 3/16/20 1:35 PM, 曾文旌(义从) wrote:
>> 
>> 
>>> 2020年3月16日 下午2:23，Prabhat Sahu >> <mailto:prabhat.s...@enterprisedb.com>> 写道：
>>> 
>>> Hi Wenjing,
>>> Please check the below scenario, where the Foreign table on GTT not showing 
>>> records.
>>> 
>>> postgres=# create extension postgres_fdw;
>>> CREATE EXTENSION
>>> postgres=# do $d$
>>> begin
>>> execute $$create server fdw foreign data wrapper postgres_fdw 
>>> options (host 'localhost',dbname 'postgres',port 
>>> '$$||current_setting('port')||$$')$$;
>>> end;
>>> $d$;
>>> DO
>>> postgres=# create user mapping for public server fdw;
>>> CREATE USER MAPPING
>>> 
>>> postgres=# create table lt1 (c1 integer, c2 varchar(50));
>>> CREATE TABLE
>>> postgres=# insert into lt1 values (1,'c21');
>>> INSERT 0 1
>>> postgres=# create foreign table ft1 (c1 integer, c2 varchar(50)) server fdw 
>>> options (table_name 'lt1');
>>> CREATE FOREIGN TABLE
>>> postgres=# select * from ft1;
>>>  c1 | c2  
>>> +-
>>>   1 | c21
>>> (1 row)
>>> 
>>> postgres=# create global temporary table gtt1 (c1 integer, c2 varchar(50));
>>> CREATE TABLE
>>> postgres=# insert into gtt1 values (1,'gtt_c21');
>>> INSERT 0 1
>>> postgres=# create foreign table f_gtt1 (c1 integer, c2 varchar(50)) server 
>>> fdw options (table_name 'gtt1');
>>> CREATE FOREIGN TABLE
>>> 
>>> postgres=# select * from gtt1;
>>>  c1 |   c2
>>> +-
>>>   1 | gtt_c21
>>> (1 row)
>>> 
>>> postgres=# select * from f_gtt1;
>>>  c1 | c2 
>>> +
>>> (0 rows)
>>> 
>>> -- 
>> 
>> I understand that postgre_fdw works similar to dblink.
>> postgre_fdw access to the table requires a new connection.
>> The data in the GTT table is empty in the newly established connection.
>> Because GTT shares structure but not data between connections.
>> 
>> Try local temp table:
>> create temporary table ltt1 (c1 integer, c2 varchar(50));
>> 
>> insert into ltt1 values (1,'gtt_c21');
>> 
>> create foreign table f_ltt1 (c1 integer, c2 varchar(50)) server fdw options 
>> (table_name 'ltt1');
>> 
>> select * from ltt1;
>>  c1 |   c2
>> +-
>>   1 | gtt_c21
>> (1 row)
>> 
>> select * from l_gtt1;
>> ERROR:  relation "l_gtt1" does not exist
>> LINE 1: select * from l_gtt1;
>> 
>> 
>> Wenjing
>> 
>> 
>>> With Regards,
>>> Prabhat Kumar Sahu
>>> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>
>> 
> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/ <https://www.enterprisedb.com/>
> The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年3月16日 下午4:58，tushar  写道：
> 
> Hi Wenjing,
> 
> I have created a global table on X session but i am not able to drop from Y 
> session ?
> 
> X session - ( connect to psql terminal )
> postgres=# create global temp table foo(n int);
> CREATE TABLE
> postgres=# select * from foo;
>  n 
> ---
> (0 rows)
> 
> 
> Y session - ( connect to psql terminal )
> postgres=# drop table foo;
> ERROR:  can not drop relation foo when other backend attached this global 
> temp table
For now, If one dba wants to drop one GTT,
he can use the view pg_gtt_attached_pids to see which backends are using this 
GTT.
then kill these sessions with pg_terminate_backend, and he can drop this GTT.

> 
> Table has been created  so i think - user should be able to drop from another 
> session as well without exit from X session. 
> 
> regards,
> 
> On 3/16/20 1:35 PM, 曾文旌(义从) wrote:
>> 
>> 
>>> 2020年3月16日 下午2:23，Prabhat Sahu >> <mailto:prabhat.s...@enterprisedb.com>> 写道：
>>> 
>>> Hi Wenjing,
>>> Please check the below scenario, where the Foreign table on GTT not showing 
>>> records.
>>> 
>>> postgres=# create extension postgres_fdw;
>>> CREATE EXTENSION
>>> postgres=# do $d$
>>> begin
>>> execute $$create server fdw foreign data wrapper postgres_fdw 
>>> options (host 'localhost',dbname 'postgres',port 
>>> '$$||current_setting('port')||$$')$$;
>>> end;
>>> $d$;
>>> DO
>>> postgres=# create user mapping for public server fdw;
>>> CREATE USER MAPPING
>>> 
>>> postgres=# create table lt1 (c1 integer, c2 varchar(50));
>>> CREATE TABLE
>>> postgres=# insert into lt1 values (1,'c21');
>>> INSERT 0 1
>>> postgres=# create foreign table ft1 (c1 integer, c2 varchar(50)) server fdw 
>>> options (table_name 'lt1');
>>> CREATE FOREIGN TABLE
>>> postgres=# select * from ft1;
>>>  c1 | c2  
>>> +-
>>>   1 | c21
>>> (1 row)
>>> 
>>> postgres=# create global temporary table gtt1 (c1 integer, c2 varchar(50));
>>> CREATE TABLE
>>> postgres=# insert into gtt1 values (1,'gtt_c21');
>>> INSERT 0 1
>>> postgres=# create foreign table f_gtt1 (c1 integer, c2 varchar(50)) server 
>>> fdw options (table_name 'gtt1');
>>> CREATE FOREIGN TABLE
>>> 
>>> postgres=# select * from gtt1;
>>>  c1 |   c2
>>> +-
>>>   1 | gtt_c21
>>> (1 row)
>>> 
>>> postgres=# select * from f_gtt1;
>>>  c1 | c2 
>>> +
>>> (0 rows)
>>> 
>>> -- 
>> 
>> I understand that postgre_fdw works similar to dblink.
>> postgre_fdw access to the table requires a new connection.
>> The data in the GTT table is empty in the newly established connection.
>> Because GTT shares structure but not data between connections.
>> 
>> Try local temp table:
>> create temporary table ltt1 (c1 integer, c2 varchar(50));
>> 
>> insert into ltt1 values (1,'gtt_c21');
>> 
>> create foreign table f_ltt1 (c1 integer, c2 varchar(50)) server fdw options 
>> (table_name 'ltt1');
>> 
>> select * from ltt1;
>>  c1 |   c2
>> +-
>>   1 | gtt_c21
>> (1 row)
>> 
>> select * from l_gtt1;
>> ERROR:  relation "l_gtt1" does not exist
>> LINE 1: select * from l_gtt1;
>> 
>> 
>> Wenjing
>> 
>> 
>>> With Regards,
>>> Prabhat Kumar Sahu
>>> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>
>> 
> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/ <https://www.enterprisedb.com/>
> The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年3月16日 下午2:23，Prabhat Sahu  写道：
> 
> Hi Wenjing,
> Please check the below scenario, where the Foreign table on GTT not showing 
> records.
> 
> postgres=# create extension postgres_fdw;
> CREATE EXTENSION
> postgres=# do $d$
> begin
> execute $$create server fdw foreign data wrapper postgres_fdw options 
> (host 'localhost',dbname 'postgres',port '$$||current_setting('port')||$$')$$;
> end;
> $d$;
> DO
> postgres=# create user mapping for public server fdw;
> CREATE USER MAPPING
> 
> postgres=# create table lt1 (c1 integer, c2 varchar(50));
> CREATE TABLE
> postgres=# insert into lt1 values (1,'c21');
> INSERT 0 1
> postgres=# create foreign table ft1 (c1 integer, c2 varchar(50)) server fdw 
> options (table_name 'lt1');
> CREATE FOREIGN TABLE
> postgres=# select * from ft1;
>  c1 | c2  
> +-
>   1 | c21
> (1 row)
> 
> postgres=# create global temporary table gtt1 (c1 integer, c2 varchar(50));
> CREATE TABLE
> postgres=# insert into gtt1 values (1,'gtt_c21');
> INSERT 0 1
> postgres=# create foreign table f_gtt1 (c1 integer, c2 varchar(50)) server 
> fdw options (table_name 'gtt1');
> CREATE FOREIGN TABLE
> 
> postgres=# select * from gtt1;
>  c1 |   c2
> +-
>   1 | gtt_c21
> (1 row)
> 
> postgres=# select * from f_gtt1;
>  c1 | c2 
> +
> (0 rows)
> 
> -- 

I understand that postgre_fdw works similar to dblink.
postgre_fdw access to the table requires a new connection.
The data in the GTT table is empty in the newly established connection.
Because GTT shares structure but not data between connections.

Try local temp table:
create temporary table ltt1 (c1 integer, c2 varchar(50));

insert into ltt1 values (1,'gtt_c21');

create foreign table f_ltt1 (c1 integer, c2 varchar(50)) server fdw options 
(table_name 'ltt1');

select * from ltt1;
 c1 |   c2
+-
  1 | gtt_c21
(1 row)

select * from l_gtt1;
ERROR:  relation "l_gtt1" does not exist
LINE 1: select * from l_gtt1;


Wenjing


> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年3月12日 上午4:12，Robert Haas  写道：
> 
> On Wed, Mar 11, 2020 at 9:07 AM 曾文旌(义从)  wrote:
>> reindex need change relfilenode, but GTT is not currently supported.
> 
> In my view that'd have to be fixed somehow.
Ok , I am working on it.



> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年3月9日 下午10:37，tushar  写道：
> 
> On 3/6/20 12:35 PM, 曾文旌(义从) wrote:
>> Fixed in global_temporary_table_v17-pg13.patch
> 
> I observed that , we do support 'global temp' keyword with views
> 
> postgres=# create or replace  global temp view v1 as select 5;
> CREATE VIEW
I think we should not support global temp view.
Fixed in global_temporary_table_v18-pg13.patch.



Wenjing


> 
> but if we take the dump( using pg_dumpall) then it only display 'create view'
> 
> look like we are skipping it ?
> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/
> The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年3月5日 下午10:38，Robert Haas  写道：
> 
> On Thu, Mar 5, 2020 at 9:19 AM tushar  wrote:
>> WARNING:  relfilenode 13589/1663/19063 not exist in gtt shared hash when 
>> forget
>> ERROR:  out of shared memory
>> HINT:  You might need to increase max_active_gtt.
>> 
>> also , would be great  if we can make this error message  user friendly like 
>>  - "max connection reached"  rather than memory error
> 
> That would be nice, but the bigger problem is that the WARNING there
> looks totally unacceptable. It's looks like it's complaining of some
> internal issue (i.e. a bug or corruption) and the grammar is poor,
> too.

Yes, WARNING should not exist.
This is a bug in the rollback process and I have fixed it in 
global_temporary_table_v17-pg13.patch


Wenjing


> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年3月5日 下午10:19，tushar  写道：
> 
> On 3/3/20 2:10 PM, 曾文旌(义从) wrote:
>> I fixed in global_temporary_table_v16-pg13.patch.
> Please refer this scenario -
> 
> --Connect to psql -
> 
> postgres=# alter system set max_active_global_temporary_table =1;
> ALTER SYSTEM
> 
> --restart the server (./pg_ctl -D data restart) 
> 
> --create global temp table 
> 
> postgres=# create global temp  table ccc1  (c int);
> CREATE TABLE
> 
> --Try to Create another global temp table
> 
> postgres=# create global temp  table ccc2  (c int);
> WARNING:  relfilenode 13589/1663/19063 not exist in gtt shared hash when 
> forget
> ERROR:  out of shared memory
> HINT:  You might need to increase max_active_gtt.
> 
> postgres=# show max_active_gtt;
> ERROR:  unrecognized configuration parameter "max_active_gtt"
> postgres=# 
> postgres=# show max_active_global_temporary_table ;
>  max_active_global_temporary_table 
> ---
>  1
> (1 row)
> 
> postgres=# 
> 
> I cannot find "max_active_gtt"  GUC . I think you are referring to  
> "max_active_global_temporary_table" here ? 
> 
You're right.

Fixed in global_temporary_table_v17-pg13.patch


Wenjing


> also , would be great  if we can make this error message  user friendly like  
> - "max connection reached"  rather than memory error
> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/ <https://www.enterprisedb.com/>
> The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月25日 下午9:36，Prabhat Sahu  写道：
> 
> Hi All,
> 
> Please check the below findings on GTT.
> -- Scenario 1:
> Under "information_schema", We are not allowed to create "temporary table", 
> whereas we can CREATE/DROP "Global Temporary Table", is it expected ?
> 
> postgres=# create temporary table information_schema.temp1(c1 int);
> ERROR:  cannot create temporary relation in non-temporary schema
> LINE 1: create temporary table information_schema.temp1(c1 int);
>^
> 
> postgres=# create global temporary table information_schema.temp1(c1 int);
> CREATE TABLE
> 
> postgres=# drop table information_schema.temp1 ;
> DROP TABLE
> 
> -- Scenario 2:
> Here I am getting the same error message in both the below cases.
> We may add a "global" keyword with GTT related error message.
> 
> postgres=# create global temporary table gtt1 (c1 int unique);
> CREATE TABLE
> postgres=# create temporary table tmp1 (c1 int unique);
> CREATE TABLE
> 
> postgres=# create temporary table tmp2 (c1 int references gtt1(c1) );
> ERROR:  constraints on temporary tables may reference only temporary tables
> 
> postgres=# create global temporary table gtt2 (c1 int references tmp1(c1) );
> ERROR:  constraints on temporary tables may reference only temporary tables
Fixed in global_temporary_table_v15-pg13.patch


Wenjing


> 
> Thanks,
> Prabhat Sahu
> 
> On Tue, Feb 25, 2020 at 2:25 PM 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> wrote:
> 
> 
>> 2020年2月24日 下午5:44，Prabhat Sahu > <mailto:prabhat.s...@enterprisedb.com>> 写道：
>> 
>> On Fri, Feb 21, 2020 at 9:10 PM 曾文旌(义从) > <mailto:wenjing@alibaba-inc.com>> wrote:
>> Hi,
>> I have started testing the "Global temporary table" feature,
>> That's great, I see hope.
>> from "gtt_v11-pg13.patch". Below is my findings:
>> 
>> -- session 1:
>> postgres=# create global temporary table gtt1(a int);
>> CREATE TABLE
>> 
>> -- seeeion 2:
>> postgres=# truncate gtt1 ;
>> ERROR:  could not open file "base/13585/t3_16384": No such file or directory
>> 
>> is it expected?
>> 
>> Oh ,this is a bug, I fixed it.
>> Thanks for the patch.
>> I have verified the same, Now the issue is resolved with v12 patch.
>> 
>> Kindly confirm the below scenario:
>> 
>> postgres=# create global temporary table gtt1 (c1 int unique);
>> CREATE TABLE
>> 
>> postgres=# create global temporary table gtt2 (c1 int references gtt1(c1) );
>> ERROR:  referenced relation "gtt1" is not a global temp table
>> 
>> postgres=# create table tab2 (c1 int references gtt1(c1) );
>> ERROR:  referenced relation "gtt1" is not a global temp table
>> 
>> Thanks, 
>> Prabhat Sahu
> 
> GTT supports foreign key constraints in global_temporary_table_v13-pg13.patch
> 
> 
> Wenjing
> 
> 
> 
> 
> 
> -- 
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月25日 下午11:31，tushar  写道：
> 
> Hi,
> 
> I have created two  global temporary tables like this -
> 
> Case 1- 
> postgres=# create global  temp table foo(n int) with 
> (on_commit_delete_rows='true');
> CREATE TABLE
> 
> Case 2- 
> postgres=# create global  temp table bar1(n int) on commit delete rows;
> CREATE TABLE
> 
> 
> but   if i try to do the same having only 'temp' keyword , Case 2 is working 
> fine but getting this error  for case 1 -
> 
> postgres=# create   temp table foo1(n int) with 
> (on_commit_delete_rows='true');
> ERROR:  regular table cannot specifie on_commit_delete_rows
> postgres=# 
> 
> postgres=#  create   temp table bar1(n int) on commit delete rows;
> CREATE TABLE
> 
> i think this error message need to be more clear .
Also fixed in global_temporary_table_v14-pg13.patch

Wenjing



> 
> regards,
> tushar 
> 
> On 2/25/20 7:19 PM, Pavel Stehule wrote/:
>> 
>> 
>> út 25. 2. 2020 v 14:36 odesílatel Prabhat Sahu 
>> mailto:prabhat.s...@enterprisedb.com>> 
>> napsal:
>> Hi All,
>> 
>> Please check the below findings on GTT.
>> -- Scenario 1:
>> Under "information_schema", We are not allowed to create "temporary table", 
>> whereas we can CREATE/DROP "Global Temporary Table", is it expected ?
>> 
>> It is ok for me. temporary tables should be created only in proprietary 
>> schema. For GTT there is not risk of collision, so it can be created in any 
>> schema where are necessary access rights.
>> 
>> Pavel
>> 
>> 
>> postgres=# create temporary table information_schema.temp1(c1 int);
>> ERROR:  cannot create temporary relation in non-temporary schema
>> LINE 1: create temporary table information_schema.temp1(c1 int);
>>^
>> 
>> postgres=# create global temporary table information_schema.temp1(c1 int);
>> CREATE TABLE
>> 
>> postgres=# drop table information_schema.temp1 ;
>> DROP TABLE
>> 
>> -- Scenario 2:
>> Here I am getting the same error message in both the below cases.
>> We may add a "global" keyword with GTT related error message.
>> 
>> postgres=# create global temporary table gtt1 (c1 int unique);
>> CREATE TABLE
>> postgres=# create temporary table tmp1 (c1 int unique);
>> CREATE TABLE
>> 
>> postgres=# create temporary table tmp2 (c1 int references gtt1(c1) );
>> ERROR:  constraints on temporary tables may reference only temporary tables
>> 
>> postgres=# create global temporary table gtt2 (c1 int references tmp1(c1) );
>> ERROR:  constraints on temporary tables may reference only temporary tables
>> 
>> Thanks,
>> Prabhat Sahu
>> 
>> On Tue, Feb 25, 2020 at 2:25 PM 曾文旌(义从) > <mailto:wenjing@alibaba-inc.com>> wrote:
>> 
>> 
>>> 2020年2月24日 下午5:44，Prabhat Sahu >> <mailto:prabhat.s...@enterprisedb.com>> 写道：
>>> 
>>> On Fri, Feb 21, 2020 at 9:10 PM 曾文旌(义从) >> <mailto:wenjing@alibaba-inc.com>> wrote:
>>> Hi,
>>> I have started testing the "Global temporary table" feature,
>>> That's great, I see hope.
>>> from "gtt_v11-pg13.patch". Below is my findings:
>>> 
>>> -- session 1:
>>> postgres=# create global temporary table gtt1(a int);
>>> CREATE TABLE
>>> 
>>> -- seeeion 2:
>>> postgres=# truncate gtt1 ;
>>> ERROR:  could not open file "base/13585/t3_16384": No such file or directory
>>> 
>>> is it expected?
>>> 
>>> Oh ,this is a bug, I fixed it.
>>> Thanks for the patch.
>>> I have verified the same, Now the issue is resolved with v12 patch.
>>> 
>>> Kindly confirm the below scenario:
>>> 
>>> postgres=# create global temporary table gtt1 (c1 int unique);
>>> CREATE TABLE
>>> 
>>> postgres=# create global temporary table gtt2 (c1 int references gtt1(c1) );
>>> ERROR:  referenced relation "gtt1" is not a global temp table
>>> 
>>> postgres=# create table tab2 (c1 int references gtt1(c1) );
>>> ERROR:  referenced relation "gtt1" is not a global temp table
>>> 
>>> Thanks, 
>>> Prabhat Sahu
>> 
>> GTT supports foreign key constraints in global_temporary_table_v13-pg13.patch
>> 
>> 
>> Wenjing
>> 
>> 
>> 
>> 
>> 
>> -- 
>> With Regards,
>> Prabhat Kumar Sahu
>> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>
> 
> -- 
> regards,tushar
> EnterpriseDB  https://www.enterprisedb.com/ <https://www.enterprisedb.com/>
> The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月24日 下午9:34，Prabhat Sahu  写道：
> 
> Hi All,
> 
> I observe a different behavior in  "temporary table" and "global temporary 
> table".
> Not sure if it is expected?
> 
> postgres=# create global temporary table parent1(a int)  on commit delete 
> rows;
> CREATE TABLE
> postgres=# create global temporary table child1() inherits (parent1);
> CREATE TABLE
> postgres=# insert into parent1 values(1);
> INSERT 0 1
> postgres=# insert into child1 values(2);
> INSERT 0 1
> postgres=# select * from parent1;
>  a 
> ---
> (0 rows)
> 
> postgres=# select * from child1;
>  a 
> ---
> (0 rows)
Because child1 inherits its father's on commit property.
I can make GTT behave like local temp table.


> 
> 
> postgres=# create temporary table parent2(a int)  on commit delete rows;
> CREATE TABLE
> postgres=# create temporary table child2() inherits (parent2);
> CREATE TABLE
> postgres=# insert into parent2 values(1);
> INSERT 0 1
> postgres=# insert into child2 values(2);
> INSERT 0 1
> postgres=# select * from parent2;
>  a 
> ---
>  2
> (1 row)
> 
> postgres=# select * from child2;
>  a 
> ---
>  2
> (1 row)
> 
> 
> Thanks,
> Prabhat Sahu
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月15日 下午6:06，Pavel Stehule  写道：
> 
> 
>> postgres=# insert into foo select generate_series(1,1);
>> INSERT 0 1
>> postgres=# \dt+ foo
>>   List of relations
>> ┌┬──┬───┬───┬─┬┬─┐
>> │ Schema │ Name │ Type  │ Owner │ Persistence │  Size  │ Description │
>> ╞╪══╪═══╪═══╪═╪╪═╡
>> │ public │ foo  │ table │ pavel │ session │ 384 kB │ │
>> └┴──┴───┴───┴─┴┴─┘
>> (1 row)
>> 
>> postgres=# truncate foo;
>> TRUNCATE TABLE
>> postgres=# \dt+ foo
>>   List of relations
>> ┌┬──┬───┬───┬─┬───┬─┐
>> │ Schema │ Name │ Type  │ Owner │ Persistence │ Size  │ Description │
>> ╞╪══╪═══╪═══╪═╪═══╪═╡
>> │ public │ foo  │ table │ pavel │ session │ 16 kB │ │
>> └┴──┴───┴───┴─┴───┴─┘
>> (1 row)
>> 
>> I expect zero size after truncate.
> Thanks for review.
> 
> I can explain, I don't think it's a bug.
> The current implementation of the truncated GTT retains two blocks of FSM 
> pages.
> The same is true for truncating regular tables in subtransactions.
> This is an implementation that truncates the table without changing the 
> relfilenode of the table.
> 
> 
> This is not extra important feature - now this is little bit a surprise, 
> because I was not under transaction.
> 
> Changing relfilenode, I think, is necessary, minimally for future VACUUM FULL 
> support.
Not allowing relfilenode changes is the current limit.
I think can improve on it. But ,This is a bit complicated.
so I'd like to know the necessity of this improvement.
Could you give me more details?

> 
> Regards
> 
> Pavel Stehule
>  
> 
> Wenjing
> 
>> 
>> Regards
>> 
>> Pavel
>> 
>> 
>> 
>> Wenjing
>> 
>> 
>> 
>> 
>> > 
>> > -- 
>> > Robert Haas
>> > EnterpriseDB: http://www.enterprisedb.com 
>> > The Enterprise PostgreSQL Company
>> 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月14日 下午5:19，Pavel Stehule  写道：
> 
> 
> 
> čt 30. 1. 2020 v 15:21 odesílatel Pavel Stehule  <mailto:pavel.steh...@gmail.com>> napsal:
> 
> 
> čt 30. 1. 2020 v 15:17 odesílatel 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> napsal:
> 
> 
> > 2020年1月29日 下午9:48，Robert Haas  > <mailto:robertmh...@gmail.com>> 写道：
> > 
> > On Tue, Jan 28, 2020 at 12:12 PM 曾文旌(义从)  > <mailto:wenjing@alibaba-inc.com>> wrote:
> >>> Opinion by Pavel
> >>> + rel->rd_islocaltemp = true;  <<<<<<< if this is valid, then the name of 
> >>> field "rd_islocaltemp" is not probably best
> >>> I renamed rd_islocaltemp
> >> 
> >> I don't see any change?
> >> 
> >> Rename rd_islocaltemp to rd_istemp  in global_temporary_table_v8-pg13.patch
> > 
> > In view of commit 6919b7e3294702adc39effd16634b2715d04f012, I think
> > that this has approximately a 0% chance of being acceptable. If you're
> > setting a field in a way that is inconsistent with the current use of
> > the field, you're probably doing it wrong, because the field has an
> > existing purpose to which new code must conform. And if you're not
> > doing that, then you don't need to rename it.
> Thank you for pointing it out.
> I've rolled back the rename.
> But I still need rd_localtemp to be true, The reason is that
> 1 GTT The GTT needs to support DML in read-only transactions ,like local temp 
> table.
> 2 GTT does not need to hold the lock before modifying the index buffer ,also 
> like local temp table.
> 
> Please give me feedback.
> 
> maybe some like
> 
> rel->rd_globaltemp = true;
> 
> and somewhere else
> 
> if (rel->rd_localtemp || rel->rd_globaltemp)
> {
>   ...
> }
> 
> 
> I tested this patch again and I am very well satisfied with behave. 
> 
> what doesn't work still - TRUNCATE statement
> 
> postgres=# insert into foo select generate_series(1,1);
> INSERT 0 1
> postgres=# \dt+ foo
>   List of relations
> ┌┬──┬───┬───┬─┬┬─┐
> │ Schema │ Name │ Type  │ Owner │ Persistence │  Size  │ Description │
> ╞╪══╪═══╪═══╪═╪╪═╡
> │ public │ foo  │ table │ pavel │ session │ 384 kB │ │
> └┴──┴───┴───┴─┴┴─┘
> (1 row)
> 
> postgres=# truncate foo;
> TRUNCATE TABLE
> postgres=# \dt+ foo
>   List of relations
> ┌┬──┬───┬───┬─┬───┬─┐
> │ Schema │ Name │ Type  │ Owner │ Persistence │ Size  │ Description │
> ╞╪══╪═══╪═══╪═╪═══╪═╡
> │ public │ foo  │ table │ pavel │ session │ 16 kB │ │
> └┴──┴───┴───┴─┴───┴─┘
> (1 row)
> 
> I expect zero size after truncate.
Thanks for review.

I can explain, I don't think it's a bug.
The current implementation of the truncated GTT retains two blocks of FSM pages.
The same is true for truncating regular tables in subtransactions.
This is an implementation that truncates the table without changing the 
relfilenode of the table.


Wenjing

> 
> Regards
> 
> Pavel
> 
> 
> 
> Wenjing
> 
> 
> 
> 
> > 
> > -- 
> > Robert Haas
> > EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>
> > The Enterprise PostgreSQL Company
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月5日 下午10:15，Robert Haas  写道：
> 
> On Wed, Feb 5, 2020 at 8:21 AM 曾文旌(义从)  wrote:
>> What do you mean by "catalog buffer"?
>> Yes, cleanup of local temp table requires deletion of correspondent entry 
>> from catalog and GTT should not do it.
>> But  I am speaking only about cleanup of data files of temp relations. It is 
>> done in the same way for local and global temp tables.
>> 
>> For native pg, the data file of temp table will not be cleaned up direct 
>> after oom happen.
>> Because the orphan local temp table(include catalog, local buffer, datafile) 
>> will be cleaned up by deleting the orphan temp schame in autovacuum.
>> So for GTT ,we cannot do the same with just deleting data files. This is why 
>> I dealt with it specifically.
> 
> After a crash restart, all temporary relfilenodes (e.g t12345_67890)
> are removed. I think GTTs should use relfilenodes of this general
> form, and then they'll be cleaned up by the existing code. For a
> regular temporary table, there is also the problem of removing the
> catalog entries, but GTTs shouldn't have this problem, because a GTT
> doesn't have any catalog entries for individual sessions, just for the
> main object, which isn't going away just because the system restarted.
> Right?
Wenjing wrote:
I have implemented its processing in global_temporary_table_v10-pg13.patch
When oom happen, all backend will be killed.
Then, I choose to clean up these files(all like t12345_67890) in startup 
process.

Wenjing

> 
>> In my patch autovacuum is prohibited for GTT.
>> 
>> But vacuum GTT is not prohibited.
> 
> That sounds right to me.
Wenjing wrote:
Also implemented in global_temporary_table_v10-pg13.patch

Wenjing

> 
> This thread is getting very hard to follow because neither Konstantin
> nor Wenjing seem to be using the standard method of quoting. When I
> reply, I get the whole thing quoted with "> " but can't easily tell
> the difference between what Wenjing wrote and what Konstantin wrote,
> because both of your mailers are quoting using indentation rather than
> "> " and it gets wiped out by my mailer. Please see if you can get
> your mailer to do what is normally done on this mailing list.
> 
> Thanks,
> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月5日 上午12:47，Konstantin Knizhnik  写道：
> 
> 
> 
> On 04.02.2020 18:01, 曾文旌(义从) wrote:
>> 
>> 
>>> 
>>> Yes, exactly.
>>> But it is still not clear to me why do we need some special handling for 
>>> GTT?
>>> Shared memory is reinitialized and storage of temporary tables is removed.
>>> It is true for both local and global temp tables.
>> Of course not. The local temp table cleans up the entire table (including 
>> catalog buffer and datafile). GTT is not.
>> 
> 
> What do you mean by "catalog buffer"?
> Yes, cleanup of local temp table requires deletion of correspondent entry 
> from catalog and GTT should not do it.
> But  I am speaking only about cleanup of data files of temp relations. It is 
> done in the same way for local and global temp tables.
For native pg, the data file of temp table will not be cleaned up direct after 
oom happen.
Because the orphan local temp table(include catalog, local buffer, datafile) 
will be cleaned up by deleting the orphan temp schame in autovacuum.
So for GTT ,we cannot do the same with just deleting data files. This is why I 
dealt with it specifically.

> 
> 
>>> In my patch autovacuum is prohibited for GTT.
>> But vacuum GTT is not prohibited. 
>> 
> Yes, but the simplest solution is to prohibit also explicit vacuum of GTT, 
> isn't it?
> 
>>> 
>>>> IMHO forced terminated of client sessions is not acceptable solution.
>>>>> And it is not an absolutely necessary requirement.
>>>>> So from my point of view we should not add such limitations to GTT design.
>>>> This limitation makes it possible for the GTT to do all the DDL.
>>>> IMHO even oracle's GTT has similar limitations.
>>> 
>>> I have checked that Oracle is not preventing creation of index for GTT if 
>>> there are some active sessions working with this table. And this index 
>>> becomes visible for all this sessions.
>> 1 Yes The creation of inde gtt has been improved in 
>> global_temporary_table_v10-pg13.patch
>> 2 But alter GTT ; drop GTT ; drop index on GTT is blocked by other sessions
>> 
> Yes, you are right.
> Orale documetation says:
> >  1) DDL operation on global temporary tables
> > It is not possible to perform a DDL operation (except TRUNCATE 
> > <https://www.oracletutorial.com/oracle-basics/oracle-truncate-table/>) on 
> > an existing global temporary table if one or more sessions are currently 
> > bound to that table.
> 
> But looks like create index is not considered as DDL operation on GTT and is 
> also supported by Oracle.

> 
> Your approach with prohibiting such accessed using shared cache is certainly 
> better then my attempt to prohibit such DDLs for GTT at all.
> I just what to eliminate maintenance of such shared cache to simplify the 
> patch.
> 
> But I still think that we should allow truncation of GTT and 
> creating/dropping indexes on it without any limitations. 
I think the goal of this work is this.
But, the first step is let GTT get as many features as possible on regular 
tables, even with some limitations.

>>> 
>>> May be the easies solution is to prohibit explicit vacuum of GTT?
>> I think vacuum is an important part of GTT.
>> 
>> Looking back at previous emails, robert once said that vacuum GTT is pretty 
>> important.
>> https://www.postgresql.org/message-id/CA%2BTgmob%3DL1k0cpXRcipdsaE07ok%2BOn%3DtTjRiw7FtD_D2T%3DJwhg%40mail.gmail.com
>>  
>> <https://www.postgresql.org/message-id/CA+Tgmob=L1k0cpXRcipdsaE07ok+On=tTjRiw7FtD_D2T=j...@mail.gmail.com>
>> 
> 
> Well, may be I am not right.
> I never saw use cases where temp table are used not like append-only storage 
> (when temp table tuples are updated multiple times).
> But I think that if such problem actually exists then solution is to support 
> autovacuum for temp tables, rather than allow manual vacuum.
> Certainly it can not be done by another  worker because it has no access to 
> private backend's data. But it can done incrementally by backend itself.
> 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月5日 上午4:57，Robert Haas  写道：
> 
> On Sat, Feb 1, 2020 at 11:14 AM 曾文旌(义从)  wrote:
>> As global_private_temp-8.patch, think about:
>> 1 session X tale several hours doing some statistical work with the GTT A, 
>> which generated some data using transaction 100, The work is not over.
>> 2 Then session Y vacuumed A, and the GTT's relfrozenxid (in pg_class) was 
>> updated to 1000 .
>> 3 Then the aotuvacuum happened, the clog  before 1000  was cleaned up.
>> 4 The data in session A could be lost due to missing clog, The analysis task 
>> failed.
>> 
>> However This is likely to happen because you allowed the GTT do vacuum.
>> And this is not a common problem, that not happen with local temp tables.
>> I feel uneasy about leaving such a question. We can improve it.
> 
> Each session is going to need to maintain its own notion of the
> relfrozenxid and relminmxid of each GTT to which it is attached.
> Storing the values in pg_class makes no sense and is completely
> unacceptable.
Yes, I've implemented it in global_temporary_table_v10-pg13.patch

> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月3日 下午4:16，Konstantin Knizhnik  写道：
> 
> 
> 
> On 01.02.2020 19:14, 曾文旌(义从) wrote:
>> 
>> 
>>> 2020年1月27日 下午5:38，Konstantin Knizhnik >> <mailto:k.knizh...@postgrespro.ru>> 写道：
>>> 
>>> 
>>> 
>>> On 25.01.2020 18:15, 曾文旌(义从) wrote:
>>>> I wonder why do we need some special check for GTT here.
>>>>> From my point of view cleanup at startup of local storage of temp tables 
>>>>> should be performed in the same way for local and global temp tables.
>>>> After oom kill, In autovacuum, the Isolated local temp table will be 
>>>> cleaned like orphan temporary tables. The definition of local temp table 
>>>> is deleted with the storage file. 
>>>> But GTT can not do that. So we have the this implementation in my patch.
>>>> If you have other solutions, please let me know.
>>>> 
>>> I wonder if it is possible that autovacuum or some other Postgres process 
>>> is killed by OOM and postmaster is not noticing it can doens't restart 
>>> Postgres instance?
>>> as far as I know, crash of any process connected to Postgres shared memory 
>>> (and autovacuum definitely has such connection) cause Postgres restart.
>> Postmaster will not restart after oom happen, but the startup process will. 
>> GTT data files are cleaned up in the startup process.
> 
> Yes, exactly.
> But it is still not clear to me why do we need some special handling for GTT?
> Shared memory is reinitialized and storage of temporary tables is removed.
> It is true for both local and global temp tables.
Of course not. The local temp table cleans up the entire table (including 
catalog buffer and datafile). GTT is not.

> 
>>> 
>>> 
>>>> In my design
>>>> 1 Because different sessions have different transaction information, I 
>>>> choose to store the transaction information of GTT in MyProc,not catalog.
>>>> 2 About the XID wraparound problem, the reason is the design of the temp 
>>>> table storage(local temp table and global temp table) that makes it can 
>>>> not to do vacuum by autovacuum. 
>>>> It should be completely solve at the storage level.
>>>> 
>>> 
>>> My point of view is that vacuuming of temp tables is common problem for 
>>> local and global temp tables. 
>>> So it has to be addressed in the common way and so we should not try to fix 
>>> this problem only for GTT.
>> I think I agree with you this point.
>> However, this does not mean that GTT transaction information stored in 
>> pg_class is correct.
>> If you keep it that way, like in global_private_temp-8.patch, It may cause 
>> data loss in GTT after aotuvauum.
> 
> In my patch autovacuum is prohibited for GTT.
But vacuum GTT is not prohibited. 

> 
>> IMHO forced terminated of client sessions is not acceptable solution.
>>> And it is not an absolutely necessary requirement.
>>> So from my point of view we should not add such limitations to GTT design.
>> This limitation makes it possible for the GTT to do all the DDL.
>> IMHO even oracle's GTT has similar limitations.
> 
> I have checked that Oracle is not preventing creation of index for GTT if 
> there are some active sessions working with this table. And this index 
> becomes visible for all this sessions.
1 Yes The creation of inde gtt has been improved in 
global_temporary_table_v10-pg13.patch
2 But alter GTT ; drop GTT ; drop index on GTT is blocked by other sessions

SQL> drop table gtt;
drop table gtt
   *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use


SQL> ALTER TABLE gtt add b int ; 
ALTER TABLE gtt add b int
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

SQL> drop index idx_gtt;
drop index idx_gtt
   *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

I'm not saying we should do this, but from an implementation perspective we 
face similar issues.
If a dba changes a GTT, he can do it. Therefore, I think it is acceptable to do 
so.

> 
> 
>> As global_private_temp-8.patch, think about:
>> 1 session X tale several hours doing some statistical work with the GTT A, 
>> which generated some data using transaction 100, The work is not over.
>> 2 Then session Y vacuumed A, and the GTT's relfrozenxid (in pg_class) was 
>> updated to 1000 .
>> 3 Then the aotuvacuum happened, the clog  before 1000  was cleaned up.
>> 4 The data in session A could be lost 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年2月2日 上午2:00，Pavel Stehule  写道：
> 
> 
> 
> so 1. 2. 2020 v 14:39 odesílatel 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> napsal:
> 
> 
>> 2020年1月30日 下午10:21，Pavel Stehule > <mailto:pavel.steh...@gmail.com>> 写道：
>> 
>> 
>> 
>> čt 30. 1. 2020 v 15:17 odesílatel 曾文旌(义从) > <mailto:wenjing@alibaba-inc.com>> napsal:
>> 
>> 
>> > 2020年1月29日 下午9:48，Robert Haas > > <mailto:robertmh...@gmail.com>> 写道：
>> > 
>> > On Tue, Jan 28, 2020 at 12:12 PM 曾文旌(义从) > > <mailto:wenjing@alibaba-inc.com>> wrote:
>> >>> Opinion by Pavel
>> >>> + rel->rd_islocaltemp = true;  <<<<<<< if this is valid, then the name 
>> >>> of field "rd_islocaltemp" is not probably best
>> >>> I renamed rd_islocaltemp
>> >> 
>> >> I don't see any change?
>> >> 
>> >> Rename rd_islocaltemp to rd_istemp  in 
>> >> global_temporary_table_v8-pg13.patch
>> > 
>> > In view of commit 6919b7e3294702adc39effd16634b2715d04f012, I think
>> > that this has approximately a 0% chance of being acceptable. If you're
>> > setting a field in a way that is inconsistent with the current use of
>> > the field, you're probably doing it wrong, because the field has an
>> > existing purpose to which new code must conform. And if you're not
>> > doing that, then you don't need to rename it.
>> Thank you for pointing it out.
>> I've rolled back the rename.
>> But I still need rd_localtemp to be true, The reason is that
>> 1 GTT The GTT needs to support DML in read-only transactions ,like local 
>> temp table.
>> 2 GTT does not need to hold the lock before modifying the index buffer ,also 
>> like local temp table.
>> 
>> Please give me feedback.
>> 
>> maybe some like
>> 
>> rel->rd_globaltemp = true;
>> 
>> and somewhere else
>> 
>> if (rel->rd_localtemp || rel->rd_globaltemp)
>> {
>>   ...
>> }
> I tried to optimize code in global_temporary_table_v10-pg13.patch
> 
> 
> Please give me feedback.
> 
> I tested this patch and I have not any objections - from my user perspective 
> it is work as I expect
> 
> +#define RELATION_IS_TEMP(relation) \
> + ((relation)->rd_islocaltemp || \
> + (relation)->rd_rel->relpersistence == RELPERSISTENCE_GLOBAL_TEMP)
>  
> It looks little bit unbalanced
> 
> maybe is better to inject rd_isglobaltemp to relation structure
> 
> and then
> 
> it should to like 
> 
> +#define RELATION_IS_TEMP(relation) \
> + ((relation)->rd_islocaltemp || \
> + (relation)->rd_isglobaltemp))
> 
> But I have not idea if it helps in complex
In my opinion
For local temp table we need (relation)->rd_rel->relpersistence == 
RELPERSISTENCE_TEMP 
and because one local temp table belongs to only one session, need to mark one 
sessions rd_islocaltemp = true ,and other to rd_islocaltemp = false.

But For GTT, just need (relation)->rd_rel->relpersistence == 
RELPERSISTENCE_GLOBAL_GLOBAL_TEMP
One GTT can be used for every session, so no need rd_isglobaltemp anymore. This 
seems duplicated and redundant.

> 
> 
> 
> 
> 
> 
> 
> Wenjing
> 
> 
> 
>> 
>> 
>> 
>> Wenjing
>> 
>> 
>> 
>> 
>> > 
>> > -- 
>> > Robert Haas
>> > EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>
>> > The Enterprise PostgreSQL Company
>> 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年1月27日 下午5:38，Konstantin Knizhnik  写道：
> 
> 
> 
> On 25.01.2020 18:15, 曾文旌(义从) wrote:
>> I wonder why do we need some special check for GTT here.
>>> From my point of view cleanup at startup of local storage of temp tables 
>>> should be performed in the same way for local and global temp tables.
>> After oom kill, In autovacuum, the Isolated local temp table will be cleaned 
>> like orphan temporary tables. The definition of local temp table is deleted 
>> with the storage file. 
>> But GTT can not do that. So we have the this implementation in my patch.
>> If you have other solutions, please let me know.
>> 
> I wonder if it is possible that autovacuum or some other Postgres process is 
> killed by OOM and postmaster is not noticing it can doens't restart Postgres 
> instance?
> as far as I know, crash of any process connected to Postgres shared memory 
> (and autovacuum definitely has such connection) cause Postgres restart.
Postmaster will not restart after oom happen, but the startup process will. GTT 
data files are cleaned up in the startup process.
> 
> 
>> In my design
>> 1 Because different sessions have different transaction information, I 
>> choose to store the transaction information of GTT in MyProc,not catalog.
>> 2 About the XID wraparound problem, the reason is the design of the temp 
>> table storage(local temp table and global temp table) that makes it can not 
>> to do vacuum by autovacuum. 
>> It should be completely solve at the storage level.
>> 
> 
> My point of view is that vacuuming of temp tables is common problem for local 
> and global temp tables. 
> So it has to be addressed in the common way and so we should not try to fix 
> this problem only for GTT.
I think I agree with you this point.
However, this does not mean that GTT transaction information stored in pg_class 
is correct.
If you keep it that way, like in global_private_temp-8.patch, It may cause data 
loss in GTT after aotuvauum.

> 
> 
>> In fact, The dba can still complete the DDL of the GTT.
>> I've provided a set of functions for this case.
>> If the dba needs to modify a GTT A(or drop GTT or create index on GTT), he 
>> needs to do:
>> 1 Use the pg_gtt_attached_pids view to list the pids for the session that is 
>> using the GTT A.
>> 2 Use pg_terminate_backend(pid)terminate they except itself.
>> 3 Do alter GTT A.
>> 
> IMHO forced terminated of client sessions is not acceptable solution.
> And it is not an absolutely necessary requirement.
> So from my point of view we should not add such limitations to GTT design.
This limitation makes it possible for the GTT to do all the DDL.
IMHO even oracle's GTT has similar limitations.

> 
> 
> 
>>> 
>>> What are the reasons of using RowExclusiveLock for GTT instead of 
>>> AccessExclusiveLock?
>>> Yes, GTT data is access only by one backend so no locking here seems to be 
>>> needed at all.
>>> But I wonder what are the motivations/benefits of using weaker lock level 
>>> here?
>> 1 Truncate GTT deletes only the data in the session, so no need use 
>> high-level lock.
>> 2 I think it still needs to be block by DDL of GTT, which is why I use 
>> RowExclusiveLock.
> 
> Sorry, I do not understand your arguments: we do not need exclusive lock 
> because we drop only local (private) data
> but we need some kind of lock. I agree with 1) and not 2).
Yes, we don't need lock for private data, but metadata need.
> 
>> 
>>> There should be no conflicts in any case...
>>> 
>>> +/* We allow to create index on global temp table only this session 
>>> use it */
>>> +if (is_other_backend_use_gtt(heapRelation->rd_node))
>>> +elog(ERROR, "can not create index when have other backend 
>>> attached this global temp table");
>>> +
>>> 
>>> The same argument as in case of dropping GTT: I do not think that 
>>> prohibiting DLL operations on GTT used by more than one backend is bad idea.
>> The idea was to give the GTT almost all the features of a regular table with 
>> few code changes.
>> The current version DBA can still do all DDL for GTT, I've already described.
> 
> I absolutely agree with you that GTT should be given the same features as 
> regular tables.
> The irony is that this most natural and convenient behavior is most easy to 
> implement without putting some extra restrictions.
> Just let indexes for GTT be constructed on demand. It it can be done using 
> the same function used for regular index creation.
The limitation on i

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年1月29日 上午1:54，Pavel Stehule  写道：
> 
> 
> 
> út 28. 1. 2020 v 18:13 odesílatel Pavel Stehule  <mailto:pavel.steh...@gmail.com>> napsal:
> 
> 
> út 28. 1. 2020 v 18:12 odesílatel 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> napsal:
> 
> 
>> 2020年1月29日 上午12:40，Pavel Stehule > <mailto:pavel.steh...@gmail.com>> 写道：
>> 
>> 
>> 
>> út 28. 1. 2020 v 17:01 odesílatel 曾文旌(义从) > <mailto:wenjing@alibaba-inc.com>> napsal:
>> 
>> 
>>> 2020年1月24日 上午4:47，Robert Haas >> <mailto:robertmh...@gmail.com>> 写道：
>>> 
>>> On Sat, Jan 11, 2020 at 8:51 PM Tomas Vondra
>>> mailto:tomas.von...@2ndquadrant.com>> wrote:
>>>> I proposed just ignoring those new indexes because it seems much simpler
>>>> than alternative solutions that I can think of, and it's not like those
>>>> other solutions don't have other issues.
>>> 
>>> +1.
>> I complete the implementation of this feature.
>> When a session x create an index idx_a on GTT A then
>> For session x, idx_a is valid when after create index.
>> For session y, before session x create index done, GTT A has some data, then 
>> index_a is invalid.
>> For session z, before session x create index done, GTT A has no data, then 
>> index_a is valid.
>> 
>>> 
>>>> For example, I've looked at the "on demand" building as implemented in
>>>> global_private_temp-8.patch, I kinda doubt adding a bunch of index build
>>>> calls into various places in index code seems somewht suspicious.
>>> 
>>> +1. I can't imagine that's a safe or sane thing to do.
>>> 
>>> -- 
>>> Robert Haas
>>> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>
>>> The Enterprise PostgreSQL Company
>> 
>> Opinion by Pavel
>> +rel->rd_islocaltemp = true;  <<<<<<< if this is valid, then the name of 
>> field "rd_islocaltemp" is not probably best
>> I renamed rd_islocaltemp
>> 
>> I don't see any change?
> Rename rd_islocaltemp to rd_istemp  in global_temporary_table_v8-pg13.patch
> 
> ok :)
> 
> I found a bug
> 
> postgres=# create global temp table x(a int);
> CREATE TABLE
> postgres=# insert into x values(1);
> INSERT 0 1
> postgres=# create index on x (a);
> CREATE INDEX
> postgres=# create index on x((a + 1));
> CREATE INDEX
> postgres=# analyze x;
> WARNING:  oid 16468 not a relation
> ANALYZE
Thanks for review.

The index expression need to store statistics on index, I missed it and I'll 
fix it later.


Wenjing

> 
> other behave looks well for me.
> 
> Regards
> 
> Pavel
> 
> 
> Pavel
> 
> 
> Wenjing
> 
> 
> 
>> 
>> 
>> 
>> Opinion by Konstantin Knizhnik
>> 1 Fixed comments
>> 2 Fixed assertion
>> 
>> 
>> Please help me review.
>> 
>> 
>> Wenjing
>> 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

Thank you for review patch.

> 2020年1月24日 下午4:20，Konstantin Knizhnik  写道：
> 
> 
> 
> On 23.01.2020 19:28, 曾文旌(义从) wrote:
>> 
>> I'm trying to improve this part of the implementation in 
>> global_temporary_table_v7-pg13.patch
>> Please check my patch and give me feedback.
>> 
>> 
>> Thanks
>> 
>> Wenjing
>> 
>> 
> 
> Below is my short review of the patch:
> 
> +/*
> + * For global temp table only
> + * use AccessExclusiveLock for ensure safety
> + */
> +{
> +{
> +"on_commit_delete_rows",
> +"global temp table on commit options",
> +RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
> +ShareUpdateExclusiveLock
> +},
> +true
> +},
> 
> 
> The comment seems to be confusing: it says about AccessExclusiveLock but 
> actually uses ShareUpdateExclusiveLock.
There is a problem with the comment description, I will fix it.

> 
> -Assert(TransactionIdIsNormal(onerel->rd_rel->relfrozenxid));
> -Assert(MultiXactIdIsValid(onerel->rd_rel->relminmxid));
> +Assert((RELATION_IS_GLOBAL_TEMP(onerel) && onerel->rd_rel->relfrozenxid 
> == InvalidTransactionId) ||
> +(!RELATION_IS_GLOBAL_TEMP(onerel) && 
> TransactionIdIsNormal(onerel->rd_rel->relfrozenxid)));
> +Assert((RELATION_IS_GLOBAL_TEMP(onerel) && onerel->rd_rel->relminmxid == 
> InvalidMultiXactId) ||
> +(!RELATION_IS_GLOBAL_TEMP(onerel) && 
> MultiXactIdIsValid(onerel->rd_rel->relminmxid)));
>  
> It is actually equivalent to:
> 
> Assert(RELATION_IS_GLOBAL_TEMP(onerel) ^ 
> TransactionIdIsNormal(onerel->rd_rel->relfrozenxid);
> Assert(RELATION_IS_GLOBAL_TEMP(onerel) ^ 
> MultiXactIdIsValid(onerel->rd_rel->relminmxid));
Yes, Thank you for your points out, It's simpler.

> 
> +/* clean temp relation files */
> +if (max_active_gtt > 0)
> +RemovePgTempFiles();
> +
>  /*
>  
> I wonder why do we need some special check for GTT here.
> From my point of view cleanup at startup of local storage of temp tables 
> should be performed in the same way for local and global temp tables.
After oom kill, In autovacuum, the Isolated local temp table will be cleaned 
like orphan temporary tables. The definition of local temp table is deleted 
with the storage file. 
But GTT can not do that. So we have the this implementation in my patch.
If you have other solutions, please let me know.

> 
> 
> -new_rel_reltup->relfrozenxid = relfrozenxid;
> -new_rel_reltup->relminmxid = relminmxid;
> +/* global temp table not remember transaction info in catalog */
> +if (relpersistence == RELPERSISTENCE_GLOBAL_TEMP)
> +{
> +new_rel_reltup->relfrozenxid = InvalidTransactionId;
> +new_rel_reltup->relminmxid = InvalidMultiXactId;
> +}
> +else
> +{
> +new_rel_reltup->relfrozenxid = relfrozenxid;
> +new_rel_reltup->relminmxid = relminmxid;
> +}
> +
> 
> 
> Why do we need to do it for GTT?
> Did you check that there will be no problems with GTT in case of XID 
> wraparound?
> Right now if you create temp table and keep session open, then it will block 
> XID wraparound.
In my design
1 Because different sessions have different transaction information, I choose 
to store the transaction information of GTT in MyProc,not catalog.
2 About the XID wraparound problem, the reason is the design of the temp table 
storage(local temp table and global temp table) that makes it can not to do 
vacuum by autovacuum. 
It should be completely solve at the storage level.

> 
> +/* We allow to drop global temp table only this session use it */
> +if (RELATION_IS_GLOBAL_TEMP(rel))
> +{
> +if (is_other_backend_use_gtt(rel->rd_node))
> +elog(ERROR, "can not drop relation when other backend attached 
> this global temp table");
> +}
> +
> 
> Here we once again introduce incompatibility with normal (permanent) tables.
> Assume that DBA or programmer need to change format of GTT. But there are 
> some active sessions which have used this GTT sometime in the past.
> We will not be able to drop this GTT until all this sessions are terminated.
> I do not think that it is acceptable behaviour.
In fact, The dba can still complete the DDL of the GTT.
I've provided a set of functions for this case.
If the dba needs to modify a GTT A(or drop GTT or create index on GTT), he 
needs to do:
1 Use the pg_gtt_attached_pids view to list the pids for the session that is 
using the GTT A.
2 Use pg_terminate_backend(pid)

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年1月22日 上午2:51，Pavel Stehule  写道：
> 
> 
> 
> út 21. 1. 2020 v 9:46 odesílatel 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> napsal:
> 
> 
>> 2020年1月12日 上午4:27，Pavel Stehule > <mailto:pavel.steh...@gmail.com>> 写道：
>> 
>> Hi
>> 
>> so 11. 1. 2020 v 15:00 odesílatel 曾文旌(义从) > <mailto:wenjing@alibaba-inc.com>> napsal:
>> Hi all
>> 
>> This is the latest patch
>> 
>> The updates are as follows:
>> 1. Support global temp Inherit table global temp partition table
>> 2. Support serial column in GTT
>> 3. Provide views pg_gtt_relstats pg_gtt_stats for GTT’s statistics
>> 4. Provide view pg_gtt_attached_pids to manage GTT
>> 5. Provide function pg_list_gtt_relfrozenxids() to manage GTT
>> 6. Alter GTT or rename GTT is allowed under some conditions
>> 
>> 
>> Please give me feedback.
>> 
>> I tested the functionality
>> 
>> 1. i think so "ON COMMIT PRESERVE ROWS" should be default mode (like local 
>> temp tables).
> 
> ON COMMIT PRESERVE ROWS is default mode now.
> 
> Thank you
> 
> * I tried to create global temp table with index. When I tried to drop this 
> table (and this table was used by second instance), then I got error message
> 
> postgres=# drop table foo;
> ERROR:  can not drop index when other backend attached this global temp table
> 
> It is expected, but it is not too much user friendly. Is better to check if 
> you can drop table, then lock it, and then drop all objects.
I don't understand what needs to be improved. Could you describe it in detail?

> 
> * tab complete can be nice for CREATE GLOBAL TEMP table
Yes, I will improve it.
> 
> \dt+ \di+ doesn't work correctly, or maybe I don't understand to the 
> implementation.
> 

postgres=# create table t(a int primary key);
CREATE TABLE
postgres=# create global temp table gt(a int primary key);
CREATE TABLE
postgres=# insert into t values(generate_series(1,1));
INSERT 0 1
postgres=# insert into gt values(generate_series(1,1));
INSERT 0 1

postgres=# \dt+
List of relations
 Schema | Name | Type  |Owner| Persistence |  Size  | Description 
+--+---+-+-++-
 public | gt   | table | wenjing.zwj | session | 384 kB | 
 public | t| table | wenjing.zwj | permanent   | 384 kB | 
(2 rows)

postgres=# \di+
  List of relations
 Schema |  Name   | Type  |Owner| Table | Persistence |  Size  | 
Description 
+-+---+-+---+-++-
 public | gt_pkey | index | wenjing.zwj | gt| session | 240 kB | 
 public | t_pkey  | index | wenjing.zwj | t | permanent   | 240 kB | 
(2 rows)


> I see same size in all sessions. Global temp tables shares same files?
No, they use their own files.
But \dt+ \di+ counts the total file sizes in all sessions for each GTT.



Wenjing

> 
> Regards
> 
> Pavel
> 
> 
> 
> 
> 
> Wenjing
> 
> 
> 
>> 
>> I tested some simple scripts 
>> 
>> test01.sql
>> 
>> CREATE TEMP TABLE foo(a int, b int);
>> INSERT INTO foo SELECT random()*100, random()*1000 FROM 
>> generate_series(1,1000);
>> ANALYZE foo;
>> SELECT sum(a), sum(b) FROM foo;
>> DROP TABLE foo; -- simulate disconnect
>> 
>> 
>> after 100 sec, the table pg_attribute has 3.2MB
>> and 64 tps, 6446 transaction
>> 
>> test02.sql
>> 
>> INSERT INTO foo SELECT random()*100, random()*1000 FROM 
>> generate_series(1,1000);
>> ANALYZE foo;
>> SELECT sum(a), sum(b) FROM foo;
>> DELETE FROM foo; -- simulate disconnect
>> 
>> 
>> after 100 sec, 1688 tps, 168830 transactions
>> 
>> So performance is absolutely different as we expected.
>> 
>> From my perspective, this functionality is great.
>> 
>> Todo:
>> 
>> pg_table_size function doesn't work
>> 
>> Regards
>> 
>> Pavel
>> 
>> 
>> Wenjing
>> 
>> 
>> 
>> 
>> 
>>> 2020年1月6日 上午4:06，Tomas Vondra >> <mailto:tomas.von...@2ndquadrant.com>> 写道：
>>> 
>>> Hi,
>>> 
>>> I think we need to do something with having two patches aiming to add
>>> global temporary tables:
>>> 
>>> [1] https://commitfest.postgresql.org/26/2349/ 
>>> <https://commitfest.postgresql.org/26/2349/>
>>> 
>>> [2] https://commitfest.postgresql.org/26/2233/ 
>>> <https://commitfest.postgresql.org/26/2233/>
>>> 
>>>

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年1月21日 下午1:43，Pavel Stehule  写道：
> 
> Hi
> 
> I have a free time this evening, so I will check this patch
> 
> I have a one question
> 
> + /* global temp table get relstats from localhash */
> + if (RELATION_IS_GLOBAL_TEMP(rel))
> + {
> + get_gtt_relstats(RelationGetRelid(rel),
> + &relpages, &reltuples, &relallvisible,
> + NULL, NULL);
> + }
> + else
> + {
> + /* coerce values in pg_class to more desirable types */
> + relpages = (BlockNumber) rel->rd_rel->relpages;
> + reltuples = (double) rel->rd_rel->reltuples;
> + relallvisible = (BlockNumber) rel->rd_rel->relallvisible;
> + }
> 
> Isbn't possible to fill the rd_rel structure too, so this branching can be 
> reduced?
I'll make some improvements to optimize this part of the code.

> 
> Regards
> 
> Pavel
> 
> po 20. 1. 2020 v 17:27 odesílatel 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> napsal:
> 
> 
> > 2020年1月20日 上午1:32，Erik Rijkers mailto:e...@xs4all.nl>> 写道：
> > 
> > On 2020-01-19 18:04, 曾文旌(义从) wrote:
> >>> 2020年1月14日 下午9:20，Pavel Stehule  >>> <mailto:pavel.steh...@gmail.com>> 写道：
> >>> út 14. 1. 2020 v 14:09 odesílatel 曾文旌(义从)  >>> <mailto:wenjing@alibaba-inc.com> <mailto:wenjing@alibaba-inc.com 
> >>> <mailto:wenjing@alibaba-inc.com>>> napsal:
> > 
> >>> [global_temporary_table_v4-pg13.patch ]
> > 
> > Hi,
> > 
> > This patch doesn't quiet apply for me:
> > 
> > patching file src/backend/access/common/reloptions.c
> > patching file src/backend/access/gist/gistutil.c
> > patching file src/backend/access/hash/hash.c
> > Hunk #1 succeeded at 149 (offset 3 lines).
> > patching file src/backend/access/heap/heapam_handler.c
> > patching file src/backend/access/heap/vacuumlazy.c
> > patching file src/backend/access/nbtree/nbtpage.c
> > patching file src/backend/access/table/tableam.c
> > patching file src/backend/access/transam/xlog.c
> > patching file src/backend/catalog/Makefile
> > Hunk #1 FAILED at 44.
> > 1 out of 1 hunk FAILED -- saving rejects to file 
> > src/backend/catalog/Makefile.rej
> > [...]
> >   (The rest applies without errors)
> > 
> > src/backend/catalog/Makefile.rej contains:
> > 
> > 
> > --- src/backend/catalog/Makefile
> > +++ src/backend/catalog/Makefile
> > @@ -44,6 +44,8 @@ OBJS = \
> >   storage.o \
> >   toasting.o
> > 
> > +OBJS += storage_gtt.o
> > +
> > BKIFILES = postgres.bki postgres.description postgres.shdescription
> > 
> > include $(top_srcdir)/src/backend/common.mk <http://common.mk/>
> > 
> > 
> > Can you have a look?
> I updated the code and remade the patch.
> Please give me feedback if you have any more questions.
> 
> 
> 
> 
> > 
> > 
> > thanks,
> > 
> > Erik Rijkers
> > 
> > 
> > 
> > 
> > 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年1月13日 下午4:08，Konstantin Knizhnik  写道：
> 
> 
> 
> On 12.01.2020 4:51, Tomas Vondra wrote:
>> On Fri, Jan 10, 2020 at 11:47:42AM +0300, Konstantin Knizhnik wrote:
>>> 
>>> 
>>> On 09.01.2020 19:48, Tomas Vondra wrote:
 
> The most complex and challenged task is to support GTT for all kind of 
> indexes. Unfortunately I can not proposed some good universal solution 
> for it.
> Just patching all existed indexes implementation seems to be the only 
> choice.
> 
 
 I haven't looked at the indexing issue closely, but IMO we need to
 ensure that every session sees/uses only indexes on GTT that were
 defined before the seesion started using the table.
>>> 
>>> Why? It contradicts with behavior of normal tables.
>>> Assume that you have active clients and at some point of time DBA 
>>> recognizes that them are spending to much time in scanning some GTT.
>>> It cab create index for this GTT but if existed client will not be able to 
>>> use this index, then we need somehow make this clients to restart their 
>>> sessions?
>>> In my patch I have implemented building indexes for GTT on demand: if 
>>> accessed index on GTT is not yet initialized, then it is filled with local 
>>> data.
>> 
>> Yes, I know the behavior would be different from behavior for regular
>> tables. And yes, it would not allow fixing slow queries in sessions
>> without interrupting those sessions.
>> 
>> I proposed just ignoring those new indexes because it seems much simpler
>> than alternative solutions that I can think of, and it's not like those
>> other solutions don't have other issues.
> 
> Quit opposite: prohibiting sessions to see indexes created before session 
> start to use GTT requires more efforts. We need to somehow maintain and check 
> GTT first access time.
> 
>> 
>> For example, I've looked at the "on demand" building as implemented in
>> global_private_temp-8.patch, I kinda doubt adding a bunch of index build
>> calls into various places in index code seems somewht suspicious.
> 
> We in any case has to initialize GTT indexes on demand even if we prohibit 
> usages of indexes created after first access by session to GTT.
> So the difference is only in one thing: should we just initialize empty index 
> or populate it with local data (if rules for index usability are the same for 
> GTT as for normal tables).
> From implementation point of view there is no big difference. Actually 
> building index in standard way is even simpler than constructing empty index. 
> Originally I have implemented
> first approach (I just forgot to consider case when GTT was already user by a 
> session). Then I rewrited it using second approach and patch even became 
> simpler.
> 
>> 
>> * brinbuild is added to brinRevmapInitialize, which is meant to
>>   initialize state for scanning. It seems wrong to build the index we're
>>   scanning from this function (layering and all that).
>> 
>> * btbuild is called from _bt_getbuf. That seems a bit ... suspicious?
> 
> 
> As I already mentioned - support of indexes for GTT is one of the most 
> challenged things in my patch.
> I didn't find good and universal solution. So I agreed that call of btbuild 
> from _bt_getbuf may be considered as suspicious.
> I will be pleased if you or sombody else can propose better elternative and 
> not only for B-Tree, but for all other indexes.
> 
> But as I already wrote above, prohibiting session to used indexes created 
> after first access to GTT doesn't solve the problem.
> For normal tables (and for local temp tables) indexes are initialized at the 
> time of their creation.
> With GTT it doesn't work, because each session has its own local data of GTT.
> We should either initialize/build index on demand (when it is first 
> accessed), either at the moment of session start initialize indexes for all 
> existed GTTs.
> Last options seem to be much worser from my point of view: there may me huge 
> number of GTT and session may not need to access GTT at all.
>> 
>> ... and so on for other index types. Also, what about custom indexes
>> implemented in extensions? It seems a bit strange each of them has to
>> support this separately.
> 
> I have already complained about it: my patch supports GTT for all built-in 
> indexes, but custom indexes has to handle it themselves.
> Looks like to provide some generic solution we need to extend index API, 
> providing two diffrent operations: creation and initialization.
> But extending index API is very critical change... And also it doesn't solve 
> the problem with all existed extensions: them in any case have
> to be rewritten to implement new API version in order to support GTT.
>> 
>> IMHO if this really is the right solution, we need to make it work for
>> existing indexes without having to tweak them individually. Why don't we
>> track a flag whether an index on GTT was initialized in a given session,
>> and if it was not then call the build function before calling an

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年1月14日 下午9:20，Pavel Stehule  写道：
> 
> 
> 
> út 14. 1. 2020 v 14:09 odesílatel 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> napsal:
> Thank you for review my patch.
> 
> 
>> 2020年1月12日 上午4:27，Pavel Stehule > <mailto:pavel.steh...@gmail.com>> 写道：
>> 
>> Hi
>> 
>> so 11. 1. 2020 v 15:00 odesílatel 曾文旌(义从) > <mailto:wenjing@alibaba-inc.com>> napsal:
>> Hi all
>> 
>> This is the latest patch
>> 
>> The updates are as follows:
>> 1. Support global temp Inherit table global temp partition table
>> 2. Support serial column in GTT
>> 3. Provide views pg_gtt_relstats pg_gtt_stats for GTT’s statistics
>> 4. Provide view pg_gtt_attached_pids to manage GTT
>> 5. Provide function pg_list_gtt_relfrozenxids() to manage GTT
>> 6. Alter GTT or rename GTT is allowed under some conditions
>> 
>> 
>> Please give me feedback.
>> 
>> I tested the functionality
>> 
>> 1. i think so "ON COMMIT PRESERVE ROWS" should be default mode (like local 
>> temp tables).
> makes sense, I will fix it.
> 
>> 
>> I tested some simple scripts 
>> 
>> test01.sql
>> 
>> CREATE TEMP TABLE foo(a int, b int);
>> INSERT INTO foo SELECT random()*100, random()*1000 FROM 
>> generate_series(1,1000);
>> ANALYZE foo;
>> SELECT sum(a), sum(b) FROM foo;
>> DROP TABLE foo; -- simulate disconnect
>> 
>> 
>> after 100 sec, the table pg_attribute has 3.2MB
>> and 64 tps, 6446 transaction
>> 
>> test02.sql
>> 
>> INSERT INTO foo SELECT random()*100, random()*1000 FROM 
>> generate_series(1,1000);
>> ANALYZE foo;
>> SELECT sum(a), sum(b) FROM foo;
>> DELETE FROM foo; -- simulate disconnect
>> 
>> 
>> after 100 sec, 1688 tps, 168830 transactions
>> 
>> So performance is absolutely different as we expected.
>> 
>> From my perspective, this functionality is great.
> Yes, frequent ddl causes catalog bloat, GTT avoids this problem.
> 
>> 
>> Todo:
>> 
>> pg_table_size function doesn't work
> Do you mean that function pg_table_size() need get the storage space used by 
> the one GTT in the entire db(include all session) .
> 
> It's question how much GTT tables should be similar to classic tables. But 
> the reporting in psql should to work \dt+, \l+, \di+
Get it, I will fix it.
> 
> 
> 
>> 
>> Regards
>> 
>> Pavel
>> 
>> 
>> Wenjing
>> 
>> 
>> 
>> 
>> 
>>> 2020年1月6日 上午4:06，Tomas Vondra >> <mailto:tomas.von...@2ndquadrant.com>> 写道：
>>> 
>>> Hi,
>>> 
>>> I think we need to do something with having two patches aiming to add
>>> global temporary tables:
>>> 
>>> [1] https://commitfest.postgresql.org/26/2349/ 
>>> <https://commitfest.postgresql.org/26/2349/>
>>> 
>>> [2] https://commitfest.postgresql.org/26/2233/ 
>>> <https://commitfest.postgresql.org/26/2233/>
>>> 
>>> As a reviewer I have no idea which of the threads to look at - certainly
>>> not without reading both threads, which I doubt anyone will really do.
>>> The reviews and discussions are somewhat intermixed between those two
>>> threads, which makes it even more confusing.
>>> 
>>> I think we should agree on a minimal patch combining the necessary/good
>>> bits from the various patches, and terminate one of the threads (i.e.
>>> mark it as rejected or RWF). And we need to do that now, otherwise
>>> there's about 0% chance of getting this into v13.
>>> 
>>> In general, I agree with the sentiment Rober expressed in [1] - the
>>> patch needs to be as small as possible, not adding "nice to have"
>>> features (like support for parallel queries - I very much doubt just
>>> using shared instead of local buffers is enough to make it work.)
>>> 
>>> regards
>>> 
>>> -- 
>>> Tomas Vondra  http://www.2ndQuadrant.com 
>>> <http://www.2ndquadrant.com/>
>>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>> 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年1月12日 上午9:14，Tomas Vondra  写道：
> 
> On Fri, Jan 10, 2020 at 03:24:34PM +0300, Konstantin Knizhnik wrote:
>> 
>> 
>> On 09.01.2020 19:30, Tomas Vondra wrote:
>> 
>> 
>>> 
 
> 
>> 3 Still no one commented on GTT's transaction information processing, 
>> they include
>> 3.1 Should gtt's frozenxid need to be care?
>> 3.2 gtt’s clog clean
>> 3.3 How to deal with "too old" gtt data
>> 
> 
> No idea what to do about this.
> 
 
 I wonder what is the specific of GTT here?
 The same problem takes place for normal (local) temp tables, doesn't it?
 
>>> 
>>> Not sure. TBH I'm not sure I understand what the issue actually is.
>> 
>> Just open session, create temporary table and insert some data in it.
>> Then in other session run 2^31 transactions (at my desktop it takes about 2 
>> hours).
>> As far as temp tables are not proceeded by vacuum, database is stalled:
>> 
>>  ERROR:  database is not accepting commands to avoid wraparound data loss in 
>> database "postgres"
>> 
>> It seems to be quite dubious behavior and it is strange to me that nobody 
>> complains about it.
>> We discuss  many issues related with temp tables (statistic, parallel 
>> queries,...) which seems to be less critical.
>> 
>> But this problem is not specific to GTT - it can be reproduced with normal 
>> (local) temp tables.
>> This is why I wonder why do we need to solve it in GTT patch.
>> 
> 
> Yeah, I think that's out of scope for GTT patch. Once we solve it for
> plain temporary tables, we'll solve it for GTT too.
1. The core problem is that the data contains transaction information (xid), 
which needs to be vacuum(freeze) regularly to avoid running out of xid.
The autovacuum supports vacuum regular table but local temp does not. 
autovacuum also does not support GTT.

2. However, the difference between the local temp table and the global temp 
table(GTT) is that
a) For local temp table: one table hava one piece of data. the frozenxid of one 
local temp table is store in the catalog(pg_class). 
b) For global temp table: each session has a separate copy of data, one GTT may 
contain maxbackend frozenxid.
and I don't think it's a good idea to keep frozenxid of GTT in the 
catalog(pg_class). 
It becomes a question: how to handle GTT transaction information?

I agree that problem 1 should be completely solved by a some feature, such as 
local transactions. It is definitely not included in the GTT patch.

But, I think we need to ensure the durability of GTT data. For example, data in 
GTT cannot be lost due to the clog being cleaned up. It belongs to problem 2.



Wenjing


> 
> regards
> 
> -- 
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

Thank you for review my patch.


> 2020年1月12日 上午4:27，Pavel Stehule  写道：
> 
> Hi
> 
> so 11. 1. 2020 v 15:00 odesílatel 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> napsal:
> Hi all
> 
> This is the latest patch
> 
> The updates are as follows:
> 1. Support global temp Inherit table global temp partition table
> 2. Support serial column in GTT
> 3. Provide views pg_gtt_relstats pg_gtt_stats for GTT’s statistics
> 4. Provide view pg_gtt_attached_pids to manage GTT
> 5. Provide function pg_list_gtt_relfrozenxids() to manage GTT
> 6. Alter GTT or rename GTT is allowed under some conditions
> 
> 
> Please give me feedback.
> 
> I tested the functionality
> 
> 1. i think so "ON COMMIT PRESERVE ROWS" should be default mode (like local 
> temp tables).
makes sense, I will fix it.

> 
> I tested some simple scripts 
> 
> test01.sql
> 
> CREATE TEMP TABLE foo(a int, b int);
> INSERT INTO foo SELECT random()*100, random()*1000 FROM 
> generate_series(1,1000);
> ANALYZE foo;
> SELECT sum(a), sum(b) FROM foo;
> DROP TABLE foo; -- simulate disconnect
> 
> 
> after 100 sec, the table pg_attribute has 3.2MB
> and 64 tps, 6446 transaction
> 
> test02.sql
> 
> INSERT INTO foo SELECT random()*100, random()*1000 FROM 
> generate_series(1,1000);
> ANALYZE foo;
> SELECT sum(a), sum(b) FROM foo;
> DELETE FROM foo; -- simulate disconnect
> 
> 
> after 100 sec, 1688 tps, 168830 transactions
> 
> So performance is absolutely different as we expected.
> 
> From my perspective, this functionality is great.
Yes, frequent ddl causes catalog bloat, GTT avoids this problem.

> 
> Todo:
> 
> pg_table_size function doesn't work
Do you mean that function pg_table_size() need get the storage space used by 
the one GTT in the entire db(include all session) .

> 
> Regards
> 
> Pavel
> 
> 
> Wenjing
> 
> 
> 
> 
> 
>> 2020年1月6日 上午4:06，Tomas Vondra > <mailto:tomas.von...@2ndquadrant.com>> 写道：
>> 
>> Hi,
>> 
>> I think we need to do something with having two patches aiming to add
>> global temporary tables:
>> 
>> [1] https://commitfest.postgresql.org/26/2349/ 
>> <https://commitfest.postgresql.org/26/2349/>
>> 
>> [2] https://commitfest.postgresql.org/26/2233/ 
>> <https://commitfest.postgresql.org/26/2233/>
>> 
>> As a reviewer I have no idea which of the threads to look at - certainly
>> not without reading both threads, which I doubt anyone will really do.
>> The reviews and discussions are somewhat intermixed between those two
>> threads, which makes it even more confusing.
>> 
>> I think we should agree on a minimal patch combining the necessary/good
>> bits from the various patches, and terminate one of the threads (i.e.
>> mark it as rejected or RWF). And we need to do that now, otherwise
>> there's about 0% chance of getting this into v13.
>> 
>> In general, I agree with the sentiment Rober expressed in [1] - the
>> patch needs to be as small as possible, not adding "nice to have"
>> features (like support for parallel queries - I very much doubt just
>> using shared instead of local buffers is enough to make it work.)
>> 
>> regards
>> 
>> -- 
>> Tomas Vondra  http://www.2ndQuadrant.com 
>> <http://www.2ndquadrant.com/>
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2020年1月6日 下午8:17，Dean Rasheed  写道：
> 
> On Mon, 6 Jan 2020 at 11:01, Tomas Vondra  
> wrote:
>> 
>> On Mon, Jan 06, 2020 at 01:04:15PM +0800, 曾文旌(义从) wrote:
>> 
>>> 2 We feel that gtt needs to maintain statistics, but there is no
>>> agreement on what it will be done.
>>> 
>> 
>> I certainly agree GTT needs to maintain statistics, otherwise it'll lead
>> to poor query plans.
> 
> +1
> 
>> AFAIK the current patch stores the info in a hash
>> table in a backend private memory, and I don't see how else to do that
>> (e.g. storing it in a catalog would cause catalog bloat).
>> 
> 
> It sounds like it needs a pair of system GTTs to hold the table and
> column statistics for other GTTs. One would probably have the same
> columns as pg_statistic, and the other just the relevant columns from
> pg_class. I can see it being useful for the user to be able to see
> these stats, so perhaps they could be UNIONed into the existing stats
> view.
The current patch provides several functions as extension(pg_gtt) for read gtt 
statistics. 
Next I can move them to the kernel and let the view pg_stats can see gtt’s 
statistics.


> Regards,
> Dean

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

In the previous communication

1 we agreed on the general direction
1.1 gtt use local (private) buffer
1.2 no replica access in first version

2 We feel that gtt needs to maintain statistics, but there is no agreement on 
what it will be done.

3 Still no one commented on GTT's transaction information processing, they 
include
3.1 Should gtt's frozenxid need to be care?
3.2 gtt’s clog clean
3.3 How to deal with "too old" gtt data

I suggest we discuss further, reach an agreement, and merge the two patches to 
one.


Wenjing


> 2020年1月6日 上午4:06，Tomas Vondra  写道：
> 
> Hi,
> 
> I think we need to do something with having two patches aiming to add
> global temporary tables:
> 
> [1] https://commitfest.postgresql.org/26/2349/
> 
> [2] https://commitfest.postgresql.org/26/2233/
> 
> As a reviewer I have no idea which of the threads to look at - certainly
> not without reading both threads, which I doubt anyone will really do.
> The reviews and discussions are somewhat intermixed between those two
> threads, which makes it even more confusing.
> 
> I think we should agree on a minimal patch combining the necessary/good
> bits from the various patches, and terminate one of the threads (i.e.
> mark it as rejected or RWF). And we need to do that now, otherwise
> there's about 0% chance of getting this into v13.
> 
> In general, I agree with the sentiment Rober expressed in [1] - the
> patch needs to be as small as possible, not adding "nice to have"
> features (like support for parallel queries - I very much doubt just
> using shared instead of local buffers is enough to make it work.)
> 
> regards
> 
> -- 
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

My comments for global_private_temp-4.patch

good side:
1 Lots of  index type on GTT. I think we need support for all kinds of indexes.
2 serial column on GTT.
3 INHERITS GTT.
4 PARTITION GTT.

I didn't choose to support them in the first release, but you did.

Other side:
1 case: create global temp table gtt2(a int primary key, b text) on commit 
delete rows;
I think you've lost the meaning of the on commit delete rows clause.
After the GTT is created, the other sessions feel that this is an on commit 
PRESERVE rows GTT.

2 truncate gtt, mybe this is a bug in DropRelFileNodeBuffers.
GTT's local buffer is not released.
Case:
postgres=# insert into gtt2 values(1,'xx');
INSERT 0 1
postgres=# truncate gtt2;
TRUNCATE TABLE
postgres=# insert into gtt2 values(1,'xx');
ERROR:  unexpected data beyond EOF in block 0 of relation base/13579/t3_16384
HINT:  This has been seen to occur with buggy kernels; consider updating your 
system.

3  lock type of truncate GTT.
I don't think it's a good idea to hold a big lock with truncate GTT, because it 
only needs to process private data.

4 GTT's ddl Those ddl that need to rewrite data files may need attention.
We have discussed in the previous email. This is why I used shared hash to 
track the GTT file.


5 There will be problems with DDL that will change relfilenode. Such as cluster 
GTT ,vacuum full GTT.
A session completes vacuum full gtt(a), and other sessions will immediately 
start reading and writing new storage files and existing data is also lost.
I disable them in my current version.

6 drop GTT
I think drop GTT should clean up all storage files and definitions. How do you 
think?

7 MVCC visibility clog clean
GTT data visibility rules, like regular tables, so GTT also need clog.
We need to avoid the clog that GTT needs to be cleaned up. 
At the same time, GTT does not do autovacuum, and retaining "too old data" will 
cause wraparound data loss.
I have given a solution in my design.


Zeng Wenjing

> 2019年11月1日 下午11:15，Konstantin Knizhnik  写道：
> 
> 
> 
> On 25.10.2019 20:00, Pavel Stehule wrote:
>> 
>> >
>> >> So except the limitation mentioned above (which I do not consider as 
>> >> critical) there is only one problem which was not addressed: maintaining 
>> >> statistics for GTT.
>> >> If all of the following conditions are true:
>> >>
>> >> 1) GTT are used in joins
>> >> 2) There are indexes defined for GTT
>> >> 3) Size and histogram of GTT in different backends can significantly vary.
>> >> 4) ANALYZE was explicitly called for GTT
>> >>
>> >> then query execution plan built in one backend will be also used for 
>> >> other backends where it can be inefficient.
>> >> I also do not consider this problem as "show stopper" for adding GTT to 
>> >> Postgres.
>> > I think that's *definitely* a show stopper.
>> Well, if both you and Pavel think that it is really "show stopper", then 
>> this problem really has to be addressed.
>> I slightly confused about this opinion, because Pavel has told me 
>> himself that 99% of users never create indexes for temp tables
>> or run "analyze" for them. And without it, this problem is not a problem 
>> at all.
>> 
>> 
>> Users doesn't do ANALYZE on temp tables in 99%. It's true. But second fact 
>> is so users has lot of problems. It's very similar to wrong statistics on 
>> persistent tables. When data are small, then it is not problem for users, 
>> although from my perspective it's not optimal. When data are not small, then 
>> the problem can be brutal. Temporary tables are not a exception. And users 
>> and developers are people - we know only about fatal problems. There are lot 
>> of unoptimized queries, but because the problem is not fatal, then it is not 
>> reason for report it. And lot of people has not any idea how fast the 
>> databases can be. The knowledges of  users and app developers are sad book.
>> 
>> Pavel
> 
> It seems to me that I have found quite elegant solution for per-backend 
> statistic for GTT: I just inserting it in backend's catalog cache, but not in 
> pg_statistic table itself.
> To do it I have to add InsertSysCache/InsertCatCache functions which insert 
> pinned entry in the correspondent cache.
> I wonder if there are some pitfalls of such approach?
> 
> New patch for GTT is attached.
> -- 
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com 
> 
> The Russian Postgres Company 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2019年11月7日 下午5:40，Pavel Stehule  写道：
> 
> 
> 
> čt 7. 11. 2019 v 10:30 odesílatel 曾文旌(义从)  <mailto:wenjing@alibaba-inc.com>> napsal:
> 
> 
> > 2019年11月7日 上午12:08，Konstantin Knizhnik  > <mailto:k.knizh...@postgrespro.ru>> 写道：
> > 
> > 
> > 
> > On 06.11.2019 16:24, 曾文旌(义从) wrote:
> >> Dear Hackers
> >> 
> >> 
> >> I attached the patch of GTT implementationI base on PG12.
> >> The GTT design came from my first email.
> >> Some limitations in patch will be eliminated in later versions.
> >> 
> >> Later, I will comment on Konstantin's patch and make some proposals for 
> >> cooperation.
> >> Looking forward to your feedback.
> >> 
> >> Thanks.
> >> 
> >> Zeng Wenjing
> >> 
> > 
> > Thank you for this patch.
> > My first comments:
> > 
> > 1.  I have ported you patch to the latest Postgres version (my patch is 
> > attached).
> > 2. You patch is supporting only B-Tree index for GTT. All other indexes 
> > (hash, gin, gist, brin,...) are not currently supported.
> Currently I only support btree index.
> I noticed that your patch supports more index types, which is where I'd like 
> to work with you.
> 
> > 3. I do not understand the reason for the following limitation:
> > "We allow to create index on global temp table only this session use it"
> > 
> > First of all it seems to significantly reduce usage of global temp tables.
> > Why do we need GTT at all? Mostly because we need to access temporary data 
> > in more than one backend. Otherwise we can just use normal table.
> > If temp table is expected to be larger enough, so that we need to create 
> > index for it, then it is hard to believe that it will be needed only in one 
> > backend.
> > 
> > May be the assumption is that all indexes has to be created before GTT 
> > start to be used.
> Yes, Currently, GTT's index is only supported and created in an empty table 
> state, and other sessions are not using it.
> There has two improvements pointer:
> 1 Index can create on GTT(A) when the GTT(A)  in the current session is not 
> empty, requiring the GTT table to be empty in the other session.
> Index_build needs to be done in the current session just like a normal table. 
> This improvement is relatively easy.
> 
> 2 Index can create on GTT(A)  when more than one session are using this 
> GTT(A).
> Because when I'm done creating an index of the GTT in this session and 
> setting it to be an valid index, it's not true for the GTT in other sessions.
> Indexes on gtt in other sessions require "rebuild_index" before using it. 
> I don't have a better solution right now, maybe you have some suggestions.
> 
> I think so DDL operations can be implemented in some reduced form - so DDL 
> are active only for one session, and for other sessions are invisible. 
> Important is state of GTT object on session start. 
> 
> For example ALTER TABLE DROP COLUMN can has very fatal impact on other 
> sessions. So I think the best of GTT can be pattern - the structure of GTT 
> table is immutable for any session that doesn't do DDL operations.
Yes, Those ddl that need to rewrite data files will have this problem.
This is why I disabled alter GTT in the current version.
It can be improved, such as Alter GTT can also be allowed when only the current 
session is in use.
Users can also choose to kick off other sessions that are using gtt, then do 
alter GTT.
I provide a function(pg_gtt_attached_pid(relation, schema)) to query which 
session a GTT is being used by.

> 
> 
> 
> > But right now this check is not working correctly in any case - if you 
> > insert some data into the table, then
> > you can not create index any more:
> > 
> > postgres=# create global temp table gtt(x integer primary key, y integer);
> > CREATE TABLE
> > postgres=# insert into gtt values (generate_series(1,10), 
> > generate_series(1,10));
> > INSERT 0 10
> > postgres=# create index on gtt(y);
> > ERROR:  can not create index when have one or more backend attached this 
> > global temp table
> > 
> > I wonder why do you need such restriction?
> > 
> > 
> > -- 
> > Konstantin Knizhnik
> > Postgres Professional: http://www.postgrespro.com 
> > <http://www.postgrespro.com/>
> > The Russian Postgres Company
> > 
> > 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2019年11月7日 上午12:08，Konstantin Knizhnik  写道：
> 
> 
> 
> On 06.11.2019 16:24, 曾文旌(义从) wrote:
>> Dear Hackers
>> 
>> 
>> I attached the patch of GTT implementationI base on PG12.
>> The GTT design came from my first email.
>> Some limitations in patch will be eliminated in later versions.
>> 
>> Later, I will comment on Konstantin's patch and make some proposals for 
>> cooperation.
>> Looking forward to your feedback.
>> 
>> Thanks.
>> 
>> Zeng Wenjing
>> 
> 
> Thank you for this patch.
> My first comments:
> 
> 1.  I have ported you patch to the latest Postgres version (my patch is 
> attached).
> 2. You patch is supporting only B-Tree index for GTT. All other indexes 
> (hash, gin, gist, brin,...) are not currently supported.
Currently I only support btree index.
I noticed that your patch supports more index types, which is where I'd like to 
work with you.

> 3. I do not understand the reason for the following limitation:
> "We allow to create index on global temp table only this session use it"
> 
> First of all it seems to significantly reduce usage of global temp tables.
> Why do we need GTT at all? Mostly because we need to access temporary data in 
> more than one backend. Otherwise we can just use normal table.
> If temp table is expected to be larger enough, so that we need to create 
> index for it, then it is hard to believe that it will be needed only in one 
> backend.
> 
> May be the assumption is that all indexes has to be created before GTT start 
> to be used.
Yes, Currently, GTT's index is only supported and created in an empty table 
state, and other sessions are not using it.
There has two improvements pointer:
1 Index can create on GTT(A) when the GTT(A)  in the current session is not 
empty, requiring the GTT table to be empty in the other session.
Index_build needs to be done in the current session just like a normal table. 
This improvement is relatively easy.

2 Index can create on GTT(A)  when more than one session are using this GTT(A).
Because when I'm done creating an index of the GTT in this session and setting 
it to be an valid index, it's not true for the GTT in other sessions.
Indexes on gtt in other sessions require "rebuild_index" before using it. 
I don't have a better solution right now, maybe you have some suggestions.


> But right now this check is not working correctly in any case - if you insert 
> some data into the table, then
> you can not create index any more:
> 
> postgres=# create global temp table gtt(x integer primary key, y integer);
> CREATE TABLE
> postgres=# insert into gtt values (generate_series(1,10), 
> generate_series(1,10));
> INSERT 0 10
> postgres=# create index on gtt(y);
> ERROR:  can not create index when have one or more backend attached this 
> global temp table
> 
> I wonder why do you need such restriction?
> 
> 
> -- 
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
> 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2019年10月26日 上午12:22，Konstantin Knizhnik  写道：
> 
> 
> 
> On 25.10.2019 18:01, Robert Haas wrote:
>> On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik
>>  wrote:
>>> Just to clarify.
>>> I have now proposed several different solutions for GTT:
>>> 
>>> Shared vs. private buffers for GTT:
>>> 1. Private buffers. This is least invasive patch, requiring no changes in 
>>> relfilenodes.
>>> 2. Shared buffers. Requires changing relfilenode but supports parallel 
>>> query execution for GTT.
>> I vote for #1. I think parallel query for temp objects may be a
>> desirable feature, but I don't think it should be the job of a patch
>> implementing GTTs to make it happen. In fact, I think it would be an
>> actively bad idea, because I suspect that if we do eventually support
>> temp relations for parallel query, we're going to want a solution that
>> is shared between regular temp tables and global temp tables, not
>> separate solutions for each.
> 
> Sorry, may be I do not not understand you.
> It seems to me that there is only one thing preventing usage of temporary 
> tables in parallel plans: private buffers.
> If global temporary tables are accessed as normal tables though shared 
> buffers then them can be used in parallel queries
> and no extra support is required for it.
> At least I have checked that parallel queries are correctly worked for my 
> implementation of GTT with shared buffers.
> So I do not understand about which "separate solutions" you are talking about.
> 
> I can agree that private buffers may be  good starting point for GTT 
> implementation, because it is less invasive and GTT access speed is exactly 
> the same as of normal temp tables.
> But I do not understand your argument why it is "actively bad idea".
> 
>>> Access to GTT at replica:
>>> 1. Access is prohibited (as for original temp tables). No changes at all.
>>> 2. Tuples of temp tables are marked with forzen XID.  Minimal changes, 
>>> rollbacks are not possible.
>>> 3. Providing special XIDs for GTT at replica. No changes in CLOG are 
>>> required, but special MVCC visibility rules are used for GTT. Current 
>>> limitation: number of transactions accessing GTT at replica is limited by 
>>> 2^32
>>> and bitmap of correspondent size has to be maintained (tuples of GTT are 
>>> not proceeded by vacuum and not frozen, so XID horizon never moved).
>> I again vote for #1. A GTT is defined to allow data to be visible only
>> within one session -- so what does it even mean for the data to be
>> accessible on a replica?
> 
> There are sessions at replica (in case of hot standby), aren't there?
> 
>> 
>>> So except the limitation mentioned above (which I do not consider as 
>>> critical) there is only one problem which was not addressed: maintaining 
>>> statistics for GTT.
>>> If all of the following conditions are true:
>>> 
>>> 1) GTT are used in joins
>>> 2) There are indexes defined for GTT
>>> 3) Size and histogram of GTT in different backends can significantly vary.
>>> 4) ANALYZE was explicitly called for GTT
>>> 
>>> then query execution plan built in one backend will be also used for other 
>>> backends where it can be inefficient.
>>> I also do not consider this problem as "show stopper" for adding GTT to 
>>> Postgres.
>> I think that's *definitely* a show stopper.
> Well, if both you and Pavel think that it is really "show stopper", then this 
> problem really has to be addressed.
> I slightly confused about this opinion, because Pavel has told me himself 
> that 99% of users never create indexes for temp tables
> or run "analyze" for them. And without it, this problem is not a problem at 
> all.
> 
>>> I still do not understand the opinion of community which functionality of 
>>> GTT is considered to be most important.
>>> But the patch with local buffers and no replica support is small enough to 
>>> become good starting point.
>> Well, it seems we now have two patches for this feature. I guess we
>> need to figure out which one is better, and whether it's possible for
>> the two efforts to be merged, rather than having two different teams
>> hacking on separate code bases.
> 
> I am open for cooperations.
> Source code of all my patches is available.
We are also willing to cooperate to complete this feature.
Let me prepare the code(merge code to pg12) and up to community, then see how 
we work together.

> -- 
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
> 
> 
> 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2019年10月11日 下午9:50，Konstantin Knizhnik  写道：
> 
> 
> 
> On 11.10.2019 15:15, 曾文旌(义从) wrote:
>> Dear Hackers,
>> 
>> This propose a way to develop global temporary tables in PostgreSQL.
>> 
>> I noticed that there is an "Allow temporary tables to exist as empty by 
>> default in all sessions" in the postgresql todolist.
>> https://wiki.postgresql.org/wiki/Todo <https://wiki.postgresql.org/wiki/Todo>
>> 
>> In recent years, PG community had many discussions about global temp table 
>> (GTT) support. Previous discussion covered the following topics: 
>> (1)  The main benefit or function: GTT offers features like “persistent 
>> schema, ephemeral data”, which avoids catalog bloat and reduces catalog 
>> vacuum. 
>> (2)  Whether follows ANSI concept of temporary tables
>> (3)  How to deal with statistics, single copy of schema definition, relcache
>> (4)  More can be seen in 
>> https://www.postgresql.org/message-id/73954ab7-44d3-b37b-81a3-69bdcbb446f7%40postgrespro.ru
>>  
>> <https://www.postgresql.org/message-id/73954ab7-44d3-b37b-81a3-69bdcbb446f7%40postgrespro.ru>
>> (5)  A recent implementation and design from Konstantin Knizhnik covered 
>> many functions of GTT: 
>> https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch
>>  
>> <https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch>
>> 
>> However, as pointed by Konstantin himself, the implementation still needs 
>> functions related to CLOG, vacuum, and MVCC visibility.
>> 
> 
> Just to clarify.
> I have now proposed several different solutions for GTT:
> 
> Shared vs. private buffers for GTT:
> 1. Private buffers. This is least invasive patch, requiring no changes in 
> relfilenodes.
> 2. Shared buffers. Requires changing relfilenode but supports parallel query 
> execution for GTT.
> 
> Access to GTT at replica:
> 1. Access is prohibited (as for original temp tables). No changes at all.
> 2. Tuples of temp tables are marked with forzen XID.  Minimal changes, 
> rollbacks are not possible.
> 3. Providing special XIDs for GTT at replica. No changes in CLOG are 
> required, but special MVCC visibility rules are used for GTT. Current 
> limitation: number of transactions accessing GTT at replica is limited by 2^32
> and bitmap of correspondent size has to be maintained (tuples of GTT are not 
> proceeded by vacuum and not frozen, so XID horizon never moved).
> 
> So except the limitation mentioned above (which I do not consider as 
> critical) there is only one problem which was not addressed: maintaining 
> statistics for GTT. 
> If all of the following conditions are true:
> 
> 1) GTT are used in joins
> 2) There are indexes defined for GTT
> 3) Size and histogram of GTT in different backends can significantly vary. 
> 4) ANALYZE was explicitly called for GTT
> 
> then query execution plan built in one backend will be also used for other 
> backends where it can be inefficient.
> I also do not consider this problem as "show stopper" for adding GTT to 
> Postgres.
When session A writes 1000 rows of data to gtt X, session B also uses X at 
the same time and it has 100 rows of different data. If B uses analyze to count 
the statistics of 10 rows of data and updates it to catalog.
Obviously, session A will get inaccurate query plan based on misaligned 
statistics when calculating the query plan for X related queries. Session A may 
think that table X is too small to be worth using index scan, but it is not. 
Each session needs to get the statistics of the self data to make the query 
plan.


> I still do not understand the opinion of community which functionality of GTT 
> is considered to be most important.
> But the patch with local buffers and no replica support is small enough to 
> become good starting point.
Yes ,the first step, we focus on complete basic functions of gtt (dml ddl index 
on gtt (MVCC visibility rules) storage).
Abnormal statistics can cause problems with index selection on gtt, so index on 
gtt and accurate statistical information is necessary.


> 
> -- 
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com 
> <http://www.postgrespro.com/>
> The Russian Postgres Company 

Re: [Proposal] Global temporary tables

[曾文旌(义从)]

> 2019年10月12日 下午1:16，Pavel Stehule  写道：
> 
> 
> 
> pá 11. 10. 2019 v 15:50 odesílatel Konstantin Knizhnik 
> mailto:k.knizh...@postgrespro.ru>> napsal:
> 
> 
> On 11.10.2019 15:15, 曾文旌(义从) wrote:
>> Dear Hackers,
>> 
>> This propose a way to develop global temporary tables in PostgreSQL.
>> 
>> I noticed that there is an "Allow temporary tables to exist as empty by 
>> default in all sessions" in the postgresql todolist.
>> https://wiki.postgresql.org/wiki/Todo <https://wiki.postgresql.org/wiki/Todo>
>> 
>> In recent years, PG community had many discussions about global temp table 
>> (GTT) support. Previous discussion covered the following topics: 
>> (1)  The main benefit or function: GTT offers features like “persistent 
>> schema, ephemeral data”, which avoids catalog bloat and reduces catalog 
>> vacuum. 
>> (2)  Whether follows ANSI concept of temporary tables
>> (3)  How to deal with statistics, single copy of schema definition, relcache
>> (4)  More can be seen in 
>> https://www.postgresql.org/message-id/73954ab7-44d3-b37b-81a3-69bdcbb446f7%40postgrespro.ru
>>  
>> <https://www.postgresql.org/message-id/73954ab7-44d3-b37b-81a3-69bdcbb446f7%40postgrespro.ru>
>> (5)  A recent implementation and design from Konstantin Knizhnik covered 
>> many functions of GTT: 
>> https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch
>>  
>> <https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch>
>> 
>> However, as pointed by Konstantin himself, the implementation still needs 
>> functions related to CLOG, vacuum, and MVCC visibility.
>> 
> 
> Just to clarify.
> I have now proposed several different solutions for GTT:
> 
> Shared vs. private buffers for GTT:
> 1. Private buffers. This is least invasive patch, requiring no changes in 
> relfilenodes.
> 2. Shared buffers. Requires changing relfilenode but supports parallel query 
> execution for GTT.
> 
> This is important argument for using share buffers. Maybe the best is mix of 
> both - store files in temporal tablespace, but using share buffers. More, it 
> can be accessible for autovacuum.
> 
> Access to GTT at replica:
> 1. Access is prohibited (as for original temp tables). No changes at all.
> 2. Tuples of temp tables are marked with forzen XID.  Minimal changes, 
> rollbacks are not possible.
> 3. Providing special XIDs for GTT at replica. No changes in CLOG are 
> required, but special MVCC visibility rules are used for GTT. Current 
> limitation: number of transactions accessing GTT at replica is limited by 2^32
> and bitmap of correspondent size has to be maintained (tuples of GTT are not 
> proceeded by vacuum and not frozen, so XID horizon never moved).
> 
> So except the limitation mentioned above (which I do not consider as 
> critical) there is only one problem which was not addressed: maintaining 
> statistics for GTT. 
> If all of the following conditions are true:
> 
> 1) GTT are used in joins
> 2) There are indexes defined for GTT
> 3) Size and histogram of GTT in different backends can significantly vary. 
> 4) ANALYZE was explicitly called for GTT
> 
> then query execution plan built in one backend will be also used for other 
> backends where it can be inefficient.
> I also do not consider this problem as "show stopper" for adding GTT to 
> Postgres.
> 
> The last issue is show stopper in my mind. It really depends on usage. There 
> are situation where shared statistics are ok (and maybe good solution), and 
> other situation, where shared statistics are just unusable.
This proposal calculates and stores independent statistics(relpages reltuples 
and histogram of GTT) for the gtt data within each session, ensuring optimizer 
can get accurate statistics.


> Regards
> 
> Pavel
> 
> 
> 
> I still do not understand the opinion of community which functionality of GTT 
> is considered to be most important.
> But the patch with local buffers and no replica support is small enough to 
> become good starting point.
> 
> 
> -- 
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com 
> <http://www.postgrespro.com/>
> The Russian Postgres Company 

[Proposal] Global temporary tables

[曾文旌(义从)]

Dear Hackers,

This propose a way to develop global temporary tables in PostgreSQL.

I noticed that there is an "Allow temporary tables to exist as empty by default 
in all sessions" in the postgresql todolist.
https://wiki.postgresql.org/wiki/Todo 

In recent years, PG community had many discussions about global temp table 
(GTT) support. Previous discussion covered the following topics: 
(1) The main benefit or function: GTT offers features like “persistent 
schema, ephemeral data”, which avoids catalog bloat and reduces catalog vacuum. 
(2) Whether follows ANSI concept of temporary tables
(3) How to deal with statistics, single copy of schema definition, relcache
(4) More can be seen in 
https://www.postgresql.org/message-id/73954ab7-44d3-b37b-81a3-69bdcbb446f7%40postgrespro.ru
(5) A recent implementation and design from Konstantin Knizhnik covered 
many functions of GTT: 
https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch
 


However, as pointed by Konstantin himself, the implementation still needs 
functions related to CLOG, vacuum, and MVCC visibility.

We developed GTT based on PG 11 and included most needed features, such as how 
to deal with concurrent DDL and DML operations, how to handle vacuum and too 
old relfrozenxids, and how to store and access GTT statistics. 

This design followed many suggestions from previous discussion in community. 
Here are some examples:
“have a separate 'relpersistence' setting for global temp tables…by 
having the backend id in all filename….   From Andres Freund
Use session memory context to store information related to GTT.   From 
Pavel Stehule
“extend the relfilenode mapper to support a backend-local 
non-persistent relfilenode map that's used to track temp table and index 
relfilenodes…” from Craig Ringer

Our implementation creates one record in pg_class for GTT’s schema definition. 
When rows are first inserted into the GTT in a session, a session specific file 
is created to store the GTT’s data. Those files are removed when the session 
ends. We maintain the GTT’s statistics in session local memory. DDL operations, 
such as DROP table or CREATE INDEX, can be executed on a GTT only by one 
session, while no other sessions insert any data into the GTT before or it is 
already truncated. This also avoids the concurrency of DML and DDL operations 
on GTT. We maintain a session level oldest relfrozenxids for GTT. This way, 
autovacuum or vacuum can truncate CLOG and increase global relfrozenxids based 
on all tables’ relfrozenxids, including GTT’s. 
The follows summarize the main design and implementation: 
Syntax: ON COMMIT PRESERVE ROWS and ON COMMIT DELETE ROWS
Data storage and buffering follows the same way as local temp table 
with a relfilenode including session id.
A hash table(A) in shared memory is used to track sessions and their 
usage of GTTs and to serialize DDL and DML operations. 
Another hash table(B) in session memory is introduced to record storage 
files for GTTs and their indexes. When a session ends, those files are removed. 
The same hash table(B) in session memory is used to record the 
relfrozenxids of each GTT. The oldest one is stored in myproc so that 
autovacuum and vacuum may use it to determine global oldest relfrozenxids and 
truncate clog. 
The same hash table(B) in session memory stores GTT’s session level 
statistics, It is generated during the operations of vacuum and analyze, and 
used by SQL optimizer to create execution plan. 
Some utility functions are added for DBA to manage GTTs. 
TRUNCATE command on a GTT behaves differently from that on a normal 
table. The command deletes the data immediately but keeps relfilenode using 
lower level table lock, RowExclusiveLock, instead of  AccessExclusiveLock. 
Main limits of this version or future improvement: need suggestions 
from community: 
1 VACUUM FULL and CLUSTER are not supported; any operations 
which may change relfilenode are disabled to GTT.
2 Sequence column is not supported in GTT for now.
3 Users defined statistics is not supported.


Details:

Requirement
The features list about global temp table:
1. global temp table (ON COMMIT clause is omitted, SQL specifies that 
the default behavior is ON COMMIT DELETE ROWS)
2. support with on commit DELETE ROWS
3. support with on commit PRESERVE ROWS
4. not support ON COMMIT DROP

Feature description
Global temp tables are defined just once and automatically exist (starting with 
empty contents) in every session that needs them.
Global temp table, each session use local buffer, read or write independent 
data files.
Use on commit DELETE ROWS for a transaction-specific global temp