What causes "Error while truncating " in pentaho/pentaho-kettle?

Grant TRUNCATE (or table ownership) to the connection user, or disable the 'Truncate table' option and empty the table another way

pentaho/pentaho-kettle · error · KettleException

Error while truncating

Error message

Error while truncating

What it means

processTruncate executes 'TRUNCATE <table>' when the step's truncate option is enabled, and rethrows any SQL failure as a KettleException with the table name. The table could not be emptied before the COPY load.

Solutions

Grant TRUNCATE (or table ownership) to the connection user, or disable the 'Truncate table' option and empty the table another way
Verify the table exists in the connected database/schema; use the correct schema-qualified name
Quote the identifier (schema."Table") if it contains mixed case or special characters, e.g. via properly quoted table name in the step metadata
Check pg_locks/pg_stat_activity for sessions blocking the TRUNCATE and terminate them

Example fix

-- before (fails for mixed-case name)
TRUNCATE MyTable
-- after
TRUNCATE "public"."MyTable"

Defensive patterns

Strategy: validation

Validate before calling

// run before enabling truncate
try ( Connection c = dm.getConnection(); Statement s = c.createStatement() ) {
  s.executeQuery( "SELECT has_table_privilege( CURRENT_USER, '" + tableName + "', 'TRUNCATE' )" );
}

Try / catch

try {
  step.processRow();
} catch ( KettleException e ) {
  if ( e.getMessage().startsWith( "Error while truncating" ) ) {
    // fall back to DELETE FROM, or grant TRUNCATE, then retry
  }
}

Prevention

Grant TRUNCATE or table ownership to the loader's DB user
Use correct schema-qualified, quoted table names for mixed-case identifiers
Only enable 'Truncate table' when concurrent writers won't hold conflicting locks

When it happens

Trigger: do_copy invokes processTruncate with the truncate option checked and statement.executeUpdate("TRUNCATE " + tableName) throws: table missing, insufficient privilege, table name needing quoting, or lock conflicts with concurrent sessions.

Common situations: Non-superuser without TRUNCATE privilege on the target table; schema-qualified or mixed-case table name unquoted; other long-running transactions blocking the lock TRUNCATE needs; pointing at the wrong database where the table doesn't exist.

Understand the failure class

Background: "query failed", "%w: SQL error" — wrapped database query errors in Go libraries explained — this error's family across 3 libraries.

Related errors

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/b43a9d3432abc001. Report an issue: GitHub.

Appendix: source

Thrown at plugins/postgresql-db-bulk-loader/impl/src/main/java/org/pentaho/di/trans/steps/pgbulkloader/PGBulkLoader.java:214

  void processTruncate() throws Exception {
    Connection connection = data.db.getConnection();

    String loadAction = environmentSubstitute( meta.getLoadAction() );

    if ( loadAction.equalsIgnoreCase( "truncate" ) ) {
      DatabaseMeta dm = meta.getDatabaseMeta();
      String tableName =
        dm.getQuotedSchemaTableCombination( environmentSubstitute( meta.getSchemaName() ),
          environmentSubstitute( meta.getTableName() ) );
      logBasic( "Launching command: " + "TRUNCATE " + tableName );

      Statement statement = connection.createStatement();

      try {
        statement.executeUpdate( "TRUNCATE " + tableName );
      } catch ( Exception ex ) {
        throw new KettleException( "Error while truncating " + tableName, ex );
      } finally {
        statement.close();
      }
    }
  }

  public boolean processRow( StepMetaInterface smi, StepDataInterface sdi ) throws KettleException {
    meta = (PGBulkLoaderMeta) smi;
    data = (PGBulkLoaderData) sdi;

    try {
      Object[] r = getRow(); // Get row from input rowset & set row busy!

      if ( r == null ) { // no more input to be expected...

        setOutputDone();

        // Close the output stream...

View on GitHub (pinned to f3058517a1)