Re: dblink() from GridSQL

> > > Hi List,
> > > We have a customer who is trying to migrate a few PostgresPlus instances
> > to
> > > GridSQL clusters. They have a process that pulls data from another
> > server
> > > using dblink every night, and we're trying to replicate that on the
> > GridSQL
> > > instance, but grid is being a bit of a pain.
> > > Grid doesn't seem to allow functions in from statements, and, of course,
> > it
> > > spits out errors about functions returning records being called in the
> > wrong
> > > context if we just try "select dblink(foo, bar);" (we had to try it).
> > > Has anyone else run into this specific issue?
> >
>
> GridSQL itself doesn't support functions.
>
>
GridSQL supports most PostgreSQL built-in functions. In terms of other
user-defined functions, one can create them directly on the data nodes, then
teach the coordinator about them by putting the function signature in the
GridSQL config file, and restarting the Coordinator. (This will not solve
the functions in FROM clause issue of course.)

I suspect there will be issues with dblink, however.

I would not recommend using dblink for this anyway. Are there only inserts
going on here, or updates and deletes, too? If only adding data, I would
just dump out data periodically and then load via COPY to the GridSQL
coordinator, as Scott mentions. It will run much, much faster.

If you really need dblink, and you are clear about the
distribution/partitioning, you could run this directly from the underlying
nodes, but you can really mess things up if you pull in data to the wrong
node. I would avoid this.

Another workaround would be to create a dummy database in PostgreSQL, access
the source data via dblink, then insert it into GridSQL.

Mason

>
> > Is there a known workaround?
> > > Any ideas on what else we should try?
> >
> >
> You'd have to present the data to be partitioned to the gsql controller for
> partitioning to happen properly, or use the high-speed import that it comes
> with. Could you dump the data to an intermediary csv and then push it at
> the import utility?
>
>

>
> --Scott
>
>
> > have you considered wrapping the output of the dblink query in a view?
> >
> > merlin
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
>