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