On Wed, Aug 6, 2008 at 3:29 PM, Florian Pflug <[EMAIL PROTECTED]> wrote:
> Merlin Moncure wrote:
>>
>> you missed the point...if your return type is a composite type that is
>> backed by the table (CREATE TABLE, not CREATE TYPE), then you can
>> 'alter' the type by altering the table. This can be
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
Le 6 août 08 à 20:42, Marko Kreen a écrit :
But you missed my point: if you don't have functions backed by table,
the DROP+CREATE results in inappropriate behaviour that can be
avoided.
Just wanted to say I agree with Marko here: it seems w
On 8/6/08, Florian Pflug <[EMAIL PROTECTED]> wrote:
> Merlin Moncure wrote:
> > you missed the point...if your return type is a composite type that is
> > backed by the table (CREATE TABLE, not CREATE TYPE), then you can
> > 'alter' the type by altering the table. This can be done without full
> >
Merlin Moncure wrote:
you missed the point...if your return type is a composite type that is
backed by the table (CREATE TABLE, not CREATE TYPE), then you can
'alter' the type by altering the table. This can be done without full
drop recreate of the function.
Which - at least IMHO - clearly sh
On 8/6/08, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On Wed, Aug 6, 2008 at 2:28 PM, Marko Kreen <[EMAIL PROTECTED]> wrote:
> > On 8/6/08, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> >> On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen <[EMAIL PROTECTED]> wrote:
> >> > But the main problem is that
Don't you think we try to be careful but still we manage to overlook several
times in year something and cause some stupid downtime.
On Wed, Aug 6, 2008 at 9:13 PM, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen <[EMAIL PROTECTED]> wrote:
> > But the main
On Wed, Aug 6, 2008 at 2:28 PM, Marko Kreen <[EMAIL PROTECTED]> wrote:
> On 8/6/08, Merlin Moncure <[EMAIL PROTECTED]> wrote:
>> On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen <[EMAIL PROTECTED]> wrote:
>> > But the main problem is that if the DROP/CREATE happens, the failure
>> > mode is very nasty
On 8/6/08, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen <[EMAIL PROTECTED]> wrote:
> > But the main problem is that if the DROP/CREATE happens, the failure
> > mode is very nasty - you get permanent error on existing backends.
> > (Main case I'm talking
On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen <[EMAIL PROTECTED]> wrote:
> But the main problem is that if the DROP/CREATE happens, the failure
> mode is very nasty - you get permanent error on existing backends.
> (Main case I'm talking about is functions calling other functions.)
>
> Some sorta rec
On Wed, 2008-08-06 at 15:41 +0200, Pavel Stehule wrote:
> 2008/8/6 Hannu Krosing <[EMAIL PROTECTED]>:
> > On Wed, 2008-08-06 at 12:13 +0200, Pavel Stehule wrote:
> >> 2008/8/6 Hannu Krosing <[EMAIL PROTECTED]>:
> >> > On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
> > ..
> >> >> you cannot
2008/8/6 Hannu Krosing <[EMAIL PROTECTED]>:
> On Wed, 2008-08-06 at 12:13 +0200, Pavel Stehule wrote:
>> 2008/8/6 Hannu Krosing <[EMAIL PROTECTED]>:
>> > On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
> ..
>> >> you cannot change header of function. It's same as change C header of
>> >> fu
On Wed, 2008-08-06 at 12:13 +0200, Pavel Stehule wrote:
> 2008/8/6 Hannu Krosing <[EMAIL PROTECTED]>:
> > On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
..
> >> you cannot change header of function. It's same as change C header of
> >> function without complete recompilation.
> >
> > SQL i
2008/8/6 Hannu Krosing <[EMAIL PROTECTED]>:
> On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
>> 2008/8/5 Asko Oja <[EMAIL PROTECTED]>:
>> > postgres=# create or replace function pavel ( i_param text, status OUT int,
>> > status_text OUT text ) returns record as $$ select 200::int, 'ok'::te
On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
> 2008/8/5 Asko Oja <[EMAIL PROTECTED]>:
> > postgres=# create or replace function pavel ( i_param text, status OUT int,
> > status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$
> > language sql;
> > CREATE FUNCTION
> >
On Tue, 2008-08-05 at 16:16 +0200, Pavel Stehule wrote:
> 2008/8/5 Martin Pihlak <[EMAIL PROTECTED]>:
> >>> DROP FUNCTION
> >>> create function foo() returns integer as $$ begin return 2; end; $$
> >>> language plpgsql;
> >>> CREATE FUNCTION
> >>> execute c1;
> >>> psql:test.sql:11: ERROR: cache
On 8/5/08, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On Tue, Aug 5, 2008 at 10:12 AM, Martin Pihlak <[EMAIL PROTECTED]> wrote:
> >>> DROP FUNCTION
> >>> create function foo() returns integer as $$ begin return 2; end; $$
> language plpgsql;
> >>> CREATE FUNCTION
> >>> execute c1;
> >>> psql
On Tue, Aug 5, 2008 at 10:12 AM, Martin Pihlak <[EMAIL PROTECTED]> wrote:
>>> DROP FUNCTION
>>> create function foo() returns integer as $$ begin return 2; end; $$
>>> language plpgsql;
>>> CREATE FUNCTION
>>> execute c1;
>>> psql:test.sql:11: ERROR: cache lookup failed for function 36555
>>
>> T
On 8/5/08, Pavel Stehule <[EMAIL PROTECTED]> wrote:
> >> ERROR: cannot change return type of existing function
> >> HINT: Use DROP FUNCTION first.
>
> you cannot change header of function. It's same as change C header of
> function without complete recompilation.
Thats why plan invalidation fo
2008/8/5 Asko Oja <[EMAIL PROTECTED]>:
> postgres=# create or replace function pavel ( i_param text, status OUT int,
> status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$
> language sql;
> CREATE FUNCTION
> postgres=# create or replace function pavel ( i_param text, status O
2008/8/5 Martin Pihlak <[EMAIL PROTECTED]>:
>>> DROP FUNCTION
>>> create function foo() returns integer as $$ begin return 2; end; $$
>>> language plpgsql;
>>> CREATE FUNCTION
>>> execute c1;
>>> psql:test.sql:11: ERROR: cache lookup failed for function 36555
>>
>> This is simply a bad, wrong, st
>> DROP FUNCTION
>> create function foo() returns integer as $$ begin return 2; end; $$ language
>> plpgsql;
>> CREATE FUNCTION
>> execute c1;
>> psql:test.sql:11: ERROR: cache lookup failed for function 36555
>
> This is simply a bad, wrong, stupid way to do it. Why do you not use
> CREATE OR
postgres=# create or replace function pavel ( i_param text, status OUT int,
status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$
language sql;
CREATE FUNCTION
postgres=# create or replace function pavel ( i_param text, status OUT int,
status_text OUT text, more_text OUT text
> This is simply a bad, wrong, stupid way to do it. Why do you not use
> CREATE OR REPLACE FUNCTION?
I totally agree we should get this fixed first :)
postgres=# create or replace function pavel ( i_param text, status OUT int,
status_text OUT text ) returns record as $$ select 200::int, 'ok'::tex
Martin Pihlak <[EMAIL PROTECTED]> writes:
> create function foo() returns integer as $$ begin return 1; end; $$ language
> plpgsql;
> CREATE FUNCTION
> prepare c1 as select * from foo();
> PREPARE
> execute c1;
> foo
> -
>1
> (1 row)
> drop function foo();
> DROP FUNCTION
> create functi
Hi
Thanks for pointing to another thing to fix :)
postgres=# create type public.ret_status as ( status integer, status_text
text);
CREATE TYPE
postgres=# create or replace function pavel ( i_param text ) returns
public.ret_status as $$ select 200::int, 'ok'::text; $$ language sql;
CREATE FUNCTION
2008/8/5 Martin Pihlak <[EMAIL PROTECTED]>:
> Pavel Stehule wrote:
>> Hello
>>
>> try version 8.3. There lot of dependencies are solved.
>>
>
> Yes, 8.3 was the version I was testing with. Same results on the HEAD:
>
> $ psql -e -f test.sql
> select version();
>
Hi
Sadly PostgreSQL inability to invalidate plan cache when function is dropped
causes us downtime and costs money.
ERROR: cache lookup failed for function 24865)
This time our developers just rewrote function to use OUT parameters instead
of return type.
Currently i had to forbid dropping functi
Pavel Stehule wrote:
> Hello
>
> try version 8.3. There lot of dependencies are solved.
>
Yes, 8.3 was the version I was testing with. Same results on the HEAD:
$ psql -e -f test.sql
select version();
version
Hello
try version 8.3. There lot of dependencies are solved.
Regards
Pavel Stehule
2008/8/5 Martin Pihlak <[EMAIL PROTECTED]>:
> Howdy,
>
> What is the status of plan invalidation vs stored procedures? From
> the initial design discussion I understand that function change handling
> was postpone
Howdy,
What is the status of plan invalidation vs stored procedures? From
the initial design discussion I understand that function change handling
was postponed to "some time in the future". Is anybody already working
on that or maybe some ideas of how to implement this?
The business case for the
Gregory Stark wrote:
"Tom Lane" <[EMAIL PROTECTED]> writes:
There doesn't seem to be any very nice way to fix this. There is
not any existing support mechanism (comparable to query_tree_walker)
for scanning whole plan trees, which means that searching a cached plan
for regclass Consts is going
I wrote:
> Well, we *have* the sequence's Oid in the regclass constant, the problem
> is the difficulty of digging through the plan tree to find it. I did
> consider having the planner extract it and save it aside somewhere, but
> there doesn't seem to be any very convenient place to do that, shor
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> Gregory Stark wrote:
>> Given that sequences are in fact relations is there some way to work around
>> the issue at least in this case by stuffing the sequence's relid someplace
>> which the plan invalldation code can check for it?
Well, we *have* t
Gregory Stark wrote:
"Tom Lane" <[EMAIL PROTECTED]> writes:
There doesn't seem to be any very nice way to fix this. There is
not any existing support mechanism (comparable to query_tree_walker)
for scanning whole plan trees, which means that searching a cached plan
for regclass Consts is going
"Tom Lane" <[EMAIL PROTECTED]> writes:
> There doesn't seem to be any very nice way to fix this. There is
> not any existing support mechanism (comparable to query_tree_walker)
> for scanning whole plan trees, which means that searching a cached plan
> for regclass Consts is going to involve a ch
Tom Lane wrote:
> ... We might want to do that someday --- in particular,
> if we ever try to extend the plan inval mechanism to react to
> redefinitions of non-table objects, we'd likely need some such thing
> anyway. I'm disinclined to try to do it for 8.3 though. The use-case
> for temp sequen
In bug #3662
http://archives.postgresql.org/pgsql-bugs/2007-10/msg00047.php
we see that it doesn't work to do nextval('seq') on a temp sequence
in a plpgsql function except via EXECUTE, because the sequence OID gets
embedded into the cached plan, same as any other temp table. This is to
be expect
"Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> On 4/3/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> If the invalidation were something that *had* to be accounted for,
>> such as a dropped index, then there should be adequate locking for it;
>> plancache is not introducing any new bug that wasn't there
On 4/3/07, Tom Lane <[EMAIL PROTECTED]> wrote:
I'm not particularly worried about missing a potential improvement
in the plan during the first command after a change is committed.
Me too. Just noticed it, so brought it up.
If the invalidation were something that *had* to be accounted for
"Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> I traced it a bit and it seems that the invalidation messages
> are not accepted in session 2 because the locks are already held
> on the relation.
Right, because of this coding in LockRelationOid():
/*
* Now that we have the lock, check for
I noticed that the plan invalidation is not immediately effective.
Not sure whether it's worth fixing or has any other side-effects,
but thought would just post it.
I was testing the following scenario:
session1session2
CREATE TABLE test
(int a, int b)
I am from pgsql-jdbc, so I may not be "in the thread", so please ignore
places where my misunderstanding goes out.
The main two questions, IMHO, is:
1) What is the key to plan cache. Current option is some statement key
(id). Another option would be statement text (you still need to store it
"Simon Riggs" <[EMAIL PROTECTED]> writes:
> On Tue, 2007-03-06 at 12:22 -0500, Tom Lane wrote:
>> A. Just accept the extra overhead, thereby preserving the current
>> behavior of unnamed statements, and gaining the benefit that plan
>> invalidation will work correctly in the few cases where an unna
On Tue, 2007-03-06 at 12:22 -0500, Tom Lane wrote:
> A. Just accept the extra overhead, thereby preserving the current
> behavior of unnamed statements, and gaining the benefit that plan
> invalidation will work correctly in the few cases where an unnamed
> statement's plan lasts long enough to ne
Tom Lane wrote:
Gregory Stark <[EMAIL PROTECTED]> writes:
Can we forcibly discard it if *any* messages are received that might
invalidate a plan? So basically it would work fine unless anyone in the system
does any DDL at all? I guess that has the downside of introducing random
unpredictable
I think C is how the JDBC driver is written. We name the statements
if they have been used more than prepareThreshold times.
So we have a mechanism by which to allow statements to be cached, or
not.
Dave
On 6-Mar-07, at 1:14 PM, Tom Lane wrote:
Gregory Stark <[EMAIL PROTECTED]> writes:
Gregory Stark <[EMAIL PROTECTED]> writes:
> Can we forcibly discard it if *any* messages are received that might
> invalidate a plan? So basically it would work fine unless anyone in the system
> does any DDL at all? I guess that has the downside of introducing random
> unpredictable failures.
Ugh
"Tom Lane" <[EMAIL PROTECTED]> writes:
> B. Don't store the unnamed statement in the plan cache. To make sure
> it's not used anymore when the plan might be stale, forcibly discard
> the unnamed statement after execution. This would get rid of a lot
> of overhead but would mean a significant ch
[ cc'd to pgsql-jdbc which seems the group most likely to be affected
by any protocol change ]
So I've been working on a plan cache module per my earlier proposal,
and I've run up against a problem with getting exec_parse_message
to use it. The problem is that the current rather hackish handlin
On Feb 18, 9:35 am, [EMAIL PROTECTED] (Tom Lane) wrote:
> Russell Smith <[EMAIL PROTECTED]> writes:
>
> > If you replan and immutable function, aren't you possibly messing up a
> > functional index that is using the old function. Hey, if you change an
> > immutable function that has an index, you
Simon Riggs wrote:
> On Sat, 2007-02-17 at 12:48 -0500, Tom Lane wrote:
>
> > Relcache inval casts a fairly wide net; for example, adding or dropping an
> > index will invalidate all plans using the index's table whether or not
> > they used that particular index, and I believe that VACUUM will al
On Sat, 2007-02-17 at 12:48 -0500, Tom Lane wrote:
> Relcache inval casts a fairly wide net; for example, adding or dropping an
> index will invalidate all plans using the index's table whether or not
> they used that particular index, and I believe that VACUUM will also
> result in a relcache inv
Gregory Stark wrote:
[snip]
Hm. The set of output columns could change? How?
If you prepare "select *" and add a column, you're saying the query should
start failing? That seems strange given the behaviour of views, which is that
once parsed the list of columns is written in stone. It seems
On 2/19/07, Tom Lane <[EMAIL PROTECTED]> wrote:
Gregory Stark <[EMAIL PROTECTED]> writes:
> If you prepare "select *" and add a column, you're saying the query should
> start failing?
Either fail or change output; which you like better? The whole point of
this exercise is to support plpgsql fun
Gregory Stark <[EMAIL PROTECTED]> writes:
> If you prepare "select *" and add a column, you're saying the query should
> start failing?
Either fail or change output; which you like better? The whole point of
this exercise is to support plpgsql functions that do something like
create temp
"Tom Lane" <[EMAIL PROTECTED]> writes:
> Russell Smith <[EMAIL PROTECTED]> writes:
>> Tom Lane wrote:
>>> 2. Given a handle for a previously stored query, check to see if the plan
>>> is still up to date; if not, regenerate it from the raw parse tree (note
>>> this could result in failure, eg if a
Russell Smith <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> 2. Given a handle for a previously stored query, check to see if the plan
>> is still up to date; if not, regenerate it from the raw parse tree (note
>> this could result in failure, eg if a column used by the query has been
>> dropped)
Tom Lane wrote:
I'm starting to think about the long-wanted plan invalidation mechanism.
Here's a sketch --- anyone see any problems?
* Create a new module, say src/backend/utils/cache/plancache.c, that we
will put in charge of all long-lived plans --- or at least those cached by
PREPARE, plpgsq
Tom Lane wrote:
place. But the question to answer is why the re-plan won't yield
just the same plan as before.
oh and when the estimated cost repeatedly do not match the actual cost,
we of course want to generate an email with all relevant information
that is send to this list ;)
regards,
Tom Lane wrote:
Lukas Kahwe Smith <[EMAIL PROTECTED]> writes:
I remember that there was discussion about invalidating plans who's
estimated cost turn out to be severely off when executed.
That's something we might think about after the infrastructure is in
place. But the question to answer is
Lukas Kahwe Smith <[EMAIL PROTECTED]> writes:
> I remember that there was discussion about invalidating plans who's
> estimated cost turn out to be severely off when executed.
That's something we might think about after the infrastructure is in
place. But the question to answer is why the re-pla
Tom Lane wrote:
Relcache inval casts a fairly wide net; for example, adding or dropping an
index will invalidate all plans using the index's table whether or not
they used that particular index, and I believe that VACUUM will also
result in a relcache inval due to updating the table's pg_class r
I'm starting to think about the long-wanted plan invalidation mechanism.
Here's a sketch --- anyone see any problems?
* Create a new module, say src/backend/utils/cache/plancache.c, that we
will put in charge of all long-lived plans --- or at least those cached by
PREPARE, plpgsql, and RI triggers
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> Is anyone working on plan invalidation? I might take a stab at it during
> the 8.3 cycle. I haven't given it any thought yet, I thought I'd check
> first to avoid duplicate work.
I'd been planning to tackle it too, but would be happy to let someon
Hi,
Is anyone working on plan invalidation? I might take a stab at it during
the 8.3 cycle. I haven't given it any thought yet, I thought I'd check
first to avoid duplicate work.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
65 matches
Mail list logo