Re: Add test module for Table Access Method

Hi all,

During the PGCon Unconference session about Table Access Method one missing
item pointed out is that currently we lack documentation and examples of
TAM.

So in order to improve things a bit in this area I'm proposing to add a
test module for Table Access Method similar what we already have for Index
Access Method.

This code is based on the "blackhole_am" implemented by Michael Paquier:
https://github.com/michaelpq/pg_plugins/tree/main/blackhole_am

Regards,

--
Fabrízio de Royes Mello

On Sat, Jun 3, 2023 at 7:42 PM Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)com> wrote:
>
>
> Hi all,
>
> During the PGCon Unconference session about Table Access Method one
missing item pointed out is that currently we lack documentation and
examples of TAM.
>
> So in order to improve things a bit in this area I'm proposing to add a
test module for Table Access Method similar what we already have for Index
Access Method.
>
> This code is based on the "blackhole_am" implemented by Michael Paquier:
https://github.com/michaelpq/pg_plugins/tree/main/blackhole_am
>

Just added some more tests, ran pgindent and also organized a bit some
comments and README.txt.

Regards,

--
Fabrízio de Royes Mello

On Mon, Jun 5, 2023 at 1:24 PM Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)com> wrote:
>
> On Sat, Jun 3, 2023 at 7:42 PM Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)com> wrote:
> >
> >
> > Hi all,
> >
> > During the PGCon Unconference session about Table Access Method one
missing item pointed out is that currently we lack documentation and
examples of TAM.
> >
> > So in order to improve things a bit in this area I'm proposing to add a
test module for Table Access Method similar what we already have for Index
Access Method.
> >
> > This code is based on the "blackhole_am" implemented by Michael
Paquier: https://github.com/michaelpq/pg_plugins/tree/main/blackhole_am
> >
>
> Just added some more tests, ran pgindent and also organized a bit some
comments and README.txt.
>

Rebased version.

--
Fabrízio de Royes Mello

On Sat, Jun 03, 2023 at 07:42:36PM -0400, Fabrízio de Royes Mello wrote:
> So in order to improve things a bit in this area I'm proposing to add a
> test module for Table Access Method similar what we already have for Index
> Access Method.
>
> This code is based on the "blackhole_am" implemented by Michael Paquier:
> https://github.com/michaelpq/pg_plugins/tree/main/blackhole_am

dummy_index_am has included from the start additional coverage for the
various internal add_*_reloption routines, that were never covered in
the core tree. Except if I am missing something, I am not seeing some
of the extra usefulness for the patch you've sent here.
--
Michael

On Thu, 28 Sept 2023 at 10:23, Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Sat, Jun 03, 2023 at 07:42:36PM -0400, Fabrízio de Royes Mello wrote:
> > So in order to improve things a bit in this area I'm proposing to add a
> > test module for Table Access Method similar what we already have for Index
> > Access Method.
> >
> > This code is based on the "blackhole_am" implemented by Michael Paquier:
> > https://github.com/michaelpq/pg_plugins/tree/main/blackhole_am
>
> dummy_index_am has included from the start additional coverage for the
> various internal add_*_reloption routines, that were never covered in
> the core tree. Except if I am missing something, I am not seeing some
> of the extra usefulness for the patch you've sent here.

I have changed the status of commitfest entry to "Returned with
Feedback" as Michael's comments have not yet been resolved. Please
handle the comments and update the commitfest entry accordingly.

Regards,
Vignesh

On Thu, 28 Sept 2023 at 03:08, Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> dummy_index_am has included from the start additional coverage for the
> various internal add_*_reloption routines, that were never covered in
> the core tree. Except if I am missing something, I am not seeing some
> of the extra usefulness for the patch you've sent here.

When trying to implement a table access method in the past I remember
very well that I was having a really hard time finding an example of
one. I remember seeing the dummy_index_am module and being quite
disappointed that there wasn't a similar one for table access methods.
I believe that I eventually found blackhole_am, but it took me quite a
bit of mailing list spelunking to get there. So I think purely for
documentation purposes this addition would already be useful.

Hi,

> When trying to implement a table access method in the past I remember
> very well that I was having a really hard time finding an example of
> one.

