Any PostgreSQL guru's out there?

mook

I'm writing a few DAOs in PHP with multiple database support for a script I will be releasing soon and I noticed that PostgreSQL doesn't have an easy method for fetching the last insertion ID. Although I have an idea as a work around ( not sure if PostgreSQL would support something like this ):


function get_insert_id ($table, $id)
{

$result =  pg_fetch_array ($this->query ("SELECT * FROM {$table} ORDER BY {$id} DESC LIMIT 1"));

return $result[$id]; 

}

I think something like this would work, but the problem is that you have to specify the name of the table and the name of the id field ( if there is any )and doing so would break the API I am trying to achieve.

I have created a DB super class in PHP to set a standard API and so far I have MySQL support. I MySQL all I have to use is the following function:


function get_insert_id () 
{ 

    return mysql_insert_id ($this->DBlink); 

}

You don't need to specify anything with the above. I was wondering if there is a simple way to achieve what I want while still keeping a standard.

I think something like this will work, but I'm not quite sure as to would I should put between the '<>':

'SELECT last_value FROM <your sequence name here>'

Help? 😕

toma42

You're relying on auto increment-type columns. This is bad database design, especially if you're writing something that's susposed to work on multiple databases.

Instead, use sequences. pear::db nextId() function would be good code to copy if you're writng your own from scratch instead of using pear.

best cross-db method:
//pseudocode
$key = db::nextId('table_seq');
$sql = 'insert into table (key, blah) values ($key, 'blah')';

and you will already know $key is the same as 'last insert id'

ultraslacker

You can also use the postgresql sequence functions.

SELECT currval('my_sequence');
Returns the current returned value for my_sequence for that connection. This is equivalent to last insert id.

SELECT nextval('my_sequence');
Increments my_sequence and returns value.

mook

Or I could just do:


SELECT MAX(id) AS 'maxId' FROM tableName

I'm retarded.

ultraslacker

max(id) isn't always a safe method, especially with concurrent connections, as it is feasible that max(id) will return same value to different processes. This will not happen using currval or nextval or the pear id functions.

Sxooter

Two ways to do it, use pg_last_oid and then run a select id from table where oid=$oid OR just fetch the sequence output yourself right before the insert and do it by hand.

select nextval('seqnametoserialfield');
insert into table (info,id) values ('$text',$id);

DO NOT do select max(id) as that is possibly the most likely to cause your data to get goofed up. It's a race condition extraordinaire.

C1: insert into table values ('infohere');
C2: insert into table values ('moreinfo');
C1: select max(id) from table;
C2: select max(id) from table;
C1: insert into table2 values ('infotogowithc1sprevious insert',id1);
C2: insert into table2 values ('infotogowithc2sprevious insert',id2);

The problem being that C1 is now inserting data against the record C2 inserted, not the master record C1 inserted.

The only way to make the max() work safely is to do the whole thing inside of a serializable mode transaction which sucks for performance.

doing it the way monk proposes is like walking around with a gun pointed at your foot. It's not guaranteed you'll shoot yourself in the foot, but each day you do it, the chances go up.

mook

Hmmm, ok, that makes much more sense. Thanks for clarifying. Sorry if I'm a little ignorant when it comes to psql, I've only been dabbling in it for a day or so. :eek:

Sxooter

Don't worry about being ignorant, worry about being stupid 🙂 You can outgrow ignorance, but stupid tends to stick around a lot longer.

If you get a chance, look for a good database design book. Celko makes some nice stuff, and Date's book on database design is great, if a bit dated in parts.