Re: trouble inserting into new partitions of partitioned

shakahshakah@gmail.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