On 23/05/2019 07:49, Steffen Kaiser via dovecot wrote: > On Wed, 22 May 2019, John Fawcett via dovecot wrote: > > > an update when insert fails seems to be a MySQL specific extension to > > standard Sql. So I think that it's clear that support for PostgreSql and > > Sqlite needs to be implemented. The same issue likely exist in other > > plugins too, for example expire. > > > My doubts are around the right solution to adopt. Initially I thought > > that there was a PostgreSql syntax similar to MySQL which could be > > easily added to the code, but closer inspection shows that the > > PostgreSql syntax requires specification of either a constraint name or > > the index column(s) for the primary/unique keys. > > You mean the "target" in ON CONFLICT target action, right? > http://www.postgresqltutorial.com/postgresql-upsert/ > Yes, whereas MySQL uses a generic syntax not requiring specific info, as far as I am aware PostgreSql requires the target. I tried without and got an error.
> > Constraint names are nowhere specified in the dictionary map syntax and > > it's not possible either to identify with 100% certainty the primary key > > column(s). > > One could dive into Postgres-specifics to get it, but there are other > SQLs, too; the quota plugin advertises to use TRIGGERs to turn an > INSERT into an UPDATE silently, which is no general approach either. > https://wiki2.dovecot.org/Quota/Dict > > > 1) logic which always tries to update and falls back to insert if the > > update fails (or viceversa) for all sql dictionaries. > > > 2) updates to the map syntax so that either the constraint name or > > primary key columns can be specified. > > > Ideas are welcome. > > Maybe, one should drop the automatic at all and let the user specify > the commands manually like with the userdb/passwd. Hence, the generic > SQL preparation code is already present. There could/should/would be > documented lots of "best practice" settings for various backends. > > In fact, this approach would better fit into the open and more > "general" base idea Dovecot uses in other places, IMHO. > thanks for that suggestion, it would mean moving away from a syntax where other dictionary types use a map statement and sql wouldn't. > Kind regards, > > -- Steffen Kaiser