Thread: possible constraint bug?

Is this particular to postgres or is this a normal SQLxx standard behavior?
-
- Thomas Swan                                   
- Graduate Student  - Computer Science
- The University of Mississippi
-
- "People can be categorized into two fundamental
- groups, those that divide people into two groups
- and those that don't."

This is particular to postgres, although the 
SQL behavior would have either dropped 
the constraint or prevented the drop in the
first place.

There's been some talk of an ALTER FUNCTION
that would let you change the code behind
a function without a drop/create.

Generally you have to re-generate things that
reference functions that have been dropped
and re-created.  This is a pain right now
for constraints, since it requires a dump
and restore of the table.

On Fri, 18 Aug 2000, Thomas Swan wrote:

> 
> Using Postgresql 7.0.2 (Linux x86, 2.2.16)
> 
> CERATE FUNCTION foo(text)
> 
> CREATE TABLE bar(
>          fud TEXT CHECK (foo(fud))
> );
> 
> DROP FUNCTION foo(TEXT);
> CREATE FUNCTION foo( .....);
> 
> INSERT INTO bar VALUES ('Hey'); results in the following error
> 
> ERROR init_fcache: Cache lookup failed for procedure 128384
> 
> Is this particular to postgres or is this a normal SQLxx standard behavior?

At 02:35 PM 8/18/00 -0500, Thomas Swan wrote: 

<excerpt>

ERROR init_fcache: Cache lookup failed for procedure 128384


Is this particular to postgres or is this a normal SQLxx standard
behavior? 

</excerpt><<<<<<<<


Particular to PG and a known limitation.  I think there's been some 
talk

about the possibility of doing a "create or replace" ala Oracle, which
also

has similar problems and uses this as a workaround (replaces it if it
exists

in such a way that existing references don't break).






- Don Baccus, Portland OR <<dhogaza@pacifier.com>
 Nature photos, on-line guides, Pacific Northwest
 Rare Bird Alert Service and other goodies at
 http://donb.photo.net.

Is this addressed in 7.1?

> 
> This is particular to postgres, although the 
> SQL behavior would have either dropped 
> the constraint or prevented the drop in the
> first place.
> 
> There's been some talk of an ALTER FUNCTION
> that would let you change the code behind
> a function without a drop/create.
> 
> Generally you have to re-generate things that
> reference functions that have been dropped
> and re-created.  This is a pain right now
> for constraints, since it requires a dump
> and restore of the table.
> 
> On Fri, 18 Aug 2000, Thomas Swan wrote:
> 
> > 
> > Using Postgresql 7.0.2 (Linux x86, 2.2.16)
> > 
> > CERATE FUNCTION foo(text)
> > 
> > CREATE TABLE bar(
> >          fud TEXT CHECK (foo(fud))
> > );
> > 
> > DROP FUNCTION foo(TEXT);
> > CREATE FUNCTION foo( .....);
> > 
> > INSERT INTO bar VALUES ('Hey'); results in the following error
> > 
> > ERROR init_fcache: Cache lookup failed for procedure 128384
> > 
> > Is this particular to postgres or is this a normal SQLxx standard behavior?
> 
> 


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

On Thu, 12 Oct 2000, Bruce Momjian wrote:

> Is this addressed in 7.1?

Not as far as I know.
It would require one or more of:ALTER FUNCTIONALTER TABLE ... DROP CONSTRAINTa reference system that automatically
drops/restricts based on objects referencing the  thing you drop (and this wouldn't make sense for constraints without
alter... drop  constraint anyway).
 

> 
> On Thu, 12 Oct 2000, Bruce Momjian wrote:
> 
> > Is this addressed in 7.1?
> 
> Not as far as I know.
> It would require one or more of:
>  ALTER FUNCTION
>  ALTER TABLE ... DROP CONSTRAINT
>  a reference system that automatically drops/
>   restricts based on objects referencing the 
>   thing you drop (and this wouldn't make sense
>   for constraints without alter ... drop 
>   constraint anyway).
> 
> 

Added to TODO:

* Add ALTER FUNCTION
* Add ALTER TABLE ... DROP CONSTRAINT
* Automatically drop constraints/functions when object is dropped

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