Re: more generic approach as for userdb? (was: Dict issue with PostgreSQL for last_login plugin (duplicate key))
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
Re: more generic approach as for userdb? (was: Dict issue with PostgreSQL for last_login plugin (duplicate key))
> 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. > > Kind regards, > > -- Steffen Kaiser Hi! You can write completely custom last_login plugin by using mail-lua plugin, by having functions mail_user_created(user) and mail_user_deinit(user) in your Lua script. This of course requires v2.3.4 or later. Aki signature.asc Description: OpenPGP digital signature
more generic approach as for userdb? (was: Dict issue with PostgreSQL for last_login plugin (duplicate key))
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 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/ 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. Kind regards, - -- Steffen Kaiser -BEGIN PGP SIGNATURE- Version: GnuPG v1 iQEVAwUBXOY0bsQnQQNheMxiAQKNOQgAmRzNVJTNn3XpHBBGnZOtZ5Ku9Cp9UZIY 70HukeDKdR6rg7XNFGhwTDGa30QRGABByoospMHLAIabZ7j9WFaajAKI01roXotc skD+T8orvpk7BH/2+f2v5f67xa3GU6LJE330yZJubFb87NFq4otdtXGjhPjCf16j /wREiuSi0CqDTMtSOXjHXtViI9EL/e+CoJtEgK+gaXINCdCP7Cb2OEjtXHpItuqm tUAQoh418wWfVt6k6NgpDVX/hD+RyRfxKI4dste0VJZ9OEhH1mpPGaRB/BIkhEh4 OJ18upVhIXbJPDyAPofSB1YGDkPl/HlChmh+QuOpVm9rolmt9SyZQg== =unPo -END PGP SIGNATURE-
