Skip to content

Instantly share code, notes, and snippets.

Dan Robinson drob

Block or report user

Report or block drob

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
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.