Обсуждение: 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.
Hi, we generate sql query at php. And interval not overlaps by php code:
--
```
<?php
$connections = [];
for($i = 0; $i < 72; $i++){
$connections[] = pg_connect("***", PGSQL_CONNECT_FORCE_NEW);
pg_query("SET application_name = 'DB.Upgrade #{$i}'");
}
$chunk = 10000;
do{
foreach($connections as $id => $connection){
if(!pg_connection_busy($connection)) {
if($result = pg_get_result($connection)){
pg_free_result($result);
}
pg_send_query($connection, strtr('
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
', [
':min' => $chunk,
':max' => $chunk+9999,
]));
$chunk += 10000;
}
}
usleep(100000);
}
while(true);
```
We assume that block throw when 2 phone at 2 different query has same mailng_id. That possible only if selection create some locks.
We assume that block throw when 2 phone at 2 different query has same mailng_id. That possible only if selection create some locks.
2015-09-24 19:43 GMT+03:00 Francisco Olarte <folarte@peoplecall.com>:
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
¿ Are you sure you're not getting fencepost overlaps when substituting
:min/:max ? ( between does closed intervals, normally half closed (
phone_id >= 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.