Thread: pg_upgrade failed with ERROR: null relpartbound for relation 18159 error.

pg_upgrade failed with ERROR: null relpartbound for relation 18159 error.

From

Rajkumar Raghuwanshi

Date:

04 October 2018, 00:58:39

Hi,

I am getting ERROR: null relpartbound for relation 18159 while doing pg_upgrade from v11 to v11/master.

-- user-defined operator class in partition key
CREATE FUNCTION my_int4_sort(int4,int4) RETURNS int LANGUAGE sql
AS $$ SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$;
CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING btree AS
OPERATOR 1 < (int4,int4), OPERATOR 2 <= (int4,int4),
OPERATOR 3 = (int4,int4), OPERATOR 4 >= (int4,int4),
OPERATOR 5 > (int4,int4), FUNCTION 1 my_int4_sort(int4,int4);
CREATE TABLE partkey_t (a int4) PARTITION BY RANGE (a test_int4_ops);
CREATE TABLE partkey_t_1 PARTITION OF partkey_t FOR VALUES FROM (0) TO (1000);
INSERT INTO partkey_t VALUES (100);
INSERT INTO partkey_t VALUES (200);

--ran pg_upgrade failed with below error.

pg_restore: creating TABLE "public.partitioned"

pg_restore: creating TABLE "public.partkey_t"
pg_restore: creating TABLE "public.partkey_t_1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 485; 1259 18159 TABLE partkey_t_1 edb
pg_restore: [archiver (db)] could not execute query: ERROR: null relpartbound for relation 18159
CONTEXT: SQL function "my_int4_sort"
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('18161'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('18160'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('18159'::pg_catalog.oid);

CREATE TABLE "public"."partkey_t_1" (
"a" integer
);

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'a'
AND attrelid = '"public"."partkey_t_1"'::pg_catalog.regclass;

-- For binary upgrade, set up inheritance and partitioning this way.
ALTER TABLE ONLY "public"."partkey_t" ATTACH PARTITION "public"."partkey_t_1" FOR VALUES FROM (0) TO (1000);

-- For binary upgrade, set heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '1915', relminmxid = '1'
WHERE oid = '"public"."partkey_t_1"'::pg_catalog.regclass;

Thanks & Regards,

Rajkumar Raghuwanshi

QMG, EnterpriseDB Corporation

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Amit Langote

Date:

05 October 2018, 12:06:52

Thanks for the report.

On 2018/10/04 3:58, Rajkumar Raghuwanshi wrote:
> Hi,
> 
> I am getting ERROR:  null relpartbound for relation 18159 while doing
> pg_upgrade from v11 to v11/master.
> 
> -- user-defined operator class in partition key
> CREATE FUNCTION my_int4_sort(int4,int4) RETURNS int LANGUAGE sql
>   AS $$ SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$;
> CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING btree AS
>   OPERATOR 1 < (int4,int4), OPERATOR 2 <= (int4,int4),
>   OPERATOR 3 = (int4,int4), OPERATOR 4 >= (int4,int4),
>   OPERATOR 5 > (int4,int4), FUNCTION 1 my_int4_sort(int4,int4);
> CREATE TABLE partkey_t (a int4) PARTITION BY RANGE (a test_int4_ops);
> CREATE TABLE partkey_t_1 PARTITION OF partkey_t FOR VALUES FROM (0) TO
> (1000);
> INSERT INTO partkey_t VALUES (100);
> INSERT INTO partkey_t VALUES (200);
> 
> --ran pg_upgrade failed with below error.
> pg_restore: [archiver (db)] could not execute query: ERROR:  null
> relpartbound for relation 18159
> CONTEXT:  SQL function "my_int4_sort"

Interesting test case.

To reproduce, the following works too (after creating the objects as
described above):

alter table partkey_t detach partition partkey_t_1;
alter table partkey_t attach partition partkey_t_1 for values from (0) to
(1000);
ERROR:  null relpartbound for relation 16396
CONTEXT:  SQL function "my_int4_sort"

The stack at the time of the error:

(gdb) bt
#0  RelationBuildPartitionDesc
#1  0x00000000009bf04e in RelationBuildDesc
#2  0x00000000009c1784 in RelationClearRelation
#3  0x00000000009c1cc5 in RelationFlushRelation
#4  0x00000000009c1dd9 in RelationCacheInvalidateEntry
#5  0x00000000009b9496 in LocalExecuteInvalidationMessage
#6  0x00000000009b91ec in ProcessInvalidationMessages
#7  0x00000000009b9cdb in CommandEndInvalidationMessages
#8  0x00000000005346ef in AtCCI_LocalCache
#9  0x0000000000534124 in CommandCounterIncrement
#10 0x00000000006c579d in fmgr_sql
#11 0x00000000009de7c2 in FunctionCall2Coll
#12 0x000000000058ac9f in partition_rbound_cmp
#13 0x0000000000588059 in check_new_partition_bound
#14 0x000000000067f536 in ATExecAttachPartition
<snip>

So, the CommandCounterIncrement done in fmgr_sql causes partkey_t's
PartitionDesc to be recomputed, which counts partkey_t_1 as its child
because ATExecAttachPartition has already finished CreateInheritance which
would've sent out an invalidation message for partkey_t.

As of commit 2fbdf1b38bc [1], which has been applied in 11 and HEAD
branches, RelationBuildPartitionDesc emits an error if we don't find
relpartbound set for a child found by scanning pg_inherits, instead of
skipping such children.  While that commit switched the order of creating
pg_inherits entry and checking a new bound against existing bounds in
DefineRelation in light of aforementioned change, it didn't in
ATExecAttachPartition, hence this error.

Attached patch fixes that.

I thought we'd need to apply this to 10, 11, HEAD, but I couldn't
reproduce this in 10.  That's because the above commit wasn't applied to
10, so the child that causes this error is being skipped in 10's case.

Maybe, we should apply parts of the above commit that apply to 10 and then
this patch on top.  Attached for-10.patch file does that.

Thanks,
Amit

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2fbdf1b38bc

Attachment

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Michael Paquier

Date:

05 October 2018, 13:05:18

On Fri, Oct 05, 2018 at 03:06:52PM +0900, Amit Langote wrote:
> To reproduce, the following works too (after creating the objects as
> described above):
>
> alter table partkey_t detach partition partkey_t_1;
> alter table partkey_t attach partition partkey_t_1 for values from (0) to
> (1000);
> ERROR:  null relpartbound for relation 16396
> CONTEXT:  SQL function "my_int4_sort"
>
> The stack at the time of the error:
>
> (gdb) bt
> #0  RelationBuildPartitionDesc
> #1  0x00000000009bf04e in RelationBuildDesc
> #2  0x00000000009c1784 in RelationClearRelation
> #3  0x00000000009c1cc5 in RelationFlushRelation
> #4  0x00000000009c1dd9 in RelationCacheInvalidateEntry
> #5  0x00000000009b9496 in LocalExecuteInvalidationMessage
> #6  0x00000000009b91ec in ProcessInvalidationMessages
> #7  0x00000000009b9cdb in CommandEndInvalidationMessages
> #8  0x00000000005346ef in AtCCI_LocalCache
> #9  0x0000000000534124 in CommandCounterIncrement
> #10 0x00000000006c579d in fmgr_sql
> #11 0x00000000009de7c2 in FunctionCall2Coll
> #12 0x000000000058ac9f in partition_rbound_cmp
> #13 0x0000000000588059 in check_new_partition_bound
> #14 0x000000000067f536 in ATExecAttachPartition
> <snip>
>
> So, the CommandCounterIncrement done in fmgr_sql causes partkey_t's
> PartitionDesc to be recomputed, which counts partkey_t_1 as its child
> because ATExecAttachPartition has already finished CreateInheritance which
> would've sent out an invalidation message for partkey_t.
>
> As of commit 2fbdf1b38bc [1], which has been applied in 11 and HEAD
> branches, RelationBuildPartitionDesc emits an error if we don't find
> relpartbound set for a child found by scanning pg_inherits, instead of
> skipping such children.  While that commit switched the order of creating
> pg_inherits entry and checking a new bound against existing bounds in
> DefineRelation in light of aforementioned change, it didn't in
> ATExecAttachPartition, hence this error.
>
> Attached patch fixes that.

Could you please add a minimal regression test in your patch?  That's
the second bug related to ATTACH PARTITION I am looking at today..

> I thought we'd need to apply this to 10, 11, HEAD, but I couldn't
> reproduce this in 10.  That's because the above commit wasn't applied to
> 10, so the child that causes this error is being skipped in 10's case.

Hmm.  Indeed, v10 does not complain but HEAD does.  (I ran the attached
SQL file, which is the complete test case both of you have compiled).
--
Michael

Attachment

signature.asc

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Amit Langote

Date:

05 October 2018, 13:55:51

On 2018/10/05 16:05, Michael Paquier wrote:
>> As of commit 2fbdf1b38bc [1], which has been applied in 11 and HEAD
>> branches, RelationBuildPartitionDesc emits an error if we don't find
>> relpartbound set for a child found by scanning pg_inherits, instead of
>> skipping such children.  While that commit switched the order of creating
>> pg_inherits entry and checking a new bound against existing bounds in
>> DefineRelation in light of aforementioned change, it didn't in
>> ATExecAttachPartition, hence this error.
>>
>> Attached patch fixes that.
> 
> Could you please add a minimal regression test in your patch?  That's
> the second bug related to ATTACH PARTITION I am looking at today..

OK, done, although I hope that's not bloating the tests.

>> I thought we'd need to apply this to 10, 11, HEAD, but I couldn't
>> reproduce this in 10.  That's because the above commit wasn't applied to
>> 10, so the child that causes this error is being skipped in 10's case.
> 
> Hmm.  Indeed, v10 does not complain but HEAD does.  (I ran the attached
> SQL file, which is the complete test case both of you have compiled).

Did you forget to attach some file?

Thanks,
Amit

Attachment

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Michael Paquier

Date:

05 October 2018, 14:38:16

On Fri, Oct 05, 2018 at 04:55:51PM +0900, Amit Langote wrote:
> On 2018/10/05 16:05, Michael Paquier wrote:
>> Hmm.  Indeed, v10 does not complain but HEAD does.  (I ran the attached
>> SQL file, which is the complete test case both of you have compiled).
>
> Did you forget to attach some file?

Yep, here you go for the archive's sake.
--
Michael

Attachment

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Alvaro Herrera

Date:

07 October 2018, 07:15:32

On 2018-Oct-05, Amit Langote wrote:

> On 2018/10/05 16:05, Michael Paquier wrote:
> >> As of commit 2fbdf1b38bc [1], which has been applied in 11 and HEAD
> >> branches, RelationBuildPartitionDesc emits an error if we don't find
> >> relpartbound set for a child found by scanning pg_inherits, instead of
> >> skipping such children.  While that commit switched the order of creating
> >> pg_inherits entry and checking a new bound against existing bounds in
> >> DefineRelation in light of aforementioned change, it didn't in
> >> ATExecAttachPartition, hence this error.
> >>
> >> Attached patch fixes that.
> > 
> > Could you please add a minimal regression test in your patch?  That's
> > the second bug related to ATTACH PARTITION I am looking at today..
> 
> OK, done, although I hope that's not bloating the tests.

Pushed to 11 and master.  I'm not convinced that it's a good idea to
mess with 10 at this point -- the partitioning code is rather completely
different already ...

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Michael Paquier

Date:

07 October 2018, 13:59:56

On Sat, Oct 06, 2018 at 10:15:32PM -0300, Alvaro Herrera wrote:
> Pushed to 11 and master.  I'm not convinced that it's a good idea to
> mess with 10 at this point -- the partitioning code is rather completely
> different already ...

While I definitely agree with you to not mess up with this portion of
the partitioning code for REL_10_STABLE, I think that we have a good set
of arguments to get the regression test into this branch.  This
particular case has been broken while developing v11, hence it would be
nice to get the insurance that any future bug fix applying to
REL_10_STABLE does not break it again.
--
Michael

Attachment

signature.asc

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Alvaro Herrera

Date:

07 October 2018, 20:13:19

On 2018-Oct-07, Michael Paquier wrote:

> On Sat, Oct 06, 2018 at 10:15:32PM -0300, Alvaro Herrera wrote:
> > Pushed to 11 and master.  I'm not convinced that it's a good idea to
> > mess with 10 at this point -- the partitioning code is rather completely
> > different already ...
> 
> While I definitely agree with you to not mess up with this portion of
> the partitioning code for REL_10_STABLE, I think that we have a good set
> of arguments to get the regression test into this branch.  This
> particular case has been broken while developing v11, hence it would be
> nice to get the insurance that any future bug fix applying to
> REL_10_STABLE does not break it again.

Good point.  I cannot do that today, but if you want to, please go
ahead.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Michael Paquier

Date:

07 October 2018, 21:09:13

On Sun, Oct 07, 2018 at 11:13:19AM -0300, Alvaro Herrera wrote:
> Good point.  I cannot do that today, but if you want to, please go
> ahead.

Okay, done.
--
Michael

Attachment

signature.asc

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Amit Langote

Date:

09 October 2018, 09:04:45

On 2018/10/08 0:09, Michael Paquier wrote:
> On Sun, Oct 07, 2018 at 11:13:19AM -0300, Alvaro Herrera wrote:
>> Good point.  I cannot do that today, but if you want to, please go
>> ahead.
> 
> Okay, done.

Thank you both.

Regards,
Amit

Re: pg_upgrade failed with ERROR: null relpartbound for relation18159 error.

From

Amit Langote

Date:

09 October 2018, 09:04:45

On 2018/10/08 0:09, Michael Paquier wrote:
> On Sun, Oct 07, 2018 at 11:13:19AM -0300, Alvaro Herrera wrote:
>> Good point.  I cannot do that today, but if you want to, please go
>> ahead.
> 
> Okay, done.

Thank you both.

Regards,
Amit