Class PostgreSQLRDBMSActionStrategy

java.lang.Object
com.kingsrook.qqq.backend.module.rdbms.strategy.BaseRDBMSActionStrategy
com.kingsrook.qqq.backend.module.postgres.strategy.PostgreSQLRDBMSActionStrategy
All Implemented Interfaces:
RDBMSActionStrategyInterface
public class PostgreSQLRDBMSActionStrategy extends BaseRDBMSActionStrategy
PostgreSQL specialization of the default RDBMS/JDBC action strategy. This class provides PostgreSQL-specific implementations for database operations, including optimized insert operations using RETURNING clause, proper handling of temporal data types, and PostgreSQL-specific parameter binding behavior.

  • Field Details

    • DEFAULT_LARGE_RESULT_FETCH_SIZE

      public static final int DEFAULT_LARGE_RESULT_FETCH_SIZE
      See Also:

  • Constructor Details

    • PostgreSQLRDBMSActionStrategy

      public PostgreSQLRDBMSActionStrategy()

  • Method Details

    • configureStatementForLargeResults

      public void configureStatementForLargeResults(Connection connection, PreparedStatement statement) throws SQLException
      The pgjdbc driver buffers the entire result set in memory unless the connection is in a transaction (autocommit off) and a fetch size is set (it then uses a server-side cursor). So, for queries hinted as potentially large, when we are inside a transaction, set a fetch size. Outside a transaction (autocommit on), a fetch size has no effect, so it is left alone.
      Throws:
      SQLException

    • setLargeResultFetchSize

      public void setLargeResultFetchSize(int largeResultFetchSize)
      Setter for largeResultFetchSize (0 disables)

    • getLargeResultFetchSize

      public int getLargeResultFetchSize()
      Getter for largeResultFetchSize

    • getInsertDefaultValuesClause

      public String getInsertDefaultValuesClause()
      PostgreSQL requires DEFAULT VALUES when no columns are supplied.

    • executeInsertForGeneratedIds

      public List<Serializable> executeInsertForGeneratedIds(Connection connection, String sql, List<Object> params, QFieldMetaData primaryKeyField) throws SQLException
      PostgreSQL uses RETURNING clause for getting generated IDs. This is more efficient than the default JDBC approach.
      Specified by:
      executeInsertForGeneratedIds in interface RDBMSActionStrategyInterface
      Overrides:
      executeInsertForGeneratedIds in class BaseRDBMSActionStrategy
      Parameters:
      connection - the database connection
      sql - the INSERT SQL statement
      params - the query parameters
      primaryKeyField - the primary key field metadata
      Returns:
      list of generated IDs
      Throws:
      SQLException - if a database error occurs

    • bindParamObject

      protected int bindParamObject(PreparedStatement statement, int index, Object value) throws SQLException
      Override parameter binding for PostgreSQL-specific handling. PostgreSQL JDBC driver requires explicit handling of temporal types and is stricter about type matching than other databases. For null values, PostgreSQL needs Types.OTHER (not Types.CHAR) so it can infer the correct type from the column definition.
      Overrides:
      bindParamObject in class BaseRDBMSActionStrategy
      Parameters:
      statement - the prepared statement
      index - the parameter index
      value - the parameter value
      Returns:
      the number of parameters bound (always 1)
      Throws:
      SQLException - if a database error occurs

    • bindParam

      protected void bindParam(PreparedStatement statement, int index, Integer value) throws SQLException
      Override bindParam for Integer to use Types.OTHER for nulls. This allows PostgreSQL to properly infer types in all contexts.
      Overrides:
      bindParam in class BaseRDBMSActionStrategy
      Parameters:
      statement - the prepared statement
      index - the parameter index
      value - the integer value (may be null)
      Throws:
      SQLException - if a database error occurs

    • bindParam

      protected void bindParam(PreparedStatement statement, int index, Long value) throws SQLException
      Override bindParam for Long to use Types.OTHER for nulls. This allows PostgreSQL to properly infer types in all contexts.
      Overrides:
      bindParam in class BaseRDBMSActionStrategy
      Parameters:
      statement - the prepared statement
      index - the parameter index
      value - the long value (may be null)
      Throws:
      SQLException - if a database error occurs

    • bindParam

      protected void bindParam(PreparedStatement statement, int index, Double value) throws SQLException
      Override bindParam for Double to use Types.OTHER for nulls. This allows PostgreSQL to properly infer types in all contexts.
      Overrides:
      bindParam in class BaseRDBMSActionStrategy
      Parameters:
      statement - the prepared statement
      index - the parameter index
      value - the double value (may be null)
      Throws:
      SQLException - if a database error occurs

    • bindParam

      protected void bindParam(PreparedStatement statement, int index, String value) throws SQLException
      Override bindParam for String to use Types.OTHER for nulls. This allows PostgreSQL to properly infer types in all contexts.
      Overrides:
      bindParam in class BaseRDBMSActionStrategy
      Parameters:
      statement - the prepared statement
      index - the parameter index
      value - the string value (may be null)
      Throws:
      SQLException - if a database error occurs

    • getIdentifierQuoteString

      public String getIdentifierQuoteString()
      PostgreSQL uses double quotes for identifier quoting.
      Returns:
      double quote string for identifier quoting

    • beginConsistentRead

      public ConsistentReadStart beginConsistentRead(Connection connection, ConsistentReadInput input) throws SQLException
      PostgreSQL: REPEATABLE READ (READ ONLY, if so requested) - so every table reflects the one snapshot the transaction takes at its first statement. For BACKEND scope, the snapshot is exported (pg_export_snapshot), and the id returned, so that other connections (parallel workers, pg_dump --snapshot) can attach to it with SET TRANSACTION SNAPSHOT (via importSnapshotId on the input). The exporting transaction must stay open for as long as others may import. Not done on a hot standby, which can't export snapshots. Caveat: a long-lived snapshot holds back vacuum (xmin), and, on a standby, can be cancelled by recovery conflicts (unless hot_standby_feedback is on).
      Specified by:
      beginConsistentRead in interface RDBMSActionStrategyInterface
      Overrides:
      beginConsistentRead in class BaseRDBMSActionStrategy
      Throws:
      SQLException

    • jsonPathExpression

      public String jsonPathExpression(String column, JsonPathStorage storage, QFieldType type)
      Extract JSON scalars using the declared QQQ type.

    • jsonObjectPlaceholder

      public String jsonObjectPlaceholder()
      A JSON document is bound as text and explicitly cast to JSONB.

    • jsonMergeAssignment

      public String jsonMergeAssignment(String column)
      Merge at the row lock; updates of independent keys cannot overwrite each other.

    • jsonPathPredicate

      public String jsonPathPredicate(String column, QFieldMetaData field, QFilterCriteria criterion, List<Serializable> values)
      Equality and IN use GIN-compatible containment, one document per alternative.

    • getSupportedAggregateCapabilities

      public Set<AggregateCapability> getSupportedAggregateCapabilities(AggregateInput input)
      PostgreSQL implements every aggregate extension.
      Specified by:
      getSupportedAggregateCapabilities in interface RDBMSActionStrategyInterface
      Overrides:
      getSupportedAggregateCapabilities in class BaseRDBMSActionStrategy

    • getSampledTableExpression

      public String getSampledTableExpression(String table, String alias, BigDecimal percent, List<Serializable> params)
      Block sampling belongs to the main relation, after its alias and before joins.
      Specified by:
      getSampledTableExpression in interface RDBMSActionStrategyInterface
      Overrides:
      getSampledTableExpression in class BaseRDBMSActionStrategy

    • getGroupByBucketExpression

      public String getGroupByBucketExpression(GroupBy group, String column, QFieldType sourceType, RDBMSBackendMetaData backend, List<Serializable> params)
      PostgreSQL date_trunc and width_bucket with an explicitly closed final numeric bin.
      Specified by:
      getGroupByBucketExpression in interface RDBMSActionStrategyInterface
      Overrides:
      getGroupByBucketExpression in class BaseRDBMSActionStrategy

    • supportsForUpdateSkipLocked

      public boolean supportsForUpdateSkipLocked()
      PostgreSQL supports SELECT ... FOR UPDATE SKIP LOCKED.