[orm-devel] No such attribute or function 'oid'
Eric Walstad
orm-devel@mailman.tux4web.de
Mon, 20 Jan 2003 13:46:38 -0800
Hi guys,
I'm trying to keep up with you here. I'm no postgresql guru, so let me
know if I'm following the ideas regarding ORM and oid's...
- It seems that the PostgreSQL folks discourage using oid's for user
tables [1], as Ross mentioned.
- Orm uses oid's to identify unique records for backwards
compatibility reasons.
- Ross thinks oid's shouldn't be used for pk's because of the known
lack of uniqueness (on large databases and/or large tables, see [1]) but
that using a pk will result in an extra call to the database on INSERTs;
the extra call retreives the newly inserted pk.
- Extra calls to the database are bad, a penalty in time and
processor load.
- Non-unique oid's are bad as they may result in duplicate records.
- If ORM used pk's instead of oid's it should be able to deal with
compound/multi-column pk's. Diedrich has ideas for how to elegantly
handle the issue of compound/multi-column pk's.
A question for Diedrich: Will ORM someday support the use of pk's
(instead of|in addition to) oid's?
In response to Diedrich's request for an example of compound primary key:
"Could you send me with a real world example in SQL?"
Here's a simple example that allows me to relate a funding source
(Contract, Work Authorization, Purchase Order; represents money against
which I can bill my clients) to zero or more invoices (or multiple
funding sources to an invoice). It has only two fields and both
together define the primary key:
CREATE TABLE "invoice_funding" (
"invoice_id" integer NOT NULL,
"funding_source_id" integer NOT NULL,
Constraint "invoice_funding_pkey" Primary Key ("invoice_id",
"funding_source_id")
);
FWIW, I'd prefer to have orm use pk's instead of oid's, or to have the
option to use pk's instead of oid's. I'm less concerned with extra
calls to the database than I am of potentially finding duplicate records
in an orm result set.
If I've misunderstood something, please let me know.
Best regards,
Eric.
[1]
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/datatype-oid.html
Ross J. Reedstrom wrote:
> On Mon, Jan 13, 2003 at 01:35:32PM -0600, Ross J. Reedstrom wrote:
>
>>On Mon, Jan 13, 2003 at 05:44:20PM +0100, Diedrich Vorberg wrote:
>>
>>>Hi Ross,
>>>
>>>
>>>>Subject: No such attribute or function 'oid'
>>>
>>>Did the patch throw this exception?
>>
>>Didn't try it. BTW, I wasn't the original poster on this, you know. That
>>was Eric Walstad.
>>
>>
>>>>>... Stuff about gpsql and oids
>>>
>>>The reason I kept the special treatment of oids is rather
>>>'traditional' than practical: oids and the idea of an "object
>>>relational database" inspired orm in the first place. Also the
>>>predecessor of orm depended on oids entirely (which turned out to be
>>>one of my worst ideas for it, because you couldn't easily backup :-).
>>>I wanted to make orm downward-compatible.
>>
>>Well ,the problem is that right now oids are deprecated, but thier correct
>>replacements are really ready. For example, the wire protocol actually
>
> ------------------^not
> Dang, I'm having trouble with dropped negations.
>
>
>>provides the OID of a newly inserted tuple, for free. Getting anything
>>else requires an extra round trip to the DB. This is an extra pain when
>>the primary key is a backend generated serial.
>
>
> Ross