Re: About partitioning - Mailing list pgsql-general

2010/1/20 Vincenzo Romano <vincenzo.romano@notorand.it>:
> 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.

It looks like Adrian is right and my fear is unfounded.
I have created both implementations, one with test-create-insert and
one with insert-trap-create-insert.
The first implementation shows an average execution time longer than
the second with the worse case of the second one
very close to the worse case of the first one.
This means that that only the EXCEPTION...WHEN sub-block is slow, not
also the BEGIN...EXCEPTION.
I would advise to make this thing clearer in the documentation (tip at
chapter "38.6.5. Trapping Errors").

Thanks again to everyone.

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS