"User" is a reserved keyword in Postgres.

[anonymous]

The work_log table contains a field named user. This is an SQL reserved keyword:

• ​http://www.postgresql.org/docs/8.2/static/sql-keywords-appendix.html

This means that when will not be able to upgrade the trac database after enable worklog, if you are using Postgres.

The upgrade pretends to succeed but is in fact failing silently:

[20071019-18:46:55]:[root@bugs]:[~]# trac-admin /var/trac/systems upgrade --no-backup
Worklog needs an upgrade
Upgrading Database
Creating work_log table
Done upgrading Worklog
Upgrade done.
[20071019-18:47:05]:[root@bugs]:[~]# trac-admin /var/trac/systems upgrade --no-backup
Worklog needs an upgrade
Upgrading Database
Creating work_log table
Done upgrading Worklog
Upgrade done.

However:

systems=# CREATE TABLE work_log (
systems(#   user       TEXT,
systems(#   ticket     INTEGER,
systems(#   lastchange INTEGER,
systems(#   starttime  INTEGER,
systems(#   endtime    INTEGER,
systems(# );
ERROR:  syntax error at or near "user" at character 27
LINE 2:   user       TEXT,
          ^
systems=# 

[Bryan Allen]

SQL changes required for standards-compliant databases.

[Carsten Fechtmann]

Patch above is not applicable for 0.11b1 and postgres-users at least not at revision r2741

Plugin is broken as is when using postgress as DB, and even manages to bring trac down, unless it is disabled again in trac.ini by hand. Also see ticket #2106

I tried to apply the patch manually (as in changing all the user -> username) but there seems to be more than that wrong. E.g. work_log table is not created at all, WorklogPlugin_Db_Version in system is not set / changed etc.

Even when work_log table and system entry are created separately, and although trac-admin upgrade stops complaining, work-log is still not working, i.e. only producing an error message.

I hope someone else got an idea how to solve this, as I just plain run out of ideas ... (or at least SQL knowledge)

[ecdpalma@…]

Corrects the problem with the "user" column name and the creation of the work_log table, that was being affected by a previous statement.

[ecdpalma@…]

I discovered that if I surround user with double quotes, it works. Here goes a patch that corrects another things also. I found that in api.py, the statement

cursor.select(SELECT * FROM work_log LIMIT 1)

leads to a strange state, so the table could not be created in the next statement. So I put a rollback in the exception.