Re: dblink: could not send query: another command is already inprogress

Thiemo Kellner wrote:
> On 03/30/18 11:14, Laurenz Albe wrote:
> > You have to consume the result before you can send the next query.
> 
> I changed implementation but still get the same error but now different 
> context. I tried to retrieve the result but I failed
> 
> I committed the last code to its project repository at SourceForge 
> https://sourceforge.net/p/pglogger/code/HEAD/tree/
> 
> and put the code to pastebin.com
> Test calls: https://pastebin.com/xfUp9NAL
> function WRITE_MESSAGE: https://pastebin.com/LDjE0Czx
> function WRITE_MESSAGE_TO_TABLE: https://pastebin.com/vkBkehZF
> 
> [...]
> 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:

> 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.


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.

One of your error messages above seems to suggest that this is your problem.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com