Re: dblink: add polymorphic functions.

On Mon, Jul 6, 2015 at 11:33 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Mon, Jul 6, 2015 at 9:52 AM, Joe Conway <mail(at)joeconway(dot)com> wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On 07/06/2015 07:37 AM, Merlin Moncure wrote:
> >> yup, and at least one case now fails where previously it ran
> >> through: postgres=# select * from dblink('a', 'b', 'c'); ERROR:
> >> function dblink(unknown, unknown, unknown) is not unique
> >
> > Hmm, that is an issue, possibly a fatal one.
> >
> > When Cory first mentioned this to me over a year ago we discussed some
> > other, arguably better and more generic solutions. One was to build
> > support for:
> >
> > SELECT * FROM srf() AS TYPE OF(foo);
> >
> > The second idea I think is actually SQL standard if I recall correctly:
> >
> > SELECT * FROM CAST(srf() AS foo) x;
> >
> > Currently this works:
> >
> > 8<--------------------
> > select *
> > from cast (row(11,'l','{a11,b11,c11}') as foo);
> > f1 | f2 | f3
> > - ----+----+---------------
> > 11 | l | {a11,b11,c11}
> > (1 row)
> > 8<--------------------
> >
> > But this fails:
> >
> > 8<--------------------
> > select *
> > from cast
> > (dblink('dbname=contrib_regression','select * from foo') as foo);
> > ERROR: cannot cast type record to foo
> > 8<--------------------
> >
> > Comments in the source have this to say:
> >
> > 8<--------------------
> > /*
> > * coerce_record_to_complex
> > * Coerce a RECORD to a specific composite type.
> > *
> > * Currently we only support this for inputs that are RowExprs or
> > * whole-row Vars.
> > */
> > 8<--------------------
> >
> > That explains why the first example works while the second does not.
> > I'm not sure how hard it would be to fix that, but it appears that
> > that is where we should focus.
>
> Yeah. FWIW, here's my 0.02$: I use dblink all the time, for all
> kinds of reasons, vastly preferring to have control over the query
> string (vs. FDW type solutions). I have two basic gripes with it. #1
> is that remote queries are not cancelled over all call sites when
> cancelled locally (off-topic for this thread) and #2 is that the SRF
> record describing mechanics are not abstractable because of using
> syntax to describe the record. Corey's proposal, overloading issues
> aside, appears to neatly deal with this problem because anyelement can
> be passed down through a wrapping API.
>
> IOW, I'd like to do:
> CREATE FUNCTION remote_call(...) RETURNS ... AS
> $$
> SELECT dblink(...) AS r(...)
> $$ language sql;
>
> ...which can't be done (discounting dynamic sql acrobatics) because of
> the syntax based expression of the 'r' record. So I like Corey's
> idea...I just think the functions need to be named differently (maybe
> to 'dblink_any', and dblink_get_result_any'?). TBH, to do better
> than that you'd need SQL level support for handling the return type in
> the vein of NEW/OLD. For fun, let's call it 'OUT'...then you could:
>
> SELECT * FROM remote_call(...) RETURNS SETOF foo;
>
> Inside remote_call, you'd see something like:
>
> SELECT dblink(...) AS OUT;
>
> As to the proposed syntax, I would vote to support the SQL standard
> variant if it could be handled during parse. I don't see what AS TYPE
> OF really buys you because FWICT it does not support wrapping.
>
> merlin
>

Your experiences with dblink are very similar to mine.

The whole issue arose for me as an outcropping of my Poor Man's Parallel
Processing extension (still not released but currently working for us in
production internally).

At some point I had to do dblink_get_result(...) as t(...) and not only did
I have to render the structure as a string, I was going to have to execute
that SQL dynamically (because plpgsql lacks a PREPARE statement) or I was
going to have to re-code in C or plv8. Overall those calls aren't terribly
expensive (it's working in production - for us - without this dblink
modification), but a cleaner solution would be better.