Horton

Thread

Commits

  1. Only allow heap in a number of contrib modules.

  1. Issue with pgstattuple on Sequences in PostgreSQL

    Ayush Vatsa <ayushvatsa1810@gmail.com> — 2024-06-24T11:09:28Z

    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

  2. Re: Issue with pgstattuple on Sequences in PostgreSQL

    Kashif Zeeshan <kashi.zeeshan@gmail.com> — 2024-06-24T11:38:33Z

    Hi

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

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

Regards
Kashif Zeeshan

On Mon, Jun 24, 2024 at 4:09 PM Ayush Vatsa <ayushvatsa1810@gmail.com>
wrote:

> 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
>

  3. Re: Issue with pgstattuple on Sequences in PostgreSQL

    Ron <ronljohnsonjr@gmail.com> — 2024-06-24T12:56:23Z

    On Mon, Jun 24, 2024 at 7:09 AM Ayush Vatsa <ayushvatsa1810@gmail.com>
wrote:
[snip]

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

  4. Re: Issue with pgstattuple on Sequences in PostgreSQL

    David G. Johnston <david.g.johnston@gmail.com> — 2024-06-24T13:35:54Z

    On Monday, June 24, 2024, Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:
>
> 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?
>

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.

  5. Re: Issue with pgstattuple on Sequences in PostgreSQL

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-06-24T14:32:45Z

    On 6/24/24 04:09, Ayush Vatsa wrote:
> 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 <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=>

File a bug report here:

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

> 
> It got stuck in this if condition - 
> 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>
> How can one use pgstattuple on sequences?
> 
> Regards,
> Ayush Vatsa
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  6. Re: Issue with pgstattuple on Sequences in PostgreSQL

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2024-06-25T00:37:50Z

    > On Monday, June 24, 2024, Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:
>>
>> 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?
>>
> 
> 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
> 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.

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

  7. Re: Issue with pgstattuple on Sequences in PostgreSQL

    Ayush Vatsa <ayushvatsa1810@gmail.com> — 2024-06-25T10:30:33Z

    >  I agree it’s a documentation bug
Thanks for confirmation, then maybe I can start a new thread in
pgsql-hackers about this bug and I can myself create a patch for the same.

> then the regression test should be fixed as well
I will add regress test for sequences as well.

We can remove *SEQUENCE* from
https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L255-L259

as well so that users can encounter *ERRCODE_FEATURE_NOT_SUPPORTED*.

Thanks
Ayush Vatsa
SDE AWS