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