Re: [HACKERS] odd pg_dump output? - Mailing list pgsql-hackers

> >> CREATE TYPE widget ( internallength = 24, externallength = -1, input = 144673(widget_in), output =
144674(widget_out),send = 144674(widget_out), receive = 144673(widget_in), default = '-'); 
> >> ERROR:  parser: parse error at or near "("
> >> CREATE TYPE _widget ( internallength = -1, externallength = -1, input = 750(array_in), output = 751(array_out),
send= 751(array_out), receive = 750(array_in), default = '-'); 
> >> ERROR:  parser: parse error at or near "("
> >
> >OK, this is my mistake.  The regproc fields output the pg_proc.proname,
> >but that is not unique, and should not be used to load into those
> >fields.  I prepended the object id of the pg_proc entry, to try and make
> >it more reliable, but forgot the parentheses would mess up the scannar.
> >
> >I have changed 233(proname) to proname_233, which will now work.
>
> Thanks. but... Seems still we have problems with pg_dump.
> I did cvs up, initdb, run regression then pg_dump. reloading db shows
> following complains:
>
> CREATE TYPE widget ( internallength = 24, externallength = -1, input = widget_in_19233, output = widget_out_19234,
send= widget_out_19234, receive = widget_in_19233, default = '-'); 
> QUERY: CREATE TYPE widget ( internallength = 24, externallength = -1, input = widget_in_19233, output =
widget_out_19234,send = widget_out_19234, receive = widget_in_19233, default = '-'); 
> ERROR:  TypeCreate: function 'widget_in_19233(opaque)' does not exist
> CREATE TYPE _widget ( internallength = -1, externallength = -1, input = array_in_750, output = array_out_751, send =
array_out_751,receive = array_in_750, default = '-'); 
> QUERY: CREATE TYPE _widget ( internallength = -1, externallength = -1, input = array_in_750, output = array_out_751,
send= array_out_751, receive = array_in_750, default = '-'); 
> ERROR:  TypeCreate: function 'array_in_750(opaque)' does not exist
> CREATE TYPE city_budget ( internallength = 16, externallength = -1, input = int44in_1287, output = int44out_653, send
=int44out_653, receive = int44in_1287, default = '-', element = int4, delimiter = ','); 
> QUERY: CREATE TYPE city_budget ( internallength = 16, externallength = -1, input = int44in_1287, output =
int44out_653,send = int44out_653, receive = int44in_1287, default = '-', element = int4, delimiter = ','); 
> ERROR:  TypeCreate: function 'int44in_1287(opaque)' does not exist
> CREATE TYPE _city_budget ( internallength = -1, externallength = -1, input = array_in_750, output = array_out_751,
send= array_out_751, receive = array_in_750, default = '-'); 
> QUERY: CREATE TYPE _city_budget ( internallength = -1, externallength = -1, input = array_in_750, output =
array_out_751,send = array_out_751, receive = array_in_750, default = '-'); 
> ERROR:  TypeCreate: function 'array_in_750(opaque)' does not exist
>
> [snip]
>
> CREATE TABLE person (name text, age int4, location point);
> QUERY: CREATE TABLE person (name text, age int4, location point);
> CREATE TABLE emp (salary int4, manager name) inherits ( person);
> QUERY: CREATE TABLE emp (salary int4, manager name) inherits ( person);
> CREATE TABLE student (gpa float8) inherits ( person);
> QUERY: CREATE TABLE student (gpa float8) inherits ( person);
> CREATE TABLE stud_emp (percent int4) inherits ( emp, student);
> QUERY: CREATE TABLE stud_emp (percent int4) inherits ( emp, student);
> CREATE TABLE city (name name, location box, budget city_budget);
> QUERY: CREATE TABLE city (name name, location box, budget city_budget);
> ERROR:  type name lookup of city_budget failed
>
> [snip]
>
> CREATE AGGREGATE newcnt ( BASETYPE = int4,   SFUNC2 = int4inc_766, STYPE2 = int4, INITCOND2 = '0'  );
> QUERY: CREATE AGGREGATE newcnt ( BASETYPE = int4,   SFUNC2 = int4inc_766, STYPE2 = int4, INITCOND2 = '0'  );
> ERROR:  AggregateCreate: 'int4inc_766'('int4') does not exist
> CREATE AGGREGATE newavg ( BASETYPE = int4,  SFUNC1 = int4pl_177, STYPE1 = int4, INITCOND1 = '0', SFUNC2 =
int4inc_766,STYPE2 = int4, INITCOND2 = '0', FINALFUNC = int4div_154 ); 
> QUERY: CREATE AGGREGATE newavg ( BASETYPE = int4,  SFUNC1 = int4pl_177, STYPE1 = int4, INITCOND1 = '0', SFUNC2 =
int4inc_766,STYPE2 = int4, INITCOND2 = '0', FINALFUNC = int4div_154 ); 
> ERROR:  AggregateCreate: 'int4pl_177('int4', 'int4') does not exist
> CREATE AGGREGATE newsum ( BASETYPE = int4,  SFUNC1 = int4pl_177, STYPE1 = int4, INITCOND1 = '0'   );
> QUERY: CREATE AGGREGATE newsum ( BASETYPE = int4,  SFUNC1 = int4pl_177, STYPE1 = int4, INITCOND1 = '0'   );
> ERROR:  AggregateCreate: 'int4pl_177('int4', 'int4') does not exist
> CREATE OPERATOR #%# (PROCEDURE = int4fac_142 , LEFTARG = int4        );
> QUERY: CREATE OPERATOR #%# (PROCEDURE = int4fac_142 , LEFTARG = int4        );
> ERROR:  OperatorDef: function 'int4fac_142(int4)' does not exist
>  CREATE OPERATOR ## (PROCEDURE = path_inter_973 , LEFTARG = path  , RIGHTARG = path  , COMMUTATOR = ##      );
> QUERY:  CREATE OPERATOR ## (PROCEDURE = path_inter_973 , LEFTARG = path  , RIGHTARG = path  , COMMUTATOR = ##      );
> ERROR:  OperatorDef: function 'path_inter_973(path, path)' does not exist
>  CREATE OPERATOR <% (PROCEDURE = pt_in_widget_19748 , LEFTARG = point  , RIGHTARG = widget  , COMMUTATOR = >=%
);
> QUERY:  CREATE OPERATOR <% (PROCEDURE = pt_in_widget_19748 , LEFTARG = point  , RIGHTARG = widget  , COMMUTATOR = >=%
    ); 
