pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Add support for data change delta tables
9+ messages / 5 participants
[nested] [flat]

* Add support for data change delta tables
@ 2024-01-15 10:00 PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:05 ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-15 10:44 ` Re: Add support for data change delta tables Thomas Kellerer <shammat@gmx.net>
  0 siblings, 2 replies; 9+ messages in thread

From: PavelTurk @ 2024-01-15 10:00 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Add support for data change delta tables
  2024-01-15 10:00 Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
@ 2024-01-15 10:05 ` Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-15 10:11   ` Re: Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Pavel Stehule @ 2024-01-15 10:05 UTC (permalink / raw)
  To: PavelTurk <pavelturk2000@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Hi

po 15. 1. 2024 v 11:00 odesílatel PavelTurk <pavelturk2000@gmail.com>
napsal:

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

This is the wrong mailing list - probably you should send your proposal to
pgsql-hackers.

https://wiki.postgresql.org/wiki/Developer_FAQ

The community development is based on patches. If you propose some
feature,  usually you should implement it. Probably nobody will implement
it instand you.

Regards

Pavel



>
> Best regards, Pavel
>
>
>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Add support for data change delta tables
  2024-01-15 10:00 Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:05 ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
@ 2024-01-15 10:11   ` PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:17     ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: PavelTurk @ 2024-01-15 10:11 UTC (permalink / raw)
  To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-general@lists.postgresql.org


On 1/15/24 12:05 PM, Pavel Stehule wrote:
> Hi
>
> po 15. 1. 2024 v 11:00 odesílatel PavelTurk <pavelturk2000@gmail.com <mailto:pavelturk2000@gmail.com>> napsal:
>
>     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.
>
>
> This is the wrong mailing list - probably you should send your proposal to pgsql-hackers.

I sent message to pgsql-hackers https://www.postgresql.org/message-id/b85900eb-9e3c-e358-aa8f-5a27b30c17e7%40gmail.com
but got no answer. I decided that my message was ignored because I used wrong mailing list.

> The community development is based on patches. If you propose some feature,  usually you should implement it. Probably nobody will implement it instand you.

I am Java developer - I wish, but I can't

Best regards, Pavel

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Add support for data change delta tables
  2024-01-15 10:00 Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:05 ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-15 10:11   ` Re: Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
@ 2024-01-15 10:17     ` Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-15 10:27       ` Re: Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Pavel Stehule @ 2024-01-15 10:17 UTC (permalink / raw)
  To: PavelTurk <pavelturk2000@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

po 15. 1. 2024 v 11:11 odesílatel PavelTurk <pavelturk2000@gmail.com>
napsal:

>
> On 1/15/24 12:05 PM, Pavel Stehule wrote:
>
> Hi
>
> po 15. 1. 2024 v 11:00 odesílatel PavelTurk <pavelturk2000@gmail.com>
> napsal:
>
>> 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.
>>
>
> This is the wrong mailing list - probably you should send your proposal to
> pgsql-hackers.
>
>
> I sent message to pgsql-hackers
> https://www.postgresql.org/message-id/b85900eb-9e3c-e358-aa8f-5a27b30c17e7%40gmail.com
> but got no answer. I decided that my message was ignored because I used
> wrong mailing list.
>
> The community development is based on patches. If you propose some
> feature,  usually you should implement it. Probably nobody will implement
> it instand you.
>
>
> I am Java developer - I wish, but I can't
>

I am sorry, but you cannot expect, so somebody will accept your proposal
extra seriously. Proposing some feature is a simple thing, but implementing
some not trivial feature requires hundreds of hours of development.

You can check the work on features for Postgres
https://commitfest.postgresql.org/ and I don't think so there is a lot of
free capacity for implementing some new features partially redundant to
already supported features.

Regards

Pavel



> Best regards, Pavel
>
>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Add support for data change delta tables
  2024-01-15 10:00 Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:05 ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-15 10:11   ` Re: Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:17     ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
@ 2024-01-15 10:27       ` PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:40         ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: PavelTurk @ 2024-01-15 10:27 UTC (permalink / raw)
  To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-general@lists.postgresql.org


On 1/15/24 12:17 PM, Pavel Stehule wrote:
>
>
> po 15. 1. 2024 v 11:11 odesílatel PavelTurk <pavelturk2000@gmail.com <mailto:pavelturk2000@gmail.com>> napsal:
>
>
>     On 1/15/24 12:05 PM, Pavel Stehule wrote:
>>     Hi
>>
>>     po 15. 1. 2024 v 11:00 odesílatel PavelTurk <pavelturk2000@gmail.com <mailto:pavelturk2000@gmail.com>> napsal:
>>
>>         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.
>>
>>
>>     This is the wrong mailing list - probably you should send your proposal to pgsql-hackers.
>
>     I sent message to pgsql-hackers https://www.postgresql.org/message-id/b85900eb-9e3c-e358-aa8f-5a27b30c17e7%40gmail.com <https://www.postgresql.org/message-id/b85900eb-9e3c-e358-aa8f-5a27b30c17e7%40gmail.com;
>     but got no answer. I decided that my message was ignored because I used wrong mailing list.
>
>>     The community development is based on patches. If you propose some feature,  usually you should implement it. Probably nobody will implement it instand you.
>
>     I am Java developer - I wish, but I can't
>
>
> I am sorry, but you cannot expect, so somebody will accept your proposal extra seriously. Proposing some feature is a simple thing, but implementing some not trivial feature requires hundreds of hours of development.
>

To tell the truth I find it rather strange. I know that there is a big difference between proposing some feature and implementing it. But all projects I came across with work this way -
you as a user come and propose a feature. The developers of the project consider your issue and either accept it to implement or decline it.

> You can check the work on features for Postgres https://commitfest.postgresql.org/ <https://commitfest.postgresql.org/; and I don't think so there is a lot of free capacity for implementing some new features partially redundant to already supported features.
>
> Regards
>
> Pavel
>
>
>
>     Best regards, Pavel
>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Add support for data change delta tables
  2024-01-15 10:00 Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:05 ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-15 10:11   ` Re: Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:17     ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-15 10:27       ` Re: Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
@ 2024-01-15 10:40         ` Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-16 19:56           ` Re: Add support for data change delta tables Jim Nasby <jim.nasby@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Pavel Stehule @ 2024-01-15 10:40 UTC (permalink / raw)
  To: PavelTurk <pavelturk2000@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

po 15. 1. 2024 v 11:27 odesílatel PavelTurk <pavelturk2000@gmail.com>
napsal:

>
> On 1/15/24 12:17 PM, Pavel Stehule wrote:
>
>
>
> po 15. 1. 2024 v 11:11 odesílatel PavelTurk <pavelturk2000@gmail.com>
> napsal:
>
>>
>> On 1/15/24 12:05 PM, Pavel Stehule wrote:
>>
>> Hi
>>
>> po 15. 1. 2024 v 11:00 odesílatel PavelTurk <pavelturk2000@gmail.com>
>> napsal:
>>
>>> 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.
>>>
>>
>> This is the wrong mailing list - probably you should send your proposal
>> to pgsql-hackers.
>>
>>
>> I sent message to pgsql-hackers
>> https://www.postgresql.org/message-id/b85900eb-9e3c-e358-aa8f-5a27b30c17e7%40gmail.com
>> but got no answer. I decided that my message was ignored because I used
>> wrong mailing list.
>>
>> The community development is based on patches. If you propose some
>> feature,  usually you should implement it. Probably nobody will implement
>> it instand you.
>>
>>
>> I am Java developer - I wish, but I can't
>>
>
> I am sorry, but you cannot expect, so somebody will accept your proposal
> extra seriously. Proposing some feature is a simple thing, but implementing
> some not trivial feature requires hundreds of hours of development.
>
>
> To tell the truth I find it rather strange. I know that there is a big
> difference between proposing some feature and implementing it. But all
> projects I came across with work this way -
> you as a user come and propose a feature. The developers of the project
> consider your issue and either accept it to implement or decline it.
>

PostgreSQL is not a project where somebody says, you will do this, and you
will do this, in this and this priority. There are a lot of nice features
from standard - SQL standard is big, but a) you have to have funds to pay
the developers, b) you have to have developers with free time who want to
do free work. Sure, you can write a proposal, but without a) money or b)
your hard work is not a big possibility so your proposal will be
implemented in the next few years. With the patch the situation is
different. Then developers can see, so it is possible to implement, and how
much code (and work) needs it.



> You can check the work on features for Postgres
> https://commitfest.postgresql.org/ and I don't think so there is a lot of
> free capacity for implementing some new features partially redundant to
> already supported features.
>
> Regards
>
> Pavel
>
>
>
>> Best regards, Pavel
>>
>>
>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Add support for data change delta tables
  2024-01-15 10:00 Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:05 ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-15 10:11   ` Re: Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:17     ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
  2024-01-15 10:27       ` Re: Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:40         ` Re: Add support for data change delta tables Pavel Stehule <pavel.stehule@gmail.com>
@ 2024-01-16 19:56           ` Jim Nasby <jim.nasby@gmail.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Jim Nasby @ 2024-01-16 19:56 UTC (permalink / raw)
  To: Pavel Stehule <pavel.stehule@gmail.com>; PavelTurk <pavelturk2000@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On 1/15/24 4:40 AM, Pavel Stehule wrote:
> PostgreSQL is not a project where somebody says, you will do this, and 
> you will do this, in this and this priority. There are a lot of nice 
> features from standard - SQL standard is big, but a) you have to have 
> funds to pay the developers, b) you have to have developers with free 
> time who want to do free work. Sure, you can write a proposal, but 
> without a) money or b) your hard work is not a big possibility so your 
> proposal will be implemented in the next few years. With the patch the 
> situation is different. Then developers can see, so it is possible to 
> implement, and how much code (and work) needs it.

And in this scenario, the big thing that a patch provides is clarity on 
what you're actually trying to accomplish. Your original ask is frankly 
as clear as mud to me - I have no clue what the syntax you showed is 
supposed to be doing.

If you were to ask about something concrete in plain English (vs some 
unspecified SQL syntax) then you'd be much more likely to at least get 
some kind of response about your idea. It's certainly possible to 
convince people on -hackers to work on something that you'd like to see 
done, but the base prerequisite for that is you have to communicate what 
you're actually looking for.
-- 
Jim Nasby, Data Architect, Austin TX






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Add support for data change delta tables
  2024-01-15 10:00 Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
@ 2024-01-15 10:44 ` Thomas Kellerer <shammat@gmx.net>
  2024-01-15 16:16   ` Re: Add support for data change delta tables Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Thomas Kellerer @ 2024-01-15 10:44 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

PavelTurk schrieb am 15.01.2024 um 11:00:
> 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.

I don't know that syntax, but it seems to me, it's basically this:

with t as (
   INSERT INTO phone_book
   VALUES ( 'Peter Doe', '555-2323' )
   returning *
)
select *
from t;

  


^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Add support for data change delta tables
  2024-01-15 10:00 Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
  2024-01-15 10:44 ` Re: Add support for data change delta tables Thomas Kellerer <shammat@gmx.net>
@ 2024-01-15 16:16   ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Adrian Klaver @ 2024-01-15 16:16 UTC (permalink / raw)
  To: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org

On 1/15/24 02:44, Thomas Kellerer wrote:
> PavelTurk schrieb am 15.01.2024 um 11:00:
>> 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.
> 
> I don't know that syntax, but it seems to me, it's basically this:
> 
> with t as (
>     INSERT INTO phone_book
>     VALUES ( 'Peter Doe', '555-2323' )
>     returning *
> )
> select *
> from t;
> 


There is also transition relations:

https://www.postgresql.org/docs/current/sql-createtrigger.html

though that involves creating a trigger on a table.

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2024-01-16 19:56 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-15 10:00 Add support for data change delta tables PavelTurk <pavelturk2000@gmail.com>
2024-01-15 10:05 ` Pavel Stehule <pavel.stehule@gmail.com>
2024-01-15 10:11   ` PavelTurk <pavelturk2000@gmail.com>
2024-01-15 10:17     ` Pavel Stehule <pavel.stehule@gmail.com>
2024-01-15 10:27       ` PavelTurk <pavelturk2000@gmail.com>
2024-01-15 10:40         ` Pavel Stehule <pavel.stehule@gmail.com>
2024-01-16 19:56           ` Jim Nasby <jim.nasby@gmail.com>
2024-01-15 10:44 ` Thomas Kellerer <shammat@gmx.net>
2024-01-15 16:16   ` Adrian Klaver <adrian.klaver@aklaver.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox