aws / pg_tle

Framework for building trusted language extensions for PostgreSQL
Apache License 2.0
337 stars 31 forks source link

Support customized alignment and storage for pg_tle create type API #266

Closed lyupan closed 9 months ago

lyupan commented 10 months ago

Issue #263.

In the previous version, we used default int4 alignment and plain storage (non-TOASTable). The data will always be stored in-line and not compressed. An error will be reported if the value exceeds the limit: ERROR: row is too big: size xxx, maximum size xxx

With this change, users can customize the alignment and storage strategies. Compression or out-of-line storage can be used depending on the storage strategy (https://www.postgresql.org/docs/current/storage-toast.html).

The default alignment is int4 while the default storage is plain, compatible with previous versions.

A new version 1.3.5 is created along with this change.

anth0nyleung commented 9 months ago

could you also test upgrading to this version works and share the results?

lyupan commented 9 months ago

could you also test upgrading to this version works and share the results?

Yes, tested locally. See results below. (This PR only changes the API to allow extra type options, it doesn't change the type storage/alignment if already defined)

  1. Create pg_tle with old version library and version

    postgres=# create extension pg_tle;
    CREATE EXTENSION
    postgres=# \dx
                        List of installed extensions
    Name   | Version |   Schema   |                Description                 
    ---------+---------+------------+--------------------------------------------
    pg_tle  | 1.3.4   | pgtle      | Trusted Language Extensions for PostgreSQL
    plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
    (2 rows)
  2. Create custom types and data

    
    ...
    -- Variable-length
    postgres=# SELECT pgtle.create_base_type('public', 'test_citext', 'test_citext_in(text)'::regprocedure, 'test_citext_out(bytea)'::regprocedure, -1);
    create_base_type 
    ------------------

(1 row)

-- fixed length postgres=# SELECT pgtle.create_base_type('public', 'test_int2', 'test_int2_in(text)'::regprocedure, 'test_int2_out(bytea)'::regprocedure, 2); create_base_type

(1 row) postgres=# INSERT INTO test_dt_int2 VALUES ('11,22'); INSERT 0 1 postgres=# SELECT * FROM test_dt_int2; c1

11,22 (1 row)

postgres=# INSERT INTO test_dt VALUES ('SELECT'), ('INSERT'), ('UPDATE'), ('DELETE'); INSERT 0 4 postgres=# select * from test_dt; c1

SELECT INSERT UPDATE DELETE (4 rows)


3. Install the new extension library (1.4.0), restart postgres and upgrade

postgres=# ALTER EXTENSION pg_tle UPDATE; ALTER EXTENSION postgres=# \dx List of installed extensions Name | Version | Schema | Description
---------+---------+------------+-------------------------------------------- pg_tle | 1.4.0 | pgtle | Trusted Language Extensions for PostgreSQL plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows)


4. Check the old types (tables) can still read/write

postgres=# select * from test_dt; c1

SELECT INSERT UPDATE DELETE (4 rows)

postgres=# select * from test_dt_int2; c1

11,22 (1 row)

postgres=# delete from test_dt; DELETE 4 postgres=# delete from test_dt_int2; DELETE 1 postgres=# INSERT INTO test_dt VALUES ('SELECT'), ('INSERT'), ('UPDATE'), ('DELETE'); INSERT 0 4 postgres=# INSERT INTO test_dt_int2 VALUES ('11,22'); INSERT 0 1 postgres=# select * from test_dt; c1

SELECT INSERT UPDATE DELETE (4 rows)

postgres=# select * from test_dt_int2; c1

11,22 (1 row)

lyupan commented 9 months ago

Thank you all for the review!