Re: Good News Everyone! + feature proposal - Mailing list pgsql-hackers

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