#13126: Postgres 9.3 -> Support -> Materialized views

ivangron reported 2015-03-06T17:49:37Z · last modified 2016-02-02T03:52:37Z

Postgres 9.3 -> Support -> Materialized views

Dev	ivangron
QA	tariqrahiman

Priority	Major
Complexity	Unknown

Component	DB - PostgreSQL
Version	17.0

Postgres 9.3 added a new DDLs for supporting MATERIALIZED VIEWS object. To support we need to create the tree nodes, extraction & scripting of the objects and add the object type to the object search tool.

To create a MATERIALIZED VIEW, use the below SQL to first create the table and then the mat view based on the table:

1. CREATE TABLE matviews (

mv_name NAME NOT NULL PRIMARY KEY

, v_name NAME NOT NULL

, last_refresh TIMESTAMP WITH TIME ZONE

);

2. CREATE OR REPLACE FUNCTION create_matview(NAME, NAME)

RETURNS VOID

SECURITY DEFINER

LANGUAGE plpgsql AS '

DECLARE

matview ALIAS FOR $1;

view_name ALIAS FOR $2;

entry matviews%ROWTYPE;

BEGIN

SELECT * INTO entry FROM matviews WHERE mv_name = matview;

IF FOUND THEN

RAISE EXCEPTION ''Materialized view ''''%'''' already exists.'',

matview;

END IF;

EXECUTE ''REVOKE ALL ON '' || view_name || '' FROM PUBLIC'';

EXECUTE ''GRANT SELECT ON '' || view_name || '' TO PUBLIC'';

EXECUTE ''CREATE TABLE '' || matview || '' AS SELECT * FROM '' || view_name;

EXECUTE ''REVOKE ALL ON '' || matview || '' FROM PUBLIC'';

EXECUTE ''GRANT SELECT ON '' || matview || '' TO PUBLIC'';

INSERT INTO matviews (mv_name, v_name, last_refresh)

VALUES (matview, view_name, CURRENT_TIMESTAMP);

RETURN;

END

3. They also added EVENT TRIGGERS. would need to create a tree node, extraction and scripting of this object as well.

15 attachments

13126_Schema Script Generator.png

2015-12-16T09:05:37Z

169 KB
13126_Schema Compare.png

2015-12-16T09:05:37Z

131 KB
13126_Script_MV.png

2015-12-16T09:05:37Z

220 KB
13126_Detail Pane.png

2015-12-16T11:26:33Z

83 KB
13126_ADS17-ga-5_Tree node.png

2015-12-18T08:39:21Z

94 KB
13126_ADS17-ga-5_MultipleObject Scripting.png

2015-12-18T08:39:21Z

136 KB
13126_ADS17-ga-5_Scripting.png

2015-12-18T08:39:21Z

108 KB
13126_ADS17-ga-7_Object Search.png

2015-12-18T08:39:22Z

127 KB
13126_ADS17-ga-5_SchemaScriptGenerator.png

2015-12-18T08:39:22Z

167 KB
13126_ADS17-ga-7_Keywords.png

2015-12-18T08:41:20Z

90 KB
13126_ADS17-ga-7_ServerConnection_SecondaryFilter.png

2015-12-18T08:41:20Z

124 KB
13126_ADS17-ga-7_Detail Pane.png

2015-12-18T08:41:20Z

83 KB
13126_ADS17-ga-7_ServerConnection_Filter.png

2015-12-18T08:41:20Z

140 KB
13126_ADS17-ga-7_SchemaCompare.png

2015-12-18T08:41:21Z

148 KB
Test cases.xlsx

2015-12-18T08:42:13Z

18 KB

All Comments (7) Change History

ivangron 2015-12-15T21:48:00Z · (edited)

will add the following:

a.) Materialized Views Tree Node -- complete

b.) Materialized Views extraction code -- complete

c.) Materialized Views scripting code -- complete

d.) Materialized Views will be added to auto completion -- Currently Not Supported

e.) Materialized View support to Schema Object script generator & Multi-Object Scripting -- complete

f.) Materialized View to Object Search: [Object Names] & [Column Names] -- complete

g.) Materialized View Detail View -- complete

h.) Server Connections Filter Type -- complete

i.) SQL syntax coloring for support keywords to editors -- completK.

j.) Add materialized views to query builder -- Currently Not Supported

ivangron 2015-12-15T21:50:43Z

initial support for postgresql materialized view is now implemented.

nishtha(*) 2015-12-16T11:26:13Z · (edited)

In ADS 17.0.0-ga-4,

Issue -1 :
java.lang.NullPointerException occurs if we try to script materialized View and if Select Materialized Views in 'Schema Script Generator' and 'Schema Compare' windows.

Steps to reproduce the issue :
1) Connect to PostgreSQL 9.4.2 DB.
2) Create below Table in any schema.

CREATE TABLE "invoice" (
    invoice_no    integer        PRIMARY KEY,
    seller_no     integer,       -- ID of salesperson
    invoice_date date,          -- date of sale
    invoice_amt   numeric(13,2) -- amount of sale
);

3) Create a Materialized View on table 'invoice' :
CREATE MATERIALIZED VIEW "sales_summary" AS
SELECT
      seller_no,
      invoice_date,
      sum(invoice_amt)::numeric(13,2) as sales_amt
    FROM invoice
    WHERE invoice_date < CURRENT_DATE
    GROUP BY
      seller_no,
      invoice_date
    ORDER BY
      seller_no,
      invoice_date;

4) Create a Index on it.
CREATE UNIQUE INDEX "sales_summary_seller"
ON "sales_summary" (seller_no, invoice_date);

5) Go to "Materialized Views" tree node -> Right click on Created Materialized View -> Select "Script Object to Windows As - > CREATE/DROP" option.

Actual Result : A pop-up appears with java.lang.NullPointerException.

java.lang.NullPointerException
   at \\.\\.\\हिñçêČάй語简�?한\\.Fꐆꐦꃩ⣒10char.v(Unknown Source)
   at \\.\\.\\हिñçêČάй語简�?한\\.Fꐆꐦꃩ⣒10char.Q(Unknown Source)
   at \\.\\.\\हिñçêČάй語简�?한\\.hꌜ⢥ꃍᡞ.a(Unknown Source)
   at \\.\\.\\हिñçêČάй語简�?한\\.hꌜ⢥ꃍᡞ.a(Unknown Source)
   at \\.\\.\\हिñçêČάй語简�?한\\.i⢲ᛒꇔ⡋2.a(Unknown Source)
   at \\.\\.\\हिñçêČάй語简�?한\\.tꍑᚵꃌꍉenumBoolean.a(Unknown Source)
   at com.aquafold.datastudio.mainframe.ActionHandler.actionPerformed(Unknown Source)
   at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
   at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2346)
   at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
   at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
   at javax.swing.AbstractButton.doClick(AbstractButton.java:376)
   at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:833)
   at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:877)
   at java.awt.Component.processMouseEvent(Component.java:6525)
   at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
   at java.awt.Component.processEvent(Component.java:6290)
   at java.awt.Container.processEvent(Container.java:2234)
   at java.awt.Component.dispatchEventImpl(Component.java:4881)
   at java.awt.Container.dispatchEventImpl(Container.java:2292)
   at java.awt.Component.dispatchEvent(Component.java:4703)
   at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4898)
   at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4533)
   at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4462)
   at java.awt.Container.dispatchEventImpl(Container.java:2278)
   at java.awt.Window.dispatchEventImpl(Window.java:2750)
   at java.awt.Component.dispatchEvent(Component.java:4703)
   at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:751)
   at java.awt.EventQueue.access$500(EventQueue.java:97)
   at java.awt.EventQueue$3.run(EventQueue.java:702)
   at java.awt.EventQueue$3.run(EventQueue.java:696)
   at java.security.AccessController.doPrivileged(Native Method)
   at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
   at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:86)
   at java.awt.EventQueue$4.run(EventQueue.java:724)
   at java.awt.EventQueue$4.run(EventQueue.java:722)
   at java.security.AccessController.doPrivileged(Native Method)
   at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
   at java.awt.EventQueue.dispatchEvent(EventQueue.java:721)
   at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
   at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
   at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
   at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
   at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
   at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

Same exception occurs when we select "MAterialized Views" in "Schema script Generator" and "Schema Compare' window.
When we try to Script Multiple Materialized Views same exception occurs.

Please refer attached screen-shots "13126_Script_MV.png" , "13126_Schema Script Generator.png" and "13126_Schema Compare.png".

Issue -2 : Materialized Views are not displayed in Detail Pane.
Select "Materialized Views" tree node.

In Detail Pane list of materialized views is not displayed.

Please refer attached screen-shot "13126_Detail Pane.png".

In ADS 17.0.0-ga-4,

5) Go to "Materialized Views" tree node -> Right click on Created Materialized View -> Select "Script Object to Windows As - > CREATE/DROP" option.

Actual Result : A pop-up appears with java.lang.NullPointerException.

Same exception occurs when we select "MAterialized Views" in "Schema script Generator" and "Schema Compare' window.
When we try to Script Multiple Materialized Views same exception occurs.

Please refer attached screen-shots "13126_Script_MV.png" , "13126_Schema Script Generator.png" and "13126_Schema Compare.png".

Issue -2 : Materialized Views are not displayed in Detail Pane.
Select "Materialized Views" tree node.

In Detail Pane list of materialized views is not displayed.

Please refer attached screen-shot "13126_Detail Pane.png".

ivangron 2015-12-16T17:04:08Z

Certain elements of the object require string manipulation and we needed to check to make sure the object was not null before attempting to work with it. issue resolved.

nishtha(*) 2015-12-18T08:37:21Z

Verified in ADS17.0.0-ga-5.

Verified for below options :
a.) Materialized Views Tree Node
b.) Materialized Views extraction code
Refer attached screen-shot "13126_ADS17-ga-5_Tree node.png".

c.) Materialized Views scripting code
Refer attached screen-shot "13126_ADS17-ga-5_Scripting.png".

e.) Materialized View support to Schema Object script generator & Multi-Object Scripting
Refer attached screen-shot "13126_ADS17-ga-5_SchemaScriptGenerator.png" and "13126_ADS17-ga-5_MultipleObjectScripting.png".

f.) Materialized View to Object Search: [Object Names] & [Column Names]
Refer attached screen-shot "13126_ADS17-ga-7_Object Search.png".

g.) Materialized View Detail View
Refer attached screen-shot "13126_ADS17-ga-7_Detail Pane.png".

h.) Server Connections Filter Type
Refer attached screen-shot "13126_ADS17-ga-7_ServerConnection_Filters.png" and "13126_ADS17-ga-7_ServerConnection_SecondaryFilter.png"

i.) SQL syntax coloring for support keywords to editors
Refer attached screen-shot "13126_ADS17-ga-7_Keywords.png".

For all test cases of Materialized views please refer attached "Test cases.xlsx" file.

tariqrahiman 2015-12-21T21:08:38Z

Verified in ADS v17.0.0-ga-9

raghavsingh 2016-02-02T03:52:37Z

Verified in ADS-18.0.0-dev-38

Search Tips

Aqua Data Studio / nhilam

Postgres 9.3 -> Support -> Materialized views

15 attachments

Issue #13126

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Postgres 9.3 -> Support -> Materialized views

15 attachments

Issue #13126

Completion