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.
