Skip to content

Instantly share code, notes, and snippets.

View lfittl's full-sized avatar

Lukas Fittl lfittl

293 followers · 72 following
View GitHub Profile
@lfittl
lfittl / postgres12.md
Last active December 11, 2018 00:39
Postgres 12 patches worth writing

Postgres Patches for 12

  • queryid in log_min_duration_statement
  • queryid generation in core instead of contrib/pg_stat_statements
  • pg_stat_plans in contrib (think of pg_stat_statements + auto_explain, possibly just store the EXPLAIN output as the text)
  • pg_stat_statements in core
  • pg_stat_statements should have idx_scan and seq_scan counters
  • changefeeds (aka logical decoding for realtime updates, instead of LISTEN/NOTIFY)
  • JSON logical decoder in core
  • COPY from URL / Make COPY extendable (so there can be an S3 handler)
@lfittl
lfittl / .flowconfig
Created December 2, 2017 21:51
Place override in flow-typed/npm/react-apollo_vx.x.x.js and ignore shipped types using .flowconfig
[ignore]
.*node_modules/react-apollo/.*
@lfittl
lfittl / drop_all_tables.sql
Created September 15, 2017 21:08
Drop All Tables
DO LANGUAGE plpgsql $$
DECLARE
t record;
tables text[];
BEGIN
FOR t IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') LOOP
tables := array_append(tables, quote_ident(t.schemaname) || '.' || quote_ident(t.tablename));
END LOOP;
IF array_length(tables, 1) > 0 THEN
@lfittl
lfittl / copy_from_client.rb
Last active March 24, 2017 22:09
Helper method to use Postgres' COPY with ActiveRecord
# Put this in config/initializers/copy_from_client.rb
class CopyFromClientHelper
attr_reader :count
def initialize(conn, column_types)
@count = 0
@conn = conn
@column_types = column_types
end
@lfittl
lfittl / pg_query_example2.rb
Last active March 23, 2017 04:55
require 'pg_query'
pp PgQuery.parse("SELECT * FROM x WHERE y = ?")
#<PgQuery:0x007f8cdaaaae10
@parsetree=
[{"SELECT"=>
{"distinctClause"=>nil,
"intoClause"=>nil,
"targetList"=>
[{"RESTARGET"=>
@lfittl
lfittl / output.txt
Last active September 20, 2016 23:24
Using pg_query fingerprinting to prevent SQL injections
#my_orm_method("1")
1
#my_orm_method("'abc'")
abc
#my_orm_method("1 UNION SELECT * FROM passwords")
test.rb:8:in `exec_with_fingerprint': Invalid fingerprint, rejecting (RuntimeError)
from test.rb:16:in `my_orm_method'
from test.rb:23:in `<main>'
@lfittl
lfittl / analyze_all.sh
Last active September 17, 2016 05:00
Running ANALYZE (or other table-specific commands) across distributed tables in a Citus cluster
# Analyzes all distributed tables
export PGHOST=...
export PGUSER=citus
export PGPASSWORD=...
#export PGSSLMODE=require
export PGDATABASE=citus
export PARALLEL_FACTOR=4
psql -tA -F" " -c "SELECT logicalrelid::regclass||'_'||s.shardid, \
@lfittl
lfittl / fix_composite_key_insert.rb
Last active September 8, 2016 00:34
# Place this in app/models/concerns/fix_composite_key_insert.rb
# Fix for https://github.com/composite-primary-keys/composite_primary_keys/issues/365
#
# Act as if the record has a single primary key column in some cases, in
# particular when we get called from _create_record with the RETURNING result:
# https://github.com/rails/rails/blob/master/activerecord/lib/active_record/persistence.rb#L560
module FixCompositeKeyInsert
extend ActiveSupport::Concern
@lfittl
lfittl / instructions.md
Last active August 29, 2016 01:00
pganalyze-collector installation instructions for other distributions

First, we'll need to place a few files in the right location:

curl -o /usr/bin/pganalyze-collector https://github.com/pganalyze/collector/releases/download/v0.9.4/pganalyze-collector-linux-amd64
curl -o /etc/systemd/system/pganalyze-collector.service https://github.com/pganalyze/collector/blob/master/contrib/systemd/pganalyze-collector.service
curl -o /etc/pganalyze-collector.conf https://github.com/pganalyze/collector/blob/master/contrib/pganalyze-collector.conf

In addition, run the following commands:

@lfittl
lfittl / example_queries.sql
Last active June 21, 2016 21:31
Example structure and queries from pganalyze timeseries data for tables/indices
SELECT
size_bytes AS table_size,
(SELECT SUM(size_bytes) FROM schema_index_stats sis JOIN schema_indices si ON (si.id = sis.schema_index_id) WHERE si.table_id = st.schema_table_id AND sis.snapshot_id = st.snapshot_id) AS index_size,
COALESCE((SELECT SUM(size_bytes) FROM schema_indices si JOIN schema_index_stats sis ON (si.id = sis.schema_index_id) WHERE si.table_id = st.schema_table_id AND sis.snapshot_id = st.snapshot_id), ?) + size_bytes AS total_size,
date_part(?, s.collected_at) AS collected_at
FROM
snapshots s JOIN schema_table_stats st
ON
(snapshot_id = s.id)
WHERE