Re: [Proposal] Global temporary tables

public inbox for [email protected]  
help / color / mirror / Atom feed

From: wenjing zeng <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: Prabhat Sahu <[email protected]>
Cc: tushar <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Konstantin Knizhnik <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: "蔡松露(子嘉)" <[email protected]>
Cc: Cai, Le <[email protected]>
Subject: Re: [Proposal] Global temporary tables
Date: Mon, 13 Jul 2020 19:59:24 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<CANEvxPrEAtmWN6z8T3ed81V5WZBxfrQ2LmHO1uOEOeUBh83Z-Q@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CANEvxPpfTrNGXw2K3MzFMt6NGBAQwLXbv6SHJ_XBEfqEimJxJA@mail.gmail.com>
	<[email protected]>
	<CANEvxPrVan7f_JgVr5chaOHGtMHFfL-A5hVcYbQj_-5aYWvDRQ@mail.gmail.com>
	<[email protected]>
	<CANEvxPpAxb30mnWdp4YZvwFF28A3zf3LCuEg91BE1djRTYSZcg@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CANEvxPoKNoddQ6UYOgXkGSZuc30KVZODySJu1E=a8hs1P7PPgA@mail.gmail.com>
	<[email protected]>
	<CANEvxPp1f1vN3ht6Bww=3yqGi46hUPVaiv8WWZwVMvWvGacVHg@mail.gmail.com>
	<CANEvxPqu2uiNW8r_ZY1iZLe6PwsJ59BY_6YqVRxmGUyUK=v=AA@mail.gmail.com>
	<CANEvxPrSOM26cGnTerCz-pJnNewroqJnw2BF97=BDmz6c+Rn8Q@mail.gmail.com>
	<[email protected]>
	<CAFj8pRCyE4iCp3s8JadPfFQNoZUv-MyMC4HtaxCpS6kiMyJbVA@mail.gmail.com>
	<[email protected]>
	<CANEvxPqetpWNzWgbeZw9RMJQRJhBH7A8Kx8DhsKrDcZEm6Ps4A@mail.gmail.com>
	<CAFj8pRDN2D3dK-+jbav_PdrWuSXX+Ld2kXoT-ZJa_nirqtLzxw@mail.gmail.com>
	<[email protected]>
	<CAFj8pRDVURvQBZhGeUEBBTaqnjfK3w7u+xZfAqVB+-ncUezFZA@mail.gmail.com>
	<[email protected]>
	<CANEvxPooq5GyoHVCw6LSjE5Ntxg+RkjBvyM_8-bJhUHmdsDG7w@mail.gmail.com>
	<[email protected]>
	<CANEvxPoWTnWvLeXsjPiCmEsUZpCgv8aQ7FU1DVoW2i49+8m5+g@mail.gmail.com>
	<[email protected]>
	<CANEvxPp-7yEBxD=N+HnRhPP+8wKUYmXgvQPxf445hphyQaH8ww@mail.gmail.com>
	<[email protected]>
	<CAFj8pRAqML3Jpp3iYpGNPWepdz=SMjxLZQDKy0UNUSxtA8mLSA@mail.gmail.com>
	<[email protected]>
	<CAFj8pRCp+r=WRXryPQ1Lxhq-tfZqomp00FjddUztqWaAOO4Pxg@mail.gmail.com>
	<[email protected]>



> 2020年7月10日 下午5:03，wenjing zeng <[email protected]> 写道：
> 
> HI all
> 
> I started using my personal email to respond to community issue.
> 
> 
> 
>> 2020年7月7日 下午6:05，Pavel Stehule <[email protected] <mailto:[email protected]>> 写道：
>> 
>> 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/;
>> 
>> 
>

view thread (492+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [Proposal] Global temporary tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox