I have defined a function into public schema which permits to execute a set of SQL statements on every schema:
Then I have executed this statement:
SELECT * FROM public.multiddl($$
CREATE TYPE enum_report_type AS ENUM ('A', 'B');
CREATE TABLE "report_layout" (
"id" serial,
"report_type" enum_report_type NOT NULL,
"layout_name" varchar(255) NOT NULL,
"report_config" jsonb,
"created_by" integer,
"id_cliente" integer,
"builder" varchar(255),
"can_modify" bool,
"can_delete" bool,
"is_default" bool,
"created_on" timestamp NULL,
"modified_on" timestamp NULL,
"modified_by" integer,
CONSTRAINT "fk_clienti_report_layout" FOREIGN KEY ("id_cliente") REFERENCES "public"."customer" ("id"),
CONSTRAINT "fk_utenti_report_layout_create" FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
CONSTRAINT "fk_utenti_report_layout_modify" FOREIGN KEY ("modified_by") REFERENCES "user" ("id")
)
WITH (OIDS=FALSE);
ALTER TABLE report ADD COLUMN id_layout integer;
$$);
All locks derived from this statement seem to be related to public views, that are commodity views which ties together all schemata. Example of view:
CREATE OR REPLACE VIEW "public"."v_contacts" AS
SELECT 'public'::text AS schema,
[FIELDS]
UNION
SELECT 'customer2'::text AS schema,
[FIELDS]
FROM ((((customer c
JOIN customer2.table1 g ON ...
JOIN customer2.table2 s ON ...
JOIN customer2.reparti r ON ...
JOIN customer2.contatto co ON ...
I cannot understand why every query which uses union view like the before mentioned is stuck.
Thanks for any advice.
--
Christian Castelli
skype: christrack