public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties 12+ messages / 3 participants [nested] [flat]
* [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-01-13 06:41 Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Khushboo Vashi @ 2017-01-13 06:41 UTC (permalink / raw) To: pgadmin-hackers Hi, Please find the attached patch to fix the RM 2069 - Wrong tablespace displayed in table properties. When the table is created without a tablespace, the "pg_default" tablespace gets selected rather than the default tablespace (the current database tablespace). So, this issue has been fixed. Thanks, Khushboo -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [text/x-patch] RM_2069.patch (9.2K, 3-RM_2069.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py index 99fa00c..64657b1 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py @@ -134,7 +134,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - This function is used to return modified SQL for the selected Table node - * get_sql(data, scid, tid) + * get_sql(did, scid, tid, data) - This function will generate sql from model data * sql(gid, sid, did, scid, tid): @@ -344,7 +344,8 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, datlastsysoid=self.datlastsysoid) + did=did, scid=scid, + datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1128,7 +1129,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1498,7 +1499,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = SQL.strip('\n').strip(' ') status, res = self.conn.execute_scalar(SQL) @@ -1547,7 +1548,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1608,7 +1609,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1656,7 +1657,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1736,7 +1737,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = re.sub('\n{2,}', '\n\n', SQL) SQL = SQL.strip('\n') if SQL == '': @@ -2104,7 +2105,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): else: return None - def get_sql(self, scid, tid, data): + def get_sql(self, did, scid, tid, data): """ This function will generate create/update sql from model data coming from client @@ -2112,7 +2113,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): if tid is not None: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2468,7 +2469,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2738,7 +2739,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2782,7 +2783,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2829,7 +2830,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2878,7 +2879,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql index d995d00..a16cf87 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql @@ -1,5 +1,9 @@ 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 'pg_default' END) as spcname, + (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, des.description, con.conname, con.conkey, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql index d995d00..a16cf87 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql @@ -1,5 +1,9 @@ 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 'pg_default' END) as spcname, + (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, des.description, con.conname, con.conkey, ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-01-20 12:01 Dave Page <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Dave Page @ 2017-01-20 12:01 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: pgadmin-hackers On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi <[email protected]> wrote: > Hi, > > Please find the attached patch to fix the RM 2069 - Wrong tablespace > displayed in table properties. > > When the table is created without a tablespace, the "pg_default" tablespace > gets selected rather than the default tablespace (the current database > tablespace). > So, this issue has been fixed. Unfortunately more work is required. I started hacking on it (see attached), but there is more effort needed and I'm out of time. Specifically: - We need to remove 'pg_default' as the default tablespace when creating objects. Let's have no default, and let PG automatically pick the right tablespace, unless the user specifically overrides it. My updated patch removes that default, and fixes some of the SQL to handle it. - Matviews don't list the correct tablespace in their properties list. - Unique constraints ignore the tablespace option if the user selects pg_default, even if the database's default is something different. So I think to complete this we need to review all objects that use tablespaces (databases, tables, matviews, indexes, index-backed constraints) and ensure: - No default tablespace is selected when creating an object. - If the user selects a tablespace, the CREATE SQL must handle it properly. Similarly, if they leave it to the default. - The Properties views must display the actual tablespace in use, whether it is specific to the object, or the database default. Thanks. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] RM_2069-Dave.patch (14.8K, 2-RM_2069-Dave.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py index 99fa00c..64657b1 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py @@ -134,7 +134,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - This function is used to return modified SQL for the selected Table node - * get_sql(data, scid, tid) + * get_sql(did, scid, tid, data) - This function will generate sql from model data * sql(gid, sid, did, scid, tid): @@ -344,7 +344,8 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, datlastsysoid=self.datlastsysoid) + did=did, scid=scid, + datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1128,7 +1129,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1498,7 +1499,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = SQL.strip('\n').strip(' ') status, res = self.conn.execute_scalar(SQL) @@ -1547,7 +1548,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1608,7 +1609,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1656,7 +1657,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1736,7 +1737,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = re.sub('\n{2,}', '\n\n', SQL) SQL = SQL.strip('\n') if SQL == '': @@ -2104,7 +2105,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): else: return None - def get_sql(self, scid, tid, data): + def get_sql(self, did, scid, tid, data): """ This function will generate create/update sql from model data coming from client @@ -2112,7 +2113,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): if tid is not None: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2468,7 +2469,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2738,7 +2739,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2782,7 +2783,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2829,7 +2830,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2878,7 +2879,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js index cfe1519..9f19b6d 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js @@ -646,7 +646,7 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { name: undefined, oid: undefined, comment: undefined, - spcname: "pg_default", + spcname: undefined, amname: "gist", fillfactor: undefined, condeferrable: undefined, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js index f552972..6439caf 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js @@ -85,7 +85,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { name: undefined, oid: undefined, comment: undefined, - spcname: "pg_default", + spcname: undefined, index: undefined, fillfactor: undefined, condeferrable: undefined, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js index 6531ba5..7efe165 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js @@ -245,7 +245,7 @@ function($, _, S, pgAdmin, pgBrowser, Backform, alertify) { oid: undefined, nspname: undefined, tabname: undefined, - spcname: 'pg_default', + spcname: undefined, amname: 'btree' }, schema: [{ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js index 2d00609..a034c64 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js @@ -250,7 +250,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { name: undefined, oid: undefined, spcoid: undefined, - spcname: 'pg_default', + spcname: undefined, relowner: undefined, relacl: undefined, relhasoids: undefined, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/create.sql index ba91ac5..d7136f7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/create.sql @@ -79,10 +79,12 @@ WITH ( 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 %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql index d995d00..a16cf87 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql @@ -1,5 +1,9 @@ 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 'pg_default' END) as spcname, + (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, des.description, con.conname, con.conkey, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/create.sql index ba91ac5..d7136f7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/create.sql @@ -79,10 +79,12 @@ WITH ( 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 %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql index d995d00..a16cf87 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql @@ -1,5 +1,9 @@ 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 'pg_default' END) as spcname, + (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, des.description, con.conname, con.conkey, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js index e233b5c..e32594c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js @@ -126,7 +126,7 @@ function($, _, S, pgAdmin, alertify, pgBrowser, CodeMirror) { pgBrowser.Node.Model.prototype.initialize.apply(this, arguments); }, defaults: { - spcname: 'pg_default', + spcname: undefined, toast_autovacuum_enabled: false, autovacuum_enabled: false }, ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-01-27 11:53 Khushboo Vashi <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Khushboo Vashi @ 2017-01-27 11:53 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi, Please find the attached updated patch. Thanks, Khushboo On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> wrote: > On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi > <[email protected]> wrote: > > Hi, > > > > Please find the attached patch to fix the RM 2069 - Wrong tablespace > > displayed in table properties. > > > > When the table is created without a tablespace, the "pg_default" > tablespace > > gets selected rather than the default tablespace (the current database > > tablespace). > > So, this issue has been fixed. > > Unfortunately more work is required. I started hacking on it (see > attached), but there is more effort needed and I'm out of time. > Specifically: > > - We need to remove 'pg_default' as the default tablespace when > creating objects. Let's have no default, and let PG automatically pick > the right tablespace, unless the user specifically overrides it. My > updated patch removes that default, and fixes some of the SQL to > handle it. > > Done > - Matviews don't list the correct tablespace in their properties list. > > Done > - Unique constraints ignore the tablespace option if the user selects > pg_default, even if the database's default is something different. > > Done > So I think to complete this we need to review all objects that use > tablespaces (databases, tables, matviews, indexes, index-backed > constraints) and ensure: > > Done > - No default tablespace is selected when creating an object. > > Done > - If the user selects a tablespace, the CREATE SQL must handle it > properly. Similarly, if they leave it to the default. > > Done > - The Properties views must display the actual tablespace in use, > whether it is specific to the object, or the database default. > > Done > Thanks. > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [text/x-patch] RM_2069_ver1.patch (45.2K, 3-RM_2069_ver1.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py index f501d3d..b08e912 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py @@ -134,7 +134,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - This function is used to return modified SQL for the selected Table node - * get_sql(data, scid, tid) + * get_sql(did, scid, tid, data) - This function will generate sql from model data * sql(gid, sid, did, scid, tid): @@ -182,7 +182,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - It will return formatted output of query result as per client model format for column node - * _index_constraints_formatter(self, tid, data): + * _index_constraints_formatter(self, did, tid, data): - It will return formatted output of query result as per client model format for index constraint node @@ -195,7 +195,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - This function will parse and return formatted list of columns added by user - * get_index_constraint_sql(self, tid, data): + * get_index_constraint_sql(self, did, tid, data): - This function will generate modified sql for index constraints (Primary Key & Unique) @@ -344,7 +344,8 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, datlastsysoid=self.datlastsysoid) + did=did, scid=scid, + datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -737,7 +738,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): return data - def _index_constraints_formatter(self, tid, data): + def _index_constraints_formatter(self, did, tid, data): """ Args: tid: Table OID @@ -758,7 +759,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): sql = render_template("/".join([self.index_constraint_template_path, 'properties.sql']), - tid=tid, + did=did, tid=tid, constraint_type=ctype) status, res = self.conn.execute_dict(sql) @@ -886,7 +887,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): return data - def _exclusion_constraint_formatter(self, tid, data): + def _exclusion_constraint_formatter(self, did, tid, data): """ Args: tid: Table OID @@ -900,7 +901,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): # We will fetch all the index constraints for the table sql = render_template("/".join([self.exclusion_constraint_template_path, 'properties.sql']), - tid=tid) + did=did, tid=tid) status, result = self.conn.execute_dict(sql) @@ -983,7 +984,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): return None - def _formatter(self, scid, tid, data): + def _formatter(self, did, scid, tid, data): """ Args: data: dict of query result @@ -1102,10 +1103,10 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data = self._columns_formatter(tid, data) # Here we will add constraint in our output - data = self._index_constraints_formatter(tid, data) + data = self._index_constraints_formatter(did, tid, data) data = self._foreign_key_formatter(tid, data) data = self._check_constraint_formatter(tid, data) - data = self._exclusion_constraint_formatter(tid, data) + data = self._exclusion_constraint_formatter(did, tid, data) return data @@ -1128,7 +1129,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1151,7 +1152,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): 'vacuum_settings_str' ].replace("=", " = ") - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) return ajax_response( response=data, @@ -1498,7 +1499,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = SQL.strip('\n').strip(' ') status, res = self.conn.execute_scalar(SQL) @@ -1547,7 +1548,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1608,7 +1609,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1656,7 +1657,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1736,7 +1737,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = re.sub('\n{2,}', '\n\n', SQL) SQL = SQL.strip('\n') if SQL == '': @@ -1748,7 +1749,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_index_constraint_sql(self, tid, data): + def get_index_constraint_sql(self, did, tid, data): """ Args: tid: Table ID @@ -1790,7 +1791,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): properties_sql = render_template("/".join( [self.index_constraint_template_path, 'properties.sql']), - tid=tid, cid=c['oid'], constraint_type=ctype) + did=did, tid=tid, cid=c['oid'], constraint_type=ctype) status, res = self.conn.execute_dict(properties_sql) if not status: return internal_server_error(errormsg=res) @@ -2104,7 +2105,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): else: return None - def get_sql(self, scid, tid, data): + def get_sql(self, did, scid, tid, data): """ This function will generate create/update sql from model data coming from client @@ -2112,14 +2113,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): if tid is not None: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) old_data = res['rows'][0] - old_data = self._formatter(scid, tid, old_data) + old_data = self._formatter(did, scid, tid, old_data) # We will convert privileges coming from client required if 'relacl' in data: @@ -2271,7 +2272,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL += column_sql.strip('\n') # Check if index constraints are added/changed/deleted - index_constraint_sql = self.get_index_constraint_sql(tid, data) + index_constraint_sql = self.get_index_constraint_sql(did, tid, data) # If we have index constraint sql then ad it in main sql if index_constraint_sql is not None: SQL += '\n' + index_constraint_sql @@ -2468,7 +2469,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2480,7 +2481,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): schema = data['schema'] table = data['name'] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) # Now we have all lis of columns which we need # to include in our create definition, Let's format them @@ -2536,7 +2537,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL = render_template("/".join([self.index_template_path, 'properties.sql']), - tid=tid, idx=row['oid'], + did=did, tid=tid, idx=row['oid'], datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -2738,14 +2739,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) data = res['rows'][0] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) columns = [] @@ -2782,14 +2783,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) data = res['rows'][0] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) columns = [] values = [] @@ -2829,14 +2830,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) data = res['rows'][0] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) columns = [] @@ -2878,7 +2879,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py index fe7d934..d4b9f9d 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py @@ -272,7 +272,7 @@ class ExclusionConstraintView(PGChildNodeView): """ sql = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, cid=exid) + did=did, tid=tid, cid=exid) status, res = self.conn.execute_dict(sql) @@ -384,6 +384,7 @@ class ExclusionConstraintView(PGChildNodeView): SQL = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid) status, res = self.conn.execute_dict(SQL) @@ -649,7 +650,7 @@ class ExclusionConstraintView(PGChildNodeView): try: data['schema'] = self.schema data['table'] = self.table - sql, name = self.get_sql(data, tid, exid) + sql, name = self.get_sql(data, did, tid, exid) sql = sql.strip('\n').strip(' ') status, res = self.conn.execute_scalar(sql) if not status: @@ -764,7 +765,7 @@ class ExclusionConstraintView(PGChildNodeView): data['schema'] = self.schema data['table'] = self.table try: - sql, name = self.get_sql(data, tid, exid) + sql, name = self.get_sql(data, did, tid, exid) sql = sql.strip('\n').strip(' ') if sql == '': sql = "--modified SQL" @@ -776,7 +777,7 @@ class ExclusionConstraintView(PGChildNodeView): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_sql(self, data, tid, exid=None): + def get_sql(self, data, did, tid, exid=None): """ This function will generate sql from model data. @@ -790,6 +791,7 @@ class ExclusionConstraintView(PGChildNodeView): """ if exid is not None: sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=exid) status, res = self.conn.execute_dict(sql) @@ -838,7 +840,7 @@ class ExclusionConstraintView(PGChildNodeView): try: SQL = render_template( "/".join([self.template_path, 'properties.sql']), - tid=tid, conn=self.conn, cid=exid) + did=did, tid=tid, conn=self.conn, cid=exid) status, result = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=result) @@ -926,7 +928,7 @@ class ExclusionConstraintView(PGChildNodeView): # Fetch index details only if extended stats available SQL = render_template( "/".join([self.template_path, 'properties.sql']), - tid=tid, conn=self.conn, cid=exid) + did=did, tid=tid, conn=self.conn, cid=exid) status, result = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=result) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js index e7bd905..02b77e1 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js @@ -653,7 +653,7 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { name: undefined, oid: undefined, comment: undefined, - spcname: "pg_default", + spcname: undefined, amname: "gist", fillfactor: undefined, condeferrable: undefined, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py index dc5c461..d337119 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py @@ -285,6 +285,7 @@ class IndexConstraintView(PGChildNodeView): """ sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=cid, constraint_type=self.constraint_type) @@ -381,6 +382,7 @@ class IndexConstraintView(PGChildNodeView): self.table = row['table'] SQL = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, constraint_type=self.constraint_type) status, res = self.conn.execute_dict(SQL) @@ -664,7 +666,7 @@ class IndexConstraintView(PGChildNodeView): try: data['schema'] = self.schema data['table'] = self.table - sql, name = self.get_sql(data, tid, cid) + sql, name = self.get_sql(data, did, tid, cid) sql = sql.strip('\n').strip(' ') status, res = self.conn.execute_scalar(sql) @@ -784,7 +786,7 @@ class IndexConstraintView(PGChildNodeView): data['schema'] = self.schema data['table'] = self.table try: - sql, name = self.get_sql(data, tid, cid) + sql, name = self.get_sql(data, did, tid, cid) sql = sql.strip('\n').strip(' ') if sql == '': sql = "--modified SQL" @@ -796,7 +798,7 @@ class IndexConstraintView(PGChildNodeView): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_sql(self, data, tid, cid=None): + def get_sql(self, data, did, tid, cid=None): """ This function will generate sql from model data. @@ -810,6 +812,7 @@ class IndexConstraintView(PGChildNodeView): """ if cid is not None: sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=cid, constraint_type=self.constraint_type) @@ -873,6 +876,7 @@ class IndexConstraintView(PGChildNodeView): try: SQL = render_template( "/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, conn=self.conn, cid=cid, @@ -950,6 +954,7 @@ class IndexConstraintView(PGChildNodeView): if is_pgstattuple: # Fetch index details only if extended stats available sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=cid, constraint_type=self.constraint_type) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js index f552972..6439caf 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js @@ -85,7 +85,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { name: undefined, oid: undefined, comment: undefined, - spcname: "pg_default", + spcname: undefined, index: undefined, fillfactor: undefined, condeferrable: undefined, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py index 2bf604f..b0b206a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py @@ -521,7 +521,7 @@ class IndexesView(PGChildNodeView): SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -665,7 +665,7 @@ class IndexesView(PGChildNodeView): # so that we create template for dropping index SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -723,7 +723,7 @@ class IndexesView(PGChildNodeView): data['schema'] = self.schema data['table'] = self.table try: - SQL, name = self.get_sql(scid, tid, idx, data) + SQL, name = self.get_sql(did, scid, tid, idx, data) SQL = SQL.strip('\n').strip(' ') status, res = self.conn.execute_scalar(SQL) if not status: @@ -765,7 +765,7 @@ class IndexesView(PGChildNodeView): data['table'] = self.table try: - sql, name = self.get_sql(scid, tid, idx, data, mode='create') + sql, name = self.get_sql(did, scid, tid, idx, data, mode='create') sql = sql.strip('\n').strip(' ') if sql == '': sql = "--modified SQL" @@ -776,14 +776,14 @@ class IndexesView(PGChildNodeView): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_sql(self, scid, tid, idx, data, mode=None): + def get_sql(self, did, scid, tid, idx, data, mode=None): """ This function will genrate sql from model data """ if idx is not None: SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -842,7 +842,7 @@ class IndexesView(PGChildNodeView): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -857,7 +857,7 @@ class IndexesView(PGChildNodeView): # Add column details for current index data = self._column_details(idx, data) - SQL, name = self.get_sql(scid, tid, None, data) + SQL, name = self.get_sql(did, scid, tid, None, data) sql_header = "-- Index: {0}\n\n-- ".format(data['name']) if hasattr(str, 'decode'): @@ -955,7 +955,7 @@ class IndexesView(PGChildNodeView): # Fetch index details only if extended stats available SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js index 6f46265..7ca2da4 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js @@ -259,7 +259,7 @@ function($, _, S, pgAdmin, pgBrowser, Backform, alertify) { oid: undefined, nspname: undefined, tabname: undefined, - spcname: 'pg_default', + spcname: undefined, amname: 'btree' }, schema: [{ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/properties.sql index 3a1c897..ba3a981 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/properties.sql @@ -2,7 +2,11 @@ SELECT cls.oid, cls.relname as name, indnatts, amname, - COALESCE(spcname, 'pg_default') as spcname, + CASE WHEN length(spcname) > 0 THEN 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, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/properties.sql index 3a1c897..ba3a981 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/properties.sql @@ -2,7 +2,11 @@ SELECT cls.oid, cls.relname as name, indnatts, amname, - COALESCE(spcname, 'pg_default') as spcname, + CASE WHEN length(spcname) > 0 THEN 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, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/properties.sql index 3a1c897..ba3a981 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/properties.sql @@ -2,7 +2,11 @@ SELECT cls.oid, cls.relname as name, indnatts, amname, - COALESCE(spcname, 'pg_default') as spcname, + CASE WHEN length(spcname) > 0 THEN 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, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/properties.sql index 2641d4c..1204234 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/properties.sql @@ -1,6 +1,11 @@ SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid, indkey, indisclustered, indisvalid, indisunique, indisprimary, n.nspname,indnatts,cls.reltablespace AS spcoid, - COALESCE(spcname, 'pg_default') as spcname, tab.relname as tabname, indclass, con.oid AS conoid, + CASE WHEN length(spcname) > 0 THEN 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, + tab.relname as tabname, indclass, con.oid AS conoid, CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description ELSE des.description END AS description, pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql index 0eabdd7..9e392eb 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql @@ -1,7 +1,11 @@ SELECT cls.oid, cls.relname as name, indnatts, - COALESCE(spcname, 'pg_default') as spcname, + CASE WHEN length(spcname) > 0 THEN 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, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js index c87a293..2552cc7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js @@ -253,7 +253,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { name: undefined, oid: undefined, spcoid: undefined, - spcname: 'pg_default', + spcname: undefined, relowner: undefined, relacl: undefined, relhasoids: undefined, @@ -315,7 +315,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { },{ id: 'spcname', label:'{{ _('Tablespace') }}', node: 'tablespace', type: 'text', control: 'node-list-by-name', disabled: 'inSchema', - mode: ['properties', 'create', 'edit'], select2:{allowClear:false}, + mode: ['properties', 'create', 'edit'], filter: function(d) { // If tablespace name is not "pg_global" then we need to exclude them return (!(d && d.label.match(/pg_global/))) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/create.sql index ba91ac5..d7136f7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/create.sql @@ -79,10 +79,12 @@ WITH ( 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 %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql index d995d00..a16cf87 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.1_plus/properties.sql @@ -1,5 +1,9 @@ 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 'pg_default' END) as spcname, + (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, des.description, con.conname, con.conkey, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql index d995d00..a16cf87 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/9.5_plus/properties.sql @@ -1,5 +1,9 @@ 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 'pg_default' END) as spcname, + (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, des.description, con.conname, con.conkey, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py index 76d57c8..5a2a578 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py @@ -476,7 +476,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): ) ) try: - SQL, nameOrError = self.getSQL(gid, sid, data) + SQL, nameOrError = self.getSQL(gid, sid, did, data) if SQL is None: return nameOrError SQL = SQL.strip('\n').strip(' ') @@ -520,7 +520,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): request.data, encoding='utf-8' ) try: - SQL, nameOrError = self.getSQL(gid, sid, data, vid) + SQL, nameOrError = self.getSQL(gid, sid, did, data, vid) if SQL is None: return nameOrError SQL = SQL.strip('\n').strip(' ') @@ -645,7 +645,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): except ValueError: data[k] = v - sql, nameOrError = self.getSQL(gid, sid, data, vid) + sql, nameOrError = self.getSQL(gid, sid, did, data, vid) if sql is None: return nameOrError @@ -659,7 +659,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): status=200 ) - def getSQL(self, gid, sid, data, vid=None): + def getSQL(self, gid, sid, did, data, vid=None): """ This function will generate sql from model data """ @@ -915,7 +915,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): return SQL_data - def get_index_sql(self, vid): + def get_index_sql(self, did, vid): """ Get all index associated with view node, generate their sql and render @@ -925,7 +925,8 @@ class ViewNode(PGChildNodeView, VacuumSettings): self.index_temp_path = 'index' SQL_data = '' SQL = render_template("/".join( - [self.index_temp_path, 'sql/9.1_plus/properties.sql']), tid=vid) + [self.index_temp_path, 'sql/9.1_plus/properties.sql']), + did=did, tid=vid) status, data = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=data) @@ -935,6 +936,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): SQL = render_template("/".join( [self.index_temp_path, 'sql/9.1_plus/properties.sql']), idx=index['oid'], + did=did, tid=vid ) status, res = self.conn.execute_dict(SQL) @@ -1021,7 +1023,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): SQL_data += SQL SQL_data += self.get_rule_sql(vid) SQL_data += self.get_trigger_sql(vid) - SQL_data += self.get_index_sql(vid) + SQL_data += self.get_index_sql(did, vid) return ajax_response(response=SQL_data) @@ -1273,13 +1275,14 @@ class MViewNode(ViewNode, VacuumSettings): '9.3_plus' ) - def getSQL(self, gid, sid, data, vid=None): + def getSQL(self, gid, sid, did, data, vid=None): """ This function will generate sql from model data """ if vid is not None: SQL = render_template("/".join( [self.template_path, 'sql/properties.sql']), + did=did, vid=vid, datlastsysoid=self.datlastsysoid ) @@ -1466,6 +1469,7 @@ class MViewNode(ViewNode, VacuumSettings): SQL_data = '' SQL = render_template("/".join( [self.template_path, 'sql/properties.sql']), + did=did, vid=vid, datlastsysoid=self.datlastsysoid ) @@ -1544,7 +1548,7 @@ class MViewNode(ViewNode, VacuumSettings): SQL_data += SQL SQL_data += self.get_rule_sql(vid) SQL_data += self.get_trigger_sql(vid) - SQL_data += self.get_index_sql(vid) + SQL_data += self.get_index_sql(did, vid) SQL_data = SQL_data.strip('\n') return ajax_response(response=SQL_data) @@ -1590,7 +1594,7 @@ class MViewNode(ViewNode, VacuumSettings): """ SQL = render_template("/".join( [self.template_path, 'sql/properties.sql'] - ), vid=vid, datlastsysoid=self.datlastsysoid) + ), did=did, vid=vid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js index e233b5c..e32594c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js @@ -126,7 +126,7 @@ function($, _, S, pgAdmin, alertify, pgBrowser, CodeMirror) { pgBrowser.Node.Model.prototype.initialize.apply(this, arguments); }, defaults: { - spcname: 'pg_default', + spcname: undefined, toast_autovacuum_enabled: false, autovacuum_enabled: false }, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql index 8a70522..eb36c91 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql @@ -6,7 +6,11 @@ SELECT c.relname AS name, c.reltablespace AS spcoid, c.relispopulated AS with_data, - (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname, + CASE WHEN length(spcname) > 0 THEN 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, c.relacl, nsp.nspname as schema, pg_get_userbyid(c.relowner) AS owner, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql index 8a70522..eb36c91 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql @@ -6,7 +6,11 @@ SELECT c.relname AS name, c.reltablespace AS spcoid, c.relispopulated AS with_data, - (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname, + CASE WHEN length(spcname) > 0 THEN 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, c.relacl, nsp.nspname as schema, pg_get_userbyid(c.relowner) AS owner, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql index 8a70522..eb36c91 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql @@ -6,7 +6,11 @@ SELECT c.relname AS name, c.reltablespace AS spcoid, c.relispopulated AS with_data, - (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname, + CASE WHEN length(spcname) > 0 THEN 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, c.relacl, nsp.nspname as schema, pg_get_userbyid(c.relowner) AS owner, ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-01-30 12:50 Dave Page <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Dave Page @ 2017-01-30 12:50 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: pgadmin-hackers Hi Can you rebase this please? Thanks. On Fri, Jan 27, 2017 at 11:53 AM, Khushboo Vashi <[email protected]> wrote: > Hi, > > Please find the attached updated patch. > > Thanks, > Khushboo > > On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> wrote: >> >> On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi >> <[email protected]> wrote: >> > Hi, >> > >> > Please find the attached patch to fix the RM 2069 - Wrong tablespace >> > displayed in table properties. >> > >> > When the table is created without a tablespace, the "pg_default" >> > tablespace >> > gets selected rather than the default tablespace (the current database >> > tablespace). >> > So, this issue has been fixed. >> >> Unfortunately more work is required. I started hacking on it (see >> attached), but there is more effort needed and I'm out of time. >> Specifically: >> >> - We need to remove 'pg_default' as the default tablespace when >> creating objects. Let's have no default, and let PG automatically pick >> the right tablespace, unless the user specifically overrides it. My >> updated patch removes that default, and fixes some of the SQL to >> handle it. >> > Done >> >> - Matviews don't list the correct tablespace in their properties list. >> > Done >> >> - Unique constraints ignore the tablespace option if the user selects >> pg_default, even if the database's default is something different. >> > Done >> >> So I think to complete this we need to review all objects that use >> tablespaces (databases, tables, matviews, indexes, index-backed >> constraints) and ensure: >> > Done >> >> - No default tablespace is selected when creating an object. >> > Done >> >> - If the user selects a tablespace, the CREATE SQL must handle it >> properly. Similarly, if they leave it to the default. >> > Done >> >> - The Properties views must display the actual tablespace in use, >> whether it is specific to the object, or the database default. >> > Done >> >> Thanks. >> >> -- >> Dave Page >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EnterpriseDB UK: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-02-03 06:23 Khushboo Vashi <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Khushboo Vashi @ 2017-02-03 06:23 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi, Please find the attached rebased patch. Thanks, Khushboo On Mon, Jan 30, 2017 at 6:20 PM, Dave Page <[email protected]> wrote: > Hi > > Can you rebase this please? > > Thanks. > > On Fri, Jan 27, 2017 at 11:53 AM, Khushboo Vashi > <[email protected]> wrote: > > Hi, > > > > Please find the attached updated patch. > > > > Thanks, > > Khushboo > > > > On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> wrote: > >> > >> On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi > >> <[email protected]> wrote: > >> > Hi, > >> > > >> > Please find the attached patch to fix the RM 2069 - Wrong tablespace > >> > displayed in table properties. > >> > > >> > When the table is created without a tablespace, the "pg_default" > >> > tablespace > >> > gets selected rather than the default tablespace (the current database > >> > tablespace). > >> > So, this issue has been fixed. > >> > >> Unfortunately more work is required. I started hacking on it (see > >> attached), but there is more effort needed and I'm out of time. > >> Specifically: > >> > >> - We need to remove 'pg_default' as the default tablespace when > >> creating objects. Let's have no default, and let PG automatically pick > >> the right tablespace, unless the user specifically overrides it. My > >> updated patch removes that default, and fixes some of the SQL to > >> handle it. > >> > > Done > >> > >> - Matviews don't list the correct tablespace in their properties list. > >> > > Done > >> > >> - Unique constraints ignore the tablespace option if the user selects > >> pg_default, even if the database's default is something different. > >> > > Done > >> > >> So I think to complete this we need to review all objects that use > >> tablespaces (databases, tables, matviews, indexes, index-backed > >> constraints) and ensure: > >> > > Done > >> > >> - No default tablespace is selected when creating an object. > >> > > Done > >> > >> - If the user selects a tablespace, the CREATE SQL must handle it > >> properly. Similarly, if they leave it to the default. > >> > > Done > >> > >> - The Properties views must display the actual tablespace in use, > >> whether it is specific to the object, or the database default. > >> > > Done > >> > >> Thanks. > >> > >> -- > >> Dave Page > >> Blog: http://pgsnake.blogspot.com > >> Twitter: @pgsnake > >> > >> EnterpriseDB UK: http://www.enterprisedb.com > >> The Enterprise PostgreSQL Company > > > > > > > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [text/x-patch] RM_2069_ver2.patch (42.2K, 3-RM_2069_ver2.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py index 9292989..7921f8e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py @@ -134,7 +134,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - This function is used to return modified SQL for the selected Table node - * get_sql(data, scid, tid) + * get_sql(did, scid, tid, data) - This function will generate sql from model data * sql(gid, sid, did, scid, tid): @@ -182,7 +182,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - It will return formatted output of query result as per client model format for column node - * _index_constraints_formatter(self, tid, data): + * _index_constraints_formatter(self, did, tid, data): - It will return formatted output of query result as per client model format for index constraint node @@ -195,7 +195,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - This function will parse and return formatted list of columns added by user - * get_index_constraint_sql(self, tid, data): + * get_index_constraint_sql(self, did, tid, data): - This function will generate modified sql for index constraints (Primary Key & Unique) @@ -332,7 +332,8 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, datlastsysoid=self.datlastsysoid) + did=did, scid=scid, + datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -725,7 +726,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): return data - def _index_constraints_formatter(self, tid, data): + def _index_constraints_formatter(self, did, tid, data): """ Args: tid: Table OID @@ -746,7 +747,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): sql = render_template("/".join([self.index_constraint_template_path, 'properties.sql']), - tid=tid, + did=did, tid=tid, constraint_type=ctype) status, res = self.conn.execute_dict(sql) @@ -874,7 +875,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): return data - def _exclusion_constraint_formatter(self, tid, data): + def _exclusion_constraint_formatter(self, did, tid, data): """ Args: tid: Table OID @@ -888,7 +889,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): # We will fetch all the index constraints for the table sql = render_template("/".join([self.exclusion_constraint_template_path, 'properties.sql']), - tid=tid) + did=did, tid=tid) status, result = self.conn.execute_dict(sql) @@ -971,7 +972,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): return None - def _formatter(self, scid, tid, data): + def _formatter(self, did, scid, tid, data): """ Args: data: dict of query result @@ -1090,10 +1091,10 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data = self._columns_formatter(tid, data) # Here we will add constraint in our output - data = self._index_constraints_formatter(tid, data) + data = self._index_constraints_formatter(did, tid, data) data = self._foreign_key_formatter(tid, data) data = self._check_constraint_formatter(tid, data) - data = self._exclusion_constraint_formatter(tid, data) + data = self._exclusion_constraint_formatter(did, tid, data) return data @@ -1116,7 +1117,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1139,7 +1140,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): 'vacuum_settings_str' ].replace("=", " = ") - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) return ajax_response( response=data, @@ -1486,7 +1487,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = SQL.strip('\n').strip(' ') status, res = self.conn.execute_scalar(SQL) @@ -1535,7 +1536,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1596,7 +1597,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1644,7 +1645,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1724,7 +1725,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = re.sub('\n{2,}', '\n\n', SQL) SQL = SQL.strip('\n') if SQL == '': @@ -1736,7 +1737,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_index_constraint_sql(self, tid, data): + def get_index_constraint_sql(self, did, tid, data): """ Args: tid: Table ID @@ -1778,7 +1779,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): properties_sql = render_template("/".join( [self.index_constraint_template_path, 'properties.sql']), - tid=tid, cid=c['oid'], constraint_type=ctype) + did=did, tid=tid, cid=c['oid'], constraint_type=ctype) status, res = self.conn.execute_dict(properties_sql) if not status: return internal_server_error(errormsg=res) @@ -2092,7 +2093,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): else: return None - def get_sql(self, scid, tid, data): + def get_sql(self, did, scid, tid, data): """ This function will generate create/update sql from model data coming from client @@ -2100,14 +2101,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): if tid is not None: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) old_data = res['rows'][0] - old_data = self._formatter(scid, tid, old_data) + old_data = self._formatter(did, scid, tid, old_data) # We will convert privileges coming from client required if 'relacl' in data: @@ -2259,7 +2260,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL += column_sql.strip('\n') # Check if index constraints are added/changed/deleted - index_constraint_sql = self.get_index_constraint_sql(tid, data) + index_constraint_sql = self.get_index_constraint_sql(did, tid, data) # If we have index constraint sql then ad it in main sql if index_constraint_sql is not None: SQL += '\n' + index_constraint_sql @@ -2456,7 +2457,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2468,7 +2469,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): schema = data['schema'] table = data['name'] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) # Now we have all lis of columns which we need # to include in our create definition, Let's format them @@ -2524,7 +2525,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL = render_template("/".join([self.index_template_path, 'properties.sql']), - tid=tid, idx=row['oid'], + did=did, tid=tid, idx=row['oid'], datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -2726,14 +2727,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) data = res['rows'][0] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) columns = [] @@ -2770,14 +2771,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) data = res['rows'][0] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) columns = [] values = [] @@ -2817,14 +2818,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) data = res['rows'][0] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) columns = [] @@ -2866,7 +2867,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py index 06e9022..036dbc4 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py @@ -266,7 +266,7 @@ class ExclusionConstraintView(PGChildNodeView): """ sql = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, cid=exid) + did=did, tid=tid, cid=exid) status, res = self.conn.execute_dict(sql) @@ -374,6 +374,7 @@ class ExclusionConstraintView(PGChildNodeView): SQL = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid) status, res = self.conn.execute_dict(SQL) @@ -635,7 +636,7 @@ class ExclusionConstraintView(PGChildNodeView): try: data['schema'] = self.schema data['table'] = self.table - sql, name = self.get_sql(data, tid, exid) + sql, name = self.get_sql(data, did, tid, exid) sql = sql.strip('\n').strip(' ') status, res = self.conn.execute_scalar(sql) if not status: @@ -750,7 +751,7 @@ class ExclusionConstraintView(PGChildNodeView): data['schema'] = self.schema data['table'] = self.table try: - sql, name = self.get_sql(data, tid, exid) + sql, name = self.get_sql(data, did, tid, exid) sql = sql.strip('\n').strip(' ') if sql == '': sql = "--modified SQL" @@ -762,7 +763,7 @@ class ExclusionConstraintView(PGChildNodeView): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_sql(self, data, tid, exid=None): + def get_sql(self, data, did, tid, exid=None): """ This function will generate sql from model data. @@ -776,6 +777,7 @@ class ExclusionConstraintView(PGChildNodeView): """ if exid is not None: sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=exid) status, res = self.conn.execute_dict(sql) @@ -824,7 +826,7 @@ class ExclusionConstraintView(PGChildNodeView): try: SQL = render_template( "/".join([self.template_path, 'properties.sql']), - tid=tid, conn=self.conn, cid=exid) + did=did, tid=tid, conn=self.conn, cid=exid) status, result = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=result) @@ -912,7 +914,7 @@ class ExclusionConstraintView(PGChildNodeView): # Fetch index details only if extended stats available SQL = render_template( "/".join([self.template_path, 'properties.sql']), - tid=tid, conn=self.conn, cid=exid) + did=did, tid=tid, conn=self.conn, cid=exid) status, result = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=result) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js index e7bd905..02b77e1 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js @@ -653,7 +653,7 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { name: undefined, oid: undefined, comment: undefined, - spcname: "pg_default", + spcname: undefined, amname: "gist", fillfactor: undefined, condeferrable: undefined, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py index dc5c461..d337119 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py @@ -285,6 +285,7 @@ class IndexConstraintView(PGChildNodeView): """ sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=cid, constraint_type=self.constraint_type) @@ -381,6 +382,7 @@ class IndexConstraintView(PGChildNodeView): self.table = row['table'] SQL = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, constraint_type=self.constraint_type) status, res = self.conn.execute_dict(SQL) @@ -664,7 +666,7 @@ class IndexConstraintView(PGChildNodeView): try: data['schema'] = self.schema data['table'] = self.table - sql, name = self.get_sql(data, tid, cid) + sql, name = self.get_sql(data, did, tid, cid) sql = sql.strip('\n').strip(' ') status, res = self.conn.execute_scalar(sql) @@ -784,7 +786,7 @@ class IndexConstraintView(PGChildNodeView): data['schema'] = self.schema data['table'] = self.table try: - sql, name = self.get_sql(data, tid, cid) + sql, name = self.get_sql(data, did, tid, cid) sql = sql.strip('\n').strip(' ') if sql == '': sql = "--modified SQL" @@ -796,7 +798,7 @@ class IndexConstraintView(PGChildNodeView): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_sql(self, data, tid, cid=None): + def get_sql(self, data, did, tid, cid=None): """ This function will generate sql from model data. @@ -810,6 +812,7 @@ class IndexConstraintView(PGChildNodeView): """ if cid is not None: sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=cid, constraint_type=self.constraint_type) @@ -873,6 +876,7 @@ class IndexConstraintView(PGChildNodeView): try: SQL = render_template( "/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, conn=self.conn, cid=cid, @@ -950,6 +954,7 @@ class IndexConstraintView(PGChildNodeView): if is_pgstattuple: # Fetch index details only if extended stats available sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=cid, constraint_type=self.constraint_type) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js index f552972..6439caf 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js @@ -85,7 +85,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { name: undefined, oid: undefined, comment: undefined, - spcname: "pg_default", + spcname: undefined, index: undefined, fillfactor: undefined, condeferrable: undefined, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py index e5093a2..4403cd8 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py @@ -521,7 +521,7 @@ class IndexesView(PGChildNodeView): SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -665,7 +665,7 @@ class IndexesView(PGChildNodeView): # so that we create template for dropping index SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -723,7 +723,7 @@ class IndexesView(PGChildNodeView): data['schema'] = self.schema data['table'] = self.table try: - SQL, name = self.get_sql(scid, tid, idx, data) + SQL, name = self.get_sql(did, scid, tid, idx, data) SQL = SQL.strip('\n').strip(' ') status, res = self.conn.execute_scalar(SQL) if not status: @@ -765,7 +765,7 @@ class IndexesView(PGChildNodeView): data['table'] = self.table try: - sql, name = self.get_sql(scid, tid, idx, data, mode='create') + sql, name = self.get_sql(did, scid, tid, idx, data, mode='create') sql = sql.strip('\n').strip(' ') if sql == '': sql = "--modified SQL" @@ -776,14 +776,14 @@ class IndexesView(PGChildNodeView): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_sql(self, scid, tid, idx, data, mode=None): + def get_sql(self, did, scid, tid, idx, data, mode=None): """ This function will genrate sql from model data """ if idx is not None: SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -842,7 +842,7 @@ class IndexesView(PGChildNodeView): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -857,7 +857,7 @@ class IndexesView(PGChildNodeView): # Add column details for current index data = self._column_details(idx, data) - SQL, name = self.get_sql(scid, tid, None, data) + SQL, name = self.get_sql(did, scid, tid, None, data) sql_header = "-- Index: {0}\n\n-- ".format(data['name']) if hasattr(str, 'decode'): @@ -955,7 +955,7 @@ class IndexesView(PGChildNodeView): # Fetch index details only if extended stats available SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js index 6f46265..7ca2da4 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js @@ -259,7 +259,7 @@ function($, _, S, pgAdmin, pgBrowser, Backform, alertify) { oid: undefined, nspname: undefined, tabname: undefined, - spcname: 'pg_default', + spcname: undefined, amname: 'btree' }, schema: [{ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql index 3a1c897..ba3a981 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql @@ -2,7 +2,11 @@ SELECT cls.oid, cls.relname as name, indnatts, amname, - COALESCE(spcname, 'pg_default') as spcname, + CASE WHEN length(spcname) > 0 THEN 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, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/default/properties.sql index 2641d4c..1204234 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/default/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/default/properties.sql @@ -1,6 +1,11 @@ SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid, indkey, indisclustered, indisvalid, indisunique, indisprimary, n.nspname,indnatts,cls.reltablespace AS spcoid, - COALESCE(spcname, 'pg_default') as spcname, tab.relname as tabname, indclass, con.oid AS conoid, + CASE WHEN length(spcname) > 0 THEN 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, + tab.relname as tabname, indclass, con.oid AS conoid, CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description ELSE des.description END AS description, pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql index 0eabdd7..9e392eb 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql @@ -1,7 +1,11 @@ SELECT cls.oid, cls.relname as name, indnatts, - COALESCE(spcname, 'pg_default') as spcname, + CASE WHEN length(spcname) > 0 THEN 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, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js index c87a293..2552cc7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js @@ -253,7 +253,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { name: undefined, oid: undefined, spcoid: undefined, - spcname: 'pg_default', + spcname: undefined, relowner: undefined, relacl: undefined, relhasoids: undefined, @@ -315,7 +315,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { },{ id: 'spcname', label:'{{ _('Tablespace') }}', node: 'tablespace', type: 'text', control: 'node-list-by-name', disabled: 'inSchema', - mode: ['properties', 'create', 'edit'], select2:{allowClear:false}, + mode: ['properties', 'create', 'edit'], filter: function(d) { // If tablespace name is not "pg_global" then we need to exclude them return (!(d && d.label.match(/pg_global/))) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql index ba91ac5..d7136f7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql @@ -79,10 +79,12 @@ WITH ( 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 %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql index d995d00..9bfb84a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql @@ -1,5 +1,9 @@ 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 'pg_default' END) as spcname, + (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, des.description, con.conname, con.conkey, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py index ff43312..08bdd0c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py @@ -462,7 +462,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): ) ) try: - SQL, nameOrError = self.getSQL(gid, sid, data) + SQL, nameOrError = self.getSQL(gid, sid, did, data) if SQL is None: return nameOrError SQL = SQL.strip('\n').strip(' ') @@ -506,7 +506,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): request.data, encoding='utf-8' ) try: - SQL, nameOrError = self.getSQL(gid, sid, data, vid) + SQL, nameOrError = self.getSQL(gid, sid, did, data, vid) if SQL is None: return nameOrError SQL = SQL.strip('\n').strip(' ') @@ -631,7 +631,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): except ValueError: data[k] = v - sql, nameOrError = self.getSQL(gid, sid, data, vid) + sql, nameOrError = self.getSQL(gid, sid, did, data, vid) if sql is None: return nameOrError @@ -645,7 +645,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): status=200 ) - def getSQL(self, gid, sid, data, vid=None): + def getSQL(self, gid, sid, did, data, vid=None): """ This function will generate sql from model data """ @@ -901,7 +901,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): return SQL_data - def get_index_sql(self, vid): + def get_index_sql(self, did, vid): """ Get all index associated with view node, generate their sql and render @@ -910,10 +910,12 @@ class ViewNode(PGChildNodeView, VacuumSettings): self.index_temp_path = 'index' SQL_data = '' + SQL = render_template("/".join( + [self.index_temp_path, 'sql/#{0}#/properties.sql'.format(self.manager.version)]), + did=did, + tid=vid) status, data = self.conn.execute_dict(SQL) if not status: - SQL = render_template("/".join( - [self.index_temp_path, 'sql/#{0}#/properties.sql'.format(self.manager.version)]), tid=vid) return internal_server_error(errormsg=data) for index in data['rows']: @@ -921,6 +923,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): SQL = render_template("/".join( [self.index_temp_path, 'sql/#{0}#/properties.sql'.format(self.manager.version)]), idx=index['oid'], + did=did, tid=vid ) status, res = self.conn.execute_dict(SQL) @@ -1007,7 +1010,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): SQL_data += SQL SQL_data += self.get_rule_sql(vid) SQL_data += self.get_trigger_sql(vid) - SQL_data += self.get_index_sql(vid) + SQL_data += self.get_index_sql(did, vid) return ajax_response(response=SQL_data) @@ -1259,13 +1262,14 @@ class MViewNode(ViewNode, VacuumSettings): '9.3_plus' ) - def getSQL(self, gid, sid, data, vid=None): + def getSQL(self, gid, sid, did, data, vid=None): """ This function will generate sql from model data """ if vid is not None: SQL = render_template("/".join( [self.template_path, 'sql/properties.sql']), + did=did, vid=vid, datlastsysoid=self.datlastsysoid ) @@ -1452,6 +1456,7 @@ class MViewNode(ViewNode, VacuumSettings): SQL_data = '' SQL = render_template("/".join( [self.template_path, 'sql/properties.sql']), + did=did, vid=vid, datlastsysoid=self.datlastsysoid ) @@ -1530,7 +1535,7 @@ class MViewNode(ViewNode, VacuumSettings): SQL_data += SQL SQL_data += self.get_rule_sql(vid) SQL_data += self.get_trigger_sql(vid) - SQL_data += self.get_index_sql(vid) + SQL_data += self.get_index_sql(did, vid) SQL_data = SQL_data.strip('\n') return ajax_response(response=SQL_data) @@ -1576,7 +1581,7 @@ class MViewNode(ViewNode, VacuumSettings): """ SQL = render_template("/".join( [self.template_path, 'sql/properties.sql'] - ), vid=vid, datlastsysoid=self.datlastsysoid) + ), did=did, vid=vid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js index e233b5c..e32594c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js @@ -126,7 +126,7 @@ function($, _, S, pgAdmin, alertify, pgBrowser, CodeMirror) { pgBrowser.Node.Model.prototype.initialize.apply(this, arguments); }, defaults: { - spcname: 'pg_default', + spcname: undefined, toast_autovacuum_enabled: false, autovacuum_enabled: false }, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql index 8a70522..eb36c91 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql @@ -6,7 +6,11 @@ SELECT c.relname AS name, c.reltablespace AS spcoid, c.relispopulated AS with_data, - (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname, + CASE WHEN length(spcname) > 0 THEN 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, c.relacl, nsp.nspname as schema, pg_get_userbyid(c.relowner) AS owner, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql index 8a70522..eb36c91 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql @@ -6,7 +6,11 @@ SELECT c.relname AS name, c.reltablespace AS spcoid, c.relispopulated AS with_data, - (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname, + CASE WHEN length(spcname) > 0 THEN 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, c.relacl, nsp.nspname as schema, pg_get_userbyid(c.relowner) AS owner, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql index 8a70522..eb36c91 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql @@ -6,7 +6,11 @@ SELECT c.relname AS name, c.reltablespace AS spcoid, c.relispopulated AS with_data, - (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname, + CASE WHEN length(spcname) > 0 THEN 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, c.relacl, nsp.nspname as schema, pg_get_userbyid(c.relowner) AS owner, ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-02-03 06:44 Khushboo Vashi <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Khushboo Vashi @ 2017-02-03 06:44 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi, On Fri, Feb 3, 2017 at 11:53 AM, Khushboo Vashi < [email protected]> wrote: > Hi, > > Please find the attached rebased patch. > > This patch also include fix for RM 2139: Message (Connection to the server has been lost.) displayed with Materialized view and view under sql tab > Thanks, > Khushboo > > On Mon, Jan 30, 2017 at 6:20 PM, Dave Page <[email protected]> wrote: > >> Hi >> >> Can you rebase this please? >> >> Thanks. >> >> On Fri, Jan 27, 2017 at 11:53 AM, Khushboo Vashi >> <[email protected]> wrote: >> > Hi, >> > >> > Please find the attached updated patch. >> > >> > Thanks, >> > Khushboo >> > >> > On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> wrote: >> >> >> >> On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi >> >> <[email protected]> wrote: >> >> > Hi, >> >> > >> >> > Please find the attached patch to fix the RM 2069 - Wrong tablespace >> >> > displayed in table properties. >> >> > >> >> > When the table is created without a tablespace, the "pg_default" >> >> > tablespace >> >> > gets selected rather than the default tablespace (the current >> database >> >> > tablespace). >> >> > So, this issue has been fixed. >> >> >> >> Unfortunately more work is required. I started hacking on it (see >> >> attached), but there is more effort needed and I'm out of time. >> >> Specifically: >> >> >> >> - We need to remove 'pg_default' as the default tablespace when >> >> creating objects. Let's have no default, and let PG automatically pick >> >> the right tablespace, unless the user specifically overrides it. My >> >> updated patch removes that default, and fixes some of the SQL to >> >> handle it. >> >> >> > Done >> >> >> >> - Matviews don't list the correct tablespace in their properties list. >> >> >> > Done >> >> >> >> - Unique constraints ignore the tablespace option if the user selects >> >> pg_default, even if the database's default is something different. >> >> >> > Done >> >> >> >> So I think to complete this we need to review all objects that use >> >> tablespaces (databases, tables, matviews, indexes, index-backed >> >> constraints) and ensure: >> >> >> > Done >> >> >> >> - No default tablespace is selected when creating an object. >> >> >> > Done >> >> >> >> - If the user selects a tablespace, the CREATE SQL must handle it >> >> properly. Similarly, if they leave it to the default. >> >> >> > Done >> >> >> >> - The Properties views must display the actual tablespace in use, >> >> whether it is specific to the object, or the database default. >> >> >> > Done >> >> >> >> Thanks. >> >> >> >> -- >> >> Dave Page >> >> Blog: http://pgsnake.blogspot.com >> >> Twitter: @pgsnake >> >> >> >> EnterpriseDB UK: http://www.enterprisedb.com >> >> The Enterprise PostgreSQL Company >> > >> > >> >> >> >> -- >> Dave Page >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EnterpriseDB UK: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> > > ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-02-03 07:38 Akshay Joshi <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Akshay Joshi @ 2017-02-03 07:38 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: Dave Page <[email protected]>; pgadmin-hackers Hi Khushboo After applying latest patch I found one error(refer attached screenshot) when clicking on "Materialized Views" collection node. On Fri, Feb 3, 2017 at 12:14 PM, Khushboo Vashi < [email protected]> wrote: > Hi, > > On Fri, Feb 3, 2017 at 11:53 AM, Khushboo Vashi < > [email protected]> wrote: > >> Hi, >> >> Please find the attached rebased patch. >> >> This patch also include fix for RM 2139: Message (Connection to the > server has been lost.) displayed with Materialized view and view under sql > tab > > > >> Thanks, >> Khushboo >> >> On Mon, Jan 30, 2017 at 6:20 PM, Dave Page <[email protected]> wrote: >> >>> Hi >>> >>> Can you rebase this please? >>> >>> Thanks. >>> >>> On Fri, Jan 27, 2017 at 11:53 AM, Khushboo Vashi >>> <[email protected]> wrote: >>> > Hi, >>> > >>> > Please find the attached updated patch. >>> > >>> > Thanks, >>> > Khushboo >>> > >>> > On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> wrote: >>> >> >>> >> On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi >>> >> <[email protected]> wrote: >>> >> > Hi, >>> >> > >>> >> > Please find the attached patch to fix the RM 2069 - Wrong tablespace >>> >> > displayed in table properties. >>> >> > >>> >> > When the table is created without a tablespace, the "pg_default" >>> >> > tablespace >>> >> > gets selected rather than the default tablespace (the current >>> database >>> >> > tablespace). >>> >> > So, this issue has been fixed. >>> >> >>> >> Unfortunately more work is required. I started hacking on it (see >>> >> attached), but there is more effort needed and I'm out of time. >>> >> Specifically: >>> >> >>> >> - We need to remove 'pg_default' as the default tablespace when >>> >> creating objects. Let's have no default, and let PG automatically pick >>> >> the right tablespace, unless the user specifically overrides it. My >>> >> updated patch removes that default, and fixes some of the SQL to >>> >> handle it. >>> >> >>> > Done >>> >> >>> >> - Matviews don't list the correct tablespace in their properties list. >>> >> >>> > Done >>> >> >>> >> - Unique constraints ignore the tablespace option if the user selects >>> >> pg_default, even if the database's default is something different. >>> >> >>> > Done >>> >> >>> >> So I think to complete this we need to review all objects that use >>> >> tablespaces (databases, tables, matviews, indexes, index-backed >>> >> constraints) and ensure: >>> >> >>> > Done >>> >> >>> >> - No default tablespace is selected when creating an object. >>> >> >>> > Done >>> >> >>> >> - If the user selects a tablespace, the CREATE SQL must handle it >>> >> properly. Similarly, if they leave it to the default. >>> >> >>> > Done >>> >> >>> >> - The Properties views must display the actual tablespace in use, >>> >> whether it is specific to the object, or the database default. >>> >> >>> > Done >>> >> >>> >> Thanks. >>> >> >>> >> -- >>> >> Dave Page >>> >> Blog: http://pgsnake.blogspot.com >>> >> Twitter: @pgsnake >>> >> >>> >> EnterpriseDB UK: http://www.enterprisedb.com >>> >> The Enterprise PostgreSQL Company >>> > >>> > >>> >>> >>> >>> -- >>> Dave Page >>> Blog: http://pgsnake.blogspot.com >>> Twitter: @pgsnake >>> >>> EnterpriseDB UK: http://www.enterprisedb.com >>> The Enterprise PostgreSQL Company >>> >> >> > -- *Akshay Joshi* *Principal Software Engineer * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [image/png] Error.png (39.1K, 3-Error.png) download | view image ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-02-03 07:47 Khushboo Vashi <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Khushboo Vashi @ 2017-02-03 07:47 UTC (permalink / raw) To: Akshay Joshi <[email protected]>; +Cc: Dave Page <[email protected]>; pgadmin-hackers Hi Akshay, Please find the attached updated patch. Thanks, Khushboo On Fri, Feb 3, 2017 at 1:08 PM, Akshay Joshi <[email protected]> wrote: > Hi Khushboo > > After applying latest patch I found one error(refer attached screenshot) > when clicking on "Materialized Views" collection node. > > Fixed > On Fri, Feb 3, 2017 at 12:14 PM, Khushboo Vashi < > [email protected]> wrote: > >> Hi, >> >> On Fri, Feb 3, 2017 at 11:53 AM, Khushboo Vashi < >> [email protected]> wrote: >> >>> Hi, >>> >>> Please find the attached rebased patch. >>> >>> This patch also include fix for RM 2139: Message (Connection to the >> server has been lost.) displayed with Materialized view and view under sql >> tab >> >> >> >>> Thanks, >>> Khushboo >>> >>> On Mon, Jan 30, 2017 at 6:20 PM, Dave Page <[email protected]> wrote: >>> >>>> Hi >>>> >>>> Can you rebase this please? >>>> >>>> Thanks. >>>> >>>> On Fri, Jan 27, 2017 at 11:53 AM, Khushboo Vashi >>>> <[email protected]> wrote: >>>> > Hi, >>>> > >>>> > Please find the attached updated patch. >>>> > >>>> > Thanks, >>>> > Khushboo >>>> > >>>> > On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> wrote: >>>> >> >>>> >> On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi >>>> >> <[email protected]> wrote: >>>> >> > Hi, >>>> >> > >>>> >> > Please find the attached patch to fix the RM 2069 - Wrong >>>> tablespace >>>> >> > displayed in table properties. >>>> >> > >>>> >> > When the table is created without a tablespace, the "pg_default" >>>> >> > tablespace >>>> >> > gets selected rather than the default tablespace (the current >>>> database >>>> >> > tablespace). >>>> >> > So, this issue has been fixed. >>>> >> >>>> >> Unfortunately more work is required. I started hacking on it (see >>>> >> attached), but there is more effort needed and I'm out of time. >>>> >> Specifically: >>>> >> >>>> >> - We need to remove 'pg_default' as the default tablespace when >>>> >> creating objects. Let's have no default, and let PG automatically >>>> pick >>>> >> the right tablespace, unless the user specifically overrides it. My >>>> >> updated patch removes that default, and fixes some of the SQL to >>>> >> handle it. >>>> >> >>>> > Done >>>> >> >>>> >> - Matviews don't list the correct tablespace in their properties >>>> list. >>>> >> >>>> > Done >>>> >> >>>> >> - Unique constraints ignore the tablespace option if the user selects >>>> >> pg_default, even if the database's default is something different. >>>> >> >>>> > Done >>>> >> >>>> >> So I think to complete this we need to review all objects that use >>>> >> tablespaces (databases, tables, matviews, indexes, index-backed >>>> >> constraints) and ensure: >>>> >> >>>> > Done >>>> >> >>>> >> - No default tablespace is selected when creating an object. >>>> >> >>>> > Done >>>> >> >>>> >> - If the user selects a tablespace, the CREATE SQL must handle it >>>> >> properly. Similarly, if they leave it to the default. >>>> >> >>>> > Done >>>> >> >>>> >> - The Properties views must display the actual tablespace in use, >>>> >> whether it is specific to the object, or the database default. >>>> >> >>>> > Done >>>> >> >>>> >> Thanks. >>>> >> >>>> >> -- >>>> >> Dave Page >>>> >> Blog: http://pgsnake.blogspot.com >>>> >> Twitter: @pgsnake >>>> >> >>>> >> EnterpriseDB UK: http://www.enterprisedb.com >>>> >> The Enterprise PostgreSQL Company >>>> > >>>> > >>>> >>>> >>>> >>>> -- >>>> Dave Page >>>> Blog: http://pgsnake.blogspot.com >>>> Twitter: @pgsnake >>>> >>>> EnterpriseDB UK: http://www.enterprisedb.com >>>> The Enterprise PostgreSQL Company >>>> >>> >>> >> > > > -- > *Akshay Joshi* > *Principal Software Engineer * > > > > *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [text/x-patch] RM_2069.ver3.patch (42.6K, 3-RM_2069.ver3.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py index 9292989..7921f8e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py @@ -134,7 +134,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - This function is used to return modified SQL for the selected Table node - * get_sql(data, scid, tid) + * get_sql(did, scid, tid, data) - This function will generate sql from model data * sql(gid, sid, did, scid, tid): @@ -182,7 +182,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - It will return formatted output of query result as per client model format for column node - * _index_constraints_formatter(self, tid, data): + * _index_constraints_formatter(self, did, tid, data): - It will return formatted output of query result as per client model format for index constraint node @@ -195,7 +195,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): - This function will parse and return formatted list of columns added by user - * get_index_constraint_sql(self, tid, data): + * get_index_constraint_sql(self, did, tid, data): - This function will generate modified sql for index constraints (Primary Key & Unique) @@ -332,7 +332,8 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, datlastsysoid=self.datlastsysoid) + did=did, scid=scid, + datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -725,7 +726,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): return data - def _index_constraints_formatter(self, tid, data): + def _index_constraints_formatter(self, did, tid, data): """ Args: tid: Table OID @@ -746,7 +747,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): sql = render_template("/".join([self.index_constraint_template_path, 'properties.sql']), - tid=tid, + did=did, tid=tid, constraint_type=ctype) status, res = self.conn.execute_dict(sql) @@ -874,7 +875,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): return data - def _exclusion_constraint_formatter(self, tid, data): + def _exclusion_constraint_formatter(self, did, tid, data): """ Args: tid: Table OID @@ -888,7 +889,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): # We will fetch all the index constraints for the table sql = render_template("/".join([self.exclusion_constraint_template_path, 'properties.sql']), - tid=tid) + did=did, tid=tid) status, result = self.conn.execute_dict(sql) @@ -971,7 +972,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): return None - def _formatter(self, scid, tid, data): + def _formatter(self, did, scid, tid, data): """ Args: data: dict of query result @@ -1090,10 +1091,10 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data = self._columns_formatter(tid, data) # Here we will add constraint in our output - data = self._index_constraints_formatter(tid, data) + data = self._index_constraints_formatter(did, tid, data) data = self._foreign_key_formatter(tid, data) data = self._check_constraint_formatter(tid, data) - data = self._exclusion_constraint_formatter(tid, data) + data = self._exclusion_constraint_formatter(did, tid, data) return data @@ -1116,7 +1117,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1139,7 +1140,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): 'vacuum_settings_str' ].replace("=", " = ") - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) return ajax_response( response=data, @@ -1486,7 +1487,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = SQL.strip('\n').strip(' ') status, res = self.conn.execute_scalar(SQL) @@ -1535,7 +1536,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1596,7 +1597,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1644,7 +1645,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -1724,7 +1725,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): data[k] = v try: - SQL, name = self.get_sql(scid, tid, data) + SQL, name = self.get_sql(did, scid, tid, data) SQL = re.sub('\n{2,}', '\n\n', SQL) SQL = SQL.strip('\n') if SQL == '': @@ -1736,7 +1737,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_index_constraint_sql(self, tid, data): + def get_index_constraint_sql(self, did, tid, data): """ Args: tid: Table ID @@ -1778,7 +1779,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): properties_sql = render_template("/".join( [self.index_constraint_template_path, 'properties.sql']), - tid=tid, cid=c['oid'], constraint_type=ctype) + did=did, tid=tid, cid=c['oid'], constraint_type=ctype) status, res = self.conn.execute_dict(properties_sql) if not status: return internal_server_error(errormsg=res) @@ -2092,7 +2093,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): else: return None - def get_sql(self, scid, tid, data): + def get_sql(self, did, scid, tid, data): """ This function will generate create/update sql from model data coming from client @@ -2100,14 +2101,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): if tid is not None: SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) old_data = res['rows'][0] - old_data = self._formatter(scid, tid, old_data) + old_data = self._formatter(did, scid, tid, old_data) # We will convert privileges coming from client required if 'relacl' in data: @@ -2259,7 +2260,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL += column_sql.strip('\n') # Check if index constraints are added/changed/deleted - index_constraint_sql = self.get_index_constraint_sql(tid, data) + index_constraint_sql = self.get_index_constraint_sql(did, tid, data) # If we have index constraint sql then ad it in main sql if index_constraint_sql is not None: SQL += '\n' + index_constraint_sql @@ -2456,7 +2457,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: @@ -2468,7 +2469,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): schema = data['schema'] table = data['name'] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) # Now we have all lis of columns which we need # to include in our create definition, Let's format them @@ -2524,7 +2525,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): SQL = render_template("/".join([self.index_template_path, 'properties.sql']), - tid=tid, idx=row['oid'], + did=did, tid=tid, idx=row['oid'], datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -2726,14 +2727,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) data = res['rows'][0] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) columns = [] @@ -2770,14 +2771,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) data = res['rows'][0] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) columns = [] values = [] @@ -2817,14 +2818,14 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) data = res['rows'][0] - data = self._formatter(scid, tid, data) + data = self._formatter(did, scid, tid, data) columns = [] @@ -2866,7 +2867,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings): """ SQL = render_template("/".join([self.template_path, 'properties.sql']), - scid=scid, tid=tid, + did=did, scid=scid, tid=tid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py index 06e9022..036dbc4 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py @@ -266,7 +266,7 @@ class ExclusionConstraintView(PGChildNodeView): """ sql = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, cid=exid) + did=did, tid=tid, cid=exid) status, res = self.conn.execute_dict(sql) @@ -374,6 +374,7 @@ class ExclusionConstraintView(PGChildNodeView): SQL = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid) status, res = self.conn.execute_dict(SQL) @@ -635,7 +636,7 @@ class ExclusionConstraintView(PGChildNodeView): try: data['schema'] = self.schema data['table'] = self.table - sql, name = self.get_sql(data, tid, exid) + sql, name = self.get_sql(data, did, tid, exid) sql = sql.strip('\n').strip(' ') status, res = self.conn.execute_scalar(sql) if not status: @@ -750,7 +751,7 @@ class ExclusionConstraintView(PGChildNodeView): data['schema'] = self.schema data['table'] = self.table try: - sql, name = self.get_sql(data, tid, exid) + sql, name = self.get_sql(data, did, tid, exid) sql = sql.strip('\n').strip(' ') if sql == '': sql = "--modified SQL" @@ -762,7 +763,7 @@ class ExclusionConstraintView(PGChildNodeView): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_sql(self, data, tid, exid=None): + def get_sql(self, data, did, tid, exid=None): """ This function will generate sql from model data. @@ -776,6 +777,7 @@ class ExclusionConstraintView(PGChildNodeView): """ if exid is not None: sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=exid) status, res = self.conn.execute_dict(sql) @@ -824,7 +826,7 @@ class ExclusionConstraintView(PGChildNodeView): try: SQL = render_template( "/".join([self.template_path, 'properties.sql']), - tid=tid, conn=self.conn, cid=exid) + did=did, tid=tid, conn=self.conn, cid=exid) status, result = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=result) @@ -912,7 +914,7 @@ class ExclusionConstraintView(PGChildNodeView): # Fetch index details only if extended stats available SQL = render_template( "/".join([self.template_path, 'properties.sql']), - tid=tid, conn=self.conn, cid=exid) + did=did, tid=tid, conn=self.conn, cid=exid) status, result = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=result) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js index e7bd905..02b77e1 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js @@ -653,7 +653,7 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { name: undefined, oid: undefined, comment: undefined, - spcname: "pg_default", + spcname: undefined, amname: "gist", fillfactor: undefined, condeferrable: undefined, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py index dc5c461..d337119 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/__init__.py @@ -285,6 +285,7 @@ class IndexConstraintView(PGChildNodeView): """ sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=cid, constraint_type=self.constraint_type) @@ -381,6 +382,7 @@ class IndexConstraintView(PGChildNodeView): self.table = row['table'] SQL = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, constraint_type=self.constraint_type) status, res = self.conn.execute_dict(SQL) @@ -664,7 +666,7 @@ class IndexConstraintView(PGChildNodeView): try: data['schema'] = self.schema data['table'] = self.table - sql, name = self.get_sql(data, tid, cid) + sql, name = self.get_sql(data, did, tid, cid) sql = sql.strip('\n').strip(' ') status, res = self.conn.execute_scalar(sql) @@ -784,7 +786,7 @@ class IndexConstraintView(PGChildNodeView): data['schema'] = self.schema data['table'] = self.table try: - sql, name = self.get_sql(data, tid, cid) + sql, name = self.get_sql(data, did, tid, cid) sql = sql.strip('\n').strip(' ') if sql == '': sql = "--modified SQL" @@ -796,7 +798,7 @@ class IndexConstraintView(PGChildNodeView): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_sql(self, data, tid, cid=None): + def get_sql(self, data, did, tid, cid=None): """ This function will generate sql from model data. @@ -810,6 +812,7 @@ class IndexConstraintView(PGChildNodeView): """ if cid is not None: sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=cid, constraint_type=self.constraint_type) @@ -873,6 +876,7 @@ class IndexConstraintView(PGChildNodeView): try: SQL = render_template( "/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, conn=self.conn, cid=cid, @@ -950,6 +954,7 @@ class IndexConstraintView(PGChildNodeView): if is_pgstattuple: # Fetch index details only if extended stats available sql = render_template("/".join([self.template_path, 'properties.sql']), + did=did, tid=tid, cid=cid, constraint_type=self.constraint_type) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js index f552972..6439caf 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/templates/index_constraint/js/index_constraint.js @@ -85,7 +85,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { name: undefined, oid: undefined, comment: undefined, - spcname: "pg_default", + spcname: undefined, index: undefined, fillfactor: undefined, condeferrable: undefined, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py index e5093a2..4403cd8 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/__init__.py @@ -521,7 +521,7 @@ class IndexesView(PGChildNodeView): SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -665,7 +665,7 @@ class IndexesView(PGChildNodeView): # so that we create template for dropping index SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -723,7 +723,7 @@ class IndexesView(PGChildNodeView): data['schema'] = self.schema data['table'] = self.table try: - SQL, name = self.get_sql(scid, tid, idx, data) + SQL, name = self.get_sql(did, scid, tid, idx, data) SQL = SQL.strip('\n').strip(' ') status, res = self.conn.execute_scalar(SQL) if not status: @@ -765,7 +765,7 @@ class IndexesView(PGChildNodeView): data['table'] = self.table try: - sql, name = self.get_sql(scid, tid, idx, data, mode='create') + sql, name = self.get_sql(did, scid, tid, idx, data, mode='create') sql = sql.strip('\n').strip(' ') if sql == '': sql = "--modified SQL" @@ -776,14 +776,14 @@ class IndexesView(PGChildNodeView): except Exception as e: return internal_server_error(errormsg=str(e)) - def get_sql(self, scid, tid, idx, data, mode=None): + def get_sql(self, did, scid, tid, idx, data, mode=None): """ This function will genrate sql from model data """ if idx is not None: SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -842,7 +842,7 @@ class IndexesView(PGChildNodeView): try: SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) @@ -857,7 +857,7 @@ class IndexesView(PGChildNodeView): # Add column details for current index data = self._column_details(idx, data) - SQL, name = self.get_sql(scid, tid, None, data) + SQL, name = self.get_sql(did, scid, tid, None, data) sql_header = "-- Index: {0}\n\n-- ".format(data['name']) if hasattr(str, 'decode'): @@ -955,7 +955,7 @@ class IndexesView(PGChildNodeView): # Fetch index details only if extended stats available SQL = render_template("/".join([self.template_path, 'properties.sql']), - tid=tid, idx=idx, + did=did, tid=tid, idx=idx, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js index 6f46265..7ca2da4 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js @@ -259,7 +259,7 @@ function($, _, S, pgAdmin, pgBrowser, Backform, alertify) { oid: undefined, nspname: undefined, tabname: undefined, - spcname: 'pg_default', + spcname: undefined, amname: 'btree' }, schema: [{ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql index 3a1c897..ba3a981 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/default/properties.sql @@ -2,7 +2,11 @@ SELECT cls.oid, cls.relname as name, indnatts, amname, - COALESCE(spcname, 'pg_default') as spcname, + CASE WHEN length(spcname) > 0 THEN 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, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/default/properties.sql index 2641d4c..1204234 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/default/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/default/properties.sql @@ -1,6 +1,11 @@ SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid, indkey, indisclustered, indisvalid, indisunique, indisprimary, n.nspname,indnatts,cls.reltablespace AS spcoid, - COALESCE(spcname, 'pg_default') as spcname, tab.relname as tabname, indclass, con.oid AS conoid, + CASE WHEN length(spcname) > 0 THEN 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, + tab.relname as tabname, indclass, con.oid AS conoid, CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description ELSE des.description END AS description, pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql index 0eabdd7..9e392eb 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/properties.sql @@ -1,7 +1,11 @@ SELECT cls.oid, cls.relname as name, indnatts, - COALESCE(spcname, 'pg_default') as spcname, + CASE WHEN length(spcname) > 0 THEN 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, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js index c87a293..2552cc7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/js/table.js @@ -253,7 +253,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { name: undefined, oid: undefined, spcoid: undefined, - spcname: 'pg_default', + spcname: undefined, relowner: undefined, relacl: undefined, relhasoids: undefined, @@ -315,7 +315,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) { },{ id: 'spcname', label:'{{ _('Tablespace') }}', node: 'tablespace', type: 'text', control: 'node-list-by-name', disabled: 'inSchema', - mode: ['properties', 'create', 'edit'], select2:{allowClear:false}, + mode: ['properties', 'create', 'edit'], filter: function(d) { // If tablespace name is not "pg_global" then we need to exclude them return (!(d && d.label.match(/pg_global/))) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql index ba91ac5..d7136f7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql @@ -79,10 +79,12 @@ WITH ( 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 %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql index d995d00..9bfb84a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/properties.sql @@ -1,5 +1,9 @@ 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 'pg_default' END) as spcname, + (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, des.description, con.conname, con.conkey, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py index ff43312..d193331 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/__init__.py @@ -320,7 +320,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): Fetches all views properties and render into properties tab """ SQL = render_template("/".join( - [self.template_path, 'sql/properties.sql']), scid=scid) + [self.template_path, 'sql/properties.sql']), did=did, scid=scid) status, res = self.conn.execute_dict(SQL) if not status: @@ -462,7 +462,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): ) ) try: - SQL, nameOrError = self.getSQL(gid, sid, data) + SQL, nameOrError = self.getSQL(gid, sid, did, data) if SQL is None: return nameOrError SQL = SQL.strip('\n').strip(' ') @@ -506,7 +506,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): request.data, encoding='utf-8' ) try: - SQL, nameOrError = self.getSQL(gid, sid, data, vid) + SQL, nameOrError = self.getSQL(gid, sid, did, data, vid) if SQL is None: return nameOrError SQL = SQL.strip('\n').strip(' ') @@ -631,7 +631,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): except ValueError: data[k] = v - sql, nameOrError = self.getSQL(gid, sid, data, vid) + sql, nameOrError = self.getSQL(gid, sid, did, data, vid) if sql is None: return nameOrError @@ -645,7 +645,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): status=200 ) - def getSQL(self, gid, sid, data, vid=None): + def getSQL(self, gid, sid, did, data, vid=None): """ This function will generate sql from model data """ @@ -901,7 +901,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): return SQL_data - def get_index_sql(self, vid): + def get_index_sql(self, did, vid): """ Get all index associated with view node, generate their sql and render @@ -910,10 +910,12 @@ class ViewNode(PGChildNodeView, VacuumSettings): self.index_temp_path = 'index' SQL_data = '' + SQL = render_template("/".join( + [self.index_temp_path, 'sql/#{0}#/properties.sql'.format(self.manager.version)]), + did=did, + tid=vid) status, data = self.conn.execute_dict(SQL) if not status: - SQL = render_template("/".join( - [self.index_temp_path, 'sql/#{0}#/properties.sql'.format(self.manager.version)]), tid=vid) return internal_server_error(errormsg=data) for index in data['rows']: @@ -921,6 +923,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): SQL = render_template("/".join( [self.index_temp_path, 'sql/#{0}#/properties.sql'.format(self.manager.version)]), idx=index['oid'], + did=did, tid=vid ) status, res = self.conn.execute_dict(SQL) @@ -1007,7 +1010,7 @@ class ViewNode(PGChildNodeView, VacuumSettings): SQL_data += SQL SQL_data += self.get_rule_sql(vid) SQL_data += self.get_trigger_sql(vid) - SQL_data += self.get_index_sql(vid) + SQL_data += self.get_index_sql(did, vid) return ajax_response(response=SQL_data) @@ -1259,13 +1262,14 @@ class MViewNode(ViewNode, VacuumSettings): '9.3_plus' ) - def getSQL(self, gid, sid, data, vid=None): + def getSQL(self, gid, sid, did, data, vid=None): """ This function will generate sql from model data """ if vid is not None: SQL = render_template("/".join( [self.template_path, 'sql/properties.sql']), + did=did, vid=vid, datlastsysoid=self.datlastsysoid ) @@ -1452,6 +1456,7 @@ class MViewNode(ViewNode, VacuumSettings): SQL_data = '' SQL = render_template("/".join( [self.template_path, 'sql/properties.sql']), + did=did, vid=vid, datlastsysoid=self.datlastsysoid ) @@ -1530,7 +1535,7 @@ class MViewNode(ViewNode, VacuumSettings): SQL_data += SQL SQL_data += self.get_rule_sql(vid) SQL_data += self.get_trigger_sql(vid) - SQL_data += self.get_index_sql(vid) + SQL_data += self.get_index_sql(did, vid) SQL_data = SQL_data.strip('\n') return ajax_response(response=SQL_data) @@ -1576,7 +1581,7 @@ class MViewNode(ViewNode, VacuumSettings): """ SQL = render_template("/".join( [self.template_path, 'sql/properties.sql'] - ), vid=vid, datlastsysoid=self.datlastsysoid) + ), did=did, vid=vid, datlastsysoid=self.datlastsysoid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js index e233b5c..e32594c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/js/mview.js @@ -126,7 +126,7 @@ function($, _, S, pgAdmin, alertify, pgBrowser, CodeMirror) { pgBrowser.Node.Model.prototype.initialize.apply(this, arguments); }, defaults: { - spcname: 'pg_default', + spcname: undefined, toast_autovacuum_enabled: false, autovacuum_enabled: false }, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql index 8a70522..eb36c91 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.3_plus/sql/properties.sql @@ -6,7 +6,11 @@ SELECT c.relname AS name, c.reltablespace AS spcoid, c.relispopulated AS with_data, - (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname, + CASE WHEN length(spcname) > 0 THEN 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, c.relacl, nsp.nspname as schema, pg_get_userbyid(c.relowner) AS owner, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql index 8a70522..eb36c91 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/pg/9.4_plus/sql/properties.sql @@ -6,7 +6,11 @@ SELECT c.relname AS name, c.reltablespace AS spcoid, c.relispopulated AS with_data, - (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname, + CASE WHEN length(spcname) > 0 THEN 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, c.relacl, nsp.nspname as schema, pg_get_userbyid(c.relowner) AS owner, diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql index 8a70522..eb36c91 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mview/ppas/9.3_plus/sql/properties.sql @@ -6,7 +6,11 @@ SELECT c.relname AS name, c.reltablespace AS spcoid, c.relispopulated AS with_data, - (CASE WHEN length(spc.spcname) > 0 THEN spc.spcname ELSE 'pg_default' END) as spcname, + CASE WHEN length(spcname) > 0 THEN 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, c.relacl, nsp.nspname as schema, pg_get_userbyid(c.relowner) AS owner, ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-02-03 09:40 Dave Page <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Dave Page @ 2017-02-03 09:40 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: Akshay Joshi <[email protected]>; pgadmin-hackers Akshay, I'll leave you to review/commit this. Please ensure that any tickets that patches close have the target version set to 1.2 (if they don't, please update them). That way the roadmap will reflect all the changes in the release. Thanks. On Fri, Feb 3, 2017 at 7:47 AM, Khushboo Vashi < [email protected]> wrote: > Hi Akshay, > > Please find the attached updated patch. > > Thanks, > Khushboo > > On Fri, Feb 3, 2017 at 1:08 PM, Akshay Joshi < > [email protected]> wrote: > >> Hi Khushboo >> >> After applying latest patch I found one error(refer attached screenshot) >> when clicking on "Materialized Views" collection node. >> >> Fixed > >> On Fri, Feb 3, 2017 at 12:14 PM, Khushboo Vashi < >> [email protected]> wrote: >> >>> Hi, >>> >>> On Fri, Feb 3, 2017 at 11:53 AM, Khushboo Vashi < >>> [email protected]> wrote: >>> >>>> Hi, >>>> >>>> Please find the attached rebased patch. >>>> >>>> This patch also include fix for RM 2139: Message (Connection to the >>> server has been lost.) displayed with Materialized view and view under sql >>> tab >>> >>> >>> >>>> Thanks, >>>> Khushboo >>>> >>>> On Mon, Jan 30, 2017 at 6:20 PM, Dave Page <[email protected]> wrote: >>>> >>>>> Hi >>>>> >>>>> Can you rebase this please? >>>>> >>>>> Thanks. >>>>> >>>>> On Fri, Jan 27, 2017 at 11:53 AM, Khushboo Vashi >>>>> <[email protected]> wrote: >>>>> > Hi, >>>>> > >>>>> > Please find the attached updated patch. >>>>> > >>>>> > Thanks, >>>>> > Khushboo >>>>> > >>>>> > On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> >>>>> wrote: >>>>> >> >>>>> >> On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi >>>>> >> <[email protected]> wrote: >>>>> >> > Hi, >>>>> >> > >>>>> >> > Please find the attached patch to fix the RM 2069 - Wrong >>>>> tablespace >>>>> >> > displayed in table properties. >>>>> >> > >>>>> >> > When the table is created without a tablespace, the "pg_default" >>>>> >> > tablespace >>>>> >> > gets selected rather than the default tablespace (the current >>>>> database >>>>> >> > tablespace). >>>>> >> > So, this issue has been fixed. >>>>> >> >>>>> >> Unfortunately more work is required. I started hacking on it (see >>>>> >> attached), but there is more effort needed and I'm out of time. >>>>> >> Specifically: >>>>> >> >>>>> >> - We need to remove 'pg_default' as the default tablespace when >>>>> >> creating objects. Let's have no default, and let PG automatically >>>>> pick >>>>> >> the right tablespace, unless the user specifically overrides it. My >>>>> >> updated patch removes that default, and fixes some of the SQL to >>>>> >> handle it. >>>>> >> >>>>> > Done >>>>> >> >>>>> >> - Matviews don't list the correct tablespace in their properties >>>>> list. >>>>> >> >>>>> > Done >>>>> >> >>>>> >> - Unique constraints ignore the tablespace option if the user >>>>> selects >>>>> >> pg_default, even if the database's default is something different. >>>>> >> >>>>> > Done >>>>> >> >>>>> >> So I think to complete this we need to review all objects that use >>>>> >> tablespaces (databases, tables, matviews, indexes, index-backed >>>>> >> constraints) and ensure: >>>>> >> >>>>> > Done >>>>> >> >>>>> >> - No default tablespace is selected when creating an object. >>>>> >> >>>>> > Done >>>>> >> >>>>> >> - If the user selects a tablespace, the CREATE SQL must handle it >>>>> >> properly. Similarly, if they leave it to the default. >>>>> >> >>>>> > Done >>>>> >> >>>>> >> - The Properties views must display the actual tablespace in use, >>>>> >> whether it is specific to the object, or the database default. >>>>> >> >>>>> > Done >>>>> >> >>>>> >> Thanks. >>>>> >> >>>>> >> -- >>>>> >> Dave Page >>>>> >> Blog: http://pgsnake.blogspot.com >>>>> >> Twitter: @pgsnake >>>>> >> >>>>> >> EnterpriseDB UK: http://www.enterprisedb.com >>>>> >> The Enterprise PostgreSQL Company >>>>> > >>>>> > >>>>> >>>>> >>>>> >>>>> -- >>>>> Dave Page >>>>> Blog: http://pgsnake.blogspot.com >>>>> Twitter: @pgsnake >>>>> >>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>> The Enterprise PostgreSQL Company >>>>> >>>> >>>> >>> >> >> >> -- >> *Akshay Joshi* >> *Principal Software Engineer * >> >> >> >> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246 >> <+91%2097678%2088246>* >> > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-02-03 09:59 Akshay Joshi <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Akshay Joshi @ 2017-02-03 09:59 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: Khushboo Vashi <[email protected]>; pgadmin-hackers Hi Dave On Fri, Feb 3, 2017 at 3:10 PM, Dave Page <[email protected]> wrote: > Akshay, I'll leave you to review/commit this. Please ensure that any > tickets that patches close have the target version set to 1.2 (if they > don't, please update them). That way the roadmap will reflect all the > changes in the release. > In pgAdmin3 for default tablespace we have one entry(*<default tablespace>*) in combo box while in pgAdmin4 we don't have such entry. Please refer the attached screenshot. Is this the expected behaviour for pgAdmin4? > > Thanks. > > On Fri, Feb 3, 2017 at 7:47 AM, Khushboo Vashi < > [email protected]> wrote: > >> Hi Akshay, >> >> Please find the attached updated patch. >> >> Thanks, >> Khushboo >> >> On Fri, Feb 3, 2017 at 1:08 PM, Akshay Joshi < >> [email protected]> wrote: >> >>> Hi Khushboo >>> >>> After applying latest patch I found one error(refer attached screenshot) >>> when clicking on "Materialized Views" collection node. >>> >>> Fixed >> >>> On Fri, Feb 3, 2017 at 12:14 PM, Khushboo Vashi < >>> [email protected]> wrote: >>> >>>> Hi, >>>> >>>> On Fri, Feb 3, 2017 at 11:53 AM, Khushboo Vashi < >>>> [email protected]> wrote: >>>> >>>>> Hi, >>>>> >>>>> Please find the attached rebased patch. >>>>> >>>>> This patch also include fix for RM 2139: Message (Connection to the >>>> server has been lost.) displayed with Materialized view and view under sql >>>> tab >>>> >>>> >>>> >>>>> Thanks, >>>>> Khushboo >>>>> >>>>> On Mon, Jan 30, 2017 at 6:20 PM, Dave Page <[email protected]> wrote: >>>>> >>>>>> Hi >>>>>> >>>>>> Can you rebase this please? >>>>>> >>>>>> Thanks. >>>>>> >>>>>> On Fri, Jan 27, 2017 at 11:53 AM, Khushboo Vashi >>>>>> <[email protected]> wrote: >>>>>> > Hi, >>>>>> > >>>>>> > Please find the attached updated patch. >>>>>> > >>>>>> > Thanks, >>>>>> > Khushboo >>>>>> > >>>>>> > On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> >>>>>> wrote: >>>>>> >> >>>>>> >> On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi >>>>>> >> <[email protected]> wrote: >>>>>> >> > Hi, >>>>>> >> > >>>>>> >> > Please find the attached patch to fix the RM 2069 - Wrong >>>>>> tablespace >>>>>> >> > displayed in table properties. >>>>>> >> > >>>>>> >> > When the table is created without a tablespace, the "pg_default" >>>>>> >> > tablespace >>>>>> >> > gets selected rather than the default tablespace (the current >>>>>> database >>>>>> >> > tablespace). >>>>>> >> > So, this issue has been fixed. >>>>>> >> >>>>>> >> Unfortunately more work is required. I started hacking on it (see >>>>>> >> attached), but there is more effort needed and I'm out of time. >>>>>> >> Specifically: >>>>>> >> >>>>>> >> - We need to remove 'pg_default' as the default tablespace when >>>>>> >> creating objects. Let's have no default, and let PG automatically >>>>>> pick >>>>>> >> the right tablespace, unless the user specifically overrides it. My >>>>>> >> updated patch removes that default, and fixes some of the SQL to >>>>>> >> handle it. >>>>>> >> >>>>>> > Done >>>>>> >> >>>>>> >> - Matviews don't list the correct tablespace in their properties >>>>>> list. >>>>>> >> >>>>>> > Done >>>>>> >> >>>>>> >> - Unique constraints ignore the tablespace option if the user >>>>>> selects >>>>>> >> pg_default, even if the database's default is something different. >>>>>> >> >>>>>> > Done >>>>>> >> >>>>>> >> So I think to complete this we need to review all objects that use >>>>>> >> tablespaces (databases, tables, matviews, indexes, index-backed >>>>>> >> constraints) and ensure: >>>>>> >> >>>>>> > Done >>>>>> >> >>>>>> >> - No default tablespace is selected when creating an object. >>>>>> >> >>>>>> > Done >>>>>> >> >>>>>> >> - If the user selects a tablespace, the CREATE SQL must handle it >>>>>> >> properly. Similarly, if they leave it to the default. >>>>>> >> >>>>>> > Done >>>>>> >> >>>>>> >> - The Properties views must display the actual tablespace in use, >>>>>> >> whether it is specific to the object, or the database default. >>>>>> >> >>>>>> > Done >>>>>> >> >>>>>> >> Thanks. >>>>>> >> >>>>>> >> -- >>>>>> >> Dave Page >>>>>> >> Blog: http://pgsnake.blogspot.com >>>>>> >> Twitter: @pgsnake >>>>>> >> >>>>>> >> EnterpriseDB UK: http://www.enterprisedb.com >>>>>> >> The Enterprise PostgreSQL Company >>>>>> > >>>>>> > >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Dave Page >>>>>> Blog: http://pgsnake.blogspot.com >>>>>> Twitter: @pgsnake >>>>>> >>>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>>> The Enterprise PostgreSQL Company >>>>>> >>>>> >>>>> >>>> >>> >>> >>> -- >>> *Akshay Joshi* >>> *Principal Software Engineer * >>> >>> >>> >>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 >>> 976-788-8246 <+91%2097678%2088246>* >>> >> >> > > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- *Akshay Joshi* *Principal Software Engineer * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [image/png] pgAdmin3.png (10.8K, 3-pgAdmin3.png) download | view image [image/png] pgAdmin4.png (12.7K, 4-pgAdmin4.png) download | view image ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-02-03 10:07 Dave Page <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Dave Page @ 2017-02-03 10:07 UTC (permalink / raw) To: Akshay Joshi <[email protected]>; +Cc: Khushboo Vashi <[email protected]>; pgadmin-hackers On Fri, Feb 3, 2017 at 9:59 AM, Akshay Joshi <[email protected]> wrote: > Hi Dave > > On Fri, Feb 3, 2017 at 3:10 PM, Dave Page <[email protected]> wrote: > >> Akshay, I'll leave you to review/commit this. Please ensure that any >> tickets that patches close have the target version set to 1.2 (if they >> don't, please update them). That way the roadmap will reflect all the >> changes in the release. >> > > In pgAdmin3 for default tablespace we have one entry(*<default > tablespace>*) in combo box while in pgAdmin4 we don't have such entry. > Please refer the attached screenshot. Is this the expected behaviour for > pgAdmin4? > I think that was just because you couldn't have an empty option in the control in pgAdmin 3. I think it's fine to just be optional in pgAdmin4 (or, just at the <default tablespace> option everywhere if you think it's better - I really don't have a preference!) > >> Thanks. >> >> On Fri, Feb 3, 2017 at 7:47 AM, Khushboo Vashi < >> [email protected]> wrote: >> >>> Hi Akshay, >>> >>> Please find the attached updated patch. >>> >>> Thanks, >>> Khushboo >>> >>> On Fri, Feb 3, 2017 at 1:08 PM, Akshay Joshi < >>> [email protected]> wrote: >>> >>>> Hi Khushboo >>>> >>>> After applying latest patch I found one error(refer attached >>>> screenshot) when clicking on "Materialized Views" collection node. >>>> >>>> Fixed >>> >>>> On Fri, Feb 3, 2017 at 12:14 PM, Khushboo Vashi < >>>> [email protected]> wrote: >>>> >>>>> Hi, >>>>> >>>>> On Fri, Feb 3, 2017 at 11:53 AM, Khushboo Vashi < >>>>> [email protected]> wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> Please find the attached rebased patch. >>>>>> >>>>>> This patch also include fix for RM 2139: Message (Connection to the >>>>> server has been lost.) displayed with Materialized view and view under sql >>>>> tab >>>>> >>>>> >>>>> >>>>>> Thanks, >>>>>> Khushboo >>>>>> >>>>>> On Mon, Jan 30, 2017 at 6:20 PM, Dave Page <[email protected]> wrote: >>>>>> >>>>>>> Hi >>>>>>> >>>>>>> Can you rebase this please? >>>>>>> >>>>>>> Thanks. >>>>>>> >>>>>>> On Fri, Jan 27, 2017 at 11:53 AM, Khushboo Vashi >>>>>>> <[email protected]> wrote: >>>>>>> > Hi, >>>>>>> > >>>>>>> > Please find the attached updated patch. >>>>>>> > >>>>>>> > Thanks, >>>>>>> > Khushboo >>>>>>> > >>>>>>> > On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> >>>>>>> wrote: >>>>>>> >> >>>>>>> >> On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi >>>>>>> >> <[email protected]> wrote: >>>>>>> >> > Hi, >>>>>>> >> > >>>>>>> >> > Please find the attached patch to fix the RM 2069 - Wrong >>>>>>> tablespace >>>>>>> >> > displayed in table properties. >>>>>>> >> > >>>>>>> >> > When the table is created without a tablespace, the "pg_default" >>>>>>> >> > tablespace >>>>>>> >> > gets selected rather than the default tablespace (the current >>>>>>> database >>>>>>> >> > tablespace). >>>>>>> >> > So, this issue has been fixed. >>>>>>> >> >>>>>>> >> Unfortunately more work is required. I started hacking on it (see >>>>>>> >> attached), but there is more effort needed and I'm out of time. >>>>>>> >> Specifically: >>>>>>> >> >>>>>>> >> - We need to remove 'pg_default' as the default tablespace when >>>>>>> >> creating objects. Let's have no default, and let PG automatically >>>>>>> pick >>>>>>> >> the right tablespace, unless the user specifically overrides it. >>>>>>> My >>>>>>> >> updated patch removes that default, and fixes some of the SQL to >>>>>>> >> handle it. >>>>>>> >> >>>>>>> > Done >>>>>>> >> >>>>>>> >> - Matviews don't list the correct tablespace in their properties >>>>>>> list. >>>>>>> >> >>>>>>> > Done >>>>>>> >> >>>>>>> >> - Unique constraints ignore the tablespace option if the user >>>>>>> selects >>>>>>> >> pg_default, even if the database's default is something different. >>>>>>> >> >>>>>>> > Done >>>>>>> >> >>>>>>> >> So I think to complete this we need to review all objects that use >>>>>>> >> tablespaces (databases, tables, matviews, indexes, index-backed >>>>>>> >> constraints) and ensure: >>>>>>> >> >>>>>>> > Done >>>>>>> >> >>>>>>> >> - No default tablespace is selected when creating an object. >>>>>>> >> >>>>>>> > Done >>>>>>> >> >>>>>>> >> - If the user selects a tablespace, the CREATE SQL must handle it >>>>>>> >> properly. Similarly, if they leave it to the default. >>>>>>> >> >>>>>>> > Done >>>>>>> >> >>>>>>> >> - The Properties views must display the actual tablespace in use, >>>>>>> >> whether it is specific to the object, or the database default. >>>>>>> >> >>>>>>> > Done >>>>>>> >> >>>>>>> >> Thanks. >>>>>>> >> >>>>>>> >> -- >>>>>>> >> Dave Page >>>>>>> >> Blog: http://pgsnake.blogspot.com >>>>>>> >> Twitter: @pgsnake >>>>>>> >> >>>>>>> >> EnterpriseDB UK: http://www.enterprisedb.com >>>>>>> >> The Enterprise PostgreSQL Company >>>>>>> > >>>>>>> > >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> Dave Page >>>>>>> Blog: http://pgsnake.blogspot.com >>>>>>> Twitter: @pgsnake >>>>>>> >>>>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>>>> The Enterprise PostgreSQL Company >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>>> -- >>>> *Akshay Joshi* >>>> *Principal Software Engineer * >>>> >>>> >>>> >>>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 >>>> 976-788-8246 <+91%2097678%2088246>* >>>> >>> >>> >> >> >> -- >> Dave Page >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EnterpriseDB UK: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> > > > > -- > *Akshay Joshi* > *Principal Software Engineer * > > > > *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246 > <+91%2097678%2088246>* > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties @ 2017-02-03 10:47 Akshay Joshi <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Akshay Joshi @ 2017-02-03 10:47 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: Khushboo Vashi <[email protected]>; pgadmin-hackers Thanks Khushboo. Patch applied. On Fri, Feb 3, 2017 at 3:37 PM, Dave Page <[email protected]> wrote: > > > On Fri, Feb 3, 2017 at 9:59 AM, Akshay Joshi < > [email protected]> wrote: > >> Hi Dave >> >> On Fri, Feb 3, 2017 at 3:10 PM, Dave Page <[email protected]> wrote: >> >>> Akshay, I'll leave you to review/commit this. Please ensure that any >>> tickets that patches close have the target version set to 1.2 (if they >>> don't, please update them). That way the roadmap will reflect all the >>> changes in the release. >>> >> >> In pgAdmin3 for default tablespace we have one entry(*<default >> tablespace>*) in combo box while in pgAdmin4 we don't have such entry. >> Please refer the attached screenshot. Is this the expected behaviour for >> pgAdmin4? >> > > I think that was just because you couldn't have an empty option in the > control in pgAdmin 3. I think it's fine to just be optional in pgAdmin4 > (or, just at the <default tablespace> option everywhere if you think it's > better - I really don't have a preference!) > > >> >>> Thanks. >>> >>> On Fri, Feb 3, 2017 at 7:47 AM, Khushboo Vashi < >>> [email protected]> wrote: >>> >>>> Hi Akshay, >>>> >>>> Please find the attached updated patch. >>>> >>>> Thanks, >>>> Khushboo >>>> >>>> On Fri, Feb 3, 2017 at 1:08 PM, Akshay Joshi < >>>> [email protected]> wrote: >>>> >>>>> Hi Khushboo >>>>> >>>>> After applying latest patch I found one error(refer attached >>>>> screenshot) when clicking on "Materialized Views" collection node. >>>>> >>>>> Fixed >>>> >>>>> On Fri, Feb 3, 2017 at 12:14 PM, Khushboo Vashi < >>>>> [email protected]> wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> On Fri, Feb 3, 2017 at 11:53 AM, Khushboo Vashi < >>>>>> [email protected]> wrote: >>>>>> >>>>>>> Hi, >>>>>>> >>>>>>> Please find the attached rebased patch. >>>>>>> >>>>>>> This patch also include fix for RM 2139: Message (Connection to the >>>>>> server has been lost.) displayed with Materialized view and view under sql >>>>>> tab >>>>>> >>>>>> >>>>>> >>>>>>> Thanks, >>>>>>> Khushboo >>>>>>> >>>>>>> On Mon, Jan 30, 2017 at 6:20 PM, Dave Page <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>>> Hi >>>>>>>> >>>>>>>> Can you rebase this please? >>>>>>>> >>>>>>>> Thanks. >>>>>>>> >>>>>>>> On Fri, Jan 27, 2017 at 11:53 AM, Khushboo Vashi >>>>>>>> <[email protected]> wrote: >>>>>>>> > Hi, >>>>>>>> > >>>>>>>> > Please find the attached updated patch. >>>>>>>> > >>>>>>>> > Thanks, >>>>>>>> > Khushboo >>>>>>>> > >>>>>>>> > On Fri, Jan 20, 2017 at 5:31 PM, Dave Page <[email protected]> >>>>>>>> wrote: >>>>>>>> >> >>>>>>>> >> On Fri, Jan 13, 2017 at 6:41 AM, Khushboo Vashi >>>>>>>> >> <[email protected]> wrote: >>>>>>>> >> > Hi, >>>>>>>> >> > >>>>>>>> >> > Please find the attached patch to fix the RM 2069 - Wrong >>>>>>>> tablespace >>>>>>>> >> > displayed in table properties. >>>>>>>> >> > >>>>>>>> >> > When the table is created without a tablespace, the >>>>>>>> "pg_default" >>>>>>>> >> > tablespace >>>>>>>> >> > gets selected rather than the default tablespace (the current >>>>>>>> database >>>>>>>> >> > tablespace). >>>>>>>> >> > So, this issue has been fixed. >>>>>>>> >> >>>>>>>> >> Unfortunately more work is required. I started hacking on it (see >>>>>>>> >> attached), but there is more effort needed and I'm out of time. >>>>>>>> >> Specifically: >>>>>>>> >> >>>>>>>> >> - We need to remove 'pg_default' as the default tablespace when >>>>>>>> >> creating objects. Let's have no default, and let PG >>>>>>>> automatically pick >>>>>>>> >> the right tablespace, unless the user specifically overrides it. >>>>>>>> My >>>>>>>> >> updated patch removes that default, and fixes some of the SQL to >>>>>>>> >> handle it. >>>>>>>> >> >>>>>>>> > Done >>>>>>>> >> >>>>>>>> >> - Matviews don't list the correct tablespace in their properties >>>>>>>> list. >>>>>>>> >> >>>>>>>> > Done >>>>>>>> >> >>>>>>>> >> - Unique constraints ignore the tablespace option if the user >>>>>>>> selects >>>>>>>> >> pg_default, even if the database's default is something >>>>>>>> different. >>>>>>>> >> >>>>>>>> > Done >>>>>>>> >> >>>>>>>> >> So I think to complete this we need to review all objects that >>>>>>>> use >>>>>>>> >> tablespaces (databases, tables, matviews, indexes, index-backed >>>>>>>> >> constraints) and ensure: >>>>>>>> >> >>>>>>>> > Done >>>>>>>> >> >>>>>>>> >> - No default tablespace is selected when creating an object. >>>>>>>> >> >>>>>>>> > Done >>>>>>>> >> >>>>>>>> >> - If the user selects a tablespace, the CREATE SQL must handle it >>>>>>>> >> properly. Similarly, if they leave it to the default. >>>>>>>> >> >>>>>>>> > Done >>>>>>>> >> >>>>>>>> >> - The Properties views must display the actual tablespace in use, >>>>>>>> >> whether it is specific to the object, or the database default. >>>>>>>> >> >>>>>>>> > Done >>>>>>>> >> >>>>>>>> >> Thanks. >>>>>>>> >> >>>>>>>> >> -- >>>>>>>> >> Dave Page >>>>>>>> >> Blog: http://pgsnake.blogspot.com >>>>>>>> >> Twitter: @pgsnake >>>>>>>> >> >>>>>>>> >> EnterpriseDB UK: http://www.enterprisedb.com >>>>>>>> >> The Enterprise PostgreSQL Company >>>>>>>> > >>>>>>>> > >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> Dave Page >>>>>>>> Blog: http://pgsnake.blogspot.com >>>>>>>> Twitter: @pgsnake >>>>>>>> >>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>>>>> The Enterprise PostgreSQL Company >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> *Akshay Joshi* >>>>> *Principal Software Engineer * >>>>> >>>>> >>>>> >>>>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 >>>>> 976-788-8246 <+91%2097678%2088246>* >>>>> >>>> >>>> >>> >>> >>> -- >>> Dave Page >>> Blog: http://pgsnake.blogspot.com >>> Twitter: @pgsnake >>> >>> EnterpriseDB UK: http://www.enterprisedb.com >>> The Enterprise PostgreSQL Company >>> >> >> >> >> -- >> *Akshay Joshi* >> *Principal Software Engineer * >> >> >> >> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246 >> <+91%2097678%2088246>* >> > > > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- *Akshay Joshi* *Principal Software Engineer * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* ^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2017-02-03 10:47 UTC | newest] Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-01-13 06:41 [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties Khushboo Vashi <[email protected]> 2017-01-20 12:01 ` Dave Page <[email protected]> 2017-01-27 11:53 ` Khushboo Vashi <[email protected]> 2017-01-30 12:50 ` Dave Page <[email protected]> 2017-02-03 06:23 ` Khushboo Vashi <[email protected]> 2017-02-03 06:44 ` Khushboo Vashi <[email protected]> 2017-02-03 07:38 ` Akshay Joshi <[email protected]> 2017-02-03 07:47 ` Khushboo Vashi <[email protected]> 2017-02-03 09:40 ` Dave Page <[email protected]> 2017-02-03 09:59 ` Akshay Joshi <[email protected]> 2017-02-03 10:07 ` Dave Page <[email protected]> 2017-02-03 10:47 ` 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