Re: [Proposal] Global temporary tables

On 3/3/22 13:20, Andres Freund wrote: > On 2022-03-03 16:07:37 -0500, Robert Haas wrote: >> I agree that the feature is desirable, but I think getting there is >> going to require a huge amount of effort that may amount to a total >> rewrite of the patch. > > Agreed. I think this needs very fundam

Re: [Proposal] Global temporary tables

Hi, On 2022-03-03 16:07:37 -0500, Robert Haas wrote: > On Thu, Mar 3, 2022 at 3:29 PM Greg Stark wrote: > > I'm still hopeful we get to advance this early in 16 because I think > > everyone agrees the feature would be great. > > I'm not saying this patch can't make progress, but I think the chan

Re: [Proposal] Global temporary tables

On Thu, Mar 3, 2022 at 3:29 PM Greg Stark wrote: > I'm still hopeful we get to advance this early in 16 because I think > everyone agrees the feature would be great. I'm not saying this patch can't make progress, but I think the chances of this being ready to commit any time in the v16 release cy

Re: [Proposal] Global temporary tables

It doesn't look like this is going to get committed this release cycle. I understand more feedback could be valuable, especially on the overall design, but as this is the last commitfest of the release we should focus on other patches for now and spend that time in the next release cycle. I'm goin

Re: [Proposal] Global temporary tables

On Wed, Mar 2, 2022 at 4:18 PM Andres Freund wrote: > I think there's just no way that it can be merged with anything close to the > current design - it's unmaintainable. The need for the feature doesn't change > that. I don't know whether the design is right or wrong, but I agree that a bad desi

Re: [Proposal] Global temporary tables

Hi > I think you'd basically have to come up with a generic design for > partitioning > catalog tables into local / non-local storage, without needing explicit > code > for each catalog. That could also be used to store the default catalog > contents separately from user defined ones (e.g. pg_pro

Re: [Proposal] Global temporary tables

Hi, On 2022-02-27 06:09:54 +0100, Pavel Stehule wrote: > ne 27. 2. 2022 v 5:13 odesílatel Andres Freund napsal: > > On 2022-02-27 04:17:52 +0100, Pavel Stehule wrote: > > > Without this, the GTT will be terribly slow like current temporary tables > > > with a lot of problems with bloating of pg_c

Re: [Proposal] Global temporary tables

st 2. 3. 2022 v 19:02 odesílatel Adam Brusselback napsal: > >In my observation, very few users require an accurate query plan for > temporary tables to > perform manual analyze. > > Absolutely not true in my observations or personal experience. It's one of > the main reasons I have needed to use

Re: [Proposal] Global temporary tables

>In my observation, very few users require an accurate query plan for temporary tables to perform manual analyze. Absolutely not true in my observations or personal experience. It's one of the main reasons I have needed to use (local) temporary tables rather than just materializing a CTE when deco

Re: [Proposal] Global temporary tables

> 2022年2月27日 12:13，Andres Freund 写道： > > Hi, > > On 2022-02-27 04:17:52 +0100, Pavel Stehule wrote: >>> You redirect stats from pg_class and pg_statistics to a local hash table. >>> This is pretty hairy :( > > As is I think the patch is architecturally completely unacceptable. Having > code

Re: [Proposal] Global temporary tables

> 2022年2月27日 08:21，Justin Pryzby 写道： > > I read through this. > Find attached some language fixes. You should be able to apply each "fix" > patch on top of your own local branch with git am, and then squish them > together. Let me know if you have trouble with that. > > I think get_seqence_s

Re: [Proposal] Global temporary tables

> 2022年2月25日 15:45，Andres Freund 写道： > > Hi, > > > This is a huge thread. Realistically reviewers and committers can't reread > it. I think there needs to be more of a description of how this works included > in the patchset and *why* it works that way. The readme does a bit of that, > but no

Re: [Proposal] Global temporary tables

ne 27. 2. 2022 v 5:13 odesílatel Andres Freund napsal: > Hi, > > On 2022-02-27 04:17:52 +0100, Pavel Stehule wrote: > > > You redirect stats from pg_class and pg_statistics to a local hash > table. > > > This is pretty hairy :( > > As is I think the patch is architecturally completely unacceptabl

Re: [Proposal] Global temporary tables

Hi, On 2022-02-27 04:17:52 +0100, Pavel Stehule wrote: > > You redirect stats from pg_class and pg_statistics to a local hash table. > > This is pretty hairy :( As is I think the patch is architecturally completely unacceptable. Having code everywhere to redirect to manually written in-memory cat

Re: [Proposal] Global temporary tables

Hi You redirect stats from pg_class and pg_statistics to a local hash table. > This is pretty hairy :( > I guess you'd also need to handle pg_statistic_ext and ext_data. > pg_stats doesn't work, since the data isn't in pg_statistic - it'd need to > look > at pg_get_gtt_statistics. > Without this,

Re: [Proposal] Global temporary tables

end_state(Relation index) /* * During the SQL initialization of the executor (InitPlan) - * Initialize storage of GTT GTT'indexes and build empty index. + * Initialize storage of GTT's indexes and build empty index. */ void init_gtt_storage(CmdType operation, Relation relation) d

Re: [Proposal] Global temporary tables

Hi, This is a huge thread. Realistically reviewers and committers can't reread it. I think there needs to be more of a description of how this works included in the patchset and *why* it works that way. The readme does a bit of that, but not particularly well. On 2022-02-25 14:26:47 +0800, Wenj

Re: [Proposal] Global temporary tables

Hi! I could not detect crashes with your last patch, so I think the patch is ready for a review. Please, also consider fixing error messages, as existing ones don't follow message writing guidelines. https://www.postgresql.org/docs/14/error-style-guide.html Regards, Andrew On Thu, Dec 23, 2021 a

Re: [Proposal] Global temporary tables

Hi! Thanks for new patches. Yet another crash reproduced on master with v63 patches: CREATE TABLESPACE ts LOCATION '/tmp/ts'; CREATE GLOBAL TEMP TABLE tbl (num1 bigint); INSERT INTO tbl (num1) values (1); CREATE INDEX tbl_idx ON tbl (num1); REINDEX (TABLESPACE ts) TABLE tbl; Got error: CREATE TAB

Re: [Proposal] Global temporary tables

Thanks for the patches. The feature has become much more stable. However, there is another simple case that generates an error: Master with v61 patches CREATE GLOBAL TEMPORARY TABLE t AS SELECT 1 AS a; ERROR: could not open file "base/13560/t3_16384": No such file or directory Andrew On Thu, Nov

Re: [Proposal] Global temporary tables

Thanks, the "group by" is fixed Yet another crash (on v58 patches), reproduced with: psql -t -c "create global temp table t(b text) with(on_commit_delete_rows=true); create index idx_b on t (b); insert into t values('test'); alter table t alter b type varchar;" server closed the connection unexpe

Re: [Proposal] Global temporary tables

Thanks, the vacuum is fixed But I found another crash (on v57 patches), reproduced with: psql -t -c "create global temp table t (a integer); insert into t values (1); select count(*) from t group by t;" server closed the connection unexpectedly This probably means the server terminated ab

Re: [Proposal] Global temporary tables

Another thanks for the fix. It works for me. But I found another crash! On master with the v56 patches applied: initdb -D data pg_ctl -w -t 5 -D data -l server.log start echo "create global temp table t(i int4); insert into t values (1); vacuum t;" > tmp.sql psql < tmp.sql CREATE TABLE INSERT 0

Re: [Proposal] Global temporary tables

On master with the v55 patches applied the following script leads to crash: initdb -D data pg_ctl -w -t 5 -D data -l server.log start psql -t -c "begin; create global temp table gtt_with_index(a int primary key, b text); commit; select pg_sleep(5);" >psql1.log & psql -t -c "select pg_sleep(1); cre

Re: [Proposal] Global temporary tables

> 2021年10月13日 13:08，Andrew Bille 写道： > > Thanks for the fix. It works for me. > > Now I'm exploring another crash related to GTT, but I need a few days to > present a simple repro. Be deeply grateful. Perhaps you can give the stack of problems so that you can start analyzing them as soon as

Re: [Proposal] Global temporary tables

Thanks for the fix. It works for me. Now I'm exploring another crash related to GTT, but I need a few days to present a simple repro. On Sat, Oct 9, 2021 at 2:41 PM wenjing wrote: > > Thank you for pointing it out. > This is a bug that occurs during transaction rollback and process exit, I > fi

Re: [Proposal] Global temporary tables

On master with the v54 patches applied the following script leads to crash: export ASAN_OPTIONS=detect_leaks=0:abort_on_error=1:disable_coredump=0:strict_string_checks=1:check_initialization_order=1:strict_init_order=1 initdb -D data pg_ctl -w -t 5 -D data -l server.log start psql -c "create global

Re: [Proposal] Global temporary tables

hi ne 26. 9. 2021 v 6:05 odesílatel wenjing napsal: > > > 2021年9月23日 21:55，Tony Zhu 写道： > > Hi Wenjing > > we have reviewed the code, and done the regression tests, all tests is > pass, we believe the feature code quality is ready for production ; and I > will change the status to "Ready for

Re: [Proposal] Global temporary tables

Hi Wenjing we have reviewed the code, and done the regression tests, all tests is pass, we believe the feature code quality is ready for production ; and I will change the status to "Ready for commit"

Re: [Proposal] Global temporary tables

Andrew Dunstan 于2021年3月28日周日 下午9:07写道： > > 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年7月14日 10:56，Ming Li 写道： Hi Wenjing, Some suggestions may help: 1) It seems that no test case covers the below scenario: 2 sessions attach the same gtt, and insert/update/select concurrently. It is better to use the test framework in src/test/isolation like the code changes in https://commi

Re: [Proposal] Global temporary tables

Hi looks so this patch is broken again. Please, can you do rebase? Regards Pavel čt 16. 9. 2021 v 8:28 odesílatel wenjing napsal: > > > > > > > > >

Re: [Proposal] Global temporary tables

Hi WenJing Thanks for the feedback, I have tested the code, it seems okay, and regression tests got pass and I have reviewed the code, and I don't find any issue anymore Hello all Review and comments for the patches V51 is welcome. if there is no feedback, I'm going to changed the status

Re: [Proposal] Global temporary tables

> 2021年7月28日 23:09，Tony Zhu 写道： > > Hi Wenjing > > would you please rebase the code? Thank you for your attention. According to the test, the latest pgmaster code can merge the latest patch and pass the test. https://www.travis-ci.com/github/wjzeng/postgres/builds

Re: [Proposal] Global temporary tables

Hi Wenjing would you please rebase the code? Thank you very much Tony The new status of this patch is: Waiting on Author

Re: [Proposal] Global temporary tables

Hi Wenjing, Some suggestions may help: 1) It seems that no test case covers the below scenario: 2 sessions attach the same gtt, and insert/update/select concurrently. It is better to use the test framework in src/test/isolation like the code changes in https://commitfest.postgresql.org/24/2233/.

Re: [Proposal] Global temporary tables

On Thu, Apr 22, 2021 at 1:11 PM wenjing wrote: > I have briefly looked into the design comments added by the patch. I have a few questions. +Feature description + + +Previously, temporary tables are defined once and automatically +created (starting with empty con

Re: [Proposal] Global temporary tables

wenjing 于2021年4月15日周四 下午3:26写道： > HI Pavel > > I added user documentation. > Please give me feedback. > > > Wenjing > > Hi, Wenjing, I have checked your documentation section and fixed a spelling mistake, adjusted some sentences for you. All the modified content is in the new patch, and please c

Re: [Proposal] Global temporary tables

po 29. 3. 2021 v 13:45 odesílatel wenjing napsal: > HI all > > I fixed the document description error and the regression test bug > mentioned by Pavel. > This patch(V45) is base on 30aaab26e52144097a1a5bbb0bb66ea1ebc0cb81 > Please give me feedback. > Yes, it is working. So please, can you write

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 chang

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

Re: [Proposal] Global temporary tables

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

Re: [Proposal] Global temporary tables

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

Re: [Proposal] Global temporary tables

Hi I wrote simple benchmarks. I checked the possible slowdown of connections to postgres when GTT is used. /usr/local/pgsql/master/bin/pgbench -c 10 -C -f script4.sql -t 1000 script has one line just with INSERT or SELECT LIMIT 1; PATCH insert to global temp table (with connect) -- 349 tps (10

Re: [Proposal] Global temporary tables

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

Re: [Proposal] Global temporary tables

Hi this patch is broken now. Please, can you check it? Regards Pavel st 25. 11. 2020 v 14:08 odesílatel 曾文旌 napsal: > > > 2020年11月25日 14:19，Pavel Stehule 写道： > > > > po 23. 11. 2020 v 10:27 odesílatel 曾文旌 > napsal: > >> >> >> 2020年11月21日 02:28，Pavel Stehule 写道： >> >> Hi >> >> pá 11. 9. 20

Re: [Proposal] Global temporary tables

Sorry, I don't have checkout the hash codebase for the patch. Compiled OK now. Best regards

Re: [Proposal] Global temporary tables

Hi, Trying to apply the last patch (global_temporary_table_v42-pg14.patch) to the code cloned from github, ending with this error: usr/bin/ld: catalog/storage_gtt.o: in function `up_gtt_relstats': storage_gtt.c:(.text+0x1276): undefined reference to `ReadNewTransactionId' collect2: error: ld ret

Re: [Proposal] Global temporary tables

Hi út 22. 12. 2020 v 4:20 odesílatel wenjing napsal: > HI all > > I rebased patch, fix OID conflicts, fix some comments. > Code updates to v41. > Please, can you do rebase? Regards Pavel > > Wenjing > >

Re: [Proposal] Global temporary tables

st 9. 12. 2020 v 7:34 odesílatel 曾文旌 napsal: > > > 2020年12月8日 13:28，Pavel Stehule 写道： > > Hi > > čt 26. 11. 2020 v 12:46 odesílatel 曾文旌 > napsal: > >> This is the latest patch for feature GTT(v38). >> Everybody, if you have any questions, please let me know. >> > > please, co you send a rebased

Re: [Proposal] Global temporary tables

Hi čt 26. 11. 2020 v 12:46 odesílatel 曾文旌 napsal: > This is the latest patch for feature GTT(v38). > Everybody, if you have any questions, please let me know. > please, co you send a rebased patch. It is broken again Regards Pavel > > Wenjing > > >

Re: [Proposal] Global temporary tables

On Thu, Nov 26, 2020 at 11:16 AM Julien Rouhaud wrote: > > On Thu, Nov 26, 2020 at 4:05 PM 曾文旌 wrote: > > > > I found that the new Patch mail failed to register to Commitfest > > https://commitfest.postgresql.org/28/2349/# > > I don't know what's wrong and how to check it? > > Could you help me f

Re: [Proposal] Global temporary tables

On Thu, Nov 26, 2020 at 4:05 PM 曾文旌 wrote: > > I found that the new Patch mail failed to register to Commitfest > 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? Apparently the attachment in https://www.postgresql.org

Re: [Proposal] Global temporary tables

I found that the new Patch mail failed to register to Commitfest 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

Re: [Proposal] Global temporary tables

po 23. 11. 2020 v 10:27 odesílatel 曾文旌 napsal: > > > 2020年11月21日 02:28，Pavel Stehule 写道： > > Hi > > pá 11. 9. 2020 v 17:00 odesílatel 曾文旌 > 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 ag

Re: [Proposal] Global temporary tables

Hi pá 11. 9. 2020 v 17:00 odesílatel 曾文旌 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. >

Re: [Proposal] Global temporary tables

>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

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 t

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 th

Re: [Proposal] Global temporary tables

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

Re: [Proposal] Global temporary tables

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

Re: [Proposal] Global temporary tables

> I am thinking about explicit LOCK statements. Some applications use > explicit locking from some reasons - typically as protection against race > conditions. > > But on GTT race conditions are not possible. So my question is - does the > exclusive lock on GTT protection other sessions do insert

Re: [Proposal] Global temporary tables

> 2020年7月14日 下午10:28，Pavel Stehule 写道： > > > > pá 10. 7. 2020 v 11:04 odesílatel wenjing zeng > napsal: > HI all > > I started using my personal email to respond to community issue. > > > >> 2020年7月7日 下午6:05，Pavel Stehule > > 写

Re: [Proposal] Global temporary tables

pá 10. 7. 2020 v 11:04 odesílatel wenjing zeng napsal: > HI all > > I started using my personal email to respond to community issue. > > > > 2020年7月7日 下午6:05，Pavel Stehule 写道： > > Hi > > >> GTT Merge the latest PGMaster and resolves conflicts. >> >> >> > I tested it and it looks fine. I think it

Re: [Proposal] Global temporary tables

po 13. 7. 2020 v 13:59 odesílatel wenjing zeng napsal: > > > 2020年7月10日 下午5:03，wenjing zeng 写道： > > HI all > > I started using my personal email to respond to community issue. > > > > 2020年7月7日 下午6:05，Pavel Stehule 写道： > > Hi > > >> GTT Merge the latest PGMaster and resolves conflicts. >> >> >>

Re: [Proposal] Global temporary tables

> 2020年7月10日 下午5:03，wenjing zeng 写道： > > HI all > > I started using my personal email to respond to community issue. > > > >> 2020年7月7日 下午6:05，Pavel Stehule > > 写道： >> >> Hi >> >> GTT Merge the latest PGMaster and resolves conflicts. >> >> >> >> I teste

Re: [Proposal] Global temporary tables

HI all I started using my personal email to respond to community issue. > 2020年7月7日 下午6:05，Pavel Stehule 写道： > > Hi > > GTT Merge the latest PGMaster and resolves conflicts. > > > > I tested it and it looks fine. I think it is very usable in current form, but > still there are some issu

Re: [Proposal] Global temporary tables

Hi > GTT Merge the latest PGMaster and resolves conflicts. > > > I tested it and it looks fine. I think it is very usable in current form, but still there are some issues: postgres=# create global temp table foo(a int); CREATE TABLE postgres=# insert into foo values(10); INSERT 0 1 postgres=# al

Re: [Proposal] Global temporary tables

Hi čt 11. 6. 2020 v 4:13 odesílatel 曾文旌 napsal: > > 2020年6月9日 下午8:15，Prabhat Sahu 写道： > > > > On Wed, Apr 29, 2020 at 8:52 AM 曾文旌 wrote: > >> 2020年4月27日 下午9:48，Prabhat Sahu 写道： >> >> Thanks Wenjing, for the fix patch for previous issues. >> I have verified the issues, now those fix look good

Re: [Proposal] Global temporary tables

> 2020年6月9日 下午8:15，Prabhat Sahu 写道： > > > > On Wed, Apr 29, 2020 at 8:52 AM 曾文旌 > wrote: >> 2020年4月27日 下午9:48，Prabhat Sahu > > 写道： >> >> Thanks Wenjing, for the fix patch for previous issues. >> I have verified the iss

Re: [Proposal] Global temporary tables

On Wed, Apr 29, 2020 at 8:52 AM 曾文旌 wrote: > 2020年4月27日 下午9:48，Prabhat Sahu 写道： > > 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:

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=# crea

Re: [Proposal] Global temporary tables

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

Re: [Proposal] Global temporary tables

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;

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 Wenji

Re: [Proposal] Global temporary tables

Hi Wenjing, Please check the below scenario shows different error message with "DROP TABLE gtt;" for gtt with and without index. *-- Session1:*postgres=# create global temporary table gtt1 (c1 int); CREATE TABLE postgres=# create global temporary table gtt2 (c1 int); CREATE TABLE postgres=# creat

Re: [Proposal] Global temporary tables

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

Re: [Proposal] Global temporary tables

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 glob

Re: [Proposal] Global temporary tables

Hi Wenjing, With the new patch(v30) as you mentioned the new syntax support for "TRUNCATE TABLE gtt DROP", but we also observe the syntax "DROP TABLE gtt DROP" is working as below: postgres=# create global temporary table gtt(c1 int) on commit preserve rows; CREATE TABLE postgres=# DROP TABLE gtt

Re: [Proposal] Global temporary tables

Hi Wenjing, Please check, the server getting crash with the below scenario(CLUSTER gtt using INDEX). *-- Session1:* postgres=# create global temporary table gtt (c1 integer) on commit preserve rows; CREATE TABLE postgres=# create index idx1 on gtt (c1); CREATE INDEX *-- Session2:* postgres=# cre

Re: [Proposal] Global temporary tables

;> >>>> postgres=# drop table gtt2; >>>> DROP TABLE >>>> >>>> >>>> >>>> >>>> *Case2: GTT with(on commit preserve rows) having data in both session1 >>>> and session2Session1:*create global temporary table

Re: [Proposal] Global temporary tables

t;> temp table >>> >>> -- To drop the table gtt2 from any session1/session2, we need to truncate >>> the table data first from all the session(session1, session2) which is >>> having data. >>> Session1: >>> truncate table g

Re: [Proposal] Global temporary tables

y table >>> already in use >>> >>> postgres=# drop table gtt2 ; >>> ERROR: can not drop relation gtt2 when other backend attached this global >>> temp table >>> >>> -- To drop the table gtt2 from any session1/session2, we need to

Re: [Proposal] Global temporary tables

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 >>> >>> postgres=# drop table gtt2 ; >>> ERROR: can not dr

Re: [Proposal] Global temporary tables

d. >> >> -- *Issue 4:* But we are not able to drop the GTT, even after TRUNCATE >> the table in all the sessions. >> -- truncate from all sessions where GTT have data. >> postgres=# truncate gtt2 ; >> TRUNCATE TABLE >> >> -- *try to DROP GTT still,

Re: [Proposal] Global temporary tables

On 4/20/20 2:59 PM, 曾文旌 wrote: Please check my new patch. Thanks Wenjing. Please refer this below scenario , getting error -  ERROR:  could not read block 0 in file "base/16466/t4_16472": read only 0 of 8192 bytes Steps to reproduce Connect to psql terminal,create a table ( create global t

Re: [Proposal] Global temporary tables

> 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. > > Hi Wenjing, Thanks for the explanation. Thanks for the new patch. I have verified the crash, Now its resolved. -- With Regards, Prabhat Kumar Sahu Enterpris

Re: [Proposal] Global temporary tables

> 2020年4月17日 下午7:26，Prabhat Sahu 写道： > > On Fri, Apr 17, 2020 at 2:44 PM 曾文旌 > 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, > Th

Re: [Proposal] Global temporary tables

Hi Wenjing, Please check below scenario, we are getting a server crash with "ALTER TABLE" add column with default value as sequence: -- Create gtt, exit and re-connect the psql prompt, create sequence, alter table add a column with sequence. postgres=# create global temporary table gtt1 (c1 int);

Re: [Proposal] Global temporary tables

On Fri, Apr 17, 2020 at 2:44 PM 曾文旌 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 fi

Re: [Proposal] Global temporary tables

On 4/9/20 6:26 PM, 曾文旌 wrote: On 4/7/20 2:27 PM, 曾文旌 wrote: Vacuum full GTT, cluster GTT is already supported in global_temporary_table_v24-pg13.patch. Here , it is skipping GTT postgres=# \c foo You are now connected to database "foo" as user "tushar". foo=# create global temporary table g1

Re: [Proposal] Global temporary tables

On 4/13/20 1:57 PM, 曾文旌 wrote: [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,

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

Re: [Proposal] Global temporary tables

On 2020-04-09 15:28, 曾文旌 wrote: [global_temporary_table_v25-pg13.patch] Part of the problem is that some variables are only used by assert statements, and I fixed those alarms. Please provide your configue parameter, and I will verify it again. Hi, Just now I compiled the newer version of

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 databa

Re: [Proposal] Global temporary tables

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 ta

Re: [Proposal] Global temporary tables

On Wed, Apr 8, 2020 at 1:48 PM 曾文旌 wrote: > > > 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 f

Re: [Proposal] Global temporary tables

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 se

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 FUL

  1   2   3   4   >