Thread: CREATE/DROP OPERATOR CLASS

CREATE/DROP OPERATOR CLASS

From
Tom Lane
Date:
Now that we have dependencies implemented, it would be a real good idea
if operator classes could be included in the web of dependencies.
However, the pg_depends code implicitly assumes that it can do a DROP,
if demanded, for any entity referenced by a dependency link.  This means
we need DROP OPERATOR CLASS.

I am thinking of picking up Bill Studenmund's patch from last fall for
CREATE OPERATOR CLASS and bringing it forward into current sources,
and adding DROP support too.

In our last episode, Peter said:
> I'm having a few issues with the syntax.

I *think* what we had agreed to was

CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE type USING accessmethod
AS {   FUNCTION number name(parms)    | OPERATOR number name [ ( type, type ) ] [ RECHECK ]    | STORAGE typename  } [,
...]

... at least that's the last message in the thread I can find in my
archives.  Anyone unhappy with this?  (Obviously the names will now
need to include optional schema qualification.)

Drop will need to look approximately like

DROP OPERATOR CLASS name USING accessmethod

since opclass names are only unique within a specific index AM.

I don't think we'd discussed permissions issues.  My inclination
is to restrict CREATE to the owner of the underlying type, on the
grounds that letting other people do it interferes with the legitimate
rights of the type owner (cf my comments about creating casts,
yesterday).  I'm willing to listen to other arguments though.
We do have an "opcowner" column in pg_opclass, so in any case we
will record the creator and allow only him to do DROP.

Thoughts, objections?

I'm off to San Diego for a week for the O'Reilly convention, and
was casting about for some fairly self-contained project to take
with me for idle moments.  This looks like it would do...
        regards, tom lane


Re: CREATE/DROP OPERATOR CLASS

From
"Christopher Kings-Lynne"
Date:
> Now that we have dependencies implemented, it would be a real good idea
> if operator classes could be included in the web of dependencies.
> However, the pg_depends code implicitly assumes that it can do a DROP,
> if demanded, for any entity referenced by a dependency link.

Hmmm...does this mean that there is any situation in which there would be a
cascade delete of a _column_?

ie.  If you drop the domain a column is using with the cascade option, will
the column get dropped automatically?  That doesn't sound like very nice
behaviour...?

Chris



Re: CREATE/DROP OPERATOR CLASS

From
Rod Taylor
Date:
On Sun, 2002-07-21 at 21:20, Christopher Kings-Lynne wrote:
> > Now that we have dependencies implemented, it would be a real good idea
> > if operator classes could be included in the web of dependencies.
> > However, the pg_depends code implicitly assumes that it can do a DROP,
> > if demanded, for any entity referenced by a dependency link.
> 
> Hmmm...does this mean that there is any situation in which there would be a
> cascade delete of a _column_?
> 
> ie.  If you drop the domain a column is using with the cascade option, will
> the column get dropped automatically?  That doesn't sound like very nice
> behaviour...?

If you drop the domain a column is using, the column has become
completely useless -- and the table broken.

More obvious if you drop a user defined type that a column was using. Or
the functions that the user defined type depended on to import / export
data then there is no method to insert or extract information from that
column -- or more to the point any tuple within that table.

This is what CASCADE does.  Ensures the structure is clean much like
foreign keys and constraints help keep data clean.

RESTRICT is there for those who don't fully understand the structure. 
Cautiously test the waters to see what else you may be affecting first.



Re: CREATE/DROP OPERATOR CLASS

From
"Christopher Kings-Lynne"
Date:
> More obvious if you drop a user defined type that a column was using. Or
> the functions that the user defined type depended on to import / export
> data then there is no method to insert or extract information from that
> column -- or more to the point any tuple within that table.
> 
> This is what CASCADE does.  Ensures the structure is clean much like
> foreign keys and constraints help keep data clean.
> 
> RESTRICT is there for those who don't fully understand the structure. 
> Cautiously test the waters to see what else you may be affecting first.


Yeah, I guess you're right!

Chris



Re: CREATE/DROP OPERATOR CLASS

From
Tom Lane
Date:
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Hmmm...does this mean that there is any situation in which there would be a
> cascade delete of a _column_?

You bet.  That's why we need DROP COLUMN done ;-)

> ie.  If you drop the domain a column is using with the cascade option, will
> the column get dropped automatically?  That doesn't sound like very nice
> behaviour...?

What did you think CASCADE was for?
        regards, tom lane