BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      16102
Logged by:          Fan Liu
Email address:      [hidden email]
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

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10

Tom Lane-2
PG Bug reporting form <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: BUG #16102: Table can't be drop on PostgreSQL 10.09 if the table was created from PostgreSQL 10.10

Sergei Kornilov
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 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