marta-jankovics commented on code in PR #3385:
URL: https://github.com/apache/fineract/pull/3385#discussion_r1302269082
##########
fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/PostgreSQLQueryService.java:
##########
@@ -54,8 +54,10 @@ public boolean isTablePresent(DataSource dataSource, String
tableName) {
@Override
public SqlRowSet getTableColumns(DataSource dataSource, String tableName) {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
- String sql = "SELECT attname AS COLUMN_NAME, not attnotnull AS
IS_NULLABLE, atttypid::regtype AS DATATYPE, attlen AS
CHARACTER_MAXIMUM_LENGTH, attnum = 1 AS COLUMN_KEY FROM pg_attribute WHERE
attrelid = '\""
- + tableName + "\"'::regclass AND attnum > 0 AND NOT
attisdropped ORDER BY attnum";
+ String sql = "SELECT column_name, is_nullable, data_type,"
Review Comment:
Yes, it is slower. Running 10000 times two different table query takes 3 sec
from information_schema.columns and less than a sec from pg_attribute. The
question is, that it isn't fast enough.
The problem was that in pg_attribute the attlen parameter is -1 for varchar
properties, so the datatable update considered all string type columns to be
changed because of the length difference.
btw I asked @galovics if there was any special reason to use pg_attribute
and if I could change this.
- information_schema.columns:
DO $$
begin
FOR i IN 0..100000 loop
PERFORM column_name, is_nullable, data_type,
coalesce(character_maximum_length, numeric_precision, datetime_precision) AS
max_length, ordinal_position = 1 AS column_key
FROM information_schema.columns WHERE table_catalog = current_catalog
AND table_schema = current_schema AND table_name = 'm_client_5F564' ORDER BY
ordinal_position;
PERFORM column_name, is_nullable, data_type,
coalesce(character_maximum_length, numeric_precision, datetime_precision) AS
max_length, ordinal_position = 1 AS column_key
FROM information_schema.columns WHERE table_catalog = current_catalog
AND table_schema = current_schema AND table_name = 'dt_m_loan_5i03u' ORDER BY
ordinal_position;
END LOOP;
END; $$
Start time Tue Aug 22 23:52:24 CEST 2023
Finish time Tue Aug 22 23:52:27 CEST 2023
Planning Time: 4.204 ms
Execution Time: 1.022 ms
- pg_attribute
DO $$
BEGIN
FOR i IN 0..10000 loop
PERFORM attname AS COLUMN_NAME, not attnotnull AS IS_NULLABLE,
atttypid::regtype AS DATATYPE, attlen AS CHARACTER_MAXIMUM_LENGTH, attnum = 1
AS COLUMN_KEY
FROM pg_attribute WHERE attrelid = '"m_client_5F564"'::regclass AND
attnum > 0 AND NOT attisdropped ORDER BY attnum;
PERFORM attname AS COLUMN_NAME, not attnotnull AS IS_NULLABLE,
atttypid::regtype AS DATATYPE, attlen AS CHARACTER_MAXIMUM_LENGTH, attnum = 1
AS COLUMN_KEY
FROM pg_attribute WHERE attrelid = '"dt_m_loan_5i03u"'::regclass AND
attnum > 0 AND NOT attisdropped ORDER BY attnum;
END LOOP;
END; $$
Start time Tue Aug 22 23:57:18 CEST 2023
Finish time Tue Aug 22 23:57:18 CEST 2023
Planning Time: 0.124 ms
Execution Time: 0.053 ms
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
