Thread: Error while restoring database
Hi, Version 7.1.3, Linux 2.2.18 Following procedure: 1. pg_dump dbname > outfile Everything is fine. 2. Recreating the database on another system (same Versions) psql dbname < infile I get once: ERROR: parser: parse error at or near "," The rest works fine. Debug -d2 shows that recreating an operator fails. There was never a problem creating this operator before and it worked fine. It just fails during restore. It seem the function numeric_neq, which is created later (after the second operator) is missing. So pg_dump doesn't seem to dump the functions before the operators. <snip> DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: CREATE FUNCTION "numeric_eq" (numeric,double precision) RETURNS b DEBUG: ProcessUtility: CREATE FUNCTION "numeric_eq" (numeric,double precision) DEBUG: query: select $1 = $2::numeric; DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: CREATE OPERATOR <> (PROCEDURE = numeric_neq , LEFTARG = numeric , RIGHTARG = double precision, COMMUTATOR = <> , NEGATOR = , RESTRICT = eqsel , JOIN = eqjoinsel ); ERROR: parser: parse error at or near "," DEBUG: AbortCurrentTransaction DEBUG: StartTransactionCommand DEBUG: query: CREATE OPERATOR = (PROCEDURE = numeric_eq , <snip> It's not real problem for me. I think it happened while playing with pgadmin, changing a function call in an operator. But still, shouldn't pg_dump look after it? Any ideas how to fix this? regards Johann Zuschlag zuschlag@online.de
"Johann Zuschlag" <zuschlag@online.de> writes:
> DEBUG: query: CREATE OPERATOR <> (PROCEDURE = numeric_neq ,
> LEFTARG = numeric ,
> RIGHTARG = double precision ,
> COMMUTATOR = <> ,
> NEGATOR = ,
> RESTRICT = eqsel ,
> JOIN = eqjoinsel );
> ERROR: parser: parse error at or near ","
Hmm, so what happened to the NEGATOR link?
You have not given us enough information to understand what, if
anything, needs to be fixed ... do you still have the original
database to look at?
regards, tom lane
"Johann Zuschlag" <zuschlag@online.de> writes:
> Here is an excerpt of the original dump-file.
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.
regards, tom lane
"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
"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
"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
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
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
euro=> select * from pg_operator where oid = 280346;
oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate
|oprlsortop | oprrsortop | oprcode | oprrest | oprjoin
---------+----------+---------+---------+-----------+------------+---------+----------+-----------+--------+-----------+------------+------------+---------+---------+---------
<> | 27 | 0 | b | f | f | 701 | 1700 | 0 | 0 | 0
| 0 | 0 | - | - | -
(1 row)
euro=> select * from pg_operator where oid = 280347;
oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate
|oprlsortop | oprrsortop | oprcode | oprrest | oprjoin
---------+----------+---------+---------+-----------+------------+---------+----------+-----------+--------+-----------+------------+------------+---------+---------+---------
| 27 | 0 | b | f | f | 1700 | 701 | 0 | 0 | 0
| 0 | 0 | - | - | -
(1 row)
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
euro=> select * from pg_operator where oid = 280343;oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash |
oprleft| oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin
---------+----------+---------+---------+-----------+------------+---------+----------+-----------+--------+-----------+------------+------------+-------------+---------+-----------<>
| 27 | 0 | b | t | f | 1700 | 701 | 16 | 280346 | 280347 |
0 | 0 | numeric_neq | eqsel | eqjoinsel
(1 row)
euro=> select * from pg_operator where oid = 280344;oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash |
oprleft| oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin
---------+----------+---------+---------+-----------+------------+---------+----------+-----------+--------+-----------+------------+------------+------------+---------+-----------=
| 27 | 0 | b | t | f | 1700 | 701 | 16 | 280342 | 280343 |
0 | 0 | numeric_eq | eqsel | eqjoinsel
(1 row)