Thread: last OID and connections?

Johnny,

> Once I get a current project to production state, I'm going to get a
> heap of users performing updates simultaneously through the same
> update.php, and that raises a few questions:
>
> 1. will pg_getlastoid() return the last oid inserted *by this user*,
> *on this page*, *on this connection*, or is there a possibility for
> them to get mixed up, because the database gets a connection from the
> same user (php), and reuses connections or something?

Use a more sophisticated scheme.  I understand that the current OID
scheme is eventually being phased out of future Postgres releases (with
backwards compatibility, of course!) and replaced with something else
because of problems with very large/very old databases.  Or so the rumor
mill goes.

I handle this issue by wrapping all adds and edits in functions; each
function returns the primary key of the record as its result.  NEXTVAL
and CURVAL are your friends!

> 2. I find oid's pretty clever, but are they good for unique keys? It
> seems, foreign key constraints won't use oid's in the current
> release, should i use serials instead?

Yes, you should use SERIALs instead.  OIDs are for system use only, and
will cause problems with backup/restore as well as databases with more
than 2 billion objects.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

On Wed, Oct 17, 2001 at 03:27:17PM +0200, "Johnny Jørgensen" wrote:
> Once I get a current project to production state, I'm going to get a heap of users performing updates simultaneously
throughthe same update.php, and that raises a few questions: 
>
> 1. will pg_getlastoid() return the last oid inserted *by this user*, *on this page*, *on this connection*, or is
therea possibility for them to get mixed up, because the database gets a connection from the same user (php), and
reusesconnections or something? 

If you pass it the result object from your last insert, it should give
an oid unique to that insert.

I usually do the insert, the immediately read the row back with a select
for the pg_lastoid() in order to get the pkey.  A little wanky, but it
seems to be the way to do it.

> e.g. If a user changes his profile, and an administrator changes some other row at the same time?

If they are through seperate connections, they should be completely
different.

> 2. I find oid's pretty clever, but are they good for unique keys? It seems, foreign key constraints won't use oid's
inthe current release, should i use serials instead? 

Don't use them for anything persistent except for large objects.  Use
serials as primary keys.


--
Adam Haberlach     | Computer science has the unfortunate characteristic
adam@newsnipple.com| that many of the pioneers of the field are still
                   | alive and doing research.
                   |        -- Adam Rifkin

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Wed, Oct 17, 2001 at 03:27:17PM +0200, "Johnny Jørgensen" wrote:
> Once I get a current project to production state, I'm going to get a heap of users performing updates simultaneously
throughthe same update.php, and that raises a few questions: 
>
> 1. will pg_getlastoid() return the last oid inserted *by this user*, *on this page*, *on this connection*, or is
therea possibility for them to get mixed up, because the database gets a connection from the same user (php), and
reusesconnections or something? 

If you pass it the result object from your last insert, it should give
an oid unique to that insert.

I usually do the insert, the immediately read the row back with a select
for the pg_lastoid() in order to get the pkey.  A little wanky, but it
seems to be the way to do it.

> e.g. If a user changes his profile, and an administrator changes some other row at the same time?

If they are through seperate connections, they should be completely
different.

> 2. I find oid's pretty clever, but are they good for unique keys? It seems, foreign key constraints won't use oid's
inthe current release, should i use serials instead? 

Don't use them for anything persistent except for large objects.  Use
serials as primary keys.


--
Adam Haberlach     | Computer science has the unfortunate characteristic
adam@newsnipple.com| that many of the pioneers of the field are still
                   | alive and doing research.
                   |        -- Adam Rifkin

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster