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=(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)=(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
<[email protected]>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: [email protected]
> > 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 <[email protected]>
> 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: [email protected]
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."
