> If you want to make oids optional on user tables,
> we can vote on that.

Let's vote. I'm proposing optional oids for 2-3 years,
so you know how I'll vote -:)

> However, OID's keep our system tables together.

How?! If we want to find function with oid X we query
pg_proc, if we want to find table with oid Y we query
pg_class - we always use oids in context of "class"
to what an object belongs. This means that two tuples
from different system tables could have same oid values
and everything would work perfectly.

There is no magic around OIDs.

Vadim

Wed, 18 Jul 2001 14:06:15 -0700
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: OID wraparound (was Re: pg_depend)
> > If you want to make oids optional on user tables,
> > we can vote on that.
> 
> Let's vote. I'm proposing optional oids for 2-3 years,
> so you know how I'll vote -:)

OK, we need to vote on whether Oid's are optional, and whether we can
have them not created by default.

> 
> > However, OID's keep our system tables together.
> 
> How?! If we want to find function with oid X we query
> pg_proc, if we want to find table with oid Y we query
> pg_class - we always use oids in context of "class"
> to what an object belongs. This means that two tuples
> from different system tables could have same oid values
> and everything would work perfectly.

I meant we use them in many cases to link entries, and in pg_description
for descriptions and lots of other things that may use them in the
future for system table use.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 18 Jul 2001 17:09:38 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: OID wraparound (was Re: pg_depend)
[trimmed cc:list]
On Wednesday 18 July 2001 17:09, Bruce Momjian wrote:
> OK, we need to vote on whether Oid's are optional, and whether we can
> have them not created by default.

[All the below IMHO]

OID's should be optional.

System tables that absolutely have to have OIDs may keep them.

No new OID usage, period.  Use some other unique primary key.

Default user tables to no OIDs.  

Document other means by which rows that are otherwise identical can be made 
unique, for the purpose of expunging duplicates (ctids or whatever is 
appropriate).

Allow a SET DEFAULT CREATE OIDS style option for those who just _must_ have 
OIDS -- and integrate with GUC.  Document that OID wrap can occur, and that 
it can cause Bad Things to happen.

Allow a CREATE TABLE WITH OIDS to supplement the above option setting.

Now for a question:  OID creation seems to be a low-overhead task. Is the 
creation of SERIAL PRIMARY KEY values as efficient?  Or will we be shooting 
ourselves in the performance foot if frequently-accessed system tables go 
from OID usage to SERIAL PRIMARY KEY usage?

> I meant we use them in many cases to link entries, and in pg_description
> for descriptions and lots of other things that may use them in the
> future for system table use.

If I may be so bold: we discourage users from using OIDs as a SERIAL PRIMARY 
KEY, yet the system does it en masse.

I say all that knowing full well that I am using OIDs in my own 
applications.... :-) I guess I'll just need to switch to proper SERIALs and 
PRIMARY KEYs.  Of course, if I wanted to be stubborn, I'd just use the GUC 
option to enable OIDs system-wide by default....

However, the utility of INSERT returning a unique identifier to the inserted 
row needs to be addressed -- I would prefer it return the defined PRIMARY KEY 
value for the tuple just inserted, if a PRIMARY KEY is defined.  If no 
PRIMARY KEY is defined, return a unique identifier (even a temporary one like 
the ctid) so that I have that information for use later in the application. 
The utility of that feature should not be underestimated.

Such a return value would of course have to be returned as a tuple with all 
the necessary metadata to process the return value -- this is probably not a 
trivial change.

Of course, I may be missing some essential usage of OID's.... and I reserve 
the right to be wrong.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Wed, 18 Jul 2001 18:10:36 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: OID wraparound (was Re: pg_depend)
Bruce Momjian wrote:
> 
> > > If you want to make oids optional on user tables,
> > > we can vote on that.
> >
> > Let's vote. I'm proposing optional oids for 2-3 years,
> > so you know how I'll vote -:)
> 
> OK, we need to vote on whether Oid's are optional, and whether we can
> have them not created by default.
> 

I don't love current OIDs. However they have lived in PostgreSQL's
world too long and few people have pointed out that there's no magic
around OIDs. I agree to change OIDs to be per class but strongly
object to let OIDs optional.

It's a big pain for generic applications to lose OIDs.
In fact I'm implementing updatable cursors in ODBC using
OIDs and Tids.

regards,
Hiroshi Inoue

Thu, 19 Jul 2001 08:48:20 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: OID wraparound (was Re: pg_depend)
Lamar Owen <lamar.owen@wgcr.org> writes:
> Now for a question:  OID creation seems to be a low-overhead task. Is the 
> creation of SERIAL PRIMARY KEY values as efficient?  Or will we be shooting 
> ourselves in the performance foot if frequently-accessed system tables go 
> from OID usage to SERIAL PRIMARY KEY usage?

Yes, nowhere near, and yes.  Sequence objects require disk I/O to
update; the OID counter essentially lives in shared memory, and can
be bumped for the price of a spinlock access.

I don't think we should discourage use of OIDs quite as vigorously
as you propose ;-).  All I want is to not expend OIDs on things that
have no need for one.  That, together with clarifying exactly how
unique OIDs should be expected to be, seems to me that it will solve
99% of the problem.

			regards, tom lane

Wed, 18 Jul 2001 19:49:01 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: OID wraparound (was Re: pg_depend) 
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I don't love current OIDs. However they have lived in PostgreSQL's
> world too long and few people have pointed out that there's no magic
> around OIDs. I agree to change OIDs to be per class but strongly
> object to let OIDs optional.

Uh ... what?  I don't follow what you are proposing here.

			regards, tom lane

Wed, 18 Jul 2001 20:14:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: OID wraparound (was Re: pg_depend) 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > I don't love current OIDs. However they have lived in PostgreSQL's
> > world too long and few people have pointed out that there's no magic
> > around OIDs. I agree to change OIDs to be per class but strongly
> > object to let OIDs optional.
> 
> Uh ... what?  I don't follow what you are proposing here.
> 

I couldn't think of the cases that we need database-wide
uniqueness. So the uniqueness of OIDs could be only within
a table. But I object to the option that tables could have
no OIDs.

regards,
Hiroshi Inoue

Thu, 19 Jul 2001 09:28:04 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: OID wraparound (was Re: pg_depend)
At 06:10 PM 18-07-2001 -0400, Lamar Owen wrote:
>applications.... :-) I guess I'll just need to switch to proper SERIALs and 
>PRIMARY KEYs.  Of course, if I wanted to be stubborn, I'd just use the GUC 
>option to enable OIDs system-wide by default....

The default 32 bit serial primary key isn't immune to roll overs either.

I doubt it'll affect my stuff, but it'll affect others.

Once you talk about storing petabytes or terabytes of data, 32 bits might
not be enough.

Cheerio,
Link.


Thu, 19 Jul 2001 11:23:06 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: OID wraparound (was Re: pg_depend)
I wrote:
> 
> Tom Lane wrote:
> >
> > Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > > I don't love current OIDs. However they have lived in PostgreSQL's
> > > world too long and few people have pointed out that there's no magic
> > > around OIDs. I agree to change OIDs to be per class but strongly
> > > object to let OIDs optional.
> >
> > Uh ... what?  I don't follow what you are proposing here.
> >
> 
> I couldn't think of the cases that we need database-wide
> uniqueness. So the uniqueness of OIDs could be only within
> a table. But I object to the option that tables could have
> no OIDs.
> 

It seems that I'm the only one who objects to optional OIDs
as usual:-).
IMHO OIDs are not for system but for users.
OIDs have lived in PostgreSQL world from the first(???).
Isn't it sufficiently long for users to believe that OIDs
are unique (at least per table) ?
As I mentioned already I'm implementing updatable cursors
in ODBC and have half done it. If OIDs would be optional
my trial loses its validity but I would never try another
implementation.

