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

From: "shakahshakah(at)gmail(dot)com" <shakahshakah(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: 8.1.3, libpq, PQprepare, plpgsql function, and partitioned tables
Date: 2006-04-01 06:08:48
Message-ID: 1143871728.489728.195430@t31g2000cwb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brendan Duddridge 2006-04-01 06:39:46 Re: WAL Archiving frequency
Previous Message Tom Lane 2006-04-01 03:47:53 Re: WAL Archiving frequency