Re: BUG #15706: Support Services page out of date - Mailing list pgsql-www

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:
>>
>>     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 <n> 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:

Looking at the timestamp on some of the old moderation comments, it
turned out the last scrub was almost 5 years to the day. Oops, time flies :(

When looking at the listed services, the main thing I checked was did
the website that was linked to from the service indicate the
organization supported PostgreSQL or PostgreSQL-related product in some
way -- this made it a fairly low bar to clear. I bucked each service
into one of 3 categories:

1. OK: I was able to find their PostgreSQL offering

2. Send a Follow-up: Website was still active & looked like a technology
provider, but no clear indication of PostgreSQL offering. This I think
is the simulation of how the "please perform this update" automation
would work

3. Remove: website was dead / company did something completely different
/ site redirected me to things I would like to unsee. All the more
reason to scrub the data.

The scrub took me about 2 hours and change.

- 167 of the listings fell into category 1
- 28 required follow-ups (category 2) and I sent as such providing a
reasonable timeline to provide at least some update (one week)
- 39 fell into category 3 and were remove.

Based on the last time I did this, I think ~25% of the category 2
organizations will get back and make the necessary updates, so assume we
remove that much  more.

Anyway, the data was much more stale than I thought it would be :( But
I'm not surprised, either.

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"

- 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.

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.

Thanks,

Jonathan