\timing on
select version();
set work_mem = '40MB';
\set ON_ERROR_STOP

-- Create and populate the table
--------------------------------
create table t1 (
  c1    integer    not null,
  c2    integer    not null,
  c3    integer    not null,
  c4    integer    not null,
  c5    integer    not null,
  c6    integer    not null,
  c7    integer    not null,
  c8    integer    not null,
primary key (c1,c2,c3,c4,c5,c6,c7)                                   -- line to comment for 6 columns pkey
--primary key (c1,c2,c3,c4,c5,c6)                                    -- line to uncomment for 6 columns pkey
);
insert into t1
  select i, 2, 3, 4, 5, 6, 7, 8
    from generate_series (1,100000) i;

-- Setup the logging mechanism
------------------------------
create table t1_log (
  c1    integer    not null,
  c2    integer    not null,
  c3    integer    not null,
  c4    integer    not null,
  c5    integer    not null,
  c6    integer    not null,
  c7    integer    not null,
  c8    integer    not null,
  verb  varchar(3) not null, 
  tuple varchar(3) not null,
  gid   bigserial  not null
);
create unique index on t1_log(gid, tuple);

CREATE OR REPLACE FUNCTION t1_log_fnct() RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    INSERT INTO t1_log SELECT OLD.*, 'DEL', 'OLD';
    RETURN OLD;
  ELSIF (TG_OP = 'UPDATE') THEN
    INSERT INTO t1_log SELECT OLD.*, 'UPD', 'OLD';
    INSERT INTO t1_log SELECT NEW.*, 'UPD', 'NEW', lastval();
    RETURN NEW;
  ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO t1_log SELECT NEW.*, 'INS', 'NEW';
    RETURN NEW;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER t1_log_trg
  AFTER INSERT OR UPDATE OR DELETE ON t1
  FOR EACH ROW EXECUTE PROCEDURE t1_log_fnct();

-- Generate table changes
-------------------------
insert into t1
  select c1, 0, c3, c4, c5, c6, c7, c8
    from t1 where c1 % 10 = 0;

-- 3 cases when the problem disappears
--delete from t1 where c1 = 1;
--update t1 set c2 = -2 where c1 = 2;
--alter table t1 disable trigger t1_log_trg;

vacuum analyze;

select tuple, count(*) from t1_log group by 1;
select * from pg_stats where tablename = 't1_log' and attname = 'tuple';

-- Cancel table's updates
-------------------------

CREATE TEMP TABLE tmp AS
  SELECT c1,c2,c3,c4,c5,c6,
         c7,                                                         -- line to comment for 6 columns pkey
         min(gid) as gid
    FROM t1_log
    WHERE gid > 0
    GROUP BY c1,c2,c3,c4,c5,c6
            ,c7                                                      -- line to comment for 6 columns pkey
;

DELETE FROM ONLY t1 USING tmp
    WHERE t1.c1 = tmp.c1 AND t1.c2 = tmp.c2 AND t1.c3 = tmp.c3
      AND t1.c4 = tmp.c4 AND t1.c5 = tmp.c5 AND t1.c6 = tmp.c6
      AND t1.c7 = tmp.c7                                             -- line to comment for 6 columns pkey
;

-- 3 identified workarounds
--create index on tmp(gid);
--delete from pg_statistic s 
--  using pg_class c, pg_namespace n, pg_attribute a 
--  where starelid = c.oid and c.relnamespace = n.oid and staattnum = a.attnum and attrelid = c.oid
--    and relname = 't1_log' and nspname = 'public' and attname = 'tuple';
set enable_nestloop  = false;

select * from pg_stats where tablename = 't1_log' and attname = 'tuple';

-- The faulting statement
explain analyze
INSERT INTO t1
  SELECT t1_log.c1,t1_log.c2,t1_log.c3,t1_log.c4,t1_log.c5,t1_log.c6,t1_log.c7,t1_log.c8
    FROM t1_log, tmp
    WHERE t1_log.c1 = tmp.c1 AND t1_log.c2 = tmp.c2 AND t1_log.c3 = tmp.c3
      AND t1_log.c4 = tmp.c4 AND t1_log.c5 = tmp.c5 AND t1_log.c6 = tmp.c6
      AND t1_log.c7 = tmp.c7                                         -- line to comment for 6 columns pkey
      AND t1_log.gid = tmp.gid AND t1_log.tuple = 'OLD';

