Ticket Navigation

Ticket #2594 (closed defect: fixed)

Opened 3 years ago

Last modified 2 years ago

[PATCH] Postgresql fails to determine correct sequence and primary key names

Reported by:	Blair Zajac <blair@orcaware.com>	Assigned to:	Jeremy Kemper <rails@bitsweat.net>
Priority:	normal	Milestone:
Component:	ActiveRecord	Version:	0.14.3
Severity:	normal	Keywords:	fd postgresql pk sequence default
Cc:

Description

In my Postgresql tables, I explicitly create a sequence and use it in the column definition as the default value for the primary key. I do this in the same manner that the companies table defined in test/fixtures/db_definitions/postgresql.sql.

CREATE SEQUENCE companies_nonstd_seq START 101;

CREATE TABLE companies (
    id integer DEFAULT nextval('companies_nonstd_seq'),
    "type" character varying(50),
    "ruby_type" character varying(50),
    firm_id integer,
    name character varying(50),
    client_of integer,
    rating integer default 1,
    PRIMARY KEY (id)
);

The current pk_and_sequence_for in 0.14.1 does not work in this case. I don't know how to fix it, but I have a patch that adds a test to the fixture test case that shows the failure, so that should help a bit.

Having this code not work is currently causing my application test suite to fail, since the fixture is loading data into the table, but the sequence is still at 1 when I try to save a brand new row into the table, so I get this error:

ActiveRecord::StatementInvalid: ERROR: duplicate key violates unique
constraint "post_pkey"

Regards,
Blair

Attachments

ror-postgresql-pk_and_sequence_for-failure-0.14.1-patch.txt (2.0 kB) - added by blair on 10/24/05 23:55:36.
ror-postgresql-pk_and_sequence_for-failure-0.14.3-patch.txt (3.7 kB) - added by blair on 11/08/05 15:45:02.
ror-2985-changeset-rake-postgresql-results.txt (14.2 kB) - added by blair on 11/12/05 19:09:19.
ror-2985-changeset-rake-postgresql-results2.txt (15.6 kB) - added by blair on 11/12/05 22:11:29.: Output from run with 'resuce nil' removed

Change History

11/07/05 22:20:28 changed by blair

By the way, for people running into this problem, here's my hacky solution. Place this at the bottom of your config/environment.rb and it'll override the normal one.

# In Ruby on Rails 0.14.2, the pk_and_sequence_for in the Postgresql
# connection module does not work when the primary key's sequence is
# explicitly named as it is in this application.  So for now, use the
# fact that all the tables have a standard primary key and sequence
# naming sequence and return those names.
#
# This works around problems in the unit and functional tests, where
# the sequence is not properly reset to return the next primary key
# after the fixtures have been loaded.  Instead, the sequence will
# return a value of 1, which is already a primary key in the table.
# See http://dev.rubyonrails.com/ticket/2594 .
module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter
      def pk_and_sequence_for(table)
        [ "pk_#{table}", "#{table}_pk_#{table}_seq" ]
      end
    end
  end
end

11/08/05 13:35:50 changed by meadow.nnick@gmail.com

Well, here's one more quick hack solution - query that retrieves pkey and sequence directly from pg_constraints and pg_attrdef (default values for columns).

Here's script to test. For testing convenience, query was wrapped as SQL function, and whole script in single BEGIN-ROLLBACK transaction.

Tested on PostgreSQL 8.0

begin;
create table t1 (
        id int not null primary key,
        data text
);
create sequence t1_id_seq start 1;
alter table t1 alter column id set default nextval('public.t1_id_seq');

create table t2 (
        id serial primary key,
        data text
);

CREATE FUNCTION sequence_and_pk (regclass) RETURNS RECORD AS $$
SELECT attr.attname::text, substr(def.adsrc,10,length(def.adsrc)-17)::text
FROM pg_attrdef def
     JOIN pg_attribute  attr ON (attrelid = adrelid AND adnum = attnum)
     JOIN pg_constraint cons ON (adrelid = conrelid)
WHERE
        cons.conrelid = $1 :: oid
        AND def.adnum = cons.conkey[1]
        AND cons.contype = 'p'
        AND adsrc like 'nextval(\'%\'::text)'
$$ language 'sql';


