INSERT INTO...RETURNING with partitioned table using trigger/rule

From: John Lumby <johnlumby(at)hotmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: INSERT INTO...RETURNING with partitioned table using trigger/rule
Date: 2012-06-15 16:39:51
Message-ID: COL116-W209D5BA2FE30A8DA0C0419A3FB0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


This topic came up before
    http://archives.postgresql.org/pgsql-general/2010-12/msg00542.php
and there was some discussion on how-to.

Briefly,   the table is partitioned and there is an id column declared as
  id bigint DEFAULT nextval('history_id_seq'::regclass) NOT NULL
and the application issues
  "INSERT into history  (column-list which excludes id)  values (....) RETURNING id"

In our case,  the table is not (yet) partitioned and this RETURNING clause works,
but I need to partition the table.      And one of the requirements is that the
application source code must not need to be changed,  so e.g. not allowed to make
it explicitly fetch nextval('history_id_seq').

I am running postgresql-9.2beta2

I can get the re-direction of the INSERT *without* RETURNING to work
using either trigger or rule,   in which the trigger/rule invokes a procedure,
but whichever way I do it,  I could not get this RETURNING clause to work.
For a trigger,  the INSERT ... RETURNING was accepted but returned no rows,
(as I would expect),   and for the RULE,   the INSERT ... RETURNING was rejected
with :
 
ERROR:  cannot perform INSERT RETURNING on relation "history"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

but this hint was not much help,  since :

CREATE RULE insert_part_history as ON INSERT to history DO INSTEAD SELECT history_insert_partitioned(NEW) returning NEW.id
ERROR:  syntax error at or near "returning"
LINE 1: ...DO INSTEAD SELECT history_insert_partitioned(NEW) returning ...

In looking at what was happening for the RULE scenario,   I found I could get it to work by making some relatively
straightforward changes to the postgresql source code in the areas of rewrite, planner and portal,
basically just to set and propagate a flag to encourage all those components that my rewritten query
really would return something so let it do it.    All the infrastructure for making this work, 
i.e. actually building the result tuple and sending it to the output stream,  was already there.

Is anyone else working on this or are there any plans to support it?   It does seem to be a useful capability.
I can send my patch in the the hackers list if there is any interest.

John

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2012-06-15 16:50:08 Re: parsing SQLERRM ?
Previous Message Thomas Boussekey 2012-06-15 16:33:49 Re: Starting a cluster as a service