Thread: XA Support for Postgres

XA Support for Postgres

From
Håkan Jacobsson
Date:

Hi,

 

I’ve posted earlier about this. I had problems connecting to the two postgresql databases our company system uses when migrating to JBoss 4.2.2.GA .

Lots of exceptions about ‘Generic JDBC connection can not be created’ (something like that).

I got a solution from a guy here at the mailing list. By using a JBoss database configuration file (postgres-ds.xml)  with ‘XA-support’ we got rid of the exceptions.

I’ve tried to search google to understand what this ‘XA-support’ really does, but I haven’t  found that much (other than an old thread ‘XA-support for Postgres) dating back to 2005.

Can someone point me to some good documentation for the XA-support for Postgres?

 

 

Håkan Jacobsson - System Developer

----------------------------------------------------------------

 

RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden

 

Mobile (+46) 736 56 97 58

Direct   (+46) 8 56 24 98 05

Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22

 

An I/O error occured while sending to the backend.

From
Stef
Date:
Hi all,


I'm using jboss 4.0.2 and j2sdk version 1.5.0_11-b03
connecting to postgresql 8.1.4 using the jdbc3 postgres
driver.

I'm using the Connection pool manager of JBoss to control
the connections to the database. When I restart postgres
without restarting JBoss as well, I get this error reported in JBoss :
An I/O error occured while sending to the backend.

The problem is that JBoss never recovers from this , and it's only fixed
when I restart JBoss. I can understand that this error may happen
occasionally, but I need to be able to recover from this situation
without restarting JBoss.

Does anyone have any idea what I can do?

Kind Regards
Stefan

Re: XA Support for Postgres

From
Heikki Linnakangas
Date:
Håkan Jacobsson wrote:
> Hi,
>
> I've posted earlier about this. I had problems connecting to the two postgresql databases our company system uses
whenmigrating to JBoss 4.2.2.GA . 
> Lots of exceptions about 'Generic JDBC connection can not be created' (something like that).
> I got a solution from a guy here at the mailing list. By using a JBoss database configuration file (postgres-ds.xml)
with'XA-support' we got rid of the exceptions. 
> I've tried to search google to understand what this 'XA-support' really does, but I haven't  found that much (other
thanan old thread 'XA-support for Postgres) dating back to 2005. 
> Can someone point me to some good documentation for the XA-support for Postgres?

X/Open XA is a standard interface for performing two-phase commit across
different resource managers, like databases.

In Java-world, XA is mapped to the Java Transaction API (JTA). XA
support basically means that the driver comes with an implementation of
the javax.sql.XADataSource interface.

You need two-phase commit if you want to update two databases in a
single atomic transaction.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: XA Support for Postgres

From
Håkan Jacobsson
Date:
Heikki,

Thanx for your answer.
So, if I want XA-support I need postgresql 8.1 at least on the databases I need to use?

I'm using a postgres-ds.xml with XA-support (JBoss config file) which I think you suggested to use (I found it on some
jira-relatedsite). Is that all I need to have XA-support/two-phase-commit-support? I mean there is nothing to modify in
mycode or elsewhere? 

Håkan Jacobsson - System Developer
----------------------------------------------------------------

RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden

Mobile (+46) 736 56 97 58
Direct   (+46) 8 56 24 98 05
Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22


-----Ursprungligt meddelande-----
Från: Heikki Linnakangas [mailto:hlinnaka@gmail.com] För Heikki Linnakangas
Skickat: den 7 januari 2008 17:06
Till: Håkan Jacobsson
Kopia: pgsql-jdbc@postgresql.org
Ämne: Re: [JDBC] XA Support for Postgres

