RETURNING, CTEs and TRANSACTION ISOLATION levels... - Mailing list pgsql-general

Hi all,

I was trying to do this:

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

see the fiddle here:

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=d3cb601af2e4c99a32c56df03f97a5f2

This works, returning 4 ids

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

but I can't seem to use them farther up the pyramid as it were.

I tried all sorts of variants - bracketing, aliases... can't seem to
get it to work- I get the same error:

ERROR: syntax error at or near "INSERT" LINE 4: INSERT INTO t_archiv

I even tried this:

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
)
);

Same error - see fiddle.


I was able to do it by chaining CTEs - but I wanted to be sure that
when chaining CTEs, all work done in a statement with multiple
modifications to data was done within the same transaction - this is
what I thought my SQL would do without using CTEs.

So, I concocted this (2 of the CTEs are for testing purposes)

WITH cte1 (id, txn) AS
(
  SELECT id,  txid_current() AS txn
  FROM tx
  WHERE EXTRACT(EPOCH FROM NOW()) - epok > 15613200
),
cte_test_1 (const, curr_tran) AS  -- <<=== JUST FOR TEST PUPOSES
cte_test_1 inserts a txid_current
(                          -- which is also later INSERTed and
SELECTed at the end - const = 1
  INSERT INTO txn_test
  SELECT 1 AS const, txid_current() AS curr_tran FROM cte1
  RETURNING const, txid_current()
),
cte2 (id, txn2) AS
(
  INSERT INTO tx_archiv
  SELECT id, oi, url, epok, txid_current() AS txn2  -- 2nd INSERT of
txid_current()
    FROM tx WHERE id IN (SELECT id FROM cte1)
  RETURNING id, txid_current()
),
cte_test_2 (const, curr_tran) AS  -- <<=== JUST FOR TEST PUPOSES
cte_test_1 inserts a txid_current
(                          -- which is also later INSERTed and
SELECTed at the end - const = 2
  INSERT INTO txn_test
  SELECT 2 AS const, txid_current() AS curr_tran FROM cte1
  RETURNING const, txid_current()
),
cte3 (id, txn3) AS
(
  DELETE FROM tx WHERE id IN
  (SELECT id FROM cte2)
  RETURNING id, txid_current()
)
SELECT * FROM cte3;:

As you'll be able to see from the results in the fiddle - there are
two separate INSERTs (const = 1 and const = 2) with the same
txid_current() - so the whole CTE is just one transaction - so that's
OK I suppose.

Fiddle here:

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=59ea9be8f3f70f5c2158a1f79b94d87b

I'm just wondering if there's any way that the RETURNING can be made
to work? It's more intuitive (at least for me) and elegant (again
IMHO) and less code.

Should you require any further information, please don't hesitate to
contact me...

TIA and rgs,


Pól...