PG driver returns weird values when result set should be empty

Help & Support
1

I’m getting a very strange behaviour from the pg driver. It’s a dead simple query: SELECT id, other_id FROM t WHERE a = '$1' and b = '$2' I loops over a list of values to see for each value if there’s already a record. The query returns two ids of type Int64 and the second one can be nil. When there is no record, the result set should not have any rows.

values.each do |value|
  result = db.connection.query_one?(query, value.a, value.b, as: {Int64, Int64?})
  next unless result
  id, other_id = result
  process(id, other_id)
end

The query works as expected when it’s executed in isolation. But in the loop, it only works when a record exists. If not, it still returns a result where the second id is nil (as expected), but the first id has a weird value which seems to lie roughly between 58_000 and 63_000. So I’m guessing it might be some kind of special value or something. But it seems to be different every time.

The code runs in a single fiber and I’m sure there is no overlapping queries on the connection.It would be pretty unlikely to result in identical behaviour every time for different value lists (identical apart from the exact value returned as id).

Does anyone have an idea what might be going on?

2

If you can provide a sample code that reproduces the problem I can help debugging it.

3

Oh, it was just a really dumb thing. What I was looking at was actually completely correct behaviour.

While testing only failure cases, I noticed that the return values turned into a sequence which increments by 1. And yes, it matches the current value of the postgres sequence used for generating new ids.

So, there were actually new records correctly inserted but they didn’t show up at the other location because they were only in a transaction and not committed. But this caused an error which rolled back the transaction so it didn’t show up afterwards either. But the sequence counter kept incrementing, that’s what gave it away ;)

Thanks for your offer, though @asterite =)

1 Like