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

One little comment.

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