Re: When the Session ends in PGSQL? - Mailing list pgsql-general

Hi!

2011/7/4 Craig Ringer <craig@postnewspapers.com.au>:
> On 4/07/2011 7:50 PM, Durumdara wrote:
>
>> As I understand you then running Queries forced to abort on the end of
>> Session and no more Query running over the session's ending point (or
>> after TCP connection broken).
>
> Correct. The server might not notice that the client broke it's connection
> for a while, though, especially if there's along tcp timeout, no keepalives
> are enabled, and the server isn't actively sending data to the client.
>
> This makes me wonder, though: If a client sends a COMMIT message to the
> server, and the server replies to the client to confirm the commit but the
> client has vanished, is the data committed? How does the client find out?

Good question.

> I'd assume it'd still be committed, because if the server had to wait for
> the client to acknowledge there would be issues with delaying other commits.
> The trouble is, though, that if a client sends a COMMIT then loses contact
> with the server it doesn't know for sure if the commit really happened. It
> can't reconnect to its old session as it's been destroyed. Is there any way
> it can ask the server "did my old xid commit successfully' if it recorded
> the xid of the transaction it lost contact with during COMMIT?
>
> Is there any way to have both server and client always know, for certain,
> whether a commit has occurred without using 2PC?

Interesting question. In other RDBMS-s the commits got before Session
Timeout finish, but if you got net problem, you never know, what
finished in background.
Stored Procedures will continue work after TCP lost, and if they
supports "COMMIT", they can do persistent modifications.

The question is very same as TWO PHASE COMMIT: when we defined some
action as closed (acknowledgement).

>
>>> Stored procedures will remain. Note that "stored procedures" in postgres
>>> are a
>>> bit different from what you may be used to in other dbs; while I assure
>>> you
>>> it's for the better, you might want to RTFM to avoid surprises.
>>
>> Please explain a little this (Just 1-2 sentence please).
>
> PostgreSQL doesn't have true stored procedures at all. It only has
> user-defined functions that can be called from a SELECT/INSERT/UPDATE/DELETE
> statement.
>
> Most importantly, PostgreSQL's "stored procedures" cannot control
> transactions. They cannot commit, rollback, or begin a new transaction. They
> have some control over subtransactions using PL/PgSQL exceptions, but that's
> about it.

So: I tried it, I created a LOOP/END LOOP infinite procedure, and
after started with pgAdmin, I killed the pgAdmin.

8 minutes passed, but server process don't stop this procedure yet.
Have the process some "limit" on running? When the server kill this process?
Never because of "working state"?

How to abort it without abort another sessions, or kill the server?

Thanks:
    dd