<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 17:08, mabi via dovecot
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:4v_z1Pm9SUKxvEuw8hZE_ZyMpT0MKJAVQfVRQDyGy6xKzwTLd-_63wcRh98W05fPk0xto7ZxXvxZsTlTyf9lBpEdFKfp9wG3oLOZYArmdf0=@protonmail.ch">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div>‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐<br>
</div>
<div> On Sunday, May 19, 2019 4:44 PM, John Fawcett via dovecot
<a class="moz-txt-link-rfc2396E" href="mailto:dovecot@dovecot.org"><dovecot@dovecot.org></a> wrote:<br>
</div>
<div> <br>
</div>
<blockquote class="protonmail_quote" type="cite">
<div class="moz-cite-prefix">I don't have PostgresSql, would you
be able to verify if this syntax would work:
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') <span>ON
</span>CONFLICT<span> </span>(<span>username</span>) <span>UPDATE
SET last_login=</span>1558273000,domain='<a
href="mailto:user@domain.tld" moz-do-not-send="true">user@domain.tld</a>'
It's important to check that this updates only the single row
for that user and it puts the right data in that row. If it
doesn't work can you give the correct syntax?<br>
</div>
</blockquote>
<div><br>
</div>
<div>So you nearly yes ;-) The only parameter missing was "DO"
keyword before the "UPDATE". So the correct query would be:<br>
</div>
<div><br>
</div>
<div>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 (username) DO UPDATE SET
last_login=1558273000,domain='domain.tld';<br>
</div>
<div><br>
</div>
<div>I also adapted the domain='domain.tld' at the end of the
query, you had domain='<a href="mailto:user@domain.tld"
moz-do-not-send="true">user@domain.tld</a>' but this is just a
"content" detail which does not matter.<br>
</div>
<div><br>
</div>
<div>Hope that helps. Let me know if I can do any further testing.<br>
</div>
<div><br>
</div>
</blockquote>
<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.</p>
<p>John<br>
</p>
<p>John<br>
</p>
</body>
</html>