onderkalaci’s gists

onderkalaci / grant_communiy

Created January 18, 2021 07:46


	SET citus.replication_model to statement ;
	set citus.shard_replication_factor TO 2;

	CREATE SCHEMA sc1;
	CREATE TABLE sc1.test (a int);
	SELECT create_distributed_table('sc1.test', 'a');

onderkalaci / overly_simplified_savepoint_bug.sql

Created January 12, 2021 08:33

Overly Simplified Savepoint Bug Repro


	CREATE OR REPLACE PROCEDURE public.top_function() LANGUAGE PLPGSQL AS $procedure$
	begin
	call second_function_that_fails();
	EXCEPTION
	when others then
	RAISE NOTICE 'Top function exception handling';
	call third_function_that_fails();
	end; $procedure$;

onderkalaci / failure.sql

Created December 10, 2020 15:18

	create or replace procedure top_function()
	language plpgsql
	as
	$$
	begin
	RAISE NOTICE 'Top function is called';

	call second_function();

	EXCEPTION

onderkalaci / local_joins.sql

Last active March 23, 2021 18:11



	- many filters
	- Partitioned table
	- Prepared statements
	- weird data types
	- https://gist.github.com/onderkalaci/bf242c70aa0045496fd43d4949987034#file-unconventional_types-sql-L2
	- Reference table
	- modify: update local FROM local Dist
	- weird target lists

onderkalaci / json.sql

Last active November 27, 2020 10:59

PG Data Types

	Create or replace function get_random_escape_char() returns char as
	$$
	declare
	i integer:=(random() * 100)::int % 8;
	begin

	if i = 0 then
	return '\t';
	elseif i = 1 then
	return '\n';

onderkalaci / unnecessary_subplans.sql

Last active November 19, 2020 19:42


	CREATE TABLE users_table (user_id int, time timestamp, value_1 int, value_2 int, value_3 float, value_4 bigint);
	SELECT create_distributed_table('users_table', 'user_id');

	INSERT INTO users_table
	SELECT i,
	now(),
	i,
	i,
	i,

onderkalaci / adf_parallel_load_test.sql

Last active August 10, 2021 08:59




	CREATE TABLE collections_list (
	key bigint,
	ts timestamptz,
	collection_id integer,
	value numeric
	) PARTITION BY LIST (collection_id );

onderkalaci / join_generation.sql

Last active October 21, 2020 08:49

useful commands

	SELECT format('begin;create table foo_%1s(id serial primary key, data1 int, data2 text, data3 json); SELECT create_distributed_table(''foo_%1s'',''id'');commit;', g.i, g.i) FROM generate_series(1, 10) g(i)
	\gexec




	select ' select count(*) from users_table ' \|\| string_Agg('INNER
	JOIN users_table u'\|\| x::text \|\| ' USING (user_id)',' ') from
	generate_Series(1,25)x;
	\gexec

onderkalaci / steps_to_assert_pg.sql

Created October 8, 2020 11:09

	pg_config
	BINDIR = /Users/onderkalaci/Documents/citus_code/pgenv/pgsql-13.0/bin
	DOCDIR = /Users/onderkalaci/Documents/citus_code/pgenv/pgsql-13.0/share/doc
	HTMLDIR = /Users/onderkalaci/Documents/citus_code/pgenv/pgsql-13.0/share/doc
	INCLUDEDIR = /Users/onderkalaci/Documents/citus_code/pgenv/pgsql-13.0/include
	PKGINCLUDEDIR = /Users/onderkalaci/Documents/citus_code/pgenv/pgsql-13.0/include
	INCLUDEDIR-SERVER = /Users/onderkalaci/Documents/citus_code/pgenv/pgsql-13.0/include/server
	LIBDIR = /Users/onderkalaci/Documents/citus_code/pgenv/pgsql-13.0/lib
	PKGLIBDIR = /Users/onderkalaci/Documents/citus_code/pgenv/pgsql-13.0/lib
	LOCALEDIR = /Users/onderkalaci/Documents/citus_code/pgenv/pgsql-13.0/share/locale

onderkalaci / attach_process_running_in_docker

Created October 1, 2020 18:00

attach process running in docker


	-- find the PID of the process that is blocked, you can use pg_stat_activity or anything else
	-- we'll use PID in the next steps
	-- assume that we find it as 6939

	-- the PID we have is running on docker
	-- now, find the corresponding PID of the process on the VM
	for i in $(ps -ef \| grep `docker inspect --format '{{.State.Pid}}' PostgreSQLCitus` \| awk '{print $2}') ; do grep NSpid: /proc/$i/status ; done \| grep 6939
	NSpid: 26857 6939

Önder Kalacı onderkalaci