Excellent news! I was worried that PostgreSQL was just not compatible with Teiid.
To be honest I assumed that Teiid used interlacing as we were able to successfully use MySQL XA data sources and the only difference I could see was that the PostgreSQL JDBC driver specifically mentions that it doesn't do interlacing.
I added <track-connection-by-tx/> to my standalone.xml file but I got an "Unexpected element: track-connection-by-tx" error from my jboss-eap-6.1 server.
Here is how I've got my PostgreSQL XA data sources defined in standalone.xml:
<xa-datasource jndi-name="java:/EmployeePeopleXA" pool-name="EmployeePeopleXA" enabled="true" use-ccm="false">
<xa-datasource-property name="DatabaseName">
EmployeePeople
</xa-datasource-property>
<xa-datasource-property name="ServerName">
localhost:5432
</xa-datasource-property>
<xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
<driver>postgresql-9.3-1101.jdbc41.jar</driver>
<xa-pool>
<is-same-rm-override>false</is-same-rm-override>
<interleaving>false</interleaving>
<pad-xid>false</pad-xid>
<wrap-xa-resource>false</wrap-xa-resource>
<track-connection-by-tx/>
</xa-pool>
<security>
<user-name>user</user-name>
<password>password</password>
</security>
<validation>
<validate-on-match>false</validate-on-match>
<background-validation>false</background-validation>
</validation>
<statement>
<share-prepared-statements>false</share-prepared-statements>
</statement>
</xa-datasource>
<xa-datasource jndi-name="java:/EmployeePeopleXA2" pool-name="EmployeePeopleXA2" enabled="true" use-ccm="false">
<xa-datasource-property name="DatabaseName">
EmployeePeople2
</xa-datasource-property>
<xa-datasource-property name="ServerName">
localhost:5432
</xa-datasource-property>
<xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
<driver>postgresql-9.3-1101.jdbc41.jar</driver>
<xa-pool>
<is-same-rm-override>false</is-same-rm-override>
<interleaving>false</interleaving>
<pad-xid>false</pad-xid>
<wrap-xa-resource>false</wrap-xa-resource>
<track-connection-by-tx/>
</xa-pool>
<security>
<user-name>user</user-name>
<password>password</password>
</security>
<validation>
<validate-on-match>false</validate-on-match>
<background-validation>false</background-validation>
</validation>
<statement>
<share-prepared-statements>false</share-prepared-statements>
</statement>
</xa-datasource>
And this is the console output from the server on startup:
08:58:16,778 INFO [org.jboss.modules] (main) JBoss Modules version 1.2.2.Final-redhat-1
08:58:16,969 INFO [org.jboss.msc] (main) JBoss MSC version 1.0.4.GA-redhat-1
08:58:17,030 INFO [org.jboss.as] (MSC service thread 1-7) JBAS015899: JBoss EAP 6.1.1.GA (AS 7.2.1.Final-redhat-10) starting
08:58:17,611 ERROR [org.jboss.as.server] (Controller Boot Thread) JBAS015956: Caught exception during boot: org.jboss.as.controller.persistence.ConfigurationPersistenceException: JBAS014676: Failed to parse configuration
at org.jboss.as.controller.persistence.XmlConfigurationPersister.load(XmlConfigurationPersister.java:141) [jboss-as-controller-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]
at org.jboss.as.server.ServerService.boot(ServerService.java:308) [jboss-as-server-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]
at org.jboss.as.controller.AbstractControllerService$1.run(AbstractControllerService.java:188) [jboss-as-controller-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_55]
Caused by: javax.xml.stream.XMLStreamException: org.jboss.as.connector.util.ParserException: IJ010061: Unexpected element: track-connection-by-tx
at org.jboss.as.connector.subsystems.datasources.DataSourcesExtension$DataSourceSubsystemParser.readElement(DataSourcesExtension.java:577)
at org.jboss.as.connector.subsystems.datasources.DataSourcesExtension$DataSourceSubsystemParser.readElement(DataSourcesExtension.java:182)
at org.jboss.staxmapper.XMLMapperImpl.processNested(XMLMapperImpl.java:110) [staxmapper-1.1.0.Final-redhat-2.jar:1.1.0.Final-redhat-2]
at org.jboss.staxmapper.XMLExtendedStreamReaderImpl.handleAny(XMLExtendedStreamReaderImpl.java:69) [staxmapper-1.1.0.Final-redhat-2.jar:1.1.0.Final-redhat-2]
at org.jboss.as.server.parsing.StandaloneXml.parseServerProfile(StandaloneXml.java:1028) [jboss-as-server-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]
at org.jboss.as.server.parsing.StandaloneXml.readServerElement_1_4(StandaloneXml.java:449) [jboss-as-server-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]
at org.jboss.as.server.parsing.StandaloneXml.readElement(StandaloneXml.java:136) [jboss-as-server-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]
at org.jboss.as.server.parsing.StandaloneXml.readElement(StandaloneXml.java:103) [jboss-as-server-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]
at org.jboss.staxmapper.XMLMapperImpl.processNested(XMLMapperImpl.java:110) [staxmapper-1.1.0.Final-redhat-2.jar:1.1.0.Final-redhat-2]
at org.jboss.staxmapper.XMLMapperImpl.parseDocument(XMLMapperImpl.java:69) [staxmapper-1.1.0.Final-redhat-2.jar:1.1.0.Final-redhat-2]
at org.jboss.as.controller.persistence.XmlConfigurationPersister.load(XmlConfigurationPersister.java:133) [jboss-as-controller-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]
... 3 more
Caused by: org.jboss.as.connector.util.ParserException: IJ010061: Unexpected element: track-connection-by-tx
at org.jboss.as.connector.subsystems.datasources.DsParser.parseXaPool(DsParser.java:868)
at org.jboss.as.connector.subsystems.datasources.DsParser.parseXADataSource(DsParser.java:437)
at org.jboss.as.connector.subsystems.datasources.DsParser.parseDataSources(DsParser.java:200)
at org.jboss.as.connector.subsystems.datasources.DsParser.parse(DsParser.java:167)
at org.jboss.as.connector.subsystems.datasources.DataSourcesExtension$DataSourceSubsystemParser.readElement(DataSourcesExtension.java:568)
... 13 more
08:58:17,617 FATAL [org.jboss.as.server] (Controller Boot Thread) JBAS015957: Server boot has failed in an unrecoverable manner; exiting. See previous messages for details.
08:58:17,635 INFO [org.jboss.as] (MSC service thread 1-2) JBAS015950: JBoss EAP 6.1.1.GA (AS 7.2.1.Final-redhat-10) stopped in 12ms
If I take <track-connection-by-tx/> out of the settings I can start it up and create a VDB using the XA connections.
I have a view model with an insert procedure that looks like this:
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO People_view.People (People_view.People.person_id, People_view.People.title, People_view.People.forename, People_view.People.surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname);
INSERT INTO Employee_view.Employees (Employee_view.Employees.employee_id, Employee_view.Employees.person_id, Employee_view.Employees.job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title);
END
But when I attempt to use it on a deployed VDB I get an error
Insert Statement:
INSERT INTO EmployeePeople(employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson');
SQL Result Error:
TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 Employees: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@66dcff09[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@17322ce4 connection handles=0 lastUse=1401783699022 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5f2138a1 pool internal context=SemaphoreArrayListManagedConnectionPool@3ae74c5b[pool=Employees] xaResource=LocalXAResourceImpl@36ebdca1[connectionListener=66dcff09 connectionManager=36e1ea32 warned=false currentXid=null productName=PostgreSQL productVersion=9.1.13 jndiName=java:/Employees] txSync=null]
Server Console:
09:21:39,016 INFO [org.teiid.PLANNER] (Worker0_QueryProcessorQueue1)
============================================================================
USER COMMAND:
INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson')
----------------------------------------------------------------------------
OPTIMIZE:
INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson')
----------------------------------------------------------------------------
GENERATE CANONICAL:
INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson')
----------------------------------------------------------------------------
OPTIMIZE:
SELECT 123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'
----------------------------------------------------------------------------
GENERATE CANONICAL:
SELECT 123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'
CANONICAL PLAN:
Project(groups=[], props={PROJECT_COLS=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']})
============================================================================
EXECUTING PlaceAccess
AFTER:
Project(groups=[], props={PROJECT_COLS=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']})
============================================================================
EXECUTING RaiseAccess
AFTER:
Project(groups=[])
============================================================================
EXECUTING AssignOutputElements
AFTER:
Project(groups=[], props={PROJECT_COLS=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'], OUTPUT_COLS=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']})
============================================================================
EXECUTING CalculateCost
AFTER:
Project(groups=[], props={PROJECT_COLS=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'], OUTPUT_COLS=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'], EST_CARDINALITY=1.0, EST_COL_STATS={123=[1.0, 0.0], 456=[1.0, 0.0], 'Software Engineer'=[1.0, 0.0], 'Mr'=[1.0, 0.0], ...}})
============================================================================
EXECUTING PlanSorts
AFTER:
Project(groups=[])
============================================================================
EXECUTING CollapseSource
AFTER:
Project(groups=[])
============================================================================
CONVERTING PLAN TREE TO PROCESS TREE
PROCESS PLAN =
ProjectNode(0) output=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'] [123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']
============================================================================
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectNode(0) output=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'] [123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']
============================================================================
----------------------------------------------------------------------------
OPTIMIZE:
CREATE VIRTUAL PROCEDURE
BEGIN ATOMIC
INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname);
INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title);
END
####################################################
PROCEDURE COMMAND: CREATE VIRTUAL PROCEDURE
BEGIN ATOMIC
INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname);
INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title);
END
OPTIMIZING SUB-COMMANDS:
----------------------------------------------------------------------------
OPTIMIZE:
INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname)
----------------------------------------------------------------------------
GENERATE CANONICAL:
INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname)
CANONICAL PLAN:
Project(groups=[], props={PROJECT_COLS=[Count]})
Source(groups=[Employees2.People], props={ATOMIC_REQUEST=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname), VIRTUAL_COMMAND=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname)})
============================================================================
EXECUTING PlaceAccess
AFTER:
Project(groups=[], props={PROJECT_COLS=[Count]})
Access(groups=[Employees2.People], props={MODEL_ID=Schema name=Employees2, nameInSource=null, uuid=mmuuid:5d465eb1-b546-4f4f-abba-e1f93455a5da})
Source(groups=[Employees2.People], props={ATOMIC_REQUEST=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname), VIRTUAL_COMMAND=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname)})
============================================================================
EXECUTING RaiseAccess
AFTER:
Access(groups=[Employees2.People], props={MODEL_ID=Schema name=Employees2, nameInSource=null, uuid=mmuuid:5d465eb1-b546-4f4f-abba-e1f93455a5da})
Project(groups=[], props={PROJECT_COLS=[Count]})
Source(groups=[Employees2.People], props={ATOMIC_REQUEST=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname), VIRTUAL_COMMAND=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname)})
============================================================================
EXECUTING AssignOutputElements
AFTER:
Access(groups=[Employees2.People], props={MODEL_ID=Schema name=Employees2, nameInSource=null, uuid=mmuuid:5d465eb1-b546-4f4f-abba-e1f93455a5da, OUTPUT_COLS=[Count]})
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
Source(groups=[Employees2.People], props={ATOMIC_REQUEST=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname), VIRTUAL_COMMAND=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname), OUTPUT_COLS=[Count]})
============================================================================
EXECUTING CalculateCost
AFTER:
Access(groups=[Employees2.People], props={MODEL_ID=Schema name=Employees2, nameInSource=null, uuid=mmuuid:5d465eb1-b546-4f4f-abba-e1f93455a5da, OUTPUT_COLS=[Count], EST_CARDINALITY=2.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=2.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
Source(groups=[Employees2.People], props={ATOMIC_REQUEST=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname), VIRTUAL_COMMAND=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname), OUTPUT_COLS=[Count], EST_CARDINALITY=2.0})
============================================================================
EXECUTING PlanSorts
AFTER:
Access(groups=[Employees2.People])
Project(groups=[])
Source(groups=[Employees2.People])
============================================================================
EXECUTING CollapseSource
AFTER:
Access(groups=[Employees2.People], props={MODEL_ID=Schema name=Employees2, nameInSource=null, uuid=mmuuid:5d465eb1-b546-4f4f-abba-e1f93455a5da, OUTPUT_COLS=[Count], EST_CARDINALITY=2.0, EST_COL_STATS={Count=[-1.0, -1.0]}, ATOMIC_REQUEST=INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname)})
============================================================================
CONVERTING PLAN TREE TO PROCESS TREE
PROCESS PLAN =
AccessNode(1) output=[Count] INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname)
============================================================================
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(1) output=[Count] INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname)
============================================================================
----------------------------------------------------------------------------
OPTIMIZE:
INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title)
----------------------------------------------------------------------------
GENERATE CANONICAL:
INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title)
CANONICAL PLAN:
Project(groups=[], props={PROJECT_COLS=[Count]})
Source(groups=[Employees.Employees], props={ATOMIC_REQUEST=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title), VIRTUAL_COMMAND=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title)})
============================================================================
EXECUTING PlaceAccess
AFTER:
Project(groups=[], props={PROJECT_COLS=[Count]})
Access(groups=[Employees.Employees], props={MODEL_ID=Schema name=Employees, nameInSource=null, uuid=mmuuid:c33e4fb1-0343-4814-bd2d-659b0f277265})
Source(groups=[Employees.Employees], props={ATOMIC_REQUEST=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title), VIRTUAL_COMMAND=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title)})
============================================================================
EXECUTING RaiseAccess
AFTER:
Access(groups=[Employees.Employees], props={MODEL_ID=Schema name=Employees, nameInSource=null, uuid=mmuuid:c33e4fb1-0343-4814-bd2d-659b0f277265})
Project(groups=[], props={PROJECT_COLS=[Count]})
Source(groups=[Employees.Employees], props={ATOMIC_REQUEST=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title), VIRTUAL_COMMAND=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title)})
============================================================================
EXECUTING AssignOutputElements
AFTER:
Access(groups=[Employees.Employees], props={MODEL_ID=Schema name=Employees, nameInSource=null, uuid=mmuuid:c33e4fb1-0343-4814-bd2d-659b0f277265, OUTPUT_COLS=[Count]})
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
Source(groups=[Employees.Employees], props={ATOMIC_REQUEST=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title), VIRTUAL_COMMAND=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title), OUTPUT_COLS=[Count]})
============================================================================
EXECUTING CalculateCost
AFTER:
Access(groups=[Employees.Employees], props={MODEL_ID=Schema name=Employees, nameInSource=null, uuid=mmuuid:c33e4fb1-0343-4814-bd2d-659b0f277265, OUTPUT_COLS=[Count], EST_CARDINALITY=4.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=4.0, EST_COL_STATS={Count=[-1.0, -1.0]}})
Source(groups=[Employees.Employees], props={ATOMIC_REQUEST=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title), VIRTUAL_COMMAND=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title), OUTPUT_COLS=[Count], EST_CARDINALITY=4.0})
============================================================================
EXECUTING PlanSorts
AFTER:
Access(groups=[Employees.Employees])
Project(groups=[])
Source(groups=[Employees.Employees])
============================================================================
EXECUTING CollapseSource
AFTER:
Access(groups=[Employees.Employees], props={MODEL_ID=Schema name=Employees, nameInSource=null, uuid=mmuuid:c33e4fb1-0343-4814-bd2d-659b0f277265, OUTPUT_COLS=[Count], EST_CARDINALITY=4.0, EST_COL_STATS={Count=[-1.0, -1.0]}, ATOMIC_REQUEST=INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title)})
============================================================================
CONVERTING PLAN TREE TO PROCESS TREE
PROCESS PLAN =
AccessNode(2) output=[Count] INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title)
============================================================================
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(2) output=[Count] INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title)
============================================================================
COMMAND STATEMENT:
INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname);
STATEMENT COMMAND PROCESS PLAN:
AccessNode(1) output=[Count] INSERT INTO Employees2.People (person_id, title, forename, surname) VALUES ("NEW".person_id, "NEW".title, "NEW".forename, "NEW".surname)
COMMAND STATEMENT:
INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title);
STATEMENT COMMAND PROCESS PLAN:
AccessNode(2) output=[Count] INSERT INTO Employees.Employees (employee_id, person_id, job_title) VALUES ("NEW".employee_id, "NEW".person_id, "NEW".job_title)
####################################################
####################################################
PROCEDURE PLAN :ProcedurePlan:
PROGRAM counter 0
0: CREATE CURSOR RESULTSET INSTRUCTION - null
1: CREATE CURSOR RESULTSET INSTRUCTION - null
####################################################
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProcedurePlan:
PROGRAM counter 0
0: CREATE CURSOR RESULTSET INSTRUCTION - null
1: CREATE CURSOR RESULTSET INSTRUCTION - null
============================================================================
CANONICAL PLAN:
Project(groups=[], props={PROJECT_COLS=[Count]})
Source(groups=[EmployeePeople], props={ATOMIC_REQUEST=INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'), VIRTUAL_COMMAND=INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'), PROCESSOR_PLAN=ForEach ProjectNode(0) output=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'] [123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']
{
ProcedurePlan:
PROGRAM counter 0
0: CREATE CURSOR RESULTSET INSTRUCTION - null
1: CREATE CURSOR RESULTSET INSTRUCTION - null
}
})
============================================================================
EXECUTING PlaceAccess
AFTER:
Project(groups=[], props={PROJECT_COLS=[Count]})
Access(groups=[EmployeePeople])
Source(groups=[EmployeePeople], props={ATOMIC_REQUEST=INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'), VIRTUAL_COMMAND=INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'), PROCESSOR_PLAN=ForEach ProjectNode(0) output=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'] [123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']
{
ProcedurePlan:
PROGRAM counter 0
0: CREATE CURSOR RESULTSET INSTRUCTION - null
1: CREATE CURSOR RESULTSET INSTRUCTION - null
}
})
============================================================================
EXECUTING RaiseAccess
AFTER:
Project(groups=[])
Access(groups=[EmployeePeople])
Source(groups=[EmployeePeople])
============================================================================
EXECUTING AssignOutputElements
AFTER:
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
Access(groups=[EmployeePeople], props={OUTPUT_COLS=[Count]})
Source(groups=[EmployeePeople], props={ATOMIC_REQUEST=INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'), VIRTUAL_COMMAND=INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'), PROCESSOR_PLAN=ForEach ProjectNode(0) output=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'] [123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']
{
ProcedurePlan:
PROGRAM counter 0
0: CREATE CURSOR RESULTSET INSTRUCTION - null
1: CREATE CURSOR RESULTSET INSTRUCTION - null
}
, OUTPUT_COLS=[Count]})
============================================================================
EXECUTING CalculateCost
AFTER:
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
Access(groups=[EmployeePeople], props={OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
Source(groups=[EmployeePeople], props={ATOMIC_REQUEST=INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'), VIRTUAL_COMMAND=INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'), PROCESSOR_PLAN=ForEach ProjectNode(0) output=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'] [123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']
{
ProcedurePlan:
PROGRAM counter 0
0: CREATE CURSOR RESULTSET INSTRUCTION - null
1: CREATE CURSOR RESULTSET INSTRUCTION - null
}
, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
============================================================================
EXECUTING PlanSorts
AFTER:
Project(groups=[])
Access(groups=[EmployeePeople])
Source(groups=[EmployeePeople])
============================================================================
EXECUTING CollapseSource
AFTER:
Project(groups=[])
Access(groups=[EmployeePeople], props={OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0, PROCESSOR_PLAN=ForEach ProjectNode(0) output=[123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson'] [123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson']
{
ProcedurePlan:
PROGRAM counter 0
0: CREATE CURSOR RESULTSET INSTRUCTION - null
1: CREATE CURSOR RESULTSET INSTRUCTION - null
}
, ATOMIC_REQUEST=INSERT INTO EmployeePeople (employee_id, person_id, job_title, title, forename, surname) VALUES (123, 456, 'Software Engineer', 'Mr', 'Lewis', 'Watson')})
============================================================================
CONVERTING PLAN TREE TO PROCESS TREE
PROCESS PLAN =
ProjectNode(3) output=[Count] [Count]
PlanExecutionNode(4) output=[Count]
============================================================================
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectNode(3) output=[Count] [Count]
PlanExecutionNode(4) output=[Count]
============================================================================
09:21:39,021 WARN [com.arjuna.ats.arjuna] (Worker0_QueryProcessorQueue1) ARJUNA012140: Adding multiple last resources is disallowed. Trying to add LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@36ebdca1[connectionListener=66dcff09 connectionManager=36e1ea32 warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffff7f000101:3a20f376:538d8444:16, node_name=1, branch_uid=0:ffff7f000101:3a20f376:538d8444:19, subordinatenodename=null, eis_name=java:/Employees > productName=PostgreSQL productVersion=9.1.13 jndiName=java:/Employees])), but already have LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@f65259f[connectionListener=23d0b14e connectionManager=29d694c4 warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffff7f000101:3a20f376:538d8444:16, node_name=1, branch_uid=0:ffff7f000101:3a20f376:538d8444:18, subordinatenodename=null, eis_name=java:/Employees2 > productName=PostgreSQL productVersion=9.1.13 jndiName=java:/Employees2]))
09:21:39,022 WARN [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue1) Connector worker process failed for atomic-request=0MXkB6kZltir.0.2.3: org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@66dcff09[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@17322ce4 connection handles=0 lastUse=1401783699022 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5f2138a1 pool internal context=SemaphoreArrayListManagedConnectionPool@3ae74c5b[pool=Employees] xaResource=LocalXAResourceImpl@36ebdca1[connectionListener=66dcff09 connectionManager=36e1ea32 warned=false currentXid=null productName=PostgreSQL productVersion=9.1.13 jndiName=java:/Employees] txSync=null]
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:277)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:51)
at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:190) [teiid-api-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:276) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:306) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:149) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:563) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:68) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:356) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:283) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:257) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:149) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.proc.ForEachRowPlan.nextBatch(ForEachRowPlan.java:123) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:149) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:157) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:139) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:435) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_55]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_55]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_55]
Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@66dcff09[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@17322ce4 connection handles=0 lastUse=1401783699022 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5f2138a1 pool internal context=SemaphoreArrayListManagedConnectionPool@3ae74c5b[pool=Employees] xaResource=LocalXAResourceImpl@36ebdca1[connectionListener=66dcff09 connectionManager=36e1ea32 warned=false currentXid=null productName=PostgreSQL productVersion=9.1.13 jndiName=java:/Employees] txSync=null]
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:147)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:275)
... 41 more
Caused by: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@66dcff09[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@17322ce4 connection handles=0 lastUse=1401783699022 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5f2138a1 pool internal context=SemaphoreArrayListManagedConnectionPool@3ae74c5b[pool=Employees] xaResource=LocalXAResourceImpl@36ebdca1[connectionListener=66dcff09 connectionManager=36e1ea32 warned=false currentXid=null productName=PostgreSQL productVersion=9.1.13 jndiName=java:/Employees] txSync=null]
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:604) [ironjacamar-core-impl-1.0.19.Final-redhat-2.jar:1.0.19.Final-redhat-2]
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:467) [ironjacamar-core-impl-1.0.19.Final-redhat-2.jar:1.0.19.Final-redhat-2]
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:139)
... 42 more
Caused by: javax.resource.ResourceException: IJ000461: Could not enlist in transaction on entering meta-aware object
at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:474) [ironjacamar-core-impl-1.0.19.Final-redhat-2.jar:1.0.19.Final-redhat-2]
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:599) [ironjacamar-core-impl-1.0.19.Final-redhat-2.jar:1.0.19.Final-redhat-2]
... 44 more
Caused by: javax.transaction.SystemException: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff7f000101:3a20f376:538d8444:16 status: ActionStatus.ABORT_ONLY >
at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:572) [ironjacamar-core-impl-1.0.19.Final-redhat-2.jar:1.0.19.Final-redhat-2]
at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:283) [ironjacamar-core-impl-1.0.19.Final-redhat-2.jar:1.0.19.Final-redhat-2]
at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:467) [ironjacamar-core-impl-1.0.19.Final-redhat-2.jar:1.0.19.Final-redhat-2]
... 45 more
09:21:39,027 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1) TEIID30020 Processing exception for request 0MXkB6kZltir.0 'TEIID30504 Employees: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@66dcff09[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@17322ce4 connection handles=0 lastUse=1401783699022 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5f2138a1 pool internal context=SemaphoreArrayListManagedConnectionPool@3ae74c5b[pool=Employees] xaResource=LocalXAResourceImpl@36ebdca1[connectionListener=66dcff09 connectionManager=36e1ea32 warned=false currentXid=null productName=PostgreSQL productVersion=9.1.13 jndiName=java:/Employees] txSync=null]'. Originally TeiidProcessingException 'IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff7f000101:3a20f376:538d8444:16 status: ActionStatus.ABORT_ONLY >' TxConnectionListener.java:572. Enable more detailed logging to see the entire stacktrace.
I can do a select over two PostgreSQL data sources but I have yet to successfully do an INSERT, UPDATE or DELETE.
Regards
Lewis
