Re: [Proposal] Global temporary tables

--0000000000002ddd6f059c1976aa
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

=C3=BAt 14. 1. 2020 v 14:09 odes=C3=ADlatel =E6=9B=BE=E6=96=87=E6=97=8C(=E4=
=B9=89=E4=BB=8E) <[email protected]>
napsal:

> Thank you for review my patch.
>
>
> 2020=E5=B9=B41=E6=9C=8812=E6=97=A5 =E4=B8=8A=E5=8D=884:27=EF=BC=8CPavel S=
tehule <[email protected]> =E5=86=99=E9=81=93=EF=BC=9A
>
> Hi
>
> so 11. 1. 2020 v 15:00 odes=C3=ADlatel =E6=9B=BE=E6=96=87=E6=97=8C(=E4=B9=
=89=E4=BB=8E) <[email protected]>
> 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=E2=80=99s statisti=
cs
>> 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 loca=
l
> 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=E5=B9=B41=E6=9C=886=E6=97=A5 =E4=B8=8A=E5=8D=884:06=EF=BC=8CTomas V=
ondra <[email protected]> =E5=86=99=E9=81=93=EF=BC=9A
>>
>> 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
>>
>>
>>
>

--0000000000002ddd6f059c1976aa
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">=C3=BAt 14. 1. 2020 v=C2=A014:09 odes=
=C3=ADlatel =E6=9B=BE=E6=96=87=E6=97=8C(=E4=B9=89=E4=BB=8E) &lt;<a href=3D"=
mailto:[email protected]">[email protected]</a>&gt; nap=
sal:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div style=
=3D"overflow-wrap: break-word;">Thank you for review my patch.<div><br><div=
><br><blockquote type=3D"cite"><div>2020=E5=B9=B41=E6=9C=8812=E6=97=A5 =E4=
=B8=8A=E5=8D=884:27=EF=BC=8CPavel Stehule &lt;<a href=3D"mailto:pavel.stehu=
[email protected]" target=3D"_blank">[email protected]</a>&gt; =E5=86=99=
=E9=81=93=EF=BC=9A</div><br><div><div dir=3D"ltr"><div>Hi<br></div><br><div=
 class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">so 11. 1. 2020=
 v=C2=A015:00 odes=C3=ADlatel =E6=9B=BE=E6=96=87=E6=97=8C(=E4=B9=89=E4=BB=
=8E) &lt;<a href=3D"mailto:[email protected]" target=3D"_blank">w=
[email protected]</a>&gt; napsal:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div>Hi all<div><br></div><div>This is the lates=
t patch</div><div><br></div><div>The updates are as follows:</div><div>1. S=
upport global temp Inherit table=C2=A0<span>global temp partition table</sp=
an></div><div><span>2. Support=C2=A0</span><font><span>serial column in GTT=
</span></font></div><div><span>3.=C2=A0</span><font><span>Provide views=C2=
=A0pg_gtt_relstats pg_gtt_stats=C2=A0for GTT=E2=80=99s statistics</span></f=
ont></div><div><span>4.=C2=A0</span><span>Provide</span>=C2=A0view=C2=A0<sp=
an>pg_gtt_attached_pids to=C2=A0</span><span>manage=C2=A0GTT</span></div><d=
iv><span>5.=C2=A0</span><span>Provide function=C2=A0</span><font>pg_list_gt=
t_relfrozenxids() to manage=C2=A0GTT</font></div><div><font>6.=C2=A0<span>A=
lter GTT or rename GTT is allowed under some conditions</span></font></div>=
<div><br></div><div><br></div><div>Please give me feedback.</div></div></bl=
ockquote><div><br></div><div>I tested the functionality</div><div><br></div=
><div>1. i think so &quot;ON COMMIT PRESERVE ROWS&quot; should be default m=
ode (like local temp tables).</div></div></div></div></blockquote>makes sen=
se, I will fix it.</div><div><br><blockquote type=3D"cite"><div><div dir=3D=
"ltr"><div class=3D"gmail_quote"><div><br></div><div>I tested some simple s=
cripts <br></div><div><br></div><div>test01.sql</div><div><br></div><div>CR=
EATE TEMP TABLE foo(a int, b int);<br>INSERT INTO foo SELECT random()*100, =
random()*1000 FROM generate_series(1,1000);<br>ANALYZE foo;<br>SELECT sum(a=
), sum(b) FROM foo;<br>DROP TABLE foo; -- simulate disconnect<br></div><div=
><br></div><div><br></div><div>after 100 sec, the table pg_attribute has 3.=
2MB</div><div>and 64 tps, 6446 transaction<br></div><div><br></div><div>tes=
t02.sql</div><div><br></div><div>INSERT INTO foo SELECT random()*100, rando=
m()*1000 FROM generate_series(1,1000);<br>ANALYZE foo;<br>SELECT sum(a), su=
m(b) FROM foo;<br>DELETE FROM foo; -- simulate disconnect<br></div><div><br=
></div><div><br></div><div>after 100 sec, 1688 tps, 168830 transactions</di=
v><div><br></div><div>So performance is absolutely different as we expected=
.</div><div><br></div><div>From my perspective, this functionality is great=
.</div></div></div></div></blockquote>Yes, frequent ddl causes=C2=A0<span s=
tyle=3D"color:rgb(0,0,0)">catalog bloat,=C2=A0</span><font color=3D"#000000=
"><span>GTT avoids this problem.</span></font></div><div><font color=3D"#00=
0000"><span><br></span></font><blockquote type=3D"cite"><div><div dir=3D"lt=
r"><div class=3D"gmail_quote"><div><br></div><div>Todo:</div><div><br></div=
><div>pg_table_size function doesn&#39;t work</div></div></div></div></bloc=
kquote>Do you mean that function=C2=A0<span style=3D"color:rgb(0,0,0)">pg_t=
able_size() need get</span>=C2=A0the storage space used by the one GTT in t=
he entire db(include all session) .</div></div></div></blockquote><div><br>=
</div><div>It&#39;s question how much GTT tables should be similar to class=
ic tables. But the reporting in psql should to work \dt+, \l+, \di+<br></di=
v><div><br></div><div> <br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><div style=3D"overflow-wrap: break-word;"><div><div><br><blockquote=
 type=3D"cite"><div><div dir=3D"ltr"><div class=3D"gmail_quote"><div><br></=
