About Me!

This blog is about my musings and thoughts. I hope you find it useful, at most, and entertaining, at least.

Résumé [PDF]

Other Pages

Quotes

Links

Oak Island

Items for Sale

Presence Elsewhere

jim@jimkeener.com

del.icio.us

Twitter

Facebook

LinkedIn

GitHub

BitBucket

Understanding PostgreSQL's EXPLAIN output and Query Planner

Date: 2015-09-07

Tags: postgres development

We’ll begin our exploration of the EXPLAIN (more information from the docs) clause and query planner by downloading the MovieLens 20 million ratings file. The README.txt file contained in the zip has informational we’ll use to build our test. (Note: The zip is 132 MB. I wanted a large dataset for reasons we’ll get into.)

(Note: While the EXPLAIN output and syntax shouldn’t change drastically, I would note that all the output comes from PostgreSQL 9.3.9.)


jim@lilith /monongahela/movielens% wget -x http://files.grouplens.org/datasets/movielens/ml-20m.zip
jim@lilith /monongahela/movielens% wget -x http://files.grouplens.org/datasets/movielens/ml-20m.zip.md5
jim@lilith /monongahela/movielens/files.grouplens.org/datasets/movielens% [ "`md5sum ml-20m.zip | awk '{print $1}'`" = "`cat ml-20m.zip.md5 | awk '{print $4}'`" ] && echo "OK"
jim@lilith /monongahela/movielens/files.grouplens.org/datasets/movielens% unzip ml-20m.zip

Then we’ll create the database and the movies table. (We’ll get to the other tables later.) We’ll then import the csv file directly. Lastly, we’ll turn the list of genres each movie has, stored as a pipe-delimeted string, into an text array field.


jim@lilith /monongahela/movielens% createdb planner_example
jim@lilith /monongahela/movielens% psql planner_example
[local]:5432 jim@planner_example=# create table movies (
  movie_id integer, 
  title text, 
  genres_text text, 
  genres text[]);
[local]:5432 jim@planner_example=# copy movies (movie_id, title, genres_text) from '/monongahela/movielens/files.grouplens.org/datasets/movielens/ml-20m/movies.csv' with (format csv, header);
[local]:5432 jim@planner_example=# update movies set genres = regexp_split_to_array(genres_text, '\|');
[local]:5432 jim@planner_example=# alter table movies drop genres_text;

To start, we will do a simple selection by the movie_id. (Note: We havn’t yet created an index, let alone a primary key). Then we’ll want to look at the EXPLAIN output.


[local]:5432 jim@planner_example=# select * from movies where movie_id = 6645;
 movie_id |      title      |             genres              
----------+-----------------+---------------------------------
     6645 | THX 1138 (1971) | {Action,Adventure,Drama,Sci-Fi}
(1 row)
Time: 7.989 ms
[local]:5432 jim@planner_example=# explain select * from movies where movie_id = 6645;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on movies  (cost=0.00..1026.97 rows=1 width=77)
   Filter: (movie_id = 6645)
(2 rows)
Time: 0.493 ms

The first thing to notice about the EXPLAIN output is that it’s named “Query Plan” and the second is that it has two lines, the second being indented. The default output1 indents additional steps that need to perform the step above it. Here, we are told that this query will do a “Seq Scan”, a.k.a. Sequence Scan, meaning that it will scan every row in the table in sequence. The indented row tells us that the results of the Seq Scan will be filtered, and what the filter is.

The Seq Scan row has some additional information attached to it: cost, rows, and width. Let’s take these backwards, as cost is the most complex to describe. Width is the approximate average size in bytes of the returned rows.


[local]:5432 jim@planner_example=# explain select title from movies where movie_id = 6645;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on movies  (cost=0.00..1026.97 rows=1 width=28)
   Filter: (movie_id = 6645)
(2 rows)
Time: 0.587 ms
[local]:5432 jim@planner_example=# explain select genres from movies where movie_id = 6645;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on movies  (cost=0.00..1026.97 rows=1 width=45)
   Filter: (movie_id = 6645)
(2 rows)
Time: 0.521 ms
[local]:5432 jim@planner_example=# explain select movie_id, title from movies where movie_id = 6645;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on movies  (cost=0.00..1026.97 rows=1 width=32)
   Filter: (movie_id = 6645)
(2 rows)
Time: 0.568 ms

In the first query we see that the integer type is 4-bytes long, which isn’t entirely unexpected. The title is a text field, which can be arbitrarily long. We see that the table statistics kept by PostgreSQL have it listed as 28-bytes long on average.


[local]:5432 jim@planner_example=# select avg(length(title)) from movies ;
         avg         
---------------------
 27.8817361976684508
(1 row)
Time: 23.780 ms

Those statistics aren’t half bad! The final query, which selects only the movie_id and title has a width of 32, which not-so-suprisingly is 4+28. The width in our original query is 77, again, which is 4+28+45.

Rows is a guess at how many rows will be returned. PostgreSQL keeps statistics on the number of total rows and the cardnality of the values in the row, along with other information, to expect how many rows will be returned. This is important because part of cost, which we cover next, is how much disk IO will be performed. Returned the majority of the table has different IO needs than returning a handful of rows.

Cost is made of two values: start-up cost and approximate cost to complete the step. Cost is not a direct measure of time, but should be proportional. The values configuration constants used by the planner are as follows

Parameter Description Default Relative cost vs seq_page_cost
random_page_cost Nonsequential disk page fetch. 4.00 4x slower
seq_page_cost Sequentially disk page fetch. 1.00
cpu_tuple_cost Processing each tuple. 0.01 100x faster
cpu_index_tuple_cost Processing each entry during an index scan. 0.005 200x faster
cpu_operator_cost Processing each operator or function call. 0.0025 400x faster

Some vocabulary:

  • Tuple is a row of information.
  • Disk page is a block of information that can be read from the disk in a single operation; this may contain multiple tuples.

Each operation that the Query Planner performs is broken into some multiple of each of these operations. As mentioned earlier, the planner attempts to estimate the number of rows returned. This information is partly used to predict if sequential or nonsequential reads will be required. If it becomes cheaper to read the entire table from disk than to read a lot of random table pages, the planner may opt to do so — manifesting in a Seq Scan as opposed to an Index Scan, for exmaple.

