No role updates using Drupal 6 with Postgresql 8.3

Hi,

Sorry that my SQL statements weren't geared toward PostgreSQL, I haven't used that database for a long while.

I've gone through and made all of the statements compliant, can you test and let me know if the development version works for you now?

Thx for the fixing.
I think you forgot 2 lines which don't run with postgres. I've attached a patch file.

With it applied it runs without errors, but the problem with the not applied roles still exists. If you could provide me some hints how to debug further I would be glad to find and fix the bug. (See post 1, I don't know if it is related to postgres or not)

I further investigated the problem myself with the devel module and discovered an oddity.
The interesting section of SQL-Statements around the regcode-role-function looks like this:

Nr function SQL
1 regcode_roles_get_rules SELECT id, category, regcode_roles.role AS rid, role.name AS role FROM regcode_roles AS regcode_roles LEFT JOIN role AS role ON (regcode_roles.role = role.rid) WHERE category='Registered User'
2 user_save SELECT data FROM users WHERE uid = 32
3 user_save UPDATE users SET data = 'b:0;' WHERE uid = 32
4 user_save DELETE FROM users_roles WHERE uid = 32
5 user_save INSERT INTO users_roles (uid, rid) VALUES (32, 8) -->ROLE is inserted correctly
6 user_load SELECT * FROM users u WHERE uid = 32
7 user_load SELECT r.rid, r.name FROM role r INNER JOIN users_roles ur ON ur.rid = r.rid WHERE ur.uid = 32
8 user_save UPDATE users SET data = 'a:3:{s:12:"regcode_code";s:4:"USER";s:13:"form_build_id";s:37:"form-dab0b084d3bce0d788b3aaad05fe8dbf";s:7:"contact";i:1;}' WHERE uid = 32
9 user_save DELETE FROM users_roles WHERE uid = 32 --> ROLE is deleted!!! --> why?
10 user_load SELECT * FROM users u WHERE uid = 32
11 user_load SELECT r.rid, r.name FROM role r INNER JOIN users_roles ur ON ur.rid = r.rid WHERE ur.uid = 32
12 dblog_watchdog INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (0, 'user', 'New user: %name (%email).', 'a:2:{s:5:"%name";s:10:"Test User5";s:6:"%email";s:23:"tester5@nsc-stgallen.ch";}', 5, 'bearbeiten', 'http://nsc.goetz.sg/user/register', 'http://nsc.goetz.sg/user/register', '213.196.172.136', 1255552594)
13 user_load SELECT * FROM users u WHERE LOWER(name) = LOWER('Test User5') AND pass = '6dac0a7703f3ed9052deb3a84aaebd02' AND status = 1
14 user_load SELECT r.rid, r.name FROM role r INNER JOIN users_roles ur ON ur.rid = r.rid WHERE ur.uid = 32
.....

I think the problem lies in Debug-Line 9. Any idea why this is executed after the role is set?
Will further look into the code and report back, but if you have the solution right in your brain plz do not hesitate to post ;-)....

It works! hooray

I now commented out the second use of the DELETE user_roles ... Statement (in the else-clause) in users.module. Don't ask me why this else was executed too after the if was executed...

I'll check the ramifications for the rest of the system tomorrow. (seems to be some bug in the user_save function)

Hi Cephos,

I think I've fixed it in a less core changing manner ... basically when you save a HABTM relationship, you clear all of the relationships and save over the top. Because we were trying to save the roles before it the record was updating, drupal was unhappy.

The way it works now is how it should have worked from the start - modifying the edit array, so drupal only saves the record once.

Cheers for helping to debug the problem!