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
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
"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
"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
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
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
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
57 matches
Mail list logo
