public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rahul Shirsat <[email protected]>
To: Akshay Joshi <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin][patch] RM6329 [SQL] bad child table DDL
Date: Mon, 26 Apr 2021 20:36:23 +0530
Message-ID: <CAKtn9dOXikuDQP1X0B1LMQH3Y-ZH=CHm5WzNnTKaPPD=EJ9HRA@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDcx=xCpjUpOajQTWvm=WooGL8rP+nu1JiawLGFRLqkMRA@mail.gmail.com>
References: <CAKtn9dO64vXpzefzaFMHYY_b=3pk0LKO7-CZYp2+=-jOJ8LZsg@mail.gmail.com>
<CANxoLDcx=xCpjUpOajQTWvm=WooGL8rP+nu1JiawLGFRLqkMRA@mail.gmail.com>
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,
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], [email protected]
Subject: Re: [pgAdmin][patch] RM6329 [SQL] bad child table DDL
In-Reply-To: <CAKtn9dOXikuDQP1X0B1LMQH3Y-ZH=CHm5WzNnTKaPPD=EJ9HRA@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