Changeset 5881


Ignore:
Timestamp:
Nov 21, 2011, 11:54:26 AM (10 years ago)
Author:
Nicklas Nordborg
Message:

Fixes #1651: Error handling problems when using PostgreSQL

Adds the possibitlity to use "failSafe" query execution. This can only be enabled when we do our own SQL execution and not via Hibernate. The "failSafe" option should be enabled when the gui must be able to continue in order to render a useful html page. I have enabled this option on the spot data list page, but there may be other places that need it as well (eg. experiment explorer).

Location:
trunk
Files:
1 added
10 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/clients/web/net/sf/basedb/clients/web/Base.java

    r5817 r5881  
    969969      query.setItemPermission(mode.forceItemPermission());
    970970    }
     971    query.setFailSafe(true);
    971972    return query;
    972973  }
  • trunk/src/core/net/sf/basedb/core/AbstractQuery.java

    r5564 r5881  
    194194 
    195195  /**
     196    If transactions must be able to continue after an SQL failure or not.
     197  */
     198  private boolean failSafe;
     199 
     200  /**
    196201    Create a new query.
    197202    @param rootEntity The root entity of the query
     
    460465    return getMainQuery(dc, true);
    461466  }
     467 
     468  @Override
     469  public void setFailSafe(boolean failSafe)
     470  {
     471    this.failSafe = failSafe;
     472  }
     473
     474  @Override
     475  public boolean isFailSafe()
     476  {
     477    return failSafe;
     478  }
     479
    462480  // -------------------------------------------
    463481
  • trunk/src/core/net/sf/basedb/core/AbstractSqlQuery.java

    r5689 r5881  
    2525
    2626import net.sf.basedb.core.hibernate.ResultSetWork;
     27import net.sf.basedb.core.hibernate.SavePointWork;
    2728import net.sf.basedb.core.query.QueryParameter;
    2829import net.sf.basedb.core.query.SqlQuery;
     
    4142import java.sql.ResultSet;
    4243import java.sql.SQLException;
     44import java.sql.Savepoint;
     45import java.sql.Statement;
    4346
    4447/**
     
    103106  {
    104107    long totalCount = -1;
    105     // Get query string
     108    CountWork work = createCountWork(dc);
     109    try
     110    {
     111      if (debugSqlEnabled) logSql.debug("Executing count query: " + work.getSql());
     112      boolean useSavePoint = isFailSafe() &&
     113          HibernateUtil.getDbEngine().useSavePointToContinueTransactionFromSqlFailure();
     114      totalCount = HibernateUtil.doJdbcWork(dc.getHibernateSession(),
     115        useSavePoint ? new SavePointWork<Long>(work) : work);
     116    }
     117    catch (SQLException ex)
     118    {
     119      logSql.error(work.getSql(), ex);
     120      throw new BaseException(ex);
     121    }
     122    return totalCount;
     123  }
     124  // -------------------------------------------
     125 
     126  /**
     127    Creates a work item for counting the number of total hits by the query.
     128  */
     129  private CountWork createCountWork(DbControl dc)
     130  {
    106131    String countSql = getCountQuery(dc, true);
    107     // Parse SQL and replace named parameters with ?
    108     final List<String> countParameterOrder = new LinkedList<String>();
    109     final AbstractSqlQuery query = this;
     132    List<String> countParameterOrder = new LinkedList<String>();
    110133    countSql = parseParameters(countSql, countParameterOrder);
    111     try
    112     {
    113       if (debugSqlEnabled) logSql.debug("Executing count query: " + countSql);
    114      
    115       totalCount = HibernateUtil.doJdbcWork(dc.getHibernateSession(),
    116         new ResultSetWork<Long>(dc.getSessionControl(), countSql)
    117         {
    118           @Override
    119           protected void setParameters(PreparedStatement ps)
    120             throws SQLException
    121           {
    122             query.setParameters(ps, countParameterOrder);
    123           }
    124           @Override
    125           protected Long getResult(ResultSet rs)
    126             throws SQLException
    127           {
    128             return rs.next() ? rs.getLong(1) : 0;
    129           }
    130         }
    131       );
    132     }
    133     catch (SQLException ex)
    134     {
    135       logSql.error(countSql, ex);
    136       throw new BaseException(ex);
    137     }
    138     return totalCount;
    139   }
    140   // -------------------------------------------
     134    return new CountWork(dc.getSessionControl(), countSql, countParameterOrder);
     135  }
    141136 
    142137  abstract Select[] getDefaultSelects();
     
    171166   
    172167    // Get the main query
    173     String sql = getMainQuery(dc, true);
     168    String mainSql = getMainQuery(dc, true);
    174169   
    175170    // Holds the names of the parameters in the query in the order they appear
     
    177172   
    178173    // Parse out named parameters
    179     sql = parseParameters(sql, parameterOrder);
     174    mainSql = parseParameters(mainSql, parameterOrder);
    180175   
    181176    // Add limit parameters
    182     sql = bindLimits(sql, parameterOrder);
     177    mainSql = bindLimits(mainSql, parameterOrder);
    183178    // Main query is now built
    184179   
    185     // Load the total count if it is requested
    186     if (isReturningTotalCount())
    187     {
    188       totalCount = count(dc);
    189     }
    190 
    191180    ResultSet result = null;
     181    Connection conn = null;
     182    Savepoint savepoint = null;
     183    PreparedStatement ps = null;
     184    RuntimeException exception = null;
     185    String currentSql = null;
    192186    try
    193187    {
    194       Connection c = HibernateUtil.getConnection(dc.getHibernateSession());
    195       if (debugSqlEnabled) logSql.debug("Executing main query: " + sql);
    196       PreparedStatement ps = c.prepareStatement(sql);
     188      conn = HibernateUtil.getConnection(dc.getHibernateSession());   
     189      if (isFailSafe() &&
     190        HibernateUtil.getDbEngine().useSavePointToContinueTransactionFromSqlFailure())
     191      {
     192        savepoint = conn.setSavepoint();
     193      }
     194
     195      // Load the total count if it is requested
     196      if (isReturningTotalCount())
     197      {
     198        CountWork countWork = createCountWork(dc);
     199        currentSql = countWork.getSql();
     200        if (debugSqlEnabled) logSql.debug("Executing count query: " + currentSql);
     201        countWork.execute(conn);
     202        totalCount = countWork.getResult();
     203      }
     204     
     205      if (debugSqlEnabled) logSql.debug("Executing main query: " + mainSql);
     206      currentSql = mainSql;
     207      ps = conn.prepareStatement(mainSql);
    197208      setParameters(ps, parameterOrder);
    198209      result = QueryExecutor.executeQuery(ps, dc.getSessionControl());
     
    204215    catch (InterruptedException ex)
    205216    {
    206       throw new SignalException("Aborted by user.", ex);
     217      exception = new SignalException("Aborted by user.", ex);
    207218    }
    208219    catch (SQLException ex)
    209220    {
    210       logSql.error(sql, ex);
    211       throw new BaseException(ex);
     221      logSql.error(currentSql, ex);
     222      exception = new BaseException(ex);
     223    }
     224    finally
     225    {
     226      if (exception != null)
     227      {
     228        if (result != null) closeResultSet(result, currentSql);
     229        if (ps != null) closeStatement(ps, currentSql);
     230        if (savepoint != null) rollbackToSavepoint(conn, savepoint, currentSql);
     231        throw exception;
     232      }
     233      else
     234      {
     235        releaseSavepoint(conn, savepoint, currentSql);
     236      }
    212237    }
    213238    return new DynamicResultIterator(result, totalCount, getMaxResults());
     
    367392  }
    368393 
     394  /**
     395    Close the result set without throwing an exception. If
     396    the {@link ResultSet#close()} method throws an exception
     397    it is logged together with the message.
     398    @param msg A message to write to the log if the close fails
     399    @param result The result set to close
     400    @since 3.1
     401  */
     402  protected void closeResultSet(ResultSet result, String msg)
     403  {
     404    try
     405    {
     406      result.close();
     407    }
     408    catch (Throwable t)
     409    {
     410      logSql.warn(msg, t);
     411    }
     412  }
     413 
     414  /**
     415    Close the statement without throwing an exception. If
     416    the {@link Statement#close()} method throws an exception
     417    it is logged together with the message.
     418    @param msg A message to write to the log if the close fails
     419    @param stmt The statement to close
     420    @since 3.1
     421  */
     422  protected void closeStatement(Statement stmt, String msg)
     423  {
     424    try
     425    {
     426      stmt.close();
     427    }
     428    catch (Throwable t)
     429    {
     430      logSql.warn(msg, t);
     431    }
     432  }
     433 
     434  /**
     435    Rollback the current transaction to the given savepoint.
     436    @param conn The connection to rollback
     437    @param savepoint The savepoint to rollback to
     438    @param msg A message to write to the log if the rollback fails
     439    @since 3.1
     440  */
     441  protected void rollbackToSavepoint(Connection conn, Savepoint savepoint, String msg)
     442  {
     443    try
     444    {
     445      conn.rollback(savepoint);
     446    }
     447    catch (Throwable t)
     448    {
     449      logSql.warn(msg, t);
     450    }
     451  }
     452
     453  /**
     454    Release the given savepoint.
     455    @param conn The connection that holds the savepoint
     456    @param savepoint The savepoint to release
     457    @param msg A message to write to the log if the release fails
     458    @since 3.1
     459  */
     460  protected void releaseSavepoint(Connection conn, Savepoint savepoint, String msg)
     461  {
     462    try
     463    {
     464      conn.releaseSavepoint(savepoint);
     465    }
     466    catch (Throwable t)
     467    {
     468      logSql.warn(msg, t);
     469    }
     470  }
     471 
     472 
     473  class CountWork
     474    extends ResultSetWork<Long>
     475  {
     476
     477   
     478    private List<String> parameterOrder;
     479   
     480    CountWork(SessionControl sc, String sql, List<String> parameterOrder)
     481    {
     482      super(sc, sql);
     483      this.parameterOrder = parameterOrder;
     484    }
     485
     486
     487    @Override
     488    protected void setParameters(PreparedStatement ps)
     489      throws SQLException
     490    {
     491      AbstractSqlQuery.this.setParameters(ps, parameterOrder);
     492    }
     493
     494    @Override
     495    protected Long getResult(ResultSet rs)
     496      throws SQLException
     497    {
     498      return rs.next() ? rs.getLong(1) : 0;
     499    }
     500
     501   
     502   
     503  }
    369504}
  • trunk/src/core/net/sf/basedb/core/ReporterPropertyExpression.java

    r5879 r5881  
    101101        if (useCloned(dynamicQuery))
    102102        {
    103           if (!dynamicQuery.getVirtualDb().hasClonedReporterProperty(property))
     103          if (!dc.loadItem(dynamicQuery.getVirtualDb()).hasClonedReporterProperty(property))
    104104          {
    105105            throw new InvalidDataException("Reporter property is not cloned: " + property);
  • trunk/src/core/net/sf/basedb/core/dbengine/AbstractDbEngine.java

    r5853 r5881  
    218218    return "\"" + name + "\"";
    219219  }
     220  /**
     221    @return Always FALSE, but may be overridden by subclasses
     222   */
     223  @Override
     224  public boolean useSavePointToContinueTransactionFromSqlFailure()
     225  {
     226    return false;
     227  }
    220228  // -------------------------------------------
    221229
  • trunk/src/core/net/sf/basedb/core/dbengine/DbEngine.java

    r5853 r5881  
    370370  public String inspectSchemaGenerationSQL(String sql, Dialect dialect, SchemaGenerator.Mode mode);
    371371 
     372  /**
     373    If the underlying database need to create a savepoint before executing
     374    an SQL statement that results in an error in order to be able to
     375    continue using the same transaction for other SQL queries. This is,
     376    for example, needed by PostgreSQL which will otherwise ignore all
     377    SQL statements executed after the one that caused an error.
     378    @return TRUE if a SAVEPOINT is needed, FALSE if not
     379  */
     380  public boolean useSavePointToContinueTransactionFromSqlFailure();
     381 
    372382}
  • trunk/src/core/net/sf/basedb/core/dbengine/PostgresDbEngine.java

    r5853 r5881  
    277277    return sql;
    278278  }
     279  /**
     280    @return Always TRUE
     281   */
     282  @Override
     283  public boolean useSavePointToContinueTransactionFromSqlFailure()
     284  {
     285    return true;
     286  }
    279287  // -------------------------------------------
     288
    280289 
    281290
  • trunk/src/core/net/sf/basedb/core/hibernate/ResultSetWork.java

    r4517 r5881  
    5050{
    5151
    52   private SessionControl sc;
    53   private String sql;
     52  private final SessionControl sc;
     53  private final String sql;
    5454  private R result;
    5555 
     
    115115
    116116  /**
     117    The that is executed by this work.
     118    @since 3.1
     119  */
     120  public String getSql()
     121  {
     122    return sql;
     123  }
     124 
     125  /**
    117126    This method should be overridden by subclasses that needs to
    118127    set parameters on the statement that is going to be executed.
  • trunk/src/core/net/sf/basedb/core/query/Query.java

    r5818 r5881  
    374374  */
    375375  public String toQl(DbControl dc);
     376 
     377  /**
     378    Set this flag to ensure that the transaction used to execute the query
     379    is allowed to continue even after a failure at the SQL level of this query.
     380    Setting this flag to TRUE should allow the transaction to continue, setting
     381    it to FALSE may or may not allow it to continue. The default setting is FALSE.
     382   
     383    @param failSafe TRUE to ensure that the transaction is allowed to continue,
     384      FALSE if it doesn't matter
     385    @since 3.1
     386  */
     387  public void setFailSafe(boolean failSafe);
     388
     389  /**
     390    Check if a failure of this query should still allow the current transaction
     391    to continue.
     392    @return TRUE if the transaction should be allowed to continue,
     393      FALSE if it doesn't  matter
     394    @since 3.1
     395  */
     396  public boolean isFailSafe();
     397 
    376398}
  • trunk/www/views/experiments/spotdata/list_spotdata.jsp

    r5879 r5881  
    119119    final DynamicSpotQuery query = bioAssay != null ? bioAssay.getSpotData() : bioAssaySet.getSpotData();
    120120    cc.configureQuery(dc, query, selected.selectedProperties);
     121    query.setFailSafe(true);
    121122    spotData = query.iterate(dc);
    122123  }
Note: See TracChangeset for help on using the changeset viewer.