Обсуждение: BUG #13168: DROP DATABASE does not clean up all references

The following bug has been logged on the website:

Bug reference:      13168
Logged by:          Cees van Zeeland
Email address:      cees.van.zeeland@xs4all.nl
PostgreSQL version: 9.4.1
Operating system:   Windows 7 Home Edition 64 bit
Description:

-- Follow the next steps to produce an unexpected error
-- The next 16 lines work fine
CREATE DATABASE new
  WITH ENCODING='LATIN1'
       TEMPLATE=template0
       LC_COLLATE='C'
       LC_CTYPE='C'
       CONNECTION LIMIT=-1;

DROP TABLE IF EXISTS category CASCADE;
CREATE TABLE category
  (
     category_id SERIAL  PRIMARY KEY
    ,category_name varchar(30) NOT NULL
    ,category_description varchar(200) NOT NULL
  );

-- Now I am going to repeat the same script without DROP TABLE
-- After the next line, I assume that all old references within the database
are gone
DROP DATABASE new;

CREATE DATABASE new
  WITH ENCODING='LATIN1'
       TEMPLATE=template0
       LC_COLLATE='C'
       LC_CTYPE='C'
       CONNECTION LIMIT=-1;

-- Here I leave out the line with DROP TABLE
-- It results to the error message:
-- ERROR: relation "category" already exists
-- Is this a bug or am I missing something?
-- DROP TABLE IF EXISTS category CASCADE;

CREATE TABLE category
  (
     category_id SERIAL  PRIMARY KEY
    ,category_name varchar(30) NOT NULL
    ,category_description varchar(200) NOT NULL
  );

DROP DATABASE new;

-- In fact I can repeat this script
-- first time: no error
-- second time: with error

cees.van.zeeland@xs4all.nl writes:
> -- Follow the next steps to produce an unexpected error
> -- The next 16 lines work fine
> CREATE DATABASE new
>   WITH ENCODING='LATIN1'
>        TEMPLATE=template0
>        LC_COLLATE='C'
>        LC_CTYPE='C'
>        CONNECTION LIMIT=-1;

> DROP TABLE IF EXISTS category CASCADE;
> CREATE TABLE category
>   (
>      category_id SERIAL  PRIMARY KEY
>     ,category_name varchar(30) NOT NULL
>     ,category_description varchar(200) NOT NULL
>   );

AFAICS you didn't reconnect to database "new", so table "category" is
in whatever database you connected to originally.

> DROP DATABASE new;

If you had reconnected, it would not have allowed you to drop the current
database ...

            regards, tom lane

On Sun, Apr 26, 2015 at 2:46 PM, <cees.van.zeeland@xs4all.nl> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13168
> Logged by:          Cees van Zeeland
> Email address:      cees.van.zeeland@xs4all.nl
> PostgreSQL version: 9.4.1
> Operating system:   Windows 7 Home Edition 64 bit
> Description:
>
> -- Follow the next steps to produce an unexpected error
> -- The next 16 lines work fine
>

=E2=80=8BYou are connected to the "postgres" database (probably, maybe temp=
late1...)
=E2=80=8B

> CREATE DATABASE new
>   WITH ENCODING=3D'LATIN1'
>        TEMPLATE=3Dtemplate0
>        LC_COLLATE=3D'C'
>        LC_CTYPE=3D'C'
>        CONNECTION LIMIT=3D-1;
>
>
=E2=80=8BYou are still connected to "postgres"...
=E2=80=8B


> DROP TABLE IF EXISTS category CASCADE;
> CREATE TABLE category
>   (
>      category_id SERIAL  PRIMARY KEY
>     ,category_name varchar(30) NOT NULL
>     ,category_description varchar(200) NOT NULL
>   );
>
>
=E2=80=8BTable "category" exists now in Database "postgres"=E2=80=8B

-- Now I am going to repeat the same script without DROP TABLE
> -- After the next line, I assume that all old references within the
> database
> are gone
> DROP DATABASE new;
>
> CREATE DATABASE new
>   WITH ENCODING=3D'LATIN1'
>        TEMPLATE=3Dtemplate0
>        LC_COLLATE=3D'C'
>        LC_CTYPE=3D'C'
>        CONNECTION LIMIT=3D-1;
>
> -- Here I leave out the line with DROP TABLE
> -- It results to the error message:
> -- ERROR: relation "category" already exists
> -- Is this a bug or am I missing something?
> -- DROP TABLE IF EXISTS category CASCADE;
>
>
=E2=80=8BSo  you dropped an added the "new" database again...while connecte=
d to
"postgres".=E2=80=8B

CREATE TABLE category
>   (
>      category_id SERIAL  PRIMARY KEY
>     ,category_name varchar(30) NOT NULL
>     ,category_description varchar(200) NOT NULL
>   );
>
>
=E2=80=8BOf course the table you created in Database "postgres" is still th=
ere...=E2=80=8B

=E2=80=8BDavid J.=E2=80=8B