more generic approach as for userdb? (was: Dict issue with PostgreSQL for last_login plugin (duplicate key))

[John Fawcett]

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