[Gambas-user] BUG: gb.db.postgres doesn't handle partitioned tables

[BB]

Benoît,

One of our databases has grown so large that we have partitioned several 
tables to get some efficiency going. Having done that I find that the 
postgres component doesn't find the partitioned table and only "sees" 
the actual partitions.

However 😁, there are only 4 changes that need to be made in the 
*main.c* class in the gb.db.postgres component to solve this problem. 
They are changes to the queries used to handle the metadata. These 
changes are:

*table_exist() function
*

change the 2 queries to:

     const char *query =
         "select relname from pg_class where (relkind in ('r', 'v', 'm', 'p')) "
         "and (relname = '&1') "
         "and (relnamespace not in (select oid from pg_namespace where nspname = 'information_schema'))";

     const char *query_schema =
         "select relname from pg_class where (relkind in ('r', 'v', 'm', 'p')) "
         "and (relname = '&1') "
         "and (relnamespace in (select oid from pg_namespace where nspname = '&2'))";

*table_is_system() function*

change the query to

	const char *query =
		"select 1 from pg_class where (relkind in ('r', 'v', 'm', 'p')) "
		"and (relname = '&1') "
		"and (relnamespace in (select oid from pg_namespace where nspname = 'pg_catalog'))";**

*table_list_73() function
*

change the query to

	const char *query =
		"select pg_class.relname,pg_namespace.nspname from pg_class,pg_namespace where (relkind in ('r', 'v', 'm', 'p')) "
		"and (pg_namespace.oid = pg_class.relnamespace) "
		"and (pg_namespace.oid not in (select oid from pg_namespace where nspname = 'information_schema'))";

I have checked the queries and they all work in various postgres 
versions. I have also made these changes locally and it now works fine. 
Hope you can find the time to put these in the system.

By way of explanation, the partitioned tables have a relkind of 'p' 
which wasn't being included in the queries, so it was a simple matter of 
including them.

regards

bruce

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20221103/c5bba7a0/attachment.htm>