Subject: Issue with pgstattuple on Sequences in PostgreSQL

From: Ayush Vatsa <[email-masked]>
Date: Mon, 24 Jun 2024 16:39:28 +0530

Hi PostgreSQL Community,

I was recently exploring the pgstattuple code directory and found this
piece of code:
https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L255-L259
.

It indicates that pgstattuple supports relations, toast tables,
materialized views, and sequences.
However, when I executed a query with a sequence, I encountered the
following error:

postgres=> CREATE SEQUENCE serial START 101;
CREATE SEQUENCE
postgres=> SELECT * FROM pgstattuple('serial');
ERROR:  only heap AM is supported
postgres=>

It got stuck in this if condition -
https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329
How can one use pgstattuple on sequences?

Regards,
Ayush Vatsa

---

From: Kashif Zeeshan <[email-masked]>
Date: Mon, 24 Jun 2024 16:38:33 +0500

Hi

I dont think Sequences are support, please refer to the following
documentation.

https://www.postgresql.org/docs/current/pgstattuple.html

Regards
Kashif Zeeshan

---

From: Ron Johnson <[email-masked]>
Date: Mon, 24 Jun 2024 08:56:23 -0400

On Mon, Jun 24, 2024 at 7:09 AM Ayush Vatsa <[email-masked]>
wrote:
[snip]

> How can one use pgstattuple on sequences?
Out of curiosity... *why*?

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 24 Jun 2024 06:35:54 -0700

On Monday, June 24, 2024, Ayush Vatsa <[email-masked]> wrote:
> piece of code: https://github.com/postgres/postgres/blob/master/contrib/
> pgstattuple/pgstattuple.c#L255-L259.
>
> It got stuck in this if condition - https://github.com/postgres/
> postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329
>
>

As-is?  Doesn’t look like you can.  I agree it’s a documentation bug that
this is the case with a brief explanation of why - sequences do not produce
dead tuples and do not behave like real tables aside from being able to be
selected from (i.e., no SQL update/delete command).

The code should produce an explicit error for that relkind as well.

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 24 Jun 2024 07:32:45 -0700

On 6/24/24 04:09, Ayush Vatsa wrote:
> https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L255-L259 <https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L255-L259>.
> 

File a bug report here:

https://www.postgresql.org/account/login/?next=/account/submitbug/

> https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329 <https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329>

-- 
Adrian Klaver
[email-masked]

---

From: Tatsuo Ishii <[email-masked]>
Date: Tue, 25 Jun 2024 09:37:50 +0900 (JST)

> As-is?  Doesn’t look like you can.

It used to work until v11.

test=# SELECT * FROM pgstattuple('serial');
-[ RECORD 1 ]------+------
table_len          | 8192
tuple_count        | 1
tuple_len          | 41
tuple_percent      | 0.5
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 8104
free_percent       | 98.93

It stopped working by this commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4b82664156c230b59607704506f5b0a32ef490a2

because pgstat_heap() rejects other than heap AM.  I think the v12
release note should have explicitly mentioned that sequences are not
supported by pgstattuple any more.

> I agree it’s a documentation bug that

If so, then the regression test should be fixed as well. Currently
there's no test case for sequences.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp