Re: Using Expanded Objects other than Arrays from plpgsql

On Tue, Nov 19, 2024 at 11:45 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > út 19. 11. 2024 v 18:51 odesílatel Michel Pelletier <
> > pelletier(dot)michel(at)gmail(dot)com> napsal:
> >> A couple years ago I tried to compress what I learned about expanded
> >> objects into a dummy extension that just provides the necessary
> >> boilerplate. It wasn't great but a start:
> >> https://github.com/michelp/pgexpanded
> >> Pavel Stehule indicated this might be a good example to put into
> contrib:
>
> > another position can be src/test/modules - I think so your example is
> > "similar" to plsample
>
> Yeah. I think we've largely adopted the position that contrib should
> contain installable modules that do something potentially useful to
> end-users. A pure skeleton wouldn't be that, but if it's fleshed out
> enough to be test code for some core features then src/test/modules
> could be a reasonable home.
>

I've circled back on this task to do some work improving the skeleton code,
but going back through our thread I landed on this point Tom made about
usefulness vs pure skeleton and my natural desire is to make a simple
expanded object that is also useful, so I brainstormed a bit and decided to
try something relatively simple but also (IMO) quite useful, an expanded
datum that wraps sqlite's serialize/derserialize API:

https://github.com/michelp/postgres-sqlite

As crazy as this sounds there are some good use cases here, very easy to
stuff relational data into a completely isolated box without having to
worry about things like very granular RLS policies or other issues of
traditional postgres multi-tenancy. Being wire compatible with sqlite-wasm
also means databases can be slurped right from postgres into a browser and
synced with no need to transform data back and forth. Large chunks of
complex structured relational data can be wiped out with a simple row
deletion, and since sqlite can't escape from its box and has no scripting
ability, it makes a nice secure sandbox that even if users could corrupt
it, it would have minimal impact on Postgres.

It's only a bit more complicated than the pgexpanded skeleton and the
expanded datum bits are is their own separate C file so they can be studied
in isolation. Based on the above comments, this seems something more
appropriate for contrib than test/modules, although I can see there may be
some understandable pushback about something so weird that also has an
external library dependency.

Any thoughts? I want to nail down the core functionality before I go back
and clean up either case based on Tom review comments on the skeleton
module (most of which still apply since I used the skeleton to make it!)

-Michel