Partitoning not working with RETURNING and INSERT/UPDATE

From: Frank Jördens <frank(at)woome(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Partitoning not working with RETURNING and INSERT/UPDATE
Date: 2009-02-11 03:55:26
Message-ID: 7d10d2df0902101955r341b0c25vf2f40055239aca36@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I created a partitioned table and am now finding that it won't (at
least not trivially) support the RETURNING syntax with insert or
update:

If I take the trigger approach (as in the online manual), allowing for
RETURNING to work means that I have to let the BEFORE INSERT trigger
return the row rather than NULL, which means it'll get inserted into
the base table as well as the partition, and I'll end up with 2 rows
per insert.

If I try to define rules (very much like the example in the docs), I get:

-- snip --
woome=# INSERT INTO "userstats_profileview" ("viewer_id", "viewed_id")
VALUES (94039, 60701) RETURNING id;
ERROR: cannot perform INSERT RETURNING on relation "userstats_profileview"
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a
RETURNING clause.
-- snap --

Is there any way to make this work?

Regards,

Frank

Browse pgsql-sql by date

  From Date Subject
Next Message Andreas 2009-02-12 23:20:54 Funtion to clean up strings?
Previous Message Bart van Houdt 2009-02-09 12:02:30 RE: [SQL] Grass Root Protectionism