BUG #8757: Dublicate rows, broken primary key.

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?

Responses

Browse pgsql-bugs by date

  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