sql-postgres connect using URL

sql-postgres connect using URL

[Rob Sargent]

I need to be able to supple option TCP options in my sql-postgres sessions.

I've tried the following, without effect so far.

|(setq sql-connection-alist '(db (sql-product 'postgres) (sql-database 
("postgresql://myusername@host/dbname?keepalives_idle=69?other=...")))|

Re: sql-postgres connect using URL

[Emanuel Berg]

Rob Sargent wrote:

> (setq sql-connection-alist '(db (sql-product 'postgres)
>  (sql-database
>  ("postgresql://myusername@host/dbname?keepalives_idle=69?other=...")))

Jean Louis: Have no fear, "I" is here

-- 
underground experts united
https://dataswamp.org/~incal

Re: sql-postgres connect using URL

[Jean Louis]

* Rob Sargent <rsargent@xmission.com> [2022-10-27 23:05]:
> I need to be able to supple option TCP options in my sql-postgres sessions.
> 
> I've tried the following, without effect so far.
> 
> |(setq sql-connection-alist '(db (sql-product 'postgres) (sql-database
> ("postgresql://myusername@host/dbname?keepalives_idle=69?other=...")))|

I think PostgreSQL listens on TCP port by default, so what you need to
supply are variables

(setq sql-database "my-db")
(setq sql-password "my-pass")
(setq sql-postgres-login-params (quote (user password server database)))
(setq sql-postgres-program "psql")
(setq sql-product (quote postgres))
(setq sql-server "my-remote-server")
(setq sql-user "my-user")


-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/

Re: sql-postgres connect using URL

[Rob Sargent]

On 10/27/22 14:56, Jean Louis wrote:
> * Rob Sargent <rsargent@xmission.com> [2022-10-27 23:05]:
>> I need to be able to supple option TCP options in my sql-postgres sessions.
>>
>> I've tried the following, without effect so far.
>>
>> |(setq sql-connection-alist '(db (sql-product 'postgres) (sql-database
>> ("postgresql://myusername@host/dbname?keepalives_idle=69?other=...")))|
> I think PostgreSQL listens on TCP port by default, so what you need to
> supply are variables
>
> (setq sql-database "my-db")
> (setq sql-password "my-pass")
> (setq sql-postgres-login-params (quote (user password server database)))
> (setq sql-postgres-program "psql")
> (setq sql-product (quote postgres))
> (setq sql-server "my-remote-server")
> (setq sql-user "my-user")
>
>
Yeah, that follows the expected pattern and my usual usage but I need to 
use the URL style connection string.  I don't believe the command line 
args include keepalives*

Re: sql-postgres connect using URL

[Jean Louis]

* Rob Sargent <rsargent@xmission.com> [2022-10-28 04:56]:
> Yeah, that follows the expected pattern and my usual usage but I need to use
> the URL style connection string.  I don't believe the command line args
> include keepalives*

I do not think that type of URL style connection string relates to
Emacs Lisp sql-postgres.  It uses `psql' client.

How sure are you that such string should work?

Another issue about keepalives is that such are configured by server
and not by client in PostgreSQL. 

See manual page of `psql': (man "psql") -- I can't find any reference
to keepalives there.

Your URL based keepalives most probably relate to some client in other
programming language; not to server.

-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/

Re: sql-postgres connect using URL

[Rob Sargent]

On 10/27/22 22:11, Jean Louis wrote:
> * Rob Sargent<rsargent@xmission.com>  [2022-10-28 04:56]:
>> Yeah, that follows the expected pattern and my usual usage but I need to use
>> the URL style connection string.  I don't believe the command line args
>> include keepalives*
> I do not think that type of URL style connection string relates to
> Emacs Lisp sql-postgres.  It uses `psql' client.
>
> How sure are you that such string should work?
>
> Another issue about keepalives is that such are configured by server
> and not by client in PostgreSQL.
>
> See manual page of `psql': (man "psql") -- I can't find any reference
> to keepalives there.
>
> Your URL based keepalives most probably relate to some client in other
> programming language; not to server.
>
This clearly works from the command line:

    psql postgresql://postgres@csgsdb/postgres?&keepalives_idle=60&keepalives_interval=30&keepalives_count=10

I do get connected to the database, at least.  I'll have to wait for the 
time out to NOT happen :). Meanwhile I'm connecting with emacs in the 
usual way, then once connected I issue a

    \c host=hostname user=rolename keepalives_idle=60
    keepalives_interval=30 keepalives_count=10

and the timeouts seem to have gone away

Re: sql-postgres connect using URL

[Jean Louis]

* Rob Sargent <rsargent@xmission.com> [2022-10-28 07:48]:
> This clearly works from the command line:
> 
>    psql
>    postgresql://postgres@csgsdb/postgres?&keepalives_idle=60&keepalives_interval=30&keepalives_count=10

I had no idea about it.

> I do get connected to the database, at least.  I'll have to wait for the
> time out to NOT happen :). Meanwhile I'm connecting with emacs in the usual
> way, then once connected I issue a
> 
>    \c host=hostname user=rolename keepalives_idle=60
>    keepalives_interval=30 keepalives_count=10

Then use variable sql-postgres-options to add extra arguments to
psql. 

You can also use ~/.psqlrc to set things up.


-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/

Re: sql-postgres connect using URL

[Rob Sargent]

On 10/27/22 23:25, Jean Louis wrote:
> * Rob Sargent <rsargent@xmission.com> [2022-10-28 07:48]:
>> This clearly works from the command line:
>>
>>     psql
>>     postgresql://postgres@csgsdb/postgres?&keepalives_idle=60&keepalives_interval=30&keepalives_count=10
> I had no idea about it.
>
>> I do get connected to the database, at least.  I'll have to wait for the
>> time out to NOT happen :). Meanwhile I'm connecting with emacs in the usual
>> way, then once connected I issue a
>>
>>     \c host=hostname user=rolename keepalives_idle=60
>>     keepalives_interval=30 keepalives_count=10
> Then use variable sql-postgres-options to add extra arguments to
> psql.
>
> You can also use ~/.psqlrc to set things up.
>
>
But question becomes How to stop emacs from prompting for all the usual 
args (host, etc).


Null the alist of prompts I suppose.  Let me dig in.

Re: sql-postgres connect using URL

[Jean Louis]

* Rob Sargent <rsargent@xmission.com> [2022-10-28 08:37]:
> But question becomes How to stop emacs from prompting for all the usual args
> (host, etc).

I have just tried this and it opens buffer straight without asking:

(let ((buffer (generate-new-buffer "*PostgreSQL*")))
  (apply #'make-comint-in-buffer "psql" buffer "psql" nil)
  (switch-to-buffer buffer))

You can make function out of it. I also use ~/.pgpass if it matters. 

`apply' is there if you wish to replace the last `nil' with `psql'
arguments. 

-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/

Re: sql-postgres connect using URL

[Rob Sargent]

On 10/28/22 01:05, Jean Louis wrote:
> * Rob Sargent <rsargent@xmission.com> [2022-10-28 08:37]:
>> But question becomes How to stop emacs from prompting for all the usual args
>> (host, etc).
> I have just tried this and it opens buffer straight without asking:
>
> (let ((buffer (generate-new-buffer "*PostgreSQL*")))
>    (apply #'make-comint-in-buffer "psql" buffer "psql" nil)
>    (switch-to-buffer buffer))
>
> You can make function out of it. I also use ~/.pgpass if it matters.
>
> `apply' is there if you wish to replace the last `nil' with `psql'
> arguments.
>
That does look promising!  Thank you.

Re: sql-postgres connect using URL

[Yuri Khan]

On Fri, 28 Oct 2022 at 11:46, Rob Sargent <rsargent@xmission.com> wrote:

> This clearly works from the command line:
>
>     psql postgresql://postgres@csgsdb/postgres?&keepalives_idle=60&keepalives_interval=30&keepalives_count=10

If you ran literally this without quoting or escaping the &s, I don’t
see how it could work. Here, you run four commands:

* ‘psql postgresql://postgres@csgsdb/postgres?’ in background
* empty command with environment variable ‘keepalives_idle’ set to
‘60’, in background
* empty command with environment variable ‘keepalives_interval’ set to
‘30’, in background
* empty command with environment variable ‘keepalives_count’ set to ‘10’

The last three have no effect and complete immediately. The first one
runs without any keepalive-related settings, and you can’t interact
with it because it’s in background.

Re: sql-postgres connect using URL

[Rob Sargent]

On 10/28/22 00:18, Yuri Khan wrote:
> On Fri, 28 Oct 2022 at 11:46, Rob Sargent <rsargent@xmission.com> wrote:
>
>> This clearly works from the command line:
>>
>>      psql postgresql://postgres@csgsdb/postgres?&keepalives_idle=60&keepalives_interval=30&keepalives_count=10
> If you ran literally this without quoting or escaping the &s, I don’t
> see how it could work. Here, you run four commands:
>
> * ‘psql postgresql://postgres@csgsdb/postgres?’ in background
> * empty command with environment variable ‘keepalives_idle’ set to
> ‘60’, in background
> * empty command with environment variable ‘keepalives_interval’ set to
> ‘30’, in background
> * empty command with environment variable ‘keepalives_count’ set to ‘10’
>
> The last three have no effect and complete immediately. The first one
> runs without any keepalive-related settings, and you can’t interact
> with it because it’s in background.
You're right.  single quote the whole argument