Search Postgresql Archives

Re: About partitioning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2010/1/20 Adrian von Bidder <avbidder@xxxxxxxxxxx>:
> Hi,
>
> On Wednesday 20 January 2010 11.57:37 Vincenzo Romano wrote:
>> 2010/1/20 Adrian von Bidder <avbidder@xxxxxxxxxxx>:
>
>> > [ 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux