Hi,
When I try on current:
abr=> select sum(endt-begt-pausen)/count(*)::float8 from tage;
ERROR:  Unable to identify an operator '/' for types 'timespan' and 'float8'
        You will have to retype this query using an explicit cast 
but:
abr=> \df
...
timespan |timespan_div       |timespan float8            |divide
...

Why? Directly used I get:
abr=> select timespan_div(sum(endt-begt-pausen),count(*)::float8) from tage;
timespan_div
-----------------------------
@ 11 hours 24 mins 34.79 secs
(1 row)      

It seems that an entry for '/' is defined:
grep timespan_div catalog/*.bki*
...
catalog/local1_template1.bki.source:insert OID = 1585 ( "/" PGUID 0 b t f 1186
1186 1186 0 0 0 0 timespan_div - - ) 
...

OR are the three 1186's wrong? 
Shouldn't at least the second input type be 701?

Any clues?

Bye!
----
Michael Reifenberger
Plaut Software GmbH, R/3 Basis



Mon, 8 Mar 1999 09:33:05 +0100 (CET)
Michael Reifenberger <root@nihil.plaut.de>
Timespan_div misbehaving?
Michael Reifenberger <root@nihil.plaut.de> writes:
> catalog/local1_template1.bki.source:insert OID = 1585 ( "/" PGUID 0 b t f 1186
> 1186 1186 0 0 0 0 timespan_div - - ) 

> OR are the three 1186's wrong? 
> Shouldn't at least the second input type be 701?

I think you are right --- the only timespan_div I can find in the source
code is TimeSpan *timespan_div(TimeSpan *span1, float8 *arg2) in adt/dt.c.
It looks like the entry for it in pg_proc is right, but the one in
pg_operator is wrong.

Hmm, a whole new class of cross-checks that opr_sanity ought to make ;-)

			regards, tom lane

Mon, 08 Mar 1999 10:21:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Timespan_div misbehaving? 
I said:
> It looks like the entry for it in pg_proc is right, but the one in
> pg_operator is wrong.
>
> Hmm, a whole new class of cross-checks that opr_sanity ought to make ;-)

Indeed, applying a mechanical cross-check was fruitful ... I found four
dozen pg_operator entries that disagree with the corresponding pg_proc
entries:

QUERY: SELECT p1.oid, p1.oprname, p2.oid, p2.proname
FROM pg_operator AS p1, pg_proc AS p2
WHERE p1.oprcode = p2.oid AND
    p1.oprkind = 'b' AND
    (p2.pronargs != 2 OR
     p1.oprresult != p2.prorettype OR
     (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR
     (p1.oprright != p2.proargtypes[1] AND p2.proargtypes[1] != 0));
 oid|oprname| oid|proname      
----+-------+----+-------------
  15|=      | 852|int48eq      
  36|<>     | 853|int48ne      
  37|<      | 854|int48lt      
  76|>      | 855|int48gt      
  80|<=     | 856|int48le      
  82|>=     | 857|int48ge      
 532|=      | 158|int24eq      
 533|=      | 159|int42eq      
 534|<      | 160|int24lt      
 535|<      | 161|int42lt      
 536|>      | 162|int24gt      
 537|>      | 163|int42gt      
 538|<>     | 164|int24ne      
 539|<>     | 165|int42ne      
 540|<=     | 166|int24le      
 541|<=     | 167|int42le      
 542|>=     | 168|int24ge      
 543|>=     | 169|int42ge      
 609|<      |  66|int4lt       
 610|>      | 147|int4gt       
 611|<=     | 149|int4le       
 612|>=     | 150|int4ge       
 626|!!=    |1285|int4notin    
 974|||     |1258|textcat      
 979|||     |1258|textcat      
1055|~      |1254|textregexeq  
1056|!~     |1256|textregexne  
1063|~      |1254|textregexeq  
1064|!~     |1256|textregexne  
1211|~~     | 850|textlike     
1212|!~~    | 851|textnlike    
1213|~~     | 850|textlike     
1214|!~~    | 851|textnlike    
1232|~*     |1238|texticregexeq
1233|!~*    |1239|texticregexne
1234|~*     |1238|texticregexeq
1235|!~*    |1239|texticregexne
1522|<->    |1476|dist_pc      
1585|/      |1326|timespan_div 
 820|=      | 920|network_eq   
 821|<>     | 925|network_ne   
 822|<      | 921|network_lt   
 823|<=     | 922|network_le   
 824|>      | 923|network_gt   
 825|>=     | 924|network_ge   
 826|<<     | 927|network_sub  
 827|<<=    | 928|network_subeq
 828|>>     | 929|network_sup  
1004|>>=    | 930|network_supeq
(49 rows)

Some of these are quasi-legitimate cases (like both "text" and "varchar"
entries for one operator --- is that really necessary?).  Quite a few
seem to be real bugs.  Working on fixing them now.

If I can figure out how to deal with the binary-equivalent cases
automatically, will commit an extension of opr_sanity regress test to
detect such mistakes in future.

			regards, tom lane

Mon, 08 Mar 1999 20:55:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Timespan_div misbehaving? 
> I said:
> > It looks like the entry for it in pg_proc is right, but the one in
> > pg_operator is wrong.
> >
> > Hmm, a whole new class of cross-checks that opr_sanity ought to make ;-)
> 
> Indeed, applying a mechanical cross-check was fruitful ... I found four
> dozen pg_operator entries that disagree with the corresponding pg_proc
> entries:

Tom, would you please take the pg/include/catalog/*.sql files and merge
them into the regression sanity checks?  They should be in there too. 
You can remove them once you have moved them over.



-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 8 Mar 1999 21:18:00 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Timespan_div misbehaving?
Bruce Momjian <maillist@candle.pha.pa.us> wrote a couple weeks ago:
> Tom, would you please take the pg/include/catalog/*.sql files and merge
> them into the regression sanity checks?  They should be in there too. 
> You can remove them once you have moved them over.

I made some progress on this last night, but still need to work on the
other test file from include/catalog/.

There is a new regress test "oidjoins" that checks consistency of all
OID and REGPROC columns in the standard tables.  Basically it looks
for dangling links --- values that do not match any OID in the target
table that the column is supposed to link to.  The test script can be
rebuilt, if the set of columns to check changes, by running the code
in contrib/findoidjoins.  (I thought about just running findoidjoins
as part of the regress test, but it seems unreasonably slow for that.
Also, you really want to eyeball findoidjoins' output before using it,
since the proggie will be misled by OIDs that join to the wrong table.)

I found a dozen or so bogus tuples in several system tables (mostly
pg_amop and pg_aggregate IIRC).  They had OID entries that referred
to OIDs that actually exist ... but are in the wrong table.  For
example some of the amopclaid fields had values that were OIDs of
procs or types instead of opclass rows.  I believe these are leftovers
from some ancient time when those OIDs were being used for something
else.  (Most of the current assignments of those OIDs have to do with
int8, so I know they are relatively new.  Evidently no one ran an
oidjoins check while the OIDs were unused...)

All of the tuples in question were either unused or else referenced
only by other bogus tuples, so I just deleted 'em.  (I'm new to
adding/removing entries in system tables; is there anything else that
needs to be done besides editing include/catalog/pg_*.h?)

Note the oidjoins test will fail until you do an initdb, because of
the aforementioned bogus tuples.

			regards, tom lane

Fri, 26 Mar 1999 10:23:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
New regress test (was Re: Timespan_div misbehaving?)
> I found a dozen or so bogus tuples in several system tables (mostly
> pg_amop and pg_aggregate IIRC).  They had OID entries that referred
> to OIDs that actually exist ... but are in the wrong table.  For
> example some of the amopclaid fields had values that were OIDs of
> procs or types instead of opclass rows.  I believe these are leftovers
> from some ancient time when those OIDs were being used for something
> else.  (Most of the current assignments of those OIDs have to do with
> int8, so I know they are relatively new.  Evidently no one ran an
> oidjoins check while the OIDs were unused...)
> 
> All of the tuples in question were either unused or else referenced
> only by other bogus tuples, so I just deleted 'em.  (I'm new to
> adding/removing entries in system tables; is there anything else that
> needs to be done besides editing include/catalog/pg_*.h?)

Great.  Good job.  No, I just edit the *.h files, and run initdb.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 26 Mar 1999 11:25:29 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] New regress test (was Re: Timespan_div misbehaving?)