Thread: cutting down the TODO list thread

Hi,

On 2020-10-27 15:24:35 -0400, John Naylor wrote:
> As I mentioned in [1], I've volunteered to clear out the TODO list of items
> that appear to be too difficult, controversial, or otherwise not worth
> doing to warrant being listed there. I'll be working a few sections at a
> time, and every so often I'll have a list of proposed items for removal. If
> I don't hear objections, I'll remove the items after a few days while going
> through the next set.
> 
> Where there's an email thread, I've skimmed a few messages to get a sense
> of the community's thoughts on it. Where easily determined, I've taken age
> into account, insofar as something from 2017 is going to get much more
> benefit of doubt than something from 2008. I've added after each item a
> phrase that sums up the reason I believe it doesn't belong anymore.
> Feedback welcome, of course, although I suspect there won't be much.
>

> - Prevent query cancel packets from being replayed by an attacker,
> especially when using SSL
>   Old and difficult

FWIW, I don't think we should remove this. Our current solution has some
serious issues that we should address at some point.



> - Allow WAL replay of CREATE TABLESPACE to work when the directory
> structure on the recovery computer is different from the original
>   Thread quote: "part of the difficult, perhaps-not-worth doing impossible
> problems"

I think we ought to do something here. Mostly because the current
situation makes it impossible to test many things on a single
system. And we have a partial solution with the tablespace mapping
files.


Greetings,

Andres Freund

On Tue, Oct 27, 2020 at 03:24:35PM -0400, John Naylor wrote:
> As I mentioned in [1], I've volunteered to clear out the TODO list of items
> that appear to be too difficult, controversial, or otherwise not worth doing to
> warrant being listed there. I'll be working a few sections at a time, and every
> so often I'll have a list of proposed items for removal. If I don't hear
> objections, I'll remove the items after a few days while going through the next
> set.
> 
> Where there's an email thread, I've skimmed a few messages to get a sense of
> the community's thoughts on it. Where easily determined, I've taken age into
> account, insofar as something from 2017 is going to get much more benefit of
> doubt than something from 2008. I've added after each item a phrase that sums
> up the reason I believe it doesn't belong anymore. Feedback welcome, of course,
> although I suspect there won't be much.

Thanks for working on this.  It certainly needs new eyes (not mine).  ;-)

I am fine reomving all the items below.  I am kind of disappointed we
have these _stuck_ items, but I don't see a clear way forward, so let's
just remove them and see what requests we get for them.

---------------------------------------------------------------------------

> **Administration
> 
> - Have custom variables be transaction-safe
>   Old and found to be difficult after attempting
> 
> - Allow custom variables to appear in pg_settings()
>   Old and controversial
> 
> - Implement the SQL-standard mechanism whereby REVOKE ROLE revokes only the
> privilege granted by the invoking role, and not those granted by other roles
>   Old and difficult
> 
> - Prevent query cancel packets from being replayed by an attacker, especially
> when using SSL
>   Old and difficult
> 
> *Configuration files
> 
> - Consider normalizing fractions in postgresql.conf, perhaps using '%'
>   At the time (2007), some gucs used an actual percentage.
> 
> - Add external tool to auto-tune some postgresql.conf parameters
>   There are already out-of-core tools that try to do this.
> 
> - Create utility to compute accurate random_page_cost value
>   Seems outdated: In the current age of SSDs and cloud environments, it's often
> just set to 1.1, and there hasn't been a demand to be more accurate than that.
> 
> - Allow synchronous_standby_names to be disabled after communication failure
> with all synchronous standby servers exceeds some timeout
>   Controversial
> 
> - Adjust rounding behavior for numeric GUC values
>   Controversial
> 
> *Tablespaces
> 
> - Allow WAL replay of CREATE TABLESPACE to work when the directory structure on
> the recovery computer is different from the original
>   Thread quote: "part of the difficult, perhaps-not-worth doing impossible
> problems"
> 
> - Allow per-tablespace quotas
>   This seems to point to the larger problem space of disk space monitoring, and
> should probably be phrased thusly, and is a much bigger project or set of
> projects.
> 
> - Allow tablespaces on RAM-based partitions for temporary objects
>   In the thread, what's desired is the ability to have some amount of
> durability on a RAM-disk without WAL logging.
> 
> - Close race in DROP TABLESPACE on Windows
>   This refers to buildfarm failures from 2014.
> 
> *Statistics Collector
> 
> - Track number of WAL files ready to be archived in pg_stat_archiver
>   Thread quote: "pg_stat_archiver already has a column for last_archived_wal
> and last_failed_wal, so you can already work out how many files there must be
> between then and now"
> 
> *Point-In-Time Recovery
> 
> - Allow archive_mode to be changed without server restart
>   Controversial and old
> 
> *Standby server mode
> 
> - Allow pg_xlogfile_name() to be used in recovery mode
>   Controversial and old
> 
> - Change walsender so that it applies per-role settings
>   Old and possibly obsolete

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

  The usefulness of a cup is in its emptiness, Bruce Lee

On Tue, Oct 27, 2020 at 03:46:14PM -0400, Bruce Momjian wrote:
> On Tue, Oct 27, 2020 at 03:24:35PM -0400, John Naylor wrote:
> > As I mentioned in [1], I've volunteered to clear out the TODO list of items
> > that appear to be too difficult, controversial, or otherwise not worth doing to
> > warrant being listed there. I'll be working a few sections at a time, and every
> > so often I'll have a list of proposed items for removal. If I don't hear
> > objections, I'll remove the items after a few days while going through the next
> > set.
> > 
> > Where there's an email thread, I've skimmed a few messages to get a sense of
> > the community's thoughts on it. Where easily determined, I've taken age into
> > account, insofar as something from 2017 is going to get much more benefit of
> > doubt than something from 2008. I've added after each item a phrase that sums
> > up the reason I believe it doesn't belong anymore. Feedback welcome, of course,
> > although I suspect there won't be much.
> 
> Thanks for working on this.  It certainly needs new eyes (not mine).  ;-)
> 
> I am fine reomving all the items below.  I am kind of disappointed we
> have these _stuck_ items, but I don't see a clear way forward, so let's
> just remove them and see what requests we get for them.

Do any of these limitations need to be documented before removing them
from the TODO list?

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

  The usefulness of a cup is in its emptiness, Bruce Lee

On Wed, Oct 28, 2020 at 8:36 AM Andres Freund <andres@anarazel.de> wrote:
> On 2020-10-27 15:24:35 -0400, John Naylor wrote:
> > - Allow WAL replay of CREATE TABLESPACE to work when the directory
> > structure on the recovery computer is different from the original
> >   Thread quote: "part of the difficult, perhaps-not-worth doing impossible
> > problems"
>
> I think we ought to do something here. Mostly because the current
> situation makes it impossible to test many things on a single
> system. And we have a partial solution with the tablespace mapping
> files.

+1, we need to get something like this working so that we can write
decent replication tests.  FWIW there was another little thread on the
topic, not listed there:

https://www.postgresql.org/message-id/flat/CALfoeisEF92F5nJ-aAcuWTvF_Aogxq_1bHLem_kVfM_tHc2mfg%40mail.gmail.com

On Tue, Oct 27, 2020 at 04:54:24PM -0400, John Naylor wrote:
> 
> 
> On Tue, Oct 27, 2020 at 3:52 PM Bruce Momjian <bruce@momjian.us> wrote:
> 
> 
>     Do any of these limitations need to be documented before removing them
>     from the TODO list?
> 
> 
> I see two areas that might use a mention:
> 
> - pg_settings not displaying custom variables
> - SQL standard difference with REVOKE ROLE (I haven't looked further into this)

OK, thanks.  Do you want to work on a doc patch or should I?  Having it
the docs at least warns our users.

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

  The usefulness of a cup is in its emptiness, Bruce Lee

On Wed, Oct 28, 2020 at 11:15 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> On Wed, 28 Oct 2020, 17:55 Oleksandr Shulgin <oleksandr.shulgin@zalando.de wrote:
>>
>> On Tue, Oct 27, 2020 at 8:25 PM John Naylor <john.naylor@enterprisedb.com> wrote:
>>>
>>> As I mentioned in [1], I've volunteered to clear out the TODO list of items that appear to be too difficult,
controversial,or otherwise not worth doing to warrant being listed there. I'll be working a few sections at a time, and
everyso often I'll have a list of proposed items for removal. If I don't hear objections, I'll remove the items after a
fewdays while going through the next set. 
>>
>>
>> I'm totally on board with cleaning the list up, but how about marking as "won't fix" (or similar) instead of
actuallyremoving the items?  That should help to prevent the same exact items from appearing on the list again, which
theyeventually would, I believe. 
>
>
> +1

A small technical detail on the topic but if doing that, let's not
mark them as that inline -- create a separate page with those items on
it.

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

On Wed, Oct 28, 2020 at 9:27 PM John Naylor
<john.naylor@enterprisedb.com> wrote:
>
> On Wed, Oct 28, 2020 at 6:52 AM Magnus Hagander <magnus@hagander.net> wrote:
>>
>> On Wed, Oct 28, 2020 at 11:15 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
>> >
>> > On Wed, 28 Oct 2020, 17:55 Oleksandr Shulgin <oleksandr.shulgin@zalando.de wrote:
>> >> I'm totally on board with cleaning the list up, but how about marking as "won't fix" (or similar) instead of
actuallyremoving the items?  That should help to prevent the same exact items from appearing on the list again, which
theyeventually would, I believe. 
>> >
>> >
>> > +1
>>
>> A small technical detail on the topic but if doing that, let's not
>> mark them as that inline -- create a separate page with those items on
>> it.
>
>
> How about a section on the same page at the bottom, near "features we don't want"?

Yes, that's what I was thinking, otherwise it'll still be too easy to miss.

On Wed, Oct 28, 2020 at 3:35 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> On Wed, Oct 28, 2020 at 9:27 PM John Naylor
> <john.naylor@enterprisedb.com> wrote:
> >
> > On Wed, Oct 28, 2020 at 6:52 AM Magnus Hagander <magnus@hagander.net> wrote:
> >>
> >> On Wed, Oct 28, 2020 at 11:15 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >> >
> >> > On Wed, 28 Oct 2020, 17:55 Oleksandr Shulgin <oleksandr.shulgin@zalando.de wrote:
> >> >> I'm totally on board with cleaning the list up, but how about marking as "won't fix" (or similar) instead of
actuallyremoving the items?  That should help to prevent the same exact items from appearing on the list again, which
theyeventually would, I believe. 
> >> >
> >> >
> >> > +1
> >>
> >> A small technical detail on the topic but if doing that, let's not
> >> mark them as that inline -- create a separate page with those items on
> >> it.
> >
> >
> > How about a section on the same page at the bottom, near "features we don't want"?
>
> Yes, that's what I was thinking, otherwise it'll still be too easy to miss.

I would personally prefer a completely seprate page, but I don't feel
strongly enough about it to push for it :) As long as it's very
clearly marked as such (and maybe the title of the page also changed
to that it's a combined todo and todon't list? :)

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

On 2020-10-28 16:20:03 +0100, Magnus Hagander wrote:
> I would personally prefer a completely seprate page

Same.

On Tue, Nov  3, 2020 at 02:06:13PM -0400, John Naylor wrote:
> I was thinking of not having the next updates during commitfest, but it could
> also be argued this is a type of review, and the things here will be returned
> with feedback or rejected, in a way. Ultimately, it comes down to "when time
> permits".

I don't understand what this is referring to.  Thanks for the rest of
the work.

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

  The usefulness of a cup is in its emptiness, Bruce Lee

On Wed, Nov 18, 2020 at 02:26:46PM -0400, John Naylor wrote:
> Here are the next couple of sections with items proposed to be moved to the
> "not worth doing" page. As before, if there are any objections, let me know.
> I'll make the move in a few days. 
> 
> Also, since 13 has been released, I'll change the explanation of Done items to
> "will appear in the PostgreSQL 14 release." Also, since that wasn't updated,

Yes, please do that.  I didn't go through this for PG 13 since you were
already working in this area.

> it's not immediately clear to me which release the [D] marking for "improve
> setting of visibility map bits for read-only and insert-only workloads" refers
> to. Does anyone know which commit that is?

Yes, this was fixed in PG 13 with this commit:

    commit b07642dbcd
    Author: David Rowley <drowley@postgresql.org>
    Date:   Sat Mar 28 19:20:12 2020 +1300
    
        Trigger autovacuum based on number of INSERTs
> - Fix problems with wrong runtime encoding conversion for NLS message files
>   What problems? There is no discussion thread.

I have no idea.  I don't think I added this one since there is no URL,
or it is very old.

> - More sensible support for Unicode combining characters, normal forms
>   We have normalization as of PG13, so I propose to mark this Done rather than
> move it.

Agreed.  Also done items should be remove anyway since they were done in
PG 13.

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

  The usefulness of a cup is in its emptiness, Bruce Lee

John Naylor <john.naylor@enterprisedb.com> writes:
> Here are the next couple of sections with items proposed to be moved to the
> "not worth doing" page. As before, if there are any objections, let me
> know. I'll make the move in a few days.

> - Fix /contrib/ltree operator
>   Bug from 2007 with zero followup

Actually, I believe this was fixed by 70dc4c509; at least, the
case shown in the bug report now yields "false" as expected.

> - Fix /contrib/btree_gist's implementation of inet indexing
>   Bug from 2010 and apparent lack of interest

This one's pretty clearly a bug.  Lack of interest or no, we
should keep it around.

> - Allow to_date() and to_timestamp() to accept localized month names

This is done too, see d67755049.

            regards, tom lane

On Mon, Nov 23, 2020 at 10:41:25AM -0400, John Naylor wrote:
> With the exception of "Fix /contrib/btree_gist's implementation of inet
> indexing", all items above have been either moved over, or removed if they were
> done already by PG13.

Thanks.

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

  The usefulness of a cup is in its emptiness, Bruce Lee

On Thu, Dec 10, 2020 at 03:29:07PM -0400, John Naylor wrote:
> Hi,

I agree with all of your analysis, but have some feedback;

> Continuing with TODO list maintenance, first a couple things to clean up:
> 
> - Allow ALTER INDEX ... RENAME concurrently
> 
> This was in the wrong section, but it's irrelevant: The lock level was lowered
> in commit 1b5d797cd4f, so I went ahead and removed this already.

Good.
> 
> - Add CORRESPONDING BY to UNION/INTERSECT/EXCEPT
> 
> The link titled "how not to write this patch" points to a web archive of the
> author's description of how he implemented the rejected patch. That doesn't
> seem useful, since it was...rejected. I propose to replace that with the
> -hackers thread, where there is discussion of the design problem:
>   https://www.postgresql.org/message-id/flat/
> CAJZSWkWN3YwQ01C3%2Bcq0%2BeyZ1DmK%3D69_6vryrsVGMC%3D%2BfWrSZA%40mail.gmail.com
> 
> Now, for the proposed items to move to "Not Worth Doing". As before, let me
> know of any objections. I plan to move these early next week:

Agreed.

> *Views and Rules
> 
> - Allow VIEW/RULE recompilation when the underlying tables change
> 
> The entry itself says "This is both difficult and controversial." and the
> linked threads confirm that.

Yes, probably shouldn't be an item.
> 
> - Make it possible to use RETURNING together with conditional DO INSTEAD rules,
> such as for partitioning setups
> 
> This was from before we got native partitioning, so the stated rationale is
> outdated.

I don't think we need that anymore.

> *SQL Commands (this is a huge section, for now just doing the miscellany at the
> top before the various subsections)
> 
> - Add a GUC variable to warn about non-standard SQL usage in queries
> 
> I don't see the reason for this, and sounds difficult anyway.

It is hard.

> - Add NOVICE output level for helpful messages
> 
> This would only be useful if turned on, so is going to be least used where it
> might help the most. It also sounds like a lot of slow menial work to
> implement.

It is menial work, but I thought it might inspire someone to do it. 
Removal at this point seems fine.

> - Allow DISTINCT to work in multiple-argument aggregate calls
> 
> Tom suggested this in 2006 for the sake of orthogonality. Given the amount of
> time passed, it seems not very important.

Yes.

> - Allow DELETE and UPDATE to be used with LIMIT and ORDER BY
> 
> Some use cases mentioned, but nearly all have some kind of workaround already.

Agreed.

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

  The usefulness of a cup is in its emptiness, Bruce Lee

On Wed, May 19, 2021 at 01:52:03PM -0400, John Naylor wrote:
> Related to the above, but has a more specific approach in mind. The discussion
> thread is not useful for getting one's head around how to think about the
> problem, much less to decide if it's worth working on -- it's mostly
> complaining about the review process. Independent of that, the idea of
> inspecting the buffer cache seems impractical.

Yes, I think you are right about all of these.

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

  If only the physical world exists, free will is an illusion.

On Mon, Jun 28, 2021 at 05:41:50PM -0400, John Naylor wrote:
> Here I'm just reviewing a couple items in the Sorting section:
> 
> https://wiki.postgresql.org/wiki/Todo#Sorting
> 
> - Consider whether duplicate keys should be sorted by block/offset
> 
> https://www.postgresql.org/message-id/flat/23321.1205726381%40sss.pgh.pa.us
> 
> It's moot since we started requiring tid as a tiebreaker for btree indexes, so
> I'll just remove this.
> 
> - Consider being smarter about memory and external files used during sorts
> 
> https://www.postgresql.org/message-id/1196186626.4246.1078.camel@ebony.site
> 
> We've had a large number of improvements in this area since 2007, and it seems
> the ideas in this thread have mostly been done or superseded already. 

Agreed.  Please remove them or I can do it.

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

  If only the physical world exists, free will is an illusion.

Spotted in the "Hashing" section:

"Use "lazy" hash tables to look up only the tuples that are actually requested"

David Rowley has knocked that one off.  He called it Result Cache.

It's been a while, but here are a few more suggested
removals/edits/additions to the TODO list. Any objections or new
information, let me know:

- Auto-fill the free space map by scanning the buffer cache or by
checking pages written by the background writer
http://archives.postgresql.org/pgsql-hackers/2006-02/msg01125.php
https://www.postgresql.org/message-id/200603011716.16984.peter_e@gmx.net

Both these threads are from 2006, so have nothing to do with the current FSM.

- Allow concurrent inserts to use recently created pages rather than
creating new ones
http://archives.postgresql.org/pgsql-hackers/2010-05/msg00853.php

Skimming the first few messages, I believe this has been covered by
commit 719c84c1b? (Extend relations multiple blocks at a time to
improve scalability.)

- Allow VACUUM FULL and CLUSTER to update the visibility map

This topic has a current CF entry which seems to have stalled, so that
newer thread would be better to list here than the one from 2013.

- Bias FSM towards returning free space near the beginning of the heap
file, in hopes that empty pages at the end can be truncated by VACUUM
http://archives.postgresql.org/pgsql-hackers/2009-09/msg01124.php
https://www.postgresql.org/message-id/20150424190403.GP4369@alvh.no-ip.org

I'm not sure what to think of this, but independently of that, the
second thread is actually talking about bringing back something like
the pre-9.0 vacuum full, so maybe it should be its own entry?

- Consider a more compact data representation for dead tuple locations
within VACUUM
http://archives.postgresql.org/pgsql-patches/2007-05/msg00143.php

Great, but let's link to this more recent thread instead:
https://www.postgresql.org/message-id/flat/CAD21AoAfOZvmfR0j8VmZorZjL7RhTiQdVttNuC4W-Shdc2a-AA%40mail.gmail.com

- Improve autovacuum tuning
http://www.postgresql.org/message-id/5078AD6B.8060802@agliodbs.com
http://www.postgresql.org/message-id/20130124215715.GE4528@alvh.no-ip.org

I'm kind of on the fence about these. The title is way too broad, and
I doubt we are going to forget to keep improving this area.

It seems the first thread is really about auto-analyze thresholds, so
maybe it should be in a separate entry if we want to do anything
mentioned in the thread?

The second thread is really about autovacuum launcher scheduling.
Probably still relevant, but the thread is very long and doesn't seem
terribly helpful to someone trying to get up to speed on the issues
that are still relevant. I don't see any more recent discussion,
either. Thoughts?

-- 
John Naylor
EDB: http://www.enterprisedb.com

On Thu, Dec 9, 2021 at 2:40 AM John Naylor <john.naylor@enterprisedb.com> wrote:
>
> - Improve autovacuum tuning
> http://www.postgresql.org/message-id/5078AD6B.8060802@agliodbs.com
> http://www.postgresql.org/message-id/20130124215715.GE4528@alvh.no-ip.org
>
> I'm kind of on the fence about these. The title is way too broad, and
> I doubt we are going to forget to keep improving this area.
>
> It seems the first thread is really about auto-analyze thresholds, so
> maybe it should be in a separate entry if we want to do anything
> mentioned in the thread?
>
> The second thread is really about autovacuum launcher scheduling.
> Probably still relevant, but the thread is very long and doesn't seem
> terribly helpful to someone trying to get up to speed on the issues
> that are still relevant. I don't see any more recent discussion,
> either. Thoughts?

There is another discussion on autovacuum scheduling in 2018 here:

https://www.postgresql.org/message-id/0A3221C70F24FB45833433255569204D1F8A4DC6%40G01JPEXMBYT05

Some algorithms were proposed there and I implemented a PoC patch:

https://www.postgresql.org/message-id/CAD21AoBUaSRBypA6pd9ZD%3DU-2TJCHtbyZRmrS91Nq0eVQ0B3BA%40mail.gmail.com

But not completed.

Regards,


--
Masahiko Sawada
EDB:  https://www.enterprisedb.com/

On Wed, Dec 8, 2021 at 1:40 PM John Naylor <john.naylor@enterprisedb.com> wrote:
>
> It's been a while, but here are a few more suggested
> removals/edits/additions to the TODO list. Any objections or new
> information, let me know:
>
> - Auto-fill the free space map by scanning the buffer cache or by
> checking pages written by the background writer
> http://archives.postgresql.org/pgsql-hackers/2006-02/msg01125.php
> https://www.postgresql.org/message-id/200603011716.16984.peter_e@gmx.net
>
> Both these threads are from 2006, so have nothing to do with the current FSM.

Moved to the Not Worth Doing list.

> - Allow concurrent inserts to use recently created pages rather than
> creating new ones
> http://archives.postgresql.org/pgsql-hackers/2010-05/msg00853.php
>
> Skimming the first few messages, I believe this has been covered by
> commit 719c84c1b? (Extend relations multiple blocks at a time to
> improve scalability.)

Removed.

> - Allow VACUUM FULL and CLUSTER to update the visibility map
>
> This topic has a current CF entry which seems to have stalled, so that
> newer thread would be better to list here than the one from 2013.

Added.

> - Bias FSM towards returning free space near the beginning of the heap
> file, in hopes that empty pages at the end can be truncated by VACUUM
> http://archives.postgresql.org/pgsql-hackers/2009-09/msg01124.php
> https://www.postgresql.org/message-id/20150424190403.GP4369@alvh.no-ip.org
>
> I'm not sure what to think of this, but independently of that, the
> second thread is actually talking about bringing back something like
> the pre-9.0 vacuum full, so maybe it should be its own entry?

Done.

> - Consider a more compact data representation for dead tuple locations
> within VACUUM
> http://archives.postgresql.org/pgsql-patches/2007-05/msg00143.php
>
> Great, but let's link to this more recent thread instead:
> https://www.postgresql.org/message-id/flat/CAD21AoAfOZvmfR0j8VmZorZjL7RhTiQdVttNuC4W-Shdc2a-AA%40mail.gmail.com

Done.

> > The second thread is really about autovacuum launcher scheduling.
> > Probably still relevant, but the thread is very long and doesn't seem
> > terribly helpful to someone trying to get up to speed on the issues
> > that are still relevant. I don't see any more recent discussion,
> > either. Thoughts?

Split into two entries.

On Wed, Dec 8, 2021 at 8:12 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> There is another discussion on autovacuum scheduling in 2018 here:
>
> https://www.postgresql.org/message-id/0A3221C70F24FB45833433255569204D1F8A4DC6%40G01JPEXMBYT05
>
> Some algorithms were proposed there and I implemented a PoC patch:
>
> https://www.postgresql.org/message-id/CAD21AoBUaSRBypA6pd9ZD%3DU-2TJCHtbyZRmrS91Nq0eVQ0B3BA%40mail.gmail.com

Added, thanks!

--
John Naylor
EDB: http://www.enterprisedb.com

On Wed, Jan 11, 2023 at 02:09:56PM +0700, John Naylor wrote:
> I've come up with some revised language, including s/15/16/ and removing the
> category of "[E]" (easier to implement), since it wouldn't be here if it were
> actually easy:

I think it is still possible for a simple item to be identified as
wanted and easy, but not completed and put on the TODO list.

> WARNING for Developers: This list contains some known PostgreSQL bugs, some
> feature requests, and some things we are not even sure we want. This is not
> meant to be a resource for beginning developers to get ideas for things to work
> on. <WIP: maybe direct them to commitfest?>
> 
> All of these items are hard, and some are perhaps impossible. Some of these
> items might have become unnecessary since they were added. Others might be
> desirable but:
> 
> - a large amount work is required
> - the problems are subtler than they might appear
> - the desirable semantics aren't clear
> - there are tradeoffs that there's not consensus about
> - some combinations of the above
> 
> If you really need a feature that is listed below, it will be worth reading the
> linked email thread if there is one, since it will often show the difficulties,
> or perhaps contain previous failed attempts to get a patch committed. If after
> that you still want to work on it, be prepared to first discuss the value of
> the feature. Do not assume that you can start coding and expect it to be
> committed. Always discuss design on the Hackers list before starting to code.
> 
> Over time, it may become clear that a TODO item has become outdated or
> otherwise determined to be either too controversial or not worth the
> development effort. Such items should be retired to the Not Worth Doing page.
> 
> [D] marks changes that are done, and will appear in the PostgreSQL 16 release.

I think we risk overloading people with too many words above, and they
will not read it fully.  Can it be simplified?  I wonder if some of this
belows in the developer's FAQ and linked to that from the TODO list.

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

Embrace your flaws.  They make you human, rather than perfect,
which you will never be.

On Mon, Jan 16, 2023 at 05:17:23PM +0700, John Naylor wrote:
> 
> I wrote:
> 
> > We could also revise the developer FAQ:
> > - remove phrase "Outstanding features are detailed in Todo."
> > - add suggestion to to check the Todo or Not_worth_doing pages to see if the
> desired feature is undesirable or problematic
> > - rephrase "Working in isolation is not advisable because others might be
> working on the same TODO item, or you might have misunderstood the TODO item."
> so it doesn't mention 'TODO' at all.
> 
> There is also
> 
> https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F#TODOs
> 
> Changing the description of what links to the todo list will probably do more
> to reduce confusion than language in the todo list itself.

Agreed.

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

Embrace your flaws.  They make you human, rather than perfect,
which you will never be.

On Tue, Jan 24, 2023 at 10:46:34AM +0700, John Naylor wrote:
> 
> On Wed, Jan 18, 2023 at 3:13 AM Bruce Momjian <bruce@momjian.us> wrote:
> 
> > I think we risk overloading people with too many words above, and they
> > will not read it fully.  Can it be simplified?  I wonder if some of this
> > belows in the developer's FAQ and linked to that from the TODO list.
> 
> I think you're right. That further drives home what I mentioned a few days ago:
> Maybe we don't need to change much in the actual list itself, but rephrase
> references to it from elsewhere. Here's a first draft to see what that would

The TODO list started as a way to record items that were either bugs or
features we couldn't yet implement.  At this point, it is more features
we are either not sure we want or don't know how to implement.  This
should be clearly specified wherever the TODO list is referenced.  I
think once this is done it should be clear how users and developers
should view that list.  I think the top of the TODO list makes that
clear:

    This list contains some known PostgreSQL bugs, some feature
    requests, and some things we are not even sure we want. Many of
    these items are hard, and some are perhaps impossible. If you
    would like to work on an item, please read the Developer FAQ
    first. There is also a development information page.

The rest of the text appears clear to me too.

> look like:
> --
> https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F#TODOs
> 
> from:
> "PostgreSQL maintains a TODO list on our wiki: http://wiki.postgresql.org/wiki/
> TODO
> 
> We have attempted to organize issues and link in relevant discussions from our
> mailing list archives. Please read background information if it is available
> before attempting to resolve a TODO. If no background information is available,
> it is appropriate to post a question to pgsql-hackers@postgresql.org and
> request additional information and inquire about the status of any ongoing work
> on the problem."
> 
> to:
> "It's worth checking if the feature of interest is found in the TODO list on
> our wiki: http://wiki.postgresql.org/wiki/TODO. That list contains some known
> PostgreSQL bugs, some feature requests, and some things we are not even sure we
> want. Many entries have a link to an email thread containing prior discussion,
> or perhaps attempts that for whatever reason didn't make it as far as getting
> committed."
> 
> ...which might make more sense if moved below the "brand new features" section.

I think we just point them at the TODO list and they will read the top
of the list first, no?  I think you are right that we updated the top of
the TODO but didn't update the places that link to it.  I am thinking we
should just trim down the text linking to it and let the top of the TODO
list do its job.

> --
> https://wiki.postgresql.org/wiki/Developer_FAQ
> 
> 1)
> from:
> "What areas need work?
> Outstanding features are detailed in Todo.
> 
> You can learn more about these features by consulting the archives, the SQL
> standards and the recommended texts (see books for developers)."
> 
> to:
> ??? -> For "what areas need work?", we need to have a different answer, but I'm
> not sure what it is.

Wow, I would not send a new person to the SQL standard docs.  ;-)  I am
thinking we just don't have a good answer to this so let's say less.

> 2)
> from:
> "What do I do after choosing an item to work on?
> 
> Send an email to pgsql-hackers with a proposal for what you want to do
> (assuming your contribution is not trivial). Working in isolation is not
> advisable because others might be working on the same TODO item, or you might
> have misunderstood the TODO item. In the email, discuss both the internal
> implementation method you plan to use, and any user-visible changes (new
> syntax, etc)."
> 
> to:
> "What do I do after choosing an area to work on?
> 
> Send an email to pgsql-hackers with a proposal for what you want to do
> (assuming your contribution is not trivial). Working in isolation is not

Can new people identify trivial?

> advisable because experience has shown that there are often requirements that
> are not obvious, and if those are not agreed on beforehand it leads to wasted
> effort. In the email, discuss both the internal implementation method you plan
> to use, and any user-visible changes (new syntax, etc)."
> 
> > On Wed, Jan 11, 2023 at 02:09:56PM +0700, John Naylor wrote:
> > > I've come up with some revised language, including s/15/16/ and removing
> the
> > > category of "[E]" (easier to implement), since it wouldn't be here if it
> were
> > > actually easy:
> >
> > I think it is still possible for a simple item to be identified as
> > wanted and easy, but not completed and put on the TODO list.
> 
> Theoretically it's possible, but in practice no one puts any easy items here.
> Currently, there are three marked as easy:
> 
> pg_dump:
> - Dump security labels and comments on databases in a way that allows to load a
> dump into a differently named database
> - Add full object name to the tag field. eg. for operators we need '=(integer,
> integer)', instead of just '='.
> 
> Dump and restore is critical to get right, and the code base is pretty large
> and hairy, so I don't actually believe for a second that these items are easy.
> 
> ECPG:
> - sqlwarn[6] should be 'W' if the PRECISION or SCALE value specified
> 
> In one sentence there are four uses of jargon that only someone with experience
> would understand. I have hacked around ECPG multiple times and have no idea
> what this means.
> 
> The last two also don't have any motivation spelled out, much less an email
> thread.
> 
> So my inclination is, the [E] marker here is unjustified.

I can now see that just removing the [E] label totally is the right
answer.  Yes, there might be an easy item on there, but the fact we have
three labeled and they are not easy makes me thing [E] is causing more
problems than it solves.

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

Embrace your flaws.  They make you human, rather than perfect,
which you will never be.

On Mon, Jan 30, 2023 at 01:13:45PM +0700, John Naylor wrote:
> On Tue, Jan 24, 2023 at 11:57 PM Bruce Momjian <bruce@momjian.us> wrote:
> > I think we just point them at the TODO list and they will read the top
> > of the list first, no?  I think you are right that we updated the top of
> > the TODO but didn't update the places that link to it.  I am thinking we
> > should just trim down the text linking to it and let the top of the TODO
> > list do its job.
> 
> Okay. How about:
> 
> "It's worth checking if the feature of interest is found in the TODO list on
> our wiki: http://wiki.postgresql.org/wiki/TODO. The entries there often have
> additional information about the feature and may point to reasons why it hasn't
> been implemented yet."

Good.

> > Wow, I would not send a new person to the SQL standard docs.  ;-)  I am
> > thinking we just don't have a good answer to this so let's say less.
> 
> Do I understand right that we could just remove this entire section "What areas
> need work?"?

Yes, I think so.

> > > 2)
> > > from:
> > > "What do I do after choosing an item to work on?
> > >
> > > Send an email to pgsql-hackers with a proposal for what you want to do
> > > (assuming your contribution is not trivial). Working in isolation is not
> > > advisable because others might be working on the same TODO item, or you
> might
> > > have misunderstood the TODO item. In the email, discuss both the internal
> > > implementation method you plan to use, and any user-visible changes (new
> > > syntax, etc)."
> > >
> > > to:
> > > "What do I do after choosing an area to work on?
> > >
> > > Send an email to pgsql-hackers with a proposal for what you want to do
> > > (assuming your contribution is not trivial). Working in isolation is not
> >
> > Can new people identify trivial?
> 
> I'd say they have some idea about that, since we do regularly get typo fixes
> and doc clarifications. Sure there is some grey area, but I don't think the
> dividing point is important. The important thing is, we also sometimes get
> large and invasive patches without design discussion, which we want to
> discourage.

Agreed.

> > I can now see that just removing the [E] label totally is the right
> > answer.  Yes, there might be an easy item on there, but the fact we have
> > three labeled and they are not easy makes me thing [E] is causing more
> > problems than it solves.
> 
> Okay, having heard no objections I'll remove it.

Thanks.

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

Embrace your flaws.  They make you human, rather than perfect,
which you will never be.

John Naylor <john.naylor@enterprisedb.com> writes:
> I've made another pass at this. Previously, I went one or two sections at a
> time, but this time I tried just skimming the whole thing and noting what
> jumps out at me. Also, I've separated things into three categories: Remove,
> move to "not wanted list", and revise. Comments and objections welcome, as
> always.

Generally agree that the items you've listed are obsolete.  Some comments:

> Allow creation of universal binaries for Darwin
> -> From 2008: Is this still a thing?

This entry might stem from 4b362c662.  It's still something that nobody
has bothered to make happen, not even after another architecture
transition on Apple's part.  And there are things partly outside our
control in the area, see d69a419e6.  I doubt it will ever happen.

> Add support for polymorphic arguments and return types to languages other
> than PL/PgSQL
> -> Seems if someone needed this, they would say so (no thread).

I think this is still an issue.  Surprised nobody has yet gotten annoyed
enough to do something about it.

> Add support for OUT and INOUT parameters to languages other than PL/PgSQL
> -> Ditto

And ditto.

> Consider allowing control of upper/lower case folding of unquoted
> identifiers
> -> Would we ever consider this?

I think that one's dead as a doornail.

> (ECPG) Fix nested C comments
> -> What needs fixing? It should work fine.

I might be mistaken, but I think 8ac5e88f9 may have fixed this.

> Improve speed of tab completion
> -> Is this still a problem?

I keep worrying that tab-complete.c will become so ungainly as to
present a human-scale performance problem.  But there's been pretty
much zero complaints so far.  Let's drop this one until some actual
issue emerges.

            regards, tom lane

On Sat, May 13, 2023 at 01:31:21AM -0400, Tom Lane wrote:
> John Naylor <john.naylor@enterprisedb.com> writes:
> > I've made another pass at this. Previously, I went one or two sections at a
> > time, but this time I tried just skimming the whole thing and noting what
> > jumps out at me. Also, I've separated things into three categories: Remove,
> > move to "not wanted list", and revise. Comments and objections welcome, as
> > always.
> 
> Generally agree that the items you've listed are obsolete.  Some comments:

I agree with this email and John's.

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

  Embrace your flaws.  They make you human, rather than perfect,
  which you will never be.

On Sat, May 13, 2023 at 12:42 AM John Naylor
<john.naylor@enterprisedb.com> wrote:
> Add support for polymorphic arguments and return types to languages other than PL/PgSQL
> -> Seems if someone needed this, they would say so (no thread).
>
> Add support for OUT and INOUT parameters to languages other than PL/PgSQL
> -> Ditto

These actually seem like pretty interesting projects.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Sat, 13 May 2023 at 06:42, John Naylor <john.naylor@enterprisedb.com> wrote:
>
>
> On Mon, Feb 6, 2023 at 11:04 AM John Naylor <john.naylor@enterprisedb.com> wrote:
> > I'll try to get back to culling the list items at the end of April.
>
> I've made another pass at this. Previously, I went one or two sections at a time, but this time I tried just skimming
thewhole thing and noting what jumps out at me. Also, I've separated things into three categories: Remove, move to "not
wantedlist", and revise. Comments and objections welcome, as always. 
>
> [...]
> --------------------
> 2. Propose to move to the "Not Wanted list":
>
> (Anything already at the bottom under the heading "Features We Do Not Want", with the exception of "threads in a
singleprocess". I'll just remove that -- if we ever decide that's worth pursuing, it'll be because we decided we can't
reallyavoid it anymore, and in that case we surely don't need to put it here.) 
>
> Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM ANALYZE, and CLUSTER
> -> There are external tools that help with this kind of analysis

Althrough there are external tools which help with the analysis, the
silent complaint of this item seems to be "PostgreSQL doesn't provide
the user with actionable maintenance tasks/DDL info", and I
wholeheartedly agree with that. Finding out which plans are bad, why
they're bad, and how to fix them currently has quite a steep learning
curve; and while external tools do help, they are not at all commonly
available.

The result I got when searching for "automatic postgresql index
suggestions" was a combination of hypopg, pg_qualstats and some manual
glue to get suggested indexes in the current database - but none of
these are available in the main distribution.

I'm not asking this to be part of the main PostgreSQL binary, but I
don't think that the idea of 'automated suggestions' should be moved
to the "not wanted" list - I'd suggest adding it to a list for Contrib
instead, if we're insisting on removing it from the main TODO list.

Kind regards,

Matthias van de Meent

PS. note how we already have _some_ suggestions about vacuum and
reindex in PostgreSQL, but that is only when things are obviously
wrong, and we don't make what I would call intelligent suggestions -
in one place we still suggest to shut down the postmaster and then
vacuum specific databases in single-user mode.

Robert Haas <robertmhaas@gmail.com> writes:
> On Sat, May 13, 2023 at 12:42 AM John Naylor
> <john.naylor@enterprisedb.com> wrote:
>> Add support for polymorphic arguments and return types to languages other than PL/PgSQL
>> -> Seems if someone needed this, they would say so (no thread).
>>
>> Add support for OUT and INOUT parameters to languages other than PL/PgSQL
>> -> Ditto

> These actually seem like pretty interesting projects.

Yeah.  I'm surprised that nobody has gotten around to scratching
this itch yet.

            regards, tom lane

On Mon, May 15, 2023 at 2:05 PM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> > Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM ANALYZE, and CLUSTER
> > -> There are external tools that help with this kind of analysis
>
> Althrough there are external tools which help with the analysis, the
> silent complaint of this item seems to be "PostgreSQL doesn't provide
> the user with actionable maintenance tasks/DDL info", and I
> wholeheartedly agree with that.

Well, if SET PERFORMANCE_TIPS is adding a new GUC, that's inherently
something that could not happen in a contrib module, only in core. If
it's dedicated syntax of some kind, same thing. I am not at all
convinced that adding something spelled SET PERFORMANCE_TIPS in any
form is a good idea, but at the very least someone would have to
propose what the possible values of that option would be and what they
would do in a pretty detailed way for us to decide whether we liked it
or not. It seems to me that it would be quite difficult to get any
kind of consensus. If you came up with several different kinds of
performance tips and made the GUC a comma-separated list of tip types,
I suspect that you'd get a good bit of opposition: maybe Tom would
dislike one of the tip types for some reason, me a second, and Andres
a third. If you remove all three of those you've gutted your
implementation. Whee.

But even if we leave the syntax aside, it is very difficult, IMHO, to
come up with something in this area that makes sense to put in core.
There are so many things you could warn about, and so many possible
algorithms that you could use to emit warnings. We do have a few
things somewhat like this in core, like the warning that checkpoints
are happening too close together, or the hint that when you said DROP
TABLE olders maybe you really meant DROP TABLE orders. But in those
cases, the situation is fairly unambiguous: if your checkpoints are
happening too close together, you should probably raise max_wal_size,
as long as it's not going to run you out of disk space. If you
specified a non-existent object name, you should probably correct the
object name to something that does exist.

But things like CREATE INDEX or CLUSTER are a lot trickier. I struggle
to think of what individual PostgreSQL command would have enough
context to know that those things are a good idea. For example,
clustering a table on an index makes sense if (1) there are queries
that would run faster with the clustering and (2) they are run
frequently enough and are expensive enough that the savings would be
material and (3) the clustering wouldn't degrade so quickly as to be
pointless. But I don't see how it would be possible to discover this
situation without instrumenting the whole workload, or at least having
some trace of the workload. Even if you have the data, you probably
need to do a bunch of number-crunching to come up with good
recommendations, and that's expensive, and you probably have to be OK
with a significantly higher risk of wrong answers, too, because the
past may be different from the future, and the planner's estimates of
what the clustering would save might be wrong.

I wouldn't go so far as to say that doing anything of this sort is
absolutely and categorically hopeless, but suggesting to an aspiring
hacker (or even an established one) that they go try to implement SET
PERFORMANCE_TIPS isn't helpful at all. At least in my opinion, it's
not clear what that means, or that we want it, or what we might want
instead, or even that we want anything at all. We should aim to have a
TODO list filled with things that are actionable and likely to be
worth the effort someone might choose to invest in them.

--
Robert Haas
EDB: http://www.enterprisedb.com

On 2023-May-13, John Naylor wrote:

> --------------------
> 2. Propose to move to the "Not Wanted list":

> Consider having single-page pruning update the visibility map
> -> Comment from Heikki in the thread:
> "I think I was worried about the possible performance impact of having to
> clear the bit in visibility map again. If you're frequently updating a
> tuple so that HOT and page pruning is helping you, setting the bit in
> visibility map seems counter-productive; it's going to be cleared soon
> again by another UPDATE. That's just a hunch, though. Maybe the overhead
> is negligible."

Hmm, I agree with removing the entry from the TODO list, but why is this
something we Do Not Want?  If somebody shows up and do some analysis
that in a certain workload it is beneficial to do this, then I don't
think we should turn them down.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

On Tue, May 16, 2023 at 4:50 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> Hmm, I agree with removing the entry from the TODO list, but why is this
> something we Do Not Want?  If somebody shows up and do some analysis
> that in a certain workload it is beneficial to do this, then I don't
> think we should turn them down.

Yeah, I think Do Not Want should only be to discourage people from
submitting patches for things we know were not going to do, and that
unless we think that's going to be a problem, we should just remove
items completely.

Thanks, John, for working on this, BTW.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Mon, 15 May 2023 at 20:51, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, May 15, 2023 at 2:05 PM Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> > > Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM ANALYZE, and CLUSTER
> > > -> There are external tools that help with this kind of analysis
> >
> > Althrough there are external tools which help with the analysis, the
> > silent complaint of this item seems to be "PostgreSQL doesn't provide
> > the user with actionable maintenance tasks/DDL info", and I
> > wholeheartedly agree with that.
>
> Well, if SET PERFORMANCE_TIPS is adding a new GUC, that's inherently
> something that could not happen in a contrib module, only in core. If

Why wouldn't it? pgss has (amongst others) pgss.save, other extensions
have their own GUCs, why can't this be one of them? Sure, it'd need to
be namespaced to the extension, but that's never held anyone back from
adding their own GUCs.

> it's dedicated syntax of some kind, same thing. I am not at all
> convinced that adding something spelled SET PERFORMANCE_TIPS in any
> form is a good idea,

Well, that part I agree with; the design of this
maintenance/performance helper is very different from how I would
imagine such feature to exist in or alongside PostgreSQL. I did not
make that very clear in my initial mail, sorry for that, but I
wouldn't suggest that "SET PERFORMANCE_TIPS"

> but at the very least someone would have to
> propose what the possible values of that option would be and what they
> would do in a pretty detailed way for us to decide whether we liked it
> or not. It seems to me that it would be quite difficult to get any
> kind of consensus. If you came up with several different kinds of
> performance tips and made the GUC a comma-separated list of tip types,
> I suspect that you'd get a good bit of opposition: maybe Tom would
> dislike one of the tip types for some reason, me a second, and Andres
> a third. If you remove all three of those you've gutted your
> implementation. Whee.

Yes, I know that bikeshedding and strong personal opinions are common
on this list - I know I am guilty of that as well. Yet the expectation
that people will tend to bikeshed shouldn't be a reason to move a
feature to the "Not Wanted" list. If that were the qualifier, then we
wouldn't add any new major features, because there's always going to
be some bikeshedding for major new features.

> But even if we leave the syntax aside, it is very difficult, IMHO, to
> come up with something in this area that makes sense to put in core.
> There are so many things you could warn about, and so many possible
> algorithms that you could use to emit warnings. We do have a few
> things somewhat like this in core, like the warning that checkpoints
> are happening too close together, or the hint that when you said DROP
> TABLE olders maybe you really meant DROP TABLE orders. But in those
> cases, the situation is fairly unambiguous: if your checkpoints are
> happening too close together, you should probably raise max_wal_size,
> as long as it's not going to run you out of disk space. If you
> specified a non-existent object name, you should probably correct the
> object name to something that does exist.
>
> But things like CREATE INDEX or CLUSTER are a lot trickier. I struggle
> to think of what individual PostgreSQL command would have enough
> context to know that those things are a good idea. For example,
> clustering a table on an index makes sense if (1) there are queries
> that would run faster with the clustering and (2) they are run
> frequently enough and are expensive enough that the savings would be
> material and (3) the clustering wouldn't degrade so quickly as to be
> pointless. But I don't see how it would be possible to discover this
> situation without instrumenting the whole workload, or at least having
> some trace of the workload. Even if you have the data, you probably
> need to do a bunch of number-crunching to come up with good
> recommendations, and that's expensive, and you probably have to be OK
> with a significantly higher risk of wrong answers, too, because the
> past may be different from the future, and the planner's estimates of
> what the clustering would save might be wrong.

Agreed on most parts, including the "one statement is unlikely to have
enough context". But, not all of those issues need to be tackled to
have actionable suggestions. Just a "look at <X = shared_buffers>
because it is likely you left performance on the table" would be a
good start. Other examples of such suggestions would be detecting a
small fraction of hot updates vs missed-hot updates which could
produce a hint to decrease the fillfactor of a table; a high number of
table scans vs tuples returned could produce an indication about
likely missing indexes; etc. These hints wouldn't necessarily have to
be produced with per-statement hints like the TODO suggests.

> I wouldn't go so far as to say that doing anything of this sort is
> absolutely and categorically hopeless, but suggesting to an aspiring
> hacker (or even an established one) that they go try to implement SET
> PERFORMANCE_TIPS isn't helpful at all. At least in my opinion, it's
> not clear what that means, or that we want it, or what we might want
> instead, or even that we want anything at all. We should aim to have a
> TODO list filled with things that are actionable and likely to be
> worth the effort someone might choose to invest in them.

Agreed; and that's why I'm not against removing the specific wording
of the item. This may not have been clearly described in my previous
mail, but I would instead like to see a TODO list item which covers
the need to improve the number of cases where we provide actionable
advice, and specifically those cases where there is not One Obvious
Issue (OOI;s like when getting close to wraparound; or close
checkpoints, or ...).
For example, we could have an extension, similar to pageinspect, that
scans the catalogs and detects actionable problems like tables with
50%+ unused space, or tables that are clustered on an index but have
lost all semblance of ordering (according to statistics), or ...

I guess my main point is that we gather a lot of statistics, but do
not provide tooling which provides actionable reports. And I think
that is the core of such a todo item - we could use a feature like
that either in core, or more likely in contrib as an extension (like
pgss and pageinspect).


Kind regards,

Matthias van de Meent.

On Tue, May 16, 2023 at 8:18 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> Agreed; and that's why I'm not against removing the specific wording
> of the item. This may not have been clearly described in my previous
> mail, but I would instead like to see a TODO list item which covers
> the need to improve the number of cases where we provide actionable
> advice, and specifically those cases where there is not One Obvious
> Issue (OOI;s like when getting close to wraparound; or close
> checkpoints, or ...).

My vote is for just removing the item, rather than putting it on the
not wanted list. I don't think it's useful to put things as general as
what you say here on the list. But putting this item in the not wanted
section might imply that it's not an area we're looking to improve,
which as you say, is false.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Tue, 16 May 2023 at 14:27, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Tue, May 16, 2023 at 8:18 AM Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> > Agreed; and that's why I'm not against removing the specific wording
> > of the item. This may not have been clearly described in my previous
> > mail, but I would instead like to see a TODO list item which covers
> > the need to improve the number of cases where we provide actionable
> > advice, and specifically those cases where there is not One Obvious
> > Issue (OOI;s like when getting close to wraparound; or close
> > checkpoints, or ...).
>
> My vote is for just removing the item, rather than putting it on the
> not wanted list. I don't think it's useful to put things as general as
> what you say here on the list. But putting this item in the not wanted
> section might imply that it's not an area we're looking to improve,
> which as you say, is false.

That makes sense. Agreed.


Kind regards,

Matthias van de Meent