Nick Johnson

Today I decided that rather than reindex everything so that all my object IDs can be unique, it would be easier to create a few views in PostgreSQL. That would solve my earlier problem of doing a join with JPA between the article table and the message roots table.

Fortunately, this is easy in PostgreSQL:

CREATE VIEW article_message_root(root_source, root_id, posting_permitted, post_count, post_count_24hr, last_post)
    AS SELECT root_source, root_id, posting_permitted='y', post_count, post_count_24hr, last_post
       FROM roots
       WHERE root_type='article';

As you can see, we can even convert one type to another with a view; when I originally made this table, eons ago, the boolean datatype wasn't available (initially this schema was from an ancient version of MySQL) or I was having some other problem with it. Now I can compensate for that in the view. PostgreSQL determines the data types of each of the resulting columns based on the data types given in the SELECT.

The view seems to be implemented by appending the conditions in the view's WHERE clause to any queries issued against the view:

=> explain select * from article_message_root where root_source = 1234;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using roots_root_source_key on roots  (cost=0.00..5.99 rows=1 width=29)
   Index Cond: ((root_source = 1234) AND ((root_type)::text = 'article'::text))

Compare that to:

=> explain select * from roots where root_source = 1234 and root_type='article';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Scan using roots_root_source_key on roots  (cost=0.00..5.99 rows=1 width=40)
   Index Cond: ((root_source = 1234) AND ((root_type)::text = 'article'::text))

Eventually I'll restructure the whole site codebase and these views will go away in favor of a system where every object has a unique identity across the entire system.

I'm also pondering using the insert trigger to create an entry in a centralized table when that time comes to record the type of object associated with each object identity. Makes it easier to hunt these things down should it become necessary.

Labels: software