public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin][patch] RM6329 [SQL] bad child table DDL 4+ messages / 2 participants [nested] [flat]
* [pgAdmin][patch] RM6329 [SQL] bad child table DDL @ 2021-04-23 08:37 Rahul Shirsat <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Rahul Shirsat @ 2021-04-23 08:37 UTC (permalink / raw) To: pgadmin-hackers 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) ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin][patch] RM6329 [SQL] bad child table DDL @ 2021-04-26 09:03 Akshay Joshi <[email protected]> parent: Rahul Shirsat <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Akshay Joshi @ 2021-04-26 09:03 UTC (permalink / raw) To: Rahul Shirsat <[email protected]>; +Cc: pgadmin-hackers Thanks, patch applied. On Fri, Apr 23, 2021 at 2:08 PM Rahul Shirsat < [email protected]> wrote: > 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. > -- *Thanks & Regards* *Akshay Joshi* *pgAdmin Hacker | Principal Software Architect* *EDB Postgres <http://edbpostgres.com>* *Mobile: +91 976-788-8246* ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin][patch] RM6329 [SQL] bad child table DDL @ 2021-04-26 15:06 Rahul Shirsat <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Rahul Shirsat @ 2021-04-26 15:06 UTC (permalink / raw) To: Akshay Joshi <[email protected]>; +Cc: pgadmin-hackers Hello Akshay, Please find the updated patch. On Mon, Apr 26, 2021 at 2:34 PM Akshay Joshi <[email protected]> wrote: > Thanks, patch applied. > > On Fri, Apr 23, 2021 at 2:08 PM Rahul Shirsat < > [email protected]> wrote: > >> 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. >> > > > -- > *Thanks & Regards* > *Akshay Joshi* > *pgAdmin Hacker | Principal Software Architect* > *EDB Postgres <http://edbpostgres.com>* > > *Mobile: +91 976-788-8246* > -- *Rahul Shirsat* Senior Software Engineer | EnterpriseDB Corporation. Attachments: [application/octet-stream] RM6329_v2.patch (4.3K, 3-RM6329_v2.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/indexes/sql/default/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/indexes/sql/default/nodes.sql index 26826ef73..c1f8cfe5e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/indexes/sql/default/nodes.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/indexes/sql/default/nodes.sql @@ -1,4 +1,5 @@ -SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name +SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, +(SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited FROM pg_catalog.pg_index idx JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid JOIN pg_catalog.pg_class tab ON tab.oid=indrelid diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/indexes/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/indexes/sql/default/properties.sql index 975099c6b..37dace967 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/indexes/sql/default/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/indexes/sql/default/properties.sql @@ -1,6 +1,6 @@ SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid, indkey, indisclustered, indisvalid, indisunique, indisprimary, n.nspname,indnatts,cls.reltablespace AS spcoid, - CASE WHEN length(spcname::text) > 0 THEN spcname ELSE + CASE WHEN (length(spcname::text) > 0 OR cls.relkind = 'I') THEN spcname ELSE (SELECT sp.spcname FROM pg_catalog.pg_database dtb JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid WHERE dtb.oid = {{ did }}::oid) @@ -9,6 +9,7 @@ SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid, indkey, CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description ELSE des.description END AS description, pg_catalog.pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname, + (SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited, substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor {% if datlastsysoid %}, (CASE WHEN cls.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_idx {% endif %} FROM pg_catalog.pg_index idx 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 93a6616b4..a19624311 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 @@ -703,6 +703,12 @@ class BaseTableView(PGChildNodeView, BasePartitionTable, VacuumSettings): from pgadmin.browser.server_groups.servers.databases.schemas. \ tables.indexes import utils as index_utils for row in rset['rows']: + # Do not include inherited indexes as those are automatically + # created by postgres. If index is inherited, exclude it + # from main sql + if 'is_inherited' in row and row['is_inherited'] is True: + continue + index_sql = index_utils.get_reverse_engineered_sql( self.conn, schema=schema, table=table, did=did, tid=tid, idx=row['oid'], datlastsysoid=self.datlastsysoid, @@ -943,6 +949,10 @@ class BaseTableView(PGChildNodeView, BasePartitionTable, VacuumSettings): # Add into partition sql to partition array partition_sql_arr.append(partition_main_sql) + # Get Reverse engineered sql for index + self._get_resql_for_index(did, row['oid'], partition_sql_arr, + json_resp, schema, table) + # Get Reverse engineered sql for ROW SECURITY POLICY self._get_resql_for_row_security_policy(scid, row['oid'], json_resp, ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin][patch] RM6329 [SQL] bad child table DDL @ 2021-04-27 06:29 Akshay Joshi <[email protected]> parent: Rahul Shirsat <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Akshay Joshi @ 2021-04-27 06:29 UTC (permalink / raw) To: Rahul Shirsat <[email protected]>; +Cc: pgadmin-hackers Thanks, patch applied. On Mon, Apr 26, 2021 at 8:37 PM Rahul Shirsat < [email protected]> wrote: > Hello Akshay, > > Please find the updated patch. > > On Mon, Apr 26, 2021 at 2:34 PM Akshay Joshi < > [email protected]> wrote: > >> Thanks, patch applied. >> >> On Fri, Apr 23, 2021 at 2:08 PM Rahul Shirsat < >> [email protected]> wrote: >> >>> 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. >>> >> >> >> -- >> *Thanks & Regards* >> *Akshay Joshi* >> *pgAdmin Hacker | Principal Software Architect* >> *EDB Postgres <http://edbpostgres.com>* >> >> *Mobile: +91 976-788-8246* >> > > > -- > *Rahul Shirsat* > Senior Software Engineer | EnterpriseDB Corporation. > -- *Thanks & Regards* *Akshay Joshi* *pgAdmin Hacker | Principal Software Architect* *EDB Postgres <http://edbpostgres.com>* *Mobile: +91 976-788-8246* ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-04-27 06:29 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-04-23 08:37 [pgAdmin][patch] RM6329 [SQL] bad child table DDL Rahul Shirsat <[email protected]> 2021-04-26 09:03 ` Akshay Joshi <[email protected]> 2021-04-26 15:06 ` Rahul Shirsat <[email protected]> 2021-04-27 06:29 ` Akshay Joshi <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox