Thread: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10
BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 16102 Logged by: Fan Liu Email address: 82820676@qq.com PostgreSQL version: 10.10 Operating system: suse Description: Hi, Have anyone meet table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10? 1) On v10.10, create table 'pgbench_accounts' with below script -----------script------------- DROP TABLE IF EXISTS pgbench_accounts; CREATE TABLE pgbench_accounts ( aid int not null, bid int, abalance int, filler char(84) ) PARTITION BY RANGE (aid); CREATE FUNCTION __create_partitions__(count INTEGER, number INTEGER DEFAULT 1000000) RETURNS VOID AS $$ DECLARE i int; partition_size int := number / count; BEGIN FOR i IN SELECT generate_series(1, count) LOOP EXECUTE format('CREATE TABLE pgbench_accounts_%s PARTITION OF pgbench_accounts FOR VALUES FROM (%s) TO (%s)', i, (i - 1) * partition_size + 1, i * partition_size + 1); END LOOP; END $$ LANGUAGE plpgsql; SELECT __create_partitions__(:pcount, :num); DROP FUNCTION __create_partitions__(INTEGER,INTEGER); INSERT INTO pgbench_accounts (aid, bid, abalance) SELECT generate_series(1, :num), 1, 0; -----------script end------------- 2) Remove v10.10 and use v10.09 start with same storage (our downgrade test on K8s, storage are decouple with application ) 3) Then we execute same script on v10.09, we find that drop table has no error, but the table still there. 4) another table pgbench_accounts_1 can be drop and no issue found. postgres=# DROP TABLE pgbench_accounts; DROP TABLE postgres=# \dt List of relations Schema | Name | Type | Owner ----------+--------------------+-------+---------- public | geometries | table | postgres public | pgbench_accounts | table | postgres public | pgbench_accounts_1 | table | postgres public | spatial_ref_sys | table | postgres topology | layer | table | postgres topology | topology | table | postgres (6 rows) BRs, Fan Liu
Re: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10
From
Tom Lane
Date:
PG Bug reporting form <noreply@postgresql.org> writes: > Have anyone meet table can't be drop on PostgreSQL 10.09 if the table was > created from PostgreSQL 10.10? Certainly possible, seeing that the 10.10 release notes mention additional dependency rules for partitioned tables: * Install dependencies to prevent dropping partition key columns > 2) Remove v10.10 and use v10.09 start with same storage (our downgrade test > on K8s, storage are decouple with application ) It is not, never has been, and never will be supported to try to run a cluster with an older server version after it's been modified by a newer one. We have enough trouble ensuring forwards compatibility. Having said that, the 10.10 bug fix should only have made a difference in cases where a table's partition key columns involve non-built-in types, which isn't the case in your example. I wonder whether this is pilot error, e.g. you created and dropped the pgbench_accounts table in some other schema than "public", but there's still one in "public". regards, tom lane
Re: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10
From
Sergei Kornilov
Date:
Hello > It is not, never has been, and never will be supported to try to run > a cluster with an older server version after it's been modified by > a newer one. We have enough trouble ensuring forwards compatibility. I agree and believe that we document this compatibility policy somewhere. But I found such description in docs: https://www.postgresql.org/docs/current/upgrading.html > Minor releases never change the internal storage format and are always compatible with earlier and later minor releasesof the same major version number. For example, version 10.1 is compatible with version 10.0 and version 10.6. Similarly,for example, 9.5.3 is compatible with 9.5.0, 9.5.1, and 9.5.6. Seems opposite. regards, Sergei
回复: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10
From
"Fan||"
Date:
Sorry, sent to wrong mail.
Sent again.
------------------ 原始邮件 ------------------
发件人: "我自己的邮箱"<82820676@qq.com>;
发送时间: 2019年11月12日(星期二) 上午9:32
收件人: "Sergei Kornilov"<sk@zsrv.org>;
主题: 回复: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10
Hello,
Thanks for the reply. I understand this is an issue really sounds strange.
So, form document it saying that it should be not problem when switching between minor release.
Will you assign someone take to look this issue? Do you need the full script? It's like 100% reproduce.
BRs,
Fan Lilu
------------------ 原始邮件 ------------------
发件人: "Sergei Kornilov"<sk@zsrv.org>;
发送时间: 2019年11月9日(星期六) 凌晨0:59
收件人: "Tom Lane"<tgl@sss.pgh.pa.us>;"Fan||"<82820676@qq.com>;
抄送: "pgsql-bugs@lists.postgresql.org"<pgsql-bugs@lists.postgresql.org>;
主题: Re: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10
> It is not, never has been, and never will be supported to try to run
> a cluster with an older server version after it's been modified by
> a newer one. We have enough trouble ensuring forwards compatibility.
I agree and believe that we document this compatibility policy somewhere. But I found such description in docs: https://www.postgresql.org/docs/current/upgrading.html
> Minor releases never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number. For example, version 10.1 is compatible with version 10.0 and version 10.6. Similarly, for example, 9.5.3 is compatible with 9.5.0, 9.5.1, and 9.5.6.
Seems opposite.
regards, Sergei
Re: 回复: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was createdfrom PostgreSQL 10.10
From
Tomas Vondra
Date:
On Thu, Nov 14, 2019 at 04:13:10PM +0800, Fan|| wrote: >Sorry, sent to wrong mail. >Sent again. > Please don't top post, it makes following the discussion much harder. > >------------------ 原始邮件 ------------------ >发件人: "我自己的邮箱"<82820676@qq.com>; >发送时间: 2019年11月12日(星期二) 上午9:32 >收件人: "Sergei Kornilov"<sk@zsrv.org>; > >主题: 回复: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10 > >Hello, > >Thanks for the reply. I understand this is an issue really sounds strange. > >So, form document it saying that it should be not problem when switching between minor release. The linked docs are about internal on-disk format, and that's true. It simply means minor releases do no change on-disk format of data files, so pg_upgrade nor dump/reload is needed. The issue here is that a minor version fixed some sorf of bug, affecting catalog contents, probably by adding a missing dependency between objects. The older minor release is unaware of that, making the drop fail. But that's expected, as Tom explains. We need to be able to make these changes, because that's what bug fixes often require. And we can't stop doing that. So we only support upgrades, i.e. going from 12.0 -> 12.1 -> 12.3 ... and the chance of us supporting downgrades is pretty much nil. >Will you assign someone take to look this issue? Do you need the full >script? It's like 100% reproduce. > Unlikely. Very few users need the capability to downgrade to older minor releases, and I don't think anyone is going to work on that. If it is an important feature for you, you'll need to look into what would it take to support it and submit a patch. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Re: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was createdfrom PostgreSQL 10.10
From
"Fan||"
Date:
------------------ Original ------------------
From: "Tomas Vondra";<tomas.vondra@2ndquadrant.com>;
Date: Nov 15, 2019
To: "Fan||"<82820676@qq.com>;
Cc: "pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
Subject: Re: 回复: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was createdfrom PostgreSQL 10.10
>Sorry, sent to wrong mail.
>Sent again.
>
Please don't top post, it makes following the discussion much harder.
>
>------------------ 原始邮件 ------------------
>发件人: "我自己的邮箱"<82820676@qq.com>;
>发送时间: 2019年11月12日(星期二) 上午9:32
>收件人: "Sergei Kornilov"<sk@zsrv.org>;
>
>主题: 回复: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10
>
>Hello,
>
>Thanks for the reply. I understand this is an issue really sounds strange.
>
>So, form document it saying that it should be not problem when switching between minor release.
The linked docs are about internal on-disk format, and that's true. It
simply means minor releases do no change on-disk format of data files,
so pg_upgrade nor dump/reload is needed.
The issue here is that a minor version fixed some sorf of bug, affecting
catalog contents, probably by adding a missing dependency between
objects. The older minor release is unaware of that, making the drop
fail. But that's expected, as Tom explains.
We need to be able to make these changes, because that's what bug fixes
often require. And we can't stop doing that.
So we only support upgrades, i.e. going from 12.0 -> 12.1 -> 12.3 ...
and the chance of us supporting downgrades is pretty much nil.
>Will you assign someone take to look this issue? Do you need the full
>script? It's like 100% reproduce.
>
Unlikely. Very few users need the capability to downgrade to older minor
releases, and I don't think anyone is going to work on that. If it is an
important feature for you, you'll need to look into what would it take
to support it and submit a patch.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
------------------ Original End ------------------
As a developer I can understand your point. It's OK, if PostgreSQL confirm this is not going to be support officially, then we will add a note to mention this in our documents.
One thing I am not that agree that this kind of 'downgrade' will be " a few". We know that many SW is going to be deploy on cloud, the this kind of 'downgrade' will common happen. For example, HELM's rollback indeed is a downgrade. This how we detract this issue.
Of cause, it's your decision for supporting or not. Or maybe just update document.
And for sure, if we solve the issue, we will contribute.
Thanks for the reply and explanation.
BRs,
Fan Liu