Class: Tina4::Drivers::PostgresDriver

Inherits:
Object
  • Object
show all
Includes:
Tina4::DatabaseAdapter, SchemaSplit
Defined in:
lib/tina4/drivers/postgres_driver.rb

Constant Summary

Constants included from Tina4::DatabaseAdapter

Tina4::DatabaseAdapter::CONNECT_TIMEOUT_SLACK_SECONDS, Tina4::DatabaseAdapter::CONNECT_TIMEOUT_VAR, Tina4::DatabaseAdapter::CONTRACT, Tina4::DatabaseAdapter::DEFAULT_CONNECT_TIMEOUT_SECONDS

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from SchemaSplit

#split_schema

Methods included from Tina4::DatabaseAdapter

bounding_connect, connect_timed_out!, connect_timeout_seconds, connect_timeout_whole_seconds, implemented_by?

Instance Attribute Details

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



18
19
20
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 18

def connection
  @connection
end

Instance Method Details

#affected_rows ⇒ Object

Rows changed by the most recent INSERT/UPDATE/DELETE on this connection.

Feeds Database#update/delete's DatabaseResult.affected_rows. The driver exposed NO such method, so write_affected fell through to its default of 0 and an UPDATE that really changed a row reported affected_rows = 0 — indistinguishable from "matched nothing". Parity with SQLite (connection.changes), MySQL (stmt.affected_rows) and the Python master (cursor.rowcount).



98
99
100
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 98

def affected_rows
  @affected_rows.to_i
end

#apply_limit(sql, limit, offset = 0) ⇒ Object

