joevandyk

$ DB[:t].insert

(0.001143s) INSERT INTO "t" DEFAULT VALUES RETURNING "id"
=> 2

joevandyk

set -e

ulimit -s 20000
cd ~/tanga
git fetch origin
git reset --hard origin/master
bundle
rake bundle_dev
rake reload_db
rake symlink_stuff

joevandyk

customer_info
  email_address
  name (this would be the name on the last billing address used?)
  necessary to have the last used billing address here?

products_ordered
  email_address
  product_name
  quantity
  date

joevandyk

/* This demonstrates a trigger for denormalizing a join table into an array.
   Hasn't really been tested much, but seems to work.

   Will put it into production tomorrow and see!

   Maybe PostgreSQL 9.4 will come with arrays of foreign keys. That will remove a lot of the
   need for simple join tables.

   http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys didn't
   make it into 9.3.

joevandyk

with
a as (select 'a'::text),
b as (select 'b'::text)
select array_agg(a) || array_agg(b) from a, b;

gives me
 {(a),(b)}

I want {a, b}

joevandyk

create table t1 (id integer primary key);
create table t2 (t1_id integer primary key references t1 on delete cascade);

insert into t1 values (1);
insert into t2 values (1);


create function a() returns trigger as $$ begin
  raise notice '%', (select t2 from t2);
  return null;

joevandyk

Notes for building / packing postgresql.

Checkout postgresql source from git.

Install fpm gem.

Run: ./configure --prefix=/opt/postgresql/<version> --with-ossp-uuid --with-libxml --with-libxslt --with-openssl

If libraries are needed for building, install them
(TODO: come up with this list for ubuntu)

joevandyk

begin;                                                                                              
                                                                                                    
create table user_ratings (                                                                         
  id serial primary key,                                                                            
  user_id integer not null,                                                                         
  rating integer not null check (rating >= 0 and rating <= 5),                                      
  ratable_id integer not null                                                                       
);                                                                                                  
                                                                                                    
create function random_int(max integer) returns integer as $$                                       

joevandyk

Process jobs in different programming languages (clojure and jruby would be a good start)

Process jobs in JVM with threaded workers

Failed jobs logging/remembering/retrying

Multiple job tables with foreign keys, constraints on columns, etc.
  Instead of a json blob of {"order_id": "1"}, have an order_id column with a foreign key to orders(id).
  This makes it impossible to insert bad jobs 
  (I sometimes screw up the options for a job)

joevandyk

1.9.1 :007 > puts DB[:test__with_filters].where(:used => true, :recently_expired => true).analyze

Seq Scan on promotions p  (cost=0.00..95696.18 rows=23046 width=87) (actual time=0.085..506.754 rows=2994 loops=1)
  Filter: (((end_at > (now() - '30 days'::interval)) IS TRUE) AND ((SubPlan 2) IS TRUE))
  Rows Removed by Filter: 599831
  SubPlan 1
    ->  Index Only Scan using index_promotion_usages_on_promotion_id on promotion_usages pu  (cost=0.00..20.54 rows=178 width=0) (actual time=0.002..0.002 rows=1 loops=2994)
          Index Cond: (promotion_id = p.id)
          Heap Fetches: 2994
  SubPlan 2