Unable to create a forum on PostgreSQL

[hrvelic@…]

I'm using the latest version from subversion, with modified SQL statements.
I get a few errors when trying to create a forum. When I try creating a forum with no moderator selected, I get the following error:

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/trac/web/modpython_frontend.py", line 206, in handler
    dispatch_request(mpr.path_info, mpr, env)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 139, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 107, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-i686/egg/tracdiscussion/core.py", line 178, in process_request
  File "build/bdist.linux-i686/egg/tracdiscussion/api.py", line 174, in add_forum
IntegrityError: ERROR:  null value in column "id" violates not-null constraint

INSERT INTO forum (name, author, time, moderators, subject, description, forum_group) VALUES ('TestForum', 'hrvelic', 1151885264, '', 'Trac Discussion plugin Test forum.', 'Test forum.  Please use for testing purposes only.', NULL)

Having one moderator, for example me ('hrvelic'), produces the same error, with value 'hrvelic' for moderators column.

On the other hand, when I try to create a forum with two moderators selected, I get the following output:

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/trac/web/modpython_frontend.py", line 206, in handler
    dispatch_request(mpr.path_info, mpr, env)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 139, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 107, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-i686/egg/tracdiscussion/core.py", line 172, in process_request
AttributeError: 'list' object has no attribute 'split'

By the way, I really like the idea behind your plugin. Now, if I can make it work with Postgres I'll be able to ditch that nasty phpBB2 thingie. :)

[Alec Thomas]

Incorrect component, as described in the create ticket warning box.

[Ken Auberry]

Looks like Postgres doesn't automatically increment the value of a column that is designated "primary key". You have to connect it to a "sequence" entity that describes the values and ranges to use for the key and then give the column a type of "serial".

When you then look at the SQL generated for the column creation, it's still using an integer (int4) entity, but now it will be auto-incremented with each new row.

I've made these changes manually to my local database, and everything seems to be working now, but I'm not sure how this would be put into the database setup files (db1.py and db2.py) and still work for both Postgres and SQLite.

[Radek Bartoň]

I'm not familiar with PostgreSQL but according to documentation change id column type from INT to SERIAL should be sufficient. Sequence is created automatically. Am I right?

[hrvelic@…]

Blackhex, if you change the id column type to SERIAL, it should work, but I haven't tried.
I have tried to use SERIAL type in an alter table alter coumn type statement and that didn't work because it is not really a type.
I have created sequences for forum, forum_group, topic and message tables and altered default values for id column in all four tables. The plugin works now, but the second problem, with moderators, remains. I will create a new ticket for it and post new information, since I just noticed that moderator list is incomplete - shows two system admins, where I know there are four.
Thanks for your time and effort,[[BR}] Hrvoje Velic

[Radek Bartoň]

Yes that was sepparate bug. I've fixed it already but currently I can't commit changes maybe tomorrow.

[Radek Bartoň]

I hope that 1006 fixed the problem. As I don't have PosgreSQL installed on my own system I'll appreciate any testing. Thank you.

[hrvelic@…]

I've created a new environment just to test that. However, I wasn't able to upgrade after creating the environment:

$ trac-admin /var/trac/Test upgrade --no-backup
Command failed: global name 'postgre_sql' is not defined

Note: --no-backup is a must when upgrading on Postgresql, otherwise upgrade will fail because trac doesn't support backing up Postgresql database.

[Radek Bartoň]

There were stupid typo mistake, variable has postrge_sql name instead of postgre_sql :-)

[hrvelic@…]

Yay! Works now!
Ok. I modified the variable assignement in tracdiscussion/db/db1.py by hand, and then rebuilt the .egg and copied it manually in site-packages folder.
Upgrade went all right and I created a forum without any problems. :D
Thank you for your time and effort, and sorry if it took me a while to respond. :)

[Radek Bartoň]

No problem, I'm not occupied with DiscussionPlugin full time too :-).