How do you run ActiveRecord `find_each` to a materialized view in postgres

Question

Because I am pulling thousands of records from my materialized view Matview, I'd like to use find_each to break it up into batches to free up memory.

However, when I run the following:

Matview.where('p_id > 100').find_each {|m| m }

I get the following eror:

PG::SyntaxError: ERROR:  zero-length delimited identifier at or near """"

Upon research I found that you cannot run find_each on a table without a primary key.

I tried adding a Unique ID to Matview using:

CREATE UNIQUE INDEX index_mv ON matview(p_id)

while that was successful, that didn't substitute for a primary key.

I tried adding a primary constraint to my materialized view using the following:

ALTER MATERIALIZED matview ADD CONSTRAINT mv_pk primary key (p_id) disabled

But that failed.

So generally, my question is, how do I run find_each on a materialized view, and maybe specifically, how do I add a primary key to a materialized view so that I can run find_each?

EDIT: For clarity, I do not have an id column in Matview. If I had one, I hear that it might be recognized by active record as a default primary key, but I have not tested that.

score 4 · Accepted Answer · answered Mar 13 '15 at 17:34

ActiveRecord doesn't care whether it is really a primary key in the database or not, but you need to set the primary_key field on your object using the primary_key= method.

So something like:

class Matview < ActiveRecord::Base
    self.table_name = 'MATERIALIZED_VIEW_NAME_HERE'
    self.primary_key = 'ID_COLUMN_NAME_HERE'
end

How do you run ActiveRecord `find_each` to a materialized view in postgres

1 Answers1