MIME-Version: 1.0
References: <DB9P194MB1825E606FD1457EC909D5BD0AA582@DB9P194MB1825.EURP194.PROD.OUTLOOK.COM>
In-Reply-To: <DB9P194MB1825E606FD1457EC909D5BD0AA582@DB9P194MB1825.EURP194.PROD.OUTLOOK.COM>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Sat, 23 Nov 2024 13:22:02 -0500
Message-ID: <CAKAnmmL6erFtY5QocedPVo9WKTuAHwQJOyLvtVP2tW4HRUuUow@mail.gmail.com>
Subject: Re: Question About Native Support for SQL:2011 Temporal Tables in PostgreSQL
To: David Lynam <davidlynam1@hotmail.co.uk>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009519c90627989541"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmL6erFtY5QocedPVo9WKTuAHwQJOyLvtVP2tW4HRUuUow%40mail.gmail.com>
Precedence: bulk

--0000000000009519c90627989541
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Nov 11, 2024 at 6:23=E2=80=AFAM 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=E2=80=99t 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=E2=80=99s 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=E2=80=99t use extensions (like on AWS RDS), is it practic=
al to
> build temporal table features using only RAW SQL. I'm aware of the Nearfo=
rm
> trigger solution but I'd really love the syntactical sugar "with SYSTEM
> VERISON" gives? If so, are there any best practices or tips for recreatin=
g
> 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 complian=
ce
> 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

--0000000000009519c90627989541
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">On Mon, Nov 11, 2024 at 6:23=E2=80=AFAM David Lynam &lt;<a=
 href=3D"mailto:davidlynam1@hotmail.co.uk">davidlynam1@hotmail.co.uk</a>&gt=
; wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Are there any=
 plans or discussions about adding native support for SQL:2011 temporal tab=
les, so we don=E2=80=99t need extensions?</blockquote><br>No concrete plans=
 I&#39;ve heard of (but see below). For the record, &quot;so we don&#39;t n=
eed extensions&quot; is not a winning argument. Postgres is designed to be =
extensible.<br><br>&gt; What are the main reasons behind needing an extensi=
on for this feature? Is it tough to build directly into PostgreSQL=E2=80=99=
s core?<br><br>By default, Postgres focuses on safely storing and retrievin=
g 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. Thi=
ngs do eventually make it into core, but there are a number of prereqs that=
 need to happen first. Being &quot;tough&quot; technically is only part of =
the equation. Other things, off the top of my head:<br><br><div>* is it som=
ething many people will benefit from?<br>* is it something that will not im=
pact the people not using it?<div>* how will it interact with other parts o=
f the system?<br>* is it worth the added lines of code, complexity, and mai=
ntenance costs?<br>* is it already handled quite well as an extension?<br>*=
 are there resources (i.e. people) available to champion it and maintain it=
 in=C2=A0perpetuity?<br><br><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x">For those who can=E2=80=99t use extensions (like on AWS RDS), is it prac=
tical to build temporal table features using only RAW SQL. I&#39;m aware of=
 the Nearform trigger solution but I&#39;d really love the syntactical suga=
r &quot;with SYSTEM VERISON&quot; gives? If so, are there any best practice=
s or tips for recreating some of that extension-like functionality?</blockq=
uote><br>I cannot speak to Nearform et. al., but for the record here, AWS R=
DS does support extensions - and a lot of them. No, you cannot install your=
 own custom extensions, but that&#39;s the tradeoff for using a managed ser=
vice. Since you are paying them money, however, you can certainly ask if th=
ey will make particular extensions available. <br><br><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">Having native temporal table support would be =
a huge help for users needing built-in tools for audits, historical trackin=
g, and meeting data compliance needs. I think adding these features nativel=
y could make PostgreSQL even more powerful and flexible for different use c=
ases.</blockquote><br></div><div>It&#39;s certainly been discussed over the=
 years. Nobody denies it can be useful, but putting things in core is a hig=
h bar. You can always argue your case on pgsql-hackers</div><div><br></div>=
<div>&gt; I&#39;ve been looking into <a href=3D"https://github.com/xocolatl=
/periods">https://github.com/xocolatl/periods</a> but I can&#39;t tell if i=
ts an extension or part of the regular deployment.<br><br>Looks like an ext=
ension to me.<br><br>Cheers,<br>Greg</div></div></div>

--0000000000009519c90627989541--