public inbox for [email protected]
help / color / mirror / Atom feedFrom: Aditya Toshniwal <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin][RM5181] Add support for parameters toast_tuple_target and parallel_workers of table
Date: Mon, 30 Mar 2020 19:47:13 +0530
Message-ID: <CAM9w-_k7QzXDVsbVc5DQ-5sgOqJbnK3uBnAAZdWDG=Eo=n3q=w@mail.gmail.com> (raw)
Hi Hackers,
Attached is a patch to add support for parameters toast_tuple_target (PG
v11+) and parallel_workers(PG 9.6+) of table. Refer -
https://www.postgresql.org/docs/11/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
.
Both the parameters are added in the advanced tab of table dialog.
The patch also fixes a related issue - RM5180, where autovacuum_enabled
parameter is added automatically in the RE-SQL when table has been created
with WITH parameter.
Please review.
--
Thanks and Regards,
Aditya Toshniwal
pgAdmin Hacker | Sr. Software Engineer | EnterpriseDB India | Pune
"Don't Complain about Heat, Plant a TREE"
Attachments:
[application/octet-stream] RM5181_5180.patch (62.9K, 3-RM5181_5180.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js
index 61761794f..f1dd27dc5 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js
@@ -291,6 +291,8 @@ define('pgadmin.node.table', [
triggercount: undefined,
relpersistence: undefined,
fillfactor: undefined,
+ toast_tuple_target: undefined,
+ parallel_workers: undefined,
reloftype: undefined,
typname: undefined,
labels: undefined,
@@ -779,6 +781,26 @@ define('pgadmin.node.table', [
return m.inSchema();
},
},{
+ id: 'toast_tuple_target', label: gettext('Toast tuple target'), type: 'int',
+ mode: ['create', 'edit'], min: 128, min_version: 110000,
+ group: gettext('advanced'),
+ disabled: function(m) {
+ if(m.get('is_partitioned')) {
+ return true;
+ }
+ return m.inSchema();
+ },
+ },{
+ id: 'parallel_workers', label: gettext('Parallel workers'), type: 'int',
+ mode: ['create', 'edit'], group: gettext('advanced'), min_version: 90600,
+ disabled: function(m) {
+ if(m.get('is_partitioned')) {
+ return true;
+ }
+ return m.inSchema();
+ },
+ },
+ {
id: 'relhasoids', label: gettext('Has OIDs?'), cell: 'switch',
type: 'switch', mode: ['properties', 'create', 'edit'],
group: gettext('advanced'),
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/10_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/10_plus/create.sql
index 478e6d752..dafc87aa2 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/10_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/10_plus/create.sql
@@ -77,7 +77,8 @@ CACHE {{c.seqcache|int}} {% endif %}
{% endif %}
WITH (
OIDS = {% if data.relhasoids %}TRUE{% else %}FALSE{% endif %}{% if data.fillfactor %},
- FILLFACTOR = {{ data.fillfactor }}{% endif %}{% if data.autovacuum_custom %},
+ FILLFACTOR = {{ data.fillfactor }}{% endif %}{% if data.parallel_workers is defined and data.parallel_workers != '' and data.parallel_workers != None %},
+ parallel_workers = {{ data.parallel_workers }}{% endif %}{% if data.autovacuum_custom %},
autovacuum_enabled = {% if data.autovacuum_enabled %}TRUE{% else %}FALSE{% endif %}{% endif %}{% if data.toast_autovacuum %},
toast.autovacuum_enabled = {% if data.toast_autovacuum_enabled %}TRUE{% else %}FALSE{% endif %}
{% endif %}{% if data.autovacuum_enabled and data.vacuum_table|length > 0 %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/10_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/10_plus/properties.sql
index e9bb22827..3d31fe60e 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/10_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/10_plus/properties.sql
@@ -27,6 +27,7 @@ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS r
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
(CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') = 'true')
THEN true ELSE false END) AS autovacuum_enabled,
substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
@@ -56,7 +57,7 @@ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS r
typ.typrelid AS typoid,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
-- Added for pgAdmin4
- (CASE WHEN array_length(rel.reloptions, 1) > 0 THEN true ELSE false END) AS autovacuum_custom,
+ (array_to_string(rel.reloptions, ',') like '%autovacuum%') AS autovacuum_custom,
(CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum,
(SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/create.sql
new file mode 100644
index 000000000..caec8f520
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/create.sql
@@ -0,0 +1,187 @@
+{% import 'macros/schemas/security.macros' as SECLABEL %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{% import 'columns/macros/security.macros' as COLUMN_SECLABEL %}
+{% import 'columns/macros/privilege.macros' as COLUMN_PRIVILEGE %}
+{% import 'tables/sql/macros/constraints.macro' as CONSTRAINTS %}
+{% import 'types/macros/get_full_type_sql_format.macros' as GET_TYPE %}
+{#===========================================#}
+{#====== MAIN TABLE TEMPLATE STARTS HERE ======#}
+{#===========================================#}
+{#
+ If user has not provided any details but only name then
+ add empty bracket with table name
+#}
+{% set empty_bracket = ""%}
+{% if data.coll_inherits|length == 0 and data.columns|length == 0 and not data.typname and not data.like_relation and data.primary_key|length == 0 and data.unique_constraint|length == 0 and data.foreign_key|length == 0 and data.check_constraint|length == 0 and data.exclude_constraint|length == 0 %}
+{% set empty_bracket = "\n(\n)"%}
+{% endif %}
+CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE {{conn|qtIdent(data.schema, data.name)}}{{empty_bracket}}
+{% if data.typname %}
+ OF {{ data.typname }}
+{% endif %}
+{% if data.like_relation or data.coll_inherits or data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 or data.foreign_key|length > 0 or data.check_constraint|length > 0 or data.exclude_constraint|length > 0 %}
+(
+{% endif %}
+{% if data.like_relation %}
+ LIKE {{ data.like_relation }}{% if data.like_default_value %}
+
+ INCLUDING DEFAULTS{% endif %}{% if data.like_constraints %}
+
+ INCLUDING CONSTRAINTS{% endif %}{% if data.like_indexes %}
+
+ INCLUDING INDEXES{% endif %}{% if data.like_storage %}
+
+ INCLUDING STORAGE{% endif %}{% if data.like_comments %}
+
+ INCLUDING COMMENTS{% endif %}{% if data.columns|length > 0 %},
+{% endif %}
+
+{% endif %}
+{### Add columns ###}
+{% if data.columns and data.columns|length > 0 %}
+{% for c in data.columns %}
+{% if c.name and c.cltype %}
+ {% if c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% endif %}{{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval is defined and c.defval is not none and c.defval != '' %} DEFAULT {{c.defval}}{% endif %}
+{% if c.colconstype == 'i' and c.attidentity and c.attidentity != '' %}
+{% if c.attidentity == 'a' %} GENERATED ALWAYS AS IDENTITY{% elif c.attidentity == 'd' %} GENERATED BY DEFAULT AS IDENTITY{% endif %}
+{% if c.seqincrement or c.seqcycle or c.seqincrement or c.seqstart or c.seqmin or c.seqmax or c.seqcache %} ( {% endif %}
+{% if c.seqcycle is defined and c.seqcycle %}
+CYCLE {% endif %}{% if c.seqincrement is defined and c.seqincrement|int(-1) > -1 %}
+INCREMENT {{c.seqincrement|int}} {% endif %}{% if c.seqstart is defined and c.seqstart|int(-1) > -1%}
+START {{c.seqstart|int}} {% endif %}{% if c.seqmin is defined and c.seqmin|int(-1) > -1%}
+MINVALUE {{c.seqmin|int}} {% endif %}{% if c.seqmax is defined and c.seqmax|int(-1) > -1%}
+MAXVALUE {{c.seqmax|int}} {% endif %}{% if c.seqcache is defined and c.seqcache|int(-1) > -1%}
+CACHE {{c.seqcache|int}} {% endif %}
+{% if c.seqincrement or c.seqcycle or c.seqincrement or c.seqstart or c.seqmin or c.seqmax or c.seqcache %}){% endif %}
+{% endif %}
+{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+{% endif %}
+{# Macro to render for constraints #}
+{% if data.primary_key|length > 0 %}{% if data.columns|length > 0 %},{% endif %}
+{{CONSTRAINTS.PRIMARY_KEY(conn, data.primary_key[0])}}{% endif %}{% if data.unique_constraint|length > 0 %}{% if data.columns|length > 0 or data.primary_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.UNIQUE(conn, data.unique_constraint)}}{% endif %}{% if data.foreign_key|length > 0 %}{% if data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.FOREIGN_KEY(conn, data.foreign_key)}}{% endif %}{% if data.check_constraint|length > 0 %}{% if data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 or data.foreign_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.CHECK(conn, data.check_constraint)}}{% endif %}{% if data.exclude_constraint|length > 0 %}{% if data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 or data.foreign_key|length > 0 or data.check_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.EXCLUDE(conn, data.exclude_constraint)}}{% endif %}
+{% if data.like_relation or data.coll_inherits or data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 or data.foreign_key|length > 0 or data.check_constraint|length > 0 or data.exclude_constraint|length > 0 %}
+
+){% endif %}{% if data.relkind is defined and data.relkind == 'p' %} PARTITION BY {{ data.partition_scheme }} {% endif %}
+
+{### If we are inheriting it from another table(s) ###}
+{% if data.coll_inherits %}
+ INHERITS ({% for val in data.coll_inherits %}{% if loop.index != 1 %}, {% endif %}{{val}}{% endfor %})
+{% endif %}
+WITH (
+ OIDS = {% if data.relhasoids %}TRUE{% else %}FALSE{% endif %}{% if data.fillfactor %},
+ FILLFACTOR = {{ data.fillfactor }}{% endif %}{% if data.parallel_workers is defined and data.parallel_workers != '' and data.parallel_workers != None %},
+ parallel_workers = {{ data.parallel_workers }}{% endif %}{% if data.toast_tuple_target is defined and data.toast_tuple_target != '' and data.toast_tuple_target != None %},
+ toast_tuple_target = {{ data.toast_tuple_target }}{% endif %}{% if data.autovacuum_custom %},
+ autovacuum_enabled = {% if data.autovacuum_enabled %}TRUE{% else %}FALSE{% endif %}{% endif %}{% if data.toast_autovacuum %},
+ toast.autovacuum_enabled = {% if data.toast_autovacuum_enabled %}TRUE{% else %}FALSE{% endif %}
+{% endif %}{% if data.autovacuum_enabled and data.vacuum_table|length > 0 %}
+{% for opt in data.vacuum_table %}{% if opt.name and opt.value %}
+,
+ {{opt.name}} = {{opt.value}}{% endif %}
+{% endfor %}{% endif %}{% if data.toast_autovacuum_enabled and data.vacuum_toast|length > 0 %}
+{% for opt in data.vacuum_toast %}{% if opt.name and opt.value %}
+,
+ toast.{{opt.name}} = {{opt.value}}{% endif %}
+{% endfor %}{% endif %}
+
+{### SQL for Tablespace ###}
+{% if data.spcname %}
+)
+TABLESPACE {{ conn|qtIdent(data.spcname) }};
+{% else %}
+);
+{% endif %}
+{### Alter SQL for Owner ###}
+{% if data.relowner %}
+
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ OWNER to {{conn|qtIdent(data.relowner)}};
+{% endif %}
+{### Security Labels on Table ###}
+{% if data.seclabels and data.seclabels|length > 0 %}
+
+{% for r in data.seclabels %}
+{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }}
+{% endfor %}
+{% endif %}
+{### ACL on Table ###}
+{% if data.relacl %}
+
+{% for priv in data.relacl %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{### SQL for COMMENT ###}
+{% if data.description %}
+COMMENT ON TABLE {{conn|qtIdent(data.schema, data.name)}}
+ IS {{data.description|qtLiteral}};
+{% endif %}
+{#===========================================#}
+{#====== MAIN TABLE TEMPLATE ENDS HERE ======#}
+{#===========================================#}
+{#===========================================#}
+{# COLUMN SPECIFIC TEMPLATES STARTS HERE #}
+{#===========================================#}
+{% if data.columns and data.columns|length > 0 %}
+{% for c in data.columns %}
+{% if c.description %}
+
+COMMENT ON COLUMN {{conn|qtIdent(data.schema, data.name, c.name)}}
+ IS {{c.description|qtLiteral}};
+{% endif %}
+{### Add variables to column ###}
+{% if c.attoptions and c.attoptions|length > 0 %}
+
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ {{ VARIABLE.SET(conn, 'COLUMN', c.name, c.attoptions) }}
+
+{% endif %}
+{### Alter column statistics value ###}
+{% if c.attstattarget is defined and c.attstattarget > -1 %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ ALTER COLUMN {{conn|qtTypeIdent(c.name)}} SET STATISTICS {{c.attstattarget}};
+
+{% endif %}
+{### Alter column storage value ###}
+{% if c.attstorage is defined and c.attstorage != c.defaultstorage %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ ALTER COLUMN {{conn|qtTypeIdent(c.name)}} SET STORAGE {%if c.attstorage == 'p' %}
+PLAIN{% elif c.attstorage == 'm'%}MAIN{% elif c.attstorage == 'e'%}
+EXTERNAL{% elif c.attstorage == 'x'%}EXTENDED{% endif %};
+
+{% endif %}
+{### ACL ###}
+{% if c.attacl and c.attacl|length > 0 %}
+
+{% for priv in c.attacl %}
+{{ COLUMN_PRIVILEGE.APPLY(conn, data.schema, data.name, c.name, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{### Security Lables ###}
+{% if c.seclabels and c.seclabels|length > 0 %}
+
+{% for r in c.seclabels %}
+{{ COLUMN_SECLABEL.APPLY(conn, 'COLUMN',data.schema, data.name, c.name, r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% endfor %}
+{% endif %}
+{#===========================================#}
+{# COLUMN SPECIFIC TEMPLATES ENDS HERE #}
+{#===========================================#}
+{#======================================#}
+{# CONSTRAINTS SPECIFIC TEMPLATES #}
+{#======================================#}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.primary_key)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.unique_constraint)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.foreign_key)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.check_constraint)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.exclude_constraint)}}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/properties.sql
new file mode 100644
index 000000000..3ac7ceae8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/properties.sql
@@ -0,0 +1,77 @@
+SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE
+ (SELECT sp.spcname FROM pg_database dtb
+ JOIN pg_tablespace sp ON dtb.dattablespace=sp.oid
+ WHERE dtb.oid = {{ did }}::oid)
+ END) as spcname,
+ (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as schema,
+ pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, rel.relkind,
+ (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
+ rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
+ EXISTS(select 1 FROM pg_trigger
+ JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
+ JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
+ WHERE tgrelid=rel.oid) AS isrepl,
+ (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
+ (SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
+ quote_ident(nspname)||'.'||quote_ident(c.relname)
+ ELSE quote_ident(c.relname) END AS inherited_tables
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
+ (SELECT count(*)
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
+ (CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
+ substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
+ substring(array_to_string(rel.reloptions, ',') FROM 'toast_tuple_target=([0-9]*)') AS toast_tuple_target,
+ (CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS autovacuum_enabled,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
+ (CASE WHEN (substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS toast_autovacuum_enabled,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
+ array_to_string(rel.reloptions, ',') AS table_vacuum_settings_str,
+ array_to_string(tst.reloptions, ',') AS toast_table_vacuum_settings_str,
+ rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype,
+ CASE WHEN typ.typname IS NOT NULL THEN (select quote_ident(nspname) FROM pg_namespace WHERE oid = {{scid}}::oid )||'.'||quote_ident(typ.typname) ELSE typ.typname END AS typname,
+ typ.typrelid AS typoid,
+ (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
+ -- Added for pgAdmin4
+ (array_to_string(rel.reloptions, ',') like '%autovacuum%') AS autovacuum_custom,
+ (CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum,
+
+ (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
+ (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table
+ -- Added for partition table
+ {% if tid %}, (CASE WHEN rel.relkind = 'p' THEN pg_get_partkeydef({{ tid }}::oid) ELSE '' END) AS partition_scheme {% endif %}
+FROM pg_class rel
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=rel.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
+ LEFT OUTER JOIN pg_class tst ON tst.oid = rel.reltoastrelid
+ LEFT JOIN pg_type typ ON rel.reloftype=typ.oid
+WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = {{ scid }}::oid
+AND NOT rel.relispartition
+{% if tid %} AND rel.oid = {{ tid }}::oid {% endif %}
+ORDER BY rel.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/update.sql
new file mode 100644
index 000000000..0f27dbed2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/11_plus/update.sql
@@ -0,0 +1,245 @@
+{% import 'macros/schemas/security.macros' as SECLABEL %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{#####################################################}
+{## Rename table ##}
+{#####################################################}
+{% if data.name and data.name != o_data.name %}
+ALTER TABLE {{conn|qtIdent(o_data.schema, o_data.name)}}
+ RENAME TO {{conn|qtIdent(data.name)}};
+
+{% endif %}
+{#####################################################}
+{## Change table schema ##}
+{#####################################################}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER TABLE {{conn|qtIdent(o_data.schema, data.name)}}
+ SET SCHEMA {{conn|qtIdent(data.schema)}};
+
+{% endif %}
+{#####################################################}
+{## Change table owner ##}
+{#####################################################}
+{% if data.relowner and data.relowner != o_data.relowner %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ OWNER TO {{conn|qtIdent(data.relowner)}};
+
+{% endif %}
+{#####################################################}
+{## Update Inherits table definition ##}
+{#####################################################}
+{% if data.coll_inherits_added|length > 0 %}
+{% for val in data.coll_inherits_added %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ INHERIT {{val}};
+
+{% endfor %}
+{% endif %}
+{% if data.coll_inherits_removed|length > 0 %}
+{% for val in data.coll_inherits_removed %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ NO INHERIT {{val}};
+
+{% endfor %}
+{% endif %}
+{#####################################################}
+{## Change hasOID attribute of table ##}
+{#####################################################}
+{% if data.relhasoids is defined and data.relhasoids != o_data.relhasoids %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET {% if data.relhasoids %}WITH{% else %}WITHOUT{% endif %} OIDS;
+
+{% endif %}
+{#####################################################}
+{## Change tablespace ##}
+{#####################################################}
+{% if data.spcname and data.spcname != o_data.spcname %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET TABLESPACE {{conn|qtIdent(data.spcname)}};
+
+{% endif %}
+{#####################################################}
+{## change fillfactor settings ##}
+{#####################################################}
+{% if data.fillfactor and data.fillfactor != o_data.fillfactor %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET (FILLFACTOR={{data.fillfactor}});
+{% elif (data.fillfactor == '' or data.fillfactor == None) and data.fillfactor != o_data.fillfactor %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ RESET (FILLFACTOR);
+
+{% endif %}
+
+{## change parallel_workers settings ##}
+{#####################################################}
+{% if (data.parallel_workers == '' or data.parallel_workers == None) and data.parallel_workers != o_data.parallel_workers %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ RESET (parallel_workers);
+{% elif data.parallel_workers is defined and data.parallel_workers != o_data.parallel_workers %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET (parallel_workers={{data.parallel_workers}});
+
+{% endif %}
+
+{## change toast_tuple_target settings ##}
+{#####################################################}
+{% if (data.toast_tuple_target == '' or data.toast_tuple_target == None) and data.toast_tuple_target != o_data.toast_tuple_target %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ RESET (toast_tuple_target);
+{% elif data.toast_tuple_target is defined and data.toast_tuple_target != o_data.toast_tuple_target %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET (toast_tuple_target={{data.toast_tuple_target}});
+
+{% endif %}
+
+{###############################}
+{## Table AutoVacuum settings ##}
+{###############################}
+{% if data.vacuum_table is defined and data.vacuum_table.set_values|length > 0 %}
+{% set has_vacuum_set = true %}
+{% endif %}
+{% if data.vacuum_table is defined and data.vacuum_table.reset_values|length > 0 %}
+{% set has_vacuum_reset = true %}
+{% endif %}
+{% if o_data.autovacuum_custom and data.autovacuum_custom == false %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET (
+ autovacuum_enabled,
+ autovacuum_analyze_scale_factor,
+ autovacuum_analyze_threshold,
+ autovacuum_freeze_max_age,
+ autovacuum_vacuum_cost_delay,
+ autovacuum_vacuum_cost_limit,
+ autovacuum_vacuum_scale_factor,
+ autovacuum_vacuum_threshold,
+ autovacuum_freeze_min_age,
+ autovacuum_freeze_table_age
+);
+{% else %}
+{% if data.autovacuum_enabled is defined or has_vacuum_set %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} SET (
+{% if data.autovacuum_enabled is defined and data.autovacuum_enabled != o_data.autovacuum_enabled %}
+ autovacuum_enabled = {% if data.autovacuum_enabled %}true{% else %}false{% endif %}{% if has_vacuum_set %},
+{% endif %}
+{% endif %}
+{% if has_vacuum_set %}
+{% for opt in data.vacuum_table.set_values %}{% if opt.name and opt.value %}
+ {{opt.name}} = {{opt.value}}{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+{% endif %}
+
+);
+{% endif %}
+{% if has_vacuum_reset %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET (
+{% for opt in data.vacuum_table.reset_values %}{% if opt.name %}
+ {{opt.name}}{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+
+);
+{% endif %}
+{% endif %}
+{#####################################}
+{## Toast table AutoVacuum settings ##}
+{#####################################}
+{% if data.vacuum_toast is defined and data.vacuum_toast.set_values|length > 0 %}
+{% set has_vacuum_toast_set = true %}
+{% endif %}
+{% if data.vacuum_toast is defined and data.vacuum_toast.reset_values|length > 0 %}
+{% set has_vacuum_toast_reset = true %}
+{% endif %}
+{% if o_data.toast_autovacuum and data.toast_autovacuum == false %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET (
+ toast.autovacuum_enabled,
+ toast.autovacuum_freeze_max_age,
+ toast.autovacuum_vacuum_cost_delay,
+ toast.autovacuum_vacuum_cost_limit,
+ toast.autovacuum_vacuum_scale_factor,
+ toast.autovacuum_vacuum_threshold,
+ toast.autovacuum_freeze_min_age,
+ toast.autovacuum_freeze_table_age,
+ toast.autovacuum_analyze_threshold,
+ toast.autovacuum_analyze_scale_factor
+);
+{% else %}
+{% if data.toast_autovacuum_enabled is defined or has_vacuum_toast_set %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} SET (
+{% if data.toast_autovacuum_enabled is defined and data.toast_autovacuum_enabled != o_data.toast_autovacuum_enabled %}
+ toast.autovacuum_enabled = {% if data.toast_autovacuum_enabled %}true{% else %}false{% endif %}{% if has_vacuum_toast_set %},
+{% endif %}
+{% endif %}
+{% if has_vacuum_toast_set %}
+{% for opt in data.vacuum_toast.set_values %}{% if opt.name and opt.value %}
+ toast.{{opt.name}} = {{opt.value}}{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+{% endif %}
+
+);
+{% endif %}
+{% if has_vacuum_toast_reset %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET (
+{% for opt in data.vacuum_toast.reset_values %}{% if opt.name %}
+ toast.{{opt.name}}{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+
+);
+{% endif %}
+{% endif %}
+{#####################################################}
+{## Change table comments ##}
+{#####################################################}
+{% if data.description is defined and data.description != o_data.description %}
+COMMENT ON TABLE {{conn|qtIdent(data.schema, data.name)}}
+ IS {{data.description|qtLiteral}};
+
+{% endif %}
+{#####################################################}
+{## Update table Privileges ##}
+{#####################################################}
+{% if data.relacl %}
+{% if 'deleted' in data.relacl %}
+{% for priv in data.relacl.deleted %}
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.relacl %}
+{% for priv in data.relacl.changed %}
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.relacl %}
+{% for priv in data.relacl.added %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{#####################################################}
+{## Update table SecurityLabel ##}
+{#####################################################}
+{% if data.seclabels and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABEL.UNSET(conn, 'TABLE', data.name, r.provider, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }}
+{% endfor %}
+{% endif %}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql
index c52861b0c..486bc3091 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql
@@ -17,7 +17,7 @@
{% set empty_bracket = "\n(\n)"%}
{% endif %}
{% set with_clause = false%}
-{% if data.fillfactor or data.autovacuum_custom or data.autovacuum_enabled or data.toast_autovacuum or data.toast_autovacuum_enabled or (data.autovacuum_enabled and data.vacuum_table|length > 0) or (data.toast_autovacuum_enabled and data.vacuum_toast|length > 0) %}
+{% if data.fillfactor or data.parallel_workers or data.toast_tuple_target or data.autovacuum_custom or data.autovacuum_enabled or data.toast_autovacuum or data.toast_autovacuum_enabled or (data.autovacuum_enabled and data.vacuum_table|length > 0) or (data.toast_autovacuum_enabled and data.vacuum_toast|length > 0) %}
{% set with_clause = true%}
{% endif %}
CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE {{conn|qtIdent(data.schema, data.name)}}{{empty_bracket}}
@@ -86,7 +86,13 @@ CACHE {{c.seqcache|int}} {% endif %}
{% set add_comma = false%}
WITH (
{% if data.fillfactor %}{% set add_comma = true%}
- FILLFACTOR = {{ data.fillfactor }}{% endif %}{% if data.autovacuum_custom %}
+ FILLFACTOR = {{ data.fillfactor }}{% endif %}{% if data.parallel_workers %}
+{% if add_comma %},
+{% endif %}
+ parallel_workers = {{ data.parallel_workers }}{% set add_comma = true%}{% endif %}{% if data.toast_tuple_target %}
+{% if add_comma %},
+{% endif %}
+ toast_tuple_target = {{ data.toast_tuple_target }}{% set add_comma = true%}{% endif %}{% if data.autovacuum_custom %}
{% if add_comma %},
{% endif %}
autovacuum_enabled = {% if data.autovacuum_enabled %}TRUE{% else %}FALSE{% endif %}{% set add_comma = true%}{% endif %}{% if data.toast_autovacuum %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/properties.sql
index e7d19055d..3d9b1f622 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/properties.sql
@@ -27,6 +27,8 @@ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS r
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
+ substring(array_to_string(rel.reloptions, ',') FROM 'toast_tuple_target=([0-9]*)') AS toast_tuple_target,
(CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') = 'true')
THEN true ELSE false END) AS autovacuum_enabled,
substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
@@ -56,7 +58,7 @@ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS r
typ.typrelid AS typoid,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
-- Added for pgAdmin4
- (CASE WHEN array_length(rel.reloptions, 1) > 0 THEN true ELSE false END) AS autovacuum_custom,
+ (array_to_string(rel.reloptions, ',') like '%autovacuum%') AS autovacuum_custom,
(CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum,
(SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql
index 3ed12302d..e2415ad2a 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql
@@ -61,6 +61,29 @@ ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
RESET (FILLFACTOR);
{% endif %}
+
+{## change parallel_workers settings ##}
+{#####################################################}
+{% if (data.parallel_workers == '' or data.parallel_workers == None) and data.parallel_workers != o_data.parallel_workers %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ RESET (parallel_workers);
+{% elif data.parallel_workers is defined and data.parallel_workers != o_data.parallel_workers %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET (parallel_workers={{data.parallel_workers}});
+
+{% endif %}
+
+{## change toast_tuple_target settings ##}
+{#####################################################}
+{% if (data.toast_tuple_target == '' or data.toast_tuple_target == None) and data.toast_tuple_target != o_data.toast_tuple_target %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ RESET (toast_tuple_target);
+{% elif data.toast_tuple_target is defined and data.toast_tuple_target != o_data.toast_tuple_target %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET (toast_tuple_target={{data.toast_tuple_target}});
+
+{% endif %}
+
{###############################}
{## Table AutoVacuum settings ##}
{###############################}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/9.6_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/9.6_plus/create.sql
new file mode 100644
index 000000000..a7447e36b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/9.6_plus/create.sql
@@ -0,0 +1,174 @@
+{% import 'macros/schemas/security.macros' as SECLABEL %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{% import 'columns/macros/security.macros' as COLUMN_SECLABEL %}
+{% import 'columns/macros/privilege.macros' as COLUMN_PRIVILEGE %}
+{% import 'tables/sql/macros/constraints.macro' as CONSTRAINTS %}
+{% import 'types/macros/get_full_type_sql_format.macros' as GET_TYPE %}
+{#===========================================#}
+{#====== MAIN TABLE TEMPLATE STARTS HERE ======#}
+{#===========================================#}
+{#
+ If user has not provided any details but only name then
+ add empty bracket with table name
+#}
+{% set empty_bracket = ""%}
+{% if data.coll_inherits|length == 0 and data.columns|length == 0 and not data.typname and not data.like_relation and data.primary_key|length == 0 and data.unique_constraint|length == 0 and data.foreign_key|length == 0 and data.check_constraint|length == 0 and data.exclude_constraint|length == 0 %}
+{% set empty_bracket = "\n(\n)"%}
+{% endif %}
+CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE {{conn|qtIdent(data.schema, data.name)}}{{empty_bracket}}
+{% if data.typname %}
+ OF {{ data.typname }}
+{% endif %}
+{% if data.like_relation or data.coll_inherits or data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 or data.foreign_key|length > 0 or data.check_constraint|length > 0 or data.exclude_constraint|length > 0 %}
+(
+{% endif %}
+{% if data.like_relation %}
+ LIKE {{ data.like_relation }}{% if data.like_default_value %}
+
+ INCLUDING DEFAULTS{% endif %}{% if data.like_constraints %}
+
+ INCLUDING CONSTRAINTS{% endif %}{% if data.like_indexes %}
+
+ INCLUDING INDEXES{% endif %}{% if data.like_storage %}
+
+ INCLUDING STORAGE{% endif %}{% if data.like_comments %}
+
+ INCLUDING COMMENTS{% endif %}{% if data.columns|length > 0 %},
+{% endif %}
+
+{% endif %}
+{### Add columns ###}
+{% if data.columns and data.columns|length > 0 %}
+{% for c in data.columns %}
+{% if c.name and c.cltype %}
+ {% if c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% endif %}{{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval is defined and c.defval is not none and c.defval != '' %} DEFAULT {{c.defval}}{% endif %}
+{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+{% endif %}
+{# Macro to render for constraints #}
+{% if data.primary_key|length > 0 %}{% if data.columns|length > 0 %},{% endif %}
+{{CONSTRAINTS.PRIMARY_KEY(conn, data.primary_key[0])}}{% endif %}{% if data.unique_constraint|length > 0 %}{% if data.columns|length > 0 or data.primary_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.UNIQUE(conn, data.unique_constraint)}}{% endif %}{% if data.foreign_key|length > 0 %}{% if data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.FOREIGN_KEY(conn, data.foreign_key)}}{% endif %}{% if data.check_constraint|length > 0 %}{% if data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 or data.foreign_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.CHECK(conn, data.check_constraint)}}{% endif %}{% if data.exclude_constraint|length > 0 %}{% if data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 or data.foreign_key|length > 0 or data.check_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.EXCLUDE(conn, data.exclude_constraint)}}{% endif %}
+{% if data.like_relation or data.coll_inherits or data.columns|length > 0 or data.primary_key|length > 0 or data.unique_constraint|length > 0 or data.foreign_key|length > 0 or data.check_constraint|length > 0 or data.exclude_constraint|length > 0 %}
+
+){% endif %}{% if data.relkind is defined and data.relkind == 'p' %} PARTITION BY {{ data.partition_scheme }} {% endif %}
+
+{### If we are inheriting it from another table(s) ###}
+{% if data.coll_inherits %}
+ INHERITS ({% for val in data.coll_inherits %}{% if loop.index != 1 %}, {% endif %}{{val}}{% endfor %})
+{% endif %}
+WITH (
+ OIDS = {% if data.relhasoids %}TRUE{% else %}FALSE{% endif %}{% if data.fillfactor %},
+ FILLFACTOR = {{ data.fillfactor }}{% endif %}{% if data.autovacuum_custom %},{% if data.parallel_workers is defined and data.parallel_workers != '' %},
+ parallel_workers = {{ data.parallel_workers }}{% endif %}
+ autovacuum_enabled = {% if data.autovacuum_enabled %}TRUE{% else %}FALSE{% endif %}{% endif %}{% if data.toast_autovacuum %},
+ toast.autovacuum_enabled = {% if data.toast_autovacuum_enabled %}TRUE{% else %}FALSE{% endif %}
+{% endif %}{% if data.autovacuum_enabled and data.vacuum_table|length > 0 %}
+{% for opt in data.vacuum_table %}{% if opt.name and opt.value %}
+,
+ {{opt.name}} = {{opt.value}}{% endif %}
+{% endfor %}{% endif %}{% if data.toast_autovacuum_enabled and data.vacuum_toast|length > 0 %}
+{% for opt in data.vacuum_toast %}{% if opt.name and opt.value %}
+,
+ toast.{{opt.name}} = {{opt.value}}{% endif %}
+{% endfor %}{% endif %}
+
+{### SQL for Tablespace ###}
+{% if data.spcname %}
+)
+TABLESPACE {{ conn|qtIdent(data.spcname) }};
+{% else %}
+);
+{% endif %}
+{### Alter SQL for Owner ###}
+{% if data.relowner %}
+
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ OWNER to {{conn|qtIdent(data.relowner)}};
+{% endif %}
+{### Security Labels on Table ###}
+{% if data.seclabels and data.seclabels|length > 0 %}
+
+{% for r in data.seclabels %}
+{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }}
+{% endfor %}
+{% endif %}
+{### ACL on Table ###}
+{% if data.relacl %}
+
+{% for priv in data.relacl %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{### SQL for COMMENT ###}
+{% if data.description %}
+COMMENT ON TABLE {{conn|qtIdent(data.schema, data.name)}}
+ IS {{data.description|qtLiteral}};
+{% endif %}
+{#===========================================#}
+{#====== MAIN TABLE TEMPLATE ENDS HERE ======#}
+{#===========================================#}
+{#===========================================#}
+{# COLUMN SPECIFIC TEMPLATES STARTS HERE #}
+{#===========================================#}
+{% if data.columns and data.columns|length > 0 %}
+{% for c in data.columns %}
+{% if c.description %}
+
+COMMENT ON COLUMN {{conn|qtIdent(data.schema, data.name, c.name)}}
+ IS {{c.description|qtLiteral}};
+{% endif %}
+{### Add variables to column ###}
+{% if c.attoptions and c.attoptions|length > 0 %}
+
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ {{ VARIABLE.SET(conn, 'COLUMN', c.name, c.attoptions) }}
+
+{% endif %}
+{### Alter column statistics value ###}
+{% if c.attstattarget is defined and c.attstattarget > -1 %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ ALTER COLUMN {{conn|qtTypeIdent(c.name)}} SET STATISTICS {{c.attstattarget}};
+
+{% endif %}
+{### Alter column storage value ###}
+{% if c.attstorage is defined and c.attstorage != c.defaultstorage %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ ALTER COLUMN {{conn|qtTypeIdent(c.name)}} SET STORAGE {%if c.attstorage == 'p' %}
+PLAIN{% elif c.attstorage == 'm'%}MAIN{% elif c.attstorage == 'e'%}
+EXTERNAL{% elif c.attstorage == 'x'%}EXTENDED{% endif %};
+
+{% endif %}
+{### ACL ###}
+{% if c.attacl and c.attacl|length > 0 %}
+
+{% for priv in c.attacl %}
+ {{ COLUMN_PRIVILEGE.APPLY(conn, data.schema, data.name, c.name, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{### Security Lables ###}
+{% if c.seclabels and c.seclabels|length > 0 %}
+
+{% for r in c.seclabels %}
+{{ COLUMN_SECLABEL.APPLY(conn, 'COLUMN',data.schema, data.name, c.name, r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% endfor %}
+{% endif %}
+{#===========================================#}
+{# COLUMN SPECIFIC TEMPLATES ENDS HERE #}
+{#===========================================#}
+{#======================================#}
+{# CONSTRAINTS SPECIFIC TEMPLATES #}
+{#======================================#}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.primary_key)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.unique_constraint)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.foreign_key)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.check_constraint)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name, data.exclude_constraint)}}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/9.6_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/9.6_plus/properties.sql
new file mode 100644
index 000000000..4ed202d4f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/9.6_plus/properties.sql
@@ -0,0 +1,76 @@
+SELECT *,
+ (CASE when pre_coll_inherits is NULL then ARRAY[]::varchar[] else pre_coll_inherits END) as coll_inherits
+FROM (
+ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
+ (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE
+ (SELECT sp.spcname FROM pg_database dtb
+ JOIN pg_tablespace sp ON dtb.dattablespace=sp.oid
+ WHERE dtb.oid = {{ did }}::oid)
+ END) as spcname,
+ (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as schema,
+ pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids,
+ rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
+ EXISTS(select 1 FROM pg_trigger
+ JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
+ JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
+ WHERE tgrelid=rel.oid) AS isrepl,
+ (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid) AS triggercount,
+ (SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
+ quote_ident(nspname)||'.'||quote_ident(c.relname)
+ ELSE quote_ident(c.relname) END AS inherited_tables
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS pre_coll_inherits,
+ (SELECT count(*)
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
+ false AS relpersistence,
+ substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
+ (CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS autovacuum_enabled,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
+ (CASE WHEN (substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS toast_autovacuum_enabled,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
+ array_to_string(rel.reloptions, ',') AS table_vacuum_settings_str,
+ array_to_string(tst.reloptions, ',') AS toast_table_vacuum_settings_str,
+ rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, NULL AS reloftype, NULL AS typname,
+ typ.typrelid AS typoid,
+ (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
+ -- Added for pgAdmin4
+ (array_to_string(rel.reloptions, ',') like '%autovacuum%') AS autovacuum_custom,
+ (CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum,
+
+ ARRAY[]::varchar[] AS seclabels,
+ (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table
+
+ FROM pg_class rel
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=rel.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
+ LEFT OUTER JOIN pg_class tst ON tst.oid = rel.reltoastrelid
+
+ WHERE rel.relkind IN ('r','s','t') AND rel.relnamespace = {{ scid }}
+ {% if tid %} AND rel.oid = {{ tid }}::oid {% endif %}
+) AS TableInformation
+ ORDER BY name
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/9.6_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/9.6_plus/update.sql
new file mode 100644
index 000000000..98e9ebf4b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/9.6_plus/update.sql
@@ -0,0 +1,233 @@
+{% import 'macros/schemas/security.macros' as SECLABEL %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{#####################################################}
+{## Rename table ##}
+{#####################################################}
+{% if data.name and data.name != o_data.name %}
+ALTER TABLE {{conn|qtIdent(o_data.schema, o_data.name)}}
+ RENAME TO {{conn|qtIdent(data.name)}};
+
+{% endif %}
+{#####################################################}
+{## Change table schema ##}
+{#####################################################}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER TABLE {{conn|qtIdent(o_data.schema, data.name)}}
+ SET SCHEMA {{conn|qtIdent(data.schema)}};
+
+{% endif %}
+{#####################################################}
+{## Change table owner ##}
+{#####################################################}
+{% if data.relowner and data.relowner != o_data.relowner %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ OWNER TO {{conn|qtIdent(data.relowner)}};
+
+{% endif %}
+{#####################################################}
+{## Update Inherits table definition ##}
+{#####################################################}
+{% if data.coll_inherits_added|length > 0 %}
+{% for val in data.coll_inherits_added %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ INHERIT {{val}};
+
+{% endfor %}
+{% endif %}
+{% if data.coll_inherits_removed|length > 0 %}
+{% for val in data.coll_inherits_removed %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ NO INHERIT {{val}};
+
+{% endfor %}
+{% endif %}
+{#####################################################}
+{## Change hasOID attribute of table ##}
+{#####################################################}
+{% if data.relhasoids is defined and data.relhasoids != o_data.relhasoids %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET {% if data.relhasoids %}WITH{% else %}WITHOUT{% endif %} OIDS;
+
+{% endif %}
+{#####################################################}
+{## Change tablespace ##}
+{#####################################################}
+{% if data.spcname and data.spcname != o_data.spcname %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET TABLESPACE {{conn|qtIdent(data.spcname)}};
+
+{% endif %}
+{#####################################################}
+{## change fillfactor settings ##}
+{#####################################################}
+{% if data.fillfactor and data.fillfactor != o_data.fillfactor %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET (FILLFACTOR={{data.fillfactor}});
+{% elif (data.fillfactor == '' or data.fillfactor == None) and data.fillfactor != o_data.fillfactor %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ RESET (FILLFACTOR);
+
+{% endif %}
+
+{## change parallel_workers settings ##}
+{#####################################################}
+{% if (data.parallel_workers == '' or data.parallel_workers == None) and data.parallel_workers != o_data.parallel_workers %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ RESET (parallel_workers);
+{% elif data.parallel_workers is defined and data.parallel_workers != o_data.parallel_workers %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ SET (parallel_workers={{data.parallel_workers}});
+
+{% endif %}
+{###############################}
+{## Table AutoVacuum settings ##}
+{###############################}
+{% if data.vacuum_table is defined and data.vacuum_table.set_values|length > 0 %}
+{% set has_vacuum_set = true %}
+{% endif %}
+{% if data.vacuum_table is defined and data.vacuum_table.reset_values|length > 0 %}
+{% set has_vacuum_reset = true %}
+{% endif %}
+{% if o_data.autovacuum_custom and data.autovacuum_custom == false %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET (
+ autovacuum_enabled,
+ autovacuum_analyze_scale_factor,
+ autovacuum_analyze_threshold,
+ autovacuum_freeze_max_age,
+ autovacuum_vacuum_cost_delay,
+ autovacuum_vacuum_cost_limit,
+ autovacuum_vacuum_scale_factor,
+ autovacuum_vacuum_threshold,
+ autovacuum_freeze_min_age,
+ autovacuum_freeze_table_age
+);
+{% else %}
+{% if data.autovacuum_enabled is defined or has_vacuum_set %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} SET (
+{% if data.autovacuum_enabled is defined and data.autovacuum_enabled != o_data.autovacuum_enabled %}
+ autovacuum_enabled = {% if data.autovacuum_enabled %}true{% else %}false{% endif %}{% if has_vacuum_set %},
+{% endif %}
+{% endif %}
+{% if has_vacuum_set %}
+{% for opt in data.vacuum_table.set_values %}{% if opt.name and opt.value %}
+ {{opt.name}} = {{opt.value}}{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+{% endif %}
+
+);
+{% endif %}
+{% if has_vacuum_reset %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET (
+{% for opt in data.vacuum_table.reset_values %}{% if opt.name %}
+ {{opt.name}}{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+
+);
+{% endif %}
+{% endif %}
+{#####################################}
+{## Toast table AutoVacuum settings ##}
+{#####################################}
+{% if data.vacuum_toast is defined and data.vacuum_toast.set_values|length > 0 %}
+{% set has_vacuum_toast_set = true %}
+{% endif %}
+{% if data.vacuum_toast is defined and data.vacuum_toast.reset_values|length > 0 %}
+{% set has_vacuum_toast_reset = true %}
+{% endif %}
+{% if o_data.toast_autovacuum and data.toast_autovacuum == false %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET (
+ toast.autovacuum_enabled,
+ toast.autovacuum_freeze_max_age,
+ toast.autovacuum_vacuum_cost_delay,
+ toast.autovacuum_vacuum_cost_limit,
+ toast.autovacuum_vacuum_scale_factor,
+ toast.autovacuum_vacuum_threshold,
+ toast.autovacuum_freeze_min_age,
+ toast.autovacuum_freeze_table_age,
+ toast.autovacuum_analyze_threshold,
+ toast.autovacuum_analyze_scale_factor
+);
+{% else %}
+{% if data.toast_autovacuum_enabled is defined or has_vacuum_toast_set %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} SET (
+{% if data.toast_autovacuum_enabled is defined and data.toast_autovacuum_enabled != o_data.toast_autovacuum_enabled %}
+ toast.autovacuum_enabled = {% if data.toast_autovacuum_enabled %}true{% else %}false{% endif %}{% if has_vacuum_toast_set %},
+{% endif %}
+{% endif %}
+{% if has_vacuum_toast_set %}
+{% for opt in data.vacuum_toast.set_values %}{% if opt.name and opt.value %}
+ toast.{{opt.name}} = {{opt.value}}{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+{% endif %}
+
+);
+{% endif %}
+{% if has_vacuum_toast_reset %}
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET (
+{% for opt in data.vacuum_toast.reset_values %}{% if opt.name %}
+ toast.{{opt.name}}{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor %}
+
+);
+{% endif %}
+{% endif %}
+{#####################################################}
+{## Change table comments ##}
+{#####################################################}
+{% if data.description is defined and data.description != o_data.description %}
+COMMENT ON TABLE {{conn|qtIdent(data.schema, data.name)}}
+ IS {{data.description|qtLiteral}};
+
+{% endif %}
+{#####################################################}
+{## Update table Privileges ##}
+{#####################################################}
+{% if data.relacl %}
+{% if 'deleted' in data.relacl %}
+{% for priv in data.relacl.deleted %}
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.relacl %}
+{% for priv in data.relacl.changed %}
+{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.relacl %}
+{% for priv in data.relacl.added %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{#####################################################}
+{## Update table SecurityLabel ##}
+{#####################################################}
+{% if data.seclabels and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABEL.UNSET(conn, 'TABLE', data.name, r.provider, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }}
+{% endfor %}
+{% endif %}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/default/properties.sql
index 932e7ffa5..47686a280 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/default/properties.sql
@@ -57,7 +57,7 @@ FROM (
typ.typrelid AS typoid,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
-- Added for pgAdmin4
- (CASE WHEN array_length(rel.reloptions, 1) > 0 THEN true ELSE false END) AS autovacuum_custom,
+ (array_to_string(rel.reloptions, ',') like '%autovacuum%') AS autovacuum_custom,
(CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum,
ARRAY[]::varchar[] AS seclabels,
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [pgAdmin][RM5181] Add support for parameters toast_tuple_target and parallel_workers of table
In-Reply-To: <CAM9w-_k7QzXDVsbVc5DQ-5sgOqJbnK3uBnAAZdWDG=Eo=n3q=w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox