[DBE] Show DDL for table doesn't work

In DB Explorer (SA v 7.0.158), option Show DDL doesn't work for tables. It shows:

	Quote:
	/* Cannot find source code for "my_schema"."my_table". Check the "DDL Code (PostgreSQL)" query in SQL Assistant Options. */

Edit PostgreSQL_ddl.bat file, and correct path to pg_dump.exe. that should do it.

I set this up but it still doesn't work.
Is there any way to get a log of how pg_dump is called by you app?

BTW I know parsing result of pg_dump is cheapest wa, but there might be some pitfalls (and other inconsistencies across your app). For example you are able to show table columns hovering over the table name without using pg_dump. But you need to use pgdump to get complete DDL. What about if user ha no pgdump installed? What if it's some older version? What if it's installed in some other than common location? Personally I would rather get this data from system tables to construct DDL.

I also noticed, that --no-password switch is used for pg_dump. What happen if password is required? (no trust is set for given user role in hba.conf of postgresql).

	Quote:
	Is there any way to get a log of how pg_dump is called by you app?

Within the batch file double the command and redirect output of one of them to a log file >> c:\mylog.log
this will give you an idea what is running including output and parameters.

pg_dump.exe is required for DDL extraction, it's used for different object types, not only for tables. Also there is big difference between column list with data types and a complete table DDL including storage, constraints, triggers, permissions, etc.... I wish PostgreSql had a better way to reverse engineer DDL without using command line tools.

Because I still couldn't get logging to work, I took ProcessMonitor (from sysinternals), and have found that SQL Editor doesn't even try to open PostgreSQL_ddl.bat.
Maybe I'm doing something wrong?
Please verify it

	Quote:
	g_dump.exe is required for DDL extraction, it's used for different object types, not only for tables. Also there is big difference between column list with data types and a complete table DDL including storage, constraints, triggers, permissions, etc.... I wish PostgreSql had a better way to reverse engineer DDL without using command line tools.

It's true that postgresql grovides no ready to use DDLs like SHOW CREATE known from MySQL. But believe me, you can construct all DDL statements using pg_ tables. Indeed, it requires more effort but at the end it's worth it.

BTW Due to some issues with applying ACLs, do not use information_schema for gathering data (for sure information_schema.schemata is affected)

Please check what you have in Options -> DB Options -> SQL Assistance -> PostgreSQL -> DDL Extraction Utility option. Please set it to [path to SA]\ddl\PostgreSQL_DDL.bat file. This way you can control and customize path to pg_dump.exe

OK. I found the reason... a few ones.

1. DDL Extraction Utility option hasn't been set. Checked and confirmed on a few our installations
2. db hostname hasn't been passed for pg_dump. missing -h %1 (I hope it's always server address)
3. path to pg_dump might be wrong (don't know if SA installer takes rid about it)

There is also potential problem with authorisation. Current solution rely on password existence in pgpass.conf file. To be honest I don't know if this file is filled by any postgresql client library or explicitly by applications. However it might happen that there will be NO password for particular db. Since that pg_dump will not connect to db. I suggest to explicitly pass the password to pg_dump using PGPASSWORD env variable:

SET PGPASSWORD=%4

But watch out a pitfal: this way you pass doublequotes (passed with bat script arguments) which makes it not working. You have to remove those doublequotes. I'm not familiar with .bat scripts, but found quick solution. So finial lines will look like this:

SET PGPASSWORD=%4
SET PGPASSWORD=%PGPASSWORD:"=%
"some_path\pg_dump.exe" -s -h %1 -U %3 -n "\"%6\"" -t "\"%7\"" %5

Thank you so much for your feedback. I will pass it to the development team and ask for their comments, I'm not an expert in pg_dump usage. But as far as I see, the password is passed via parameters, this is the same password you enter into SQL Assistant database connection dialog (please be sure you choose Save password option or it won't work)

You can use password with pg_dump in 3 ways:
1. using interacgtive password request (not available for scripting)
2. using PGPASSWORD environment variable
3. using pgpass.conf in windows (AppData\Roaming\postgresql\) or .pgpass file in linux

I suggested to use 2nd option over 3rd, because you have no guarantee that required passwords are in pgpass file. Especially, because either SQLEditor nor SA, don't put passwords into this file (I verified it). One program I know which do that is pgAdmin. But if you don;t use pgadmin and start work with new database with SQLEditor, you have no chance to get DDL using current implementation of SQLEditor.

That's why I suggested to pass the password via env variable, making it independent from pgpass file.

Thank you very much again. I passed your latest info to the development team too. Still waiting for their response.

On my end, I've got test install of local PG 9.1 and it seems to be working for me with the SQL Editor including the DDL part, not sure how, but it's working, maybe because it's a local instance, and I'm a local admin.

There are a few possibilities.
Database is configured with trust permission (especially for localhost). No credentials are needed to log in. Check hba.conf file for this.
or
Postgresql database installation program, has silently created pgpass file (try to find it somewhere in your filesystem)

Please remember, about not using --no-password switch in bat script we talked about.
Here is quote from pg_dump utility:

	Quote:
	--no-password Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

Since your bat script uses no-password switch, it's impossible to connect databases which requires password for connection, which cannot be found in pgpass file or set in PGPASSWORD env variable.

Based on your suggestions, a simple solution for the issue would be modifying PostgreSQL_ddl.bat and making 2 small changes

1. From the last line remove --no-password
2. Before the last line, insert SET PGPASSWORD=%4

This should allow the password to be passed dynamically.

Additionally, to resolve the issue with quotes, password prompts, and different PG versions, change the last line to something like
"C:\Program Files\PostgreSQL\9.3\bin\pg_dump.exe" -i -h %~1 -p %~2 -w -s -n %~6 -t %~7 %~5

Please let me know if that works for you

I believe you meant (missing tilde):

2. Before the last line, insert SET PGPASSWORD=%~4

Thank you. You are correct.

By the way, the coming very very soon 7.1 includes fixes for this issue, including cases when it's not using pg_dump.

After testing recent 7.1 beta I found following bugs in the .bat file

- missing -U switch
- missing schema name in table name. Looks like -n schema -t table doesn;t work as expected. You have to use -t schema.table

here is working command line:

"path_to_pgdump\pg_dump.exe" -i -h %~1 -U %~3 -p %~2 -w -s -n %~6 -t %~6.%~7 %~5

I left -n untouched, because does not any harm in for getting table DDL. I know nothing about other use cases.

IMO serious glitch is missing path to pg_dump after clean installation.
By default pg_dump is installed with postgresql database and/or with pgAdmin - if you would like to use one. It would require to dig into registers to get the path.
However I hope it is temporary solution, finally replaced by data taken directly from pg_catalog schema