Horton
Menu

Thread

  1. Good News Everyone! + feature proposal

    Jon Erdman <jon@thewickedtribe.net> — 2023-10-05T02:22:26Z

    Hiya Hackers!

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

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.

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.

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.

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?

Let the flame war begin!

PS: I'm SO happy that this phase of my postgres journey has finally 
started!!!!
-- 
Jon Erdman (aka StuckMojo on IRC)
     PostgreSQL Zealot

  2. Re: Good News Everyone! + feature proposal

    Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-05T09:22:01Z

    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
https://www.postgresql.org/message-id/flat/20160721045746.GA25043%40fetter.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

  3. Re: Good News Everyone! + feature proposal

    Aleksander Alekseev <aleksander@timescale.com> — 2023-10-05T09:24:11Z

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

  4. Re: Good News Everyone! + feature proposal

    Gurjeet Singh <gurjeet@singh.im> — 2023-10-05T12:10:37Z

    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

  5. Re: Good News Everyone! + feature proposal

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-10-05T13:53:16Z

    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

  6. Re: Good News Everyone! + feature proposal

    David G. Johnston <david.g.johnston@gmail.com> — 2023-10-05T14:04:15Z

    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.

  7. Re: Good News Everyone! + feature proposal

    Jon Erdman <jon@thewickedtribe.net> — 2023-10-05T14:58:05Z

    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

  8. Re: Good News Everyone! + feature proposal

    Julien Rouhaud <rjuju123@gmail.com> — 2023-10-05T16:02:15Z

    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.

  9. Re: Good News Everyone! + feature proposal

    Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-05T19:54:29Z

    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

  10. Re: Good News Everyone! + feature proposal

    Bruce Momjian <bruce@momjian.us> — 2023-10-06T19:52:22Z

    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.