regards,
Hiroshi Inoue

Thu, 19 Jul 2001 12:54:30 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: OID wraparound (was Re: pg_depend)
On Thursday 19 July 2001 06:08, you wrote:
>ï¿½> Bruce Momjian <pgman@candle.pha.pa.us> writes:


>ï¿½I think it should be off on user tables by default, but kept on system
>ï¿½tables just for completeness. ï¿½It could be added at table creation time
>ï¿½or from ALTER TABLEL ADD. ï¿½It seems we just use them too much for system
>ï¿½stuff. ï¿½pg_description is just one example.


and what difference should it make, to have a few extra hundred or thousand 
OIDs used by system tables, when I insert daily some ten thousand records 
each using an OID for itself?

Why not make OIDs 64 bit? Might slow down a little on legacy hardware, but in 
a couple of years we'll all run 64 bit hardware anyway.

I believe that just using 64 bit would require the least changes to Postgres. 
Now, why would that look that obvious to me and yet I saw no mentioing of 
this in the recent postings. Surely it has been discussed before, so which is 
the point I miss or don't understand?

I would need 64 bit sequences anyway, as it is predictable that our table for 
pathology results will run out of unique IDs in a couple of years.

Horst 

Thu, 19 Jul 2001 13:55:53 +1000
Horst Herb <hherb@malleenet.net.au>
Re: Re: OID wraparound (was Re: pg_depend)
Lamar Owen <lamar.owen@wgcr.org> writes:
> However, the utility of INSERT returning a unique identifier to the
> inserted row needs to be addressed -- I would prefer it return the
> defined PRIMARY KEY value for the tuple just inserted, if a PRIMARY
> KEY is defined.  If no PRIMARY KEY is defined, return a unique
> identifier (even a temporary one like the ctid) so that I have that
> information for use later in the application.  The utility of that
> feature should not be underestimated.

That's something that needs to be thought about, all right.  I kinda
like the idea of returning the ctid, because it is (a) very low
overhead, which is nice for something that the client may not actually
need, and (b) the tuple can be retrieved *very* quickly given a tid,
much more so than was possible with OID.  OTOH, if you want to use a
tid you'd best use it right away, before someone else can update the
row...

The major problem with any change away from returning OID is that it'll
break client libraries and apps.  How much pain do we want to cause
ourselves in that line?

Certainly, to return anything besides/instead of OID we'd have to change
the FE/BE protocol.  IIRC, there are a number of other things pending
that require protocol changes, so gathering them all together and
updating the protocol isn't necessarily a bad thing.  But I don't think
we have time for it in the 7.2 cycle, unless we slip the schedule past
the beta-by-end-of-August that I believe we're shooting for.

Another possibility, given that any app using a feature like this is
nonportable anyway, is to extend the INSERT statement along the lines
that someone (maybe Larry R?  I forget now) proposed before:

	INSERT INTO foo ... RETURNING x,y,z,...

where x,y,z, etc are expressions in the variables of the inserted
tuple(s).  This could be made to look like a SELECT at the protocol
level, which would mean that it wouldn't break client libraries or
require a protocol bump, and it's *way* more flexible than any
hardwired decision about what columns to return.  It wouldn't have
any problem with multiple tuples inserted by an INSERT ... SELECT,
either.

			regards, tom lane

Thu, 19 Jul 2001 00:00:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: OID wraparound (was Re: pg_depend) 
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> As I mentioned already I'm implementing updatable cursors
> in ODBC and have half done it. If OIDs would be optional
> my trial loses its validity but I would never try another
> implementation.

Could you use CTID instead of OID?

			regards, tom lane

Thu, 19 Jul 2001 00:08:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: OID wraparound (was Re: pg_depend) 
On Thursday 19 July 2001 12:00 am, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > However, the utility of INSERT returning a unique identifier to the
> > inserted row needs to be addressed -- I would prefer it return the

> Another possibility, given that any app using a feature like this is
> nonportable anyway, is to extend the INSERT statement along the lines
> that someone (maybe Larry R?  I forget now) proposed before:

> 	INSERT INTO foo ... RETURNING x,y,z,...

> where x,y,z, etc are expressions in the variables of the inserted

I like this one.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Thu, 19 Jul 2001 00:09:02 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: OID wraparound (was Re: pg_depend)
On Wednesday 18 July 2001 07:49 pm, Tom Lane wrote:
> I don't think we should discourage use of OIDs quite as vigorously
> as you propose ;-).

Just playing devil's advocate.  As I said, I am one who is using OID's in a 
client now.... but who is willing to forgo that feature for large-system 
stability.

> All I want is to not expend OIDs on things that
> have no need for one.  That, together with clarifying exactly how
> unique OIDs should be expected to be, seems to me that it will solve
> 99% of the problem.

99% solved for 1% effort... The other 1% would take alot more effort.

I think you're barking up the right tree, as usual, Tom.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Thu, 19 Jul 2001 00:11:54 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: OID wraparound (was Re: pg_depend)
Tom Lane wrote:

 >Lamar Owen <lamar.owen@wgcr.org> writes:
 >
 >><snip>
 >>
 >
 ><snip>
 >
 >Another possibility, given that any app using a feature like this is
 >nonportable anyway, is to extend the INSERT statement along the lines
 >that someone (maybe Larry R?  I forget now) proposed before:
 >
 >	INSERT INTO foo ... RETURNING x,y,z,...
 >
 >where x,y,z, etc are expressions in the variables of the inserted
 >tuple(s).  This could be made to look like a SELECT at the protocol
 >level, which would mean that it wouldn't break client libraries or
 >require a protocol bump, and it's *way* more flexible than any
 >hardwired decision about what columns to return.  It wouldn't have
 >any problem with multiple tuples inserted by an INSERT ... SELECT,
 >either.
 >

This would be a good thing (tm).  I use Oracle quite extensively as well
as PG and Oracle's method of "RETURNING :avalue" is very good for
returning values from newly inserted rows.

There was some talk a while back about [not?] implementing variable
binding.  This seems to become very closely related to that. It would 
seem to solve the problem of having a unique identifier returned for 
inserts.  I'm sure it would please quite a few people in the process, 
especially ones moving across from Oracle.  (kill two birds with one stone)

 >
 > 
		regards, tom lane
 >

Ashley Cambrell




Thu, 19 Jul 2001 15:48:43 +1000
Ashley Cambrell <ash@freaky-namuh.com>
Re: OID wraparound (was Re: pg_depend)
At 00:00 19/07/01 -0400, Tom Lane wrote:
>that someone (maybe Larry R?  I forget now) proposed before:
>
>	INSERT INTO foo ... RETURNING x,y,z,...
>

That would have been me; at the time we also talked about
UPDATE...RETURNING and Jan proposed allowing UPDATE...RETURNING
{[Old.|New.]Attr,...}

Needless to say, I'd love to see it implemented.




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Thu, 19 Jul 2001 16:20:45 +1000
Philip Warner <pjw@rhyme.com.au>
Re: OID wraparound (was Re: pg_depend) 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > As I mentioned already I'm implementing updatable cursors
> > in ODBC and have half done it. If OIDs would be optional
> > my trial loses its validity but I would never try another
> > implementation.
> 
> Could you use CTID instead of OID?
> 

I am using both.
TIDs for fast access and OIDs for identification.
Unfortunately TIDs are transient and they aren't
that reliable as for identification. But the
transience of TIDs are useful for row-versioning
fortunately. The combination of OID and TID has
been my plan since I introduced Tid scan.

regards,
Hiroshi Inoue

Thu, 19 Jul 2001 15:36:20 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: OID wraparound (was Re: pg_depend)
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Tom Lane wrote:
>> Could you use CTID instead of OID?

