Обсуждение: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
sergey.frolov@smetarik.ru
Дата:
The following bug has been logged on the website: Bug reference: 14940 Logged by: sergey frolov Email address: sergey.frolov@smetarik.ru PostgreSQL version: 9.6.6 Operating system: Windows 10, 64 Description: Hi, I have noticed duplicated records inspite of primary key and unique constraint. select version () PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit The DDL is CREATE TABLE nb.nb_basedtl ( id integer NOT NULL, base_id integer NOT NULL, norm_id integer NOT NULL, ... CONSTRAINT pk_nb_basedtl PRIMARY KEY (id), CONSTRAINT unq_nb_basedtl UNIQUE (norm_id, base_id), .... The problem is select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id = 11658502 ;-- expected ONE row (0,49);364507;0;11658502;269;46203 (0,49);370881;0;11658502;269;46203 (0,49);370882;0;11658502;269;46203 select (select count(1) from nb.nb_basedtl), (select count(1) from (select id, count(1) from nb.nb_basedtl group by id having count(1) > 1 ) t ) 3586895;50
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Tomas Vondra
Дата:
On 12/01/2017 01:45 PM, sergey.frolov@smetarik.ru wrote: > The following bug has been logged on the website: > > Bug reference: 14940 > Logged by: sergey frolov > Email address: sergey.frolov@smetarik.ru > PostgreSQL version: 9.6.6 > Operating system: Windows 10, 64 > Description: > > Hi, I have noticed duplicated records inspite of primary key and unique > constraint. > > select version () > PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit > > The DDL is > CREATE TABLE nb.nb_basedtl > ( > id integer NOT NULL, > base_id integer NOT NULL, > norm_id integer NOT NULL, > ... > CONSTRAINT pk_nb_basedtl PRIMARY KEY (id), > CONSTRAINT unq_nb_basedtl UNIQUE (norm_id, base_id), > .... > > The problem is > > select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id = > 11658502 ;-- expected ONE row > > (0,49);364507;0;11658502;269;46203 > (0,49);370881;0;11658502;269;46203 > (0,49);370882;0;11658502;269;46203 > > > select (select count(1) from nb.nb_basedtl), (select count(1) from (select > id, count(1) from nb.nb_basedtl group by id having count(1) > 1 ) t ) > 3586895;50 > Seems like some sort of data corruption, but it's impossible to say how the database got into this state. You'll have to tell us more about the system. Did it crash in the past? What sort of filesystem/storage does it use? How old is the database/which PostgreSQL versions was it running (e.g. it may be a new system loaded last week, or it may be an old system started on 9.0 and upgraded using pg_upgrade). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Сергей А. Фролов
Дата:
The database was created at october 2016 on PG 9.5.3 then backuped/restored into PG 9.6.5 and then backuped/restored into PG 9.6.6. I am sure that the ~10 problematic records were added on PG 9.6.5. and ~40 were added on PG 9.6.6. The file systems is NTFS. Windows 10 runs as virtual machine under Hyper-V. Windows logs contains nothing suspicious on both. I have wrote the script to generate select to check for duplicates all tables in all schemas - all other tables are OK. The only problem I observed - the PG dbugger hanged once and we had to kill related postgres process via taskmanager (killing session had no effect) , but I am shure that the killed session did not touch the problem table at all. wbr, Sergey. 01.12.2017 17:20, Tomas Vondra пишет: > On 12/01/2017 01:45 PM, sergey.frolov@smetarik.ru wrote: >> The following bug has been logged on the website: >> >> Bug reference: 14940 >> Logged by: sergey frolov >> Email address: sergey.frolov@smetarik.ru >> PostgreSQL version: 9.6.6 >> Operating system: Windows 10, 64 >> Description: >> >> Hi, I have noticed duplicated records inspite of primary key and unique >> constraint. >> >> select version () >> PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit >> >> The DDL is >> CREATE TABLE nb.nb_basedtl >> ( >> id integer NOT NULL, >> base_id integer NOT NULL, >> norm_id integer NOT NULL, >> ... >> CONSTRAINT pk_nb_basedtl PRIMARY KEY (id), >> CONSTRAINT unq_nb_basedtl UNIQUE (norm_id, base_id), >> .... >> >> The problem is >> >> select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id = >> 11658502 ;-- expected ONE row >> >> (0,49);364507;0;11658502;269;46203 >> (0,49);370881;0;11658502;269;46203 >> (0,49);370882;0;11658502;269;46203 >> >> >> select (select count(1) from nb.nb_basedtl), (select count(1) from (select >> id, count(1) from nb.nb_basedtl group by id having count(1) > 1 ) t ) >> 3586895;50 >> > Seems like some sort of data corruption, but it's impossible to say how > the database got into this state. You'll have to tell us more about the > system. > > Did it crash in the past? > > What sort of filesystem/storage does it use? > > How old is the database/which PostgreSQL versions was it running (e.g. > it may be a new system loaded last week, or it may be an old system > started on 9.0 and upgraded using pg_upgrade). > > regards >
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Tomas Vondra
Дата:
Hi, On 12/01/2017 04:19 PM, Сергей А. Фролов wrote: > The database was created at october 2016 on PG 9.5.3 then > backuped/restored into PG 9.6.5 and then backuped/restored into PG 9.6.6. > > I am sure that the ~10 problematic records were added on PG 9.6.5. and > ~40 were added on PG 9.6.6. > By backup/restore you mean pg_dump? If that's the case, it's pretty sure the duplicates happened on 9.6.6 (otherwise the restore would fail). But that contradicts the 9.6.5 -> 9.6.6 upgrade, if your claim that 10 duplicates originate on 9.6.5 is correct. BTW are you running vanilla PostgreSQL, or some of the EDB versions? > The file systems is NTFS. > > Windows 10 runs as virtual machine under Hyper-V. Windows logs contains > nothing suspicious on both. > No idea. My experience with modern Windows systems is minimal, but I suppose it certainly shouldn't corrupt data in normal operation. > I have wrote the script to generate select to check for duplicates all > tables in all schemas - all other tables are OK. > > The only problem I observed - the PG dbugger hanged once and we had to > kill related postgres process via taskmanager (killing session had no > effect) , but I am shure that the killed session did not touch the > problem table at all. > Not sure which debugger you mean, but again - killing a process should not result in data corruption. It may cause the database to crash and perform recovery, but that's about it. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Alvaro Herrera
Дата:
sergey.frolov@smetarik.ru wrote: > select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id = > 11658502 ;-- expected ONE row > > (0,49);364507;0;11658502;269;46203 > (0,49);370881;0;11658502;269;46203 > (0,49);370882;0;11658502;269;46203 Since these rows all have the same CTID, it looks like the index on id must be corrupt and returned the same tuple more than once. But the weird thing is that the xmin differs ... How can that be? Does this change if you set enable_indexscan and enable_bitmapscan to off? Would you try running amcheck on this index? https://github.com/petergeoghegan/amcheck -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Tomas Vondra
Дата:
On 12/01/2017 06:15 PM, Alvaro Herrera wrote: > sergey.frolov@smetarik.ru wrote: > >> select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id = >> 11658502 ;-- expected ONE row >> >> (0,49);364507;0;11658502;269;46203 >> (0,49);370881;0;11658502;269;46203 >> (0,49);370882;0;11658502;269;46203 > > Since these rows all have the same CTID, it looks like the index on id > must be corrupt and returned the same tuple more than once. But the > weird thing is that the xmin differs ... How can that be? Does this > change if you set enable_indexscan and enable_bitmapscan to off? > > Would you try running amcheck on this index? > https://github.com/petergeoghegan/amcheck > Oh, haven't noticed this. I wonder if the other cases (supposedly there are 50 duplicate records) look the same way. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Сергей А. Фролов
Дата:
1. I'll try amcheck after I get system on Debian and if the problem will remain after dump/restore. 2. Switthing off enable_indexscan enable_bitmapscan has no effect: set enable_indexscan = off; set enable_bitmapscan = off; select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id = 11658502; (0,49);364507;0;11658502;269;46203 (0,49);370881;0;11658502;269;46203 (0,49);370882;0;11658502;269;46203 select ctid,xmin,xmax,id, base_id, norm_id from nb.nb_basedtl where id in (select id from nb.nb_basedtl group by id having count(1) > 1 ) order by ctid,id (0,1);364507;0;16101774;321;1239643 (0,1);370881;0;16101774;321;1239643 (0,1);370882;0;16101774;321;1239643 (0,2);370882;0;20365934;425;2 (0,2);370881;0;20365934;425;2 (0,2);364507;0;20365934;425;2 (0,3);370881;0;20365935;425;3 (0,3);364507;0;20365935;425;3 (0,3);370882;0;20365935;425;3 (0,4);370881;0;20365936;425;4 (0,4);364507;0;20365936;425;4 (0,4);370882;0;20365936;425;4 (0,5);364507;0;20365937;425;276807 (0,5);370882;0;20365937;425;276807 (0,5);370881;0;20365937;425;276807 (0,6);370882;0;20365938;425;276808 (0,6);364507;0;20365938;425;276808 (0,6);370881;0;20365938;425;276808 (0,7);370882;0;20365939;425;7 (0,7);364507;0;20365939;425;7 (0,7);370881;0;20365939;425;7 (0,8);370881;0;20365940;425;8 (0,8);364507;0;20365940;425;8 (0,8);370882;0;20365940;425;8 (0,9);370882;0;20365941;425;9 (0,9);370881;0;20365941;425;9 (0,9);364507;0;20365941;425;9 (0,10);370881;0;20365942;425;10 (0,10);370882;0;20365942;425;10 (0,10);364507;0;20365942;425;10 (0,11);364507;0;20365943;425;11 (0,11);370882;0;20365943;425;11 (0,11);370881;0;20365943;425;11 (0,12);370882;0;20365944;425;12 (0,12);364507;0;20365944;425;12 (0,12);370881;0;20365944;425;12 (0,13);370881;0;20365945;425;13 (0,13);364507;0;20365945;425;13 (0,13);370882;0;20365945;425;13 (0,14);370882;0;20365946;425;14 (0,14);364507;0;20365946;425;14 (0,14);370881;0;20365946;425;14 (0,15);364507;0;20365947;425;434507 (0,15);370882;0;20365947;425;434507 (0,15);370881;0;20365947;425;434507 (0,16);370882;0;20365948;425;16 (0,16);364507;0;20365948;425;16 (0,16);370881;0;20365948;425;16 (0,17);370882;0;20365949;425;17 (0,17);370881;0;20365949;425;17 (0,17);364507;0;20365949;425;17 (0,18);370881;0;20365950;425;18 (0,18);364507;0;20365950;425;18 (0,18);370882;0;20365950;425;18 (0,19);370882;0;20365951;425;19 (0,19);370881;0;20365951;425;19 (0,19);364507;0;20365951;425;19 (0,20);364507;0;20365952;425;276809 (0,20);370882;0;20365952;425;276809 (0,20);370881;0;20365952;425;276809 (0,21);370882;0;20365953;425;21 (0,21);370881;0;20365953;425;21 (0,21);364507;0;20365953;425;21 (0,22);370882;0;20365954;425;22 (0,22);364507;0;20365954;425;22 (0,22);370881;0;20365954;425;22 (0,23);370881;0;20365955;425;1492 (0,23);364507;0;20365955;425;1492 (0,23);370882;0;20365955;425;1492 (0,24);370882;0;20365956;425;24 (0,24);370881;0;20365956;425;24 (0,24);364507;0;20365956;425;24 (0,25);370881;0;20365957;425;25 (0,25);364507;0;20365957;425;25 (0,25);370882;0;20365957;425;25 (0,26);370881;0;20365958;425;26 (0,26);364507;0;20365958;425;26 (0,26);370882;0;20365958;425;26 (0,27);370882;0;20365959;425;27 (0,27);364507;0;20365959;425;27 (0,27);370881;0;20365959;425;27 (0,28);370881;0;20365960;425;28 (0,28);364507;0;20365960;425;28 (0,28);370882;0;20365960;425;28 (0,29);370881;0;20365961;425;29 (0,29);364507;0;20365961;425;29 (0,29);370882;0;20365961;425;29 (0,30);364507;0;20365962;425;1497 (0,30);370881;0;20365962;425;1497 (0,30);370882;0;20365962;425;1497 (0,31);370882;0;20365963;425;31 (0,31);364507;0;20365963;425;31 (0,31);370881;0;20365963;425;31 (0,32);370881;0;20365964;425;32 (0,32);364507;0;20365964;425;32 (0,32);370882;0;20365964;425;32 (0,33);370882;0;20365965;425;33 (0,33);370881;0;20365965;425;33 (0,33);364507;0;20365965;425;33 (0,34);364507;0;20365966;425;34 (0,34);370882;0;20365966;425;34 (0,34);370881;0;20365966;425;34 (0,35);364507;0;20365967;425;35 (0,35);370881;0;20365967;425;35 (0,35);370882;0;20365967;425;35 (0,36);370882;0;20365968;425;36 (0,36);370881;0;20365968;425;36 (0,36);364507;0;20365968;425;36 (0,37);364507;0;20365969;425;37 (0,37);370881;0;20365969;425;37 (0,37);370882;0;20365969;425;37 (0,38);370882;0;20365970;425;38 (0,38);370881;0;20365970;425;38 (0,38);364507;0;20365970;425;38 (0,39);370881;0;20365971;425;39 (0,39);364507;0;20365971;425;39 (0,39);370882;0;20365971;425;39 (0,40);370882;0;20365972;425;40 (0,40);364507;0;20365972;425;40 (0,40);370881;0;20365972;425;40 (0,41);364507;0;20365973;425;1507 (0,41);370881;0;20365973;425;1507 (0,41);370882;0;20365973;425;1507 (0,42);370881;0;20365974;425;42 (0,42);364507;0;20365974;425;42 (0,42);370882;0;20365974;425;42 (0,43);370881;0;20365975;425;43 (0,43);364507;0;20365975;425;43 (0,43);370882;0;20365975;425;43 (0,44);364507;0;11658485;269;46190 (0,44);370882;0;11658485;269;46190 (0,44);370881;0;11658485;269;46190 (0,45);370881;0;12066836;280;95258 (0,45);370882;0;12066836;280;95258 (0,45);364507;0;12066836;280;95258 (0,46);370881;0;11658496;269;46194 (0,46);370882;0;11658496;269;46194 (0,46);364507;0;11658496;269;46194 (0,47);370882;0;11658494;269;46196 (0,47);370881;0;11658494;269;46196 (0,47);364507;0;11658494;269;46196 (0,48);364507;0;11658492;269;46197 (0,48);370881;0;11658492;269;46197 (0,48);370882;0;11658492;269;46197 (0,49);370882;0;11658502;269;46203 (0,49);370881;0;11658502;269;46203 (0,49);364507;0;11658502;269;46203 (0,50);364507;0;11658508;269;46204 (0,50);370882;0;11658508;269;46204 (0,50);370881;0;11658508;269;46204 regards, Sergey. 01.12.2017 20:15, Alvaro Herrera пишет: > sergey.frolov@smetarik.ru wrote: > >> select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id = >> 11658502 ;-- expected ONE row >> >> (0,49);364507;0;11658502;269;46203 >> (0,49);370881;0;11658502;269;46203 >> (0,49);370882;0;11658502;269;46203 > Since these rows all have the same CTID, it looks like the index on id > must be corrupt and returned the same tuple more than once. But the > weird thing is that the xmin differs ... How can that be? Does this > change if you set enable_indexscan and enable_bitmapscan to off? > > Would you try running amcheck on this index? > https://github.com/petergeoghegan/amcheck >
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Сергей А. Фролов
Дата:
1. Yes, I have used pg_dump/pg_restore. I am sure that the ~10 problematic records were added on PG 9.6.5. and ~40 were added on PG 9.6.6., But I do not know when they were duplicated. 2. I have downloaded MS Win version from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#windows 01.12.2017 20:07, Tomas Vondra пишет: > Hi, > > On 12/01/2017 04:19 PM, Сергей А. Фролов wrote: >> The database was created at october 2016 on PG 9.5.3 then >> backuped/restored into PG 9.6.5 and then backuped/restored into PG 9.6.6. >> >> I am sure that the ~10 problematic records were added on PG 9.6.5. and >> ~40 were added on PG 9.6.6. >> > By backup/restore you mean pg_dump? If that's the case, it's pretty sure > the duplicates happened on 9.6.6 (otherwise the restore would fail). > > But that contradicts the 9.6.5 -> 9.6.6 upgrade, if your claim that 10 > duplicates originate on 9.6.5 is correct. > > BTW are you running vanilla PostgreSQL, or some of the EDB versions? > >> The file systems is NTFS. >> >> Windows 10 runs as virtual machine under Hyper-V. Windows logs contains >> nothing suspicious on both. >> > No idea. My experience with modern Windows systems is minimal, but I > suppose it certainly shouldn't corrupt data in normal operation. > >> I have wrote the script to generate select to check for duplicates all >> tables in all schemas - all other tables are OK. >> >> The only problem I observed - the PG dbugger hanged once and we had to >> kill related postgres process via taskmanager (killing session had no >> effect) , but I am shure that the killed session did not touch the >> problem table at all. >> > Not sure which debugger you mean, but again - killing a process should > not result in data corruption. It may cause the database to crash and > perform recovery, but that's about it. > > regards >
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Tomas Vondra
Дата:
On 12/04/2017 09:22 AM, Сергей А. Фролов wrote: > 1. I'll try amcheck after I get system on Debian and if the problem > will remain after dump/restore. > > 2. Switthing off enable_indexscan enable_bitmapscan has no effect: > > set enable_indexscan = off; > set enable_bitmapscan = off; > > select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id = > 11658502; > > (0,49);364507;0;11658502;269;46203 > (0,49);370881;0;11658502;269;46203 > (0,49);370882;0;11658502;269;46203 > > > select ctid,xmin,xmax,id, base_id, norm_id from nb.nb_basedtl where id > in (select > id from nb.nb_basedtl group by id having count(1) > 1 ) order by ctid,id > (0,1);364507;0;16101774;321;1239643 > (0,1);370881;0;16101774;321;1239643 > (0,1);370882;0;16101774;321;1239643 > (0,2);370882;0;20365934;425;2 > (0,2);370881;0;20365934;425;2 > (0,2);364507;0;20365934;425;2 > (0,3);370881;0;20365935;425;3 > (0,3);364507;0;20365935;425;3 > (0,3);370882;0;20365935;425;3 > (0,4);370881;0;20365936;425;4 > (0,4);364507;0;20365936;425;4 > ... > (0,49);370882;0;11658502;269;46203 > (0,49);370881;0;11658502;269;46203 > (0,49);364507;0;11658502;269;46203 > (0,50);364507;0;11658508;269;46204 > (0,50);370882;0;11658508;269;46204 > (0,50);370881;0;11658508;269;46204 > Interesting. All the duplicate records seem to be on the first page, and there are always three of them ... I wonder if those records are part of the same HOT chain, or something like that. Can you look at the page using pageinspect? Something like SELECT * FROM heap_page_items(get_raw_page('nb.nb_basedtl', 0)); regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Сергей А. Фролов
Дата:
page inspection is SELECT * FROM page_header(get_raw_page('nb.nb_basedtl', 0)); SELECT * FROM heap_page_items(get_raw_page('nb.nb_basedtl', 0)); lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid -----+----------+-------+-------+-------+---------+----------+---------+----------- 0/0 | 0 | 0 | 320 | 416 | 8192 | 8192 | 4 | 0 (1 строка) lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+----------------------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 | 8080 | 1 | 108 | 364507 | 0 | 0 | (0,1) | 27 | 2817 | 32 | 11111110011100111100000010000000 | | \x8eb1f500410100005bea1200010000008eb1754b0500000001000000030001000100000000000000000000000000000000000000000000000000000024ec67b4b0554c108a66bfbbf8014844 2 | 7976 | 1 | 100 | 364507 | 0 | 0 | (0,2) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x6ec23601a9010000020000007318000037619b4b040000000300010001000000000000000000000000000000000000000000000051c1a3a8df3942349f00c38bf752f6ba 3 | 7872 | 1 | 100 | 364507 | 0 | 0 | (0,3) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x6fc23601a901000003000000391c000038619b4b04000000030001000100000000000000000000000000000000000000000000009dc256f553ce4788a0b188518dbc9849 4 | 7768 | 1 | 100 | 364507 | 0 | 0 | (0,4) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x70c23601a901000004000000001f000038619b4b04000000030001000100000000000000000000000000000000000000000000005443a939f3e945c8b730b9de5564a7e1 5 | 7664 | 1 | 100 | 364507 | 0 | 0 | (0,5) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x71c23601a901000047390400ab78000038619b4b04000000030001000100000000000000000000000000000000000000000000002ef13821755949ffae2a74c6925700b9 6 | 7560 | 1 | 100 | 364507 | 0 | 0 | (0,6) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x72c23601a901000048390400f579000039619b4b0400000003000100010000000000000000000000000000000000000000000000251131c6abbb4f7db2443cb4036037ce 7 | 7456 | 1 | 100 | 364507 | 0 | 0 | (0,7) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x73c23601a901000007000000d71200003a619b4b04000000030001000100000000000000000000000000000000000000000000000e5a804fc78346afa5978fe6281f5b2b 8 | 7352 | 1 | 100 | 364507 | 0 | 0 | (0,8) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x74c23601a9010000080000001d1800003a619b4b0400000003000100010000000000000000000000000000000000000000000000a5c5424ea884459a82e6c49068fe6333 9 | 7248 | 1 | 100 | 364507 | 0 | 0 | (0,9) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x75c23601a901000009000000f41b00003a619b4b04000000030001000100000000000000000000000000000000000000000000006ce9467219014ec888e4b9a012733c51 10 | 7144 | 1 | 100 | 364507 | 0 | 0 | (0,10) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x76c23601a90100000a000000c81e00003b619b4b040000000300010001000000000000000000000000000000000000000000000042c99f702c894f789904a50fb6f9455f 11 | 7040 | 1 | 100 | 364507 | 0 | 0 | (0,11) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x77c23601a90100000b000000e52000003c619b4b040000000300010001000000000000000000000000000000000000000000000033bdd8bf6f564ba5a1e6624b3d405b3a 12 | 6936 | 1 | 100 | 364507 | 0 | 0 | (0,12) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x78c23601a90100000c0000008f2200003c619b4b0400000003000100010000000000000000000000000000000000000000000000d4b98ad3c8384c32b291c63e2ebd15b3 13 | 6832 | 1 | 100 | 364507 | 0 | 0 | (0,13) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x79c23601a90100000d000000df2300003c619b4b0400000003000100010000000000000000000000000000000000000000000000e8f718a6e9fe48c4b356e929c2e9615e 14 | 6728 | 1 | 100 | 364507 | 0 | 0 | (0,14) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x7ac23601a90100000e000000dd2400003d619b4b04000000030001000100000000000000000000000000000000000000000000005ef27e1c19484e6d898e848cf838fca6 15 | 6624 | 1 | 100 | 364507 | 0 | 0 | (0,15) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x7bc23601a90100004ba10600b22500003e619b4b040000000300010001000000000000000000000000000000000000000000000099e5c2ef53e1406d8a23fe3cad83d63a 16 | 6520 | 1 | 100 | 364507 | 0 | 0 | (0,16) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x7cc23601a901000010000000662600003e619b4b04000000030001000100000000000000000000000000000000000000000000007009f89c7bc94d8bbc0a762b88e3e57a 17 | 6416 | 1 | 100 | 364507 | 0 | 0 | (0,17) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x7dc23601a901000011000000002700003e619b4b040000000300010001000000000000000000000000000000000000000000000011cf5c03714c4e76bc7377862d4399bd 18 | 6312 | 1 | 100 | 364507 | 0 | 0 | (0,18) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x7ec23601a901000012000000862700003f619b4b0400000003000100010000000000000000000000000000000000000000000000b7e1f58ffa5b481c965dc1661fb75c77 19 | 6208 | 1 | 100 | 364507 | 0 | 0 | (0,19) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x7fc23601a901000013000000f827000040619b4b04000000030001000100000000000000000000000000000000000000000000009ba4dbd67bfa4ab18e4a1309e04ebd71 20 | 6104 | 1 | 100 | 364507 | 0 | 0 | (0,20) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x80c23601a9010000493904005528000040619b4b04000000030001000100000000000000000000000000000000000000000000001388dde21b224a0f84ef568ecf0d6f84 21 | 6000 | 1 | 100 | 364507 | 0 | 0 | (0,21) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x81c23601a901000015000000a728000040619b4b040000000300010001000000000000000000000000000000000000000000000007f2d7b1eee64fb18eb3032ffed66433 22 | 5896 | 1 | 100 | 364507 | 0 | 0 | (0,22) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x82c23601a901000016000000f528000041619b4b04000000030001000100000000000000000000000000000000000000000000002337b03ab0d54bd88c322a3c2803b729 23 | 5792 | 1 | 100 | 364507 | 0 | 0 | (0,23) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x83c23601a9010000d40500003929000042619b4b04000000030001000100000000000000000000000000000000000000000000004a5b39a156cf40f386cfbff1dee13212 24 | 5688 | 1 | 100 | 364507 | 0 | 0 | (0,24) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x84c23601a9010000180000007629000042619b4b0400000003000100010000000000000000000000000000000000000000000000c83fa2cb5b9c44818fb6b36d8984c677 25 | 5584 | 1 | 100 | 364507 | 0 | 0 | (0,25) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x85c23601a901000019000000ab29000042619b4b0400000003000100010000000000000000000000000000000000000000000000d98cdd4d6a0848fc8bab2f77ed03748a 26 | 5480 | 1 | 100 | 364507 | 0 | 0 | (0,26) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x86c23601a90100001a000000d929000043619b4b0400000003000100010000000000000000000000000000000000000000000000b3fc06344acb4d7f997da4912464c8cd 27 | 5376 | 1 | 100 | 364507 | 0 | 0 | (0,27) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x87c23601a90100001b000000042a000044619b4b04000000030001000100000000000000000000000000000000000000000000002223b2d82ecd42ef881619d7fcf987be 28 | 5272 | 1 | 100 | 364507 | 0 | 0 | (0,28) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x88c23601a90100001c0000002c2a000044619b4b04000000030001000100000000000000000000000000000000000000000000005d93a65a068448b1ae15ac9c4aa61d1a 29 | 5168 | 1 | 100 | 364507 | 0 | 0 | (0,29) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x89c23601a90100001d000000532a000044619b4b040000000300010001000000000000000000000000000000000000000000000010db2c640c63445dbc15d5b39de66c13 30 | 5064 | 1 | 100 | 364507 | 0 | 0 | (0,30) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8ac23601a9010000d9050000772a000045619b4b0400000003000100010000000000000000000000000000000000000000000000fa6c35a685094e05a7c4acce29f4fec5 31 | 4960 | 1 | 100 | 364507 | 0 | 0 | (0,31) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8bc23601a90100001f000000982a000046619b4b040000000300010001000000000000000000000000000000000000000000000053758943e7374638b41f8400a12451ae 32 | 4856 | 1 | 100 | 364507 | 0 | 0 | (0,32) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8cc23601a901000020000000b72a000046619b4b04000000030001000100000000000000000000000000000000000000000000003611dde222b04f7c8c34526c7b732eb7 33 | 4752 | 1 | 100 | 364507 | 0 | 0 | (0,33) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8dc23601a901000021000000d22a000046619b4b04000000030001000100000000000000000000000000000000000000000000002ffac4510e5847faa25d9e655f810fe5 34 | 4648 | 1 | 100 | 364507 | 0 | 0 | (0,34) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8ec23601a901000022000000ec2a000047619b4b0400000003000100010000000000000000000000000000000000000000000000b326aaf52ec74b47bddfe4a7a7f50a2e 35 | 4544 | 1 | 100 | 364507 | 0 | 0 | (0,35) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8fc23601a9010000230000003313000048619b4b0400000003000100010000000000000000000000000000000000000000000000572d618dbd1e4168ae36fa6bce863053 36 | 4440 | 1 | 100 | 364507 | 0 | 0 | (0,36) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x90c23601a9010000240000006e18000048619b4b04000000030001000100000000000000000000000000000000000000000000001973b57fff8941f2a508b09eaa2794e4 37 | 4336 | 1 | 100 | 364507 | 0 | 0 | (0,37) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x91c23601a901000025000000321c000048619b4b0400000003000100010000000000000000000000000000000000000000000000b36304500ad246409e71428698040abf 38 | 4232 | 1 | 100 | 364507 | 0 | 0 | (0,38) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x92c23601a901000026000000f51e000049619b4b0400000003000100010000000000000000000000000000000000000000000000d725ad4459774176a7995e0a010c1b0e 39 | 4128 | 1 | 100 | 364507 | 0 | 0 | (0,39) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x93c23601a901000027000000072100004a619b4b0400000003000100010000000000000000000000000000000000000000000000f1bc125c82944224a2b1c570ca90d489 40 | 4024 | 1 | 100 | 364507 | 0 | 0 | (0,40) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x94c23601a901000028000000a52200004a619b4b0400000003000100010000000000000000000000000000000000000000000000441053b3de3b49c4b63d89129b8225c6 41 | 3920 | 1 | 100 | 364507 | 0 | 0 | (0,41) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x95c23601a9010000e3050000ec2300004a619b4b040000000300010001000000000000000000000000000000000000000000000055772ed5d53549aaac95567d3af69a0e 42 | 3816 | 1 | 100 | 364507 | 0 | 0 | (0,42) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x96c23601a90100002a000000e92400004b619b4b040000000300010001000000000000000000000000000000000000000000000026a737b9d86b4de3ba08f3a20a003ca0 43 | 3712 | 1 | 100 | 364507 | 0 | 0 | (0,43) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x97c23601a90100002b000000bb2500004c619b4b04000000030001000100000000000000000000000000000000000000000000000857323740d84b3e96e350aca0e17037 44 | 3600 | 1 | 108 | 364507 | 0 | 0 | (0,44) | 27 | 2817 | 32 | 11111100111100111100000010000000 | | \xf5e4b1000d0100006eb4000068070000f5e4314b04000000f10000000300010001000000000000000000000000000000000000000000000000000000e04dec8e4e9f4f118d4007b80128cac8 45 | 3496 | 1 | 100 | 364507 | 0 | 0 | (0,45) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x1420b800180100001a740100e12100001420384b05000000030001000100000000000000000000000000000000000000000000004dd73ceb2e6146988e54b3b9671ceb6c 46 | 3384 | 1 | 108 | 364507 | 0 | 0 | (0,46) | 27 | 2817 | 32 | 11111100111100111100000010000000 | | \x00e5b1000d01000072b400004118000000e5314b04000000f10000000300010001000000000000000000000000000000000000000000000000000000b5953f2bc29d4d7e80c01dad480116fb 47 | 3272 | 1 | 108 | 364507 | 0 | 0 | (0,47) | 27 | 2817 | 32 | 11111100111100111100000010000000 | | \xfee4b1000d01000074b40000d5240000fee4314b04000000f10000000300010001000000000000000000000000000000000000000000000000000000d4b45d12d9ef4d1f8d0e2590ff9e33f0 48 | 3160 | 1 | 108 | 364507 | 0 | 0 | (0,48) | 27 | 2817 | 32 | 11111100111100111100000010000000 | | \xfce4b1000d01000075b400005e260000fce4314b04000000f1000000030001000100000000000000000000000000000000000000000000000000000026988d121e58455ca17cac9129aeef56 49 | 3048 | 1 | 108 | 364507 | 0 | 0 | (0,49) | 27 | 2817 | 32 | 11111100111100111100000010000000 | | \x06e5b1000d0100007bb40000d71e000006e5314b04000000f100000003000100010000000000000000000000000000000000000000000000000000005398f1f2b17d4cd9a407e499f7a90c95 50 | 2936 | 1 | 108 | 364507 | 0 | 0 | (0,50) | 27 | 2817 | 32 | 11111100111100111100000010000000 | | \x0ce5b1000d0100007cb40000352900000ce5314b04000000f100000003000100010000000000000000000000000000000000000000000000000000001373efcdc1cb4b0bb198532d0e739418 51 | 2824 | 1 | 108 | 364507 | 0 | 0 | (0,51) | 27 | 2817 | 32 | 11111100111100111100000010000000 | | \x03e5b1000d0100007db400004c18000003e5314b04000000f10000000300010001000000000000000000000000000000000000000000000000000000512a6064ac9e4af3a7509bd7e9f5746f 52 | 2712 | 1 | 108 | 364507 | 0 | 0 | (0,52) | 27 | 2817 | 32 | 11111100111100111100000010000000 | | \x0be5b1000d0100007eb40000101c00000be5314b04000000f10000000300010001000000000000000000000000000000000000000000000000000000105738d33e9f49bfab5ea3063db224ff 53 | 2600 | 1 | 108 | 364507 | 0 | 0 | (0,53) | 27 | 2817 | 32 | 11111100111100111100000010000000 | | \x08e5b1000d0100007fb400009022000008e5314b04000000f100000003000100010000000000000000000000000000000000000000000000000000006e5208cc77e24305b47a3025c8a16c20 54 | 2496 | 1 | 100 | 364507 | 0 | 0 | (0,54) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x6dc23601a9010000010000003213000036619b4b0400000003000100010000000000000000000000000000000000000000000000fc3a34d9f38a4bd0a8c003a4ae4b5d0a 55 | 2392 | 1 | 100 | 364507 | 0 | 0 | (0,55) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8ab85600290000004d810000c71e00001471ad4a04000000030001000100000000000000000000000000000000000000000000000fe15efadd4a4d73a1d04ce005f03668 56 | 2288 | 1 | 100 | 364507 | 0 | 0 | (0,56) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8bb85600290000000f770000680700001671ad4a04000000030001000100000000000000000000000000000000000000000000008593d1a30e5441e4b5d992f867292935 57 | 2184 | 1 | 100 | 364507 | 0 | 0 | (0,57) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8cb85600290000004f9d0000051c00001871ad4a040000000300010001000000000000000000000000000000000000000000000032d28d9afc2b41809d9910342f189792 58 | 2080 | 1 | 100 | 364507 | 0 | 0 | (0,58) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8db8560029000000509d0000411800001a71ad4a0400000003000100010000000000000000000000000000000000000000000000abbeb5bdd1374f9680f927c01fe7c83e 59 | 1976 | 1 | 100 | 364507 | 0 | 0 | (0,59) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8eb8560029000000519d0000f82600001c71ad4a0400000003000100010000000000000000000000000000000000000000000000883ce547ccfe45a5a317b485c2d8cd9f 60 | 1872 | 1 | 100 | 364507 | 0 | 0 | (0,60) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x8fb8560029000000be950000e62000001e71ad4a040000000300010001000000000000000000000000000000000000000000000069de83e110704b8baaf67430dff4e6e6 61 | 1768 | 1 | 100 | 364507 | 0 | 0 | (0,61) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x90b8560029000000539d0000d52400002071ad4a0400000003000100010000000000000000000000000000000000000000000000bfd4d9f661834c1d9da2c1696cd94f3a 62 | 1664 | 1 | 100 | 364507 | 0 | 0 | (0,62) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x91b8560029000000549d00005e2600002271ad4a04000000030001000100000000000000000000000000000000000000000000007af61f2c50a4411ea223e8a37a977848 63 | 1560 | 1 | 100 | 364507 | 0 | 0 | (0,63) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x92b8560029000000559d0000f92600002471ad4a0400000003000100010000000000000000000000000000000000000000000000a38e00d402a74274bca57da415d52a7a 64 | 1456 | 1 | 100 | 364507 | 0 | 0 | (0,64) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x93b8560029000000569d0000431800002671ad4a040000000300010001000000000000000000000000000000000000000000000062efc8cacc35402caf1ff337d9b1976d 65 | 1352 | 1 | 100 | 364507 | 0 | 0 | (0,65) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x94b8560029000000579d0000091c00002871ad4a04000000030001000100000000000000000000000000000000000000000000006501a708a49f4ed3b76298c1c53c8298 66 | 1248 | 1 | 100 | 364507 | 0 | 0 | (0,66) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x95b8560029000000589d00005f2600002a71ad4a0400000003000100010000000000000000000000000000000000000000000000bc4b088b4dcb43389f910c3188858aaa 67 | 1144 | 1 | 100 | 364507 | 0 | 0 | (0,67) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x96b8560029000000599d0000fa2600002c71ad4a0400000003000100010000000000000000000000000000000000000000000000648eb0beba9b424cb3be18c67811c3b6 68 | 1040 | 1 | 100 | 364507 | 0 | 0 | (0,68) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x97b85600290000005a9d0000081300002e71ad4a0400000003000100010000000000000000000000000000000000000000000000829f9e78de264d76a5850f0797240e85 69 | 936 | 1 | 100 | 364507 | 0 | 0 | (0,69) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x98b85600290000005b9d0000d21e00003071ad4a0400000003000100010000000000000000000000000000000000000000000000057ddf8254204486a7ee1445a9a5dd28 70 | 832 | 1 | 100 | 364507 | 0 | 0 | (0,70) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x99b85600290000005c9d00008a2200003271ad4a0400000003000100010000000000000000000000000000000000000000000000c0d771275f8e414088327a4f02fb5058 71 | 728 | 1 | 100 | 364507 | 0 | 0 | (0,71) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x9ab85600290000005d9d00000a1300003471ad4a04000000030001000100000000000000000000000000000000000000000000006689a141006b48a3a42f72e5d47d1bdf 72 | 624 | 1 | 100 | 364507 | 0 | 0 | (0,72) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x9bb8560029000000b09900000b1c00003671ad4a04000000030001000100000000000000000000000000000000000000000000008aef57c494124db3b05649e64184577e 73 | 520 | 1 | 100 | 364507 | 0 | 0 | (0,73) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x9cb85600290000005f9d0000d31e00003871ad4a0400000003000100010000000000000000000000000000000000000000000000575485d7714f4fb9b374d85e3fcf9cb6 74 | 416 | 1 | 100 | 364507 | 0 | 0 | (0,74) | 27 | 2817 | 32 | 11111100011100111100000010000000 | | \x9db8560029000000fd360200e92000003a71ad4a0400000003000100010000000000000000000000000000000000000000000000a044d144045040baa6e230e33b0fb566 (74 строки)
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Tomas Vondra
Дата:
On 12/04/2017 04:01 PM, Сергей А. Фролов wrote: > page inspection is > > SELECT * FROM page_header(get_raw_page('nb.nb_basedtl', 0)); > SELECT * FROM heap_page_items(get_raw_page('nb.nb_basedtl', 0)); > > lsn | checksum | flags | lower | upper | special | pagesize | version | > prune_xid > -----+----------+-------+-------+-------+---------+----------+---------+----------- > > 0/0 | 0 | 0 | 320 | 416 | 8192 | 8192 | 4 > | 0 > (1 строка) > > lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | > t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data > ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+----------------------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------ > > 1 | 8080 | 1 | 108 | 364507 | 0 | 0 | (0,1) > | 27 | 2817 | 32 | 11111110011100111100000010000000 > | | > Thanks. Unfortunately that does not explain anything, and makes it ever stranger - none of the items on the page is HOT-updated (t_infomask2 is 27, so it only contains number of attributes) or even deleted. And the only xmin value on the page is 364507. So where did the other values (370881, 370882) come from? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Сергей А. Фролов
Дата:
I have scanned all pages for tran numbers 370881,370882,364507 . It looks like 364507 is the number of restore transaction. Is it possible that 370881,370882 are the numbers of failed maintenance jobs like vacuum or rindex? regards, Sergey. select xmin,count(1) from nb.nb_basedtl where xmin in (370881,370882,364507) group by xmin >>364507;3236715 >>370881;50 >>370882;50 select xmax,count(1) from nb.nb_basedtl where xmax in (370881,370882,364507) group by xmax >> ------------------- --analyze verbose nb.nb_basedtl -->>49090 do $$ declare n integer = 49090 -1 ; total37088X int = 0; total364507 int = 0; begin loop if exists (SELECT 1 FROM heap_page_items(get_raw_page('nb.nb_basedtl', n)) where t_xmin in (370881,370882) or t_xmax in (370881,370882) ) then total37088X=total37088X+1; end if; if exists (SELECT 1 FROM heap_page_items(get_raw_page('nb.nb_basedtl', n)) where t_xmin in (364507) ) then total364507=total364507+1; end if; n=n-1; exit when n < 0; end loop; raise notice 'total37088X % total364507 % ',total37088X,total364507; end$$; >>NOTICE: total37088X 0 total364507 44223 04.12.2017 18:13, Tomas Vondra пишет: > > On 12/04/2017 04:01 PM, Сергей А. Фролов wrote: >> page inspection is >> >> SELECT * FROM page_header(get_raw_page('nb.nb_basedtl', 0)); >> SELECT * FROM heap_page_items(get_raw_page('nb.nb_basedtl', 0)); >> >> lsn | checksum | flags | lower | upper | special | pagesize | version | >> prune_xid >> -----+----------+-------+-------+-------+---------+----------+---------+----------- >> >> 0/0 | 0 | 0 | 320 | 416 | 8192 | 8192 | 4 >> | 0 >> (1 строка) >> >> lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | >> t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data >> ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+----------------------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------ >> >> 1 | 8080 | 1 | 108 | 364507 | 0 | 0 | (0,1) >> | 27 | 2817 | 32 | 11111110011100111100000010000000 >> | | >> > Thanks. Unfortunately that does not explain anything, and makes it ever > stranger - none of the items on the page is HOT-updated (t_infomask2 is > 27, so it only contains number of attributes) or even deleted. > > And the only xmin value on the page is 364507. So where did the other > values (370881, 370882) come from? > > regards >
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Tomas Vondra
Дата:
On 12/04/2017 05:06 PM, Сергей А. Фролов wrote: > I have scanned all pages for tran numbers 370881,370882,364507 . > > It looks like 364507 is the number of restore transaction. > > Is it possible that 370881,370882 are the numbers of failed maintenance > jobs like vacuum or rindex? > I don't think any of those operations create new rows in the heap. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Сергей А. Фролов
Дата:
Hi, I have got a VirtualBox with Debian 8.3 and have installed PG 9.6 and amcheck: select version () "PostgreSQL 9.6.6 on i686-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 32-bit" No errors were raised by amcheck: SELECT bt_index_check(index => indexrelid, heapallindexed => true),bt_index_parent_check(index =>indexrelid), relname, indexrelname FROM pg_stat_all_indexes WHERE indexrelname in ('pk_nb_basedtl','unq_nb_basedtl_basenorm'); "";"";"nb_basedtl";"pk_nb_basedtl" "";"";"nb_basedtl";"unq_nb_basedtl_basenorm" BUT duplicate rows were restored via pg_dump/pg_restore. select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id =11658502; (0,49);5513;0;11658502;269;46203 (0,49);5518;0;11658502;269;46203 (0,49);5519;0;11658502;269;46203 select ctid,xmin,xmax,id, base_id, norm_id from nb.nb_basedtl where id in (select id from nb.nb_basedtl group by id having count(1) > 1 ) order by ctid,id (0,1);5513;0;16101774;321;1239643 (0,1);5519;0;16101774;321;1239643 (0,1);5518;0;16101774;321;1239643 (0,2);5519;0;20365934;425;2 (0,2);5518;0;20365934;425;2 (0,2);5513;0;20365934;425;2 (0,3);5513;0;20365935;425;3 (0,3);5519;0;20365935;425;3 (0,3);5518;0;20365935;425;3 (0,4);5513;0;20365936;425;4 (0,4);5519;0;20365936;425;4 (0,4);5518;0;20365936;425;4 (0,5);5513;0;20365937;425;276807 (0,5);5519;0;20365937;425;276807 (0,5);5518;0;20365937;425;276807 (0,6);5519;0;20365938;425;276808 (0,6);5513;0;20365938;425;276808 (0,6);5518;0;20365938;425;276808 ... (0,49);5519;0;11658502;269;46203 (0,49);5513;0;11658502;269;46203 (0,49);5518;0;11658502;269;46203 (0,50);5513;0;11658508;269;46204 (0,50);5519;0;11658508;269;46204 (0,50);5518;0;11658508;269;46204 I have tried reindex - no effect, clustering nb_basedtl using pk_nb_basedtl changed lowest ctid and moved records last (0,1);5519;0;16101774;321;1239643 (0,1);5518;0;16101774;321;1239643 (0,2);5519;0;20365934;425;2 (0,2);5518;0;20365934;425;2 (0,3);5519;0;20365935;425;3 (0,3);5518;0;20365935;425;3 (0,4);5519;0;20365936;425;4 (0,4);5518;0;20365936;425;4 ... (0,49);5519;0;11658502;269;46203 (0,49);5518;0;11658502;269;46203 (0,50);5519;0;11658508;269;46204 (0,50);5518;0;11658508;269;46204 (19306,68);5513;0;11658485;269;46190 (19306,75);5513;0;11658492;269;46197 (19306,77);5513;0;11658494;269;46196 (19307,1);5513;0;11658496;269;46194 (19307,7);5513;0;11658502;269;46203 (19307,13);5513;0;11658508;269;46204 (21100,27);5513;0;12066836;280;95258 (24575,75);5513;0;16101774;321;1239643 (24575,77);5513;0;20365934;425;2 (24575,78);5513;0;20365935;425;3 (24575,79);5513;0;20365936;425;4 ... (24576,36);5513;0;20365974;425;42 (24576,37);5513;0;20365975;425;43 This stuff have made me slightly mad: alter table nb.nb_basedtl add constraint unq_nb_basedtl_basenorm2 unique(norm_id, base_id); create unique index pk_nb_basedtl2 on nb.nb_basedtl(id); select bt_index_check(index => indexrelid, heapallindexed => true), bt_index_parent_check(index =>indexrelid), relname, indexrelname from pg_stat_all_indexes where indexrelname in ('pk_nb_basedtl','pk_nb_basedtl2','unq_nb_basedtl_basenorm','unq_nb_basedtl_basenorm2') "";"";"nb_basedtl";"pk_nb_basedtl" "";"";"nb_basedtl";"unq_nb_basedtl_basenorm" "";"";"nb_basedtl";"unq_nb_basedtl_basenorm2" "";"";"nb_basedtl";"pk_nb_basedtl2" But this stuff have returned my trust in PG: create table nb.nb_basedtl_copy (like nb.nb_basedtl including all); insert into nb.nb_basedtl_copy select * from nb.nb_basedtl where id =11658502; ERROR: duplicate key value violates unique constraint "nb_basedtl_copy_pkey" DETAIL: Key (id)=(11658502) already exists. ********** Error ********** wbr, Sergey
Re: BUG #14940: Duplicated records inspite of primary key and uniqueconstraint
От
Tomas Vondra
Дата:
On 12/18/2017 04:28 PM, Сергей А. Фролов wrote: > Hi, > > I have got a VirtualBox with Debian 8.3 and have installed PG 9.6 and > amcheck: > > select version () > "PostgreSQL 9.6.6 on i686-pc-linux-gnu, compiled by gcc (Debian > 4.9.2-10) 4.9.2, 32-bit" > > No errors were raised by amcheck: > SELECT bt_index_check(index => indexrelid, heapallindexed => > true),bt_index_parent_check(index =>indexrelid), relname, indexrelname > FROM pg_stat_all_indexes > WHERE indexrelname in ('pk_nb_basedtl','unq_nb_basedtl_basenorm'); > > "";"";"nb_basedtl";"pk_nb_basedtl" > "";"";"nb_basedtl";"unq_nb_basedtl_basenorm" > > BUT duplicate rows were restored via pg_dump/pg_restore. > select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id > =11658502; > > (0,49);5513;0;11658502;269;46203 > (0,49);5518;0;11658502;269;46203 > (0,49);5519;0;11658502;269;46203 > So, can you create a reproducer? That is, share the pg_dump export (for this one table) which we could use to reproduce the issue? That would make investigation so much simpler. regard -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #14940: Duplicated records inspite of primary key and unique constraint
От
Peter Geoghegan
Дата:
You should probably use the new heapallindexed option, from the github version of amcheck.
--
Peter Geoghegan
(Sent from my phone)
Peter Geoghegan
(Sent from my phone)