Re: Pgstattuple on Sequences: Seeking Community Feedback on Potential Patch

On Mon, Aug 26, 2024 at 11:44 AM Ayush Vatsa <ayushvatsa1810(at)gmail(dot)com> wrote:
> Hi PostgreSQL Community,
> I have encountered an issue when attempting to use pgstattuple extension with sequences. When executing the following command:
>
> SELECT * FROM pgstattuple('serial');
> ERROR: only heap AM is supported
>
> This behaviour is observed in PostgreSQL versions post v11 [1] , where sequences support in pgstattuple used to work fine. However, this issue slipped through as we did not have any test cases to catch it.
>
> Given the situation, I see two potential paths forward:
> 1/ Reintroduce Support for Sequences in pgstattuple: This would be a relatively small change. However, it's important to note that the purpose of pgstattuple is to provide statistics like the number of tuples, dead tuples, and free space in a relation. Sequences, on the other hand, return only one value at a time and don’t have attributes like dead tuples. Therefore, the result for any sequence would consistently look something like this:
>
> SELECT * FROM pgstattuple('serial');
> table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
> -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
> 8192 | 1 | 41 | 0.5 | 0 | 0 | 0 | 8104 | 98.93
> (1 row)
>
>
> 2/ Explicitly Block Sequence Support in pgstattuple: We could align sequences with other unsupported objects, such as foreign tables, by providing a more explicit error message. For instance:
>
> SELECT * FROM pgstattuple('x');
> ERROR: cannot get tuple-level statistics for relation "x"
> DETAIL: This operation is not supported for foreign tables.
>
> This approach would ensure that the error handling for sequences is consistent with how other unsupported objects are handled.
> Personally, I lean towards the second approach, as it promotes consistency and clarity. However, I would greatly appreciate the community's feedback and suggestions on the best way to proceed.
> Based on the feedback received, I will work on the appropriate patch.
>
> Looking forward to your comments and feedback.

I don't really see what the problem is here. You state that the
information pgstattuple provides isn't really useful for sequences, so
that means there's no real reason to do (1). As for (2), I'm not
opposed to improving error messages but it's not clear to me why you
think that the current one is bad. You say that we should provide a
more explicit error message, but "only heap AM is supported" seems
pretty explicit to me: it doesn't spell out that this only works for
relkind='r', but since relam=heap is only possible for relkind='r',
there's not really any other reasonable interpretation, which IMHO
makes this pretty specific about what the problem is. Maybe you just
find it confusing, but that's a bit different from whether it's
explicit enough.

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