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 1hKEK3-00048f-OA for pgsql-www@arkaria.postgresql.org; Sat, 27 Apr 2019 03:48:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hKEK1-0000me-3t for pgsql-www@arkaria.postgresql.org; Sat, 27 Apr 2019 03:48:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hKEK0-0000mX-LT for pgsql-www@lists.postgresql.org; Sat, 27 Apr 2019 03:48:16 +0000 Received: from meldrar.postgresql.org ([2a02:c0:301:0:ffff::31]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hKEJw-0007v6-De for pgsql-www@lists.postgresql.org; Sat, 27 Apr 2019 03:48:14 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Content-Type:In-Reply-To:MIME-Version:Date: Message-ID:From:References:Cc:To:Subject:Sender:Reply-To: Content-Transfer-Encoding:Content-ID:Content-Description; bh=b9JHQRK9TCJwK6PGgck/ipEzSi3BOHBPnR0EXFu8VCg=; b=pxGdXK5htkZqT9rUdaUk3dGEzx I1PfmsCifw3soh06pSHM8Y1UgMaCU7zPt3cDa9aUmGi71HW4AruX/GNACWJn/CfUe0kA5vzWJip6B t8tVxJWc6j87PAMtZDqt8DSF4BCSocFbOK3nN2AM217LWNgmc3gyKd6UGzJdSzWvs8oo0itfmzAhg GXwxEuJnw6W/gNBK8bOXKtVI/vPlO9J+x3PqSFtNRLTrKQw9pOAiPrkD2DRYPrTMMXY1Rjme9Tzle TtXqrjlk5FwNshujHHZmsZ4hZvOXx+1YQ/KkIkG8fLYwWO+Qj+ZdysfDXJNDh3vVXiY7dlxM1Zhfm cFRgm7Sg==; Received: from pool-96-250-83-19.nycmny.fios.verizon.net ([96.250.83.19] helo=Ph33r-Retina.home) by meldrar.postgresql.org with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hKEJq-0000YT-4R; Sat, 27 Apr 2019 03:48:10 +0000 Subject: Re: BUG #15706: Support Services page out of date To: Daniel Gustafsson , Magnus Hagander Cc: Euler Taveira , PostgreSQL WWW , "jtara-github@spamex.com" References: <15706-8ded5d7a37af6278@postgresql.org> <9ef787c5-62a9-c0a3-6261-5ef4863c78d3@postgresql.org> From: "Jonathan S. Katz" Openpgp: preference=signencrypt Autocrypt: addr=jkatz@postgresql.org; keydata= mQINBFtShwABEAC7PNHDUOTYuifpUCk23KqfxdQQkn4nkoxOXRK0+rAj36FiwqGB4TJFuOVZ sDFAEiSlC8Jt4y5Cs7B5tetT8JNd2cs6zp/udMJJDz9d65O9PDpdlMgAmIiTzpLlSdx8FG56 DTksaDv1d8j3cTJPSE4/fWSxqzA7o3Y9UuL7atZPrzfImgpRKs0of7elIHwOa8GucjyhYqcR h60wFBJc2KXqQdDYRTZy43DSnY/0VNc0omiH355fustvpm+m5HjD3w7qZyfN3fpKJpnX1LCF f3MnPHaDGITIYGRCBXvf0UqUtD6OEVWPv2C2gyqWMIpWmZTOgDufltKyIByKBoS9x0PlFkij 04X3KODCngt+N8Ssc9OICc6QSxhjoP48PYPdmiTmkrGuf0LX084wj1xeo1NX7XxZK39F6dTJ DhsIiW0sNS0xMxQHLHG9VLbPjx3SANQBh6BuryPz5ZupW9/TIDmkvprtU/oXfKgtfYm3fxmk EctxbWrEPsFTFPyuMqQu6l+xyQv0s1VLZfjNWaua6H1/gGoIt6kRnn5qMXDVVpijuWkHbv7G ngaQMd258UrrOEHnnjzhQ7jxMWV9D+emxbAtlIxnYvCWlV4IwAQhEHfvudqYaIY3hNWrvQ6H GB2KXoTZYN9g5djm14/5nj1IU5zOcovkjJnKhoo9iStnpFF2cwARAQABtCdKb25hdGhhbiBT LiBLYXR6IDxqa2F0ekBwb3N0Z3Jlc3FsLm9yZz6JAlQEEwEIAD4CGwMFCQeGH4AFCwkIBwIG FQoJCAsCBBYCAwECHgECF4AWIQT6hLaVryv3miBkP/HxBJxynxxlJwUCW1KJJQAKCRDxBJxy nxxlJwjrD/kBgqsW4QpNpTFw7ifRokZV08CCX4huPBJQ91rrv+UEWlEcotFBHVkYyHnpzARl tcZxhJ9CbFxjniH9cOTty5T/O1yolbOHtZSW8Z8aWV6BVEbjMb+BFxSSLm7RnvJdzQbGCZq2 ZZvfVpB6z3EHYph4KDdVKvMFjoLskxmdS1DE0tE3zTxvoQsi24Q+HOS07kUjs6fsu/WICMfz mgO++AWG9Y0CvN0mm4TkujESzyKM9E5irD+leEMIcddl51Aa2c/VMfBXQbRmpHIgUFTmuHQD CnQih+9i3OJAksDg66SP8a7yiXv5mwvyDi1EfTGVKYR2j+pwyjwnC3oIbvDMmB3uTn2JIjnT iZKPVtAcAylXjubFltihQgNyuShdP4W+kBwZizhUFqUVL8Anx+KoytYmJPfMRFLGuK4obXKq a2ZS3k9KB+H+isOx2nFJOsc7V360Zp1DVaNmuiK10TT6QndShSPaqkJqFtCb6r92rZ9sZM/L 3vtCI4Rrl3Pt1MgtENXupS8gZpJnAYS0j5A1PAZ09r6ANoaeMHspF+5J5fOHeEvqphXr36mm a83Vl1t4orPb0+QmmijmlpseDU63M88Aw5p3c4qj7t8Qr2EZ5zrn7/sFn5wOfbs8Nymxafif QCnlV2vg9p0m7vSk/yLJ4PFZvs52FgqAGRCdRn0s2EC99bkCDQRbUocAARAAv8ho/toQ9DG3 j4f9h9n1aRHr2FlviN2Utpy6L8+dfDggO0geilmkGQOolZ2E60gGfye/kUtF9W3NByO4hxDR 9u6qbOXcdqnuA+cc68EfqlWFJrVtYFxt0h4ElWYOYnIezKthriWch/FY70FGrxs3z8UHOHq5 0wBW433eTvZm90WixBiXEt2v1DgW4Vr3ymfO7Aap/IYyPuE4JzgudAuAl0HKPyNEHWHG1dAb jX1RiCw9gknIDWQOF0B4UAaJctWGVcnZ3A2ULwNGMa1P9ZJlBWf1vcj01aiHMU0yQ7JjJiSp vfm9eM0uSLwRdDrJjyl5ZZqVumjdv2SMNQ8GvYRbEMys3GGDSt9zXgfCSUnPnJfYxjzBHRI6 x44Wfsx8S6hWxepOogCJJ/g67Bk9mY8YV4klWIXDJVOL5jnBC09DbsZG81JaE2QxB8Y7W36Z Mroi9XMxg3s805hQAQUvdG/poU8hN8BWdrnTm/+4eQQp7gDY1ePDmGM6bJC+OHOSnFtR/f+7 0zpKJ10cc7cBygGnl1yR3KjhFyAWUFvP4ZGziKCcpMwXZfe9PGuyA/YOubMphxIn3YsK2wrd faKZYX2GMZCZhMMcvx9IpQrxIJgU+VlwXu/O+Lk10VIPcxPJJwmpdI6HzcS8ZgG6IMcC444X XTuLaP8j2mgcMvYak3ScCykAEQEAAYkCPAQYAQgAJhYhBPqEtpWvK/eaIGQ/8fEEnHKfHGUn BQJbUocAAhsMBQkHhh+AAAoJEPEEnHKfHGUnReMP/RA2UhGQj+G4uBshkRLjpRysabdPqgQB dEBk6wYbio88Wg/2/hgY7UzmDDEwX3sZfQDcrI6+vIobI8uqstZID+WgAAa1JLfChMyVQnSy 0zfWMOABXscc5tGuvFRZvJklTissMFjXUwaREEKp4ZikTvJ/62MCjSdtrUhnPLvoTsHTKRKD ichE+b5A54alwsubTijw12O6N22r5IjZiiSZV0u7dsShyKw+7wCSax9fuBoE43NMYf+dnjMK nerAQYUcZWYMnk+EC8RaqYAxv6XZ2tKx1AkGGktwkQIBwrz9IlDSvJ3LWJ2UIIuLRTdngNgg GIL4zzuUa2F56FqskQIuYMaNETk6LYfalBDQ6TVLAcgCPQxp4k4i/PRsZ3lZ2ZhRHRYciOvm kp+I6EfHwllQpYrWs1thluBGqlJSVJgKl0IOFvKLsQ6KKfqzAwh6FxrO5qajp4viNIgtWoFw O5Bp0jgFTbH2OrMWIRfUdUCH1Djbuo65svhNj7FNsQVYzHDI+Nd1I/LOBoPc3UorRMF2M4JV kUR7skOHWUbPTSNUr7qc14NSMY3PKjGeVGAsBVHBPvmRx/Ss2tW/TpJWpxg4pmquFQSXuaYh Yf4FN8Sxy320pcr+FqN8AhZkYJcNY82OTtR5VEKRC/mYcyq61qXIKqngydn79bJjGxHipxyF FmEC Message-ID: <847625e0-7726-0cf7-8c68-ec2500a07b45@postgresql.org> Date: Fri, 26 Apr 2019 23:48:01 -0400 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:60.0) Gecko/20100101 Thunderbird/60.6.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="smUVKTzBLpg50lXvdD7hcESJCuy4fgMZX" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --smUVKTzBLpg50lXvdD7hcESJCuy4fgMZX Content-Type: multipart/mixed; boundary="fW40GmieOfEjPw8lqCUSUGAyl1LZbVWwq"; protected-headers="v1" From: "Jonathan S. Katz" To: Daniel Gustafsson , Magnus Hagander Cc: Euler Taveira , PostgreSQL WWW , "jtara-github@spamex.com" Message-ID: <847625e0-7726-0cf7-8c68-ec2500a07b45@postgresql.org> Subject: Re: BUG #15706: Support Services page out of date References: <15706-8ded5d7a37af6278@postgresql.org> <9ef787c5-62a9-c0a3-6261-5ef4863c78d3@postgresql.org> In-Reply-To: --fW40GmieOfEjPw8lqCUSUGAyl1LZbVWwq Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: quoted-printable 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. >=20 > 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. >=20 >> Oh, and=C2=A0+1 for doing the same for products.=C2=A0 >=20 > +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. 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 --fW40GmieOfEjPw8lqCUSUGAyl1LZbVWwq-- --smUVKTzBLpg50lXvdD7hcESJCuy4fgMZX Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAEBCAAdFiEE+oS2la8r95ogZD/x8QSccp8cZScFAlzD0PEACgkQ8QSccp8c ZScmew//fyJKX4L0Cued+ktaFrsJ0IEkNvKo51u6AmtmiFBbXSlKB14vKjtGcjto Pr9clQAPwbIDTu0WYQ64mNTqHSTWeu/9jq4f1XSNKXgrL2R+lJlm50RcanqmZ4QG j9YIHCIGaV0XOVHKlwLNZc/XD2VtHweaPmzhsg6QG2vvfPbQHiZdanEYUEiJ6SdD BahSd/54m2JSXs2rBJiOOUP8YF7SKYqvHeUy2EvghV0tYtnJBYKRJ9+46NBO+7Nx BKcKoHtlyVbi19c47MYSWT04B3znPj07ycC/wJJDRNgNEX7TzrBnMZ+sOdRpf3if Rri8B/zv7SSInXx5EGlc4BZLT2aYPBoieTlHggoHCr1cgJWW0WJN9+cBDhWtSNvu dp4fbj1HwtyfXe12GE867+1hkvKHdSbmLB/Uvkgg4XdwOj6KK5giQD29grHi72+F 6jr6PywPzdCLAPoJGkabx+jrEMLryex8I/4Qq4FPPKCIuXOqhmRsgUA8k0roFxuG j+eHdSLOORkuLRtWJSLiM4FFy1qDad617jj6qyIr48JSJDqJMUihw8avAQ5NF/qn Zxyh9JCa/AYv74c9MJK24LQQON4WXZ2y275Zgo5o4U20R0XBQO4j0Tynb6ybJLtY HU01NuF7d6yRCtEhoS3vNebd53KR7WK+Whz8H1d1oJ299DoI6tU= =6Klf -----END PGP SIGNATURE----- --smUVKTzBLpg50lXvdD7hcESJCuy4fgMZX--