Good News Everyone! + feature proposal

On Thu, 2023-10-05 at 02:22 +0000, Jon Erdman wrote:

For the proposal (this one is a bit Apple specific): because my team
offers managed postgres to our Apple-internal customers, many of whom
are not database experts, or at least not postgres experts, we'd like to
be able to toggle the availability of UNLOGGED tables in
postgresql.conf, so our less clueful users have fewer footguns.

So, my proposal is for a GUC to implement that, which would *OF COURSE*
undefault to allowing UNLOGGED.

It certainly sounds harmless, but there are two things that make me
unhappy about this:

- Yet another GUC. It's not like we don't have enough of them.
(This is a small quibble.)

- This setting would influence the way SQL is processed.
We have had bad experiences with those; an often-quoted example is
the "autocommit" parameter that got removed in 7.4.
This certainly is less harmfuls, but still another pitfall that
can confuse users.

This reminds me of the proposal for a GUC to forbid UPDATE and DELETE
without a WHERE clause. That didn't get anywhere, see
/messages/by-id/20160721045746.GA25043@fetter.org

PS: I'm SO happy that this phase of my postgres journey has finally
started!!!!

I am happy for you.

Please don't be discouraged if some of your patches get stuck because
no consensus can be reached or because nobody cares enough. Your
contributions are still welcome. One good way to gain experience
is to review others' patches. In fact, you are expected to do that
if you submit your own.

Yours,
Laurenz Albe

Hi Jon,

Had my performance review today, and Apple wants me to get a patch
accepted this quarter, with the promise of more to come after that.
Luckily, this first patch can be anything (doesn't have to be of use to
Apple, more to prove that I can get a patch accepted), so I'm open to
suggestions of smaller stuff that is in demand at the moment.

My sincere congratulations!

From personal experience however delivering any non-trivial patch may
take from several years up to infinity even if the RFC is in agreement
with the community and generally everyone is enthusiastic about the
proposal change. Take "Clarify the behavior of the system when
approaching XID wraparound" [1]https://commitfest.postgresql.org/45/4128/ as a recent example. It's a fairly
simple change but after 10 months it's only yet to be committed. I
know people who were working on a single patch for 5 years.

Please make sure your employer understands the specifics of working on
open source, especially the fact that no one cares about this
employer's internal deadlines, and also that this is reflected in your
team metrics. There are also many other things to be mindful of. I
would recommend making sure that your team owns only one product (i.e.
PostgreSQL Upstream), no extensions, no forks etc. Make sure the team
charter reflects this, otherwise other products will always be a
priority.

Regarding your deliverables for this quarter. If the size of the patch
is not critical, I would suggest focusing on simple refactorings and
also code reviews. Especially code reviews. Practice shows that it's
realistic for one person to deliver somewhere between 10 to 20 patches
per quarter this way. Then compare the number you got to the average
amount of patches one person (except for the Core Team) typically
contributes. Your goal is to be above the median. If on top of that
you are able, lets say, to make internal technical talks about
PostgreSQL internals and/or speak at conferences and/or ... this will
look great on your PFD and your manager will be extremely happy with
your performance.

I know this may sound like gaming the metrics or something but this is
exactly how large companies work.

I honestly wish you all the best at your new job and will be happy to
share my findings regarding building the processes around OSS
development. Please don't hesitate reaching out to me off-list.

[1]: https://commitfest.postgresql.org/45/4128/

--
Best regards,
Aleksander Alekseev

On Thu, Oct 5, 2023 at 1:52 AM Jon Erdman <jon@thewickedtribe.net> wrote:

So I have some good news! At long last I've found a company/manager that
wants to actually factually pay me to do some work on PG!!

Congratulations!

For the proposal (this one is a bit Apple specific): because my team
offers managed postgres to our Apple-internal customers, many of whom
are not database experts, or at least not postgres experts, we'd like to
be able to toggle the availability of UNLOGGED tables in
postgresql.conf, so our less clueful users have fewer footguns.

So, my proposal is for a GUC to implement that, which would *OF COURSE*
undefault to allowing UNLOGGED.

That was difficult to parse at first glance. I guess you mean the
GUC's default value will not change the current behaviour, as you
mention below.

The reasoning here is we have autofailover set up for our standard
cluster offering that we give to customers, using sync-rep to guarantee
no data loss if we flop to the HA node. Any users not up to speed on
what UNLOGGED really means could inadvertently lose data, so we'd like
to be able to force it to be off, and turn it on upon request after
educating the customer in question it's it's a valid use case.

So to begin with: I'm sure some folks will hate this idea, but maybe a
good many wont, and remember, this would default to UNLOGGED enabled, so
no change to current behaviour. and no encouragement to disallow it, but
just the ability to do so, which i think is useful in
hosted/managed/multi-tenant environment where most things are taken care
of for the customer.

I see the need to disable this feature and agree that some
installations may need it, where the users are not savvy enough to
realize its dangers and fall for its promise to increase INSERT/UPDATE
performance. Your specific example of an internal hosted/managed
service is a good example. Even in smaller installations the DBA might
want to disable this, so that unwary developers don't willy-nilly
create unlogged tables and end up losing data after a failover. I hope
others can provide more examples and situations where this may be
useful, to make it obvious that we need this feature.

My first reaction was to make it a GRANTable permission. But since one
can always do `ALTER USER savvy_user SET allow_unlogged_tables TO
true` and override the system-wide setting in postgresql.conf, for a
specific user, I feel a GUC would be the right way to implement it.

The complaint about too many GUCs is a valid one, but I'd worry more
about it if it were an optimizer/performance improvement being hidden
behind a GUC. This new GUC would be a on-off switch to override the
SQL/grammar feature provided by the UNLOGGED keyword, hence not really
a concern IMO.

So I'd like to get a general idea how likely this would be to getting
accepted if it did it, and did it right?

Like others said, there are no guarantees. A working patch may help
guide people's opinion one way or the other, so I'd work on submitting
a patch while (some) people are in agreement.

Let the flame war begin!

Heh. I'm sure you already know this, but this community's flame wars
are way more timid compared to what members of other communities may
be used to :-) I consider it lucky if someone throws as much as a lit
match.

PS: I'm SO happy that this phase of my postgres journey has finally
started!!!!

I, too, am very happy for you! :-)

Jon Erdman (aka StuckMojo on IRC)

TIL.

I wish there was a directory of IRC identities that pointed to real
identities (at least for folks who don't mind this mapping available
in the open), so that when we converse in IRC, we have a face to go
with the IRC handles. As a human I feel that necessity.

Best regards,
Gurjeet
http://Gurje.et

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Thu, 2023-10-05 at 02:22 +0000, Jon Erdman wrote:

For the proposal (this one is a bit Apple specific): because my team
offers managed postgres to our Apple-internal customers, many of whom
are not database experts, or at least not postgres experts, we'd like to
be able to toggle the availability of UNLOGGED tables in
postgresql.conf, so our less clueful users have fewer footguns.

I'm doubtful that this is a problem that needs a solution.
If anything, the right answer is to fix whatever part of the
documentation isn't warning of the hazards strongly enough.

Even more to the point: if we accept this, how many other
footgun-preventing GUCs will have the same or stronger claim to
existence?

It certainly sounds harmless, but there are two things that make me
unhappy about this:

- Yet another GUC. It's not like we don't have enough of them.
(This is a small quibble.)

- This setting would influence the way SQL is processed.
We have had bad experiences with those; an often-quoted example is
the "autocommit" parameter that got removed in 7.4.
This certainly is less harmfuls, but still another pitfall that
can confuse users.

Same objections here. Also note that the semantics we've defined
for GUCs (when they can be set and where) don't always line up
nicely with requirements of this sort. It's far from clear to me
whether such a GUC should be SUSET (making it a hard prohibition
for ordinary users) or USERSET (making it just a training wheel).

regards, tom lane

On Wednesday, October 4, 2023, Jon Erdman <jon@thewickedtribe.net> wrote:

So I'd like to get a general idea how likely this would be to getting
accepted if it did it, and did it right?

Run a cron job checking for them. Allow for overrides by adding a comment
to any unclogged tables you’ve identified as being acceptable.

David J.

On 10/5/23 8:53 AM, Tom Lane wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Thu, 2023-10-05 at 02:22 +0000, Jon Erdman wrote:

For the proposal (this one is a bit Apple specific): because my team
offers managed postgres to our Apple-internal customers, many of whom
are not database experts, or at least not postgres experts, we'd like to
be able to toggle the availability of UNLOGGED tables in
postgresql.conf, so our less clueful users have fewer footguns.

I'm doubtful that this is a problem that needs a solution.
If anything, the right answer is to fix whatever part of the
documentation isn't warning of the hazards strongly enough.

Even more to the point: if we accept this, how many other
footgun-preventing GUCs will have the same or stronger claim to
existence?

It certainly sounds harmless, but there are two things that make me
unhappy about this:

- Yet another GUC. It's not like we don't have enough of them.
(This is a small quibble.)

- This setting would influence the way SQL is processed.
We have had bad experiences with those; an often-quoted example is
the "autocommit" parameter that got removed in 7.4.
This certainly is less harmfuls, but still another pitfall that
can confuse users.

Same objections here. Also note that the semantics we've defined
for GUCs (when they can be set and where) don't always line up
nicely with requirements of this sort. It's far from clear to me
whether such a GUC should be SUSET (making it a hard prohibition
for ordinary users) or USERSET (making it just a training wheel).

Someone on linked-in suggested an event trigger, so now I'm thinking of
a custom extension that would do nothing but create said event trigger,
and maybe could be toggled with a customized setting (but that might
allow users to turn it off themselves...which is maybe ok).

If the extension were installed by the DBA user, the customer wouldn't
be able to drop it, and if we decided to give them an exception, we just
drop or disable the extension.

As a second more general question: could my original idea (i.e. sans
event trigger) be implemented in an extension somehow, or is that not
technically possible (I suspect not)?
--
Jon Erdman (aka StuckMojo on IRC)
PostgreSQL Zealot

On Thu, Oct 5, 2023 at 11:11 PM Jon Erdman <jon@thewickedtribe.net> wrote:

As a second more general question: could my original idea (i.e. sans
event trigger) be implemented in an extension somehow, or is that not
technically possible (I suspect not)?

It should be easy to do using the ProcessUtility_hook hook, defined in
a custom module written in C. As long as your module is preloaded
(one of the *_preload_libraries GUC), your code will be called without
the need for any SQL-level object and you would be free to add any
custom GUC you want to enable it on a per-user basis or anything else.

On Thu, 2023-10-05 at 14:58 +0000, Jon Erdman wrote:

For the proposal (this one is a bit Apple specific): because my team
offers managed postgres to our Apple-internal customers, many of whom
are not database experts, or at least not postgres experts, we'd like to
be able to toggle the availability of UNLOGGED tables in
postgresql.conf, so our less clueful users have fewer footguns.

Someone on linked-in suggested an event trigger, so now I'm thinking of
a custom extension that would do nothing but create said event trigger,
and maybe could be toggled with a customized setting (but that might
allow users to turn it off themselves...which is maybe ok).

An event trigger is the perfect solution for this requirement.

If the extension were installed by the DBA user, the customer wouldn't
be able to drop it, and if we decided to give them an exception, we just
drop or disable the extension.

Right. Also, only a superuser can create or drop event triggers.

As a second more general question: could my original idea (i.e. sans
event trigger) be implemented in an extension somehow, or is that not
technically possible (I suspect not)?

You could perhaps use "object_access_hook" in an extension.

Yours,
Laurenz Albe

On Thu, Oct 5, 2023 at 05:10:37AM -0700, Gurjeet Singh wrote:

I wish there was a directory of IRC identities that pointed to real
identities (at least for folks who don't mind this mapping available
in the open), so that when we converse in IRC, we have a face to go
with the IRC handles. As a human I feel that necessity.

There is:

https://wiki.postgresql.org/wiki/IRC2RWNames

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.