From: | dimon99901(at)mail(dot)ru |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #8757: Dublicate rows, broken primary key. |
Date: | 2014-01-09 09:55:25 |
Message-ID: | E1W1CKj-0000cI-KF@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 8757
Logged by: Dmitry Sarafannikov
Email address: dimon99901(at)mail(dot)ru
PostgreSQL version: 9.3.2
Operating system: Debian 7.3
Description:
Hi. We have newly migrated from 9.1.10 to 9.3.2 version with pg_upgrade and
find where strange behaviour.
We have table with 70 rows:
=# \d blog.blogs
Table "blog.blogs"
Column | Type |
Modifiers
-------------------+-----------------------------+--------------------------------------------------------------
id_blog | bigint | not null default
nextval('blog.blogs_id_blog_seq'::regclass)
blog_url | character varying(50) | not null
......
......
Indexes:
"pk_blog_blogs" PRIMARY KEY, btree (id_blog)
Referenced by:
TABLE "blog.blog_contest" CONSTRAINT "fk_blog_blog_contest_blog" FOREIGN
KEY (id_blog) REFERENCES blog.blogs(id_blog)
TABLE "blog.post_votes" CONSTRAINT "fk_blog_post_votes_blog" FOREIGN KEY
(id_blog) REFERENCES blog.blogs(id_blog)
TABLE "blog.post_visits" CONSTRAINT "fk_post_last_visits_blog" FOREIGN
KEY (id_blog) REFERENCES blog.blogs(id_blog)
Strange behaviour observed with row id_blog = 26, blog_url = 'orders'.
We have no deletes or insertes, but have intensive updates on this table.
And we have intensive inserts in tables blog.post_votes and
blog.post_visits.
In the random time. We get this error:
ERROR: insert or update on table "post_visits" violates foreign key
constraint "fk_post_last_visits_blog"
Detail: Key (id_blog)=(26) is not present in table "blogs".
Context: SQL statement "insert into blog.post_visits (...)
and this:
ERROR: insert or update on table "post_votes" violates foreign key
constraint "fk_blog_post_votes_blog"
Detail: Key (id_blog)=(26) is not present in table "blogs".
Context: SQL statement "insert into blog.post_votes (...)
And we look on the table blog.blogs;
select * from blog.blogs where id_blog = 26;
no rows.
select * from blog.blogs where blog_url = 'orders';
We have 2 same rows (but sometimes 3 rows) with id_blog = 26!!!
explain analyze select * from blog.blogs where id_blog = 26;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using pk_blog_blogs on blogs (cost=0.14..8.17 rows=1 width=781)
(actual time=0.028..0.042 rows=1 loops=1)
Index Cond: (id_blog = 26)
Total runtime: 0.093 ms
So, primary key have dublicate rows and is broken.
Then we drop 3 references from tables blog.blog_contest, blog.post_votes,
blog.post_visits.
delete from blog.blogs where blog_url = 'orders';
insert into blog.blogs (id_blog, blog_url, ...) values (26, 'orders', ...);
Create foreign keys and this ok.
Through the several hours this situation repeated. Then repeated again, and
again.
And then we just drop the 2 of 3 referenses to this tables (in table
blog.blog_contes we have no rows, updates, inserts with id_blog = 26). We
drop references from tables blog.post_votes and blog.post_visits. And this
situation don't repeated along more then 24 hours.
This is bug?
From | Date | Subject | |
---|---|---|---|
Next Message | rabigul | 2014-01-09 12:01:57 | BUG #8760: Large Objects |
Previous Message | Peter Balzer | 2014-01-09 09:49:17 | ODBC Postgresql Driver for Windows 2008 Server 64 |