Thread: storage engine , mysql syntax CREATE TABLE t (i INT) ENGINE = INNODB|BDB

In mysql, we can wrote a create table like 
CREATE TABLE t (i INT) ENGINE = INNODB||BDB|;
where the storage engine is the innodb one. 
This allow to have differents kind of storage format, and allow to easly implements memory table or remote table. 
I try to make the same thing for postgresql but i do not understand where the logical storage engine is in the source
code.
May i have somme help to find it .
Thank you.
pierre

Pierre Emmanuel Gros wrote:
> In mysql, we can wrote a create table like CREATE TABLE t (i INT) ENGINE 
> = INNODB||BDB|;
> where the storage engine is the innodb one. 

MySQL needs this because they have a weird understanding of RDBMS.
There's absolutely no sense in trying to transfer this stuff into 
PostgreSQL. Use it as designed, and you'll never miss this MySQL "feature".

Regards,
Andreas

Pierre Emmanuel Gros wrote:
> In mysql, we can wrote a create table like CREATE TABLE t (i
> INT) ENGINE = INNODB||BDB|; where the storage engine is the
> innodb one.  This allow to have differents kind of storage
> format, and allow to easly implements memory table or remote
> table.  I try to make the same thing for postgresql but i do
> not understand where the logical storage engine is in the source
> code.  May i have somme help to find it .

We only have one table type, full transactions, full storage support.
We don't plan to support any sub-optimial storage systems.

-- Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Andreas Pflug wrote:

> Pierre Emmanuel Gros wrote:
> 
>> In mysql, we can wrote a create table like CREATE TABLE t (i INT) 
>> ENGINE = INNODB||BDB|;
>> where the storage engine is the innodb one. 
> 
> 
> MySQL needs this because they have a weird understanding of RDBMS.

This could be true, but the answer doesn't make sense, at least it's
in a perfect "mysql people" style. Isn't ? We don't need transactions,
we don't needs store procedure and so on...

> There's absolutely no sense in trying to transfer this stuff into 
> PostgreSQL. Use it as designed, and you'll never miss this MySQL "feature".

Another "mysql people" style answer.


We have only one engine: the full transactional one. If the OP need to have
for example the MEMORY one the he can easily create a RAM disk and with the
tablespaces support he can create tables or index or whatever objects
in memory.



Regards
Gaetano Mendola

Gaetano Mendola wrote:
> We have only one engine: the full transactional one. If the OP need
> to have for example the MEMORY one the he can easily create a RAM
> disk and with the tablespaces support he can create tables or index
> or whatever objects in memory.

Well, it certainly could make sense to have different storage engines 
for different access patterns.  (Not for different degrees of 
implementation correctness, mind you.)  So let's just say we don't have 
them.

Postgres was, however, one of the systems that in fact pioneered 
pluggable storage managers.  So we could say we're already one 
generation ahead of everyone else: we had switchable storage managers, 
realized we didn't need them, and got rid of them.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:
> Postgres was, however, one of the systems that in fact pioneered 
> pluggable storage managers.  So we could say we're already one 
> generation ahead of everyone else: we had switchable storage managers, 
> realized we didn't need them, and got rid of them.

We do actually still have the smgr switch interface, so in theory you
could plug in a new storage manager just as well as you could back in
the Berkeley days.  If anything better --- smgr is now allowed to handle
stuff that was kluged in upper layers back then.

I think the reason that this feature is moribund is largely that
substituting behaviors at that low level stopped being interesting some
time ago.  In modern systems the equivalent behavior is down inside the
kernel device driver, if not in the storage device itself (think SAN,
RAID controllers, etc) and it's just not useful to try to manage it
inside an unprivileged-application database.

The complaint that's commonly leveled against the MySQL table-handler
design is that it puts the switch at too *high* a level --- there are
very significant semantic issues that are left to the table handler
(eg locking), which means that an application is pretty much locked into
the handler it was designed for.  MySQL isn't so much one database as
it is three or four databases with roughly similar APIs.  I don't think
it's either practical or interesting to try to introduce an equivalent
layering into Postgres.

