Command history configuration/HISTFILE option

[Seanstoppable]

In my psqlrc file, I have the following:

\set HISTFILE ~/.psql_history- :DBNAME

This allows for a history file per database I connect to, making it easy to get the history of commands for that database.

It would be nice if that were supported, ideally from parsing .psqlrc, to make switching easier.

Similarly, it would be nice if the history were done the same way as psql's (which is unfortunately not time stamped), again to ease transition and allow a new user to just start up pgcli and get their existing history.

[hunsakerbn]

For me a history-file-per-database ability is a must. I connect to a lot of different databases and I don't want history from other DBs showing up when working with my current DB. This is going to keep me from using this great product.

[anthonydb]

Adding my +1 for this as well as for HISTCONTROL ignoredups support. Love pgcli!

[eliottwiener]

Here's a dumb, hacky workaround for those who want history-file-per-database with the current release version of pgcli.

If, like me, you formerly used the environment variable PSQL_HISTORY (or any other variable) to configure your history-file-per-database setup, you can launch pgcli like so:

pgcli --pgclirc =(sed 's@history_file =.*@history_file = '$PSQL_HISTORY'@' ~/.config/pgcli/config) your_database_name

To explain briefly: The above creates a new temporary config file that is the same as the actual pgcli config file, only with the history_file = ... setting replaced to point to our desired history file ($PSQL_HISTORY in this case). Then pgcli is launched with the temporary config file.

The =(...) is a zsh-ism (sorry) for creating a temporary file, and is used in a similar fashion to <(...) of bash and others. In this case <(...) seems to not be suitable, possibly due to the way pgcli reads the config file. =(...) can easily be emulated in other shells. See: http://zsh.sourceforge.net/Doc/Release/Expansion.html#Process-Substitution

Create an alias or shell function incorporating the above approach, and you're good to go.

A more convenient solution would be to create a shell function that extracts the db name (and maybe host, etc) from the command line, and incorporates that into the history_file path used.

[viniciusd]

I am currently struggling switching among different databases. A per-database history would be neat

[laixintao]

I'd like to pick this four-years-ago feature request up. 😀

[laixintao]

I have been thinking this feature for days. So here's what I want to do:

pgcli allow user to set ?history_file=name in [alias_dsn] configuration. E.g. You can have this in your pgclirc:

  # history_file location.
  # In Unix/Linux: ~/.config/pgcli/history
  # In Windows: %USERPROFILE%\AppData\Local\dbcli\pgcli\history
  # %USERPROFILE% is typically C:\Users\{username}
  history_file = default
  [alias_dsn]
  db1 = postgresql://postgres:[email protected]:5432/mydb1?history_file=db1

Then, when you connect this db via $ pgcli -D db1 , pgcli will use ~/.config/pgcli/history.db1 (the history_file config in you pgclirc and suffix when config in alias_dsn)

See when you have a develop database on your machine, and another database on staging environment, you may want them share the same history file. You can set alias_dsn like this:

  [alias_dsn]
  dev = postgresql://postgres:[email protected]:5432/mydb1?history_file=coolproject
  staging = postgresql://postgres:[email protected]:5432/staging_db?history_file=coolproject

---

@Seanstoppable

It would be nice if that were supported, ideally from parsing .psqlrc, to make switching easier.

I think we can read ~/.psql_history on startup, but put those commands from ~/.psql_history to the end of pgcli's history. And newer command only write to pgcli's history, not ~/.psql_history.

I also want to add a new option on pgclirc like ignore_psql_history = False.

@j-bennet @amjith What do you think?

[j-bennet]

@laixintao I like the idea of history_file being linked to alias_dsn, and the possibility of sharing the file for databases that are located on different hosts but have the same structure. I don't exactly like the history_file parameter in connection string. I'd rather leave connection string alone, and add another section to our config file to handle mapping of dsn alias (or optionally of database name) to history file:

[alias_dsn]
dev = postgresql://postgres:[email protected]:5432/mydb1
staging = postgresql://postgres:[email protected]:5432/mydb1

[history_file]
dev = mydb1_history.txt
staging = mydb1_history.txt

what do you think?

[laixintao]

Good idea!

Since it's a separate section I think we should only allow absolute path (~ is allowed), also, default is supported.

But I am worried that the section's name is the same as history_file( https://github.com/dbcli/pgcli/blob/7626d9a5f27a221d87f31dcb5bb8fc12024f6c58/pgcli/pgclirc#L65 ). Not sure if it is clear enough if we add explicit comments in that section. :)

[j-bennet]

Hah. I forgot that we have the same name already. You're right, it may be confusing. We better name the section differently. Maybe history_file_mapping, or something like that.

[laixintao]

I wrote a demo pgclirc and support --history option, what do you think?

(Naming things are hard, I hope users can understand what those options/configs are used for, without contemplate...)

[quezak]

Wild idea: maybe it would be nice to generalize this feature, to be able to set any configuration options per database?

For example, dedicate a whole config section for a particular database. Or, even better, allow extending the default config file, so I can create separate config files per db containing the few differences I want to set, but all the common settings would still be loaded from the main config. This would be very useful, because you can then override some parameters when starting pgcli from commandline, and we surely don't want to add a CLI parameter for all of the config options there :)

Example usecases:

• turn off autocommit on "important" databases
• set custom prompt per database (I'm already hacking around this with a script that passes the --prompt param, but it's kinda ugly)
• sharing a script with my team, that runs pgcli on different databases that need some different settings -- but I still want it to respect everyone's personal config

edit: Another approach I just thought of would be to enable appending -o optionName=value to CLI params, similarily to ssh and other commands. (just droppin it in to have more ideas in one place)

[j-bennet]

Another approach I just thought of would be to enable appending -o optionName=value to CLI params, similarily to ssh and other commands

@quezak This sounds like a good solution for something that you envision will change a lot, and does not make much sense in config file.

For existing options, we could support specifying them "standalone" (example: pgcli --prompt '>') or via --options (example: pgcli -o prompt='> '.

In general, when I try to decide where a new option should go, my thought process is something like this:

To clarify, we currently don't have support for database-specific config file sections. But that would be a great feature to add.

[laixintao]

This flow is cool and makes sense to me.

@quezak Isn't --pgclirc option enough?

[quezak]

@laixintao --pgclirc allows only to override the whole configuration, so you can't change just a few options while still honoring user's personal preferences in his .pgclirc file. But that's a completely separate problem, probably out of scope for this issue :)