Thread: Timespan_div misbehaving?
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 usingan 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
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
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_subeq828|>> | 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
> 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, Pennsylvania19026
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
> 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, Pennsylvania19026