PostgreSQL 10 : Each sequence does not have `increment_by` column, need to use `pg_sequences`

[yahonda]

Environment:

$ more /etc/redhat-release
Fedora release 25 (Twenty Five)

• Enable PostgreSQL 10 repository https://yum.postgresql.org/repopackages.php#pg10

$ rpm -qa |grep postgresql10
postgresql10-contrib-10.0-20170414_1PGDG.f25.2.x86_64
postgresql10-10.0-20170414_1PGDG.f25.2.x86_64
postgresql10-devel-10.0-20170414_1PGDG.f25.2.x86_64
postgresql10-server-10.0-20170414_1PGDG.f25.2.x86_64
postgresql10-libs-10.0-20170414_1PGDG.f25.2.x86_64
$

• Ruby and bundler version

$ ruby -v
ruby 2.4.1p111 (2017-03-22 revision 58053) [x86_64-linux]

$ bundle show |grep pg
  * pg (0.19.0)

Steps to reproduce:

$ git clone https://github.com/rails/rails.git
$ cd rails/activerecord
$ bundle exec rake db:postgresql:build
$ ARCONN=postgresql bundle exec ruby -w -Itest test/cases/primary_keys_test.rb -n test_read_attribute_with_custom_primary_key

Results:

$ ARCONN=postgresql bundle exec ruby -w -Itest test/cases/primary_keys_test.rb -n test_read_attribute_with_custom_primary_key
/home/yahonda/git/rails/activesupport/lib/active_support/core_ext/enumerable.rb:20: warning: method redefined; discarding old sum
Using postgresql
Run options: -n test_read_attribute_with_custom_primary_key --seed 6500

# Running:

E

Finished in 0.050451s, 19.8212 runs/s, 0.0000 assertions/s.

  1) Error:
PrimaryKeysTest#test_read_attribute_with_custom_primary_key:
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "increment_by" does not exist
LINE 1: ...pics_id_seq"', (SELECT COALESCE(MAX("id")+(SELECT increment_...
                                                             ^
:               SELECT setval('"public"."topics_id_seq"', (SELECT COALESCE(MAX("id")+(SELECT increment_by FROM "public"."topics_id_seq"), (SELECT min_value FROM "public"."topics_id_seq")) FROM "topics"), false)

    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:613:in `async_exec'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:613:in `block (2 levels) in exec_no_cache'
    /home/yahonda/git/rails/activesupport/lib/active_support/dependencies/interlock.rb:46:in `block in permit_concurrent_loads'
    /home/yahonda/git/rails/activesupport/lib/active_support/concurrency/share_lock.rb:185:in `yield_shares'
    /home/yahonda/git/rails/activesupport/lib/active_support/dependencies/interlock.rb:45:in `permit_concurrent_loads'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:612:in `block in exec_no_cache'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:580:in `block (2 levels) in log'
    /home/yahonda/.rbenv/versions/2.4.1/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block in log'
    /home/yahonda/git/rails/activesupport/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:571:in `log'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:611:in `exec_no_cache'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `execute_and_clear'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:182:in `select_result'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:11:in `select_value'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb:285:in `reset_pk_sequence!'
    /home/yahonda/git/rails/activerecord/lib/active_record/fixtures.rb:578:in `block (3 levels) in create_fixtures'
    /home/yahonda/git/rails/activerecord/lib/active_record/fixtures.rb:559:in `each'
    /home/yahonda/git/rails/activerecord/lib/active_record/fixtures.rb:559:in `block (2 levels) in create_fixtures'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:225:in `block in transaction'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb:194:in `block in within_new_transaction'
    /home/yahonda/.rbenv/versions/2.4.1/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb:191:in `within_new_transaction'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:225:in `transaction'
    /home/yahonda/git/rails/activerecord/lib/active_record/fixtures.rb:557:in `block in create_fixtures'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb:41:in `block in disable_referential_integrity_with_alter_constraint'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:225:in `block in transaction'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb:194:in `block in within_new_transaction'
    /home/yahonda/.rbenv/versions/2.4.1/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb:191:in `within_new_transaction'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:225:in `transaction'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb:38:in `disable_referential_integrity_with_alter_constraint'
    /home/yahonda/git/rails/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb:12:in `disable_referential_integrity'
    /home/yahonda/git/rails/activerecord/lib/active_record/fixtures.rb:542:in `create_fixtures'
    /home/yahonda/git/rails/activerecord/lib/active_record/fixtures.rb:1049:in `load_fixtures'
    /home/yahonda/git/rails/activerecord/lib/active_record/fixtures.rb:986:in `setup_fixtures'
    /home/yahonda/git/rails/activerecord/lib/active_record/fixtures.rb:870:in `before_setup'

1 runs, 0 assertions, 0 failures, 1 errors, 0 skips
$

Additional information:

It looks this behavior change is introduced by Add pg_sequence system catalog

• PostgreSQL version

activerecord_unittest=# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 10devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit
(1 row)

• Each sequence does not have increment_by

activerecord_unittest=# select * FROM "public"."topics_id_seq";
 last_value | log_cnt | is_called
------------+---------+-----------
        100 |       0 | t
(1 row)

activerecord_unittest=#

• Now pg_sequences has it

activerecord_unittest=# select increment_by from pg_sequences where schemaname = 'public' and sequencename = 'topics_id_seq';
 increment_by
--------------
            1
(1 row)

activerecord_unittest=#

[pixeltrix]

Do we even need to add increment_by? Just calling setval with the maximum PK value should be enough since nextval will add the increment_by for us.

[yahonda]

increment_by is necessary since setval third argument is false, removing increment_by causes ActiveRecord::RecordNotUnique: PG::UniqueViolation.

$ psql -d test
psql (10devel)
Type "help" for help.

test=# create sequence test_seq;
CREATE SEQUENCE
test=# select * from pg_sequences;
 schemaname | sequencename | sequenceowner | data_type | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value
------------+--------------+---------------+-----------+-------------+-----------+---------------------+--------------+-------+------------+------------
 public     | test_seq     | yahonda       | bigint    |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |
(1 row)

• setval with false

test=# select setval('test_seq', 100, false);
 setval
--------
    100
(1 row)

test=# select nextval('test_seq');
 nextval
---------
     100
(1 row)

test=#

• nextval returns 100. which causes ActiveRecord::RecordNotUnique: PG::UniqueViolation as below.

$ ARCONN=postgresql bundle exec ruby -w -Itest test/cases/primary_keys_test.rb -n test_integer_key
/home/ubuntu/rails/activesupport/lib/active_support/core_ext/enumerable.rb:20: warning: method redefined; discarding old sum
Using postgresql
Run options: -n test_integer_key --seed 51286

# Running:

E

Finished in 0.101177s, 9.8837 runs/s, 29.6511 assertions/s.

  1) Error:
PrimaryKeysTest#test_integer_key:
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "topics_pkey"
DETAIL:  Key (id)=(5) already exists.
: INSERT INTO "topics" ("title", "author_email_address", "written_on", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:613:in `async_exec'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:613:in `block (2 levels) in exec_no_cache'
    /home/ubuntu/rails/activesupport/lib/active_support/dependencies/interlock.rb:46:in `block in permit_concurrent_loads'
    /home/ubuntu/rails/activesupport/lib/active_support/concurrency/share_lock.rb:185:in `yield_shares'
    /home/ubuntu/rails/activesupport/lib/active_support/dependencies/interlock.rb:45:in `permit_concurrent_loads'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:612:in `block in exec_no_cache'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:580:in `block (2 levels) in log'
    /usr/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block in log'
    /home/ubuntu/rails/activesupport/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:571:in `log'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:611:in `exec_no_cache'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `execute_and_clear'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:103:in `exec_query'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:92:in `exec_insert'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_insert'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:123:in `insert'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:17:in `insert'
    /home/ubuntu/rails/activerecord/lib/active_record/relation.rb:61:in `insert'
    /home/ubuntu/rails/activerecord/lib/active_record/persistence.rb:578:in `_create_record'
    /home/ubuntu/rails/activerecord/lib/active_record/counter_cache.rb:178:in `_create_record'
    /home/ubuntu/rails/activerecord/lib/active_record/locking/optimistic.rb:76:in `_create_record'
    /home/ubuntu/rails/activerecord/lib/active_record/attribute_methods/dirty.rb:296:in `_create_record'
    /home/ubuntu/rails/activerecord/lib/active_record/callbacks.rb:340:in `block in _create_record'
    /home/ubuntu/rails/activesupport/lib/active_support/callbacks.rb:131:in `run_callbacks'
    /home/ubuntu/rails/activesupport/lib/active_support/callbacks.rb:825:in `_run_create_callbacks'
    /home/ubuntu/rails/activerecord/lib/active_record/callbacks.rb:340:in `_create_record'
    /home/ubuntu/rails/activerecord/lib/active_record/timestamp.rb:95:in `_create_record'
    /home/ubuntu/rails/activerecord/lib/active_record/persistence.rb:555:in `create_or_update'
    /home/ubuntu/rails/activerecord/lib/active_record/callbacks.rb:336:in `block in create_or_update'
    /home/ubuntu/rails/activesupport/lib/active_support/callbacks.rb:131:in `run_callbacks'
    /home/ubuntu/rails/activesupport/lib/active_support/callbacks.rb:825:in `_run_save_callbacks'
    /home/ubuntu/rails/activerecord/lib/active_record/callbacks.rb:336:in `create_or_update'
    /home/ubuntu/rails/activerecord/lib/active_record/persistence.rb:154:in `save!'
    /home/ubuntu/rails/activerecord/lib/active_record/validations.rb:50:in `save!'
    /home/ubuntu/rails/activerecord/lib/active_record/attribute_methods/dirty.rb:43:in `save!'
    /home/ubuntu/rails/activerecord/lib/active_record/transactions.rb:313:in `block in save!'
    /home/ubuntu/rails/activerecord/lib/active_record/transactions.rb:384:in `block in with_transaction_returning_status'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:225:in `block in transaction'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb:194:in `block in within_new_transaction'
    /usr/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb:191:in `within_new_transaction'
    /home/ubuntu/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:225:in `transaction'
    /home/ubuntu/rails/activerecord/lib/active_record/transactions.rb:210:in `transaction'
    /home/ubuntu/rails/activerecord/lib/active_record/transactions.rb:381:in `with_transaction_returning_status'
    /home/ubuntu/rails/activerecord/lib/active_record/transactions.rb:313:in `save!'
    /home/ubuntu/rails/activerecord/lib/active_record/suppressor.rb:46:in `save!'
    test/cases/primary_keys_test.rb:49:in `block in test_integer_key'
    /home/ubuntu/rails/activesupport/lib/active_support/testing/assertions.rb:30:in `assert_nothing_raised'
    test/cases/primary_keys_test.rb:49:in `test_integer_key'

1 runs, 3 assertions, 0 failures, 1 errors, 0 skips
$

Then replacing setval 3rd argument with true, it causes this kind of failure.

$ ARCONN=postgresql bundle exec ruby -w -Itest test/cases/fixtures_test.rb -n test_resets_to_min_pk_with_default_pk_and_sequence
/home/ubuntu/rails/activesupport/lib/active_support/core_ext/enumerable.rb:20: warning: method redefined; discarding old sum
Using postgresql
Run options: -n test_resets_to_min_pk_with_default_pk_and_sequence --seed 54529

# Running:

F

Finished in 0.078813s, 12.6883 runs/s, 12.6883 assertions/s.

  1) Failure:
FixturesResetPkSequenceTest#test_resets_to_min_pk_with_default_pk_and_sequence [test/cases/fixtures_test.rb:392]:
Sequence reset for accounts failed..
Expected: 1
  Actual: 2

1 runs, 1 assertions, 1 failures, 0 errors, 0 skips
$

[yahonda]

For references:

• increment_by introduced by this commit

• It used to add 1 instead of increment_by and setval third argument set to false f5f7beb

[yahonda]

Also, we will need to address the later part of this sql statement.

activerecord_unittest=# SELECT min_value FROM "public"."topics_id_seq";
ERROR:  column "min_value" does not exist
LINE 1: SELECT min_value FROM "public"."topics_id_seq";
               ^
activerecord_unittest=#

[pixeltrix]

@yahonda could we not set the third argument based on whether the table is empty or not?

[yahonda]

Thanks for the suggestion. I have opened #28789 which supports both PostgreSQL 10 and PostgreSQL 9.x.

[yahonda]

Closed since #28864 has been merged to master.

[tddrmllr]

Is there a fix for this in Rails 4.2?

[BartlomiejSkwira]

If anyone is on legacy Rails 3.2 here is a version that worked for me and is just slightly modified of what @replaid posted (I just fixed the module/class hierarchy). It landed in my config/initializers/backport_pg_10_support_to_rails_3.rb

require 'active_record/connection_adapters/postgresql_adapter'

#
# Monkey-patch the refused Rails 4.2 patch at https://github.com/rails/rails/pull/31330
# Changed the module/class hierarchy to work with Rails 3.2
# Based on solution for Rails 4.2 https://github.com/rails/rails/issues/28780#issuecomment-354868174
#
# Updates sequence logic to support PostgreSQL 10.
#

module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter
      # Resets the sequence of a table's primary key to the maximum value.
      def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc:
        unless pk and sequence
          default_pk, default_sequence = pk_and_sequence_for(table)

          pk ||= default_pk
          sequence ||= default_sequence
        end

        if @logger && pk && !sequence
          @logger.warn "#{table} has primary key #{pk} with no default sequence"
        end

        if pk && sequence
          quoted_sequence = quote_table_name(sequence)
          max_pk = select_value("SELECT MAX(#{quote_column_name pk}) FROM #{quote_table_name(table)}")
          if max_pk.nil?
            if postgresql_version >= 100000
              minvalue = select_value("SELECT seqmin FROM pg_sequence WHERE seqrelid = #{quote(quoted_sequence)}::regclass")
            else
              minvalue = select_value("SELECT min_value FROM #{quoted_sequence}")
            end
          end

          select_value <<-end_sql, 'SCHEMA'
          SELECT setval(#{quote(quoted_sequence)}, #{max_pk ? max_pk : minvalue}, #{max_pk ? true : false})
          end_sql
        end
      end
    end
  end
end

I was a bit skeptical and wondered if the method that is fixed (reset_pk_sequence) changed a lot between versions 3.2 and 5.1.1 (the version prior to the fix from 30 May 2017). But the reset_pk_sequence didn't change too much. Here is a diff:

So I think it should work fine