Thread: Return type of triger functions from OPAQUE to TRIGGER in 7.3

Return type of triger functions from OPAQUE to TRIGGER in 7.3

From
Roberto Mello
Date:
In OpenACS, we have _a lot_ of functions, and many many triggers. Having
endured the pains of trying to write upgrade scripts for functions, we
were overjoyed with the addition of CREATE OR REPLACE for functions.

7.3 is looking great, so we're starting migrating towards it, with an eye
on maintaining 7.2 compatibility for those that need it.

We found out a few days ago that in PG 7.3 the return type for trigger 
functions was redefined from OPAQUE to TRIGGER. What's the rationale
behind this? Is this for standards compliance (forgive my ignorance about
the standard's details)? Were there other gains to be obtained from this
move?

It seems that new functions are automatically converted from OPAQUE to
TRIGGER return type, but when one tries to CREATE OR REPLACE such
function, we get an error saying that the return type of a function can't
be redefined.

I would classify this as a bug, because it defeats the purpose of CREATE
OR REPLACE, at least for triggers. If the behavior of CREATE FUNCTION is to
change OPAQUE to TRIGGER, then CREATE OR REPLACE FUNCTION should be consistent 
with that behavior.

So right now we are stuck on either changing the return type of every trigger 
for TRIGGER (which means no 7.2 compatibility) or stay stuck in 7.2-land.

Are there plans to make the behavior of CREATE OR REPLACE FUNCTION consistent with
that of CREATE FUNCTION?

Thanks,

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
Air conditioned environment - Do not open Windows.


Re: Return type of triger functions from OPAQUE to TRIGGER in 7.3

From
Josh Berkus
Date:
Roberto,

> We found out a few days ago that in PG 7.3 the return type for trigger
> functions was redefined from OPAQUE to TRIGGER. What's the rationale
> behind this? Is this for standards compliance (forgive my ignorance about
> the standard's details)? Were there other gains to be obtained from this
> move?

Yes.  In < 7.3, OPAQUE actually referred to 3 different types of non-parsable
return values.   Differntiating the different types should be an improvement
in both performance and error prevention.

> So right now we are stuck on either changing the return type of every
> trigger for TRIGGER (which means no 7.2 compatibility) or stay stuck in
> 7.2-land.

Seems to me that this could be done to the whole OpenACS package with a Perl
or Sed script.

--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco


Re: Return type of triger functions from OPAQUE to TRIGGER in 7.3

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Yes.  In < 7.3, OPAQUE actually referred to 3 different types of non-parsable
> return values.   Differntiating the different types should be an improvement 
> in both performance and error prevention.

Actually, there were at least half a dozen distinguishable meanings of
"OPAQUE" in older releases :-(.  The failure to make these distinctions
led to such infamous security holes as the "select cash_out(2)" bug.
I don't think there's any real speed gain involved ... but not having
the backend dump core when you pass something the wrong kind of argument
is a big improvement IMHO.

>> I would classify this as a bug, because it defeats the purpose of CREATE
>> OR REPLACE, at least for triggers. If the behavior of CREATE FUNCTION is to
>> change OPAQUE to TRIGGER, then CREATE OR REPLACE FUNCTION should be
>> consistent with that behavior.

But that *isn't* the behavior of CREATE FUNCTION.  There are some hacks
(temporary ones, I hope) in CREATE TRIGGER and other places to adjust
the type of an existing function rather than declaring error.  We
intended those hacks only to serve to let existing pre-7.3 pg_dump scripts
be loaded into 7.3; not as an indefinite equivalencing of OPAQUE with
other types, because that would defeat the purpose of introducing the
other types to begin with.

What you're essentially suggesting is that "CREATE OR REPLACE FUNCTION
foo() RETURNS OPAQUE" shouldn't fail if it finds an existing function
returning trigger --- nor, presumably, any of the several other
pseudotypes that replaced OPAQUE.  We could do that, I suppose, but it
strikes me as opening a big hole for the same kinds of mistakes that we
are trying to prevent.  Who's to say whether the function definition
you're supplying is actually meant as a trigger, or a language handler,
or the other possibilities?

Also, it would be inconsistent with the behavior in the cases where
an OPAQUE input parameter gets adjusted.  There's no chance of "CREATE
OR REPLACE FUNCTION foo(opaque) ..." even realizing which function you
meant to replace, if the existing function's input type has been changed
to cstring or something else entirely.

>> So right now we are stuck on either changing the return type of every
>> trigger for TRIGGER (which means no 7.2 compatibility) or stay stuck in
>> 7.2-land.

It's hard to believe that this is the only thing standing between you
and having SQL code that works in both 7.2 and 7.3.  That would mean
that you have no intention of adopting *any* 7.3 improvements.  Is that
really your plan?
        regards, tom lane


Re: Return type of triger functions from OPAQUE to TRIGGER in 7.3

From
Roberto Mello
Date:
On Tue, Feb 25, 2003 at 04:42:35PM -0800, Josh Berkus wrote:
> 
> > So right now we are stuck on either changing the return type of every
> > trigger for TRIGGER (which means no 7.2 compatibility) or stay stuck in
> > 7.2-land.
> 
> Seems to me that this could be done to the whole OpenACS package with a Perl 
> or Sed script.

Yes, I have heard of regular expressions :-) That was not my point, as you
can see if you'll re-read my phrase. But thanks for your reply. 

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
ASCII stupid question, get stupid ANSI.


Re: Return type of triger functions from OPAQUE to TRIGGER in 7.3

From
Roberto Mello
Date:
On Tue, Feb 25, 2003 at 04:10:14PM -0500, Tom Lane wrote:
> 
> Actually, there were at least half a dozen distinguishable meanings of
> "OPAQUE" in older releases :-(.  The failure to make these distinctions
> led to such infamous security holes as the "select cash_out(2)" bug.
> I don't think there's any real speed gain involved ... but not having
> the backend dump core when you pass something the wrong kind of argument
> is a big improvement IMHO.

Ok, I see. Makes sense now. I was trying to understand the rationale for
the change, and you explained it well.
> >> I would classify this as a bug, because it defeats the purpose of CREATE
> >> OR REPLACE, at least for triggers. If the behavior of CREATE FUNCTION is to
> >> change OPAQUE to TRIGGER, then CREATE OR REPLACE FUNCTION should be
> >> consistent with that behavior.
> 
> But that *isn't* the behavior of CREATE FUNCTION.  There are some hacks
> (temporary ones, I hope) in CREATE TRIGGER and other places to adjust
> the type of an existing function rather than declaring error.  We
> intended those hacks only to serve to let existing pre-7.3 pg_dump scripts
> be loaded into 7.3; not as an indefinite equivalencing of OPAQUE with
> other types, because that would defeat the purpose of introducing the
> other types to begin with.

Ok.
> What you're essentially suggesting is that "CREATE OR REPLACE FUNCTION
> foo() RETURNS OPAQUE" shouldn't fail if it finds an existing function
> returning trigger --- nor, presumably, any of the several other
> pseudotypes that replaced OPAQUE.  We could do that, I suppose, but it
> strikes me as opening a big hole for the same kinds of mistakes that we
> are trying to prevent.  Who's to say whether the function definition
> you're supplying is actually meant as a trigger, or a language handler,
> or the other possibilities?

No, that was not what I was suggesting. I was suggesting (based on my
assumption that 'create function ... returns opaque' would convert the
return type to trigger), that its behaviour be made consistent with
'create or replace function' and throw an error if the type is opaque.

> It's hard to believe that this is the only thing standing between you
> and having SQL code that works in both 7.2 and 7.3.  That would mean
> that you have no intention of adopting *any* 7.3 improvements.  Is that
> really your plan?

For our next release, our plan was to have both 7.2 and 7.3
compatibility. For the release after that, 7.2 compatibility would
probably be dumped in favor of 7.3, where we could make use of 7.3-specific
features. We wanted it to be a gradual path.

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
OS/2, Windows/0


Re: Return type of triger functions from OPAQUE to TRIGGER in 7.3

From
Josh Berkus
Date:
Roberto,

> No, that was not what I was suggesting. I was suggesting (based on my
> assumption that 'create function ... returns opaque' would convert the
> return type to trigger), that its behaviour be made consistent with
> 'create or replace function' and throw an error if the type is opaque.

I can't see any way to do that without making it very difficult to upgrade to
7.3.

> For our next release, our plan was to have both 7.2 and 7.3
> compatibility. For the release after that, 7.2 compatibility would
> probably be dumped in favor of 7.3, where we could make use of 7.3-specific
> features. We wanted it to be a gradual path.

Where does OpenACS do its object creation?  Just at setup/install time, or
through the life of the program?   If the former, I'd suggest having two
seperate database install scripts ... one for 7.2.and one for 7.3.

--
Josh Berkus
Aglio Database Solutions
San Francisco