Thread: Resue of the sql dump to create new database.

Resue of the sql dump to create new database.

From

Anil Jangam

Date:

18 December 2001, 07:22:18

Hi,

I am trying to use the exiting sql dump (stored in a file test.sql) as
follows to create the database tables in another database. Now, When I run
this using command "psql -U uss -d enterprisedata -f test.sql" , I get the
following error.

NOTE: There is alreay a database exist named "enterprise" with same
properties. I am just trying to create a mirror image in another database
under new owner.

What is wrong with this?? Where as when it is created using pgAdmin, it
doesn't give this kind of error message. I hope we should be able to use the
.sql file content to create the database. (This test.sql file is beiong
generated with "File -> Save DB Schema" option in the pull down menu. Also
pg_dump command can be used on the prompt.)

-anil.

*********** Content of test.sql file *****************

CREATE TABLE "FunctionEntityInventory" (
  "ComponentID" int2 NOT NULL,
  "EntityType" varchar(25),
  "EntityName" varchar(25),
  "RedundencyRequired" char(1),
  CONSTRAINT "FunctionEntityInventory_pkey" PRIMARY KEY ("ComponentID")
);
REVOKE ALL ON "FunctionEntityInventory" FROM PUBLIC;
GRANT ALL ON "FunctionEntityInventory" TO "uss";
COMMENT ON TABLE "FunctionEntityInventory" IS 'This table is the central
repository for all the compnents in the network. The components here are UCM
and USC.';

CREATE TABLE "DataAccessIdentifier" (
  "DataAccessID" int2 NOT NULL,
  "PrimaryComponentID" int2 NOT NULL,
  "SecondaryComponentID" int2 NOT NULL,
  CONSTRAINT "DataAccessIdentifier_pkey" PRIMARY KEY ("DataAccessID"),
  CONSTRAINT "PrimaryCompID" FOREIGN KEY (PrimaryComponentID) REFERENCES
"FunctionEntityInventory" (ComponentID) ON DELETE CASCADE ON UPDATE CASCADE
NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "SecondaryCompID" FOREIGN KEY (SecondaryComponentID) REFERENCES
"FunctionEntityInventory" (ComponentID) ON DELETE CASCADE ON UPDATE CASCADE
NOT DEFERRABLE INITIALLY IMMEDIATE
);
REVOKE ALL ON "DataAccessIdentifier" FROM PUBLIC;
GRANT ALL ON "DataAccessIdentifier" TO "uss";
COMMENT ON TABLE "DataAccessIdentifier" IS 'This table defines the data
access identifier, which is a common interface for primary and secondary
components. Thus both the components looks at the same data.';

*******************************************************

**************** Error message **********************

$ psql -U uss -d enterprisedata -f test.sql
psql:test.sql:7: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
index 'F
unctionEntityInventory_pkey' for table 'FunctionEntityInventory'
CREATE
CHANGE
CHANGE
COMMENT

  =============== upto this it creates the 'FunctionEntityInventory' table
in the database ==================

psql:test.sql:19: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
index '
DataAccessIdentifier_pkey' for table 'DataAccessIdentifier'
psql:test.sql:19: NOTICE:  CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)
psql:test.sql:19: ERROR:  columns referenced in foreign key constraint not
found.
psql:test.sql:20: ERROR:  Relation 'DataAccessIdentifier' does not exist
psql:test.sql:21: ERROR:  Relation 'DataAccessIdentifier' does not exist
psql:test.sql:22: ERROR:  pg_ownercheck: class "DataAccessIdentifier" not
found

*******************************************************

Re: Resue of the sql dump to create new database.

From

Dave Page

Date:

18 December 2001, 07:32:33

I think you've found a bug in pgAdmin which I'll look to fix for the next
release.. Can you try editting the dump file to add double quotes arround
the column names in the fk definition eg.

CREATE TABLE "DataAccessIdentifier" (
  "DataAccessID" int2 NOT NULL,
  "PrimaryComponentID" int2 NOT NULL,
  "SecondaryComponentID" int2 NOT NULL,
  CONSTRAINT "DataAccessIdentifier_pkey" PRIMARY KEY ("DataAccessID"),
  CONSTRAINT "PrimaryCompID" FOREIGN KEY ("PrimaryComponentID") REFERENCES
"FunctionEntityInventory" ("ComponentID") ON DELETE CASCADE ON UPDATE
CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "SecondaryCompID" FOREIGN KEY ("SecondaryComponentID")
REFERENCES "FunctionEntityInventory" ("ComponentID") ON DELETE CASCADE ON
UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE );

Does that fix it?

Regards, Dave.

> -----Original Message-----
> From: Anil Jangam [mailto:anilj@indts.com]
> Sent: 18 December 2001 09:33
> To: 'Dave Page'; pgsql-cygwin@postgresql.org
> Subject: Resue of the sql dump to create new database.
> Importance: High
>
>
> Hi,
>
> I am trying to use the exiting sql dump (stored in a file
> test.sql) as follows to create the database tables in another
> database. Now, When I run this using command "psql -U uss -d
> enterprisedata -f test.sql" , I get the following error.
>
> NOTE: There is alreay a database exist named "enterprise"
> with same properties. I am just trying to create a mirror
> image in another database under new owner.
>
> What is wrong with this?? Where as when it is created using
> pgAdmin, it doesn't give this kind of error message. I hope
> we should be able to use the .sql file content to create the
> database. (This test.sql file is beiong generated with "File
> -> Save DB Schema" option in the pull down menu. Also pg_dump
> command can be used on the prompt.)
>
> -anil.
>
> *********** Content of test.sql file *****************
>
> CREATE TABLE "FunctionEntityInventory" (
>   "ComponentID" int2 NOT NULL,
>   "EntityType" varchar(25),
>   "EntityName" varchar(25),
>   "RedundencyRequired" char(1),
>   CONSTRAINT "FunctionEntityInventory_pkey" PRIMARY KEY
> ("ComponentID") ); REVOKE ALL ON "FunctionEntityInventory"
> FROM PUBLIC; GRANT ALL ON "FunctionEntityInventory" TO "uss";
> COMMENT ON TABLE "FunctionEntityInventory" IS 'This table is
> the central repository for all the compnents in the network.
> The components here are UCM and USC.';
>
> CREATE TABLE "DataAccessIdentifier" (
>   "DataAccessID" int2 NOT NULL,
>   "PrimaryComponentID" int2 NOT NULL,
>   "SecondaryComponentID" int2 NOT NULL,
>   CONSTRAINT "DataAccessIdentifier_pkey" PRIMARY KEY
> ("DataAccessID"),
>   CONSTRAINT "PrimaryCompID" FOREIGN KEY (PrimaryComponentID)
> REFERENCES "FunctionEntityInventory" (ComponentID) ON DELETE
> CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
>   CONSTRAINT "SecondaryCompID" FOREIGN KEY
> (SecondaryComponentID) REFERENCES "FunctionEntityInventory"
> (ComponentID) ON DELETE CASCADE ON UPDATE CASCADE NOT
> DEFERRABLE INITIALLY IMMEDIATE ); REVOKE ALL ON
> "DataAccessIdentifier" FROM PUBLIC; GRANT ALL ON
> "DataAccessIdentifier" TO "uss"; COMMENT ON TABLE
> "DataAccessIdentifier" IS 'This table defines the data access
> identifier, which is a common interface for primary and
> secondary components. Thus both the components looks at the
> same data.';
>
> *******************************************************
>
> **************** Error message **********************
>
> $ psql -U uss -d enterprisedata -f test.sql
> psql:test.sql:7: NOTICE:  CREATE TABLE/PRIMARY KEY will
> create implicit index 'F unctionEntityInventory_pkey' for
> table 'FunctionEntityInventory' CREATE CHANGE CHANGE COMMENT
>
>   =============== upto this it creates the
> 'FunctionEntityInventory' table in the database ==================
>
> psql:test.sql:19: NOTICE:  CREATE TABLE/PRIMARY KEY will
> create implicit index ' DataAccessIdentifier_pkey' for table
> 'DataAccessIdentifier'
> psql:test.sql:19: NOTICE:  CREATE TABLE will create implicit
> trigger(s) for FOREIGN KEY check(s)
> psql:test.sql:19: ERROR:  columns referenced in foreign key
> constraint not found.
> psql:test.sql:20: ERROR:  Relation 'DataAccessIdentifier'
> does not exist
> psql:test.sql:21: ERROR:  Relation 'DataAccessIdentifier'
> does not exist
> psql:test.sql:22: ERROR:  pg_ownercheck: class
> "DataAccessIdentifier" not found
>
> *******************************************************
>

Re: Resue of the sql dump to create new database.

From

Anil Jangam

Date:

18 December 2001, 07:40:31

Hi Dave,

Yes, its a bug.
It does wrok after addition of the double quotes around the column names in
the FK definitions.
I think it relates to the previous mail reg - "Use of capital letters in the
table name".

-anil.



> -----Original Message-----
> From: Dave Page [mailto:dpage@vale-housing.co.uk]
> Sent: Tuesday, December 18, 2001 2:54 PM
> To: 'Anil Jangam'; pgsql-cygwin@postgresql.org
> Subject: RE: Resue of the sql dump to create new database.
>
>
> I think you've found a bug in pgAdmin which I'll look to fix
> for the next
> release.. Can you try editting the dump file to add double
> quotes arround
> the column names in the fk definition eg.
>
> CREATE TABLE "DataAccessIdentifier" (
>   "DataAccessID" int2 NOT NULL,
>   "PrimaryComponentID" int2 NOT NULL,
>   "SecondaryComponentID" int2 NOT NULL,
>   CONSTRAINT "DataAccessIdentifier_pkey" PRIMARY KEY
> ("DataAccessID"),
>   CONSTRAINT "PrimaryCompID" FOREIGN KEY
> ("PrimaryComponentID") REFERENCES
> "FunctionEntityInventory" ("ComponentID") ON DELETE CASCADE ON UPDATE
> CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
>   CONSTRAINT "SecondaryCompID" FOREIGN KEY ("SecondaryComponentID")
> REFERENCES "FunctionEntityInventory" ("ComponentID") ON
> DELETE CASCADE ON
> UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE );
>
> Does that fix it?
>
> Regards, Dave.
>
> > -----Original Message-----
> > From: Anil Jangam [mailto:anilj@indts.com]
> > Sent: 18 December 2001 09:33
> > To: 'Dave Page'; pgsql-cygwin@postgresql.org
> > Subject: Resue of the sql dump to create new database.
> > Importance: High
> >
> >
> > Hi,
> >
> > I am trying to use the exiting sql dump (stored in a file
> > test.sql) as follows to create the database tables in another
> > database. Now, When I run this using command "psql -U uss -d
> > enterprisedata -f test.sql" , I get the following error.
> >
> > NOTE: There is alreay a database exist named "enterprise"
> > with same properties. I am just trying to create a mirror
> > image in another database under new owner.
> >
> > What is wrong with this?? Where as when it is created using
> > pgAdmin, it doesn't give this kind of error message. I hope
> > we should be able to use the .sql file content to create the
> > database. (This test.sql file is beiong generated with "File
> > -> Save DB Schema" option in the pull down menu. Also pg_dump
> > command can be used on the prompt.)
> >
> > -anil.
> >
> > *********** Content of test.sql file *****************
> >
> > CREATE TABLE "FunctionEntityInventory" (
> >   "ComponentID" int2 NOT NULL,
> >   "EntityType" varchar(25),
> >   "EntityName" varchar(25),
> >   "RedundencyRequired" char(1),
> >   CONSTRAINT "FunctionEntityInventory_pkey" PRIMARY KEY
> > ("ComponentID") ); REVOKE ALL ON "FunctionEntityInventory"
> > FROM PUBLIC; GRANT ALL ON "FunctionEntityInventory" TO "uss";
> > COMMENT ON TABLE "FunctionEntityInventory" IS 'This table is
> > the central repository for all the compnents in the network.
> > The components here are UCM and USC.';
> >
> > CREATE TABLE "DataAccessIdentifier" (
> >   "DataAccessID" int2 NOT NULL,
> >   "PrimaryComponentID" int2 NOT NULL,
> >   "SecondaryComponentID" int2 NOT NULL,
> >   CONSTRAINT "DataAccessIdentifier_pkey" PRIMARY KEY
> > ("DataAccessID"),
> >   CONSTRAINT "PrimaryCompID" FOREIGN KEY (PrimaryComponentID)
> > REFERENCES "FunctionEntityInventory" (ComponentID) ON DELETE
> > CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
> >   CONSTRAINT "SecondaryCompID" FOREIGN KEY
> > (SecondaryComponentID) REFERENCES "FunctionEntityInventory"
> > (ComponentID) ON DELETE CASCADE ON UPDATE CASCADE NOT
> > DEFERRABLE INITIALLY IMMEDIATE ); REVOKE ALL ON
> > "DataAccessIdentifier" FROM PUBLIC; GRANT ALL ON
> > "DataAccessIdentifier" TO "uss"; COMMENT ON TABLE
> > "DataAccessIdentifier" IS 'This table defines the data access
> > identifier, which is a common interface for primary and
> > secondary components. Thus both the components looks at the
> > same data.';
> >
> > *******************************************************
> >
> > **************** Error message **********************
> >
> > $ psql -U uss -d enterprisedata -f test.sql
> > psql:test.sql:7: NOTICE:  CREATE TABLE/PRIMARY KEY will
> > create implicit index 'F unctionEntityInventory_pkey' for
> > table 'FunctionEntityInventory' CREATE CHANGE CHANGE COMMENT
> >
> >   =============== upto this it creates the
> > 'FunctionEntityInventory' table in the database ==================
> >
> > psql:test.sql:19: NOTICE:  CREATE TABLE/PRIMARY KEY will
> > create implicit index ' DataAccessIdentifier_pkey' for table
> > 'DataAccessIdentifier'
> > psql:test.sql:19: NOTICE:  CREATE TABLE will create implicit
> > trigger(s) for FOREIGN KEY check(s)
> > psql:test.sql:19: ERROR:  columns referenced in foreign key
> > constraint not found.
> > psql:test.sql:20: ERROR:  Relation 'DataAccessIdentifier'
> > does not exist
> > psql:test.sql:21: ERROR:  Relation 'DataAccessIdentifier'
> > does not exist
> > psql:test.sql:22: ERROR:  pg_ownercheck: class
> > "DataAccessIdentifier" not found
> >
> > *******************************************************
> >
>

