Date: Fri, 21 Nov 2008 11:53:41 +0000 (GMT)
From: Jaume Sabater <jsabater@linuxsilo.net>
To: pgsql-admin@postgresql.org
Message-ID: <3481767.6501227268421440.JavaMail.root@zimbra.linuxsilo.net>
In-Reply-To: <29176653.6481227268381440.JavaMail.root@zimbra.linuxsilo.net>
Subject: Variable table name in a trigger function or in a rule
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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 archives of 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 primary keys 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 find a way to have just one query, parametrized, so that I don't end up with a VERY long trigger function. I had to use the EXECUTE command. I understand that the reason why it is not possible to precompile a function without knowing the table name at that time. Still, this problem must be very common to everyone using partitioning. Therefore, given how important the process of "redistributing" the row to the appropriate child table is, I presume that I should end up with a very 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 all end 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-go for 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 rule once 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 approach for the case? At the moment, after reading all I've read, and given my case, it's very long triggers with no EXECUTE statements, but I'd like to hear opinions and get some feedback, if possible.

Thanks.  

--
Jaume Sabater
http://linuxsilo.net/

"Ubi sapientas ibi libertas"