pg_depend

Bruce Momjian writes:

I have found that many TODO items would benefit from a pg_depend table
that tracks object dependencies. TODO updated.

I'm not so convinced on that idea. Assume you're dropping object foo.
You look at pg_depend and see that objects 145928, 264792, and 1893723
depend on it. Great, what do you do now?

Every system catalog (except the really badly designed ones) already
contains dependency information. What might help is that we make the
internal API for altering and dropping any kind of object more consistent
and general so that they can call each other in the dependency case.
(E.g., make sure none of them require whereToSendOutput or parser state as
an argument.)

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Bruce Momjian writes:

I have found that many TODO items would benefit from a pg_depend table
that tracks object dependencies. TODO updated.

I'm not so convinced on that idea. Assume you're dropping object foo.
You look at pg_depend and see that objects 145928, 264792, and 1893723
depend on it. Great, what do you do now?

Every system catalog (except the really badly designed ones) already
contains dependency information. What might help is that we make the
internal API for altering and dropping any kind of object more consistent
and general so that they can call each other in the dependency case.
(E.g., make sure none of them require whereToSendOutput or parser state as
an argument.)

Yes, it is not simple. The table is just one part of it. Code has to
do lookups and have cascade/failure options based on what it finds.

Things can get quite complicated, especially circular dependencies. It
needs a general overhaul and has to hit every area. We need a central
location to keep all this info.

-- 
  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

On Mon, 16 Jul 2001, Peter Eisentraut wrote:

Bruce Momjian writes:

I have found that many TODO items would benefit from a pg_depend table
that tracks object dependencies. TODO updated.

I'm not so convinced on that idea. Assume you're dropping object foo.
You look at pg_depend and see that objects 145928, 264792, and 1893723
depend on it. Great, what do you do now?

I believe someone else previously suggested this:

drop <type> object [RESTRICT | CASCADE]

to make use of dependency info.

Every system catalog (except the really badly designed ones) already
contains dependency information. What might help is that we make the
internal API for altering and dropping any kind of object more consistent
and general so that they can call each other in the dependency case.
(E.g., make sure none of them require whereToSendOutput or parser state as
an argument.)

Yes, that's definitely requirement to implement the above...

Alex Pilosov writes:

I'm not so convinced on that idea. Assume you're dropping object foo.
You look at pg_depend and see that objects 145928, 264792, and 1893723
depend on it. Great, what do you do now?

I believe someone else previously suggested this:

drop <type> object [RESTRICT | CASCADE]

to make use of dependency info.

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

On Tue, 17 Jul 2001, Peter Eisentraut wrote:

Alex Pilosov writes:

I'm not so convinced on that idea. Assume you're dropping object foo.
You look at pg_depend and see that objects 145928, 264792, and 1893723
depend on it. Great, what do you do now?

I believe someone else previously suggested this:

drop <type> object [RESTRICT | CASCADE]

to make use of dependency info.

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

have a view pg_objecttype which is a UNION across all the [relevant]
system tables sounds fine to me, but maybe I'm missing something?

Alex Pilosov writes:

I'm not so convinced on that idea. Assume you're dropping object foo.
You look at pg_depend and see that objects 145928, 264792, and 1893723
depend on it. Great, what do you do now?

I believe someone else previously suggested this:

drop <type> object [RESTRICT | CASCADE]

to make use of dependency info.

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

I think we will need the relid of the system table. I imagine four
columns:

object relid
object oid
reference relid
references oid

-- 
  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

Peter Eisentraut <peter_e@gmx.net> writes:

You look at pg_depend and see that objects 145928, 264792, and 1893723
depend on it. Great, what do you do now?

I believe someone else previously suggested this:
drop <type> object [RESTRICT | CASCADE]
to make use of dependency info.

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

Even more to the point, what guarantee can we have that that OID even
defines a unique object at all? We have unique indexes that ensure
there are not two tables with the same OID, or two functions with the
same OID, etc --- but none that ensure uniqueness across system
catalogs.

The objects would need to be identified by two-part IDs, one part
specifying the object type and one giving its OID (which is known unique
within that type). Possibly object type would be best handled by giving
the OID of the system catalog containing the object's definition row.
In any case, looking at the type part would let users of the pg_depend
catalog figure out what they needed to do.

BTW, pg_description is broken because it assumes that OID alone is a
sufficient identifier ... but since it's such a noncritical function,
I haven't gotten too excited about it.

regards, tom lane

Let me clearify. I am suggesting system table relid for each entry:

object sysrelid
object oid
reference sysrelid
references oid

-- 
  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

The objects would need to be identified by two-part IDs, one part
specifying the object type and one giving its OID (which is known unique
within that type). Possibly object type would be best handled by giving
the OID of the system catalog containing the object's definition row.
In any case, looking at the type part would let users of the pg_depend
catalog figure out what they needed to do.

Yes, exactly. Also, I can see code that will handles dependencies
differently if it is a pg_class or pg_type row that is mentioned in
pg_depend.

-- 
  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

On Tue, 17 Jul 2001, Peter Eisentraut wrote:

Alex Pilosov writes:

I'm not so convinced on that idea. Assume you're dropping object foo.
You look at pg_depend and see that objects 145928, 264792, and 1893723
depend on it. Great, what do you do now?

I believe someone else previously suggested this:

drop <type> object [RESTRICT | CASCADE]

to make use of dependency info.

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

You have three columns, depender, dependee, and the third the oid of the
entry of pg_class describing what the depender is. Oh, actually you'd
probably need four columns, depender, dependee, depender in pg_class, and
dependee in pg_class.

Take care,

Bill

Peter Eisentraut wrote:

Alex Pilosov writes:

I'm not so convinced on that idea. Assume you're dropping object foo.
You look at pg_depend and see that objects 145928, 264792, and 1893723
depend on it. Great, what do you do now?

I believe someone else previously suggested this:

drop <type> object [RESTRICT | CASCADE]

to make use of dependency info.

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

Is it really determined that *DROP OBJECT* drops the objects
which are dependent on it ?

regards,
Hiroshi Inoue

On Tue, 17 Jul 2001, Hiroshi Inoue wrote:

Peter Eisentraut wrote:

Alex Pilosov writes:

drop <type> object [RESTRICT | CASCADE]

to make use of dependency info.

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

Is it really determined that *DROP OBJECT* drops the objects
which are dependent on it ?

If you used DROP OBJECT CASCADE, yes. That's what CASCADE is saying.

I think the idea is that you can say what happens - delete dependents, or
do something else.

Take care,

Bill

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Is it really determined that *DROP OBJECT* drops the objects
which are dependent on it ?

DROP object CASCADE should work that way, because that's what the spec
says.

Whether the default DROP behavior should be CASCADE, RESTRICT, or the
current laissez-faire behavior remains to be debated ;-). The spec
is no help since it has no default: DROP *requires* a CASCADE or
RESTRICT option in SQL92. But I doubt our users will let us get away
with changing the syntax that way. So, once we have the CASCADE and
RESTRICT options implemented, we'll need to decide what an unadorned
DROP should do. Opinions anyone?

regards, tom lane

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Is it really determined that *DROP OBJECT* drops the objects
which are dependent on it ?

DROP object CASCADE should work that way, because that's what the spec
says.

Whether the default DROP behavior should be CASCADE, RESTRICT, or the
current laissez-faire behavior remains to be debated ;-). The spec
is no help since it has no default: DROP *requires* a CASCADE or
RESTRICT option in SQL92. But I doubt our users will let us get away
with changing the syntax that way. So, once we have the CASCADE and
RESTRICT options implemented, we'll need to decide what an unadorned
DROP should do. Opinions anyone?

Don't forget RENAME.

And what do we do if two items depend on the same object.

-- 
  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

Whether the default DROP behavior should be CASCADE, RESTRICT, or the
current laissez-faire behavior remains to be debated ;-). The spec
is no help since it has no default: DROP *requires* a CASCADE or
RESTRICT option in SQL92. But I doubt our users will let us get away
with changing the syntax that way. So, once we have the CASCADE and
RESTRICT options implemented, we'll need to decide what an unadorned
DROP should do. Opinions anyone?

