Re: [Proposal] Global temporary tables

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

>
>
> 2020年1月22日 上午2:51，Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> 写道：
>
>
>
> út 21. 1. 2020 v 9:46 odesílatel 曾文旌(义从) <wenjing(dot)zwj(at)alibaba-inc(dot)com>
> napsal:
>
>>
>>
>> 2020年1月12日 上午4:27，Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> 写道：
>>
>> Hi
>>
>> so 11. 1. 2020 v 15:00 odesílatel 曾文旌(义从) <wenjing(dot)zwj(at)alibaba-inc(dot)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

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

I think so it is wrong. The data are independent and the sizes should be
independent too

>
>
> 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(dot)vondra(at)2ndquadrant(dot)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
>>> <http://www.2ndquadrant.com/>
>>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>>
>>>
>>>
>>
>