shakahshakah@xxxxxxxxx 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
