Hi all
I try to use dblink to create a asynchronous logging facility. I have
the following code
-- open the dblink if it does not yet exist
V_DBLINK_CONNECTION_NAME :=
GET_PROPERTY_VALUE_STRING(
I_PROPERTY_NAME => 'DBLINK_CONNECTION_NAME'
);
select dblink_get_connections() into V_DBLINK_CONNECTION_NAMES;
if (
V_DBLINK_CONNECTION_NAMES is null
or V_DBLINK_CONNECTION_NAME != any(V_DBLINK_CONNECTION_NAMES)
) then
V_DBLINK_CONNECT_STRING :=
GET_PROPERTY_VALUE_STRING(
I_PROPERTY_NAME => 'DBLINK_CONNECT_STRING'
);
-- better to use dblink_connect_u with password file?
perform dblink_connect(
V_DBLINK_CONNECTION_NAME,
V_DBLINK_CONNECT_STRING
);
end if;
-- send query asynchronously
-- Use literal (%L) as it returns the value null as the
unquoted
-- string NULL.
V_QUERY := format(
$s$select true $s$ || C_LB ||
$s$ from %I( $s$ || C_LB ||
$s$ I_FUNCTION => %L, $s$ || C_LB ||
$s$ I_MESSAGE => %L, $s$ || C_LB ||
$s$ I_LEVEL => %L, $s$ || C_LB ||
$s$ I_PRESENT_USER => %L, $s$ || C_LB ||
$s$ I_SESSION_USER => %L, $s$ || C_LB ||
$s$ I_TRANSACTION_TIMESTAMP => $s$ ||
$s$ %L::timestamp, $s$ || C_LB ||
$s$ I_TRANSACTION_ID => $s$ ||
$s$ %L::bigint, $s$ || C_LB ||
$s$ I_SERVER_PID => $s$ ||
$s$ %L::bigint, $s$ || C_LB ||
$s$ I_REMOTE_ADDRESS => $s$ ||
$s$ %L::inet, $s$ || C_LB ||
$s$ I_REMOTE_PORT => $s$ ||
$s$ %L::bigint $s$ || C_LB ||
$s$ ); $s$ || C_LB ||
$s$commit $s$,
'WRITE_MESSAGE_TO_TABLE',
C_CALLER_FUNCTION,
I_MESSAGE,
I_LEVEL,
C_PRESENT_USER,
C_SESSION_USER,
C_TRANSACTION_TIMESTAMP,
C_TRANSACTION_ID,
C_SERVER_PID,
C_REMOTE_ADDRESS,
C_REMOTE_PORT
);
-- send query when connection is ready
V_WAIT_FOR :=
GET_PROPERTY_VALUE_INTERVAL(
I_PROPERTY_NAME => 'BUSY_WAIT_INTERVAL'
); -- to avoid continuous re-querying, already queried here
-- surprisingly, dblink_is_busy does not return boolean,
but 0 for
-- false
while dblink_is_busy(V_DBLINK_CONNECTION_NAME) != 0 loop
perform pg_sleep_for(V_WAIT_FOR);
end loop;
perform dblink_send_query(
V_DBLINK_CONNECTION_NAME,
V_QUERY
);
raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME);
raise notice 'Last error: %',
dblink_error_message(V_DBLINK_CONNECTION_NAME);
raise notice 'Cancel query: %',
dblink_cancel_query(V_DBLINK_CONNECTION_NAME);
-- ??? commit needed?
raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME);
while dblink_is_busy(V_DBLINK_CONNECTION_NAME) != 0 loop
perform pg_sleep_for(V_WAIT_FOR);
raise notice 'Waited for commit for % seconds', V_WAIT_FOR;
raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME);
end loop;
perform dblink_send_query(
V_DBLINK_CONNECTION_NAME,
'commit'
);
I get the following output.
psql:testing/test.pg_sql:41: NOTICE: Connection busy: 1
psql:testing/test.pg_sql:41: NOTICE: Last error: OK
psql:testing/test.pg_sql:41: NOTICE: Cancel query: OK
psql:testing/test.pg_sql:41: NOTICE: Connection busy: 0
psql:testing/test.pg_sql:41: NOTICE: could not send query: another
command is already in progress
I did all the raise notice and dblink querying and cancelling to get
some information on what is going on but I am no wiser than before as
without that the connection was not busy either. But it was still
blocking I had the second call even though the commit did not seem to
work and I was trying to send it for good. Btw, there is no entry in the
logging table which is being done when the same function is called
without using dblink.
Maybe I am wrong but I tried the solution with dblink_connect
dblink_send_query instead of simply dblink believing that dblink
function would open and close a connection at every call. I wanted to
avoid this overhead.
Has anyone an idea?
--
SIP/iptel.org: thiemo.kellner
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC
begin:vcard
fn:Thiemo Kellner
n:Kellner;Thiemo
adr:;;Landstr. 34;Weilheim-Bannholz;BW;79809;Deutschland
email;internet:thiemo@xxxxxxxxxxxxxxxxxxxx
tel;work:+49 1578 772 37 37
tel;cell:+41 78 947 36 21
note;quoted-printable:Auf Gelassene Pferde kann man bauen!=0D=0A=
+49 (0)1578-772 37 37 (Mo, Di)=0D=0A=
+41 (0)78 947 36 21 (Mi - Fr)=0D=0A=
sip: thiemo.kellner@xxxxxxxxx=0D=0A=
Skype: thiemo.kellner=0D=0A=
http://www.gelassene-pferde.biz=0D=0A=
Mitglied bei http://www.keep-it-natural.org=0D=0A=
=C3=96ffentlicher PGP-Schl=C3=BCssel: http://pgp.mit.edu/pks/lookup?op=3D=
get&search=3D0x8F70EFD2D972CBEF
x-mozilla-html:FALSE
url:http://www.gelassene-pferde.biz
version:2.1
end:vcard
