Thread: Resue of the sql dump to create new database.
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 *******************************************************
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 > > ******************************************************* >
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 > > > > ******************************************************* > > >
> -----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.
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. >
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.