Thread: Return type of triger functions from OPAQUE to TRIGGER in 7.3
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.
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
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
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.
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
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