[SA 7.2.328 Pro] Postgresql - DDL issues

[SA 7.2.328 Pro] Postgresql - DDL issues

Hello
With this version I still encounter DDL issues listed bellow. Right now I don't know if those isses has been 'imported' to latest version during update process (I installed new version into new directory) or just existing in the build.

1. Overloaded functions support missing - postgresql supports overloaded functions (the same name, but different argument list). Explorer shows such function as single one, but with extended number of arguments (all args from all functions with the same name)
2. either function or table DDL missing. When I setup SQL code for DDL (in Options), it allows me to get function DDL/Edit function working, but table DDL stopped to work. I realized that SQL for DDL doesn't contain part for CREATE TABLE. I extended it by simply code (see at the end of this post), but it need to be finished. There is lack of constraints, indexes etc
3. Triggers doesn't contain trigger DDL. In Explorer, in tree Table/Triggers, trigger function DDL is available. It's fine, but there should be trigger DDL also. Or rather there should be trigger and trigger function
4. Speaking about trigger functions, I would like to see trigger functions separated from functions (like in pgAdmin). Such functions differ by one thing: return value, which is TRIGGER in case of trigger functions.
5. ACL - DLLs contains no owner and grant assignments. At least for tables (I can see these for functions)
6. in Explorer, datatypes based on tables are named "USER_DATATYPE". There should be table name.

Ahh and one more thing. Is it possible to make connection window bigger? Current one suffers of very small "Server Name" combo-box. It makes selecting desired servers very uncomfy, since most of name is hidden (especially aliases)

BTW I can see .331 build reports. Is it possible to obtain this one? Is there any list of changes?

here is the code for create table :

Code:

SELECT
'CREATE TABLE "' || t.table_schema || '"."' || t.table_name || '"
(
' || (
SELECT string_agg(colls.x,E',\n')
FROM
(
SELECT
column_name || ' ' || udt_name::regtype ||
CASE
WHEN c.character_maximum_length IS NOT NULL THEN '('|| c.character_maximum_length ||')'
ELSE ''
END ||
CASE
WHEN coalesce(c.numeric_scale,0) > 0 THEN '('|| c.numeric_precision || ',' || c.numeric_precision_radix ||')'
ELSE ''
END ||
CASE
WHEN c.datetime_precision IS NOT NULL AND c.datetime_precision<6 THEN '('|| c.datetime_precision ||')'
ELSE ''
END ||
CASE
WHEN c.is_nullable = 'NO' THEN ' NOT NULL'
ELSE ''
END ||
CASE
WHEN c.column_default IS NOT NULL THEN ' DEFAULT ' || c.column_default
ELSE ''
END x
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema -- :SCHEMA_NAME
AND c.table_name = t.table_name -- :OBJECT_NAME
ORDER BY c.ordinal_position
) colls)
|| '
)'
FROM
information_schema.tables t
WHERE
t.table_schema = :SCHEMA_NAME
AND t.table_name = :OBJECT_NAME

Please note this query is still limited regarding various aspects. Consider it as draft rather than final solution.
Also there are some issues known related to access to information_schema. I suggest to rewrite all such queries making use of tables from pg_catalog schema

PS I found some glitch while attempting to save SQL code in this post. Originally I had CASEs of numeric scale and date_time_precission reverted. But parser went crazy joining both cases doesn't matter a way I did chose to solve it.

Last edited by michalk on Mon Jun 08, 2015 12:39 pm; edited 1 time in total

Just to extend information about why to not use information schema.
If object is owned by user A, but you log into the DB with user B, you will not see this object in information schema tables. Even if you have rights to select/execute or even modify this object.
On other side this object is listed by tables in pg_catalog schema.

Another thing I found DDL related:
There are no additional attributes shown in function DDL like: COST, ROWS, Security or variables set by SET. There is snipped of such attributes:

	Code:
	... body of function... $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET "DateStyle" TO iso COST 100 ROWS 1000;

I suggest to make use of pg_get_ function family, for example:

	Code:
	SELECT pg_get_functiondef(oid) FROM pg_catalog.pg_proc pp WHERE proname = 'function_name' AND pp.pronamespace = (SELECT pn.oid FROM pg_catalog.pg_namespace pn WHERE pn.nspname='function_schema')

Note, it still doesn't solve overloaded function issue.
see more on: http://www.postgresql.org/docs/9.2/static/functions-info.html

If your license is covered by the support and maintenance, please upgrade to the most recent version 7.2.338. Simply right-click on SQL Assistant icon in the system tray area and select Check for Updates menu.

	Quote:
	1. Overloaded functions support missing - postgresql supports overloaded functions (the same name, but different argument list). Explorer shows such function as single one, but with extended number of arguments (all args from all functions with the same name)

We currently support PF overloaded functions in DB Explorer, in Schema compare and in a couple of other places where they can be shown as separate objects and we can internally refer to OID. We don't handle them in popups yet as you may expect, this is a pending enhancement which requires deep down internal changes in the SQL Assistant's object cache implementation, something that we are still working on.

	Quote:
	2. either function or table DDL missing. When I setup SQL code for DDL (in Options), it allows me to get function DDL/Edit function working, but table DDL stopped to work. I realized that SQL for DDL doesn't contain part for CREATE TABLE. I extended it by simply code (see at the end of this post), but it need to be finished. There is lack of constraints, indexes etc

normally SQL Assistant uses pg_dump for reverse engineering database code. This should work for *all* PG object types including tables. If your config uses sql queries to reverse-engineer DDL, then you use non-factory settings. Normally the setup should be as pictures on the below screenshot

	Quote:
	3. Triggers doesn't contain trigger DDL. In Explorer, in tree Table/Triggers, trigger function DDL is available. It's fine, but there should be trigger DDL also. Or rather there should be trigger and trigger function

This is likely related to the issue described in #2 above

	Quote:
	4. Speaking about trigger functions, I would like to see trigger functions separated from functions (like in pgAdmin). Such functions differ by one thing: return value, which is TRIGGER in case of trigger functions.

I have submitted an enhancement request, but don't know if it will be accepted

	Quote:
	5. ACL - DLLs contains no owner and grant assignments. At least for tables (I can see these for functions)

This is likely related to the issue described in #2 above. In my setup I get all GRANT statements as part of the object DDL.

	Quote:
	6. in Explorer, datatypes based on tables are named "USER_DATATYPE". There should be table name.

Please try customizing "Columns (PostgreSQL) + Keys" query in SQL Assistant options on DB Options tab. If you succeed, please share your updated query code as other people may find it helpful.

	Quote:
	Ahh and one more thing. Is it possible to make connection window bigger? Current one suffers of very small "Server Name" combo-box. It makes selecting desired servers very uncomfy, since most of name is hidden (especially aliases)

I see in our internal tracking system that we already have a queued enhancement for the same. It will make the the server drop-down twice as wide.

Thanx for response.

I just downloaded recent version (not available yesterday). Installation overwrote previous settings.
I had 2 things to do manually:
1. define path to PostgreSQL_ddl.bat in SA options - field was empty
2. set up proper path to pg_dump.exe

Because it still wasn't work, I dig into it, and found that SA is using pg_dump in wrong way.

This is how it pg_dump is called from the bat:

"d:\_programs\pgadmin\1.20\1.20\pg_dump.exe" -i -h cztc-pldbpre -p 5432 -s -w -U "myrole" -n "myschema" -t "tab_log" "testdb"

After some trial-errors I found that schema must be put into table definition: -t "myschema.tab_log"
We discusses it in that thread: http://www.softtreetech.com/support/phpBB2/viewtopic.php?t=24249
I give you proper syntax for it (see the last post in the thread)

However it still doesn't manage extraction of single function. SA executes pgdump in this way:

"d:\_programs\pgadmin\1.20\1.20\pg_dump.exe" -i -h cztc-pldbpre -p 5432 -s -w -U "myrole" -n "myschema" -t "add_log" "testdb"

where desired function is myschema.add_log()
It just can't work.

I'm using pg_dump version 9.4.0, delivered in package with pgadmin 1.20 (postgresql 9.4 I believe)

The pg_dump parameters and behavior differ in different PostgreSQL versions. That's why we put it into a batch file so that it can be easily customized.

I'm not an expert in pg_dump. Let me forward this question to the development team.

Here is the response to the USER-DATATYPE thing. If you want to expand it into an actual datatype name, customize two quesries in SQL Assistant options. In the Options dialog, select DB Options tab, expand DB Queries section. Locate Columns (PostgreSQL) + Keys query and select it. change the query text to the following

Code:

SELECT 'oid', 'oid', 'NP', 0 as ordinal_position
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = :SCHEMA_NAME
AND c.relname = :OBJECT_NAME
AND c.relhasoids

UNION ALL

SELECT
column_name,
REPLACE(CASE WHEN character_maximum_length is not null THEN data_type || '(' || character_maximum_length || ')'
   WHEN data_type = 'numeric' AND numeric_precision is not null THEN data_type || '(' || numeric_precision || ',' || numeric_scale || ')'
   ELSE
      CASE WHEN data_type = 'USER-DEFINED' AND udt_name IS NOT NULL
         THEN udt_schema || '.' || udt_name
         ELSE data_type
      END
   END, '"', ''),
CASE WHEN column_default LIKE 'nextval(%' THEN 'A' ELSE '' END
|| COALESCE(substr(is_nullable,1,1), 'Y') /* N,Y */
|| COALESCE(( /* P,F,U */
      select max(substr(tc.constraint_type,1,1))
      from information_schema.key_column_usage k, information_schema.table_constraints tc
      where k.constraint_schema = tc.constraint_schema and k.constraint_name = tc.constraint_name
         and k.table_schema = c.table_schema and k.table_name = c.table_name and k.column_name = c.column_name
   ),'')
|| COALESCE(( /* I */
      select max('I')
      from pg_catalog.pg_indexes i
      where i.schemaname = c.table_schema
         and i.tablename = c.table_name
         and replace(replace(replace(i.indexdef, '(', ','), ')', ','), ' ', '') LIKE ('%,' || c.column_name || ',%')
   ),' '),
ordinal_position
FROM
information_schema.columns c
WHERE
table_schema = :SCHEMA_NAME
AND table_name = :OBJECT_NAME

ORDER BY
ordinal_position

Similarly, change Arguments (PostgreSQL) query text to

Code:

select
parameter_name,
data_type,
parameter_mode || substr(specific_name, length(specific_name)-1)
from
(
select '' as parameter_name,
   replace(CASE WHEN data_type = 'USER-DEFINED' AND udt_name IS NOT NULL
            THEN type_udt_schema || '.' || type_udt_name
            ELSE data_type
         END, '"', '') as data_type,
   'R' as parameter_mode,
   0 as ordinal_position,
specific_name
from information_schema.routines
where routine_schema = :SCHEMA_NAME
and routine_name = :OBJECT_NAME

union all

select coalesce(p.parameter_name, 'unnamed parameter ' || p.ordinal_position) as parameter_name,
   replace(CASE WHEN p.data_type = 'USER-DEFINED' AND p.udt_name IS NOT NULL
            THEN p.udt_schema || '.' || p.udt_name
            ELSE p.data_type
         END, '"', ''),
   case when p.parameter_mode = 'OUT' then 'O'
when p.parameter_mode = 'INOUT' then 'U'
else 'I'
end,
   p.ordinal_position,
r.specific_name
from information_schema.parameters p
   join information_schema.routines r
   on p.specific_catalog = r.specific_catalog
   and p.specific_schema = r.specific_schema
   and p.specific_name = r.specific_name
where r.routine_schema = :SCHEMA_NAME
and r.routine_name = :OBJECT_NAME
) a
order by specific_name, ordinal_position

Thank you - it words.

One note regarding .bat script for DDL. It works for public schema, but not for other schamas.

I'm running 7.2.338 and in my test environment it works for all schemas. As you can see on my screenshots in previous posts for my test admin schema too. I'm using all factory-default options. Maybe you have some of the queries in SQL Assistant options customized and that's why you get different results. Please try reverting to factory default options (Options dialog -> Import/Export Options button -> Load Default Options -> Import)

No. it doesn't work.
I reset to default, then setup path to ddl extraction bat.
I've got tables DDL only from public schema.
It doesn't work for other schemas. And it can't due to how pgdump is called. As I explained before, pg_dump's -t parameter takes complete name of table, including it's schema.
If it works to you, please check if you don't have the table in public schema, named in the same way as table you are checking.

Here is modified pg_dump call make it working for all schemas:

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

Note, DDL for functions is still unavailable this way. Please give me some solution is possible.

Thank you. My test PG server is 9.1. I will try later today installing 9.4 and checking if I get the same results with 9.4. Also my test server is local and the pg_dump is invoked from the [server home]\bin subfolder folder

Please note that pg_dump might be installed in different locations. 2 most common are:
1. postgresql distribution (program root/bin directory)
2. pgAdmin (program root directory)

Moreoever a lot of people doesn't install programs into program files directory.

I suggest to add to installation process some configuration stage allowing setting up paths to pg_dump, or just inform about such need.

with regards