Question About Native Support for SQL:2011 Temporal Tables in PostgreSQL
Hi Postgres Community,
I wanted to ask about support for temporal tables as defined in the SQL:2011 standard. Right now, I know that we can get similar functionality using extensions like pg_temporal, which has been really useful. But for those of us running PostgreSQL on managed platforms like AWS RDS (where adding custom extensions isn’t an option), it creates a bit of a challenge.
Here are a few questions I’d love your thoughts on:
1.
Are there any plans or discussions about adding native support for SQL:2011 temporal tables, so we don’t need extensions?
2.
What are the main reasons behind needing an extension for this feature? Is it tough to build directly into PostgreSQL’s core?
3.
For those who can’t use extensions (like on AWS RDS), is it practical to build temporal table features using only RAW SQL. I'm aware of the Nearform trigger solution but I'd really love the syntactical sugar "with SYSTEM VERISON" gives? If so, are there any best practices or tips for recreating some of that extension-like functionality?
Having native temporal table support would be a huge help for users needing built-in tools for audits, historical tracking, and meeting data compliance needs. I think adding these features natively could make PostgreSQL even more powerful and flexible for different use cases.
I've been looking into https://github.com/xocolatl/periods but I can't tell if its an extension or part of the regular deployment.
Thanks so much for taking the time to read this and share your insights. Looking forward to hearing what you all think!
Dave
On Mon, Nov 11, 2024 at 6:23 AM David Lynam <davidlynam1@hotmail.co.uk>
wrote:
Are there any plans or discussions about adding native support for
SQL:2011 temporal tables, so we don’t need extensions?
No concrete plans I've heard of (but see below). For the record, "so we
don't need extensions" is not a winning argument. Postgres is designed to
be extensible.
What are the main reasons behind needing an extension for this feature?
Is it tough to build directly into PostgreSQL’s core?
By default, Postgres focuses on safely storing and retrieving your data in
a relational database system. Having to use extensions to go beyond this in
various ways is considered a feature, not a limitation. Things do
eventually make it into core, but there are a number of prereqs that need
to happen first. Being "tough" technically is only part of the equation.
Other things, off the top of my head:
* is it something many people will benefit from?
* is it something that will not impact the people not using it?
* how will it interact with other parts of the system?
* is it worth the added lines of code, complexity, and maintenance costs?
* is it already handled quite well as an extension?
* are there resources (i.e. people) available to champion it and maintain
it in perpetuity?
For those who can’t use extensions (like on AWS RDS), is it practical to
build temporal table features using only RAW SQL. I'm aware of the Nearform
trigger solution but I'd really love the syntactical sugar "with SYSTEM
VERISON" gives? If so, are there any best practices or tips for recreating
some of that extension-like functionality?
I cannot speak to Nearform et. al., but for the record here, AWS RDS does
support extensions - and a lot of them. No, you cannot install your own
custom extensions, but that's the tradeoff for using a managed service.
Since you are paying them money, however, you can certainly ask if they
will make particular extensions available.
Having native temporal table support would be a huge help for users needing
built-in tools for audits, historical tracking, and meeting data compliance
needs. I think adding these features natively could make PostgreSQL even
more powerful and flexible for different use cases.
It's certainly been discussed over the years. Nobody denies it can be
useful, but putting things in core is a high bar. You can always argue your
case on pgsql-hackers
I've been looking into https://github.com/xocolatl/periods but I can't
tell if its an extension or part of the regular deployment.
Looks like an extension to me.
Cheers,
Greg
Greg Sabino Mullane <htamfids@gmail.com> writes:
On Mon, Nov 11, 2024 at 6:23 AM David Lynam <davidlynam1@hotmail.co.uk>
wrote:Are there any plans or discussions about adding native support for
SQL:2011 temporal tables, so we don’t need extensions?
No concrete plans I've heard of (but see below).
See this long-running thread:
/messages/by-id/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com
regards, tom lane