div><div>Regards</div><div><br></div><div>Pavel<br></div><div><br></div><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t:1px solid rgb(204,204,204);padding-left:1ex"><div><div><br></div><div>Wen=
jing</div><div><br></div><div><br></div><div><br></div><div></div></div><di=
v><div><br><div><br><blockquote type=3D"cite"><div>2020=E5=B9=B41=E6=9C=886=
=E6=97=A5 =E4=B8=8A=E5=8D=884:06=EF=BC=8CTomas Vondra &lt;<a href=3D"mailto=
:[email protected]" target=3D"_blank">[email protected]=
om</a>&gt; =E5=86=99=E9=81=93=EF=BC=9A</div><br><div><div>Hi,<br><br>I thin=
k we need to do something with having two patches aiming to add<br>global t=
emporary tables:<br><br>[1] <a href=3D"https://commitfest.postgresql.org/26=
/2349/" target=3D"_blank">https://commitfest.postgresql.org/26/2349/</a><br=
><br>[2] <a href=3D"https://commitfest.postgresql.org/26/2233/" target=3D"_=
blank">https://commitfest.postgresql.org/26/2233/</a><br><br>As a reviewer =
I have no idea which of the threads to look at - certainly<br>not without r=
eading both threads, which I doubt anyone will really do.<br>The reviews an=
d discussions are somewhat intermixed between those two<br>threads, which m=
akes it even more confusing.<br><br>I think we should agree on a minimal pa=
tch combining the necessary/good<br>bits from the various patches, and term=
inate one of the threads (i.e.<br>mark it as rejected or RWF). And we need =
to do that now, otherwise<br>there&#39;s about 0% chance of getting this in=
to v13.<br><br>In general, I agree with the sentiment Rober expressed in [1=
] - the<br>patch needs to be as small as possible, not adding &quot;nice to=
 have&quot;<br>features (like support for parallel queries - I very much do=
ubt just<br>using shared instead of local buffers is enough to make it work=
.)<br><br>regards<br><br>-- <br>Tomas Vondra =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0<a =
href=3D"http://www.2ndquadrant.com/" target=3D"_blank">http://www.2ndQuadra=
nt.com</a><br>PostgreSQL Development, 24x7 Support, Remote DBA, Training &a=
mp; Services</div></div></blockquote></div><br></div></div></blockquote></d=
iv></div>
</div></blockquote></div><br></div></div></blockquote></div></div>

--0000000000002ddd6f059c1976aa--