[postgis-users] Delete locks temp table

Wed Jun 6 02:56:36 PDT 2018

 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180606/1a9417e5/attachment.html>