2010/1/20 Adrian von Bidder <avbidder@fortytwo.ch>:
> Hi,
>
> On Wednesday 20 January 2010 11.57:37 Vincenzo Romano wrote:
>> 2010/1/20 Adrian von Bidder <avbidder@fortytwo.ch>:
>
>> > [ creating db partitions on demand ]
>> >
>> > On Wednesday 20 January 2010 11.20:21 Vincenzo Romano wrote:
>> >> In case 1 I need to inspect the catalog with at least a select, while
>> >> in case 2 I need to trap errors.
>> >> In my (little) experience trapping errors is slow, so I would go for
>> >> option 1.
>> >
>> > Trapping/handling the error might be slow, but remember that creating a
>> > new partition (presumably) doesn't happen often (and creating the
>> > partition is slow anyway.)
>
>> Hmmm ... also trapping would happen for every single line being inserted
>
> Why?
>
> By "trapping" I mean: reacting to the error if the INSERT statement fails.
> If the INSERT does not fail, there is no error, so there is no error
> condition to handle.
>
> Compare:
>
> * query server to see if partition exists
> (!!! slow: this uses the database server)
> * if partition does not exist (this is almost never the case), create it
> * insert row
>
> Against:
>
> * try inserting (same speed as the final step above)
> * if (error)
> (this is fast, since it only uses the return value from the insert. No
> additional database action)
> -> then create partition (this, again, is slow but almost never happens)
> -> and then re-try the insert.
"trapping" should have the same meaning as in "38.6.5. Trapping Errors",
that is the BEGIN...EXCEPTION...END.
In my case:
BEGIN
INSERT INTO a_child_table SELECT NEW.*;
EXCEPTION
WHEN the_table_doesn_t_exist THEN
CREATE TABLE a_child_table ...
END;
"Tip: A block containing an EXCEPTION clause is significantly more
expensive to enter and exit than
a block without one. Therefore, don’t use EXCEPTION without need."
So my fear is that having such a trapping block defined at runtime for
every INSERT
would yield to a slow implementation.
--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS
