# PostgreSQL Plugin

Contents
Introduction
New Tabs
Index Details Tab
Index Source Tab
Stored Procedure Source Tab
Sequence Details Tab
Trigger Source Tab
Trigger Details Tab
View Source Tab
VACUUM
EXPLAIN ANALYZE (Query Plan)

## Authors

- Rob Manning
- Contributors

- Daniel Regli
- Yannick Winiger

## Introduction

The PostgreSQL plugin adds PostgreSQL-specific functionality to the SQuirreL SQL Client. Read access is required to the following system views in order for this additional functionality to work correctly:

- information_schema.triggers
- information_schema.views
- pg_catalog.pg_stat_all_indexes
- pg_catalog.pg_index
- pg_catalog.pg_proc
- pg_catalog.pg_namespace

Note: Some statistical counts are only available when statistics collection is enabled. The statistics collector must be configured to be launched before the server is started by setting stats_start_collector to true in postgresql.conf. (This is the default setting) Additionally, statistics need to be sent to the collector with a few more configuration items which can be made at runtime. To change these for a session the user needs to be a superuser. This can be done in postgresql.conf or on a per-session basis with the following commands:

    	set stats_block_level to on

    	set stats_row_level to on

Be aware that these settings will add overhead to query execution (which is why they are disabled by default)

## New Tabs

Stored Procedures, Triggers and Views are shown in the object tree and have a "Source" tab which displays the source of the selected object and a "Details" tab which gives PostgreSQL-specific information about the object. Sequences and Indexes are also shown in the object tree and have a details tab giving DB2-specific information about them.

### Index Details Tab

The index details tab can be accessed by navigating the object tree to the INDEX folder beneath any table that has one or multiple indexes. The index(es) will be listed by name under the table folder, and selecting one will place a details tab in the right-hand view of the session window.

The information provided by the details tab for indexes is derived by the following query:

    	select inds.schemaname as schemaname,
    	       inds.relname as tablename,
    	       i.indisunique as is_unique,
    	       i.indisprimary as is_primary_key,
    	       i.indisclustered as is_clustered,
    	       inds.idx_scan as num_index_scans,
    	       inds.idx_tup_read as num_index_entries_returned,
    	       inds.idx_tup_fetch as num_table_rows_fetched
    	from pg_catalog.pg_index i, pg_catalog.pg_stat_all_indexes inds
    	where i.indexrelid = inds.indexrelid
    	and inds.schemaname = ?
    	and inds.indexrelname = ?

### Index Source Tab

The index source tab can be accessed by navigating the object tree as above and selecting the "Source" tab which is adjacent to the "Details" tab. An example is shown below:

The information provided by the source tab for indexes is derived by the following query:

    	select indexdef
    	from pg_indexes
    	where schemaname = ?
    	and indexname = ?

### Stored Procedure Source Tab

The Stored Procedure Source tab can be accessed by navigating to a schema and then to the PROCEDURE folder beneath the schema. In the following picture, the schema is "dbcopydest"

The source code for stored procedures is derived by the following query:

    	SELECT p.prosrc FROM pg_proc p, pg_namespace n
    	where p.pronamespace = n.oid
    	and n.nspname = ?
    	and p.proname = ?

### Sequence Details Tab

Sequences will appear in the object tree under the SCHEMA folder. The details tab for sequences displays information about the selected sequence according to the system catalog. An example of this is shown in the following picture:

The information in the details tab for a sequence is derived from the following query:

    SELECT last_value, max_value, min_value, cache_value, increment_by, is_cycled
    FROM  <schema.sequencename>

### Trigger Source Tab

The source tab for triggers displays the source for a table trigger and can be accessed by navigating to the "TRIGGER" folder beneath a table object in the object tree. An example of this is shown in the following picture:

The source code for triggers is derived from the following query:

    	select 'create trigger ' || t.trigger_name || ' ' || condition_timing
    	|| ' ' || v.manip || ' on ' || event_object_table
    	|| ' for each ' || action_orientation || ' ' || action_statement as trigdef
    	FROM information_schema.triggers t,
    	(select  trigger_schema,
    	        trigger_name,
    	        rtrim(
    	            max(case when pos=0 then manip else '' end)||
    	            max(case when pos=1 then manip else '' end)||
    	            max(case when pos=2 then manip else '' end), ' or '
    	            ) as manip
    	from (
    	    select a.trigger_schema,
    	           a.trigger_name,
    	           a.event_manipulation||' or ' as manip,
    	           d.cnt,
    	           a.rnk as pos
    	    from (  select trigger_name,
    	                   trigger_schema,
    	                   event_manipulation,
    	                   (select count(distinct is1.event_manipulation)
    	                    from information_schema.triggers is1
    	                    where is2.event_manipulation < is1.event_manipulation) as rnk
    	            from information_schema.triggers is2
    	          ) a,
    	         (select trigger_schema, trigger_name, count(event_manipulation) as cnt
    	          from (
    	                select trigger_schema,
    	                       trigger_name,
    	                       event_manipulation,
    	                       (select count(distinct is3.event_manipulation)
    	                        from information_schema.triggers is3
    	                        where is4.event_manipulation < is3.event_manipulation) as rnk
    	                from information_schema.triggers is4
    	                ) y
    	          group by trigger_schema, trigger_name) d
    	    where d.trigger_name = a.trigger_name
    	    and d.trigger_schema = a.trigger_schema
    	) x
    	group by trigger_schema, trigger_name
    	order by 1) v
    	where t.trigger_schema = v.trigger_schema
    	and t.trigger_name = v.trigger_name
    	and t.trigger_schema = ?
    	and t.trigger_name = ?
    	group by trigdef

### Trigger Details Tab

The details tab for triggers displays information about the selected trigger according to the system catalog. An example of this is shown in the following picture:

The information in the details tab for a trigger is derived from the following query:

    	select condition_timing AS trigger_time,
    	       v.manip AS triggering_event,
    	       action_orientation AS granularity,
    	       event_object_table AS table_name
    	FROM information_schema.triggers t,
    	(select  trigger_schema,
    	        trigger_name,
    	        rtrim(
    	            max(case when pos=0 then manip else '' end)||
    	            max(case when pos=1 then manip else '' end)||
    	            max(case when pos=2 then manip else '' end), ' or '
    	            ) as manip
    	from (
    	    select a.trigger_schema,
    	           a.trigger_name,
    	           a.event_manipulation||' or ' as manip,
    	           d.cnt,
    	           a.rnk as pos
    	    from (  select trigger_name,
    	                   trigger_schema,
    	                   event_manipulation,
    	                   (select count(distinct is1.event_manipulation)
    	                    from information_schema.triggers is1
    	                    where is2.event_manipulation < is1.event_manipulation) as rnk
    	            from information_schema.triggers is2
    	          ) a,
    	         (select trigger_schema, trigger_name, count(event_manipulation) as cnt
    	          from (
    	                select trigger_schema,
    	                       trigger_name,
    	                       event_manipulation,
    	                       (select count(distinct is3.event_manipulation)
    	                        from information_schema.triggers is3
    	                        where is4.event_manipulation < is3.event_manipulation) as rnk
    	                from information_schema.triggers is4
    	                ) y
    	          group by trigger_schema, trigger_name) d
    	    where d.trigger_name = a.trigger_name
    	    and d.trigger_schema = a.trigger_schema
    	) x
    	group by trigger_schema, trigger_name
    	order by 1) v
    	where t.trigger_schema = v.trigger_schema
    	and t.trigger_name = v.trigger_name
    	and t.trigger_schema = ?
    	and t.trigger_name = ?

### View Source Tab

The source tab for views displays the source for a view and can be accessed by navigating to the "VIEW" folder beneath a schema object in the object tree. An example of this is shown in the following picture:

The source code for views is derived from the following query:

    	select view_definition
    	from information_schema.views
    	where table_schema = ?
    	and table_name = ?

### VACUUM

The PostgreSQL-specific VACUUM statement garbage-collects and optionally analyzes tables or a whole database.
This plugin allows it to directly generate and execute these vacuum statements in the object tree tab of squirrel.
The generated statements can optionaly contain the FULL and/or ANALYZE parameters:

- FULL: May reclaim more space, but takes much longer and exclusively locks the tables.
- ANALYZE: Updates statistics used by the planner to determine the most efficient way to execute a query.

Both vacuum dialog (for tables or the database) look almost the same and offer the same set of buttons:

- Show SQL: displays a dialog with the generated sql statement.
- Edit SQL: copys the generated sql statement into the sql panel for further editing.
- Execute: executes the database vacuumization and displays the results into an extra dialog.

Vacuum Database
To vacuum the whole database, right-click the top most node in the object tree (the database node). Access the PostgreSQL menu and select Vacuum Database.
Hint: The PostgreSQL menu can also be accessed at any time via the Session menu in Squirrels main menu bar.

The dialog above would generate the following SQL statement:

    	VACUUM FULL VERBOSE ANALYZE ;

Vacuum Table(s)
To vacuum a set of tables, right-click a selection of one or more tables in the object tree. Access the PostgreSQL menu and select Vacuum Table(s).

The dialog above would generate the following SQL statement:

    	VACUUM FULL VERBOSE ANALYZE "show"."AccountStatus";

    	VACUUM FULL VERBOSE ANALYZE "show"."CityLookup";

    	VACUUM FULL VERBOSE ANALYZE "show"."EmployeeIdentification";

### EXPLAIN ANALYZE (Query Plan)

The PostgreSQL-specific EXPLAIN ANALYZE statement shows the execution plan (query plan) of an sql statement. This plugin adds an additional executor tab to the sql panel for the display of the execution plan.

To display the execution plan of a selected statement in the sql panel, switch to the Query Plan tab.
Executing an SQL statement while the Query Plan tab is the activ executor tab, will display the query plan of the statement. (Instead of actualy executing the statement on the database.)

EXPLAIN ANALYZE can be applied to any sql statement, except for DDL related. All calls are wrapped into rolled-back transactions so investigating INSERT/DELETE/UPDATE is safe. This is important as postgres actually calls query when using ANALYZE to get actual times.

Multiple Statements
It is also possible to execute multiple statements. Each query plan for a statement will be displayed in own tab within the Query Plan tab.

Closing a Query Plan
Query Plans will stay in the Query Plan tab until removed by using one of the close action in the right-click menu or by clicking on the close icon on the upper right edge of the panel.