Thread: Add support for data change delta tables

Add support for data change delta tables

From
PavelTurk
Date:
Hello all,

Currently PostgreSQL doesn't support data change delta tables. For example, it doesn't support this type of query:

SELECT * FROM NEW TABLE (
     INSERT INTO phone_book
     VALUES ( 'Peter Doe', '555-2323' )
) AS t

PostgreSQL has RETURNING that provides only a subset of this functionality.

So I suggest to add support for data change delta tables. Because this feature is more powerful and it is included
in the SQL Standard.

Best regards, Pavel



Re: Add support for data change delta tables

From
Vik Fearing
Date:
On 1/13/24 12:41, PavelTurk wrote:
> Hello all,

Hi Pavel!

> Currently PostgreSQL doesn't support data change delta tables. For 
> example, it doesn't support this type of query:
> 
> SELECT * FROM NEW TABLE (
>      INSERT INTO phone_book
>      VALUES ( 'Peter Doe', '555-2323' )
> ) AS t


Correct.  We do not yet support that.


> PostgreSQL has RETURNING that provides only a subset of this functionality.


I think that because of the way postgres is designed, it will only ever 
provide a subset of that functionality anyway.  Other people know more 
of the internals than I do, but I don't think we can easily distinguish 
between NEW TABLE and FINAL TABLE.

Unfortunately, your example does not show how postgres is inadequate.

For example,

     INSERT INTO t1 (c1)
         SELECT c2
         FROM OLD TABLE (
             DELETE FROM t2
             WHERE ...
         ) AS t

can be written as

     WITH
     old_table (c2) AS (
         DELETE FROM t2
         WHERE ...
         RETURNING c2
     )
     INSERT INTO t1 (c1) TABLE old_table


> So I suggest to add support for data change delta tables. Because this 
> feature is more powerful and it is included
> in the SQL Standard.


It is indeed included in the SQL Standard, but is it really more powerful?

Consider this example which is currently not implemented but could be, 
and compare it to the standard where such a query could not be possible 
at all:

     UPDATE t
     SET a = ...
     WHERE ...
     RETURNING OLD.a, NEW.a, FINAL.a


All this being said, I would love to see data change delta tables 
implemented per-spec in PostgreSQL; in addition to improving the 
RETURNING clause.  I believe I have heard that we can't just do a 
syntactic transformation because the trigger execution order is not the 
same, but I would have to research that.
-- 
Vik Fearing