BUG #8757: Dublicate rows, broken primary key. - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8757
Logged by:          Dmitry Sarafannikov
Email address:      dimon99901@mail.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?