What is the quickest query in the database?

Hi P G,

On Thu, 20 Feb 2003 09:52:07 -0800 (PST)
P G <pg_dba@yahoo.com> wrote:

What is the quickest and least intrusive query in the
database that will always succeed?

select 1;

:o)

Regards
Tino

On Thursday 20 Feb 2003 5:52 pm, P G wrote:

What is the quickest and least intrusive query in the
database that will always succeed?

select current_user;

-- OR --

select datname from pg_database where datname =
'some_database';

Or would it be something else?

Probably something like

SELECT 1;

--
Richard Huxton

Might select NULL; be faster, since the number doesn't have to be parsed,
and null is probably a keyword?

Jon

On Thu, 20 Feb 2003, Tino Wildenhain wrote:

Tino Wildenhain wrote:

Hi P G,

On Thu, 20 Feb 2003 09:52:07 -0800 (PST)
P G <pg_dba@yahoo.com> wrote:

What is the quickest and least intrusive query in the
database that will always succeed?

select 1;

Not if you're currently in transaction aborted state.

An empty query is. In psql this can be done with just a semicolon. It
will not affect the current transaction state, it will return
PGRES_EMPTY_QUERY (IIRC) or bail out because of a lost connection. And
it doesn't even need to parse a single keyword.

Jan

:o)

Regards
Tino

select current_user;

-- OR --

select datname from pg_database where datname =
'some_database';

Or would it be something else?

TIA.

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

It's academic. Set timing on in psql and run a bunch of tests. The variation
from test to test is large enough to mask any difference between select 0 or
select '' or select null.

It would, however, be nice to know the reason for this query. I suspect it is
to "ping" the server and making sure it is up by connecting and running a
simple query with a known result. (Many colos have all sorts of setups for
monitoring web servers but fewer for other services - setting up a "test"
page on the web server that makes a request from the app server which, in
turn, does a simple db query, all of which ultimately returns a standard
string, say "system up", to the http request works quite well to set off
alarm bells at the colo or even to allow a load balancer to take action).

But I am surmising. What is the real reason for the query?

Cheers,
Steve

You are correct Steve Crawford. The query is used to
remotely test the connection to the db using JDBC. I
think, though, Jan Wieck provides the most valuable
solution so far. Any more thoughts, anyone?

TIA.

--- Steve Crawford <scrawford@pinpointresearch.com>
wrote:

It's academic. Set timing on in psql and run a bunch
of tests. The variation
from test to test is large enough to mask any
difference between select 0 or
select '' or select null.

It would, however, be nice to know the reason for
this query. I suspect it is
to "ping" the server and making sure it is up by
connecting and running a
simple query with a known result. (Many colos have
all sorts of setups for
monitoring web servers but fewer for other services
- setting up a "test"
page on the web server that makes a request from the
app server which, in
turn, does a simple db query, all of which
ultimately returns a standard
string, say "system up", to the http request works
quite well to set off
alarm bells at the colo or even to allow a load
balancer to take action).

But I am surmising. What is the real reason for the
query?

Cheers,
Steve

On Thursday 20 February 2003 11:52 am, Jonathan
Bartlett wrote:

Might select NULL; be faster, since the number

doesn't have to be parsed,

and null is probably a keyword?

Jon

On Thu, 20 Feb 2003, Tino Wildenhain wrote:

Hi P G,

On Thu, 20 Feb 2003 09:52:07 -0800 (PST)

P G <pg_dba@yahoo.com> wrote:

What is the quickest and least intrusive query

in the

database that will always succeed?

select 1;

:o)

Regards
Tino

select current_user;

-- OR --

select datname from pg_database where datname

=

'some_database';

Or would it be something else?

TIA.

__________________________________________________

Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips,

more

http://taxes.yahoo.com/

---------------------------(end of
broadcast)--------------------------- TIP 3:

if posting/reading through

Usenet, please send an appropriate

subscribe-nomail command to

majordomo@postgresql.org so that your message

can get through to the

mailing list cleanly

---------------------------(end of

broadcast)---------------------------

TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of

broadcast)---------------------------

TIP 3: if posting/reading through Usenet, please

send an appropriate

subscribe-nomail command to

majordomo@postgresql.org so that your

message can get through to the mailing list

cleanly

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

But, unless there is a timeout on that query, wont the system being down
really hang you up, until your script reaches it's default timeout?

We had this problem with PHP and Perl in the past. Is there a way to get
around this in Java?

- Ericson Smith
eric@did-it.com

On Thu, 2003-02-20 at 15:32, P G wrote:

You are correct Steve Crawford. The query is used to
remotely test the connection to the db using JDBC. I
think, though, Jan Wieck provides the most valuable
solution so far. Any more thoughts, anyone?

TIA.

--- Steve Crawford <scrawford@pinpointresearch.com>
wrote:

It's academic. Set timing on in psql and run a bunch
of tests. The variation
from test to test is large enough to mask any
difference between select 0 or
select '' or select null.

It would, however, be nice to know the reason for
this query. I suspect it is
to "ping" the server and making sure it is up by
connecting and running a
simple query with a known result. (Many colos have
all sorts of setups for
monitoring web servers but fewer for other services
- setting up a "test"
page on the web server that makes a request from the
app server which, in
turn, does a simple db query, all of which
ultimately returns a standard
string, say "system up", to the http request works
quite well to set off
alarm bells at the colo or even to allow a load
balancer to take action).

But I am surmising. What is the real reason for the
query?

Cheers,
Steve

On Thursday 20 February 2003 11:52 am, Jonathan
Bartlett wrote:

Might select NULL; be faster, since the number

doesn't have to be parsed,

and null is probably a keyword?

Jon

On Thu, 20 Feb 2003, Tino Wildenhain wrote:

Hi P G,

On Thu, 20 Feb 2003 09:52:07 -0800 (PST)

P G <pg_dba@yahoo.com> wrote:

What is the quickest and least intrusive query

in the

database that will always succeed?

select 1;

:o)

Regards
Tino

select current_user;

-- OR --

select datname from pg_database where datname

=

'some_database';

Or would it be something else?

TIA.

__________________________________________________

Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips,

more

http://taxes.yahoo.com/

---------------------------(end of
broadcast)--------------------------- TIP 3:

if posting/reading through

Usenet, please send an appropriate

subscribe-nomail command to

majordomo@postgresql.org so that your message

can get through to the

mailing list cleanly

---------------------------(end of

broadcast)---------------------------

TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of

broadcast)---------------------------

TIP 3: if posting/reading through Usenet, please

send an appropriate

subscribe-nomail command to

majordomo@postgresql.org so that your

message can get through to the mailing list

cleanly

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Ericson Smith <eric@did-it.com>

It sounds like the caller is simply trying to see if the engine is up
and operational.
If that is the case, then just connect, disconnect.

Jonathan Bartlett wrote:

That question sounds like one more of design or
architecture. Timing out the connection is one
solution. Another immediate idea comes to mind where
you can use a seperate thread or fork another process
allowing the rest of the application to continue if
needed.

Thanks.

--- Ericson Smith <eric@did-it.com> wrote:

But, unless there is a timeout on that query, wont
the system being down
really hang you up, until your script reaches it's
default timeout?

We had this problem with PHP and Perl in the past.
Is there a way to get
around this in Java?

- Ericson Smith
eric@did-it.com

On Thu, 2003-02-20 at 15:32, P G wrote:

You are correct Steve Crawford. The query is used

to

remotely test the connection to the db using JDBC.

I

think, though, Jan Wieck provides the most

valuable

solution so far. Any more thoughts, anyone?

TIA.

--- Steve Crawford

<scrawford@pinpointresearch.com>

wrote:

It's academic. Set timing on in psql and run a

bunch

of tests. The variation
from test to test is large enough to mask any
difference between select 0 or
select '' or select null.

It would, however, be nice to know the reason

for

this query. I suspect it is
to "ping" the server and making sure it is up by
connecting and running a
simple query with a known result. (Many colos

have

all sorts of setups for
monitoring web servers but fewer for other

services

- setting up a "test"
page on the web server that makes a request from

the

app server which, in
turn, does a simple db query, all of which
ultimately returns a standard
string, say "system up", to the http request

works

quite well to set off
alarm bells at the colo or even to allow a load
balancer to take action).

But I am surmising. What is the real reason for

the

query?

Cheers,
Steve

On Thursday 20 February 2003 11:52 am, Jonathan
Bartlett wrote:

Might select NULL; be faster, since the number

doesn't have to be parsed,

and null is probably a keyword?

Jon

On Thu, 20 Feb 2003, Tino Wildenhain wrote:

Hi P G,

On Thu, 20 Feb 2003 09:52:07 -0800 (PST)

P G <pg_dba@yahoo.com> wrote:

What is the quickest and least intrusive

query

in the

database that will always succeed?

select 1;

:o)

Regards
Tino

select current_user;

-- OR --

select datname from pg_database where

datname

=

'some_database';

Or would it be something else?

TIA.

__________________________________________________

Do you Yahoo!?
Yahoo! Tax Center - forms, calculators,

tips,

more

http://taxes.yahoo.com/

---------------------------(end of
broadcast)--------------------------- TIP

3:

if posting/reading through

Usenet, please send an appropriate

subscribe-nomail command to

majordomo@postgresql.org so that your

message

can get through to the

mailing list cleanly

---------------------------(end of

broadcast)---------------------------

TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of

broadcast)---------------------------

TIP 3: if posting/reading through Usenet,

please

send an appropriate

subscribe-nomail command to

majordomo@postgresql.org so that your

message can get through to the mailing list

cleanly

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

---------------------------(end of

broadcast)---------------------------

TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Ericson Smith <eric@did-it.com>

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/