Обсуждение: 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