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ón
d_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ón
d_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 CASCADE ON 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 integer NOT NULL,
d_name character varying(50), -- nombre del área
d_code character(3) NOT NULL, -- código del área para generar los 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_code character(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 integer NOT 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_instance PRIMARY 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_instance UNIQUE
(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 relaciones
Esquema | Nombre |
Tipo | Due├▒o
---------+-----------------------------------------------------------------+-----------+----------
public | scada_equipment_type |
tabla | postgres
public | scada_equipment_type_x_scada_equipment_type_seq |
secuencia | postgres
public | scada_facility_area_instance |
tabla | postgres
public | scada_facility_area_instance_x_scada_facility_area_instance_seq |
secuencia | postgres
public | scada_facility_instance |
tabla | postgres
public | scada_facility_instance_x_scada_facility_instance_seq |
secuencia | postgres
public | scada_facility_type |
tabla | postgres
public | 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