Thread: Let's make CPgAN!

The idea that came up in the -hackers and -advocacy lists, and I think
it should be brought up as a separate thread, in -general.

 Backgroud

Once in a while someone comes and suggests adding some package to
postgresql-contrib.  Some other person asks for some feature and is directed
at Pgfoundry. Yet again, someone submits a package and is usually directed
to Pgfoundry to put it there.

While it is a great place to keep development there, but not so obvious a place
for random administrator/DBA/programmer...  So the idea is to make something
akin to:

 Comrehensive PostgreSQL Archive Network, or CPgAN

I would like to provoke a discussion how should such a thing look like,
and hopefully to make one.

First, I think it should be pretty intergrated into PostgreSQL, and should
by distribution agnostic.  From user perspective it should be similar to
createuser, createdb, createlang commands.  ie. user would call a shell
command, say:
  pg_package dbname install name_of_package
and it would connect with cpgan, get list of packages, get sources,
compile sources (if C-backed), maintain dependencies, etc.  Or at
least it should.

It's better to talk with an example.  Let's assume the issn/isbn datatype
would be handled by that infrastructure.

If DBA would  want to install it from source, she would use this pg_package
(or whatever the name) utility to download it, unpack, compile and install
generated lib...so file.  It would also install, in some common place a sql
script which would install and uninstall the package.  DBA should be able
to specify if package is available to all users or not (grant its usage), but
it is not essential right now.  Command should be able to handle binary
packages well.  Think: Linux distributions etc -- they would put all files
in place, then call pg_package to "notify" PostgreSQL about new additon.

The second part is "enabling" the usage within given database.  A simple
running of bunch "CREATE" commands on given database.  Plus a matching
deinstall script.

This raises couple of questions:

1. CREATE PACKAGE -- maybe its time to rethink such a command. :)

2. Where to store state information.  A natural place would be a database
itself.  A dedicated cpgan db with all dependencies?  A bit fragile solution,
and much of managament will become cross-database (to install "PL/R"
to template1 db you would need to connect both to cpgan and template1,
but it may work.

3. There would be a need to automate the server side as possible.  User
should be able to use it to search for package she desires. And developers
should be able to efficiently upload packages.

4. A "tree" of packages, PgFoundry's is too general I think.  For instance
I feel that PgAdmin and other DB Administration projects would not belong
here, as they are rather interfaces to than extensions of. :)  Say, a tree
with such a look:

pl/
pl/plr
pl/plperlng
replication/slony
datatype/uri
datatype/email
index/ltree

...of course its not complete and not ideal.  I wnat to know how would
you organise such a tree.

5. A common documentation format.  It would be great to be able to automatically
"merge in" documentation of extensions into PostgreSQL's Manual as a separate
Appendix.  Right now its somewhere between Use The Source, Luke, README
files and what-author-provided documentation format.

What would this all give us?  It would encourage development of simple
extensions,
and would greatly increase the ease of extending PostgreSQL by newbies.  In the
long run it would bring more developers, as PostgreSQL would be perceived not
only as a "SQL DB" but also as a development platform, extensible and powerful.

What do you think?

  Regards,
      Dawid

I think the implementation of postgresql installable packages (and
package-space) should precede this idea. Then, any package management
system can install the packages.

On May 20, 2006, at 2:12 PM, Dawid Kuroczko wrote:

> Comrehensive PostgreSQL Archive Network, or CPgAN

¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@themactionfaction.com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬

I am not sure that Postgres needs CPAN. CPAN is particularly useful for
handling dependencies. I doubt that there will be lots of dependencies in
Postgres add ons. So having something like the current system where you
download and build packages from source isn't going to be improved much
with a CPAN like system.

On 5/20/06, Bruno Wolff III <bruno@wolff.to> wrote:
> I am not sure that Postgres needs CPAN. CPAN is particularly useful for
> handling dependencies. I doubt that there will be lots of dependencies in
> Postgres add ons. So having something like the current system where you
> download and build packages from source isn't going to be improved much
> with a CPAN like system.

Hmm, I was thinking rather it would be more a definitive system to look
for PgSQL extenstions and a way to encourage distributors to package
the extensions together with "mainstream" PostgreSQL. :)

   Regards,
       Dawid

On Sat, 20 May 2006, Bruno Wolff III wrote:

> I am not sure that Postgres needs CPAN. CPAN is particularly useful for
> handling dependencies. I doubt that there will be lots of dependencies in
> Postgres add ons. So having something like the current system where you
> download and build packages from source isn't going to be improved much
> with a CPAN like system.
>

Perhaps not, but I haven't found the Postgres site pushing or linking to
projects like PostGIS or PL/R, which provide some very effective Postgres
based power.

If not CPAN-like, how about CRAN (joking :-)...

Postgres is the common denominator & foundation for several commercial &
OS packages, and highlighting these on the Postgres site would be useful
for Postgres users looking to extend Postgres functionality.


Brent Wood

Agent M wrote:
> I think the implementation of postgresql installable packages (and
> package-space) should precede this idea. Then, any package management
> system can install the packages.

Having a standardizes package management for postgresql would be great.
I believe one could use schemas to encapsulate installed packages, but
maybe some backend support would be necessary for dependency tracking.

Oracle seems to support some kind of "package" concept - does anyone know
any details?

greetings, Florian Pflug

Hi Florian :

Oracle has packages.

Packages are collections of functions, procedures, variables, constants,
ect, put all together.

You has two commands

CREATE PACKAGES, CREATE PACKAGES BODY.

CREATE PACKAGES this creates the definations, list of components, and the
name.

CREATE PACKAGES BODY is components defination. Components code.

You can have information global to packages, is this, share information
between function and other components via global variables or constants.

Is usefull to make a packages for manage logic of a system. In place of
create function, procedures, etc, all individual.
You can put all logic together.

Oracle has many prefined packages, to make most commonly task.

Alejandro Michelin Salomon
Porto Alegre
Brasil


-->-----Mensagem original-----
-->De: pgsql-general-owner@postgresql.org
-->[mailto:pgsql-general-owner@postgresql.org] Em nome de
-->Florian G. Pflug
-->Enviada em: segunda-feira, 22 de maio de 2006 12:39
-->Para: Agent M
-->Cc: Postgres general mailing list
-->Assunto: Re: [GENERAL] Let's make CPgAN!
-->
-->
-->Agent M wrote:
-->> I think the implementation of postgresql installable packages (and
-->> package-space) should precede this idea. Then, any package
-->management
-->> system can install the packages.
-->
-->Having a standardizes package management for postgresql
-->would be great. I believe one could use schemas to
-->encapsulate installed packages, but maybe some backend
-->support would be necessary for dependency tracking.
-->
-->Oracle seems to support some kind of "package" concept -
-->does anyone know any details?
-->
-->greetings, Florian Pflug
-->
-->
-->---------------------------(end of
-->broadcast)---------------------------
-->TIP 5: don't forget to increase your free space map settings
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.392 / Virus Database: 268.7.0/345 - Release
-->Date: 22/5/2006
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.392 / Virus Database: 268.7.0/345 - Release
-->Date: 22/5/2006
-->
-->

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.7.0/345 - Release Date: 22/5/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.7.0/345 - Release Date: 22/5/2006

This issue is a very old issue and people have not come up with
the definitive solution to distributing "datablades" as Stonebraker
called them.

For now, the best way is to have small well managed projects on
PgFoundry and encourage people to review what is available
there.  Maybe we can even make perusing the projects there
easier.

As people build smaller projects on pgfoundry is would be
ideal to have "blessed" projects where "blessing" means
adhered to an installation procedure.

There is already a standard way to add things into the contrib
line (but I'm blanking on the name xpg?).  All addons require
some SQL and most add-ons only require SQL.  But those that
require C should adhere to the xpg (?) standard.  Those that
require other languages installed should have the mechanisms built
in to at minimum flag those dependencies.  For example if a new
datatype depended on Perl6, it must check that Perl6 is installed
before actually installing itself.

We have a lot of little solutions and none of them are that
far away from each other.  I believe the trend is already to use
"what worked for that other project".  The issue then is to
encourage projects on pgfoundry to discover the exact installation
standard and promote it.  The individual project owners will
be required to implement them.

Another aspect is that little code clips, from my site and from
previous published papers and/or presentations should probably
be collected somehow.  I think this may be a different class
of object, though.  I already have a web page of these and it
surely can be added to (feel free to recommend some!).  But the
installation of these fragments is usually cut and paste.

--elein
elein@varlena.com

On Mon, May 22, 2006 at 05:38:35PM +0200, Florian G. Pflug wrote:
> Agent M wrote:
> >I think the implementation of postgresql installable packages (and
> >package-space) should precede this idea. Then, any package management
> >system can install the packages.
>
> Having a standardizes package management for postgresql would be great.
> I believe one could use schemas to encapsulate installed packages, but
> maybe some backend support would be necessary for dependency tracking.
>
> Oracle seems to support some kind of "package" concept - does anyone know
> any details?
>
> greetings, Florian Pflug
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

elein wrote:
> This issue is a very old issue and people have not come up with
> the definitive solution to distributing "datablades" as Stonebraker
> called them.
True, but OTOH there is no "definitive solution" for OS-level package
management too, but still "apt-get" or "rpm" do a pretty decent job.
So, 99% solutions are possible ;-)

> For now, the best way is to have small well managed projects on
> PgFoundry and encourage people to review what is available
> there.  Maybe we can even make perusing the projects there
> easier.
>
> As people build smaller projects on pgfoundry is would be
> ideal to have "blessed" projects where "blessing" means
> adhered to an installation procedure.
The problem is not such much the initial installation, but rather
upgrade to new versions of either the "package", or postgresql.

> There is already a standard way to add things into the contrib
> line (but I'm blanking on the name xpg?).  All addons require
> some SQL and most add-ons only require SQL.  But those that
> require C should adhere to the xpg (?) standard.  Those that
> require other languages installed should have the mechanisms built
> in to at minimum flag those dependencies.  For example if a new
> datatype depended on Perl6, it must check that Perl6 is installed
> before actually installing itself.

> We have a lot of little solutions and none of them are that
> far away from each other.  I believe the trend is already to use
> "what worked for that other project".  The issue then is to
> encourage projects on pgfoundry to discover the exact installation
> standard and promote it.  The individual project owners will
> be required to implement them.
I'd really like to see a solution that enables me to install
a package using something like "pgpkg install <dbname> <package>".
I'd ask me to install prerequisites (like procedural languages
for example). pg_dump should have an option to exclude any installed
packages from the dump.

As long as "packages" only contain functions, views and types, things
are quite straight forward, I believe. The hard part would be to support
packages including table-definitions. What do you do when an upgrade
wants to modify a table, but it already contains user data?

greetings, Florian Pflug

On Mon, May 22, 2006 at 08:10:11PM +0200, Florian G. Pflug wrote:
> elein wrote:
> >This issue is a very old issue and people have not come up with
> >the definitive solution to distributing "datablades" as Stonebraker
> >called them.
> True, but OTOH there is no "definitive solution" for OS-level package
> management too, but still "apt-get" or "rpm" do a pretty decent job.
> So, 99% solutions are possible ;-)
>

Theoretically if we have our own packaging system we can nudge the
package developers to want to use it.  Not everyone will, hence
the "blessed" projects.

> >For now, the best way is to have small well managed projects on
> >PgFoundry and encourage people to review what is available
> >there.  Maybe we can even make perusing the projects there
> >easier.
> >
> >As people build smaller projects on pgfoundry is would be
> >ideal to have "blessed" projects where "blessing" means
> >adhered to an installation procedure.
> The problem is not such much the initial installation, but rather
> upgrade to new versions of either the "package", or postgresql.
>

The developer should be responsible for for the upgrade processes
and it should be incorporated into the scripts that "pgpkg install"
runs.


> >There is already a standard way to add things into the contrib
> >line (but I'm blanking on the name xpg?).  All addons require
> >some SQL and most add-ons only require SQL.  But those that
> >require C should adhere to the xpg (?) standard.  Those that
> >require other languages installed should have the mechanisms built
> >in to at minimum flag those dependencies.  For example if a new
> >datatype depended on Perl6, it must check that Perl6 is installed
> >before actually installing itself.
>
> >We have a lot of little solutions and none of them are that
> >far away from each other.  I believe the trend is already to use
> >"what worked for that other project".  The issue then is to
> >encourage projects on pgfoundry to discover the exact installation
> >standard and promote it.  The individual project owners will
> >be required to implement them.
> I'd really like to see a solution that enables me to install
> a package using something like "pgpkg install <dbname> <package>".
> I'd ask me to install prerequisites (like procedural languages
> for example). pg_dump should have an option to exclude any installed
> packages from the dump.

Having that framework requires developers to adhere to the installation
requirements.  That was my first requirement and it is also a big
burden.  Those requirements should include all upgrade specific tasks
as well as uninstall.  Only the project owner knows exactly what those
dependencies would be.

First things first--the install infrastructure needs to be agreed upon.
This is a difficult task.  That is why I suggested the basics of
1) executing SQL 2) using xpg(?) for C languages 3) determining
other dependencies.  We've got to start somewhere.

>
> As long as "packages" only contain functions, views and types, things
> are quite straight forward, I believe. The hard part would be to support
> packages including table-definitions.

>What do you do when an upgrade
> wants to modify a table, but it already contains user data?

