Re: dblink: could not send query: another command is already in progress

On 04/03/18 11:28, Laurenz Albe wrote:
>> [...]
>> psql:testing/test.pg_sql:42: ERROR: function
>> WRITE_MESSAGE_TO_TABLE(i_function => text, i_message => text, i_level =>
>> text, i_present_user => name, i_session_user => name,
>> i_transaction_timestamp => timestamp with time zone, i_transaction_id =>
>> bigint, i_server_pid => bigint, i_remote_address => inet, i_remote_port
>> => integer) does not exist
>> HINT: No function matches the given name and argument types. You might
>> need to add explicit type casts.
>> CONTEXT: Error occurred on dblink connection named
>> "PGLOGGER_CONNECTION": could not execute query.
>> SQL statement "SELECT * from
>> dblink_get_result(V_DBLINK_CONNECTION_NAME) as t(ID bigint)"
>> PL/pgSQL function write_message(text,text) line 126 at PERFORM
>> psql:testing/test.pg_sql:42: STATEMENT: select WRITE_MESSAGE('Test
>> WRITE_MESSAGE 2nd call', 'NOTICE');
>
> I couldn't dig through all your code, but two things seemed suspicious:

Thanks for looking into all the same.

>> if (
>> V_DBLINK_CONNECTION_NAMES is null
>> or V_DBLINK_CONNECTION_NAME != any(V_DBLINK_CONNECTION_NAMES)
>
> I don't think you want "!=ANY" there. Shouldn't that be "<>ALL" ?
> "<>ANY" will *always* be true if there are at least two different entries.

In my simple test it works as expected with respect to that. But I did
use it the wrong way as I thought that ! would invert the result of the
any operation as a whole. I changed it to != all but the error persists.

> The other thing is that you seem to call "dblink_get_result" on any existing
> connection before use. But you can only call the function if there is a
> result outstanding.

I call dblink_get_result only if I do not open a dblink connection, i.
e. only on second and following function calls. I put more notice output
into the code showing that dblink_send_query has been called once before
the first call of dblink_get_result. I changed my query to reflect
return bigint value of the called function write_message_to_table. Error
persists.

I put a new pastebin https://pastebin.com/7R45R7qK and committed the
changes.

--
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
SIP/iptel.org: thiemo.kellner
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC