10

I want to do an upsert. Rails doesn't support this yet. The query is something like this:

INSERT INTO foos (thing_id, bar_id) VALUES (1, 2)
ON CONFLICT (thing_id, bar_id) DO NOTHING

I can easily do this with self.class.connection.execute or exec_insert. But I want to also leverage prepared statements. I thought I can do this like so:

thing_id = ActiveRecord::Relation::QueryAttribute.new("thing_id", thing.id, ActiveRecord::Type::Integer.new)
bar_id = ActiveRecord::Relation::QueryAttribute.new("bar_id", id, ActiveRecord::Type::Integer.new)

self.class.connection.exec_insert(<<-SQL, nil, [thing_id, bar_id])
  INSERT INTO foos (thing_id, bar_id) VALUES ($1, $2)
  ON CONFLICT (thing_id, bar_id) DO NOTHING
SQL

But when I experimented with this it seems that a prepared statement is not created.

I tried this style:

query = <<-SQL
  INSERT INTO foos (thing_id, bar_id) VALUES ($1, $2)
  ON CONFLICT (thing_id, bar_id) DO NOTHING
SQL

connection = ActiveRecord::Base.connection.raw_connection
connection.prepare('some_name', query)
st = connection.exec_prepared('some_name', [ thing.id, id ])

And it does create a prepared statement. BUT, the second time it is run, postgres complains about creating a prepared statement with the same name. So, rails' prepared statement management happens at a level above this, and I'm unable to leverage it here. I would have to manually manage it here. I'm not confident I could do this properly, and even if I could it would be verbose.

execute and friends do not accept the ("foo=?", 1) api that where accepts.

Is there any way to leverage rails' automagic prepared statement management for raw SQL?

John Bachir
  • 22,495
  • 29
  • 154
  • 227
  • 1
    This can help https://stackoverflow.com/questions/13805627/prepared-statement-on-postgresql-in-rails – MrYoshiji Apr 20 '18 at 18:54
  • Thanks! So, it looks like if I run `connection.prepare(...)` twice with the same params, postgres complains that it's already made. So whatever smart tracking rails does so a prepared statement is only made once is not leveraged. i could manage this manually but the point of my question is to use as much of the rails stack as possible. – John Bachir Apr 20 '18 at 19:23
  • 1
    I don't know if Rails or the PG gem has a built-in solution for you. Buuuut you can manually `DEALLOCATE` the prep. statement name: https://stackoverflow.com/questions/13967333/prepared-statements-already-exists – MrYoshiji Apr 20 '18 at 20:39
  • Right but then I won't be taking advantage of the prepared statement, if I always delete it and recreate it. – John Bachir Apr 21 '18 at 01:22
  • Would it work to prepare the statement in a migration, and then your use of #exec_prepared can assume that the statement is always available? – dcorking May 14 '18 at 14:29
  • No it wouldn't. From the docs: "Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use." https://www.postgresql.org/docs/current/static/sql-prepare.html Sounds like you need to deallocate. You still get some security advantages with a prepared statement. – dcorking May 14 '18 at 14:34

1 Answers1

5

#exec_query accepts an optional keyword argument, prepare, which defaults to false. Take a look at the method definition (and here).

Given the following table definition:

CREATE TABLE foos (
    thing_id INT,
    bar_id INT,
    UNIQUE (thing_id, bar_id)
);

I tested the following:

conn = ActiveRecord::Base.connection
stmt = 'INSERT INTO foos (thing_id, bar_id) VALUES ($1, $2) ' \
       'ON CONFLICT (thing_id, bar_id) DO NOTHING'

# "Old" bind parameters
binds = [[nil, 1], [nil, 2]]
conn.exec_query stmt, 'SQL', binds, prepare: true
conn.exec_query stmt, 'SQL', binds, prepare: true

# "New" bind parameters
type  = ActiveModel::Type::Integer.new limit: 4
binds = [
  ActiveRecord::Relation::QueryAttribute.new('thing_id', 1, type),
  ActiveRecord::Relation::QueryAttribute.new('bar_id',   2, type)
]
conn.exec_query stmt, 'SQL', binds, prepare: true
conn.exec_query stmt, 'SQL', binds, prepare: true

Both of those bind param styles worked for me with ActiveRecord 5.2.0 and pg 1.0.0. Multiple INSERT statements using the same values ultimately result in only one row being inserted with no errors raised. I checked the Postgres logs, and there was only one "parse" (before the first INSERT), so it appears that the prepared statement mechanism is being used correctly.

Steve
  • 6,618
  • 3
  • 44
  • 42
  • I wonder why there is no official docs on that. Also it seems like one can get by with `ActiveRecord::Relation::QueryAttribute.new(nil, 1, ActiveRecord::Type::Value.new)` alike. At least those work for me with `#select_value` while using integer and string parameters. – mlt Jun 12 '20 at 02:57