Thread: INSERT INTO...RETURNING with partitioned table based on trigger function
INSERT INTO...RETURNING with partitioned table based on trigger function
From
pgsql.30.miller_2555@spamgourmet.com
Date:
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.
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.30.miller_2555@spamgourmet.com <pgsql.30.miller_2555@spamgourmet.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
Re: INSERT INTO...RETURNING with partitioned table based on trigger function
From
pgsql.30.miller_2555@spamgourmet.com
Date:
> 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
>
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.