pstef

WITH RECURSIVE
categories_rel (id, parent_id) AS (VALUES
  (1, NULL), (2, NULL),
  (11, 1), (12, 1), (23, 2),
  (119, 11), (121, 12),
  (1193, 119), (1193, 1)
),
tree (id, parent_id, r, path) AS (
  SELECT id, parent_id, 0, ARRAY[id]::int[]
  FROM categories_rel

pstef

diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index bfa8499..fc9221b 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -287,6 +287,14 @@ void
 InitProcess(void)
 {
 	PGPROC	   *volatile *procgloballist;
+#define LIST_TYPE_ENTRY(e) [(e)] = (#e)
+	enum listType { autovac, bgworker, backend } which;

pstef

WITH RECURSIVE
categories_rel (id, parent_id, "order") AS (VALUES
  (1, NULL, 20), (2, NULL, 10),
  (99, 1, 6), (12, 1, 1), (23, 2, 1),
  (119, 99, 1), (121, 12, 1),
  (1193, 119, 1), (1193, 1, 5), (7, 1, 2), (6, 1, 3)
),
tree (id, parent_id, r, path) AS (
  SELECT id, parent_id, 0 AS r, ARRAY["order"], "order"
  FROM categories_rel

pstef

me@localhost postgres=# CREATE SCHEMA pokemon;
CREATE SCHEMA
Time: 9.719 ms
me@localhost postgres=# CREATE TABLE pokemon.types
  (type_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (MINVALUE 0), name TEXT UNIQUE);
CREATE TABLE
Time: 51.735 ms
me@localhost postgres=# CREATE TABLE pokemon.pokemon
  (pokemon_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name TEXT UNIQUE, seq INT);
CREATE TABLE

pstef

I’ve been working on a flexible audit table solution for Postgres, based on the official docs, some blog posts and advice from the Postgres IRC channel. It works like this:

First you create the audit table to store the changes. Yes, the table means there’s only one for one or more other tables to be tracked. The assumption is that this table is only for storage and whenever you need to manipulate data, you copy a subset of the table into a temporary table for further work.

    CREATE TABLE IF NOT EXISTS public.audit
    (
      change_date timestamp with time zone NOT NULL DEFAULT now(),

      -- session_user may be the (or an) application's DB role or perhaps a developer's role
      session_user_name text NOT NULL,

pstef

# pwd
/usr/local/etc/munin/plugins
# tail -n4 ../plugin-conf.d/plugins.conf
[pg_stat_statements]
env.STSTSCH statstatements
env.PGUSER munin
env.PGDATABASE postgres
# cat pg_stat_statements
#!/bin/sh
LIM=${STSTLIM:-10}

pstef

INSERT INTO x_y
SELECT l.x_id, unnest(array_positions) - 1
FROM (
  SELECT x_y.x_id, bit_or(1 << y.id) AS yids
  FROM x_y
  GROUP BY x_y.x_id
  HAVING count(*) <> (SELECT count(*) FROM y)
) AS l,
reverse(((SELECT bit_or(1 << y.id) FROM y) # l.yids)::bit(64)::text),
string_to_array(reverse, ''),

pstef

portmaster shells/bash sysutils/coreutils textproc/diffutils misc/findutils lang/gawk lang/gcc10 misc/getopt devel/git-lite devel/gmake textproc/gsed archivers/gtar devel/patch lang/perl5.30 lang/python37 net/rsync ftp/wget
or
pkg install bash coreutils diffutils findutils gawk gcc getopt git-lite gmake gsed gtar patch perl5 python rsync wget


git clone --shallow-since=2016-03-01 'https://github.com/pstef/openwrt' --branch=freebsd && cd openwrt

mkdir -p staging_dir/host/bin && cd staging_dir/host/bin
ln -s /usr/local/bin/getopt
ln -s /usr/local/bin/gmake make

pstef

pstef@thinkpad:~ $ for i in `apropos -w .` ; do printf '%s,"%s"\n' "$(basename $i)" "$(zcat $i | mandoc -T html | sed -En '/<h1.*id="(SUMMARY|DESCRIPTION)">/,/<\/section>/p' | sed '/<h1.*/d;/^$/d;s/"/\"/g' | tr -s '\n' ' ' | sed 's/<[^>]*>//g')" ; done > man.csv
pstef@thinkpad:~ $ sqlite3 man.db "CREATE VIRTUAL TABLE test USING fts5(title unindexed, text, tokenize = 'porter unicode61');"
pstef@thinkpad:~ $ sqlite3 man.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .import man.csv test
sqlite> SELECT rank, title, snippet(test, 1, '<', '>', '', 5) FROM test WHERE text MATCH 'set timer' ORDER BY rank;
-10.8040906839996,timer_getoverrun.2.gz,"last <set> by <timer>_settime"
-10.4134584558555,getitimer.2.gz,"call <sets> a <timer> to"

pstef

sqlite> WITH RECURSIVE
up AS (
  SELECT reference, 0 AS n
  FROM mails
  WHERE msgid = '[email protected]'
  UNION ALL
  SELECT m.reference, n + 1
  FROM mails m JOIN up ON m.msgid = up.reference AND m.reference IS NOT NULL
),
down AS (