[BUGS] BUG #14791: Error 42P07 but the relation DOESN'T Exists! Error 42P01 - Mailing list pgsql-bugs
From | jfblazquez.ayesa@gmail.com |
---|---|
Subject | [BUGS] BUG #14791: Error 42P07 but the relation DOESN'T Exists! Error 42P01 |
Date | |
Msg-id | 20170830154553.1708.34801@wrigleys.postgresql.org Whole thread Raw |
Responses |
Re: [BUGS] BUG #14791: Error 42P07 but the relation DOESN'T Exists! Error 42P01
Re: [BUGS] BUG #14791: Error 42P07 but the relation DOESN'T Exists!Error 42P01 |
List | pgsql-bugs |
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
pgsql-bugs by date: