Hi everyone!
I presume that this problem must have been discussed many times, but I have not been able to find a solution in the
archivesof this list or googling. This is the situation:
- I have partitioned 1 table into 10 tables, being the check constraint the language id, and activated
"constraint_exclusion"in postgresql.conf. Child tables do not add new columns to those inherited.
- Parent table is TTopographyLevels. Child tables are TTopographyLevels_en, TTopographyLevels_es, and so on. They have
primarykeys and indexes where required, as per the online documentation.
- I've created 3 triggers (insert, update and delete) on the parent table to distribute the data accordingly.
Questions/doubts:
1. Inside the triggers, table name is variable (in the three of them), depending on the check constraint. I have not
finda way to have just one query, parametrized, so that I don't end up with a VERY long trigger function. I had to use
theEXECUTE command. I understand that the reason why it is not possible to precompile a function without knowing the
tablename at that time. Still, this problem must be very common to everyone using partitioning. Therefore, given how
importantthe process of "redistributing" the row to the appropriate child table is, I presume that I should end up with
avery long function, plenty of if..elseif..else statements, which does not use the EXECUTE command. Correct?
2. Rules or triggers. I've read a lot about it and there are two points I have to consider:
2.1. When we use the COPY command for the initial bulk import, rules will be disabled, therefore the information will
allend up in the parent table. But there is no way not to disable rules when running a COPY command, correct? So it's a
no-gofor me. It's gotta be triggers, it seems.
2.2. On the other hand, we will be doing lots of updates every night (reads during the day, mostly), and evaluating the
ruleonce per transaction instead of executing the trigger once per row seems like very good news.
So I was wonderig: this must be a very common situation for EVERYONE partitioning tables. Which is the most common
approachfor the case? At the moment, after reading all I've read, and given my case, it's very long triggers with no
EXECUTEstatements, but I'd like to hear opinions and get some feedback, if possible.
Thanks.
--
Jaume Sabater
http://linuxsilo.net/
"Ubi sapientas ibi libertas"