ololobus

Final submission for a PostgreSQL GSOC'17 project 'Add errors handling and parallel execution to COPY'

Alexey Kondratov ([email protected]).

Errors handling in COPY FROM

Details

In my initial proposal I was planning to use subtransactions for errors handling, since it is the only one completely safe way to catch all possible errors during the COPY FROM execution. However, it would cause a serious problem – severe transactional IDs (XIDs) consumption hidden from the end-user. It may lead to a huge performance drop in the case, when errors are too frequent in the input data.

ololobus

From 18f328c8a20dfdbd805921c6dcb6d665067507a5 Mon Sep 17 00:00:00 2001
From: Alex K <[email protected]>
Date: Fri, 9 Jun 2017 23:41:51 +0300
Subject: [PATCH 1/8] Allow ignoring some errors during COPY FROM

---
 contrib/file_fdw/file_fdw.c |   4 +-
 src/backend/commands/copy.c | 352 +++++++++++++++++++++++++-------------------
 src/include/commands/copy.h |   2 +-
 3 files changed, 207 insertions(+), 151 deletions(-)

ololobus

From 22045780b95db74a99dc6e13e57413401da92c81 Mon Sep 17 00:00:00 2001
From: Alex K <[email protected]>
Date: Mon, 10 Jul 2017 17:48:26 +0300
Subject: [PATCH 01/13] Dummy COPY FROM BGWorker v0.1

---
 src/backend/commands/copy.c       | 42 +++++++++++++++++++++++++++++++++++++++
 src/backend/postmaster/bgworker.c |  4 ++++
 src/include/commands/copy.h       |  1 +
 3 files changed, 47 insertions(+)

ololobus

#!/bin/sh -ex
#export PATH=/usr/pgsql-11/bin:$PATH

pg_ctl stop -D /tmp/master || echo "ok"
pg_ctl stop -D /tmp/slave || echo "ok"
rm -rf /tmp/master
rm -rf /tmp/slave

# setup master
initdb -D /tmp/master

ololobus

Clone ICU repo:

git clone https://github.com/unicode-org/icu.git
cd icu

Run script:

python3 grep-icu.py

ololobus

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

#define MAXBUFSIZE 20

void put(char (*dest)[MAXBUFSIZE], char *src)
{
    strcpy((char *) dest, src);
}

ololobus

PostgreSQL Multi-Tenant Sharding Test

Setup

1. Create two Postgres clusters, one on 5432 port (node1) and one on 5433 (node2). Compile and install postgres_fdw extension on both.

2. Set enable_partitionwise_join and enable_partitionwise_aggregate to on.

3. Set postgres_fdw.use_remote_estimate to true.

ololobus

A Tour of Go excersises

ololobus

\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

ololobus

/*
 * Data structures defining the semantics of the standard lock methods.
 *
 * The conflict table defines the semantics of the various lock modes.
 */
static const LOCKMASK LockConflicts[] = {
	0,

	/* AccessShareLock */
	LOCKBIT_ON(AccessExclusiveLock),