New Features¶ ↑

• An integer64 extension has been added, which treats the Integer class as a generic 64-bit integer type. Sequel’s default behavior for Integer is to use the integer type, which on most databases is a 32-bit type.

  This affects all internal use of the Integer class as a generic database type, so that methods like primary_key and foreign_key also default to using a 64-bit integer type when using this extension.

• When using PostgreSQL 10+, you can use the :identity option when creating columns to create identity columns:

  DB.create_table(:table){Integer :id, identity: true}
  # CREATE TABLE "table" ("id" integer GENERATED BY DEFAULT AS IDENTITY)
  

  If you want to disallow using a user provided value when inserting, or updating you can use a value of :always:

  DB.create_table(:table){Integer :id, identity: :always}
  # CREATE TABLE "table" ("id" integer GENERATED ALWAYS AS IDENTITY)
  

• Database#convert_serial_to_identity has been added on PostgreSQL 10.2+. This method can convert existing serial columns to identity columns in most cases, but it currently requires superuser permissions as it modifies the system tables directly.

• Dataset#overriding_system_value and overriding_user_value are now supported on PostgreSQL to work with identity columns. You can use overriding_system_value to force the use of a user provided value for identity columns that are GENERATED ALWAYS, and you can use overriding_user_value to ignore any user value for identity columns and always use the next entry in the sequence.

Other Improvements¶ ↑

• On PostgreSQL 10.2+, identity columns are now used instead of serial columns as the default for auto incrementing primary keys:

  DB.create_table(:table){primary_key :id}
  
  # Sequel 5.7.0+ and PostgreSQL 10.2+
  # CREATE TABLE "table" ("id" integer
  #   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY)
  
  # Older Sequel version or older PostgreSQL version
  # CREATE TABLE "table" ("id" serial PRIMARY KEY)
  

  Identity columns fix many issues that serial columns have, in addition to being the SQL standard way to support auto incrementing columns.

• PostgreSQL identity columns are now correctly recognized and the :auto_increment schema entry is now populated for them.

• Dataset#with_sql_{all,each,first,single_value} now use a cached dataset to avoid clobbering the current dataset’s columns. Previously, the clobbering of the current dataset’s columns was documented and the method warned against using SQL with different columns. These methods are now safe to use in such cases, but will not have the same performance advantages if the current dataset is not cached.

• On ruby 2.1+, Sequel now uses Process::CLOCK_MONOTONIC when performing elapsed time calculations so that it is not affected by modifications to the system’s time.

• In the postgres adapter, prepared statement errors related to changing types are now treated as disconnect errors. While they are not technically disconnect errors, treating them as such will in general reduce the total number of exceptions generated from 1 per affected statement per connection to 1 per connection.

• In the pg_array_associations plugin, the array_type for pg_array_to_many and many_to_pg_array association reflections is now always the scalar type for the array (e.g. integer). Previously, the array type (e.g. integer[]) was used in some cases. This didn’t previously result in issues as PostgreSQL considers integer[] the same type as integer[].

• In the pg_array_associations plugin, the many_to_pg_array association remove_all_* method now uses the appropriate cast to work for non-integer array types such as bigint[].

• Database#server_version on PostgreSQL 10.1+ now works correctly when the connection does not support the server_version method. Now the server_version_num database setting is always used to ensure consistent behavior across adapters.

• In the jdbc/oracle adapter, temporary clobs are now manually freed to prevent a memory leak, in line with the Oracle JDBC driver recommendations.

• The Sequel <4 release notes and changelog are no longer shipped with the gem, decreasing the size of the gem by 20%.

Backwards Compatibility¶ ↑

• The switch to using identity columns instead of serial columns by default on PostgreSQL 10.2+ may break backwards compatibilty in some situations, such as code that relies on what are generally considered bugs in serial columns, such as CREATE TABLE LIKE using the same sequence for the column in both the existing table and the new table, or that dropping the default value for the column does not drop the related sequence.