Re: Creating a read/write virtual table?

On 12/04/10 11:34, Ben Martin wrote:

> In my SQLite vtable both of the above restrictions don't apply. To
> mitigate (1) I was hopeful that there was a way to get the connection to
> PG on the server to fork/setuid

The pg backends are designed to run as one user. You might have issues
setting it up to do otherwise.

Consider talking to your storage backend over a unix socket, pipe,
tcp/ip socket, shared memory, or other suitable IPC mechanism from Pg.
This also protects Pg from any issues caused by the storage backend
doing things like spawning unexpected threads, corrupting the
stack/heap, outright crashing, etc.

> I think using a function restricts the results to being read only.

If you're planning on writing a C-level UDF loaded via 'CREATE
FUNCTION', then yes it'll have to return a materialized data set and
there's no way to UPDATE/INSERT/DELETE against the function's results.

> Perhaps having a second function that allows updates to be processed
> separately.

Yep, though that's clumsy I expect it'd work, and honestly it's probably
your best bet.

> Any pointers / suggestions on how to do this sort of thing with
> postgresql would be wonderful. Please CC any such replies as I'm not
> currently on list.

AFAIK Pg's innards aren't currently very friendly toward extension with
random user-provided table/database storage backends. Unlike
(say) MySQL, there's no pluggable storage engine system.

That means Pg's planner/executor can make a lot of decisions based on
things it "knows" about how Pg's data storage works, speeding and
simplifying it and letting it be much smarter. Pg can enforce strict
rules about transactional scope, ACID, error handling, etc based on
knowledge of the data storage's capabilities and expectations. On the
other hand, it means it's hard to plug in your own storage system.

After all, how would your external system handle something like this:

BEGIN;
INSERT INTO my_external_storage(a, b) VALUES (1,2);
SAVEPOINT sp_a;
INSERT INTO my_external_storage(a, b) VALUES (3,4);
ROLLBACK TO SAVEPOINT sp_a;
INSERT INTO my_external_storage(a, b) VALUES (5,6);
COMMIT;

How would it honour SERIALIZABLE isolation if it doesn't its self
maintain MVCC snapshots and have some way of mapping pg's xids to its
internal versions? Etc.

Pg would have to be able to error out on many commands with "not
supported by underlying storage system" or the like. Yet sometimes it
won't even know that until it's well into executing something.

SQLite has the advantage of simplicity. It doesn't have to worry about
complicated concurrency rules, versioning, user-defined data type
storage, etc etc etc. And MySQL was designed to handle pluggable
backends, so it's prepared for storage backends not to be able to
perform certain operations, and it makes some big sacrifices in terms of
its broader capabilities to permit pluggable backends.

I guess at this point I have to ask "why do you want to do this? What
will you gain?"

--
Craig Ringer