
> Is anyone looking at doing this?  Is this purely a MySQL-ism, or is it
> something that everyone else has except us?

We should not only support access to all db's under one postmaster,
but also remote access to other postmaster's databases.
All biggie db's allow this in one way or another (synonyms, 
qualified object names) including 2-phase commit.
Ideally this includes access to other db manufacturers, flat files, bdb ...
Meaning, that this is a problem needing a generic approach.

Andreas

> > > Is there any possibility to get a port for MySQL with BerkleyDB support?
> > > I realy need the transaction support and I'd like to build MySQL from a
> > > port.
> >
> > why not just build PgSQL, and have transaction support *with* subselects
> > and everything else that mySQL doesn't have?
> 
> I'd *love* to use PgSQL, but it doesn't support cross-DB joins (or at
> least I couldn't figure out how to do it.)  MySQL handles this, so
> I'm using MySQL and would also like to have transaction support...

Mon, 22 Jan 2001 17:01:13 +0100
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
AW: Re: MySQL and BerkleyDB (fwd)

sounds like something that should be handled at the application level
though ... at least the concept of 'access to other db manufacturers' ...
no?


On Mon, 22 Jan 2001, Zeugswetter Andreas SB wrote:

>
> > Is anyone looking at doing this?  Is this purely a MySQL-ism, or is it
> > something that everyone else has except us?
>
> We should not only support access to all db's under one postmaster,
> but also remote access to other postmaster's databases.
> All biggie db's allow this in one way or another (synonyms,
> qualified object names) including 2-phase commit.
> Ideally this includes access to other db manufacturers, flat files, bdb ...
> Meaning, that this is a problem needing a generic approach.
>
> Andreas
>
> > > > Is there any possibility to get a port for MySQL with BerkleyDB support?
> > > > I realy need the transaction support and I'd like to build MySQL from a
> > > > port.
> > >
> > > why not just build PgSQL, and have transaction support *with* subselects
> > > and everything else that mySQL doesn't have?
> >
> > I'd *love* to use PgSQL, but it doesn't support cross-DB joins (or at
> > least I couldn't figure out how to do it.)  MySQL handles this, so
> > I'm using MySQL and would also like to have transaction support...
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


Mon, 22 Jan 2001 12:31:19 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: AW: Re: MySQL and BerkleyDB (fwd)
On Mon, 22 Jan 2001, Zeugswetter Andreas SB wrote:

> 
> > Is anyone looking at doing this?  Is this purely a MySQL-ism, or is it
> > something that everyone else has except us?
> 
> We should not only support access to all db's under one postmaster,
> but also remote access to other postmaster's databases.
> All biggie db's allow this in one way or another (synonyms, 
> qualified object names) including 2-phase commit.
> Ideally this includes access to other db manufacturers, flat files, bdb ...
> Meaning, that this is a problem needing a generic approach.

Of course, a generic, powerful approach would be great.

However, a simple, limited approach would a be solution for (I
suspect) 97% of the cases, which is that one software package creates a
database to store mailing list names, and another creates a database to
store web permissions, and you want to write a query that encompasses
both, w/o semi-tedious COPY TO FILEs to temporarily move a table back and
forth. And of course, a simple solution might be completed faster :-)

How could this be handled? 

* a syntax for db-table names, such as mydb.myfield or something like
that. (do we have any unused punctuation? :-) )

* aliases, so that tblFoo in dbA can be called as ToFoo in dbB

* other ways?

The second might be easier from a conversion view: the user wouldn't have
to understand that this was a 'link', but it might prove complicated when
there are many links to keep track of, etc.


-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington


Mon, 22 Jan 2001 12:18:54 -0500 (EST)
Joel Burton <jburton@scw.org>
Re: AW: Re: MySQL and BerkleyDB (fwd)
On Mon, Jan 22, 2001 at 12:18:54PM -0500, Joel Burton wrote:
> On Mon, 22 Jan 2001, Zeugswetter Andreas SB wrote:
> 
> > 
> > > Is anyone looking at doing this?  Is this purely a MySQL-ism, or is it
> > > something that everyone else has except us?
> > 
> > We should not only support access to all db's under one postmaster,
> > but also remote access to other postmaster's databases.
> > All biggie db's allow this in one way or another (synonyms, 
> > qualified object names) including 2-phase commit.
> > Ideally this includes access to other db manufacturers, flat files, bdb ...
> > Meaning, that this is a problem needing a generic approach.
> 
> Of course, a generic, powerful approach would be great.
> 
> However, a simple, limited approach would a be solution for (I
> suspect) 97% of the cases, which is that one software package creates a
> database to store mailing list names, and another creates a database to
> store web permissions, and you want to write a query that encompasses
> both, w/o semi-tedious COPY TO FILEs to temporarily move a table back and
> forth. And of course, a simple solution might be completed faster :-)
> 
> How could this be handled? 
> 

And this case can be handled within one database by having multiple
schema, one for each package. It's not there yet, but it's a simpler
solution than the generic solution. The problem (as others have mentioned)
is that we don't want to open the door to remote access until we have a
two-phase transaction commit mechanism in place. Doing it any other way
is not a 'partial solution', it's a corrupt database waiting to happen.


> * a syntax for db-table names, such as mydb.myfield or something like
> that. (do we have any unused punctuation? :-) )

This is the sort of syntax that SQL9* specify for cross schema access.
So far, it fits into the parser just fine.

> * aliases, so that tblFoo in dbA can be called as ToFoo in dbB

This can be done with views, once schema are in place.

Ross
-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.

Mon, 22 Jan 2001 11:32:31 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Re: AW: Re: MySQL and BerkleyDB (fwd)
On Mon, 22 Jan 2001, Ross J. Reedstrom wrote:

> And this case can be handled within one database by having multiple
> schema, one for each package. It's not there yet, but it's a simpler
> solution than the generic solution. The problem (as others have mentioned)
> is that we don't want to open the door to remote access until we have a
> two-phase transaction commit mechanism in place. Doing it any other way
> is not a 'partial solution', it's a corrupt database waiting to happen.

What does '2-phase transaction commit mechanism' mean in this case?

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington


Mon, 22 Jan 2001 12:41:38 -0500 (EST)
Joel Burton <jburton@scw.org>
Re: Re: AW: Re: MySQL and BerkleyDB (fwd)
On Mon, Jan 22, 2001 at 12:41:38PM -0500, Joel Burton wrote:
> On Mon, 22 Jan 2001, Ross J. Reedstrom wrote:
> 
> > And this case can be handled within one database by having multiple
> > schema, one for each package. It's not there yet, but it's a simpler
> > solution than the generic solution. The problem (as others have mentioned)
> > is that we don't want to open the door to remote access until we have a
> > two-phase transaction commit mechanism in place. Doing it any other way
> > is not a 'partial solution', it's a corrupt database waiting to happen.
> 
> What does '2-phase transaction commit mechanism' mean in this case?

Same thing it means elsewhere. Typing "two phase commit" into Google gets me
this url:

http://webopedia.internet.com/Computer_Science/Transaction_Processing/two_phase_commit.html

Which says:

   A feature of transaction processing systems that enables databases
   to be returned to the pre-transaction state if some error condition
   occurs. A single transaction can update many different databases. The
   two-phase commit strategy is designed to ensure that either all the
   databases are updated or none of them, so that the databases remain
   synchronized.

   Database changes required by a transaction are initially stored
   temporarily by each database. The transaction monitor then
   issues a "pre-commit" command to each database which requires an
   acknowledgment. If the monitor receives the appropriate response from
   each database, the monitor issues the "commit" command, which causes
   all databases to simultaneously make the transaction changes permanent.


This 'pre-commit' 'really commit' two-step (get 'yer cowboy hats, right
here) is what's needed, and is currently missing from pgsql. 


Ross

Mon, 22 Jan 2001 11:55:36 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Re: AW: Re: MySQL and BerkleyDB (fwd)
The Hermit Hacker wrote:
> 
> sounds like something that should be handled at the application level
> though ... at least the concept of 'access to other db manufacturers' ...
> no?

If and when we will get functions that can return rowsets (IIRC Oracle's 
RETURN AND CONTINUE)the simplest case can be easily implemented by
having 
a user-defined method that just does the query for the whole table (or 
for rows where "field in (x,y,z)"

Then putting this in a view and then using it as a table should also 
be quite simple (or at least possible ;).

Only after that should we start optimizing ...

--------------
Hannu

Tue, 23 Jan 2001 00:10:19 +0200
Hannu Krosing <hannu@tm.ee>
Re: AW: Re: MySQL and BerkleyDB (fwd)
> This 'pre-commit' 'really commit' two-step (get 'yer cowboy hats, right
> here) is what's needed, and is currently missing from pgsql. 

  Hello,

  I'm very interested in this topic since I am involved in a
distributed, several-PostgreSQLs-backed, open-source,
buzzword-compliant database replication middleware (still in the draft
stage though --- this is not an announcement :-).
  I had thought that the pre-commit information could be stored in an
auxiliary table by the middleware program ; we would then have
to re-implement some sort of higher-level WAL (I thought of the list
of the commands performed in the current transaction, with a sequence
number for each of them that would guarantee correct ordering between
concurrent transactions in case of a REDO). But I fear I am missing
a number of important issues there ; so could you please comment on my
idea ? 
  * what should I try not to forget to record in the higher-level WAL
  if I want consistency ?
  * how could one collect consistent ordering information without
  impacting performance too much ? Will ordering suffice to guarantee
  correctness of the REDO ? (I mean, are there sources of
  nondeterminism in PostgreSQL such as resource exhaustion etc. that I
  should be aware of ?)
  * would it be easier or harder to help implement 2-phase commit
  inside PostgreSQL (but I am not quite a PostgreSQL hacker yet !)

  Many thanks in advance !

-- 
<< Tout n'y est pas parfait, mais on y honore certainement les jardiniers >>

			Dominique Quatravaux <dom@kilimandjaro.dyndns.org>

Tue, 23 Jan 2001 20:45:54 +0100
dom@idealx.com
Re: Re: AW: Re: MySQL and BerkleyDB (fwd)
 [ sorry to repost this, but I didn't receive my mail back... Anything
 wrong with the mailserver ? ]

 I am involved in a project of open-source, PostgreSQL-backed,
buzzword-compliant replication/high availability software that would
act as an SQL ï¿½ one-to-many ï¿½ gateway (but still in the design phase
--- this is *not* an announcement :-). Of course, the topic of 2-phase
commit is important to us ; we currently plan to record all write
commands issued during the transaction in an auxiliary table (some
sort of higher-level WAL). First commit phase would then consist in
closing this record and ensuring it can be  REDOne in the case of a
crash (UNDO would be done by just rolling back the current
transaction). But this is quite complicated and may require to
serialize all accesses (both read and write) to a given database so as
to guarantee that REDO will yield the very same result.

 I understand it would certainly be better and more profitable for
the community if I could help implement 2-phase commit inside
PostgreSQL. But I am not much of a PostgreSQL hacker yet. What do you
think ?

-- 
<< Tout n'y est pas parfait, mais on y honore certainement les jardiniers >>

			Dominique Quatravaux <dom@kilimandjaro.dyndns.org>

Wed, 24 Jan 2001 13:00:25 +0100
dom@idealx.com
Re: 2-phase commit