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.
[1]* Reference to Earlier Discussion:* For additional context, I previously
discussed this issue on the pgsql-general mailing list. You can find the
discussion
https://www.postgresql.org/message-id/CACX%2BKaMOd3HHteOJNX7fkWxO%2BR%3DuLJkfKqE2-QUK8fKmKfOwqw%40mail.gmail.com.
In that thread, it was suggested that this could be considered a
documentation bug, and that we might update the documentation and
regression tests accordingly.
Regards
Ayush Vatsa
AWS
