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.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit

SHOW TRANSACTION ISOLATION LEVEL;

transaction_isolation

read committed

CREATE TABLE x (y INT, z VARCHAR (25));
INSERT INTO x VALUES (1, 'One'), (2, 'Two'), (3, 'Three'); 
3 rows affected

SELECT * FROM x;

y
z

1
One

2
Two

3
Three

START TRANSACTION;

SHOW TRANSACTION ISOLATION LEVEL;

SELECT TRANSACTION_TIMESTAMP(), CLOCK_TIMESTAMP();

-- do my 1st real SQL here - i.e. this SQL "sees" the data at clock_timestamp()

-- SELECT *
-- FROM api_log
-- WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0

SELECT PG_SLEEP(2);

SELECT * FROM x;

SELECT PG_SLEEP(2);

SELECT TRANSACTION_TIMESTAMP(), CLOCK_TIMESTAMP();

-- do my 2nd real SQL here - this SQL "sees" the data again at clock_timestamp()

--DELETE
-- FROM api_log
-- WHERE id in (SELECT id FROM data_to_move);

SHOW TRANSACTION ISOLATION LEVEL;

COMMIT;

transaction_isolation

read committed

transaction_timestamp
clock_timestamp

2021-05-13 16:44:23.926138+01
2021-05-13 16:44:23.926435+01

pg_sleep

y
z

1
One

2
Two

3
Three

pg_sleep

transaction_timestamp
clock_timestamp

2021-05-13 16:44:23.926138+01
2021-05-13 16:44:27.931208+01

transaction_isolation

read committed

SHOW TRANSACTION ISOLATION LEVEL;

transaction_isolation

read committed

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

SHOW TRANSACTION ISOLATION LEVEL;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT TRANSACTION_TIMESTAMP(), CLOCK_TIMESTAMP();

-- do my 1st real SQL here - i.e. this SQL "sees" the data at transaction_timestamp()
-- which remains the same throughout the transaction  - when SERIALIZABLE.

-- SELECT *
-- FROM api_log
-- WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0

SHOW TRANSACTION ISOLATION LEVEL;

SELECT PG_SLEEP(2);

SELECT * FROM x;

SELECT PG_SLEEP(2);

-- do my 2nd real SQL here - this SQL "sees" the data again at transaction_timestamp()
-- which remains the same throughout the transaction  - when SERIALIZABLE.

--DELETE
-- FROM api_log
-- WHERE id in (SELECT id FROM data_to_move);

SELECT TRANSACTION_TIMESTAMP(), CLOCK_TIMESTAMP();

SHOW TRANSACTION ISOLATION LEVEL;
COMMIT;

transaction_isolation

read committed

transaction_timestamp
clock_timestamp

2021-05-13 16:44:27.93243+01
2021-05-13 16:44:27.932636+01

transaction_isolation

serializable

pg_sleep

y
z

1
One

2
Two

3
Three

pg_sleep

transaction_timestamp
clock_timestamp

2021-05-13 16:44:27.93243+01
2021-05-13 16:44:31.939329+01

transaction_isolation

serializable