On Sat, 20 Oct 2001 12:36:16 -0400, Tom Lane wrote:

>That's what you showed us already.  What I'd like to see is the
>original database contents, particularly
>
>	select * from pg_operator where oid = 280343;
>	select * from pg_operator where oid = 280344;
>
>so we can see why pg_dump is producing the bogus output.

I'm sorry. I'm not so deep in the internals of postgreSQL. I'm just
doing some psqlodbc-supports and tests.

Attached you find the results of the above selects.

regards

Johann Zuschlag
zuschlag@online.de
Sun, 21 Oct 2001 14:45:03 +0200
"Johann Zuschlag" <zuschlag@online.de>
Re: Error while restoring database
"Johann Zuschlag" <zuschlag@online.de> writes:
>> select * from pg_operator where oid = 280343;
>> select * from pg_operator where oid = 280344;
> Attached you find the results of the above selects.

Okay ... are there any rows in pg_operator with OID 280346 or 280347 ?

			regards, tom lane

Sun, 21 Oct 2001 12:42:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Error while restoring database 
On Sun, 21 Oct 2001 12:42:57 -0400, Tom Lane wrote:

>>> select * from pg_operator where oid = 280343;
>>> select * from pg_operator where oid = 280344;
>> Attached you find the results of the above selects.
>
>Okay ... are there any rows in pg_operator with OID 280346 or 280347 ?

Yes, seems so. See the attachment. Again, the negator stuff
never worked for numeric.

regards


Johann Zuschlag
zuschlag@online.de
Sun, 21 Oct 2001 19:54:03 +0200
"Johann Zuschlag" <zuschlag@online.de>
Re: Error while restoring database
"Johann Zuschlag" <zuschlag@online.de> writes:
>> Okay ... are there any rows in pg_operator with OID 280346 or 280347 ?

> Yes, seems so. See the attachment. Again, the negator stuff
> never worked for numeric.

Looks like these are "shell" operator definitions left over from
commutator or negator forward references that were never satisfied.
pg_dump did the right thing to not dump them.  I'd say that the backend
should never have accepted a shell operator def with an empty name,
though, which is what you seem to have at OID 280347.

Do you happen to have the exact command that you gave to create
operator 280343 (numeric_neq)?  I think what this really boils down
to is insufficient error checking somewhere in CREATE OPERATOR.

			regards, tom lane

Sun, 21 Oct 2001 14:14:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Error while restoring database 
On Sun, 21 Oct 2001 14:14:05 -0400, Tom Lane wrote:

>Looks like these are "shell" operator definitions left over from
>commutator or negator forward references that were never satisfied.
>pg_dump did the right thing to not dump them.  I'd say that the backend
>should never have accepted a shell operator def with an empty name,
>though, which is what you seem to have at OID 280347.
>
>Do you happen to have the exact command that you gave to create
>operator 280343 (numeric_neq)?  I think what this really boils down
>to is insufficient error checking somewhere in CREATE OPERATOR.

Fortunately I still have the scripts. I used pgAdminII. But I think some
time earlier I used psql for the same operator. So shouldn't make any
difference.

I used the two scripts below. I think in that particular order. 
(Still wondering, why there is a negator '<>' in the first one :-)

I just tested them again. No error message.
And I've got one with an empty name!
Always wondered why.

(1)
create function numeric_eq(numeric,float8) returns bool as '
    select $1 = $2::numeric;
' language 'sql';

create operator = (
 leftarg=numeric,
 rightarg=float8,
 procedure=numeric_eq,
 commutator='=',
 negator='<>',
 restrict=eqsel,
 join=eqjoinsel
 );

(2)
create function numeric_neq(numeric,float8) returns bool as '
    select $1 = $2::numeric;
' language 'sql';

create operator <> (
 leftarg=numeric,
 rightarg=float8,
 procedure=numeric_neq,
 commutator='<>',
 negator='',
 restrict=eqsel,
 join=eqjoinsel
 );

regards


Johann Zuschlag
zuschlag@online.de



Sun, 21 Oct 2001 22:03:52 +0200
"Johann Zuschlag" <zuschlag@online.de>
Re: Error while restoring database
"Johann Zuschlag" <zuschlag@online.de> writes:
> create function numeric_neq(numeric,float8) returns bool as '
>     select $1 = $2::numeric;
> ' language 'sql';

> create operator <> (
>  leftarg=numeric,
>  rightarg=float8,
>  procedure=numeric_neq,
>  commutator='<>',
>  negator='',
   ^^^^^^^^^^
>  restrict=eqsel,
>  join=eqjoinsel
>  );

Well, there's your problem...

For 7.2, I have added some error checking to the system that will
prevent it accepting invalid operator names in commutator/negator
parameters.

			regards, tom lane

Sun, 21 Oct 2001 16:18:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Error while restoring database 