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

Re: DISCARD ALL ; stored procedures

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: DISCARD ALL ; stored procedures
Date: 2011-01-06 22:22:10
Message-ID: 20110106222210.GN4933@tamriel.snowman.net (view raw or flat)
Thread:
Lists: pgsql-hackers
* Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
> this has been discussed a couple of times -- a plausible alternative
> might be to adjust the plan caching mechanism to organize the plan
> cache around search_path.  that way you get a separate plan per
> search_path instance.

That would certainly be fine for me.  To be honest, I feel like I've
even suggested that in the past, somewhere.

> discard has zero backwards compatibility issues but has one big
> problem -- if you are using combination of connection pooling, lots of
> plpgsql and search_path manipulation, you take a big performance hit.
> in other words, even if you can discard everything., do you really
> want to?

I don't see how this can be an unnecessary performance hit.  You might
argue that I should redesign things to not work this way, but that's a
whole different discussion.  At the moment the options are:

- switch to using execute
- force a full database reconnect

To get the 'correct' behavior.

If it's "performance" vs. "correctness", you can guess what I'm going to
vote for, however, in this case, I can't see how either of the other
options would perform better than a discard-like approach.  If people
are already using 'discard all;' then they're already throwing away
their plans for prepared queries, it strikes me as unlikely that they'd
have an issue with also getting rid of stored procedure plans.  If they
do, they could certainly use the individual 'discard' statements
instead (presuming we implement this with a new discard argument).

	Thanks

		Stephen

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-01-06 22:52:10
Subject: Re: DISCARD ALL ; stored procedures
Previous:From: Merlin MoncureDate: 2011-01-06 22:09:12
Subject: Re: DISCARD ALL ; stored procedures

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