Postgresql doesn't auto complete table names and their associated information, while mysql does work well, what's the problem? #165

pltf492325129 · 2023-03-14T08:21:47Z

I want to configure autocomplete table names and their associated information, but it still doesn't work in postgresql, while mysql does work with the same configuration, what's the problem?

 {
    "language_servers": {
        "sql-language-server": {
            "serverSettings": {
                "sqlLanguageServer": {
                    "connections": [
                        {
                          "name": "sql-language-server",
                          "adapter": "mysql",
                          "host": "localhost",
                          "port": 3306,
                          "user": "root",
                          "password": "123456",
                          "database": "test",
                          "projectPaths": ["D:\\test\\sqltest"],
                        },
                        {
                          "name": "postgres_conf",
                          "adapter": "postgres",
                          "host": "100.85.127.227",
                          "port": 8000,
                          "user": "dbadmin",
                          "password": "123456",
                          "database": "gaussdb"
                        }
                    ]
                }
            }
        }
    },
    "loggingConsole": "floating",
    "loggingLevel": "debug",
        "setTrace": "verbose"
}

pltf492325129 · 2023-03-14T11:36:52Z

Auto complete table names do not work in postgre sql

joe-re · 2023-03-14T21:00:28Z

@pltf492325129 HI, if it works on mysql, maybe connection params could be wrong for postgresql.
Can you check it can connect to your postgresql on your local terminal first?

eg.

 $ psql -h 100.85.127.227 -p 8000 -U dbadmin -d gaussdb --password 123456

If it can connect to the server, please check the log if there are any errors on it.
The log should be in the tmpdir and you can confirm its location by os.tmpdir() method of NodeJS.

eg.

$ node -e 'console.log(os.tmpdir())'

pltf492325129 · 2023-03-15T08:38:01Z

First of all, thank you very much for your answer，but I still face some problem

connection is right for postgresql.
Thers are errors in log:
[DEBUG] default - Not to extract personal config, { path: , projectName: }

[2023-03-15T16:25:35.299] [DEBUG] default - onInitialize: receive change event from SettingStore
[2023-03-15T16:27:07.196] [DEBUG] default - onDidChangeConfiguration {"settings":{"sqlLanguageServer":{"connections":[{"name":"postgres-project","adapter":"postgres","host":"100.85.121.0","port":5432,"user":"root","password":"UQuery123!","database":"company","projectPaths":["/home/ma-user/work"],"path":"","projectName":""}]}}}
[2023-03-15T16:27:07.196] [DEBUG] default - Not to extract personal config, { path: , projectName:  }
[2023-03-15T16:27:07.196] [DEBUG] default - Set config: {"name":"postgres-project","adapter":"postgres","host":"100.85.121.0","port":5432,"user":"root","password":"UQuery123!","database":"company","projectPaths":["/home/ma-user/work"],"path":"","projectName":""}
[2023-03-15T16:27:07.197] [DEBUG] default - setting store, emit "change"
[2023-03-15T16:27:07.197] [DEBUG] default - onInitialize: receive change event from SettingStore

my personal config in Jupyterlab is:

    "language_servers": {
        "sql-language-server": {
            "serverSettings": {
                "sqlLanguageServer": {
                    "connections": [
                        {
                          "name": "postgres-project",
                          "adapter": "postgres",
                          "host": "100.85.121.0",
                          "port": 5432,
                          "user": "root",
                          "password": "UQuery123!",
                          "database": "company",
                          "projectPaths": ["/home/ma-user/work"],
                            "path": "",
                            "projectName": ""
                        }
                    ]
                }
            }
        }
    },
}

Even though I config path and projectName, there was still some error

Why do I need to config these two parameters, and how to config path and projectName? @joe-re

joe-re · 2023-03-17T02:04:14Z

@pltf492325129 No need to care about personal config if you don't set it. The debug message just indicates it was not able to find a personal config setting and it's not merged.
(Please see project configuration section https://github.com/joe-re/sql-language-server#project-confuguration-file)

As long as I can see from the below log, at least it succeeded to set configuration.

Set config: {"name":"postgres-project","adapter":"postgres","host":"100.85.121.0","port":5432,"user":"root","password":"UQuery123!","database":"company","projectPaths":["/home/ma-user/work"],"path":"","projectName":""}

Can I check is the projectPath is right?(/home/ma-user/work)
Is there any logs after onInitialize: receive change event from SettingStore ?

pltf492325129 · 2023-03-17T07:37:06Z

@joe-re I tried all the methods, but postgres still couldn't complete the table name and other information, but mysql, sqlite, and spark all worked

I know the reason is that didn't get the postgres schema, my configuration is right, so I don't know how to resolve this problem.
All configuration and log information are here. Could you please help me check the problem again? Thank you

1、Advanced Setting in Jupyterlab and log info

1.1 settings -> Advanced Setting Editor - >Language Server :

{
    "language_servers": {
        "sql-language-server": {
            "serverSettings": {
                "sqlLanguageServer": {
                    "connections": [
                        {
                          "name": "postgres-project",
                          "adapter": "postgres",
                          "host": "100.85.121.0",
                          "port": 5432,
                          "user": "root",
                          "password": "UQuery123!",
                          "database": "company",
                          "projectPaths": ["/pp"]
                        },
                    ]
                }
            }
        }
    },
}

1.2 log:

when default - onInitialize: receive change event from SettingStore It's going to get stuck here and it's not going to get the schema information

[2023-03-17T15:17:35.264] [DEBUG] default - onDidChangeConfiguration {"settings":{"sqlLanguageServer":{"connections":[{"name":"postgres-project","adapter":"postgres","host":"100.85.121.0","port":5432,"user":"root","password":"UQuery123!","database":"company","projectPaths":["/pp"]}]}}}
[2023-03-17T15:17:35.265] [DEBUG] default - Not to extract personal config, { path: , projectName:  }
[2023-03-17T15:17:35.265] [DEBUG] default - Set config: {"name":"postgres-project","adapter":"postgres","host":"100.85.121.0","port":5432,"user":"root","password":"UQuery123!","database":"company","projectPaths":["/pp"]}
[2023-03-17T15:17:35.265] [DEBUG] default - setting store, emit "change"
[2023-03-17T15:17:35.265] [DEBUG] default - onInitialize: receive change event from SettingStore




[2023-03-17T15:17:59.489] [DEBUG] default - onDidChangeContent: file:///pp/.virtual_documents/Untitled.ipynb.python-1(sql).sql, 3
[2023-03-17T15:17:59.489] [DEBUG] default - createDiagnostics
[2023-03-17T15:17:59.490] [DEBUG] default - parse error
[2023-03-17T15:17:59.490] [DEBUG] default - peg$SyntaxError: Expected "$", "(", "--", "/*", "SELECT", "\"", "`", [ \t\n\r], or [A-Za-z_] but end of input found.
    at peg$buildStructuredError (/pp/node_modules/@joe-re/sql-parser/base/parser.js:1393:12)
    at Object.peg$parse [as parse] (/pp/node_modules/@joe-re/sql-parser/base/parser.js:13086:11)
    at exports.parse (/pp/node_modules/@joe-re/sql-parser/index.js:7:19)
    at createDiagnostics (/pp/node_modules/sql-language-server/dist/src/createDiagnostics.js:53:44)
    at makeDiagnostics (/pp/node_modules/sql-language-server/dist/src/createServer.js:84:61)
    at /pp/node_modules/sql-language-server/dist/src/createServer.js:89:9
    at CallbackList.invoke (/pp/node_modules/vscode-jsonrpc/lib/common/events.js:55:39)
    at Emitter.fire (/pp/node_modules/vscode-jsonrpc/lib/common/events.js:117:36)
    at /pp/node_modules/vscode-languageserver/lib/common/textDocuments.js:138:42
    at handleNotification (/pp/node_modules/vscode-jsonrpc/lib/common/connection.js:640:25) {
  expected: [
    {
      type: 'class',
      parts: [Array],
      inverted: false,
      ignoreCase: false
    },
    { type: 'literal', text: '--', ignoreCase: false },
    { type: 'literal', text: '/*', ignoreCase: false },
    {
      type: 'class',
      parts: [Array],
      inverted: false,
      ignoreCase: false
    },
    {
      type: 'class',
      parts: [Array],
      inverted: false,
      ignoreCase: false
    },
    {
      type: 'class',
      parts: [Array],
      inverted: false,
      ignoreCase: false
    },
    { type: 'literal', text: '`', ignoreCase: false },
    { type: 'literal', text: '"', ignoreCase: false },
    { type: 'literal', text: '$', ignoreCase: false },
    { type: 'literal', text: '$', ignoreCase: false },
    { type: 'literal', text: 'SELECT', ignoreCase: true },
    { type: 'literal', text: '(', ignoreCase: false },
    { type: 'literal', text: '(', ignoreCase: false },
    { type: 'literal', text: '(', ignoreCase: false }
  ],
  found: null,
  location: {
    start: { offset: 15, line: 5, column: 1 },
    end: { offset: 15, line: 5, column: 1 }
  }
}

2、Project confuguration file and log info

2.1 /home/ma-user/work/.sqllsrc.json

{
  "name": "postgres-project",
  "adapter": "postgres",
  "host": "100.85.121.0",
  "port": 5432,
  "user": "root",
  "password": "UQuery123!",
  "database": "company"
}

2.2 log:

[2023-03-17T15:02:39.255] [DEBUG] default - onDidChangeConfiguration {"settings":{}}
[2023-03-17T15:02:39.255] [DEBUG] default - onDidChangeConfiguration it doesn't have sqlLanguageServer property
[2023-03-17T15:02:39.256] [DEBUG] default - onDidChangeConfiguration {"settings":{"sqlLanguageServer":{"connections":[{"name":"postgres-conf","adapter":"postgresql","host":"100.85.121.0","port":5432,"user":"root","password":"UQuery123!","database":"company"}]}}}
[2023-03-17T15:02:39.256] [DEBUG] default - Not to extract personal config, { path: , projectName:  }
[2023-03-17T15:02:39.256] [DEBUG] default - Set config: {"name":"postgres-conf","adapter":"postgresql","host":"100.85.121.0","port":5432,"user":"root","password":"UQuery123!","database":"company"}
[2023-03-17T15:02:39.257] [DEBUG] default - setting store, emit "change"
[2023-03-17T15:02:39.257] [DEBUG] default - onInitialize: receive change event from SettingStore
[2023-03-17T15:02:39.354] [DEBUG] default - onDidChangeContent: file:///pp/.virtual_documents/Untitled.ipynb.python-1(sql).sql, 0
[2023-03-17T15:02:39.354] [DEBUG] default - createDiagnostics
[2023-03-17T15:02:39.360] [DEBUG] default - ast: {"type":"select","keyword":{"type":"keyword","value":"SELECT","location":{"start":{"offset":0,"line":1,"column":1},"end":{"offset":6,"line":1,"column":7}}},"distinct":null,"columns":{"type":"star","value":"*"},"from":{"type":"from","keyword":{"type":"keyword","value":"FROM","location":{"start":{"offset":9,"line":3,"column":1},"end":{"offset":13,"line":3,"column":5}}},"tables":[{"type":"table","catalog":"","db":"","table":"compa","as":null,"location":{"start":{"offset":14,"line":4,"column":1},"end":{"offset":20,"line":5,"column":1}}}],"location":{"start":{"offset":9,"line":3,"column":1},"end":{"offset":20,"line":5,"column":1}}},"where":null,"groupby":null,"orderby":null,"limit":null,"location":{"start":{"offset":0,"line":1,"column":1},"end":{"offset":20,"line":5,"column":1}}}
[2023-03-17T15:02:39.363] [DEBUG] default - []
[2023-03-17T15:02:39.363] [DEBUG] default - diagnostics: []

3、Personal config file and log info:

3.1 ~/.config/sql-language-server/.sqllsrc.json

{
  "connections": [
    {
	  "name": "postgres-project",
	  "adapter": "postgres",
	  "host": "100.85.121.0",
	  "port": 5432,
	  "user": "root",
	  "password": "UQuery123!",
	  "database": "company",
	  "projectPaths": ["/pp"]
    }
  ]
}

3.2 log：

[2023-03-17T15:13:06.571] [INFO] default - start sql-languager-server
[2023-03-17T15:13:06.594] [DEBUG] default - onInitialize: undefined
[2023-03-17T15:13:06.675] [DEBUG] default - onDidChangeConfiguration {"settings":{}}
[2023-03-17T15:13:06.675] [DEBUG] default - onDidChangeConfiguration it doesn't have sqlLanguageServer property
[2023-03-17T15:13:06.761] [DEBUG] default - onDidChangeContent: file:///pp/.virtual_documents/Untitled.ipynb.python-1(sql).sql, 0
[2023-03-17T15:13:06.761] [DEBUG] default - createDiagnostics
[2023-03-17T15:13:06.766] [DEBUG] default - ast: {"type":"select","keyword":{"type":"keyword","value":"SELECT","location":{"start":{"offset":0,"line":1,"column":1},"end":{"offset":6,"line":1,"column":7}}},"distinct":null,"columns":{"type":"star","value":"*"},"from":{"type":"from","keyword":{"type":"keyword","value":"FROM","location":{"start":{"offset":9,"line":3,"column":1},"end":{"offset":13,"line":3,"column":5}}},"tables":[{"type":"table","catalog":"","db":"","table":"com","as":null,"location":{"start":{"offset":14,"line":4,"column":1},"end":{"offset":18,"line":5,"column":1}}}],"location":{"start":{"offset":9,"line":3,"column":1},"end":{"offset":18,"line":5,"column":1}}},"where":null,"groupby":null,"orderby":null,"limit":null,"location":{"start":{"offset":0,"line":1,"column":1},"end":{"offset":18,"line":5,"column":1}}}

MySQL right log info:

MySQL can get schema right .

[2023-03-17T14:42:48.277] [DEBUG] default - onDidChangeConfiguration {"settings":{"sqlLanguageServer":{"connections":[{"name":"sql-language-server","adapter":"mysql","host":"100.85.125.154","port":3306,"user":"root","password":"UQuery123!","database":"mysql"}]}}}
[2023-03-17T14:42:48.277] [DEBUG] default - Not to extract personal config, { path: , projectName:  }
[2023-03-17T14:42:48.277] [DEBUG] default - Set config: {"name":"sql-language-server","adapter":"mysql","host":"100.85.125.154","port":3306,"user":"root","password":"UQuery123!","database":"mysql"}
[2023-03-17T14:42:48.277] [DEBUG] default - setting store, emit "change"
[2023-03-17T14:42:48.277] [DEBUG] default - onInitialize: receive change event from SettingStore
[2023-03-17T14:42:48.318] [DEBUG] default - get schema
[2023-03-17T14:42:48.318] [DEBUG] default - {"tables":[{"catalog":null,"database":"mysql","tableName":"columns_priv","columns":[{"columnName":"Host","description":"Host(Type: char(60), Null: NO, Default: )"},{"columnName":"Db","description":"Db(Type: char(64), Null: NO, Default: )"},{"columnName":"User","description":"User(Type: char(32), Null: NO, Default: )"},{"columnName":"Table_name","description":"Table_name(Type: char(64), Null: NO, Default: )"},{"columnName":"Column_name","description":"Column_name(Type: char(64), Null: NO, Default: )"},{"columnName":"Timestamp","description":"Timestamp(Type: timestamp, Null: NO, Default: CURRENT_TIMESTAMP)"}}

pltf492325129 · 2023-03-20T08:01:58Z

@pltf492325129 No need to care about personal config if you don't set it. The debug message just indicates it was not able to find a personal config setting and it's not merged. (Please see project configuration section https://github.com/joe-re/sql-language-server#project-confuguration-file)

As long as I can see from the below log, at least it succeeded to set configuration.
Set config: {"name":"postgres-project","adapter":"postgres","host":"100.85.121.0","port":5432,"user":"root","password":"UQuery123!","database":"company","projectPaths":["/home/ma-user/work"],"path":"","projectName":""}
Can I check is the projectPath is right?(/home/ma-user/work) Is there any logs after onInitialize: receive change event from SettingStore ?

First, projectPath is right
After onInitialize: receive change event from SettingStore , log console will be stuck here, it wasn't get any schema info.

joe-re · 2023-03-21T23:48:18Z

@pltf492325129 Hi, thanks for your sharing the details.
I've checked the implementation of our Postgres client and found some problems.
Fixed and released it with v1.3.2. I think it would fix your problem also. Can you try to update sql-language-server version to the latest and try it again? Thanks.

pltf492325129 · 2023-03-23T09:59:05Z

@joe-re I'm very Existed！！！ Now, everything is work well. I admire your efficiency and timely prompt response.
At the same time, I'd like to ask you about some iteration plan. Whether there is such a plan in the future

Support code formatting
Real time update schema in jupyterlab

joe-re · 2023-03-23T17:34:13Z

@pltf492325129 Thanks for your response!

We support code formatting by fixAllFixableProblems command.
Please check lint section on the README. https://github.com/joe-re/sql-language-server#lint
When we implement something lint rules, we can also implement fixing the problem.
Lint rules are not very much yet, please raise the issue anytime when you find something lacking in your use case.
(Of course your PR is always welcome :) )

So we have had the code formatting feature itself already but actually code formatting should be implemented on textDocument/formatting.
https://microsoft.github.io/language-server-protocol/specifications/lsp/3.17/specification/

We'll let it be able to call it by executing textDocument/formatting soon.

According to the real-time update schema, I'm actually not familiar with jupyterlab.
We need to have another discussion for knowing the use case and how we detect update in software lifecycle.
Please feel free to raise the issue and let's discuss.

Thanks.

joe-re self-assigned this Mar 14, 2023

joe-re mentioned this issue Mar 21, 2023

upgrade pg-client #174

Merged

pltf492325129 closed this as completed Apr 2, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Postgresql doesn't auto complete table names and their associated information, while mysql does work well, what's the problem? #165

Postgresql doesn't auto complete table names and their associated information, while mysql does work well, what's the problem? #165

pltf492325129 commented Mar 14, 2023 •

edited

Loading

pltf492325129 commented Mar 14, 2023

joe-re commented Mar 14, 2023 •

edited

Loading

pltf492325129 commented Mar 15, 2023 •

edited

Loading

joe-re commented Mar 17, 2023

pltf492325129 commented Mar 17, 2023 •

edited

Loading

pltf492325129 commented Mar 20, 2023 •

edited

Loading

joe-re commented Mar 21, 2023

pltf492325129 commented Mar 23, 2023 •

edited

Loading

joe-re commented Mar 23, 2023 •

edited

Loading

Postgresql doesn't auto complete table names and their associated information, while mysql does work well, what's the problem? #165

Postgresql doesn't auto complete table names and their associated information, while mysql does work well, what's the problem? #165

Comments

pltf492325129 commented Mar 14, 2023 • edited Loading

pltf492325129 commented Mar 14, 2023

joe-re commented Mar 14, 2023 • edited Loading

pltf492325129 commented Mar 15, 2023 • edited Loading

joe-re commented Mar 17, 2023

pltf492325129 commented Mar 17, 2023 • edited Loading

1、Advanced Setting in Jupyterlab and log info

1.1 settings -> Advanced Setting Editor - >Language Server :

1.2 log:

2、Project confuguration file and log info

2.1 /home/ma-user/work/.sqllsrc.json

2.2 log:

3、Personal config file and log info:

3.1 ~/.config/sql-language-server/.sqllsrc.json

3.2 log：

MySQL right log info:

pltf492325129 commented Mar 20, 2023 • edited Loading

joe-re commented Mar 21, 2023

pltf492325129 commented Mar 23, 2023 • edited Loading

joe-re commented Mar 23, 2023 • edited Loading

pltf492325129 commented Mar 14, 2023 •

edited

Loading

joe-re commented Mar 14, 2023 •

edited

Loading

pltf492325129 commented Mar 15, 2023 •

edited

Loading

pltf492325129 commented Mar 17, 2023 •

edited

Loading

pltf492325129 commented Mar 20, 2023 •

edited

Loading

pltf492325129 commented Mar 23, 2023 •

edited

Loading

joe-re commented Mar 23, 2023 •

edited

Loading