system.install creates the role table with the "rid" field of type "serial" which automatically creates a sequence called "{role}_serial_rid_seq" and sets the default value for the field to "nextval('{role}_serial_rid_seq')".

Howver, the system.install script then goes on to insert two records, explicitly setting their "rid" values to DRUPAL_ANONYMOUS_RID and DRUPAL_AUTHENTICATED_RID, without updating the sequence.

As a result, adding roles to a new install will fail twice before succeeding.

The fix is to update the sequence, setting currval to the greater of DRUPAL_ANONYMOUS_RID and DRUPAL_AUTHENTICATED_RID.

Patchfile attached.

CommentFileSizeAuthor
#8 system_install-postgres_seqs.patch1.36 KBhalcyonCorsair
#2 modules-system-system.install_0.patch457 bytespillarsdotnet
modules-system-system.install.patch472 bytespillarsdotnet

Comments

pillarsdotnet’s picture

pillarsdotnet commented

Probably should mention that this only affect postgresql users.

Not sure if the proper "Component" should be:

system.module
install system
postgresql database

pillarsdotnet’s picture

pillarsdotnet commented
StatusFileSize
new modules-system-system.install_0.patch457 bytes

Bah! Named the sequence wrong. Fixed and re-rolled the patch.

--- orig/drupal/modules/system/system.install   2007-07-19 01:39:25.000000000 -0400
+++ patched/drupal/modules/system/system.install        2007-09-08 16:12:17.000000000 -0400
@@ -882,6 +882,8 @@
         UNIQUE (name)
       )");

+      db_query("SELECT setval('{role}_rid_seq',".max(DRUPAL_ANONYMOUS_RID,DRUPAL_AUTHENTICATED_RID)."));");
+
       db_query("CREATE TABLE {blocks_roles} (
         module varchar(64) NOT NULL,
         delta varchar(32) NOT NULL,
bdragon’s picture

bdragon commented

http://drupal.org/node/174071 marked as duplicate.

pillarsdotnet’s picture

pillarsdotnet commented
Title: Need to update {role}_serial_rid_seq in modules/system/system.install » Need to update {role}_rid_seq in modules/system/system.install

Adjust subject line.

drumm’s picture

drumm
he/him
Location NY, US
commented
Version: 5.2 » 5.x-dev
Status: Needs review » Fixed

Committed to 5.x. Please look over Drupal 6.x, which may have this issue.

ned0r’s picture

ned0r commented

+ db_query("SELECT setval('{role}_rid_seq',".max(DRUPAL_ANONYMOUS_RID,DRUPAL_AUTHENTICATED_RID)."));");

should really be ...

+ db_query("SELECT setval('{role}_rid_seq',".max(DRUPAL_ANONYMOUS_RID,DRUPAL_AUTHENTICATED_RID).");");

i.e. you have one too many closing parens :-(

This makes stuff break in nasty ways :(

pillarsdotnet’s picture

pillarsdotnet commented
Status: Fixed » Needs work

ned0r wrote:

... i.e. you have one too many closing parens ...

You're right; sorry about that. Changing Status.

halcyonCorsair’s picture

halcyonCorsair commented
Title: Need to update {role}_rid_seq in modules/system/system.install » Need to update {users}_uid_seq and {role}_rid_seq in modules/system/system.install
Version: 5.x-dev » 6.x-dev
Priority: Normal » Critical
Status: Needs work » Needs review
Issue tags: +PostgreSQL
StatusFileSize
new system_install-postgres_seqs.patch1.36 KB

Adjust subject, add patch.

If these sequences aren't updated then the attempt to insert a role or a user will fail.

damien tournoud’s picture

damien tournoud commented
Status: Needs review » Closed (won't fix)

I don't believe this is needed anymore.