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

2020年1月22日 上午2:51，Pavel Stehule <pavel.stehule@gmail.com> 写道：

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

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

ON COMMIT PRESERVE ROWS is default mode now.

Thank you

* I tried to create global temp table with index. When I tried to drop this table (and this table was used by second instance), then I got error message

postgres=# drop table foo;
ERROR:  can not drop index when other backend attached this global temp table

It is expected, but it is not too much user friendly. Is better to check if you can drop table, then lock it, and then drop all objects.

I don't understand what needs to be improved. Could you describe it in detail?

* tab complete can be nice for CREATE GLOBAL TEMP table

Yes, I will improve it.

\dt+ \di+ doesn't work correctly, or maybe I don't understand to the implementation.

postgres=# create table t(a int primary key);

CREATE TABLE

postgres=# create global temp table gt(a int primary key);

CREATE TABLE

postgres=# insert into t values(generate_series(1,10000));

INSERT 0 10000

postgres=# insert into gt values(generate_series(1,10000));

INSERT 0 10000

postgres=# \dt+

                            List of relations

 Schema | Name | Type  |    Owner    | Persistence |  Size  | Description 

--------+------+-------+-------------+-------------+--------+-------------

 public | gt   | table | wenjing.zwj | session     | 384 kB | 

 public | t    | table | wenjing.zwj | permanent   | 384 kB | 

(2 rows)

postgres=# \di+

                                  List of relations

 Schema |  Name   | Type  |    Owner    | Table | Persistence |  Size  | Description 

--------+---------+-------+-------------+-------+-------------+--------+-------------

 public | gt_pkey | index | wenjing.zwj | gt    | session     | 240 kB | 

 public | t_pkey  | index | wenjing.zwj | t     | permanent   | 240 kB | 

(2 rows)

I see same size in all sessions. Global temp tables shares same files?

No, they use their own files.

But \dt+ \di+ counts the total file sizes in all sessions for each GTT.

Wenjing

Regards

Pavel

Wenjing

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.

Todo:

pg_table_size function doesn't work

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