public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Page <[email protected]>
To: Khushboo Vashi <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties
Date: Fri, 20 Jan 2017 12:01:26 +0000
Message-ID: <CA+OCxowgSxEL__C5ygC+nyP5tPKkyDaROYrLTP1D9JNr=ib5=g@mail.gmail.com> (raw)
In-Reply-To: <CAFOhELfYpisg_bE+ndopMw7kirReqhLJQk1wKPpWwUmq3H3dzg@mail.gmail.com>
References: <CAFOhELfYpisg_bE+ndopMw7kirReqhLJQk1wKPpWwUmq3H3dzg@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-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
},
view thread (12+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [pgAdmin4][Patch]: RM 2069 - Wrong tablespace displayed in table properties
In-Reply-To: <CA+OCxowgSxEL__C5ygC+nyP5tPKkyDaROYrLTP1D9JNr=ib5=g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox