Playing with black matter – continuation
Some time ago I started experimenting with PostgreSQL features to allow creating foreign keys for ActiveRecord polymorphic relations. Since PostgreSQL is a pretty powerful tool, I ended up creating a pg_morph gem which made it much easier to achieve. How it works I already described in my previous post, but if you don’t want to go back in the past, I’ll give a short refresher.
Pg_morph takes advantage of inheritance and partitioning features of PostgreSQL. For a main table which contains records associated with different tables it creates partitions in which proper records may be stored based on their association type. All those partitions inherit from main table, so by running searches on the main table you can retrieve data from its partitions. It has one additional advantage, which is searching by only one type of association requires looking into only one partition, the others are omitted, so queries may be notably faster.
From the ActiveRecord’s point of view nothing has changed and this was also a goal for a new version of pg_morph, which took magic even further beyond ActiveRecord.
So what has changed?
The initial version had one caveat, but a serious one. Because all inserts to the main table had to be redirected to partitions, there was nothing created in the main table and RETURNING id statement at the end of each INSERT wasn’t returning what every Ruby on Rails programmer is used to – newly created records had id equal nil. Not nice. To bypass this behavior, PostgreSQL was allowed to create records in the main table, and then, after insert, the duplicated record was removed.
To avoid such ugly solution I added new layer on top of all tables, which is the view of the main table. Having that, there is no need to overload database by creating redundant rows and deleting them immediately after. Thanks to that, additional operations which may cause some problems, are avoided. The main table remains empty so whole structure of partitions inheriting from main the table is build in accordance with PostgreSQL specification. And what’s most important is that ActiveRecord now doesn’t have any problems with missing ids of newly created records.
‘Whoa, wait!’ you can exclaim. ‘How can AR know that now there is a view which has to be used instead of a good old table. Does it mean that I should change all my associations to use new relation?’ No, don’t worry, pg_morph takes care of it. It creates view in place of the main table and renames main table once again taking care of possible naming conflicts.
Give me some code!
Let’s take some example data model:
So after running:
you will have a view named images which knows exactly which tables to ask for the records. And what’s most important, both partitions – for users images and items images – will have foreign keys.
Also removing this whole additional structure is as easy as adding that line to your migrations:
It doesn’t matter if you want to remove all partitions or only one particular. It will check if there are other partitions of the main table (in this example case images) which still have to be supported, and in the case that there are no more of them, the view is removed and the main table recovers its previous name. Everything starts to work like nothing has even happened.
Of course there is still room for improvements, so if you see some ways of making it better or spot any problems, simply share. Or even better – fork, and pull request!