Thread: RETURNING, CTEs and TRANSACTION ISOLATION levels...

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...

=?UTF-8?B?UMOzbCBVYSBMYW/DrW5lY2jDoWlu?= <linehanp@tcd.ie> writes:
> 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;

Try putting the INSERT ... RETURNING in a CTE (WITH clause).
We don't support putting RETURNING into any random place
where a sub-select can appear, because the semantics would
be too unpredictable.  But a WITH has guaranteed evaluate-once
semantics, so DML with RETURNING is OK there.

(Maybe something more specific than a syntax error would
be nice ...)

            regards, tom lane

One little comment.

On Fri, May 14, 2021 at 5:33 PM Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:
> 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;

...

The complex stuff about withs/cte etc.. has already been answered by
more knowledgeable people.

I just wanted to point it seems you are trying to move some records
from a table to an archive.

IIRC this can be done in an easier way doing something like ...

WITH rows_to_move AS (
    DELETE  FROM t
    WHERE EXTRACT(EPOCH FROM NOW()) - epok > 15613200
)
INSERT into T_arch ( SELECT * FROM rows_to_move);

( I remember doing a similar thing, but returning * from the insert as
I also needed to do some reporting on the moved rows ).

This is, IMHO, clearer as you only have one WHERE, you only mention
each table once, it reads like "take old rows from t and put them into
t_archive", it works without an unique id field ( I assume id is a pk,
otherwise your query may break havoc ).

Also, the condition could probably be better written as

EXTRACT(EPOCH FROM NOW()) - 15613200 > epok

or even reversed ( epok < EXTRACT(EPOCH FROM NOW()) - 15613200 )

I haven't tried, but if you some day index epok ( or already had ) the
pattern field-op-constant is normally more readily recognized by
optimizers ( probably they get it anyway ).

Francisco Olarte.