From 63644f4be710839679910beb6e4667a5c6de8aa3 Mon Sep 17 00:00:00 2001 From: Hiroshi Inoue Date: Thu, 17 May 2018 12:05:25 +0900 Subject: [PATCH] Reduce DB access to pg_class or pg_index by caching relhasoids, relhassubclass etc. It would improve the performance of SQLSetPos() or SQLBulkOperations() very much in some cases. Per report from Takayuki Tsunakawa. --- catfunc.h | 1 + connection.h | 5 ++ info.c | 26 +++++-- parse.c | 103 +++++++++++++++++++++++++++ results.c | 1 - test/expected/catalogfunctions.out | 1 + test/expected/catalogfunctions_1.out | 1 + test/src/bulkoperations-test.c | 2 +- test/src/cursor-block-delete-test.c | 2 +- 9 files changed, 133 insertions(+), 9 deletions(-) diff --git a/catfunc.h b/catfunc.h index 12e91c9..6815ce2 100644 --- a/catfunc.h +++ b/catfunc.h @@ -48,6 +48,7 @@ enum { ,COLUMNS_TABLE_OID ,COLUMNS_BASE_TYPEID ,COLUMNS_ATTTYPMOD + ,COLUMNS_TABLE_INFO ,NUM_OF_COLUMNS_FIELDS }; /* SQLPrimaryKeys field position */ diff --git a/connection.h b/connection.h index 317af45..817563f 100644 --- a/connection.h +++ b/connection.h @@ -238,8 +238,13 @@ struct col_info pgNAME schema_name; pgNAME table_name; OID table_oid; + int table_info; time_t acc_time; }; +enum { + TBINFO_HASOIDS = 1L + ,TBINFO_HASSUBCLASS = (1L << 1) +}; #define free_col_info_contents(coli) \ { \ if (NULL != coli->result) \ diff --git a/info.c b/info.c index 30462ff..e5ff2b5 100644 --- a/info.c +++ b/info.c @@ -2196,7 +2196,7 @@ cleanup: * for oid or xmin */ static void -add_tuple_for_oid_or_xmin(TupleField *tuple, int ordinal, const char *colname, OID the_type, const char *typname, const ConnectionClass *conn, const char *table_owner, const char *table_name, OID greloid, int attnum, BOOL auto_increment) +add_tuple_for_oid_or_xmin(TupleField *tuple, int ordinal, const char *colname, OID the_type, const char *typname, const ConnectionClass *conn, const char *table_owner, const char *table_name, OID greloid, int attnum, BOOL auto_increment, int table_info) { int sqltype; const int atttypmod = -1; @@ -2229,6 +2229,7 @@ add_tuple_for_oid_or_xmin(TupleField *tuple, int ordinal, const char *colname, O set_tuplefield_int4(&tuple[COLUMNS_TABLE_OID], greloid); set_tuplefield_int4(&tuple[COLUMNS_BASE_TYPEID], 0); set_tuplefield_int4(&tuple[COLUMNS_ATTTYPMOD], -1); + set_tuplefield_int4(&tuple[COLUMNS_TABLE_INFO], table_info); } RETCODE SQL_API @@ -2260,7 +2261,7 @@ PGAPI_Columns(HSTMT hstmt, result_cols; Int4 mod_length, ordinal, - typmod, relhasoids; + typmod, relhasoids, relhassubclass; OID field_type, greloid, basetype; char not_null[MAX_INFO_STRING], relhasrules[MAX_INFO_STRING], relkind[8], attidentity[2]; @@ -2272,6 +2273,7 @@ PGAPI_Columns(HSTMT hstmt, const char *like_or_eq = likeop, *op_string; const SQLCHAR *szSchemaName; BOOL setIdentity = FALSE; + int table_info = 0; MYLOG(0, "entering...stmt=%p scnm=%p len=%d\n", stmt, szTableOwner, cbTableOwner); @@ -2339,7 +2341,7 @@ retry_public_schema: "t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, " "c.relhasrules, c.relkind, c.oid, pg_get_expr(d.adbin, d.adrelid), " "case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod, " - "c.relhasoids, %s " + "c.relhasoids, %s, c.relhassubclass " "from (((pg_catalog.pg_class c " "inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace", PG_VERSION_GE(conn, 10.0) ? "attidentity" : "''"); if (search_by_ids) @@ -2517,6 +2519,12 @@ retry_public_schema: goto cleanup; } + result = PGAPI_BindCol(col_stmt, 18, SQL_C_LONG, + &relhassubclass, sizeof(relhassubclass), NULL); + if (!SQL_SUCCEEDED(result)) + { + goto cleanup; + } if (res = QR_Constructor(), !res) { SC_set_error(stmt, STMT_NO_MEMORY_ERROR, "Couldn't allocate memory for PGAPI_Columns result.", func); @@ -2567,6 +2575,7 @@ retry_public_schema: QR_set_field_info_v(res, COLUMNS_TABLE_OID, "TABLE OID", PG_TYPE_OID, 4); QR_set_field_info_v(res, COLUMNS_BASE_TYPEID, "BASE TYPEID", PG_TYPE_OID, 4); QR_set_field_info_v(res, COLUMNS_ATTTYPMOD, "TYPMOD", PG_TYPE_INT4, 4); + QR_set_field_info_v(res, COLUMNS_TABLE_INFO, "TABLE INFO", PG_TYPE_INT4, 4); ordinal = 1; result = PGAPI_Fetch(col_stmt); @@ -2578,8 +2587,12 @@ retry_public_schema: */ relisaview = (relkind[0] == 'v'); - if (result != SQL_ERROR) + if (SQL_SUCCEEDED(result)) { + if (relhasoids) + table_info |= TBINFO_HASOIDS; + if (relhassubclass) + table_info |= TBINFO_HASSUBCLASS; if (!relisaview && relhasoids && (show_oid_column || @@ -2599,7 +2612,7 @@ retry_public_schema: } else typname = OID_NAME; - add_tuple_for_oid_or_xmin(tuple, ordinal, OID_NAME, PG_TYPE_OID, typname, conn, table_owner, table_name, greloid, OID_ATTNUM, TRUE); + add_tuple_for_oid_or_xmin(tuple, ordinal, OID_NAME, PG_TYPE_OID, typname, conn, table_owner, table_name, greloid, OID_ATTNUM, TRUE, table_info); ordinal++; } } @@ -2725,6 +2738,7 @@ MYLOG(0, " and the data=%s\n", attdef); set_tuplefield_int4(&tuple[COLUMNS_TABLE_OID], greloid); set_tuplefield_int4(&tuple[COLUMNS_BASE_TYPEID], basetype); set_tuplefield_int4(&tuple[COLUMNS_ATTTYPMOD], mod_length); + set_tuplefield_int4(&tuple[COLUMNS_TABLE_INFO], table_info); ordinal++; result = PGAPI_Fetch(col_stmt); @@ -2748,7 +2762,7 @@ MYLOG(0, " and the data=%s\n", attdef); /* For Row Versioning fields */ tuple = QR_AddNew(res); - add_tuple_for_oid_or_xmin(tuple, ordinal, XMIN_NAME, PG_TYPE_XID, "xid", conn, table_owner, table_name, greloid, XMIN_ATTNUM, FALSE); + add_tuple_for_oid_or_xmin(tuple, ordinal, XMIN_NAME, PG_TYPE_XID, "xid", conn, table_owner, table_name, greloid, XMIN_ATTNUM, FALSE, table_info); ordinal++; } ret = SQL_SUCCESS; diff --git a/parse.c b/parse.c index 6645627..b4fe912 100644 --- a/parse.c +++ b/parse.c @@ -379,6 +379,92 @@ void lower_the_name(char *name, ConnectionClass *conn, BOOL dquote) } } +static BOOL CheckHasOidsUsingSaved(StatementClass *stmt, TABLE_INFO *ti) +{ + const COL_INFO *coli; + int table_info; + BOOL hasoids = FALSE, hassubclass =FALSE, keyFound = FALSE; + + MYLOG(DETAIL_LOG_LEVEL, "ti->col_info=%p\n", ti->col_info); + if (TI_checked_hasoids(ti)) + ; + else if (coli = ti->col_info, NULL != coli) + { + table_info = coli->table_info; + if (0 == (table_info & TBINFO_HASSUBCLASS)) + { + TI_set_has_no_subclass(ti); + } + else + { + hassubclass = TRUE; + TI_set_hassubclass(ti); + STR_TO_NAME(ti->bestitem, TABLEOID_NAME); + STRX_TO_NAME(ti->bestqual, "\"" TABLEOID_NAME "\" = %u"); + } + if (!hassubclass) + { + if (0 == (table_info & TBINFO_HASOIDS)) + { + TI_set_has_no_oids(ti); + } + else + { + hasoids = TRUE; + TI_set_hasoids(ti); + STR_TO_NAME(ti->bestitem, OID_NAME); + STRX_TO_NAME(ti->bestqual, "\"" OID_NAME "\" = %u"); + } + } + ti->table_oid = coli->table_oid; + if (!hasoids && !hassubclass) + { + QResultClass *res = coli->result; + int num_tuples = res ? QR_get_num_cached_tuples(res) : -1; + + if (num_tuples > 0) + { + int i; + + for (i = 0; i < num_tuples; i++) + { + if (QR_get_value_backend_int(res, i, COLUMNS_AUTO_INCREMENT, NULL) != 0&& + QR_get_value_backend_int(res, i, COLUMNS_FIELD_TYPE, NULL) == PG_TYPE_INT4) + { + char query[512]; + + STR_TO_NAME(ti->bestitem, QR_get_value_backend_text(res, i, COLUMNS_COLUMN_NAME)); + SPRINTF_FIXED(query, "\"%s\" = %%d", SAFE_NAME(ti->bestitem)); + STRX_TO_NAME(ti->bestqual, query); + break; + } + } + } + } + TI_set_hasoids_checked(ti); + } + else + return FALSE; + + stmt->num_key_fields = PG_NUM_NORMAL_KEYS; + if (TI_has_subclass(ti)) + keyFound = FALSE; + else if (TI_has_oids(ti)) + keyFound = TRUE; + else if (NAME_IS_NULL(ti->bestqual)) + { + keyFound = TRUE; + stmt->num_key_fields--; + } + else + keyFound = TRUE; + MYLOG(DETAIL_LOG_LEVEL, "subclass=%d oids=%d bestqual=%s keyFound=%d num_key_fields=%d\n", TI_has_subclass(ti), TI_has_oids(ti), PRINT_NAME(ti->bestqual), keyFound, stmt->num_key_fields); + + SC_set_checked_hasoids(stmt, keyFound); + + return TRUE; +} + static BOOL CheckHasOids(StatementClass * stmt) { QResultClass *res; @@ -387,11 +473,15 @@ static BOOL CheckHasOids(StatementClass * stmt) ConnectionClass *conn = SC_get_conn(stmt); TABLE_INFO *ti; +MYLOG(0, "Entering\n"); if (0 != SC_checked_hasoids(stmt)) return TRUE; if (!stmt->ti || !stmt->ti[0]) return FALSE; ti = stmt->ti[0]; + if (CheckHasOidsUsingSaved(stmt, ti)) + return TRUE; + // no longer come here?? SPRINTF_FIXED(query, "select relhasoids, c.oid, relhassubclass from pg_class c, pg_namespace n where relname = '%s' and nspname = '%s' and c.relnamespace = n.oid", SAFE_NAME(ti->table_name), SAFE_NAME(ti->schema_name)); @@ -456,6 +546,9 @@ static BOOL CheckHasOids(StatementClass * stmt) } QR_Destructor(res); SC_set_checked_hasoids(stmt, foundKey); + + MYLOG(DETAIL_LOG_LEVEL, "subclass=%d oids=%d bestqual=%s foundKey=%d num_key_fields=%d\n", TI_has_subclass(ti), TI_has_oids(ti), PRINT_NAME(ti->bestqual), foundKey, stmt->num_key_fields); + return TRUE; } @@ -933,6 +1026,8 @@ getColumnsInfo(ConnectionClass *conn, TABLE_INFO *wti, OID greloid, StatementCla coli->result = res; if (res && QR_get_num_cached_tuples(res) > 0) { + int num_tuples = QR_get_num_cached_tuples(res); + if (!greloid) greloid = (OID) strtoul(QR_get_value_backend_text(res, 0, COLUMNS_TABLE_OID), NULL, 10); if (!wti->table_oid) @@ -943,6 +1038,14 @@ getColumnsInfo(ConnectionClass *conn, TABLE_INFO *wti, OID greloid, StatementCla if (NAME_IS_NULL(wti->table_name)) STR_TO_NAME(wti->table_name, QR_get_value_backend_text(res, 0, COLUMNS_TABLE_NAME)); + for (int i = 0; i < num_tuples; i++) + { + if (NULL != QR_get_value_backend_text(res, 0, COLUMNS_TABLE_INFO)) + { + coli->table_info = QR_get_value_backend_int(res, 0, COLUMNS_TABLE_INFO, NULL); + break; + } + } } MYLOG(DETAIL_LOG_LEVEL, "#2 %p->table_name=%s(%u)\n", wti, PRINT_NAME(wti->table_name), wti->table_oid); /* diff --git a/results.c b/results.c index 00148ef..8aa88f1 100644 --- a/results.c +++ b/results.c @@ -2374,7 +2374,6 @@ AddDeleted(QResultClass *res, SQLULEN index, const KeySet *keyset) SQLLEN *deleted; KeySet *deleted_keyset; UWORD status; - Int2 num_fields = res->num_fields; MYLOG(DETAIL_LOG_LEVEL, "entering " FORMAT_ULEN "\n", index); dl_count = res->dl_count; diff --git a/test/expected/catalogfunctions.out b/test/expected/catalogfunctions.out index 3c84d9b..a6d11fd 100644 --- a/test/expected/catalogfunctions.out +++ b/test/expected/catalogfunctions.out @@ -65,6 +65,7 @@ PHYSICAL NUMBER: SMALLINT(5) digits: 0, nullable TABLE OID: INTEGER(10) digits: 0, nullable BASE TYPEID: INTEGER(10) digits: 0, nullable TYPMOD: INTEGER(10) digits: 0, nullable +TABLE INFO: INTEGER(10) digits: 0, nullable Result set: contrib_regression public booltab id 4 int4 contrib_regression public booltab t 12 varchar diff --git a/test/expected/catalogfunctions_1.out b/test/expected/catalogfunctions_1.out index 6c65d46..d438549 100644 --- a/test/expected/catalogfunctions_1.out +++ b/test/expected/catalogfunctions_1.out @@ -65,6 +65,7 @@ PHYSICAL NUMBER: SMALLINT(5) digits: 0, nullable TABLE OID: INTEGER(10) digits: 0, nullable BASE TYPEID: INTEGER(10) digits: 0, nullable TYPMOD: INTEGER(10) digits: 0, nullable +TABLE INFO: INTEGER(10) digits: 0, nullable Result set: contrib_regression public booltab id 4 int4 contrib_regression public booltab t -9 varchar diff --git a/test/src/bulkoperations-test.c b/test/src/bulkoperations-test.c index 0d59a46..37e035d 100644 --- a/test/src/bulkoperations-test.c +++ b/test/src/bulkoperations-test.c @@ -68,7 +68,7 @@ int main(int argc, char **argv) * Initialize a table with some test data. */ printf("Creating test table bulkoperations_test\n"); - rc = SQLExecDirect(hstmt, (SQLCHAR *) "CREATE TEMPORARY TABLE bulkoperations_test(i int4, orig int4)", SQL_NTS); + rc = SQLExecDirect(hstmt, (SQLCHAR *) "CREATE TEMPORARY TABLE bulkoperations_test(i int4, orig serial)", SQL_NTS); CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt); rc = SQLExecDirect(hstmt, (SQLCHAR *) "INSERT INTO bulkoperations_test SELECT g, g FROM generate_series(1, 10) g", SQL_NTS); CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt); diff --git a/test/src/cursor-block-delete-test.c b/test/src/cursor-block-delete-test.c index 1831cb6..2069fe2 100644 --- a/test/src/cursor-block-delete-test.c +++ b/test/src/cursor-block-delete-test.c @@ -76,7 +76,7 @@ int main(int argc, char **argv) exit(1); } - rc = SQLExecDirect(hstmt, (SQLCHAR *) "create temporary table tmptable(id int4 primary key)", SQL_NTS); + rc = SQLExecDirect(hstmt, (SQLCHAR *) "create temporary table tmptable(id serial primary key)", SQL_NTS); CHECK_STMT_RESULT(rc, "SQLExecDirect create table failed", hstmt); /* insert into a table */ -- 2.39.5