Thread: INSERT RETURNING and partitioning

INSERT RETURNING and partitioning

From
"pdovera@tiscali.it"
Date:
Hi all,
I've noticed that an insert command with returning clause returns an
empty result set if done on a master table. Instead the same insert
with returning on partitioned tables works correctly.

Do you know if it's a normal action? I'm doing something wrong?

The partitioning works correctly with all kind of SQL commands:
select, delete, update and "normal" insert.

I've read the manual and it reports this:
"The optional RETURNING clause causes INSERT to compute and return
value(s) based on each row actually inserted. This is primarily useful
for obtaining values that were supplied by defaults, such as a serial
sequence number. However, any expression using the table's columns is
allowed. The syntax of the RETURNING list is identical to that of the
output list of SELECT."

but nothing about partitioning ...

Regards,
Paolo

PS: I'm using PostgreSQL 8.3.4 on x86 Linux box


SCARICA TISCALI WIPHONE: parla e invia SMS gratis dal tuo cellulare.
http://wiphone.tiscali.it


Re: INSERT RETURNING and partitioning

From
Thom Brown
Date:
On 20 July 2010 14:42, pdovera@tiscali.it <pdovera@tiscali.it> wrote:
> Hi all,
> I've noticed that an insert command with returning clause returns an
> empty result set if done on a master table. Instead the same insert
> with returning on partitioned tables works correctly.
>
> Do you know if it's a normal action? I'm doing something wrong?
>
> The partitioning works correctly with all kind of SQL commands:
> select, delete, update and "normal" insert.
>
> I've read the manual and it reports this:
> "The optional RETURNING clause causes INSERT to compute and return
> value(s) based on each row actually inserted. This is primarily useful
> for obtaining values that were supplied by defaults, such as a serial
> sequence number. However, any expression using the table's columns is
> allowed. The syntax of the RETURNING list is identical to that of the
> output list of SELECT."
>
> but nothing about partitioning ...
>
> Regards,
> Paolo
>
> PS: I'm using PostgreSQL 8.3.4 on x86 Linux box
>
>

Yes, that should work even on partitioned tables.  Could you provide
some sample SQL you're using?

Thom

Re: INSERT RETURNING and partitioning

From
"pdovera@tiscali.it"
Date:
Hi,
I'm testing the system with these two insert commands:

1) this command returns an empty result set:
insert into support.master (a) VALUES (2) RETURNING seq;

2) this command returns correctly the seq (serial) value into result
set:
insert into support.partitionB (a) VALUES (2) RETURNING seq;

I'm doing something wrong?

I'm using the following DDL to create the partitioning tables, trigger
and so on ...

create table support.master(
seq serial,
a INTEGER PRIMARY KEY
);

create table support.partitionA(
CHECK (a = 1)
) INHERITS (support.master);

create table support.partitionB(
CHECK (a = 2)
) INHERITS (support.master);

create table support.partitionC(
CHECK (a = 3)
) INHERITS (support.master);

create table support.partitionD(
CHECK (a = 4)
) INHERITS (support.master);

CREATE OR REPLACE FUNCTION support.master_insert()
  RETURNS trigger AS
$BODY$
BEGIN
     IF ( NEW.a = 1) THEN INSERT INTO support.partitionA VALUES (NEW.
*);
ELSIF ( NEW.a = 2) THEN INSERT INTO support.partitionB VALUES (NEW.*);
ELSIF ( NEW.a = 3) THEN INSERT INTO support.partitionC VALUES (NEW.*);
ELSIF ( NEW.a = 4) THEN INSERT INTO support.partitionD VALUES (NEW.*);
ELSE RAISE EXCEPTION 'A (%)is out of range ',NEW.a;
END IF;
RETURN NULL;
END;
$BODY$
  LANGUAGE 'plpgsql';


CREATE TRIGGER master_insert_trigger
  BEFORE INSERT
  ON support.master
  FOR EACH ROW
  EXECUTE PROCEDURE support.master_insert();



Regards,
Paolo


SCARICA TISCALI WIPHONE: parla e invia SMS gratis dal tuo cellulare.
http://wiphone.tiscali.it


Re: INSERT RETURNING and partitioning

From
Jan Otto
Date:
hi,

