Failed to get Postgres indexes properly when calling DBMetas() for tables with specific index functions #2412

New Issue

slotix · 2024-02-15T23:52:07Z

slotix commented

2024-02-15 23:52:07 +00:00

Description:

When using the DBMetas() function in the Engine type to retrieve database metadata, there seems to be an issue with properly detecting Postgres indexes, especially when the index involves functions like to_tsvector.

Steps to Reproduce:

Create a table with an index involving the to_tsvector function, for example:

CREATE TABLE articles (
	id SERIAL PRIMARY KEY,
	title VARCHAR(255) NOT NULL,
	body TEXT
);

CREATE INDEX idx_articles_document
ON articles
USING gin(to_tsvector('english', title || ' ' || body));

Attempt to retrieve database metadata using the DBMetas() function.

Expected Behavior:

The database metadata should be retrieved successfully, including information about the indexes on the tables.

Actual Behavior:

Encounter the following error:

Unknown col to_tsvector seq 0, in index title of table articles, columns [id title body]

## Description: When using the DBMetas() function in the Engine type to retrieve database metadata, there seems to be an issue with properly detecting Postgres indexes, especially when the index involves functions like `to_tsvector`. ## Steps to Reproduce: 1. Create a table with an index involving the `to_tsvector` function, for example: ``` CREATE TABLE articles ( id SERIAL PRIMARY KEY, title VARCHAR(255) NOT NULL, body TEXT ); CREATE INDEX idx_articles_document ON articles USING gin(to_tsvector('english', title || ' ' || body)); ``` 2. Attempt to retrieve database metadata using the DBMetas() function. ## Expected Behavior: The database metadata should be retrieved successfully, including information about the indexes on the tables. ## Actual Behavior: Encounter the following error: ``` Unknown col to_tsvector seq 0, in index title of table articles, columns [id title body] ```

slotix commented

2024-02-16 12:56:21 +00:00

I have tried another approach

ALTER TABLE articles
    ADD COLUMN text_index_col tsvector
               GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
CREATE INDEX idx_articles_document
ON articles
USING gin(text_index_col);

This way, I'm encountering the following error when attempting to call DBMetas():

"unknown colType: tsvector - TSVECTOR"

Do you have plans to support complex data types/indexes in Postgres?

I have tried another approach ``` ALTER TABLE articles ADD COLUMN text_index_col tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED; CREATE INDEX idx_articles_document ON articles USING gin(text_index_col); ``` This way, I'm encountering the following error when attempting to call DBMetas(): ``` "unknown colType: tsvector - TSVECTOR" ``` Do you have plans to support complex data types/indexes in Postgres?

slotix commented

2024-02-16 13:01:32 +00:00

// DBMetas Retrieve all tables, columns, indexes' informations from database.
func (engine *Engine) DBMetas() ([]*schemas.Table, error) {
	tables, err := engine.dialect.GetTables(engine.db, engine.defaultContext)
	if err != nil {
		return nil, err
	}

	for _, table := range tables {
		if err = engine.loadTableInfo(engine.defaultContext, table); err != nil {
			return nil, err
		}
	}
	return tables, nil
}

Is is possible in loadTableInfo to return the columns that have been parsed successfully and skip unparsed ones somehow (with a warning maybe) ?

```go // DBMetas Retrieve all tables, columns, indexes' informations from database. func (engine *Engine) DBMetas() ([]*schemas.Table, error) { tables, err := engine.dialect.GetTables(engine.db, engine.defaultContext) if err != nil { return nil, err } for _, table := range tables { if err = engine.loadTableInfo(engine.defaultContext, table); err != nil { return nil, err } } return tables, nil } ``` Is is possible in `loadTableInfo` to return the columns that have been parsed successfully and skip unparsed ones somehow (with a warning maybe) ?

lunny added the

kind

proposal

label 2024-02-19 06:47:08 +00:00

slotix commented

2024-02-22 23:25:56 +00:00

Regular indexes look like:

CREATE INDEX equipment_state_index ON public.equipment_old USING btree (state)

However, the problem arises when parsing indexdefs like:

CREATE INDEX idx_fulltext ON public.equipment_old USING gin (to_tsvector('english'::regconfig, (((document_name)::text || ' '::text) || (inventory_number)::text)))

in

func getIndexColName(indexdef string) []string {
	var colNames []string

	cs := strings.Split(indexdef, "(")
	for _, v := range strings.Split(strings.Split(cs[1], ")")[0], ",") {
		colNames = append(colNames, strings.Split(strings.TrimLeft(v, " "), " ")[0])
	}

	return colNames
}

As a result, I'm getting the value

<[]string> (length: 1, cap: 1)
[0]: "to_tsvector"

But it should be something like

<[]string> (length: 2, cap: 2)
[0]: "document_name"
[1]: "inventory_number"

Regular indexes look like: ```sql CREATE INDEX equipment_state_index ON public.equipment_old USING btree (state) ``` However, the problem arises when parsing `indexdefs` like: ```sql CREATE INDEX idx_fulltext ON public.equipment_old USING gin (to_tsvector('english'::regconfig, (((document_name)::text || ' '::text) || (inventory_number)::text))) ``` in ```go func getIndexColName(indexdef string) []string { var colNames []string cs := strings.Split(indexdef, "(") for _, v := range strings.Split(strings.Split(cs[1], ")")[0], ",") { colNames = append(colNames, strings.Split(strings.TrimLeft(v, " "), " ")[0]) } return colNames } ``` As a result, I'm getting the value ``` <[]string> (length: 1, cap: 1) [0]: "to_tsvector" ``` But it should be something like ``` <[]string> (length: 2, cap: 2) [0]: "document_name" [1]: "inventory_number" ```

Sign in to join this conversation.

No Milestone

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#2412