How to find all tables referring to another table in Postgres

Tagged column, postgresql  Languages sql

This example lists all tables that contain a column named ‘organization_id’:

 SELECT c.relname AS table,
       a.attname AS column,
       n.nspname AS schema
FROM   pg_class AS c
       INNER JOIN pg_attribute AS a
               ON a.attrelid = c.oid
       JOIN pg_namespace n
         ON n.oid = c.relnamespace
WHERE  a.attname = 'organization_id'
       AND c.relkind = 'r'
       AND c.relname NOT LIKE 'pg_%'
ORDER  BY c.relname,
          a.attname  

The Scientific Method

Tagged growth, hypothesis, marketing, method, research, scientific, testing  Languages 

“The Scientific Method” from https://www.reforge.com/blog/run-growth-team-like-a-scientist

Inputs

  1. Question: What do I want to understand?
  2. Hypothesis: What is my proposed explanation to the question?
  3. Experiment: Design an experiment to test the hypothesis.

Outputs

  1. Observation: Observe the experiment and record results.
  2. Analysis: Interpret the results.

Notes

the qualitative inputs - question, hypothesis, and experiment design - are the most important steps of the process. If you get these steps right, you increase your chances of running successful experiments that produce valid results and actionable insights.

So, what separates the good growth teams (and research labs) from the great ones?

It’s the quality of the questions they ask, the hypotheses they come up with, and how they construct their experiments.

A one-day or one-week in vitro test using a landing page builder to mockup a subscription pricing page, driving traffic to it via ads, and measuring conversion rates against current pricing may have quickly revealed that a subscription pricing model wasn’t appealing to their customer base.

the point is to use an in vitro test to get validation that an in vivo test is worth the additional resources. Tools like Unbounce, Optimizely, and paid advertising make these kinds of in vitro tests easy and fast.

One of my favorite mentors, Anantha Katragadda, told me when I was starting out in growth that if I couldn’t think of a way to fake a product experience in order to get validating data, I simply wasn’t trying hard enough.

To publish a paper, every result must disprove the null hypothesis. This means that you prove, with statistical significance, that the result you are seeing is not due to random chance or biased observation

Temporal tables in PostgreSQL

Tagged postgres, slowly changing, temporal  Languages sql

To implement temporal tables in PostgreSQL, see Magnus Hagander’s “A TARDIS for your ORM” presentation: https://www.hagander.net/talks/tardis_orm.pdf

Temporal tables allow you to travel back in time and see your data as it was right after the big bang, or right before your data arrived at the event horizon.

The following code is from https://gist.github.com/iperdomo/74890685f3c76b0fc139 and implements the gist of time travel:

-- psql -U postgres -h localhost -f /path/to/tardis.sql

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

CREATE DATABASE tardis WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

\connect tardis

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;


CREATE SCHEMA history;

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;

SET search_path = history, pg_catalog;

CREATE FUNCTION log_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
    DECLARE
      c refcursor;
      tt tstzrange;
    BEGIN
        IF TG_OP = 'INSERT' THEN
            EXECUTE 'INSERT INTO history.' || TG_TABLE_NAME ||
              ' SELECT $1.*, tstzrange(now(), $$infinity$$, $$[)$$)' USING NEW;
            RETURN NEW;
        ELSIF TG_OP = 'UPDATE' THEN
            OPEN c FOR EXECUTE 'SELECT _validrange FROM history.' || TG_TABLE_NAME ||
              ' WHERE id = $1 ORDER BY _validrange DESC LIMIT 1 FOR UPDATE'
              USING NEW.id;
            FETCH FROM c INTO tt;

            IF isempty(tstzrange(lower(tt), now(), $$[)$$)) THEN

                -- What edge case are we missing by commenting the following lines of code?
        -- How can we get the lastxid value?

        --IF NOT lastxid = txid_current() THEN
        --    RAISE EXCEPTION 'UPDATE would have empty validity: %d!', OLD;
        --END IF;

        EXECUTE 'DELETE FROM history.' || TG_TABLE_NAME ||
          ' WHERE CURRENT OF ' || quote_ident(c::text);
            ELSE
                EXECUTE 'UPDATE history.' || TG_TABLE_NAME || ' SET _validrange = tstzrange($1, now(), $$[)$$)' ||
                  ' WHERE CURRENT OF ' || quote_ident(c::text) USING lower(tt);
            END IF;

            EXECUTE 'INSERT INTO history.' || TG_TABLE_NAME ||
              ' SELECT $1.*, tstzrange(now(), $$infinity$$, $$[)$$)' USING NEW;

            RETURN NEW;
        ELSIF TG_OP = 'DELETE' THEN

          OPEN c FOR EXECUTE 'SELECT _validrange FROM history.' || TG_TABLE_NAME ||
            ' WHERE id = $1 ORDER BY _validrange DESC LIMIT 1 FOR UPDATE' USING OLD.id;

          FETCH FROM c into tt;

          IF isempty(tstzrange(lower(tt), now(), $$[)$$)) THEN
            EXECUTE 'DELETE FROM history.' || TG_TABLE_NAME ||
              ' WHERE CURRENT OF ' || quote_ident(c::text);
            RETURN OLD;
          END IF;

          EXECUTE 'UPDATE history.' || TG_TABLE_NAME ||
            ' SET _validrange = tstzrange($1, now(), $$[)$$) WHERE CURRENT OF ' ||
            quote_ident(c::text) USING lower(tt);

          RETURN OLD;
        END IF;
        RETURN NULL;
    END;
$_$;


CREATE TABLE table1 (
    id text NOT NULL,
    name text NOT NULL,
    _validrange tstzrange
);

ALTER TABLE ONLY table1
    ADD CONSTRAINT table1_exclusion EXCLUDE
    USING gist (id WITH =, _validrange WITH &&);


SET search_path = public, pg_catalog;

-- We're using text instead of uuid because btree_gist don't support
-- uuid data type yet and we want to use it for the EXCLUDE constraint
-- See: https://commitfest.postgresql.org/7/332/

CREATE TABLE table1 (
    id text PRIMARY KEY DEFAULT (uuid_generate_v4())::text NOT NULL,
    name text NOT NULL
);

CREATE TRIGGER table1_history BEFORE
  INSERT OR DELETE OR UPDATE ON table1
  FOR EACH ROW EXECUTE PROCEDURE history.log_change();


-- Data

BEGIN;
INSERT INTO table1 (name) VALUES ('name 1');
INSERT INTO table1 (name) VALUES ('name 2');
COMMIT;

BEGIN;
UPDATE table1 SET name = 'name 1.1' WHERE name = 'name 1';
COMMIT;

BEGIN;
-- Since this is a transaction, the first UPDATE is not recorded
-- in the history.table1 (as expected)
UPDATE table1 SET name = 'name 1.2' WHERE name = 'name 1.1';
UPDATE table1 SET name = 'name 1.3' WHERE name = 'name 1.2';
COMMIT;

SELECT * FROM public.table1 WHERE name = 'name 1.3';
SELECT * FROM history.table1 ORDER BY _validrange DESC;

My Mac won't go to sleep

Tagged mac, pmset, sleep  Languages bash

If your Mac won’t go to sleep, try this:

pmset -g assertions

Check the output for details on which processes and voodo magic is keeping your iMac up all night:

Listed by owning process:
   pid 4627(cloudd)...
Idle sleep preventers: IODisplayWrangler

Things you can try:

  • Resetting the printing system (CTRL+click in the left pane of the Printers and scanners settings)
  • Unplugging external devices (USB, bluetooth, etc)
  • Voodoo

References:

https://discussions.apple.com/thread/7256162

https://www.reddit.com/r/applehelp/comments/43ks7u/macbook_wont_go_to_sleep_hidd_preventing_sleep/

https://apple.stackexchange.com/questions/307151/significance-of-iodisplaywrangler-in-power-management-assertions

How to set up a Digital Ocean Kubernetes cluster

Tagged digitalocean, kubernetes, setup  Languages bash, yaml

How to set up a Digital Ocean Kubernetes cluster (Q1 2019).

Introduction

Overview of how a request gets from a browser to a Docker container managed by Kubernetes:

Internet[Browser => DigitalOcean LoadBalancer] => Kubernetes[Ingress => Service => Deployment => Pod] => Docker[Container]

Prerequisites

  • Install kubectl
$ brew install kubectl
  1. Create cluster in DigitalOcean dashboard

Download the cluster configuration (YAML file) and put it in the ~/.kube folder.

  1. Set KUBECONFIG environment variable
$ export KUBECONFIG=/Users/christian/.kube/staging-kubeconfig.yaml
$ kubectl config current-context
$ kubectl get nodes
NAME               STATUS   ROLES    AGE   VERSION
xxx-staging-qv72   Ready    <none>   70m   v1.14.1
xxx-staging-qv7p   Ready    <none>   71m   v1.14.1
xxx-staging-qv7s   Ready    <none>   71m   v1.14.1
  1. Install ingress controller
$ kubectl apply -f https://raw.githubusercontent.com/kubernetes/ingress-nginx/master/deploy/mandatory.yaml
$ kubectl apply -f https://raw.githubusercontent.com/kubernetes/ingress-nginx/master/deploy/provider/cloud-generic.yaml

Get the EXTERNAL-IP of the load balancer:

$ kubectl get services --namespace=ingress-nginx
  1. Create ingress resource

