On Fri, Jan 3, 2014 at 9:05 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Fri, Jan 3, 2014 at 12:51 AM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> On 01/02/2014 02:24 PM, Rushabh Lathia wrote:
>>> Do you think we should detoast the local variable before
>>> RollbackAndReleaseCurrentSubTransaction ? Or any other options ?
>>
>>
>> Hmm, that would fix this particular test case, but not the other case where
>> you DROP or TRUNCATE the table in the same transaction.
>>
>> The simplest fix would be to just detoast everything on assignment but that
>> was rejected on performance grounds in that previous thread. I don't see any
>> other realistic way to fix this, however, so maybe we should just bite the
>> bullet and do it anyway. For simple variables like, in your test case, it's
>> a good bet to detoast the value immediately; it'll be detoasted as soon as
>> you try to do anything with it anyway. But it's not a good bet for record or
>> row variables, because you often fetch the whole row into a variable but
>> only access a field or two.
>
> Yeah, this is exactly what came to my mind as well the first time I saw this
> problem that for row and record variables it can be penalty which user might
> not expect as he might not be using toasted values.
>
> However is it possible that we do detoasting on assignment when the
> variable of function is declared with some specific construct.
After reading about handling for similar problem in other databases and
thinking more on it, I wonder if we can make a rule such that values
lesser than some threshold (8K or 16K or 32K) can be allowed to
be retrieved in plpgsql variables.
So with this, we can always detoast on assignment if the value is
less than threshold and return error otherwise.
I think this will help in reducing the performance impact and allow
users to retrieve values (which are of less than threshold) in plpgsql
variables without worrying about the behaviour reported in this and
similar thread.
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com