Postgrest docker-compose.yml

Tagged postgrest, docker  Languages yaml
version: "3"
services:
  postgrest:
    container_name: postgrest
    image: postgrest/postgrest:v7.0.1
    # entrypoint: ''
    # command: ls -l
    ports:
      # Forward host port 8080 to postgrest
      - "8080:3000"
    environment:
      PGRST_DB_URI: postgres://user:[email protected]:5432/db_name
      # The schema that Postgrest exposes
      PGRST_DB_SCHEMA: public
      # The role that is used to perform unauthenticated queries
      PGRST_DB_ANON_ROLE: app_user
      PGRST_SERVER_PROXY_URI: "http://127.0.0.1:3001"

  swagger:
    image: swaggerapi/swagger-ui
    ports:
      # Forward host port 8081 to swagger
      - "8081:8080"
    expose:
      - "8080"
    environment:
      # Tell Swagger to document the postgrest API
      API_URL: http://localhost:8080/

Exactly-once delivery with RabbitMQ

Tagged exactly-once, messaging, amqp, rabbitmq  Languages 

Use late acknowledgment and idempotency to achieve fake exactly-once delivery with RabbitMQ.

Late acknowledgment = acknowledge the message after the database transaction has been committed.

Idempotency = don’t process the same message twice, or ensure the effect is the same when processing once or multiple times

Could not find 'bundler' (2.2.16) required by your Gemfile.lock

Tagged docker, gemfile, bundler  Languages bash

You need to install the version listed in the Gemfile.lock file to fix this error:

gem install --force "bundler:$(grep -A 1 "BUNDLED WITH" Gemfile.lock | tail -n 1)"

In a Dockerfile you could run it like this:

RUN gem install --force "bundler:$(grep -A 1 "BUNDLED WITH" Gemfile.lock | tail -n 1)" rake && \
  bundle config set without development test && \
  bundle config set --local deployment 'true' && \
  bundle install --jobs 5 --retry 5 && \
  bundle clean --force

Troubleshooting Python's Celery

Tagged celery, python  Languages python

Task celery.chord_unlock[38d5105a-12f2-4119-80e5-184167998f4b] retry: Retry in 1.0s

The notes https://docs.celeryproject.org/en/latest/userguide/canvas.html#chords:

If you’re using chords with the Redis result backend and also overriding the Task.after_return() method, you need to make sure to call the super method or else the chord callback won’t be applied.

TypeError: task() argument after ** must be a mapping, not list

missing 3 required positional arguments

If you get this error:

TypeError: after_return() missing 3 required positional arguments: ‘args’, ‘kwargs’, and ‘einfo’

You might have specified the arguments incorrectly, for example:

job = job_task.subtask(1, 2, 3)

Use a list to fix the error:

job = job_task.subtask((1, 2, 3))

Fastest way of importing data into PostgreSQL

Tagged import, stdin, copy, psql  Languages bash

The fastest way of importing data into PostgreSQL is to avoid any additional processing, i.e., use PostgreSQL tools instead of writing scripts in Python or other languages.

This will import the file directly from a file into PostgreSQL:

unzip -p data.csv.gz | PGOPTIONS=-—client-min-messages=warning psql —-no-psqlrc —-set ON_ERROR_STOP=on <db name> —-command="COPY table from STDIN"

You can also add preprocessing easily, such as removal of data with AWK, by piping commands together into a workflow.

How to unnest an array of arrays in PostgreSQL

Tagged jsonb_agg, unnest, jsonb_array_elements_text, array_agg  Languages sql

If you try to use unnest with array_agg you will get the following error:

SELECT array_agg(unnest(ids)) FROM (
  SELECT
    month, array_agg(id) as ids
  FROM x
  GROUP BY month
);
ERROR:  0A000: aggregate function calls cannot contain set-returning function calls
LINE 14:   array_agg(unnest(ids)) AS ids,
                     ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.

You can use jsonb_agg and jsonb_array_elements_text to flatten or unnest an array of arrays in PostgreSQL:

SELECT
  jsonb_array_elements_text(jsonb_agg(array_of_arrays))
FROM
  x;

But multiple rows will be returned.

For other solutions, see:

https://stackoverflow.com/a/8142998

https://wiki.postgresql.org/wiki/Unnest_multidimensional_array

Answering what has happened before and after with WINDOW functions and PostgreSQL

Tagged function, before, after, window, postgresql  Languages sql

This query answers what has happened and if something has happened before and after the current row in the group defined by the window function:

WITH log AS (
  SELECT
    array_agg(event_type) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS what_happened_before,
    array_agg(event_type) OVER (w ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS what_happened_after,
    bool_or(event_type='error') OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS has_happened_before,
    bool_or(event_type='error') OVER (w ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS has_happened_after,
    *
  FROM events
  WINDOW w AS (PARTITION BY month ORDER BY month, id DESC)
  ORDER BY month, id DESC
)
SELECT * FROM log;

This query answers the following questions:

  • What has happened in this group before/after the current row?
  • Has a specific event happened in this group before/after the current row?

See documentation for details: https://www.postgresql.org/docs/12/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

Mixing arguments and keywords in Ruby

Tagged arguments, keywords, options, ruby, splat  Languages ruby

Mixing arguments and keywords in Ruby:

def hello(*args, **keywords)
  { args: args, keywords: keywords }
end

Splat to the rescue:

* turns all arguments into an array.

** turns all keyword arguments into a hash.

This allows you to do the following:

hello(:one, :two, { three: :four })
# or, simply
hello(:one, :two, three: :four)

=> {:args=>[:one, :two], :keyword_args=>{:three=>:four}}

Readability is improved by using proper names:

def hello(name, **options)
  { name: name, options: options }
end

How to get cron to log to STDOUT under Docker and Kubernetes

Tagged dockerfile, cron, stdout, stderr, pid  Languages bash

Dockerfile

FROM python:3.9-slim-buster
...
COMMAND ["cron", "-f"]

In cron scripts, redirect the scripts’ output to the file descriptor of PID 1, which is cron (Dockerfile’s COMMAND):

# Redirects both stderr and stdout to stdout of PID 1:
run.sh &>> /proc/1/fd/1
# Redirects stderr and stdout to stdout and stderr of PID 1:
run.sh 1>> /proc/1/fd/1 2>> /proc/1/fd/2

Each PID (process) has it’s own file descriptors:

/proc/{PID}/fd/0 # STDIN
/proc/{PID}/fd/1 # STDOUT
/proc/{PID}/fd/2 # STDERR