Postgres table partitioning with Rails

Rich — Fri, 03 Jan 2020 19:50:21 +0000

Hi all, Our system currently uses Postgresql 9.6 with Rails but has been having table performance problems. We decided to add table partitioning and I created the triggers to do that. My problem is that CREATE works perfectly, but UPDATE gives me constraint errors. Has anyone had good luck in implementing this? I would also note that I added a DELETE trigger for removing the row from parent after insert, but that gives me problems in tests because it can't find the row. Any help would be greatly appreciated. Thanks

example of error

PG::ForeignKeyViolation: ERROR:  insert or update on table "table2" violates foreign key constraint "fk_rails_647be12f23"
         DETAIL:  Key (id)=(7867) is not present in table "table1".

I'm assuming I'm missing something that's right in front of my face but I can't seem to find the right posts/articles to jog that.

To answer some questions on the problem:

have you tried testing the partitions without Rails?

I have through psql statements. updates don't work. I'm assuming that I would need a seperate process for moving the record, but I haven't found clear solutions to help.

Do the various CRUD operations work?

Inserts do

how are the partitions defined?

Through a check on an id and by month

what are you using in Rails to support partitions?

Just function triggers in Postgres. no gem usage

DEV Community: Rich

Postgres table partitioning with Rails