Skip to content

Commit

Permalink
postgres: add support for partitioned tables (implements #17302)
Browse files Browse the repository at this point in the history
  • Loading branch information
@jef-n
jef-n committed Oct 21, 2017
1 parent e6f5236 commit f243b84
Show file tree
Hide file tree
Showing 5 changed files with 19 additions and 14 deletions.
12 changes: 6 additions & 6 deletions python/plugins/db_manager/db_plugins/postgis/connector.py
View file
Expand Up @@ -157,7 +157,7 @@ def _checkRaster(self):

def _checkGeometryColumnsTable(self):
c = self._execute(None,
u"SELECT relkind = 'v' OR relkind = 'm' FROM pg_class WHERE relname = 'geometry_columns' AND relkind IN ('v', 'r', 'm')")
u"SELECT relkind = 'v' OR relkind = 'm' FROM pg_class WHERE relname = 'geometry_columns' AND relkind IN ('v', 'r', 'm', 'p')")
res = self._fetchone(c)
self._close_cursor(c)
self.has_geometry_columns = (res is not None and len(res) != 0)
Expand All @@ -173,7 +173,7 @@ def _checkGeometryColumnsTable(self):

def _checkRasterColumnsTable(self):
c = self._execute(None,
u"SELECT relkind = 'v' OR relkind = 'm' FROM pg_class WHERE relname = 'raster_columns' AND relkind IN ('v', 'r', 'm')")
u"SELECT relkind = 'v' OR relkind = 'm' FROM pg_class WHERE relname = 'raster_columns' AND relkind IN ('v', 'r', 'm', 'p')")
res = self._fetchone(c)
self._close_cursor(c)
self.has_raster_columns = (res is not None and len(res) != 0)
Expand Down Expand Up @@ -322,7 +322,7 @@ def getTables(self, schema=None, add_sys_tables=False):
pg_catalog.obj_description(cla.oid)
FROM pg_class AS cla
JOIN pg_namespace AS nsp ON nsp.oid = cla.relnamespace
WHERE cla.relkind IN ('v', 'r', 'm') """ + schema_where + """
WHERE cla.relkind IN ('v', 'r', 'm', 'p') """ + schema_where + """
ORDER BY nsp.nspname, cla.relname"""

c = self._execute(None, sql)
Expand Down Expand Up @@ -389,7 +389,7 @@ def getVectorTables(self, schema=None):
""" + geometry_column_from + """
WHERE cla.relkind IN ('v', 'r', 'm') """ + schema_where + """
WHERE cla.relkind IN ('v', 'r', 'm', 'p') """ + schema_where + """
ORDER BY nsp.nspname, cla.relname, att.attname"""

items = []
Expand Down Expand Up @@ -461,7 +461,7 @@ def getRasterTables(self, schema=None):
""" + raster_column_from + """
WHERE cla.relkind IN ('v', 'r', 'm') """ + schema_where + """
WHERE cla.relkind IN ('v', 'r', 'm', 'p') """ + schema_where + """
ORDER BY nsp.nspname, cla.relname, att.attname"""

items = []
Expand Down Expand Up @@ -1019,7 +1019,7 @@ def getSqlDictionary(self):
# get schemas, tables and field names
items = []
sql = u"""SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_' AND nspname != 'information_schema'
UNION SELECT relname FROM pg_class WHERE relkind IN ('v', 'r', 'm')
UNION SELECT relname FROM pg_class WHERE relkind IN ('v', 'r', 'm', 'p')
UNION SELECT attname FROM pg_attribute WHERE attnum > 0"""
c = self._execute(None, sql)
for row in self._fetchall(c):
Expand Down
8 changes: 4 additions & 4 deletions python/plugins/processing/tools/postgis.py
View file
Expand Up @@ -317,7 +317,7 @@ def list_geotables(self, schema=None):
reltuples, relpages, NULL, NULL, NULL, NULL
FROM pg_class
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_class.relkind IN ('v', 'r')""" \
WHERE pg_class.relkind IN ('v', 'r', 'm', 'p')""" \
+ schema_where + 'ORDER BY nspname, relname'
else:
# Discovery of all tables and whether they contain a
Expand All @@ -334,7 +334,7 @@ def list_geotables(self, schema=None):
OR pg_attribute.atttypid IN
(SELECT oid FROM pg_type
WHERE typbasetype='geometry'::regtype))
WHERE pg_class.relkind IN ('v', 'r') """ \
WHERE pg_class.relkind IN ('v', 'r', 'm', 'p') """ \
+ schema_where + 'ORDER BY nspname, relname, attname'

self._exec_sql(c, sql)
Expand All @@ -352,7 +352,7 @@ def list_geotables(self, schema=None):
JOIN pg_namespace ON relnamespace=pg_namespace.oid
LEFT OUTER JOIN geometry_columns ON
relname=f_table_name AND nspname=f_table_schema
WHERE (relkind = 'r' or relkind='v') """ \
WHERE relkind IN ('r','v','m','p') """ \
+ schema_where + 'ORDER BY nspname, relname, \
f_geometry_column'
self._exec_sql(c, sql)
Expand Down Expand Up @@ -462,7 +462,7 @@ def get_view_definition(self, view, schema=None):
sql = """SELECT pg_get_viewdef(c.oid)
FROM pg_class c
JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
WHERE relname='%s' %s AND relkind='v'""" \
WHERE relname='%s' %s AND relkind IN ('v','m')""" \
% (self._quote_unicode(view), schema_where)
c = self.con.cursor()
self._exec_sql(c, sql)
Expand Down
4 changes: 2 additions & 2 deletions src/providers/postgres/qgspostgresconn.cpp
View file
Expand Up @@ -573,7 +573,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
" JOIN pg_namespace n ON n.oid=c.relnamespace"
" JOIN pg_type t ON t.oid=a.atttypid"
" LEFT JOIN pg_type b ON b.oid=t.typbasetype"
" WHERE c.relkind IN ('v','r','m')"
" WHERE c.relkind IN ('v','r','m','p')"
" AND has_schema_privilege( n.nspname, 'usage' )"
" AND has_table_privilege( '\"' || n.nspname || '\".\"' || c.relname || '\"', 'select' )"
" AND (t.typname IN ('geometry','geography','topogeometry') OR b.typname IN ('geometry','geography','topogeometry','pcpatch'))";
Expand Down Expand Up @@ -689,7 +689,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
" WHERE pg_namespace.oid=pg_class.relnamespace"
" AND has_schema_privilege(pg_namespace.nspname,'usage')"
" AND has_table_privilege('\"' || pg_namespace.nspname || '\".\"' || pg_class.relname || '\"','select')"
" AND pg_class.relkind IN ('v','r','m')";
" AND pg_class.relkind IN ('v','r','m','p')";

// user has select privilege
if ( searchPublicOnly )
Expand Down
6 changes: 5 additions & 1 deletion src/providers/postgres/qgspostgresprovider.cpp
View file
Expand Up @@ -1339,7 +1339,7 @@ bool QgsPostgresProvider::determinePrimaryKey()
}
}
}
else if ( type == Relkind::View || type == Relkind::MaterializedView )
else if ( type == Relkind::View || type == Relkind::MaterializedView || type == Relkind::PartitionedTable )
{
determinePrimaryKeyFromUriKeyColumn();
}
Expand Down Expand Up @@ -4331,6 +4331,10 @@ QgsPostgresProvider::Relkind QgsPostgresProvider::relkind() const
{
kind = Relkind::ForeignTable;
}
else if ( type == QLatin1String( "p" ) )
{
kind = Relkind::PartitionedTable;
}

return kind;
}
Expand Down
3 changes: 2 additions & 1 deletion src/providers/postgres/qgspostgresprovider.h
View file
Expand Up @@ -59,7 +59,8 @@ class QgsPostgresProvider : public QgsVectorDataProvider
MaterializedView, // m
CompositeType, // c
ToastTable, // t
ForeignTable // f
ForeignTable, // f
PartitionedTable // p - PostgreSQL 10
};
Q_ENUM( Relkind );

Expand Down

0 comments on commit f243b84

Please sign in to comment.