[PATCH] sequence names for PostgreSQL

Quick solution

This patch fixes reset_sequences in fixtures.rb in order to work properly with tables that shares one sequence (that was an original intent of the ticket) and uses #{pk} instead of id. The previous attachment should be limited to patching postgresql_adapter.rb only

I like the idea of asking the database which sequence is used as the default value for a column, but I have reservations regarding this patch.

The sequence_name method is implemented twice: once in the pg adapter and once in fixtures. You can use the connection's method in fixtures.

The caching in sequence_name is roundabout and the lookup itself is performed in three queries instead of one.

*There are no unit tests.*

Jeremy,

If the basic idea of patch will be preliminary agreed to add to Rails, I'll refactor it and add unit tests.

Please do! Also, note that the PostgreSQL connection adapter has changed a bit: schema support was recently committed to trunk.

Ok! I'll do it within a week or so. Thanks for the support!

There's a patch in ticket #962 that takes care of the fixtures problem and does sequence name lookup in one query instead of three. Maybe this approach is useful in conjunction with the above patches...

This patch will not work if you have multiple SCHEMAS in PostgreSQL with the same table name and primary key column name.

For example:

=# \d legacy.people
                                       Table "legacy.people"
  Column   |         Type          |                           Modifiers                           
-----------+-----------------------+---------------------------------------------------------------
 people_id | integer               | not null default nextval('legacy.people_people_id_seq'::text)
 name      | character varying(50) | 
Indexes:
    "people_pkey" PRIMARY KEY, btree (people_id)

=# \d foo.people
                                       Table "foo.people"
  Column   |         Type          |                         Modifiers                          
-----------+-----------------------+------------------------------------------------------------
 people_id | integer               | not null default nextval('foo.people_people_id_seq'::text)
 name      | character varying(50) | 
Indexes:
    "people_pkey" PRIMARY KEY, btree (people_id)      

This part of his patch will return multiple records now:

=# SELECT oid FROM pg_class WHERE relname = 'people';
  oid  
-------
 17526
 17517
(2 rows) 

If the following query, the result will look like this.

=# SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM pg_class WHERE relname = 'people');
ERROR:  more than one row returned by a subquery used as an expression  

The solution that I posted in ticket #2016, helps get around this problem, but does not work with non-standard sequence names. I have yet to find a solution for that easily within the PostgreSQL system tables.

Superseded by #2016.