pg_depend
Bruce Momjian
that tracks object dependencies. TODO updated.
---------------------------------------------------------------------------
DEPENDENCY CHECKING / pg_depend
* Auto-destroy sequence on DROP of table with SERIAL, perhaps with a
separate SERIAL type
* Prevent column dropping if column is used by foreign key
* Propagate column or table renaming to foreign key constraints
* Automatically drop constraints/functions when object is dropped
* Make constraints clearer in dump file
* Make foreign keys easier to identify
--
Bruce Momjian | http://candle.pha.pa.us
pg...@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Peter Eisentraut
> 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 pet...@gmx.net http://funkturm.homeip.net/~peter
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Bruce Momjian
>
> > 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
pg...@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Alex Pilosov
> 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...
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Peter Eisentraut
> > 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 pet...@gmx.net http://funkturm.homeip.net/~peter
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
Alex Pilosov
> 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?
Bruce Momjian
>
> > > 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
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Bruce Momjian
> object sysrelid
> object oid
> reference sysrelid
> references oid
--
Bruce Momjian | http://candle.pha.pa.us
pg...@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
---------------------------(end of broadcast)---------------------------
Bill Studenmund
> 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
Bruce Momjian
> 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
pg...@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
---------------------------(end of broadcast)---------------------------
Hiroshi Inoue
>
> 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
Bill Studenmund
> 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
Tom Lane
> 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
Bruce Momjian
> > 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
pg...@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
---------------------------(end of broadcast)---------------------------
Peter Eisentraut
> > 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 pet...@gmx.net http://funkturm.homeip.net/~peter
Bruce Momjian
>
> > > 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
pg...@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
---------------------------(end of broadcast)---------------------------
Peter Eisentraut
> > 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 pet...@gmx.net http://funkturm.homeip.net/~peter
---------------------------(end of broadcast)---------------------------
Bruce Momjian
>
> > > 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
> 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?
Peter Eisentraut
> 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 pet...@gmx.net http://funkturm.homeip.net/~peter
---------------------------(end of broadcast)---------------------------
Tom Lane
> 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
Bruce Momjian
> 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
pg...@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
---------------------------(end of broadcast)---------------------------
Peter Eisentraut
> 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 pet...@gmx.net http://funkturm.homeip.net/~peter
---------------------------(end of broadcast)---------------------------
Bruce Momjian
>
> > 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
pg...@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
---------------------------(end of broadcast)---------------------------
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
Bill Studenmund
> 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
> 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
---------------------------(end of broadcast)---------------------------
Bill Studenmund
> Bill Studenmund <wrst...@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.
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 like
object relid
object oid
object name
reference relid
reference oid
and unadorned DROP doesn't drop dependent objects.
regards,
Hiroshi Inoue
Bruce Momjian
> 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
pg...@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
---------------------------(end of broadcast)---------------------------
Hiroshi Inoue
>
> > 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
Bruce Momjian
>
> 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
pg...@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
---------------------------(end of broadcast)---------------------------
Philip Warner
>
>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 |/
Ross J. Reedstrom
>
> 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
Philip Warner
>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 |/
---------------------------(end of broadcast)---------------------------
Ross J. Reedstrom
> 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
Bruce Momjian
> > I think any deisgn needs to cater for attr dependencies. eg.
>
> 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.
Tom Lane
> 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
Philip Warner
>> 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
>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 |/
---------------------------(end of broadcast)---------------------------
Bruce Momjian
> 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
pg...@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
---------------------------(end of broadcast)---------------------------
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
Bruce Momjian
> >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.
> >
>
> 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
pg...@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
---------------------------(end of broadcast)---------------------------
Bill Studenmund
> 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
Tom Lane
>> 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
---------------------------(end of broadcast)---------------------------
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
Hiroshi Inoue
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> 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
Bruce Momjian
> > 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?