Re: BUG #2379: Duplicate pkeys in table - Mailing list pgsql-bugs

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"...__@!inte
  0fb0: 726e616c 5f64656c 69766572 795f7573  rnal_delivery_us
  0fc0: 65722140 5f5f0000 00000000 00000000  er!@__..........
  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"...__@!inte
  0aec: 726e616c 5f64656c 69766572 795f7573  rnal_delivery_us
  0afc: 65722140 5f5f0000 00000000 00000000  er!@__..........
  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"...__@!inte
  1e8c: 726e616c 5f64656c 69766572 795f7573  rnal_delivery_us
  1e9c: 65722140 5f5f0000 00000000 00000000  er!@__..........
  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"...__@!inte
  1fb8: 726e616c 5f64656c 69766572 795f7573  rnal_delivery_us
  1fc8: 65722140 5f5f0000 00000000 00000000  er!@__..........
  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"...__@!inte
  1fb8: 726e616c 5f64656c 69766572 795f7573  rnal_delivery_us
  1fc8: 65722140 5f5f0000 00000000 00000000  er!@__..........
  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"...__@!inte
  1fb8: 726e616c 5f64656c 69766572 795f7573  rnal_delivery_us
  1fc8: 65722140 5f5f0000 00000000 00000000  er!@__..........
  1fd8: 01000000 00000000 3c307819 0e1fa441  ........<0x....A
  1fe8: 3c307819 0e1fa441 3c307819 0e1fa441  <0x....A<0x....A
  1ff8: 00000000 00000000                    ........