Re: Resue of the sql dump to create new database.

From

Dave Page

Date:

18 December 2001, 07:42:15


> -----Original Message-----
> From: Anil Jangam [mailto:anilj@indts.com]
> Sent: 18 December 2001 09:49
> To: 'Dave Page'; pgsql-cygwin@postgresql.org
> Subject: RE: Resue of the sql dump to create new database.
>
>
> Hi Dave,
>
> Yes, its a bug.
> It does wrok after addition of the double quotes around the
> column names in the FK definitions. I think it relates to the
> previous mail reg - "Use of capital letters in the table name".
>
> -anil.

Hi Anil,

I've fixed that bug - if you want, you can grab the lastest binaries from
http://cvs.social-housing.org, or wait for the next release.

Regards, Dave.

Re: Resue of the sql dump to create new database.

From

Anil Jangam

Date:

18 December 2001, 11:22:33

HI Dave,

I coiped (rather downloaded) the tarball from the link below. I got the
"pgadmin2.exe" from the "./.tar\cvsroot\binaries" directory. The problem
still persist. Do I have to copy some more files?? (There are many .dll
files present), and what is the sequence to copy them??

I even tried to copy all the .dll file(s) accroding to their location on the
original installation, but it gave me an runtime error, "application-defined
or object-defined error". Now I have reverted back to the old setup.. i.e.
1.1.66-Dev.

ANyways this is not a very urgent need though.

regds,
-anil.

> -----Original Message-----
> From: Dave Page [mailto:dpage@vale-housing.co.uk]
> Sent: Tuesday, December 18, 2001 3:14 PM
> To: 'Anil Jangam'; pgsql-cygwin@postgresql.org
> Subject: RE: Resue of the sql dump to create new database.
>
>
>
>
> > -----Original Message-----
> > From: Anil Jangam [mailto:anilj@indts.com]
> > Sent: 18 December 2001 09:49
> > To: 'Dave Page'; pgsql-cygwin@postgresql.org
> > Subject: RE: Resue of the sql dump to create new database.
> >
> >
> > Hi Dave,
> >
> > Yes, its a bug.
> > It does wrok after addition of the double quotes around the
> > column names in the FK definitions. I think it relates to the
> > previous mail reg - "Use of capital letters in the table name".
> >
> > -anil.
>
> Hi Anil,
>
> I've fixed that bug - if you want, you can grab the lastest
> binaries from
> http://cvs.social-housing.org, or wait for the next release.
>
> Regards, Dave.
>

Re: Resue of the sql dump to create new database.

From

"Dave Page"

Date:

18 December 2001, 11:32:43

Anil Jangam allegedly said:
> HI Dave,
>
> I coiped (rather downloaded) the tarball from the link below. I got the
> "pgadmin2.exe" from the "./.tar\cvsroot\binaries" directory. The
> problem still persist. Do I have to copy some more files?? (There are
> many .dll files present), and what is the sequence to copy them??
>
> I even tried to copy all the .dll file(s) accroding to their location
> on the original installation, but it gave me an runtime error,
> "application-defined or object-defined error". Now I have reverted back
> to the old setup.. i.e. 1.1.66-Dev.
>
> ANyways this is not a very urgent need though.

The bug was actually in pgSchema.dll which would need registering on your
system, along with all the other dlls. I have actually produced the new
release now anyway, so you would probably be better off just downloading it
from http://pgadmin.postgresql.org/

Regards, Dave.