INSERT INTO...RETURNING with partitioned table based on trigger function

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.

On 2010-12-16, pgsql(dot)30(dot)miller_2555(at)spamgourmet(dot)com <pgsql(dot)30(dot)miller_2555(at)spamgourmet(dot)com> wrote:
> --0015174c1e4aaf077604977d7e62
> Content-Type: text/plain; charset=ISO-8859-1
>
> 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).

partitioning doesn't work with "INSERT ... RETURNING ..." and trigger
based partitioning.

use a rule instead have the rule FOR EACH ROW DO INSTEAD

SELECT insertfunc(NEW)

and have insertfunc do the insert and return the id column.

for declaring the function the type of NEW is table_name%ROWTYPE

> 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.

another option is the application could call nextval itself or call
lastval after the insert. both of these SQL functions are thread safe.

> 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,

because you can't change history.

--
⚂⚃ 100% natural