From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "shakahshakah(at)gmail(dot)com" <shakahshakah(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: trouble inserting into new partitions of partitioned |
Date: | 2006-12-04 10:39:55 |
Message-ID: | 4573FAFB.70504@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
shakahshakah(at)gmail(dot)com wrote:
> =========================================
> 6. thinking it might be the prepared stmt causing the problem I
> tried a direct call to the stored proc, to no avail:
>
> pg> SELECT silly_insert('cccc','va',999) ;
> ERROR: inserts only allowed into silly partition tables (state was va)
>
> =========================================
> 7. a direct insert does work, however:
> pg> INSERT INTO silly(bcid,state,some_value) VALUES('asdf','ny',8888) ;
> INSERT 0 0
>
> 8. if the process from (2) disconnects and reconnects everything
> works as expected (i.e. it can insert Virgina rows).
What you're missing is the fact that queries within a function have
their query-plan cached. That means silly_insert()'s "INSERT INTO"
statement gets re-written on the first call and the plan saved.
Workarounds:
1. Reconnect (as you discovered) thus re-planning the function's query
2. Re-create the function (CREATE OR REPLACE FUNCTION ...)
3. Use the EXECUTE statement to dynamically construct your query
4. Use a different language that doesn't cache query-plans
We probably need a "de-cache function" command, but no-one's implemented
such a thing yet.
HTH
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2006-12-04 10:41:16 | Re: Locking for function creation |
Previous Message | Alban Hertroys | 2006-12-04 09:25:47 | Re: sudden drop in delete performance |