Data is inserted in both child tables and parent table

[LordNoteworthy]

Hello,

Thank you so much for this awesome tool.

I use postgresql 9.4 and SQLAlchemy 1.0.9.

I added this as in the doc: @architect.install('partition', type='range', subtype='date', constraint='month', column='time_received', db=config.database.engine)

Then I run: architect partition -m core.db.models, it returns success.

Then, when I start inserting rows from sqlalchemy, architect it creates the right tables but I find my rows in the parent and also the child table, they should be available only in my child tables.

Do you know why ?

Thanks !

[LordNoteworthy]

It seems that my table_delete_master () does not delete the row from the parent table,

Even If I try to execute the DELETE FROM ONLY .., I got 0 rows affected.

First thing that pops to my mind that he couln't not delete the row because maybe of a cascading issue.

Best,

[maxtepkeev]

Hi,

If I understood you correctly, all the triggers and trigger function were generated, i.e. you can see them in the database, but for some reason the delete_master() doesn't work, right ?

I'll need to see your model to try to reproduce this issue here.

[LordNoteworthy]

Hi,

btw, I just figured out the problem, there is nothing wrong with architect.

I was using: select * from my_table which will give my of course all the rows including my child ones,

I should have used select * from ONLY my_table,

Thanks for your support.

I have last question: Why you have an after insert trigger, why not only a before insert which will insert the row and return NULL ? Is it for safety ?

[maxtepkeev]

I was using: select * from my_table which will give my of course all the rows including my child ones,

I should have used select * from ONLY my_table,

This is definitely not a solution, the whole point of table partitioning is that it should be absolutely transparent to your application and queries. As you wrote earlier, it looks like your delete_master() trigger doesn't work for some reason and I need to see your model to figure out what's so special about it.

I have last question: Why you have an after insert trigger, why not only a before insert which will insert the row and return NULL ? Is it for safety ?

This is because some ORMs (e.g. Django and maybe others, I don't remember right now for sure) are waiting for the result from the database and returning NULL will break them. I have plans to make trigger generations more ORM specific, so for example if SQLAlchemy works correctly with return NULL there is no need to generate additional triggers for it.

[LordNoteworthy]

Hello,

Here are my models: https://gist.github.com/LordNoteworthy/57278d0a9da068795ce2

My parent table is 'samples'.

I was using PgAdmin to query: select * from samples which will obviously return me all rows.

When I setted up architect and start inserting some rows. I fired up PgAdmin only to see really if the child table get created when it needs to, then I checked in my child tables is rows are inserted fine, all was good.

My mistake was that I taught if I do select * from samples, I shouln't have seen any rows, but this not not true, because in this case PG just grab all the rows from its childs. There is no row in my parent table.

I know that in my web app with SQLALchemy, it's completely transparent to the user that if he queries some data which belongs to a date range, PG will just grab them from that specefic tables which searching in the whole tables.

[maxtepkeev]

Hi,

Ah, now I see, yes of course doing select * from samples will return you rows from all your child tables, and yes, when you append the where condition then Postgres will give you only rows from the needed partitions. So now, from what you've described, it looks like that everything is working exactly like it should ;-)

If everything is ok now and you don't have any more questions feel free to close this issue.

[LordNoteworthy]

Thank you so much, Great Work, I appreciate it !