Обсуждение: 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)