Postgres table partitioning with Rails

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

Comments

[rhymes]

Hi @d0nkeybob , I think we're missing some information here to be able to help effectively :)

• have you tried testing the partitions without Rails? Do the various CRUD operations work?
• how are the partitions defined?
• what's the schema of these two tables? what about the constraints?
• what are you using in Rails to support partitions?

[Rich]

Apologies, I'll update the post with that info.

[rhymes]

Gathering from your updates:

I have through psql statements. updates don't work.

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.

Through a check on an id and by month

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?