Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tU7AM-00EEWJ-FD for pgsql-hackers@arkaria.postgresql.org; Sat, 04 Jan 2025 16:38:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tU7AL-00BhvD-2h for pgsql-hackers@arkaria.postgresql.org; Sat, 04 Jan 2025 16:38:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tU7AK-00Bhu6-FY for pgsql-hackers@lists.postgresql.org; Sat, 04 Jan 2025 16:38:20 +0000 Received: from mail-lj1-x234.google.com ([2a00:1450:4864:20::234]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tU7AH-0024hs-2p for pgsql-hackers@lists.postgresql.org; Sat, 04 Jan 2025 16:38:18 +0000 Received: by mail-lj1-x234.google.com with SMTP id 38308e7fff4ca-30167f4c1e3so70502211fa.3 for ; Sat, 04 Jan 2025 08:38:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736008696; x=1736613496; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ugydTXSzNlXDv4l+EJjSp9VW5G6jAxFfTtHJaH1GTgo=; b=itp/sQLRVugjz+1zIMiGocMxsg3UGaLhUc/Eol/E/7vY2m+fvrO1GkZkA8Z0oR0A/1 QQwnteponjD5AZHqhh7iQTNpxBhSt/LRFtd9qGsCMeNRtTTqwoZAruqiYUnU6IhcwaWs uaGZh7PXkJuoMWq5x3CRz9GTmembxB5spdWGL+3HyrlePeiLVPkkAadX630topO/k5rV x8j4MitP2mRwfGTOKrHGEgvt49F/4agHI7HS8Zp4UDVa/xWmCD0wlRi2XEgnQjEKvmvw H4lvuKBjz1CRb4kKrGqEmrzFA5Pa5ghhCFV+kLAwSx10cZ0rYj1XSSruHta65qzFLjno Z7BQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736008696; x=1736613496; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=ugydTXSzNlXDv4l+EJjSp9VW5G6jAxFfTtHJaH1GTgo=; b=EcFfeXTWNlYc6ESmK/2XrllSPZUiPTx34gGMeBUm86kOhKUC/dP3P4kuRlZQt1AUP1 p9AOHgiC/yZH8EYyIPNspYsKYFv3BEiq/EY7p0SMxI6mFUOH6VmBRa4Yg7nd0JyfEsSl mmYUF3/OsS33MsOf2X1Yj7rHIXqPC2Tv3pqoaKJGF4Zbpwi8If/xwH8E2xjU+JqsbGjt xUaVmJBJ4DkEXlcQU8pLZjtdZLTe91lxp53v+bHhj8rcijH1TZn11U/IpmfI6dJU2rnO bU1hw1u31Al1rnBpxY9XwYf+qNl1qEWsaZ+rHTGXKM6FEXdfSDsPzx+GrD0KpaBjDGjU f/+g== X-Forwarded-Encrypted: i=1; AJvYcCVKToDBhuWQujpTc+DWbFTNKNs/mSlFOnmZXtduYVF2q8nZK0Y0B7I1jqtv96t0TCLn2WtjZJBzL1qkm/oc@lists.postgresql.org X-Gm-Message-State: AOJu0YyK4zyxHoFm9LBWvMHY0uQceSkc+TIy/tqsoKsdJGWG4253Koj6 XEUITQeW6Zn8D8n5T6FOoa/97vXAh8OyB9I/fJcvXruirEJKC5C79AFlnNeC3EB92FR3tJqQFNw xx5GAYYNzZAOp3gJkp2jI300qYCg= X-Gm-Gg: ASbGnctRA4SMbmkBDWrzGdbcG/Bux01CX2BP/o8GNZovEitkTa5/Kd4Bs2bCAA36iFQ lVs/bX6PrsiEMgsK7vXkm8nwMI0KNFx6CGd4lBv0= X-Google-Smtp-Source: AGHT+IECJ2t/Eyj6pqscXyIErOOprwlKa1Bft3YuMrPl1X9PeZfWrQvgjlSB5KNALiyQe0r6HDolwSNnWyXq3La4utA= X-Received: by 2002:a05:651c:2204:b0:300:1d45:8705 with SMTP id 38308e7fff4ca-3046850a2b0mr164979821fa.1.1736008695718; Sat, 04 Jan 2025 08:38:15 -0800 (PST) MIME-Version: 1.0 References: <1342498.1729444411@sss.pgh.pa.us> <1445998.1729482404@sss.pgh.pa.us> <2062830.1729625620@sss.pgh.pa.us> <2265411.1729699470@sss.pgh.pa.us> <2354718.1729737539@sss.pgh.pa.us> <2581216.1729794746@sss.pgh.pa.us> <1948345.1730500073@sss.pgh.pa.us> <3797606.1732045516@sss.pgh.pa.us> In-Reply-To: <3797606.1732045516@sss.pgh.pa.us> From: Michel Pelletier Date: Sat, 4 Jan 2025 08:37:39 -0800 Message-ID: Subject: Re: Using Expanded Objects other than Arrays from plpgsql To: Tom Lane Cc: Pavel Stehule , pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a2ab71062ae40538" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a2ab71062ae40538 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Nov 19, 2024 at 11:45=E2=80=AFAM Tom Lane wrote= : > Pavel Stehule writes: > > =C3=BAt 19. 11. 2024 v 18:51 odes=C3=ADlatel 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 --000000000000a2ab71062ae40538 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Nov 19, 2024 at 11:45=E2=80=AFAM = Tom Lane <tgl@sss.pgh.pa.us>= wrote:
Pavel Stehule <pavel.stehule@gmail.com> wr= ites:
> =C3=BAt 19. 11. 2024 v 18:51 odes=C3=ADlatel Michel Pelletier <
> pellet= ier.michel@gmail.com> napsal:
>> A couple years ago I tried to compress what I learned about expand= ed
>> objects into a dummy extension that just provides the necessary >> boilerplate.=C2=A0 It wasn't great but a start:
>> https://github.com/michelp/pgexpanded
>> Pavel Stehule indicated this might be a good example to put into c= ontrib:

> another position can be src/test/modules - I think so your example is<= br> > "similar" to plsample

Yeah.=C2=A0 I think we've largely adopted the position that contrib sho= uld
contain installable modules that do something potentially useful to
end-users.=C2=A0 A pure skeleton wouldn't be that, but if it's fles= hed out
enough to be test code for some core features then src/test/modules
could be a reasonable home.

I've ci= rcled back on this task to do some work improving the skeleton code, but go= ing back through our thread I landed on this point Tom made about usefulnes= s vs pure skeleton and my natural desire is to make a simple expanded objec= t 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 wrap= s sqlite's=C2=A0serialize/derserialize=C2=A0API:

https://github.co= m/michelp/postgres-sqlite

As crazy as this sou= nds 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 ve= ry granular RLS policies or other issues of traditional postgres multi-tena= ncy.=C2=A0 Being wire compatible with sqlite-wasm also means databases can = be slurped right from postgres into a browser and synced with no need to tr= ansform data back and forth.=C2=A0 Large chunks of complex structured relat= ional data can be wiped out with a simple row deletion, and since sqlite ca= n't escape from its box and has no scripting ability, it makes a nice s= ecure sandbox that even if users could corrupt it, it would have minimal im= pact on Postgres.

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

Any thoughts?=C2=A0 I want to nail down the core function= ality before I go back and clean up either case based on Tom review comment= s on the skeleton module (most of which still apply since I used the skelet= on to make it!)

-Michel
--000000000000a2ab71062ae40538--