1. Douglas Paes
  2. Valentina Studio
  3. Четверг, Март 28 2019, 02:27 PM
  4.  Подписаться через email
Hi!

I currently use version 9.1 (64-bit) of Valentina Studio on Windows 10 and the "Related tables" functionality is not working. Can you help me?

Thanks.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Douglas,

Are the tables in the same schema or different?
Комментарий
There are no comments made yet.
Douglas Paes Ответ принят
Hello Sergey,

The tables are in the same schema.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Could you please add SQL definitions of both tables?
You can use a context menu for it: "Generate SQL"->"Create"
Комментарий
There are no comments made yet.
Douglas Paes Ответ принят
Table "estado" (Create):

BEGIN;

-- CREATE TABLE "estado" ---------------------------------------
CREATE TABLE "base_corporativa"."estado" (
"id" Integer NOT NULL,
"nome" Character Varying( 200 ) COLLATE "pg_catalog"."default" NOT NULL,
"sigla" Character Varying( 2 ) COLLATE "pg_catalog"."default" NOT NULL,
"area" Numeric( 13, 4 ) NOT NULL,
"the_geom" "public"."geometry",
PRIMARY KEY ( "id" ),
CONSTRAINT "enforce_dims_the_geom" CHECK(public.st_ndims(the_geom) = 2),
CONSTRAINT "enforce_geotype_the_geom" CHECK((public.geometrytype(the_geom) = 'MULTIPOLYGON'::text) OR (the_geom IS NULL)),
CONSTRAINT "enforce_srid_the_geom" CHECK(public.st_srid(the_geom) = 4674) );
;
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "id" -----------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."id" IS 'Identificador único da entidade estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "nome" ---------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."nome" IS 'Armazena o nome do estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "sigla" --------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."sigla" IS 'Armazena a sigla do estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "area" ---------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."area" IS 'Area do estado (ha).';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "the_geom" -----------------------
COMMENT ON COLUMN "base_corporativa"."estado"."the_geom" IS 'Coluna georreferenciada.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "TABLE "estado" -------------------------
COMMENT ON TABLE "base_corporativa"."estado" IS 'Entidade responsável por armazenar os estados.';
-- -------------------------------------------------------------

-- CREATE INDEX "sidx_estado_the_geom" -------------------------
CREATE INDEX "sidx_estado_the_geom" ON "base_corporativa"."estado" USING gist( "the_geom" );
-- -------------------------------------------------------------

COMMIT;

//

Table "municipio" (Create):

BEGIN;

-- CREATE TABLE "municipio" ------------------------------------
CREATE TABLE "base_corporativa"."municipio" (
"id" Integer NOT NULL,
"nome" Character Varying( 200 ) COLLATE "pg_catalog"."default" NOT NULL,
"id_estado" Integer NOT NULL,
"area" Numeric( 13, 4 ) NOT NULL,
"the_geom" "public"."geometry",
"nome_sem_formatacao" Character Varying( 200 ) COLLATE "pg_catalog"."default",
PRIMARY KEY ( "id" ),
CONSTRAINT "enforce_dims_the_geom" CHECK(public.st_ndims(the_geom) = 2),
CONSTRAINT "enforce_geotype_the_geom" CHECK(((public.geometrytype(the_geom) = 'MULTIPOLYGON'::text) OR (public.geometrytype(the_geom) = 'POLYGON'::text)) OR (the_geom IS NULL)),
CONSTRAINT "enforce_srid_the_geom" CHECK(public.st_srid(the_geom) = 4674) );
;
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "id" -----------------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."id" IS 'Identificado único da entidade municipio.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "nome" ---------------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."nome" IS 'Armazena o nome do município.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "id_estado" ----------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."id_estado" IS 'Identificador da entidade estado que realiza o relacionamento entre as entidades municipio e estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "area" ---------------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."area" IS 'Área do município (ha).';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "the_geom" -----------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."the_geom" IS 'Coluna georreferenciada.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "nome_sem_formatacao" ------------
COMMENT ON COLUMN "base_corporativa"."municipio"."nome_sem_formatacao" IS 'Nome sem formatação.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "TABLE "municipio" ----------------------
COMMENT ON TABLE "base_corporativa"."municipio" IS 'Entidade responsável por armazenar os municipios.';
-- -------------------------------------------------------------

-- CREATE INDEX "sidx_municipio_the_geom" ----------------------
CREATE INDEX "sidx_municipio_the_geom" ON "base_corporativa"."municipio" USING gist( "the_geom" );
-- -------------------------------------------------------------

COMMIT;

BEGIN;

-- CREATE LINK "fk_m_estado" -----------------------------------
ALTER TABLE "base_corporativa"."municipio"
ADD CONSTRAINT "fk_m_estado" FOREIGN KEY ( "id_estado" )
REFERENCES "base_corporativa"."estado" ( ) MATCH SIMPLE
ON DELETE No Action
ON UPDATE No Action;
-- -------------------------------------------------------------

COMMIT;
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Thank you, for some reason, the PK field is not defined for the link fk_m_estado.
What version of PostgreSQL is used?
Комментарий
There are no comments made yet.
Douglas Paes Ответ принят
PostgresSQL 9.2.15 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Can't reproduce yet.

What if you execute the following query:
SELECT column_name FROM information_schema.constraint_column_usage WHERE constraint_schema = 'base_corporativa' AND constraint_name = 'fk_m_estado'

Do you get the primary key column?
Комментарий
There are no comments made yet.
Douglas Paes Ответ принят
Does not get.

See the attachment.
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Thank you, that's very strange.
What if you execute
SELECT * FROM information_schema.constraint_column_usage

I attached my output - id column is here.
Вложения
Комментарий
There are no comments made yet.
Douglas Paes Ответ принят
Really, very strange.

In attachment, the output for the query SELECT * FROM information_schema.constraint_column_usage
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Could you please create a SQL dump of the "municipio" table? If it contains the correct definition:
ALTER TABLE ONLY base_corporativa.municipio
ADD CONSTRAINT fk_m_estado FOREIGN KEY (id_estado) REFERENCES base_corporativa.estado(id);

then I can check the pg_dump sources to see how it gets information about the foreign keys.

Also, if it is possible to create a new test database, uncompress and load attached dump into it - is the issue reproducible (no id column for foreign key in information_schema.constraint_column_usage) ?
Вложения
Комментарий
There are no comments made yet.
Douglas Paes Ответ принят
In the dump of table "municipio" constains the definition in attachment.

About creating the test database, unfortunately I can not do it at the moment. Sorry.
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Thank you, I’ll review the pg_dump algorithms, there should be some difference.
Комментарий
There are no comments made yet.
Douglas Paes Ответ принят
Thanks, Sergey.
Комментарий
There are no comments made yet.
Douglas Paes Ответ принят
Sergey, I created the test database and gave the restore in its dump.

I ran the query SELECT * FROM information_schema.constraint_column_usage and the output is attached.
Вложения
Комментарий
There are no comments made yet.
Douglas Paes Ответ принят
I discovered the reason for the problem. It is the permission of a specific user that I was using to connect to the database.

Thanks for your help, Sergey.
Комментарий
There are no comments made yet.
  • Страница :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories