<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">On 19/05/2019 22:45, John Fawcett via
dovecot wrote:<br>
</div>
<blockquote type="cite"
cite="mid:c97d4232-e4e2-ab86-aa29-6f6add94c24c@voipsupport.it">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<div class="moz-cite-prefix">On 19/05/2019 22:37, John Fawcett via
dovecot wrote:<br>
</div>
<blockquote type="cite"
cite="mid:17ec324b-f2dd-debe-fd64-c72da671c9dd@voipsupport.it">
<meta http-equiv="Content-Type" content="text/html;
charset=UTF-8">
<div class="moz-cite-prefix">On 19/05/2019 20:31, mabi via
dovecot wrote:<br>
</div>
<blockquote type="cite"
cite="mid:RHyoimPhza46KCON7dwVra5QdR6jn-POtXK1U3USPnw_zO48pJ03HTBEvDGkj301mGjGmg37ARd2We63_18rvCG1pijwjoDYszcyarrnBq4=@protonmail.ch">
<meta http-equiv="content-type" content="text/html;
charset=UTF-8">
<div><br>
</div>
<div>‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐<br>
</div>
<div> On Sunday, May 19, 2019 7:36 PM, John Fawcett via
dovecot <a class="moz-txt-link-rfc2396E"
href="mailto:dovecot@dovecot.org" moz-do-not-send="true"><dovecot@dovecot.org></a>
wrote: <br>
</div>
<blockquote class="protonmail_quote" type="cite">
<p>Attached is a tentative patch. I've verified no
regression for mysql. There should be no regression for
sqlite as the code path is identical. <br>
</p>
<p>Are you able to test for pgsql? As mentioned by Akie it
will break for PostgresSql < 9.5 but probably it was
not working anyway due to duplicate keys. Whether this is
a wider problem depends on whether the insert code is
being used for other purposes too. <br>
</p>
<p>If you or someone can verify it works on PostgresSql
>= 9.5, then the next step will be to make it
conditional on the version.<br>
</p>
</blockquote>
<div>Thank you very much John for your patch, that's
fantastic. I am on OpenBSD 6.5 and will recompile dovecot
from the ports by adding your patch to it, I hope that works
and will let you know if I managed. If I understand
correctly the relevant binary file I need to replace is the
following right:<br>
</div>
<div><br>
</div>
<div>/usr/local/lib/dovecot/dict/libdriver_pgsql.so<br>
</div>
<div><br>
</div>
<div>or are there any others I also need to replace in order
to test? I am planning to test live by just replacing the
relevant file(s) so that I hopefully don't need to
re-install the whole dovecot package.<br>
</div>
</blockquote>
<p>I'm not sure how the source compilation works on OpenBSD,
when I do it on linux and run "make install" it installs all
relevant binaries/libraries.</p>
<p>I saw one issue with the fix though, it does not correctly
pull out the username field. I'm wondering if the query can be
rewritten not to mention the name of the field that fails the
constraint....</p>
<p>John<br>
</p>
</blockquote>
<p>so basically if this works just as well:</p>
<p>INSERT INTO last_logins (last_login,username,domain) VALUES
(1558273000,'<a href="mailto:user@domain.tld"
moz-do-not-send="true">user@domain.tld</a>','domain.tld') ON
CONFLICT DO UPDATE SET
last_login=1558273000,domain='domain.tld';</p>
<p>then the fix can be altered to attached file which is more
similar to the MYSQL syntax and does not require extra logic to
get the username field.<br>
</p>
<p>John<br>
</p>
<p><br>
</p>
</blockquote>
<p>So looking into this with a postgresql databse to work with: the
above query does not work. You have to specify either the column
name or the constraint name that you expect to be violated in
order for the update to take place.</p>
<p>With a map like this one you're using</p>
<pre><code>map {
pattern = shared/last-login/$user/$domain
table = last_login
value_field = last_login
value_type = uint
fields {
username = $user
domain = $domain
}
}</code></pre>
<p>there's no field name that is obviously the primary key. I've
reworked the patch to use the postgres default primary key
constraint name (tablename_pkey). <br>
</p>
<p>The attached fix should work in that case, although I feel it's
not general enough.<br>
</p>
<p>John<br>
</p>
</body>
</html>