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

From 曾文旌(义从)
Subject Re: [Proposal] Global temporary tables
Date
Msg-id 8B1EEE23-D7A0-419B-9A41-C6FBDB49C441@alibaba-inc.com
Whole thread Raw
In response to Re: [Proposal] Global temporary tables  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-hackers


2020年1月22日 下午2:31,Pavel Stehule <pavel.stehule@gmail.com> 写道:



st 22. 1. 2020 v 7:16 odesílatel 曾文旌(义从) <wenjing.zwj@alibaba-inc.com> napsal:


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?

the error messages should be some like

can not drop table when other backend attached this global temp table.

It is little bit messy, when you try to drop table and you got message about index
It has been repaired in global_temporary_table_v7-pg13.patch




* tab complete can be nice for CREATE GLOBAL TEMP table
Yes, I will improve it.
It has been repaired in global_temporary_table_v7-pg13.patch


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

I think so it is wrong. The data are independent and the sizes should be independent too
It has been repaired in global_temporary_table_v7-pg13.patch.


Wenjing







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




Attachment

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Allow to_date() and to_timestamp() to accept localized names
Next
From: "曾文旌(义从)"
Date:
Subject: Re: [Proposal] Global temporary tables