[GENERAL] INSERT INTO...RETURNING with partitioned table based on trigger function

[pgsql . 30 . miller_2555]

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.

Re: [GENERAL] INSERT INTO...RETURNING with partitioned table based on trigger function

[pgsql . 30 . miller_2555]

> On 2010-12-16, 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
>

Thanks. I had attempted to use rules prior to the trigger
implementation, but opted for the trigger-based implementation due to
easier maintenance (specific to this particular database, at least).

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

This is a great idea, and the one I'll probably end up implementing.
Many thanks for the suggestion!

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

I agree that handling the return value of a trigger function when
TG_OP=AFTER in such a way that alters the database itself does not
make sense on its face, though I think that allowing trigger-defined
return values to pass back to the trigger caller (and so on trough the
call stack back to the external application) is a reasonable
behaviour.