These values, taken from many years of experience, can be tweeked in postgresql.conf, though I wouldn’t recommend that unless you are very sure of what you’re doing. For instance, an SSD may have a random-access cost similar to a sequential read.

Additionally, this is why I opted to do this tutorial on such a large example set. When the table size is small, it is often faster to Seq Scan, than it is to read the index and then read the tuples required. When the planner thinks it’ll be reading the majority of the rows anyway, it may forgo indices, even in very large tables, for the sake of decreasing the total amount of disk IO.

Since movie_id is supposed to be the primary key, let’s create a unique index and set it as the primary key.


[local]:5432 jim@planner_example=# create unique index on movies (movie_id);
[local]:5432 jim@planner_example=# alter table movies add primary key using index movies_movie_id_idx ;
[local]:5432 jim@planner_example=# \d movies
     Table "public.movies"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 movie_id | integer | not null
 title    | text    | 
 genres   | text[]  | 
Indexes:
    "movies_movie_id_idx" PRIMARY KEY, btree (movie_id)

Now, let’s rerun the query and see what’s changed.


[local]:5432 jim@planner_example=# select * from movies where movie_id = 6645; movie_id |      title      |             genres              
----------+-----------------+---------------------------------
     6645 | THX 1138 (1971) | {Action,Adventure,Drama,Sci-Fi}
(1 row)
Time: 0.737 ms

The first thing we should notice is that the query ran 10x faster! Let’s look at the explain.


[local]:5432 jim@planner_example=# explain select * from movies where movie_id = 6645;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Index Scan using movies_movie_id_idx on movies  (cost=0.29..8.30 rows=1 width=77)
   Index Cond: (movie_id = 6645)
(2 rows)
Time: 0.551 ms

We should notice that that Seq Scan from before has been transformed to an Index Scan, which additionally tells us the index being used, and which condition is being used in the index (the indented Index Cond line). The index scan allows us to not read pages from disk and skip directly to the one that will fulfull our query. This is evident in the cost: over 1000 to 8.

Another use of EXPLAIN is EXPLAIN ANALYZE, which actually runs the query and compares the actual values to the predicted ones.


[local]:5432 jim@planner_example=# explain analyze select * from movies where movie_id = 6645;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using movies_movie_id_idx on movies  (cost=0.29..8.30 rows=1 width=77) (actual time=0.023..0.025 rows=1 loops=1)
   Index Cond: (movie_id = 6645)
 Total runtime: 0.057 ms
(3 rows)
Time: 0.561 ms

Again, cost and actual time are in different units and will never be identical — cost is unitless and actual time is in ms. There is a rows value which is the same as in the predicted values. A hint here is that if the predicted number of rows is vastly different from the actual, a VACUUM ANALYZE may need to be run to update table and index statistics. The predicted number of rows won’t often match the actual rows exactly, but should be close. There is an additional value in the actual paramters: loops. This is an indication of how many times this part of the plan was run. We’ll come back to this value.

Now, let’s look at searching on the title.


[local]:5432 jim@planner_example=# select * from movies where title like '%THX%';
 movie_id |                  title                   |             genres              
----------+------------------------------------------+---------------------------------
     6645 | THX 1138 (1971)                          | {Action,Adventure,Drama,Sci-Fi}
   116157 | Electronic Labyrinth THX 1138 4EB (1967) | {Sci-Fi}
(2 rows)
Time: 14.442 ms

We see that the full-length motion picture and the short film that inspired it. Let’s see what the planner did to do the search.


[local]:5432 jim@planner_example=# explain select * from movies where title like '%THX%';
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on movies  (cost=0.00..1026.97 rows=3 width=77)
   Filter: (title ~~ '%THX%'::text)
(2 rows)
Time: 0.580 ms

PostgreSQL does a Seq Scan, which isn’t surprising considering we haven’t created an index on the title field. Let’s create an index and see what happens.


[local]:5432 jim@planner_example=# create index on movies (title);
CREATE INDEX
Time: 205.577 ms
[local]:5432 jim@planner_example=# \d movies
     Table "public.movies"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 movie_id | integer | not null
 title    | text    | 
 genres   | text[]  | 
Indexes:
    "movies_movie_id_idx" PRIMARY KEY, btree (movie_id)
    "movies_title_idx" btree (title)
[local]:5432 jim@planner_example=# select * from movies where title like '%THX%';
 movie_id |                  title                   |             genres              
----------+------------------------------------------+---------------------------------
     6645 | THX 1138 (1971)                          | {Action,Adventure,Drama,Sci-Fi}
   116157 | Electronic Labyrinth THX 1138 4EB (1967) | {Sci-Fi}
(2 rows)
Time: 14.207 ms
[local]:5432 jim@planner_example=# explain select * from movies where title like '%THX%';
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on movies  (cost=0.00..1026.97 rows=3 width=77)
   Filter: (title ~~ '%THX%'::text)
(2 rows)
Time: 0.738 ms

It doesn’t use the index! What!

Taking a look at table description, we see that movies_title_idx is a btree index. These indecies are fast to use and update, but can only handle equality and inequality comparisions (<, <=, =, >=, and >), not set contains or wild-card searches. Luckily! there is the pg_trgm (trgm = trigram) which creates an index based on all substrings up-to 3 characters long: Widdershins is broken into ‘e’, ‘n’, ‘W’, ‘r’, ‘h’, ‘i’, ‘d’, ‘s’, ‘ns’, ‘er’, ‘Wi’, ‘in’, ‘de’, ‘dd’, ‘rs’, ‘hi’, ‘id’, ‘sh’, ‘dde’, ‘ers’, ‘shi’, ‘der’, ‘ins’, ‘hin’, ‘rsh’, ‘idd’, and ‘Wid’.


[local]:5432 jim@planner_example=# create extension pg_trgm;
[local]:5432 jim@planner_example=# create index on movies using gist (title gist_trgm_ops);
[local]:5432 jim@planner_example=# \d movies
     Table "public.movies"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 movie_id | integer | not null
 title    | text    | 
 genres   | text[]  | 
Indexes:
    "movies_movie_id_idx" PRIMARY KEY, btree (movie_id)
    "movies_title_idx" btree (title)
    "movies_title_idx1" gist (title gist_trgm_ops)
[local]:5432 jim@planner_example=# drop index movies_title_idx;
[local]:5432 jim@planner_example=# select * from movies where title like '%THX%';
 movie_id |                  title                   |             genres              
----------+------------------------------------------+---------------------------------
     6645 | THX 1138 (1971)                          | {Action,Adventure,Drama,Sci-Fi}
   116157 | Electronic Labyrinth THX 1138 4EB (1967) | {Sci-Fi}
(2 rows)
Time: 10.321 ms
[local]:5432 jim@planner_example=# explain select * from movies where title like '%THX%';
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Bitmap Heap Scan on movies  (cost=4.30..15.74 rows=3 width=77)
   Recheck Cond: (title ~~ '%THX%'::text)
   -&gt;  Bitmap Index Scan on movies_title_idx1  (cost=0.00..4.30 rows=3 width=0)
         Index Cond: (title ~~ '%THX%'::text)
(4 rows)
Time: 0.648 ms

There is a lot going on there. pg_trgm is a built-in extension, meaning that, like hstore, it needs enabled with CREATE EXTENSION, but that 3rd part software need not be installed. Next, we’re using GiST index. While I’ll leave an in-depth examination of the types of indices for another post, I will say that b-trees are tree-like structure that keeps data in sorted order and are fast to update and use; GiST indices can keep arbitrary, multi-dimentional indices which are more flexible, a little slower to used than b-trees, and much slower to update. (Note: GiST isn’t an index, actually. It’s an interface for an index to be presented as and therefor may have different performance characteristics for different types. However, all types should offer the same types of operations that can be accelerated by the index.)

We see our query runs a little faster, but not significantly faster. Let’s take a look at what steps go into performing our query. First, we see that a “Bitmap Heap Scan” is being done. Looking beneath, to the -&gt; we see that to do the Bitmap Heap Scan, we need to do the Bitmap Index Scan, which traverses the index and ascertains if a disk page contains a tuple matching the Index Cond, additionally, we are told what index and what condition is being used. The Recheck Cond done by the Bitmap Heap Scan is what condition is used to filter the correct tuple from the disk page (remember! the Bitmap Index Scan only tells us a tuple is on the page, not where!)

———-

1 PostgreSQL has multiple output formats for EXPLAIN, some of which are suitable for consumption by other tools.


[local]:5432 jim@planner_example=# explain (format xml) select * from movies where movie_id = 6645;
                        QUERY PLAN                        
----------------------------------------------------------
 &lt;explain xmlns="http://www.postgresql.org/2009/explain"&gt;+
   &lt;Query&gt;                                               +
     &lt;Plan&gt;                                              +
       &lt;Node-Type&gt;Index Scan&lt;/Node-Type&gt;                 +
       &lt;Scan-Direction&gt;Forward&lt;/Scan-Direction&gt;          +
       &lt;Index-Name&gt;movies_movie_id_idx&lt;/Index-Name&gt;      +
       &lt;Relation-Name&gt;movies&lt;/Relation-Name&gt;             +
       &lt;Alias&gt;movies&lt;/Alias&gt;                             +
       &lt;Startup-Cost&gt;0.29&lt;/Startup-Cost&gt;                 +
       &lt;Total-Cost&gt;8.30&lt;/Total-Cost&gt;                     +
       &lt;Plan-Rows&gt;1&lt;/Plan-Rows&gt;                          +
       &lt;Plan-Width&gt;77&lt;/Plan-Width&gt;                       +
       &lt;Index-Cond&gt;(movie_id = 6645)&lt;/Index-Cond&gt;        +
     &lt;/Plan&gt;                                             +
   &lt;/Query&gt;                                              +
 &lt;/explain&gt;
(1 row)
Time: 2.919 ms
[local]:5432 jim@planner_example=# explain (format json) select * from movies where movie_id = 6645;
                 QUERY PLAN                 
--------------------------------------------
 [                                         +
   {                                       +
     "Plan": {                             +
       "Node Type": "Index Scan",          +
       "Scan Direction": "Forward",        +
       "Index Name": "movies_movie_id_idx",+
       "Relation Name": "movies",          +
       "Alias": "movies",                  +
       "Startup Cost": 0.29,               +
       "Total Cost": 8.30,                 +
       "Plan Rows": 1,                     +
       "Plan Width": 77,                   +
       "Index Cond": "(movie_id = 6645)"   +
     }                                     +
   }                                       +
 ]
(1 row)
Time: 0.601 ms
[local]:5432 jim@planner_example=# explain (format yaml) select * from movies where movie_id = 6645;
              QUERY PLAN               
---------------------------------------
 - Plan:                              +
     Node Type: "Index Scan"          +
     Scan Direction: "Forward"        +
     Index Name: "movies_movie_id_idx"+
     Relation Name: "movies"          +
     Alias: "movies"                  +
     Startup Cost: 0.29               +
     Total Cost: 8.30                 +
     Plan Rows: 1                     +
     Plan Width: 77                   +
     Index Cond: "(movie_id = 6645)"
(1 row)
Time: 0.537 ms

(The +‘s at the end of the line represent a new line and would not be returned in a programmatic fashion, such as your favorite programming languages postgresql library.)

These formats allow for nesting of the query plan as well. (Ed Note: Replace these with a better example when we get to that point).

Eddie Bauer Langley Set

Date: 2015-08-21

Tags: furniture

I sent this to Target and Dorel Living because of the experience I’ve had with their dresser.

My wife and I received a Langley Dresser and Crib in expectation of our first child.

The assembly of the crib was made simple, sturdy, and quick by the length of the bolts, and metal counter-screws already in the crib frame. I was quite glad it was not a lock-and-cam system that plagues cheaper items, as they always seem to wobble after a while.

The dresser, on the other hand, has been a complete nightmare. The first step has cross beams being mounted to the middle section of the dresser with lock-and-cam faster, whose screw-locks never stabilize and just continue to turn as I attempt to tighten them and cams never lock as well as they should compared to an actual screw, or worse, the cross beams are simply sitting on a peg, in a hole too large to prevent movement.

Moving onto the second step, and praying that everything from the first step doesn’t just fall apart because nothing is adequately fastened (or fastened at all!) for what should be the core of the crib and the nuclease that the rest of the piece is assembled to, I found my prayers were not answered, and in attempting to attach one of the 5 crossbeams the core shifted, pulling out the plastic anchors I had already screwed and cammed into place.

