Subject: Error about inherited table of column in pgadmin1.20.0

From: JasmineLiu <[email-masked]>
Date: Wed, 14 Oct 2015 22:45:19 -0700 (MST)

Hi, all !

This is Liu Yuanyuan.
I find the inherited table of column shown in pgadmin 1.20.0, sometimes
isn’t right. That should be a bug.
Hope someone to do something about this problem!
Here is my example:

First I execute the following sql in pgadmin 1.20.0 by query tool:
create schema test;
create table test.a1(id int);
create table test.a2(id int, name varchar);
create table test.a3(age int);
create table test.aa() inherits(test.a1,test.a2,test.a3);

Then in main view of pgadmin 1.20.0：
It shows the column age is inherited from table test.a2, this is not right.
<http://postgresql.nabble.com/file/n5870044/1.jpg> 
<http://postgresql.nabble.com/file/n5870044/2.jpg>  

I also check the code of pgadmin 1.20.0, I think the method to specify the
inherited table of columns is not correct,especially when the inherited
tables have same column.

Thanks and hope to hear from you soon!
 
Best wishes!
Liu Yuanyuan

--
View this message in context: http://postgresql.nabble.com/Error-about-inherited-table-of-column-in-pgadmin1-20-0-tp5870044.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

-- 
Sent via pgadmin-support mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

---

From: Ashesh Vashi <[email-masked]>
Date: Thu, 15 Oct 2015 13:51:58 +0530

> <http://postgresql.nabble.com/file/n5870044/2.jpg>
Looks like a bug to me.

Akshay,

Can you please take a look at it?

--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company
<http://www.enterprisedb.com>

*http://www.linkedin.com/in/asheshvashi*
<http://www.linkedin.com/in/asheshvashi>

> View this message in context:
> http://postgresql.nabble.com/Error-about-inherited-table-of-column-in-pgadmin1-20-0-tp5870044.html

---

From: Akshay Joshi <[email-masked]>
Date: Fri, 16 Oct 2015 17:40:38 +0530

Hi Ashesh

On Thu, Oct 15, 2015 at 1:51 PM, Ashesh Vashi <[email-masked]

> Can you please take a look at it?

   I have fixed the issue, attached is the patch file. Can you please
review it and if it looks good can you please commit the code.

-- 
*Akshay Joshi*
*Principal Software Engineer *

*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*

---

From: Ashesh Vashi <[email-masked]>
Date: Fri, 23 Oct 2015 13:27:07 +0530

On Fri, Oct 16, 2015 at 5:40 PM, Akshay Joshi <[email-masked]

> Hi Ashesh
Hi Akshay,

I've modified the patch a little bit for betterment.
Can you please review and test it?

--
Thanks & Regards,
Ashesh Vashi

>
>
> On Thu, Oct 15, 2015 at 1:51 PM, Ashesh Vashi <ashesh.vashi@enterprisedb
> .com> wrote:
>
diff --git a/pgadmin/include/schema/pgColumn.h b/pgadmin/include/schema/pgColumn.h
index fca8456..9630794 100644
--- a/pgadmin/include/schema/pgColumn.h
+++ b/pgadmin/include/schema/pgColumn.h
@@ -15,6 +15,8 @@
 // App headers
 #include "pgTable.h"
 
+WX_DECLARE_STRING_HASH_MAP(wxString, inheritHashMap);
+
 class pgCollection;
 
 class pgColumnFactory : public pgTableObjFactory
diff --git a/pgadmin/schema/pgColumn.cpp b/pgadmin/schema/pgColumn.cpp
index b3796e2..0e463cd 100644
--- a/pgadmin/schema/pgColumn.cpp
+++ b/pgadmin/schema/pgColumn.cpp
@@ -545,23 +545,43 @@ pgObject *pgColumnFactory::CreateObjects(pgCollection *coll, ctlTree *browser, c
 	pgTableObjCollection *collection = (pgTableObjCollection *)coll;
 	pgColumn *column = 0;
 	pgDatabase *database = collection->GetDatabase();
-	wxString sql;
-	int currentcol;
-	int currentlimit;
+	inheritHashMap inhMap;
 
-	// grab inherited tables
-	sql = wxT("SELECT inhparent::regclass AS inhrelname,\n")
-	      wxT("  (SELECT count(*) FROM pg_attribute WHERE attrelid=inhparent AND attnum>0) AS colscount\n")
-	      wxT("  FROM pg_inherits\n")
-	      wxT("  WHERE inhrelid =  ") + collection->GetOidStr() + wxT("::oid\n")
-	      wxT("  ORDER BY inhseqno");
-	pgSet *inhtables = database->ExecuteSet(sql);
+	// grab inherited tables with attibute names
+	pgSet *inhtables = database->ExecuteSet(
+			wxT("SELECT\n")
+			wxT("    array_to_string(array_agg(inhrelname), ', ') inhrelname,\n")
+			wxT("    attrname\n")
+			wxT("FROM\n")
+			wxT("    (SELECT\n")
+			wxT("        inhparent::regclass AS inhrelname,\n")
+			wxT("        a.attname AS attrname\n")
+			wxT("    FROM\n")
+			wxT("        pg_inherits i\n")
+			wxT("        LEFT JOIN pg_attribute a ON\n")
+			wxT("            (attrelid = inhparent AND attnum > 0)\n")
+			wxT("    WHERE inhrelid = ") + collection->GetOidStr() + wxT("::oid\n")
+			wxT("    ORDER BY inhseqno) a\n")
+			wxT("GROUP BY attrname"));
+
+	if (inhtables)
+	{
+		while (!inhtables->Eof())
+		{
+			wxString attrName = inhtables->GetVal(wxT("attrname"));
+			wxString inhrelName = inhtables->GetVal(wxT("inhrelname"));
+			inhMap[attrName] = inhrelName;
+			inhtables->MoveNext();
+		}
+
+		delete inhtables;
+	}
 
 	wxString systemRestriction;
 	if (!settings->GetShowSystemObjects())
 		systemRestriction = wxT("\n   AND att.attnum > 0");
 
-	sql =
+	wxString sql =
 	    wxT("SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval, CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray, format_type(ty.oid,NULL) AS typname, format_type(ty.oid,att.atttypmod) AS displaytypname, tn.nspname as typnspname, et.typname as elemtypname,\n")
 	    wxT("  ty.typstorage AS defaultstorage, cl.relname, na.nspname, att.attstattarget, description, cs.relname AS sername, ns.nspname AS serschema,\n")
 	    wxT("  (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup, indkey");
@@ -604,17 +624,10 @@ pgObject *pgColumnFactory::CreateObjects(pgCollection *coll, ctlTree *browser, c
 	pgSet *columns = database->ExecuteSet(sql);
 	if (columns)
 	{
-		currentcol = 0;
-		if (inhtables && !inhtables->Eof())
-		{
-			currentlimit = inhtables->GetLong(wxT("colscount"));
-		}
 		while (!columns->Eof())
 		{
-			if (columns->GetLong(wxT("attnum")) > 0) // ignore system columns before inherited columns
-				currentcol++;
-
-			column = new pgColumn(collection->GetTable(), columns->GetVal(wxT("attname")));
+			wxString attrName = columns->GetVal(wxT("attname"));
+			column = new pgColumn(collection->GetTable(), attrName);
 
 			column->iSetAttTypId(columns->GetOid(wxT("atttypid")));
 			column->iSetColNumber(columns->GetLong(wxT("attnum")));
@@ -665,18 +678,10 @@ pgObject *pgColumnFactory::CreateObjects(pgCollection *coll, ctlTree *browser, c
 			column->iSetTableName(columns->GetVal(wxT("relname")));
 			column->iSetInheritedCount(columns->GetLong(wxT("attinhcount")));
 
-			if (inhtables)
-			{
-				if (currentcol > currentlimit)
-				{
-					inhtables->MoveNext();
-					if (!inhtables->Eof())
-						currentlimit += inhtables->GetLong(wxT("colscount"));
-				}
-
-				if (!inhtables->Eof())
-					column->iSetInheritedTableName(inhtables->GetVal(wxT("inhrelname")));
-			}
+			// Check whether the attribute is inherited
+			inheritHashMap::iterator it = inhMap.find(attrName);
+			if (it != inhMap.end())
+				column->iSetInheritedTableName(it->second);
 
 			column->iSetIsLocal(columns->GetBool(wxT("attislocal")));
 			column->iSetAttstattarget(columns->GetLong(wxT("attstattarget")));
@@ -711,9 +716,10 @@ pgObject *pgColumnFactory::CreateObjects(pgCollection *coll, ctlTree *browser, c
 				break;
 		}
 
-		delete inhtables;
 		delete columns;
 	}
+
+	inhMap.clear();
 	return column;
 }

-- 
Sent via pgadmin-hackers mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

---

From: Akshay Joshi <[email-masked]>
Date: Fri, 23 Oct 2015 14:59:59 +0530

Hi Ashesh

On Fri, Oct 23, 2015 at 1:27 PM, Ashesh Vashi <[email-masked]

> On Fri, Oct 16, 2015 at 5:40 PM, Akshay Joshi <
> [email-masked]> wrote:
>
>> Hi Ashesh
>>
> Hi Akshay,
>

   Patch looks good to me. I have tested it, works fine.

---

From: Ashesh Vashi <[email-masked]>
Date: Fri, 23 Oct 2015 15:46:28 +0530

On Fri, Oct 23, 2015 at 2:59 PM, Akshay Joshi <[email-masked]

> Hi Ashesh
>
> On Fri, Oct 23, 2015 at 1:27 PM, Ashesh Vashi <
> [email-masked]> wrote:
>
Akshay,

Thanks - checked in!

Liu Yuanyuan,
Thanks for the report.

--

Thanks & Regards,

Ashesh Vashi

>>>> On Thu, Oct 15, 2015 at 11:15 AM, JasmineLiu <[email-masked]
>>>> > wrote:
>>>>
>>>>> Hi, all !
>>>>>
>>>>> This is Liu Yuanyuan.