Skip site navigation (1) Skip section navigation (2)

Re: Avoiding bad prepared-statement plans.

From: Kenneth Marshall <ktm(at)rice(dot)edu>
To: David Christensen <david(at)endpoint(dot)com>
Cc: Pierre C <lists(at)peufeu(dot)com>, Dimitri Fontaine <dfontaine(at)hi-media(dot)com>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-19 13:57:51
Message-ID: 20100219135751.GD25733@it.is.rice.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, Feb 18, 2010 at 08:31:05PM -0600, David Christensen wrote:
>
> On Feb 18, 2010, at 2:19 PM, Pierre C wrote:
>
>>
>>> What about catching the error in the application and INSERT'ing into the
>>> current preprepare.relation table? The aim would be to do that in dev or
>>> in pre-prod environments, then copy the table content in production.
>>
>> Yep, but it's a bit awkward and time-consuming, and not quite suited to 
>> ORM-generated requests since you got to generate all the plan names, when 
>> the SQL query itself would be the most convenient "unique identifier"...
>>
>> A cool hack would be something like that :
>>
>> pg_execute( "SELECT ...", arguments... )
>>
>> By inserting a hook which calls a user-specified function on non-existing 
>> plan instead of raising an error, this could work.
>> However, this wouldn't work as-is since the plan name must be <= 
>> NAMEDATALEN, but you get the idea ;)
>
> How about the SHA1 hash of the query?  Hey, it works for git... :-)
>
> Regards,
>
> David
> --
> David Christensen
> End Point Corporation
> david(at)endpoint(dot)com
>
>
Hi David,

Not to beat out own drum, but we already include a hashing function
that can be used for this purpose and is much faster than SHA-1. We
would want to use all 64-bits for this use instead of just the 32-bits
we currently use for the internal DB hashing. Here is an article
comparing the Jenkins' Hash (the one we use) and SHA-1:

http://home.comcast.net/~bretm/hash/

Regards,
Ken

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-02-19 14:19:48
Subject: Re: Merge join and index scan strangeness
Previous:From: Yeb HavingaDate: 2010-02-19 13:51:39
Subject: Re: Merge join and index scan strangeness

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group