Re: BUG #15473: Incorrect error when executing a certain create table statement through psql

st 31. 10. 2018 v 17:03 odesílatel PG Bug reporting form <
noreply(at)postgresql(dot)org> napsal:

> The following bug has been logged on the website:
>
> Bug reference: 15473
> Logged by: Erki Eessaar
> Email address: erki(dot)eessaar(at)taltech(dot)ee
> PostgreSQL version: 11.0
> Operating system: CentOS release 6.10 (Final)
> Description:
>
> I use psql.
>
> I execute the following statements without a problem.
>
> CREATE TABLE riik (
> riik_kood character varying(3) NOT NULL,
> nimetus character varying(150) NOT NULL,
> CONSTRAINT pk_riik PRIMARY KEY (riik_kood),
> CONSTRAINT ak_riik_nimetus UNIQUE (nimetus),
> CONSTRAINT chk_riik_nimetus_pole_tyhikutest_ega_tyhi CHECK
> (nimetus::text !~ '^[[:space:]]*$'::text),
> CONSTRAINT chk_riik_riik_kood_koosneb_kolmest_suurest_tahest CHECK
> (riik_kood::text ~ '^[A-Z]{3}$'::text)
> );
>
>
> CREATE TABLE isiku_seisundi_liik(
> isiku_seisundi_liik_kood smallint NOT NULL,
> nimetus character varying(100) NOT NULL,
> CONSTRAINT pk_isiku_seisundi_liik PRIMARY KEY
> (isiku_seisundi_liik_kood),
> CONSTRAINT ak_isiku_seisundi_liik_nimetus UNIQUE (nimetus),
> CONSTRAINT chk_isiku_seisundi_liik_nimetus_pole_tyhikutest_ega_tyhi
> CHECK (nimetus::text !~ '^[[:space:]]*$'::text),
> CONSTRAINT
> chk_isiku_seisundi_liik_isiku_seisundi_liik_kood_positiivne_arv CHECK
> (isiku_seisundi_liik_kood > 0)
> );
>
> Now, I want to execute a CREATE TABLE statement to create table Isik
> (Person) that references by using foreign keys to the tables Riik (Country)
> and Isiku_seisundi_liik (Person_state_type).
>
> CREATE TABLE Isik
> (
> isik_id serial NOT NULL ,
> isikukoodi_riik varchar(3) NOT NULL,
> isiku_seisundi_liik_kood smallint NOT NULL DEFAULT 1,
> isikukood varchar(50) NOT NULL,
> e_meil varchar(254) NOT NULL,
> parool varchar(60) NOT NULL,
> synni_kp date NOT NULL,
> reg_aeg timestamp NOT NULL DEFAULT localtimestamp(0),
> elukoht varchar(3000) NOT NULL,
> eesnimi varchar(3000) ,
> perenimi varchar(3000) ,
> CONSTRAINT PK_Isik PRIMARY KEY (isik_id),
> CONSTRAINT AK_Isik_e_meil UNIQUE (e_meil),
> CONSTRAINT AK_Isik_isukukood_riik_kood UNIQUE
> (isikukood,isikukoodi_riik),
> CONSTRAINT chk_Isik_eesnimi_perenimi_voi_molemad CHECK ((eesnimi
> Is Not
> Null) Or (perenimi Is Not Null)),
> CONSTRAINT chk_Isik_isikukood_pole_tyhikutest_ega_tyhi CHECK
> (isikukood!~'^[[:space:]]*$'),
> CONSTRAINT chk_Isik_e_meil_sisaldab_uhte_at_marki CHECK
> (e_meil~'^[a-zA-Z0-9]+@([a-zA-Z0-9]+\.)+[a-zA-Z0-9]{2,4}$'),
> CONSTRAINT chk_Isik_eesnimi_pole_tyhikutest_ega_tyhi CHECK
> (eesnimi!~'^[[:space:]]*$'),
> CONSTRAINT chk_Isik_perenimi_pole_tyhikutest_ega_tyhi CHECK
> (perenimi!~'^[[:space:]]*$'),
> CONSTRAINT chk_Isik_synni_kp_vaartuste_vahemik CHECK
> (synni_kp>='1900-01-01' AND synni_kp<'2101-01-01'),
> CONSTRAINT chk_Isik_reg_aeg_vaartuste_vahemik CHECK
> (reg_aeg>='2010-01-01'
> AND reg_aeg<'2101-01-01'),
> CONSTRAINT chk_Isik_elukoht_pole_tyhikutest_ega_tyhi CHECK
> (elukoht!~'^[[:space:]]*$'),
> CONSTRAINT chk_Isik_elukoht_ei_koosne_ainult_numbritest CHECK
> (elukoht!~'^[[:digit:]]*$'),
> CONSTRAINT chk_Isik_reg_aeg_suurem_kui_synni_kp CHECK
> (reg_aeg>=synni_kp),
> CONSTRAINT chk_Isik_isikukoodis_lubatud_symbolid CHECK
> (isikukood~*'^[a-z0-9 \/-]+$'),
> CONSTRAINT FK_Isik_Isiku_seisundi_liik FOREIGN KEY
> (isiku_seisundi_liik_kood) REFERENCES Isiku_seisundi_liik
> (isiku_seisundi_liik_kood) ON DELETE No Action ON UPDATE Cascade,
> CONSTRAINT FK_Isik_Riik FOREIGN KEY (isikukoodi_riik) REFERENCES
> Riik
> (riik_kood) ON DELETE No Action ON UPDATE Cascade
> )
> ;
>
> I expect the statement to succeed. However, I get an error message
> instead:
>
> ERROR: syntax error at or near "CONSTRAINT"
> LINE 29: SET CONSTRAINT FK_Isik_Riik FOREIGN KEY (isikukoodi_riik) RE...
>
> If I try to execute the same statement through pgAdmin (ver 4.3) query
> tool,
> then it is completed without a problem and the table is created.
>
> If I remove one or both foreign key declarations from the CREATE TABLE Isik
> ... statement, then the execution of the CREATE TABLE Isik ... statement
> succeeds in psql.
>
> The result of SELECT Version();
> PostgreSQL 11.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313
> (Red Hat 4.4.7-23), 64-bit
>
>
> Sorry for having table names not in English (in Estonian). I tried to
> replicate the bug with artificially created statements.
>
> CREATE TABLE A(a_id INTEGER PRIMARY KEY);
> CREATE TABLE B(b_id INTEGER PRIMARY KEY);
>
> CREATE TABLE C(c_id INTEGER PRIMARY KEY,
> a_id INTEGER NOT NULL,
> b_id INTEGER NOT NULL,
> CONSTRAINT ak_c UNIQUE (a_id, b_id),
> CONSTRAINT fk_c_a FOREIGN KEY (a_id) REFERENCES A(a_id) ON DELETE No Action
> ON UPDATE Cascade,
> CONSTRAINT fk_c_b FOREIGN KEY (b_id) REFERENCES B(b_id) ON DELETE No Action
> ON UPDATE Cascade);
>
> However, executing these through psql succeeds without a problem.
>
>
> The same problem appears in a machine with Ubuntu 18.04 and PostgreSQL
> 10.5.
>
> The problem DOES NOT APPEAR (i.e., CREATE TABLE Isik ... succeeds in psql)
> in a machine with CentOS release 5.7 (Final)
> and PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc
> (GCC)
> 4.1.2 2
> 0080704 (Red Hat 4.1.2-44), 64-bit
>

please, can you attach files with commands that allows reproducing of this
bug. I checked example from this mail without any problem.

maybe, you use some specific char in name or some similar.

Regards

Pavel