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?
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