select * from sequence_and_pk('t1'::regclass) as (attname text, seqname text);
select * from sequence_and_pk('t2'::regclass) as (attname text, seqname text);

rollback;

11/08/05 15:44:22 changed by blair

version changed from 0.14.2 to 0.14.3.
summary changed from [PATCH] 0.14.2: Postgresql pk_and_sequence_for fails for explicit sequences to [PATCH] 0.14.3: Postgresql pk_and_sequence_for fails for explicit sequences.

Thanks to meadow.nnick@gmail.com's SQL code, I'm attaching a new patch with a new pk_and_sequence_for and tests to check that pk_and_sequence_for works with both a Serial column and a column that explicitly uses a sequence name.

To verify that the new pk_and_sequence_for works, apply the patch, revert the pk_and_sequence_for change and run the test suite. It should then fail on the Company table. With the patch, it works fine.

As mentioned by meadow.nnick@gmail.com, this is a 'quick hack', so somebody who is more of an Postgresql expert may want to try this out.

Will this patch work with Postgresql 7.x and the just announced 8.1?

Regards,
Blair

11/12/05 22:09:01 changed by blair

Jeremy,

I'm testing 8.1.0 freshly compiled using Debian's sources from

http://ftp.us.debian.org/debian/pool/main/p/postgresql-8.1/ http://ftp.us.debian.org/debian/pool/main/p/postgresql-8.1/postgresql-8.1_8.1.0.orig.tar.gz http://ftp.us.debian.org/debian/pool/main/p/postgresql-8.1/postgresql-8.1_8.1.0-1.diff.gz

My package applies a patch I put together to install PostGIS 1.0.4 into the contrib directory and recompile it with

dpkg-buildpackage -us -uc -rfakeroot

The package runs 'make check' and this passes all except for a date test (which fails I believe to daylight saving times). So I believe I have a good build.

Because newly created sequences in 8.1 are different than 8.0, I did run the postgresql.drop.sql, postgresql2.drop.sql, postgresql.sql and postgresql2.sql on the database.

I'm attaching a new output with the rescue nil removed.

Regards, Blair

11/13/05 00:57:03 changed by bitsweat

Could you do the fallback query? When the first query (the one you executed) has no results, the adapter falls back to second query which looks for a custom sequence.

SELECT attr.attname, (name.nspname || '.' || split_part(def.adsrc, '\'', 2))
  FROM pg_class       t
  JOIN pg_namespace   name ON (t.relnamespace = name.oid)
  JOIN pg_attribute   attr ON (t.oid = attrelid)
  JOIN pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum)
  JOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1])
 WHERE t.oid = 'companies'::regclass
   AND cons.contype = 'p'
   AND def.adsrc ~ 'nextval\\(\'[^\']*\'::[^\\)]*\\)'

You can run this query directly in psql (note that it has less quoting that the query in the Ruby code.)

Thanks!

11/13/05 01:00:26 changed by bitsweat

BTW, on my powerbook with pg8.1:

$ psql activerecord_unittest
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

activerecord_unittest=# SELECT attr.attname, (name.nspname || '.' || split_part(def.adsrc, '\'', 2))
activerecord_unittest-#   FROM pg_class       t
activerecord_unittest-#   JOIN pg_namespace   name ON (t.relnamespace = name.oid)
activerecord_unittest-#   JOIN pg_attribute   attr ON (t.oid = attrelid)
activerecord_unittest-#   JOIN pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum)
activerecord_unittest-#   JOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1])
activerecord_unittest-#  WHERE t.oid = 'companies'::regclass
activerecord_unittest-#    AND cons.contype = 'p'
activerecord_unittest-#    AND def.adsrc ~ 'nextval\\(\'[^\']*\'::[^\\)]*\\)';
 attname |          ?column?           
---------+-----------------------------
 id      | public.companies_nonstd_seq
(1 row)

11/13/05 01:30:49 changed by bitsweat

(In [2991]) r3008@asus (orig r2978): david | 2005-11-11 01:50:42 -0800

Make sure that legacy db tasks also reference :database for SQLite (closes #2830) [kazuhiko@fdiary.net] r3009@asus (orig r2979): david | 2005-11-11 02:02:44 -0800 Changelogging r3011@asus (orig r2981): bitsweat | 2005-11-11 10:49:01 -0800 Include the Enumerable module in ActiveRecord::Errors. r3012@asus (orig r2982): bitsweat | 2005-11-11 15:45:02 -0800 SQLServer: don't report limits for unsupported field types. Closes #2835. r3014@asus (orig r2984): minam | 2005-11-11 21:09:05 -0800 Make Validations#create! use the current scope

r3015@asus (orig r2985): bitsweat | 2005-11-12 03:59:54 -0800

r4325@asus: jeremy | 2005-11-12 03:57:46 -0800 PostgreSQL: correctly discover custom primary key sequences. PostgreSQL: smarter sequence name defaults, stricter last_insert_id, warn on pk without sequence. Base.reset_sequence_name analogous to reset_table_name (mostly useful for testing). Base.define_attr_method allows nil values. References #2594.

r3017@asus (orig r2987): david | 2005-11-12 08:26:23 -0800 Pulled auto-starting browser: More pain than gain r3019@asus (orig r2989): bitsweat | 2005-11-12 14:28:38 -0800 PostgreSQL: min_messages = warning for AR tests. r3020@asus (orig r2990): bitsweat | 2005-11-12 17:12:48 -0800 SQLite: the clone_structure_to_test Rake task should always use the test environment. References #2846.

11/13/05 03:20:57 changed by anonymous

Removing the regex got the query to succeed:

activerecord_unittest=> SELECT attr.attname, (name.nspname || '.' || split_part(def.adsrc, '\'', 2)) FROM pg_class       t JOIN pg_namespace   name ON (t.relnamespace = name.oid) JOIN pg_attribute   attr ON (t.oid = attrelid) JOIN pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum) JOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1]) WHERE t.oid = 'companies'::regclass AND cons.contype = 'p';
 attname |          ?column?
---------+-----------------------------
 id      | public.companies_nonstd_seq
(1 row)

Here's a query showing the format of adsrc in my database:

activerecord_unittest=> select adsrc from pg_attrdef where adsrc ~ 'compan';
                       adsrc
---------------------------------------------------
 nextval(('companies_nonstd_seq'::text)::regclass)
(1 row)

activerecord_unittest=> select adsrc from pg_attrdef where adsrc ~ 'accoun';                 adsrc
--------------------------------------
 nextval('accounts_id_seq'::regclass)
(1 row)

Regards, Blair

08/04/06 03:44:01 changed by anonymous

Rails Trac

Ticket Navigation

Ticket #2594 (closed defect: fixed)

[PATCH] Postgresql fails to determine correct sequence and primary key names

Description

Attachments

Change History

10/24/05 23:55:36 changed by blair

11/07/05 17:23:00 changed by bitsweat

11/07/05 19:22:17 changed by blair

11/07/05 22:20:28 changed by blair

11/08/05 13:35:50 changed by meadow.nnick@gmail.com

11/08/05 15:44:22 changed by blair

11/08/05 15:45:02 changed by blair

11/08/05 20:50:51 changed by bitsweat

11/08/05 20:54:18 changed by bitsweat

11/08/05 22:25:00 changed by bitsweat

11/11/05 16:26:54 changed by blair

11/12/05 12:00:01 changed by bitsweat

11/12/05 12:02:57 changed by bitsweat

11/12/05 19:08:33 changed by blair

11/12/05 19:09:19 changed by blair

11/12/05 21:56:57 changed by bitsweat

11/12/05 22:09:01 changed by blair

11/12/05 22:11:29 changed by blair

11/12/05 22:12:34 changed by blair

11/12/05 22:23:14 changed by bitsweat

11/13/05 00:04:23 changed by blair

11/13/05 00:57:03 changed by bitsweat

11/13/05 01:00:26 changed by bitsweat

11/13/05 01:30:49 changed by bitsweat

11/13/05 02:02:18 changed by blair

11/13/05 02:05:45 changed by blair

11/13/05 02:32:57 changed by bitsweat

11/13/05 03:20:57 changed by anonymous

11/13/05 03:43:39 changed by bitsweat

11/13/05 06:32:58 changed by bitsweat

11/13/05 06:38:38 changed by bitsweat

11/13/05 18:39:23 changed by blair

11/14/05 00:50:15 changed by bitsweat

08/04/06 03:44:01 changed by anonymous