Hmmm...an unadorned drop could remove the object without RESRICTing it or
CASCADEing it. Hence, if there are objects that depend on it, the object
will be removed anyway, and dependent objects will not be touched. It's one
of those things that gives the DBA power, but might let them munge their
database. (Although it's exactly the same as the current way things happen)

Chris

Christopher Kings-Lynne wrote:

Whether the default DROP behavior should be CASCADE, RESTRICT, or the
current laissez-faire behavior remains to be debated ;-). The spec
is no help since it has no default: DROP *requires* a CASCADE or
RESTRICT option in SQL92. But I doubt our users will let us get away
with changing the syntax that way. So, once we have the CASCADE and
RESTRICT options implemented, we'll need to decide what an unadorned
DROP should do. Opinions anyone?

Hmmm...an unadorned drop could remove the object without RESRICTing it or
CASCADEing it. Hence, if there are objects that depend on it, the object
will be removed anyway, and dependent objects will not be touched.

We could mark the objects(and their dependent objects) as *INVALID*.
They would revive when reference objects revive in the world of *name*s.

regards,
Hiroshi Inoue

Bruce Momjian writes:

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

I think we will need the relid of the system table. I imagine four
columns:

object relid
object oid
reference relid
references oid

I'm not seeing the point. You're essentially duplicating the information
that's already available in the system catalogs. This is bound to become
a catastrophe the minute a user steps in and does manual surgery on some
catalog. (And yes, manual surgery should still be possible.)

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Bruce Momjian writes:

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

I think we will need the relid of the system table. I imagine four
columns:

object relid
object oid
reference relid
references oid

I'm not seeing the point. You're essentially duplicating the information
that's already available in the system catalogs. This is bound to become
a catastrophe the minute a user steps in and does manual surgery on some
catalog. (And yes, manual surgery should still be possible.)

But how then do you find the system table that uses the given oid?
Wasn't that your valid complaint?

-- 
  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

Bruce Momjian writes:

I'm not seeing the point. You're essentially duplicating the information
that's already available in the system catalogs. This is bound to become
a catastrophe the minute a user steps in and does manual surgery on some
catalog. (And yes, manual surgery should still be possible.)

But how then do you find the system table that uses the given oid?

It's implied by the column you're looking at.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Bruce Momjian writes:

I'm not seeing the point. You're essentially duplicating the information
that's already available in the system catalogs. This is bound to become
a catastrophe the minute a user steps in and does manual surgery on some
catalog. (And yes, manual surgery should still be possible.)

But how then do you find the system table that uses the given oid?

It's implied by the column you're looking at.

Is it? Are we going to record dependency both ways, e.g primary table
-> foreign table and foreign table -> primary table, or just one of
them. And when we see we depend on something, do we know always what it
could be. If I drop a table and I depend on oid XXX, do I know if that
is a type, function, or serial sequence?

-- 
  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

Bruce Momjian writes:

Is it? Are we going to record dependency both ways, e.g primary table
-> foreign table and foreign table -> primary table, or just one of
them. And when we see we depend on something, do we know always what it
could be. If I drop a table and I depend on oid XXX, do I know if that
is a type, function, or serial sequence?

When you drop a table, there are only so many things that could depend on
it:

* rules/views
* triggers
* check constraints
* foreign key constraints
* primary key constraints
* unique constraints
* subtables

including their dependencies. There might be others I forgot but a
finite list can be defined.

When a table is dropped, you scan all of these objects (their system
catalogs) for matches against the table and either do a cascade or
restrict. This is not new, we already do this for indexes and
descriptions, for instance.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

When a table is dropped, you scan all of these objects (their system
catalogs) for matches against the table and either do a cascade or
restrict. This is not new, we already do this for indexes and
descriptions, for instance.

I was thinking we could centralize all that checking in pg_depend.
However, we could decide just to do the areas where system tables don't
work, like foreign keys and sequences. But when I find an oid depends
on me, do I start scanning tables looking to see if is a sequence or a
foreign key?

-- 
  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

Peter Eisentraut <peter_e@gmx.net> writes:

Bruce Momjian writes:

But how then do you find the system table that uses the given oid?

It's implied by the column you're looking at.

It is? Remember that we need to use this table to get from an object
to the objects that depend on it. A datatype OID, for example, would
have table OIDs (for column datatypes), function OIDs (for argument
datatypes), operator OIDs (ditto), aggregate OIDs (ditto), etc etc
dependent on it. How will you intuit which of those is represented
by a given row in pg_depend?

The alternative to pg_depend is to do a brute force scan of all the
system catalogs looking for dependent objects. In that case, you'd
know what you are looking at, but if we extract the dependencies as
a separate table, I don't see how you'd know without being told.

regards, tom lane

When you drop a table, there are only so many things that could depend on
it:

* rules/views
* triggers
* check constraints
* foreign key constraints
* primary key constraints
* unique constraints
* subtables

including their dependencies. There might be others I forgot but a
finite list can be defined.

When a table is dropped, you scan all of these objects (their system
catalogs) for matches against the table and either do a cascade or
restrict. This is not new, we already do this for indexes and
descriptions, for instance.

Here is how I see it. If you use the pg_depend table to track these
dependencies, you know at the time you do the insert where they come
from so why not just record it at that time? Why poke around later
looking at many system tables? The big issue is that you can pretty
much centralize the stuff during INSERT and just use that on
DROP/RENAME. I can even see a loop that says, "I am OK with sequence
dependencies, but not other pg_class dependencies" or stuff like that.
You can just trigger on the sysrelid in the table and determine where to
go. If not you have to have all sorts of system poking code in
DROP/RENAME, unless you want to just call a function to hit _every_
system table looking for the oid, which I doubt you want to do.

-- 
  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

Tom Lane writes:

The alternative to pg_depend is to do a brute force scan of all the
system catalogs looking for dependent objects. In that case, you'd
know what you are looking at, but if we extract the dependencies as
a separate table, I don't see how you'd know without being told.

The former is what I'm advocating.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Tom Lane writes:

The alternative to pg_depend is to do a brute force scan of all the
system catalogs looking for dependent objects. In that case, you'd
know what you are looking at, but if we extract the dependencies as
a separate table, I don't see how you'd know without being told.

The former is what I'm advocating.

So you are basically saying you don't like pg_depend. Would you prefer
to use it only in cases we can't encode the dependencies easily in the
system catalogs, like functions that require certain relations?

-- 
  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

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane writes:

The alternative to pg_depend is to do a brute force scan of all the
system catalogs looking for dependent objects. In that case, you'd
know what you are looking at, but if we extract the dependencies as
a separate table, I don't see how you'd know without being told.

The former is what I'm advocating.

Seems like a bad idea; it'll slow down deletes quite a lot, no? Do you
really want to (for example) parse every SQL function in the system to
see if it refers to a table being dropped? Why would we want to do that
work over again for every such delete, rather than doing it once when
an object is created and storing the info in a table? Also consider
that what you are proposing is (at least) an O(N^2) algorithm when there
are a large number of objects.

Furthermore, a separate dependency table would allow us to support
user-defined dependencies. It could be that the user knows function A
should go away if table B does, yet there is no physical dependency that
the system would recognize for it.

regards, tom lane

On Tue, 17 Jul 2001, Peter Eisentraut wrote:

Tom Lane writes:

The alternative to pg_depend is to do a brute force scan of all the
system catalogs looking for dependent objects. In that case, you'd
know what you are looking at, but if we extract the dependencies as
a separate table, I don't see how you'd know without being told.

The former is what I'm advocating.

Why? It's grossly inefficient and requires lots of effort. And scales
horribly to adding new things which can depend on others.

Following that argument (admittedly to an extreme conclusion), we should
rip out index support. After all, all of the info in the index is stored
in the table, we don't need to duplicate it elsewhere.

pg_depend is a concise way to encode dependencies. We do all of the work
at insert, where we know what depends on what. To not have pg_depend means
that on delete, we have to scan EVERYTHING to see what depends on what
we're dropping. If we find something (and are CASCADEing), we have to
check and see if _it_ depends on anything (another complete scan). We have
to keep doing complete scans until we find nothing.

Take care,

Bill

On Tue, 17 Jul 2001, Tom Lane wrote:

Seems like a bad idea; it'll slow down deletes quite a lot, no? Do you
really want to (for example) parse every SQL function in the system to
see if it refers to a table being dropped? Why would we want to do that
work over again for every such delete, rather than doing it once when
an object is created and storing the info in a table? Also consider
that what you are proposing is (at least) an O(N^2) algorithm when there
are a large number of objects.

