Thread: Multiple psql history files
The .psql_history file is naturally used by different DB connections (distinguished by a different combination of host + port + database + user).
At least in my multi-database working environment, this leads sometimes to frustration because there are commands that cannot or should not be used by different connections.What do you say?
On Tue, Oct 18, 2016 at 12:26 PM, Jonathan Jacobson <jonjac@gmail.com> wrote:
JonathanI will be happy to make this my first contribution to PostgreSQL's code.To solve this, psql could keep a separate command history file for each connection.The .psql_history file is naturally used by different DB connections (distinguished by a different combination of host + port + database + user).At least in my multi-database working environment, this leads sometimes to frustration because there are commands that cannot or should not be used by different connections.What do you say?Regards,
\set HISTFILE ~/.psql_history- :DBNAME
There's also :PORT and a few other vars.
I will be happy to make this my first contribution to PostgreSQL's code.To solve this, psql could keep a separate command history file for each connection.The .psql_history file is naturally used by different DB connections (distinguished by a different combination of host + port + database + user).At least in my multi-database working environment, this leads sometimes to frustration because there are commands that cannot or should not be used by different connections.What do you say?
I would say that users needing such fine grained control of the history file should avail themselves of HISTFILE and other related psql variables and/or the PSQL_HISTORY environment variable.
David J.
On 18/10/2016 18:26, Jonathan Jacobson wrote: > The .psql_history file is naturally used by different DB connections > (distinguished by a different combination of host + port + database + user). > At least in my multi-database working environment, this leads sometimes > to frustration because there are commands that cannot or should not be > used by different connections. > To solve this, psql could keep a separate command history file for each > connection. You can already do this, for instance in your .psqlrc: \set HISTFILE ~/.psql_history- :HOST - :PORT - :DBNAME - :USER -- Julien Rouhaud http://dalibo.com - http://dalibo.org
I will be happy to make this my first contribution to PostgreSQL's code.To solve this, psql could keep a separate command history file for each connection.The .psql_history file is naturally used by different DB connections (distinguished by a different combination of host + port + database + user).At least in my multi-database working environment, this leads sometimes to frustration because there are commands that cannot or should not be used by different connections.What do you say?
Existing capabilities not withstanding the feature itself would probably be considered but I'm doubtful it would replace the default behavior.
The user would, at minimum, have to designate
a directory into which the separate history files would be created - polluting the home directory is unappealing.
If you want to propose the specifics of a feature that meets those two criteria specific comments and opinions on include-ability can be made.
David J.
Jonathan Jacobson <jonjac@gmail.com> writes:
> The .psql_history file is naturally used by different DB connections
> (distinguished by a different combination of host + port + database + user).
> At least in my multi-database working environment, this leads sometimes to
> frustration because there are commands that cannot or should not be used by
> different connections.
> To solve this, psql could keep a separate command history file for each
> connection.
> I will be happy to make this my first contribution to PostgreSQL's code.
> What do you say?
Personally, I'd be strongly against that because I frequently *want*
to re-use the same command on different connections. As an example,
comparing the behavior of the same command in different PG versions
(hence different postmasters) is an everyday task for me.
I can see that others might have different needs, but surely this
is going to be a use-case-specific requirement.
It's already possible to control which history file is used via psql's
HISTFILE variable and/or the PSQL_HISTORY environment variable. Perhaps
you can solve your problem today by manipulating those?
One interesting point, if you wish to consider history as being
connection-specific, is what happens during a \c command. Right
now the answer is "nothing" but you might wish it were different.
regards, tom lane
Jonathan Jacobson <jonjac@gmail.com> writes:
> The .psql_history file is naturally used by different DB connections
> (distinguished by a different combination of host + port + database + user).
> At least in my multi-database working environment, this leads sometimes to
> frustration because there are commands that cannot or should not be used by
> different connections.
> To solve this, psql could keep a separate command history file for each
> connection.
> I will be happy to make this my first contribution to PostgreSQL's code.
> What do you say?
One interesting point, if you wish to consider history as being
connection-specific, is what happens during a \c command. Right
now the answer is "nothing" but you might wish it were different.
Just to clarify/confirm a point inferred from the docs...
If you place "\set HISTFILE ~/.psql_history- :DBNAME
"
into your .psqlrc file then when you perform a "\c" the .psqlrc file is re-read and the new value for DBNAME is used to generate a new history file name.
As Julien pointed out cross-thread this really does seem to be the best place to implement such logic - though shell functions and aliases can be used to some good effect as well.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Oct 18, 2016 at 9:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> One interesting point, if you wish to consider history as being
>> connection-specific, is what happens during a \c command. Right
>> now the answer is "nothing" but you might wish it were different.
> Just to clarify/confirm a point inferred from the docs...
> If you place "\set HISTFILE ~/.psql_history- :DBNAME"
> into your .psqlrc file then when you perform a "\c" the .psqlrc file is
> re-read and the new value for DBNAME is used to generate a new history
> file name.
Um, no, I see no indication of that in the code. Where did you read that
in the docs?
If we wanted the history file to change at \c, I think the best way would
be to invent some escape-patterns that could be placed in the value of
HISTFILE, say along the lines of "\set HISTFILE ~/.psql_history-%h-%p",
and then specify that if the value contains any such patterns we'll dump
and reload the history file when reconnecting. But TBH I don't think
it's worth the trouble. I'd sure like to have seen multiple requests
for such functionality before we go to the trouble.
regards, tom lane
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Oct 18, 2016 at 9:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> One interesting point, if you wish to consider history as being
>> connection-specific, is what happens during a \c command. Right
>> now the answer is "nothing" but you might wish it were different.
> Just to clarify/confirm a point inferred from the docs...
> If you place "\set HISTFILE ~/.psql_history- :DBNAME"
> into your .psqlrc file then when you perform a "\c" the .psqlrc file is
> re-read and the new value for DBNAME is used to generate a new history
> file name.
Um, no, I see no indication of that in the code. Where did you read that
in the docs?
DBNAMEThe name of the database you are currently connected to. This is set every time you connect to a database (including program start-up), but can be unset.
HISTFILE
The file name that will be used to store the history list. The default value is ~/.psql_history. For example, putting:
\set HISTFILE ~/.psql_history- :DBNAME
in ~/.psqlrc will cause psql to maintain a separate history for each database.
The "including program start-up" aspect to DBNAME means that it is changed upon using "\c"
I inferred the part about .psqlrc being re-read and thus taking on the new value of :DBNAME in the example.
psqlrc is later defined to be "the [system|user] startup file" so it was wrong to conclude that it was re-read upon issuing "\c" - though "connection startup" isn't a totally unreasonable interpretation of the timing at which this file is read. Not everyone is going to associate the "rc" suffix with the file only being read during program startup.
If we wanted the history file to change at \c, I think the best way would
be to invent some escape-patterns that could be placed in the value of
HISTFILE, say along the lines of "\set HISTFILE ~/.psql_history-%h-%p",
and then specify that if the value contains any such patterns we'll dump
and reload the history file when reconnecting. But TBH I don't think
it's worth the trouble. I'd sure like to have seen multiple requests
for such functionality before we go to the trouble.
A slightly less invasive approach would be to have a "connection startup script" file..."psql-conn-rc"...that is re-read immediately after a successful connection is made to a database.
David J.