FW: Overloading functions that are used by operators.

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

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

To: [pgADMIN]

Sent: Thursday, July 10, 2003 10:31 AM

Subject: Re: [pgadmin-support] Overloading functions that are used by operators.

 

----- Original Message -----

From: Dave Page

To: Donald Fraser ; [pgADMIN]

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