NEW LINE, not a space — appended inline the clause lands inside a trailing -- comment and the engine ignores it (see the note on Drivers::SqliteDriver#apply_limit).



198
199
200
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 198

def apply_limit(sql, limit, offset = 0)
  "#{sql}\nLIMIT #{limit} OFFSET #{offset}"
end

#begin_transaction ⇒ Object 



202
203
204
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 202

def begin_transaction
  @connection.exec("BEGIN")
end

#close ⇒ Object 



57
58
59
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 57

def close
  @connection&.close
end

#columns(table_name) ⇒ Object 



231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 231

def columns(table_name)
  # v3.13.14 (#48): honour a schema-qualified name; default to public.
  schema, tbl = split_schema(table_name)
  schema ||= "public"
  # :primary_key was hardcoded false, so Database#primary_key introspected
  # NOTHING on PostgreSQL. The filterless-write guard (feature 4) reads it,
  # so `update(table, data)` keyed on the primary key in `data` raised
  # "update requires a filter or the complete primary key in the data"
  # against every PostgreSQL table. Port the Python master's LEFT JOIN so
  # the cross-engine columns() contract (#48) actually holds here — the
  # subquery yields every column of the PK, so a COMPOSITE key reports
  # true on each of its columns, not just the first.
  sql = <<~SQL
    SELECT c.column_name, c.data_type, c.is_nullable, c.column_default,
           CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END AS is_primary
    FROM information_schema.columns c
    LEFT JOIN (
      SELECT ku.column_name
      FROM information_schema.table_constraints tc
      JOIN information_schema.key_column_usage ku
        ON tc.constraint_name = ku.constraint_name
       AND tc.table_schema = ku.table_schema
      WHERE tc.table_name = $1 AND tc.table_schema = $2
        AND tc.constraint_type = 'PRIMARY KEY'
    ) pk ON c.column_name = pk.column_name
    WHERE c.table_name = $3 AND c.table_schema = $4
    ORDER BY c.ordinal_position
  SQL
  rows = execute_query(sql, [tbl, schema, tbl, schema])
  rows.map do |r|
    {
      name: r[:column_name],
      type: r[:data_type],
      nullable: r[:is_nullable] == "YES",
      default: r[:column_default],
      # The result type map decodes bool to true/false, but stay tolerant
      # of the raw "t"/"f" text form if the map could not be built.
      primary_key: r[:is_primary] == true || r[:is_primary] == "t"
    }
  end
end

#commit ⇒ Object 



206
207
208
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 206

def commit
  @connection.exec("COMMIT")
end

#connect(connection_string, username: nil, password: nil) ⇒ Object 



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 20

def connect(connection_string, username: nil, password: nil)
  begin
    require "pg"
  rescue LoadError
    raise LoadError,
          "The 'pg' gem is required for PostgreSQL connections. Install one of:\n" \
          "    bundle add pg     # if your project uses Bundler\n" \
          "    gem install pg    # bare driver"
  end
  url = connection_string
  if username || password
    uri = URI.parse(url)
    uri.user = username if username
    uri.password = password if password
    url = uri.to_s
  end
  # libpq's OWN connect_timeout, in whole seconds. MEASURED: it bounds the
  # entire connect INCLUDING the startup handshake - 3.01s against a
  # TCPServer that accepts and never replies, where the same connect with
  # no bound sat past 20s and needed SIGKILL. An operator who spelled
  # connect_timeout in the URL themselves keeps their value.
  seconds = Tina4::DatabaseAdapter.connect_timeout_whole_seconds
  seconds = nil if url.include?("connect_timeout=")
  # Host/port for the timeout message only. A libpq keyword/value conninfo
  # string is not a URL and simply yields nil here, which is fine.
  target = begin
    URI.parse(url)
  rescue URI::Error
    nil
  end
  Tina4::DatabaseAdapter.bounding_connect(target&.host, target&.port || 5432) do
    @connection = seconds ? PG.connect(url, connect_timeout: seconds) : PG.connect(url)
  end
  apply_result_type_map(@connection)
  @connection
end

#count_subquery_alias ⇒ Object

Postgres, MySQL, MSSQL and ODBC all REQUIRE a name for a derived table, so the COUNT probe in Database#count_probe wraps as FROM (sql) AS _count_query. SQLite and Firebird do not define this and get no alias - Firebird rejects AS in that position.



12
13
14
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 12

def count_subquery_alias
  "_count_query"
end

#execute(sql, params = []) ⇒ Object 



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 72

def execute(sql, params = [])
  # Issue #256: a bare INSERT run through execute() (not #insert, so no
  # RETURNING captured) must NOT let a previously-captured RETURNING id
  # leak into a later last_insert_id() — that would surface a stale id
  # (e.g. a UUID string from an earlier db.insert) for this new write.
  # Clear the cache so last_insert_id falls back to the lastval() probe,
  # which is the correct source for a sequence-backed bare INSERT.
  @last_returning_id = nil if sql.lstrip[0, 6].upcase == "INSERT"
  converted_sql = convert_placeholders(sql)
  result = if params.empty?
             @connection.exec(converted_sql)
           else
             @connection.exec_params(converted_sql, params)
           end
  track_affected(result)
  result
end

#execute_query(sql, params = []) ⇒ Object 



61
62
63
64
65
66
67
68
69
70
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 61

def execute_query(sql, params = [])
  converted_sql = convert_placeholders(sql)
  result = if params.empty?
             @connection.exec(converted_sql)
           else
             @connection.exec_params(converted_sql, params)
           end
  track_affected(result)
  symbolize_result(result)
end

#insert(table, data) ⇒ Object

Issue #256: surface the ACTUAL primary key value an INSERT wrote — including a server-generated UUID — instead of guessing it from a session sequence after the fact.

Before this, Database#insert ran a bare INSERT and then probed last_insert_id (SELECT lastval()). For a UUID PK (id uuid PRIMARY KEY DEFAULT gen_random_uuid()) there is no session sequence, so the probe returned nil — or, worse, a STALE integer left over from an unrelated SERIAL table's nextval() earlier in the same session (a silently WRONG id). The SERIAL integer path was correct only by luck of lastval() pointing at the right sequence.

Fix (mirrors the Python master's INSERT ... RETURNING * and the Node adapter): append RETURNING * and read the generated id back from the returned row. The value is normalised so the SERIAL path keeps returning an Integer while a UUID PK surfaces its real 36-char string. No lastval() probe, so the issue-#38 transaction-abort can't happen on this path at all.

Returns { success: true, last_id: }. last_id is nil only when the table truly has no id column.



123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 123

def insert(table, data)
  columns = data.keys.map(&:to_s)
  placeholders = placeholders(columns.length)
  sql = "INSERT INTO #{table} (#{columns.join(', ')}) VALUES (#{placeholders}) RETURNING *"
  result = execute_query(sql, data.values)
  row = result.is_a?(Array) ? result.first : nil
  id = normalize_returned_id(row)
  # Remember the real id so a follow-up #last_insert_id / db.get_last_id
  # surfaces THIS value (incl. a UUID string) instead of re-probing
  # lastval(), which has no sequence for a UUID PK and would return a
  # stale wrong integer from an unrelated table.
  @last_returning_id = id
  { success: true, last_id: id }
end

#last_insert_id ⇒ Object 



138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 138

def last_insert_id
  # Issue #256: if the most recent write surfaced its real primary key
  # through ``RETURNING *`` (the #insert path), return that — it is the
  # actual id written (a UUID string stays a string, a SERIAL stays an
  # integer), not a guess. Only fall back to the lastval() probe below
  # when nothing has been captured yet (e.g. a bare
  # ``execute("INSERT ...")`` with no RETURNING).
  return @last_returning_id unless @last_returning_id.nil?

  # Issue #38: ``SELECT lastval()`` raises on tables with no sequence
  # (UUID, ULID, hash PKs etc.). The exception itself isn't fatal,
  # but the pg gem marks the whole transaction as aborted, so every
  # subsequent statement on this connection fails with
  # ``PG::InFailedSqlTransaction`` — far away from the real cause.
  #
  # Fix: wrap the probe in a SAVEPOINT. If ``lastval()`` raises, we
  # ROLLBACK TO SAVEPOINT and the outer transaction stays usable;
  # ``last_insert_id`` just returns ``nil`` (same as before for
  # tables without a sequence). On success we RELEASE SAVEPOINT.
  begin
    @connection.exec("SAVEPOINT _t4_lastval_probe")
  rescue PG::Error
    # No active transaction (autocommit/idle) — fall back to a plain
    # probe; psycopg2-style transaction abort can't happen here.
    begin
      result = @connection.exec("SELECT lastval()")
      return result.first["lastval"].to_i
    rescue PG::Error
      return nil
    end
  end

  begin
    result = @connection.exec("SELECT lastval()")
    @connection.exec("RELEASE SAVEPOINT _t4_lastval_probe")
    result.first["lastval"].to_i
  rescue PG::Error
    begin
      @connection.exec("ROLLBACK TO SAVEPOINT _t4_lastval_probe")
      @connection.exec("RELEASE SAVEPOINT _t4_lastval_probe")
    rescue PG::Error
      # If even the rollback fails, there's nothing we can do — the
      # connection is in a state we can't recover. Surface nil so
      # callers don't get a half-set last_id.
    end
    nil
  end
end

#placeholder ⇒ Object 



187
188
189
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 187

def placeholder
  "?"
end

#placeholders(count) ⇒ Object 



191
192
193
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 191

def placeholders(count)
  (1..count).map { |i| "$#{i}" }.join(", ")
end

#rollback ⇒ Object 



210
211
212
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 210

def rollback
  @connection.exec("ROLLBACK")
end

#table_exists?(name) ⇒ Boolean

v3.13.14 (#48): to_regclass resolves a (possibly schema-qualified) relation name and search_path like a FROM clause; nil if absent.

Returns:

  • (Boolean) 


216
217
218
219
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 216

def table_exists?(name)
  rows = execute_query("SELECT to_regclass($1) AS oid", [name.to_s])
  !rows.empty? && !rows[0][:oid].nil?
end

#tables ⇒ Object 



221
222
223
224
225
226
227
228
229
 
# File 'lib/tina4/drivers/postgres_driver.rb', line 221

def tables
  # v3.13.14 (#48): list every user schema; public tables stay bare,
  # others are returned schema-qualified.
  sql = "SELECT schemaname, tablename FROM pg_tables " \
        "WHERE schemaname NOT IN ('pg_catalog', 'information_schema') " \
        "ORDER BY schemaname, tablename"
  rows = execute_query(sql)
  rows.map { |r| r[:schemaname] == "public" ? r[:tablename] : "#{r[:schemaname]}.#{r[:tablename]}" }
end