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