Thread: When the Session ends in PGSQL?

Hi!

I asked same thing in the list formerly,
http://archives.postgresql.org/pgsql-general/2011-06/msg00888.php

And because of lack of answers now I asked here also:
http://stackoverflow.com/questions/6534654/postgresql-session-timeout

I'm very wondering that I don't (and can't) found a system variable
that define the timeout of the client session. Maybe I'm a real lama,
or search with wrong terms... :-)

The manual often refers to "Session end" - when the resources
released, but never I saw a section that describe when it happens.

The RDBMS-s needs some rule to mark a Session inactive or/and dead.

Commonly they are uses a "Timeout" parameter that control how many
minutes has ellapsed to session marked as Dead.

FireBird and ElevateDB do this too. If the client is do anything, this
time period extends again.

The cleanup is needed for remove the locks, unfinished (limbo or
started) transactions, and decrease the deadlock, or lock situations;
and to never reach the maximum connections.

So: is anybody here that can tell me how this working in PGSQL?

And if there is no control, when the Session ends?

Practical example (real):
A client program died on a transaction, and it leaves many locks in
the DataBase.

If Session Timeout is 5 minutes, then we can tell the other users who
got "Row lock", or "Transaction Timeout" errors to wait 6-7 minutes
and then trying again, because after 5 minutes the problematic session
is cleaned up, and locks/transactions removed from the objects.

Thanks for your help:
   dd

On Thu, Jun 30, 2011 at 04:11:32PM +0200, Durumdara wrote:
> Hi!
>
> I asked same thing in the list formerly,
> http://archives.postgresql.org/pgsql-general/2011-06/msg00888.php

> I'm very wondering that I don't (and can't) found a system variable
> that define the timeout of the client session. Maybe I'm a real lama,
> or search with wrong terms... :-)

The session timeout is defined by the TCP timeout.  See the
tcp_keepalives options.

You can time out statements by statement_timeout.


--
Andrew Sullivan
ajs@crankycanuck.ca

In response to Durumdara <durumdara@gmail.com>:

> Hi!
>
> I asked same thing in the list formerly,
> http://archives.postgresql.org/pgsql-general/2011-06/msg00888.php
>
> And because of lack of answers now I asked here also:
> http://stackoverflow.com/questions/6534654/postgresql-session-timeout
>
> I'm very wondering that I don't (and can't) found a system variable
> that define the timeout of the client session. Maybe I'm a real lama,
> or search with wrong terms... :-)
>
> The manual often refers to "Session end" - when the resources
> released, but never I saw a section that describe when it happens.
>
> The RDBMS-s needs some rule to mark a Session inactive or/and dead.
>
> Commonly they are uses a "Timeout" parameter that control how many
> minutes has ellapsed to session marked as Dead.
>
> FireBird and ElevateDB do this too. If the client is do anything, this
> time period extends again.
>
> The cleanup is needed for remove the locks, unfinished (limbo or
> started) transactions, and decrease the deadlock, or lock situations;
> and to never reach the maximum connections.
>
> So: is anybody here that can tell me how this working in PGSQL?
>
> And if there is no control, when the Session ends?
>
> Practical example (real):
> A client program died on a transaction, and it leaves many locks in
> the DataBase.
>
> If Session Timeout is 5 minutes, then we can tell the other users who
> got "Row lock", or "Transaction Timeout" errors to wait 6-7 minutes
> and then trying again, because after 5 minutes the problematic session
> is cleaned up, and locks/transactions removed from the objects.

Session ends when the connection is terminated.  I.e., a PostgreSQL
session is synonymous with a TCP connection (or domain socket in some
cases).

There is no setting in PostgreSQL to pro-actively terminate connections.
Such an option wouldn't make any sense to include, if you ask me.  I
mean, if I open a psql and start a transaction, then get interrupted or
have to spend some time researching something else before I can finish,
the last thing I want is to come back to psql and find that my session
was terminated and my in-progress data changes lost.

For your concern about dying applications, the OS will tear down the
TCP/IP connection eventually, which will result in PG ending the
session (rolling back and uncommitted transaction), so that approach
doesn't really cause you problems there.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Hi!

2011/6/30 Bill Moran <wmoran@potentialtech.com>:
> In response to Durumdara <durumdara@gmail.com>:
>
> Session ends when the connection is terminated.  I.e., a PostgreSQL
> session is synonymous with a TCP connection (or domain socket in some
> cases).

In Windows OS what's this meaning?
I know there is a difference between Win's and Lin's TCP handling.

>
> There is no setting in PostgreSQL to pro-actively terminate connections.

Ok.

> Such an option wouldn't make any sense to include, if you ask me.

In EDB and FireBird we experienced that Timeout is good thing for
avoid the short net problems.
For example: many place they are using notebooks, and wifis.
If somebody get out the wifi area, lost the connection - but the wifi
CAN reactivate it when he/she is go again the needed place. And here
the DB service also can reactivate the Session in the background -
except the period exhaustion.

> I
> mean, if I open a psql and start a transaction, then get interrupted or
> have to spend some time researching something else before I can finish,
> the last thing I want is to come back to psql and find that my session
> was terminated and my in-progress data changes lost.

Maybe in PSQL, but not in client applications where users working, and
sometimes they are got some failures, like power, or blue screen, or
application dying.
They want to continue their's work, but when the locks/trs are
reamining they cannot do it.

>
> For your concern about dying applications, the OS will tear down the
> TCP/IP connection eventually, which will result in PG ending the
> session (rolling back and uncommitted transaction), so that approach
> doesn't really cause you problems there.

Uhhhhhh... This sounds awfully.

Is this meaning that I periodically lost my temp tables, locks,
transactions because of OS's network handling what is out of my
control?

It sounds horrible for me. When this thing happens commonly?

I must sure in my Session still alive, and to keeps my important temp
tables, locks, etc in safely place...

I will waiting for your answer.

Thanks:
    dd

In response to Durumdara <durumdara@gmail.com>:

> Hi!
>
> 2011/6/30 Bill Moran <wmoran@potentialtech.com>:
> > In response to Durumdara <durumdara@gmail.com>:
> >
> > Session ends when the connection is terminated.  I.e., a PostgreSQL
> > session is synonymous with a TCP connection (or domain socket in some
> > cases).
>
> In Windows OS what's this meaning?
> I know there is a difference between Win's and Lin's TCP handling.

There's differences between TCP handling in different distros of Linux,
but it doesn't change the simple fact that all OSes will kill dead
sockets eventually, and when the socket is closed, the PG session ends.

> > Such an option wouldn't make any sense to include, if you ask me.
>
> In EDB and FireBird we experienced that Timeout is good thing for
> avoid the short net problems.
> For example: many place they are using notebooks, and wifis.
> If somebody get out the wifi area, lost the connection - but the wifi
> CAN reactivate it when he/she is go again the needed place. And here
> the DB service also can reactivate the Session in the background -
> except the period exhaustion.

All of that can be done with the correct settings at the TCP level as
well.

> > I
> > mean, if I open a psql and start a transaction, then get interrupted or
> > have to spend some time researching something else before I can finish,
> > the last thing I want is to come back to psql and find that my session
> > was terminated and my in-progress data changes lost.
>
> Maybe in PSQL, but not in client applications where users working, and
> sometimes they are got some failures, like power, or blue screen, or
> application dying.
> They want to continue their's work, but when the locks/trs are
> reamining they cannot do it.

Incorrect.  If a Windows client bluescreens, the server will eventually
notice that the socket is dead and clean it up.  If that's taking too
long, then you need to review the TCP settings on your server.

However, it sounds to me like your application is poorly designed.  If
it's being used via unreliable connections, but requires that database
locks be held for any length of time, you've got two warring design
requirements, and one or the other is always going to suffer.

> > For your concern about dying applications, the OS will tear down the
> > TCP/IP connection eventually, which will result in PG ending the
> > session (rolling back and uncommitted transaction), so that approach
> > doesn't really cause you problems there.
>
> Uhhhhhh... This sounds awfully.
>
> Is this meaning that I periodically lost my temp tables, locks,
> transactions because of OS's network handling what is out of my
> control?
>
> It sounds horrible for me. When this thing happens commonly?

You are the first person I've had complain that this is a common
scenario with database applications.  It sounds like your application
was not designed properly to take into account the various demands of
the environment.

Keeping things moving along happily in a situation where disconnects
are frequent and unpredictable is damn difficult.  If you were to add
a "session timeout" variable, you'd find that you haven't fixed the
problem, only caused it to manifest in different ways.  Additionally,
that's not a problem that it's in PostgreSQL's best interest to try to
solve, as it's really closer to the application level (from a requirement
perspective).

> I must sure in my Session still alive, and to keeps my important temp
> tables, locks, etc in safely place...

If "temp tables" are "important", then you're doing it wrong.  Using
temp tables for any data that is not completely sacrificial is a poor
design decision that is liable to haunt you in many ways.  It destroys
your ability to use connection pooling, replication, failover, etc in
addition to the problem you are currently complaining about.

Locks are the same way, keeping DB locks in place for extended periods
is almost always a bad idea, and not really the purpose of RDBMS-
level locking.  Look into implementing advisory locking at the application
level.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Dear Bill!

I don't want to raise flame - I only reflect.

As I see we misunderstand ourselves, and I want to correct this
"question", and show my viewpoint.

Preface:

1.) DataBases need to close the resources kept by Sessions.
2.) There is need some "border", or a rule to split - which Session is
considered as "finished".

The FireBird is uses "DummyPacketInterval" to recognize dead sessions,
EDB uses "Session Timeout" value for it.
The EDB can still alive if network connection lost, if before Timeout
the client also send a sign to the server.

To I can use PGSQL also, I need to know the limitations, and how to
handle the connections, how to manage them.

I wondering to PGSQL handle this with living TCP connections, because
this is may out of our control.
If we have a timeout value, we can control, which time we have, and
what time is acceptable for a Session.
For example: some places we have that uses wifi connections are
sometimes broken for just a little period. This is enough to
disconnect, but because of higher "Session Timeout" variable our
DataBase connections still alive without close the applications.

Another thing is sign (packet). We must do something periodically to
keep alive the connection. For example: every 1 minutes we do some
dummy thing one server, like "select date" or etc.

The reflections:

2011/6/30 Bill Moran <wmoran@potentialtech.com>:
> In response to Durumdara <durumdara@gmail.com>:
>
>> Hi!
>>
>> 2011/6/30 Bill Moran <wmoran@potentialtech.com>:
>> > In response to Durumdara <durumdara@gmail.com>:
>> >
>> > Session ends when the connection is terminated.  I.e., a PostgreSQL
>> > session is synonymous with a TCP connection (or domain socket in some
>> > cases).
>>
>> In Windows OS what's this meaning?
>> I know there is a difference between Win's and Lin's TCP handling.
>
> There's differences between TCP handling in different distros of Linux,
> but it doesn't change the simple fact that all OSes will kill dead
> sockets eventually, and when the socket is closed, the PG session ends.

Ok, I understand it. Thanks.
But 2 questions are remaining:
1.) how can I control the OS's TCP/IP to make more timeout?
2.) how can force PG to change some parameter, to make bigger keep
alive timeouts? (tcp_keepalives_*?)

>
>> > Such an option wouldn't make any sense to include, if you ask me.
>>
>> In EDB and FireBird we experienced that Timeout is good thing for
>> avoid the short net problems.
>> For example: many place they are using notebooks, and wifis.
>> If somebody get out the wifi area, lost the connection - but the wifi
>> CAN reactivate it when he/she is go again the needed place. And here
>> the DB service also can reactivate the Session in the background -
>> except the period exhaustion.
>
> All of that can be done with the correct settings at the TCP level as
> well.

Can you show me an example, or some descriptions/links/documents about
it for "RTFM" operation? :-)

>
>> > I
>> > mean, if I open a psql and start a transaction, then get interrupted or
>> > have to spend some time researching something else before I can finish,
>> > the last thing I want is to come back to psql and find that my session
>> > was terminated and my in-progress data changes lost.
>>
>> Maybe in PSQL, but not in client applications where users working, and
>> sometimes they are got some failures, like power, or blue screen, or
>> application dying.
>> They want to continue their's work, but when the locks/trs are
>> reamining they cannot do it.
>
> Incorrect.  If a Windows client bluescreens, the server will eventually
> notice that the socket is dead and clean it up.  If that's taking too
> long, then you need to review the TCP settings on your server.

If pgsql connection is socket based then this is true.

>
> However, it sounds to me like your application is poorly designed.

Not. It is only uses the database supported connection model which is
not same as in PGSQL.

>If
> it's being used via unreliable connections, but requires that database
> locks be held for any length of time, you've got two warring design
> requirements, and one or the other is always going to suffer.

We don't have long perioded locks, transactions, but we had some
situations when the locks remained in unreleased state because of
client freezes.
Then we must waiting for the timeout period.

>
>> > For your concern about dying applications, the OS will tear down the
>> > TCP/IP connection eventually, which will result in PG ending the
>> > session (rolling back and uncommitted transaction), so that approach
>> > doesn't really cause you problems there.
>>
>> Uhhhhhh... This sounds awfully.
>>
>> Is this meaning that I periodically lost my temp tables, locks,
>> transactions because of OS's network handling what is out of my
>> control?
>>
>> It sounds horrible for me. When this thing happens commonly?
>
> You are the first person I've had complain that this is a common
> scenario with database applications.

Always have a "FIRST TIME", and a "FIRST PERSON"... :-)

But this is not common scenario, because many DB-s support reconnect
on TCP connection lost.


>  It sounds like your application
> was not designed properly to take into account the various demands of
> the environment.

More than 150 places we can't have homogenous environment.
So we are very dependent on customer.

>
> Keeping things moving along happily in a situation where disconnects
> are frequent and unpredictable is damn difficult.  If you were to add
> a "session timeout" variable, you'd find that you haven't fixed the
> problem, only caused it to manifest in different ways.

I understand it, but what you can do in "wifi-maniac"
multi-corporation customer where only Timeout can help on keep alive
the DB connection?

>  Additionally,
> that's not a problem that it's in PostgreSQL's best interest to try to
> solve, as it's really closer to the application level (from a requirement
> perspective).

I think it's not. The programs are working with many database objects
which are lost on database disconnection. The only way to construct
them all to restart the application.

>
>> I must sure in my Session still alive, and to keeps my important temp
>> tables, locks, etc in safely place...
>
> If "temp tables" are "important", then you're doing it wrong.

No. It's all is in place.
The applcation starts, and connect, then it is build many temp tables,
and other database objects to hold the important data for the session.
For only this session.
On disconnection these infos lost - so we can only restart the client app.

>Using
> temp tables for any data that is not completely sacrificial is a poor
> design decision that is liable to haunt you in many ways.

I don't know how to do it without temp tables.

I can do only if I move all data to the client which is very slow, and
painful operation.
And do this periodically is very slowing down the software.

>  It destroys
> your ability to use connection pooling, replication, failover, etc in
> addition to the problem you are currently complaining about.

I don't think. Connection pooling must prepared to drop all Session
specific thing, like transaction states, temp tables, etc, and start
"as a new connection".
Without this the pooled sessions can add more and more objects, can
lock other and other resources, and can exhausting the system
resources.
Connection Pool must reset the session.

>
> Locks are the same way, keeping DB locks in place for extended periods
> is almost always a bad idea,

We don't keep them if not possible.
But as you know, the "Devil never sleeps" so you can write buggy code
without "Close the transaction" section and then very fast we can get
problems, if the DB server not is doing housekeeping on Sessions.

> and not really the purpose of RDBMS-
> level locking.  Look into implementing advisory locking at the application
> level.

Yes, advisory locks are good for use mutexes over the transactions.
But what a pity they are not uses user defined strings...

Adv_Lock(What)

Adv_Lock('emp_table_record: 118')

It would be better than two integers.

Thanks for your read, and for your help too:
    dd

On Monday 04 July 2011 10:48:48 Durumdara wrote:
> 1.) DataBases need to close the resources kept by Sessions.
> 2.) There is need some "border", or a rule to split - which Session is
> considered as "finished".

So far so good.

> The FireBird is uses "DummyPacketInterval" to recognize dead sessions,
> EDB uses "Session Timeout" value for it.
> The EDB can still alive if network connection lost, if before Timeout
> the client also send a sign to the server.
>
> To I can use PGSQL also, I need to know the limitations, and how to
> handle the connections, how to manage them.
>
> I wondering to PGSQL handle this with living TCP connections, because
> this is may out of our control.
> If we have a timeout value, we can control, which time we have, and
> what time is acceptable for a Session.
> For example: some places we have that uses wifi connections are
> sometimes broken for just a little period. This is enough to
> disconnect, but because of higher "Session Timeout" variable our
> DataBase connections still alive without close the applications.
>
> Another thing is sign (packet). We must do something periodically to
> keep alive the connection. For example: every 1 minutes we do some
> dummy thing one server, like "select date" or etc.

AFAIK postgres doesn't distinguish between a "TCP session" and a "database
session" like (if I understand you correctly) FireBird/EDB does. You cannot
reconnect and say "hello it's me again from session FOOBAR, can I resume that
session ?". I believe you'll have to solve this at the application layer :

* Make transactions, locks, temp tables, etc as short-lived as possible (this
   is always a good thing to do anyway).
* If that's still not enough, store your "current working state" in a purpose-
   built table and add logic in your client to reinitialize session state
   using that data, and to purge the data after it has been used / timed out.

Another thing you could do (but I'm not sure it is a good idea) is to write a
proxy application that runs on the server machine and understands your session
requirements. Then connect your application to this proxy instead of the
database.

--
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.dephily@mobile-devices.fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.

Hi!

2011/7/4 Vincent de Phily <vincent.dephily@mobile-devices.fr>:
> On Monday 04 July 2011 10:48:48 Durumdara wrote:
>> 1.) DataBases need to close the resources kept by Sessions.
>> 2.) There is need some "border", or a rule to split - which Session is
>> considered as "finished".
>
> So far so good.

...so what! (Megadeth).

:-)

>> Another thing is sign (packet). We must do something periodically to
>> keep alive the connection. For example: every 1 minutes we do some
>> dummy thing one server, like "select date" or etc.
>
> AFAIK postgres doesn't distinguish between a "TCP session" and a "database
> session" like (if I understand you correctly) FireBird/EDB does. You cannot
> reconnect and say "hello it's me again from session FOOBAR, can I resume that
> session ?". I believe you'll have to solve this at the application layer :
>
> * Make transactions, locks, temp tables, etc as short-lived as possible (this
>   is always a good thing to do anyway).
> * If that's still not enough, store your "current working state" in a purpose-
>   built table and add logic in your client to reinitialize session state
>   using that data, and to purge the data after it has been used / timed out.
>
> Another thing you could do (but I'm not sure it is a good idea) is to write a
> proxy application that runs on the server machine and understands your session
> requirements. Then connect your application to this proxy instead of the
> database.

Thanks for your information.

And then I ask the question that is remaining hidden in prev. mail:

What happens with running statements and stored procs at Session's end?

They will be aborted and destroyed, or they can continue the running?

For example:
User X starts a long Query/STP.
But he is lost "in deep space" (like in "Space Oddity":
http://www.youtube.com/watch?v=rKE3FSPJu-4&feature=related).
The TCP connection aborted, then the Session is set to dead.
But I don't know what happens with this Query? Is it aborted by Server
or Running may infinitively?

Thanks:
   dd

On Monday 04 July 2011 12:49:24 Durumdara wrote:
> What happens with running statements and stored procs at Session's end?
>
> They will be aborted and destroyed, or they can continue the running?
>
> For example:
> User X starts a long Query/STP.
> But he is lost "in deep space" (like in "Space Oddity":
> http://www.youtube.com/watch?v=rKE3FSPJu-4&feature=related).
> The TCP connection aborted, then the Session is set to dead.
> But I don't know what happens with this Query? Is it aborted by Server
> or Running may infinitively?

The running query will be rolled back when the session ends. Note that "when
the session ends" must be understood from the server's point of view, not the
client's.

Temporary tables, prepared statements, and cursors (not an exhaustive list)
will be destroyed too.

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.
--
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.dephily@mobile-devices.fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.

Ahoj!

2011/7/4 Vincent de Phily <vincent.dephily@mobile-devices.fr>:
> On Monday 04 July 2011 12:49:24 Durumdara wrote:
>> What happens with running statements and stored procs at Session's end?
>>
>> They will be aborted and destroyed, or they can continue the running?
>>
>> For example:
>> User X starts a long Query/STP.
>> But he is lost "in deep space" (like in "Space Oddity":
>> http://www.youtube.com/watch?v=rKE3FSPJu-4&feature=related).
>> The TCP connection aborted, then the Session is set to dead.
>> But I don't know what happens with this Query? Is it aborted by Server
>> or Running may infinitively?
>
> The running query will be rolled back when the session ends. Note that "when
> the session ends" must be understood from the server's point of view, not the
> client's.

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

>
> Temporary tables, prepared statements, and cursors (not an exhaustive list)
> will be destroyed too.
>
> 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).
Or suggest me some info, if possible... :-) (Prayer) :-)
I want to know about differences.

Thanks for it!
    dd

On Monday 04 July 2011 13:50:46 Durumdara wrote:
> > The running query will be rolled back when the session ends. Note that
> > "when the session ends" must be understood from the server's point of
> > view, not the client's.
>
> 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).

Yes.

> > 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).
> Or suggest me some info, if possible... :-) (Prayer) :-)
> I want to know about differences.

Sorry it's a vast subject, and what you would be surprised by depends on what
you expect. Start here :
http://www.postgresql.org/docs/9.0/static/xfunc.html
The postgres docs are well written; read them at your leisure like a book,
follow the links where curiosity leads you, it can actually be entertaining :)

--
Vincent de Phily

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

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

--
Craig Ringer

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

Hi!

2011/7/6 Durumdara <durumdara@gmail.com>:
>> 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?

Interesting:

CREATE OR REPLACE FUNCTION a()
  RETURNS integer AS
$BODY$BEGIN
   LOOP
    -- x
   END LOOP;
   return 1;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

select a();

Then the server cannot abort my "query".
Only statement limitation (timeout) helps in this situation.

But I'm not sure what's this timeout meaning?
What is the "statement" what measured?

The main statement (which is visible as last Query in monitor)?
Or substatements also measured one by one, no matter the main length?

For example I have a complex stored procedure that call subqueries, to
provide some records to main query.
The timeout is affected on Total length of main Query, or resetted on
each subselects (statements) I do in my STP?

Thanks:
    dd