Thread: 8.1.3, libpq, PQprepare, plpgsql function, and partitioned tables

I have some long-running processes which connect to Postgres, use
PQprepare on a function call, and use PQexecPrepared to effectively
insert rows into a set of partitioned tables (by month).

In a nutshell, up until yesterday I had a base widgets table and a
widgets_200603 table, an INSERT rule in front of widgets to insert into
200603 based on a timestamp column, and an INSERT trigger on the base
table to reject inserts into it (nothing special, just following
section 5.9.2 in the 8.1.3 docs). The processes in question were
started in early Mar 2006, everything worked fine.

Yesterday (31 Mar) I created a widgets_200604 table and a rule & used
psql to verify that the "insert function" indeed placed rows in the new
table when appropriate (based on the timestamp value). However, I just
noticed that the inserts from the existing processes were rejected as
if the new rule was not there. Bouncing the processes fixed things, I
assume because the call to the "insert function" was prepared anew.

Am I correct in assuming that when Postgres prepared the SQL to execute
the "insert function" that the existing rules on the base table were
also resolved at that time?  If so, is there any way to avoid that
behavior?

In case it isn't clear, by the way, the "insert function" mentioned
above is roughly:

CREATE OR REPLACE FUNCTION insert_widget(...) RETURNS void AS $$
BEGIN
  INSERT INTO widgets(...) VALUES(...) ;
  RETURN ;
END ;
$$ LANGUAGE plpgsql ;

"shakahshakah@gmail.com" <shakahshakah@gmail.com> writes:
> Am I correct in assuming that when Postgres prepared the SQL to execute
> the "insert function" that the existing rules on the base table were
> also resolved at that time?  If so, is there any way to avoid that
> behavior?

Yes; no.  We are working on infrastructure to automatically redo
prepared plans when relevant catalog entries change, but it's not there
today :-(

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> "shakahshakah@gmail.com" <shakahshakah@gmail.com> writes:
> > Am I correct in assuming that when Postgres prepared the SQL to execute
> > the "insert function" that the existing rules on the base table were
> > also resolved at that time?  If so, is there any way to avoid that
> > behavior?
>
> Yes; no.  We are working on infrastructure to automatically redo
> prepared plans when relevant catalog entries change, but it's not there
> today :-(

Wouldn't it be possible to use 'execute' instead and have the plan
re-generated each time that way?  It'd be less efficient but I think
it'd work as a work-around...

Just some thoughts,

    Thanks,

        Stephen

Stephen Frost wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > "shakahshakah@gmail.com" <shakahshakah@gmail.com> writes:
> > > Am I correct in assuming that when Postgres prepared the SQL to execute
> > > the "insert function" that the existing rules on the base table were
> > > also resolved at that time?  If so, is there any way to avoid that
> > > behavior?
> >
> > Yes; no.  We are working on infrastructure to automatically redo
> > prepared plans when relevant catalog entries change, but it's not there
> > today :-(
>
> Wouldn't it be possible to use 'execute' instead and have the plan
> re-generated each time that way?  It'd be less efficient but I think
> it'd work as a work-around...

Thank you both for the responses. Though I haven't tried it yet I
suspect that using 'execute' would work in my case.

However, my initial expectation was that preparing the stored procedure
call would be limited to consulting the catalog for the stored
procedure name, the args, the arg types, etc. Would the behavior be
different if the stored procedure were more complicated (rather than
the current thin shell around a single INSERT stmt)?