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
Top comments (3)
Hi @d0nkeybob , I think we're missing some information here to be able to help effectively :)
Apologies, I'll update the post with that info.
Gathering from your updates:
So the issue is not Rails though. If the CRUD operations don't all work manually, there's probably something wrong in how the partition is set or configured.
But what's the schema of the tables? Can you share it? Maybe through sqlfiddle.com/ or db-fiddle.com/ so we can take a look?