Hi all,
I think I've finally gained some insight into ticket UP-2019 (
http://www.ja-sig.org/issues/browse/UP-2019), which has been a thorn in my
side for some time now. The ticket represents a problem I've encountered on
PostgreSQL databases in which attempting to use the "ant crn-import" command
to re-import an already-existing layout fails. The problem appears to occur
only for layouts which have portlet preferences defined, and to my
knowledge, has only been observed on Postgres.
Here's the associated console output from the current trunk:
[java] INFO Join test failed:
org.jasig.portal.rdbm.DatabaseMetaDataImpl$OracleDb on statement: 'SELECT
COUNT(UP_USER.USER_ID) FROM UP
_USER, UP_USER_LAYOUT WHERE UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID(+) AND
UP_USER.USER_ID=0':
[java] StatementCallback; bad SQL grammar [SELECT
COUNT(UP_USER.USER_ID) FROM UP_USER, UP_USER_LAYOUT WHERE UP_USER.USER_ID =
UP_USER_LAYOUT.USER_ID(+) AND UP_USER.USER_ID=0]; nested exception is
org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
[java] INFO Join test failed:
org.jasig.portal.rdbm.DatabaseMetaDataImpl$PostgreSQLDb on statement:
'SELECT COUNT(UP_USER.USER_ID) FROM UP_USER LEFT OUTER JOIN UP_USER_LAYOUT
ON UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID WHERE UP_USER.USER_ID=0':
[java] StatementCallback; uncategorized SQLException for SQL [SELECT
COUNT(UP_USER.USER_ID) FROM UP_USER LEFT OUTER JOIN UP_USER_LAYOUT
ON UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID WHERE UP_USER.USER_ID=0]; SQL
state [25P02]; error code [0]; ERROR: current transaction is aborted,
commands ignored until end of transaction block; nested exception is
org.postgresql.util.PSQLException: ERROR: current transaction is aborted,
commands ignored until end of transaction block
The problem appears to be caused by one of the join tests executed to
determine whether the database supports outer joins. It looks like
org.jasig.portal.rdbm.DatabaseMetaDataImpl determines whether outer joins
are supported by cycling through a list of supplied outer join tests. If a
test throws an exception, the exception is caught and the next statement in
the join list is attempted. If any statement returns successfully, the
method sets the known working join test to the current SQL statement and
will report that the database supports outer joins.
In PostgreSQL, it seems that after the first Oracle-specific join test is
executed, the transaction is invalidated, and subsequent join tests fail
simply because the transaction has already been aborted. It appears that
this behavior is being caused by some quirky PostgreSQL behavior involving
the handling of transactions without savepoints (
http://archives.postgresql.org/pgsql-jdbc/2006-04/msg00002.php).
Switching the order of the test join statements, such that the Postgres
statement appears first in the list, fixes the problem completely from
Postgres's perspective. I'm not sure if switching the order would cause
issues for Oracle, or cause any other issues. Alternately, perhaps we could
first test whether the database supports transactions, and if it does, make
use of savepoints and rollbacks during the join test. Does anyone with a
better understanding of the behavior of this class have thoughts on the best
way to resolve the issue?
Thanks,
- Jen
--
You are currently subscribed to [email protected] as:
[email protected]
To unsubscribe, change settings or access archives, see
http://www.ja-sig.org/wiki/display/JSG/uportal-dev
