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