Hi -
Issue:
How to return a sequence value generated upon INSERT of records into a partitioned table using trigger functions (without having to insert into the child table directly).
Current implementation:
The master table of the partitioned table uses a trigger function to alter an incoming record on INSERT by calling `nextval()` on a sequence. The trigger function, in turn, calls a helper function that identifies the child table in which to insert the incoming records (along with the newly minted sequence value). Since the trigger function attached to the master table must return NULL when
TG_OP=BEFORE (to avoid duplicating the insertion of records into the master table
and the child table) and the return value is ignored when TG_OP=AFTER, the sequence value generated by the trigger function on the master table is lost (from the perspective of the external application performing the INSERT).
Rationale for implementation:
The partitioned table into which the external application inserts the first set of records contains summary information (i.e. aggregated data used in a report summary), which uses the sequence value as something akin to a report ID. After the external application inserts the summary data, it needs to insert detailed information underlying the summary report into a separate table (to permit drill-down reporting), which uses the sequence value generated as a foreign key against the partitioned table containing the summary information. In other words, the sequence value generated is an identifier that links the detailed report information to the report summary.
Other notations/ questions:
1) it is highly preferable that the application inserting into the database not be aware of the child tables (table partitioning is somewhat fluid as new hardware becomes available, and patching the external application is best avoided).
2) multiple instances of the application may be running, so generation of the sequence number in the application is not feasible (moreover, the application is multi-threaded and additional summary data insertions may occur between the insertion of summary data and detailed data in the two partitioned tables.
3) is there a technical reason as to why the return values of trigger functions are ignored when TG_OP=AFTER for INSERT/ UPDATE operations, or is this a valid feature request for an upstream versions? -- please advise if this is already addressed in a post-v8.3 version of Postgres.
Environment:
Postgres v8.3
Linux 2.6.27.41-170.2.117.fc10.x86_64
Thanks.
