search

jeremyevans / sequel

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

PG::SyntaxError on create_join_table #1996

Closed madmaniak closed 1 year ago

madmaniak commented 1 year ago

Complete Description of Issue

While using pg with sequel on DB.create_join_table(left_id: left_table, right_id: right_table) I receive following error:

ERROR: syntax error at or near "'ProjectID'" (PG::SyntaxError) LINE 1: ... integer NOT NULL REFERENCES "User", PRIMARY KEY ('ProjectID...

When I debug here sequel-5.65.0/lib/sequel/adapters/postgres.rb:171 I receive following sql query being used:

"CREATE TABLE \"Project_User\" (\"ProjectID\" integer NOT NULL REFERENCES \"Project\", \"UserID\" integer NOT NULL REFERENCES \"User\", PRIMARY KEY ('ProjectID', 'UserID'))"

I can fix that query and use successfully with psql by changing last quotes ' to \". I didn't manage to fix it with Sequel though.

Full Backtrace of Exception (if any)

/home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:171:in `exec': ERROR:  syntax error at or near "'ProjectID'" (PG::SyntaxError)
LINE 1: ... integer NOT NULL REFERENCES "User", PRIMARY KEY ('ProjectID...
                                                             ^

    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:171:in `block in execute_query'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/database/logging.rb:43:in `log_connection_yield'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:171:in `execute_query'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel_pg-1.17.1/lib/sequel/extensions/pg_streaming.rb:96:in `execute_query'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:159:in `block in execute'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:136:in `check_disconnect_errors'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:159:in `execute'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:532:in `_execute'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel_pg-1.17.1/lib/sequel/extensions/pg_streaming.rb:49:in `_execute'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:348:in `block (2 levels) in execute'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:555:in `check_database_errors'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:348:in `block in execute'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/connection_pool/threaded.rb:92:in `hold'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/database/connecting.rb:293:in `synchronize'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/postgres.rb:348:in `execute'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/database/query.rb:50:in `execute_dui'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/database/query.rb:43:in `execute_ddl'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/database/schema_methods.rb:702:in `create_table_from_generator'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/database/schema_methods.rb:203:in `create_table'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/adapters/shared/postgres.rb:468:in `create_table'
    from /home/marek/.rbenv/versions/3.2.0/lib/ruby/gems/3.2.0/gems/sequel-5.65.0/lib/sequel/database/schema_methods.rb:121:in `create_join_table'
    from /home/marek/praca/tomek/uv/tools/db/model/migration.rb:39:in `block in <top (required)>'
    from /home/marek/praca/tomek/uv/tools/db/model/migration.rb:36:in `each'
    from /home/marek/praca/tomek/uv/tools/db/model/migration.rb:36:in `<top (required)>'
    from /home/marek/praca/tomek/uv/tools/db/model/call.rb:17:in `require_relative'
    from /home/marek/praca/tomek/uv/tools/db/model/call.rb:17:in `<top (required)>'
    from tools/call.rb:18:in `require'
    from tools/call.rb:18:in `block in <main>'
    from tools/call.rb:17:in `each'
    from tools/call.rb:17:in `<main>'
jeremyevans commented 1 year ago

You are probably providing strings instead of symbols (cannot say definitively because you didn't post a self-contained example.). Ruby strings are generally treated as SQL strings in Sequel, and Ruby symbols are generally treated as SQL identifiers (table/column names). The create_join_table method documentation shows that the keys and values are supposed to be symbols. If that isn't what is happening, can you please post a minimal self-contained example showing the problem?

madmaniak commented 1 year ago

@jeremyevans thanks a lot for a quick answer - that was the case.