I think it's actually O(N^M) where there are N system objects and a chain
of M dependencies (A depends on B which depends on C => M = 3).

Take care,

Bill

Bill Studenmund <wrstuden@zembu.com> writes:

I think it's actually O(N^M) where there are N system objects and a chain
of M dependencies (A depends on B which depends on C => M = 3).

It's probably not *that* bad. It's reasonable to assume that only a
small number of objects actually depend directly on any one object you
might want to delete. (Performance of deleting, say, the int4 datatype
is probably not of major interest ;-) ...) Only for those objects, not
for all N, would you need to descend to the next level of search.

Nonetheless, a properly indexed pg_depend table would allow you to find
these objects directly, and again to find their dependents directly,
etc. The brute force approach would require a rather expensive scan
over all the system catalogs, plus nontrivial analysis for some types
of system objects such as functions. Repeating that for each cascaded
delete is even less appetizing than doing it once.

regards, tom lane

On Tue, 17 Jul 2001, Tom Lane wrote:

Bill Studenmund <wrstuden@zembu.com> writes:

I think it's actually O(N^M) where there are N system objects and a chain
of M dependencies (A depends on B which depends on C => M = 3).

It's probably not *that* bad. It's reasonable to assume that only a
small number of objects actually depend directly on any one object you
might want to delete. (Performance of deleting, say, the int4 datatype
is probably not of major interest ;-) ...) Only for those objects, not
for all N, would you need to descend to the next level of search.

Ah yes. It'll be O(ND) where D is the number of dependers (the number of
leaves in the dependency tree).

Nonetheless, a properly indexed pg_depend table would allow you to find
these objects directly, and again to find their dependents directly,
etc. The brute force approach would require a rather expensive scan
over all the system catalogs, plus nontrivial analysis for some types
of system objects such as functions. Repeating that for each cascaded
delete is even less appetizing than doing it once.

Indeed.

Take care,

Bill

Peter Eisentraut wrote:

Bruce Momjian writes:

That was me. The point, however, was, given object id 145928, how the
heck to you know what table this comes from?

I think we will need the relid of the system table. I imagine four
columns:

object relid
object oid
reference relid
references oid

I like
object relid
object oid
object name
reference relid
reference oid

and unadorned DROP doesn't drop dependent objects.

regards,
Hiroshi Inoue

I like
object relid
object oid
object name
reference relid
reference oid

Can I ask why you like the object name?

and unadorned DROP doesn't drop dependent objects.

OK.

-- 
  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

Bruce Momjian wrote:

I like
object relid
object oid
object name
reference relid
reference oid

Can I ask why you like the object name?

Oops I made a mistake.
Reference name is needed not an object name,
i.e
object relid
object oid
relerence relid
reference oid
reference name

create table a (...);
create view view_a as select .. from a;

Then we have an pg_depend entry e.g.

pg_class_relid
oid of the view_a
pg_class_relid
oid of the table a
'a' the name of the table

and so on.

drop table a; (unadorned drop).

Then the above entry would be changed to

pg_class_relid(unchanged)
oid of the view_s(unchagned)
pg_class_relid(unchanged)
InvalidOid
'a' the name of the table(unchanged)

create table a (...);

Then the pg_depend entry would be

pg_class_relid(unchanged)
oid of the view_s(unchagned)
pg_class_relid(unchanged)
the oid of the new table a
'a' the name of the table(unchanged)

regards,
Hiroshi Inoue

Then we have an pg_depend entry e.g.

pg_class_relid
oid of the view_a
pg_class_relid
oid of the table a
'a' the name of the table

and so on.

drop table a; (unadorned drop).

Then the above entry would be changed to

pg_class_relid(unchanged)
oid of the view_s(unchagned)
pg_class_relid(unchanged)
InvalidOid
'a' the name of the table(unchanged)

create table a (...);

Then the pg_depend entry would be

pg_class_relid(unchanged)
oid of the view_s(unchagned)
pg_class_relid(unchanged)
the oid of the new table a
'a' the name of the table(unchanged)

So you want to keep the name of the referenced object in case it is
dropped. Makes sense.

-- 
  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

At 11:25 18/07/01 +0900, Hiroshi Inoue wrote:

Oops I made a mistake.
Reference name is needed not an object name,
i.e
object relid
object oid
relerence relid
reference oid
reference name

I think any deisgn needs to cater for attr dependencies. eg.

create table a (f1 int4, f2 int8);
create view view_a as select f2 from a;

Then

alter table a drop f1; -- Is OK. Should just happen
alter table a drop f2; -- Should warn about the view, and/or cascade etc.
alter table a alter f2 float; -- Should trigger a view recompilation.

...same thing needs to happen with constraints that reference attrs

I *think* tables are the only items that can have subobjects with dependant.

----------------------------------------------------------------
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 |/

On Tue, Jul 17, 2001 at 07:13:10PM -0400, Tom Lane wrote:

Nonetheless, a properly indexed pg_depend table would allow you to find
these objects directly, and again to find their dependents directly,
etc. The brute force approach would require a rather expensive scan
over all the system catalogs, plus nontrivial analysis for some types
of system objects such as functions. Repeating that for each cascaded
delete is even less appetizing than doing it once.

Stated that way, the performance argument sounds very convincing. However,
the _real_ convincer for me is the support for user designated
dependencies, as Tom pointed out earlier. That allows the system to do
as much as possible automatically, (even functional dependency analysis,
if someone want to write it) but doesn't require the automatic mechanisms
to be perfect: the DBA has a mechanism to do the crazy, edge case things.

Ross

On Wed, Jul 18, 2001 at 01:08:15PM +1000, Philip Warner wrote:

At 11:25 18/07/01 +0900, Hiroshi Inoue wrote:

Oops I made a mistake.
Reference name is needed not an object name,
i.e
object relid
object oid
relerence relid
reference oid
reference name

I think any deisgn needs to cater for attr dependencies. eg.

create table a (f1 int4, f2 int8);
create view view_a as select f2 from a;

Then

alter table a drop f1; -- Is OK. Should just happen
alter table a drop f2; -- Should warn about the view, and/or cascade etc.
alter table a alter f2 float; -- Should trigger a view recompilation.

...same thing needs to happen with constraints that reference attrs

I *think* tables are the only items that can have subobjects with dependant.

Wouldn't that work simply by using the oid for the column in pg_attribute
as the primary dependency, rather than the table itself, from pg_class? So,
the dependency chain would be:

view -> attribute -> table

So your examples would 'just work', I think.

Ross

Wouldn't that work simply by using the oid for the column in pg_attribute
as the primary dependency, rather than the table itself, from pg_class? So,
the dependency chain would be:

view -> attribute -> table

So your examples would 'just work', I think.

True. We need to remember to store both sets of dependencies (used attrs as
well as the table dependency).

----------------------------------------------------------------
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 |/

Wouldn't that work simply by using the oid for the column in pg_attribute
as the primary dependency, rather than the table itself, from pg_class? So,
the dependency chain would be:

view -> attribute -> table

So your examples would 'just work', I think.

True. We need to remember to store both sets of dependencies (used attrs as
well as the table dependency).

TODO update with column labels:

* Add pg_depend table for dependency recording; use sysrelid, oid,
depend_sysrelid, depend_oid, name

-- 
  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

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Reference name is needed not an object name,

Only if we want to support the notion that drop-and-recreate-with-same-name
means that references from other objects should now apply to the new
object. I do not think that that's really a good idea, at least not
without a heck of a lot of compatibility checking. It'd be way too easy
to create cases where the properties of the new object do not match
what the referring object expects.

The majority of the cases I've heard about where this would be useful
are for functions, and we could solve that a lot better with an ALTER
FUNCTION command that allows changing the function body (but not the
name, arguments, or result type).

BTW, name alone is not a good enough referent for functions... you'd
have to store the argument types too.

regards, tom lane

Philip Warner <pjw@rhyme.com.au> writes:

I think any deisgn needs to cater for attr dependencies. eg.

I don't really see a need to recognize dependencies at finer than table
level. I'd just make the dependency be from view_a to a and keep things
simple. What's so wrong with recompiling the view for *every* change
of the underlying table?

