Re: BUG #6393: cluster sometime fail under heavy concurrent write load

Hi.

The test case:

The first stage - preparation:

CREATE TABLE test (id serial primary key, flag integer, value text);
cluster "test_pkey" on test;
insert into test select nextval('test_id_seq') as id, 0 as flag,(SELECT
array(select random() from generate_series(1,1000)))::text as value from
generate_series(1,1000) as gs(id);
cluster test;

The second stage - test:

Console one:
postgres(at)db10:~$ while true; do psql -d test_db -c "begin;update test set
flag=(random()*1000)::integer where id IN (select (random()*1000)::integer
from generate_series(1,30)); select pg_sleep(1); commit;"; done
COMMIT
COMMIT
COMMIT
COMMIT
COMMIT
COMMIT
COMMIT
COMMIT
COMMIT
COMMIT

Console two:
postgres(at)db10:~$ while true; do psql -d test_db -c "cluster test;"; done
CLUSTER
ERROR: duplicate key value violates unique constraint
"pg_toast_119812558_index"
DETAIL: Key (chunk_id, chunk_seq)=(119811577, 0) already exists.
ERROR: duplicate key value violates unique constraint
"pg_toast_119812564_index"
DETAIL: Key (chunk_id, chunk_seq)=(119811601, 0) already exists.
ERROR: duplicate key value violates unique constraint
"pg_toast_119812570_index"
DETAIL: Key (chunk_id, chunk_seq)=(119811494, 0) already exists.
ERROR: duplicate key value violates unique constraint
"pg_toast_119812576_index"
DETAIL: Key (chunk_id, chunk_seq)=(119811552, 0) already exists.
ERROR: duplicate key value violates unique constraint
"pg_toast_119812582_index"
DETAIL: Key (chunk_id, chunk_seq)=(119811525, 0) already exists.

PS: no such effects happens on 9.0.4
I think it can be related to TOAST related changes in 9.0.6

On Thu, Jan 12, 2012 at 2:24 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com>wrote:

>
> Excerpts from maxim.boguk's message of mar ene 10 23:00:59 -0300 2012:
> > The following bug has been logged on the website:
> >
> > Bug reference: 6393
> > Logged by: Maxim Boguk
> > Email address: maxim(dot)boguk(at)gmail(dot)com
> > PostgreSQL version: 9.0.6
> > Operating system: Linux Ubuntu
> > Description:
> >
> > I have heavy write-load table under PostgreSQL 9.0.6 and sometime (not
> > always but more then 50% chance) i'm getting the next error during
> cluster:
> >
> > db=# cluster public.enqueued_mail;
> > ERROR: duplicate key value violates unique constraint
> > "pg_toast_119685646_index"
> > DETAIL: Key (chunk_id, chunk_seq)=(119685590, 0) already exists.
> >
> > chunk_id different each time.
> >
> > No uncommon datatypes exists in the table.
> >
> > Currently I work on create reproducible test case (but it seems require
> 2-3
> > open write transaction on the table).
>
> I don't see how can this be done at all, given that cluster grabs an
> exclusive lock on the table in question. An better example illustrating
> what you're really doing would be useful.
>
> --
> Álvaro Herrera <alvherre(at)commandprompt(dot)com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

--
Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
"If they can send one man to the moon... why can't they send them all?"

МойКруг: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."