Re: BUG #6393: cluster sometime fail under heavy concurrent write load - Mailing list pgsql-bugs

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@db10:~$ while true; do psql -d test_db -c "begin;update test set
flag=3D(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@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)=3D(119811577, 0) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_toast_119812564_index"
DETAIL:  Key (chunk_id, chunk_seq)=3D(119811601, 0) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_toast_119812570_index"
DETAIL:  Key (chunk_id, chunk_seq)=3D(119811494, 0) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_toast_119812576_index"
DETAIL:  Key (chunk_id, chunk_seq)=3D(119811552, 0) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_toast_119812582_index"
DETAIL:  Key (chunk_id, chunk_seq)=3D(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@commandprompt.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.boguk@gmail.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=3D# cluster public.enqueued_mail;
> > ERROR:  duplicate key value violates unique constraint
> > "pg_toast_119685646_index"
> > DETAIL:  Key (chunk_id, chunk_seq)=3D(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.
>
> --
> =C3=81lvaro Herrera <alvherre@commandprompt.com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>



--=20
Maxim Boguk
Senior Postgresql DBA.

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

Skype: maxim.boguk
Jabber: maxim.boguk@gmail.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?"

=D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: 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."