We could support attr-level dependencies within the proposed pg_depend
layout if we made pg_attribute one of the allowed object categories.
However, I'd prefer not to make OID of pg_attribute rows be a primary
key for that table (in the long run I'd like to not assign OIDs at all
to pg_attribute, as well as other tables that don't need OIDs). So the
better way to do it would be to make the pg_depend entries include
attribute numbers. But I really think this is unnecessary complexity.

regards, tom lane

At 11:38 18/07/01 -0400, Tom Lane wrote:

Philip Warner <pjw@rhyme.com.au> writes:

I think any deisgn needs to cater for attr dependencies. eg.

I don't really see a need to recognize dependencies at finer than table
level. I'd just make the dependency be from view_a to a and keep things
simple. What's so wrong with recompiling the view for *every* change
of the underlying table?

Not a problem for views, but when you get to constraints on large tables,
re-evaluating all the constraints unnecessarily could be a nightmare, and
especially frustrating when you just dropped an irrelevant attr.

----------------------------------------------------------------
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 |/

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Reference name is needed not an object name,

Only if we want to support the notion that drop-and-recreate-with-same-name
means that references from other objects should now apply to the new
object. I do not think that that's really a good idea, at least not
without a heck of a lot of compatibility checking. It'd be way too easy
to create cases where the properties of the new object do not match
what the referring object expects.

The majority of the cases I've heard about where this would be useful
are for functions, and we could solve that a lot better with an ALTER
FUNCTION command that allows changing the function body (but not the
name, arguments, or result type).

BTW, name alone is not a good enough referent for functions... you'd
have to store the argument types too.

I assume the name was only for reference use so you could give the user
an idea of what is missing. Clearly you don't use that to recreate
anything, or I hope not.

-- 
  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

Philip Warner <pjw@rhyme.com.au> writes:

I think any deisgn needs to cater for attr dependencies. eg.

I don't really see a need to recognize dependencies at finer than table
level. I'd just make the dependency be from view_a to a and keep things
simple. What's so wrong with recompiling the view for *every* change
of the underlying table?

What about other objects. Foreign keys? Serial?

We could support attr-level dependencies within the proposed pg_depend
layout if we made pg_attribute one of the allowed object categories.
However, I'd prefer not to make OID of pg_attribute rows be a primary
key for that table (in the long run I'd like to not assign OIDs at all
to pg_attribute, as well as other tables that don't need OIDs). So the
better way to do it would be to make the pg_depend entries include
attribute numbers. But I really think this is unnecessary complexity.

I liked the pg_attribute references for some uses. I agree doing that
for a view seems overly complex.

I don't see any value in dropping oid from pg_attribute.

-- 
  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

Philip Warner <pjw@rhyme.com.au> writes:

At 11:38 18/07/01 -0400, Tom Lane wrote:

I'd just make the dependency be from view_a to a and keep things
simple. What's so wrong with recompiling the view for *every* change
of the underlying table?

Not a problem for views, but when you get to constraints on large tables,
re-evaluating all the constraints unnecessarily could be a nightmare, and
especially frustrating when you just dropped an irrelevant attr.

Huh? You seem to be thinking that we'd need to re-check the constraint
at each row of the table, but I don't see why we'd need to. I was just
envisioning re-parsing the constraint source text.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Reference name is needed not an object name,

Only if we want to support the notion that
drop-and-recreate-with-same-name
means that references from other objects should now apply to the new
object. I do not think that that's really a good idea, at least not
without a heck of a lot of compatibility checking. It'd be way too easy
to create cases where the properties of the new object do not match
what the referring object expects.

For example, we would process the following step to drop a
column.

select ....(all columns except a column) from a into b;
drop table a;
alter table b rename to a;

But we would lose all relelvant objects.

Though we may be able to solve this problem by implementing
*drop column* properly, we couldn't solve this kind of problems
at once. In fact neither *drop column* nor *cluster* is solved.
We could always have (at least) the second best way by
allowing drop-and-recreate-with-same-name revival.

The majority of the cases I've heard about where this would be useful
are for functions, and we could solve that a lot better with an ALTER
FUNCTION command that allows changing the function body (but not the
name, arguments, or result type).

BTW, name alone is not a good enough referent for functions... you'd
have to store the argument types too.

??? Isn't an entry
pg_proc_relid
the oid of the function
pg_type_relid
the oid of an argument type
the name of the argument type
made ?

regards,
Hiroshi Inoue

On Wed, 18 Jul 2001, Hiroshi Inoue wrote:

Oops I made a mistake.
Reference name is needed not an object name,
i.e
object relid
object oid
relerence relid
reference oid
reference name

create table a (...);
create view view_a as select .. from a;

Then we have an pg_depend entry e.g.

pg_class_relid
oid of the view_a
pg_class_relid
oid of the table a
'a' the name of the table

and so on.

drop table a; (unadorned drop).

Then the above entry would be changed to

pg_class_relid(unchanged)
oid of the view_s(unchagned)
pg_class_relid(unchanged)
InvalidOid
'a' the name of the table(unchanged)

create table a (...);

Then the pg_depend entry would be

pg_class_relid(unchanged)
oid of the view_s(unchagned)
pg_class_relid(unchanged)
the oid of the new table a
'a' the name of the table(unchanged)

This step I disagree with. Well, I disagree with the automated aspect of
the update. How does postgres know that the new table a is sufficiently
like the old table that it should be used? A way the DBA could say, "yeah,
restablish that," would be fine.

Which is better, a view which is broken as the table it was based off of
was dropped (even though there's a table of the same name now) or a view
which is broken because there is now a table whose name matches its
old table's name, but has different columns (either names or types)?

I'd say #1.

Take care,

Bill

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

BTW, name alone is not a good enough referent for functions... you'd
have to store the argument types too.

??? Isn't an entry
pg_proc_relid
the oid of the function
pg_type_relid
the oid of an argument type
the name of the argument type
made ?

That's the entry that was dropped, no? Given a pg_depend row pointing
at a function named foo, with an OID that no longer exists, how will you
tell which of the (possibly many) functions named foo is wanted?

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I don't see any value in dropping oid from pg_attribute.

Conservation of OIDs. Assigning an OID to every row of pg_attribute
chews up lots of OIDs, for a table that should never be referenced by
OID --- its primary key is (table OID, attribute number).

Right now this isn't really significant, but if/when we have an option
to suppress OID generation for user tables, I have every intention of
applying it to a bunch of the system tables as well. pg_attribute is
a prime candidate.

("When" probably means "next month", btw. This is on my 7.2 list...)

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I don't see any value in dropping oid from pg_attribute.

Conservation of OIDs. Assigning an OID to every row of pg_attribute
chews up lots of OIDs, for a table that should never be referenced by
OID --- its primary key is (table OID, attribute number).

Right now this isn't really significant, but if/when we have an option
to suppress OID generation for user tables, I have every intention of
applying it to a bunch of the system tables as well. pg_attribute is
a prime candidate.

("When" probably means "next month", btw. This is on my 7.2 list...)

Yikes, I am not sure we are ready to make oids optional. System table
oid's seem like the last place to try and preserve oids. Do we return
unused oids back to the pool on backend exit yet? (I don't see it on
the TODO list.) That seems like a much more profitable place to start.

Will we have cheap 64-bit oids by the time oid wraparound becomes an
issue?

-- 
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Yikes, I am not sure we are ready to make oids optional.

We've discussed it enough, it's time to do it. I have an ulterior plan
here: I want 7.2 not to have any limitations that prevent it from being
used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed
now, or nearly. The other stumbling blocks for continuous runs are OID
wraparound and XID wraparound. We've got unique indexes on OIDs for all
system catalogs that need them (we were short a couple as of 7.1, btw),
but OID wrap is still likely to lead to unwanted "duplicate key"
failures. So we still need a way to reduce the system's appetite for
OIDs. In a configuration where OIDs are used only where *necessary*,
it'd be a long time till wrap. I also intend to do something about XID
wrap next month...

Do we return unused oids back to the pool on backend exit yet?

Since WAL, and that was never a fundamental answer anyway.

Will we have cheap 64-bit oids by the time oid wraparound becomes an
issue?

No, we won't, because OID wrap is an issue already for any long-uptime
installation. (64-bit XIDs are not a real practical answer either,
btw.)

regards, tom lane

On Wednesday 18 July 2001 13:52, Tom Lane wrote:

here: I want 7.2 not to have any limitations that prevent it from being
used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed
now, or nearly. The other stumbling blocks for continuous runs are OID

Go for it, Tom. After the posting the other day about the 200GB data per
week data load, this _really_ needs to be done. It won't directly affect me,
as my needs are a little more modest (just about anything looks modest
compared to _that_ data load).

Petty limitations such as these two need to go away, and soon -- we're
getting used by big installations now. This isn't Stonebraker's research
Postgres anymore. The 7.1 removal of previous limitations was nearly overdue
-- and these two issues of ID wrap need to be addressed -- my gut feel is
that the reports of OID/XID wrap are going to skyrocket within 6 months as
bigger and bigger installations try out PostgreSQL/RHDB (fact is that many
are going to try it out _because_ it has been relabeled by Red Hat....).

The MySQL/NuSphere articles illustrate that -- the NuSphere guy goes as far
as saying that the support of _Red_Hat_ is what gives PG credibilitiy -- and,
you have to admit, RH's adoption of PG does increase, in many circles, PG's
credibility.

Of course, PG has credibility with me for other reasons -- it was, IMHO, just
a matter of time before Red Hat saw the PostgreSQL Light.....
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Lamar Owen <lamar.owen@wgcr.org> writes:

... these two issues of ID wrap need to be addressed -- my gut feel is
that the reports of OID/XID wrap are going to skyrocket within 6 months as
bigger and bigger installations try out PostgreSQL/RHDB

Yes, my thoughts exactly. We're trying to play in the big leagues now.
I don't believe we can put these problems off any longer.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Yikes, I am not sure we are ready to make oids optional.

We've discussed it enough, it's time to do it. I have an ulterior plan
here: I want 7.2 not to have any limitations that prevent it from being
used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed
now, or nearly. The other stumbling blocks for continuous runs are OID
wraparound and XID wraparound. We've got unique indexes on OIDs for all
system catalogs that need them (we were short a couple as of 7.1, btw),
but OID wrap is still likely to lead to unwanted "duplicate key"
failures. So we still need a way to reduce the system's appetite for
OIDs. In a configuration where OIDs are used only where *necessary*,
it'd be a long time till wrap. I also intend to do something about XID
wrap next month...

If you want to make oids optional on user tables, we can vote on that.
However, OID's keep our system tables together. Though we don't need
them on every system table, it seems they should be on all system tables
just for completeness. Are we really losing a significant amount of
oids through system tables?

Do we return unused oids back to the pool on backend exit yet?

Since WAL, and that was never a fundamental answer anyway.

Will we have cheap 64-bit oids by the time oid wraparound becomes an
issue?

No, we won't, because OID wrap is an issue already for any long-uptime
installation. (64-bit XIDs are not a real practical answer either,
btw.)

Have we had a wraparound yet?

-- 
  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

Lamar Owen <lamar.owen@wgcr.org> writes:

... these two issues of ID wrap need to be addressed -- my gut feel is
that the reports of OID/XID wrap are going to skyrocket within 6 months as
bigger and bigger installations try out PostgreSQL/RHDB

Yes, my thoughts exactly. We're trying to play in the big leagues now.
I don't believe we can put these problems off any longer.

Is the idea to make oid's optional, with them disabled by default on
user tables?

-- 
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is the idea to make oid's optional, with them disabled by default on
user tables?

My thought is to make OID generation optional on a per-table basis, and
disable it on system tables that don't need unique OIDs. (OID would
read as NULL on any row for which an OID wasn't generated.)

It remains to be debated exactly how users should control the choice for
user tables, and which choice ought to be the default. I don't have a
strong opinion about that either way, and am prepared to hear
suggestions.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is the idea to make oid's optional, with them disabled by default on
user tables?

My thought is to make OID generation optional on a per-table basis, and
disable it on system tables that don't need unique OIDs. (OID would
read as NULL on any row for which an OID wasn't generated.)

It remains to be debated exactly how users should control the choice for
user tables, and which choice ought to be the default. I don't have a
strong opinion about that either way, and am prepared to hear
suggestions.

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.

-- 
  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

On Wednesday 18 July 2001 16:06, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is the idea to make oid's optional, with them disabled by default on
user tables?

It remains to be debated exactly how users should control the choice for
user tables, and which choice ought to be the default. I don't have a
strong opinion about that either way, and am prepared to hear
suggestions.

SET OIDGEN boolean for database-wide default policy.
CREATE TABLE WITH OIDS for individual tables? CREATE TABLE WITHOUT OIDS?
?? Is this sort of thing addressed by any SQL standard (Thomas?)?
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Lamar Owen <lamar.owen@wgcr.org> writes:

On Wednesday 18 July 2001 16:06, Tom Lane wrote:

It remains to be debated exactly how users should control the choice for
user tables, and which choice ought to be the default. I don't have a
strong opinion about that either way, and am prepared to hear
suggestions.

SET OIDGEN boolean for database-wide default policy.
CREATE TABLE WITH OIDS for individual tables? CREATE TABLE WITHOUT OIDS?

Something along that line, probably.

?? Is this sort of thing addressed by any SQL standard (Thomas?)?

OIDs aren't standard, so the standards are hardly likely to help us
decide how they should work.

I think the really critical choice here is how much backwards
compatibility we want to keep. The most backwards-compatible way,
obviously, is OIDs on by default and things work exactly as they
do now. But if we were willing to bend things a little then some
interesting possibilities open up. One thing I've been wondering
about is whether an explicit WITH OIDS spec ought to cause automatic
creation of a unique index on OID for that table. ISTM that any
application that wants OIDs at all would want such an index...

regards, tom lane

If OIDs are dropped a mechanism for retrieving the primary key of the
last insert would be greatly appreciated. Heck, it would be useful
now (rather than returning OID).

I much prefer retrieving the sequence number after the insert than
before insert where the insert uses it. Especially when trigger
muckary is involved.

--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Lamar Owen" <lamar.owen@wgcr.org>
Cc: "Bruce Momjian" <pgman@candle.pha.pa.us>; "PostgreSQL-development"
<pgsql-hackers@postgresql.org>
Sent: Wednesday, July 18, 2001 4:30 PM
Subject: Re: OID wraparound (was Re: [HACKERS] pg_depend)

Lamar Owen <lamar.owen@wgcr.org> writes:

On Wednesday 18 July 2001 16:06, Tom Lane wrote:

It remains to be debated exactly how users should control the

choice for

user tables, and which choice ought to be the default. I don't

have a

strong opinion about that either way, and am prepared to hear
suggestions.

SET OIDGEN boolean for database-wide default policy.
CREATE TABLE WITH OIDS for individual tables? CREATE TABLE

WITHOUT OIDS?

Something along that line, probably.

?? Is this sort of thing addressed by any SQL standard (Thomas?)?

OIDs aren't standard, so the standards are hardly likely to help us
decide how they should work.

I think the really critical choice here is how much backwards
compatibility we want to keep. The most backwards-compatible way,
obviously, is OIDs on by default and things work exactly as they
do now. But if we were willing to bend things a little then some
interesting possibilities open up. One thing I've been wondering
about is whether an explicit WITH OIDS spec ought to cause automatic
creation of a unique index on OID for that table. ISTM that any
application that wants OIDs at all would want such an index...

regards, tom lane

---------------------------(end of

broadcast)---------------------------

TIP 1: subscribe and unsubscribe commands go to

majordomo@postgresql.org

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

If OIDs are dropped a mechanism for retrieving the primary key of the
last insert would be greatly appreciated. Heck, it would be useful
now (rather than returning OID).

I much prefer retrieving the sequence number after the insert than
before insert where the insert uses it. Especially when trigger
muckary is involved.

Doesn't currval() work for your needs.

-- 
  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

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

Also, without OID's, how do you fix EXACT duplicate records that happen
by accident?

LER

Original Message <<<<<<<<<<<<<<<<<<

On 7/18/01, 3:46:30 PM, Rod Taylor <rbt@barchord.com> wrote regarding Re:
OID wraparound (was Re: [HACKERS] pg_depend) :

Also, without OID's, how do you fix EXACT duplicate records that happen
by accident?

How about tid's? SELECT tid FROM tab1.

-- 
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Also, without OID's, how do you fix EXACT duplicate records that happen
by accident?

How about tid's? SELECT tid FROM tab1.

"SELECT ctid", actually, but that is still the fallback. (Actually
it always was --- OIDs aren't necessarily unique either, Larry.)

regards, tom lane

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

Optional OIDs: YES
No OIDs by default: YES

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.

So, add class' ID (uniq id from pg_class) when linking.

Vadim

Didn't know about that one, at least from the reading of the docs...

Thanks,
You answered the question. I knew OID's weren't unique, but they are
likely to be able to distinguish between 2 rows in the same table.

Maybe ctid needs to be documented better?

LER

Original Message <<<<<<<<<<<<<<<<<<

On 7/18/01, 4:32:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote regarding Re:
OID wraparound (was Re: [HACKERS] 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

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.

pg_description is a point I hadn't thought about --- it uses OIDs
to refer to pg_attribute entries. However, pg_description is pretty
broken in its assumptions about OIDs anyway. I'm inclined to change
it to be indexed by

(object type ID, object OID, attributenumber)

the same way that Philip proposed indexing pg_depend. Among other
things, that'd make it much cheaper to drop comments during a DROP
TABLE. You could just scan on (object type ID, object OID), and get
both the table and all its columns in a single indexscan search,
not one per column as happens now.

regards, tom lane

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.

Clearly, certain system tables *must* have OIDs --- pg_class, pg_type,
pg_operator, etc --- because we use those OIDs to refer to objects.
These are exactly the same tables that have unique indexes on OID.

However, I don't see the point of consuming OIDs for entries in, say,
pg_listener. The notion that it must have OIDs simply because it's
a system table seems silly.

pg_attribute is on the edge --- are table columns objects in their own
right, deserving of a separate OID, or not? So far I don't see any
really good reason why they should have one.

Since the goal is to minimize OID consumption, not assigning OIDs to
pg_attribute entries seems like a good idea. I don't think this is
just a marginal hack. ISTM the main source of OID consumption for an
up-and-running system (if it has no large user tables with OIDs) will be
creation of temp tables. We can expend two OIDs per temp table
(pg_class and pg_type), or we can expend N+9 for an N-column temp table
(the seven system attributes plus the N user ones plus pg_class and
pg_type). That's *at least* a 5x difference in steady-state rate of OID
consumption. If that doesn't get your attention, it should.

regards, tom lane

Larry Rosenman <ler@lerctr.org> writes:

Maybe ctid needs to be documented better?

I think it's documented about as well as OID is, actually --- see

http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-syntax-columns.html

which AFAIR is the only formal documentation of any of the system
columns.

regards, tom lane

currval() could work nicely, but thats an additional query. Currently
OID (in php among others) can be retrieved along with the insert
response which is instantly retrievable. This makes for a very quick
middleware enforced foreign key entry in other databases.

Returning the entire primary key of the last row inserted without
doing additional queries -- this is a known element which could be
cached -- could be very useful in these situations.

With tables requiring multi-key elements we do a second select asking
for currval()s of the sequences.

--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Rod Taylor" <rbt@barchord.com>
Cc: "Lamar Owen" <lamar.owen@wgcr.org>; "Tom Lane"
<tgl@sss.pgh.pa.us>; "PostgreSQL-development"
<pgsql-hackers@postgresql.org>
Sent: Wednesday, July 18, 2001 5:06 PM
Subject: Re: OID wraparound (was Re: [HACKERS] pg_depend)

If OIDs are dropped a mechanism for retrieving the primary key of

the

last insert would be greatly appreciated. Heck, it would be

useful

now (rather than returning OID).

I much prefer retrieving the sequence number after the insert than
before insert where the insert uses it. Especially when trigger
muckary is involved.

Doesn't currval() work for your needs.

--
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

On Wed, Jul 18, 2001 at 04:06:28PM -0400, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is the idea to make oid's optional, with them disabled by default on
user tables?

My thought is to make OID generation optional on a per-table basis, and
disable it on system tables that don't need unique OIDs. (OID would
read as NULL on any row for which an OID wasn't generated.)

How about generalizing this to user defineable system attributes? OID
would just be a special case: it's really just a system 'serial' isn't it?

We occasionally get calls for other system type attributes that would
be too expensive for every table, but would be useful for individual
tables. One is creation_timestamp. Or this could be a route to bringing
timetravel back in: start_date stop_date, anyone?

It remains to be debated exactly how users should control the choice for
user tables, and which choice ought to be the default. I don't have a
strong opinion about that either way, and am prepared to hear
suggestions.

Two ways come to mind: either special WITH options, at the end, or
a new per attribute SYSTEM keyword:

CREATE TABLE <...> WITH OIDS
CREATE TABLE <...> WITH TIMETRAVEL
CREATE TABLE <...> WITH DATESTAMP

CREAT TABLE foo (oid oid SYSTEM,
created timestamp SYSTEM DEFAULT CURRENT_TIMESTAMP,
my_id serial,
my_field text);

So, basically it just creates the type and gives it a negative attnum.
The 'oid system' case would need to be treated specially, hooking the
oid up to the system wide counter.

I'm not sure the special behavior of returning NULL for oid on a table
without one is going to be useful: any client code that expects everything
to have an oid is unlikely to handle NULL better than an error. In fact,
in combination with the MS-Access compatability hack of '= NULL' as
'IS NULL', I see a potential great loss of data:

SELECT oid,* from some_table;

<display to user for editing>

UPDATE some_table set field1=$field1, field2=$field2, <...> WHERE oid = $oid;

if $oid is NULL ... There goes the entire table.

Ross

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.

pg_description is a point I hadn't thought about --- it uses OIDs
to refer to pg_attribute entries. However, pg_description is pretty
broken in its assumptions about OIDs anyway. I'm inclined to change
it to be indexed by

(object type ID, object OID, attributenumber)

the same way that Philip proposed indexing pg_depend. Among other
things, that'd make it much cheaper to drop comments during a DROP
TABLE. You could just scan on (object type ID, object OID), and get
both the table and all its columns in a single indexscan search,
not one per column as happens now.

Remember most pg_description comments are not on column but on functions
and stuff. That attributenumber is not going to apply there.

-- 
  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

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

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Remember most pg_description comments are not on column but on functions
and stuff. That attributenumber is not going to apply there.

Sure, it'd just be zero for non-column items.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Remember most pg_description comments are not on column but on functions
and stuff. That attributenumber is not going to apply there.

Sure, it'd just be zero for non-column items.

What do we do with other columns that need descriptions and don't have
oid column. Make the attribute column mean something else? I just
don't see a huge gain here and lots of confusion. User tables are a
different story.

-- 
  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

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

On Wed, Jul 18, 2001 at 04:06:28PM -0400, Tom Lane wrote:

My thought is to make OID generation optional on a per-table basis, and
disable it on system tables that don't need unique OIDs. (OID would
read as NULL on any row for which an OID wasn't generated.)

How about generalizing this to user defineable system attributes? OID
would just be a special case: it's really just a system 'serial' isn't it?

Hmm. Of the existing system attributes, OID is the only one that's
conceivably optional --- ctid,xmin,xmax,cmin,cmax are essential to
the functioning of the system. (tableoid doesn't count here, since
it's a "virtual" attribute that doesn't occupy any storage space on
disk, and thus making it optional wouldn't buy anything.) So there's
no gain to be seen in that direction.

In the other direction, I have no desire to buy into adding creation
timestamp or anything else in this go-round. Maybe sometime in the
future.

BTW, I'm not intending to change the on-disk format of tuple headers;
if no OID is assigned to a row, the OID field will still be there,
it'll just be 0. Given that it's only four bytes, it's probably not
worth dealing with a variable header format to suppress the space usage.
(On machines where MAXALIGN is 8 bytes, there likely wouldn't be any
savings anyway.)

I wouldn't much care for dealing with a variable tuple header format to
support creation timestamp either, and that leads to the conclusion that
it's just going to be a user field anyway. People who need it can do it
with a trigger ...

I'm not sure the special behavior of returning NULL for oid on a table
without one is going to be useful: any client code that expects everything
to have an oid is unlikely to handle NULL better than an error.

Well, I can see three possible choices: return NULL, return zero, or
don't create an OID entry in pg_attribute at all for such a table
(I *think* that would be sufficient to prevent people from accessing
the OID column, but am not sure). Of these I'd think the first is
least likely to break stuff. However, you might be right that breaking
stuff is preferable to the possibility of an app that thinks it knows
what it's doing causing major data lossage because it doesn't.

regards, tom lane

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

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

What do we do with other columns that need descriptions and don't have
oid column.

Like what?

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

What do we do with other columns that need descriptions and don't have
oid column.

Like what?

Depends what other system tables you are intending to remove oid's for?

-- 
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

What do we do with other columns that need descriptions and don't have
oid column.

Like what?

Depends what other system tables you are intending to remove oid's for?

Nothing that requires a description ;-)

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

What do we do with other columns that need descriptions and don't have
oid column.

Like what?

Depends what other system tables you are intending to remove oid's for?

Nothing that requires a description ;-)

You are a sly one. :-)

-- 
  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

From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: OID wraparound (was Re: [HACKERS] pg_depend)
Date: Wed, 18 Jul 2001 13:52:45 -0400
Message-ID: <6335.995478765@sss.pgh.pa.us>

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Yikes, I am not sure we are ready to make oids optional.

We've discussed it enough, it's time to do it. I have an ulterior plan
here: I want 7.2 not to have any limitations that prevent it from being
used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed
now, or nearly.

What about pg_log? It will easily become a huge file. Currently the
only solution is re-installing whole database, that is apparently
unacceptable for very big installation like 1TB.

The other stumbling blocks for continuous runs are OID
wraparound and XID wraparound. We've got unique indexes on OIDs for all
system catalogs that need them (we were short a couple as of 7.1, btw),
but OID wrap is still likely to lead to unwanted "duplicate key"
failures. So we still need a way to reduce the system's appetite for
OIDs. In a configuration where OIDs are used only where *necessary*,
it'd be a long time till wrap. I also intend to do something about XID
wrap next month...

So are we going to remove OID? I see following in the SQL99 draft (not
sure it actually becomes a part of the SQL99 standard, though). Can we
implement the "Object identifier" without the current oid mechanism?

---------------------------------------------------------------------
4.10 Object identifier

An object identifier OID is a value generated when an object is
created, to give that object an immutable identity. It is unique in
the known universe of objects that are instances of abstract data
types, and is conceptually separate from the value, or state, of
the instance.

The object identifier type is described by an object identifier
type descriptor. An object identifier type descriptor contains:

- an indication that this is an object identifier type; and

- the name of the abstract data type within which the object
identifier type is used.

The object identifier type is only used to define the OID pseudo-
column implicitly defined in object ADTs within an ADT definition.

___________________________________________________________________

An OID literal exists for an object identifier type only if the
associated abstract data type was defined WITH OID VISIBLE. The OID
value is materialized as a character string with an implementation-
defined length and character set SQL_TEXT.

---------------------------------------------------------------------

Will we have cheap 64-bit oids by the time oid wraparound becomes an
issue?

No, we won't, because OID wrap is an issue already for any long-uptime
installation. (64-bit XIDs are not a real practical answer either,
btw.)

What's wrong with 64-bit oids (except extra 4bytes)?
--
Tatsuo Ishii

Tatsuo Ishii <t-ishii@sra.co.jp> writes:

What about pg_log? It will easily become a huge file. Currently the
only solution is re-installing whole database, that is apparently
unacceptable for very big installation like 1TB.

That's part of the XID wraparound issue, which is a separate
discussion... but yes, I want to do something about that for 7.2 also.

So are we going to remove OID?

No, only make it optional for user tables.

I see following in the SQL99 draft (not
sure it actually becomes a part of the SQL99 standard, though). Can we
implement the "Object identifier" without the current oid mechanism?

As near as I can tell, SQL99's idea of OIDs has little to do with ours
anyway. Note that they want to assign an OID to an "instance of an
abstract data type". Thus, if you created a table with several columns
each of which is one or another kind of ADT, then each column value
would contain an associated OID --- the OID is assigned to each value,
not to table rows.

My suspicion is that SQL99-style OIDs would be implemented as a separate
counter, and would be 8 bytes from the get-go.

What's wrong with 64-bit oids (except extra 4bytes)?

Portability, mostly. I'm not ready to tell platforms without 'long
long' that we don't support them at all anymore. If they don't have
int8, or someday they don't have SQL99 OIDs, that's one thing, but
zero functionality is something else.

I'm also somewhat concerned about the speed price of widening Datum to
8 bytes on machines where that's not a well-supported datatype --- note
that we'll pay for that almost everywhere, not only in Oid
manipulations.

regards, tom lane

What's wrong with 64-bit oids (except extra 4bytes)?

Portability, mostly.

Oh, there's one other small problem: breaking the on-the-wire protocol.
We send OIDs as column datatype identifiers, so an 8-byte-OID backend
would not interoperate with clients that didn't also think OID is 8
bytes. Aside from client/server compatibility issues, that raises the
portability ante a good deal --- not only your server machine has to
have 'long long' support, but so do all your application environments.

regards, tom lane

Bill Studenmund wrote:

On Wed, 18 Jul 2001, Hiroshi Inoue wrote:

Oops I made a mistake.
Reference name is needed not an object name,
i.e
object relid
object oid
relerence relid
reference oid
reference name

create table a (...);
create view view_a as select .. from a;

Then we have an pg_depend entry e.g.

pg_class_relid
oid of the view_a
pg_class_relid
oid of the table a
'a' the name of the table

and so on.

drop table a; (unadorned drop).

Then the above entry would be changed to

pg_class_relid(unchanged)
oid of the view_s(unchagned)
pg_class_relid(unchanged)
InvalidOid
'a' the name of the table(unchanged)

create table a (...);

Then the pg_depend entry would be

pg_class_relid(unchanged)
oid of the view_s(unchagned)
pg_class_relid(unchanged)
the oid of the new table a
'a' the name of the table(unchanged)

This step I disagree with. Well, I disagree with the automated aspect of
the update. How does postgres know that the new table a is sufficiently
like the old table that it should be used? A way the DBA could say, "yeah,
restablish that," would be fine.

You could DROP a table with CASCADE or RESTRICT keyword if
you hate the behavior.

regards,
Hiroshi Inoue

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

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.

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

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

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

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

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

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

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

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 |/

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

Bruce Momjian said:

[...]

No, we won't, because OID wrap is an issue already for any long-uptime
installation. (64-bit XIDs are not a real practical answer either,
btw.)

Have we had a wraparound yet?

Just for the record, I had an OID overflow on production database (most middleware crashed mysteriously but no severe data loss) about a month ago. This was on 7.0.2 which probably had some bug ... preventing real wrap to happen. No new allocations (INSERTs that used autoincrementing sequences) were possible in most tables.

Anyway, I had to dump/restore the database - several hours downtime. The database is not very big in size (around 10 GB in the data directory), but contains many objects (logs) and many objects are inserted/deleted from the database - in my opinion at not very high rate. Many tables are also created/dropped during processing.

What is worrying is that this database lived about half a year only...

In my opinion, making OIDs optional would help things very much. In my case, I don't need OIDs for log databases. Perhaps it would additionally help if OIDs are separately increasing for each database - not single counter for the entire PostgreSQL installation.

Regards,
Daniel

At 12:37 18/07/01 -0400, Tom Lane wrote:

Philip Warner <pjw@rhyme.com.au> writes:

At 11:38 18/07/01 -0400, Tom Lane wrote:

I'd just make the dependency be from view_a to a and keep things
simple. What's so wrong with recompiling the view for *every* change
of the underlying table?

Not a problem for views, but when you get to constraints on large tables,
re-evaluating all the constraints unnecessarily could be a nightmare, and
especially frustrating when you just dropped an irrelevant attr.

Huh? You seem to be thinking that we'd need to re-check the constraint
at each row of the table, but I don't see why we'd need to. I was just
envisioning re-parsing the constraint source text.

I'm paranoid, but there could be a case for doing so, especially if we
allow CHAR(n) to become CHAR(m) where m < n. Or any similar data-affecting
field change.

----------------------------------------------------------------
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 |/

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

J-P wrote:

I need to create a new system table like pg_log to
implement a replication scheme. The big problem is

how

I could get an OID for it, a unique OID that is
reserved for that table???

Hiroshi Inoue wrote:

Do you need the following ?

visco=# select oid from pg_class where relname =
'pg_log';
oid
------
1269
(1 row)

I'm afraid of misunderstanding.

Sorry my question was wrongly asked.
What I need is a unique OID for my new system table
that is reserved for that table?
A new Id that is not used by anything else, and that
will never be used.
(The reference to pg_log was just to show the
similarity of what I need).

N.B. I can't just
#select oid from pg_class
and take one that is not there, since I don't know if
the oid I choose will be used by something else in the
system??

Thanks for your help,
J-P

_______________________________________________________
Do You Yahoo!?
Get your free @yahoo.ca address at http://mail.yahoo.ca

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.

Sequences also cache values (32 afair) - ie one log record is required
for 32 nextval-s. Sequence' data file is updated at checkpoint time,
so - not so much IO. I really think that using sequences for system
tables IDs would be good.

Vadim

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

On Thu, 19 Jul 2001, Hiroshi Inoue wrote:

This step I disagree with. Well, I disagree with the automated aspect

of

the update. How does postgres know that the new table a is sufficiently
like the old table that it should be used? A way the DBA could say, "yeah,
restablish that," would be fine.

You could DROP a table with CASCADE or RESTRICT keyword if
you hate the behavior.

You didn't answer the question. :-)

"How does postgres know that the new table a is sufficiently like the old
table that it should be used?"

By making the reattachment automatic, you are saying that once we make an
object of a given name and make objects depend on it, we can never have
another object of the same name but different. Because PG is going to try
to re-attach the dependants for you.

That's different than current behavior, and strikes me as the system being
overly helpful (a class of behavior I personally find very annoying).

Please understand I like the idea of being ABLE to do this reattachment. I
can see a lot of places where it would be VERY useful. My vote though is
to just make reattachment a seperate step or something you flag, like in
the CREATE TABLE, say attach me to everything wanting a table of this
name. Make it something you have to indicate you want.

Take care,

Bill

Bill Studenmund wrote:

On Thu, 19 Jul 2001, Hiroshi Inoue wrote:

This step I disagree with. Well, I disagree with the automated aspect

of

the update. How does postgres know that the new table a is sufficiently
like the old table that it should be used? A way the DBA could say, "yeah,
restablish that," would be fine.

You could DROP a table with CASCADE or RESTRICT keyword if
you hate the behavior.

You didn't answer the question. :-)

"How does postgres know that the new table a is sufficiently like the old
table that it should be used?"

By making the reattachment automatic, you are saying that once we make an
object of a given name and make objects depend on it, we can never have
another object of the same name but different. Because PG is going to try
to re-attach the dependants for you.

That's different than current behavior, and strikes me as the system being
overly helpful (a class of behavior I personally find very annoying).

Please understand I like the idea of being ABLE to do this reattachment. I
can see a lot of places where it would be VERY useful.

It doesn't seem preferable that the default(unadorned) DROP
allows reattachement after the DROP. The default(unadorned) DROP
should be the same as DROP RESTRICT(or CASCADE because the current
behabior is halfway CASCADE?). How about adding another keyword
to allow reattachment after the DROP ?
All depende(a?)nt objects must be re-complied after the
reattachment and the re-compilation would fail if the new table
isn't sufficiently like the old one.

Anyway my opinion seems in a minority as usual.

regards,
Hiroshi Inoue

On Fri, 20 Jul 2001, Hiroshi Inoue wrote:

Bill Studenmund wrote:

"How does postgres know that the new table a is sufficiently like the old
table that it should be used?"

By making the reattachment automatic, you are saying that once we make an
object of a given name and make objects depend on it, we can never have
another object of the same name but different. Because PG is going to try
to re-attach the dependants for you.

That's different than current behavior, and strikes me as the system being
overly helpful (a class of behavior I personally find very annoying).

Please understand I like the idea of being ABLE to do this reattachment. I
can see a lot of places where it would be VERY useful.

It doesn't seem preferable that the default(unadorned) DROP
allows reattachement after the DROP. The default(unadorned) DROP
should be the same as DROP RESTRICT(or CASCADE because the current
behabior is halfway CASCADE?). How about adding another keyword
to allow reattachment after the DROP ?

Hmmm... My preference is for the subsequent CREATE to indicate if reattach
should happen or not. But I'm not sure if that would leave dangling depend
entries around.

All depende(a?)nt objects must be re-complied after the
reattachment and the re-compilation would fail if the new table
isn't sufficiently like the old one.

Anyway my opinion seems in a minority as usual.

Only partly. I think everyone likes the idea of being able to reattach
later, an idea you came up with. :-)

Take care,

Bill

On Fri, Jul 20, 2001 at 08:45:05AM +0900, Hiroshi Inoue wrote:

It doesn't seem preferable that the default(unadorned) DROP
allows reattachement after the DROP. The default(unadorned) DROP
should be the same as DROP RESTRICT(or CASCADE because the current
behabior is halfway CASCADE?). How about adding another keyword
to allow reattachment after the DROP ?
All depende(a?)nt objects must be re-complied after the
reattachment and the re-compilation would fail if the new table
isn't sufficiently like the old one.

Anyway my opinion seems in a minority as usual.

How about making that functionality happen with ALTER <FOO> REPLACE
as Tom suggested? If I'm wanting to change an underlying table, how
likely is it that I don't have the replacement ready right now?

So, instead of:

DROP <FOO> <name> WITH INTENT TO REPLACE

CREATE <FOO> <name> <body>

it's just:

ALTER <FOO> <name> REPLACE <body>

All nice and transactional: if the attempt to reattach one of the
subordinate objects fails, you roll back to the old one.

Ross

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

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.

Tom Lane wrote:

What's wrong with 64-bit oids (except extra 4bytes)?

Portability, mostly.

Oh, there's one other small problem: breaking the on-the-wire protocol.

So 8-byte-OID is for PostgreSQL 8? :-)

--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750

"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925

Tom mentioned what should be stored in the OID system column if no oid's
are in the table. He also mentioned that he doesn't want a
variable-length tuple header so will always have an oid system column.

What about moving the oid column out of the tuple header. This saves 4
bytes in the header in cases where there is no oid on the table.

If they ask for an OID in a table, make it the first column of a table.
Also, if they have asked for oid's on the table, odds are they want
SELECT * to show it.

Also, how about a GUC option that controls whether tables are created
with OID's by default.

-- 
  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

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.

But how can you do that ? The oid index is only created by
the dba for specific tables, thus your update would do an update
with a where restriction, that is not indexed.
This would be darn slow, no ?

How about instead selecting the primary key and one of the tid's
(I never remember which, was it ctid ?) instead, so you can validate
when a row changed between the select and the update ?

Andreas

Bruce Momjian <pgman@candle.pha.pa.us> writes:

What about moving the oid column out of the tuple header. This saves 4
bytes in the header in cases where there is no oid on the table.

No it doesn't --- at least not on machines where MAXALIGN is eight
bytes.

I don't think this is worth the trouble...

regards, tom lane

-----Original Message-----
Zeugswetter Andreas SB

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.

But how can you do that ? The oid index is only created by
the dba for specific tables, thus your update would do an update
with a where restriction, that is not indexed.
This would be darn slow, no ?

Please look at my another(previous ?) posting to pgsql-hackers.
I would use both TIDs and OIDs, TIDs for fast access, OIDs
for identification.

How about instead selecting the primary key and one of the tid's
(I never remember which, was it ctid ?) instead, so you can validate
when a row changed between the select and the update ?

Xmin is also available for row-versioning. But now I'm wondering
if TID/xmin are guranteed to keep such characteriscs.
Even Object IDentifier is about to lose the existence.
Probably all-purpose application mustn't use system columns
at all though I've never heard of it in other dbms-s.

regards,
Hiroshi Inoue

Would it be possible to offer an option for the OID column to get its value
from an int4 primary key (settable on a per table basis maybe)?
- Stuart

"Henshall, Stuart - WCP" wrote:

Would it be possible to offer an option for the OID column to get its value
from an int4 primary key (settable on a per table basis maybe)?
- Stuart

Sorry I don't understand well what you mean.
What kind of advantages are there if we let OIDs be optional
and allow such options like you offer ?

regards,
Hiroshi Inoue

I was thinking that this would help stop OID wrap around while not totally
breaking clients that used OIDs as row identifiers as they'd now have the
int4 primary key value (although I guess there could be risks if the client
assumes there'd be globally unique). Also the primary key would have to be
placed into the OID in all places it could be referenced (for WHERE
clauses,etc...). It'd only work on those tables that had int4 priamary keys,
but I suspect thats a fair few. I don't know wether this'd be worth while,
but was rather throwing it out for thought.
- Stuart