Thread: BUG #13635: Interlocks at selection with array_agg

The following bug has been logged on the website:

Bug reference:      13635
Logged by:          pawel
Email address:      pawel.samysev@gmail.com
PostgreSQL version: 9.4.4
Operating system:   Debian 7
Description:

My company develops mailing server. We introduce new server and copy
database to it. It has 24 processor server. Every tables has millions
records. After copy we start update tables.We run 72 queries at parallel at
different part of table. And we periodical see locks with drammatical fail
productivity. But query shouldn`t create any interlocks.

Query:

```
    UPDATE phone p
    SET mailing_id = (
        SELECT array_agg(mailing_id)
        FROM message m
        WHERE m.phone_id = p.phone_id
    )
    WHERE
        p.phone_id BETWEEN :min AND :max
```

Table schemas:

```

-- Table: phone

-- DROP TABLE phone;

CREATE TABLE phone
(
  phone_id bigserial NOT NULL, -- Primary key
  "number" character varying(15), -- Number
  date_modified timestamp with time zone DEFAULT now(),
  region_id integer, -- ID region of number
  last_visit interval,
  mailing_id integer[],
  client_db_id integer[],
  CONSTRAINT phone_pkey PRIMARY KEY (phone_id),
  CONSTRAINT phone_number_key UNIQUE (number)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE phone
  OWNER TO "postgres";
GRANT ALL ON TABLE phone TO "postgres";


-- Index: phone_client_db_id_idx

-- DROP INDEX phone_client_db_id_idx;

CREATE INDEX phone_client_db_id_idx
  ON phone
  USING gin
  (client_db_id);


-- Index: phone_mailing_id_idx

-- DROP INDEX phone_mailing_id_idx;

CREATE INDEX phone_mailing_id_idx
  ON phone
  USING gin
  (mailing_id);

-- Index: phone_number_idx

-- DROP INDEX phone_number_idx;

CREATE INDEX phone_number_idx
  ON phone
  USING btree
  (number COLLATE pg_catalog."default");

-- Index: phone_region_id_idx

-- DROP INDEX phone_region_id_idx;

CREATE INDEX phone_region_id_idx
  ON phone
  USING btree
  (region_id);



-- Table: message

-- DROP TABLE message;

CREATE TABLE message
(
  message_id integer NOT NULL DEFAULT
nextval('message_message_id_seq'::regclass), -- Primary key
  mailing_id integer,
  phone_id integer,
  message_status integer,
  date_added timestamp without time zone,
  date_modified timestamp without time zone,
  CONSTRAINT message_pkey PRIMARY KEY (message_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE message
  OWNER TO "postgres";


-- Index: message_date_modified_idx

-- DROP INDEX message_date_modified_idx;

CREATE INDEX message_date_modified_idx
  ON message
  USING btree
  (date_modified);

-- Index: message_mailing_id_idx

-- DROP INDEX message_mailing_id_idx;

CREATE INDEX message_mailing_id_idx
  ON message
  USING btree
  (mailing_id);

-- Index: message_message_id_idx

-- DROP INDEX message_message_id_idx;

CREATE INDEX message_message_id_idx
  ON message
  USING btree
  (message_id);

-- Index: message_message_status_idx

-- DROP INDEX message_message_status_idx;

CREATE INDEX message_message_status_idx
  ON message
  USING btree
  (message_status);

-- Index: message_phone_id_idx

-- DROP INDEX message_phone_id_idx;

CREATE INDEX message_phone_id_idx
  ON message
  USING btree
  (phone_id);

```

Hi pawel:

On Thu, Sep 24, 2015 at 11:56 AM,  <pawel.samysev@gmail.com> wrote:

Not seeing the whole query I cannot say too much but:

>     WHERE
>         p.phone_id BETWEEN :min AND :max

=C2=BF Are you sure you're not getting fencepost overlaps when substituting
:min/:max ? ( between does closed intervals, normally half closed (
phone_id >=3D min and phone_id<max ) are used for this kinds of queries
( I ask because it seems a query doing b-c could lock out other doing
a-b for a long time ).

Francisco Olarte.