Why not install pgstattuple by default?

On Fri, May 6, 2011 at 00:34, Josh Berkus <josh.berkus@pgexperts.com> wrote:

Hackers,

I've run into a couple of occasions lately where I really wanted
pgstattuple on a production server in order to check table/index bloat.
 However, in the production environment at a large site installing a
contrib module can involve a process which takes days or weeks.

That can be said for a lot of things in contrib. pg_standby in 8.4 for
example. Or adminpack. Or dblink. Or hstore. There's a mix of "example
stuff" and "actually pretty darn useful in production stuff". I'm sure
you can find a couple of hundred emails in the archives on this very
topic.

From 9.1, it'll be a simple CREATE EXTENSION command - so much of the
problem goes away. Well. It doesn't go away, but it gets a lot more
neatly swept under the rug.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Em 06-05-2011 05:06, Magnus Hagander escreveu:

On Fri, May 6, 2011 at 00:34, Josh Berkus<josh.berkus@pgexperts.com> wrote:

Hackers,

I've run into a couple of occasions lately where I really wanted
pgstattuple on a production server in order to check table/index bloat.
However, in the production environment at a large site installing a
contrib module can involve a process which takes days or weeks.

I already faced that problem too.

From 9.1, it'll be a simple CREATE EXTENSION command - so much of the
problem goes away. Well. It doesn't go away, but it gets a lot more
neatly swept under the rug.

That's half of the history. Admin needs to install postgresql-contrib package.
Sometimes it takes too much time to convince clients that some additional
supplied modules are useful for them.

Now that we have extensions, why not build and package the contrib modules by
default? 'make world' is not the answer. There is not an option for "install
all pieces of software". Let's install pg+contrib and leave only 'CREATE
EXTENSION foo' for the admins.

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

On Fri, May 6, 2011 at 18:22, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

Em 06-05-2011 05:06, Magnus Hagander escreveu:

On Fri, May 6, 2011 at 00:34, Josh Berkus<josh.berkus@pgexperts.com>
 wrote:

Hackers,

I've run into a couple of occasions lately where I really wanted
pgstattuple on a production server in order to check table/index bloat.
 However, in the production environment at a large site installing a
contrib module can involve a process which takes days or weeks.

I already faced that problem too.

 From 9.1, it'll be a simple CREATE EXTENSION command - so much of the
problem goes away. Well. It doesn't go away, but it gets a lot more
neatly swept under the rug.

That's half of the history. Admin needs to install postgresql-contrib
package. Sometimes it takes too much time to convince clients that some
additional supplied modules are useful for them.

Now that we have extensions, why not build and package the contrib modules
by default? 'make world' is not the answer. There is not an option for
"install all pieces of software". Let's install pg+contrib and leave only
'CREATE EXTENSION foo' for the admins.

That's mostly an issue to be solved by the packagers. Some contrib
modules add dependencies, but those that don't could easily be
packaged in the main server package.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Fri, May 6, 2011 at 1:32 PM, Magnus Hagander <magnus@hagander.net> wrote:

On Fri, May 6, 2011 at 18:22, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

Em 06-05-2011 05:06, Magnus Hagander escreveu:

On Fri, May 6, 2011 at 00:34, Josh Berkus<josh.berkus@pgexperts.com>
 wrote:

Hackers,

I've run into a couple of occasions lately where I really wanted
pgstattuple on a production server in order to check table/index bloat.
 However, in the production environment at a large site installing a
contrib module can involve a process which takes days or weeks.

I already faced that problem too.

 From 9.1, it'll be a simple CREATE EXTENSION command - so much of the
problem goes away. Well. It doesn't go away, but it gets a lot more
neatly swept under the rug.

That's half of the history. Admin needs to install postgresql-contrib
package. Sometimes it takes too much time to convince clients that some
additional supplied modules are useful for them.

Now that we have extensions, why not build and package the contrib modules
by default? 'make world' is not the answer. There is not an option for
"install all pieces of software". Let's install pg+contrib and leave only
'CREATE EXTENSION foo' for the admins.

That's mostly an issue to be solved by the packagers. Some contrib
modules add dependencies, but those that don't could easily be
packaged in the main server package.

It seems to me that there's something of a "packaging policy" question to this.

A long time ago, on a pre-buildfarm planet, far, far away, it was
pretty uncertain what contrib modules could be hoped to run on what
platform.

At Afilias, we used to have to be *really* picky, because the subset
that ran on Solaris and AIX were not even close to all of them.
pgstattuples *was* one that the DBAs always wanted, but what would
compile was alway hit-and-miss.

Once we got AIX running a buildfarm node, that led to getting *ALL* of
contrib working there, and I'm pretty sure that similar happened with
other platforms at around the same time (I'm thinking this was 7.4,
but it might have been 8.0)

Be that all as it may, there has been a "sea change", where we have
moved from "sporadic usability of contrib" to it being *continually*
tested on *all* buildfarm platforms, which certainly adds to the
confidence level.

But people are evidently still setting packaging policies based on how
things were back in 7.3, even though that perhaps isn't necessary
anymore.

Certainly it's not a huge amount of code; less than 2MB these days.
-> % wc `dpkg -L postgresql-contrib-9.0` | tail -1
15952 67555 1770987 total

I'm getting "paper cuts" quite a bit these days over the differences
between what different packaging systems decide to install. The one
*I* get notably bit on, of late, is that I have written code that
expects to have pg_config to do some degree of self-discovery, only to
find production folk complaining that they only have "psql" available
in their environment.

I don't expect the extension system to help with any of this, since if
"production folk" try to install minimal sets of packages, they're
liable to consciously exclude extension support. The "improvement"
would come from drawing contrib a bit closer to core, and encouraging
packagers (dpkg, rpm, ports) to fold contrib into "base" rather than
separating it. I'm sure that would get some pushback, though.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On 05/06/2011 01:55 PM, Christopher Browne wrote:

Once we got AIX running a buildfarm node, that led to getting *ALL* of
contrib working there, and I'm pretty sure that similar happened with
other platforms at around the same time (I'm thinking this was 7.4,
but it might have been 8.0)

FYI, the buildfarm started in late 2004, near the end of the 8.0
development cycle. It quickly led to a number of contrib fixes.

Time flies when you're having fun ...

cheers

andrew

Em 06-05-2011 14:55, Christopher Browne escreveu:

The "improvement"
would come from drawing contrib a bit closer to core, and encouraging
packagers (dpkg, rpm, ports) to fold contrib into "base" rather than
separating it. I'm sure that would get some pushback, though.

I'm in favor of find out what are the popular extensions and make them into
"base"; the other ones could be moved to PGXN.

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Christopher Browne wrote:

I'm getting "paper cuts" quite a bit these days over the differences
between what different packaging systems decide to install. The one
*I* get notably bit on, of late, is that I have written code that
expects to have pg_config to do some degree of self-discovery, only to
find production folk complaining that they only have "psql" available
in their environment.

Given the other improvements in being able to build extensions in 9.1,
we really should push packagers to move pg_config from the PostgreSQL
development package into the main one starting in that version. I've
gotten bit by this plenty of times.

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

On Fri, May 6, 2011 at 2:32 PM, Greg Smith <greg@2ndquadrant.com> wrote:

Christopher Browne wrote:

I'm getting "paper cuts" quite a bit these days over the differences
between what different packaging systems decide to install.  The one
*I* get notably bit on, of late, is that I have written code that
expects to have pg_config to do some degree of self-discovery, only to
find production folk complaining that they only have "psql" available
in their environment.

Given the other improvements in being able to build extensions in 9.1, we
really should push packagers to move pg_config from the PostgreSQL
development package into the main one starting in that version.  I've gotten
bit by this plenty of times.

I'm agreeable to that, in general.

If there's a "server" package and a "client" package, it likely only
fits with the "server" package. On a host where only the "client" is
installed, they won't be able to install extensions, so it's pretty
futile to have it there.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On 05/06/2011 03:14 PM, Christopher Browne wrote:

On Fri, May 6, 2011 at 2:32 PM, Greg Smith<greg@2ndquadrant.com> wrote:

Christopher Browne wrote:

I'm getting "paper cuts" quite a bit these days over the differences
between what different packaging systems decide to install. The one
*I* get notably bit on, of late, is that I have written code that
expects to have pg_config to do some degree of self-discovery, only to
find production folk complaining that they only have "psql" available
in their environment.

Given the other improvements in being able to build extensions in 9.1, we
really should push packagers to move pg_config from the PostgreSQL
development package into the main one starting in that version. I've gotten
bit by this plenty of times.

I'm agreeable to that, in general.

If there's a "server" package and a "client" package, it likely only
fits with the "server" package. On a host where only the "client" is
installed, they won't be able to install extensions, so it's pretty
futile to have it there.

