[JBoss JIRA] (TEIID-5431) Function postgis_full_version() does not exist; Transaction Fails
by Ramesh Reddy (JIRA)
Ramesh Reddy created TEIID-5431:
-----------------------------------
Summary: Function postgis_full_version() does not exist; Transaction Fails
Key: TEIID-5431
URL: https://issues.jboss.org/browse/TEIID-5431
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 11.0
Reporter: Ramesh Reddy
Assignee: Steven Hawkins
Within a XA transaction, when Teiid tries to do "initCapabilities" on Postgresql JDBC connector, it issues a function call "select postgis_full_version()" to detect the postgis version. However, if the postgis is not installed, then this fails.
The undesired side effect is, the query already started a transaction then the whole transaction is rolled back.
{code}
Caused by: org.postgresql.util.PSQLException: ERROR: function postgis_full_version() does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 8
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:303) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:289) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:266) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:233) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_161]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_161]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_161]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_161]
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:423) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at com.sun.proxy.$Proxy90.executeQuery(Unknown Source) ~[na:na]
at org.teiid.translator.jdbc.postgresql.PostgreSQLExecutionFactory.initCapabilities(PostgreSQLExecutionFactory.java:324) ~[translator-jdbc-11.1.0-SNAPSHOT.jar!/:11.1.0-SNAPSHOT]
at org.teiid.translator.jdbc.postgresql.PostgreSQLExecutionFactory.initCapabilities(PostgreSQLExecutionFactory.java:72) ~[translator-jdbc-11.1.0-SNAPSHOT.jar!/:11.1.0-SNAPSHOT]
{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5397) Make RETURN_GENERATED_KEYS work with views
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5397?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5397:
---------------------------------------
> I think it is very uncommon and I did not find any information about it in docs.
Oracle also allows constraints on views, but has a more verbose syntax which allows for the constraints to be enforced or disabled. We'll make sure the dos mention more about constraints on views.
It looks like another change would be to allow for the view attribute types to be inferred from the query expression. We're just reusing the parsing routine from create table, but we should just allow:
create view v (a, b, c) as ...
> Make RETURN_GENERATED_KEYS work with views
> ------------------------------------------
>
> Key: TEIID-5397
> URL: https://issues.jboss.org/browse/TEIID-5397
> Project: Teiid
> Issue Type: Feature Request
> Affects Versions: 10.2.1
> Reporter: Lukáš Svačina
> Assignee: Steven Hawkins
> Fix For: 11.1
>
>
> *+VDB:+*
> {code}
> <vdb name="_GENERATED_form_shoes" version="2">
> <model name="_INTERNAL_internalModel" type="PHYSICAL">
> <source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/>
> </model>
> <model name="GEN_view" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true')
> AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model",
> "public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]>
> </metadata>
> </model>
> </vdb>
> {code}
> *+TABLE:+*
> name: form_shoes_2
> columns: id (SERIAL) | size (INTEGER *NULLABLE*) | model (VARCHAR *NULLABLE*)
> *+PROBLEM:+*
> Connected into VDB using JDBC like:
> {code:java}
> final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS);
> statement.executeUpdate();
> final ResultSet generatedKeys = statement.getGeneratedKeys();
> {code}
> *generatedKeys is empty if:*
> # INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK!
> # INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here?
> *+QUESTIONS:+*
> # How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
> # How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?
> Thanks for fixing/adding this functionality.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5363) SimpleDB connector not quoting table names in internal Select queries
by Pranav K (JIRA)
[ https://issues.jboss.org/browse/TEIID-5363?page=com.atlassian.jira.plugin... ]
Pranav K commented on TEIID-5363:
---------------------------------
Ah, I think that's it. I was trying some overridden behavior to the SimpleDBMetadataProcessor for some other purpose, just realized the overridden class didn't have the logic to quote the nameInSource while importing metadata. Sorry for the additional iteration; and thanks too!
> SimpleDB connector not quoting table names in internal Select queries
> ---------------------------------------------------------------------
>
> Key: TEIID-5363
> URL: https://issues.jboss.org/browse/TEIID-5363
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Reporter: Pranav K
> Assignee: Steven Hawkins
> Fix For: 11.0, 10.3.2
>
>
> Facing an issue while querying the Simple DB connector for a specific type of tables - the tables that contain a hyphen in the name.
> According to the SimpleDB documentation:
> Attribute and domain names may appear without quotes if they contain only letters, numbers, underscores (_), or dollar symbols ($) and do not start with a number. You must quote all other attribute and domain names with the backtick (`).
> (https://docs.aws.amazon.com/AmazonSimpleDB/latest/DeveloperGuide/QuotingR...)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5430) Closing Teiid JDBC connection is delayed 5 seconds
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5430?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5430:
---------------------------------------
Using SIGSTOP to reproduce effectively puts the server in a non-responsive state. Is the customer using SIGSTOP or is the server unresponsive due to other circumstances?
> Closing Teiid JDBC connection is delayed 5 seconds
> --------------------------------------------------
>
> Key: TEIID-5430
> URL: https://issues.jboss.org/browse/TEIID-5430
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Driver
> Affects Versions: 8.12.13.6_4
> Reporter: Hiroki Daicho
> Assignee: Steven Hawkins
> Attachments: JDBCClient.java
>
>
> Teiid JDBC Driver waits 5 seconds during closing connection when the client can not reach teiid server.
> If many connections are pooling by the client as a Datasource, it takes more time the closing all connections.
> e.g. if the datasource has 50 connections, the close time would be 5 seconds * 50.
> [1] is the stacktrace whie the connection is closing with the delay.
> When closing connection, teiid JDBC waits server response by Future#get() with timeout of 5 seconds.
> So, it would be waiting for the time if the socket doesn't receive any response.
> [1] Stacktrace when the connection waits 5 seconds.
> {code}
> "main" #1 prio=5 os_prio=0 tid=0x00007f5b1c009000 nid=0x707b runnable [0x00007f5b238ac000]
> java.lang.Thread.State: RUNNABLE
> at java.net.SocketInputStream.socketRead0(Native Method)
> at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
> at java.net.SocketInputStream.read(SocketInputStream.java:171)
> at java.net.SocketInputStream.read(SocketInputStream.java:141)
> at java.io.BufferedInputStream.fill(BufferedInputStream.java:246)
> at java.io.BufferedInputStream.read(BufferedInputStream.java:265)
> - locked <0x000000071c98cad0> (a org.teiid.core.util.AccessibleBufferedInputStream)
> at java.io.DataInputStream.readInt(DataInputStream.java:387)
> at org.teiid.netty.handler.codec.serialization.ObjectDecoderInputStream.readObjectOverride(ObjectDecoderInputStream.java:97)
> at java.io.ObjectInputStream.readObject(ObjectInputStream.java:416)
> at org.teiid.net.socket.OioOjbectChannelFactory$OioObjectChannel.read(OioOjbectChannelFactory.java:117)
> at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:308)
> at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:411)
> at org.teiid.net.socket.SocketServerConnection.logoff(SocketServerConnection.java:331)
> at org.teiid.net.socket.SocketServerConnection.close(SocketServerConnection.java:306)
> - locked <0x000000071c51bb48> (a org.teiid.net.socket.SocketServerConnection)
> at org.teiid.jdbc.ConnectionImpl.close(ConnectionImpl.java:282)
> at ClientMain.invoke(ClientMain.java:83)
> at ClientMain.main(ClientMain.java:30)
> {code}
> [2]
> https://github.com/teiid/teiid/blob/master/client/src/main/java/org/teiid...
> {code}
> private void logoff() {
> disconnect();
> try {
> //make a best effort to send the logoff
> Future<?> writeFuture = this.serverInstance.getService(ILogon.class).logoff();
> writeFuture.get(5000, TimeUnit.MILLISECONDS);
> } catch (Exception e) {
> //ignore
> }
> closeServerInstance();
> }
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5409) left join is reversed
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5409?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5409:
---------------------------------------
Yes it needs some specific conditions to trigger the issue. It has to be a left outer join that cannot be initially pushed (in this issue it's the timestampdiff function that prevents that initially) with a nested join where one of the nested tables can be pushed with the outer side - which it can here as it's all the same source.
> left join is reversed
> ---------------------
>
> Key: TEIID-5409
> URL: https://issues.jboss.org/browse/TEIID-5409
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 10.3.1, 10.3.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 11.1, 11.0.1, 10.3.3
>
> Attachments: plan_teiid_10.3.2.txt, plan_teiid_9.1.txt
>
>
> A query that is run on teiid 9.1 results in 56186 rows.
> The same query that is run on teiid 10.3.2 results in 5919 rows.
> It seems like the left join is reversed.
> I've attached both query plans.
> The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5409) left join is reversed
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5409?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-5409:
-------------------------------------
[~shawkins], Is there a specific case where this issue occured. I was already using 10.3 for some project and I would like to check for issues that might have occured. But I'm fairly confident that this left join issue has only happened in specific cases.
> left join is reversed
> ---------------------
>
> Key: TEIID-5409
> URL: https://issues.jboss.org/browse/TEIID-5409
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 10.3.1, 10.3.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 11.1, 11.0.1, 10.3.3
>
> Attachments: plan_teiid_10.3.2.txt, plan_teiid_9.1.txt
>
>
> A query that is run on teiid 9.1 results in 56186 rows.
> The same query that is run on teiid 10.3.2 results in 5919 rows.
> It seems like the left join is reversed.
> I've attached both query plans.
> The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5363) SimpleDB connector not quoting table names in internal Select queries
by Pranav K (JIRA)
[ https://issues.jboss.org/browse/TEIID-5363?page=com.atlassian.jira.plugin... ]
Pranav K edited comment on TEIID-5363 at 7/24/18 1:36 AM:
----------------------------------------------------------
While debugging the query that I catch at SimpleDBConnectionImpl.performSelect is -> select name from test-hyph, without the ` ` backticks, and hence the error I guess. If I modify the selectExpression during debugging to have the backticks and let the flow continue, it works fine and returns the query.
was (Author: pranavk):
While debugging the query that I catch at SimpleDBConnectionImpl.performSelect is -> select name from test-hyph, without the ` ` backticks. That's what you wanted to know right?
> SimpleDB connector not quoting table names in internal Select queries
> ---------------------------------------------------------------------
>
> Key: TEIID-5363
> URL: https://issues.jboss.org/browse/TEIID-5363
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Reporter: Pranav K
> Assignee: Steven Hawkins
> Fix For: 11.0, 10.3.2
>
>
> Facing an issue while querying the Simple DB connector for a specific type of tables - the tables that contain a hyphen in the name.
> According to the SimpleDB documentation:
> Attribute and domain names may appear without quotes if they contain only letters, numbers, underscores (_), or dollar symbols ($) and do not start with a number. You must quote all other attribute and domain names with the backtick (`).
> (https://docs.aws.amazon.com/AmazonSimpleDB/latest/DeveloperGuide/QuotingR...)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5430) Closing Teiid JDBC connection is delayed 5 seconds
by Hiroki Daicho (JIRA)
[ https://issues.jboss.org/browse/TEIID-5430?page=com.atlassian.jira.plugin... ]
Hiroki Daicho updated TEIID-5430:
---------------------------------
> Closing Teiid JDBC connection is delayed 5 seconds
> --------------------------------------------------
>
> Key: TEIID-5430
> URL: https://issues.jboss.org/browse/TEIID-5430
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Driver
> Affects Versions: 8.12.13.6_4
> Reporter: Hiroki Daicho
> Assignee: Steven Hawkins
> Attachments: JDBCClient.java
>
>
> Teiid JDBC Driver waits 5 seconds during closing connection when the client can not reach teiid server.
> If many connections are pooling by the client as a Datasource, it takes more time the closing all connections.
> e.g. if the datasource has 50 connections, the close time would be 5 seconds * 50.
> [1] is the stacktrace whie the connection is closing with the delay.
> When closing connection, teiid JDBC waits server response by Future#get() with timeout of 5 seconds.
> So, it would be waiting for the time if the socket doesn't receive any response.
> [1] Stacktrace when the connection waits 5 seconds.
> {code}
> "main" #1 prio=5 os_prio=0 tid=0x00007f5b1c009000 nid=0x707b runnable [0x00007f5b238ac000]
> java.lang.Thread.State: RUNNABLE
> at java.net.SocketInputStream.socketRead0(Native Method)
> at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
> at java.net.SocketInputStream.read(SocketInputStream.java:171)
> at java.net.SocketInputStream.read(SocketInputStream.java:141)
> at java.io.BufferedInputStream.fill(BufferedInputStream.java:246)
> at java.io.BufferedInputStream.read(BufferedInputStream.java:265)
> - locked <0x000000071c98cad0> (a org.teiid.core.util.AccessibleBufferedInputStream)
> at java.io.DataInputStream.readInt(DataInputStream.java:387)
> at org.teiid.netty.handler.codec.serialization.ObjectDecoderInputStream.readObjectOverride(ObjectDecoderInputStream.java:97)
> at java.io.ObjectInputStream.readObject(ObjectInputStream.java:416)
> at org.teiid.net.socket.OioOjbectChannelFactory$OioObjectChannel.read(OioOjbectChannelFactory.java:117)
> at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:308)
> at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:411)
> at org.teiid.net.socket.SocketServerConnection.logoff(SocketServerConnection.java:331)
> at org.teiid.net.socket.SocketServerConnection.close(SocketServerConnection.java:306)
> - locked <0x000000071c51bb48> (a org.teiid.net.socket.SocketServerConnection)
> at org.teiid.jdbc.ConnectionImpl.close(ConnectionImpl.java:282)
> at ClientMain.invoke(ClientMain.java:83)
> at ClientMain.main(ClientMain.java:30)
> {code}
> [2]
> https://github.com/teiid/teiid/blob/master/client/src/main/java/org/teiid...
> {code}
> private void logoff() {
> disconnect();
> try {
> //make a best effort to send the logoff
> Future<?> writeFuture = this.serverInstance.getService(ILogon.class).logoff();
> writeFuture.get(5000, TimeUnit.MILLISECONDS);
> } catch (Exception e) {
> //ignore
> }
> closeServerInstance();
> }
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months