Search Postgresql Archives

thoughts on interactive query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I am playing around with making interactive queries and was wondering if anyone had any comments.

If your comment is "That is a stupid idea", please try to qualify that with something constructive as well.


The idea is that sometimes during a process, user input is required. The way we have been doing this is to return an error code and then the GUI asks the user the question and restarts the query with the answer passed as a parameter.


The problem with this is that it is sometimes a long, complicated transaction and ending it in the middle just to ask the user "yes or no" and then running the entire transaction again seems awfully inefficient.


What I have tried successfully is the following plpython function:


create or replace function python_prompt(v_question text) returns bool as
$$
import socket
ipaddr=plpy.execute("select inet_client_addr()")[0]["inet_client_addr"]
HOST, PORT = str(ipaddr), 9999
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.settimeout(30)
sock.connect((HOST, PORT))
sock.send(v_question + "\n")
ans=sock.recv(1024) # Processing waits here for either an answer or the timeout to expire
sock.close()
if ans=="yes":
    return 1
else:
    return 0
$$ language 'plpythonu';


I added a socket server to my application, so each client listens on the same port.

If the query requires user input in the middle of the function, it can then ask and wait for the answer. If it doesn't receive an answer within the timeout period (30 seconds in this case) it dies with a timeout error.


A use case for this is:


create or replace function myprocess() returns int as

$$

begin

--long process

if not python_prompt('The final computed numbers fall out of normal range. To continue with this process you must manually override. Do you want to override?') then

        raise exception 'Numbers out of normal range';

    end if;

    return 0;

end;

$$ language 'plpgsql';


During the wait time, I didn't see any CPU or memory usage , so setting a high timeout will use a connection but won't grind the server to a halt.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux