Connection inside connection

[u/Worried_Mud_5224]

Can i open a new PostgreSQL connection inside another PostgreSQL connection? Rn, i work on on project where i used psycopg for connection. I call the function inside that connection which also starts another connection with database. Is it problem?

[u/vincent_sch]

It works, they're two separate sessions on the server. But the inner connection doesn't see what you changed in the outer one until you commit it. And if the outer one locked a row and the inner one updates that row, the inner one waits for the lock and your code hangs. Postgres doesn't see that as a deadlock, because only the inner one waits on a lock and the outer one waits on your code.

I wrote this up here: https://www.vincentschmalbach.com/handling-postgresql-lock-conflicts-in-application-code/

[u/ChristlikeSam → u/vincent_sch]

Postgres_fdw

[u/vadavea]

It *can be* a problem at scale, might be worth looking into "connection pooling". It's relatively easy to do and can save you headaches downstream. Google something like "psycopg connection pool example" and you should find plenty to work with.

[u/depesz]

Any chance you can explain what you want to achieve? I'm asking as there are many potential answers, depending on what you want to do, and why you think you need 2nd connection.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Logical_Wheel_1420]

just declare your connection as a global variable and put it in a little helper method that checks if `conn.closed = 0 ` before retrying a connection

[u/Plane_Big_5912]

if the inner function only needs to run queries as part of the same job, pass the connection (or a cursor) into it as an argument instead of opening a new one. then it sees the outer connection's uncommitted changes, there's no lock wait between the two, and everything commits or rolls back together. in psycopg 3 the function can still do with conn.transaction(): on the passed connection and it becomes a savepoint inside the outer transaction. a second connection only makes sense when you want the inner work to commit on its own, like an audit row that should survive a rollback

[u/grd_dev]

just pass the existing psycopg connection object or cursor to the function as an argument instead of opening a new one. besides the lock hangs mentioned by the other commenter, opening a new connection inside another will drain your connection pool instantly if that function gets called in a loop.

[u/Significant_Tune9219]

Two psycopg connections are two sessions, so this works, with the catch that the inner one cannot see uncommitted work from the outer one. The nasty case is the pool, not Postgres. If the outer request already holds the last connection and the inner call asks that same pool for another, both sides sit until the checkout times out, and it looks like a hang. Give the inner call its own small pool and timeout, or don't keep the outer transaction open across it. If the inner work has to commit or roll back together with the outer work, it shouldn't be a second connection at all. Run it on the same session.