To be fair, Postgres uses TAM internally, so there is at least one
complete and up-to-date real-life example. Learning curve for TAMs is
indeed steep, and I wonder if we could do a better job in this respect
e.g. by providing a simpler example. This being said, I know several
people who learned TAM successfully (so far only for R&D tasks) which
indicates that its difficulty is adequate.

--
Best regards,
Aleksander Alekseev

On Mon, 15 Jan 2024 at 14:26, Aleksander Alekseev
<aleksander(at)timescale(dot)com> wrote:
> To be fair, Postgres uses TAM internally, so there is at least one
> complete and up-to-date real-life example.

Sure, but that one is quite hard to follow if you don't already know
lots of details of the heap storage. At least for me, having a minimal
example was extremely helpful and it made for a great code skeleton to
start from.

On Mon, Jan 15, 2024 at 03:40:30PM +0100, Jelte Fennema-Nio wrote:
> On Mon, 15 Jan 2024 at 14:26, Aleksander Alekseev
> <aleksander(at)timescale(dot)com> wrote:
>> To be fair, Postgres uses TAM internally, so there is at least one
>> complete and up-to-date real-life example.
>
> Sure, but that one is quite hard to follow if you don't already know
> lots of details of the heap storage. At least for me, having a minimal
> example was extremely helpful and it made for a great code skeleton to
> start from.

Hmm. I'd rather have it do something useful in terms of test coverage
rather than being just an empty skull.

How about adding the same kind of coverage as dummy_index_am with a
couple of reloptions then? That can serve as a point of reference
when a table AM needs a few custom options. A second idea would be to
show how to use toast relations when implementing your new AM, where a
toast table could be created even in cases where we did not want one
with heap, when it comes to size limitations with char and/or varchar,
and that makes for a simpler needs_toast_table callback.
--
Michaxel

On Tue, Jan 16, 2024 at 10:28 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> Hmm. I'd rather have it do something useful in terms of test coverage
> rather than being just an empty skull.
>
> How about adding the same kind of coverage as dummy_index_am with a
> couple of reloptions then? That can serve as a point of reference
> when a table AM needs a few custom options. A second idea would be to
> show how to use toast relations when implementing your new AM, where a
> toast table could be created even in cases where we did not want one
> with heap, when it comes to size limitations with char and/or varchar,
> and that makes for a simpler needs_toast_table callback.

I think a test module for a table AM will really help developers. Just
to add to the above list - how about the table AM implementing a
simple in-memory (columnar if possible) database storing tables
in-memory and subsequently providing readers with the access to the
tables?

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Tue, 16 Jan 2024 at 13:15, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> On Tue, Jan 16, 2024 at 10:28 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>>
>> Hmm. I'd rather have it do something useful in terms of test coverage
>> rather than being just an empty skull.
>>
>> How about adding the same kind of coverage as dummy_index_am with a
>> couple of reloptions then? That can serve as a point of reference
>> when a table AM needs a few custom options. A second idea would be to
>> show how to use toast relations when implementing your new AM, where a
>> toast table could be created even in cases where we did not want one
>> with heap, when it comes to size limitations with char and/or varchar,
>> and that makes for a simpler needs_toast_table callback.
>
> I think a test module for a table AM will really help developers. Just
> to add to the above list - how about the table AM implementing a
> simple in-memory (columnar if possible) database storing tables
> in-memory and subsequently providing readers with the access to the
> tables?

That's a good idea.

Hi,

> > I think a test module for a table AM will really help developers. Just
> > to add to the above list - how about the table AM implementing a
> > simple in-memory (columnar if possible) database storing tables
> > in-memory and subsequently providing readers with the access to the
> > tables?
>
> That's a good idea.

Personally I would be careful with this idea.

Practice shows that when you show the first incomplete, limited and
buggy PoC it ends up being in the production environment the next day
:) In other words sooner or later there will be users demanding a full
in-memory columnar storage support from Postgres. I believe it would
be a problem. Last time I checked TAM was not extremely good for
implementing proper columnar storages, and there are lots of open
questions when it comes to in-memory tables (e.g. what to do with
foreign keys, inherited tables, etc).

All in all I don't think we should provide something that can look /
be interpreted as first-class alternative storage but in fact is not.

> How about adding the same kind of coverage as dummy_index_am with a
> couple of reloptions then? That can serve as a point of reference
> when a table AM needs a few custom options. A second idea would be to
> show how to use toast relations when implementing your new AM, where a
> toast table could be created even in cases where we did not want one
> with heap, when it comes to size limitations with char and/or varchar,
> and that makes for a simpler needs_toast_table callback.

Good ideas. Additionally we could provide a proxy TAM for a heap TAM
which does nothing but logging used TAM methods, its arguments and
return values. This would be a good example and also potentially can
be used as a debugging tool.

--
Best regards,
Aleksander Alekseev

Hi all,

On Tue, Jan 16, 2024 at 10:40 AM Aleksander Alekseev <
aleksander(at)timescale(dot)com> wrote:

> Hi,
>
> > > I think a test module for a table AM will really help developers. Just
> > > to add to the above list - how about the table AM implementing a
> > > simple in-memory (columnar if possible) database storing tables
> > > in-memory and subsequently providing readers with the access to the
> > > tables?
> >
> > That's a good idea.
>
> Personally I would be careful with this idea.
>
> Practice shows that when you show the first incomplete, limited and
> buggy PoC it ends up being in the production environment the next day
> :) In other words sooner or later there will be users demanding a full
> in-memory columnar storage support from Postgres. I believe it would
> be a problem. Last time I checked TAM was not extremely good for
> implementing proper columnar storages, and there are lots of open
> questions when it comes to in-memory tables (e.g. what to do with
> foreign keys, inherited tables, etc).
>
> All in all I don't think we should provide something that can look /
> be interpreted as first-class alternative storage but in fact is not.
>

I tossed together a table access method for in-memory storage in column
format for experimental purposes over the holidays (I actually have a
row-based one as well, but that is in no shape to share at this point).
It's available under https://github.com/mkindahl/pg_arrow. The intention
was mostly to have something simple to play and experiment with. It is
loosely based on the Apache Arrow Columnar format, but the normal data
structures are not suitable for storing in shared memory so I have tweaked
it a little.

> > How about adding the same kind of coverage as dummy_index_am with a
> > couple of reloptions then? That can serve as a point of reference
> > when a table AM needs a few custom options. A second idea would be to
> > show how to use toast relations when implementing your new AM, where a
> > toast table could be created even in cases where we did not want one
> > with heap, when it comes to size limitations with char and/or varchar,
> > and that makes for a simpler needs_toast_table callback.
>
> Good ideas. Additionally we could provide a proxy TAM for a heap TAM
> which does nothing but logging used TAM methods, its arguments and
> return values. This would be a good example and also potentially can
> be used as a debugging tool.
>

We wrote a table access method for experimenting with and to be able to
trace what happens while executing various statements. It is available
under https://github.com/timescale/pg_traceam for anybody who is interested.

Best wishes,
Mats Kindahl

>
> --
> Best regards,
> Aleksander Alekseev
>
>
>

On Tue, Jan 16, 2024 at 6:15 AM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> On Tue, Jan 16, 2024 at 10:28 AM Michael Paquier <michael(at)paquier(dot)xyz>
> wrote:
> >
> > Hmm. I'd rather have it do something useful in terms of test coverage
> > rather than being just an empty skull.
> >
> > How about adding the same kind of coverage as dummy_index_am with a
> > couple of reloptions then? That can serve as a point of reference
> > when a table AM needs a few custom options. A second idea would be to
> > show how to use toast relations when implementing your new AM, where a
> > toast table could be created even in cases where we did not want one
> > with heap, when it comes to size limitations with char and/or varchar,
> > and that makes for a simpler needs_toast_table callback.
>
> I think a test module for a table AM will really help developers. Just
> to add to the above list - how about the table AM implementing a
> simple in-memory (columnar if possible) database storing tables
> in-memory and subsequently providing readers with the access to the
> tables?
>

Hi,

One idea I wanted to implement is a table access method that you can use to
test the interface, something like a "mock TAM" where you can
programmatically decide on the responses to unit-test the API. I was
thinking that you could implement a framework that allows you to implement
the TAM in some scripting language like Perl, Python, or (horrors) Tcl for
easy prototyping.

Best wishes,
Mats Kindahl

> --
> Bharath Rupireddy
> PostgreSQL Contributors Team
> RDS Open Source Databases
> Amazon Web Services: https://aws.amazon.com
>
>
>