Re: [Proposal] Global temporary tables - Mailing list pgsql-hackers

2020年1月14日 下午9:20，Pavel Stehule <pavel.stehule@gmail.com> 写道：

út 14. 1. 2020 v 14:09 odesílatel 曾文旌(义从) <wenjing.zwj@alibaba-inc.com> napsal:

Thank you for review my patch.

2020年1月12日 上午4:27，Pavel Stehule <pavel.stehule@gmail.com> 写道：

Hi

so 11. 1. 2020 v 15:00 odesílatel 曾文旌(义从) <wenjing.zwj@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+

Regards

Pavel

Wenjing

2020年1月6日 上午4:06，Tomas Vondra <tomas.vondra@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/

[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