Re: [Proposal] Global temporary tables

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

* tab complete can be nice for CREATE GLOBAL TEMP table

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

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

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