Thread: odd pg_dump output?
I found that current pg_dump command produces wrong output if a table
name includes upper letters (See below).
in bin/pg_dump.c:
sprintf(q, "CREATE TABLE \"%s\" (", fmtId(tblinfo[i].relname));
Here fmtId() returns double quoted results if the table name (or
attribute name, index name...) has upper letters. then sprintf adds
extra double quotations. The result is too much double quotations!
I don't understand why sprintf adds extra quotes (I think 6.3.2
didn't).
Comments?
--
Tatsuo Ishii
t-ishii@sra.co.jp
---------------------------------------------------------------
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: test
test=> create table "TTT" ("III" int);
CREATE
test=> insert into "TTT" values(100);
INSERT 143849 1
test=> select * from "TTT";
III
---
100
(1 row)
test=>
[srapc451.sra.co.jp]t-ishii{274} pg_dump test
CREATE TABLE ""TTT"" (""III"" int4);
COPY "TTT" FROM stdin;
100
\.
> I found that current pg_dump command produces wrong output if a table
> name includes upper letters (See below).
> fmtId() returns double quoted results if the table name (or
> attribute name, index name...) has upper letters.
> I don't understand why sprintf adds extra quotes (I think 6.3.2
> didn't).
I added some stuff in to handle double quotes; obviously some parts were
already handled. Thanks for catching it; will look at it...
- Tom
> I found that current pg_dump command produces wrong output if a table
> name includes upper letters (See below).
>
> in bin/pg_dump.c:
>
> sprintf(q, "CREATE TABLE \"%s\" (", fmtId(tblinfo[i].relname));
>
> Here fmtId() returns double quoted results if the table name (or
> attribute name, index name...) has upper letters. then sprintf adds
> extra double quotations. The result is too much double quotations!
>
> I don't understand why sprintf adds extra quotes (I think 6.3.2
> didn't).
> Comments?
> --
> Tatsuo Ishii
> t-ishii@sra.co.jp
This appears to be fixed. Beta is September 1.
> ---------------------------------------------------------------
> Welcome to the POSTGRESQL interactive sql monitor:
> Please read the file COPYRIGHT for copyright terms of POSTGRESQL
>
> type \? for help on slash commands
> type \q to quit
> type \g or terminate with semicolon to execute query
> You are currently connected to the database: test
>
> test=> create table "TTT" ("III" int);
>
CREATE
> test=> insert into "TTT" values(100);
>
INSERT 143849 1
> test=> select * from "TTT";
> III
> ---
> 100
> (1 row)
>
> test=>
> [srapc451.sra.co.jp]t-ishii{274} pg_dump test
> CREATE TABLE ""TTT"" (""III"" int4);
> COPY "TTT" FROM stdin;
> 100
> \.
>
>
>
--
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)
>This appears to be fixed. Beta is September 1.
Ok. BTW Mr. Kataoka who is maintaing Japanese version of PostgreSQL
ODBC driver have found a bug in 6.3.2 pg_dump and have made patches. I
confirmed that the same bug still exists in the current source
tree. So I made up patches based on Kataoka's. Here are some
explanations.
o fmtId() returns pointer to a static memory in it. In the meantime
there is a line where is fmtId() called twice without saving the first
value returned by fmtId(). So second call to fmtId() will break the
first one.
o findTableByName() looks up a table by its name. if a table name
contanins upper letters or non ascii chars, fmtId() will returns a
name quoted in double quotes, which will not what findTableByName()
wants. The result is SEG fault.
--
Tatsuo Ishii
t-ishii@sra.co.jp
*** pg_dump.c.orig Wed Aug 26 00:02:04 1998
--- pg_dump.c Sat Aug 29 22:34:24 1998
***************
*** 2435,2441 ****
int i,
j,
k;
! char q[MAXQUERYLEN];
char **parentRels; /* list of names of parent relations */
int numParents;
int actual_atts; /* number of attrs in this CREATE statment */
--- 2435,2443 ----
int i,
j,
k;
! char q[MAXQUERYLEN],
! id1[MAXQUERYLEN],
! id2[MAXQUERYLEN];
char **parentRels; /* list of names of parent relations */
int numParents;
int actual_atts; /* number of attrs in this CREATE statment */
***************
*** 2506,2516 ****
}
else
{
sprintf(q, "%s%s%s %s",
q,
(actual_atts > 0) ? ", " : "",
! fmtId(tblinfo[i].attnames[j]),
! fmtId(tblinfo[i].typnames[j]));
actual_atts++;
}
if (tblinfo[i].adef_expr[j] != NULL)
--- 2508,2520 ----
}
else
{
+ strcpy(id1, fmtId(tblinfo[i].attnames[j]));
+ strcpy(id2, fmtId(tblinfo[i].typnames[j]));
sprintf(q, "%s%s%s %s",
q,
(actual_atts > 0) ? ", " : "",
! id1,
! id2);
actual_atts++;
}
if (tblinfo[i].adef_expr[j] != NULL)
***************
*** 2572,2584 ****
indclass;
int nclass;
! char q[MAXQUERYLEN];
PGresult *res;
for (i = 0; i < numIndices; i++)
{
tableInd = findTableByName(tblinfo, numTables,
! fmtId(indinfo[i].indrelname));
if (strcmp(indinfo[i].indproc, "0") == 0)
funcname = NULL;
--- 2576,2590 ----
indclass;
int nclass;
! char q[MAXQUERYLEN],
! id1[MAXQUERYLEN],
! id2[MAXQUERYLEN];
PGresult *res;
for (i = 0; i < numIndices; i++)
{
tableInd = findTableByName(tblinfo, numTables,
! (indinfo[i].indrelname));
if (strcmp(indinfo[i].indproc, "0") == 0)
funcname = NULL;
***************
*** 2659,2666 ****
attname, indinfo[i].indexrelname);
exit_nicely(g_conn);
}
sprintf(attlist + strlen(attlist), "%s%s %s",
! (k == 0) ? "" : ", ", fmtId(attname), fmtId(classname[k]));
free(classname[k]);
}
}
--- 2665,2674 ----
attname, indinfo[i].indexrelname);
exit_nicely(g_conn);
}
+ strcpy(id1, fmtId(attname));
+ strcpy(id2, fmtId(classname[k]));
sprintf(attlist + strlen(attlist), "%s%s %s",
! (k == 0) ? "" : ", ", id1, id2);
free(classname[k]);
}
}
***************
*** 2668,2677 ****
if (!tablename || (!strcmp(indinfo[i].indrelname, tablename)))
{
sprintf(q, "CREATE %s INDEX %s on %s using %s (",
(strcmp(indinfo[i].indisunique, "t") == 0) ? "UNIQUE" : "",
! fmtId(indinfo[i].indexrelname),
! fmtId(indinfo[i].indrelname),
indinfo[i].indamname);
if (funcname)
{
--- 2676,2687 ----
if (!tablename || (!strcmp(indinfo[i].indrelname, tablename)))
{
+ strcpy(id1, fmtId(indinfo[i].indexrelname));
+ strcpy(id2, fmtId(indinfo[i].indrelname));
sprintf(q, "CREATE %s INDEX %s on %s using %s (",
(strcmp(indinfo[i].indisunique, "t") == 0) ? "UNIQUE" : "",
! id1,
! id2,
indinfo[i].indamname);
if (funcname)
{
> o fmtId() returns pointer to a static memory in it. In the meantime
> there is a line where is fmtId() called twice without saving the first
> value returned by fmtId(). So second call to fmtId() will break the
> first one.
>
> o findTableByName() looks up a table by its name. if a table name
> contanins upper letters or non ascii chars, fmtId() will returns a
> name quoted in double quotes, which will not what findTableByName()
> wants. The result is SEG fault.
Thanks Tatsuo! But these are older problems, not new breakage from me,
right? Have you had a chance to test the latest pg_dump with your
patches? I'm still concerned that I didn't test pg_dump with a
regression test dump/reload/dump sequence (by comparing the two dump
files).
- Tom
Patch applied.
> >This appears to be fixed. Beta is September 1.
>
> Ok. BTW Mr. Kataoka who is maintaing Japanese version of PostgreSQL
> ODBC driver have found a bug in 6.3.2 pg_dump and have made patches. I
> confirmed that the same bug still exists in the current source
> tree. So I made up patches based on Kataoka's. Here are some
> explanations.
>
> o fmtId() returns pointer to a static memory in it. In the meantime
> there is a line where is fmtId() called twice without saving the first
> value returned by fmtId(). So second call to fmtId() will break the
> first one.
>
> o findTableByName() looks up a table by its name. if a table name
> contanins upper letters or non ascii chars, fmtId() will returns a
> name quoted in double quotes, which will not what findTableByName()
> wants. The result is SEG fault.
> --
> Tatsuo Ishii
> t-ishii@sra.co.jp
>
> *** pg_dump.c.orig Wed Aug 26 00:02:04 1998
> --- pg_dump.c Sat Aug 29 22:34:24 1998
> ***************
> *** 2435,2441 ****
> int i,
> j,
> k;
> ! char q[MAXQUERYLEN];
> char **parentRels; /* list of names of parent relations */
> int numParents;
> int actual_atts; /* number of attrs in this CREATE statment */
> --- 2435,2443 ----
> int i,
> j,
> k;
> ! char q[MAXQUERYLEN],
> ! id1[MAXQUERYLEN],
> ! id2[MAXQUERYLEN];
> char **parentRels; /* list of names of parent relations */
> int numParents;
> int actual_atts; /* number of attrs in this CREATE statment */
> ***************
> *** 2506,2516 ****
> }
> else
> {
> sprintf(q, "%s%s%s %s",
> q,
> (actual_atts > 0) ? ", " : "",
> ! fmtId(tblinfo[i].attnames[j]),
> ! fmtId(tblinfo[i].typnames[j]));
> actual_atts++;
> }
> if (tblinfo[i].adef_expr[j] != NULL)
> --- 2508,2520 ----
> }
> else
> {
> + strcpy(id1, fmtId(tblinfo[i].attnames[j]));
> + strcpy(id2, fmtId(tblinfo[i].typnames[j]));
> sprintf(q, "%s%s%s %s",
> q,
> (actual_atts > 0) ? ", " : "",
> ! id1,
> ! id2);
> actual_atts++;
> }
> if (tblinfo[i].adef_expr[j] != NULL)
> ***************
> *** 2572,2584 ****
> indclass;
> int nclass;
>
> ! char q[MAXQUERYLEN];
> PGresult *res;
>
> for (i = 0; i < numIndices; i++)
> {
> tableInd = findTableByName(tblinfo, numTables,
> ! fmtId(indinfo[i].indrelname));
>
> if (strcmp(indinfo[i].indproc, "0") == 0)
> funcname = NULL;
> --- 2576,2590 ----
> indclass;
> int nclass;
>
> ! char q[MAXQUERYLEN],
> ! id1[MAXQUERYLEN],
> ! id2[MAXQUERYLEN];
> PGresult *res;
>
> for (i = 0; i < numIndices; i++)
> {
> tableInd = findTableByName(tblinfo, numTables,
> ! (indinfo[i].indrelname));
>
> if (strcmp(indinfo[i].indproc, "0") == 0)
> funcname = NULL;
> ***************
> *** 2659,2666 ****
> attname, indinfo[i].indexrelname);
> exit_nicely(g_conn);
> }
> sprintf(attlist + strlen(attlist), "%s%s %s",
> ! (k == 0) ? "" : ", ", fmtId(attname), fmtId(classname[k]));
> free(classname[k]);
> }
> }
> --- 2665,2674 ----
> attname, indinfo[i].indexrelname);
> exit_nicely(g_conn);
> }
> + strcpy(id1, fmtId(attname));
> + strcpy(id2, fmtId(classname[k]));
> sprintf(attlist + strlen(attlist), "%s%s %s",
> ! (k == 0) ? "" : ", ", id1, id2);
> free(classname[k]);
> }
> }
> ***************
> *** 2668,2677 ****
> if (!tablename || (!strcmp(indinfo[i].indrelname, tablename)))
> {
>
> sprintf(q, "CREATE %s INDEX %s on %s using %s (",
> (strcmp(indinfo[i].indisunique, "t") == 0) ? "UNIQUE" : "",
> ! fmtId(indinfo[i].indexrelname),
> ! fmtId(indinfo[i].indrelname),
> indinfo[i].indamname);
> if (funcname)
> {
> --- 2676,2687 ----
> if (!tablename || (!strcmp(indinfo[i].indrelname, tablename)))
> {
>
> + strcpy(id1, fmtId(indinfo[i].indexrelname));
> + strcpy(id2, fmtId(indinfo[i].indrelname));
> sprintf(q, "CREATE %s INDEX %s on %s using %s (",
> (strcmp(indinfo[i].indisunique, "t") == 0) ? "UNIQUE" : "",
> ! id1,
> ! id2,
> indinfo[i].indamname);
> if (funcname)
> {
>
--
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)
At 4:27 PM 98.8.29 +0000, Thomas G. Lockhart wrote: >Thanks Tatsuo! But these are older problems, not new breakage from me, >right? Oh yes, yes. I know you are not responsible for these bugs. I should have made clear this point. >Have you had a chance to test the latest pg_dump with your >patches? I'm still concerned that I didn't test pg_dump with a >regression test dump/reload/dump sequence (by comparing the two dump >files). Not yet. pg_dump with the regression db fails while dumping constraints related tables (I don't remeber table names). Seems this is due to the constraints test failure (backend's death). Still I have serious regression problems with my LinuxPPC box. As someone mentioned none -O2 might hep. If that fixes the problems, I think I can start the testing of pg_dump. I will try this today. -- Tatsuo Ishii t-ishii@sra.co.jp
>Thanks Tatsuo! But these are older problems, not new breakage from me,
>right? Have you had a chance to test the latest pg_dump with your
>patches? I'm still concerned that I didn't test pg_dump with a
>regression test dump/reload/dump sequence (by comparing the two dump
>files).
I ran pg_dump with the regression DB, then did psql to reload.
seems there are some problems in the output from pg_dump.
BTW, this is FreeBSD 2.2.6 and one of the regression gets dump core
(select_having).
--
Tatsuo Ishii
t-ishii@sra.co.jp
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 "("
CREATE TYPE city_budget ( internallength = 16, externallength = -1, input = 1287(int44in), output = 653(int44out), send
=653(int44out), receive = 1287(int44in), default = '-', element = int4, delimiter = ',');
ERROR: parser: parse error at or near "("
CREATE TYPE _city_budget ( 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 "("
CREATE SEQUENCE default_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('default_seq');
CREATE
nextval
-------
1
(1 row)
CREATE SEQUENCE check_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
CREATE SEQUENCE insert_seq start 8 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('insert_seq');
CREATE
CREATE
nextval
-------
8
(1 row)
CREATE SEQUENCE rtest_seq start 4 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('rtest_seq');
CREATE
nextval
-------
4
(1 row)
CREATE TABLE char_tbl (f1 char(4));
CREATE TABLE varchar_tbl (f1 varchar(4));
CREATE TABLE text_tbl (f1 text);
CREATE TABLE int2_tbl (f1 int2);
CREATE TABLE int4_tbl (f1 int4);
CREATE TABLE int8_tbl (q1 int8, q2 int8);
CREATE TABLE float4_tbl (f1 float4);
CREATE TABLE float8_tbl (f1 float8);
CREATE TABLE point_tbl (f1 point);
CREATE TABLE lseg_tbl (s lseg);
CREATE TABLE box_tbl (f1 box);
CREATE TABLE path_tbl (f1 path);
CREATE TABLE polygon_tbl (f1 polygon);
CREATE TABLE circle_tbl (f1 circle);
CREATE TABLE timespan_tbl (f1 timespan);
CREATE TABLE datetime_tbl (d1 datetime);
CREATE TABLE reltime_tbl (f1 reltime);
CREATE TABLE abstime_tbl (f1 abstime);
CREATE TABLE tinterval_tbl (f1 tinterval);
CREATE TABLE hobbies_r (name text, person text);
CREATE TABLE equipment_r (name text, hobby text);
CREATE TABLE onek (unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4,
twothousandint4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name);
CREATE TABLE tenk1 (unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand
int4,twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name);
CREATE TABLE tenk2 (unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand
int4,twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name);
CREATE TABLE person (name text, age int4, location point);
CREATE TABLE emp (salary int4, manager name) inherits ( person);
CREATE TABLE student (gpa float8) inherits ( person);
CREATE TABLE stud_emp (percent int4) inherits ( emp, student);
CREATE TABLE city (name name, location box, budget city_budget);
ERROR: type name lookup of city_budget failed
CREATE TABLE dept (dname name, mgrname text);
CREATE TABLE slow_emp4000 (home_base box);
CREATE TABLE fast_emp4000 (home_base box);
CREATE TABLE road (name text, thepath path);
CREATE TABLE ihighway () inherits ( road);
CREATE TABLE shighway (surface text) inherits ( road);
CREATE TABLE real_city (pop int4, cname text, outline path);
CREATE TABLE a_star (class char(1), aa int4, a text);
CREATE TABLE b_star (bb text) inherits ( a_star);
CREATE TABLE c_star (cc name) inherits ( a_star);
CREATE TABLE d_star (dd float8) inherits ( b_star, c_star);
CREATE TABLE e_star (ee int2, e int4) inherits ( c_star);
CREATE TABLE f_star (ff polygon, f int4) inherits ( e_star);
CREATE TABLE aggtest (a int2, b float4);
CREATE TABLE arrtest (a _int2, b _int4, c _name, d _text, e _float8);
CREATE TABLE hash_i4_heap (seqno int4, random int4);
CREATE TABLE hash_name_heap (seqno int4, random name);
CREATE TABLE hash_txt_heap (seqno int4, random text);
CREATE TABLE hash_f8_heap (seqno int4, random float8);
CREATE TABLE bt_i4_heap (seqno int4, random int4);
CREATE TABLE bt_name_heap (seqno name, random int4);
CREATE TABLE bt_txt_heap (seqno text, random int4);
CREATE TABLE bt_f8_heap (seqno float8, random int4);
CREATE TABLE default_tbl (i int4 DEFAULT 100, x text DEFAULT 'vadim', f float8 DEFAULT 123.456);
CREATE TABLE defaultexpr_tbl (i1 int4 DEFAULT 100 + ( 200 - 199 ) * 2, i2 int4 DEFAULT nextval ( 'default_seq' ));
CREATE TABLE check_tbl (x int4, CONSTRAINT check_con CHECK (x > 3));
CREATE TABLE check2_tbl (x int4, y text, z int4, CONSTRAINT sequence_con CHECK (x > 3 AND y <> 'check failed' AND z <
8));
CREATE TABLE insert_tbl (x int4 DEFAULT nextval ( 'insert_seq' ), y text DEFAULT '-NULL-', z int4 DEFAULT - 1 * currval
('insert_seq' ), CONSTRAINT insert_con CHECK (x >= 3 AND y <> 'check failed' AND x < 8), CHECK (x + z = 0));
CREATE TABLE copy_tbl (x int4, y text, z int4, CONSTRAINT copy_con CHECK (x > 3 AND y <> 'check failed' AND x < 7));
CREATE TABLE onek2 (unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand
int4,twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name);
CREATE TABLE bprime (unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand
int4,twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name);
CREATE TABLE ramp (name text, thepath path);
CREATE TABLE iportaltest (i int4, d float4, p polygon);
CREATE TABLE test_having (a int4, b int4, c char(8), d char(1));
CREATE TABLE subselect_tbl (f1 int4, f2 int4, f3 float8);
CREATE TABLE xacttest (a int2, b float4);
CREATE TABLE random_tbl (random int4);
CREATE TABLE rtest_t1 (a int4, b int4);
CREATE TABLE rtest_t2 (a int4, b int4);
CREATE TABLE rtest_t3 (a int4, b int4);
CREATE TABLE rtest_system (sysname text, sysdesc text);
CREATE TABLE rtest_interface (sysname text, ifname text);
CREATE TABLE rtest_person (pname text, pdesc text);
CREATE TABLE rtest_admin (pname text, sysname text);
CREATE TABLE rtest_emp (ename char(20), salary money);
CREATE TABLE rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money);
CREATE TABLE rtest_empmass (ename char(20), salary money);
CREATE TABLE rtest_t4 (a int4, b text);
CREATE TABLE rtest_t5 (a int4, b text);
CREATE TABLE rtest_t6 (a int4, b text);
CREATE TABLE rtest_t7 (a int4, b text);
CREATE TABLE rtest_t8 (a int4, b text);
CREATE TABLE rtest_t9 (a int4, b text);
CREATE TABLE rtest_order1 (a int4);
CREATE TABLE rtest_order2 (a int4, b int4, c text);
CREATE TABLE rtest_nothn1 (a int4, b text);
CREATE TABLE rtest_nothn2 (a int4, b text);
CREATE TABLE rtest_nothn3 (a int4, b text);
CREATE TABLE rtest_nothn4 (a int4, b text);
CREATE FUNCTION widget_in (opaque ) RETURNS widget AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
NOTICE: ProcedureCreate: type 'widget' is not yet defined
CREATE FUNCTION widget_out (opaque ) RETURNS opaque AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
CREATE FUNCTION check_primary_key ( ) RETURNS opaque AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../../../../contrib/spi/refint.so'LANGUAGE
'C';
CREATE FUNCTION check_foreign_key ( ) RETURNS opaque AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../../../../contrib/spi/refint.so'LANGUAGE
'C';
CREATE FUNCTION autoinc ( ) RETURNS opaque AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../../../../contrib/spi/autoinc.so'LANGUAGE
'C';
CREATE FUNCTION funny_dup17 ( ) RETURNS opaque AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
CREATE FUNCTION ttdummy ( ) RETURNS opaque AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
CREATE FUNCTION set_ttdummy (int4 ) RETURNS int4 AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
CREATE FUNCTION hobbies (person ) RETURNS SETOF hobbies_r AS 'select * from hobbies_r where person = $1.name' LANGUAGE
'SQL';
CREATE FUNCTION hobby_construct (text,text ) RETURNS hobbies_r AS 'select $1 as name, $2 as hobby' LANGUAGE 'SQL';
CREATE FUNCTION equipment (hobbies_r ) RETURNS SETOF equipment_r AS 'select * from equipment_r where hobby = $1.name'
LANGUAGE'SQL';
CREATE FUNCTION user_relns ( ) RETURNS SETOF name AS 'select relname
from pg_class
where relname !~ ''pg_.*'' and
relkind <> ''i'' ' LANGUAGE 'SQL';
CREATE FUNCTION pt_in_widget (point,widget ) RETURNS int4 AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
NOTICE: ProcedureCreate: arg type 'widget' is only a shell
CREATE FUNCTION overpaid (emp ) RETURNS bool AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
CREATE FUNCTION boxarea (box ) RETURNS int4 AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
CREATE FUNCTION interpt_pp (path,path ) RETURNS point AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
CREATE FUNCTION reverse_name (name ) RETURNS name AS
'/mnt2/home/mgr/t-ishii/src/PostgreSQL/anonCVS/pgsql/src/test/regress/input/../regress.so'LANGUAGE 'C';
CREATE AGGREGATE newcnt ( BASETYPE = int4, SFUNC2 = 766(int4inc), STYPE2 = int4, INITCOND2 = '0' );
ERROR: parser: parse error at or near "("
CREATE AGGREGATE newavg ( BASETYPE = int4, SFUNC1 = 177(int4pl), STYPE1 = int4, INITCOND1 = '0', SFUNC2 =
766(int4inc),STYPE2 = int4, INITCOND2 = '0', FINALFUNC = 154(int4div) );
ERROR: parser: parse error at or near "("
CREATE AGGREGATE newsum ( BASETYPE = int4, SFUNC1 = 177(int4pl), STYPE1 = int4, INITCOND1 = '0' );
ERROR: parser: parse error at or near "("
CREATE OPERATOR #%# (PROCEDURE = 142(int4fac) , LEFTARG = int4 );
ERROR: parser: parse error at or near "("
CREATE OPERATOR ## (PROCEDURE = 973(path_inter) , LEFTARG = path , RIGHTARG = path , COMMUTATOR = ## );
ERROR: parser: parse error at or near "("
CREATE OPERATOR <% (PROCEDURE = 145188(pt_in_widget) , LEFTARG = point , RIGHTARG = widget , COMMUTATOR = >=%
);
ERROR: parser: parse error at or near "("
CREATE OPERATOR >=% (PROCEDURE = 145188(pt_in_widget) , LEFTARG = point , RIGHTARG = widget , COMMUTATOR = <%
);
ERROR: parser: parse error at or near "("
CREATE OPERATOR @#@ (PROCEDURE = 142(int4fac) , RIGHTARG = int4 );
ERROR: parser: parse error at or near "("
CREATE OPERATOR #@# (PROCEDURE = 142(int4fac) , LEFTARG = int4 );
ERROR: parser: parse error at or near "("
COPY char_tbl FROM stdin;
> >Thanks Tatsuo! But these are older problems, not new breakage from me,
> >right? Have you had a chance to test the latest pg_dump with your
> >patches? I'm still concerned that I didn't test pg_dump with a
> >regression test dump/reload/dump sequence (by comparing the two dump
> >files).
>
> I ran pg_dump with the regression DB, then did psql to reload.
> seems there are some problems in the output from pg_dump.
>
> BTW, this is FreeBSD 2.2.6 and one of the regression gets dump core
> (select_having).
> --
> Tatsuo Ishii
> t-ishii@sra.co.jp
>
> 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.
Sorry.
--
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)
> >Thanks Tatsuo! But these are older problems, not new breakage from me,
> >right? Have you had a chance to test the latest pg_dump with your
> >patches? I'm still concerned that I didn't test pg_dump with a
> >regression test dump/reload/dump sequence (by comparing the two dump
> >files).
>
> I ran pg_dump with the regression DB, then did psql to reload.
> seems there are some problems in the output from pg_dump.
>
> BTW, this is FreeBSD 2.2.6 and one of the regression gets dump core
> (select_having).
> --
> Tatsuo Ishii
> t-ishii@sra.co.jp
>
> 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 "("
Ah, I see now. It is even more complicated that I thought. Creating a
type requires you to specify a valud regproc value WITHOUT QUOTES.
Tricky. My fix should do it. It will appear as widget_in_144673. You
can optionally just specify the object id. Output will always show
both. I have to use an undercore, because that is the only way to make
them valid identifiers.
--
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)
>> 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;
--ELM906585503-11559-0_
Content-Type: text/plain
Content-Disposition: inline; filename="/tmp/a2"
Content-Transfer-Encoding: 7bit
>> 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;
--ELM906585503-11559-0_
Content-Type: text/plain
Content-Disposition: inline; filename="/tmp/a2"
Content-Transfer-Encoding: 7bit
>> 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;
--ELM906585503-11559-0_
Content-Type: text/plain
Content-Disposition: inline; filename="/tmp/a2"
Content-Transfer-Encoding: 7bit
> >> 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
> >> 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
> >> 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