Postgres Pro
Downloads
Home   >   mailing lists

Locks when launching function across schemata - Mailing list pgsql-performance

From Christian Castelli
Subject Locks when launching function across schemata
Date
Msg-id CAN7CK_xDeLa1Da7oyJihQYOnRv3oKouVTgMsg0KXnCx1MQCJKg@mail.gmail.com
Whole thread Raw
List pgsql-performance
Tree view
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

pgsql-performance by date:

Previous
From: John Gorman
Date:
Subject: Re: Database transaction with intermittent slow responses
Next
From: Justin Pryzby
Date:
Subject: index fragmentation on insert-only table with non-unique column