Hi allI 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
<<attachment: thiemo.vcf>>