This post originated from an RSS feed registered with Ruby Buzz by Guy Naor. Original Post: Fixing Rails for Postgres Schemas Feed Title: Famundo - The Dev Blog Feed URL: http://devblog.famundo.com/xml/rss/feed.xml Feed Description: A blog describing the development and related technologies involved in creating famundo.com - a family management sytem written using Ruby On Rails and postgres Latest Ruby Buzz Posts Latest Ruby Buzz Posts by Guy Naor Latest Posts From Famundo - The Dev Blog

Postgres has a very powerful feature in the schemas, especially with the schema search_path. Using it I can have multiple separate families, all using the same database connection, but also fully separated (with a different user to access the schema files). Using the search path and the SESSION AUTHORIZATION it is completely transparent to rails. I'll have a later post explaining how to use this technique in a rails application.

The cool thing about the search_path is that you can have multiple instances of the same table in different schemas, and using SET SESSION AUTHORIZATION, you can see a different table each time. Rails works great with this.

But there's a small bug in the postgres driver for handling sequences. (The bug doesn't show up in developmnet mode because the objects are reloaded all the time.) When postgres loads the sequence name used for the auto increment keys (serial type in postgres), it adds the schema to the sequence name. So instead of messages_id_seq, it will return public.messages_id_seq. If another request coming in, tries to insert a record, the call to SELECT currval() will return the wrong sequence, and if (like I do) there's user security between schemas, you get an access error and the insert fails.

The fix is very simple - just return the unqualified sequence name, as the rest of the access in rails is unqualified.

I'm posting a ticket with the fix and a test file for it. The fix to the rails edge is the following (in diff format):

Index: lib/active_record/connection_adapters/postgresql_adapter.rb
===================================================================
--- lib/active_record/connection_adapters/postgresql_adapter.rb (revision 4414)
+++ lib/active_record/connection_adapters/postgresql_adapter.rb (working copy)
@@ -296,8 +296,9 @@
               AND def.adsrc ~* 'nextval'
           end_sql
         end
-        # check for existence of . in sequence name as in public.foo_sequence.  if it does not exist, join the current namespace
-        result.last['.'] ? [result.first, result.last] : [result.first, "#{result[1]}.#{result[2]}"]
+        # check for existence of . in sequence name as in public.foo_sequence.  if it does not exist, return unqualified sequence
+        # We cannot qualify unqualified sequences, as rails doesn't qualify any table access, using the search path
+        [result.first, result.last]
       rescue
         nil
       end