Prev: Suppress "Command died with status 1" in Pipe transport
Next: Force bounce from queue
From: Rob Foehl on 18 Jun 2010 14:10
On Fri, 18 Jun 2010, Victor Duchovni wrote:

> Indeed. One still needs tools to insert data into the database.
> Does Postfix need to provide a minimal interface for this, or do we
> assume that SQLite users will have adequate tools outside Postfix.

It wouldn't hurt to omit this support for the time being, as that's at
least consistent with the approach to other SQL engines. It might make
sense to roll this up into generic update support for any SQL database,
which I've been considering for a while...

> Does Postfix need to do anything to indicate transaction boundaries
> between its lookups? Or is not starting a transaction in the first place,
> sufficient to indicate that each query is independent from all the others?

SQLite v3 implements implicit transactions for writers which don't
explicitly request them, the only thing that the readers need to handle is
retrying queries after SQLITE_BUSY. The full locking semantics are
described here:

http://www.sqlite.org/lockingv3.html

-Rob

From: Patrick Ben Koetter on 18 Jun 2010 14:11
* Wietse Venema <postfix-users(a)postfix.org>:
> Victor Duchovni:
> > On Fri, Jun 18, 2010 at 05:58:02PM +0200, Patrick Ben Koetter wrote:
> >
> > > > Right now this is a read-only implementation (like mysql/pgsql)
> > > > but it may be worthwhile to add update support. SQLite implements
> > > > locking internally. That would allow us to avoid the problems with
> > > > Postfix's external locks on Berkeley DB maps, which are broken
> > > > after BDB version 2.
> > >
> > > That's great news!
> >
> > Indeed. One still needs tools to insert data into the database.
> > Does Postfix need to provide a minimal interface for this, or do we
> > assume that SQLite users will have adequate tools outside Postfix.
>
> A "postmap" option to create an SQLite file would make sense.

Do you mean creating an SQLite database from a flat file that, for example,
contains access rules mapping addresses to actions (recp(a)foo REJECT)?

What if there were many files that wanted to be stored in a SQLite database?
Creating a database only for one table would be a waste of ressources, I
guess.

Would it make sense to have two commands? One to create the database and one
to add flat files as tables to the database or update existing tables?

Or do you mean only creating a database and then use other tools to maintain
the data therein?

The easiest part - assuming the SQLite driver can also write - would probably
be to use it on everything that goes into $data_directory i.e. databases that
keep track of tls session keys or verified senders and so on.

p(a)rick

--
All technical questions asked privately will be automatically answered on the
list and archived for public access unless privacy is explicitely required and
justified.

saslfinger (debugging SMTP AUTH):
<http://postfix.state-of-mind.de/patrick.koetter/saslfinger/>

From: Ralf Hildebrandt on 18 Jun 2010 14:55
* Patrick Ben Koetter <p(a)state-of-mind.de>:

> > A "postmap" option to create an SQLite file would make sense.
>
> Do you mean creating an SQLite database from a flat file that, for example,
> contains access rules mapping addresses to actions (recp(a)foo REJECT)?
>
> What if there were many files that wanted to be stored in a SQLite database?
> Creating a database only for one table would be a waste of ressources, I
> guess.

It would make a great tool for a flat-file -> database migration:

* Use flat files first
* verify that "it works"
* then convert into SQLite
* verify that "it (still) works"
* then convert into "real" Database

It would actually help the user to use the path that has been
recommended by Victor et.al.

--
Ralf Hildebrandt
Geschäftsbereich IT | Abteilung Netzwerk
Charité - Universitätsmedizin Berlin
Campus Benjamin Franklin
Hindenburgdamm 30 | D-12203 Berlin
Tel. +49 30 450 570 155 | Fax: +49 30 450 570 962
ralf.hildebrandt(a)charite.de | http://www.charite.de


From: Wietse Venema on 18 Jun 2010 15:01
Rob Foehl:
> On Fri, 18 Jun 2010, Victor Duchovni wrote:
>
> > Indeed. One still needs tools to insert data into the database.
> > Does Postfix need to provide a minimal interface for this, or do we
> > assume that SQLite users will have adequate tools outside Postfix.
>
> It wouldn't hurt to omit this support for the time being, as that's at
> least consistent with the approach to other SQL engines. It might make
> sense to roll this up into generic update support for any SQL database,
> which I've been considering for a while...
>
> > Does Postfix need to do anything to indicate transaction boundaries
> > between its lookups? Or is not starting a transaction in the first place,
> > sufficient to indicate that each query is independent from all the others?
>
> SQLite v3 implements implicit transactions for writers which don't
> explicitly request them, the only thing that the readers need to handle is
> retrying queries after SQLITE_BUSY. The full locking semantics are
> described here:
>
> http://www.sqlite.org/lockingv3.html

I'll add a check for that.

Wietse

From: Wietse Venema on 18 Jun 2010 15:17
Patrick Ben Koetter:
> * Wietse Venema <postfix-users(a)postfix.org>:
> > Victor Duchovni:
> > > On Fri, Jun 18, 2010 at 05:58:02PM +0200, Patrick Ben Koetter wrote:
> > >
> > > > > Right now this is a read-only implementation (like mysql/pgsql)
> > > > > but it may be worthwhile to add update support. SQLite implements
> > > > > locking internally. That would allow us to avoid the problems with
> > > > > Postfix's external locks on Berkeley DB maps, which are broken
> > > > > after BDB version 2.
> > > >
> > > > That's great news!
> > >
> > > Indeed. One still needs tools to insert data into the database.
> > > Does Postfix need to provide a minimal interface for this, or do we
> > > assume that SQLite users will have adequate tools outside Postfix.
> >
> > A "postmap" option to create an SQLite file would make sense.
>
> Do you mean creating an SQLite database from a flat file that, for example,
> contains access rules mapping addresses to actions (recp(a)foo REJECT)?

Yes.

> What if there were many files that wanted to be stored in a SQLite database?
> Creating a database only for one table would be a waste of ressources, I
> guess.

With sqlite:mapname, the "mapname" configuration file specifies
the name of the database file and the name of the table in that
database. So you can have it either way: multiple tables in one
file, or one file per table.

I would recommend to use separate database files for read/write
data and read-mostly data; if you don't write to a file it is less
likely to become corrupted.

Likewise, use separate database files for data that is sensitive
or trusted, and for data that isn't.

But, there is no way to enforce this, since every SQLite map
is configured independently.

Wietse

First  |  Prev  | 
Pages: 1 2
Prev: Suppress "Command died with status 1" in Pipe transport
Next: Force bounce from queue