search

jeremyevans / sequel

Sequel: The Database Toolkit for Ruby
http://sequel.jeremyevans.net
Other
4.97k stars 1.07k forks source link

insert_conflict on id (SQLite 3.45.0) #2130

Closed graf0 closed 7 months ago

graf0 commented 7 months ago

Complete Description of Issue

Hi, I have following tables:

create_table(:_systems) do
  primary_key :id
  String :name, unique: true, null: false
end

create_table(:_nodes) do
  primary_key :id
  foreign_key :_system_id, :_systems, on_delete: :cascade
  String :hostname, null: false
  String :platform
  String :uri, null: false
end

And following models:

class System < Sequel::Model(DB[:_systems])
  one_to_many :nodes, key: :_system_id
  one_to_many :tokens, key: :_system_id
end

class Node < Sequel::Model(DB[:_nodes])
  many_to_one :system, key: :_system_id

  plugin :validation_helpers
  plugin :insert_conflict

  def validate
    super
    validates_presence :hostname
    validates_presence :uri
  end
end

Then I try to upsert node like this:

Node.new(hostname: 'host1', uri: 'ssh://1.1.1.1:22')
.insert_conflict(target: :id, update: {hostname:'host1'})
.save

Idea is - I keep id of node in files somewhere else and I periodically sync files with db. So if id is the same - I'd like to just update hostname if it was changed.

It works first time, but everytime after that if failes with UNIQUE constraint in sqlite3. SQL logs shows, that no ON CONFLICT is generated - see exception

Am I doing something wrong?

I'm using sqlite3 gem version 1.7.1, with sqlite3 library in version 3.45.0 (checked via SQLite3::SQLITE_LOADED_VERSION)

Simplest Possible Self-Contained Example Showing the Bug

No response

Full Backtrace of Exception (if any)

E, [2024-02-06T21:41:18.347532 #24112] ERROR -- : SQLite3::ConstraintException: UNIQUE constraint failed: _nodes.id: INSERT INTO _nodes (hostname, uri, _system_id, id) VALUES ('ad01', 'local:///', 1, 32767) E, [2024-02-06T21:41:18.347630 #24112] ERROR -- : Sequel::ConstraintViolation: SQLite3::ConstraintException: UNIQUE constraint failed: _nodes.id: SELECT * FROM _systems I, [2024-02-06T21:41:18.347680 #24112] INFO -- : (0.000017s) ROLLBACK bundler: failed to load command: dfmd (/usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/bin/dfmd) /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:100:in step': SQLite3::ConstraintException: UNIQUE constraint failed: _nodes.id (Sequel::ConstraintViolation) from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:100:innext' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:125:in each' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:98:into_a' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:98:in block in execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:72:inprepare' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:88:in execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:257:inblock (2 levels) in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/logging.rb:43:in log_connection_yield' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:257:inblock in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:88:in hold' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:insynchronize' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:248:in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:186:inexecute_insert' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:1205:in execute_insert' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:423:ininsert' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1744:in _insert_raw' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1726:in_insert' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1794:in block (2 levels) in _save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1047:inaround_create' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1791:in block in _save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1047:inaround_save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1786:in _save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:inblock (2 levels) in save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:235:in block in transaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:88:inhold' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:in synchronize' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:197:intransaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1954:in checked_transaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:inblock in save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1940:in checked_save_failure' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:insave' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:69:in upsert_node' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:50:inblock in sync_system_nodes_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:50:in each' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:50:insync_system_nodes_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:37:in block (2 levels) in sync_all_nodes_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:36:inchdir' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:36:in block in sync_all_nodes_with_db' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:162:inblock in each' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:421:in block (2 levels) in fetch_rows' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:126:ineach' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:410:in block in fetch_rows' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/database.rb:361:inquery' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:255:in block (2 levels) in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/logging.rb:43:inlog_connection_yield' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:255:in block in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:88:inhold' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:in synchronize' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:248:in_execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:166:in execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:1189:inexecute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:403:in fetch_rows' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:162:ineach' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/plugins.rb:33:in each' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:35:insync_all_nodes_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:12:in block (2 levels) in sync_with_db' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:264:in_transaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:239:in block in transaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:92:inhold' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:in synchronize' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:197:intransaction' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:10:in block in sync_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:9:inchdir' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:9:in sync_with_db' from /home/marsz/code/dfmx-rb/dfmd/exe/dfmd:55:in<top (required)>' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/bin/dfmd:25:in load' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/bin/dfmd:25:in<top (required)>' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli/exec.rb:58:in load' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli/exec.rb:58:inkernel_load' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli/exec.rb:23:in run' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli.rb:451:inexec' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/vendor/thor/lib/thor/command.rb:28:in run' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/vendor/thor/lib/thor/invocation.rb:127:ininvoke_command' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/vendor/thor/lib/thor.rb:527:in dispatch' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli.rb:34:indispatch' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/vendor/thor/lib/thor/base.rb:584:in start' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli.rb:28:instart' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/bundler-2.5.3/exe/bundle:28:in block in <top (required)>' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/friendly_errors.rb:117:inwith_friendly_errors' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/bundler-2.5.3/exe/bundle:20:in <top (required)>' from /usr/lib/rbenv/versions/3.3.0-jemalloc/bin/bundle:25:inload' from /usr/lib/rbenv/versions/3.3.0-jemalloc/bin/bundle:25:in <main>' /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:100:instep': UNIQUE constraint failed: _nodes.id (SQLite3::ConstraintException) from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:100:in next' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:125:ineach' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:98:in to_a' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:98:inblock in execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:72:in prepare' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/litestack-0.4.2/lib/litestack/litedb.rb:88:inexecute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:257:in block (2 levels) in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/logging.rb:43:inlog_connection_yield' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:257:in block in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:88:inhold' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:in synchronize' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:248:in_execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:186:in execute_insert' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:1205:inexecute_insert' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:423:in insert' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1744:in_insert_raw' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1726:in _insert' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1794:inblock (2 levels) in _save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1047:in around_create' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1791:inblock in _save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1047:in around_save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1786:in_save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:in block (2 levels) in save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:235:inblock in transaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:88:in hold' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:insynchronize' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:197:in transaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1954:inchecked_transaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:in block in save' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1940:inchecked_save_failure' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/base.rb:1500:in save' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:69:inupsert_node' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:50:in block in sync_system_nodes_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:50:ineach' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:50:in sync_system_nodes_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:37:inblock (2 levels) in sync_all_nodes_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:36:in chdir' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:36:inblock in sync_all_nodes_with_db' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:162:in block in each' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:421:inblock (2 levels) in fetch_rows' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/resultset.rb:126:in each' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:410:inblock in fetch_rows' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.1-x86_64-linux/lib/sqlite3/database.rb:361:in query' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:255:inblock (2 levels) in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/logging.rb:43:in log_connection_yield' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:255:inblock in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:88:in hold' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:insynchronize' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:248:in _execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:166:inexecute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:1189:in execute' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/adapters/sqlite.rb:403:infetch_rows' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/dataset/actions.rb:162:in each' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/model/plugins.rb:33:ineach' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:35:in sync_all_nodes_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:12:inblock (2 levels) in sync_with_db' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:264:in _transaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:239:inblock in transaction' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/connection_pool/threaded.rb:92:in hold' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/connecting.rb:293:insynchronize' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/sequel-5.77.0/lib/sequel/database/transactions.rb:197:in transaction' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:10:inblock in sync_with_db' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:9:in chdir' from /home/marsz/code/dfmx-rb/dfmd/lib/dfmd/store.rb:9:insync_with_db' from /home/marsz/code/dfmx-rb/dfmd/exe/dfmd:55:in <top (required)>' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/bin/dfmd:25:inload' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/bin/dfmd:25:in <top (required)>' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli/exec.rb:58:inload' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli/exec.rb:58:in kernel_load' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli/exec.rb:23:inrun' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli.rb:451:in exec' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/vendor/thor/lib/thor/command.rb:28:inrun' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/vendor/thor/lib/thor/invocation.rb:127:in invoke_command' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/vendor/thor/lib/thor.rb:527:indispatch' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli.rb:34:in dispatch' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/vendor/thor/lib/thor/base.rb:584:instart' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/cli.rb:28:in start' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/bundler-2.5.3/exe/bundle:28:inblock in <top (required)>' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/3.3.0/bundler/friendly_errors.rb:117:in with_friendly_errors' from /usr/lib/fullstaq-ruby/versions/3.3.0-jemalloc/lib/ruby/gems/3.3.0/gems/bundler-2.5.3/exe/bundle:20:in<top (required)>' from /usr/lib/rbenv/versions/3.3.0-jemalloc/bin/bundle:25:in load' from /usr/lib/rbenv/versions/3.3.0-jemalloc/bin/bundle:25:in

'

SQL Log (if any)

E, [2024-02-06T21:41:18.347532 #24112] ERROR -- : SQLite3::ConstraintException: UNIQUE constraint failed: _nodes.id: INSERT INTO _nodes (hostname, uri, _system_id, id) VALUES ('ad01', 'local:///', 1, 32767) E, [2024-02-06T21:41:18.347630 #24112] ERROR -- : Sequel::ConstraintViolation: SQLite3::ConstraintException: UNIQUE constraint failed: _nodes.id: SELECT * FROM _systems I, [2024-02-06T21:41:18.347680 #24112] INFO -- : (0.000017s) ROLLBACK

Ruby Version

3.3.0

Sequel Version

5.77

jeremyevans commented 7 months ago

In your example, you are using target: :id, which looks wrong as you are not setting the id field (it is autogenerated). If you introduce a conflict with the id field, it is handled correctly

Here's a modified version of what you provided showing things working:

DB.create_table(:_systems) do
  primary_key :id
  String :name, unique: true, null: false
end

DB.create_table(:_nodes) do
  primary_key :id
  foreign_key :_system_id, :_systems, on_delete: :cascade
  String :hostname, null: false
  String :platform
  String :uri, null: false
end

class System < Sequel::Model(DB[:_systems])
  one_to_many :nodes, key: :_system_id
  one_to_many :tokens, key: :_system_id
end

class Node < Sequel::Model(DB[:_nodes])
  many_to_one :system, key: :_system_id

  plugin :validation_helpers
  plugin :insert_conflict

  def validate
    super
    validates_presence :hostname
    validates_presence :uri
  end
end

2.times do
  node = Node.new(hostname: 'host1', uri: 'ssh://1.1.1.1:22')
  node.id = 1
  node.insert_conflict(target: :id, update: {hostname:'host1'}).save
end
p Node.count

Output:

$ ruby bin/sequel -E sqlite:/ t.rb
I, [2024-02-06T12:53:59.452980 #37065]  INFO -- : (0.000406s) PRAGMA foreign_keys = 1
I, [2024-02-06T12:53:59.453160 #37065]  INFO -- : (0.000025s) PRAGMA case_sensitive_like = 1
I, [2024-02-06T12:53:59.454406 #37065]  INFO -- : (0.000351s) CREATE TABLE `_systems` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `name` varchar(255) NOT NULL UNIQUE)
I, [2024-02-06T12:53:59.454788 #37065]  INFO -- : (0.000143s) CREATE TABLE `_nodes` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `_system_id` integer REFERENCES `_systems` ON DELETE CASCADE, `hostname` varchar(255) NOT NULL, `platform` varchar(255), `uri` varchar(255) NOT NULL)
I, [2024-02-06T12:53:59.455394 #37065]  INFO -- : (0.000158s) SELECT sqlite_version()
I, [2024-02-06T12:53:59.455736 #37065]  INFO -- : (0.000114s) PRAGMA table_xinfo('_systems')
I, [2024-02-06T12:53:59.457225 #37065]  INFO -- : (0.000141s) PRAGMA table_xinfo('_nodes')
I, [2024-02-06T12:53:59.461949 #37065]  INFO -- : (0.000113s) BEGIN
I, [2024-02-06T12:53:59.462700 #37065]  INFO -- : (0.000450s) INSERT INTO `_nodes` (`hostname`, `uri`, `id`) VALUES ('host1', 'ssh://1.1.1.1:22', 1) ON CONFLICT (`id`) DO UPDATE SET `hostname` = 'host1' RETURNING *
I, [2024-02-06T12:53:59.462812 #37065]  INFO -- : (0.000041s) COMMIT
I, [2024-02-06T12:53:59.462983 #37065]  INFO -- : (0.000020s) BEGIN
I, [2024-02-06T12:53:59.463279 #37065]  INFO -- : (0.000123s) INSERT INTO `_nodes` (`hostname`, `uri`, `id`) VALUES ('host1', 'ssh://1.1.1.1:22', 1) ON CONFLICT (`id`) DO UPDATE SET `hostname` = 'host1' RETURNING *
I, [2024-02-06T12:53:59.463407 #37065]  INFO -- : (0.000067s) COMMIT
I, [2024-02-06T12:53:59.463707 #37065]  INFO -- : (0.000049s) SELECT count(*) AS 'count' FROM `_nodes` LIMIT 1
1

The 1 at the bottom shows that only one row was added (i.e. the insert conflict when inserting the second row was turned into an update).

graf0 commented 7 months ago

Ok, you are right. I made 2 mistakes:

  1. not assigning id,
  2. I use litedb, not sqlite driver. And litedb seems to ignore insert_conflict even after assigning id

I think I'll open ticket in litestack gem.

Thank you for you help! :)