Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE t
(
  id        SERIAL        PRIMARY KEY,
  oi        INTEGER       NOT NULL,     -- some random integer
  url       VARCHAR (265) NOT NULL,     -- whatever...
  epok      INTEGER       NOT NULL,  -- CHECK epok > xxxx AND epok <= yyyyy
  txn_id    INTEGER
);
CREATE TABLE t_archiv
(
  id     INTEGER,
  oi     INTEGER NOT NULL,
  url    VARCHAR (265) NOT NULL,
  epok   INTEGER NOT NULL,  -- CHECK epok
  txn_id INTEGER
);
SELECT EXTRACT(EPOCH FROM '2021-05-14 07:22:00'::TIMESTAMP);


date_part


1620976920


INSERT INTO t (oi, url, epok) VALUES
(1, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-14 07:22:00'::TIMESTAMP)),
(1, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-13 07:22:00'::TIMESTAMP)),
(1, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-12 06:22:00'::TIMESTAMP)),
(2, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-11 06:22:00'::TIMESTAMP)),
(2, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-10 05:22:00'::TIMESTAMP)),
(2, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-09 05:22:00'::TIMESTAMP)),
(3, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-08 04:22:00'::TIMESTAMP)),
(3, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-07 04:22:00'::TIMESTAMP)),
(3, 'https://www.url.ie', EXTRACT(EPOCH FROM '2021-05-06 03:22:00'::TIMESTAMP)),

-- Below here to be deleted...

(4, 'https://www.url.ie', EXTRACT(EPOCH FROM '2020-09-09 22:00:12'::TIMESTAMP)),
(4, 'https://www.url.ie', EXTRACT(EPOCH FROM '2020-09-09 21:00:23'::TIMESTAMP)), 
(4, 'https://www.url.ie', EXTRACT(EPOCH FROM '2020-09-09 20:00:33'::TIMESTAMP)),
(5, 'https://www.url.ie', EXTRACT(EPOCH FROM '2020-09-09 19:00:59'::TIMESTAMP));
13 rows affected

SELECT * FROM t;
SELECT * FROM t_archiv;


id
oi
url
epok
txn_id


1
1
https://www.url.ie
1620976920
null

2
1
https://www.url.ie
1620890520
null

3
1
https://www.url.ie
1620800520
null

4
2
https://www.url.ie
1620714120
null

5
2
https://www.url.ie
1620624120
null

6
2
https://www.url.ie
1620537720
null

7
3
https://www.url.ie
1620447720
null

8
3
https://www.url.ie
1620361320
null

9
3
https://www.url.ie
1620271320
null

10
4
https://www.url.ie
1599688812
null

11
4
https://www.url.ie
1599685223
null

12
4
https://www.url.ie
1599681633
null

13
5
https://www.url.ie
1599678059
null


-- Just testing...

SELECT EXTRACT(EPOCH FROM NOW()) - epok FROM t;


?column?


29311.514487028122

115711.51448702812

205711.51448702812

292111.5144870281

382111.5144870281

468511.5144870281

558511.5144870281

644911.5144870281

734911.5144870281

21317419.514487028

21321008.514487028

21324598.514487028

21328172.514487028


SELECT id
FROM t
WHERE EXTRACT(EPOCH FROM NOW())::INT - epok::INT > 15613200;  -- 15613200 is 6 months!


id


10

11

12

13


INSERT INTO t_archiv
(    
  SELECT * 
  FROM t
  WHERE EXTRACT(EPOCH FROM NOW()) - epok > 15613200
)
RETURNING id;


id


10

11

12

13


SELECT * FROM t_archiv;


id
oi
url
epok
txn_id


10
4
https://www.url.ie
1599688812
null

11
4
https://www.url.ie
1599685223
null

12
4
https://www.url.ie
1599681633
null

13
5
https://www.url.ie
1599678059
null


DELETE FROM t_archiv;
4 rows affected

DELETE FROM t
WHERE id IN
(
  INSERT INTO t_archiv
  (    
    SELECT *
    FROM t
    WHERE EXTRACT(EPOCH FROM NOW()) - epok > 15613200
  )
  RETURNING id
);
ERROR:  syntax error at or near "INTO"
LINE 4:   INSERT INTO t_archiv
                 ^


DELETE FROM t
WHERE id IN

  INSERT INTO t_archiv
  (    
    SELECT *
    FROM t
    WHERE EXTRACT(EPOCH FROM NOW()) - epok > 15613200
  )
  RETURNING id;
ERROR:  syntax error at or near "INSERT"
LINE 4:   INSERT INTO t_archiv
          ^


SELECT * FROM t_archiv;
SELECT * FROM t;


id
oi
url
epok
txn_id


1
1
https://www.url.ie
1620976920
null

2
1
https://www.url.ie
1620890520
null

3
1
https://www.url.ie
1620800520
null

4
2
https://www.url.ie
1620714120
null

5
2
https://www.url.ie
1620624120
null

6
2
https://www.url.ie
1620537720
null

7
3
https://www.url.ie
1620447720
null

8
3
https://www.url.ie
1620361320
null

9
3
https://www.url.ie
1620271320
null

10
4
https://www.url.ie
1599688812
null

11
4
https://www.url.ie
1599685223
null

12
4
https://www.url.ie
1599681633
null

13
5
https://www.url.ie
1599678059
null


DELETE FROM t y
WHERE y.id IN
(
  INSERT INTO t_archiv
  (    
    SELECT x.*
    FROM t x
    WHERE EXTRACT(EPOCH FROM NOW()) - x.epok > 15613200
  )
  RETURNING x.id  -- tried various aliasing strategies..
);
ERROR:  syntax error at or near "INTO"
LINE 4:   INSERT INTO t_archiv
                 ^


DELETE FROM t
WHERE id IN
(
SELECT id FROM
(
  INSERT INTO t_archiv
  (    
    SELECT *
    FROM t
    WHERE EXTRACT(EPOCH FROM NOW()) - epok > 15613200
  )
  RETURNING id
)
);
ERROR:  syntax error at or near "INTO"
LINE 6:   INSERT INTO t_archiv
                 ^


SELECT id FROM
(
  INSERT INTO t_archiv
  (    
    SELECT *
    FROM t
    WHERE EXTRACT(EPOCH FROM NOW()) - epok > 15613200
  )
  RETURNING id

);
ERROR:  syntax error at or near "INTO"
LINE 3:   INSERT INTO t_archiv
                 ^

id	oi	url	epok	txn_id
1	1	https://www.url.ie	1620976920	null
2	1	https://www.url.ie	1620890520	null
3	1	https://www.url.ie	1620800520	null
4	2	https://www.url.ie	1620714120	null
5	2	https://www.url.ie	1620624120	null
6	2	https://www.url.ie	1620537720	null
7	3	https://www.url.ie	1620447720	null
8	3	https://www.url.ie	1620361320	null
9	3	https://www.url.ie	1620271320	null
10	4	https://www.url.ie	1599688812	null
11	4	https://www.url.ie	1599685223	null
12	4	https://www.url.ie	1599681633	null
13	5	https://www.url.ie	1599678059	null