Identifying duplicate rowids

Answer

Each record in a table is automatically assigned a unique rowid that is stored with the record on disk. The rowid has the following characteristics:

• Is a bigint (8 bytes) data type
• Can be used to query/update rows in a table; for example, "where rowid = "
• Is a constant -- it does not change even if you update the row
• Is unique across all rows, tables, and databases on a particular NPS server

Scenario 1 

If you use an external table to unload a table and specify "format 'internal' compress true", NPS includes the rowids in the output. If you later reload that external table, the original rowids are preserved and brought back in. If you happen to reload that external table -- appending the data to the original table that was unloaded -- not only would you have duplicate rowids, but you would have them within the SAME table.

Scenario 2

You may have encountered a transaction manager bug in which NPS did not fully commit/rollback a particular transaction.

Analysis

If you suspect a table of having duplicate rowids, use the SQL below to identify any issues. The output will also include the particular transaction IDs (createxid) associated with those records, which may help you track down the cause of the problem. By having the transaction ID, you can use the logs and transaction journals to track down who tried to update the table, when it occurred, how it happened, and whether or not it was successful.

\\set TABLE_TO_BE_PROCESSED your_tablename_goes_here

begin;

**Find out if there are any duplicate ROWIDs in this table

select count(*) as "Number of DISTINCT rowids",
sum("rows per rowid") as "Total number of rows"
from (select count(*) as "rows per rowid", rowid from :TABLE_TO_BE_PROCESSED group by rowid) as the_subquery;

**Create a table of all of the duplicate ROWIDs, listing each rowid only once
**drop table problem_rowids;

create table problem_rowids as
select rowid as "the_rowid" from :TABLE_TO_BE_PROCESSED group by rowid having count(rowid) > 1;

**Exactly how many records are affected? Some may have been duplicated multiple times.

select count(*) as "# Of rowids w/duplicates" from problem_rowids;

**Create a table of the transactions that are involved in this duplication
*
**drop table problem_createxids;

create table problem_createxids as
select distinct(createxid) as "the_createxid" from :TABLE_TO_BE_PROCESSED where rowid in (select the_rowid from problem_rowids);

**For each of those transactions, how many rows are associated with the transaction?
**Group this by the datasliceid -- to see if, perhaps, only some dataslices were involved.

select createxid as "problematic createxid's",
datasliceid as "grouped by datasliceid",
count(*) as "row counts"
from :TABLE_TO_BE_PROCESSED
where createxid in (select the_createxid from problem_createxids)
group by 1, 2
order by 1, 2;

**Dump up to a million of those duplicated rows with additional details.

select rowid, createxid, datasliceid, * from :TABLE_TO_BE_PROCESSED
where rowid in (select the_rowid from problem_rowids)
order by 1, 2, 3
limit 1000000;


commit;