Thread: List of all* PostgreSQL EXTENSIONs in the world

On Thu, Feb 10, 2022 at 3:19 PM Joel Jacobson <joel@compiler.org> wrote:
> I've compiled a list of all* PostgreSQL EXTENSIONs in the world:
>
> https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8aee9d47
>
> *) It's not all, but 1041, compared to the 338 found on PGXN.
>
> Maybe it would be an idea to provide a lightweight solution,
> e.g. maintaining a simple curated list of repo urls,
> published at postgresql.org or wiki.postgresql.org,
> with a simple form allowing missing repos to be suggested for insertion?

I think a list like this is probably not useful without at least a
brief description of each one, and maybe some attempt at
categorization. If I want a PostgreSQL extension to bake tasty
lasagne, I'm not going to go scroll through 1041 entries and hope
something jumps out at me. I'm going to Google "PostgreSQL lasagne
extension" and see if anything promising shows up. But if I had a list
that were organized by category, I might try looking for a relevant
category and then reading the blurbs for each one...

-- 
Robert Haas
EDB: http://www.enterprisedb.com

On 2/10/22 15:35, Robert Haas wrote:
> On Thu, Feb 10, 2022 at 3:19 PM Joel Jacobson <joel@compiler.org> wrote:
>> I've compiled a list of all* PostgreSQL EXTENSIONs in the world:
>>
>> https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8aee9d47
>>
>> *) It's not all, but 1041, compared to the 338 found on PGXN.
>>
>> Maybe it would be an idea to provide a lightweight solution,
>> e.g. maintaining a simple curated list of repo urls,
>> published at postgresql.org or wiki.postgresql.org,
>> with a simple form allowing missing repos to be suggested for insertion?
> 
> I think a list like this is probably not useful without at least a
> brief description of each one, and maybe some attempt at
> categorization. If I want a PostgreSQL extension to bake tasty
> lasagne, I'm not going to go scroll through 1041 entries and hope
> something jumps out at me. I'm going to Google "PostgreSQL lasagne
> extension" and see if anything promising shows up. But if I had a list
> that were organized by category, I might try looking for a relevant
> category and then reading the blurbs for each one...

Agreed.

The example I really like is the R project CRAN "Task Views" for their 
packages (currently the CRAN package repository has 18917 available 
packages):

https://cloud.r-project.org/web/views/

Joe
-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

2022年2月11日(金) 5:19 Joel Jacobson <joel@compiler.org>:
>
> Hi hackers,
>
> I've compiled a list of all* PostgreSQL EXTENSIONs in the world:
>
> https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8aee9d47
>
> *) It's not all, but 1041, compared to the 338 found on PGXN.

More precisely it's a list of all? the repositories with PostgreSQL
extensions on
GitHub?

FWIW I see a few of mine on there, which are indeed PostgreSQL extensions,
but for are also mostly just random, mainly unmaintained non-production-ready
code I've dumped on GitHub in the unlikely event it's of any interest.

The only one I consider of possible general, viable use (and which is available
as part of the community packaging infrastructure) is also listed on PGXN.

OTOH not everything is on GitHub; PostGIS comes to mind.

> Maybe it would be an idea to provide a lightweight solution,
> e.g. maintaining a simple curated list of repo urls,
> published at postgresql.org or wiki.postgresql.org,
> with a simple form allowing missing repos to be suggested for insertion?

The wiki sounds like a good starting point, assuming someone is willing to
create/curate/maintain the list. It would need weeding out of any
extensions which
are inactive/unmaintained, duplicates/copies/forks (e.g. I see three
instances of
blackhole_fdw listed, but not the original repo, which is not even on
GitHub) etc..

Regards

Ian Barwick

--
EnterpriseDB: https://www.enterprisedb.com

Hi,

On Fri, Feb 11, 2022 at 09:22:01AM +0900, Ian Lawrence Barwick wrote:
> 2022年2月11日(金) 5:19 Joel Jacobson <joel@compiler.org>:
> >
> > Hi hackers,
> >
> > I've compiled a list of all* PostgreSQL EXTENSIONs in the world:
> >
> > https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8aee9d47
> >
> > *) It's not all, but 1041, compared to the 338 found on PGXN.
> 
> The only one I consider of possible general, viable use (and which is available
> as part of the community packaging infrastructure) is also listed on PGXN.

Agreed, PGXN is already the official place for that and has all you need to
properly look for what you want AFAICT.

> > Maybe it would be an idea to provide a lightweight solution,
> > e.g. maintaining a simple curated list of repo urls,
> > published at postgresql.org or wiki.postgresql.org,
> > with a simple form allowing missing repos to be suggested for insertion?
> 
> The wiki sounds like a good starting point, assuming someone is willing to
> create/curate/maintain the list. It would need weeding out of any
> extensions which
> are inactive/unmaintained, duplicates/copies/forks (e.g. I see three
> instances of
> blackhole_fdw listed, but not the original repo, which is not even on
> GitHub) etc..

There are already some repositories that tries to gather some curated list of
projects around postgres, which probably already have the same problem with
abandoned or simply moved projects.

The only real solution is to have authors keep publishing and updating their
tools on PGXN.

On Thu, Feb 10, 2022 at 04:04:17PM -0500, Joe Conway wrote:
> On 2/10/22 15:35, Robert Haas wrote:
> >On Thu, Feb 10, 2022 at 3:19 PM Joel Jacobson <joel@compiler.org> wrote:
> >>I've compiled a list of all* PostgreSQL EXTENSIONs in the world:
> >>
> >>https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8aee9d47
> >>
> >>*) It's not all, but 1041, compared to the 338 found on PGXN.

How did you make the list?  (I'd imagine doing it by searching for
repositories containing evidence like \bpgxs\b matches.)

> >>Maybe it would be an idea to provide a lightweight solution,
> >>e.g. maintaining a simple curated list of repo urls,
> >>published at postgresql.org or wiki.postgresql.org,
> >>with a simple form allowing missing repos to be suggested for insertion?
> >
> >I think a list like this is probably not useful without at least a
> >brief description of each one, and maybe some attempt at
> >categorization. If I want a PostgreSQL extension to bake tasty
> >lasagne, I'm not going to go scroll through 1041 entries and hope
> >something jumps out at me. I'm going to Google "PostgreSQL lasagne
> >extension" and see if anything promising shows up. But if I had a list
> >that were organized by category, I might try looking for a relevant
> >category and then reading the blurbs for each one...
> 
> Agreed.

When I change back-branch APIs and ABIs, I use a PGXN snapshot to judge the
scope of affected modules.  Supplementing the search with these git
repositories would help, even without further curation.  I agree curation
would make the list more valuable for other use cases.  Contributing to PGXN
may be the way to go, though.

Hi Joel,

> I've compiled a list of all* PostgreSQL EXTENSIONs in the world:

The list is great. Thanks for doing this!

Just curious, is it a one-time experiment or you are going to keep the
list in an actual state similarly to awesome-* lists on GitHub, or ...
?

-- 
Best regards,
Aleksander Alekseev

On Fri, Feb 11, 2022, at 01:22, Ian Lawrence Barwick wrote:
> More precisely it's a list of all? the repositories with PostgreSQL
> extensions on
> GitHub?

Yes, the ambition is to list all repos at GitHub, and to manually add repos hosted at GitLab and other places.

> OTOH not everything is on GitHub; PostGIS comes to mind.

I've created a new category, "Spatial and Geographic Objects",
and added PostGIS and all other PostGIS-related extensions to it.

(The reason why it wasn't found automatically is because there is no .control-file is the root dir,
and its Makefile didn't contain a PGXS line.)

> The wiki sounds like a good starting point, assuming someone is willing to
> create/curate/maintain the list. It would need weeding out of any
> extensions which
> are inactive/unmaintained, duplicates/copies/forks

I agree, it needs to be curated, lots of noise.
I'm working on it, hopefully others will join in.

> (e.g. I see three
> instances of
> blackhole_fdw listed, but not the original repo, which is not even on
> GitHub) etc..

Thanks, I've fixed blackhole_fdw manually:

- https://bitbucket.org/adunstan/blackhole_fdw
    - https://github.com/chenquanzhao/blackhole_fdw

/Joel

On Fri, Feb 11, 2022, at 04:46, Noah Misch wrote:
> How did you make the list?  (I'd imagine doing it by searching for
> repositories containing evidence like \bpgxs\b matches.)

Searching Github for repos with a *.control file in the root dir and a Makefile containing ^PGXS

Hmm, now that you say it, maybe the ^PGXS regex should be case-insensitive,
if pgxs can be written in e.g. lower case?

/Joel

On 2/23/22 03:52, Joel Jacobson wrote:
>
>> (e.g. I see three
>> instances of
>> blackhole_fdw listed, but not the original repo, which is not even on
>> GitHub) etc..
> Thanks, I've fixed blackhole_fdw manually:
>
> - https://bitbucket.org/adunstan/blackhole_fdw
>     - https://github.com/chenquanzhao/blackhole_fdw
>


Yeah, I have several others on bitbucket that might be of interest (in
varying states of doneness):


A thin layer over the Redis API:

https://bitbucket.org/adunstan/redis_wrapper


Generate a CSV line from a row:

https://bitbucket.org/adunstan/row_to_csv


Closed forms of discrete builtin ranges:

https://bitbucket.org/adunstan/pg-closed-ranges


FDW to generate random data:

https://bitbucket.org/adunstan/rotfang-fdw


Comparison ops for JSON:

https://bitbucket.org/adunstan/jsoncmp



cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On Fri, Feb 11, 2022, at 04:46, Noah Misch wrote:

> How did you make the list?  (I'd imagine doing it by searching for

> repositories containing evidence like \bpgxs\b matches.)

Searching Github for repos with a *.control file in the root dir and a Makefile containing ^PGXS

Hmm, now that you say it, maybe the ^PGXS regex should be case-insensitive,

if pgxs can be written in e.g. lower case?

On 2/23/22 09:33, Euler Taveira wrote:
> On Wed, Feb 23, 2022, at 6:00 AM, Joel Jacobson wrote:
>> On Fri, Feb 11, 2022, at 04:46, Noah Misch wrote:
>> > How did you make the list?  (I'd imagine doing it by searching for
>> > repositories containing evidence like \bpgxs\b matches.)
>>
>> Searching Github for repos with a *.control file in the root dir and a 
>> Makefile containing ^PGXS
> Interesting. What's an extension? It is something that contains user-defined
> objects. It would be good if your list was expanded to contain addons 
> (modules)
> that are basically plugins that don't create additional objects in the 
> database
> e.g. an output plugin or a module that uses any hooks (such as auth_delay).
> They generally don't provide control file (for example, wal2json). I 
> don't know
> if can only rely on PGXS check because there are client programs that 
> uses the
> PGXS infrastructure to build it.
> 
>> Hmm, now that you say it, maybe the ^PGXS regex should be 
>> case-insensitive,
>> if pgxs can be written in e.g. lower case?
> Makefile variable names are case-sensitive. You cannot write pgxs or 
> PgXs; it
> should be PGXS.


What about scanning for "PG_MODULE_MAGIC"?

Joe
-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On 2/23/22 09:52, Aleksander Alekseev wrote:
>  > What about scanning for "PG_MODULE_MAGIC"?
> 
> An extension can be written without using C at all. BTW some extensions 
> [1] are written in Rust these days.

Sure, but scanning for PG_MODULE_MAGIC may well pick up repos that would 
otherwise have been missed. I didn't say that should be the only filter 
used ¯\_(ツ)_/¯

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On Wed, Feb 23, 2022, at 15:23, Andrew Dunstan wrote:
> Yeah, I have several others on bitbucket that might be of interest (in
> varying states of doneness):

Thanks, added.

/Joel