Hi all, A pgAdmin user has noticed that Foreign Keys take significantly longer to create when migrating a database in pgAdmin in v1.4.12 than in v1.4.2.
The only difference in the migration code between these releases, is that pgAdmin now qualifies tables names with the schema name. The following log snippets are from identical migrations from an Access database to PostgreSQL 7.3.1: pgAdmin 1.4.12 ============== Jan 19 16:34:26 lmb042 postgres[17986]: [78991-1] LOG: query: ALTER TABLE public.articles ADD CONSTRAINT productsarticles_fk FOREIGN KEY(product_ref) REFERENCES public.products Jan 19 16:34:26 lmb042 postgres[17986]: [78991-2] (product_id) ON DELETE CASCADE ON UPDATE CASCADE Jan 19 16:34:26 lmb042 postgres[17986]: [78992] NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) Jan 19 16:34:26 lmb042 postgres[17986]: [78993] LOG: query: SELECT 1 FROM ONLY "public"."products" x WHERE "product_id" = $1 FOR UPDATE OF x Jan 19 16:38:33 lmb042 postgres[17986]: [78994] LOG: duration: 247.585771 sec pgAdmin 1.4.2 ============= Jan 19 15:48:56 lmb042 postgres[17542]: [78991-1] LOG: query: ALTER TABLE articles ADD CONSTRAINT productsarticles_fk FOREIGN KEY(product_ref) REFERENCES products (product_id) Jan 19 15:48:56 lmb042 postgres[17542]: [78991-2] ON DELETE CASCADE ON UPDATE CASCADE Jan 19 15:48:56 lmb042 postgres[17542]: [78992] NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) Jan 19 15:48:56 lmb042 postgres[17542]: [78993] LOG: query: SELECT 1 FROM ONLY "public"."products" x WHERE "product_id" = $1 FOR UPDATE OF x Jan 19 15:48:58 lmb042 postgres[17542]: [78994] LOG: duration: 1.988144 sec A similar select query follows every key creation, but is not issued by pgAdmin, or the user's application(s), so I assume PostgreSQL is doing it for some reason. Any ideas why it takes so long (for the same data) when pgAdmin qualifies the table name in the ALTER statement? Thanks, Dave. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