Håkan Jacobsson wrote:
> Hi,
>
> I've posted earlier about this. I had problems connecting to the two postgresql databases our company system uses
whenmigrating to JBoss 4.2.2.GA . 
> Lots of exceptions about 'Generic JDBC connection can not be created' (something like that).
> I got a solution from a guy here at the mailing list. By using a JBoss database configuration file (postgres-ds.xml)
with'XA-support' we got rid of the exceptions. 
> I've tried to search google to understand what this 'XA-support' really does, but I haven't  found that much (other
thanan old thread 'XA-support for Postgres) dating back to 2005. 
> Can someone point me to some good documentation for the XA-support for Postgres?

X/Open XA is a standard interface for performing two-phase commit across
different resource managers, like databases.

In Java-world, XA is mapped to the Java Transaction API (JTA). XA
support basically means that the driver comes with an implementation of
the javax.sql.XADataSource interface.

You need two-phase commit if you want to update two databases in a
single atomic transaction.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: XA Support for Postgres

From
Heikki Linnakangas
Date:
Håkan Jacobsson wrote:
> So, if I want XA-support I need postgresql 8.1 at least on the databases I need to use?

Yep.

> I'm using a postgres-ds.xml with XA-support (JBoss config file) which I think you suggested to use (I found it on
somejira-related site). Is that all I need to have XA-support/two-phase-commit-support? I mean there is nothing to
modifyin my code or elsewhere? 

The short answer is yes, that's all you need to do.

Note that as an important optimization, the app server won't actually do
two-phase commit unless you really use two different databases in a
transaction.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: An I/O error occured while sending to the backend.

From
Altaf Malik
Date:
This is because when your database is down, all the 'connected' instances of Connection become invalid. Since pooled connection is created once, it should be restarted on each database restart. This is possible either by restarting the JBoss, or restarting the Datasource in jboss by the using JMX console.
You'll have to do something. It'll not re-connect automatically.
 
Regards,
Altaf Malik
EnterpriseDB
 

Stef <svb@ucs.co.za> wrote:
Hi all,


I'm using jboss 4.0.2 and j2sdk version 1.5.0_11-b03
connecting to postgresql 8.1.4 using the jdbc3 postgres
driver.

I'm using the Connection pool manager of JBoss to control
the connections to the database. When I restart postgres
without restarting JBoss as well, I get this error reported in JBoss :
An I/O error occured while sending to the backend.

The problem is that JBoss never recovers from this , and it's only fixed
when I restart JBoss. I can understand that this error may happen
occasionally, but I need to be able to recover from this situation
without restarting JBoss.

Does anyone have any idea what I can do?

Kind Regards
Stefan

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


Never miss a thing. Make Yahoo your homepage.

Re: XA Support for Postgres

From
Håkan Jacobsson
Date:
Heikki,

Thanx a lot for your help!

Håkan Jacobsson - System Developer
----------------------------------------------------------------

RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden

Mobile (+46) 736 56 97 58
Direct   (+46) 8 56 24 98 05
Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22


-----Ursprungligt meddelande-----
Från: Heikki Linnakangas [mailto:hlinnaka@gmail.com] För Heikki Linnakangas
Skickat: den 7 januari 2008 17:40
Till: Håkan Jacobsson
Kopia: pgsql-jdbc@postgresql.org
Ämne: Re: SV: [JDBC] XA Support for Postgres

Håkan Jacobsson wrote:
> So, if I want XA-support I need postgresql 8.1 at least on the databases I need to use?

Yep.

> I'm using a postgres-ds.xml with XA-support (JBoss config file) which I think you suggested to use (I found it on
somejira-related site). Is that all I need to have XA-support/two-phase-commit-support? I mean there is nothing to
modifyin my code or elsewhere? 

The short answer is yes, that's all you need to do.

Note that as an important optimization, the app server won't actually do
two-phase commit unless you really use two different databases in a
transaction.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: An I/O error occured while sending to the backend.

From
Kris Jurka
Date:

On Mon, 7 Jan 2008, Stef wrote:

> I'm using the Connection pool manager of JBoss to control
> the connections to the database. When I restart postgres
> without restarting JBoss as well, I get this error reported in JBoss :
> An I/O error occured while sending to the backend.
>
> The problem is that JBoss never recovers from this , and it's only fixed
> when I restart JBoss. I can understand that this error may happen
> occasionally, but I need to be able to recover from this situation
> without restarting JBoss.
>

JBoss has a check-valid-connection-sql parameter for datasources which
runs every time a connection from a pool is allocated.  If this statement
fails, the connection is discarded and a new connection is opened.  This
should allow recovery from a disappeared database.

Kris Jurka