On Jul 21, 2010, at 10:02, "pdovera@tiscali.it" <pdovera@tiscali.it>
wrote:

> Hi,
> I'm testing the system with these two insert commands:
>
> 1) this command returns an empty result set:
> insert into support.master (a) VALUES (2) RETURNING seq;
>
> 2) this command returns correctly the seq (serial) value into result
> set:
> insert into support.partitionB (a) VALUES (2) RETURNING seq;
>
> I'm doing something wrong?
>
> I'm using the following DDL to create the partitioning tables, trigger
> and so on ...
>
> create table support.master(
> seq serial,
> a INTEGER PRIMARY KEY
> );
>
> create table support.partitionA(
> CHECK (a = 1)
> ) INHERITS (support.master);
>
> create table support.partitionB(
> CHECK (a = 2)
> ) INHERITS (support.master);
>
> create table support.partitionC(
> CHECK (a = 3)
> ) INHERITS (support.master);
>
> create table support.partitionD(
> CHECK (a = 4)
> ) INHERITS (support.master);
>
> CREATE OR REPLACE FUNCTION support.master_insert()
>  RETURNS trigger AS
> $BODY$
> BEGIN
>     IF ( NEW.a = 1) THEN INSERT INTO support.partitionA VALUES (NEW.
> *);
> ELSIF ( NEW.a = 2) THEN INSERT INTO support.partitionB VALUES (NEW.*);
> ELSIF ( NEW.a = 3) THEN INSERT INTO support.partitionC VALUES (NEW.*);
> ELSIF ( NEW.a = 4) THEN INSERT INTO support.partitionD VALUES (NEW.*);
> ELSE RAISE EXCEPTION 'A (%)is out of range ',NEW.a;
> END IF;
> RETURN NULL;

a trigger for insert should return NEW, no? ;-)

change that and it will work.

regards, jan

Re: INSERT RETURNING and partitioning

From
Thom Brown
Date:
On 21 July 2010 09:17, Jan Otto <asche@me.com> wrote:
> hi,
>
> On Jul 21, 2010, at 10:02, "pdovera@tiscali.it" <pdovera@tiscali.it> wrote:
>
>> Hi,
>> I'm testing the system with these two insert commands:
>>
>> 1) this command returns an empty result set:
>> insert into support.master (a) VALUES (2) RETURNING seq;
>>
>> 2) this command returns correctly the seq (serial) value into result
>> set:
>> insert into support.partitionB (a) VALUES (2) RETURNING seq;
>>
>> I'm doing something wrong?
>>
>> I'm using the following DDL to create the partitioning tables, trigger
>> and so on ...
>>
>> create table support.master(
>> seq serial,
>> a INTEGER PRIMARY KEY
>> );
>>
>> create table support.partitionA(
>> CHECK (a = 1)
>> ) INHERITS (support.master);
>>
>> create table support.partitionB(
>> CHECK (a = 2)
>> ) INHERITS (support.master);
>>
>> create table support.partitionC(
>> CHECK (a = 3)
>> ) INHERITS (support.master);
>>
>> create table support.partitionD(
>> CHECK (a = 4)
>> ) INHERITS (support.master);
>>
>> CREATE OR REPLACE FUNCTION support.master_insert()
>>  RETURNS trigger AS
>> $BODY$
>> BEGIN
>>    IF ( NEW.a = 1) THEN INSERT INTO support.partitionA VALUES (NEW.
>> *);
>> ELSIF ( NEW.a = 2) THEN INSERT INTO support.partitionB VALUES (NEW.*);
>> ELSIF ( NEW.a = 3) THEN INSERT INTO support.partitionC VALUES (NEW.*);
>> ELSIF ( NEW.a = 4) THEN INSERT INTO support.partitionD VALUES (NEW.*);
>> ELSE RAISE EXCEPTION 'A (%)is out of range ',NEW.a;
>> END IF;
>> RETURN NULL;
>
> a trigger for insert should return NEW, no? ;-)
>
> change that and it will work.
>
> regards, jan
>

Yes, Jan's right.  You're effectively overriding the return values with NULL.

Although I think I know why you're doing it, because you want to
redirect the value to the child table so that it doesn't get inserted
into the parent table as that would cause duplicate values being
displayed in the parent table.

Unfortunately, you can't use RULEs as an alternative as they won't
allow returning values if they have conditions on them.

Thom

Re: INSERT RETURNING and partitioning

From
"pdovera@tiscali.it"
Date:
Hi Jan,

----Messaggio originale----
Da: asche@me.com
Data: 21/07/2010 10.17
A: "pdovera@tiscali.it"<pdovera@tiscali.it>
Cc: "pgsql-general@postgresql.org"<pgsql-general@postgresql.org>
Ogg: Re: [GENERAL] INSERT RETURNING and partitioning

hi,

On Jul 21, 2010, at 10:02, "pdovera@tiscali.it" <pdovera@tiscali.it>
wrote:

> Hi,
> I'm testing the system with these two insert commands:
>
> 1) this command returns an empty result set:
> insert into support.master (a) VALUES (2) RETURNING seq;
>
> 2) this command returns correctly the seq (serial) value into result
> set:
> insert into support.partitionB (a) VALUES (2) RETURNING seq;
>
> I'm doing something wrong?
>
> I'm using the following DDL to create the partitioning tables,
trigger
> and so on ...
>
> create table support.master(
> seq serial,
> a INTEGER PRIMARY KEY
> );
>
> create table support.partitionA(
> CHECK (a = 1)
> ) INHERITS (support.master);
>
> create table support.partitionB(
> CHECK (a = 2)
> ) INHERITS (support.master);
>
> create table support.partitionC(
> CHECK (a = 3)
> ) INHERITS (support.master);
>
> create table support.partitionD(
> CHECK (a = 4)
> ) INHERITS (support.master);
>
> CREATE OR REPLACE FUNCTION support.master_insert()
>  RETURNS trigger AS
> $BODY$
> BEGIN
>     IF ( NEW.a = 1) THEN INSERT INTO support.partitionA VALUES (NEW.
> *);
> ELSIF ( NEW.a = 2) THEN INSERT INTO support.partitionB VALUES (NEW.
*);
> ELSIF ( NEW.a = 3) THEN INSERT INTO support.partitionC VALUES (NEW.
*);
> ELSIF ( NEW.a = 4) THEN INSERT INTO support.partitionD VALUES (NEW.
*);
> ELSE RAISE EXCEPTION 'A (%)is out of range ',NEW.a;
> END IF;
> RETURN NULL;

a trigger for insert should return NEW, no? ;-)

change that and it will work.

regards, jan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Your solution does not works completely .. it duplicates the rows

I've made the change, now RETURNINGon support.master works BUT the
rows are duplicated.
The row are written into support.master and support.partition?
and this in not good for me ...

regards,
Paolo

PS: sorry for previous direct email



SCARICA TISCALI WIPHONE: parla e invia SMS gratis dal tuo cellulare.
http://wiphone.tiscali.it


Re: INSERT RETURNING and partitioning

From
"pdovera@tiscali.it"
Date:
Hi Tom

----Messaggio originale----
Da: thombrown@gmail.com
Data: 21/07/2010 10.38
A: "Jan Otto"<asche@me.com>
Cc: "pdovera@tiscali.it"<pdovera@tiscali.it>, "pgsql-
general@postgresql.org"<pgsql-general@postgresql.org>
Ogg: Re: [GENERAL] INSERT RETURNING and partitioning

