Postgres dblink stored procedure call with specifying transaction level

Question

I want to use dblink in PL/pgSQL stored procedure in such way:

PERFORM dblink_exec('myconn', 'BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE');
PERFORM dblink_exec('myconn', 'SELECT another_stored_procedure()');
PERFORM dblink_exec('myconn', 'COMMIT');

but I got an error in runtime:

ERROR:  statement returning results not allowed
CONTEXT:  SQL statement "SELECT dblink_exec('myconn', 'select another_stored_procedure()')"

so execution fails, although I tried to get the desired result in different ways.

UPDATE 1:

I know that stored procedures in postgresql are transactional. I'm using dblink for the autonomous transactions functionality to use it on the same server.

The matter is that the default level of transactions on my server is "read commited" but sometimes I need to start transactions with another level, e.g. "serializable".

So I need to execute stored procedure in autonomous transaction with explicit transaction level specifying.

And as far as I know dblink allows that, but I failed to find any useful info about dblink or dblink_exec functions which are suitable for my situation.

Have you connected with another PostgreSQL server at the other end? — pozs
– pozs, Commented Feb 3, 2015 at 13:14

Community · Accepted Answer · 2017-05-23 10:29:52Z

8

I assume, you have connected with another PostgreSQL server at the other end.

You need to call the dblink() function to execute statements, which has result(s), and not dblink_exec(). (Even if your function on the other end has returns void -- in that case, you could get a single NULL from calling that function in a SELECT.)

Also, you might not need transaction management:

Are PostgreSQL functions transactional?

In short, you need to execute:

-- PERFORM dblink_exec('myconn', 'BEGIN ...');
--    if you need explicit transaction management
PERFORM * FROM dblink('myconn', 'SELECT another_stored_procedure()') alias(col text);
-- PERFORM dblink_exec('myconn', 'COMMIT');

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Feb 3, 2015 at 13:22

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pozs Over a year ago

@FrozenHeart that doesn't matter (that's why I wrote you might not need transaction management). As I stated, you should use dblink() for the SELECT statement. Have you tried yet?

FrozenHeart Over a year ago

Thanks for aiming. Your variant failed with: ERROR: statement returning results not allowed but this works for me (my another_stored_procedure() returns void): PERFORM * FROM dblink('myconn', 'SELECT another_stored_procedure()') as t1(test text);

pozs Over a year ago

@FrozenHeart yeah, I forgot returns setof record (like dblink() returns) always need an alias, where you specify column alias/types.

Collectives™ on Stack Overflow

Postgres dblink stored procedure call with specifying transaction level

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related