I don't agree. It can be useful even there, to see how the libraries are
configured, for example. I'd be inclined to bundle it with
postgresql-libs or the moral equivalent.

cheers

andrew

On Fri, May 6, 2011 at 21:19, Andrew Dunstan <andrew@dunslane.net> wrote:

On 05/06/2011 03:14 PM, Christopher Browne wrote:

On Fri, May 6, 2011 at 2:32 PM, Greg Smith<greg@2ndquadrant.com>  wrote:

Christopher Browne wrote:

I'm getting "paper cuts" quite a bit these days over the differences
between what different packaging systems decide to install.  The one
*I* get notably bit on, of late, is that I have written code that
expects to have pg_config to do some degree of self-discovery, only to
find production folk complaining that they only have "psql" available
in their environment.

Given the other improvements in being able to build extensions in 9.1, we
really should push packagers to move pg_config from the PostgreSQL
development package into the main one starting in that version.  I've
gotten
bit by this plenty of times.

I'm agreeable to that, in general.

If there's a "server" package and a "client" package, it likely only
fits with the "server" package.  On a host where only the "client" is
installed, they won't be able to install extensions, so it's pretty
futile to have it there.

I don't agree. It can be useful even there, to see how the libraries are
configured, for example. I'd be inclined to bundle it with postgresql-libs
or the moral equivalent.

+1.

And it's not like it wastes huge amount of space...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:

On Fri, May 6, 2011 at 21:19, Andrew Dunstan <andrew@dunslane.net> wrote:

On 05/06/2011 03:14 PM, Christopher Browne wrote:

If there's a "server" package and a "client" package, it likely only
fits with the "server" package. �On a host where only the "client" is
installed, they won't be able to install extensions, so it's pretty
futile to have it there.

I don't agree. It can be useful even there, to see how the libraries are
configured, for example. I'd be inclined to bundle it with postgresql-libs
or the moral equivalent.

+1.

Well, actually, I think packagers have generally put it into a -devel
subpackage. If it were in either a "server" or "client" package there
would be much less of an issue.

Bundling pg_config into a -libs package is probably not going to happen,
at least not on Red Hat systems, because it would create multilib issues
(ie, you're supposed to be able to install 32-bit and 64-bit libraries
concurrently, but there's noplace to put a /usr/bin file without causing
a conflict).

FWIW, I did move pg_config from -devel to the "main" (really client)
postgresql package in Fedora, as of 9.0. That will ensure it's present
in either client or server installations. Eventually that packaging
will reach RHEL ...

regards, tom lane

On 05/06/2011 04:06 PM, Tom Lane wrote:

Magnus Hagander<magnus@hagander.net> writes:

On Fri, May 6, 2011 at 21:19, Andrew Dunstan<andrew@dunslane.net> wrote:

On 05/06/2011 03:14 PM, Christopher Browne wrote:

If there's a "server" package and a "client" package, it likely only
fits with the "server" package. On a host where only the "client" is
installed, they won't be able to install extensions, so it's pretty
futile to have it there.

I don't agree. It can be useful even there, to see how the libraries are
configured, for example. I'd be inclined to bundle it with postgresql-libs
or the moral equivalent.

+1.

Well, actually, I think packagers have generally put it into a -devel
subpackage. If it were in either a "server" or "client" package there
would be much less of an issue.

Bundling pg_config into a -libs package is probably not going to happen,
at least not on Red Hat systems, because it would create multilib issues
(ie, you're supposed to be able to install 32-bit and 64-bit libraries
concurrently, but there's noplace to put a /usr/bin file without causing
a conflict).

FWIW, I did move pg_config from -devel to the "main" (really client)
postgresql package in Fedora, as of 9.0. That will ensure it's present
in either client or server installations. Eventually that packaging
will reach RHEL ...

That's reasonable, and certainly better than having it in -devel.

cheers

andrew

Christopher Browne <cbbrowne@gmail.com> writes:

But people are evidently still setting packaging policies based on how
things were back in 7.3, even though that perhaps isn't necessary
anymore.

FWIW, once you get past the client versus server distinction, I think
most subpackaging decisions are based on either the idea that "only a
minority of people will want this", or a desire to limit how many
dependencies are pulled in by the main package(s). Both of those
concerns apply to various subsets of -contrib, which means it's going
to be hard to persuade packagers to fold -contrib into the -server
package altogether. Nor would you gain their approval by trying to
pre-empt the decision.

We might get somewhere by trying to identify a small set of particularly
popular contrib modules that don't add any extra dependencies, and then
recommending to packagers that those ones get bundled into the main
server package.

Certainly it's not a huge amount of code; less than 2MB these days.
-> % wc `dpkg -L postgresql-contrib-9.0` | tail -1
15952 67555 1770987 total

Well, to add some concrete facts rather than generalities to my own post,
here are the sizes of the built RPMs from my last build for Fedora:

-rw-r--r--. 1 tgl tgl 3839458 Apr 18 10:50 postgresql-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 490788 Apr 18 10:50 postgresql-contrib-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 27337677 Apr 18 10:51 postgresql-debuginfo-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 961660 Apr 18 10:50 postgresql-devel-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 7569048 Apr 18 10:50 postgresql-docs-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 246506 Apr 18 10:50 postgresql-libs-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 64940 Apr 18 10:50 postgresql-plperl-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 65776 Apr 18 10:50 postgresql-plpython-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 45941 Apr 18 10:50 postgresql-pltcl-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 5302117 Apr 18 10:50 postgresql-server-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 1370509 Apr 18 10:50 postgresql-test-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 3644113 Apr 18 10:50 postgresql-upgrade-9.0.4-1.fc13.x86_64.rpm

The separate debuginfo package is distro policy enforced by toolchain;
I couldn't do anything about that even if I wanted to. The separate
-libs subpackage is also hard to avoid because of distro policy about
multilib installations. Separating devel support files (such as
headers) is also standard practice. The other subdivisions are either
my fault or those of my predecessors. plperl, plpython, and pltcl are
split out for dependency reasons, ie to not have the -server package
require you to install those languages and their respective ecosystems.
I think the separation of the -docs, -test, and -upgrade subpackages is
also pretty easy to defend on the grounds that "they're big and not
everyone wants 'em, especially not in production".

That leaves us with these three subpackages about which there's room
for argument:

-rw-r--r--. 1 tgl tgl 3839458 Apr 18 10:50 postgresql-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 490788 Apr 18 10:50 postgresql-contrib-9.0.4-1.fc13.x86_64.rpm
-rw-r--r--. 1 tgl tgl 5302117 Apr 18 10:50 postgresql-server-9.0.4-1.fc13.x86_64.rpm

Merging -contrib into the server package would increase the size of the
latter by almost 10%, which is enough to bother people. Also, a bit of
dependency extraction shows that -contrib has these dependencies beyond
the ones in the two main packages:

libcrypt.so.1
libossp-uuid.so.16
libxslt.so.1

That's not a particularly large list, I guess, but they're still the
sorts of dependencies that don't win any friends when it's time to get
the distro to fit on a DVD.

Bottom line is that I'd rather have a smaller postgresql-server package
that gets included in the shipping DVD than a complete one that gets
kicked off because it's too large and pulls in too many other non-core
dependencies.

So, again, some selective migration of contrib modules into the main
-server package might be doable, but the key word there is selective.

regards, tom lane

All,

We might get somewhere by trying to identify a small set of particularly
popular contrib modules that don't add any extra dependencies, and then
recommending to packagers that those ones get bundled into the main
server package.

Yeah, I wasn't thinking of including all of contrib. There's a lot of
reasons not to do that. I was asking about pgstattuple in particular,
since it's:
(a) small
(b) has no external dependancies
(c) adds no stability risk or performance overhead
(d) is usually needed on production systems when it's needed at all

It's possible that we have one or two other diagnostic utilities which
meet the above profile. pageinspect, maybe?

The reason why this is such an issue is that for big users with
high-demand production environments, installing any software, even
postgresql-devel or postgresql-contrib packages, are big major IT deals
which require weeks of advance scheduling. As a result, diagnostic
tools from contrib tend not to be used because the problem they need to
diagnose is much more urgent than that.

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

On Fri, May 6, 2011 at 5:58 PM, Josh Berkus <josh@agliodbs.com> wrote:

Yeah, I wasn't thinking of including all of contrib.  There's a lot of
reasons not to do that.

Slightly off-topic, but I really think we would benefit from trying to
divide up contrib. Right now it's a mixture of (a) debugging and
instrumentation tools (e.g. pgstattuple, pageinspect, pgrowlocks,
pg_freespacemap, pg_buffercache), (b) server functionality that is
generally useful but considered worth including in core (e.g. hstore,
citext, pg_trgm), (c) deprecated modules that we keep around mostly
for hysterical reasons (tsearch2, xml2, intagg), and (d) examples and
regression test support (dummy_seclabel, spi, start-scripts). I think
it would make things a lot easier for both packagers and actual users
if we separated these things into different directories, e.g.:

debugging and instrumentation tools -> src/debug
server functionality -> contrib
server functionality (deprecated) -> contrib/deprecated
examples & regression test suport -> src/test/examples

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

On 05/06/2011 05:58 PM, Josh Berkus wrote:

Yeah, I wasn't thinking of including all of contrib. There's a lot of
reasons not to do that. I was asking about pgstattuple in particular,
since it's:
(a) small
(b) has no external dependancies
(c) adds no stability risk or performance overhead
(d) is usually needed on production systems when it's needed at all

It's possible that we have one or two other diagnostic utilities which
meet the above profile. pageinspect, maybe?

I use pgstattuple, pageinspect, pg_freespacemap, and pg_buffercache
regularly enough that I wish they were more common. Throw in pgrowlocks
and you've got the whole group Robert put into the debug set. It makes
me sad every time I finish a utility using one of these and realize I'll
have to include the whole "make sure you have the contrib modules
installed" disclaimer in its documentation again.

These are the only ones I'd care about moving into a more likely place.
The rest of the contrib modules are the sort where if you need them, you
realize that early and get them installed. These are different by
virtue of their need popping up most often during emergencies. The fact
that I believe they all match the low impact criteria too makes it even
easier to consider.

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

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, May 6, 2011 at 5:58 PM, Josh Berkus <josh@agliodbs.com> wrote:

Yeah, I wasn't thinking of including all of contrib. �There's a lot of
reasons not to do that.

Slightly off-topic, but I really think we would benefit from trying to
divide up contrib. [ snip ]
I think
it would make things a lot easier for both packagers and actual users
if we separated these things into different directories, e.g.:

debugging and instrumentation tools -> src/debug
server functionality -> contrib
server functionality (deprecated) -> contrib/deprecated
examples & regression test suport -> src/test/examples

From a packager's standpoint, that would be entirely worthless. The
source tree's just a source tree, they don't care what lives where
within it. I was just thinking about what it'd take to actually
repackage things for Fedora, and the main problem is here:

%files contrib
...
%{_datadir}/pgsql/contrib/
...

If you're not adept at reading RPM specfiles, what that is saying
is that everything that "make install" has stuck under
${prefix}/share/pgsql/contrib/ is to be included in the contrib RPM.
To selectively move some stuff to the server RPM, I'd have to replace
that one line with a file-by-file list of *everything* in share/contrib,
and then move some of those lines to the "%files server" section, and
then look forward to having to maintain that list in future versions.
I'm already maintaining a file-by-file list of contrib's .so's, and I
can tell you it's a PITA.

As a packager, what I'd really want to see from a division into
recommended and not-so-recommended packages is that they get installed
into different subdirectories by "make install". Then I could just
point RPM at those directories and I'd be done.

I don't know how practical this is from our development standpoint,
nor from a user's standpoint --- I doubt we want to ask people to use
different CREATE EXTENSION commands depending on the preferredness of
the extension.

A possibly workable compromise would be to provide two separate makefile
installation targets for preferred and less preferred modules. The RPM
script could then do something like

make install-contrib-preferred
ls -R .../sharedir >contrib.files.for.server-package
make install-contrib-second-class-citizens
ls -R .../sharedir >all.contrib.files
... and then some magic with "comm" to separate out the contrib
... files not mentioned in contrib.files.for.server-package ...

Pretty grotty but it would work. Anyway my point is that this is all
driven off the *installed* file tree. A specfile writer doesn't know
nor want to know where "make install" is getting things from in the
source tree.

regards, tom lane

On 5/6/11 3:19 PM, Robert Haas wrote:

Slightly off-topic, but I really think we would benefit from trying to
divide up contrib.

I don't agree, unless by "divide up" you mean "move several things to
extensions".

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

These are the only ones I'd care about moving into a more likely place.
The rest of the contrib modules are the sort where if you need them, you
realize that early and get them installed. These are different by
virtue of their need popping up most often during emergencies. The fact
that I believe they all match the low impact criteria too makes it even
easier to consider.

Yes, precisely. If I need intarray, I'm going to need it for a
development push, which is planned well in advance. But if I need
pageinspect, it's almost certainly because an emergency has arisen.

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