[rt-users] Performance on PostgreSQL

[Joby Walker]

Vivek Khera wrote:
> 
> On Oct 13, 2006, at 6:48 PM, Joby Walker wrote:
> 
>> OIDs were used to get the id of the last insert.  The was abandoned 
>> because of pgsql 8.1, but it was a horrible way to get the last id 
>> anyway.  Current versions of SB insert the row and then get the 
>> current value of the sequence associated with that table.  This is 
>> much faster and *should* be safe.
> 
> Does it actually query the sequence or does it just pull the last insert 
> id from the connection's return status?  if the former, then it is not 
> multi-thread safe, if the latter then it is faster than your method of 
> pulling a sequence then doing insert (one trip to DB vs. two).
> 

As was pointed out to me the current value function returns the last 
value used for that connection, so from the pgsql side it is multithread 
safe -- but I don't know how using a connection pooling system like 
SQLRelay impacts things (because we might use a sqlrelay in the future 
is one of the reasons I modified our instance of SB).

Pgsql returns the number of rows inserted or if the count is one and the 
table has oids it returns the oid of the row -- it doesn't return the PK 
of the table.  This is why SB previously made the OID query.

http://www.postgresql.org/docs/8.1/interactive/sql-insert.html

jbw