If that case is possible and the developer did not take that into account
for upgrade the it is a bug.  The sql in the installation should handle this case.
The developer decides what is necessary.

>
> greetings, Florian Pflug
>

Carry on!

elein
elein@varlena.com

On 5/22/06, Florian G. Pflug <fgp@phlo.org> wrote:
> elein wrote:
> > This issue is a very old issue and people have not come up with
> > the definitive solution to distributing "datablades" as Stonebraker
> > called them.
> True, but OTOH there is no "definitive solution" for OS-level package
> management too, but still "apt-get" or "rpm" do a pretty decent job.
> So, 99% solutions are possible ;-)

Yet a RPM/DPKG/whatever will only make a collection-wide installation,
or rather preparation of package.  Think: PLpgSQL.  It is installed by
default.  But to use it, you have to actually createlang it into your
specific database.

I think the "CPgAN" should aim at the latter (managament of "packages"
throughout whole PostgreSQL collection) and help with the former
(make it easy to rpmify/dbmify/whateverify the package; help with
raw source-installation/update) at the same time.  It is what C*ANs
do to other projects. :)

> I'd really like to see a solution that enables me to install
> a package using something like "pgpkg install <dbname> <package>".
> I'd ask me to install prerequisites (like procedural languages
> for example). pg_dump should have an option to exclude any installed
> packages from the dump.
>
> As long as "packages" only contain functions, views and types, things
> are quite straight forward, I believe. The hard part would be to support
> packages including table-definitions. What do you do when an upgrade
> wants to modify a table, but it already contains user data?

Tricky.  Ideally it should either upgrade it, if possible, or fail
with some hints how to update the structure manually.
And it can happen to functions views (think views depending
on  views) and types also.

   Regards,
       Dawid

Dawid Kuroczko wrote:
> On 5/22/06, Florian G. Pflug <fgp@phlo.org> wrote:
>> elein wrote:
>> > This issue is a very old issue and people have not come up with
>> > the definitive solution to distributing "datablades" as Stonebraker
>> > called them.
>> True, but OTOH there is no "definitive solution" for OS-level package
>> management too, but still "apt-get" or "rpm" do a pretty decent job.
>> So, 99% solutions are possible ;-)
>
> Yet a RPM/DPKG/whatever will only make a collection-wide installation,
> or rather preparation of package.  Think: PLpgSQL.  It is installed by
> default.  But to use it, you have to actually createlang it into your
> specific database.
I didn't suggest using apt-get/rpm/whatever, I was just trying to say
that while now 100% perfect solution for package management exists (be
it for databases or for operating systems), there are still quite good
solutions, which work "in the real world".

> I think the "CPgAN" should aim at the latter (managament of "packages"
> throughout whole PostgreSQL collection) and help with the former
> (make it easy to rpmify/dbmify/whateverify the package; help with
> raw source-installation/update) at the same time.  It is what C*ANs
> do to other projects. :)
full ack.

>> I'd really like to see a solution that enables me to install
>> a package using something like "pgpkg install <dbname> <package>".
>> I'd ask me to install prerequisites (like procedural languages
>> for example). pg_dump should have an option to exclude any installed
>> packages from the dump.
>>
>> As long as "packages" only contain functions, views and types, things
>> are quite straight forward, I believe. The hard part would be to support
>> packages including table-definitions. What do you do when an upgrade
>> wants to modify a table, but it already contains user data?
>
> Tricky.  Ideally it should either upgrade it, if possible, or fail
> with some hints how to update the structure manually.
> And it can happen to functions views (think views depending
> on  views) and types also.
If the package contains only non-user-modifyable objects, then one
could "cheat" by dropping the whole schema the package lives in, and
recreating it from scratch.

But of course this has quite a few downsides - it would make it impossible
for user of packages to use types or views the package provides.

greetings, Florian Pflug

On Mon, May 22, 2006 at 09:41:10AM +1200, Brent Wood wrote:
>
>
> On Sat, 20 May 2006, Bruno Wolff III wrote:
>
> > I am not sure that Postgres needs CPAN. CPAN is particularly useful for
> > handling dependencies. I doubt that there will be lots of dependencies in
> > Postgres add ons. So having something like the current system where you
> > download and build packages from source isn't going to be improved much
> > with a CPAN like system.
> >
>
> Perhaps not, but I haven't found the Postgres site pushing or linking to
> projects like PostGIS or PL/R, which provide some very effective Postgres
> based power.

Yeah, I think this is a case of 'if we build it, they will come'.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461