public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rahul Shirsat <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin][patch] RM6329 [SQL] bad child table DDL
Date: Fri, 23 Apr 2021 14:07:37 +0530
Message-ID: <CAKtn9dO64vXpzefzaFMHYY_b=3pk0LKO7-CZYp2+=-jOJ8LZsg@mail.gmail.com> (raw)
Hi Hackers,
Please find below patch which resolves the issue of inherited key
constraint getting in partition SQL.
Simultaneously, added the partition table's RLS policy, Triggers, Rules,
Compound Triggers in the main SQL tab as previously it was not included.
--
*Rahul Shirsat*
Senior Software Engineer | EnterpriseDB Corporation.
Attachments:
[application/octet-stream] RM6329.patch (13.1K, 3-RM6329.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/nodes.sql
index e3ac2505b..c859975e9 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/nodes.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/nodes.sql
@@ -1,5 +1,5 @@
SELECT c.oid, conname as name,
- NOT convalidated as convalidated
+ NOT convalidated as convalidated, conislocal
FROM pg_catalog.pg_constraint c
WHERE contype = 'c'
AND conrelid = {{ tid }}::oid
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/properties.sql
index 08a1aa5fc..5673b1312 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/9.2_plus/properties.sql
@@ -1,6 +1,6 @@
SELECT c.oid, conname as name, relname, nspname, description as comment,
pg_catalog.pg_get_expr(conbin, conrelid, true) as consrc,
- connoinherit, NOT convalidated as convalidated
+ connoinherit, NOT convalidated as convalidated, conislocal
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=relnamespace
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/default/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/default/nodes.sql
index 020eecca0..424706199 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/default/nodes.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/default/nodes.sql
@@ -1,4 +1,4 @@
-SELECT c.oid, conname as name
+SELECT c.oid, conname as name, conislocal
FROM pg_catalog.pg_constraint c
WHERE contype = 'c'
AND conrelid = {{ tid }}::oid
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/default/properties.sql
index ef0d65e22..19bd6fba7 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/check_constraint/sql/default/properties.sql
@@ -1,5 +1,5 @@
SELECT c.oid, conname as name, relname, nspname, description as comment ,
- pg_catalog.pg_get_expr(conbin, conrelid, true) as consrc
+ pg_catalog.pg_get_expr(conbin, conrelid, true) as consrc, conislocal
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=relnamespace
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/properties.sql
index ce2c5a1fe..610100093 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/9.1_plus/properties.sql
@@ -16,7 +16,8 @@ SELECT ct.oid,
nr.nspname as refnsp,
cr.relname as reftab,
description as comment,
- convalidated
+ convalidated,
+ conislocal
FROM pg_catalog.pg_constraint ct
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=cl.relnamespace
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/properties.sql
index 0a2b4f484..a827b783f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/default/properties.sql
@@ -17,7 +17,8 @@ SELECT
cl.relname as fktab,
nr.nspname as refnsp,
cr.relname as reftab,
- description as comment
+ description as comment,
+ conislocal
FROM pg_catalog.pg_constraint ct
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=cl.relnamespace
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/11_plus/properties.sql
index b1d5c69c5..220a3d84f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/11_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/11_plus/properties.sql
@@ -14,6 +14,7 @@ SELECT cls.oid,
END AS comment,
condeferrable,
condeferred,
+ conislocal,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/default/properties.sql
index 0307e46c6..83ade7ff0 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/default/properties.sql
@@ -14,6 +14,7 @@ SELECT cls.oid,
END AS comment,
condeferrable,
condeferred,
+ conislocal,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
index c93f4a861..93a6616b4 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
@@ -294,20 +294,25 @@ class BaseTableView(PGChildNodeView, BasePartitionTable, VacuumSettings):
idxcons_utils.get_index_constraints(self.conn, did, tid, ctype)
if status:
for cons in constraints:
- data.setdefault(
- index_constraints[ctype], []).append(cons)
+ if not self.\
+ _is_partition_and_constraint_inherited(cons, data):
+ data.setdefault(
+ index_constraints[ctype], []).append(cons)
# Add Foreign Keys
status, foreign_keys = fkey_utils.get_foreign_keys(self.conn, tid)
if status:
for fk in foreign_keys:
- data.setdefault('foreign_key', []).append(fk)
+ if not self._is_partition_and_constraint_inherited(fk, data):
+ data.setdefault('foreign_key', []).append(fk)
# Add Check Constraints
status, check_constraints = \
check_utils.get_check_constraints(self.conn, tid)
if status:
- data['check_constraint'] = check_constraints
+ for cc in check_constraints:
+ if not self._is_partition_and_constraint_inherited(cc, data):
+ data.setdefault('check_constraint', []).append(cc)
# Add Exclusion Constraint
status, exclusion_constraints = \
@@ -316,6 +321,23 @@ class BaseTableView(PGChildNodeView, BasePartitionTable, VacuumSettings):
for ex in exclusion_constraints:
data.setdefault('exclude_constraint', []).append(ex)
+ @staticmethod
+ def _is_partition_and_constraint_inherited(constraint, data):
+
+ """
+ This function will check whether a constraint is local or
+ inherited only for partition table
+ :param constraint: given constraint
+ :param data: partition table data
+ :return: True or False based on condition
+ """
+ # check whether the table is partition or not, then check conislocal
+ if 'relispartition' in data and data['relispartition'] is True:
+ if 'conislocal' in constraint \
+ and constraint['conislocal'] is False:
+ return True
+ return False
+
def get_table_dependents(self, tid):
"""
This function get the dependents and return ajax response
@@ -820,7 +842,7 @@ class BaseTableView(PGChildNodeView, BasePartitionTable, VacuumSettings):
main_sql.append(rules_sql)
def _get_resql_for_partitions(self, data, rset, json_resp,
- diff_partition_sql, main_sql):
+ diff_partition_sql, main_sql, did):
"""
##########################################
# Reverse engineered sql for PARTITIONS
@@ -828,6 +850,7 @@ class BaseTableView(PGChildNodeView, BasePartitionTable, VacuumSettings):
"""
sql_header = ''
+ partition_sql_arr = []
if len(rset['rows']):
if json_resp:
sql_header = "\n-- Partitions SQL"
@@ -900,18 +923,48 @@ class BaseTableView(PGChildNodeView, BasePartitionTable, VacuumSettings):
part_data['vacuum_toast'] = \
copy.deepcopy(self.parse_vacuum_data(
self.conn, row, 'toast'))
+
+ scid = row['schema_id']
+ schema = part_data['schema']
+ table = part_data['name']
+
+ # Get all the supported constraints for partition table
+ self._add_constrints_to_output(part_data, did, row['oid'])
+
partition_sql += render_template("/".join(
[self.partition_template_path, self._CREATE_SQL]),
data=part_data, conn=self.conn) + '\n'
- # Add into main sql
- partition_sql = re.sub(self.pattern, self.double_newline,
- partition_sql).strip('\n')
- partition_main_sql = partition_sql.strip('\n')
+ partition_sql = re.sub(self.pattern, self.double_newline,
+ partition_sql).strip('\n')
+
+ partition_main_sql = partition_sql.strip('\n')
+
+ # Add into partition sql to partition array
+ partition_sql_arr.append(partition_main_sql)
+
+ # Get Reverse engineered sql for ROW SECURITY POLICY
+ self._get_resql_for_row_security_policy(scid, row['oid'],
+ json_resp,
+ partition_sql_arr,
+ schema, table)
+
+ # Get Reverse engineered sql for Triggers
+ self._get_resql_for_triggers(row['oid'], json_resp,
+ partition_sql_arr, schema, table)
+
+ # Get Reverse engineered sql for Compound Triggers
+ self._get_resql_for_compound_triggers(row['oid'],
+ partition_sql_arr,
+ schema, table)
+
+ # Get Reverse engineered sql for Rules
+ self._get_resql_for_rules(row['oid'], partition_sql_arr, table,
+ json_resp)
+
if not diff_partition_sql:
- main_sql.append(
- sql_header + self.double_newline + partition_main_sql
- )
+ main_sql.append(sql_header + '\n')
+ main_sql += partition_sql_arr
def get_reverse_engineered_sql(self, **kwargs):
"""
@@ -964,7 +1017,7 @@ class BaseTableView(PGChildNodeView, BasePartitionTable, VacuumSettings):
return internal_server_error(errormsg=rset)
self._get_resql_for_partitions(data, rset, json_resp,
- diff_partition_sql, main_sql)
+ diff_partition_sql, main_sql, did)
sql = '\n'.join(main_sql)
view thread (4+ 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][patch] RM6329 [SQL] bad child table DDL
In-Reply-To: <CAKtn9dO64vXpzefzaFMHYY_b=3pk0LKO7-CZYp2+=-jOJ8LZsg@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