<div dir="ltr"><br><div>There are several problems with this query:</div><div> - Firstly, it is not very good to delete the table at the very beginning of the function. This means that if two such functions are launched in parallel, they will queue up. Just remove the drop of this table, and all the temporary ones will start to be created in their own namespace.</div><div> - Secondly, it's not very good not to drop the temporary table before exiting the function. All this function can be replaced by one in the SQL language, without creating temporary tables at all.</div><div> - Thirdly, the function execution continues after RETURN QUERY, which you can in principle call several times to return some more roes. In your case there's nothing after it - but you could delete the temporary table there, making all the parallel function calls independent.<br><br><br></div><div class="gmail_quote"><div dir="ltr">ср, 6 июн. 2018 г. в 12:56, Paulo Pires <<a href="mailto:paulopires16@gmail.com">paulopires16@gmail.com</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_default">
<div class="gmail_default" style="background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><div class="gmail_default"><font color="#444444" face="times new roman, serif">I noticed that when creating temporary tables and even with their deletion, the process associated with creating this tables is locked and is only released at the end of the whole process.</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"><br></font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif">"internal_angle" and "corner_triplets" are also temporary tables</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"><br></font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif">Here is an example of creating and deleting the temporary table "internal_angs":</font></div></div></div></div><div dir="ltr"><div class="gmail_default"><div class="gmail_default" style="background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><div class="gmail_default"><font color="#444444" face="times new roman, serif"><br></font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> CREATE OR REPLACE FUNCTION internal_angles(geom geometry)</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> RETURNS table(corner geometry, angle float)</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> AS $$</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> DECLARE</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> pid_num integer;</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> BEGIN</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> DROP TABLE IF EXISTS internal_angs;</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> CREATE TEMP TABLE internal_angs on commit drop as select p1, internal_angle(geom, p1, p2, p3) from (select (c).* from (select corner_triplets(geom) as c) as t1) as t2;</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> return QUERY TABLE internal_angs;</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> END;</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> $$</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"> LANGUAGE plpgsql;</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"><br></font></div></div></div></div><div dir="ltr"><div class="gmail_default"><div class="gmail_default" style="background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><div class="gmail_default"><font color="#444444" face="times new roman, serif">Until the process finishes, the processes in memory (pids) are increasing by each temporary table ...</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"><br></font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif">Is there any way to terminate the process when the table is deleted?</font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif"><br></font></div><div class="gmail_default"><font color="#444444" face="times new roman, serif">Thank you</font></div></div>
<br></div></div><div class="gmail_extra"></div><div class="gmail_extra"><br clear="all"><div><div class="m_-3439654487781851229gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><p style="FONT-FAMILY:'Times New Roman';FONT-SIZE:medium"></p>
<p></p>
<p style="FONT-FAMILY:'Times New Roman';FONT-SIZE:medium"></p>
<p></p>
<p style="FONT-FAMILY:'Times New Roman';FONT-SIZE:medium"></p>
<p></p>
<p style="FONT-FAMILY:'Times New Roman';FONT-SIZE:medium"></p>
<p style="FONT-FAMILY:'Courier New';FONT-SIZE:12px"></p>
<p><font face="times new roman, serif"><span style="COLOR:rgb(153,153,153)"><br></span></font></p>
<p><font face="times new roman, serif"><span style="COLOR:rgb(153,153,153)">Paulo Pires</span> </font></p>
<p></p>
<hr align="left" size="1" width="95">
<font size="1" face="times new roman, serif"><span style="COLOR:rgb(255,102,0)">phone</span> <span style="COLOR:rgb(153,153,153)">964592113</span> <br><span style="COLOR:rgb(255,102,0)">e-mail</span> <a href="mailto:paulopires16@gmail.com" target="_blank"><span style="COLOR:rgb(153,153,153)">paulopires16@gmail.com</span></a> <br><span style="COLOR:rgb(255,102,0)">crypt</span> <a href="http://kerckhoffs.surfnet.nl/pks/lookup?op=get&search=0x878711213981EEF6" target="_blank"><span style="COLOR:rgb(153,153,153)">myPGP public key for encryption emails</span></a> <br><span style="COLOR:rgb(255,102,0)">links </span> <a href="http://facebook.com/paulopires16" target="_blank"><span style="COLOR:rgb(153,153,153)">facebook</span></a> · <a href="http://twitter.com/#!/paulopires16" target="_blank"><span style="COLOR:rgb(153,153,153)">twitter</span></a> · <a href="http://www.linkedin.com/pub/paulo-pires/36/4a7/607" target="_blank"><span style="COLOR:rgb(153,153,153)">linkedin</span></a> · <a href="http://netlog.com/paulopires16" target="_blank"><span style="COLOR:rgb(153,153,153)">netlog</span></a> · <a href="http://download.oracle.com/javase/6/docs/api/" target="_blank"><span style="COLOR:rgb(153,153,153)">javase</span></a> · <a href="http://www.oracle.com/us/syndication/feeds/index.html" target="_blank"><span style="COLOR:rgb(153,153,153)">oracle</span></a> <br></font>
<div align="center" style="text-align:center"><span style="font-size:12.0pt;font-family:"Times New Roman","serif"">
<hr size="2" width="100%" align="center">
</span></div>
<p style="margin-bottom:12.0pt"><span style="font-size:7.5pt;font-family:"Arial","sans-serif";color:gray">Aviso Legal:<br>
Esta mensagem de Paulo Pires é destinada exclusivamente ao destinatário. Pode conter
informação confidencial ou legalmente protegida. A incorrecta transmissão desta
mensagem não significa a perda de confidencialidade. Se esta mensagem for
recebida por engano, por favor envie-a de volta para o remetente e apague-a do
seu sistema de imediato. É proibido a qualquer pessoa que não o destinatário de
usar, revelar ou distribuir qualquer parte desta mensagem.<br>
<br>
Disclaimer:<br>
This message from Paulo Pires is destined exclusively to the intended receiver. It
may contain confidential or legally protected information. The incorrect
transmission of this message does not mean the loss of its confidentiality. If
this message is received by mistake, please send it back to the sender and
delete it from your system immediately. It is forbidden to any person who is
not the intended receiver to use, distribute or copy any part of this message.</span></p></div></div></div>
<br></div><div class="gmail_extra"><div class="gmail_quote">2018-06-06 10:49 GMT+01:00 Darafei "Komяpa" Praliaskouski <span dir="ltr"><<a href="mailto:me@komzpa.net" target="_blank">me@komzpa.net</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Привет,<br><br>This is English mailing list. Hope we all can translate ;)<br><br>В этом запросе есть несколько проблем:<br> - во-первых, не очень хорошо удалять таблицу в самом начале функции. Это значит, что если две таких функции запустятся параллельно, они встанут в очередь. Просто уберите drop этой таблицы, и все временные начнут создаваться в своих отдельных неймспейсах.<br> - во-вторых, не очень хорошо не удалять временную таблицу перед выходом из функции. Всю эту функцию можно заменить на одну на языке SQL, не создавая временных таблиц вовсе.<br> - в-третьих, выполнение функции продолжается после RETURN QUERY, который в общем-то ничего не мешает даже вызвать несколько раз. В вашем случае оно упирается в никуда - но можно было бы удалить там временную таблицу, сделав все параллельные запуски функции независимыми.<br><br><div class="gmail_quote"><div dir="ltr">ср, 6 июн. 2018 г. в 12:36, Paulo Pires <<a href="mailto:paulopires16@gmail.com" target="_blank">paulopires16@gmail.com</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="m_-3439654487781851229h5"><div dir="ltr"><div class="gmail_default" style="font-family:"times new roman",serif;font-size:small;color:rgb(68,68,68)">Verifiquei que aquando da criação de tabelas temporárias e mesmo com a sua eliminação, o processo associado à criação dessa tabela fica lock e só é libertado no fim de todo o processo.</div><div class="gmail_default" style="font-family:"times new roman",serif;font-size:small;color:rgb(68,68,68)"><br></div><div class="gmail_default" style="font-family:"times new roman",serif;font-size:small;color:rgb(68,68,68)">
<span style="background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">"internal_angle"</span> e "<span style="background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">corner_triplets"</span> são também tabelas temporárias<br></div><div class="gmail_default" style="font-family:"times new roman",serif;font-size:small;color:rgb(68,68,68)"><br></div><div class="gmail_default" style="font-family:"times new roman",serif;font-size:small;color:rgb(68,68,68)">Segue função exemplo da criação e eliminação da tabela temporária "<span style="background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">internal_angs<span> </span></span>":</div><div class="gmail_default" style="font-family:"times new roman",serif;font-size:small;color:rgb(68,68,68)"><br></div><div class="gmail_default" style="font-family:"times new roman",serif;font-size:small;color:rgb(68,68,68)"><div class="gmail_default"> CREATE OR REPLACE FUNCTION internal_angles(geom geometry)</div><div class="gmail_default"> RETURNS table(corner geometry, angle float)</div><div class="gmail_default"> AS $$</div><div class="gmail_default"> DECLARE</div><div class="gmail_default"> pid_num integer;</div><div class="gmail_default"> BEGIN</div><div class="gmail_default"> DROP TABLE IF EXISTS internal_angs;</div><div class="gmail_default"> CREATE TEMP TABLE internal_angs on commit drop as select p1, internal_angle(geom, p1, p2, p3) from (select (c).* from (select corner_triplets(geom) as c) as t1) as t2;</div><div class="gmail_default"> return QUERY TABLE internal_angs;</div><div class="gmail_default"> END;</div><div class="gmail_default"> $$</div><div class="gmail_default"> LANGUAGE plpgsql;</div><div class="gmail_default"><br></div><div class="gmail_default">Até o processo terminar, os processos em memória (pids) vão aumentando por cada tabela temporária...</div><div class="gmail_default"><br></div><div class="gmail_default">Existe alguma maneira de terminar o processo quando a tabela é eliminada?</div><div class="gmail_default"><br></div><div class="gmail_default">Obrigado</div></div><div><div class="m_-3439654487781851229m_-7219594907527111499m_-7687356021876080807gmail_signature"><div dir="ltr">
<p><font face="times new roman, serif"><span style="color:rgb(153,153,153)">Paulo Pires</span> </font></p>
<p></p>
<hr align="left" size="1" width="95">
<font size="1" face="times new roman, serif"><span style="color:rgb(255,102,0)">phone</span> <span style="color:rgb(153,153,153)">964592113</span> <br><span style="color:rgb(255,102,0)">e-mail</span> <a href="mailto:paulopires16@gmail.com" target="_blank"><span style="color:rgb(153,153,153)">paulopires16@gmail.com</span></a> <br><span style="color:rgb(255,102,0)">crypt</span> <a href="http://kerckhoffs.surfnet.nl/pks/lookup?op=get&search=0x878711213981EEF6" target="_blank"><span style="color:rgb(153,153,153)">myPGP public key for encryption emails</span></a> <br><span style="color:rgb(255,102,0)">links </span> <a href="http://facebook.com/paulopires16" target="_blank"><span style="color:rgb(153,153,153)">facebook</span></a> · <a href="http://twitter.com/#!/paulopires16" target="_blank"><span style="color:rgb(153,153,153)">twitter</span></a> · <a href="http://www.linkedin.com/pub/paulo-pires/36/4a7/607" target="_blank"><span style="color:rgb(153,153,153)">linkedin</span></a> · <a href="http://netlog.com/paulopires16" target="_blank"><span style="color:rgb(153,153,153)">netlog</span></a> · <a href="http://download.oracle.com/javase/6/docs/api/" target="_blank"><span style="color:rgb(153,153,153)">javase</span></a> · <a href="http://www.oracle.com/us/syndication/feeds/index.html" target="_blank"><span style="color:rgb(153,153,153)">oracle</span></a> <br></font>
<div align="center" style="text-align:center"><span style="font-size:12pt;font-family:"Times New Roman",serif">
<hr size="2" width="100%" align="center">
</span></div>
<p style="margin-bottom:12pt"><span style="font-size:7.5pt;font-family:Arial,sans-serif;color:gray">Aviso Legal:<br>
Esta mensagem de Paulo Pires é destinada exclusivamente ao destinatário. Pode conter
informação confidencial ou legalmente protegida. A incorrecta transmissão desta
mensagem não significa a perda de confidencialidade. Se esta mensagem for
recebida por engano, por favor envie-a de volta para o remetente e apague-a do
seu sistema de imediato. É proibido a qualquer pessoa que não o destinatário de
usar, revelar ou distribuir qualquer parte desta mensagem.<br>
<br>
Disclaimer:<br>
This message from Paulo Pires is destined exclusively to the intended receiver. It
may contain confidential or legally protected information. The incorrect
transmission of this message does not mean the loss of its confidentiality. If
this message is received by mistake, please send it back to the sender and
delete it from your system immediately. It is forbidden to any person who is
not the intended receiver to use, distribute or copy any part of this message.</span></p></div></div></div>
</div></div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div></div>