There might be some way to design an intermediate switching layer where
interesting behavioral changes could be introduced without breaking
application API expectations.  But we don't have one, and I think it'd
be quite a bit of work to introduce one, even if you could get people
to buy into the idea in advance of proof of usefulness :-(
        regards, tom lane

On Mon, 26 Jul 2004, Tom Lane wrote:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > Postgres was, however, one of the systems that in fact pioneered
> > pluggable storage managers.  So we could say we're already one
> > generation ahead of everyone else: we had switchable storage managers,
> > realized we didn't need them, and got rid of them.
>
> We do actually still have the smgr switch interface, so in theory you
> could plug in a new storage manager just as well as you could back in
> the Berkeley days.  If anything better --- smgr is now allowed to handle
> stuff that was kluged in upper layers back then.
>
> I think the reason that this feature is moribund is largely that
> substituting behaviors at that low level stopped being interesting some
> time ago.  In modern systems the equivalent behavior is down inside the
> kernel device driver, if not in the storage device itself (think SAN,
> RAID controllers, etc) and it's just not useful to try to manage it
> inside an unprivileged-application database.
>
> The complaint that's commonly leveled against the MySQL table-handler
> design is that it puts the switch at too *high* a level --- there are
> very significant semantic issues that are left to the table handler
> (eg locking), which means that an application is pretty much locked into
> the handler it was designed for.  MySQL isn't so much one database as
> it is three or four databases with roughly similar APIs.  I don't think
> it's either practical or interesting to try to introduce an equivalent
> layering into Postgres.
>
> There might be some way to design an intermediate switching layer where
> interesting behavioral changes could be introduced without breaking
> application API expectations.  But we don't have one, and I think it'd
> be quite a bit of work to introduce one, even if you could get people
> to buy into the idea in advance of proof of usefulness :-(

I've looked into this.

The problem is that many storage management systems also want to take
higher level control of indexing. They also often do their own WAL and
PITR. Some do their own buffer management, locking and replication/load
management too. So, as you say, its hard say where an interface should be
abstracted.

Its definately a difficult issue.

Gavin

On Sun, 2004-07-25 at 22:23, Tom Lane wrote:
> I don't think
> it's either practical or interesting to try to introduce an equivalent
> layering into Postgres.

I can possibly see a use for a row locking storage system, i.e. non MVCC
for some applications.  But I can't see it being worth the amount of
work it would require.

Or is the locking model too high level to be handled this way?  Just
wondering.

"Scott Marlowe" <smarlowe@qwest.net> writes:
> On Sun, 2004-07-25 at 22:23, Tom Lane wrote:
>> I don't think
>> it's either practical or interesting to try to introduce an equivalent
>> layering into Postgres.

> I can possibly see a use for a row locking storage system, i.e. non MVCC
> for some applications.  But I can't see it being worth the amount of
> work it would require.

I can't either.  The implications of such a thing really are so
far-reaching that it could not be isolated in a nice little layered API.
To take one example, we'd have to reexamine the locking and crash-safety
behavior for every single command that updates the system catalogs.

For better or for worse, I think we're married to MVCC.
        regards, tom lane

Pierre Emmanuel Gros wrote:
> In mysql, we can wrote a create table like CREATE TABLE t (i INT) ENGINE 
> = INNODB||BDB|;
> where the storage engine is the innodb one. This allow to have 
> differents kind of storage format, and allow to easly implements memory 
> table or remote table. I try to make the same thing for postgresql but i 
> do not understand where the logical storage engine is in the source code.
> May i have somme help to find it .
> Thank you.
> pierre


Pierre,

Basically the code related to the storage engine can be found in 
src/backend/storage. There you will find some important parts of 
PostgreSQL such as the free space map, the lock manager and so forth. 
The storage system is implemented there. The code should be extendable 
although nobody has implemented different storage systems in the past 
(at least I can't remember) because it has never been important.

Personally I don't think that real memory tables are important. In 7.5 
you can even model them with the help of RAM drives and tablespaces (in 
case you really need it).

As far as remote tables are concerned: To do database links properly you 
will need some mutli-phase commit algorithm. I don't think it is that 
easy. How would you ensure integrity? Did you already think about 
transaction isolation related issues? How would the planner treat those 
issues and how should recovery in case of disaster work? Recall, you are 
in a distributed system then ...
Regards,
    Hans

-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/720/10 1234567 or +43/664/816 40 77
www.cybertec.at, www.postgresql.at, kernel.cybertec.at