Hi,
I have defined a function into public schema which permits to execute a set
of SQL statements on every schema:
CREATE OR REPLACE FUNCTION "public"."multiddl"("sql" text)
RETURNS "pg_catalog"."bool" AS $BODY$DECLARE
r record;
BEGIN
FOR r IN
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT LIKE 'pg_%' AND
schema_name NOT IN('information_schema')
LOOP
EXECUTE 'SET search_path TO ' || r.schema_name || ', public';
RAISE NOTICE 'Executing for %', r.schema_name;
EXECUTE sql;
END LOOP;
RETURN 't';
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100
;
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 Castelliskype: christrack*
