Re: BUG #2379: Duplicate pkeys in table

Tom Lane wrote:
>> Updates happen regularly from many sources, but the procedure that does
>> the most updates is a trigger. Do you want to see that?
>>
>
> Please.
>
public | tg_update_qqq_date | "trigger"
| | mail | plpgsql |
Declare
uid bigint;
Begin
uid = (select owner_id from yyy m where m.f1 = NEW.f1);
if (uid <> 0 and not uid is null) then
update xxx set qqq_date = 'now' where id=uid;
end if;
Return NEW;
End; |

and there's also a rewrite rule:

zzz_update_r1 AS
ON UPDATE TO zzz DO INSTEAD UPDATE xxx SET f1 = new.f1
WHERE xxx.id = new.id
zzz_update_r2 AS
ON UPDATE TO zzz DO INSTEAD UPDATE xxx SET f2 = new.f2
WHERE xxx.id = new.id

> Also, if you care to run pg_filedump -i -F over the table, it'd be
> interesting to see the complete header info for each of these tuples.
>
obviously from different blocks (do you need more details?):

Item 7 -- Length: 168 Offset: 3920 (0x0f50) Flags: USED
XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872
Block Id: 0 linp Index: 7 Attributes: 34 Size: 36
infomask: 0x2913
(HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84
[4]: 0x00

0f50: 730ef601 14000000 00000000 d80ef601 s...............
0f60: 00000000 07002200 1329249f 807e8400 ......"..)$..~..
0f70: d37e0000 25600000 00000000 09000000 .~..%`..........
0f80: 00000000 00000000 00000000 00000000 ................
0f90: 00000000 00000000 04000000 12bcf968 ...............h
0fa0: d28fa741 22000000 5f5f4021 696e7465 ...A"(dot)(dot)(dot)__(at)!inte
0fb0: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us
0fc0: 65722140 5f5f0000 00000000 00000000 er!(at)__(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)
0fd0: 01000000 00000000 3c307819 0e1fa441 ........<0x....A
0fe0: 3c307819 0e1fa441 3c307819 0e1fa441 <0x....A<0x....A
0ff0: 00000000 00000000 ........

Item 27 -- Length: 168 Offset: 2700 (0x0a8c) Flags: USED
XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872
Block Id: 2 linp Index: 27 Attributes: 34 Size: 36
infomask: 0x2913
(HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84
[4]: 0x00

0a8c: 730ef601 14000000 00000000 d80ef601 s...............
0a9c: 00000200 1b002200 1329249f 807e8400 ......"..)$..~..
0aac: d37e0000 25600000 00000000 09000000 .~..%`..........
0abc: 00000000 00000000 00000000 00000000 ................
0acc: 00000000 00000000 04000000 12bcf968 ...............h
0adc: d28fa741 22000000 5f5f4021 696e7465 ...A"(dot)(dot)(dot)__(at)!inte
0aec: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us
0afc: 65722140 5f5f0000 00000000 00000000 er!(at)__(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)
0b0c: 01000000 00000000 3c307819 0e1fa441 ........<0x....A
0b1c: 3c307819 0e1fa441 3c307819 0e1fa441 <0x....A<0x....A
0b2c: 00000000 00000000 ........

Item 27 -- Length: 168 Offset: 7724 (0x1e2c) Flags: USED
XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872
Block Id: 58 linp Index: 27 Attributes: 34 Size: 36
infomask: 0x2913
(HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84
[4]: 0x00

1e2c: 730ef601 14000000 00000000 d80ef601 s...............
1e3c: 00003a00 1b002200 1329249f 807e8400 ..:..."..)$..~..
1e4c: d37e0000 25600000 00000000 09000000 .~..%`..........
1e5c: 00000000 00000000 00000000 00000000 ................
1e6c: 00000000 00000000 04000000 12bcf968 ...............h
1e7c: d28fa741 22000000 5f5f4021 696e7465 ...A"(dot)(dot)(dot)__(at)!inte
1e8c: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us
1e9c: 65722140 5f5f0000 00000000 00000000 er!(at)__(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)
1eac: 01000000 00000000 3c307819 0e1fa441 ........<0x....A
1ebc: 3c307819 0e1fa441 3c307819 0e1fa441 <0x....A<0x....A
1ecc: 00000000 00000000 ........

Item 28 -- Length: 168 Offset: 8024 (0x1f58) Flags: USED
XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872
Block Id: 60 linp Index: 28 Attributes: 34 Size: 36
infomask: 0x2913
(HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84
[4]: 0x00

1f58: 730ef601 14000000 00000000 d80ef601 s...............
1f68: 00003c00 1c002200 1329249f 807e8400 ..<..."..)$..~..
1f78: d37e0000 25600000 00000000 09000000 .~..%`..........
1f88: 00000000 00000000 00000000 00000000 ................
1f98: 00000000 00000000 04000000 12bcf968 ...............h
1fa8: d28fa741 22000000 5f5f4021 696e7465 ...A"(dot)(dot)(dot)__(at)!inte
1fb8: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us
1fc8: 65722140 5f5f0000 00000000 00000000 er!(at)__(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)
1fd8: 01000000 00000000 3c307819 0e1fa441 ........<0x....A
1fe8: 3c307819 0e1fa441 3c307819 0e1fa441 <0x....A<0x....A
1ff8: 00000000 00000000 ........

Item 3 -- Length: 168 Offset: 8024 (0x1f58) Flags: USED
XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872
Block Id: 69 linp Index: 3 Attributes: 34 Size: 36
infomask: 0x2913
(HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84
[4]: 0x00

1f58: 730ef601 14000000 00000000 d80ef601 s...............
1f68: 00004500 03002200 1329249f 807e8400 ..E..."..)$..~..
1f78: d37e0000 25600000 00000000 09000000 .~..%`..........
1f88: 00000000 00000000 00000000 00000000 ................
1f98: 00000000 00000000 04000000 12bcf968 ...............h
1fa8: d28fa741 22000000 5f5f4021 696e7465 ...A"(dot)(dot)(dot)__(at)!inte
1fb8: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us
1fc8: 65722140 5f5f0000 00000000 00000000 er!(at)__(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)
1fd8: 01000000 00000000 3c307819 0e1fa441 ........<0x....A
1fe8: 3c307819 0e1fa441 3c307819 0e1fa441 <0x....A<0x....A
1ff8: 00000000 00000000 ........

Item 27 -- Length: 168 Offset: 8024 (0x1f58) Flags: USED
XMIN: 32902771 CMIN: 20 XMAX: 33048159 CMAX|XVAC: 20
Block Id: 318 linp Index: 6 Attributes: 34 Size: 36
infomask: 0x2913
(HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84
[4]: 0x00

1f58: 730ef601 14000000 5f46f801 14000000 s......._F......
1f68: 00003e01 06002200 1329249f 807e8400 ..>..."..)$..~..
1f78: d37e0000 25600000 00000000 09000000 .~..%`..........
1f88: 00000000 00000000 00000000 00000000 ................
1f98: 00000000 00000000 04000000 12bcf968 ...............h
1fa8: d28fa741 22000000 5f5f4021 696e7465 ...A"(dot)(dot)(dot)__(at)!inte
1fb8: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us
1fc8: 65722140 5f5f0000 00000000 00000000 er!(at)__(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)(dot)
1fd8: 01000000 00000000 3c307819 0e1fa441 ........<0x....A
1fe8: 3c307819 0e1fa441 3c307819 0e1fa441 <0x....A<0x....A
1ff8: 00000000 00000000 ........