trac-(pre|post)-commit-hook do not work with a schema enabled trac project

[robert_creager@…]

The contrib files track-pre-commit-hook and trac-post-commit-hook do not work with a single trac database which uses schemas to manage multiple trac projects.

The pre-commit hook is obviously flawed by using direct SQL and not setting a search path. It's less obvious to me how the post-commit hook fails, but I've not looked at the API it's using.

Note that the hooks work fine when no schema is specified for the Trac project.

Using Trac 10.3 with PostgreSQL 8.1.1 database.

[mgood]

Replying to robert_creager@logicalchaos.org:

The pre-commit hook is obviously flawed by using direct SQL and not setting a search path.

This shouldn't be directly responsible for the problem. The schema handling is done transparently by the database backend since it's specific to Postgres.

[robert_creager@…]

Yeah, that would make sense. If the db connection is set up correctly (search_path), it should work. But it's not for me ;-)

$ svn ci -m "Remove unused tables from sql - fixes #1" robert@thunder's password: Sending trunk/trigger/tassiv_trigger.sql.in Transmitting file data .svn: Commit failed (details follow): svn: 'pre-commit' hook failed with error output: At least one open ticket must be mentioned in the log message.

I turned on query logging, and the search_path is not being set up:

Dec 22 22:12:01 thunder postgres811[30361]: [2-1] LOG: statement: select version() Dec 22 22:12:01 thunder postgres811[30361]: [3-1] LOG: statement: BEGIN WORK Dec 22 22:12:01 thunder postgres811[30361]: [4-1] LOG: statement: DECLARE "PgSQL_-48CA25F4" CURSOR FOR SELECT value FROM system WHERE name='database_version' Dec 22 22:12:01 thunder postgres811[30361]: [5-1] LOG: statement: FETCH 1 FROM "PgSQL_-48CA25F4" Dec 22 22:12:01 thunder postgres811[30361]: [6-1] LOG: statement: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 25 Dec 22 22:12:01 thunder postgres811[30361]: [7-1] LOG: statement: CLOSE "PgSQL_-48CA25F4" Dec 22 22:12:01 thunder postgres811[30361]: [8-1] LOG: statement: ROLLBACK WORK Dec 22 22:12:01 thunder postgres811[30361]: [9-1] LOG: statement: BEGIN WORK Dec 22 22:12:01 thunder postgres811[30361]: [10-1] LOG: statement: DECLARE "PgSQL_-48C16B94" CURSOR FOR SELECT COUNT(id) FROM ticket WHERE status <> 'closed' AND id IN (1) Dec 22 22:12:01 thunder postgres811[30361]: [11-1] LOG: statement: FETCH 1 FROM "PgSQL_-48C16B94" Dec 22 22:12:01 thunder postgres811[30361]: [12-1] LOG: statement: SELECT typname, -1 , typelem FROM pg_type WHERE oid = 20 Dec 22 22:12:01 thunder postgres811[30361]: [13-1] LOG: statement: CLOSE "PgSQL_-48C16B94" Dec 22 22:12:01 thunder postgres811[30361]: [14-1] LOG: statement: ROLLBACK WORK

When done correctly:

trac=# set search_path TO tass_reduce; SET trac=# select count(id) from ticket where status <> 'closed' and id in (1);

count

---

1

(1 row)

One thing that does puzzle me, is why maintain both pre and post commit hooks for referencing tickets? It seems like a better(?) solution would be to modify the current post commit hook to accept an option to only check to see if the criteria was met (ticket reference). I had to modify the pre hook to accept the same search patterns that the post commit hook accepted (I use both). Then, in this case at least, the problem only has to be fixed once. Although you have me believing now that the root cause is in the trac db connection setup, not the hook scripts.

[robert_creager@…]

Whoops, sorry about the formatting of that message. Teach me to preview the submission...

[cboos]

Seems worth checking if this still happens with recent trac-post-commit-hooks.