Обсуждение: pg_dump error
Hi all, I think there's an error on pg_dump, my environment is: Lynux 2.0.33 PostgreSQL 6.3 1) ----VARCHAR(-50)------------------------------------------ I created a table as: CREATE TABLE utente ( intestazione_azienda varchar, indirizzo varchar ); using pg_dump -d mydatabase > file file is like: \connect - postgres CREATE TABLE utente (intestazione_azienda varchar(-5), indirizzo varchar(-5)); if I try to load it using psql -d mydatabase < file I have this: ERROR: length for 'varchar' type must be at least 1 2) ----CONSTRAINT-------------------------------------------- I created a table like: CREATE TABLE attivita_a ( azienda CHAR(11) NOT NULL, attivita CHAR(03) NOT NULL, operatore CHAR(03), vet_esterno VARCHAR(45), tipo_allevamento1 CHAR(02), tipo_allevamento2 CHAR(02), esonerato CHAR CHECK(esonerato = 'S' OR esonerato = 'N'), razza_prevalente1 CHAR(03), razza_prevalente2 CHAR(03), iscrizione_libro DATE, iscritta_funzionali CHAR CHECK(iscritta_funzionali = 'S' OR iscritta_funzionali = 'N'), iscritta_tutela CHAR CHECK(iscritta_tutela = 'S' OR iscritta_tutela = 'N'), sigla_tutela CHAR(04), adesione_altri_piani VARCHAR(50), data_adesione DATE, PRIMARY KEY (azienda,attivita) ); using pg_dump I have this: \connect - postgres CREATE TABLE attivita_a ( azienda char(11) NOT NULL, attivita char(3) NOT NULL, operatore char(3), vet_esterno varchar(45), tipo_allevamento1 char(2), tipo_allevamento2 char(2), esonerato char, razza_prevalente1 char(3), razza_prevalente2 char(3), iscrizione_libro date, iscritta_funzionali char, iscritta_tutela char, sigla_tutela char(4), adesione_altri_piani varchar(50), data_adesione date) CONSTRAINT attivita_a_esonerato CHECK esonerato = 'S' OR esonerato = 'N', CONSTRAINT attivita_a_iscritta_funzionali CHECK iscritta_funzionali = 'S' OR iscritta_funzionali = 'N', CONSTRAINT attivita_a_iscritta_tutela CHECK iscritta_tutela = 'S' OR iscritta_tutela = 'N'; -- Note that CONSTRAINTs are the wrong syntax, they are defined after the close parenthesis of CREATE TABLE. 3)----VIEWS------------------------------------------------- I have some views on my database but seems that pg_dump doesn't see those views. Jose'
> > Hi all, > > I think there's an error on pg_dump, > my environment is: > Lynux 2.0.33 > PostgreSQL 6.3 > > 1) ----VARCHAR(-50)------------------------------------------ > > I created a table as: > CREATE TABLE utente ( > intestazione_azienda varchar, > indirizzo varchar > ); > > using pg_dump -d mydatabase > file > > file is like: > \connect - postgres > CREATE TABLE utente (intestazione_azienda varchar(-5), indirizzo varchar(-5)); Basically, something major is wrong in your installation. I have never heard a report like this, and people use pg_dump all the time. -- 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)
On Mon, 15 Jun 1998, Bruce Momjian wrote: > > > > Hi all, > > > > I think there's an error on pg_dump, > > my environment is: > > Lynux 2.0.33 > > PostgreSQL 6.3 > > > > 1) ----VARCHAR(-50)------------------------------------------ > > > > I created a table as: > > CREATE TABLE utente ( > > intestazione_azienda varchar, > > indirizzo varchar > > ); > > > > using pg_dump -d mydatabase > file > > > > file is like: > > \connect - postgres > > CREATE TABLE utente (intestazione_azienda varchar(-5), indirizzo varchar(-5)); > > Basically, something major is wrong in your installation. I have never > heard a report like this, and people use pg_dump all the time. > I have three bugs Bruce: 1) VARCHAR(-5) 2) CONSTRAINTs wrong syntax 3) no VIEWs ?? hygea=> create table prova (var varchar, bp bpchar check (bp='zero')); CREATE hygea=> create view wprova as select var from prova; CREATE pg_dump hygea -s prova \connect - postgres CREATE TABLE prova (var varchar(-5), bp char(-5)) CONSTRAINT prova_bp CHECK bp =COPY prova FROM stdin; \. Jose'
I (thought I) forwarded fixes for the pg_dump constraint syntax bug to this list a couple of weeks ago. I added a -c (compatible) switch to pg_dump to force it to dump constraints in a syntax that pgsql can understand. Here's another copy of the diffs (against 6.3.2). ccb ---------------- *** /usr/local/src/pgsql/6.3.2/src/bin/pg_dump/pg_dump.c Thu Apr 9 19:02:24 1998 --- ./pg_dump.c Tue Jun 9 14:27:36 1998 *************** *** 110,115 **** --- 110,116 ---- int attrNames; /* put attr names into insert strings */ int schemaOnly; int dataOnly; + int compatConstraint; char g_opaque_type[10]; /* name for the opaque type */ *************** *** 126,131 **** --- 127,134 ---- fprintf(stderr, "\t -a \t\t dump out only the data, no schema\n"); fprintf(stderr, + "\t -c \t\t generate pgsql-compatible CONSTRAINT syntax\n"); + fprintf(stderr, "\t -d \t\t dump data as proper insert strings\n"); fprintf(stderr, "\t -D \t\t dump data as inserts with attribute names\n"); *************** *** 551,567 **** g_comment_end[0] = '\0'; strcpy(g_opaque_type, "opaque"); ! dataOnly = schemaOnly = dumpData = attrNames = 0; progname = *argv; ! while ((c = getopt(argc, argv, "adDf:h:op:st:vzu")) != EOF) { switch (c) { case 'a': /* Dump data only */ dataOnly = 1; break; case 'd': /* dump data as proper insert strings */ dumpData = 1; break; --- 554,574 ---- g_comment_end[0] = '\0'; strcpy(g_opaque_type, "opaque"); ! compatConstraint = dataOnly = schemaOnly = dumpData = attrNames = 0; progname = *argv; ! while ((c = getopt(argc, argv, "acdDf:h:op:st:vzu")) != EOF) { switch (c) { case 'a': /* Dump data only */ dataOnly = 1; break; + case 'c': /* generate constraint syntax that + can be read back into postgreSQL */ + compatConstraint = 1; + break; case 'd': /* dump data as proper insert strings */ dumpData = 1; break; *************** *** 1496,1502 **** query[0] = 0; if (name[0] != '$') sprintf(query, "CONSTRAINT %s ", name); ! sprintf(query, "%sCHECK %s", query, expr); tblinfo[i].check_expr[i2] = strdup(query); } PQclear(res2); --- 1503,1514 ---- query[0] = 0; if (name[0] != '$') sprintf(query, "CONSTRAINT %s ", name); ! if( compatConstraint ) { ! sprintf(query, "%sCHECK (%s)", query, expr); ! } ! else { ! sprintf(query, "%sCHECK %s", query, expr); ! } tblinfo[i].check_expr[i2] = strdup(query); } PQclear(res2); *************** *** 2518,2523 **** --- 2530,2546 ---- } } + if( compatConstraint ) { + /* put the CONSTRAINTS inside the table def */ + for (k = 0; k < tblinfo[i].ncheck; k++) + { + sprintf(q, "%s%s %s", + q, + (actual_atts + k > 0) ? ", " : "", + tblinfo[i].check_expr[k]); + } + } + strcat(q, ")"); if (numParents > 0) *************** *** 2533,2540 **** strcat(q, ")"); } ! if (tblinfo[i].ncheck > 0) { for (k = 0; k < tblinfo[i].ncheck; k++) { sprintf(q, "%s%s %s", --- 2556,2564 ---- strcat(q, ")"); } ! if( !compatConstraint ) { + /* put the CONSTRAINT defs outside the table def */ for (k = 0; k < tblinfo[i].ncheck; k++) { sprintf(q, "%s%s %s", *************** *** 2543,2548 **** --- 2567,2573 ---- tblinfo[i].check_expr[k]); } } + strcat(q, ";\n"); fputs(q, fout); if (acls)
> > > I (thought I) forwarded fixes for the pg_dump constraint syntax > bug to this list a couple of weeks ago. I added a -c (compatible) > switch to pg_dump to force it to dump constraints in a syntax that > pgsql can understand. > > Here's another copy of the diffs (against 6.3.2). > I just applied this patch a few days ago. I e-mailed you asking why there is an option for this behavour. Seems like it should always be on. Please let me know. -- 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)
On Wed, 17 Jun 1998, Charles Bennett wrote: > > I (thought I) forwarded fixes for the pg_dump constraint syntax > bug to this list a couple of weeks ago. I added a -c (compatible) > switch to pg_dump to force it to dump constraints in a syntax that > pgsql can understand. > > Here's another copy of the diffs (against 6.3.2). > > ccb I applied your patch, Charles and it works, obviouly I remove the -c parameter because there isn't another syntax for CONSTRAINTs. PostgreSQL has the SQL92 syntax. Thanks, Jose'
Bruce Momjian said: > I just applied this patch a few days ago. I e-mailed you asking why > there is an option for this behavour. Seems like it should always be > on. > > Please let me know. Sorry I missed the mail... I set this up as an option because I though the initial behavior might have been put in for a reason - one that I didn't understand. I have no objection if you decide to make PGSQL-compatible dump syntax the default. ccb --- Charles C. Bennett, Jr. PubWeb, Inc. Software Engineer The Publishing <-> Printing Network Agent of Disintermediation 4A Gill St. ccb@pubweb.net Woburn, MA 01801
> > On Wed, 17 Jun 1998, Charles Bennett wrote: > > > > > I (thought I) forwarded fixes for the pg_dump constraint syntax > > bug to this list a couple of weeks ago. I added a -c (compatible) > > switch to pg_dump to force it to dump constraints in a syntax that > > pgsql can understand. > > > > Here's another copy of the diffs (against 6.3.2). > > > > ccb > I applied your patch, Charles and it works, obviouly I remove the -c parameter > because there isn't another syntax for CONSTRAINTs. PostgreSQL has > the SQL92 syntax. OK, I have removed the -c syntax for pg_dump, so all dumps now use the new format. -- 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)
> > > Bruce Momjian said: > > > I just applied this patch a few days ago. I e-mailed you asking why > > there is an option for this behavour. Seems like it should always be > > on. > > > > Please let me know. > > > Sorry I missed the mail... > > I set this up as an option because I though the initial behavior > might have been put in for a reason - one that I didn't understand. > I have no objection if you decide to make PGSQL-compatible dump > syntax the default. Done. -- 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)