Re: Postgresql and XA Datasource

I don't see any setSchema method in PGXADataSource:
http://jdbc.postgresql.org/development/privateapi/org/postgresql/xa/PGXADataSource.html
So it's probably not supported.

Note that it's not in the public API either:
http://java.sun.com/j2se/1.4.2/docs/api/javax/sql/XADataSource.html

Are you sure <xa-datasource-property name="Schema"> is supposed to work?

Florent

On 7 May 2008, at 10:48, Tiry wrote:

>
> Sean Radford a écrit :
>> Hi,
>>
>> I understand how to configure all the services for a particular db  
>> - have done
>> so for both Postgres and MySQL.
>>
>> However, I need to use Postgres with XA and I want Nuxeo to use a  
>> particular
>> database schema within the assigned database.
>>
>> I guess, this maybe more of a Postgres question, but thought that  
>> you guys would
>> very likely know.
>>
> We usually use a dedicated PGSQL DB and then we use the default  
> schema (public).
> Can't see anything in the PGSQL JDBC lib API to set the schema.
> May be PGSQL guys may give you a more precise answer.
>> Regards,
>>
>> Sean
>>
>>
>>> Hi Sean,
>>>
>>> XA transactions only works if your PGSQL version is 8.x. Check  
>>> your version.
>>>
>>> Moreover, you need install the JDBC driver and I think you have to
>>> configure the Relation Service and the Compass plugin. You can see  
>>> how
>>> to do this in the Nuxeo Book:
>>>
>>>
>>>
>> http://doc.nuxeo.org/5.1/nuxeo-book/reference/html/administration.html#admin-database-configuration
>>
>>> Regards.
>>>
>>>
>>> Tiry escribió:
>>>
>>>> Sean Radford a écrit :
>>>>
>>>>> Hi,
>>>>>
>>>>> How do you define the schema to use for XA datasources in Nuxeo?
>>>>>
>>>> This should not have anything to do with Nuxeo : the datasources  
>>>> are
>>>> managed by JBoss.
>>>> Tests have been done with PGSQL in XA.
>>>> I don't have a example here.
>>>> Just check JBoss doc and check your PGSQL JDBC package.
>>>>
>>>> Do you have this problem on all datasources or only on the jbpm  
>>>> db ?
>>>>
>>>>
>>>>> For example I've tried:
>>>>>
>>>>> <xa-datasource>
>>>>> <jndi-name>nxworkflowjbpm</jndi-name>
>>>>> <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa- 
>>>>> datasource-class>
>>>>> <xa-datasource-property
>>>>> name="ServerName">localhost</xa-datasource-property>
>>>>> <xa-datasource-property name="PortNumber">5432</xa-datasource- 
>>>>> property>
>>>>> <xa-datasource-property
>>>>> name="DatabaseName">MY_DB</xa-datasource-property>
>>>>> <xa-datasource-property
>>>>> name="Schema">my_schema</xa-datasource-property>
>>>>> <xa-datasource-property name="User">my_user</xa-datasource- 
>>>>> property>
>>>>> <xa-datasource-property
>>>>> name="Password">password</xa-datasource-property>
>>>>> <track-connection-by-tx/>
>>>>> </xa-datasource>
>>>>>
>>>>> But this fails with:
>>>>>
>>>>> 2008-05-06 21:53:45,117 WARN
>>>>> [org.jboss.resource.connectionmanager.JBossManagedConnectionPool]
>>>>> Throwable while attempting to get a new connection: null
>>>>> org.jboss.resource.JBossResourceException: Could not create
>>>>> connection; - nested throwable:
>>>>> (org.jboss.resource.JBossResourceException: Could not find  
>>>>> accessor
>>>>> on XADataSou
>>>>> rce: ; - nested throwable: (java.lang.NoSuchMethodException:
>>>>> org.postgresql.xa.PGXADataSource.setSchema(java.lang.String)))
>>>>>       at     
>>>>> org 
>>>>> .jboss 
>>>>> .resource 
>>>>> .adapter 
>>>>> .jdbc 
>>>>> .xa 
>>>>> .XAManagedConnectionFactory 
>>>>> .createManagedConnection(XAManagedConnectionFactory.java:164)
>>>>>       at
>>>>> org 
>>>>> .jboss 
>>>>> .resource 
>>>>> .connectionmanager 
>>>>> .InternalManagedConnectionPool 
>>>>> .createConnectionEventListener 
>>>>> (InternalManagedConnectionPool.java:565)
>>>>>       at
>>>>> org 
>>>>> .jboss 
>>>>> .resource 
>>>>> .connectionmanager 
>>>>> .InternalManagedConnectionPool 
>>>>> .getConnection(InternalManagedConnectionPool.java:250)
>>>>>
>>>>> Any ideas?
>>>>>
>>>>> Thanks in advance.
>>>>>
>>>>> Sean
>>>>>

--

-- 
Florent Guillaume, Director of R&D, Nuxeo
Open Source Enterprise Content Management (ECM)
http://www.nuxeo.com   http://www.nuxeo.org   +33 1 40 33 79 87