Postgres 8.4 Reverse Engineer - PK index issues

Toad Data Modeler
1

I’m using the Trial version (3.6.6.7) to reverse engineer from a Postgres 8.4 database.

However virtually none of the referential constraints are coming through. The only ones I do get are where the RI is against a unique index rather than the primary key index.

The reverse engineer does alert to “no parent item (key or index) has been found during foreign key creation”. Looking on the entity properties, the Keys tab has one “Key1” item marked as a PK but double clicking it shows no attributes are selected.

In short, somehow it isn’t picking up the primary key index.

I can see it in the SQL pane in pgAdmin [eg CONSTRAINT xx_pk PRIMARY KEY (xx_id)] and I can see that the index is used for a query.

Is it likely to be a privilege issue ? Known bug in either Postgres (eg it isn’t showing in a catalog view) or TDM ?

Can anyone say what query TDM is issuing to determine the primary key / index / columns ?

2

Hi Gary,

Yes, the problem is most probably in that primary keys haven’t been loaded.
In TDM everything is correct. It would be greatly helpful if you could send SQL commands you used to create the table,
into which TDM doesn’t import a primary key, despite the key is there (please, include also SQL command which pgAdmin shows for the given table).

You can send it to modelingquest.com, in case you don’t want to display the code here on the forum.

Regards,
Lukas

3

This is the creation text out of pgAdmin:
I’ve renamed it to xx and removed a bunch of irrelevant columns.

CREATE TABLE xx
(
xx_id integer NOT NULL DEFAULT nextval(‘xx_sq’::regclass),
xx_num integer NOT NULL,
“type” character varying(10) NOT NULL,
ve_type_id integer,
CONSTRAINT xx_pk PRIMARY KEY (xx_id)
)
WITH (
OIDS=TRUE
);
ALTER TABLE xx OWNER TO …;
GRANT ALL ON TABLE xx TO …;
GRANT SELECT ON TABLE xx TO …_read_role;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xx TO …_udi_role;

CREATE UNIQUE INDEX xx_ix01
ON xx
USING btree
(xx_num);

RI on the xx_num column is getting picked up, but not on the xx_id primary key.

4

Hi Gary,

we have tried the script and haven’t found any problem there. Therefore, to find the problem, could you try some other PostgreSQL db and create tables with primary key there? Then reverse engineer and see if the problem persists and let us know, please.

Regards,
Lukas

5

Hi Gary,

in case the issue persists, could you try the following SQL queries, please?

For list of keys:

SELECT  cls.relname as "tablename",c.conname,c.conkey,c.conrelid,
            ci.relname as "indexname", ci.reloptions[1] as "str_fillfactor"
            FROM  pg_catalog.pg_constraint c
                    JOIN pg_catalog.pg_class cls ON cls.oid = c.conrelid
                    JOIN pg_catalog.pg_index i ON i.indrelid = c.conrelid,
                 pg_catalog.pg_class ci
            WHERE  c.conrelid > 0 AND
                  c.contype = 'p' AND
                  ci.relkind='i' and i.indexrelid = ci.oid and ci.relname=c.conname

For individual attributes of a key:

SELECT  a.attname,a.attnum
            FROM  pg_catalog.pg_attribute a
            WHERE  a.attrelid = CONRELID and a.attnum>0
  • where for CONRELID a number value from conrelid column should be written, which should return the preceeding query. If it returns what you expect, could you send us one of the values in c.conkey column, please?

Cheers,
Lukas

6

It looks as if it is permissions related

In the problem environment, my user gets a permission denied if I try to look at the underlying sequence object (in the example it would be xx_sq).

xx_id integer NOT NULL DEFAULT nextval(‘xx_sq’::regclass)

I’ll try to get either the permissions changed or my privileges boosted.

Thanks