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

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

From

jfblazquez.ayesa@gmail.com

Date:

30 August 2017, 21:45:53

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

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

From

Tom Lane

Date:

30 August 2017, 23:56:51

jfblazquez.ayesa@gmail.com writes:
> TEST=# CREATE TABLE scada_equipment_instance
> ...
> TEST(#   CONSTRAINT scada_equipment_instance UNIQUE
> ...
> ERROR:  la relación «scada_equipment_instance» ya existe

The problem here is that that unique constraint has to have an underlying
index, and the index will be named the same as the constraint, causing it
to collide with the table name.  So this error is really coming from
the implied CREATE INDEX command: it sees a conflicting relation name
already in place.  After the whole command rolls back, of course you
have no table either, so this is unsurprising:

> TEST=# ALTER TABLE scada_equipment_instance
> TEST-#   OWNER TO postgres;
> ERROR:  no existe la relación «scada_equipment_instance»

Short answer is that unique/pkey constraints can't be named the same as
any table in the same schema.  Personally I'd leave off the "CONSTRAINT
name" part altogether and let the system pick a nonconflicting index name.

(Not sure that our documentation is sufficiently clear on this.
Since it's not really what you'd expect from reading the SQL standard,
maybe we need to mention it in more places than we do now.)
        regards, tom lane

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

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

From

Alvaro Herrera

Date:

31 August 2017, 05:46:28

jfblazquez.ayesa@gmail.com wrote:

> 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

[Translation: relation "scada_equipment_instance" already exists]

Juan Francisco followed up to the moderator address to indicate that he
found the problem -- there's a constraint that uses the same name as the
table, causing the error.

Case closed.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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