Problems with migration - Mailing list pgsql-admin
From | Tobias Schmalfuss |
---|---|
Subject | Problems with migration |
Date | |
Msg-id | ONEIJFHLDDLLOLKCKBIEOEEFCAAA.tobiasschmalfuss@web.de Whole thread Raw |
Responses |
Re: Problems with migration
|
List | pgsql-admin |
Hi i have a little database with the following tables: CREATE SEQUENCE "CIF_ID" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; CREATE TABLE CUSTOMER ( CIF_ID int8 DEFAULT nextval('"CIF_ID"') NOT NULL PRIMARY KEY, SUBSCRIBER_NUMBER VARCHAR(20) NULL, MAGAZINE_NAME VARCHAR(40) NULL, FAXNUMBER VARCHAR(15) NULL, FAX_AREACODE VARCHAR(9) NULL, PHONE_NUMBER_BUSSINES VARCHAR(15) NULL, PHONE_AREACODE_BUSSINES VARCHAR(9) NULL, PHONE_NUMBER_PRIVAT VARCHAR(15) NULL, PHONE_AREACODE_PRIVAT VARCHAR(9) NULL, COUNTRY VARCHAR(2) NULL, CITY VARCHAR(28) NULL, ZIP_CODE VARCHAR(10) NULL, ATTACHMENT VARCHAR(27) NULL, HOUSE_NUMBER VARCHAR(8) NULL, STREET VARCHAR(40) NULL, DATE_OF_BIRTH DATE NULL, NAME_2 VARCHAR(40) NULL, NAME_1 VARCHAR(40) NULL, SALUTATION VARCHAR(40) NULL, ORDERNUMBER INTEGER NULL, CUSTOMER_ID VARCHAR(6) NULL, MASTER_PASSWD VARCHAR(48) NOT NULL, MASTERNAME VARCHAR(40) NOT NULL, SURNAME VARCHAR(40) NULL, EXISTING_EMAIL VARCHAR(40) NULL, UPDATE_FLAG NUMERIC(1) NULL, STATUS2 NUMERIC(1,0) NULL ); /*CREATE SEQUENCE "PRODUCT_ID" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;*/ CREATE TABLE PRODUCTS ( LIABLE_FOR_COST NUMERIC(1) NULL, DESCRIPTION VARCHAR(200) NULL, PRODUCT_ID int4 DEFAULT nextval('"PRODUCT_ID"') NOT NULL PRIMARY KEY, PRODUCT_NAME VARCHAR(40) NULL, EMAIL_HTML VARCHAR(4000) NULL, EMAIL_TEXT VARCHAR(4000) NULL, ERASEABLE NUMERIC(1) NULL, MAXIMUM NUMERIC(3) NULL, MINIMUM NUMERIC(3) ); CREATE TABLE ACCOUNT_CONFIG ( TEL_NUMBER2 VARCHAR(20) NULL, TEL_NUMBER1 VARCHAR(20) NULL, SMTP VARCHAR(20) NULL, POP3 VARCHAR(20) NULL ); CREATE TABLE ACCT00 ( PASSWORD VARCHAR(48) NULL, EMAIL_ADRESS VARCHAR(80) NULL, ACCT_ID NUMERIC(12,0) NOT NULL, MAILDROP NUMERIC(3,0) ); CREATE TABLE ACCT99 ( PASSWORD VARCHAR(48) NULL, USERNAME VARCHAR(40) NULL, ACCT_ID NUMERIC(12,0) NOT NULL ); CREATE TABLE BANK_COLLECTION ( BANK_NAME VARCHAR(27) NULL, BANK_CODE VARCHAR(8) NOT NULL PRIMARY KEY, LONGNAME VARCHAR(58) NULL ); CREATE TABLE BANK_DETAILS ( CIF_ID NUMERIC(8) NOT NULL , LAST_NAME VARCHAR(40) NULL, FIRST_NAME VARCHAR(40) NULL, BANK_ACCOUNT_NUMBER VARCHAR(11) NULL, BANK_CODE VARCHAR(8) NOT NULL ); CREATE TABLE BL_BANK_ACCOUNT_NUMBER ( BANK_ACCOUNT_TOKEN VARCHAR(11) NULL, BANK_ACCOUNT VARCHAR(11) NULL ); CREATE TABLE BL_BANK_CODE ( BANK_CODE_TOKEN VARCHAR(8) NULL, BANK_CODE VARCHAR(8) NULL ); CREATE TABLE BL_CITY ( CITY_TOKEN VARCHAR(28) NULL, CITY VARCHAR(28) NULL ); CREATE TABLE BL_STREET ( STREET_TOKEN VARCHAR(40) NULL, STREET VARCHAR(40) NULL ); CREATE TABLE BL_ZIPCODE ( ZIPCODE_TOKEN VARCHAR(10) NULL, ZIPCODE VARCHAR(10) NULL ); CREATE TABLE "credit_card" ( "cif_id" numeric(8, 0) NOT NULL, "cvv" numeric(4, 0), "credit_card_number" varchar(16), "owner" varchar(40), "valid_to" date, "kredit_institut" varchar(40), CONSTRAINT "cif_id" FOREIGN KEY ("cif_id") REFERENCES "customer" ("cif_id") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE ); CREATE SEQUENCE "ACCT_ID" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; CREATE TABLE CUSTOMER_PRODUCT ( ACCT_ID NUMERIC(12,0) NOT NULL PRIMARY KEY, CIF_ID NUMERIC(8) NOT NULL, PRODUCT_ID NUMERIC(4) NOT NULL ); CREATE TABLE ORDER_HISTORY ( TEXT VARCHAR(4000) NULL, TIMESTAMP TIMESTAMP NULL, CIF_ID NUMERIC(8) NOT NULL ); CREATE TABLE "doublettest_account" ( "username" varchar(40) NOT NULL, "timestamp" timestamp, CONSTRAINT "doublettest_username_pkey" PRIMARY KEY ("username") ); CREATE TABLE "doublettest_email" ( "email_adress" varchar(80) NOT NULL, "timestamp" timestamp, CONSTRAINT "doublettest_email_adress_pkey" PRIMARY KEY ("email_adress") ); CREATE TABLE "doublettest_mastername" ( "mastername" varchar(40) NOT NULL, "timestamp" timestamp, CONSTRAINT "doublettest_mastername_pkey" PRIMARY KEY ("mastername") ); ALTER TABLE "customer_product" ADD CONSTRAINT "product_id" FOREIGN KEY ("product_id") REFERENCES products("product_id") MATCH FULL; ALTER TABLE "customer_product" ADD CONSTRAINT "cif_id" FOREIGN KEY ("cif_id") REFERENCES customer("cif_id") MATCH FULL; ALTER TABLE "acct00" ADD CONSTRAINT "acct_id" FOREIGN KEY ("acct_id") REFERENCES customer_product("acct_id") MATCH FULL; ALTER TABLE "acct99" ADD CONSTRAINT "acct_id" FOREIGN KEY ("acct_id") REFERENCES customer_product("acct_id") MATCH FULL; ALTER TABLE "order_history" ADD CONSTRAINT "cif_id" FOREIGN KEY ("cif_id") REFERENCES customer("cif_id") MATCH FULL; ALTER TABLE "credit_card" ADD CONSTRAINT "cif_id" FOREIGN KEY ("cif_id") REFERENCES customer("cif_id") MATCH FULL; ALTER TABLE "bank_details" ADD CONSTRAINT "cif_id" FOREIGN KEY ("cif_id") REFERENCES customer("cif_id") MATCH FULL; ALTER TABLE "bank_details" ADD CONSTRAINT "bank_code" FOREIGN KEY ("bank_code") REFERENCES bank_collection("bank_code") MATCH FULL; now i try to fill this tables with java from oracle tables. i think 70000 collums are in the table customer. the problem is that at the beginning it is so fast by the insterts in the tables. but after a wile it is slower and slower. somtime he need some minutes for one record. when i delete the foreign keys it is verry fast. but i need the keys for consistent datas. what can i do? tobias
pgsql-admin by date: