https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful

Option 1 haproxy in front of squid or varnish

Option 2 write a proxy in Golang

See https://github.com/elazarl/goproxy/

localhost=# select 1 / 0;
ERROR:  22012: division by zero
LOCATION:  int4div, int.c:824

Hmmm:

localhost=# select 1 / NULL;
┌──────────┐
│ ?column? │
├──────────┤
│        ¤ │
└──────────┘
(1 row)

Time: 7.067 ms

Ergo:

localhost=# select 1 / NULLIF(0, 0);
┌──────────┐
│ ?column? │
├──────────┤
│        ¤ │
└──────────┘
(1 row)

Time: 0.303 ms

Dockerfile:

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 10 --retry 5 && \
  bundle clean --force

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/

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 the same message multiple times

Reconciliation step = if you need 100% reliability create a process (manual or automatic) that checks that all work is done, if not resend the message

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

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

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.

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