ingress.yaml:

apiVersion: extensions/v1beta1
kind: Ingress
metadata:
  name: app-ingress
  annotations:
    ingress.kubernetes.io/rewrite-target: /
spec:
  rules:
  - http:
      paths:
        - path: /
          backend:
            serviceName: app-service
            servicePort: 80
$ kubectl apply -f ingress.yml
  1. Create deployment

deployment.yaml:

apiVersion: v1
kind: Service
metadata:
  name: app-service
spec:
  ports:
  - port: 80
    targetPort: 5678
  selector:
    app: app
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: app
spec:
  selector:
    matchLabels:
      app: app
  replicas: 1
  template:
    metadata:
      labels:
        app: app
    spec:
      containers:
      - name: app
        image: hashicorp/http-echo
        args:
        - "-text=Hello. This is your Digital Ocean k8s cluster."
        ports:
        - containerPort: 5678
$ kubectl apply -f deployment.yml
  1. Curl your app
curl https://<EXTERNAL-IP> --insecure

References

How to debug CrashLoopBackOff

Tagged crashloopbackoff, kubectl, kubernetes  Languages bash, yaml

One day, you see CrashLoopBackOff in the kubectl output:

$ kubectl get pod
NAME                               READY   STATUS             RESTARTS   AGE
app-548c9ddc46-z2fng               0/1     CrashLoopBackOff   79         6h26m

You already know that executing bash in the container is not possible because the container has crashed:

$ kubectl exec -ti app-548c9ddc46-z2fng bash
error: unable to upgrade connection: container not found ("app")

Option 1: Analyze the container logs

You can view the container’s logs with kubectl logs:

$ kubectl logs -f app-548c9ddc46-z2fng

Option 2: Modify Dockerfile’s CMD

Modifying the Dockerfile’s ‘CMD’ is not needed, as it can be done indirectly through the Pod’s YAML configuration file:

apiVersion: v1
kind: Pod
metadata:
  name: app
spec:
  containers:
    - name: app
      image: company/app
      # DEBUG env variables
      command: [ "/bin/sh", "-c", "env" ]

The modified command will print the environment variables to the logs:

command: [ "/bin/sh", "-c", "env" ]

To view the output run:

$ kubectl logs -f app-548c9ddc46-z2fng

Option 3: Start a shell and sleep (CMD)

Sleeping usually helps, and this can be done by modifying the container’s command:

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: xxx-service
spec:
  replicas: 1
  template:
    ...
    spec:
      containers:
      - image: yyy/xxx:1.0.0
        name: xxx-service
        ...
        command:
          - "sh"
          - "-c"
          - "sleep 10000"

The container will start and run “sleep 10000” in a shell, giving you exactly 10000 seconds to debug the issue by connecting to the “sleeping” container:

$ kubectl exec -ti app-548c9ddc46-z2fng bash

How to configure Kubernetes to pull images from a private Docker registry

Tagged docker, kubernetes, private, registry  Languages bash, yml

How to configure Kubernetes to pull images from a private Docker registry:

  • First configure Docker by following the steps outlined here:

https://snippets.aktagon.com/snippets/869-configure-docker-to-use-a-private-container-registry-using-a-self-signed-certificate

  • Verify that the Docker configuration contains the authentication information
sudo cat ~/.docker/config.json
{
    "auths": {
        "<registry-server>": {
            "auth": "<hash>"
        }
    },
    "HttpHeaders": {
        "User-Agent": "Docker-Client/18.09.4 (linux)"
    }
}
  • Base64 encode the config.json file
cat ~/.docker/config.json | base64 -w0 > config.base64.json
  • Create secret.yml and add the contents of config.base64.json to dockerconfigjson
apiVersion: v1
kind: Secret
metadata:
 name: registrypullsecret
data:
 .dockerconfigjson: <config.base64.json>
type: kubernetes.io/dockerconfigjson
  • Import the secret into Kubernetes
kubectl create -f secret.yml && kubectl get secrets
  • Test that the secret was imported into Kubernetes
kubectl get secrets

Configure Docker to use a private container registry using a self-signed certificate

Tagged docker, registry, self-signed  Languages bash

How to configure Docker to use a private container registry using a self-signed certificate.

Tested on Docker version 18.09.4, build d14af54.

  • Copy self-signed certificate from the registry server to the docker server

On your laptop:

$ scp christian@registry-server://etc/ssl/certs/selfsigned.crt christian@docker-server://etc/ssl/certs/private-docker-registry.crt
  • Restart docker daemon

On the docker server:

$ sudo service docker restart
  • Login to the registry from the docker server

On the docker server:

sudo docker login -u christian registry-server
> WARNING! Your password will be stored unencrypted in /home/christian/.docker/config.json.