Thread

Including a sample Table Access Method with core code

Hannu Krosing <hannuk@google.com> — 2023-07-03T18:33:32Z

At PgCon 2023 in Ottawa we had an Unconference session on Table Access
Methods [1]

One thing that was briefly mentioned (but is missing from the notes)
is need to have a sample API client in contrib/ , both for having a
2nd user for API to make it more likely that non-heap AMs are doable
and also to serve as an easy starting point for someone interested in
developing a new AM.

There are a few candidates which could be lightweight enough for this

* in-memory temp tables, especially if you specify max table size at
creation and/or limit data types which can be used.

* "overlay tables" - tables which "overlay" another - possibly
read-only - table and store only changed rows and tombstones for
deletions. (this likely would make more sense as a FDW itself as Table
AM currently knows nothing about Primary Keys and these are likely
needed for overlays)

* Table AM as a (pl/)Python Class - this is inspired by the amazing
Multicorn [2] FDW-in-Python tool which made it ridiculously easy to
expose anything (mailbox, twitter feed, git commit history,
you-name-it) as a Foreign Table


Creating any of these seems to be a project of size suitable for a
student course project or maybe Google Summer of Code [3].


Included Mark Dilger directly to this mail as he mentioned he has a
Perl script that makes a functional copy of heap AM that can be
compiled as installed as custom AM.

@mark - maybe you can create 3 boilerplate Table AMs for the above
named `mem_am`, `overlay_am` and `py3_am` and we could put them
somewhere for interested parties to play with ?

[1] https://wiki.postgresql.org/wiki/PgCon_2023_Developer_Unconference#Table_AMs
[2] https://multicorn.org/ - unfortunately unmaintained since 2016,
but there are some forks supporting later PostgreSQL versions
[3] https://wiki.postgresql.org/wiki/GSoC - Google Summer of Code

---
Best Regards
Hannu

Re: Including a sample Table Access Method with core code

Michael Paquier <michael@paquier.xyz> — 2023-07-04T06:00:54Z

On Mon, Jul 03, 2023 at 08:33:32PM +0200, Hannu Krosing wrote:
> One thing that was briefly mentioned (but is missing from the notes)
> is need to have a sample API client in contrib/ , both for having a
> 2nd user for API to make it more likely that non-heap AMs are doable
> and also to serve as an easy starting point for someone interested in
> developing a new AM.

That sounds like a fair thing to have, though templates may live
better under src/test/modules.

> There are a few candidates which could be lightweight enough for this
> 
> * in-memory temp tables, especially if you specify max table size at
> creation and/or limit data types which can be used.
>
> * "overlay tables" - tables which "overlay" another - possibly
> read-only - table and store only changed rows and tombstones for
> deletions. (this likely would make more sense as a FDW itself as Table
> AM currently knows nothing about Primary Keys and these are likely
> needed for overlays)
> 
> * Table AM as a (pl/)Python Class - this is inspired by the amazing
> Multicorn [2] FDW-in-Python tool which made it ridiculously easy to
> expose anything (mailbox, twitter feed, git commit history,
> you-name-it) as a Foreign Table

I cannot say how simple that is without seeing the code, but limiting
the use of an AM to be linked to a single session sounds like a
concept simple enough, limiting its relpersistence on the way.  One
thing that may be also interesting is something that does not go
through the Postgres buffer pool.

> Included Mark Dilger directly to this mail as he mentioned he has a
> Perl script that makes a functional copy of heap AM that can be
> compiled as installed as custom AM.

Similar discussion has happened in 640c198 related to the creation of 
dummy_index_am, where the argument is that such a module needs to
provide value in testing some of the core internals.  dummy_index_am
did so for reloptions on indexes because there was not much coverage
for that part of the system.

> @mark - maybe you can create 3 boilerplate Table AMs for the above
> named `mem_am`, `overlay_am` and `py3_am` and we could put them
> somewhere for interested parties to play with ?

Not sure if that's worth counting, but I also have a table AM template
stored in my plugin repo:
https://github.com/michaelpq/pg_plugins/tree/main/blackhole_am

It does as much as its name states, being able to eat all the data fed
to it.
--
Michael

Re: Including a sample Table Access Method with core code

Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2023-07-05T20:22:32Z

>
> > Included Mark Dilger directly to this mail as he mentioned he has a
> > Perl script that makes a functional copy of heap AM that can be
> > compiled as installed as custom AM.
>
> Similar discussion has happened in 640c198 related to the creation of
> dummy_index_am, where the argument is that such a module needs to
> provide value in testing some of the core internals.  dummy_index_am
> did so for reloptions on indexes because there was not much coverage
> for that part of the system.
>
> > @mark - maybe you can create 3 boilerplate Table AMs for the above
> > named `mem_am`, `overlay_am` and `py3_am` and we could put them
> > somewhere for interested parties to play with ?
>
> Not sure if that's worth counting, but I also have a table AM template
> stored in my plugin repo:
> https://github.com/michaelpq/pg_plugins/tree/main/blackhole_am
>

And based on your `blackhole_am` I've sent a patch [1] to add a
`dummy_table_am` for testing purposes.

Regards,

[1]
https://www.postgresql.org/message-id/CAFcNs+pcU2ib=jvjNZNboD+M2tHO+vD77C_YZJ2rsGR0Tp35mg@mail.gmail.com

--
Fabrízio de Royes Mello