From: Joe Conway <joe@conway-family.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Re: [INTERFACES] remote database queries
Date: Mon, 28 May 2001 19:55:37 -0700
Message-ID: <002901c0e7ea$d7ac9830$0705a8c0@jecw2k1> (raw)
References: <Pine.LNX.4.30.0105271914200.14985-100000@commandprompt.com>
<002901c0e733$5e683590$0705a8c0@jecw2k1>
<15650.991084313@sss.pgh.pa.us>
> [ Redirected away from the entirely inappropriate pgsql-interfaces list ]
oops . . . sorry!
> Until we fix that (maybe for 7.2, maybe not) your existing hack is
> probably pretty reasonable. You could save some cycles by avoiding
> conversion to text, though --- instead return an opaque datum that is
> pointer-to-tuple-slot and let the dblink_tok function extract fields
> from the tuple. Look at SQL function support and the FieldSelect
> expression node type for inspiration.
Thanks -- I'll take a look.
> I think this would work if the planner weren't so enthusiastic about
> trying to collapse the sub-SELECT query together with the main query.
> Unfortunately it doesn't check to see if any set-valued functions are
> involved before it collapses 'em --- leaving you with a set-valued
> function call in the WHERE clause. Not sure if this is worth fixing,
> considering that using set-valued functions in this way is just a
> band-aid that doesn't have a long life expectancy.
I'd certainly bow to your wisdom in this area, but I was thinking it would
be useful (at times) to force a FROM clause sub-select to be treated as if
it were a "real" table (probably not the best way to express this, but
hopefully you get the idea). In MSSQL I've found many situations where
putting intermediate results into a temp table, and then joining to it, is
significantly faster than letting the optimizer do it's best. But the fact
that MSSQL will return record sets from a stored procedure help makes this
tolerable/manageable -- i.e. the whole ugly mess can be rolled into one nice
neat strored procedure call. If the FROM clause sub-select could be treated,
in a sense, like an on-the-fly temp table in PostgreSQL, a similar result is
possible. And if the whole ugly mess is rolled up behind a view, no one has
to know except the especially curious ;-)
>
> If you just need a hack with a short life expectancy, here's a hack that
> I recommend not reading right before dinner ... might make you lose your
> appetite ;-). Build the view as a dummy UNION:
*Very* few things make me lose my appetite -- and this worked perfectly!
Thank you.
-- Joe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: joe@conway-family.com, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
Subject: Re: Re: [INTERFACES] remote database queries
In-Reply-To: <002901c0e7ea$d7ac9830$0705a8c0@jecw2k1>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox