Tested version 11.

The DDL needs to be working.  For example:  In version 10, I create a function go to Schema Script Generator and see the function, See attached screenshot for what is missing.

Script Object to window, nothing happens.

See screenshot.

The script function to windows executes the following SQL. It seems that the "proiswindow" column does not exist in PostgreSQL 11.

select
	pn.nspname,
	proname,
	prosrc,
	lanname,
	proargtypes,
	prosecdef,
	proisstrict,
	provolatile,
	proargnames,
	proretset,
	procost,
	prorows,
	proconfig ,
	proiswindow ,
	proleakproof ,
	proparallel ,
	description,
	u.rolname 
from
	pg_catalog.pg_proc pp 
		INNER JOIN pg_catalog.pg_language pl 
		ON pp.prolang = pl.oid 
			INNER JOIN pg_namespace pn 
			ON pp.pronamespace = pn.oid 
				LEFT JOIN pg_description pd 
				ON pp.oid = pd.objoid 
					LEFT JOIN pg_catalog.pg_roles u 
					on pp.proowner = u.oid 
where
	proname = 'fx_hello_world' and
	pn.nspname = 'public' and
	proargtypes = '23 23' 
order by
	proname

The schema script generator executes the following SQL when "Functions" is checked. It seems that the "pg_catalog.pg_proc.prolang" column does not exists in PostgreSQL 11.

select
	pn.nspname,
	proname,
	prosrc,
	lanname,
	proargtypes,
	pp.proname || '_' || pp.oid AS specific_name,
	proargnames 
from
	pg_catalog.pg_proc pp,
	pg_catalog.pg_language pl,
	pg_namespace pn,
	pg_catalog.pg_type t 
where
	pp.prolang = pl.oid and
	pp.pronamespace = pn.oid and
	pp.prorettype=t.oid and
	(pl.lanname != 'internal' AND
	pl.lanname != 'c') and
	pn.nspname = 'public' and
	t.typname != 'void' and
	pp.proisagg != true 
order by
	proname

Add prokind column, replacing proisagg and proiswindow

The new column distinguishes normal functions, procedures, aggregates,
and window functions.  This replaces the existing columns proisagg and
proiswindow, and replaces the convention that procedures are indicated
by prorettype == 0.  Also change prorettype to be VOIDOID for procedures.

SQL procedures can now be created using the CREATE PROCEDURE command, executed using the CALL command, and are supported by the server-side procedural languages PL/pgSQL, PL/Perl, PL/Python, and PL/Tcl using Postgres version 11. The ADS procedure node will have to be changed to support procedures just for version 11. This should include the create, alter and drop procedure gui as well as any scripting support. There should now be a clear distinction between procedures and functions for version 11. Scripting will also have to change for "Script Object to Window" and Schema Script Generator"

Postgres version 11 now has an include column when using CREATE INDEX. That should be added to ADS.

You will need to test functionality across the entire ADS tool for this database and fix any issues as a result of this testing.

Hi Akshit, 

I know that you are still working on this but just wanted to point out a few minor details... When you generate the extract sql in either ExtractFunction or ExtractProcedure, remember to check the spacing.  

It is still in progress and not completed yet . Added the following changes

1. spacing in SQL queries

2. changing the result set to handle appropriate data type

Hi Tom ,

I have made the UI for include columns in Postgres11 , the screenshot is attached above , I will be changing the background for include columns to white .

Is this UI is good to go , or need some changes.

Hi Tom ,

I have made the UI for include columns in Postgres11 , the screenshot is attached above , I will be changing the background for include columns to white .

Is this UI is good to go , or need some changes.

Hi Akshit,

A better approach would be to add a tab between Options and Comments called "Include Columns".  Inside that tab, format the fields similar to the way Grouping does.  This will make it a little more consistent visually and separate out the include columns since there might be a lot of columns. The obviously behavior would be... if there are any columns checked, include clause should be added in the Create Index statement. 

Remember, that these fields also need to be populated when showing Index Properties and Alter Properties. 

Thanks, Tom

Revision #56921

Can we make the statement for indvidual version in a separate line. Similar issue in ExtractFunction, ExtractProcedure

sql += (connSettings.isPostgreSQL(ConnectionProperties.MATCH_EQUAL_OR_GREATER,ConnectionProperties.VERSION_POSTGRESQL_11_0))?", prokind":((connSettings.isPostgreSQL(ConnectionProperties.MATCH_EQUAL_OR_GREATER, ConnectionProperties.VERSION_POSTGRESQL_8_4)) ? ", proiswindow " : ", false as proiswindow ");

Hi Akshit,

A couple tweaks... Please use the column icon in the "Include Columns" tab - Elegant.get(Elegant.TREE_CONN_COLUMN) 

Also, please remove the word checkbox from the column heading.

See here.

Notice the mechanics of the group menu - Selection colors, gray checkbox.  Please set your menu up that way  for consistency. Have a look at the group menu.

We should also add an "Include" to the right of the column in the schema tree so that it is clear which is key and which is include. See here. See PGIndexColumnsNode->getSelect()

Thanks,

Tom

See issue #10690. For Postgres  version 11, we should support creating procedures for the procedure object in the schema tree.

Index properties and alter menu's are not displaying the include columns. See here.

Scripting Procedures is not working. See here.

Query Builder is not showing include indexes like the schema tree. See here. Asif can give you direction on this.

SchemaScriptGenerator is not supporting procedures. See here.

Hi QA Team, 

Please regression test Postgres 10 and make sure none of the changes have affected the behavior.

Thanks, Tom

FYI - Had a call with Akshit and walked through the changes needed to make Tom's feedback.

>> Query Builder is not showing include indexes like the schema tree. See here. Asif can give you direction on this.

As discussed attached patch on how to add INCLUDE column information in QB > Schema tree

Hi Akshit, 

Getting the following exception when I open index alter or index properties or script object to window for index  tom->tomtest->zxzxc. Looks like the query is coded incorrectly. See here.

Please have a look..

Thanks, Tom

Revision: 57007

Author: Darpan Kochar

1. Added procedure options to schema tree
2. Scripting support for procedures
3. Fixed SchemaScriptGenerator issue

Hi Akshit,

Have a look at this scenario. I created a function in Postgres 11 that returns a void. It is getting placed in the procedures node. I faintly remember that in the previous releases, since Postgres didn't have procedures, we would categorize functions that return a void as procedures where as functions that return a value were considered a function. I could be wrong; didn't get a chance to look at the code. For Postgres 11 only, we need to change that flow so that all functions are grouped in the function node. Please have a look.

Thanks, Tom

SVN revision: 57009

1. Fixed void function issue for postgreSQL 11

Hi Tom,

We are done with all the changes from dev side.

In procedures there are two attributes that cannot be supported. First one is SECURITY INVOKER which cannot be supported because it is not stored in the database properties so we cannot retrieve it. The other one is TRANSFORM which is not exposed to users at the SQL level because of security concerns. 

These two attributes are part of Postgres Functions as well and these were not supported by ADS in Postgres Functions.

Thanks

Darpan

Hi Darpan, 

Looks good. Please mark this issue as resolved.

Hi QA Team, 

Please regression test Postgres 10 also and make sure none of the changes have affected the behavior. Also, please do some quick sanity checks for procedures/functions for the RDS Postgres. This version is equivalent  to Postgres 10.

Thanks, Tom

Hi Darpan,

The functions in Schema Sync are showing up as procedures. See here. Please have a look.

Thanks, Tom

Hi Tom,

Committed the fix for the issue in revision: 57019

Thanks

Darpan

Hi Darpan and Tom,

Can you please confirm whether we are supporting renaming procedures in Postgres11 ?
I was trying to alter the procedure name and found out that procedure name textfield is ineditable.

Thanks,
Hitesh

Hi Hitesh,

We provided the functionalities taking the reference of MySQL and as i can see, renaming of procedures in alter procedure is not supported for MySQL and Oracle.

Tom can you please verify if this is required or not.

Thanks 

Darpan

Postgres 11 support alter procedure SQL syntax to rename procedure, so ideally we should support the rename option.

https://www.postgresql.org/docs/11/sql-alterprocedure.html

>> renaming of procedures in alter procedure is not supported for MySQL and Oracle.

There is no SQL support to rename procedure for MySQL and Oracle.

I agree with Asif that we should support renaming in Alter Procedure for Postgres 11. SQL Server supports this. Please take a look at that.

Hi Darpan,

I agree with Nhi who agree's with Asif. You can have a look at the gui for alter stored procedures for SQL Server 2017. In there, you will see that the name field is editable which allows for the name to change.. You can use that approach. Verify the name is different from the original and code in the commands for the name change.

Hope that helps,

Tom

Hi Team, 

Since we re-categorized procedures in Schema Sync for version 11 with this change, we will no longer be able to compare procedures and functions with accuracy between pre version 11 and and post version 11. In version 10 and below, procedures were really functions returning voids. In version 11, these are now classified as functions. Just keep that in mind when testing Schema Sync and Schema Compare.

Thanks, Tom

Added support for new commands for mongo/postgres, which comes of 7 test cases.

Added Postgres test cases

QE was completed. Please find the details below.

https://idera.testrail.net/index.php?/runs/view/2710&group_by=cases:section_id&group_order=asc

https://idera.testrail.net/index.php?/runs/view/2696&group_by=cases:section_id&group_order=asc