Обсуждение: BUG #15168: "pg_isready -d" effectively ignores given database name
The following bug has been logged on the website:
Bug reference: 15168
Logged by: Jake
Email address: jakelist@zoho.com
PostgreSQL version: 9.5.10
Operating system: Fedora 25
Description:
* Using Bash
* Tried to update as recommended. 9.6.12 does not appear to be available for
Fedora 25.
$ cd /run/media/jthomas/BackupOne/development/snowdrift
$ export PGDATA="$PWD"/.postgres-work
$ export PGHOST="$PGDATA"
$ export PGDATABASE="snowdrift"
$ pg_ctl initdb
$ pg_ctl start -w -o "-F -h '' -k "$PGHOST"" -l "$PGDATA"/log
# As you would expect; "pg_ctl initdb" created database "postgres":
$ pg_isready -d postgres
/run/media/jthomas/BackupOne/development/snowdrift/.postgres-work:5432 -
accepting connections
# Not expected, database "jibberish" does not exist:
$ pg_isready -d jibberish
/run/media/jthomas/BackupOne/development/snowdrift/.postgres-work:5432 -
accepting connections
# Just to confirm:
$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+---------+----------+-------------+-------------+---------------------
postgres | jthomas | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | jthomas | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/jthomas
+
| | | | |
jthomas=CTc/jthomas
template1 | jthomas | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/jthomas
+
| | | | |
jthomas=CTc/jthomas
(3 rows)
# No database "jibberish"
Note: https://www.postgresql.org/docs/10/static/bug-reporting.html , 5.3
portrays the mailing list as the preferred method, but then the pgsql-bugs
page
asks that the bug reporting form be used instead.
Re: BUG #15168: "pg_isready -d" effectively ignores given database name
От
"David G. Johnston"
Дата:
On Monday, April 23, 2018, PG Bug reporting form <noreply@postgresql.org> wrote:
# Not expected, database "jibberish" does not exist:
$ pg_isready -d jibberish
/run/media/jthomas/BackupOne/development/snowdrift/. postgres-work:5432 -
accepting connections
The notes section of the docs for the command cover this behavior. It is not a bug, the utility answers whether the server will accept connections generally, not that a specific connection string and credentials are valid.
David J.
Ah-ha. Thank you for pointing that out. I did not notice.
In case you're interested, I used a StackOverflow solution to programmatically determine whether a database exists, after first ensuring that the cluster has been initialized and that the server is running.
Thank you for your rapid response,
Jake
---- On Mon, 23 Apr 2018 21:29:11 -0700 David G. Johnston <david.g.johnston@gmail.com> wrote ----
On Monday, April 23, 2018, PG Bug reporting form <noreply@postgresql.org> wrote:# Not expected, database "jibberish" does not exist:$ pg_isready -d jibberish/run/media/jthomas/BackupOne/development/snowdrift/.postgres-work:5432 -accepting connectionsThe notes section of the docs for the command cover this behavior. It is not a bug, the utility answers whether the server will accept connections generally, not that a specific connection string and credentials are valid.David J.
After discussing this with the Lead Developer of Snowdrift.coop, I'd like to humbly suggest that the specification be slightly adjusted to remove the surprising aspect.
Usually, an exit status of zero means that all went well. The aforementioned behavior is, by Postgres' own admittance, not only an error, but a fatal error, indicated in the log:
FATAL: database "jibberish" does not exist
And yet, pg_isready exits with a zero, contradicting the usual meaning. Again, it is following spec perfectly; I only suggest a change to the spec.
Specifically, I suggest two small adjustments. One, an exit status of 4 would mean that a connection to the server was made, but the specified database does not exist. In the future, this may be generalized to mean that a connection was made, but some parameter value did not make sense with the cluster's current state. Though right now, the only possible such friction I see is that the specified database does not exist. And for the second adjustment: an exit status of 0 would mean "complete success": a connection was made and the parameters made sense.
Note the exit statuses of 1, 2 and 3 would keep their old meanings. In order to get an exit status of 4, the arguments must be syntactically valid. Otherwise, 3 is returned.
Jake T.
---- On Tue, 24 Apr 2018 13:01:46 -0700 jake <jakelist@zoho.com> wrote ----
Ah-ha. Thank you for pointing that out. I did not notice.In case you're interested, I used a StackOverflow solution to programmatically determine whether a database exists, after first ensuring that the cluster has been initialized and that the server is running.Thank you for your rapid response,Jake---- On Mon, 23 Apr 2018 21:29:11 -0700 David G. Johnston <david.g.johnston@gmail.com> wrote ----On Monday, April 23, 2018, PG Bug reporting form <noreply@postgresql.org> wrote:# Not expected, database "jibberish" does not exist:$ pg_isready -d jibberish/run/media/jthomas/BackupOne/development/snowdrift/.postgres-work:5432 -accepting connectionsThe notes section of the docs for the command cover this behavior. It is not a bug, the utility answers whether the server will accept connections generally, not that a specific connection string and credentials are valid.David J.
Re: BUG #15168: "pg_isready -d" effectively ignores given database name
От
"David G. Johnston"
Дата:
After discussing this with the Lead Developer of Snowdrift.coop, I'd like to humbly suggest that the specification be slightly adjusted to remove the surprising aspect.
I'd argue that you would spell that:
psql -c 'SELECT 1;'
or (with maybe a more useful select-list, like version, to reinforce you are where you think you are), with whatever options and environment you wish tacked onto it.
Turning something that today that conforms to the spec and results in success into a failure is generally undesirable.
I'd be more inclined to modify pg_isready to simply ignore any user/password/database arguments in the environment, and remove them from the command line spec (or document them as being ignored since they are accepted today), if that is possible. Then the API and its charter would match AND you'd avoid the spurious FATAL in the log.
For the spec you describe a new utility command would likely be a better solution.
David J.
Well, after fleshing out your command, I got:
psql -tc "select 1 from pg_catalog.pg_database where datname='snowdrift';" | grep -qw 1
That looks more complicated than the StackOverflow solution:
psql -lt | cut -f1 -d \| | grep -qw <db-name>
Or at least it's longer. Hmm. I guess personal taste is a big factor here.
Jake T.
Re: BUG #15168: "pg_isready -d" effectively ignores given database name
От
"David G. Johnston"
Дата:
On Tuesday, April 24, 2018, jake <jakelist@zoho.com> wrote:
Well, after fleshing out your command, I got:psql -tc "select 1 from pg_catalog.pg_database where datname='snowdrift';" | grep -qw 1That looks more complicated than the StackOverflow solution:psql -lt | cut -f1 -d \| | grep -qw <db-name>Or at least it's longer. Hmm. I guess personal taste is a big factor here.Jake T.
If your psql connection is logging you onto the snowdrift database checking for its existence explicitly seems pointless...otherwise, yes, using the -l option (which connects you, generally, to the default postgres database) in psql is going to be the better way to check for database presence since -l basically invokes the pg_database query for you.
David J.
Thank you again. You're right that checking for database x's existence, while logged into database x, is pointless. I didn't mention that for that command I'd be using the default postgres database.
But again, you're right about what's simple/better and what's not.
In fact, just today, I discovered that there may be an even simpler solution for our scenario: have Yesod deal with postgres altogether -- no script from us. But that's outside the scope of this thread.
Back to scope: I simply vote that the '-d' option of pg_isready be deprecated in favor of a dedicated db_exists utility, rather than change existing spec, for the reasons you gave.
Jake T.