On 21 July 2010 09:17, Jan Otto <asche@me.com> wrote:
> hi,
>
> On Jul 21, 2010, at 10:02, "pdovera@tiscali.it" <pdovera@tiscali.it>
wrote:
>
>> Hi,
>> I'm testing the system with these two insert commands:
>>
>> 1) this command returns an empty result set:
>> insert into support.master (a) VALUES (2) RETURNING seq;
>>
>> 2) this command returns correctly the seq (serial) value into
result
>> set:
>> insert into support.partitionB (a) VALUES (2) RETURNING seq;
>>
>> I'm doing something wrong?
>>
>> I'm using the following DDL to create the partitioning tables,
trigger
>> and so on ...
>>
>> create table support.master(
>> seq serial,
>> a INTEGER PRIMARY KEY
>> );
>>
>> create table support.partitionA(
>> CHECK (a = 1)
>> ) INHERITS (support.master);
>>
>> create table support.partitionB(
>> CHECK (a = 2)
>> ) INHERITS (support.master);
>>
>> create table support.partitionC(
>> CHECK (a = 3)
>> ) INHERITS (support.master);
>>
>> create table support.partitionD(
>> CHECK (a = 4)
>> ) INHERITS (support.master);
>>
>> CREATE OR REPLACE FUNCTION support.master_insert()
>>  RETURNS trigger AS
>> $BODY$
>> BEGIN
>>    IF ( NEW.a = 1) THEN INSERT INTO support.partitionA VALUES (NEW.
>> *);
>> ELSIF ( NEW.a = 2) THEN INSERT INTO support.partitionB VALUES (NEW.
*);
>> ELSIF ( NEW.a = 3) THEN INSERT INTO support.partitionC VALUES (NEW.
*);
>> ELSIF ( NEW.a = 4) THEN INSERT INTO support.partitionD VALUES (NEW.
*);
>> ELSE RAISE EXCEPTION 'A (%)is out of range ',NEW.a;
>> END IF;
>> RETURN NULL;
>
> a trigger for insert should return NEW, no? ;-)
>
> change that and it will work.
>
> regards, jan
>

Yes, Jan's right.  You're effectively overriding the return values
with NULL.

Although I think I know why you're doing it, because you want to
redirect the value to the child table so that it doesn't get inserted
into the parent table as that would cause duplicate values being
displayed in the parent table.

Unfortunately, you can't use RULEs as an alternative as they won't
allow returning values if they have conditions on them.

Thom

I prefer to avoid duplicated rows because that is not the idea of
partitioning

Paolo


SCARICA TISCALI WIPHONE: parla e invia SMS gratis dal tuo cellulare.
http://wiphone.tiscali.it


Re: INSERT RETURNING and partitioning

From
Alban Hertroys
Date:
On 21 Jul 2010, at 11:35, pdovera@tiscali.it wrote:

>> Yes, Jan's right.  You're effectively overriding the return values
>> with NULL.
>>
>> Although I think I know why you're doing it, because you want to
>> redirect the value to the child table so that it doesn't get inserted
>> into the parent table as that would cause duplicate values being
>> displayed in the parent table.
>>
>> Unfortunately, you can't use RULEs as an alternative as they won't
>> allow returning values if they have conditions on them.
>>
>> Thom
>>
> I prefer to avoid duplicated rows because that is not the idea of
> partitioning
>
> Paolo


Well, you didn't insert anything in the parent table after all, did you? So the database isn't giving you the "wrong"
answer,just not the one you hoped for. 

The usual advice in these cases is to insert into the child tables directly. That's not really a pretty solution,
especiallyif you tend to insert multiple rows at once that could end up in different partitions. 


This problem pops up often enough that it would be worth putting some effort into a solution. A simple library that
allowsyou to make a copy of the diagnostics, keeps reference counts to that copy (so that you can create and free it
fromyour application without causing memory leaks) and allows you to retrieve and update it would help a lot already I
think.That should be doable with just libPQ and passing that struct along with the context. 

Your trigger function would need a little change in that case (pseudo-code):

CREATE OR REPLACE FUNCTION support.master_insert()
 RETURNS trigger AS
$BODY$
BEGIN

-- Retrieve row-count from context

    IF ( NEW.a = 1) THEN INSERT INTO support.partitionA VALUES (NEW.
*);
ELSIF ( NEW.a = 2) THEN INSERT INTO support.partitionB VALUES (NEW.*);
ELSIF ( NEW.a = 3) THEN INSERT INTO support.partitionC VALUES (NEW.*);
ELSIF ( NEW.a = 4) THEN INSERT INTO support.partitionD VALUES (NEW.*);
ELSE RAISE EXCEPTION 'A (%)is out of range ',NEW.a;
END IF;

-- Update row-count in context

RETURN NULL;
END;
$BODY$
 LANGUAGE 'plpgsql';



In your application you would do something like:

newRowcount(context);
execute(context, "INSERT INTO parent_table (...) SELECT ...");
count = getRowCount(context);




Maybe you could even override the database's internal diagnostics ROW_COUNT value (after taking the sum of the results
ofinserting into each child table) and have that "faked" result available after the insert into the parent table
finishes.Probably not though, seeing you have to return NULL at the end of that before INSERT trigger... 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c46cca5286211533124439!