I was a bit surprised that using a RETURNING clause requires explicit consumption of the resulting statement object. Example using Python:
conn = sqlite3.connect(':memory:')
conn.execute('create table reg(id integer primary key, k text)')
cur = conn.execute('insert into reg (k) values (?), (?), (?) returning id',
('k1', 'k2', 'k3'))
print(next(cur)) # First result row.
cur = conn.execute('select * from reg')
print(cur.fetchall())
The above code prints out:
(1,) <-- the first id inserted
[(1, 'k1'), (2, 'k2')] <-- first and second row
The python driver has called sqlite3_step twice under-the-hood for our INSERT, and as a result only the first 2 rows are inserted.
My expectation, and the way postgres seems to work, is that the INSERT is performed regardless of whether we consume all the returned rows. I want to raise this now, so if this is expected behavior, I can plan how to work around it in my library code.