[BUGS] BUG #14791: Error 42P07 but the relation DOESN'T Exists! Error 42P01

The following bug has been logged on the website:

Bug reference:      14791
Logged by:          Juan Francisco Blázquez Martínez
Email address:      jfblazquez.ayesa@gmail.com
PostgreSQL version: 9.4.1
Operating system:   Windows 7 SP1 x32
Description:

I was creating tables using scripts via pgAdmin when it suddenly crashed
while creating a table: "scada_equipment_instance"

After restarting pgAdmin and psql service, I can't create or use
"scada_equipment_instance" table anymore. It says:

ERROR: la relación «scada_equipment_instance» ya existe
SQL state: 42P07

or 

ERROR: no existe la relación «scada_equipment_instance»
SQL state: 42P01

I've tried in a different database, but I have still the same problem (see
code below), even using command line psql (instead pgadmin).

What should I do?
Thank you!

========================================
SCRIPT:
========================================


CREATE TABLE scada_facility_type
( x_scada_facility_type serial NOT NULL, -- identificador interno d_name character varying(50), -- nombre del tipo de
instalaciónd_code character(1), -- código del tipo de instalación para generar los 
TAGS en SCADA CONSTRAINT pk_scada_facility_type PRIMARY KEY (x_scada_facility_type)
)
WITH ( OIDS=FALSE
);
ALTER TABLE scada_facility_type OWNER TO postgres;
COMMENT ON TABLE scada_facility_type IS 'Contiene los códigos de instalación para generar los TAGs en SCADA';
COMMENT ON COLUMN scada_facility_type.x_scada_facility_type IS
'identificador interno';
COMMENT ON COLUMN scada_facility_type.d_name IS 'nombre del tipo de
instalación';
COMMENT ON COLUMN scada_facility_type.d_code IS 'código del tipo de
instalación para generar los TAGS en SCADA';


CREATE TABLE scada_facility_instance
( x_scada_facility_instance serial NOT NULL, -- identificador interno d_name character varying(50), -- nombre de la
instalaciónd_number character(3), -- código secuencial de instalación para generar 
los TAGS en SCADA facility_type_x_facility_type integer NOT NULL, CONSTRAINT pk_scada_facility_instance PRIMARY KEY
(x_scada_facility_instance), CONSTRAINT fk_scada_facility_instance_scada_facility_type FOREIGN KEY
(facility_type_x_facility_type)     REFERENCES scada_facility_type (x_scada_facility_type) MATCH SIMPLE     ON UPDATE
CASCADEON DELETE CASCADE
 
)
WITH ( OIDS=FALSE
);
ALTER TABLE scada_facility_instance OWNER TO postgres;
COMMENT ON TABLE scada_facility_instance IS 'Contiene la lista de instalaciones para generar los TAGs en SCADA';
COMMENT ON COLUMN scada_facility_instance.x_scada_facility_instance IS
'identificador interno';
COMMENT ON COLUMN scada_facility_instance.d_name IS 'nombre de la
instalación';
COMMENT ON COLUMN scada_facility_instance.d_number IS 'código secuencial de
instalación para generar los TAGS en SCADA';


CREATE TABLE scada_facility_area_instance
( x_scada_facility_area_instance serial NOT NULL, -- identificador interno facility_instance_x_facility_instance
integerNOT NULL, d_name character varying(50), -- nombre del área d_code character(3) NOT NULL, -- código del área para
generarlos TAGS en 
SCADA d_number character(3) NOT NULL, -- código secuencial del área de
instalación para generar los TAGS en SCADA CONSTRAINT pk_scada_facility_area_instance PRIMARY KEY
(x_scada_facility_area_instance), CONSTRAINT fk_scada_facility_area_instance_facility_instance FOREIGN KEY
(facility_instance_x_facility_instance)     REFERENCES scada_facility_instance (x_scada_facility_instance) MATCH
SIMPLE     ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH ( OIDS=FALSE
);
ALTER TABLE scada_facility_area_instance OWNER TO postgres;
COMMENT ON TABLE scada_facility_area_instance IS 'Contiene las áreas asociadas a cada instalación para generar las
señales de SCADA';
COMMENT ON COLUMN
scada_facility_area_instance.x_scada_facility_area_instance IS
'identificador interno';
COMMENT ON COLUMN scada_facility_area_instance.d_name IS 'nombre del
área';
COMMENT ON COLUMN scada_facility_area_instance.d_code IS 'código del área
para generar los TAGS en SCADA';
COMMENT ON COLUMN scada_facility_area_instance.d_number IS 'código
secuencial del área de instalación para generar los TAGS en SCADA';


CREATE TABLE scada_equipment_type
( x_scada_equipment_type serial NOT NULL, -- identificador interno d_name character varying(50), -- nombre del equipo
d_codecharacter(2), -- código del equipo para generar los TAGS en SCADA CONSTRAINT pk_scada_equipment_type PRIMARY KEY
(x_scada_equipment_type)
)
WITH ( OIDS=FALSE
);
ALTER TABLE scada_equipment_type OWNER TO postgres;
COMMENT ON TABLE scada_equipment_type IS 'Contiene los códigos de equipos para generar los TAGs en SCADA';
COMMENT ON COLUMN scada_equipment_type.x_scada_equipment_type IS
'identificador interno';
COMMENT ON COLUMN scada_equipment_type.d_name IS 'nombre del equipo';
COMMENT ON COLUMN scada_equipment_type.d_code IS 'código del equipo para
generar los TAGS en SCADA';

CREATE TABLE scada_equipment_instance
( x_scada_equipment_instance serial NOT NULL, -- identificador interno facility_area_instance_x_facility_area_instance
integerNOT NULL, equipment_type_x_equipment_type integer NOT NULL, -- Referencia al tipo de 
equipo d_number character(3) NOT NULL, -- código secuencial del equipo de
instalación para generar los TAGS en SCADA d_name character varying(50), -- nombre del equipo CONSTRAINT
pk_scada_equipment_instancePRIMARY KEY 
(x_scada_equipment_instance), CONSTRAINT fk_scada_equipment_instance_facility_area_instance FOREIGN KEY
(facility_area_instance_x_facility_area_instance)     REFERENCES scada_facility_area_instance
(x_scada_facility_area_instance) MATCH SIMPLE     ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT
scada_equipment_instanceUNIQUE 
(facility_area_instance_x_facility_area_instance,
equipment_type_x_equipment_type, d_number)
)
WITH ( OIDS=FALSE
);
ALTER TABLE scada_equipment_instance OWNER TO postgres;
COMMENT ON TABLE scada_equipment_instance IS 'Contiene los equipos asociados a cada área de instalación para generar
las señales de SCADA';
COMMENT ON COLUMN scada_equipment_instance.x_scada_equipment_instance IS
'identificador interno';
COMMENT ON COLUMN scada_equipment_instance.d_name IS 'nombre del equipo';
COMMENT ON COLUMN scada_equipment_instance.equipment_type_x_equipment_type
IS 'Referencia al tipo de equipo';
COMMENT ON COLUMN scada_equipment_instance.d_number IS 'código secuencial
del área de instalación para generar los TAGS en SCADA';


========================================
OUTPUT:
========================================


c:\PostgreSQL\9.4\bin>psql.exe -U postgres
psql (9.4.1)
ADVERTENCIA: El código de página de la consola (850) difiere del código           de página de Windows (1252).
Digite «help» para obtener ayuda.

postgres=# CREATE DATABASE "TEST"
postgres-#   WITH OWNER = postgres
postgres-#        ENCODING = 'UTF8'
postgres-#        TABLESPACE = pg_default
postgres-#        LC_COLLATE = 'Spanish_Spain.1252'
postgres-#        LC_CTYPE = 'Spanish_Spain.1252'
postgres-#        CONNECTION LIMIT = -1;
CREATE DATABASE
postgres=#
postgres=# \connect TEST
ADVERTENCIA: El código de página de la consola (850) difiere del código           de página de Windows (1252).
Ahora está conectado a la base de datos «TEST» con el usuario
«postgres».
TEST=# \d
No se encontraron relaciones.
TEST=#
TEST=# CREATE TABLE scada_facility_type
TEST-# (
TEST(#   x_scada_facility_type serial NOT NULL, -- identificador interno
TEST(#   d_name character varying(50), -- nombre del tipo de instalación
TEST(#   d_code character(1), -- código del tipo de instalación para generar
los TAGS en SCADA
TEST(#   CONSTRAINT pk_scada_facility_type PRIMARY KEY
(x_scada_facility_type)
TEST(# )
TEST-# WITH (
TEST(#   OIDS=FALSE
TEST(# );
CREATE TABLE
TEST=# ALTER TABLE scada_facility_type
TEST-#   OWNER TO postgres;
ALTER TABLE
TEST=# COMMENT ON TABLE scada_facility_type
TEST-#   IS 'Contiene los códigos de instalación para generar los TAGs en
SCADA';
COMMENT
TEST=# COMMENT ON COLUMN scada_facility_type.x_scada_facility_type IS
'identificador interno';
COMMENT
TEST=# COMMENT ON COLUMN scada_facility_type.d_name IS 'nombre del tipo de
instalación';
COMMENT
TEST=# COMMENT ON COLUMN scada_facility_type.d_code IS 'código del tipo de
instalación para generar los TAGS en SCADA';
COMMENT
TEST=#
TEST=#
TEST=# CREATE TABLE scada_facility_instance
TEST-# (
TEST(#   x_scada_facility_instance serial NOT NULL, -- identificador
interno
TEST(#   d_name character varying(50), -- nombre de la instalación
TEST(#   d_number character(3), -- código secuencial de instalación para
generar los TAGS en SCADA
TEST(#   facility_type_x_facility_type integer NOT NULL,
TEST(#   CONSTRAINT pk_scada_facility_instance PRIMARY KEY
(x_scada_facility_instance),
TEST(#   CONSTRAINT fk_scada_facility_instance_scada_facility_type FOREIGN
KEY (facility_type_x_facility_type)
TEST(#       REFERENCES scada_facility_type (x_scada_facility_type) MATCH
SIMPLE
TEST(#       ON UPDATE CASCADE ON DELETE CASCADE
TEST(# )
TEST-# WITH (
TEST(#   OIDS=FALSE
TEST(# );
CREATE TABLE
TEST=# ALTER TABLE scada_facility_instance
TEST-#   OWNER TO postgres;
ALTER TABLE
TEST=# COMMENT ON TABLE scada_facility_instance
TEST-#   IS 'Contiene la lista de instalaciones para generar los TAGs en
SCADA';
COMMENT
TEST=# COMMENT ON COLUMN scada_facility_instance.x_scada_facility_instance
IS 'identificador interno';
COMMENT
TEST=# COMMENT ON COLUMN scada_facility_instance.d_name IS 'nombre de la
instalación';
COMMENT
TEST=# COMMENT ON COLUMN scada_facility_instance.d_number IS 'código
secuencial de instalación para generar los TAGS en SCADA';
COMMENT
TEST=#
TEST=#
TEST=# CREATE TABLE scada_facility_area_instance
TEST-# (
TEST(#   x_scada_facility_area_instance serial NOT NULL, -- identificador
interno
TEST(#   facility_instance_x_facility_instance integer NOT NULL,
TEST(#   d_name character varying(50), -- nombre del área
TEST(#   d_code character(3) NOT NULL, -- código del área para generar los
TAGS en SCADA
TEST(#   d_number character(3) NOT NULL, -- código secuencial del área de
instalación para generar los TAGS en SCADA
TEST(#   CONSTRAINT pk_scada_facility_area_instance PRIMARY KEY
(x_scada_facility_area_instance),
TEST(#   CONSTRAINT fk_scada_facility_area_instance_facility_instance
FOREIGN KEY (facility_instance_x_facility_instance)
TEST(#       REFERENCES scada_facility_instance (x_scada_facility_instance)
MATCH SIMPLE
TEST(#       ON UPDATE NO ACTION ON DELETE NO ACTION
TEST(# )
TEST-# WITH (
TEST(#   OIDS=FALSE
TEST(# );
CREATE TABLE
TEST=# ALTER TABLE scada_facility_area_instance
TEST-#   OWNER TO postgres;
ALTER TABLE
TEST=# COMMENT ON TABLE scada_facility_area_instance
TEST-#   IS 'Contiene las áreas asociadas a cada instalación para generar
las señales de SCADA';
COMMENT
TEST=# COMMENT ON COLUMN
scada_facility_area_instance.x_scada_facility_area_instance IS
'identificador interno';
COMMENT
TEST=# COMMENT ON COLUMN scada_facility_area_instance.d_name IS 'nombre del
área';
COMMENT
TEST=# COMMENT ON COLUMN scada_facility_area_instance.d_code IS 'código del
área para generar los TAGS en SCADA';
COMMENT
TEST=# COMMENT ON COLUMN scada_facility_area_instance.d_number IS 'código
secuencial del área de instalación para generar los TAGS en SCADA';
COMMENT
TEST=#
TEST=#
TEST=# CREATE TABLE scada_equipment_type
TEST-# (
TEST(#   x_scada_equipment_type serial NOT NULL, -- identificador interno
TEST(#   d_name character varying(50), -- nombre del equipo
TEST(#   d_code character(2), -- código del equipo para generar los TAGS en
SCADA
TEST(#   CONSTRAINT pk_scada_equipment_type PRIMARY KEY
(x_scada_equipment_type)
TEST(# )
TEST-# WITH (
TEST(#   OIDS=FALSE
TEST(# );
CREATE TABLE
TEST=# ALTER TABLE scada_equipment_type
TEST-#   OWNER TO postgres;
ALTER TABLE
TEST=# COMMENT ON TABLE scada_equipment_type
TEST-#   IS 'Contiene los códigos de equipos para generar los TAGs en
SCADA';
COMMENT
TEST=# COMMENT ON COLUMN scada_equipment_type.x_scada_equipment_type IS
'identificador interno';
COMMENT
TEST=# COMMENT ON COLUMN scada_equipment_type.d_name IS 'nombre del
equipo';
COMMENT
TEST=# COMMENT ON COLUMN scada_equipment_type.d_code IS 'código del equipo
para generar los TAGS en SCADA';
COMMENT
TEST=#
TEST=# \d                                     Listado de relacionesEsquema |                             Nombre
                    | Tipo    |  Due├▒o
 
---------+-----------------------------------------------------------------+-----------+----------public  |
scada_equipment_type                                           | 
tabla     | postgrespublic  | scada_equipment_type_x_scada_equipment_type_seq                 |
secuencia | postgrespublic  | scada_facility_area_instance                                    |
tabla     | postgrespublic  | scada_facility_area_instance_x_scada_facility_area_instance_seq |
secuencia | postgrespublic  | scada_facility_instance                                         |
tabla     | postgrespublic  | scada_facility_instance_x_scada_facility_instance_seq           |
secuencia | postgrespublic  | scada_facility_type                                             |
tabla     | postgrespublic  | scada_facility_type_x_scada_facility_type_seq                   |
secuencia | postgres
(8 filas)


TEST=#
TEST=# CREATE TABLE scada_equipment_instance
TEST-# (
TEST(#   x_scada_equipment_instance serial NOT NULL, -- identificador
interno
TEST(#   facility_area_instance_x_facility_area_instance integer NOT NULL,
TEST(#   equipment_type_x_equipment_type integer NOT NULL, -- Referencia al
tipo de equipo
TEST(#   d_number character(3) NOT NULL, -- código secuencial del equipo de
instalación para generar los TAGS en SCADA
TEST(#   d_name character varying(50), -- nombre del equipo
TEST(#   CONSTRAINT pk_scada_equipment_instance PRIMARY KEY
(x_scada_equipment_instance),
TEST(#   CONSTRAINT fk_scada_equipment_instance_facility_area_instance
FOREIGN KEY (facility_area_instance_x_facility_area_instance)
TEST(#       REFERENCES scada_facility_area_instance
(x_scada_facility_area_instance) MATCH SIMPLE
TEST(#       ON UPDATE NO ACTION ON DELETE NO ACTION,
TEST(#   CONSTRAINT scada_equipment_instance UNIQUE
(facility_area_instance_x_facility_area_instance,
equipment_type_x_equipment_type, d_number)
TEST(# )
TEST-# WITH (
TEST(#   OIDS=FALSE
TEST(# );
ERROR:  la relación «scada_equipment_instance» ya existe
TEST=# ALTER TABLE scada_equipment_instance
TEST-#   OWNER TO postgres;
ERROR:  no existe la relación «scada_equipment_instance»

========================================




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs