[postgis-users] Delete locks temp table
Darafei "Komяpa" Praliaskouski
me at komzpa.net
Wed Jun 6 03:00:37 PDT 2018
There are several problems with this query:
- 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.
- 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.
- 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.
ср, 6 июн. 2018 г. в 12:56, Paulo Pires <paulopires16 at gmail.com>:
> 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.
>
> "internal_angle" and "corner_triplets" are also temporary tables
>
> Here is an example of creating and deleting the temporary table
> "internal_angs":
>
> CREATE OR REPLACE FUNCTION internal_angles(geom geometry)
> RETURNS table(corner geometry, angle float)
> AS $$
> DECLARE
> pid_num integer;
> BEGIN
> DROP TABLE IF EXISTS internal_angs;
> 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;
> return QUERY TABLE internal_angs;
> END;
> $$
> LANGUAGE plpgsql;
>
> Until the process finishes, the processes in memory (pids) are increasing
> by each temporary table ...
>
> Is there any way to terminate the process when the table is deleted?
>
> Thank you
>
>
>
> Paulo Pires
>
> ------------------------------
> phone 964592113
> e-mail paulopires16 at gmail.com
> crypt myPGP public key for encryption emails
> <http://kerckhoffs.surfnet.nl/pks/lookup?op=get&search=0x878711213981EEF6>
>
> links facebook <http://facebook.com/paulopires16> · twitter
> <http://twitter.com/#!/paulopires16> · linkedin
> <http://www.linkedin.com/pub/paulo-pires/36/4a7/607> · netlog
> <http://netlog.com/paulopires16> · javase
> <http://download.oracle.com/javase/6/docs/api/> · oracle
> <http://www.oracle.com/us/syndication/feeds/index.html>
> ------------------------------
>
> Aviso Legal:
> 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.
>
> Disclaimer:
> 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.
>
> 2018-06-06 10:49 GMT+01:00 Darafei "Komяpa" Praliaskouski <me at komzpa.net>:
>
>> Привет,
>>
>> This is English mailing list. Hope we all can translate ;)
>>
>> В этом запросе есть несколько проблем:
>> - во-первых, не очень хорошо удалять таблицу в самом начале функции.
>> Это значит, что если две таких функции запустятся параллельно, они встанут
>> в очередь. Просто уберите drop этой таблицы, и все временные начнут
>> создаваться в своих отдельных неймспейсах.
>> - во-вторых, не очень хорошо не удалять временную таблицу перед выходом
>> из функции. Всю эту функцию можно заменить на одну на языке SQL, не
>> создавая временных таблиц вовсе.
>> - в-третьих, выполнение функции продолжается после RETURN QUERY, который
>> в общем-то ничего не мешает даже вызвать несколько раз. В вашем случае оно
>> упирается в никуда - но можно было бы удалить там временную таблицу, сделав
>> все параллельные запуски функции независимыми.
>>
>> ср, 6 июн. 2018 г. в 12:36, Paulo Pires <paulopires16 at gmail.com>:
>>
>>> 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.
>>>
>>> "internal_angle" e "corner_triplets" são também tabelas temporárias
>>>
>>> Segue função exemplo da criação e eliminação da tabela temporária "
>>> internal_angs ":
>>>
>>> CREATE OR REPLACE FUNCTION internal_angles(geom geometry)
>>> RETURNS table(corner geometry, angle float)
>>> AS $$
>>> DECLARE
>>> pid_num integer;
>>> BEGIN
>>> DROP TABLE IF EXISTS internal_angs;
>>> 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;
>>> return QUERY TABLE internal_angs;
>>> END;
>>> $$
>>> LANGUAGE plpgsql;
>>>
>>> Até o processo terminar, os processos em memória (pids) vão aumentando
>>> por cada tabela temporária...
>>>
>>> Existe alguma maneira de terminar o processo quando a tabela é eliminada?
>>>
>>> Obrigado
>>>
>>> Paulo Pires
>>>
>>> ------------------------------
>>> phone 964592113
>>> e-mail paulopires16 at gmail.com
>>> crypt myPGP public key for encryption emails
>>> <http://kerckhoffs.surfnet.nl/pks/lookup?op=get&search=0x878711213981EEF6>
>>>
>>> links facebook <http://facebook.com/paulopires16> · twitter
>>> <http://twitter.com/#!/paulopires16> · linkedin
>>> <http://www.linkedin.com/pub/paulo-pires/36/4a7/607> · netlog
>>> <http://netlog.com/paulopires16> · javase
>>> <http://download.oracle.com/javase/6/docs/api/> · oracle
>>> <http://www.oracle.com/us/syndication/feeds/index.html>
>>> ------------------------------
>>>
>>> Aviso Legal:
>>> 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.
>>>
>>> Disclaimer:
>>> 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.
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180606/4a3ffecc/attachment.html>
More information about the postgis-users
mailing list