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

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

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

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) 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

WITH
manufacturers (name, headquarters) AS (VALUES
  ('BMW', 'Munich'), ('Toyota', 'Toyota'), ('Fiat', 'Turin')
),
models (manufacturer, name, year) AS (VALUES
  ('BMW', 'm4', 2000), ('BMW', 'm5', 2000),
  ('Toyota', 'corolla', 2000), ('Toyota', 'yaris', 2000),
  ('Fiat', 'panda', 2000), ('Fiat', 'uno', 2000)
),
products (manufacturer, model, color, quantity, pretax) AS (VALUES

pstef

psql -d 'linspector' <<EOF
DROP TABLE IF EXISTS symbols, "files";
CREATE TABLE symbols (
    filename text NOT NULL,
    flag "char" NOT NULL,
    symbol text NOT NULL,
    PRIMARY KEY (filename, flag, symbol)
);
COPY symbols (filename, flag, symbol) FROM stdin;

pstef

SELECT array_to_string(a[array_upper(a, 1)-1:array_upper(a, 1)], '.')
FROM string_to_array('gist.github.com', '.') a

pstef

#include <string.h>
#include <stdio.h>

enum car { honda };

enum number { ZERO, ONE };

enum number function_returning_number(char const *keyword) {
	if (!strcmp(keyword, "zero"))
		return ZERO;

pstef

valgrind --tool=callgrind  --toggle-collect=function ./program