Especially after a simple, quick, and sturdy assembly process with the crib, I expected the same simple, quick, and sturdy process with the dresser and was greatly surprised and very displeased at the shoddy and inadequate fastening system provided with the dresser. I expect as much from sub-$100 pieces of furniture, but not something that’s suppose to be of better quality — otherwise why would I have bought it?

Beyond the shoddy and inadequate fastening system for a dresser with a long expected lifetime, I am beyond worried that a part designed to hold a screw-lock in position could be pulled out with such little force, that is the middle section twisting because nothing is tightly fastened to it. I will more-than-likley end up using wood glue to secure the part that detached and any others that continue to. Moreover, I am also planning on purchasing larger screws that I can use to fasten the dresser properly.

While the crib is exactly what I expected, the dresser was worlds apart. I cannot in good conscience recommend this set to anyone.

UPDATE (2015-08-23):

hello James,

I’m sorry to hear about the trouble you had with the crib you bought at Target. I know how frustrating it can be when an item doesn’t work out.

We’re always looking for ways to make the Target experience better for every guest. I’ll be sure to share your comments with our Merchandising team for further review with our buyers. In the meantime, we’ll keep working to provide you with the high quality merchandise you’ve come to expect at Target.

Thanks for writing. Hearing about your experience is important to us.

Sincerely,

Gagan

Target Guest Relations

www.target.com

Thanks for actually reading my complaint and not just skimming the first line for a product name! /s Also, I’d rather not be contacted if all I’m getting is a form letter.

Row Level Security in PostgreSQL 9.5

Date: 2015-08-21

Tags: postgres development

I am extremely excited about What’s new in PostgreSQL 9.5 that I wanted to try it out before its final release. Here I’ll be discussing the new Row Level Security (RLS) features in PostgreSQL 9.5. All examples here are done in postgresql-9.5 α2.

RLS is a feature that allows the contents of a table to be filtered before being returned to the user. For instance, if you have a web store and would like to allow a user to only see their own orders, traditionally we would filter with an explicit WHERE clause.

Setup:

rlstest=# create table orders (order_id serial primary key, amount integer not null check (amount &gt; 0.0), time_placed timestamptz default current_timestamp, customer text);
CREATE TABLE
rlstest=# insert into orders (amount, customer) values (1000, &#39;honor&#39;), (2175, &#39;nimitz&#39;);
INSERT 0 2

Honor is viewing a page that shows her orders.

rlstest=# select order_id, amount, customer from orders where customer = &#39;honor&#39;;
 order_id | amount | customer 
----------+--------+----------
        1 |   1000 | honor      
(1 row)

This is all fine and dandy, until someone, somewhere, inadvertently or maliciously leaves off or makes ineffective the access control clause

rlstest=# select order_id, amount, customer from orders;
 order_id | amount | customer 
----------+--------+----------
        1 |   1000 | honor      
        2 |   2175 | nimitz     
(2 rows)

Whoops!

How can RLS help to prevent issues such as these? The database can be told to only allow different roles to see only certain roles, defined once, not for every query. This differes from the normal GRANTs you could give roles because they filter the rows, not simply allow or disallow operations on a given table as a whole.

Set up: Let’s create the honor and nimitz roles. Note, this does mean that each of your application users would need a corresponding user in the database.

rlstest=# create role honor;
CREATE ROLE
rlstest=# create role nimitz;
CREATE ROLE

Now let’s enable RLS on our orders table and create a read-only policy if you’re the customer of the order.

rlstest=# alter table orders enable row level security;
ALTER TABLE
rlstest=# create policy orders_reader on orders for select using (customer = current_user);
CREATE POLICY

Now, let’s switch roles to honor and look at our order!

rlstest=&gt; select order_id, amount, customer, time_placed from orders;
ERROR:  permission denied for relation orders

Oh No!

RLS does not supercede the normal GRANT allowances. If the role can’t see a table, then the RLS policy can’t change that! set role *YOUR USER* and GRANT select on orders. Now try again.

rlstest=# grant select on orders to honor;
GRANT
rlstest=# grant select on orders to nimitz;
GRANT
rlstest=# set role honor;
SET
rlstest=&gt; select order_id, amount, customer, time_placed from orders;
 order_id | amount | customer |         time_placed          
 ----------+--------+----------+------------------------------
         1 |   1000 | honor    | 2015-08-21 14:05:13.03572-04
         (1 row)

Success!

rlstest=# set role nimitz;
SET
rlstest=&gt; select order_id, amount, customer, time_placed from orders;
 order_id | amount | customer |         time_placed          
 ----------+--------+----------+------------------------------
         2 |   2175 | nimitz   | 2015-08-21 14:05:13.03572-04
         (1 row)

Yes!

Now, being good database developers, we normalize our databases and wouldn’t store the customer’s username in the order. So, let’s create a customers table and update our orders table. (Remember to switch back to your role!)

rlstest=# create table customers (customer_id serial primary key, username text not null unique);  -- You could imagine a real table having more fields for email, phone, address, &amp;c
CREATE TABLE
rlstest=# insert into customers (username) values (&#39;honor&#39;), (&#39;nimitz&#39;);
INSERT 0 2
rlstest=# grant select on customers to honor;
GRANT
rlstest=# grant select on customers to nimitz;
GRANT
rlstest=# alter table orders add column customer_id integer references customers;
ALTER TABLE
rlstest=# update orders set customer_id = (select customer_id from customers where username = orders.customer);
UPDATE 2
rlstest=# alter table orders alter column customer_id set not null; -- We couldn&#39;t do this when we created the column, because on creation the column has the default (NULL in this case) value
ALTER TABLE
rlstest=# alter table orders drop column customer;
ERROR:  cannot drop table orders column customer because other objects depend on it
DETAIL:  policy orders_reader on table orders depends on table orders column customer
HINT:  Use DROP ... CASCADE to drop the dependent objects too.


Good, we can’t drop the column because there is a policy on it!

rlstest=# alter table orders drop column customer cascade;
NOTICE:  drop cascades to policy orders_reader on table orders
ALTER TABLE

Wait! Won’t everyone be able to see everything!

rlstest=# set role honor;
SET
rlstest=&gt; select * from orders;
 order_id | amount | time_placed | customer_id 
 ----------+--------+-------------+-------------
 (0 rows)

OK, well, still breaks things, but things breaking by not leaking information is a good way to break! Go back to your role.

So, what we want to do is allow people to see orders if their username is the current role, and the orders.customer_id is the same as that customers.customer_id. If we were writing a straight SQL query, we’d do something like

select * from orders inner join customers using (customer_id) where username = current_user;

However, we need to do this row by row, not as a set.

rlstest=# create policy orders_reader on orders for select using ( (select true from customers where username = current_user and customers.customer_id = orders.customer_id) );
CREATE POLICY
test=# set role honor;
SET
rlstest=&gt; select * from orders;
 order_id | amount |         time_placed          | customer_id 
 ----------+--------+------------------------------+-------------
         1 |   1000 | 2015-08-21 14:05:13.03572-04 |           1
         (1 row)
rlstest=&gt; set role nimitz;
SET
rlstest=&gt; select * from orders;
 order_id | amount |         time_placed          | customer_id 
 ----------+--------+------------------------------+-------------
         2 |   2175 | 2015-08-21 14:05:13.03572-04 |           2
         (1 row)

But wait! Now everyone can see all the customers!

rlstest=# set role nimitz;
SET
rlstest=&gt; select * from customers;
 customer_id | username 
-------------+----------
           1 | honor
           2 | nimitz
(2 rows)

Well, if we have phone numbers and other PII in there, that’s not good! No back to your user and enable RLS for customers!

rlstest=# alter table customers enable row level security;
ALTER TABLE

Remember how we couldn’t see any orders before we created policies? Same is true.

rlstest=&gt; select * from customers;
 customer_id | username 
-------------+----------
(0 rows)

Same true here! Since the policy for orders depends on the customers, will it affect how we see orders?

rlstest=&gt; select * from orders;
 order_id | amount | time_placed | customer_id 
----------+--------+-------------+-------------
(0 rows)

Yes! Yes it does! Since the policy (or lack there of) won’t let us see any customers, none of the customers match the criteria for the orders policy!

Go back to your users and lets create a read-only policy for customers.

rlstest=# create policy customer_reader on customers for select using (username = current_user);
CREATE POLICY

Now, let’s try this again

rlstest=# set role nimitz;
SET
rlstest=&gt; select * from customers;
 customer_id | username 
-------------+----------
           2 | nimitz
(1 row)
rlstest=&gt; select * from orders;
 order_id | amount |         time_placed          | customer_id 
----------+--------+------------------------------+-------------
        2 |   2175 | 2015-08-21 14:05:13.03572-04 |           2
(1 row)

Awesome! It works again!

But wait! In the policy for orders, we are explicitly limiting the customers based on username, and we do that on the policy for the customers too, so why do double work? Additionally, what if we ever want to change the rules for who can see customers (maybe we’ll add customers who can manage other customers, like a business account). As your role:

rlstest=# drop policy orders_reader on orders;
DROP POLICY
rlstest=# create policy orders_reader on orders for select using ( (select true from customers where customers.customer_id = orders.customer_id) );
CREATE POLICY

Let’s test it out!

rlstest=&gt; set role honor;
SET
rlstest=&gt; select * from customers;
 customer_id | username 
-------------+----------
           1 | honor
(1 row)
rlstest=&gt; select * from orders;
 order_id | amount |         time_placed          | customer_id 
----------+--------+------------------------------+-------------
        1 |   1000 | 2015-08-21 14:05:13.03572-04 |           1
(1 row)
rlstest=&gt; set role nimitz;
SET
rlstest=&gt; select * from customers;
 customer_id | username 
-------------+----------
           2 | nimitz
(1 row)
rlstest=&gt; select * from orders;
 order_id | amount |         time_placed          | customer_id 
----------+--------+------------------------------+-------------
        2 |   2175 | 2015-08-21 14:05:13.03572-04 |           2
(1 row)

Awesome! Now we are controlling the majority of our access control directly in the database, and what’s more, we’re controlling its granulary per table without having to cross-filter from one table to another, enabling us to work on the access control without leaking permissions without repeating yourself (“Keep it DRY”).

There is a caveat to all of this, though: views. Views run as the user who created them, not the user who executes them. As such, if your table-owner were to create a view, the view would have the RLS permissions of the table owner, namly, all permissions. As your role:

rlstest=# create view customer_view as select customer_id, username from customers;
CREATE VIEW
rlstest=# drop policy orders_reader on orders;
DROP POLICY
rlstest=# create policy orders_reader on orders for select using ( (select true from customer_view where customer_view.customer_id = orders.customer_id) );
CREATE POLICY
rlstest=# set role honor;
SET
rlstest=&gt; select * from customers;
 customer_id | username 
-------------+----------
           1 | honor
(1 row)
rlstest=&gt; set role jim;
SET
rlstest=# grant select on customer_view to honor;
GRANT
rlstest=# grant select on customer_view to nimitz;
GRANT
rlstest=# set role honor;
SET
rlstest=&gt; select * from customers;
 customer_id | username 
-------------+----------
           1 | honor
(1 row)
rlstest=&gt; select * from customer_view;
 customer_id | username 
-------------+----------
           1 | honor
           2 | nimitz
(2 rows)
rlstest=&gt; select * from orders;
 order_id | amount |         time_placed          | customer_id 
----------+--------+------------------------------+-------------
        1 |   1000 | 2015-08-21 14:05:13.03572-04 |           1
        2 |   2175 | 2015-08-21 14:05:13.03572-04 |           2
(2 rows)

As you can see, the view is being run as the table owner and as such allows access to all rows.

One work around (if you really need to use a view, say hierarchical permissions), functions in a are excuted as the caller not the owner of the view.

rlstest=# create function can_see_customer(username text) returns boolean as $$begin
return current_user = username;
end;
$$
language &#39;plpgsql&#39;;
CREATE FUNCTION
rlstest=# create or replace view customer_view as (select * from customers where can_see_customer(username));
CREATE VIEW
rlstest=# set role honor;
SET
rlstest=&gt; select * from customer_view;
 customer_id | username 
-------------+----------
           1 | honor
(1 row)
rlstest=&gt; select * from orders;
 order_id | amount |         time_placed          | customer_id 
----------+--------+------------------------------+-------------
        1 |   1000 | 2015-08-21 14:05:13.03572-04 |           1
(1 row)

While this isn’t perfect, because you’re duplicating the access rules (to the customers in this example).

Now that we have policies working, we can look at them by describing a table in psql.

rlstest=# \d customers
                                Table &#34;public.customers&#34;
   Column    |  Type   |                            Modifiers                            
-------------+---------+-----------------------------------------------------------------
 customer_id | integer | not null default nextval(&#39;customers_customer_id_seq&#39;::regclass)
 username    | text    | not null
Indexes:
    &#34;customers_pkey&#34; PRIMARY KEY, btree (customer_id)
    &#34;customers_username_key&#34; UNIQUE CONSTRAINT, btree (username)
Referenced by:
    TABLE &#34;orders&#34; CONSTRAINT &#34;orders_customer_id_fkey&#34; FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
Policies:
    POLICY &#34;customer_reader&#34; FOR SELECT
      USING ((username = (&#34;current_user&#34;())::text))
rlstest=# \d orders
                                       Table &#34;public.orders&#34;
   Column    |           Type           |                         Modifiers                         
-------------+--------------------------+-----------------------------------------------------------
 order_id    | integer                  | not null default nextval(&#39;orders_order_id_seq&#39;::regclass)
 amount      | integer                  | not null
 time_placed | timestamp with time zone | default now()
 customer_id | integer                  | not null
Indexes:
    &#34;orders_pkey&#34; PRIMARY KEY, btree (order_id)
Check constraints:
    &#34;orders_amount_check&#34; CHECK (amount::numeric &gt; 0.0)
Foreign-key constraints:
    &#34;orders_customer_id_fkey&#34; FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
Policies:
    POLICY &#34;orders_reader&#34; FOR SELECT
      USING (( SELECT true AS bool
   FROM customer_view
  WHERE (customer_view.customer_id = orders.customer_id)))

In a later article, I’ll discuss how RLS policies affect the query planner.

Projections and Why They're Important

Date: 2015-07-25

Tags: gis projection

In GIS a projection defines how a three dimentional object is drawn in two dimentions. There are two basic types of projections, Geographic Coordinate Systems (GCS) and Projected Coordinate Systems (PCS). While there is much to be said about different types of projections, and I would highly recommend starting with the wikipedia article and moving to the ones on the ESRI site, here I’m going to focus on the difference and usage GCS and PCS.

GCS is a three dimensional model that is fit to the Earth and how to map that to a two dimensional surface. A position on it is measured as the angle north or south of the Equator and east or west of the Prime Meridian. An example is the familar WGS84/EPSG 4326 (GPS coordinates). However, since the Earth isn’t a perfect spheroid, GCS are OK everywhere, but better in some places.

The green line represents the shape of the Earth, whereas the black line represents a perfect sheroid; the match is very close in the bottom right, but not as good in the top left.

A PCS, on the other hand, is defined on a two dimensional surface and how to map that onto a three dimentional model. A position on it is measured as a unit of length (often the meter or foot) left or right and above or below an origin. An example is the Pennsylvania South State Plane which is valid for the souther counties in Pennsylvania and UTM Zone 17N (used in the Military Grid Reference System).

PCS are valid over a smaller area than a GCS, but allow for more accurate measure of length and area within those smaller areas; this is because the Earth isn’t spherical and an angular change at the Equator is not equal to an angular change on the Prime Meridian. And the irregularities of the shape of the Earth make compesnsation for the spheroidal shape still imperfect.

As an example, if I wanted to create a 110mi buffer around Pittsburgh, which is approximately 2 degrees at my latitude.

However, I know that Erie is roughly 110mi from Pittsburgh (about 120mi driving), so I immediately knew something was wrong. It dawned on me that I had used a GCS and I’m a bit beyond where you can fudge a measure up equals a measure left. I converted my layer to a PCS, Zone 17N, which uses meters as its base unit and created a 178km buffer.

That looked more realistic and I double checked some distances to some destinations in Ohio, Pennsylvania, and Weste Virginia to be sure.

To illustrate the difference, I overlayed my original 2 degrees buffer.

You can see that 2 degrees, when measured in meters, is an oval, and not a circle.

To sum up: use a GCS when you are dealing with the relationship between objects over a very wide area and a PCS when you are dealing with linear measurments (e.g. distance and area). Which GCS or PCS to use is another set of posts.

Allegheny County Real Estate CD

Date: 2015-07-24

Tags: gis real-estate

The Allegheny County Offered by the Office of Property Assessments offers a CD with information for all of the properties in the county that is updated quarterly. The CD contains a single 242MB file, currently in xlsx format.

To convert the Microsoft format to a format which can be consumed by PostgreSQL, namely CSV, LibreOffice can be used:

libreoffice --headless --convert-to csv ALLEGHENY_COUNTY_MASTER_FILE_07022015.xlsx

We can now create a table to import the CSV directly into. I created this schema by looking at the data found in the csv and correcting any errors found on import. PostgreSQL is very nice in that it won’t truncate data and will complain if invalid data is inserted. If you choose to use MySQL, please be aware that it can truncate and coerce data.

CREATE TABLE tax_assessments (
    parid character(16),
    propertyowner character varying(255),
    propertyhousenum character varying(255),
    propertyfraction character varying(255),
    propertyaddress character varying(255),
    propertycity character varying(255),
    propertystate character varying(255),
    propertyunit character varying(255),
    propertylocation2 character varying(255),
    propertyzip character(5),
    municode character varying(4),
    munidesc character varying(255),
    schoolcode character varying(4),
    schooldesc character varying(255),
    neighcode character varying(50),
    neighdesc character varying(255),
    taxcode character(1),
    taxdesc character varying(255),
    ownercode character(2),
    ownerdesc character varying(255),
    statecode character(1),
    statedesc character varying(255),
    usecode character(3),
    usedesc character varying(255),
    lotarea integer,
    homesteadflag character(3),
    farmsteadflag character(3),
    saledate character(8),
    saleprice integer,
    salecode character(2),
    saledesc character varying(255),
    deedbook character varying(50),
    deedpage character varying(50),
    mabt character(1),
    agent character varying(255),
    taxfulladdress1 character varying(255),
    taxfulladdress2 character varying(255),
    taxfulladdress3 character varying(255),
    taxfulladdress4 character(5),
    changenoticeaddress1 character varying(255),
    changenoticeaddress2 character varying(255),
    changenoticeaddress3 character varying(255),
    changenoticeaddress4 character(5),
    countybuilding integer,
    countyland integer,
    countytotal integer,
    countyexemptbldg integer,
    localbuilding integer,
    localland integer,
    localtotal integer,
    fairmarketbuilding integer,
    fairmarketland integer,
    fairmarkettotal integer,
    style character(2),
    styledesc character varying(255),
    stories numeric,
    yearblt character(4),
    exteriorfinish character(1),
    extfinish_desc character varying(255),
    roof character(1),
    roofdesc character varying(255),
    basement character(1),
    basementdesc character varying(255),
    grade character varying(3),
    gradedesc character varying(255),
    condition character(1),
    conditiondesc character varying(255),
    totalrooms integer,
    bedrooms integer,
    fullbaths integer,
    halfbaths integer,
    heatingcooling character(1),
    heatingcoolingdesc character varying(255),
    fireplaces integer,
    attachedgarages integer,
    finishedlivingarea integer,
    cardnumber integer,
    alt_id character varying(255),
    taxsubcode_desc character varying(255),
    taxsubcode character(1)
);
CREATE INDEX parid_idx ON tax_assessments ( parid );

From there we can import the CSV:

COPY tax_assessments FROM 'ALLEGHENY_COUNTY_MASTER_FILE_07022015.csv' WITH CSV HEADER

Here’s an assorted set of some of the fields. You’ll note that these are sorted lexically, not numerically and that the fields are character fields (see above). This is intentional because since I didn’t create this data, I can’t always guarentee there won’t be a change in the various codes later on. See heatingcooling. There is no reason you couldn’t make them integer fields in your own import as applicable.

ownercode ownerdesc
10 REGULAR
11 REGULAR-ETAL
12 REGULAR-ETUX OR ET VIR
13 REGULAR-ESTATE
16 REGULAR-ETAL & ETUX
18 REGULAR-UNFINISHED
20 CORPORATION
21 CORPORATION-RAILROAD
23 CORPORATION-RIGHTOFWAY
28 CORPORATION-UNFINISHED
schoolcode schooldesc
1 Allegheny Valley
10 Clairton City
11 Cornell
12 Deer Lakes
13 Duquesne City
14 East Allegheny
16 Elizabeth Forward
17 Fox Chapel Area
18 Monroeville Gateway
2 Avonworth
20 Hampton Township
21 Highlands
22 Keystone Oaks
23 McKeesport Area
24 Montour
25 Moon Area
26 Mt Lebanon
27 North Allegheny
28 North Hills
29 Northgate
3 Pine-Richland
30 Penn Hills Twp
31 Plum Boro
32 Quaker Valley
33 Riverview
34 Shaler Area
35 South Allegheny
36 South Fayette Twp
37 South Park
38 Steel Valley
39 Sto-Rox
4 Baldwin Whitehall
42 Upper St Clair
43 West Allegheny
44 West Jefferson
45 West Mifflin Area
46 Wilkinsburg Boro
47 City Of Pittsburgh
48 McDonald
49 Trafford
5 Bethel Park
50 Norwin
6 Brentwood Boro
7 Carlynton
8 Chartiers Valley
9 Woodland Hills
usecode usedesc
1 RES SKELETON RECORD
10 SINGLE FAMILY
100 VACANT LAND
101 GENERAL FARM
102 LIVE STOCK FARM
103 DAIRY FARM
104 POULTRY FARM
105 FRUIT & NUT FARM
106 VEGETABLE FARM
108 NURSERY
109 GREENHOUSES, VEG & FLORACULTURE
110 >10 ACRES VACANT
111 BUILDERS LOT
112 LIVESTOCK O/T D & P-CAUV
115 FRUIT & NUT FARMCAUV
118 CONDOMINIUM COMMON PROPERTY
120 TIMBER OR FOREST LAND
130 RIGHT OF WAYRESIDENTIAL
131 RETENTION PONDRESIDENTIAL
190 OTHER
199 OTHER AGRICULTURALCAUV
2 COM SKELETON RECORD
20 TWO FAMILY
210 COAL LAND, SURFACE RIGHTS
220 COAL RIGHTS, WORKING INTERESTS
230 COAL RIGHTS SEP. ROYALTY INTEREST
240 OIL & GAS RIGHTS WORKING INTEREST
260 OTHER MINERALS
30 THREE FAMILY
300 VACANT INDUSTRIAL LAND
310 FOOD & DRINK PROCESSING
317 FORESTRY WITH BUILDING
320 HEAVY MANUFACTURING
330 MEDIUM MANUFACTURING
340 LIGHT MANUFACTURING
341 RECYCLING/SCRAP YARDS
345 BULK TRANSFER TERMINAL
350 WAREHOUSE
351 WAREHOUSE/MULTI-TENANT
352 MINI WAREHOUSE
353 DISTRIBUTION WAREHOUSE
360 INDUSTRIAL TRUCK TERM
370 SMALL SHOP
380 MINES AND QUARRIES
389 INDUSTRIAL/UTILITY
399 OTHER
40 FOUR FAMILY
400 VACANT COMMERCIAL LAND
401 APART: 5-19 UNITS
402 APART:20-39 UNITS
403 APART:40+ UNITS
404 RETL/APT’S OVER
405 RETL/OFF OVER
406 RETL/STOR OVER
409 BED & BREAKFAST
410 MOTEL & TOURIST CABINS
411 HOTELS
412 NURSING HOME/PRIVATE HOS
413 INDEPENDENT LIVING (SENIORS)
415 MOBILE HOMES/TRAILER PKS
416 CAMPGROUNDS
418 DAYCARE/PRIVATE SCHOOL
419 OTHER COMMERCIAL HOUSING
420 SMALL DETACHED RET
421 SUPERMARKETS
422 DISCOUNT STORE
423 PHARMACY (CHAIN)
424 DEPARTMENT STORE
425 NEIGH SHOP CENTER
426 COMMUNITY SHOPPING CENTER
427 REGIONAL SHOPPING CENTER
429 OTHER RETAIL STRUCTURES
430 RESTAURANT, CAFET AND/OR BAR
431 OFFICE/APARTMENTS OVER
432 OFFICE/RETAIL OVER
433 OFFICE/STORAGE OVER
434 BARS
435 DRIVE IN REST OR FOOD SERVICE
437 FAST FOOD/DRIVE THRU WINDOW
439 OTHER FOOD SERVICE
440 DRY CLEANING PLANTS/LAUNDRIES
441 FUNERAL HOMES
442 MEDICAL CLINICS/OFFICES
444 BANK
445 SAVINGS AND LOANS
447 OFFICE – 1-2 STORIES
448 OFFICE-WALKUP -3 + STORIES
449 OFFICE-ELEVATOR -3 + STORIES
450 CONDOMINIUM OFFICE BUILDING
451 FIRE DEPARTMENT/EMS
452 AUTO SERV STATION
453 CAR WASH
454 AUTO SALES & SERVICE
455 COMMERCIAL GARAGE
456 PARKING GARAGE/LOTS
458 GAS STATION KIOSK
460 THEATER
461 COUNTRY CLUBS
462 GOLF DRIVING RANGE/MINIATURE
463 GOLF COURSES (PUBLIC)
464 BOWLING ALLEYS/REC FACILITY
465 LODGE HALL/AMUSEMENT PARK
470 DWG USED AS OFFICE
471 DWG USED AS RETAIL
472 DWG APT CONVERSION
473 GROUP HOME
474 HEAVY EQUIPMENT SALES/RENTAL
480 OFFICE/WAREHOUSE
481 OTHER COMMERCIAL
482 COMMERCIAL TRUCK TERMINAL
488 AIR RIGHTS
489 COMMERCIAL/UTILITY
490 MARINE SERV FACILITY
491 CONVENIENCE STORE
492 CONVENIENCE STORE/GAS
493 CONVENIENCE STORE GAS/REPAIRS
494 BIG BOX RETAIL
496 MARINA
499 COMM AUX BUILDING
50 CONDOMINIUM
500 RESIDENTIAL VACANT LAND
501 VACANT LAND 0-9 ACRES
517 FORESTRY W/BUILDINGS
530 RIGHT OF WAYCOMMERCIAL
531 RETENTION PONDCOMMERCIAL
55 COMMON AREA
550 CONDOMINIUM UNIT
553 H.O.A RECREATIONS AREA
556 COMMON AREA OR GREENBELT
557 COMM APRTM CONDOS 5-19 UNITS
558 COMM APRTM CONDOS 20-39 UNITS
559 COMM APRTM CONDOS 40+ UNITS
56 CONDO DEVELOPMENTAL LAND
57 CONDO GARAGE UNITS
599 OTHER RESIDENTIAL STRUCTURE
60 TOWNHOUSE
600 FEDERAL GOVERNMENT
601 HUD PROJ #202
602 HUD PROJ #207/223
603 HUD PROJ #213
604 HUD PROJ #220
605 HUD PROJ #221
606 HUD PROJ #223
607 HUD PROJ #232
609 HUD PROJ #236
610 STATE GOVERNMENT
620 COUNTY GOVERNMENT
630 TOWNSHIP GOVERNMENT
640 MUNICIPAL GOVERNMENT
645 OWNED BY METRO HOUSING AU
650 OWNED BY BOARD OF EDUCATION
660 PUBLIC PARK
670 OWNED BY COLLEGE/UNIV/ACADEMY
680 CHARITABLE EXEMPTION/HOS/HOMES
685 CHURCHES, PUBLIC WORSHIP
690 CEMETERY/MONUMENTS
70 ROWHOUSE
700 COMMUNITY URBAN RENEWAL
710 COMMUNITY REINVESTMENT
720 MUNICIPAL IMPROVEMENT
730 MUNICIPAL URBAN RENEWAL
740 OTHER
750 CASINO
777 INCOME PRODUCING PARKING LOT
80 MOBILE HOME
800 AGR LAND
810 MINERAL LAND
820 INDUSTRIAL LAND
830 COMMERCIAL LAND
840 R.R. – USED IN OPERATION
850 R.R. – NOT USED IN OPERATION
860 RR-PP – USED IN OPERATION
880 P.P. – P.U. – OTHER THAN R.R.
90 MOBILE HOME (IN PARK)
96 MINOR FIRE DAMAGE
97 TOTAL/MAJOR FIRE DAMAGE
98 CONDEMNED/BOARDED-UP
99 RES AUX BUILDING (NO HOUSE)
996 MINOR FIRE DAMAGECOMM
997 TOTAL/MAJOR FIRE DAMAGECOMM
998 TOTAL/MAJOR FIRE DAMAGECOMM
999 UNLOCATED PARCEL
style styledesc
1 RANCH
10 MODULAR HOME
11 ROW END
12 ROW INTERIOR
13 MULTI-FAMILY
14 VICTORIAN
15 OTHER
16 OLD STYLE
17 LOG CABIN
18 BUNGALOW
19 TUDOR
2 SPLIT LEVEL
20 SEMI DETACHED
21 CONDO HR
22 CONDO GRDN
23 CONDO SINGLE
24 CONDO END
25 CONDO INT
26 CONDO PAT/CARG
27 CONDO CONV
3 BI-LEVEL
4 COLONIAL
5 CAPE COD
6 CONVENTIONAL
7 CONTEMPORARY
8 CONDO
9 TOWNHOUSE
M1 MANUFACTURED
M2 MANUFACTURED
exteriorfinish extfinish_desc
1 Frame
2 Brick
3 Stone
4 Stucco
5 Concrete Block
6 Masonry FRAME
7 Concrete
8 Log
roof roofdesc
1 SHINGLE
2 SLATE
3 METAL
4 ROLL
5 TILE
6 RUBBER
basement basementdesc
1 None
2 Slab/Piers
3 Crawl
4 Part
5 Full
heatingcooling heatingcoolingdesc
1 None
2 Central Heat
3 Wall Furnace
4 Electric
5 Unit Heat
6 Heat Pump
7 Floor Furnace
8 Other
A No Heat but with AC
B Central Heat with AC
C Wall Furnace with AC
D Electric Heat with AC
E Unit Heat with AC
F Heat Pump with AC
G Floor Furnace with AC
H Other
homesteadflag
HOM
farmsteadflag
FRM