Обсуждение: table partition and column default
Hi, As the document explains, column defaults can be specified separately for each partition. But I found that INSERT via the partitioned table ignores that default. Is this expected behavior or bug? CREATE TABLE test (i INT, j INT) PARTITION BY RANGE (i); CREATE TABLE test1 PARTITION OF test (j DEFAULT 99) FOR VALUES FROM (1) TO (10); INSERT INTO test VALUES (1, DEFAULT); INSERT INTO test1 VALUES (2, DEFAULT); SELECT * FROM test; i | j ---+-------- 1 | (null) 2 | 99 (2 rows) In the above example, INSERT accessing directly to the partition uses the default, but INSERT via the partitioned table not. Regards, -- Fujii Masao
Fujii-san, On Wed, Dec 25, 2019 at 12:19 PM Fujii Masao <masao.fujii@gmail.com> wrote: > > Hi, > > As the document explains, column defaults can be specified separately for > each partition. But I found that INSERT via the partitioned table ignores > that default. Is this expected behavior or bug? > > CREATE TABLE test (i INT, j INT) PARTITION BY RANGE (i); > CREATE TABLE test1 PARTITION OF test (j DEFAULT 99) FOR VALUES FROM (1) TO (10); > INSERT INTO test VALUES (1, DEFAULT); > INSERT INTO test1 VALUES (2, DEFAULT); > SELECT * FROM test; > i | j > ---+-------- > 1 | (null) > 2 | 99 > (2 rows) > > In the above example, INSERT accessing directly to the partition uses > the default, but INSERT via the partitioned table not. This is as of now expected. IIRC, there was some discussion about implementing a feature whereby partition's default will used for an attribute if it's null even after considering the parent table's default, that is, when no default value is defined in the parent. The details are at toward the end of this thread: https://www.postgresql.org/message-id/flat/578398af46350effe7111895a4856b87b02e000e.camel%402ndquadrant.com Thanks, Amit
On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <amitlangote09@gmail.com> wrote: > > Fujii-san, > > On Wed, Dec 25, 2019 at 12:19 PM Fujii Masao <masao.fujii@gmail.com> wrote: > > > > Hi, > > > > As the document explains, column defaults can be specified separately for > > each partition. But I found that INSERT via the partitioned table ignores > > that default. Is this expected behavior or bug? > > > > CREATE TABLE test (i INT, j INT) PARTITION BY RANGE (i); > > CREATE TABLE test1 PARTITION OF test (j DEFAULT 99) FOR VALUES FROM (1) TO (10); > > INSERT INTO test VALUES (1, DEFAULT); > > INSERT INTO test1 VALUES (2, DEFAULT); > > SELECT * FROM test; > > i | j > > ---+-------- > > 1 | (null) > > 2 | 99 > > (2 rows) > > > > In the above example, INSERT accessing directly to the partition uses > > the default, but INSERT via the partitioned table not. > > This is as of now expected. > > IIRC, there was some discussion about implementing a feature whereby > partition's default will used for an attribute if it's null even after > considering the parent table's default, that is, when no default value > is defined in the parent. The details are at toward the end of this > thread: > > https://www.postgresql.org/message-id/flat/578398af46350effe7111895a4856b87b02e000e.camel%402ndquadrant.com Thanks for pointing that thread! As you mentioned in that thread, I also think that this current behavior (maybe restriction) should be documented. What about adding the note like "a partition's default value is not applied when inserting a tuple through a partitioned table." into the document? Patch attached. Regards, -- Fujii Masao
Вложения
On Wed, Dec 25, 2019 at 5:40 PM Fujii Masao <masao.fujii@gmail.com> wrote: > On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <amitlangote09@gmail.com> wrote: > > IIRC, there was some discussion about implementing a feature whereby > > partition's default will used for an attribute if it's null even after > > considering the parent table's default, that is, when no default value > > is defined in the parent. The details are at toward the end of this > > thread: > > > > https://www.postgresql.org/message-id/flat/578398af46350effe7111895a4856b87b02e000e.camel%402ndquadrant.com > > Thanks for pointing that thread! > > As you mentioned in that thread, I also think that this current > behavior (maybe restriction) should be documented. > What about adding the note like "a partition's default value is > not applied when inserting a tuple through a partitioned table." > into the document? Agreed. > Patch attached. Thanks for creating the patch, looks good to me. Thanks, Amit
On Wed, Dec 25, 2019 at 5:47 PM Amit Langote <amitlangote09@gmail.com> wrote: > > On Wed, Dec 25, 2019 at 5:40 PM Fujii Masao <masao.fujii@gmail.com> wrote: > > On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <amitlangote09@gmail.com> wrote: > > > IIRC, there was some discussion about implementing a feature whereby > > > partition's default will used for an attribute if it's null even after > > > considering the parent table's default, that is, when no default value > > > is defined in the parent. The details are at toward the end of this > > > thread: > > > > > > https://www.postgresql.org/message-id/flat/578398af46350effe7111895a4856b87b02e000e.camel%402ndquadrant.com > > > > Thanks for pointing that thread! > > > > As you mentioned in that thread, I also think that this current > > behavior (maybe restriction) should be documented. > > What about adding the note like "a partition's default value is > > not applied when inserting a tuple through a partitioned table." > > into the document? > > Agreed. > > > Patch attached. > > Thanks for creating the patch, looks good to me. Thanks for reviewing the patch. Committed! Regards, -- Fujii Masao
Fuji-san, On Thu, Dec 26, 2019 at 7:12 AM Fujii Masao <masao.fujii@gmail.com> wrote: > > On Wed, Dec 25, 2019 at 5:47 PM Amit Langote <amitlangote09@gmail.com> wrote: > > > > On Wed, Dec 25, 2019 at 5:40 PM Fujii Masao <masao.fujii@gmail.com> wrote: > > > On Wed, Dec 25, 2019 at 1:56 PM Amit Langote <amitlangote09@gmail.com> wrote: > > > > IIRC, there was some discussion about implementing a feature whereby > > > > partition's default will used for an attribute if it's null even after > > > > considering the parent table's default, that is, when no default value > > > > is defined in the parent. The details are at toward the end of this > > > > thread: > > > > > > > > https://www.postgresql.org/message-id/flat/578398af46350effe7111895a4856b87b02e000e.camel%402ndquadrant.com > > > > > > Thanks for pointing that thread! > > > > > > As you mentioned in that thread, I also think that this current > > > behavior (maybe restriction) should be documented. > > > What about adding the note like "a partition's default value is > > > not applied when inserting a tuple through a partitioned table." > > > into the document? > > > > Agreed. > > > > > Patch attached. > > > > Thanks for creating the patch, looks good to me. > > Thanks for reviewing the patch. Committed! I saw that you only pushed it on master, shouldn't we backpatch it down to pg10 as this is the declarative partitioning behavior since the beginning?
On Thu, Dec 26, 2019 at 6:21 PM Julien Rouhaud <rjuju123@gmail.com> wrote: > On Thu, Dec 26, 2019 at 7:12 AM Fujii Masao <masao.fujii@gmail.com> wrote: > > Thanks for reviewing the patch. Committed! > > I saw that you only pushed it on master, shouldn't we backpatch it > down to pg10 as this is the declarative partitioning behavior since > the beginning? I had meant to reply to this but somehow forgot. I agree that it might be a good idea to back-patch this down to PG 10. Thanks, Amit
On 2020/02/04 13:56, Amit Langote wrote: > On Thu, Dec 26, 2019 at 6:21 PM Julien Rouhaud <rjuju123@gmail.com> wrote: >> On Thu, Dec 26, 2019 at 7:12 AM Fujii Masao <masao.fujii@gmail.com> wrote: >>> Thanks for reviewing the patch. Committed! >> >> I saw that you only pushed it on master, shouldn't we backpatch it >> down to pg10 as this is the declarative partitioning behavior since >> the beginning? > > I had meant to reply to this but somehow forgot. > > I agree that it might be a good idea to back-patch this down to PG 10. Back-patched to v10. Thanks Julien and Amit for pointing out this! Regards, -- Fujii Masao NTT DATA CORPORATION Advanced Platform Technology Group Research and Development Headquarters