Subject Re: External procedures: implementation proposal.
Author Roman Rokytskyy
> I still don't like it, sorry. But I don't think it stops our ESP
> discussion :-)

Ok, for now we have two "aye"s and one against. :)

> This is for Java. For binary compiled modules RDB$EXTERNAL_NAME
> should contain both the library name and the entrypoint. Should we
> split them in system tables a-la RDB$FUNCTIONS or the external
> engine module should take care about name decoding and library
> loading?

As I already posted, this specification might be inconvenient for the
use cases similar to UDFs as it assumes there is a "module" somewhere
there that resolves the external name into parts usable for calling.
Java has also two-component name - class name and method name. And the
convention we used is not the official one, but is human readable.

So, the question is whether we define a new module for "C" language
that would resolve the external name "ib_util:some_procedure" on
Windows into "ib_util.dll" and "some_procedure" entry point?
Personally I'm ok with this scheme, but I have little experience with
other systems. The idea was just that external name is something
module groks, how this happens is irrelevant.

> Given the questions above, perhaps we should start distinguishing
> between external engine modules and ESP modules. If we talk about
> the former ones here, then I also don't have any strong preference.
> But I don't like to see something residing in the memory even if
> nobody uses it. But the auto-loading question could be also solved
> via the config options, so I don't think this is a big issue.

Let's wait for the module initialization and callback mechanism
discussion, as belongs there, not here.

> Perhaps you're right. I just don't want to see server crashes when
> it could be avoided.

Then don't let it crash. I do not think that C++ is not capable of
handling something like "NullPointerException" (in that case it would
be access violation) generated in the SP/UDF.

> The engine calls attachThread() before calling ESP. So we have a
> list of threads allowed to do this. The
> code could return NULL handles if it's called from threads outside
> this list.

Yes, this is possible.

> If the child thread is spawned at the beginning of ESP and
> terminated at the end, what bad happens? And please also don't
> forget about other languages. People have created a lot of
> multi-threaded UDFs for their needs, so they expect to do the same
> in ESPs. With C++ or Delphi, you have abilities to
> keep control over all resources you have.

You have control over the resources you created, but you do not have
control about your own lifetime. That's the problem. If you want to
support threads spawned from SPs, we need at least a lot of events
that describe what is happening in the engine and in the module right
now to notify the spawned thread that module is shut down, that engine
is shut down, etc. to let it clean the resources first. And we have
also to guarantee that no call from the spawned thread comes into
engine in some different context (probably your idea with NULL db and
tr handles is enough - spawned thread would need to create its own
context). Personally I do not trust myself to design such solution.

If people really need to start some thread that is out of server's
control, why cannot they just extend the module and do it in the
module initialization part?

> > - If OUT parameters are defined and RS contains more than one row
> > - error.
> This one behaves differently from the current engine, but I agree
> here too.

Personally I think this is logical. We might keep current behavior for
PSQL, but I see no reason to do it for the rest of languages.

I had a short discussion with Paul Ruizendaal and he asked why didn't
we specify external procedures that return simultaneously OUT params
and result set(s). I suspect this is a feature of the procedural
languages from Oracle, DB2 and SQL Server. Is it necessary?

What is your opinion here?