On Mon, Aug 26, 2024 at 11:44 AM Ayush Vatsa <ayushvatsa1810@gmail.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
tonote that the purpose of pgstattuple is to provide statistics like the number of tuples, dead tuples, and free space
ina 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
foreigntables, 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
appreciatethe 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
