Обсуждение: INSERT RETURNING and partitioning
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
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
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
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
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
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
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
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!