> ERROR:  OperatorGet: left type 'widget' nonexistent
>  CREATE OPERATOR >=% (PROCEDURE = pt_in_widget_19748 , LEFTARG = point  , RIGHTARG = widget  , COMMUTATOR = <%
);
> QUERY:  CREATE OPERATOR >=% (PROCEDURE = pt_in_widget_19748 , LEFTARG = point  , RIGHTARG = widget  , COMMUTATOR = <%
    ); 
> ERROR:  OperatorGet: left type 'widget' nonexistent
>  CREATE OPERATOR @#@ (PROCEDURE = int4fac_142  , RIGHTARG = int4       );
> QUERY:  CREATE OPERATOR @#@ (PROCEDURE = int4fac_142  , RIGHTARG = int4       );
> ERROR:  OperatorDef: function 'int4fac_142(int4)' does not exist
>  CREATE OPERATOR #@# (PROCEDURE = int4fac_142 , LEFTARG = int4        );
> QUERY:  CREATE OPERATOR #@# (PROCEDURE = int4fac_142 , LEFTARG = int4        );
> ERROR:  OperatorDef: function 'int4fac_142(int4)' does not exist
>  COPY char_tbl FROM stdin;
> QUERY:  COPY char_tbl FROM stdin;
>

Looks like I am going to need some help here.

The old code dumped out regproc fields as the pg_proc.proname.  There is
a problem with this.  First, you can have multiple proname entries with
the same proname.  The differ in their argument number/types.  The old
code, when reading in a regproc name, would do a sequential scan of the
pg_proc table, and find the first entry that matches the given proname.
If that is not the one you wanted, too bad.  No way to change it.

The new code outputs the proname, followed by the oid, int4in_1312.
When reading in regproc, you can specify the value just the same as it
was output, or you can specify just the pg_proc oid.  Much more
accurate.

The problem now is that certain functions in pg_dump look at the regproc
values of the defined type, and use those to try and recreate the type.
The problem is that again, the CREATE TYPE expects a function NAME, not
name and oid:

       create type typename (internallength = (number | variable),
            [ externallength = (number | variable), ]
            input = input_function,
            output = output_function
            [, element = typename]
            [, delimiter = <character>]
            [, default = "string" ]
            [, send = send_function ]
            [, receive = receive_function ]
            [, passedbyvalue])

How do people want to handle this?  We are using functions with the same
name more and more for type conversion stuff.  Picking the first
matching entry, and sequentially scanning the table, just seem bad, but
I am not sure of the best solution.

I can imagine in the case above, that we usually would not have multiple
entries with the same name.  Should we have "create type" accept the new
format just like regprocin/regprocout.  It would be easy to do.  You
could specify the name&oid, or just the oid.

It appears create type, create operator, and create aggregate all have
this problem.  I can fix them with very little code.  Just call
regprocin, and it returns the oid.

Comments?

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)



--ELM906585503-11559-0_
Content-Type: text/plain
Content-Disposition: inline; filename="/tmp/a3"
Content-Transfer-Encoding: 7bit