Thread: psql doesn't reuse -p after backend fail
hi,
pg version: 9.0.5 - head from 9.0 branch in git.
ran psql with specyfying port:
psql -p 4329 -U postgres -d some_database
then I run query which breaks backend:
=# select * from categories limit 1;
The connection to the server was lost. Attempting reset: Failed.
!>
When I'll try to re-issue \c some_database now, I got:
!> \c some_database
could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
!>
Clearly it is looking for socket on port 5432, which is bad, because I
explicitly specified the port to be 4329.
before backend crash, \c works normally.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
hubert depesz lubaczewski <depesz@depesz.com> writes:
> ran psql with specyfying port:
> psql -p 4329 -U postgres -d some_database
> then I run query which breaks backend:
> =# select * from categories limit 1;
> The connection to the server was lost. Attempting reset: Failed.
> !>
> When I'll try to re-issue \c some_database now, I got:
> !> \c some_database
> could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
> !>
It's not just the port, it's all the connection parameters ---
do_connect relies on the PGconn object to remember those, and in this
case there no longer is a PGconn object.
We could have psql keep that information separately, but I'm not sure
it's really worth the trouble.
regards, tom lane
On 09/05/2011 08:27 PM, Tom Lane wrote: > hubert depesz lubaczewski <depesz@depesz.com> writes: >> ran psql with specyfying port: >> psql -p 4329 -U postgres -d some_database > >> then I run query which breaks backend: > >> =# select * from categories limit 1; >> The connection to the server was lost. Attempting reset: Failed. >> !> > >> When I'll try to re-issue \c some_database now, I got: > >> !> \c some_database >> could not connect to server: No such file or directory >> Is the server running locally and accepting >> connections on Unix domain socket "/tmp/.s.PGSQL.5432"? >> !> > > It's not just the port, it's all the connection parameters --- > do_connect relies on the PGconn object to remember those, and in this > case there no longer is a PGconn object. > > We could have psql keep that information separately, but I'm not sure > it's really worth the trouble. hmm I can see this as potentially very dangerous, just picture what might happen if you actually get a connection because there is another instance actually running on the default port (say 5432 is production and 4329 is development) and you end up issuing the wrong commands. If we provide "automatic reconnect" we should rather do that correctly or not at all... Stefan
On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
> It's not just the port, it's all the connection parameters ---
> do_connect relies on the PGconn object to remember those, and in this
> case there no longer is a PGconn object.
>
> We could have psql keep that information separately, but I'm not sure
> it's really worth the trouble.
well, I think it's definitely worth the trouble. If I had datbaase
standing at 5432, it would connect to it, and then I could mistakenly
ran commands to wrong database.
this is clearly not a good thing.
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
>> It's not just the port, it's all the connection parameters ---
>> do_connect relies on the PGconn object to remember those, and in this
>> case there no longer is a PGconn object.
>>
>> We could have psql keep that information separately, but I'm not sure
>> it's really worth the trouble.
> well, I think it's definitely worth the trouble.
[ shrug.. ] So submit a patch. Personally I don't think the case comes
up often enough to be worth the trouble, and I'd much rather spend
development time on preventing the server from crashing in the first
place.
regards, tom lane
On Tue, Sep 06, 2011 at 11:35:43AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
> >> It's not just the port, it's all the connection parameters ---
> >> do_connect relies on the PGconn object to remember those, and in this
> >> case there no longer is a PGconn object.
> >>
> >> We could have psql keep that information separately, but I'm not sure
> >> it's really worth the trouble.
>
> > well, I think it's definitely worth the trouble.
>
> [ shrug.. ] So submit a patch. Personally I don't think the case comes
way above my skillset :(
> up often enough to be worth the trouble, and I'd much rather spend
> development time on preventing the server from crashing in the first
> place.
the reason for the crash is discussed in the pg_upgrade thread on
hackers. ( 8.3 ltree + pg_upgrade to 9.0.5 == backend crash on select).
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
Tom Lane wrote: > hubert depesz lubaczewski <depesz@depesz.com> writes: > > On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote: > >> It's not just the port, it's all the connection parameters --- > >> do_connect relies on the PGconn object to remember those, and in this > >> case there no longer is a PGconn object. > >> > >> We could have psql keep that information separately, but I'm not sure > >> it's really worth the trouble. > > > well, I think it's definitely worth the trouble. > > [ shrug.. ] So submit a patch. Personally I don't think the case comes > up often enough to be worth the trouble, and I'd much rather spend > development time on preventing the server from crashing in the first > place. Should we document this somewhere? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Yeah, Definitely it's not *expected* behavior so documentation is the good starting point unless we fix the code soon. I don't agree with Tom's comment on we should find out the reason for crash instead. In most of the cases, reason for the crash is because someone restarted database and user trying to get connection back. my 2 cents! On Tue, Sep 6, 2011 at 11:49 AM, Bruce Momjian <bruce@momjian.us> wrote: > Tom Lane wrote: > > hubert depesz lubaczewski <depesz@depesz.com> writes: > > > On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote: > > >> It's not just the port, it's all the connection parameters --- > > >> do_connect relies on the PGconn object to remember those, and in this > > >> case there no longer is a PGconn object. > > >> > > >> We could have psql keep that information separately, but I'm not sure > > >> it's really worth the trouble. > > > > > well, I think it's definitely worth the trouble. > > > > [ shrug.. ] So submit a patch. Personally I don't think the case comes > > up often enough to be worth the trouble, and I'd much rather spend > > development time on preventing the server from crashing in the first > > place. > > Should we document this somewhere? > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Denish Patel, OmniTi Computer Consulting Inc. Database Administrator, Phone: 443.325.1357 x 232
On tis, 2011-09-06 at 17:12 +0200, hubert depesz lubaczewski wrote: > On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote: > > It's not just the port, it's all the connection parameters --- > > do_connect relies on the PGconn object to remember those, and in this > > case there no longer is a PGconn object. > > > > We could have psql keep that information separately, but I'm not sure > > it's really worth the trouble. > > well, I think it's definitely worth the trouble. If I had datbaase > standing at 5432, it would connect to it, and then I could mistakenly > ran commands to wrong database. > this is clearly not a good thing. Perhaps just prevent \connect without argument if the information is no longer available.
On Thu, Sep 8, 2011 at 5:09 AM, Peter Eisentraut <peter_e@gmx.net> wrote: > On tis, 2011-09-06 at 17:12 +0200, hubert depesz lubaczewski wrote: >> On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote: >> > It's not just the port, it's all the connection parameters --- >> > do_connect relies on the PGconn object to remember those, and in this >> > case there no longer is a PGconn object. >> > >> > We could have psql keep that information separately, but I'm not sure >> > it's really worth the trouble. >> >> well, I think it's definitely worth the trouble. If I had datbaase >> standing at 5432, it would connect to it, and then I could mistakenly >> ran commands to wrong database. >> this is clearly not a good thing. > > Perhaps just prevent \connect without argument if the information is no > longer available. I think it'd be worth actually having psql maintain the information separately from the PGconn... but if nobody feels motivated to go do that, doing at least this much would remove the foot-gun. So +1 for that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Sep 14, 2011 at 10:52:50PM -0500, Robert Haas wrote:
> On Thu, Sep 8, 2011 at 5:09 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> > On tis, 2011-09-06 at 17:12 +0200, hubert depesz lubaczewski wrote:
> >> On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
> >> > It's not just the port, it's all the connection parameters ---
> >> > do_connect relies on the PGconn object to remember those, and in this
> >> > case there no longer is a PGconn object.
> >> >
> >> > We could have psql keep that information separately, but I'm not sure
> >> > it's really worth the trouble.
> >>
> >> well, I think it's definitely worth the trouble. If I had datbaase
> >> standing at 5432, it would connect to it, and then I could mistakenly
> >> ran commands to wrong database.
> >> this is clearly not a good thing.
> >
> > Perhaps just prevent \connect without argument if the information is no
> > longer available.
>
> I think it'd be worth actually having psql maintain the information
> separately from the PGconn... but if nobody feels motivated to go do
> that, doing at least this much would remove the foot-gun. So +1 for
> that.
OK, I have applied the attached, applied patch to do as you suggest.
Here are examples:
!> SELECT * FROM mytable WHERE to_ascii(convert_to(mytext, 'latin1'), 'latin1')
-> = to_ascii(convert_to('nicetry', 'latin1'), 'latin1');
You are currently not connected to a database.
!> \c
All connection parameters must be supplied because no database connection exists
!> \q
$ psql -p 5433 test
psql (9.3devel)
Type "help" for help.
test=> \c
You are now connected to database "test" as user "postgres".
test=> \q
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +