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.


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Tom Lane-2]

"Phil Cairns" <phil@...> 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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote:
> "Phil Cairns" <phil@...> 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.


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Scott Marlowe-2]

On Fri, Oct 30, 2009 at 2:07 PM, Phil Cairns <phil@...> 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.

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Scott Marlowe-2]

On Fri, Oct 30, 2009 at 3:57 PM, Phil Cairns <phil@...> 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?

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Scott Marlowe-2]

Oh, nevermind, read the thread again.

On Fri, Oct 30, 2009 at 5:40 PM, Scott Marlowe <scott.marlowe@...> wrote:

> On Fri, Oct 30, 2009 at 3:57 PM, Phil Cairns <phil@...> 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.

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[David Fetter]

On Sat, Oct 31, 2009 at 08:57:03AM +1100, Phil Cairns wrote:

> Tom Lane wrote:
> > "Phil Cairns" <phil@...> 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@...> http://fetter.org/
Phone: [phone]  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@...
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Vick Khera]

On Sat, Oct 31, 2009 at 1:06 AM, David Fetter <david@...> 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.

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Raymond O'Donnell]

On 02/11/2009 19:47, Vick Khera wrote:

> On Sat, Oct 31, 2009 at 1:06 AM, David Fetter <david@...> 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@...

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general