Core Extensions relocation

Hello Greg, hello All,

This is my first post at Hackers, so sorry if I am been a noob here, but I
am pretty confused about
how to create the extension pg_buffercache.

First of all, I was trying to create using the old method by calling the
pg_buffercache--1.0.sql directly.
Then I discover the change that occurs recently to use CREATE EXTENSION, but
even now I am getting the weird error:

# select * from pg_available_extensions;
name | default_version | installed_version | comment
----------------+-----------------+-------------------+---------------------------------
plpgsql | 1.0 | 1.0 | PL/pgSQL
procedural language
pg_buffercache | 1.0 | | examine the
shared buffer cache
(2 rows)

postgres=# CREATE EXTENSION pg_buffercache SCHEMA pg_catalog;
ERROR: syntax error at or near "NO"

Right now, talking with some fellows at #postgresql they tell that the error
is NOT occurring for they.
This was about 9.1beta from git.

But even so, I need to ask, because my production is on another versions:

What is the right way to install this contrib at 9.0.1, 9.0.2 and 9.0.4 ?

Many thanks,

Best regards,
vinnix

On Thu, Jun 9, 2011 at 1:14 AM, Greg Smith <greg@2ndquadrant.com> wrote:

Following up on the idea we've been exploring for making some extensions
more prominent, attached is the first rev that I think may be worth
considering seriously. Main improvement from the last is that I reorganized
the docs to break out what I decided to tentatively name "Core Extensions"
into their own chapter. No longer mixed in with the rest of the contrib
modules, and I introduce them a bit differently. If you want to take a
quick look at the new page, I copied it to
http://www.2ndquadrant.us/docs/html/extensions.html

I'm not completely happy on the wordering there yet. The use of both
"modules" and "extensions" is probably worth eliminating, and maybe that
continues on to doing that against the language I swiped from the contrib
intro too. There's also a lot of shared text at the end there, common
wording from that and the contrib page about how to install and migrate
these extensions. Not sure how to refactor it out into another section
cleanly though.

Regression tests came up last time I posted this. Doesn't look like there
are any for the modules I'm suggesting should be promoted. Only code issue
I noticed during another self-review here is that I didn't rename
contrib/pgrowlocks/pgrowlocks--unpackaged--1.0.sql cleanly, may need to do
that one over again to get the commits as clean as possible.

Updated code is at
https://github.com/greg2ndQuadrant/postgres/tree/move-contrib too, and
since this is painful as a patch the compare view at
https://github.com/greg2ndQuadrant/postgres/compare/master...move-contribwill be easier for browsing the code changes.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--

Vinícius Abrahão Bazana Schmidt
Desenvolvimento
Dextra Sistemas
www.dextra.com.br
+55 19 3256-6722 Ramal 246

Este email é confidencial. Mais informações em:
This message is confidential. More information at:
www.dextra.com.br/confidencial.htm

--
vi[nnix]™
aka: Vinícius Abrahão Bazana Schmidt
vischmidt.wordpress.com
twitter.com/vischmidt

Vinicius Abrahao wrote:

This is my first post at Hackers, so sorry if I am been a noob here,
but I am pretty confused about
how to create the extension pg_buffercache.

This list is for talking about development of new features, normally on
the latest development version of the software (right now 9.1). There
is no such thing as CREATE EXTENSION in versions before that. A
question like "how do I install pg_buffercache for 9.0?" should normally
get sent to one of the other mailing lists; any of pgsql-performance,
pgsql-admin, or pgsql-general would be appropriate to ask that at. This
one really isn't. It's also better to avoid taking someone else's
discussion and replying to it with your questions.

But even so, I need to ask, because my production is on another versions:
What is the right way to install this contrib at 9.0.1, 9.0.2 and 9.0.4 ?

But since I happen to know this answer, here's an example from a RedHat
derived Linux system running PostgreSQL 9.0.4, logged in as the postgres
user:

-bash-3.2$ locate pg_buffercache.sql
/usr/pgsql-9.0/share/contrib/pg_buffercache.sql
/usr/pgsql-9.0/share/contrib/uninstall_pg_buffercache.sql
-bash-3.2$ psql -d pgbench -f
/usr/pgsql-9.0/share/contrib/pg_buffercache.sql
SET
CREATE FUNCTION
CREATE VIEW
REVOKE
REVOKE
-bash-3.2$ psql -d pgbench -c "select count(*) from pg_buffercache"
count
-------
4096

The location of the file will be different on other platforms, but
that's the basic idea of how you install it.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

Please do not piggyback on an unrelated thread to ask a question.
Start a new thread.

Vinicius Abrahao <vinnix.bsd@gmail.com> writes:

postgres=# CREATE EXTENSION pg_buffercache SCHEMA pg_catalog;
ERROR: syntax error at or near "NO"

This looks like a syntax error in the pg_buffercache--1.0.sql file ...
have you tampered with that at all?

I believe BTW that you cannot specify pg_catalog as the target schema
here. When I try that, I get:

regression=# CREATE EXTENSION pg_buffercache SCHEMA pg_catalog;
ERROR: permission denied to create "pg_catalog.pg_buffercache"
DETAIL: System catalog modifications are currently disallowed.

but it goes through fine without the SCHEMA clause.

regards, tom lane

Hi,

Greg Smith <greg@2ndquadrant.com> writes:

Following up on the idea we've been exploring for making some extensions
more prominent, attached is the first rev that I think may be worth
considering seriously. Main improvement from the last is that I reorganized
the docs to break out what I decided to tentatively name "Core Extensions"
into their own chapter. No longer mixed in with the rest of the contrib
modules, and I introduce them a bit differently. If you want to take a
quick look at the new page, I copied it to
http://www.2ndquadrant.us/docs/html/extensions.html

Thanks a lot for working on this!

I have two remarks here. First, I think that the “core extensions” (+1
for this naming) should not be found in a documentation appendix, but in
the main documentation, as a new Chapter in Part II where we list data
types and operators and system functions. Between current chapters 9
and 10 would be my vote.

Then, I think the angle to use to present “core extensions” would be
that those are things maintained like the core server, but that you
might not need at all. There's no SQL level feature that rely on them,
it's all “extra”, but it's there nonetheless, because you might need it.

Other than that, +1 for your patch. I'd stress out that I support your
idea to split the extensions at the source level, I think that's really
helping to get the message out: that is trustworthy and maintained code.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On tor, 2011-06-09 at 00:14 -0400, Greg Smith wrote:

Following up on the idea we've been exploring for making some
extensions
more prominent, attached is the first rev that I think may be worth
considering seriously. Main improvement from the last is that I
reorganized the docs to break out what I decided to tentatively name
"Core Extensions" into their own chapter. No longer mixed in with
the
rest of the contrib modules, and I introduce them a bit
differently.

For the directory name, I'd prefer either src/extensions (since there is
more than one), or if you want to go for short somehow, src/ext. (Hmm,
I guess the installation subdirectory is also called "extension". But
it felt wrong on first reading anyway.)

There is some funny business in your new src/extension/Makefile. You
apparently based this on a very old version of contrib/Makefile (if
still contains a CVS keyword header), it uses for loops in make targets
after we just got rid of them, and it references some modules that
aren't there at all. That file needs a complete redo based on current
sources, I think.

Equally, your new extension-global.mk sets MODULEDIR, which is no longer
necessary, and has a CVS header. What version did you branch this
off? :)

Perhaps a small addition to the installation instructions would also be
appropriate, to tell people that certain core extensions, as it were,
are installed by default.

Peter Eisentraut wrote:

For the directory name, I'd prefer either src/extensions (since there is
more than one), or if you want to go for short somehow, src/ext. (Hmm,
I guess the installation subdirectory is also called "extension". But
it felt wrong on first reading anyway.)

I jumped between those two a couple of times myself, settling on
"extension" to match the installation location as you figured out.
Assuming that name shouldn't change at this point, this seemed the best
way to name the new directory, even though I agree it seems weird at first.

What version did you branch this off? :)

Long enough ago that apparently I've missed some major changes; Magnus
already pointed out I needed to revisit how MODULEDIR was used. Looks
like I need to rebuild the first patch in this series yet again, which
shouldn't be too bad. The second time I did that, I made the commits
atomic enough that the inevitable third one would be easy.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

On Sat, Jun 11, 2011 at 12:38 PM, Greg Smith <greg@2ndquadrant.com> wrote:

Peter Eisentraut wrote:

For the directory name, I'd prefer either src/extensions (since there is
more than one), or if you want to go for short somehow, src/ext.  (Hmm,
I guess the installation subdirectory is also called "extension".  But
it felt wrong on first reading anyway.)

I jumped between those two a couple of times myself, settling on "extension"
to match the installation location as you figured out.  Assuming that name
shouldn't change at this point, this seemed the best way to name the new
directory, even though I agree it seems weird at first.

What version did you branch this off? :)

Long enough ago that apparently I've missed some major changes; Magnus
already pointed out I needed to revisit how MODULEDIR was used.  Looks like
I need to rebuild the first patch in this series yet again, which shouldn't
be too bad.  The second time I did that, I made the commits atomic enough
that the inevitable third one would be easy.

Are you going to do this work for this CommitFest?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Is this going to be done for 9.2?

---------------------------------------------------------------------------

Greg Smith wrote:

Following up on the idea we've been exploring for making some extensions
more prominent, attached is the first rev that I think may be worth
considering seriously. Main improvement from the last is that I
reorganized the docs to break out what I decided to tentatively name
"Core Extensions" into their own chapter. No longer mixed in with the
rest of the contrib modules, and I introduce them a bit differently.
If you want to take a quick look at the new page, I copied it to
http://www.2ndquadrant.us/docs/html/extensions.html

I'm not completely happy on the wordering there yet. The use of both
"modules" and "extensions" is probably worth eliminating, and maybe that
continues on to doing that against the language I swiped from the
contrib intro too. There's also a lot of shared text at the end there,
common wording from that and the contrib page about how to install and
migrate these extensions. Not sure how to refactor it out into another
section cleanly though.

Regression tests came up last time I posted this. Doesn't look like
there are any for the modules I'm suggesting should be promoted. Only
code issue I noticed during another self-review here is that I didn't
rename contrib/pgrowlocks/pgrowlocks--unpackaged--1.0.sql cleanly, may
need to do that one over again to get the commits as clean as possible.

Updated code is at
https://github.com/greg2ndQuadrant/postgres/tree/move-contrib too, and
since this is painful as a patch the compare view at
https://github.com/greg2ndQuadrant/postgres/compare/master...move-contrib
will be easier for browsing the code changes.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 14 October 2011 17:48, Bruce Momjian <bruce@momjian.us> wrote:

Is this going to be done for 9.2?

---------------------------------------------------------------------------

I didn't spot this thread before. I posted something related
yesterday: http://archives.postgresql.org/pgsql-hackers/2011-10/msg00781.php

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 10/14/2011 01:48 PM, Bruce Momjian wrote:

Is this going to be done for 9.2?

Refreshing this patch is on my list of things to finish before the next
CommitFest starts later this month.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

On 11/2/11 8:25 AM, Greg Smith wrote:

On 10/14/2011 01:48 PM, Bruce Momjian wrote:

Is this going to be done for 9.2?

Refreshing this patch is on my list of things to finish before the next
CommitFest starts later this month.

Put me down as reviewer.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

I've revived the corpose of the patch submitted in May, now that it's a
much less strange time of the development cycle to consider it.
http://archives.postgresql.org/message-id/4DF048BD.8040302@2ndquadrant.com
was the first attempt to move some extensions from contrib/ to a new
src/extension/ directory. I have fixed the main complaints from the
last submit attempt, that I accidentally grabbed some old makesfiles and
CVS junk. The new attempt is attached, and is easiest to follow with
the a diff view that understands "moved a file", like github's:
https://github.com/greg2ndQuadrant/postgres/compare/master...core-extensions

You can also check out the docs changes done so far at
http://www.highperfpostgres.com/docs/html/extensions.html I reorganized
the docs to break out what I decided to tentatively name "Core
Extensions" into their own chapter. They're no longer mixed in with the
rest of the contrib modules, and I introduce them a bit differently.
I'm not completely happy on the wordering there yet. The use of both
"modules" and "extensions" is probably worth eliminating, and maybe that
continues on to doing that against the language I swiped from the
contrib intro too. There's also a lot of shared text at the end there,
common wording from that and the contrib page about how to install and
migrate these extensions. Not sure how to refactor it out into another
section cleanly though.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

On 14 November 2011 09:08, Greg Smith <greg@2ndquadrant.com> wrote:

I've revived the corpose of the patch submitted in May, now that it's a much
less strange time of the development cycle to consider it.
 http://archives.postgresql.org/message-id/4DF048BD.8040302@2ndquadrant.com
was the first attempt to move some extensions from contrib/ to a new
src/extension/ directory.  I have fixed the main complaints from the last
submit attempt, that I accidentally grabbed some old makesfiles and CVS
junk.  The new attempt is attached, and is easiest to follow with the a diff
view that understands "moved a file", like github's:
 https://github.com/greg2ndQuadrant/postgres/compare/master...core-extensions

You can also check out the docs changes done so far at
http://www.highperfpostgres.com/docs/html/extensions.html  I reorganized the
docs to break out what I decided to tentatively name "Core Extensions" into
their own chapter.  They're no longer mixed in with the rest of the contrib
modules, and I introduce them a bit differently.  I'm not completely happy
on the wordering there yet.  The use of both "modules" and "extensions" is
probably worth eliminating, and maybe that continues on to doing that
against the language I swiped from the contrib intro too.  There's also a
lot of shared text at the end there, common wording from that and the
contrib page about how to install and migrate these extensions.  Not sure
how to refactor it out into another section cleanly though.

I'm all for removing all mention of "modules". It's ambiguous and
used inconsistently.

In my previous post in this area
(http://archives.postgresql.org/pgsql-hackers/2011-10/msg00781.php) I
suggested that bundling tools, libraries and extensions together in
the same category is confusing. So those are still a problem for me.
And auto_explain appears in your new "Core Extensions" section, but
it's not an extension in the terminology PostgreSQL uses, so that's
also potentially confusing.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Thom Brown <thom@linux.com> writes:

I'm all for removing all mention of "modules". It's ambiguous and
used inconsistently.

The module is the shared library object. It should be possible to use
that consistently. And I have some plans on my TODO list about them
anyway, so making them disappear from the manual would not serve my
later plans :)

And auto_explain appears in your new "Core Extensions" section, but
it's not an extension in the terminology PostgreSQL uses, so that's
also potentially confusing.

This is a related problem, we should have a terminology for contrib
tools such as pg_standby or pg_archivecleanup, for modules like the one
you talk about, that provide new features but nothing visible from SQL,
and extensions, that are all about SQL --- and if I can work on my plans
will get even more about SQL in a near future.

It's too late for me today to contribute nice ideas here though.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

This is a related problem, we should have a terminology for contrib
tools such as pg_standby or pg_archivecleanup, for modules like the one
you talk about, that provide new features but nothing visible from SQL,
and extensions, that are all about SQL --- and if I can work on my plans
will get even more about SQL in a near future.

I see nothing wrong with "Tools" and "Extensions". I'm not sure that
having one catch-all name for them serves the user.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Greg,

So I'm a bit unclear on why most of the optional data types were
excluded from your list of Core Extensions. I would regard the
following as stable and of general utility:

btree_gin
btree_gist
citext
dblink
file_fdw
fuzzystrmatch
hstore
intarray
isn
ltree
pgcrypto
pg_trgm
unaccent
uuid-ossp

These should, in my opinion, all be Core Extensions. I'd go further to
say that if something is materially an extension (as opposed to a tool
or a code example), and we're shipping it with the core distribution, it
either ought to be a core extension, or it should be kicked out to PGXN.

Am I completely misunderstanding what you're trying to accomplish here?

... also, why is there still a "tsearch2" contrib module around at all?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 15 November 2011 00:56, Josh Berkus <josh@agliodbs.com> wrote:

Greg,

So I'm a bit unclear on why most of the optional data types were
excluded from your list of Core Extensions.  I would regard the
following as stable and of general utility:

btree_gin
btree_gist
citext
dblink
file_fdw
fuzzystrmatch
hstore
intarray
isn
ltree
pgcrypto
pg_trgm
unaccent
uuid-ossp

Greg clarified on the core extensions page text:

"These core extensions supply useful features in areas such as
database diagnostics and performance monitoring."

None of those others perform such a role. Instead they add additional
functionality intended to be utilised as part of general data usage,
adding new types, operators, query functions etc. Maybe the term
"core" is inappropriate. Instead we might wish to refer to them as
"utility extensions" or something like that, although that may be just
as vague.

... also, why is there still a "tsearch2" contrib module around at all?

Backwards compatibility. No-one will use it except if they're coming
from an older version.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 15 November 2011 00:56, Josh Berkus <josh@agliodbs.com> wrote:

So I'm a bit unclear on why most of the optional data types were
excluded from your list of Core Extensions.  I would regard the
following as stable and of general utility:

isn

I consider contrib/isn to be quite broken. It hard codes ISBN prefixes
for the purposes of sanitising ISBNs, even though their assignment is
actually controlled by a decentralised body of regional authorities.
I'd vote for kicking it out of contrib.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On 11/14/2011 07:56 PM, Josh Berkus wrote:

So I'm a bit unclear on why most of the optional data types were
excluded from your list of Core Extensions.

I was aiming for the extensions that seemed uncontroversial for a first
pass here. One of the tests I applied was "do people sometimes need
this module after going into production with their application?" The
very specific problem I was most concerned about eliminating was people
discovering they needed an extension to troubleshoot performance or
corruption issues, only to discover it wasn't available--because they
hadn't installed the postgresql-contrib package. New package
installation can be a giant pain to get onto a production system in some
places, if it wasn't there during QA etc.

All of the data type extensions fail that test. If you need one of
those, you would have discovered that on your development server, and
made sure the contrib package was available on production too. There
very well may be some types that should be rolled into the core
extensions list, but I didn't want arguments over that to block moving
forward with the set I did suggest. We can always move more of them
later, if this general approach is accepted. It only takes about 5
minutes per extension to move them from contrib to src/extension, once
the new directory tree and doc section is there. But I didn't want to
do the work of moving another 15 of them if the whole idea was going to
get shot down.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us