Thread: Locks when launching function across schemata

Locks when launching function across schemata

From

Christian Castelli

Date:

18 May 2016, 14:10:43

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 Castelli
skype: christrack