Skip to content

Instantly share code, notes, and snippets.

Dan Robinson drob

View GitHub Profile
View install-new-relic.yml
---
- name: Register New Relic repository.
get_url: url=http://download.newrelic.com/debian/newrelic.list
dest=/etc/apt/sources.list.d/newrelic.list
- name: Download repo key.
apt_key: url=http://download.newrelic.com/548C16BF.gpg
- name: Install New Relic.
apt: pkg=newrelic-sysmond update_cache=yes
View blowup.sql
CREATE OR REPLACE FUNCTION blowup(num BIGINT) RETURNS HSTORE[] AS $$
DECLARE
result HSTORE[] := '{}';
i BIGINT;
BEGIN
FOR i IN 1..num LOOP
result := array_append(result, ('num=>' || i)::HSTORE);
END LOOP;
RETURN result;
View dedupe_events_2.sql
-- Filter an array of events such that there is only one event with each event_id.
-- When more than one event with the same event_id, is present, take the latest one.
CREATE OR REPLACE FUNCTION dedupe_events_2(events HSTORE[]) RETURNS HSTORE[] AS $$
SELECT array_agg(event)
FROM (
-- Filter for rank = 1, i.e. select the latest event for any collisions on event_id.
SELECT event
FROM (
-- Rank elements with the same event_id by position in the array, descending.
SELECT event, row_number AS index, rank()
View does_not_blowup.sql
CREATE OR REPLACE FUNCTION does_not_blowup(num BIGINT) RETURNS HSTORE[] AS $$
SELECT array_agg(('num=>' || i)::HSTORE)
FROM generate_series(1, num) AS i;
$$ LANGUAGE SQL IMMUTABLE;
View random_hstore_array.rb
#!/usr/bin/env ruby
num_events = ARGV[0].to_i
@rng = Random.new
def rand_id
@rng.rand 0...1000
end
def rand_data
View dedupe_events_1.sql
-- This is slow, and you don't want to use it!
--
-- Filter an array of events such that there is only one event with each event_id.
-- When more than one event with the same event_id is present, take the latest one.
CREATE OR REPLACE FUNCTION dedupe_events_1(events HSTORE[]) RETURNS HSTORE[] AS $$
SELECT array_agg(event)
FROM (
-- Filter for rank = 1, i.e. select the latest event for any collisions on event_id.
SELECT event
FROM (
View nums_not_sorted.sql
SELECT remainder, array_agg(i)
FROM (
SELECT i % 2 AS remainder, i
FROM generate_series(1, 10) AS i
ORDER BY i
) nums
GROUP BY remainder;
View sorted_nums.sql
SELECT array_agg(i)
FROM (
SELECT i
FROM generate_series(1, 10) AS i
ORDER BY i
) nums;
View autovacuum_overdue.sql
SELECT
nspname,
relname,
dead_tuples,
autovacuum_threshold,
dead_tuples > autovacuum_threshold AS overdue
FROM (
SELECT
nspname,
relname,
View lateral_join_funnel_1_1.sql
SELECT
user_id,
1 AS view_homepage,
min(time) AS view_homepage_time
FROM event
WHERE
data->>'type' = 'view_homepage'
GROUP BY user_id
You can’t perform that action at this time.