Re: default to WITHOUT OIDS?

Neil Conway wrote:

>Folks,
>
>Is it a good idea for CREATE TABLE to default to WITHOUT OIDS, rather
>than WITH OIDS? Naturally, this would (a) be some time in the future
>(7.5, perhaps) and (b) only apply to user tables.
><snip>
>
>The main disadvantage I can see is just backward compatibility. In order
>to improve that, we could add a GUC var "use_oids_default" (or some
>other name), which would control whether a CREATE TABLE defaults to WITH
>or WITHOUT OIDS. In 7.4, the GUC var would default to false (so there
>would be no change in behavior), but in 7.5 we could switch it to true.
>
>Comments?
>
The problem with getting rid of OIDs as default is there is then no way
to get the primary key of a just inserted row with out OIDs (as far as I
know)

Oracle has the ability to bind variable which allows you to use
RETURNING INTO, Postgresql only has (eg in PHP) pg_getlastoid.

eg

assuming:

Pg:
CREATE TABLE testtable(test_id serial, text1 text);

Oracle:
CREATE TABLE testtable(test_id integer, text1 VARCHAR(255));
CREATE SEQUENCE test_id_seq INCREMENT BY 1;

Postgresql   $sql = "INSERT INTO testtable(text1) ";   $sql .= "VALUES(" . $some_str . ") ";   $result = pg_exec($conn,
$sql);  $oid = pg_getlastoid($result);
 
   $sql_pk = "SELECT test_id FROM testtable WHERE oid = '$oid'";   $result_array = pg_fetch_array($result, 0);   $pk =
$result_array[0];

Oracle   $sql = "INSERT INTO testtable(test_id, text1) ";   $sql .= "VALUES(test_id_seq.nextval, :text1) RETURNING
test_idINTO
 
:test_id";   $stmt = ociparse($conn,$sql);   ocibindbyname($stmt, :text1) &$some_str, -1);   ocibindbyname($stmt,
':test_id',&$test_id, 32);   ociexecute($stmt,OCI_DEFAULT);
 

No OIDs, no way to get a handle to a just inserted row.

>
>Cheers,
>
>Neil
>  
>
Ashley Cambrell