How can a table violate its own primary key index?

Question

I have a PostgreSQL database that has a table with a primary key applied to three columns. According to the database, there is a key on a key:

Indexes: "full_log_pkey" PRIMARY KEY, btree (server_name, line_number, log_generation) 

However, some simple tests show that I have duplicate keys:

 select count(*) from full_log; count ---------- 60644405 select count(*) from (select distinct server_name, line_number, log_generation from full_log) as foo; count ---------- 60636564 

It is clear that on lines with fewer lines (based on the primary key) there are fewer lines. My question is: how is this possible?

Edit: The full definition of the table is as follows:

  Table "public.full_log" Column | Type | Modifiers ----------------+-----------------------------+----------- activity | character(1) | archivaldate | timestamp without time zone | media_type | character varying(5) | vsn | text | archive_set | character varying(20) | copy | smallint | file_start | integer | file_offset | integer | fs_name | character varying(20) | inode | double precision | file_length | bigint | file_type | character(1) | overflow | integer | device_number | integer | server_name | text | not null path | text | line_number | integer | not null log_generation | integer | not null Indexes: "full_log_pkey" PRIMARY KEY, btree (server_name, line_number, log_generation) Foreign-key constraints: "full_log_server_name_fkey" FOREIGN KEY (server_name) REFERENCES servers(server_name) Rules: insert_update_full_log AS ON INSERT TO full_log WHERE (EXISTS ( SELECT full_log.activity, full_log.archivaldate, full_log.media_type, full_log.vsn, full_log.archive_set, full_log.copy, full_log.file_start, full_log.file_offset, full_log.fs_name, full_log.inode, full_log.file_length, full_log.file_type, full_log.overflow, full_log.device_number, full_log.server_name, full_log.path, full_log.line_number, full_log.log_generation FROM full_log WHERE full_log.server_name = new.server_name AND full_log.line_number = new.line_number AND full_log.log_generation = new.log_generation)) DO INSTEAD UPDATE full_log SET activity = new.activity, archivaldate = new.archivaldate, media_type = new.media_type, vsn = new.vsn, archive_set = new.archive_set, copy = new.copy, file_start = new.file_start, file_offset = new.file_offset, fs_name = new.fs_name, inode = new.inode, file_length = new.file_length, file_type = new.file_type, overflow = new.overflow, device_number = new.device_number, path = new.path WHERE full_log.server_name = new.server_name AND full_log.line_number = new.line_number AND full_log.log_generation = new.log_generation 

Example of duplicate lines:

  select * from full_log where line_number = 6332986; activity | archivaldate | media_type | vsn | archive_set | copy | file_start | file_offset | fs_name | inode | file_length | file_type | overflow | device_number | server_name | path | line_number | log_generation ----------+---------------------+------------+--------+-------------+------+------------+-------------+---------+------------+-------------+-----------+----------+---------------+-------------+-------------------------------------------------------------------------------------------+-------------+---------------- A | 2010-10-13 10:49:49 | ti | Z00711 | lcbp_rel | 1 | 226237 | 779099 | lcbp | 21798068.3 | 31198108 | f | 0 | 8511 | redact | wdl/delivery/irishparis_2010_09/MSE2_Histoire des rois d'Angleterre/MSE2_239.TIF | 6332986 | 1 A | 2010-10-13 10:49:49 | ti | Z00711 | lcbp_rel | 1 | 226237 | 779099 | lcbp | 21798068.3 | 31198108 | f | 0 | 8511 | redact | wdl/delivery/irishparis_2010_09/MSE2_Histoire des rois d'Angleterre/MSE2_239.TIF | 6332986 | 1 (2 rows) 

Answer 1

What returns this query?

 select server_name, line_number, log_generation from full_log group by server_name, line_number, log_generation having count(*) > 1 

It can help compare this to

 select line_number, log_generation from full_log group by line_number, log_generation having count(*) > 1 

but it may not be. I think this item

 WHERE (EXISTS ( SELECT full_log.activity, full_log.archivaldate, full_log.media_type, full_log.vsn, full_log.archive_set, full_log.copy, full_log.file_start, full_log.file_offset, full_log.fs_name, full_log.inode, full_log.file_length, full_log.file_type, full_log.overflow, full_log.device_number, full_log.server_name, full_log.path, full_log.line_number, full_log.log_generation FROM full_log WHERE full_log.server_name = new.server_name AND full_log.line_number = new.line_number AND full_log.log_generation = new.log_generation)) 

may be simplified in this section. (Although I do not think this contributes to this problem.)

 WHERE (EXISTS ( SELECT full_log.server_name, full_log.line_number, full_log.log_generation FROM full_log WHERE full_log.server_name = new.server_name AND full_log.line_number = new.line_number AND full_log.log_generation = new.log_generation)) 

You said that PostgreSQL reduces and recreates the index when you change the data type of a column without a key. I do not see this happening here, and I'm not sure I have ever seen this. Perhaps I did not notice whether a change occurred, and I am not changing the data type of the column. (Now that I said this, I couldn't tell you the last time I did this.) Now I have PostgreSQL 9.0.2.

Answer 2

Do you use table inheritance? If so, the PC does not apply to children (at least not in 8.2).

Answer 3

I would suspect NULL values. perhaps a request to see if there is.

Answer 4

I saw how this happens when the ctid of the target line changes as a result before the trigger. I can’t remember the exact steps, but basically this is a problem.

If you have triggers, my answer on this related topic may be that you are using:

What are the right conditions for PostgreSQL?

Conclusion: if you issue insert / update / delete instructions, postgres does not return the correct number of affected lines, you may run into oddities. A rule (or use after launch instead) can help get rid of them.