Horton

Thread

Commits

  1. Add note about how each partition's default value is treated, into the doc.

  1. table partition and column default

    Fujii Masao <masao.fujii@gmail.com> — 2019-12-25T03:19:04Z

    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

  2. Re: table partition and column default

    Amit Langote <amitlangote09@gmail.com> — 2019-12-25T04:56:15Z

    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

  3. Re: table partition and column default

    Fujii Masao <masao.fujii@gmail.com> — 2019-12-25T08:40:16Z

    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

  4. Re: table partition and column default

    Amit Langote <amitlangote09@gmail.com> — 2019-12-25T08:47:38Z

    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

  5. Re: table partition and column default

    Fujii Masao <masao.fujii@gmail.com> — 2019-12-26T06:11:48Z

    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

  6. Re: table partition and column default

    Julien Rouhaud <rjuju123@gmail.com> — 2019-12-26T09:21:45Z

    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?

  7. Re: table partition and column default

    Amit Langote <amitlangote09@gmail.com> — 2020-02-04T04:56:30Z

    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

  8. Re: table partition and column default

    Fujii Masao <masao.fujii@oss.nttdata.com> — 2020-02-05T05:09:29Z

    
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