回复：回复：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

回复：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 曾文旌(义从

回复：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 Hack

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

: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

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

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

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

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

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

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 lon

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

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

Re: [Proposal] Global temporary tables

gt; > > 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 曾文

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

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

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

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

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 &

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>> 写道： >> >

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

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)

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 al

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_c

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

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

[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" 

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

Re: [Proposal] Global temporary tables

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

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

Re: [Proposal] Global temporary tables

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

Re: [Proposal] Global temporary tables

he 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. > >

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

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. > > -- > Ro

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

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

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

Re: [Proposal] Global temporary tables

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

Re: [Proposal] Global temporary tables

stgres=# 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: constra

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

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

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

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

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? >>> S

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,

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 2

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>> 写道： >> >> >&g

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

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:

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

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 >&g

Re: [Proposal] Global temporary tables

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

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

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&

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

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. Sup

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

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

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

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>> 写道： > > >

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 limita

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

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 table

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 w

[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