Re: Using Expanded Objects other than Arrays from plpgsql

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

> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > út 19. 11. 2024 v 18:51 odesílatel Michel Pelletier <
> > pelletier.michel@gmail.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