Installation fails on Postgres

[Peter]

trac-admin upgrade is not successful due to Postgres 8.2 error in the following statament:

{{ CREATE TABLE work_log (user TEXT,ticket INTEGER,lastchange INTEGER,starttime INTEGER,endtime INTEGER) }}}

Reason: 'user' is reserved word in Postgres

[Colin Guthrie]

Crap. :(

Is there a way to e.g. quote it (in MySQL you use backticks). I'd prefer to avoid renaming the column as it makes upgrading harder, but if it's a simple matter of just backticking the code then that solves all the problems.

I don't have a handy pgsql to test so if you could let me know that would be ace :)

If not then I guess I'll just do the complex upgrade route. Thanks for this (I think I have a transaction based issue on new sqlite installs too but that's another issue!)

[Peter]

It is possible to double quote column names like this (works!):

CREATE TABLE work_log ("user" TEXT,"ticket" INTEGER,"lastchange" INTEGER,"starttime" INTEGER,"endtime" INTEGER)

However, you would have to double-quote "user" field in all SQL queries, or it will assume it's system variable named 'user'

[Colin Guthrie]

Thanks for the info. I'll try and take a look soon. I think a rename is a good idea but I can leave the quoted user field stuff in the initial table creation and just rename it later and thus limit the quoting to just one little area rather than all over the plugin.

(I'm trying to purge all my open source bugs just now as I seem to have let several build up on various different projects!!)

[Colin Guthrie]

(In [3063]) Hopefully fix #2418 by renaming the column 'user' to 'worker'. This requires some jiggery pokery in the db upgrade/install script in order to remain cross db-backend compatible (if it even is in the first place.... ;))

[Peter]

Confirm: seems to work now.