Rewriting select statements
I want to have the server do this:
If the query has no where clause, use a where clause of "where 1=0".
Is this possible?
Why would I want to do this? Because a third party library (ArcGIS) has a
"feature" such that when a relation name is registered with it, it does a
"select * from <relation>" and then does nothing with the results. It looks
like it's just checking the field names or the fact that the relation
exists. In this case, it's selecting a view that joins 14 tables. The main
table has 350,000 rows, and some of the subsidiary tables have up to 1.5
million rows. This view is not queried without a where clause during the
normal execution of the program, so to save between 6 and 20 minutes of
startup time depending on server load, I'd like to be able to add the where
clause.
Thanks for any help,
Phil Cairns.
"Phil Cairns" <phil@pagaros.com.au> writes:
I want to have the server do this:
If the query has no where clause, use a where clause of "where 1=0".
Is this possible?
It's doubtless *possible*, but if you're asking for it to actually
happen in any supported version of Postgres, the answer is no way.
It's directly contrary to the SQL standard.
Why would I want to do this? Because a third party library (ArcGIS) has a
"feature" such that when a relation name is registered with it, it does a
"select * from <relation>" and then does nothing with the results.
Tell the library authors to fix their broken code. This is blithering
stupidity in *any* SQL database, not only Postgres.
regards, tom lane
Tom Lane wrote:
"Phil Cairns" <phil@pagaros.com.au> writes:
I want to have the server do this:
If the query has no where clause, use a where clause of "where 1=0".Is this possible?
It's doubtless *possible*, but if you're asking for it to actually
happen in any supported version of Postgres, the answer is no way.
It's directly contrary to the SQL standard.
Yeah. Not asking for a feature to be added, just asking if something already
existed, some sort of rule that could be created that would rewrite the
query.
Why would I want to do this? Because a third party library (ArcGIS) has
a
"feature" such that when a relation name is registered with it, it does
a
"select * from <relation>" and then does nothing with the results.
Tell the library authors to fix their broken code. This is blithering
stupidity in *any* SQL database, not only Postgres.
Tried that, and got no response. I fully agree that it's broken, but my
users just see the delay. I might have to try some sort of proxy
arrangement.
Thanks for your response,
Phil.
On Fri, Oct 30, 2009 at 2:07 PM, Phil Cairns <phil@pagaros.com.au> wrote:
I want to have the server do this:
If the query has no where clause, use a where clause of "where 1=0".
Is this possible?
Anyway to add a 'limit 1' to the end of the query somewhere on the app
side? Like Tom said, this app sounds retartded.
On Fri, Oct 30, 2009 at 3:57 PM, Phil Cairns <phil@pagaros.com.au> wrote:
Tom Lane wrote:
Tell the library authors to fix their broken code. This is blithering
stupidity in *any* SQL database, not only Postgres.Tried that, and got no response. I fully agree that it's broken, but my
users just see the delay. I might have to try some sort of proxy
arrangement.
BTW, any chance of finding out what this company is so I can avoid
them in the future?
Oh, nevermind, read the thread again.
On Fri, Oct 30, 2009 at 5:40 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Fri, Oct 30, 2009 at 3:57 PM, Phil Cairns <phil@pagaros.com.au> wrote:
Tom Lane wrote:
Tell the library authors to fix their broken code. This is blithering
stupidity in *any* SQL database, not only Postgres.Tried that, and got no response. I fully agree that it's broken, but my
users just see the delay. I might have to try some sort of proxy
arrangement.BTW, any chance of finding out what this company is so I can avoid
them in the future?
--
When fascism comes to America, it will be intolerance sold as diversity.
On Sat, Oct 31, 2009 at 08:57:03AM +1100, Phil Cairns wrote:
Tom Lane wrote:
"Phil Cairns" <phil@pagaros.com.au> writes:
I want to have the server do this: If the query has no where
clause, use a where clause of "where 1=0".Is this possible?
It's doubtless *possible*, but if you're asking for it to actually
happen in any supported version of Postgres, the answer is no way.
It's directly contrary to the SQL standard.Yeah. Not asking for a feature to be added, just asking if something
already existed, some sort of rule that could be created that would
rewrite the query.Why would I want to do this? Because a third party library
(ArcGIS) has a "feature" such that when a relation name is
registered with it, it does a "select * from <relation>" and
then does nothing with the results.Tell the library authors to fix their broken code. This is
blithering stupidity in *any* SQL database, not only Postgres.Tried that, and got no response.
Name and shame the people who failed to respond. Heck, you can do it
right here. Included their contact info, curriculum vit� of everyone
you can find who's involved with this horrific bug, etc., etc. You
can start here :)
http://www.esri.com/company/contact.html
Make sure *everybody* knows that the bug is in the ArcGIS software.
Make sure they let ESRI know loudly, publicly and frequently that the
problem is on ESRI's end.
I fully agree that it's broken, but my users just see the delay. I
might have to try some sort of proxy arrangement.
This is not your problem. Treating it as though it were your problem
will only lead to enormous pain to you, and will not actually solve
the problem.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sat, Oct 31, 2009 at 1:06 AM, David Fetter <david@fetter.org> wrote:
I fully agree that it's broken, but my users just see the delay. I
might have to try some sort of proxy arrangement.This is not your problem. Treating it as though it were your problem
will only lead to enormous pain to you, and will not actually solve
the problem.
Funny, this is exactly how the bulk of the computing world deals with
brokenness in the PoS that Microsoft calls a web browser... You'd
think that fixing it to work the right way would be better than having
a bazillion people work around it one-by-one on their own web sites.
On 02/11/2009 19:47, Vick Khera wrote:
On Sat, Oct 31, 2009 at 1:06 AM, David Fetter <david@fetter.org> wrote:
I fully agree that it's broken, but my users just see the delay. I
might have to try some sort of proxy arrangement.This is not your problem. Treating it as though it were your problem
will only lead to enormous pain to you, and will not actually solve
the problem.Funny, this is exactly how the bulk of the computing world deals with
brokenness in the PoS that Microsoft calls a web browser... You'd
think that fixing it to work the right way would be better than having
a bazillion people work around it one-by-one on their own web sites.
Indeed, and they did fix it - Firefox!
:-)
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie