Thread: Lost connection after postgres restarted
Hi, I have the following problem with my PostgreSQL+Tomcat+JDBC: I have got a PostgreSQL server, a Tomcat, and some jsp file what connects to the postgresql via JDBC. I stop postmaster (because of file system level backup) After the backup I start postmaster But the connection between JDBC and postgresql doesn't recover any more. I need to restart Tomcat to build up the connection again. Why? Thank you in advance, Somav
On 20/01/2004 17:22 Vámos Balázs wrote: > Hi, > > I have the following problem with my PostgreSQL+Tomcat+JDBC: > > I have got a PostgreSQL server, a Tomcat, and some jsp file what connects > to > the postgresql via JDBC. > > I stop postmaster (because of file system level backup) > After the backup I start postmaster > But the connection between JDBC and postgresql doesn't recover any more. > > I need to restart Tomcat to build up the connection again. Why? IMHO, this is more a limitation of the connection pooling which doesn't seem to be able to recognise that a connection has gone bad and that it should discard it and create a new one. BTW, its not necessary to stop Tomcat as you can use its manager application to simply stop and restart your application. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Thomas, And how can I solve this connection pooling problem? Restarting the application is not good, because this process must be automatic instead of manual. somav > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Paul Thomas > Sent: Tuesday, January 20, 2004 8:00 PM > To: Vámos Balázs > Cc: pgsql-jdbc @ postgresql . org > Subject: Re: [JDBC] Lost connection after postgres restarted > > > > On 20/01/2004 17:22 Vámos Balázs wrote: > > Hi, > > > > I have the following problem with my PostgreSQL+Tomcat+JDBC: > > > > I have got a PostgreSQL server, a Tomcat, and some jsp file > what connects > > to > > the postgresql via JDBC. > > > > I stop postmaster (because of file system level backup) > > After the backup I start postmaster > > But the connection between JDBC and postgresql doesn't recover any more. > > > > I need to restart Tomcat to build up the connection again. Why? > > IMHO, this is more a limitation of the connection pooling which doesn't > seem to be able to recognise that a connection has gone bad and that it > should discard it and create a new one. BTW, its not necessary to stop > Tomcat as you can use its manager application to simply stop and restart > your application. > > -- > Paul Thomas > +------------------------------+---------------------------------- > -----------+ > | Thomas Micro Systems Limited | Software Solutions for the Smaller > Business | > | Computer Consultants | > http://www.thomas-micro-systems-ltd.co.uk | > +------------------------------+---------------------------------- > -----------+ > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > > >
Hi, Since we're implementing transparent connection pooling in C-JDBC, you can run C-JDBC on top of the Postgresql driver and you'll be able to recover your connection without starting stopping application. And since, C-JDBC is basically a cluster, you can replicate your Postgresql database on multiple backends, and when the time of backing up has come, only one of your database backend will be offline during the process, the other ones will still be available. Maybe you should have a look ... Nicolas, --- Nicolas Modrzyk Software Engineer INRIA Rhone-Alpes C-JDBC http://c-jdbc.objectweb.org On Thursday, January 22, 2004, at 09:52 AM, Vámos Balázs wrote: > Thomas, > > And how can I solve this connection pooling problem? > > Restarting the application is not good, because this process must be > automatic instead of manual. > > somav > >> -----Original Message----- >> From: pgsql-jdbc-owner@postgresql.org >> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Paul Thomas >> Sent: Tuesday, January 20, 2004 8:00 PM >> To: Vámos Balázs >> Cc: pgsql-jdbc @ postgresql . org >> Subject: Re: [JDBC] Lost connection after postgres restarted >> >> >> >> On 20/01/2004 17:22 Vámos Balázs wrote: >>> Hi, >>> >>> I have the following problem with my PostgreSQL+Tomcat+JDBC: >>> >>> I have got a PostgreSQL server, a Tomcat, and some jsp file >> what connects >>> to >>> the postgresql via JDBC. >>> >>> I stop postmaster (because of file system level backup) >>> After the backup I start postmaster >>> But the connection between JDBC and postgresql doesn't recover any >>> more. >>> >>> I need to restart Tomcat to build up the connection again. Why? >> >> IMHO, this is more a limitation of the connection pooling which >> doesn't >> seem to be able to recognise that a connection has gone bad and that >> it >> should discard it and create a new one. BTW, its not necessary to stop >> Tomcat as you can use its manager application to simply stop and >> restart >> your application. >> >> -- >> Paul Thomas >> +------------------------------+---------------------------------- >> -----------+ >> | Thomas Micro Systems Limited | Software Solutions for the Smaller >> Business | >> | Computer Consultants | >> http://www.thomas-micro-systems-ltd.co.uk | >> +------------------------------+---------------------------------- >> -----------+ >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 7: don't forget to increase your free space map settings >> >> >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
C-JDBC is a really interesting idea! I have has a look at it in the past,
and stopped when I discovered that the cluster cannot support functions
that return results eg
{? = call <procedure-name>[<arg1>,<arg2>, ...]}
Is this restriction still in place? We use functions a lot, and it is a
show stopper for us.
In fact, it is what is driving us to use an alternate replication system,
since we want our db replicated.
Thanks for any info.
John Sidney-Woollett
Nicolas Modrzyk said:
> Hi,
>
> Since we're implementing transparent connection pooling in C-JDBC, you
> can run C-JDBC on top of the Postgresql driver
> and you'll be able to recover your connection without starting stopping
> application.
> And since, C-JDBC is basically a cluster, you can replicate your
> Postgresql database on multiple backends, and when the
> time of backing up has come, only one of your database backend will be
> offline during the process, the other ones will still be available.
>
> Maybe you should have a look ...
>
>
> Nicolas,
>
>
> ---
> Nicolas Modrzyk
> Software Engineer INRIA Rhone-Alpes
> C-JDBC http://c-jdbc.objectweb.org
>
On 22/01/2004 08:52 Vámos Balázs wrote: > Thomas, > > And how can I solve this connection pooling problem? > > Restarting the application is not good, because this process must be > automatic instead of manual. Its all open-source. You could modify it to suit your needs. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Hi John,
C-JDBC does support simple stored procedures calls, but we are
wondering about control of these calls...
For example, in a heterogeneous cluster of database, do you want to
force each database to implement all of the functions ?
If a call to a store procedure fails, how do you detect it ?
If a call get multiple different return values or out parameters, how
to you react ?
Which return value do you keep ? How do you verify synchronization of
the different backends of the cluster ?
I'd be interest in putting those features in C-JDBC for a cluster of
postgresql backends since they have de facto large support for store
procedures.
For homogenous clusters, this is a short term plan, so maybe we can
work something together with your solution ?
Nicolas Modrzyk.
On Thursday, January 22, 2004, at 10:41 AM, John Sidney-Woollett wrote:
> C-JDBC is a really interesting idea! I have has a look at it in the
> past,
> and stopped when I discovered that the cluster cannot support functions
> that return results eg
>
> {? = call <procedure-name>[<arg1>,<arg2>, ...]}
>
> Is this restriction still in place? We use functions a lot, and it is a
> show stopper for us.
>
> In fact, it is what is driving us to use an alternate replication
> system,
> since we want our db replicated.
>
> Thanks for any info.
>
> John Sidney-Woollett
>
> Nicolas Modrzyk said:
>> Hi,
>>
>> Since we're implementing transparent connection pooling in C-JDBC, you
>> can run C-JDBC on top of the Postgresql driver
>> and you'll be able to recover your connection without starting
>> stopping
>> application.
>> And since, C-JDBC is basically a cluster, you can replicate your
>> Postgresql database on multiple backends, and when the
>> time of backing up has come, only one of your database backend will be
>> offline during the process, the other ones will still be available.
>>
>> Maybe you should have a look ...
>>
>>
>> Nicolas,
>>
>>
>> ---
>> Nicolas Modrzyk
>> Software Engineer INRIA Rhone-Alpes
>> C-JDBC http://c-jdbc.objectweb.org
>>
>
>
>
Hi Nicolas
We use functions a lot to abstract business logic away from our java
middle tier. We would want the stored procedures to be called in all
databases, because if this didn't happen then the databases would become
inconsistant, and we would be better off using a different replication
method.
We also use updateable resultsets which are also not supported.
I can see though that if you're just reading and writing to the DB
(cluster), that C-JDBC would be superb!
I understand your difficulty and am not sure of the best means of either
detecting a failing or failed server in the cluster, or distinguishing
between a good reply or a bad reply from all the dbs in the cluster when
there are differences.
Perhaps the C-JDBC driver can detect any difference in function results
(or out parameters (but not for postgres!)), and then throw an exception.
But what the java app does with that exception I don't know, because the
call may have legitimately worked on one of the servers...
Perhaps some other developers can give some comments, ideas or feedback.
John
Nicolas Modrzyk said:
> Hi John,
>
> C-JDBC does support simple stored procedures calls, but we are
> wondering about control of these calls...
>
> For example, in a heterogeneous cluster of database, do you want to
> force each database to implement all of the functions ?
> If a call to a store procedure fails, how do you detect it ?
> If a call get multiple different return values or out parameters, how
> to you react ?
> Which return value do you keep ? How do you verify synchronization of
> the different backends of the cluster ?
>
> I'd be interest in putting those features in C-JDBC for a cluster of
> postgresql backends since they have de facto large support for store
> procedures.
>
> For homogenous clusters, this is a short term plan, so maybe we can
> work something together with your solution ?
>
> Nicolas Modrzyk.
>
> On Thursday, January 22, 2004, at 10:41 AM, John Sidney-Woollett wrote:
>
>> C-JDBC is a really interesting idea! I have has a look at it in the
>> past,
>> and stopped when I discovered that the cluster cannot support functions
>> that return results eg
>>
>> {? = call <procedure-name>[<arg1>,<arg2>, ...]}
>>
>> Is this restriction still in place? We use functions a lot, and it is a
>> show stopper for us.
>>
>> In fact, it is what is driving us to use an alternate replication
>> system,
>> since we want our db replicated.
>>
>> Thanks for any info.
>>
>> John Sidney-Woollett
>>
>> Nicolas Modrzyk said:
>>> Hi,
>>>
>>> Since we're implementing transparent connection pooling in C-JDBC, you
>>> can run C-JDBC on top of the Postgresql driver
>>> and you'll be able to recover your connection without starting
>>> stopping
>>> application.
>>> And since, C-JDBC is basically a cluster, you can replicate your
>>> Postgresql database on multiple backends, and when the
>>> time of backing up has come, only one of your database backend will be
>>> offline during the process, the other ones will still be available.
>>>
>>> Maybe you should have a look ...
>>>
>>>
>>> Nicolas,
>>>
>>>
>>> ---
>>> Nicolas Modrzyk
>>> Software Engineer INRIA Rhone-Alpes
>>> C-JDBC http://c-jdbc.objectweb.org
>>>
>>
>>
>>
>
>