Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hKKlq-0005ht-Pq for pgsql-www@arkaria.postgresql.org; Sat, 27 Apr 2019 10:41:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hKKlp-0005F6-Hd for pgsql-www@arkaria.postgresql.org; Sat, 27 Apr 2019 10:41:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hKKlp-0005Ez-3n for pgsql-www@lists.postgresql.org; Sat, 27 Apr 2019 10:41:25 +0000 Received: from mail-lj1-x243.google.com ([2a00:1450:4864:20::243]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hKKll-0003Dx-Pd for pgsql-www@lists.postgresql.org; Sat, 27 Apr 2019 10:41:24 +0000 Received: by mail-lj1-x243.google.com with SMTP id z26so5222538ljj.2 for ; Sat, 27 Apr 2019 03:41:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hagander-net.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=U/sAj/zthmsqM4pbTxvAk0cpdvJ9PqyJE8uH6KmOpcA=; b=Z4DNLmHVL+/l3cB/6zaA2BmMcdLd2w2V9CKYwHCm4Ippzzrtv+QTqa5emOM7dsIYNc OYHNRPiR2Fg57aj73F+8s9xg2nGXxswdyPbItzCpiyb1B54k8YE2nIY9Sp+eKGydsBph v7GvxSVgI4/pLsCb9eP9XJqBUHEackr5QOPjdWyV9T0ElzYnFdu1edvA55ElBNOeqBda THShdX6JRy9uaeM5FWgR1UUL8QtFjy65nUkf39irny2NnzLelzcyL8iK5gGhHq7ihLLN wv39XI+7ZSvz3aZcNOaMs7LibQhSWvMQcMsysZzyZ9RwI+vku3YFQ3nQH+OCpGeFQ57k 0Kbg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=U/sAj/zthmsqM4pbTxvAk0cpdvJ9PqyJE8uH6KmOpcA=; b=G7FzJ09DFuJYmLY0Tjvr5ysuRnyb29G4tXuckyjP9zD2I4js9dIGp7t2g2EiDhcV7V JBP43yfwoHXpHWRc2hNk2BwUFOpGoz0ZA1uD8FwE8VtGNA2gM0dFPvChqloxPuD5ZHkH QbbsgEYl3ScfcSyUk7XyJrfFU/1EyEN+SVsY2SyCXSJvDP4w+mUWXbDiozewvCvWCX4p xy5yn+850HA3utXMlSuikFJrs3Z33Ob3xBxnipeCHvdad3pR0Q9mIwDYsHTH9jQI6Qm+ R69mnA2gS8nlElV+5fTGLVIJJWkai8d78fQAaa0r1h+FKQIYef0Pz+vsk/rcq8F/F6PH bNCw== X-Gm-Message-State: APjAAAVZXuQQvNdvsKWJDWka5u2pm/0QnnZ+kbVs0imiKDmFXOwvdNjo xqiphfaeF5ixfhZS73HqA+WTJzcM0au4bIsvL94bUNyV X-Google-Smtp-Source: APXvYqwSZwFpFOmt+dAMQOtEvnSV2WHfgPYq2GYi0NrVMZN6T5OmL7y9t2DVcXWD5G5Mx9Qq2gkeMKIpGag3rFnXZes= X-Received: by 2002:a2e:8819:: with SMTP id x25mr20697224ljh.18.1556361679912; Sat, 27 Apr 2019 03:41:19 -0700 (PDT) MIME-Version: 1.0 References: <15706-8ded5d7a37af6278@postgresql.org> <9ef787c5-62a9-c0a3-6261-5ef4863c78d3@postgresql.org> <847625e0-7726-0cf7-8c68-ec2500a07b45@postgresql.org> In-Reply-To: <847625e0-7726-0cf7-8c68-ec2500a07b45@postgresql.org> From: Magnus Hagander Date: Sat, 27 Apr 2019 12:41:09 +0200 Message-ID: Subject: Re: BUG #15706: Support Services page out of date To: "Jonathan S. Katz" Cc: Daniel Gustafsson , Euler Taveira , PostgreSQL WWW , "jtara-github@spamex.com" Content-Type: multipart/alternative; boundary="00000000000012d3da058780b1c7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --00000000000012d3da058780b1c7 Content-Type: text/plain; charset="UTF-8" On Sat, Apr 27, 2019 at 5:48 AM Jonathan S. Katz wrote: > On 3/25/19 5:33 AM, Daniel Gustafsson wrote: > > On Sunday, March 24, 2019 6:50 PM, Magnus Hagander > > wrote: > >> On Thu, Mar 21, 2019 at 1:20 AM Jonathan S. Katz >> > wrote: > >> > >> Some of the pgweb folks have discussed doing that before; it's not > >> a bad > >> idea, and perhaps would cut down on some of the headaches. > >> > >> > >> That was in fact the original plan, nobody just got around to building > >> it. There was even a db field for it in the early dev snapshots, but > >> it was removed since it was never quite done. > >> > >> The general idea was to just have a "last confirmed" timestamp field > >> on each entry, and just stop showing any entries that have not been > >> confirmed in days/weeks/months/whatever. We can keep them around > >> some extra time beyond that in case people come back to update them > >> later of course. > > > > I think it makes sense to remove after a set timeout, if the user hasn't > > verified in 3 months (or some > > other sufficiently long period) then the odds that the entry is out of > > date seems quite high. > > > >> Oh, and +1 for doing the same for products. > > > > +1 > > So I had (made?) a chunk of uninterrupted time and went ahead and did a > manual scrub of the professional services page with a test for the > proposed above algorithm. Summary and details to follow: > Thanks! That must've been a painful couple of hours :) > This is also a long way of saying that I think we should proceed with > the solution to help automate this. After going through the painstaking > process again, I'd recommend it works kind of like this: > > - An email goes out once every {6 months, 1 year} to the organizational > contact that says something like: "Hi, This is an automated check to > ensure your company is still an active PostgreSQL service provider. > Please click the URL provided to confirm. If you do not > confirm within 7 days, your listing will be unpublished" > 7 days might be a bit on the short side. If someone is unlucky they get the notification while on vacation for example, and then they're delisted before they can even see it. - If user clicks URL, it is confirmed and the last_confirmed_date is set > to CURRENT_DATE > > - If user does not click URL, we set the entry to unpublished. This > would put it into the moderator feed, and one of the pgweb moderators > can then take further action if need be. > That's pretty close to my original thoughts around that, except I'd just delete them as step 3 and not bother the queue. I'm fine with de-listing it as well, as long as pgweb moderators are happy to handle that :) That would also take care of the scenario where the email bounces, by simply being able to ignore that fact and leave it up to the individual moderator to handle manually. Anyway, products needs a scrub as well, which I will try to block out > some time for in the coming days to handle those as well. Those should > hopefully not take as much time as they are a bit easier to verify. > > Cool. Thanks! -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/ --00000000000012d3da058780b1c7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Sat, Apr 27, 2019 at 5:48 AM Jonat= han S. Katz <jkatz@postgresql.or= g> wrote:
On 3/25/19 5:33 AM, Daniel Gustafsson wrote:
> On Sunday, March 24, 2019 6:50 PM, Magnus Hagander <magnus@hagander.net>
> wrote:
>> On Thu, Mar 21, 2019 at 1:20 AM Jonathan S. Katz <jkatz@postgresql.org
>> <mailto:jkatz@postgresql.org>> wrote:
>>
>>=C2=A0 =C2=A0 =C2=A0Some of the pgweb folks have discussed doing th= at before; it's not
>>=C2=A0 =C2=A0 =C2=A0a bad
>>=C2=A0 =C2=A0 =C2=A0idea, and perhaps would cut down on some of the= headaches.
>>
>>
>> That was in fact the original plan, nobody just got around to buil= ding
>> it. There was even a db field for it in the early dev snapshots, b= ut
>> it was removed since it was never quite done.
>>
>> The general idea was to just have a "last confirmed" tim= estamp field
>> on each entry, and just stop showing any entries that have not bee= n
>> confirmed in <n> days/weeks/months/whatever. We can keep the= m around
>> some extra time beyond that in case people come back to update the= m
>> later of course.
>
> I think it makes sense to remove after a set timeout, if the user hasn= 't
> verified in 3 months (or some
> other sufficiently long period) then the odds that the entry is out of=
> date seems quite high.
>
>> Oh, and=C2=A0+1 for doing the same for products.=C2=A0
>
> +1

So I had (made?) a chunk of uninterrupted time and went ahead and did a
manual scrub of the professional services page with a test for the
proposed above algorithm. Summary and details to follow:

Thanks! That must've been a painful couple of hours := )

<snip>=C2=A0


=

This is also a long way of saying that I think we should proceed with
the solution to help automate this. After going through the painstaking
process again, I'd recommend it works kind of like this:

- An email goes out once every {6 months, 1 year} to the organizational
contact that says something like: "Hi, This is an automated check to ensure your company is still an active PostgreSQL service provider.
Please click the URL provided to confirm. <UniqueURL> If you do not confirm within 7 days, your listing will be unpublished"

7 days might be a bit on the short side. If someone = is unlucky they get the notification while on vacation for example, and the= n they're delisted before they can even see it.


- If user clicks URL, it is confirmed and the last_confirmed_date is set to CURRENT_DATE

- If user does not click URL, we set the entry to unpublished. This
would put it into the moderator feed, and one of the pgweb moderators
can then take further action if need be.

That's pretty close to my original thoughts around that, except I'= ;d just delete them as step 3 and not bother the queue.=C2=A0 I'm fine = with de-listing it as well, as long as pgweb moderators are happy to handle= that :) That would also take care of the scenario where the email bounces,= by simply being able to ignore that fact and leave it up to the individual= moderator to handle manually.


Anyway, products needs a scrub as well, which I will try to block out
some time for in the coming days to handle those as well. Those should
hopefully not take as much time as they are a bit easier to verify.

Cool. Thanks!

--
=C2=A0Magnus = Hagander
=C2=A0Me: https://www.hagander.net/
=C2=A0Work: https://www.redpill-linpro.com/
<= /div>
--00000000000012d3da058780b1c7--