Re: too many trigger records found for relation "item" - - Mailing list pgsql-general

[Update: the post didn't make it to the list probably due to the attachment, so I resend it inlined... and I was not
ableto trigger the same behavior on 8.2, so it might have been already fixed.] 

[snip]
> Well, if you can show a reproducible test case, I'd like to look at it.

OK, I have a test case which has ~ 90% success rate in triggering the
issue on my box. It is written in Java, hope you can run it, in any case
you'll get the idea how to reproduce the issue.

The code is attached, and I list here some typical output run against an
8.1.3 postgres installation. The first exception is strange on it's own,
it was produced after a few runs, might be caused by another issue with
creating/dropping tables (I think I have seen this too some time ago).

I'll go and run it against 8.2 and see if the issue is still there. My
problems on the integration box turned out to be postgres logging set to
too high level and running out of disk space due to log amount...

Cheers,
Csaba.


Error executing sql: CREATE TABLE test_child_0 (a bigint primary key
references test_parent(a))
org.postgresql.util.PSQLException: ERROR: duplicate key violates unique
constraint "pg_type_typname_nsp_index"
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)
    at
com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:155)
    at
com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork1(TestChildTableCreationIndependent.java:91)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:76)
Error executing sql: DROP TABLE test_child_0
com.domeus.trials.TestChildTableCreationIndependent$MissingTableException
    at
com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:158)
    at
com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork1(TestChildTableCreationIndependent.java:98)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:76)
Error executing sql: DROP TABLE test_child_251
org.postgresql.util.PSQLException: ERROR: 2 trigger record(s) not found
for relation "test_parent"
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)
    at
com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:155)
    at
com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork1(TestChildTableCreationIndependent.java:98)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:76)
Error executing sql: DROP TABLE test_child_258
org.postgresql.util.PSQLException: ERROR: too many trigger records found
for relation "test_parent"
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)
    at
com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:155)
    at
com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork1(TestChildTableCreationIndependent.java:98)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:76)
Error executing sql: DROP TABLE test_child_262
org.postgresql.util.PSQLException: ERROR: too many trigger records found
for relation "test_parent"
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)
    at
com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:155)
    at
com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork1(TestChildTableCreationIndependent.java:98)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:76)

From another run:

Error executing sql: insert into test_parent values (96)
org.postgresql.util.PSQLException: ERROR: 2 trigger record(s) not found
for relation "test_parent"
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)
    at
com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:155)
    at
com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork2(TestChildTableCreationIndependent.java:108)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:78)
Error executing sql: insert into test_parent values (215)
org.postgresql.util.PSQLException: ERROR: too many trigger records found
for relation "test_parent"
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)
    at
com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:155)
    at
com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork2(TestChildTableCreationIndependent.java:108)
    at
com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:78)


(I tried first attaching the file, but didn't make it to the list, so I inline)
************************************************
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestChildTableCreationIndependent {

    private String url;
    private String user;
    private String password;

    TestChildTableCreationIndependent() throws Exception {
        Class.forName("org.postgresql.Driver");
        // replace these with your values
        url = "jdbc:postgresql://<host>:5432/<dbname>";
        user = "<user>";
        password = "<pass>";
    }

    public static void main(String[] args) throws Exception {
        new TestChildTableCreationIndependent().testChildTableCreation();
    }

    public void testChildTableCreation() throws Exception {

        final int THREAD_COUNT = 20;
        final int EXECUTION_COUNT = 30;

        final Connection connection = getConnection();

        dropTables(connection, THREAD_COUNT * EXECUTION_COUNT);

        Thread[] threads = new Thread[THREAD_COUNT];

        executeQuery(connection, "create table test_parent(a bigint primary key)");

        for (int i = 0; i < threads.length; i++) {
            threads[i] =
              new TestChildTableCreationIndependent.WorkerThread(EXECUTION_COUNT);
        }
        for (int i = 0; i < threads.length; i++) {
            threads[i].start();
        }
        for (int i = 0; i < threads.length; i++) {
            threads[i].join();
        }

        dropTables(connection, THREAD_COUNT * EXECUTION_COUNT);

    }

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    private class WorkerThread extends Thread {

        private final Connection connection;
        private final int executionCount;

        public WorkerThread(int executionCount) throws SQLException {
            this.executionCount = executionCount;
            connection = getConnection();
        }

        @Override
        public void run() {
            for (int i = 0; i < executionCount; i++) {
                try {
                    if (Math.random() < 0.5) {
                        doWork1();
                    } else {
                        doWork2();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

        private void doWork1() {
            final String tableName = getChildTableName();
            String sql = "CREATE TABLE " + tableName +
                         " (a bigint primary key references test_parent(a))";
            try {
                executeQuery(connection, sql);
            } catch (Throwable e) {
                System.err.println("Error executing sql: " + sql);
                e.printStackTrace();
            }
            sql = "DROP TABLE " + tableName;
            try {
                executeQuery(connection, sql);
            } catch (Throwable e) {
                System.err.println("Error executing sql: " + sql);
                e.printStackTrace();
            }
        }
        private void doWork2() {
            final String sql =
              "insert into test_parent values (" + idCounter++ + ")";
            try {
                executeQuery(connection, sql);
            } catch (Throwable e) {
                System.err.println("Error executing sql: " + sql);
                e.printStackTrace();
            }
        }
    }

    private void dropTables(Connection connection, int childCount)
      throws SQLException
    {
        childCounter = 0;
        for (int i = 0; i < childCount; i++) {
            final String tableName = TestChildTableCreationIndependent.getChildTableName();
            try {
                executeQuery(connection, "DROP TABLE " + tableName);
            } catch (MissingTableException e) {
                // ignore, it was already dropped
            } catch (SQLException e) {
                e.printStackTrace();
                throw e;
            }
        }
        try {
            executeQuery(connection, "DROP TABLE test_parent");
        } catch (MissingTableException e) {
            // ignore, it was already dropped
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        childCounter = 0;
    }

    private static volatile int childCounter = 0;
    private static volatile int idCounter = 0;

    private static String getChildTableName() {
        // this is good enough on my box to not need synchronisation, YMMV - synchronize this method if it won't work
        return "test_child_" + TestChildTableCreationIndependent.childCounter++;
    }

    private void executeQuery(Connection connection, String sql)
      throws SQLException,
             MissingTableException
    {
        Statement statement = connection.createStatement();
        try {
            statement.execute(sql);
        } catch (SQLException e) {
            String message = e.getMessage();
            if (message.indexOf("does not exist") != -1
                && message.indexOf("table") != -1)
            {
                throw new MissingTableException();
            }
            throw e;
        }
    }

    private static class MissingTableException extends Exception {}

}