Toad World® Forums

Postgres DDL generation's errors


#1

Currently an order of the object’s generation for Postgresql is incorrect. I work with Postgresql 9.2 but the same issue will exist for other versions. The roles are generated almost at the end of the script, but schema is generated at the very beginning. The problem is that schema may require an authorization associated with the role like

CREATE SCHEMA “test_schema” AUTHORIZATION "test_application_admin"

This statement will fail because role “test_application_admin” wasn’t yet created. I suggest to create roles first, but to leave a section that grants permissions to the roles at the end of the DDL script.

The second problem is that script tries to create without pre-existence check even standard and pre-defined schemas and roles like

CREATE SCHEMA “public” AUTHORIZATION “postgres”
;

CREATE SCHEMA “pg_catalog”
;

CREATE ROLE “postgres” LOGIN
;

These statements will just generate error messages.


#2

Hello Sergei,

I suggest to create roles first, but to leave a section that grants permissions to the roles at the end of the DDL script.

Thanks for your suggestion. CR TDM-357 created.

The second problem is that script tries to create without pre-existence check even standard and pre-defined schemas…

Let me explain - TDM generates DDL script from model. It doesn’t check objects that/if exist in database.
In DDL script generator (F9), you can set up OTPs and select what objects you want to include in the DDL script - see tabs What to Generate and Select List.

Feel free to post your suggestion to the Idea Pond and let other users vote or comment on it.

Thanks.

Regards, Vladka


#3

Hi Vladka,

I didn’t mean to check existence of all objects in DDL script. My point was that TDM trying to create roles “postgres” and “public”. It is like an attempt to create an user SYS or PUBLIC in Oracle. hey are create when POSTGRESQL is installed, and I don’t think they even can be dropped. The statement CREATE ROLE “postgres” LOGIN; is guaranteed to create an error message.


#4

Hello Sergei,

Thanks for your explanation. I’ve created a new TDM-362 . We will work on this.

Thanks again!

Regards,

Vladka + TDM Team


#5

How to generate DDL’s for Postgresql 9.* databases using TOAD Datamodeler freeware ?


#6

Hello,

You generate DDL scripts in Postgresql models just as in any other models. That is either by clicking the Generate button on Model Toolbar or by selecting Model Menu | Generate DDL Script.

sol1.png

sol2.png

For further information I recommend checking the application Help | Projects and Models | Models | Physical Data Model | Script Generation.
Also please note that the freeware versions has certain limitations. For example, you can only generate first 25 entities as DDL script. The limitations are described in detail in this document.

Regards

Lukas