FW: Overloading functions that are used by operators.

-----Original Message-----
From: Donald Fraser [mailto:demolish(at)cwgsy(dot)net]
Sent: 10 July 2003 11:37
To: Dave Page
Subject: Fw: [pgadmin-support] Overloading functions that are
used by operators.


Hi Dave,
I sent this email off this morning but it hasn't made it through
so wandering if you can post it to the group.
(I got a whole load of repeated message this morning too so may
be there's something going on...)

Cheers
Donald

----- Original Message -----
From: Donald Fraser <mailto:demolish(at)cwgsy(dot)net>
To: [pgADMIN] <mailto:pgadmin-support(at)postgresql(dot)org>
Sent: Thursday, July 10, 2003 10:31 AM
Subject: Re: [pgadmin-support] Overloading functions that are
used by operators.

----- Original Message -----
From: Dave Page <mailto:dpage(at)vale-housing(dot)co(dot)uk>
To: Donald Fraser <mailto:demolish(at)cwgsy(dot)net> ;
[pgADMIN] <mailto:pgadmin-support(at)postgresql(dot)org>
Sent: Wednesday, July 09, 2003 10:58 PM
Subject: RE: [pgadmin-support] Overloading functions
that are used by operators.

-----Original Message-----
From: Donald Fraser [mailto:demolish(at)cwgsy(dot)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.oid

Note 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.