Обсуждение: Name proliferation in functions with OUT parameters
Hi developers! Hi Dave!
Testing pgAdmin III v1.6.3 rev: 6112, client Win XP, host: Debian Sarge,
PG 8.1.8.
I got nonsensical error messages when trying to change properties of a
function in the properties dialogue window.
After some investigation I was able to build the following, very simple
testcases that should demonstrate the problem.
1.) 2 unnamed IN parameters, 1 named OUT parameter
CREATE FUNCTION test1(integer, integer, OUT a integer) AS
'SELECT 1, 1;'
LANGUAGE 'sql' VOLATILE;
Now, try changing the volatility to "STABLE" in the properties dialogue
window. It seems to work, but pgAdmin messes with parameter names:
That's the actual command sent back to the database:
CREATE OR REPLACE FUNCTION test1(IN a integer, IN integer, OUT
integer) AS
'SELECT 1;'
LANGUAGE 'sql' STABLE;
Note how the name was "a" was transferred to the first IN parameter.
(The IAEA would call this illegal proliferation!!)
2.) 2 unnamed IN parameters, 2 named OUT parameter
CREATE FUNCTION test2(integer, integer, OUT a integer, OUT b integer) AS
'SELECT 1, 1;'
LANGUAGE 'sql' VOLATILE;
Try changing the volatility to "STABLE" in the properties dialogue
window again. This time it fails with an error message.
The command sent to the database:
CREATE OR REPLACE FUNCTION test2(IN a integer, IN b integer, OUT
integer, OUT integer) AS
'SELECT 1, 1;'
LANGUAGE 'sql' STABLE;
The logged error message:
FEHLER: kann Rückgabetyp einer bestehenden Funktion nicht ändern
DETAIL: Der von OUT-Parametern bestimmte Zeilentyp ist verschieden.
TIPP: Verwenden Sie zuerst DROP FUNCTION.
ANWEISUNG: CREATE OR REPLACE FUNCTION test2(IN a integer, IN b
integer, OUT integer, OUT integer) AS
'SELECT 1, 1;'
LANGUAGE 'sql' STABLE;
Not sure why postgres wouldn't throw an error in the first case. Looks
like the same error to me. Maybe a glitch in postgresql itself?
3.) 1 unnamed IN parameters, 1 named OUT parameter
CREATE FUNCTION test3(IN a integer, integer, OUT b integer) AS
'SELECT 1;'
LANGUAGE 'sql' VOLATILE;
Gets transformed to:
CREATE OR REPLACE FUNCTION test3(IN a integer, IN b integer, OUT
integer) AS
'SELECT 1;'
LANGUAGE 'sql' STABLE;
4.) 2 named IN parameters, 2 named OUT parameter
CREATE FUNCTION test4(IN a integer, IN b integer, OUT a integer, OUT b
integer) AS
'SELECT 1,1;'
LANGUAGE 'sql' VOLATILE;
This example finally works as it should. You get the idea ...
Names of OUT parameters are "shifted" to unnamed IN parameters, one
after the other.
Regards
Erwin
Erwin Brandstetter wrote: > This example finally works as it should. You get the idea ... > Names of OUT parameters are "shifted" to unnamed IN parameters, one > after the other. Hi Erwin, I rewrote all the code that handles that in SVN trunk the other day - I just ran all your tests through it and it seems fine now. The problem was that the old code was written originally to handle just datatypes, which then had modes and later names added to the existing strings. The code parsed and analysed each string to death to figure out how to render the controls on the dialog. To complicate matters, EDB stored procedures use different ordering for the elements as well! The browser code was rewritten a while back, which made it *vastly* more robust and significantly less complex, and the dialog was done last week. We now keep a set of 3 arrays to track the data. http://svn.pgadmin.org/cgi-bin/viewcvs.cgi?rev=6259&view=rev http://svn.pgadmin.org/cgi-bin/viewcvs.cgi?rev=6014&view=rev Thanks for the bug report though! I'll post a new snapshot soon and mail the list so you can be using more up to date code. Regards, Dave
Hi Dave! dpage@postgresql.org wrote: > http://svn.pgadmin.org/cgi-bin/viewcvs.cgi?rev=6259&view=rev > http://svn.pgadmin.org/cgi-bin/viewcvs.cgi?rev=6014&view=rev > > Thanks for the bug report though! I'll post a new snapshot soon and > mail the list so you can be using more up to date code. I've had a look at the code. Sure looks like a major rewrite even if one can't properly read C++. Promising! :) I think the reported issue is rather grave, as it can silently change functionality and break things. Therefore a new snapshot is very welcome. Btw: I am sure you are aware that OUT parameters or parameter names are not part of the function signature. This entry in the changelog suggests otherwise - or did I get it wrong? http://pgadmin.org/development/changelog.php > 2007-03-14 DP 1.8.0 Function/procedure signatures do not include OUT params. > Fix this oversight, and cleanup much of the related code. Currently pgAdmin 1.6.3 includes OUT parameters in many places where they aren't actually needed. Example: ALTER FUNCTION myfunc(IN a integer, OUT b text) OWNER TO postgres; This would suffice: ALTER FUNCTION myfunc(integer) OWNER TO postgres; Sorry if I am telling you things you already know. Just to make sure .. Speaking of function management - I have mentioned this before on list (as have others): one of my biggest wishes would be to merge the two nodes "Procedures" and "Functions". The separation does not fit in with Postgresql. It's causing me extra work and is a constant source of confusion. Is this changelog entry the good news I think it is? 2007-03-09 DP 1.8.0 Treat all functions & procedures as 'Functions' in PostgreSQL, and class only EnterpriseDB edbspl functions returning void as 'Procedures'. Regards Erwin
Erwin Brandstetter wrote: > Hi Dave! > > dpage@postgresql.org wrote: >> http://svn.pgadmin.org/cgi-bin/viewcvs.cgi?rev=6259&view=rev >> http://svn.pgadmin.org/cgi-bin/viewcvs.cgi?rev=6014&view=rev >> >> Thanks for the bug report though! I'll post a new snapshot soon and >> mail the list so you can be using more up to date code. > > I've had a look at the code. Sure looks like a major rewrite even if one > can't properly read C++. Promising! :) > I think the reported issue is rather grave, as it can silently change > functionality and break things. Therefore a new snapshot is very welcome. Later tonight, if not, tomorrow - promise!! > Btw: I am sure you are aware that OUT parameters or parameter names are > not part of the function signature. This entry in the changelog suggests > otherwise - or did I get it wrong? > http://pgadmin.org/development/changelog.php > >> 2007-03-14 DP 1.8.0 Function/procedure signatures do not include OUT >> params. >> Fix this oversight, and cleanup much of the >> related code. You're reading it backwards. I was fixing the incorrect code that did include the out params when in shouldn't have. It's correct now - it's been extensively tested with PostgreSQL functions and EnterpriseDB stored procedures. I'm sure you'll still find something I missed though :-p > Currently pgAdmin 1.6.3 includes OUT parameters in many places where > they aren't actually needed. Example: > ALTER FUNCTION myfunc(IN a integer, OUT b text) OWNER TO postgres; > This would suffice: > ALTER FUNCTION myfunc(integer) OWNER TO postgres; > > Sorry if I am telling you things you already know. Just to make sure .. Yeah - per the message, it was fixed for 1.8.0. It was too much of an invasive fix for 1.6.x, and too close to 1.8 anyway. > > Speaking of function management - I have mentioned this before on list > (as have others): one of my biggest wishes would be to merge the two > nodes "Procedures" and "Functions". The separation does not fit in with > Postgresql. It's causing me extra work and is a constant source of > confusion. Is this changelog entry the good news I think it is? > > 2007-03-09 DP 1.8.0 Treat all functions & procedures as 'Functions' in > PostgreSQL, and class only EnterpriseDB edbspl > functions > returning void as 'Procedures'. Yup. If it ain't an edbspl stored procedure returning void it's a function. The Procedures collection node isn't even shown unless your running EnterpriseDB. Regards, Dave.