Postgres


#1

alex> Ya, postgres has a different idea of OID. (I.E. when you do an insert into
alex> table with auto-incremented column, you can get back an OID of the row
alex> inserted, then you have to do a query (select pkey from table where
alex> oid=youroid) to get the pkey. This allows to scale where more than one
alex> field is autoincremented, or where autoincremented field is not pkey (or
alex> where’s no pkey).

[I haven’t seen the schema – so maybe I’m off base here – I just barely have time to listen.] IMHO, I think it’s better not to use
Postgres’ OID for references – instead you want a separate column of
type ‘serial’ (auto incremented integer). The disadvantage with using
OID for anything other than transitive identification of a particular
row (which may in fact be what you’re using it for!) is that they’re
not preserved across dumps (normally). It is possible to dump tables
with OID’s, but you have to specifically ask for that to happen –
it’s asking for trouble to use them as references to other tables.

alex> > 2. Creating a schema
alex> > This is really just porting the existing mysql schema
alex> I’ll probably start with oracle schema, as mysql has way too many
alex> non-portable idiosyncrasies.

Makes much more sense to me too…

alex> I managed to get RT 1.3.somewhere-in-march with postgres, shouldn’t be
alex> that hard to do it again. That RT was badly broken so I threw it away and
alex> never worked more on it :slight_smile:

I’m eagerly awaiting this, as is my team (who want the data that we
currently have stored in mysql (only because that’s where RT wants it)
tied to all the rest of our data, which is in postgres. I’ll be an
early adopter; just don’t have time to do the port right now.

Jim Rowan
DCSI
jmr@computing.com


#2

alex> Ya, postgres has a different idea of OID. (I.E. when you do an insert into
alex> table with auto-incremented column, you can get back an OID of the row
alex> inserted, then you have to do a query (select pkey from table where
alex> oid=youroid) to get the pkey. This allows to scale where more than one
alex> field is autoincremented, or where autoincremented field is not pkey (or
alex> where’s no pkey).

[I haven’t seen the schema – so maybe I’m off base here – I just barely have time to listen.] IMHO, I think it’s better not to use
Postgres’ OID for references – instead you want a separate column of
type ‘serial’ (auto incremented integer). The disadvantage with using
OID for anything other than transitive identification of a particular
row (which may in fact be what you’re using it for!) is that they’re
not preserved across dumps (normally). It is possible to dump tables
with OID’s, but you have to specifically ask for that to happen –
it’s asking for trouble to use them as references to other tables.

No, the serial will be used for references. I am talking about different
ways how databases return ‘what-did-i-just-insert-into-database’. MySQL
returns the primary key, and Postgres returns OID, and you need to do
additional select to get primary key.

alex> I managed to get RT 1.3.somewhere-in-march with postgres, shouldn’t be
alex> that hard to do it again. That RT was badly broken so I threw it away and
alex> never worked more on it :slight_smile:

I’m eagerly awaiting this, as is my team (who want the data that we
currently have stored in mysql (only because that’s where RT wants it)
tied to all the rest of our data, which is in postgres. I’ll be an
early adopter; just don’t have time to do the port right now.
Great :wink:

-alex