View is not using a table index
We have a table which we want to normalize and use the same SQL to
perform selects using a view.
The old table had 3 columns in it's index
(region_id,wx_element,valid_time).
The new table meteocode_elmts has a similar index but the region_id is a
reference to another table region_lookup and wx_element to table
meteocode_elmts_lookup. This will make our index and table
significantly smaller.
As stated ablove we want to use the same SQL query to check the view.
The problem is we have not been able to set up the view so that it
references the "rev" index. It just uses the region_id but ignores the
wx_element, therefore the valid_time is also ignored. The rev index now
consists of region_id(reference to region_lookup
table),wx_element(reference to meteocode_elmts_lookup) and valid_time.
We are using Postgresql 7.4.0. Below is the relevant views and tables
plus an explain analyze of the query to the old table and the view.
Old table forceastelement
phoenix=# \d forecastelement
Table "public.forecastelement"
Column | Type | Modifiers
----------------+-----------------------------+-----------
origin | character varying(10) | not null
timezone | character varying(99) | not null
region_id | character varying(20) | not null
wx_element | character varying(99) | not null
value | character varying(99) | not null
flag | character(3) | not null
units | character varying(99) | not null
valid_time | timestamp without time zone | not null
issue_time | timestamp without time zone | not null
next_forecast | timestamp without time zone | not null reception_time
| timestamp without time zone | not null
Indexes:
"forecastelement_rwv_idx" btree (region_id, wx_element, valid_time)
New and view nad tables are
phoenix=# \d fcstelmt_view
View "public.fcstelmt_view"
Column | Type | Modifiers
----------------+-----------------------------+-----------
origin | character varying(10) |
timezone | character varying(10) |
region_id | character varying(99) |
wx_element | character varying(99) |
value | character varying(99) |
flag | character(3) |
unit | character varying |
valid_time | timestamp without time zone |
issue_time | timestamp without time zone |
next_forecast | timestamp without time zone | reception_time |
timestamp without time zone |
View definition:
SELECT meteocode_bltns.origin, meteocode_bltns.timezone,
region_lookup.region_id, meteocode_elmts_lookup.wx_element,
meteocode_elmts.value, meteocode_bltns.flag, ( SELECT
meteocode_units_lookup.unit FROM meteocode_units_lookup WHERE
meteocode_units_lookup.id = meteocode_elmts.unit_id) AS unit,
meteocode_elmts.valid_time, meteocode_bltns.issue_time,
meteocode_bltns.next_forecast, meteocode_bltns.reception_time FROM
meteocode_bltns, meteocode_elmts, region_lookup, meteocode_elmts_lookup
WHERE meteocode_bltns.meteocode_id = meteocode_elmts.meteocode AND
region_lookup.id = meteocode_elmts.reg_id AND meteocode_elmts_lookup.id
= meteocode_elmts.wx_element_id;
phoenix=# \d meteocode_elmts
Table "public.meteocode_elmts"
Column | Type | Modifiers
---------------+-----------------------------+-----------
meteocode | integer |
value | character varying(99) | not null
unit_id | integer |
valid_time | timestamp without time zone | not null
lcleffect | integer |
reg_id | integer |
wx_element_id | integer |
Indexes:
"rev" btree (reg_id, wx_element_id, valid_time) phoenix=# \d
meteocode_bltns
Table "public.meteocode_bltns"
Column | Type |
Modifiers
----------------+-----------------------------+-------------------------
----------------+-----------------------------+-------------------------
----------------+-----------------------------+---------
meteocode_id | integer | not null default
nextval('"meteocode_bltns_idseq"'::text)
origin | character varying(10) | not null
header | character varying(20) | not null
timezone | character varying(10) | not null
flag | character(3) | not null
initial | character varying(40) | not null
issue_time | timestamp without time zone | not null
next_forecast | timestamp without time zone | not null reception_time
| timestamp without time zone | not null
Indexes:
"meteocode_bltns_meteocode_id_idx" btree (meteocode_id)
phoenix=# \d region_lookup
Table "public.region_lookup"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer | not null
region_id | character varying(99) |
Indexes:
"region_lookup_pkey" primary key, btree (id)
phoenix=# \d meteocode_elmts_lookup
Table "public.meteocode_elmts_lookup"
Column | Type | Modifiers
------------+-----------------------+-----------
id | integer | not null
wx_element | character varying(99) | not null
Indexes:
"meteocode_elmts_lookup_pkey" primary key, btree (id)
"wx_element_idx" btree (wx_element)
phoenix=# \d meteocode_units_lookup
Table "public.meteocode_units_lookup"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
unit | character varying(99) | not null
Indexes:
"meteocode_units_lookup_pkey" primary key, btree (id)
VIEW
PWFPM_DEV=# explain analyze SELECT
origin,timezone,region_id,wx_element,value,flag,unit,valid_time,issue_ti
me,next_forecast FROM fcstelmt_view where origin = 'OFFICIAL' and
timezone = 'CST6CDT' and region_id = 'PU-REG-WNT-00027' and wx_element
= 'NGTPERIOD_MINTEMP' and value = '-26' and flag= 'REG' and unit =
'CELSIUS' and valid_time = '2007-04-09 00:00:00' and issue_time =
'2007-04-08 15:00:00' and next_forecast = '2007-04-09 04:00:00' ;
QUERY PLAN
Hash Join (cost=1.47..1309504.33 rows=1 width=264) (actual
time=21.609..84.940 rows=1 loops=1)
Hash Cond: ("outer".wx_element = "inner".id)
-> Nested Loop (cost=0.00..1309501.76 rows=1 width=201) (actual
time=17.161..80.489 rows=1 loops=1)
-> Nested Loop (cost=0.00..1309358.57 rows=1 width=154)
(actual time=17.018..80.373 rows=2 loops=1)
-> Seq Scan on region_lookup (cost=0.00..26.73 rows=7
width=71) (actual time=0.578..2.135 rows=1 loops=1)
Filter: ((region_id)::text = 'PU-REG-WNT-00027'::text)
-> Index Scan using rev on meteocode_elmts
(cost=0.00..187047.39 rows=1 width=91) (actual time=16.421..78 .208
rows=2 loops=1)
Index Cond: ("outer".id = meteocode_elmts.region_id)
Filter: (((value)::text = '-26'::text) AND (valid_time = '2007-04-09
00:00:00'::timestamp without tim e zone) AND (((subplan))::text =
'CELSIUS'::text))
SubPlan -> Seq Scan on meteocode_units_lookup
(cost=0.00..1.09 rows=1 width=67) (actual time=0.013..0.018 rows=1
loops=2)
Filter: (id = $0)
-> Index Scan using meteocode_bltns_meteocode_id_idx on
meteocode_bltns (cost=0.00..143.18 rows=1 width=55) (ac tual
time=0.044..0.045 rows=0 loops=2)
Index Cond: (meteocode_bltns.meteocode_id =
"outer".meteocode)
Filter: (((origin)::text = 'OFFICIAL'::text) AND
((timezone)::text = 'CST6CDT'::text) AND (flag = 'REG'::bp char) AND
(issue_time = '2007-04-08 15:00:00'::timestamp without time zone) AND
(next_forecast = '2007-04-09 04:00:00'::ti mestamp without time
zone))
-> Hash (cost=1.46..1.46 rows=2 width=71) (actual time=0.081..0.081
rows=0 loops=1)
-> Seq Scan on meteocode_elmts_lookup (cost=0.00..1.46 rows=2
width=71) (actual time=0.042..0.076 rows=1 loops= 1)
Filter: ((wx_element)::text = 'NGTPERIOD_MINTEMP'::text)
SubPlan
-> Seq Scan on meteocode_units_lookup (cost=0.00..1.09 rows=1
width=67) (actual time=0.007..0.012 rows=1 loops=1)
Filter: (id = $0)
Total runtime: 85.190 ms
(22 rows)
OLD TABLE
PWFPM_DEV=# explain analyze SELECT
origin,timezone,region_id,wx_element,value,flag,units,valid_time,issue_t
ime,next_forecast FROM forecastelement where origin = 'OFFICIAL' and
timezone = 'CST6CDT' and region_id = 'PU-REG-WNT-00027' and wx_element =
'NGTPERIOD_MINTEMP' and value = '-26' and flag= 'REG' and units =
'CELSIUS' and valid_time = '2007-04-09 00:00:00' and issue_time =
'2007-04-08 15:00:00' and next_forecast = '2007-04-09 04:00:00' ;
QUERY PLAN
Index Scan using forecastelement_rwv_idx on forecastelement
(cost=0.00..4.03 rows=1 width=106) (actual time=0.207..0.207 rows=0
loops=1)
Index Cond: (((region_id)::text = 'PU-REG-WNT-00027'::text) AND
((wx_element)::text = 'NGTPERIOD_MINTEMP'::text) AND (valid_time =
'2007-04-09 00:00:00'::timestamp without time zone))
Filter: (((origin)::text = 'OFFICIAL'::text) AND ((timezone)::text =
'CST6CDT'::text) AND ((value)::text = '-26'::text) AND (flag =
'REG'::bpchar) AND ((units)::text = 'CELSIUS'::text) AND (issue_time =
'2007-04-08 15:00:00'::timestamp without time zone) AND (next_forecast =
'2007-04-09 04:00:00'::timestamp without time zone))
Total runtime: 0.327 ms
(4 rows)
Dan Shea wrote:
We have a table which we want to normalize and use the same SQL to
perform selects using a view.
The old table had 3 columns in it's index
(region_id,wx_element,valid_time).
The new table meteocode_elmts has a similar index but the region_id is a
reference to another table region_lookup and wx_element to table
meteocode_elmts_lookup. This will make our index and table
significantly smaller.
As stated ablove we want to use the same SQL query to check the view.
The problem is we have not been able to set up the view so that it
references the "rev" index. It just uses the region_id but ignores the
wx_element, therefore the valid_time is also ignored. The rev index now
consists of region_id(reference to region_lookup
table),wx_element(reference to meteocode_elmts_lookup) and valid_time.We are using Postgresql 7.4.0. Below is the relevant views and tables
plus an explain analyze of the query to the old table and the view.
Please say it's not really 7.4.0 - you're running 7.4.xx actually,
aren't you, where xx is quite a high number?
phoenix=# \d region_lookup
Table "public.region_lookup"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer | not null
region_id | character varying(99) |
Indexes:
"region_lookup_pkey" primary key, btree (id)phoenix=# \d meteocode_elmts_lookup
Table "public.meteocode_elmts_lookup"
Column | Type | Modifiers
------------+-----------------------+-----------
id | integer | not null
wx_element | character varying(99) | not null
Indexes:
"meteocode_elmts_lookup_pkey" primary key, btree (id)
"wx_element_idx" btree (wx_element)
Anyway, you're joining to these tables and testing against the text
values without any index useful to the join.
Try indexes on (wx_element, id) and (region_id,id) etc. Re-analyse the
tables and see what that does for you.
Oh - I'd expect an index over the timestamps might help too.
Then, if you've got time try setting up an 8.2 installation, do some
basic configuration and transfer the data. I'd be surprised if you
didn't get some noticeable improvements just from the version number
increase.
--
Richard Huxton
Archonet Ltd
Version is PWFPM_DEV=# select version();
version
------------------------------------------------------------------------
--------------------------------
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
(1 row)
We used the rpm source from postgresql-7.4-0.5PGDG.
You make it sound so easy. Our database size is at 308 GB. We actually
have 8.2.3 running and would like to transfer in the future. We have to
investigate the best way to do it.
Dan.
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Tuesday, April 24, 2007 11:42 AM
To: Shea,Dan [NCR]
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] View is not using a table index
Dan Shea wrote:
We have a table which we want to normalize and use the same SQL to
perform selects using a view.
The old table had 3 columns in it's index
(region_id,wx_element,valid_time).
The new table meteocode_elmts has a similar index but the region_id is
a reference to another table region_lookup and wx_element to table
meteocode_elmts_lookup. This will make our index and table
significantly smaller.
As stated ablove we want to use the same SQL query to check the view.
The problem is we have not been able to set up the view so that it
references the "rev" index. It just uses the region_id but ignores
the wx_element, therefore the valid_time is also ignored. The rev
index now consists of region_id(reference to region_lookup
table),wx_element(reference to meteocode_elmts_lookup) and valid_time.We are using Postgresql 7.4.0. Below is the relevant views and tables
plus an explain analyze of the query to the old table and the view.
Please say it's not really 7.4.0 - you're running 7.4.xx actually,
aren't you, where xx is quite a high number?
phoenix=# \d region_lookup
Table "public.region_lookup"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer | not null
region_id | character varying(99) |
Indexes:
"region_lookup_pkey" primary key, btree (id)phoenix=# \d meteocode_elmts_lookup
Table "public.meteocode_elmts_lookup"
Column | Type | Modifiers
------------+-----------------------+-----------
id | integer | not null
wx_element | character varying(99) | not null
Indexes:
"meteocode_elmts_lookup_pkey" primary key, btree (id)
"wx_element_idx" btree (wx_element)
Anyway, you're joining to these tables and testing against the text
values without any index useful to the join.
Try indexes on (wx_element, id) and (region_id,id) etc. Re-analyse the
tables and see what that does for you.
Oh - I'd expect an index over the timestamps might help too.
Then, if you've got time try setting up an 8.2 installation, do some
basic configuration and transfer the data. I'd be surprised if you
didn't get some noticeable improvements just from the version number
increase.
--
Richard Huxton
Archonet Ltd
* Dan Shea <dan.shea@ec.gc.ca> [070424 19:33]:
Version is PWFPM_DEV=# select version();
version
------------------------------------------------------------------------
--------------------------------
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
(1 row)We used the rpm source from postgresql-7.4-0.5PGDG.
You make it sound so easy. Our database size is at 308 GB. We actually
have 8.2.3 running and would like to transfer in the future. We have to
investigate the best way to do it.
That depends upon your requirements for the uptime.
Andreas
Dan Shea <dan.shea@ec.gc.ca> writes:
You make it sound so easy. Our database size is at 308 GB.
Well, if you can't update major versions that's understandable; that's
why we're still maintaining the old branches. But there is no excuse
for not running a reasonably recent sub-release within your branch.
Read the release notes, and consider what you will say if one of the
several data-loss-causing bugs that were fixed long ago eats your DB:
http://developer.postgresql.org/pgdocs/postgres/release.html
regards, tom lane
Tom Lane wrote:
Dan Shea <dan.shea@ec.gc.ca> writes:
You make it sound so easy. Our database size is at 308 GB.
Well, if you can't update major versions that's understandable; that's
why we're still maintaining the old branches. But there is no excuse
for not running a reasonably recent sub-release within your branch.
Read the release notes, and consider what you will say if one of the
several data-loss-causing bugs that were fixed long ago eats your DB:
Was it Feb 2002? The Slammer effectively shut down the entire Internet,
due to a severe bug in Microsucks SQL Server... A fix for that buffer
overflow bug had been available since August 2001; yet 90% of all SQL
servers on the planet were unpatched.
As much as it pains me to admit it, the lesson about the importance of
being a conscious, competent administrator takes precedence over the
lesson of how unbelievably incompetent and irresponsible and etc. etc.
Microsoft is to have such a braindead bug in such a high-profile and
high-price product.
Tom said it really nicely --- do stop and think about it; the day arrives
when you *lost* all those 308 GB of valuable data; and it was only in
your hands to have prevented it! Would you want to see the light of
*that* day?
Carlos
--
Carlos Moreno wrote:
Tom Lane wrote:
Well, if you can't update major versions that's understandable; that's
why we're still maintaining the old branches. But there is no excuse
for not running a reasonably recent sub-release within your branch.Slammer..bug in Microsucks SQL Server....fix...had been available
Feature request.
How about if PostgreSQL periodically check for updates on the
internet and log WARNINGs as soon as it sees it's not running
the newest minor version for a branch. Ideally, it could
be set so the time-of-day's configurable to avoid setting off
pagers in the middle of the night.
I might not lurk on the mailinglists enough to notice every
dot release; but I sure would notice if pagers went off with
warnings in the log files from production servers.
Is that a possible TODO?
(The thread started on the performance mailing lists but
I moved it to general since it drifted off topic).
Ron Mayer wrote:
Carlos Moreno wrote:
Tom Lane wrote:
Well, if you can't update major versions that's understandable; that's
why we're still maintaining the old branches. But there is no excuse
for not running a reasonably recent sub-release within your branch.Slammer..bug in Microsucks SQL Server....fix...had been available
Feature request.
How about if PostgreSQL periodically check for updates on the
internet and log WARNINGs as soon as it sees it's not running
the newest minor version for a branch. Ideally, it could
be set so the time-of-day's configurable to avoid setting off
pagers in the middle of the night.
uhmmm gah, errm no... ehhhh why? :)
I could see a contrib module that was an agent that did that but not as
part of actual core.
Joshua D. Drake
I might not lurk on the mailinglists enough to notice every
dot release; but I sure would notice if pagers went off with
warnings in the log files from production servers.Is that a possible TODO?
(The thread started on the performance mailing lists but
I moved it to general since it drifted off topic).---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Ron Mayer <rm_pg@cheapcomplexdevices.com> wrote:
Carlos Moreno wrote:
Tom Lane wrote:
Well, if you can't update major versions that's understandable; that's
why we're still maintaining the old branches. But there is no excuse
for not running a reasonably recent sub-release within your branch.Slammer..bug in Microsucks SQL Server....fix...had been available
Feature request.
How about if PostgreSQL periodically check for updates on the
internet and log WARNINGs as soon as it sees it's not running
the newest minor version for a branch. Ideally, it could
be set so the time-of-day's configurable to avoid setting off
pagers in the middle of the night.I might not lurk on the mailinglists enough to notice every
dot release; but I sure would notice if pagers went off with
warnings in the log files from production servers.Is that a possible TODO?
If you switch to FreeBSD, you can easily have this done automatically
with existing tools.
...
Actually, I've a feeling that it would be trivial to do with just
about any existing packaging system ...
--
Bill Moran
http://www.potentialtech.com
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 04/25/07 21:52, Bill Moran wrote:
[snip]
If you switch to FreeBSD, you can easily have this done automatically
with existing tools....
Actually, I've a feeling that it would be trivial to do with just
about any existing packaging system ...
Or Debian, the Universal Operating System.
And if you don't want to move up to a good OS, you could always
parse http://www.postgresql.org/versions.xml for the exact
information you need.
- --
Ron Johnson, Jr.
Jefferson LA USA
Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFGMKk1S9HxQb37XmcRAjZAAKCsgXoDofYQJGixA1vV0/IUr0tPjACeJeWR
ZbLeGYpEwiwEZ7Q1ELrqOuU=
=SM1D
-----END PGP SIGNATURE-----
Actually, I've a feeling that it would be trivial to do with just
about any existing packaging system ...
Yes pretty much every version of Linux, and FreeBSD, heck even Solaris
if you are willing to run 8.1.
J
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Thursday 26. April 2007 17:10, Joshua D. Drake wrote:
Actually, I've a feeling that it would be trivial to do with just
about any existing packaging system ...Yes pretty much every version of Linux, and FreeBSD, heck even Solaris
if you are willing to run 8.1.
Gentoo is still on version 8.1.8, though, and even that is soft-masked
(stable is at 8.0.12). Seems like a problem with getting 8.2.x to build
on this platform:
<http://forums.gentoo.org/viewtopic-t-534835-highlight-postgresql.html>
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/
Leif B. Kristensen wrote:
On Thursday 26. April 2007 17:10, Joshua D. Drake wrote:
Actually, I've a feeling that it would be trivial to do with just
about any existing packaging system ...Yes pretty much every version of Linux, and FreeBSD, heck even Solaris
if you are willing to run 8.1.Gentoo is still on version 8.1.8, though, and even that is soft-masked
(stable is at 8.0.12). Seems like a problem with getting 8.2.x to build
on this platform:<http://forums.gentoo.org/viewtopic-t-534835-highlight-postgresql.html>
I run 8.2.x on a Gentoo/x86_64 development box (just did the upgrade to
8.2.4 yesterday) using the postgresql-experimental overlay (via layman)
and have run into no problems. Everything has compiled,
installed/upgraded and been run with no hiccups along the way, nor any
hacky workarounds.
The 8.2 series isn't in the main portage tree yet because, as I
understand it (and I could certainly be mistaken), the contributors
maintaining the ebuilds are reworking the slotting setup as well as
cleaning up the distinctions between server/library/client-only installs.
Granted, I'm not advising a mission-critical server that happens to be
running Gentoo use a portage overlay explicitly marked "experimental"
for its RDBMS package management -- just pointing out that there is a
pretty straight-forward way to get the 8.2 series through portage if
you're willing to use an overlay for it.
-Jon
--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/
Joshua D. Drake wrote:
Ron Mayer wrote:
How about if PostgreSQL periodically check for updates on the
internet and log WARNINGs as soon as it sees it's not running
the newest minor version for a branch. ...uhmmm gah, errm no... ehhhh why? :)
Mostly because it seems like a near FAQ here that someone
posts questions about people running very old postgresqls
where the answers are "that was fixed in the latest minor version".
Regarding people saying that their OS package manager
can do this for them - I note that the people who have
this problem the worst seem to be the people running
older postgresqls, and your OS vendor may not be keeping
the major version number of their postgresql the same
as yours. For example, apt-cache search here isn't showing
me 8.0 (though it does show 7.4, 8.1, and 8.2).
I could see a contrib module that was an agent that did that but not as
part of actual core.
I was thinking it would protect the more ignorant users
who didn't even know about contrib. I imagine anyone
who did know enough to install a contrib module would
also know how to write such a script without it.
No big deal, though - if others don't think there's a need, then
I'm not going to push for it.
On 4/25/07, Ron Mayer <rm_pg@cheapcomplexdevices.com> wrote:
Carlos Moreno wrote:
Tom Lane wrote:
Well, if you can't update major versions that's understandable; that's
why we're still maintaining the old branches. But there is no excuse
for not running a reasonably recent sub-release within your branch.Slammer..bug in Microsucks SQL Server....fix...had been available
Feature request.
How about if PostgreSQL periodically check for updates on the
internet and log WARNINGs as soon as it sees it's not running
the newest minor version for a branch. Ideally, it could
be set so the time-of-day's configurable to avoid setting off
pagers in the middle of the night.I might not lurk on the mailinglists enough to notice every
dot release; but I sure would notice if pagers went off with
warnings in the log files from production servers.Is that a possible TODO?
(The thread started on the performance mailing lists but
I moved it to general since it drifted off topic).---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
what about the distros that do backporting for the bug fixes ?
those would be saying you are with a outdated PostgreSQL version
--
Leonel
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 04/26/07 13:38, Ron Mayer wrote:
Joshua D. Drake wrote:
Ron Mayer wrote:
How about if PostgreSQL periodically check for updates on the
internet and log WARNINGs as soon as it sees it's not
running the newest minor version for a branch. ...uhmmm gah, errm no... ehhhh why? :)
Mostly because it seems like a near FAQ here that someone posts
questions about people running very old postgresqls where the
answers are "that was fixed in the latest minor version".Regarding people saying that their OS package manager can do this
for them - I note that the people who have this problem the worst
seem to be the people running older postgresqls, and your OS
vendor may not be keeping the major version number of their
postgresql the same as yours. For example, apt-cache search
here isn't showing me 8.0 (though it does show 7.4, 8.1, and
8.2).
For example: Debian. It's Stable releases only get *security*
patches, nothing related to features or performance.
I could see a contrib module that was an agent that did that
but not as part of actual core.I was thinking it would protect the more ignorant users who
didn't even know about contrib. I imagine anyone who did know
enough to install a contrib module would also know how to write
such a script without it.No big deal, though - if others don't think there's a need, then
I'm not going to push for it.
A *tiny* Perl/Python script to parse
http://www.postgresql.org/versions.xml is all you need. Putting it
in cron and emailing when "someone" a version changes seems useful.
Ok, it's official: you're elected to implement it!
- --
Ron Johnson, Jr.
Jefferson LA USA
Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFGMQJ/S9HxQb37XmcRAgUEAKDWKzM8scO7Mc8uB26iqIo8WnJGmwCg6e4w
vRuaSXH0sMhtnNZbYsuDKmc=
=wGYf
-----END PGP SIGNATURE-----
On Thursday 26. April 2007 20:12, Jon Sime wrote:
I run 8.2.x on a Gentoo/x86_64 development box (just did the upgrade
to 8.2.4 yesterday) using the postgresql-experimental overlay (via
layman) and have run into no problems. Everything has compiled,
installed/upgraded and been run with no hiccups along the way, nor any
hacky workarounds.
Postgresql-8.2.4 went soft-masked today. I've upgraded and my own local
web application is working just fine, but dependencies for several
other libs and apps are broken, and I'm in the process of running
revdep-rebuild and rebuilding 14 packages right now.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/