Re: [Proposal] Global temporary tables

> 2020年7月10日 下午5:03，wenjing zeng <wjzeng2012(at)gmail(dot)com> 写道：
>
> HI all
>
> I started using my personal email to respond to community issue.
>
>
>
>> 2020年7月7日 下午6:05，Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com <mailto:pavel(dot)stehule(at)gmail(dot)com>> 写道：
>>
>> 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=# alter table foo add column x int;
>> ALTER TABLE
>> postgres=# analyze foo;
>> WARNING: reloid 16400 not support update attstat after add colunm
>> WARNING: reloid 16400 not support update attstat after add colunm
>> ANALYZE
> This is a limitation that we can completely eliminate.
>
>>
>> Please, can you summarize what is done, what limits are there, what can be implemented hard, what can be implemented easily?
> Sure.
>
> The current version of the GTT implementation supports all regular table operations.
> 1 what is done
> 1.1 insert/update/delete on GTT.
> 1.2 The GTT supports all types of indexes, and the query statement supports the use of GTT indexes to speed up the reading of data in the GTT.
> 1.3 GTT statistics keep a copy of THE GTT local statistics, which are provided to the optimizer to choose the best query plan.
> 1.4 analyze vacuum GTT.
> 1.5 truncate cluster GTT.
> 1.6 all DDL on GTT.
> 1.7 GTT table can use GTT sequence or Regular sequence.
> 1.8 Support for creating views on GTT.
> 1.9 Support for creating views on foreign key.
> 1.10 support global temp partition.
>
> I feel like I cover all the necessary GTT requirements.
>
> For cluster GTT,I think it's complicated.
> I'm not sure the current implementation is quite reasonable. Maybe you can help review it.
>
>
>>
>>
>>
>> I found one open question - how can be implemented table locks - because data is physically separated, then we don't need table locks as protection against race conditions.
> Yes, but GTT’s DML DDL still requires table locking.
> 1 The DML requires table locks (RowExclusiveLock) to ensure that
> definitions do not change during run time (the DDL may modify or delete them).
> This part of the implementation does not actually change the code,
> because the DML on GTT does not block each other between sessions.
As a side note, since the same row of GTT data can not modified by different sessions,
So, I don't see the need to care the GTT's PG_class.relminmxID.
What do you think?

Wenjing

>
> 2 For truncate/analyze/vacuum reinidex cluster GTT is now like DML,
> they only modify local data and do not modify the GTT definition.
> So I lowered the table lock level held by the GTT, only need RowExclusiveLock.
>
> 3 For DDLs that need to be modified the GTT table definition(Drop GTT Alter GTT),
> an exclusive level of table locking is required(AccessExclusiveLock),
> as is the case for regular table.
> This part of the implementation also does not actually change the code.
>
> Summary: What I have done is to adjust the GTT lock levels in different types of statements based on the above thinking.
> For example, truncate GTT, I'm reducing the GTT holding table lock level to RowExclusiveLock,
> So We can truncate data in the same GTT between different sessions at the same time.
>
> What do you think about table locks on GTT?
>
>
> Wenjing
>
>
>>
>> Now, table locks are implemented on a global level. So exclusive lock on GTT in one session block insertion on the second session. Is it expected behaviour? It is safe, but maybe it is too strict.
>>
>> We should define what table lock is meaning on GTT.
>>
>> Regards
>>
>> Pavel
>>
>> Pavel
>>
>>
>>> With Regards,
>>> Prabhat Kumar Sahu
>>> EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>
>>
>>
>