Re: Using oids

Bo Lorentsen <bl(at)netgroup(dot)dk> writes:

> > If I were doing it would extract the primary key of each table on startup
> > and then change that one line of code to:
> >
> > os << "SELECT * FROM " << sTable << " WHERE "
> > << prikey << " = currval('" << sTable << "_" << prikey << "_seq')";
>
> Thanks, but I have to be aware of the "prikey" name, and demand a prikey
> for all tables to insert row into :-(

This is an issue faced mostly by driver developers that want to provide high
level abstract interfaces.

The problem is that using OIDs is basically imposing a primary key on every
table even when the application designer didn't want one. They're mostly
redundant because most tables will have a primary key, wasteful for small
tables, and inadequate for large tables.

I don't like hard coding the assumption that the sequence name is based on the
primary key column name either though. Not every table will have a primary key
of "serial" type. Consider reference tables where the primary key is
non-arbitrary value. Even when it is, the sequence name can be truncated.

The new binary FE protocol included some discussion of API features to allow
drivers like JDBC get column information. I believe that API included an
indication of what the primary key column was. I'm not sure it includes a hook
to get the value of the last insertion, presumably via the sequence. If it
does I would think that would be far preferable to using OIDs.

The disadvantage: tables with no primary key whatsoever would couldn't be
supported by your high level abstraction. I only end up with tables with no
primary keys for many-to-many relationships (or one-to-many sets of immutable
data which amounts to the same thing) anyways. You want to insert, do mass
deletions, but never update such records anyways.

The pros: no extra overhead for OIDs, more portable to other databases.

--
greg