Обсуждение: Overloading functions that are used by operators.
I have a question regarding operators which are causing problems with the new pgAdmin III software being developed and my database.
The problem has been discussed in depth with the pgAdmin III development team and they have not been able to resolve the issue and suggested that I have either done something that I shouldn't have of or that there is a possible bug with postgresql.
Background information:
I have my own data type ('citext') that uses many of the built-in system functions based on the fact that the storage is identical to the data type 'text'.
For example I have overloaded the function 'textcat' and the definition I have used is:
CREATE
FUNCTION pg_catalog.textcat(citext, citext) RETURNS citext AS 'textcat' LANGUAGE 'internal' IMMUTABLE STRICT;When you view the functions via the pg_proc table you will see that there are two functions with 'textcat' in the column pg_proc.proname.
I have been using the database for over a year and had no reported problems with using the operator || on data types 'text', 'varchar', 'bpchar' or calling the overloaded function 'textcat(citext, citext)'.
First question:
Am I allowed to overload functions, for example the 'textcat' function, when they are being used by system operators?
If the answer is no then read no further - the problem is mine and I will have to work around it.
The system has at least three operators such as:
||(text,text)
||(varchar, text)
||(bpchar, text)
All use the function 'textcat'.
Now the new pgAdmin III software interrogates the pg_operator table to extract as much information as possible about the operators.
They are using the following SQL statement to retrieve the information that they want.
SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner, op.oprkind, op.oprcanhash,
op.oprleft, op.oprright,
lt.typname as lefttype,
rt.typname as righttype,
et.typname as resulttype,
co.oprname as compop,
ne.oprname as negop,
lso.oprname as leftsortop,
rso.oprname as rightsortop,
lco.oprname as lscmpop,
gco.oprname as gtcmpop,
po.proname as operproc,
pj.proname as joinproc,
pr.proname as restrproc,
description
FROM pg_operator op
JOIN pg_type lt ON lt.oid=op.oprleft
JOIN pg_type rt ON rt.oid=op.oprright
JOIN pg_type et on et.oid=op.oprresult
LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom
LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate
LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop
LEFT OUTER JOIN pg_operator rso ON rso.oid=op.oprrsortop
LEFT OUTER JOIN pg_operator lco ON lco.oid=op.oprltcmpop
LEFT OUTER JOIN pg_operator gco ON gco.oid=op.oprgtcmpop
JOIN pg_proc po ON po.oid=op.oprcode
LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest
LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin
LEFT OUTER JOIN pg_description des ON des.objoid=op.oid
WHERE op.oprnamespace = 2200::oid
ORDER BY op.oprname
The offending part of this SQL statement is the join:
JOIN pg_proc po ON po.oid=op.oprcode
It produces the error message "ERROR: There is more than one procedure named textcat".
Investigation into this has shown that the type conversion of the column named pg_operator.oprcode to type oid is done by the function call to:
regprocin('textcat')
My question would be - what is the correct way to find the function's oid that this operator uses? But first read on in case it is not necessary...
It seems to me that the join statements they are using are unnecessary so long as the column pg_operator.oprcode has the identical name to its matching function that has its name defined by column pg_proc.proname, which seems to be the only data that they are using.
On a similar basis, do the columns named pg_operator.oprjoin and pg_operator.oprrest have identical names defined by the column pg_proc.proname for which they are also retrieving? If so then those corresponding joins are also unnecessarily.
Many thanks in advance,
regards
Donald Fraser.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 08 July 2003 21:00
To: [ADMIN]
Cc: [pgADMIN]
Subject: [pgadmin-support] Overloading functions that are used by operators.I have a question regarding operators which are causing problems with the new pgAdmin III software being developed and my database.The problem has been discussed in depth with the pgAdmin III development team and they have not been able to resolve the issue and suggested that I have either done something that I shouldn't have of or that there is a possible bug with postgresql.
Where was it discussed in depth? The last I heard was that Andreas was going to look into it.
I will see if I can reproduce the problem myself.
Regards, Dave.
Sorry, I or Andreas can forward you the discussion we had, it didn't get published which I guess it should have (whoops).
(I always get fooled by reply as it doesn't default to the mailing group)
Regards
Donald
----- Original Message -----From: Dave PageTo: Donald Fraser ; [ADMIN]Cc: [pgADMIN]Sent: Tuesday, July 08, 2003 9:12 PMSubject: RE: [pgadmin-support] Overloading functions that are used by operators.-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 08 July 2003 21:00
To: [ADMIN]
Cc: [pgADMIN]
Subject: [pgadmin-support] Overloading functions that are used by operators.I have a question regarding operators which are causing problems with the new pgAdmin III software being developed and my database.The problem has been discussed in depth with the pgAdmin III development team and they have not been able to resolve the issue and suggested that I have either done something that I shouldn't have of or that there is a possible bug with postgresql.Where was it discussed in depth? The last I heard was that Andreas was going to look into it.I will see if I can reproduce the problem myself.Regards, Dave.
Ahh, thanks. Was just looking at that.
Well I always thought you could overload functions in this way, however with your example below, although it creates it OK, PostgreSQL (not pgAdmin) gives the error 'ERROR: There is more than one function named textcat'.
As I said, I'm pretty sure that you can overload functions (in fact I know you can - there's one of mine in PostgreSQL 7.3!). I wonder if in this case the system knows that it can implicitly cast the arguments of one function to the other and hence the error is given?
Would this be the case for your citext datatype?
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 08 July 2003 21:27
To: Dave Page
Subject: Re: [pgadmin-support] Overloading functions that are used by operators.Hi Dave,if you want an easy way to reproduce it do the following:CREATE FUNCTION pg_catalog.textcat(varchar, text) RETURNS text AS 'textcat' LANGUAGE 'internal' IMMUTABLE STRICT;This wouldn't normally be necessary as we have type casting that will handle it and or operators but anyway that's beside the point...Either the above statement is illegal or you have to find a different method for locating the function name associated with an operator.That's the crux of the matter.RegardsDonald Fraser.----- Original Message -----From: Dave PageTo: Donald Fraser ; [ADMIN]Cc: [pgADMIN]Sent: Tuesday, July 08, 2003 9:12 PMSubject: RE: [pgadmin-support] Overloading functions that are used by operators.-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 08 July 2003 21:00
To: [ADMIN]
Cc: [pgADMIN]
Subject: [pgadmin-support] Overloading functions that are used by operators.I have a question regarding operators which are causing problems with the new pgAdmin III software being developed and my database.The problem has been discussed in depth with the pgAdmin III development team and they have not been able to resolve the issue and suggested that I have either done something that I shouldn't have of or that there is a possible bug with postgresql.Where was it discussed in depth? The last I heard was that Andreas was going to look into it.I will see if I can reproduce the problem myself.Regards, Dave.
Donald Fraser wrote:
> I have a question regarding operators which are causing problems with
> the new pgAdmin III software being developed and my database.
> The problem has been discussed in depth with the pgAdmin III
> development team and they have not been able to resolve the issue and
> suggested that I have either done something that I shouldn't have of
> or that there is a possible bug with postgresql.
>
> Background information:
> I have my own data type ('citext') that uses many of the built-in
> system functions based on the fact that the storage is identical to
> the data type 'text'.
> For example I have overloaded the function 'textcat' and the
> definition I have used is:
> CREATE FUNCTION pg_catalog.textcat(citext, citext) RETURNS citext AS
> 'textcat' LANGUAGE 'internal' IMMUTABLE STRICT;
> When you view the functions via the pg_proc table you will see that
> there are two functions with 'textcat' in the column pg_proc.proname.
> I have been using the database for over a year and had no reported
> problems with using the operator || on data types 'text', 'varchar',
> 'bpchar' or calling the overloaded function 'textcat(citext, citext)'.
>
> First question:
> Am I allowed to overload functions, for example the
> 'textcat' function, when they are being used by system operators?
>
> If the answer is no then read no further - the problem is mine and I
> will have to work around it.
>
> The system has at least three operators such as:
> ||(text,text)
> ||(varchar, text)
> ||(bpchar, text)
> All use the function 'textcat'.
>
> Now the new pgAdmin III software interrogates the pg_operator table to
> extract as much information as possible about the operators.
> They are using the following SQL statement to retrieve the information
> that they want.
>
> SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner,
> op.oprkind, op.oprcanhash,
> op.oprleft, op.oprright,
> lt.typname as lefttype,
> rt.typname as righttype,
> et.typname as resulttype,
> co.oprname as compop,
> ne.oprname as negop,
> lso.oprname as leftsortop,
> rso.oprname as rightsortop,
> lco.oprname as lscmpop,
> gco.oprname as gtcmpop,
> po.proname as operproc,
> pj.proname as joinproc,
> pr.proname as restrproc,
> description
> FROM pg_operator op
> JOIN pg_type lt ON lt.oid=op.oprleft
> JOIN pg_type rt ON rt.oid=op.oprright
> JOIN pg_type et on et.oid=op.oprresult
> LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom
> LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate
> LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop
> LEFT OUTER JOIN pg_operator rso ON rso.oid=op.oprrsortop
> LEFT OUTER JOIN pg_operator lco ON lco.oid=op.oprltcmpop
> LEFT OUTER JOIN pg_operator gco ON gco.oid=op.oprgtcmpop
> JOIN pg_proc po ON po.oid=op.oprcode
> LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest
> LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin
> LEFT OUTER JOIN pg_description des ON des.objoid=op.oid
> WHERE op.oprnamespace = 2200::oid
> ORDER BY op.oprname
>
> The offending part of this SQL statement is the join:
> JOIN pg_proc po ON po.oid=op.oprcode
>
> It produces the error message "ERROR: There is more than one procedure
> named textcat".
> Investigation into this has shown that the type conversion of the
> column named pg_operator.oprcode to type oid is done by the function
> call to:
> regprocin('textcat')
> My question would be - what is the correct way to find the function's
> oid that this operator uses? But first read on in case it is not
> necessary...
> It seems to me that the join statements they are using are unnecessary
> so long as the column pg_operator.oprcode has the identical name to
> its matching function that has its name defined by column
> pg_proc.proname, which seems to be the only data that they are using.
>
> On a similar basis, do the columns named pg_operator.oprjoin and
> pg_operator.oprrest have identical names defined by the column
> pg_proc.proname for which they are also retrieving? If so then those
> corresponding joins are also unnecessarily.
>
Donald,
we discussed that this should go to pgsql-bugs, not any admin list.
While there might be some question if joining pg_proc to pg_type is
necessary for pgAdmin3, but it's certainly legal to query which pg_proc
is used for a type.
The underlying question for your case is whether it's allowed to create
overloaded functions of a function that's used for a type.
regprocin will not like this, so CREATE TYPE should deny creation if
input or output function isn't unique by name.
Regards,
Andreas
----- Original Message -----From: Dave PageTo: Donald FraserSent: Tuesday, July 08, 2003 9:49 PMSubject: RE: [pgadmin-support] Overloading functions that are used by operators.Ahh, thanks. Was just looking at that.Well I always thought you could overload functions in this way, however with your example below, although it creates it OK, PostgreSQL (not pgAdmin) gives the error 'ERROR: There is more than one function named textcat'.As I said, I'm pretty sure that you can overload functions (in fact I know you can - there's one of mine in PostgreSQL 7.3!). I wonder if in this case the system knows that it can implicitly cast the arguments of one function to the other and hence the error is given?Would this be the case for your citext datatype?Regards, Dave.
Hi Dave and Andreas,
sorry about all the confusion I've created.
Hopefully this email will clear everything up as there are only two things at issue here - operators and function overloading, leave data types out even though they are partially involved, my custom type has nothing to do with it.
I have removed my data type all together and can still produce the problem by simply overloading any function that is also used by an operator. Hence the 'textcat' example I gave previously.
Here is another example that doesn't use any custom data types. Lets say I wanted to concatenate binary data together and so I define my own function in a 'C' module named 'bincat.so'. The function definition in postgres might be:
CREATE FUNCTION pg_catalog.textcat(bytea, bytea) RETURNS bytea AS 'bincat', 'bincat.so' LANGUAGE 'c' IMMUTABLE STRICT;
Now forget why you would give it a name such as 'textcat', the thing it that it is possible simply because postgresql allows and supports function overloading.
Everything in postgres will still work, even the operators for the data types 'text', 'varchar' and 'bpchar'. ('textcat' is used as operators for ||)
Function overloading is definitely allowed because you can look in the pg_catalog.pg_proc table for yourselves and see many internal functions such as 'abs' which are overloaded many times.
Now back to pgAdmin III: now that I have created this new overloaded function, pgAdmin III will fail when it trys to populate the "Operators" section of the public schema. Why - because I now have two functions named 'textcat', which is perfectly legal but pgAdmin is making an assumption. The assumption is that the name of the function associated to an operator defined by pg_operator.oprcode is unique. Operators don't just use the name of the function to decide which function to call - they also have all of the information about the arguments. That is how an operator knows exactly which function to call. Hence pg_operator.oprcode is not the sole means for deciding which function will be called, which is what pgAdmin III is assuming.
The offending SQL in pgAdmin III are the joins that use the pg_operator.oprcode, pg_operator.oprrest and pg_operator.oprjoin columns.
For example:
JOIN pg_proc po ON po.oid=op.oprcode
postgresql has to convert the op.oprcode to an oid via a function call to "regprocin(op.oprcode)".
The function "regprocin" can only do this if the function name passed to it is unique within the table column pg_catalog.proname, otherwise it doesn't know which function oid it should return.
For example if you do: SELECT regprocin('abs') you get the same sort of error message that we are experiencing: "ERROR: There is more than one procedure named abs"
I had another idea that I thought would explain things much easier for you, but unfortunately it exposes yet another bug.
Try this:
CREATE OPERATOR !@ (PROCEDURE = abs, LEFTARG = int4);
I think you can see where I was going with this example. 'abs' is a function that already exists and is already overloaded many times by the system, no additional data types necessary or any additional function overloading. Unfortunately I don't get the error I was expecting pgAdmin III to display, but that's probably because pgAdmin III is not seeing this operator anywhere - not in 'public' or 'pg_catalog' schemas.
I also noticed that in pg_catalog using pgAdmin II there are 643 operators yet pgAdmin III reports only 596?
I hope that clears things up for you and that I haven't gone down the wrong road yet again.
Regards
Donald Fraser.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 09 July 2003 11:04
To: [pgADMIN]
Subject: Re: [pgadmin-support] Overloading functions that are used by operators.Now back to pgAdmin III: now that I have created this new overloaded function, pgAdmin III will fail when it trys to populate the "Operators" section of the public schema. Why - because I now have two functions named 'textcat', which is perfectly legal but pgAdmin is making an assumption. The assumption is that the name of the function associated to an operator defined by pg_operator.oprcode is unique. Operators don't just use the name of the function to decide which function to call - they also have all of the information about the arguments. That is how an operator knows exactly which function to call. Hence pg_operator.oprcode is not the sole means for deciding which function will be called, which is what pgAdmin III is assuming.
An operator function is selected by it's name, and the left and/or right operands. pgAdmin is doing this because the query uses pg_operator.oprleft and oprright so it knows the types.
I also noticed that in pg_catalog using pgAdmin II there are 643 operators yet pgAdmin III reports only 596?
pgAdmin II is correct - there are 643 operators in pg_catalog in PostgreSQL 7.3.x. The major reason why pgAdmin II gets it right is because unlike pgAdmin III it simply does a select on pg_operator. The rest of the details (function/type names etc) are retrieved from internal caches.
In pgAdmin III's case, the much more complex query obviously has a problem somewhere as it is not returning the correct number of operators.
I think the correct query is:
SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner, op.oprkind, op.oprcanhash,
op.oprleft, op.oprright, lt.typname as lefttype, rt.typname as righttype, et.typname as resulttype,
co.oprname as compop, ne.oprname as negop, lso.oprname as leftsortop, rso.oprname as rightsortop,
lco.oprname as lscmpop, gco.oprname as gtcmpop,
po.proname as operproc, pj.proname as joinproc, pr.proname as restrproc, description
FROM pg_operator op
LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft
LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright
JOIN pg_type et on et.oid=op.oprresult
LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom
LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate
LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop
LEFT OUTER JOIN pg_operator rso ON rso.oid=op.oprrsortop
LEFT OUTER JOIN pg_operator lco ON lco.oid=op.oprltcmpop
LEFT OUTER JOIN pg_operator gco ON gco.oid=op.oprgtcmpop
JOIN pg_proc po ON po.oid=op.oprcode
LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest
LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin
LEFT OUTER JOIN pg_description des ON des.objoid=op.oid
op.oprleft, op.oprright, lt.typname as lefttype, rt.typname as righttype, et.typname as resulttype,
co.oprname as compop, ne.oprname as negop, lso.oprname as leftsortop, rso.oprname as rightsortop,
lco.oprname as lscmpop, gco.oprname as gtcmpop,
po.proname as operproc, pj.proname as joinproc, pr.proname as restrproc, description
FROM pg_operator op
LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft
LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright
JOIN pg_type et on et.oid=op.oprresult
LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom
LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate
LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop
LEFT OUTER JOIN pg_operator rso ON rso.oid=op.oprrsortop
LEFT OUTER JOIN pg_operator lco ON lco.oid=op.oprltcmpop
LEFT OUTER JOIN pg_operator gco ON gco.oid=op.oprgtcmpop
JOIN pg_proc po ON po.oid=op.oprcode
LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest
LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin
LEFT OUTER JOIN pg_description des ON des.objoid=op.oid
Note the addition of LEFT OUTER to the lt and rt table joins. It looks like the unary operators were getting ignored.
Andreas: Please check this and confirm I'm right.
Regards, Dave.
----- Original Message -----From: Dave PageTo: Donald Fraser ; [pgADMIN]Sent: Wednesday, July 09, 2003 10:58 PMSubject: RE: [pgadmin-support] Overloading functions that are used by operators.-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 09 July 2003 11:04
To: [pgADMIN]
Subject: Re: [pgadmin-support] Overloading functions that are used by operators.Now back to pgAdmin III: now that I have created this new overloaded function, pgAdmin III will fail when it trys to populate the "Operators" section of the public schema. Why - because I now have two functions named 'textcat', which is perfectly legal but pgAdmin is making an assumption. The assumption is that the name of the function associated to an operator defined by pg_operator.oprcode is unique. Operators don't just use the name of the function to decide which function to call - they also have all of the information about the arguments. That is how an operator knows exactly which function to call. Hence pg_operator.oprcode is not the sole means for deciding which function will be called, which is what pgAdmin III is assuming.An operator function is selected by it's name, and the left and/or right operands. pgAdmin is doing this because the query uses pg_operator.oprleft and oprright so it knows the types.
I it difficult to understand exactly how the query can use the left and right operands combined with the function name?
Given the explain on the query I notice the following lines:
-> Index Scan using pg_proc_oid_index on pg_proc po (cost=0.00..5.98 rows=1 width=68)
Index Cond: (po.oid = ("outer".oprcode)::oid)
-> Index Scan using pg_proc_oid_index on pg_proc pr (cost=0.00..5.98 rows=1 width=68)
Index Cond: (pr.oid = ("outer".oprrest)::oid)
-> Index Scan using pg_proc_oid_index on pg_proc pj (cost=0.00..5.98 rows=1 width=68)
Index Cond: (pj.oid = ("outer".oprjoin)::oid)
Now how does for example ("outer".oprcode)::oid get converted to an oid using the function name and the left and right operands?
For the best of my knowledge it has to be converted via the function 'regprocin' as there are no arguments in the 'oprcode' column.
I know my SQL isn't the best, and I therefore apologise if I have missunderstood something basic here, but I don't see where the operands get combnied so the the oid can be found via function 'regprocedurein'.
To prove my point I go back to overloading a function. For my example I will use an existing internal function and incompatible data types. Obviously do not call this function with this data type as it will more than likely crash. Its purely for this example.
CREATE FUNCTION pg_catalog.textcat(bytea, bytea) RETURNS bytea AS 'textcat' LANGUAGE 'internal' IMMUTABLE STRICT;
'bytea' is certainly not type castable to type 'text' and therefore we cannot use the argument that that postgresql is getting confused about type conversion.
Now run your query again and we get the error message: 'ERROR: There is more than one function named textcat'.
This seems to backup my argument that the function 'regprocin' is being called rather than 'regprocedurein'.
Unfortunately I can also disprove this with another example, which would suggest that 'regprocedurein' is being called or something else - and this is where I get confussed as to what is going on.
Try creating an operator to do absolute values such as:
CREATE OPERATOR pg_catalog.!@ (PROCEDURE = 'abs', RIGHTARG = int4);
This will popuilate a row in the table pg_operator with column oprcode set to 'pg_catalog.abs'.
Now the function named 'abs' is overloaded many times, for example if you do SELECT regprocin('pg_catalog.abs')
you get the message: 'ERROR: There is more than one function named pg_catalog.abs'.
If you run the query again without the above overloaded function (textcat(bytea, bytea)), the query runs with out any errors suggesting that it was able to convert function 'abs' into an oid?
I'm at the end of my ability with this one and so will leave it in your good hands!
I know this doesn't solve the problem, but the only suggestion that I have, which I have said before, is it really necessary to convert the fully qualified schema-function name into just the function name via the 'JOIN pg_proc po ON po.oid = op.oprcode' statements. Why can't you just use the op.oprcode, op.oprrest and op.oprjoin names directly. In my opinion they contain more information as they include the schema name for any operator that is not a system operator.
I also noticed that in pg_catalog using pgAdmin II there are 643 operators yet pgAdmin III reports only 596?pgAdmin II is correct - there are 643 operators in pg_catalog in PostgreSQL 7.3.x. The major reason why pgAdmin II gets it right is because unlike pgAdmin III it simply does a select on pg_operator. The rest of the details (function/type names etc) are retrieved from internal caches.In pgAdmin III's case, the much more complex query obviously has a problem somewhere as it is not returning the correct number of operators.I think the correct query is:SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner, op.oprkind, op.oprcanhash,
op.oprleft, op.oprright, lt.typname as lefttype, rt.typname as righttype, et.typname as resulttype,
co.oprname as compop, ne.oprname as negop, lso.oprname as leftsortop, rso.oprname as rightsortop,
lco.oprname as lscmpop, gco.oprname as gtcmpop,
po.proname as operproc, pj.proname as joinproc, pr.proname as restrproc, description
FROM pg_operator op
LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft
LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright
JOIN pg_type et on et.oid=op.oprresult
LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom
LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate
LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop
LEFT OUTER JOIN pg_operator rso ON rso.oid=op.oprrsortop
LEFT OUTER JOIN pg_operator lco ON lco.oid=op.oprltcmpop
LEFT OUTER JOIN pg_operator gco ON gco.oid=op.oprgtcmpop
JOIN pg_proc po ON po.oid=op.oprcode
LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest
LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin
LEFT OUTER JOIN pg_description des ON des.objoid=op.oidNote the addition of LEFT OUTER to the lt and rt table joins. It looks like the unary operators were getting ignored.
A quick test and this version of the query seems to return the correct number of rows.
Regards
Donald Fraser.
Dave Page wrote: > > > -----Original Message----- > *From:* Donald Fraser [mailto:demolish@cwgsy.net] > *Sent:* 09 July 2003 11:04 > *To:* [pgADMIN] > *Subject:* Re: [pgadmin-support] Overloading functions that are > used by operators. > > Now back to pgAdmin III: now that I have created this new > overloaded function, pgAdmin III will fail when it trys to > populate the "Operators" section of the public schema. Why - > because I now have two functions named 'textcat', which is > perfectly legal but pgAdmin is making an assumption. The > assumption is that the name of the function associated to an > operator defined by pg_operator.oprcode is unique. Operators don't > just use the name of the function to decide which function to call > - they also have all of the information about the arguments. That > is how an operator knows exactly which function to call. Hence > pg_operator.oprcode is not the sole means for deciding which > function will be called, which is what pgAdmin III is assuming. > > > An operator function is selected by it's name, and the left and/or > right operands. pgAdmin is doing this because the query uses > pg_operator.oprleft and oprright so it knows the types. > > I also noticed that in pg_catalog using pgAdmin II there are 643 > operators yet pgAdmin III reports only 596? > > > pgAdmin II is correct - there are 643 operators in pg_catalog in > PostgreSQL 7.3.x. The major reason why pgAdmin II gets it right is > because unlike pgAdmin III it simply does a select on pg_operator. The > rest of the details (function/type names etc) are retrieved from > internal caches. > > In pgAdmin III's case, the much more complex query obviously has a > problem somewhere as it is not returning the correct number of operators. > > I think the correct query is: > > SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner, > op.oprkind, op.oprcanhash, > op.oprleft, op.oprright, lt.typname as lefttype, > rt.typname as righttype, et.typname as resulttype, > co.oprname as compop, ne.oprname as negop, lso.oprname > as leftsortop, rso.oprname as rightsortop, > lco.oprname as lscmpop, gco.oprname as gtcmpop, > po.proname as operproc, pj.proname as joinproc, > pr.proname as restrproc, description > FROM pg_operator op > LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft > LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright > JOIN pg_type et on et.oid=op.oprresult > LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom > LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate > LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop > LEFT OUTER JOIN pg_operator rso ON rso.oid=op.oprrsortop > LEFT OUTER JOIN pg_operator lco ON lco.oid=op.oprltcmpop > LEFT OUTER JOIN pg_operator gco ON gco.oid=op.oprgtcmpop > JOIN pg_proc po ON po.oid=op.oprcode > LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest > LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin > LEFT OUTER JOIN pg_description des ON des.objoid=op.oid > > Note the addition of LEFT OUTER to the lt and rt table joins. It looks > like the unary operators were getting ignored. > > Andreas: Please check this and confirm I'm right. Ah no, I just thought that binary operators are only half operators, and we only want to display complete ones, right? ;-) Regards, Andreas
----- Original Message ----- From: "Andreas Pflug" <pgadmin@pse-consulting.de> To: "Dave Page" <dpage@vale-housing.co.uk> Cc: "Donald Fraser" <demolish@cwgsy.net>; "[pgADMIN]" <pgadmin-support@postgresql.org> Sent: Thursday, July 10, 2003 11:03 AM Subject: Re: [pgadmin-support] Overloading functions that are used by operators. > Dave Page wrote: > > > > > > > -----Original Message----- > > *From:* Donald Fraser [mailto:demolish@cwgsy.net] > > *Sent:* 09 July 2003 11:04 > > *To:* [pgADMIN] > > *Subject:* Re: [pgadmin-support] Overloading functions that are > > used by operators. > > > > Now back to pgAdmin III: now that I have created this new > > overloaded function, pgAdmin III will fail when it trys to > > populate the "Operators" section of the public schema. Why - > > because I now have two functions named 'textcat', which is > > perfectly legal but pgAdmin is making an assumption. The > > assumption is that the name of the function associated to an > > operator defined by pg_operator.oprcode is unique. Operators don't > > just use the name of the function to decide which function to call > > - they also have all of the information about the arguments. That > > is how an operator knows exactly which function to call. Hence > > pg_operator.oprcode is not the sole means for deciding which > > function will be called, which is what pgAdmin III is assuming. > > > > > > An operator function is selected by it's name, and the left and/or > > right operands. pgAdmin is doing this because the query uses > > pg_operator.oprleft and oprright so it knows the types. > > > > I also noticed that in pg_catalog using pgAdmin II there are 643 > > operators yet pgAdmin III reports only 596? > > > > > > pgAdmin II is correct - there are 643 operators in pg_catalog in > > PostgreSQL 7.3.x. The major reason why pgAdmin II gets it right is > > because unlike pgAdmin III it simply does a select on pg_operator. The > > rest of the details (function/type names etc) are retrieved from > > internal caches. > > > > In pgAdmin III's case, the much more complex query obviously has a > > problem somewhere as it is not returning the correct number of operators. > > > > I think the correct query is: > > > > SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner, > > op.oprkind, op.oprcanhash, > > op.oprleft, op.oprright, lt.typname as lefttype, > > rt.typname as righttype, et.typname as resulttype, > > co.oprname as compop, ne.oprname as negop, lso.oprname > > as leftsortop, rso.oprname as rightsortop, > > lco.oprname as lscmpop, gco.oprname as gtcmpop, > > po.proname as operproc, pj.proname as joinproc, > > pr.proname as restrproc, description > > FROM pg_operator op > > LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft > > LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright > > JOIN pg_type et on et.oid=op.oprresult > > LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom > > LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate > > LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop > > LEFT OUTER JOIN pg_operator rso ON rso.oid=op.oprrsortop > > LEFT OUTER JOIN pg_operator lco ON lco.oid=op.oprltcmpop > > LEFT OUTER JOIN pg_operator gco ON gco.oid=op.oprgtcmpop > > JOIN pg_proc po ON po.oid=op.oprcode > > LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest > > LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin > > LEFT OUTER JOIN pg_description des ON des.objoid=op.oid > > > > Note the addition of LEFT OUTER to the lt and rt table joins. It looks > > like the unary operators were getting ignored. > > > > Andreas: Please check this and confirm I'm right. > > Ah no, I just thought that binary operators are only half operators, and > we only want to display complete ones, right? ;-) > Well if you try this: CREATE OPERATOR pg_catalog.!@ (PROCEDURE = 'abs', RIGHTARG = int4); which is a legal operator and one that you would expect to see, it doesn't show up with your version of the query but does show up with the above. Regards Donald Fraser.
> -----Original Message----- > From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] > Sent: 10 July 2003 11:03 > To: Dave Page > Cc: Donald Fraser; [pgADMIN] > Subject: Re: [pgadmin-support] Overloading functions that are > used by operators. > > > Note the addition of LEFT OUTER to the lt and rt table > joins. It looks > > like the unary operators were getting ignored. > > > > Andreas: Please check this and confirm I'm right. > > Ah no, I just thought that binary operators are only half > operators, and > we only want to display complete ones, right? ;-) Sounds good to me - I rarely use them ;-) Patch applied last night btw... Regards, Dave.
Donald Fraser wrote: >>>ndreas: Please check this and confirm I'm right. >>> >>> >>Ah no, I just thought that binary operators are only half operators, and >>we only want to display complete ones, right? ;-) >> >> >> > >Well if you try this: >CREATE OPERATOR pg_catalog.!@ (PROCEDURE = 'abs', RIGHTARG = int4); >which is a legal operator and one that you would expect to see, it doesn't show >up with your version of the query but does show up with the above. > > > Hey Donald, I was joking!!! Dave fixed this already. Regards, Andreas
Dave Page wrote:
>
>
> -----Original Message-----
> *From:* Donald Fraser [mailto:demolish@cwgsy.net]
> *Sent:* 10 July 2003 11:37
> *To:* Dave Page
> *Subject:* Fw: [pgadmin-support] Overloading functions that are
> used by operators.
>
> I it difficult to understand exactly how the query can use the
> left and right operands combined with the function name?
> Given the explain on the query I notice the following lines:
>
> -> Index Scan using pg_proc_oid_index on pg_proc po
> (cost=0.00..5.98 rows=1 width=68)
> Index Cond: (po.oid = ("outer".oprcode)::oid)
> -> Index Scan using pg_proc_oid_index on pg_proc pr
> (cost=0.00..5.98 rows=1 width=68)
> Index Cond: (pr.oid = ("outer".oprrest)::oid)
> -> Index Scan using pg_proc_oid_index on pg_proc pj
> (cost=0.00..5.98 rows=1 width=68)
> Index Cond: (pj.oid = ("outer".oprjoin)::oid)
>
> Now how does for example ("outer".oprcode)::oid get converted to
> an oid using the function name and the left and right operands?
> For the best of my knowledge it has to be converted via the
> function 'regprocin' as there are no arguments in the 'oprcode'
> column.
> I know my SQL isn't the best, and I therefore apologise if I have
> missunderstood something basic here, but I don't see where the
> operands get combnied so the the oid can be found via function
> 'regprocedurein'.
>
>
> You're right, it doesn't - but then pgAdmin doesn't care anyway. All
> it's trying to do is display the function name and the operand types.
> oprcode is adequate to get the correct function name (though not the
> exact signature), and the left and/or right operands come from
> oprleft/oprright.
>
> Anyway, the following patch appears to get rid of the error, and leave
> things looking correct on my system:
>
>
> Index: pgOperator.cpp
> ===================================================================
> RCS file: /disk1/cvsroot/pgadmin3/src/schema/pgOperator.cpp,v
> retrieving revision 1.16
> diff -r1.16 pgOperator.cpp
> 148c148
> < wxT(" po.proname as operproc, pj.proname as joinproc, pr.proname as
> restrproc, description\n")
> ---
>> wxT(" op.oprcode as operproc, op.oprjoin as joinproc, op.oprrest as
> restrproc, description\n")
> 159,161d158
> < wxT(" JOIN pg_proc po ON po.oid=op.oprcode\n")
> < wxT(" LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest\n")
> < wxT(" LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin\n")
>
> Look OK to you Andreas?
>
It "kind-of" works like this, but the question why the join won't work
for Donald is still not answered.
I created the same stuff as Donald, and pgadmin works ok.
On my 7.4, po.oid=op.oprcode is evaluated as ::oid=::oid. Maybe on 7.3
this is done po.oid::regproc::text=op.oprcode::regproc::text, which
could lead to that problem.
Regards,
Andreas
> -----Original Message----- > From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] > Sent: 10 July 2003 12:52 > To: Dave Page > Cc: Donald Fraser; pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Overloading functions that are > used by operators. > > It "kind-of" works like this, but the question why the join > won't work > for Donald is still not answered. > I created the same stuff as Donald, and pgadmin works ok. > On my 7.4, po.oid=op.oprcode is evaluated as ::oid=::oid. > Maybe on 7.3 > this is done po.oid::regproc::text=op.oprcode::regproc::text, which > could lead to that problem. If I create the textcat(bytea, bytea) function Donald suggests, then the original query errors. The new one does not. I don't know why, but clearly it is a PostgreSQL issue, not a pgAdmin one as your original query is clearly valid. Regards, Dave
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 10 July 2003 11:37
To: Dave Page
Subject: Fw: [pgadmin-support] Overloading functions that are used by operators.I it difficult to understand exactly how the query can use the left and right operands combined with the function name?Given the explain on the query I notice the following lines:-> Index Scan using pg_proc_oid_index on pg_proc po (cost=0.00..5.98 rows=1 width=68)Index Cond: (po.oid = ("outer".oprcode)::oid)-> Index Scan using pg_proc_oid_index on pg_proc pr (cost=0.00..5.98 rows=1 width=68)Index Cond: (pr.oid = ("outer".oprrest)::oid)-> Index Scan using pg_proc_oid_index on pg_proc pj (cost=0.00..5.98 rows=1 width=68)Index Cond: (pj.oid = ("outer".oprjoin)::oid)Now how does for example ("outer".oprcode)::oid get converted to an oid using the function name and the left and right operands?For the best of my knowledge it has to be converted via the function 'regprocin' as there are no arguments in the 'oprcode' column.I know my SQL isn't the best, and I therefore apologise if I have missunderstood something basic here, but I don't see where the operands get combnied so the the oid can be found via function 'regprocedurein'.
You're right, it doesn't - but then pgAdmin doesn't care anyway. All it's trying to do is display the function name and the operand types. oprcode is adequate to get the correct function name (though not the exact signature), and the left and/or right operands come from oprleft/oprright.
Anyway, the following patch appears to get rid of the error, and leave things looking correct on my system:
Index: pgOperator.cpp
===================================================================
RCS file: /disk1/cvsroot/pgadmin3/src/schema/pgOperator.cpp,v
retrieving revision 1.16
diff -r1.16 pgOperator.cpp
148c148
< wxT(" po.proname as operproc, pj.proname as joinproc, pr.proname as restrproc, description\n")
---
> wxT(" op.oprcode as operproc, op.oprjoin as joinproc, op.oprrest as restrproc, description\n")
159,161d158
< wxT(" JOIN pg_proc po ON po.oid=op.oprcode\n")
< wxT(" LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest\n")
< wxT(" LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin\n")
Look OK to you Andreas?
Regards, Dave.
"Dave Page" <dpage@vale-housing.co.uk> writes:
> Well I always thought you could overload functions in this way, however
> with your example below, although it creates it OK, PostgreSQL (not
> pgAdmin) gives the error 'ERROR: There is more than one function named
> textcat'.
I see no such error here ...
regression=# create function pg_catalog.textcat(varchar,text)
regression-# returns text as 'textcat' language 'internal';
CREATE FUNCTION
regards, tom lane
Just to say that I tested the July 10th snap-shot and all works with Operators etc..
Many thanks to Dave and Andreas for your patience.
Regards
Donald Fraser.
You're welcome - glad it works :-)
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 14 July 2003 16:32
To: [pgADMIN]
Subject: Re: [pgadmin-support] Overloading functions that are used by operators.Just to say that I tested the July 10th snap-shot and all works with Operators etc..Many thanks to Dave and Andreas for your patience.RegardsDonald Fraser.