Re: [HACKERS] Names that suddenly include an OID - Mailing list pgsql-hackers

> There's been a very recent change to include the OID in certain names
> in the system table.  Do a "select * from pg_amop;" to see what I
> mean: the values for amopselect and amopnpages didn't have the OID
> appended before.  Was this change intentional?  If so, I'm really
> curious as to why...  And what's the best way to find out, from SQL,
> that 'btreesel' should now be 'btreesel_1268'?

OK, it was me who added it, and it still has some problems.  The reason
the oid was added is that the old code just displayed the
pg_proc.proname for regproc fields, and the input function just found
the FIRST function with a matching name, which is a problem because we
can have multiple functions with the same name but different types.

You can input into regproc with the name_oid format, or you can just
give it the oid.

The problem is that certain function create items now have problems, as
outlined in the e-mails I am attaching, and am still looking for
suggestions on this.  I can put it back to the old way too.

--
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)
From t-ishii@srapc451.sra.co.jp Mon Aug 31 22:41:27 1998
Received: from renoir.op.net (root@renoir.op.net [209.152.193.4])
    by candle.pha.pa.us (8.8.5/8.8.5) with ESMTP id WAA00824
    for <maillist@candle.pha.pa.us>; Mon, 31 Aug 1998 22:41:25 -0400 (EDT)
Received: from sraigw.sra.co.jp (sraigw.sra.co.jp [202.32.10.2]) by renoir.op.net (o1/$Revision: 1.18 $) with ESMTP id
WAA28374for <maillist@candle.pha.pa.us>; Mon, 31 Aug 1998 22:32:36 -0400 (EDT) 
Received: from srashd.sra.co.jp (srashd [133.137.44.12])
    by sraigw.sra.co.jp (8.8.7/3.6Wbeta7-sraigw) with ESMTP id LAA27847;
    Tue, 1 Sep 1998 11:32:12 +0900 (JST)
Received: from srapc451.sra.co.jp (srapc451.sra.co.jp [133.137.44.37])
    by srashd.sra.co.jp (8.8.7/3.6Wbeta7-srambox) with ESMTP id LAA18924;
    Tue, 1 Sep 1998 11:32:26 +0900 (JST)
Received: from srapc451.sra.co.jp (localhost [127.0.0.1]) by srapc451.sra.co.jp (8.8.8/3.5Wpl7) with ESMTP id LAA23293;
Tue,1 Sep 1998 11:32:25 +0900 (JST) 
Message-Id: <199809010232.LAA23293@srapc451.sra.co.jp>
To: Bruce Momjian <maillist@candle.pha.pa.us>
cc: t-ishii@sra.co.jp, lockhart@alumni.caltech.edu, hackers@postgreSQL.org
Subject: Re: [HACKERS] odd pg_dump output?
From: Tatsuo Ishii <t-ishii@sra.co.jp>
Reply-To: t-ishii@sra.co.jp
In-reply-to: Your message of Mon, 31 Aug 1998 03:51:27 -0400.
             <199808310751.DAA11608@candle.pha.pa.us>
Date: Tue, 01 Sep 1998 11:32:24 +0900
Sender: t-ishii@srapc451.sra.co.jp
Status: ROr

>> 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;

From owner-pgsql-hackers@hub.org Tue Sep  1 01:01:08 1998
Received: from renoir.op.net (root@renoir.op.net [209.152.193.4])
    by candle.pha.pa.us (8.8.5/8.8.5) with ESMTP id BAA17462
    for <maillist@candle.pha.pa.us>; Tue, 1 Sep 1998 01:01:06 -0400 (EDT)
Received: from hub.org (hub.org [209.47.148.200]) by renoir.op.net (o1/$Revision: 1.18 $) with ESMTP id AAA07177 for
<maillist@candle.pha.pa.us>;Tue, 1 Sep 1998 00:49:28 -0400 (EDT) 
Received: from localhost (majordom@localhost) by hub.org (8.8.8/8.7.5) with SMTP id AAA26177; Tue, 1 Sep 1998 00:44:21
-0400(EDT) 
Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Tue, 01 Sep 1998 00:41:47 +0000 (EDT)
Received: (from majordom@localhost) by hub.org (8.8.8/8.7.5) id AAA26005 for pgsql-hackers-outgoing; Tue, 1 Sep 1998
00:41:45-0400 (EDT) 
Received: from candle.pha.pa.us (maillist@s5-03.ppp.op.net [209.152.195.67]) by hub.org (8.8.8/8.7.5) with ESMTP id
AAA25803for <hackers@postgreSQL.org>; Tue, 1 Sep 1998 00:41:03 -0400 (EDT) 
Received: (from maillist@localhost)
    by candle.pha.pa.us (8.8.5/8.8.5) id AAA11318;
    Tue, 1 Sep 1998 00:38:06 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Message-Id: <199809010438.AAA11318@candle.pha.pa.us>
Subject: Re: [HACKERS] odd pg_dump output?
In-Reply-To: <199809010232.LAA23293@srapc451.sra.co.jp> from Tatsuo Ishii at "Sep 1, 1998 11:32:24 am"
To: t-ishii@sra.co.jp
Date: Tue, 1 Sep 1998 00:38:06 -0400 (EDT)
Cc: t-ishii@sra.co.jp, lockhart@alumni.caltech.edu, hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL43 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Sender: owner-pgsql-hackers@hub.org
Precedence: bulk
Status: RO

> >> 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)