> I am using both.
> TIDs for fast access and OIDs for identification.
> Unfortunately TIDs are transient and they aren't
> that reliable as for identification.

Hmm ... within a transaction I think they'd be reliable enough,
but for long-term ID I agree they're not.  What behavior do you
need exactly; do you need to be able to find the updated version
of a row you originally inserted?  What would it take to use a
user-defined primary key instead of OID?

			regards, tom lane

Thu, 19 Jul 2001 10:16:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: OID wraparound (was Re: pg_depend) 
Philip Warner <pjw@rhyme.com.au> writes:
> At 00:00 19/07/01 -0400, Tom Lane wrote:
>> INSERT INTO foo ... RETURNING x,y,z,...

> That would have been me; at the time we also talked about
> UPDATE...RETURNING and Jan proposed allowing UPDATE...RETURNING
> {[Old.|New.]Attr,...}

Hm.  I'm less excited about UPDATE ... RETURNING since it would seem
that SELECT FOR UPDATE followed by UPDATE would get that job done
in a somewhat-less-nonstandard manner.  But anyway ---

Thinking about this some more, it seems that it's straightforward enough
for a plain INSERT, but I don't understand what's supposed to happen if
the INSERT is replaced by an ON INSERT DO INSTEAD rule.  The rule might
not contain an INSERT at all, or it might contain several INSERTs into
various tables with no simple relationship to the original.  What then?

			regards, tom lane

Thu, 19 Jul 2001 13:19:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: OID wraparound (was Re: pg_depend) 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Tom Lane wrote:
> >> Could you use CTID instead of OID?
> 
> > I am using both.
> > TIDs for fast access and OIDs for identification.
> > Unfortunately TIDs are transient and they aren't
> > that reliable as for identification.
> 
> Hmm ... within a transaction I think they'd be reliable enough,
> but for long-term ID I agree they're not.  What behavior do you
> need exactly;do you need to be able to find the updated version
> of a row you originally inserted? 

What I was about to do in the case e.g. UPDATE is the following.

1) UPDATE .. set .. where CTID = saved_ctid and OID = saved_oid;
   If one row was updated it's OK and return.
2) Otherwise something has changed and the update operation would
   fail. However the driver has to try to find the updated
   version of the row in case of keyset-driven cursors by the query
   SELECT CTID, .. from .. where CTID = 
        currtid2(table_name, saved_ctid) and OID = saved_oid;
   If a row was found, the content of cursors' buffer is 
   replaced and return.
3) If no row was found, the row may be deleted. Or we could
   issue another query
   SELECT CTID, .. from .. where OID = saved_oid;
   though the performance is doubtful.

The OIDs are (mainly) to prevent updating the wrong records.

> What would it take to use a
> user-defined primary key instead of OID?

Yes it could be. In fact M$ provides the ODBC cursor library
in that way and we have used it(indirectly) for a long time.
It's the reason why ODBC users don't complain about the non-existence
of updatable cursors that often. Must I repeat the implementation ?

regards,
Hiroshi Inoue

Fri, 20 Jul 2001 09:24:09 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: OID wraparound (was Re: pg_depend)
lamar.owen@wgcr.org (Lamar Owen) wrote in message news:<01071818103609.00973@lowen.wgcr.org>...

> [trimmed cc:list]

> On Wednesday 18 July 2001 17:09, Bruce Momjian wrote:

> > OK, we need to vote on whether Oid's are optional, and whether we can

> > have them not created by default.

> 

> [All the below IMHO]

> 

> OID's should be optional.


yep.  we don't depend upon oids > 32 bits.  that's pretty standard
practice for serious db apps.  however, tx limit is a real problem.

my vote is for solving the tx limit before chaning the oid problem.

19 Jul 2001 20:34:03 -0700
jmscott@yahoo.com (jmscott@REMOVEMEyahoo.com)
Re: OID wraparound (was Re: pg_depend)