Hi Greg,
On Sun, Oct 25, 2026 at 4:33 PM Greg Burd <[email protected]> wrote:
> Hello, here is v11.
On the question you put up front: I think evolution belongs in,
opt-in and off by default as you have it; what follows is about how,
not whether, and point 3 is the one I'd fix first. On your question
in the previous mail, I'd leave the 0003 message as it is; it already
names the case.
v11 applies cleanly on master dfd7ede529. With zstd 1.5.7 and lz4
1.10.0, make check (243 tests) and amcheck pass at -O2 and under
cassert+ASan, with no warnings. The four attached diffs stack in the
order numbered below.
1. lz4 without zstd does not compile. lz4_dict_get() is only under
#ifdef USE_LZ4 and calls dict_fetch(), which is static and inside
#ifdef USE_ZSTD; the call is at toast_dictionary.c:537. The first diff
puts dict_fetch() under USE_ZSTD || USE_LZ4 and keeps only the zstd
callback registration under USE_ZSTD. An lz4-only build then passes
make check, and it reads and writes lz4_dict values whose dictionary a
zstd build trained.
2. With zstd but without lz4, compression_zstd_dict fails: its SET
COMPRESSION lz4 gets "compression method lz4 not supported". The same
diff uses pglz there.
3. 0005 swallows errors, a cancel included.
toast_dictionary_maybe_evolve() catches every ERROR with
FlushErrorState() and no subtransaction (toast_dictionary.c:1112-1290).
A real SIGINT on entry to it: ANALYZE completed and only logged "...
was skipped: canceling statement due to user request". A sampled
external value with a damaged chunk number, which errors inside the
TOAST index scan: ANALYZE returns with seven "resource was not closed"
warnings. From reading, an error halfway through dict_install() would
also commit the old dictionary as non-current with no new one.
The third diff runs the evaluation in an internal subtransaction and
re-throws a cancel. It tests the error code, because ProcessInterrupts()
clears QueryCancelPending before raising it; testing only the pending
flags still swallows the SIGINT. With it, under cassert+ASan, the
SIGINT gives the usual ERROR, the damaged chunk one WARNING and no
leak, and adoption works in plain ANALYZE, BEGIN/COMMIT,
BEGIN/ROLLBACK, a rolled-back savepoint and VACUUM (ANALYZE).
4. The dictionary caches hold one entry. Every switch between
dictionaries fetches the dictionary again, and zstd digests it again.
In user-space instructions per value: one zstd_dict column 23,223,
two in a row 265,236 (4,000 fetches for 4,000 values); lz4 11,501 and
189,002. One column retrained without a rewrite, which evolution now
does on its own, with new rows in space freed among old ones: 258,163
(1,946 fetches for 2,000 values).
The second diff, on top of the first, gives each cache 4 entries keyed
by OID, least recently used out. The victim is picked after
dict_fetch(), because dict_fetch() can run the invalidation callback.
With it, those cases take 2 fetches and 27,872, 10,836 and 27,891
instructions per value; one dictionary stays at 23,223. The 4 is
arbitrary, and lz4 can now keep up to 4 dictionary copies per backend,
each up to 1 MB. Under ASan it stays correct with retrains, VACUUM
FULL and DROP COLUMN from another session between one backend's
reads, eviction across six columns and debug_discard_caches = 1.
5. Evolution without drift: I ran 24 ANALYZEs on unchanged data with a
dictionary trained on the same distribution, setting min_gain to 1.0
so the incumbent stayed. The gains were 0.9% to 2.3% at 600 and at
6,000 held-out values, all under the default 5% margin. At 6,000 one
evaluation calls ZSTD_createCDict() 12,005 times: 12,000 from the
held-out test, one digest per size it measures, and 5 inside ZDICT.
The fourth diff replaces compressed_size() with compressed_total(),
one call per dictionary: two digests, same totals and decision.
6. On the ~1kB numbers: a 1 kB value alone in its row stays below
TOAST_TUPLE_THRESHOLD (heapam.c:2266), so it is stored uncompressed
with every method, even with toast_tuple_target = 128. At 3 kB the
dictionary takes lz4 from 1.94 to 2.50 on docs, 3.58 to 4.76 on json.
Corrupt dictionary values error cleanly under ASan, and substr()
matches the raw text at 56 offset/length pairs on UTF8 and SQL_ASCII.
Numbers: measurements-v11.txt; scripts: tests-v11.txt.
Regards,
Manu
diff --git a/src/backend/access/common/toast_dictionary.c
b/src/backend/access/common/toast_dictionary.c
index 48e9142253..c4364a7fee 100644
--- a/src/backend/access/common/toast_dictionary.c
+++ b/src/backend/access/common/toast_dictionary.c
@@ -310,20 +310,29 @@ dict_cache_invalidate(Datum arg, SysCacheIdentifier
cacheid, uint32 hashvalue)
ddict = NULL;
cdict_id = ddict_id = InvalidOid;
}
+#endif /* USE_ZSTD */
+#if defined(USE_ZSTD) || defined(USE_LZ4)
+/*
+ * Fetch a dictionary's bytes from the catalog. Used by both the zstd and
+ * the lz4 codecs, so it must not depend on USE_ZSTD alone.
+ */
static bytea *
dict_fetch(Oid dictid)
{
- static bool registered = false;
HeapTuple tup;
bytea *result;
+#ifdef USE_ZSTD
+ static bool registered = false;
+
if (!registered)
{
CacheRegisterSyscacheCallback(TOASTDICTOID,
dict_cache_invalidate,
(Datum) 0);
registered = true;
}
+#endif
tup = SearchSysCache1(TOASTDICTOID, ObjectIdGetDatum(dictid));
if (!HeapTupleIsValid(tup))
@@ -335,7 +344,9 @@ dict_fetch(Oid dictid)
ReleaseSysCache(tup);
return result;
}
+#endif
+#ifdef USE_ZSTD
static ZSTD_CDict *
dict_get_cdict(Oid dictid)
{
diff --git a/src/test/regress/expected/compression_zstd_dict.out
b/src/test/regress/expected/compression_zstd_dict.out
index f645687b32..9591e59106 100644
--- a/src/test/regress/expected/compression_zstd_dict.out
+++ b/src/test/regress/expected/compression_zstd_dict.out
@@ -276,7 +276,7 @@ SELECT bool_and(f1 = doc(id::int)) AS all_equal FROM cmdict
WHERE id <= 400;
-- switching the column away from zstd_dict: existing values stay readable
-- until the next rewrite, which recompresses them and drops the dictionary
-ALTER TABLE cmdict ALTER COLUMN f1 SET COMPRESSION lz4;
+ALTER TABLE cmdict ALTER COLUMN f1 SET COMPRESSION pglz;
SELECT bool_and(f1 = doc(id::int)) AS all_equal FROM cmdict WHERE id <= 400;
all_equal
-----------
@@ -299,7 +299,7 @@ SELECT count(*) AS ndicts FROM pg_toast_dictionary WHERE
tdrelid = 'cmdict'::reg
SELECT pg_column_compression(f1), count(*) FROM cmdict WHERE id <= 400 GROUP
BY 1 ORDER BY 1;
pg_column_compression | count
-----------------------+-------
- lz4 | 200
+ pglz | 200
zstd | 200
(2 rows)
diff --git a/src/test/regress/sql/compression_zstd_dict.sql
b/src/test/regress/sql/compression_zstd_dict.sql
index dc8a5cf979..3cbe75b8e0 100644
--- a/src/test/regress/sql/compression_zstd_dict.sql
+++ b/src/test/regress/sql/compression_zstd_dict.sql
@@ -155,7 +155,7 @@ SELECT bool_and(f1 = doc(id::int)) AS all_equal FROM cmdict
WHERE id <= 400;
-- switching the column away from zstd_dict: existing values stay readable
-- until the next rewrite, which recompresses them and drops the dictionary
-ALTER TABLE cmdict ALTER COLUMN f1 SET COMPRESSION lz4;
+ALTER TABLE cmdict ALTER COLUMN f1 SET COMPRESSION pglz;
SELECT bool_and(f1 = doc(id::int)) AS all_equal FROM cmdict WHERE id <= 400;
SELECT count(*) AS ndicts FROM pg_toast_dictionary WHERE tdrelid =
'cmdict'::regclass;
VACUUM FULL cmdict;
diff --git a/src/backend/access/common/toast_dictionary.c
b/src/backend/access/common/toast_dictionary.c
index c4364a7fee..240c408e26 100644
--- a/src/backend/access/common/toast_dictionary.c
+++ b/src/backend/access/common/toast_dictionary.c
@@ -282,6 +282,57 @@ toast_dictionary_keep(Relation rel, AttrNumber attnum,
const varlena *value)
return result;
}
+/*
+ * The per-backend dictionary caches below hold DICT_CACHE_SIZE entries each,
+ * keyed by dictionary OID, least recently used out. More than one entry
+ * matters whenever reads alternate between dictionaries: two dictionary
+ * columns in one row, or one column retrained without a rewrite, whose old
+ * and new values sit side by side. With a single entry each such switch
+ * re-fetches the dictionary and, for zstd, digests it again.
+ */
+#if defined(USE_ZSTD) || defined(USE_LZ4)
+#define DICT_CACHE_SIZE 4
+
+static uint64 dict_cache_clock = 0;
+
+/*
+ * Return the slot of dictid in ids[], or -1. Marks a hit as just used.
+ */
+static int
+dict_cache_lookup(Oid *ids, uint64 *used, Oid dictid)
+{
+ for (int i = 0; i < DICT_CACHE_SIZE; i++)
+ {
+ if (ids[i] == dictid)
+ {
+ used[i] = ++dict_cache_clock;
+ return i;
+ }
+ }
+ return -1;
+}
+
+/*
+ * Pick the slot for a new entry: an empty one, else the least recently used.
+ * Call it only after dict_fetch(), which may run invalidation callbacks that
+ * empty the cache.
+ */
+static int
+dict_cache_victim(Oid *ids, uint64 *used)
+{
+ int victim = 0;
+
+ for (int i = 0; i < DICT_CACHE_SIZE; i++)
+ {
+ if (!OidIsValid(ids[i]))
+ return i;
+ if (used[i] < used[victim])
+ victim = i;
+ }
+ return victim;
+}
+#endif
+
#ifdef USE_ZSTD
/*
* Per-backend cache of digested dictionaries. Digesting builds the entropy
@@ -289,26 +340,30 @@ toast_dictionary_keep(Relation rel, AttrNumber attnum,
const varlena *value)
* ZSTD_CDict/ZSTD_DDict objects are kept and dropped on invalidation of
* pg_toast_dictionary.
*
- * XXX: one entry per direction is enough for a scan of one column; a small
- * hash table would serve workloads that mix many dictionaries.
+ * DICT_CACHE_SIZE entries per direction.
*/
-static Oid cdict_id = InvalidOid;
-static ZSTD_CDict *cdict = NULL;
-static Oid ddict_id = InvalidOid;
-static ZSTD_DDict *ddict = NULL;
+static Oid cdict_ids[DICT_CACHE_SIZE];
+static uint64 cdict_used[DICT_CACHE_SIZE];
+static ZSTD_CDict *cdicts[DICT_CACHE_SIZE];
+static Oid ddict_ids[DICT_CACHE_SIZE];
+static uint64 ddict_used[DICT_CACHE_SIZE];
+static ZSTD_DDict *ddicts[DICT_CACHE_SIZE];
static ZSTD_CCtx *dict_cctx = NULL;
static ZSTD_DCtx *dict_dctx = NULL;
static void
dict_cache_invalidate(Datum arg, SysCacheIdentifier cacheid, uint32 hashvalue)
{
- if (cdict)
- ZSTD_freeCDict(cdict);
- if (ddict)
- ZSTD_freeDDict(ddict);
- cdict = NULL;
- ddict = NULL;
- cdict_id = ddict_id = InvalidOid;
+ for (int i = 0; i < DICT_CACHE_SIZE; i++)
+ {
+ if (cdicts[i])
+ ZSTD_freeCDict(cdicts[i]);
+ if (ddicts[i])
+ ZSTD_freeDDict(ddicts[i]);
+ cdicts[i] = NULL;
+ ddicts[i] = NULL;
+ cdict_ids[i] = ddict_ids[i] = InvalidOid;
+ }
}
#endif /* USE_ZSTD */
@@ -350,44 +405,52 @@ dict_fetch(Oid dictid)
static ZSTD_CDict *
dict_get_cdict(Oid dictid)
{
- if (cdict_id != dictid)
+ int slot = dict_cache_lookup(cdict_ids, cdict_used,
dictid);
+
+ if (slot < 0)
{
bytea *d = dict_fetch(dictid);
- if (cdict)
- ZSTD_freeCDict(cdict);
- cdict_id = InvalidOid;
- cdict = ZSTD_createCDict(VARDATA_ANY(d), VARSIZE_ANY_EXHDR(d),
-
ZSTD_CLEVEL_DEFAULT);
+ slot = dict_cache_victim(cdict_ids, cdict_used);
+ if (cdicts[slot])
+ ZSTD_freeCDict(cdicts[slot]);
+ cdict_ids[slot] = InvalidOid;
+ cdicts[slot] = ZSTD_createCDict(VARDATA_ANY(d),
VARSIZE_ANY_EXHDR(d),
+
ZSTD_CLEVEL_DEFAULT);
pfree(d);
- if (cdict == NULL)
+ if (cdicts[slot] == NULL)
elog(ERROR, "could not load compression dictionary %u",
dictid);
- cdict_id = dictid;
+ cdict_ids[slot] = dictid;
+ cdict_used[slot] = ++dict_cache_clock;
}
if (dict_cctx == NULL && (dict_cctx = ZSTD_createCCtx()) == NULL)
elog(ERROR, "could not create zstd compression context");
- return cdict;
+ return cdicts[slot];
}
static ZSTD_DDict *
dict_get_ddict(Oid dictid)
{
- if (ddict_id != dictid)
+ int slot = dict_cache_lookup(ddict_ids, ddict_used,
dictid);
+
+ if (slot < 0)
{
bytea *d = dict_fetch(dictid);
- if (ddict)
- ZSTD_freeDDict(ddict);
- ddict_id = InvalidOid;
- ddict = ZSTD_createDDict(VARDATA_ANY(d), VARSIZE_ANY_EXHDR(d));
+ slot = dict_cache_victim(ddict_ids, ddict_used);
+ if (ddicts[slot])
+ ZSTD_freeDDict(ddicts[slot]);
+ ddict_ids[slot] = InvalidOid;
+ ddicts[slot] = ZSTD_createDDict(VARDATA_ANY(d),
VARSIZE_ANY_EXHDR(d));
pfree(d);
- if (ddict == NULL)
+ if (ddicts[slot] == NULL)
elog(ERROR, "could not load compression dictionary %u",
dictid);
- ddict_id = dictid;
+ ddict_ids[slot] = dictid;
+ ddict_used[slot] = ++dict_cache_clock;
}
if (dict_dctx == NULL && (dict_dctx = ZSTD_createDCtx()) == NULL)
elog(ERROR, "could not create zstd decompression context");
- return ddict;
+ return ddicts[slot];
}
#endif /* USE_ZSTD */
@@ -515,27 +578,32 @@ zstd_dict_compressed_size(const char *dict, size_t
dictlen,
#ifdef USE_LZ4
/*
* Per-backend cache of the raw dictionary bytes most recently used, keyed by
- * OID. Unlike zstd, lz4 has no digested dictionary object: LZ4_loadDict()
+ * OID, DICT_CACHE_SIZE entries. Unlike zstd, lz4 has no digested dictionary
object: LZ4_loadDict()
* and LZ4_decompress_safe_usingDict() take the dictionary bytes directly, so
* caching the bytea is all that is needed. Dropped on invalidation of
* pg_toast_dictionary.
*/
-static Oid lz4dict_id = InvalidOid;
-static bytea *lz4dict = NULL;
+static Oid lz4dict_ids[DICT_CACHE_SIZE];
+static uint64 lz4dict_used[DICT_CACHE_SIZE];
+static bytea *lz4dicts[DICT_CACHE_SIZE];
static void
lz4_dict_cache_invalidate(Datum arg, SysCacheIdentifier cacheid, uint32
hashvalue)
{
- if (lz4dict)
- pfree(lz4dict);
- lz4dict = NULL;
- lz4dict_id = InvalidOid;
+ for (int i = 0; i < DICT_CACHE_SIZE; i++)
+ {
+ if (lz4dicts[i])
+ pfree(lz4dicts[i]);
+ lz4dicts[i] = NULL;
+ lz4dict_ids[i] = InvalidOid;
+ }
}
static bytea *
lz4_dict_get(Oid dictid)
{
static bool registered = false;
+ int slot;
if (!registered)
{
@@ -543,7 +611,8 @@ lz4_dict_get(Oid dictid)
(Datum) 0);
registered = true;
}
- if (lz4dict_id != dictid)
+ slot = dict_cache_lookup(lz4dict_ids, lz4dict_used, dictid);
+ if (slot < 0)
{
bytea *d = dict_fetch(dictid);
bytea *cached;
@@ -557,12 +626,14 @@ lz4_dict_get(Oid dictid)
cached = (bytea *) MemoryContextAlloc(CacheMemoryContext,
VARSIZE(d));
memcpy(cached, d, VARSIZE(d));
pfree(d);
- if (lz4dict)
- pfree(lz4dict);
- lz4dict = cached;
- lz4dict_id = dictid;
+ slot = dict_cache_victim(lz4dict_ids, lz4dict_used);
+ if (lz4dicts[slot])
+ pfree(lz4dicts[slot]);
+ lz4dicts[slot] = cached;
+ lz4dict_ids[slot] = dictid;
+ lz4dict_used[slot] = ++dict_cache_clock;
}
- return lz4dict;
+ return lz4dicts[slot];
}
#endif /* USE_LZ4 */
diff --git a/src/backend/access/common/toast_dictionary.c
b/src/backend/access/common/toast_dictionary.c
index 240c408e26..dbcbced460 100644
--- a/src/backend/access/common/toast_dictionary.c
+++ b/src/backend/access/common/toast_dictionary.c
@@ -62,6 +62,7 @@
#include "access/tableam.h"
#include "access/toast_compression.h"
#include "access/toast_dictionary.h"
+#include "access/xact.h"
#include "catalog/catalog.h"
#include "catalog/indexing.h"
#include "catalog/objectaddress.h"
@@ -78,6 +79,7 @@
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
+#include "utils/resowner.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
#include "utils/timestamp.h"
@@ -1155,7 +1157,11 @@ dict_count(Relation drel, Oid relid, AttrNumber attnum)
* manual trainer), reuses ANALYZE's already-fetched sample rows, and installs
* a new current dictionary only when a held-out size test says a candidate
* beats the current one by the configured margin. The write path is never
- * involved. Any error is logged and swallowed so it cannot abort ANALYZE.
+ * involved. The work runs in an internal subtransaction: an error rolls it
+ * back, releasing whatever the failed step held (a TOAST scan, buffer pins,
+ * a half-done dict_install()), is reported as a WARNING, and ANALYZE goes on.
+ * A query cancel or a pending interrupt is re-thrown instead, so ANALYZE stops
+ * as it would anywhere else.
*/
void
toast_dictionary_maybe_evolve(Relation onerel, AttrNumber attnum,
@@ -1166,6 +1172,7 @@ toast_dictionary_maybe_evolve(Relation onerel, AttrNumber
attnum,
const DictCompressRoutine *routine;
MemoryContext evolvecxt,
oldcxt;
+ ResourceOwner oldowner;
TupleDesc tupdesc = RelationGetDescr(onerel);
/* (a) opted in, and still a dictionary column? */
@@ -1183,13 +1190,18 @@ toast_dictionary_maybe_evolve(Relation onerel,
AttrNumber attnum,
/*
* Everything below runs in a short-lived context so that samples, the
* candidate dictionary, and fetched current-dictionary bytes are all
- * freed on return, and in a PG_TRY so a codec or catalog error degrades
- * to "no evolution this round" rather than aborting ANALYZE.
+ * freed on return, and in an internal subtransaction so that a codec or
+ * catalog error degrades to "no evolution this round" rather than
+ * aborting ANALYZE, without leaking what the failed step held.
*/
- evolvecxt = AllocSetContextCreate(CurrentMemoryContext,
+ oldcxt = CurrentMemoryContext;
+ oldowner = CurrentResourceOwner;
+ evolvecxt = AllocSetContextCreate(oldcxt,
"Dictionary evolution",
ALLOCSET_DEFAULT_SIZES);
- oldcxt = MemoryContextSwitchTo(evolvecxt);
+
+ BeginInternalSubTransaction(NULL);
+ MemoryContextSwitchTo(evolvecxt);
PG_TRY();
{
@@ -1361,7 +1373,9 @@ toast_dictionary_maybe_evolve(Relation onerel, AttrNumber
attnum,
(int64) totalrows, candbuf,
candlen);
done:
- ;
+ ReleaseCurrentSubTransaction();
+ MemoryContextSwitchTo(oldcxt);
+ CurrentResourceOwner = oldowner;
}
PG_CATCH();
{
@@ -1370,18 +1384,31 @@ done:
MemoryContextSwitchTo(oldcxt);
edata = CopyErrorData();
FlushErrorState();
- ereport(LOG,
+ RollbackAndReleaseCurrentSubTransaction();
+ MemoryContextSwitchTo(oldcxt);
+ CurrentResourceOwner = oldowner;
+ MemoryContextDelete(evolvecxt);
+
+ /*
+ * A cancel (including statement_timeout) must stop ANALYZE as
it
+ * would anywhere else. ProcessInterrupts() clears
QueryCancelPending
+ * before raising it, so test the error code; also re-throw if
an
+ * interrupt is still pending.
+ */
+ if (edata->sqlerrcode == ERRCODE_QUERY_CANCELED ||
+ QueryCancelPending || ProcDiePending)
+ ReThrowError(edata);
+
+ ereport(WARNING,
(errmsg("automatic dictionary evolution for
column \"%s\" of \"%s\" was skipped: %s",
NameStr(att->attname),
RelationGetRelationName(onerel),
edata->message)));
FreeErrorData(edata);
- MemoryContextDelete(evolvecxt);
return;
}
PG_END_TRY();
- MemoryContextSwitchTo(oldcxt);
MemoryContextDelete(evolvecxt);
}
diff --git a/src/backend/access/common/toast_dictionary.c
b/src/backend/access/common/toast_dictionary.c
index dbcbced460..a4f61c63e5 100644
--- a/src/backend/access/common/toast_dictionary.c
+++ b/src/backend/access/common/toast_dictionary.c
@@ -542,38 +542,50 @@ zstd_dict_decompress_datum_slice(const varlena *value,
int32 slicelength)
}
/*
- * Compressed size of one value against raw dictionary bytes, for the held-out
- * test of dictionary evolution. Builds a one-shot CDict from the given bytes
- * (not the per-backend digested cache, which keys on OID and a candidate has
- * no OID yet) and returns the compressed byte count without storing anything.
- * Returns 0 on any zstd failure so the caller can treat it as "no estimate."
+ * Total compressed size of the held-out values against raw dictionary bytes,
+ * for the held-out test of dictionary evolution. Digests the bytes into a
+ * one-shot CDict once for all the values (not the per-backend digested cache,
+ * which keys on OID and a candidate has no OID yet) and returns the total
+ * compressed byte count without storing anything. Returns 0 on any zstd
+ * failure so the caller can treat it as "no estimate."
*/
static size_t
-zstd_dict_compressed_size(const char *dict, size_t dictlen,
- const char *val, size_t len)
+zstd_dict_compressed_total(const char *dict, size_t dictlen,
+ const char *vals, const
size_t *sizes, int nvals)
{
#ifndef USE_ZSTD
return 0;
#else
ZSTD_CDict *cd;
- size_t bound = ZSTD_compressBound(len);
- char *out = palloc(bound);
- size_t clen;
+ size_t total = 0;
+ size_t maxlen = 0;
+ char *out;
+ for (int i = 0; i < nvals; i++)
+ maxlen = Max(maxlen, sizes[i]);
if (dict_cctx == NULL && (dict_cctx = ZSTD_createCCtx()) == NULL)
elog(ERROR, "could not create zstd compression context");
cd = ZSTD_createCDict(dict, dictlen, ZSTD_CLEVEL_DEFAULT);
if (cd == NULL)
- {
- pfree(out);
return 0;
+ out = palloc(ZSTD_compressBound(maxlen));
+ for (int i = 0; i < nvals; i++)
+ {
+ size_t clen = ZSTD_compress_usingCDict(dict_cctx, out,
+
ZSTD_compressBound(maxlen),
+
vals, sizes[i], cd);
+
+ if (ZSTD_isError(clen))
+ {
+ total = 0;
+ break;
+ }
+ total += clen;
+ vals += sizes[i];
}
- clen = ZSTD_compress_usingCDict(dict_cctx, out, bound, val, len, cd);
ZSTD_freeCDict(cd);
pfree(out);
- if (ZSTD_isError(clen))
- return 0;
- return clen;
+ return total;
#endif
}
@@ -729,36 +741,44 @@ lz4_dict_decompress_datum_slice(const varlena *value,
int32 slicelength)
}
/*
- * Compressed size of one value against raw dictionary bytes, for the held-out
- * test of dictionary evolution. Loads the bytes into a fresh lz4 stream and
- * returns the compressed byte count without storing anything. Returns 0 on
- * any lz4 failure.
+ * Total compressed size of the held-out values against raw dictionary bytes,
+ * for the held-out test of dictionary evolution. Loads the bytes into a fresh
+ * lz4 stream for each value, as compression does, and returns the total
+ * compressed byte count without storing anything. Returns 0 on any lz4
+ * failure.
*/
static size_t
-lz4_dict_compressed_size(const char *dict, size_t dictlen,
- const char *val, size_t len)
+lz4_dict_compressed_total(const char *dict, size_t dictlen,
+ const char *vals, const
size_t *sizes, int nvals)
{
#ifndef USE_LZ4
return 0;
#else
- LZ4_stream_t *stream;
- int32 bound = LZ4_compressBound(len);
- char *out;
- int32 clen;
+ size_t total = 0;
- if (bound <= 0)
- return 0;
- stream = LZ4_createStream();
- if (stream == NULL)
- return 0;
- LZ4_loadDict(stream, dict, dictlen);
- out = palloc(bound);
- clen = LZ4_compress_fast_continue(stream, val, out, len, bound, 1);
- LZ4_freeStream(stream);
- pfree(out);
- if (clen <= 0)
- return 0;
- return clen;
+ for (int i = 0; i < nvals; i++)
+ {
+ LZ4_stream_t *stream;
+ int32 bound = LZ4_compressBound(sizes[i]);
+ char *out;
+ int32 clen;
+
+ if (bound <= 0)
+ return 0;
+ stream = LZ4_createStream();
+ if (stream == NULL)
+ return 0;
+ LZ4_loadDict(stream, dict, dictlen);
+ out = palloc(bound);
+ clen = LZ4_compress_fast_continue(stream, vals, out, sizes[i],
bound, 1);
+ LZ4_freeStream(stream);
+ pfree(out);
+ if (clen <= 0)
+ return 0;
+ total += clen;
+ vals += sizes[i];
+ }
+ return total;
#endif
}
@@ -770,7 +790,7 @@ static const DictCompressRoutine lz4_dict_routine = {
.decompress = lz4_dict_decompress_datum,
.decompress_slice = lz4_dict_decompress_datum_slice,
.train = dict_train_zdict,
- .compressed_size = lz4_dict_compressed_size,
+ .compressed_total = lz4_dict_compressed_total,
};
/*
@@ -782,7 +802,7 @@ static const DictCompressRoutine zstd_dict_routine = {
.decompress = zstd_dict_decompress_datum,
.decompress_slice = zstd_dict_decompress_datum_slice,
.train = dict_train_zdict,
- .compressed_size = zstd_dict_compressed_size,
+ .compressed_total = zstd_dict_compressed_total,
};
/*
@@ -1335,29 +1355,16 @@ toast_dictionary_maybe_evolve(Relation onerel,
AttrNumber attnum,
adopt = false; /* nothing to judge by; keep
current */
else
{
- size_t curtotal = 0;
- size_t candtotal = 0;
- const char *p = held.data;
- bool ok = true;
-
- for (i = 0; i < nheld; i++)
- {
- size_t c =
routine->compressed_size(VARDATA_ANY(curbytes),
-
VARSIZE_ANY_EXHDR(curbytes),
-
p, heldsizes[i]);
- size_t n =
routine->compressed_size(candbuf, candlen,
-
p, heldsizes[i]);
-
- if (c == 0 || n == 0)
- {
- ok = false;
- break;
- }
- curtotal += c;
- candtotal += n;
- p += heldsizes[i];
- }
- adopt = ok &&
+ /* one call per dictionary: each is digested once, not
per value */
+ size_t curtotal =
routine->compressed_total(VARDATA_ANY(curbytes),
+
VARSIZE_ANY_EXHDR(curbytes),
+
held.data, heldsizes,
+
nheld);
+ size_t candtotal =
routine->compressed_total(candbuf, candlen,
+
held.data, heldsizes,
+
nheld);
+
+ adopt = curtotal != 0 && candtotal != 0 &&
(double) candtotal <= (double) curtotal *
(1.0 - dictionary_evolution_min_gain);
diff --git a/src/include/access/toast_dictionary.h
b/src/include/access/toast_dictionary.h
index 169f801653..ce368d785e 100644
--- a/src/include/access/toast_dictionary.h
+++ b/src/include/access/toast_dictionary.h
@@ -40,11 +40,13 @@
* evolution (see toast_dictionary_maybe_evolve()). train() builds a
* dictionary of at most max_dict_bytes from nsamples concatenated samples
* (sizes[] gives each one's length), writing it into dictbuf and returning
- * its length, or 0 on failure (a decline, not an error). compressed_size()
- * compresses one value against raw dictionary bytes and returns the
- * compressed byte count without storing anything, so the evolution policy can
- * weigh a candidate dictionary against the current one. Both are codec
- * contract points only: the policy never interprets the dictionary bytes.
+ * its length, or 0 on failure (a decline, not an error). compressed_total()
+ * compresses each of nvals concatenated values (sizes[] gives each one's
+ * length) against raw dictionary bytes and returns the total compressed byte
+ * count without storing anything, or 0 if any value fails, so the evolution
+ * policy can weigh a candidate dictionary against the current one with one
+ * call per dictionary. Both are codec contract points only: the policy never
+ * interprets the dictionary bytes.
*/
typedef struct DictCompressRoutine
{
@@ -53,8 +55,9 @@ typedef struct DictCompressRoutine
varlena *(*decompress_slice) (const varlena *value, int32
slicelength);
size_t (*train) (const char *samples, const size_t *sizes,
int nsamples, size_t
max_dict_bytes, void *dictbuf);
- size_t (*compressed_size) (const char *dict, size_t dictlen,
- const
char *val, size_t len);
+ size_t (*compressed_total) (const char *dict, size_t dictlen,
+ const
char *vals, const size_t *sizes,
+ int
nvals);
} DictCompressRoutine;
/* Current dictionary of rel.attnum, or InvalidOid if none */
measurements-v11.txt -- ZSTD TOAST v11 review (uid 163493), 25 Oct 2026
Tree: master dfd7ede529 + v11-0001..0005 (git am clean). gcc 16.2.1,
zstd 1.5.7, lz4 1.10.0. Scripts: tests-v11.txt. Counts and sizes only.
1. Builds and make check (build_v11.sh)
build result
both, -O2 0 warnings; check 243/243; amcheck ok
both, cassert+ASan 0 warnings; check 243/243; amcheck ok;
0 ASan reports
lz4 only FAILS: toast_dictionary.c:537 implicit
declaration of dict_fetch
zstd only builds; check 242/243: compression_zstd_dict
"compression method lz4 not supported"
neither 0 warnings; check 243/243
With nocfbot-v11-lz4-without-zstd.diff.txt: lz4 only and zstd only
0 warnings, check 243/243, amcheck ok. With both diffs: -O2 and
cassert+ASan 0 warnings, check 243/243, amcheck ok, 0 ASan reports.
2. lz4-only build with the first diff (t_fix.sh): cluster written by a
build with both libraries (both diffs), read by the lz4-only one:
R1 803 lz4_dict values (3 external) read whole: true
R2 slices: true; R3/R4 100 new lz4_dict rows written and read: true
R5 never-trained lz4_dict column stored as lz4: true
R6-R8 training, reading zstd_dict, new zstd_dict column: clean
"requires ... zstd support" errors; R9 VACUUM FULL then read: true
3. Dictionary caches (t_cache.sh), 3000-byte json values, 64 kB
dictionaries. fetch = dict_fetch() calls, ddict = ZSTD_createDDict()
calls (gdb); instr = user-space instructions per value (perf stat,
(6 queries - 1) / 5 / values).
v11 with the cache diff
case values fetch ddict instr fetch ddict instr
zstd 1 dict 2000 1 1 23223 1 1 23223
zstd 2 dicts 4000 4000 4000 265236 2 2 27872
lz4 1 dict 2000 1 - 11501 1 - 11485
lz4 2 dicts 4000 4000 - 189002 2 - 10836
retrain, new 1000 1 1 23579 1 1 23599
retrain, mixed 2000 1946 1946 258163 2 2 27891
"retrain": one column, dictionary #1 rows, every other row deleted,
VACUUM, retrained (#2), new rows into the freed slots: 1946 dictionary
changes along the physical scan of 2000 rows. The two columns are
separate runs of the same script; repeated runs move by tens of
instructions per value.
4. Cache diff under invalidation and eviction (t_inval.sh, cassert+ASan):
10 of 10 true (another session retrains, VACUUM FULL, DROP COLUMN
between reads of one backend; six dictionary columns; reads and writes
with debug_discard_caches = 1); amcheck 0 corruption rows; 0 ASan.
5. Evolution in ANALYZE (t_evolve.sh), 60000 rows of 3000-byte json, one
dictionary trained on the same distribution, no drift; every ANALYZE
evaluates (growth 1.0, min_rows 0, min_interval 0), none adopts
(min_gain 1.0); 12 ANALYZE e (v) per statistics target:
target 10 (heldout=600) gain %: 0.9 0.9 1.4 1.5 1.6 1.7 1.7 1.9 1.9
2.0 2.0 2.3
target 100 (heldout=6000) gain %: 1.2 1.2 1.3 1.4 1.7 1.8 1.8 1.9 1.9
2.2 2.2 2.3
All 24 below the 5% default margin; 1 dictionary before and after.
One evaluation at target 100: 12005 ZSTD_createCDict() calls (gdb).
Cancel: a real SIGINT delivered on entry to toast_dictionary_maybe_evolve()
(gdb, single-user backend): ANALYZE completes, the next statement runs,
and the log has LOG "automatic dictionary evolution for column "v" of
"e" was skipped: canceling statement due to user request". Control,
SIGINT on entry to do_analyze_rel(): ERROR "canceling statement due to
user request".
5b. ERROR inside the TOAST fetch of a sampled value (t_evolve_err.sh): one
external zstd_dict value's chunk 1 renumbered to 77 on disk ("unexpected
chunk number 77 (expected 1)", raised inside the TOAST index scan).
ANALYZE, evolution off clean
ANALYZE, evolution on returns; LOG "...was skipped: unexpected chunk
number..."; 7 WARNING "resource was not
closed": 3 buffer pins (toast table blk 7 x2,
toast index blk 1), toast table, toast index
x2, a TupleDesc. Same twice, same at -O2
(v11) and cassert+ASan (v11a); 0 ASan reports.
5c. With nocfbot-v11-evolve-subxact.diff.txt (internal subtransaction,
re-throw on ERRCODE_QUERY_CANCELED or a pending interrupt), all four
diffs, cassert+ASan:
SIGINT on entry to toast_dictionary_maybe_evolve() ERROR "canceling
statement due to user request" (as the
control on do_analyze_rel())
same, mutant testing only QueryCancelPending/ProcDiePending
WARNING "... was skipped: canceling
statement ..." (swallowed: the flag is
cleared before the ERROR is raised)
damaged chunk (5b) 2 x WARNING "... was skipped: unexpected
chunk number ...", 0 "resource was not
closed", 0 ASan reports
adoption (t_evolve_xact.sh, a new key distribution before each step)
ANALYZE 1->2, BEGIN/ANALYZE/INSERT/COMMIT
2->3 (new rows zstd_dict), BEGIN/ANALYZE/
ROLLBACK 3, SAVEPOINT/ANALYZE/ROLLBACK TO
3, VACUUM (ANALYZE) 3->4; one current
dictionary and all values correct after
each; clean server log
5d. With nocfbot-v11-evolve-digest-once.diff.txt, one evaluation at
6000 held-out values, ZSTD_createCDict() by caller (gdb):
before after
held-out test 12000 2
ZDICT training (libzstd) 5 5
Same cluster, every row sampled (t_evolve_eq.sh, 20000 rows, zstd_dict
and lz4_dict): the DEBUG1 evolution lines, cur= and cand= included, are
identical before and after (4 of 4).
6. Corrupt values (t_corrupt.sh, cassert+ASan): 16 bytes of 0xff at
offset 900 of chunk 1 of an external value.
method full slice(1,100) subxact good values after
lz4_dict error error error all true
zstd_dict error error error all true
lz4 error 100 bytes error all true
18 of 18 true, 0 false, 0 ASan reports.
7. Slices (t_slice.sh): substr() at 14 offsets x 4 lengths, 504 values
(200 dictionary inline, 3 dictionary external ~300 kB, 301 compressed
before training), against an uncompressed copy: 0 mismatches in each
of 4 x 28224 checks (lz4_dict, zstd_dict; UTF8 with 2- and 3-byte
characters; SQL_ASCII).
8. Ratio raw/stored (t_size.sh), 3000-byte values, 1000 training rows,
5000 evaluation rows (evaluation rows only):
corpus pglz lz4 zstd lz4_dict 32k/64k/128k zstd_dict 32k/64k/128k
docs 2.21 1.94 2.78 2.47/2.50/2.51 3.62/3.70/3.77
json 5.14 3.58 7.03 4.74/4.76/4.83 9.03/8.97/9.39
random 1.00 1.00 1.36 1.00 (stored raw) 1.36/1.36/1.34
pg_train_toast_dictionary() default dict_size = 65536.
9. 1 kB values (t_small.sh): a 1000-byte value alone in its row is stored
uncompressed with pglz, lz4, zstd, lz4_dict and zstd_dict, with the
default toast_tuple_target and with 128; 3000-byte values are
compressed with each method. heapam.c:2266 calls the toaster only
above TOAST_TUPLE_THRESHOLD.
tests-v11.txt -- the scripts behind measurements-v11.txt, one after another.
Split at the '===== file:' lines (build_v11.sh at the top, the rest in a tests/
directory). Only the default of W (where the src-*, b-* and i-* trees live)
was replaced by a required variable; nothing else differs from what ran.
Each test makes its own small cluster in $D (an empty scratch directory):
D=/some/empty/dir bash tests/t_cache.sh v11
===== file: build_v11.sh =====
#!/bin/bash
# Builds for reviewing v11 of "ZSTD TOAST compression, and an extensible
# compression method encoding" (Greg Burd, 25-10, uid 163493), on master
# dfd7ede529 + v11-0001..0005 (src-v11):
# v11 -O2 --with-lz4 --with-zstd v11a same, cassert + ASan
# v11l -O2 lz4 only v11z -O2 zstd only
# v11n -O2 neither
# v11fl, v11fz src-v11f = v11 + nocfbot-v11-lz4-without-zstd.diff.txt
# v11fc, v11fca, v11fcn src-v11fc = v11f +
nocfbot-v11-dict-cache-on-fix.diff.txt
# (-O2 both / cassert+ASan both / -O2 neither)
# v11e src-v11e = v11fc + nocfbot-v11-evolve-subxact.diff.txt, -O2 both
# v11g, v11ga, v11gl, v11gz, v11gn src-v11g = v11e +
# nocfbot-v11-evolve-digest-once.diff.txt: -O2 both / cassert+ASan both
# / lz4 only / zstd only / neither
# v11em mutant of src-v11g: re-throw only on QueryCancelPending ||
# ProcDiePending (no error-code test), -O2 both
# Then make check (+ contrib/amcheck) where the build succeeded.
# BUILDS and CHECKS pick a subset. make -j8 at most (shared machine).
set -u
W=${W:?directory holding the src-*, b-* and i-* trees}
A=$(cd "$(dirname "$0")" && pwd)
G() { git -c safe.directory='*' -c user.name=pgzstd -c
user.email=pgzstd@localhost "$@"; }
for t in v11 v11f v11fc v11e v11g; do
[ -d $W/src-$t ] || { echo "src-$t missing"; exit 1; }
echo "$t tree: $(GIT_DIR=$W/src-$t/.git G log --format='%h %s'
-1)$(GIT_DIR=$W/src-$t/.git GIT_WORK_TREE=$W/src-$t G diff --quiet || echo ' +
uncommitted diff')"
done
build() { # build NAME TREE CFLAGS LDFLAGS CONFIGURE-ARGS...
local name=$1 tree=$W/src-$2 cf=$3 lf=$4; shift 4
mkdir -p $W/b-$name && cd $W/b-$name
[ -f config.status ] || CFLAGS="$cf" LDFLAGS="$lf" $tree/configure
--prefix=$W/i-$name \
"$@" --quiet > configure.log 2>&1 || { echo "$name: configure FAILED";
tail -5 configure.log; return 1; }
if make -j8 -s > build.log 2>&1; then
make -s -C contrib -j8 > contrib-build.log 2>&1
make -s install > install.log 2>&1 && make -s -C contrib install >
contrib.log 2>&1
echo "$name: built, warnings=$(cat build.log contrib-build.log | grep -c
'warning:')"
grep 'warning:' build.log contrib-build.log | head -5 || true
else
echo "$name: BUILD FAILED"; grep -E 'error|undefined reference' build.log |
head -8
return 1
fi
}
ok=""
for b in ${BUILDS-v11 v11a v11l v11z v11n v11fl v11fz v11fc v11fca v11fcn}; do
case $b in
v11) build v11 v11 -O2 "" --with-lz4 --with-zstd && ok="$ok v11" ;;
v11a) build v11a v11 "-O1 -g -fsanitize=address -fno-omit-frame-pointer"
-fsanitize=address \
--with-lz4 --with-zstd --enable-cassert --enable-debug && ok="$ok
v11a" ;;
v11l) build v11l v11 -O2 "" --with-lz4 --without-zstd && ok="$ok v11l" ;;
v11z) build v11z v11 -O2 "" --without-lz4 --with-zstd && ok="$ok v11z" ;;
v11n) build v11n v11 -O2 "" --without-lz4 --without-zstd && ok="$ok v11n"
;;
v11fl) build v11fl v11f -O2 "" --with-lz4 --without-zstd && ok="$ok v11fl"
;;
v11fz) build v11fz v11f -O2 "" --without-lz4 --with-zstd && ok="$ok v11fz"
;;
v11fc) build v11fc v11fc -O2 "" --with-lz4 --with-zstd && ok="$ok v11fc" ;;
v11fca) build v11fca v11fc "-O1 -g -fsanitize=address
-fno-omit-frame-pointer" -fsanitize=address \
--with-lz4 --with-zstd --enable-cassert --enable-debug && ok="$ok
v11fca" ;;
v11fcn) build v11fcn v11fc -O2 "" --without-lz4 --without-zstd && ok="$ok
v11fcn" ;;
v11e) build v11e v11e -O2 "" --with-lz4 --with-zstd && ok="$ok v11e" ;;
v11g) build v11g v11g -O2 "" --with-lz4 --with-zstd && ok="$ok v11g" ;;
v11ga) build v11ga v11g "-O1 -g -fsanitize=address
-fno-omit-frame-pointer" -fsanitize=address \
--with-lz4 --with-zstd --enable-cassert --enable-debug && ok="$ok
v11ga" ;;
v11gl) build v11gl v11g -O2 "" --with-lz4 --without-zstd && ok="$ok v11gl"
;;
v11gz) build v11gz v11g -O2 "" --without-lz4 --with-zstd && ok="$ok v11gz"
;;
v11gn) build v11gn v11g -O2 "" --without-lz4 --without-zstd && ok="$ok
v11gn" ;;
v11em) [ -d $W/src-v11em ] || { cp -r $W/src-v11g $W/src-v11em &&
python3 -I -c "import sys; p=sys.argv[1]; s=open(p).read(); a='if
(edata->sqlerrcode == ERRCODE_QUERY_CANCELED ||\n\t\t\tQueryCancelPending ||
ProcDiePending)'; assert s.count(a)==1; open(p,'w').write(s.replace(a,'if
(QueryCancelPending || ProcDiePending)'))"
$W/src-v11em/src/backend/access/common/toast_dictionary.c; }
build v11em v11em -O2 "" --with-lz4 --with-zstd && ok="$ok v11em" ;;
esac
done
check() { # check NAME
cd $W/b-$1
export ASAN_OPTIONS=detect_leaks=0:abort_on_error=1:log_path=$W/b-$1/asan
if make -s check > check.log 2>&1; then r=green; else r=RED; fi
echo "$1: make check $r: $(grep -E '^# All [0-9]+ tests passed|tests?
(failed|passed)|^not ok' check.log | tail -3 | tr '\n' ';')"
echo "$1: compression tests: $(grep -E 'compression'
src/test/regress/regression.out 2>/dev/null | grep -v '^#' | awk '{print $2,
$3}' | tr '\n' ';')"
echo "$1: lz4_dict/zstd_dict skipped?: $(grep -l 'skipping TOAST tests'
src/test/regress/results/compression_*dict*.out 2>/dev/null | xargs -r -n1
basename | tr '\n' ' ')"
if make -s -C contrib/amcheck check > amcheck.log 2>&1; then r=green; else
r=RED; fi
echo "$1: contrib/amcheck check $r"
echo "$1: asan reports: $(ls $W/b-$1/asan* 2>/dev/null | wc -l)"
}
for b in ${CHECKS-$ok}; do check $b; done
echo BUILD-DONE
===== file: tests/lib.sh =====
# Shared helpers for the v10 review tests. Source it; then:
# newcluster BUILD ENCODING initdb into $D/data (BUILD = install name)
# start BUILD / stop BUILD run the cluster with that build's binaries
# q DB [psql args] psql as postgres, ON_ERROR_STOP on
# qe DB [psql args] same, ON_ERROR_STOP off (errors expected)
# Small clusters only (shared_buffers 64MB), unix socket in $D.
W=${W:?directory holding the src-*, b-* and i-* trees}
D=${D:?set D to an empty scratch directory}
P=${P:-$((56500 + RANDOM % 100))}
PSQLBIN=${PSQLBIN:-$W/i-v11/bin/psql}
newcluster() {
"$W/i-$1/bin/initdb" -D $D/data -A trust --no-sync --no-data-checksums -U
postgres \
-E $2 --locale=C >/dev/null
cat >> $D/data/postgresql.conf <<CONF
port = $P
unix_socket_directories = '$D'
listen_addresses = ''
shared_buffers = 64MB
max_connections = 20
autovacuum = off
jit = off
CONF
}
start() { "$W/i-$1/bin/pg_ctl" -D $D/data -l $D/log-$1 -w -t 60 start
>/dev/null; CUR=$1; }
stop() { "$W/i-$1/bin/pg_ctl" -D $D/data -m fast -w stop >/dev/null; CUR=; }
q() { local db=$1; shift; "$PSQLBIN" -X -qAt -h $D -p $P -U postgres -d $db -v
ON_ERROR_STOP=1 "$@"; }
qe() { local db=$1; shift; "$PSQLBIN" -X -qAt -h $D -p $P -U postgres -d $db -v
ON_ERROR_STOP=0 "$@" 2>&1; }
cleanup() { [ -n "${CUR:-}" ] && stop $CUR 2>/dev/null; rm -rf $D; }
# 1 kB-ish JSON documents with repetitive keys (bench.sh's json corpus, one
# document per value), and SGML doc slices; value of row g, deterministic.
SQL_FUNCS=$(cat <<'SQL'
CREATE FUNCTION jdoc(g int, size int DEFAULT 1000) RETURNS text LANGUAGE SQL
IMMUTABLE AS $f$
SELECT left(string_agg(format(
'{"id": %s, "customer": {"name": "customer %s", "tier": "%s"}, "status":
"%s", "items": [{"sku": "SKU-%s", "qty": %s, "price": %s}], "tags": ["%s",
"%s"]}',
g * 1000 + i, (g * 31 + i) % 5000, (ARRAY['gold','silver','bronze'])[1 + i
% 3],
(ARRAY['paid','shipped','pending','refunded'])[1 + (g + i) % 4],
(g * 17 + i) % 100000, 1 + i % 9, round(((g * 13 + i) % 100000) / 100.0, 2),
(ARRAY['web','store','app'])[1 + g % 3], (ARRAY['promo','none'])[1 + i %
2]), ','), size)
FROM generate_series(1, size / 150 + 1) i
$f$;
SQL
)
===== file: tests/t_fix.sh =====
#!/bin/bash
# Directed test for nocfbot-v10-lz4-without-zstd.diff.txt: an lz4-only build
# (v10fl) reading and writing lz4_dict values whose dictionary was trained by
# a build with zstd (v10f) on the same cluster -- the dict_fetch() path that
# does not compile in v10. Also: lz4_dict without a dictionary = plain lz4,
# and what training and zstd_dict do without zstd.
# t_fix.sh [WRITER READER] install names (default v10f v10fl)
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
WR=${1:-v10f} RD=${2:-v10fl}
newcluster $WR SQL_ASCII; start $WR
q postgres <<SQL
$SQL_FUNCS
CREATE TABLE l (id int, v text COMPRESSION lz4_dict, h text);
CREATE TABLE z (id int, v text COMPRESSION zstd_dict, h text);
CREATE TABLE ln (id int, v text COMPRESSION lz4_dict, h text);
INSERT INTO l SELECT g, jdoc(g, 3000), md5(jdoc(g, 3000)) FROM
generate_series(1, 500) g;
INSERT INTO z SELECT g, jdoc(g, 3000), md5(jdoc(g, 3000)) FROM
generate_series(1, 500) g;
SELECT 'trained l ' || (pg_train_toast_dictionary('l', 'v') IS NOT NULL);
SELECT 'trained z ' || (pg_train_toast_dictionary('z', 'v') IS NOT NULL);
INSERT INTO l SELECT g, jdoc(g, 3000), md5(jdoc(g, 3000)) FROM
generate_series(501, 800) g;
INSERT INTO l SELECT g, string_agg(jdoc(g * 100 + i, 3000), ''), NULL FROM
generate_series(801, 803) g, generate_series(1, 300) i GROUP BY g;
UPDATE l SET h = md5(v) WHERE h IS NULL;
INSERT INTO z SELECT g, jdoc(g, 3000), md5(jdoc(g, 3000)) FROM
generate_series(501, 800) g;
INSERT INTO ln SELECT g, jdoc(g, 3000), md5(jdoc(g, 3000)) FROM
generate_series(1, 100) g;
SQL
for t in l z ln; do q postgres -c "SELECT 'written by $WR: $t ' ||
string_agg(coalesce(m, 'uncompressed') || '=' || n, ' ') FROM (SELECT
pg_column_compression(v) m, count(*) n FROM $t GROUP BY 1 ORDER BY 1) s"; done
q postgres -c "SELECT 'written by $WR: l external ' || count(*) FROM l WHERE
pg_column_toast_chunk_id(v) IS NOT NULL"
stop $WR
start $RD
echo "now on $RD: $(q postgres -c "SELECT 'enumvals ' ||
array_to_string(enumvals, ',') FROM pg_settings WHERE name =
'default_toast_compression'")"
qe postgres <<'SQL'
SELECT 'R1 lz4_dict read whole ' || count(*) || ' ' || bool_and(md5(v) = h)
FROM l;
SELECT 'R2 lz4_dict slices ' || bool_and(md5(substr(v, 1 + id % 500, 300)) =
md5(substr(jdoc(id, 3000), 1 + id % 500, 300))) FROM l WHERE id <= 800;
INSERT INTO l SELECT g, jdoc(g, 3000), md5(jdoc(g, 3000)) FROM
generate_series(901, 1000) g;
SELECT 'R3 new lz4_dict rows ' || string_agg(m || '=' || n, ' ') FROM (SELECT
pg_column_compression(v) m, count(*) n FROM l WHERE id > 900 GROUP BY 1) s;
SELECT 'R4 new rows read back ' || bool_and(md5(v) = h) FROM l WHERE id > 900;
SELECT 'R5 never-trained lz4_dict column ' || string_agg(DISTINCT
pg_column_compression(v), ',') || ' ' || bool_and(md5(v) = h) FROM ln;
SELECT 'R6 train without zstd:';
SELECT pg_train_toast_dictionary('ln', 'v');
SELECT 'R7 read zstd_dict without zstd:';
SELECT count(*) FROM z WHERE md5(v) = h AND pg_column_compression(v) =
'zstd_dict';
SELECT 'R8 new zstd_dict column without zstd:';
CREATE TABLE z2 (v text COMPRESSION zstd_dict);
SELECT 'R9 VACUUM FULL l, then read:';
VACUUM FULL l;
SELECT 'R9 ' || count(*) || ' ' || bool_and(md5(v) = h) FROM l;
SQL
stop $RD
===== file: tests/t_cache.sh =====
#!/bin/bash
# The per-backend dictionary caches hold one entry (zstd: one ZSTD_DDict;
# lz4: one dictionary copy). Count, for reads that use one dictionary and
# reads that alternate between two:
# fetch calls to dict_fetch() (catalog lookup + copy of the dictionary)
# ddict calls to ZSTD_createDDict() (digest of a zstd dictionary)
# instr user-space instructions per value (perf stat, one query vs six,
# so backend start-up cancels); counts, not time
# Cases: c = two dictionary columns in one row (a,b zstd_dict; la,lb
# lz4_dict, each column its own dictionary); r = one zstd_dict column
# retrained without a rewrite, old- and new-dictionary rows interleaved
# (DELETE every other row, VACUUM, retrain, insert into the freed slots).
# t_cache.sh [BUILD] (default v10)
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
B=${1:-v10}; N=2000
newcluster $B SQL_ASCII; start $B
q postgres <<SQL
$SQL_FUNCS
CREATE TABLE c (id int, a text COMPRESSION zstd_dict, b text COMPRESSION
zstd_dict,
la text COMPRESSION lz4_dict, lb text COMPRESSION lz4_dict);
INSERT INTO c SELECT g, jdoc(g, 3000), jdoc(g + 50000, 3000), jdoc(g, 3000),
jdoc(g + 50000, 3000)
FROM generate_series(1, 500) g;
SELECT 'trained c ' || count(*) FROM (VALUES ('a'), ('b'), ('la'), ('lb'))
v(col),
LATERAL pg_train_toast_dictionary('c', v.col::name) d WHERE d IS NOT NULL;
INSERT INTO c SELECT 10000 + g, jdoc(g + 1000, 3000), jdoc(g + 60000, 3000),
jdoc(g + 1000, 3000), jdoc(g + 60000, 3000)
FROM generate_series(1, $N) g;
CREATE TABLE r (id int, v text COMPRESSION zstd_dict);
INSERT INTO r SELECT 100000 + g, jdoc(g, 3000) FROM generate_series(1, 500) g;
SELECT 'trained r#1 ' || (pg_train_toast_dictionary('r', 'v') IS NOT NULL);
INSERT INTO r SELECT g, jdoc(g + 1000, 3000) FROM generate_series(1, $N) g;
DELETE FROM r WHERE id < 100000 AND id % 2 = 0;
VACUUM r;
SELECT 'trained r#2 ' || (pg_train_toast_dictionary('r', 'v') IS NOT NULL);
INSERT INTO r SELECT 3000 + g, jdoc(g + 9000, 3000) FROM generate_series(1, $N
/ 2) g;
SQL
q postgres -c "SELECT 'c: ' || string_agg(x, ' ') FROM (SELECT col || '=' ||
string_agg(DISTINCT m, ',') x FROM (SELECT 'a' col, pg_column_compression(a) m
FROM c WHERE id > 10000 UNION ALL SELECT 'b', pg_column_compression(b) FROM c
WHERE id > 10000 UNION ALL SELECT 'la', pg_column_compression(la) FROM c WHERE
id > 10000 UNION ALL SELECT 'lb', pg_column_compression(lb) FROM c WHERE id >
10000) s GROUP BY col ORDER BY col) t"
q postgres -c "SELECT 'r: dictionaries ' || count(*) || ', current ' ||
count(*) FILTER (WHERE tdcurrent) FROM pg_toast_dictionary WHERE tdrelid =
'r'::regclass"
# How interleaved r is: changes between a dict#1 row (odd id < 3000) and a
# dict#2 row (id > 3000) along the physical order.
q postgres -c "SELECT 'r: rows ' || count(*) || ', dictionary changes along the
scan ' || sum(chg) FROM (SELECT CASE WHEN (id > 3000) IS DISTINCT FROM lag(id >
3000) OVER (ORDER BY ctid) THEN 1 ELSE 0 END chg FROM r WHERE id < 100000) s"
q postgres -c "SELECT 'sizes: dictionary bytes ' || string_agg(DISTINCT
octet_length(tddict)::text, ',') FROM pg_toast_dictionary"
stop $B
cat > $D/count.gdb <<'GDB'
set pagination off
set confirm off
set breakpoint pending on
break dict_fetch
commands
silent
continue
end
break ZSTD_createDDict
commands
silent
continue
end
run
info breakpoints
GDB
single() { "$W/i-$B/bin/postgres" --single -D $D/data -c shared_buffers=64MB
postgres; }
run_case() { # NAME VALUES SQL
local name=$1 nv=$2 sql=$3 out f dd one six
out=$(echo "$sql" | gdb -q -batch -x $D/count.gdb --args
"$W/i-$B/bin/postgres" --single -D $D/data -c shared_buffers=64MB postgres 2>&1)
f=$(echo "$out" | sed -n '/^Num/,$p' | awk '/<dict_fetch/{s=1;next} /in
ZSTD_createDDict|<ZSTD_createDDict/{s=2;next} /already hit/{h[s]=$4} END{printf
"%d %d", h[1], h[2]}')
one=$(echo "$sql;" | perf stat -x, -e instructions:u "$W/i-$B/bin/postgres"
--single -D $D/data -c shared_buffers=64MB postgres 2>&1 >/dev/null | awk -F,
'/instructions/{print $1}')
six=$(for i in 1 2 3 4 5 6; do echo "$sql;"; done | perf stat -x, -e
instructions:u "$W/i-$B/bin/postgres" --single -D $D/data -c
shared_buffers=64MB postgres 2>&1 >/dev/null | awk -F, '/instructions/{print
$1}')
set -- $f
echo "case=$name values=$nv fetch=$1 ddict=$2 instr_per_value=$(( (six - one)
/ 5 / nv ))"
}
run_case zstd_1dict $N "SELECT sum(length(a || '')) FROM c WHERE id >
10000"
run_case zstd_2dict $((2*N)) "SELECT sum(length(a || '') + length(b || ''))
FROM c WHERE id > 10000"
run_case lz4_1dict $N "SELECT sum(length(la || '')) FROM c WHERE id >
10000"
run_case lz4_2dict $((2*N)) "SELECT sum(length(la || '') + length(lb || ''))
FROM c WHERE id > 10000"
run_case retrain_new $((N/2)) "SELECT sum(length(v || '')) FROM r WHERE id >
3000 AND id < 100000"
run_case retrain_mix $N "SELECT sum(length(v || '')) FROM r WHERE id <
100000"
===== file: tests/t_inval.sh =====
#!/bin/bash
# Correctness of the dictionary caches under invalidation and eviction. One
# backend (S1) fills its caches reading several dictionary columns; between
# its steps another session (S2, through \!) retrains, rewrites (VACUUM FULL
# retires superseded dictionaries) and drops a column. S1 checks every value
# against its md5 after each step, also with debug_discard_caches = 1, and
# reads a table with six dictionary columns (more than four cache entries:
# eviction). Every check line must end in "true"; no ASan report.
# t_inval.sh BUILD (v10a = v10 as posted, v10ca = with the cache diff)
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
B=$1
export ASAN_OPTIONS=detect_leaks=0:abort_on_error=0:log_path=$D/asan
newcluster $B SQL_ASCII; start $B
q postgres <<SQL
$SQL_FUNCS
CREATE TABLE c (id int, a text COMPRESSION zstd_dict, b text COMPRESSION
zstd_dict,
la text COMPRESSION lz4_dict, lb text COMPRESSION lz4_dict);
INSERT INTO c SELECT g, jdoc(g, 3000), jdoc(g + 50000, 3000), jdoc(g, 3000),
jdoc(g + 50000, 3000) FROM generate_series(1, 300) g;
SELECT count(*) FROM (VALUES ('a'), ('b'), ('la'), ('lb')) v(col), LATERAL
pg_train_toast_dictionary('c', v.col::name);
INSERT INTO c SELECT g, jdoc(g, 3000), jdoc(g + 50000, 3000), jdoc(g, 3000),
jdoc(g + 50000, 3000) FROM generate_series(301, 600) g;
CREATE TABLE m (id int, c1 text COMPRESSION zstd_dict, c2 text COMPRESSION
zstd_dict, c3 text COMPRESSION zstd_dict,
c4 text COMPRESSION lz4_dict, c5 text COMPRESSION lz4_dict, c6
text COMPRESSION lz4_dict);
INSERT INTO m SELECT g, jdoc(g, 3000), jdoc(g + 1e4::int, 3000), jdoc(g +
2e4::int, 3000), jdoc(g + 3e4::int, 3000), jdoc(g + 4e4::int, 3000), jdoc(g +
5e4::int, 3000) FROM generate_series(1, 300) g;
SELECT count(*) FROM (VALUES ('c1'), ('c2'), ('c3'), ('c4'), ('c5'), ('c6'))
v(col), LATERAL pg_train_toast_dictionary('m', v.col::name);
INSERT INTO m SELECT g, jdoc(g, 3000), jdoc(g + 1e4::int, 3000), jdoc(g +
2e4::int, 3000), jdoc(g + 3e4::int, 3000), jdoc(g + 4e4::int, 3000), jdoc(g +
5e4::int, 3000) FROM generate_series(301, 600) g;
SQL
S2="$PSQLBIN -X -qAt -h $D -p $P -U postgres -d postgres -v ON_ERROR_STOP=1"
qe postgres > $D/s1.out <<SQL
SELECT 'pid ' || pg_backend_pid();
SELECT 'K1 all four columns ' || bool_and(a = jdoc(id, 3000) AND b = jdoc(id +
50000, 3000) AND la = jdoc(id, 3000) AND lb = jdoc(id + 50000, 3000)) FROM c;
\! $S2 -c "SELECT pg_train_toast_dictionary('c', 'a') IS NOT NULL" -c "SELECT
pg_train_toast_dictionary('c', 'la') IS NOT NULL" -c "INSERT INTO c SELECT g,
jdoc(g, 3000), jdoc(g + 50000, 3000), jdoc(g, 3000), jdoc(g + 50000, 3000) FROM
generate_series(601, 800) g" >/dev/null
SELECT 'K2 after S2 retrained a, la and inserted ' || bool_and(a = jdoc(id,
3000) AND b = jdoc(id + 50000, 3000) AND la = jdoc(id, 3000) AND lb = jdoc(id +
50000, 3000)) FROM c;
INSERT INTO c SELECT g, jdoc(g, 3000), jdoc(g + 50000, 3000), jdoc(g, 3000),
jdoc(g + 50000, 3000) FROM generate_series(801, 900) g;
SELECT 'K3 S1 inserted with the new dictionaries ' || bool_and(a = jdoc(id,
3000) AND la = jdoc(id, 3000)) || ' ' || string_agg(DISTINCT
pg_column_compression(a) || '/' || pg_column_compression(la), ',') FROM c WHERE
id > 800;
\! $S2 -c "VACUUM FULL c" >/dev/null
SELECT 'K4 after VACUUM FULL retired old dictionaries ' || bool_and(a =
jdoc(id, 3000) AND b = jdoc(id + 50000, 3000) AND la = jdoc(id, 3000) AND lb =
jdoc(id + 50000, 3000)) FROM c;
\! $S2 -c "ALTER TABLE c DROP COLUMN b" -c "ALTER TABLE c DROP COLUMN lb"
>/dev/null
SELECT 'K5 after S2 dropped b and lb ' || bool_and(a = jdoc(id, 3000) AND la =
jdoc(id, 3000)) FROM c;
SELECT 'K6 six dictionary columns, eviction ' || bool_and(c1 = jdoc(id, 3000)
AND c2 = jdoc(id + 1e4::int, 3000) AND c3 = jdoc(id + 2e4::int, 3000) AND c4 =
jdoc(id + 3e4::int, 3000) AND c5 = jdoc(id + 4e4::int, 3000) AND c6 = jdoc(id +
5e4::int, 3000)) FROM m;
INSERT INTO m SELECT g, jdoc(g, 3000), jdoc(g + 1e4::int, 3000), jdoc(g +
2e4::int, 3000), jdoc(g + 3e4::int, 3000), jdoc(g + 4e4::int, 3000), jdoc(g +
5e4::int, 3000) FROM generate_series(601, 700) g;
SELECT 'K7 six columns written alternately, read back ' || bool_and(c1 =
jdoc(id, 3000) AND c2 = jdoc(id + 1e4::int, 3000) AND c3 = jdoc(id + 2e4::int,
3000) AND c4 = jdoc(id + 3e4::int, 3000) AND c5 = jdoc(id + 4e4::int, 3000) AND
c6 = jdoc(id + 5e4::int, 3000)) FROM m WHERE id > 600;
SET debug_discard_caches = 1;
SELECT 'K8 debug_discard_caches=1, c ' || bool_and(a = jdoc(id, 3000) AND la =
jdoc(id, 3000)) FROM c WHERE id % 20 = 0;
SELECT 'K9 debug_discard_caches=1, m ' || bool_and(c1 = jdoc(id, 3000) AND c2 =
jdoc(id + 1e4::int, 3000) AND c4 = jdoc(id + 3e4::int, 3000) AND c6 = jdoc(id +
5e4::int, 3000)) FROM m WHERE id % 30 = 0;
INSERT INTO m SELECT g, jdoc(g, 3000), jdoc(g + 1e4::int, 3000), jdoc(g +
2e4::int, 3000), jdoc(g + 3e4::int, 3000), jdoc(g + 4e4::int, 3000), jdoc(g +
5e4::int, 3000) FROM generate_series(701, 705) g;
SELECT 'K10 debug_discard_caches=1, write then read ' || bool_and(c1 = jdoc(id,
3000) AND c3 = jdoc(id + 2e4::int, 3000) AND c5 = jdoc(id + 4e4::int, 3000))
FROM m WHERE id > 700;
SQL
cat $D/s1.out
q postgres -c "CREATE EXTENSION amcheck" -c "SELECT 'amcheck c,m: ' || count(*)
|| ' corruption rows' FROM (SELECT * FROM verify_heapam('c') UNION ALL SELECT *
FROM verify_heapam('m')) s"
asan=$(cat $D/asan.* 2>/dev/null | grep -c "ERROR: AddressSanitizer" || true)
n_true=$(grep -cE "^K[0-9]+ .*true" $D/s1.out || true); n_bad=$(grep -cE
"false|ERROR" $D/s1.out || true)
echo "build=$B checks_true=$n_true (expected 10) false_or_error=$n_bad
asan_reports=$asan"
===== file: tests/t_evolve.sh =====
#!/bin/bash
# v11-0005, automatic dictionary evolution during ANALYZE. Counts, no time.
# A. Noise of the decision: ANALYZE repeated on unchanged data, evolution
# forced to evaluate every time (growth 1.0, min_rows 0, min_interval 0)
# but never to adopt (min_gain 1.0), so the incumbent stays fixed; the
# DEBUG1 line gives gain = 1 - cand/cur per run. Two samples sizes:
# statistics target 10 (3000 sampled rows) and 100 (30000), on 60000
# rows whose distribution never changes (the dictionary was trained on
# the same distribution). Compare the spread with the 5% margin.
# B. Cost of one evaluation: ZSTD_createCDict() calls during one ANALYZE
# (gdb), statistics target 100.
# C. Cancel inside evolution: a real SIGINT delivered to the backend on
# entry to toast_dictionary_maybe_evolve() (gdb, single-user backend);
# control: the same SIGINT on entry to do_analyze_rel().
# t_evolve.sh [BUILD] (default v11)
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
B=${1:-v11}; RUNS=${RUNS:-12}
newcluster $B SQL_ASCII; start $B
q postgres <<SQL
$SQL_FUNCS
CREATE TABLE e (id int, v text COMPRESSION zstd_dict) WITH (autovacuum_enabled
= off);
INSERT INTO e SELECT g, jdoc(g, 3000) FROM generate_series(1, 2000) g;
SELECT 'trained ' || (pg_train_toast_dictionary('e', 'v') IS NOT NULL);
INSERT INTO e SELECT g, jdoc(g, 3000) FROM generate_series(2001, 60000) g;
ALTER TABLE e ALTER COLUMN v SET (dictionary_evolution = on,
dictionary_evolution_growth = 1.0,
dictionary_evolution_min_rows = 0);
SQL
q postgres -c "SELECT 'rows ' || count(*) || ', stored ' || string_agg(DISTINCT
pg_column_compression(v), ',') || ', dictionaries ' || (SELECT count(*) FROM
pg_toast_dictionary) FROM e"
evolve() { # evolve TARGET -> one "gain=" line per run
q postgres -c "ALTER TABLE e ALTER COLUMN v SET STATISTICS $1" >/dev/null
for r in $(seq $RUNS); do
"$PSQLBIN" -X -qAt -h $D -p $P -U postgres -d postgres -c "SET
dictionary_evolution_min_interval = 0" \
-c "SET dictionary_evolution_max_retained = 1000000" -c "SET
dictionary_evolution_min_gain = 1.0" \
-c "SET client_min_messages = debug1" -c "ANALYZE e (v)" 2>&1 | grep -o
'dictionary evolution for "v": .*' || echo "no evaluation line"
done
}
for t in 10 100; do
out=$(evolve $t)
echo "$out" | sed "s/^/A target=$t /"
echo "$out" | grep -o 'gain=-\?[0-9.]*' | cut -d= -f2 | sort -n | tr '\n' ' '
| sed "s/^/A target=$t gains sorted: /"; echo
echo "A target=$t heldout values: $(echo "$out" | grep -o 'heldout=[0-9]*' |
sort -u | tr '\n' ' ') adopted: $(echo "$out" | grep -c 'adopt=1')"
done
q postgres -c "SELECT 'A dictionaries after the runs: ' || count(*) FROM
pg_toast_dictionary"
q postgres -c "ALTER TABLE e ALTER COLUMN v SET STATISTICS 100" >/dev/null
stop $B
GUCS="-c dictionary_evolution_min_interval=0 -c
dictionary_evolution_max_retained=1000000 -c dictionary_evolution_min_gain=1.0
-c shared_buffers=64MB"
cat > $D/cdict.gdb <<'GDB'
set pagination off
set confirm off
set breakpoint pending on
break ZSTD_createCDict
commands
silent
bt 3
continue
end
break toast_dictionary_maybe_evolve
commands
silent
continue
end
run
info breakpoints
GDB
out=$(echo "ANALYZE e" | gdb -q -batch -x $D/cdict.gdb --args
"$W/i-$B/bin/postgres" --single -D $D/data $GUCS -c client_min_messages=debug1
postgres 2>&1)
echo "B ZSTD_createCDict callers: $(echo "$out" | grep -E '^#1 ' | grep -o -E '
in [A-Za-z_0-9]+ \(' | sed 's/ in //; s/ (//' | sort | uniq -c | tr -s ' ' | tr
'\n' ';')"
echo "B one evaluation: ZSTD_createCDict calls $(echo "$out" | sed -n
'/^Num/,$p' | awk '/ZSTD_createCDict/{s=1;next}
/toast_dictionary_maybe_evolve/{s=2;next} /already hit/{h[s]=$4} END{print
h[1]+0 ", evolve entries " h[2]+0}'); $(echo "$out" | grep -o 'heldout=[0-9]*
dictsize=[0-9]*' | head -1)"
inject() { # inject FUNCTION -> the outcome of ANALYZE with a SIGINT on entry
to FUNCTION
cat > $D/sig.gdb <<GDB
set pagination off
set confirm off
handle SIGINT nostop noprint pass
break $1
commands
silent
call (int) kill((int) getpid(), 2)
delete 1
continue
end
run
GDB
printf 'ANALYZE e\nSELECT 1 AS analyze_returned_then_this_ran\n' | gdb -q
-batch -x $D/sig.gdb --args "$W/i-$B/bin/postgres" --single -D $D/data $GUCS
postgres 2>&1 |
grep -E "ERROR|LOG: automatic dictionary|analyze_returned|canceling" | sed
's/^[[:space:]]*//' | sort -u | sed "s/^/C SIGINT at $1: /"
}
inject toast_dictionary_maybe_evolve
inject do_analyze_rel
===== file: tests/t_evolve_err.sh =====
#!/bin/bash
# An ERROR raised inside the TOAST fetch of a sampled value, while
# toast_dictionary_maybe_evolve() detoasts its sample: what does the
# PG_TRY/PG_CATCH without a subtransaction leave behind? One external
# zstd_dict value gets its chunk 1 renumbered on disk, so fetching it raises
# "unexpected chunk number" inside the TOAST index scan. ANALYZE runs with
# evolution off (control) and on; prints the ANALYZE outcome and every
# WARNING/ERROR/PANIC/TRAP in the server log. All rows are sampled (fewer
# rows than the sample size).
# t_evolve_err.sh [BUILD] (default v11a: cassert + ASan)
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
B=${1:-v11a}
export ASAN_OPTIONS=detect_leaks=0:abort_on_error=0:log_path=$D/asan
newcluster $B SQL_ASCII; start $B
q postgres <<SQL
$SQL_FUNCS
CREATE FUNCTION big(k int) RETURNS text LANGUAGE SQL IMMUTABLE AS \$\$
SELECT string_agg(jdoc(k * 100 + i, 3000), '') FROM generate_series(1, 100) i
\$\$;
CREATE TABLE e (id int, v text COMPRESSION zstd_dict) WITH (autovacuum_enabled
= off);
INSERT INTO e SELECT g, jdoc(g, 3000) FROM generate_series(1, 500) g;
SELECT pg_train_toast_dictionary('e', 'v') IS NOT NULL;
INSERT INTO e SELECT 1000 + g, big(g) FROM generate_series(1, 5) g;
SQL
toast=$(q postgres -c "SELECT reltoastrelid::regclass FROM pg_class WHERE
relname = 'e'")
echo "values: $(q postgres -c "SELECT count(*) || ' rows, external ' ||
count(*) FILTER (WHERE pg_column_toast_chunk_id(v) IS NOT NULL) || ', ' ||
string_agg(DISTINCT pg_column_compression(v), ',') FROM e")"
q postgres -c "COPY (SELECT chunk_seq || '|' || encode(chunk_data, 'hex') FROM
$toast WHERE chunk_id::text = (SELECT pg_column_toast_chunk_id(v)::text FROM e
WHERE id = 1003) ORDER BY chunk_seq) TO STDOUT" > $D/chunks.txt
rel=$(q postgres -c "SELECT pg_relation_filepath('$toast')")
q postgres -c CHECKPOINT; stop $B
python3 -I $T/corrupt_seq.py $D/chunks.txt $D/data/$rel 1 77
start $B
echo "read of the damaged value: $(qe postgres -c "SELECT length(v || '') FROM
e WHERE id = 1003" | head -1)"
an() { # an LABEL EVOLUTION(on|off)
q postgres -c "ALTER TABLE e ALTER COLUMN v SET (dictionary_evolution = $2,
dictionary_evolution_growth = 1.0, dictionary_evolution_min_rows = 0)"
>/dev/null
n0=$(wc -l < $D/log-$B)
out=$(qe postgres -c "SET dictionary_evolution_min_interval = 0" -c "SET
dictionary_evolution_max_retained = 1000000" \
-c "SET dictionary_evolution_min_gain = 1.0" -c "ANALYZE e" -c
"SELECT 'ANALYZE returned, next statement ran'" | tr '\n' ' ')
echo "$1: client: $out"
sed -n "$((n0 + 1)),\$p" $D/log-$B | grep -E
"WARNING|ERROR|PANIC|TRAP|FATAL|LOG: automatic|terminated|leak" | sed -E
's/^[0-9-]+ [0-9:.]+ [-+0-9]+ \[[0-9]+\] //' | sort | uniq -c | sed "s/^/$1:
log: /"
}
an "evolution off" off
an "evolution on" on
an "evolution on, again" on
echo "server still up: $(qe postgres -c "SELECT 'yes'")"
echo "asan reports: $(cat $D/asan.* 2>/dev/null | grep -c 'ERROR:
AddressSanitizer' || true)"
===== file: tests/t_evolve_xact.sh =====
#!/bin/bash
# Adoption through the internal subtransaction of
# nocfbot-v11-evolve-subxact.diff.txt, in every transaction shape ANALYZE runs
# in. Before each step that should adopt, the rows are replaced by a new
# distribution (every JSON key renamed with a per-step prefix), so the current
# dictionary no longer fits and the candidate wins; min_gain 0.
# After each step: dictionaries of the column, current ones, and all values
# read back against their md5. Expected counts are printed next to each.
# t_evolve_xact.sh BUILD
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
B=$1
export ASAN_OPTIONS=detect_leaks=0:abort_on_error=0:log_path=$D/asan
newcluster $B SQL_ASCII; start $B
cat >> $D/data/postgresql.conf <<CONF
dictionary_evolution_min_interval = 0
dictionary_evolution_max_retained = 1000
dictionary_evolution_min_gain = 0
CONF
stop $B; start $B
q postgres <<SQL
$SQL_FUNCS
CREATE FUNCTION kdoc(g int, k int) RETURNS text LANGUAGE SQL IMMUTABLE AS \$\$
SELECT regexp_replace(jdoc(g, 3000), '"([a-z]+)"', '"' || repeat('k', k) ||
'q_\\1"', 'g') \$\$;
CREATE TABLE e (id int, v text COMPRESSION zstd_dict, h text) WITH
(autovacuum_enabled = off);
INSERT INTO e SELECT g, kdoc(g, 0), md5(kdoc(g, 0)) FROM generate_series(1,
3000) g;
SELECT pg_train_toast_dictionary('e', 'v') IS NOT NULL;
ALTER TABLE e ALTER COLUMN v SET (dictionary_evolution = on,
dictionary_evolution_growth = 1.0, dictionary_evolution_min_rows = 0);
SQL
drift() { q postgres -c "DELETE FROM e" -c "INSERT INTO e SELECT g, kdoc(g,
$1), md5(kdoc(g, $1)) FROM generate_series(1, 3000) g"; }
st() { q postgres -c "SELECT format('%s: dictionaries %s (expected $2), current
%s, values ok %s', '$1', count(*), count(*) FILTER (WHERE tdcurrent), (SELECT
bool_and(md5(v) = h) FROM e)) FROM pg_toast_dictionary WHERE tdrelid =
'e'::regclass"; }
st "start" 1
drift 1; q postgres -c "SET client_min_messages = debug1" -c "ANALYZE e" 2>&1 |
grep -o "dictionary evolution for.*" || true; st "ANALYZE" 2
drift 2; q postgres -c "BEGIN" -c "ANALYZE e" -c "INSERT INTO e SELECT g,
kdoc(g, 2), md5(kdoc(g, 2)) FROM generate_series(3001, 3100) g" -c "COMMIT"; st
"BEGIN; ANALYZE; INSERT; COMMIT" 3
q postgres -c "SELECT 'rows written after the in-transaction adoption: ' ||
string_agg(DISTINCT pg_column_compression(v), ',') FROM e WHERE id > 3000"
drift 3; q postgres -c "BEGIN" -c "ANALYZE e" -c "ROLLBACK"; st "BEGIN;
ANALYZE; ROLLBACK" 3
q postgres -c "BEGIN" -c "SAVEPOINT s" -c "ANALYZE e" -c "ROLLBACK TO s" -c
"COMMIT"; st "SAVEPOINT; ANALYZE; ROLLBACK TO" 3
q postgres -c "VACUUM (ANALYZE) e"; st "VACUUM (ANALYZE)" 4
n=$(grep -c -E "WARNING|ERROR|TRAP|PANIC" $D/log-$B || true)
echo "server log WARNING/ERROR/TRAP/PANIC lines: $n; asan reports: $(cat
$D/asan.* 2>/dev/null | grep -c 'ERROR: AddressSanitizer' || true)"
===== file: tests/t_evolve_eq.sh =====
#!/bin/bash
# The held-out test gives the same totals with nocfbot-v11-evolve-digest-once
# (one digest per dictionary) as without it (one per value): same cluster, a
# deterministic sample (every row is sampled: fewer rows and blocks than the
# sample size), one zstd_dict and one lz4_dict column, each trained, and the
# DEBUG1 evolution line of each column compared between the two builds.
# t_evolve_eq.sh BEFORE AFTER (install names, e.g. v11e v11g)
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
B1=$1 B2=$2
newcluster $B1 SQL_ASCII; start $B1
q postgres <<SQL
$SQL_FUNCS
CREATE TABLE e (id int, z text COMPRESSION zstd_dict, l text COMPRESSION
lz4_dict) WITH (autovacuum_enabled = off);
INSERT INTO e SELECT g, jdoc(g, 3000), jdoc(g, 3000) FROM generate_series(1,
2000) g;
SELECT pg_train_toast_dictionary('e', 'z') IS NOT NULL,
pg_train_toast_dictionary('e', 'l') IS NOT NULL;
INSERT INTO e SELECT g, jdoc(g, 3000), jdoc(g, 3000) FROM generate_series(2001,
20000) g;
ALTER TABLE e ALTER COLUMN z SET (dictionary_evolution = on,
dictionary_evolution_growth = 1.0, dictionary_evolution_min_rows = 0);
ALTER TABLE e ALTER COLUMN l SET (dictionary_evolution = on,
dictionary_evolution_growth = 1.0, dictionary_evolution_min_rows = 0);
SQL
echo "rows $(q postgres -c "SELECT count(*) FROM e"), blocks $(q postgres -c
"SELECT pg_relation_size('e') / 8192")"
ev() {
for r in 1 2; do
"$PSQLBIN" -X -qAt -h $D -p $P -U postgres -d postgres -c "SET
dictionary_evolution_min_interval = 0" \
-c "SET dictionary_evolution_max_retained = 1000000" -c "SET
dictionary_evolution_min_gain = 1.0" \
-c "SET client_min_messages = debug1" -c "ANALYZE e" 2>&1 | grep -o
'dictionary evolution for ".*' | sed "s/^/run $r: /"
done
}
ev > $D/before.txt; stop $B1; start $B2; ev > $D/after.txt; stop $B2
sed "s/^/$B1 /" $D/before.txt; sed "s/^/$B2 /" $D/after.txt
if [ -s $D/before.txt ] && cmp -s $D/before.txt $D/after.txt; then echo "RESULT
identical ($(wc -l < $D/before.txt) lines)"; else echo "RESULT DIFFERENT"; fi
===== file: tests/corrupt_seq.py =====
"""Overwrite the chunk_seq of one TOAST chunk, on disk.
corrupt_seq.py CHUNKS RELFILE SEQ NEWSEQ
CHUNKS is "chunk_seq|hex of chunk_data" per line, for one value, dumped while
the server ran; RELFILE the TOAST relation file, with the server stopped.
The chunk_seq int4 sits right before chunk_data's 4-byte varlena header, so
this rewrites the 4 bytes at (start of the chunk data) - 8.
"""
import struct
import sys
chunks_path, relfile, seq, newseq = sys.argv[1], sys.argv[2], int(sys.argv[3]),
int(sys.argv[4])
chunk = None
for line in open(chunks_path):
s, hx = line.strip().split("|")
if int(s) == seq:
chunk = bytes.fromhex(hx[2:] if hx.startswith("\\x") else hx)
assert chunk is not None and len(chunk) > 127, "no such chunk, or chunk too
short for a 4-byte header"
raw = bytearray(open(relfile, "rb").read())
at = raw.find(chunk)
assert at >= 8 and raw.find(chunk, at + 1) < 0, "chunk not found once in the
file"
old = struct.unpack_from("<i", raw, at - 8)[0]
assert old == seq, f"expected chunk_seq {seq} before the data, found {old}"
struct.pack_into("<i", raw, at - 8, newseq)
open(relfile, "wb").write(raw)
print(f"chunk_seq {seq} -> {newseq} (file offset {at - 8})")
===== file: tests/corrupt_bytes.py =====
"""Overwrite bytes of one out-of-line TOAST value, on disk.
corrupt_bytes.py CHUNKS RELFILE SEQ OFFSET LEN BYTE
CHUNKS is "chunk_seq|hex of chunk_data" per line, for one value, dumped while
the server ran; RELFILE the TOAST relation file, with the server stopped.
Sets LEN bytes at OFFSET inside chunk SEQ to BYTE (hex, e.g. ff).
"""
import sys
chunks_path, relfile, seq, off, n, byte = sys.argv[1:7]
seq, off, n, byte = int(seq), int(off), int(n), int(byte, 16)
chunk = None
for line in open(chunks_path):
s, hx = line.strip().split("|")
if int(s) == seq:
chunk = bytes.fromhex(hx[2:] if hx.startswith("\\x") else hx)
assert chunk is not None and off + n <= len(chunk), "no such chunk or range"
raw = bytearray(open(relfile, "rb").read())
at = raw.find(chunk)
assert at >= 0 and raw.find(chunk, at + 1) < 0, "chunk not found once in the
file"
raw[at + off:at + off + n] = bytes([byte]) * n
open(relfile, "wb").write(raw)
print(f"chunk_seq {seq}: {n} bytes at {off} set to 0x{byte:02x} (file offset
{at + off})")
===== file: tests/t_corrupt.sh =====
#!/bin/bash
# Corrupt dictionary-compressed values must fail cleanly and leave the
# backend's dictionary caches and contexts usable. Per method (lz4_dict,
# zstd_dict; plain lz4 as the reference), three external values; value 2 gets
# 16 bytes overwritten with 0xff in the middle of its second chunk, on disk.
# Then in ONE session: full read and slice of the corrupt value (expected:
# an error), good values right after (full and slice), the error inside a
# subtransaction, and all good values in one query. Lines must be "true"
# or the expected error; no ASan report.
# t_corrupt.sh BUILD (v10a: cassert + ASan)
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
B=$1
export ASAN_OPTIONS=detect_leaks=0:abort_on_error=0:log_path=$D/asan
newcluster $B SQL_ASCII; start $B
q postgres <<SQL
$SQL_FUNCS
CREATE FUNCTION big(k int) RETURNS text LANGUAGE SQL IMMUTABLE AS \$\$
SELECT string_agg(jdoc(k * 100 + i, 3000), '') FROM generate_series(1, 100) i
\$\$;
SQL
for m in lz4_dict zstd_dict lz4; do
q postgres -c "CREATE TABLE t_$m (id int, v text COMPRESSION $m, h text)" \
-c "INSERT INTO t_$m SELECT g, jdoc(g, 3000), md5(jdoc(g, 3000))
FROM generate_series(100, 400) g"
[ $m != lz4 ] && q postgres -c "SELECT pg_train_toast_dictionary('t_$m', 'v')
IS NOT NULL" >/dev/null
q postgres -c "INSERT INTO t_$m SELECT g, big(g), md5(big(g)) FROM
generate_series(1, 3) g"
echo "$m: $(q postgres -c "SELECT string_agg(id || ':' ||
pg_column_compression(v) || (CASE WHEN pg_column_toast_chunk_id(v) IS NOT NULL
THEN '/ext' ELSE '' END), ' ') FROM t_$m WHERE id <= 3")"
toast=$(q postgres -c "SELECT reltoastrelid::regclass FROM pg_class WHERE
relname = 't_$m'")
q postgres -c "COPY (SELECT chunk_seq || '|' || encode(chunk_data, 'hex')
FROM $toast WHERE chunk_id::text = (SELECT pg_column_toast_chunk_id(v)::text
FROM t_$m WHERE id = 2) ORDER BY chunk_seq) TO STDOUT" > $D/chunks_$m.txt
echo "$m relfile $(q postgres -c "SELECT pg_relation_filepath('$toast')")" >>
$D/relfiles
done
q postgres -c CHECKPOINT; stop $B
while read m _ rel; do echo "$m: $(python3 -I $T/corrupt_bytes.py
$D/chunks_$m.txt $D/data/$rel 1 900 16 ff)"; done < $D/relfiles
start $B
for m in lz4_dict zstd_dict lz4; do
qe postgres > $D/out_$m.txt <<SQL
SELECT 'A0 $m good full before ' || (md5(v) = h) FROM t_$m WHERE id = 1;
SELECT 'E1 $m corrupt full ' || length(v || '') FROM t_$m WHERE id = 2;
SELECT 'A1 $m good full after error ' || (md5(v) = h) FROM t_$m WHERE id = 1;
SELECT 'A2 $m good slice after error ' || (substr(v, 50000, 300) =
substr(big(id), 50000, 300)) FROM t_$m WHERE id = 3;
SELECT 'E2 $m corrupt slice ' || length(substr(v, 1, 100)) FROM t_$m WHERE id =
2;
SELECT 'A3 $m good small values after error ' || bool_and(md5(v) = h) FROM t_$m
WHERE id >= 100;
BEGIN;
SAVEPOINT s;
SELECT 'E3 $m corrupt in subtransaction ' || length(v || '') FROM t_$m WHERE id
= 2;
ROLLBACK TO s;
SELECT 'A4 $m good after rollback to savepoint ' || (md5(v) = h) FROM t_$m
WHERE id = 3;
COMMIT;
SELECT 'A5 $m all good values ' || count(*) || ' ' || bool_and(md5(v) = h) FROM
t_$m WHERE id <> 2;
SQL
cat $D/out_$m.txt
done
asan=$(cat $D/asan.* 2>/dev/null | grep -c "ERROR: AddressSanitizer" || true)
echo "errors: $(cat $D/out_*.txt | grep ERROR | sort | uniq -c | tr -s ' ' | tr
'\n' ';')"
echo "build=$B true=$(cat $D/out_*.txt | grep -c ' true$') (expected 18)
false=$(cat $D/out_*.txt | grep -c ' false$') asan_reports=$asan"
===== file: tests/t_slice.sh =====
#!/bin/bash
# Slices and prefixes of dictionary-compressed values give the same text as
# the uncompressed value, at every offset of a grid, on a UTF8 database with
# multibyte text (2- and 3-byte characters) and on SQL_ASCII. Inline values
# (~3 kB) and external ones (~300 kB). The control column is stored
# uncompressed (STORAGE EXTERNAL).
# t_slice.sh BUILD
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
B=$1
for enc in UTF8 SQL_ASCII; do
rm -rf $D/data; newcluster $B $enc; start $B
[ $enc = UTF8 ] && mb="replace(replace(jdoc(g, 3000), 'customer', 'clienté
ñandú 顧客'), 'paid', 'pagó')" || mb="jdoc(g, 3000)"
q postgres <<SQL
$SQL_FUNCS
CREATE TABLE s (id int, m text, v text, c text);
ALTER TABLE s ALTER COLUMN c SET STORAGE EXTERNAL;
SQL
for m in lz4_dict zstd_dict; do
q postgres <<SQL
CREATE TABLE s_$m (id int, v text COMPRESSION $m, c text);
ALTER TABLE s_$m ALTER COLUMN c SET STORAGE EXTERNAL;
INSERT INTO s_$m SELECT g, $mb, $mb FROM generate_series(1000, 1300) g;
SELECT pg_train_toast_dictionary('s_$m', 'v') IS NOT NULL;
INSERT INTO s_$m SELECT g, $mb, $mb FROM generate_series(1, 200) g;
INSERT INTO s_$m SELECT 500 + k, string_agg(x, ''), string_agg(x, '') FROM
generate_series(1, 3) k,
LATERAL (SELECT $mb AS x FROM generate_series(k * 100, k * 100 + 99) g) s
GROUP BY k;
SQL
q postgres <<SQL
WITH o(off) AS (VALUES (1), (2), (3), (100), (1000), (2047), (2048), (2999),
(3000), (3001), (65536), (150000), (299990), (400000)),
l(len) AS (VALUES (1), (2), (50), (5000)),
r AS (SELECT s.id, o.off, l.len, substr(s.v, o.off, l.len) = substr(s.c,
o.off, l.len) AS ok
FROM s_$m s, o, l WHERE s.id <= 200 OR s.id > 500)
SELECT format('enc=%s method=%s stored=%s values=%s checks=%s mismatches=%s
multibyte_chars=%s',
'$enc', '$m', (SELECT string_agg(DISTINCT
pg_column_compression(v) || CASE WHEN pg_column_toast_chunk_id(v) IS NULL THEN
'' ELSE '/ext' END, ',') FROM s_$m WHERE id <= 200 OR id > 500),
(SELECT count(*) FROM s_$m WHERE id <= 200 OR id > 500),
count(*), count(*) FILTER (WHERE NOT ok),
(SELECT bool_or(octet_length(v) > length(v)) FROM s_$m))
FROM r;
SQL
done
stop $B
done
===== file: tests/t_size.sh =====
#!/bin/bash
# Compression ratio with and without dictionaries on our three corpora, and
# the effect of dict_size (the default is 65536; lz4 can only reference the
# last 64 kB of a dictionary). Sizes, not time. 3000-byte values (above the
# TOAST threshold, so they are compressed and stay inline); per table 1000
# training rows, then 5000 evaluation rows; ratio = raw bytes / stored bytes
# (sum of octet_length / sum of pg_column_size) over the evaluation rows only.
# t_size.sh DOCS_TXT (the concatenated SGML docs, as bench.sh builds it)
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
DOCS=$1; B=${B:-v11}
newcluster $B SQL_ASCII; start $B
q postgres <<SQL
$SQL_FUNCS
CREATE TABLE docs_src (t text); ALTER TABLE docs_src ALTER COLUMN t SET STORAGE
EXTERNAL;
INSERT INTO docs_src SELECT pg_read_file('$DOCS');
CREATE TABLE src (corpus text, id int, v text); ALTER TABLE src ALTER COLUMN v
SET STORAGE EXTERNAL;
INSERT INTO src SELECT 'docs', g, substr((SELECT t FROM docs_src), 1 + (g *
7919) % (length((SELECT t FROM docs_src)) - 3000), 3000) FROM
generate_series(1, 6000) g;
INSERT INTO src SELECT 'json', g, jdoc(g, 3000) FROM generate_series(1, 6000) g;
SELECT setseed(0.42);
INSERT INTO src SELECT 'random', g, left(string_agg(md5(random()::text) ||
encode(sha256(random()::text::bytea), 'base64'), ''), 3000)
FROM generate_series(1, 6000) g, generate_series(1, 3000 / 76 + 1) i GROUP BY
g;
SQL
for corpus in docs json random; do
for m in pglz lz4 zstd lz4_dict:32768 lz4_dict:65536 lz4_dict:131072
zstd_dict:32768 zstd_dict:65536 zstd_dict:131072; do
meth=${m%%:*}; size=${m#*:}; [ "$size" = "$m" ] && size=
t=t_${corpus}_${meth}${size:+_$size}
q postgres -c "CREATE TABLE $t (id int, v text COMPRESSION $meth)" \
-c "INSERT INTO $t SELECT id, v FROM src WHERE corpus =
'$corpus' AND id <= 1000"
trained=-
if [ -n "$size" ]; then
trained=$(qe postgres -c "SELECT pg_train_toast_dictionary('$t', 'v',
$size) IS NOT NULL" | tr '\n' ' ' | cut -c1-90)
fi
q postgres -c "INSERT INTO $t SELECT id, v FROM src WHERE corpus =
'$corpus' AND id > 1000"
q postgres -c "SELECT format('corpus=%s method=%s dict_size=%s trained=%s
ratio=%s stored_as=%s', '$corpus', '$meth', '${size:--}', '$trained',
round(sum(octet_length(v))::numeric /
sum(pg_column_size(v)), 2),
string_agg(DISTINCT coalesce(pg_column_compression(v),
'none'), ','))
FROM $t WHERE id > 1000"
q postgres -c "DROP TABLE $t"
done
done
q postgres -c "SELECT 'dictionaries left ' || count(*) FROM pg_toast_dictionary"
===== file: tests/t_small.sh =====
#!/bin/bash
# Is a ~1 kB value compressed at all in a table? One text column per table,
# with and without toast_tuple_target = 128, for pglz, lz4, zstd, lz4_dict and
# zstd_dict (dictionary trained on 3 kB values of the same corpus first).
# Prints how the 1 kB and the 3 kB values are stored.
# t_small.sh [BUILD] (default v10)
set -eu
T=$(cd "$(dirname "$0")" && pwd); source $T/lib.sh; trap cleanup EXIT
B=${1:-v10}
newcluster $B SQL_ASCII; start $B
q postgres <<SQL
$SQL_FUNCS
SQL
for m in pglz lz4 zstd lz4_dict zstd_dict; do
for tt in default 128; do
t=s_${m}_$tt; w=; [ $tt = 128 ] && w="WITH (toast_tuple_target = 128)"
q postgres -c "CREATE TABLE $t (id int, v text COMPRESSION $m) $w" \
-c "INSERT INTO $t SELECT g, jdoc(g, 3000) FROM
generate_series(1, 500) g"
case $m in *_dict) q postgres -c "SELECT pg_train_toast_dictionary('$t',
'v') IS NOT NULL" >/dev/null ;; esac
q postgres -c "INSERT INTO $t SELECT 1000 + g, jdoc(1000 + g, 1000) FROM
generate_series(1, 200) g" \
-c "INSERT INTO $t SELECT 2000 + g, jdoc(2000 + g, 3000) FROM
generate_series(1, 200) g"
q postgres -c "SELECT format('method=%s toast_tuple_target=%s 1kB:%s
3kB:%s', '$m', '$tt',
(SELECT string_agg(DISTINCT coalesce(pg_column_compression(v),
'uncompressed'), ',') FROM $t WHERE id BETWEEN 1001 AND 1200),
(SELECT string_agg(DISTINCT coalesce(pg_column_compression(v),
'uncompressed'), ',') FROM $t WHERE id > 2000))"
done
done
