public inbox for [email protected]
help / color / mirror / Atom feedHelp speeding up delete
24+ messages / 12 participants
[nested] [flat]
* Help speeding up delete
@ 2005-11-14 22:07 Steve Wampler <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: Steve Wampler @ 2005-11-14 22:07 UTC (permalink / raw)
To: Postgres-performance <[email protected]>
We've got an older system in production (PG 7.2.4). Recently
one of the users has wanted to implement a selective delete,
but is finding that the time it appears to take exceeds her
patience factor by several orders of magnitude. Here's
a synopsis of her report. It appears that the "WHERE
id IN ..." is resulting in a seq scan that is causing
the problem, but we're not SQL expert enough to know
what to do about it.
Can someone point out what we're doing wrong, or how we
could get a (much) faster delete? Thanks!
Report:
============================================================
This command yields results in only a few seconds:
# SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a
# WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
However, the following command does not seen to want to ever
complete (the person running this killed it after 1/2 hour).
# DELETE FROM "tmp_table2" WHERE id IN
# (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
# WHERE at.id=a.id and a.name='obsid' and a.value='oid080505');
==============================================================
The table has four columns. There are 6175 rows satifying the condition
given, and the table itself has 1539688 entries. Layout is:
lab.devel.configdb=# \d tmp_table2
Table "tmp_table2"
Column | Type | Modifiers
--------+--------------------------+-----------
id | character varying(64) |
name | character varying(64) |
units | character varying(32) |
value | text |
time | timestamp with time zone |
==============================================================
lab.devel.configdb=# EXPLAIN DELETE FROM "tmp_table2" WHERE id IN
lab.devel.configdb-# (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
lab.devel.configdb(# WHERE at.id=a.id AND a.name='obsid' AND a.value='oid080505');
NOTICE: QUERY PLAN:
Seq Scan on tmp_table2 (cost=0.00..154893452082.10 rows=769844 width=6)
SubPlan
-> Materialize (cost=100600.52..100600.52 rows=296330 width=100)
-> Hash Join (cost=42674.42..100600.52 rows=296330 width=100)
-> Seq Scan on tmp_table2 at (cost=0.00..34975.88 rows=1539688 width=50)
-> Hash (cost=42674.32..42674.32 rows=38 width=50)
-> Seq Scan on tmp_table2 a (cost=0.00..42674.32 rows=38 width=50)
EXPLAIN
lab.devel.configdb=# EXPLAIN (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
lab.devel.configdb(# WHERE at.id=a.id AND a.name='obsid' AND a.value='oid080505');
NOTICE: QUERY PLAN:
Hash Join (cost=42674.42..100600.52 rows=296330 width=100)
-> Seq Scan on tmp_table2 at (cost=0.00..34975.88 rows=1539688 width=50)
-> Hash (cost=42674.32..42674.32 rows=38 width=50)
-> Seq Scan on tmp_table2 a (cost=0.00..42674.32 rows=38 width=50)
EXPLAIN
--
Steve Wampler -- [email protected]
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-14 23:20 Scott Lamb <[email protected]>
parent: Steve Wampler <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Scott Lamb @ 2005-11-14 23:20 UTC (permalink / raw)
To: Steve Wampler <[email protected]>; +Cc: Postgres-performance <[email protected]>
On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote:
> # SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a
> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
Isn't this equivalent?
select id from tmp_table2 where name = 'obsid' and value = 'oid080505';
> # DELETE FROM "tmp_table2" WHERE id IN
> # (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505');
and this?
delete from tmp_table2 where name = 'obsid' and value = 'oid080505';
Why are you doing a self-join using id, which I assume is a primary key?
--
Scott Lamb <http://www.slamb.org/;
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-14 23:42 Tom Lane <[email protected]>
parent: Steve Wampler <[email protected]>
1 sibling, 2 replies; 24+ messages in thread
From: Tom Lane @ 2005-11-14 23:42 UTC (permalink / raw)
To: Steve Wampler <[email protected]>; +Cc: Postgres-performance <[email protected]>
Steve Wampler <[email protected]> writes:
> We've got an older system in production (PG 7.2.4). Recently
> one of the users has wanted to implement a selective delete,
> but is finding that the time it appears to take exceeds her
> patience factor by several orders of magnitude. Here's
> a synopsis of her report. It appears that the "WHERE
> id IN ..." is resulting in a seq scan that is causing
> the problem, but we're not SQL expert enough to know
> what to do about it.
> Can someone point out what we're doing wrong, or how we
> could get a (much) faster delete? Thanks!
Update to 7.4 or later ;-)
Quite seriously, if you're still using 7.2.4 for production purposes
you could justifiably be accused of negligence. There are three or four
data-loss-grade bugs fixed in the later 7.2.x releases, not to mention
security holes; and that was before we abandoned support for 7.2.
You *really* need to be thinking about an update.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-14 23:52 Steve Wampler <[email protected]>
parent: Scott Lamb <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: Steve Wampler @ 2005-11-14 23:52 UTC (permalink / raw)
To: Scott Lamb <[email protected]>; +Cc: Postgres-performance <[email protected]>
Scott Lamb wrote:
> On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote:
>
>> # SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a
>> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
>
>
> Isn't this equivalent?
>
> select id from tmp_table2 where name = 'obsid' and value = 'oid080505';
Probably, the user based the above on a query designed to find
all rows with the same id as those rows that have a.name='obsid' and
a.value='oid080505'. However, I think the above would work to locate
all the ids, which is all we need for the delete (see below)
>> # DELETE FROM "tmp_table2" WHERE id IN
>> # (SELECT at.id FROM "tmp_table2" at, "tmp_table2" a
>> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505');
>
>
> and this?
>
> delete from tmp_table2 where name = 'obsid' and value = 'oid080505';
>
> Why are you doing a self-join using id, which I assume is a primary key?
Because I think we need to. The above would only delete rows that have
name = 'obsid' and value = 'oid080505'. We need to delete all rows that
have the same ids as those rows. However, from what you note, I bet
we could do:
DELETE FROM "tmp_table2" WHERE id IN
(SELECT id FROM "temp_table2" WHERE name = 'obsid' and value= 'oid080505');
However, even that seems to have a much higher cost than I'd expect:
lab.devel.configdb=# explain delete from "tmp_table2" where id in
(select id from tmp_table2 where name='obsid' and value = 'oid080505');
NOTICE: QUERY PLAN:
Seq Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844 width=6)
SubPlan
-> Materialize (cost=42674.32..42674.32 rows=38 width=50)
-> Seq Scan on tmp_table2 (cost=0.00..42674.32 rows=38 width=50)
EXPLAIN
And, sure enough, is taking an extrordinarily long time to run (more than
10 minutes so far, compared to < 10seconds for the select). Is this
really typical of deletes? It appears (to me) to be the Seq Scan on tmp_table2
that is the killer here. If we put an index on, would it help? (The user
claims she tried that and it's EXPLAIN cost went even higher, but I haven't
checked that...)
Thanks!
--
Steve Wampler -- [email protected]
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 00:00 Steve Wampler <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Steve Wampler @ 2005-11-15 00:00 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Postgres-performance <[email protected]>
Tom Lane wrote:
> Steve Wampler <[email protected]> writes:
>
>>We've got an older system in production (PG 7.2.4). Recently
>>one of the users has wanted to implement a selective delete,
>>but is finding that the time it appears to take exceeds her
>>patience factor by several orders of magnitude. Here's
>>a synopsis of her report. It appears that the "WHERE
>>id IN ..." is resulting in a seq scan that is causing
>>the problem, but we're not SQL expert enough to know
>>what to do about it.
>
>
>>Can someone point out what we're doing wrong, or how we
>>could get a (much) faster delete? Thanks!
>
>
> Update to 7.4 or later ;-)
I was afraid you'd say that :-) I'm not officially involved in
this project anymore and was hoping for a fix that wouldn't drag
me back in. The security issues aren't a concern because this
DB is *well* hidden from the outside world (it's part of a telescope
control system behind several firewalls with no outside access).
However, the data-loss-grade bugs issue *is* important. We'll
try to do the upgrade as soon as we get some cloudy days to
actually do it!
Is the performance behavior that we're experiencing a known
problem with 7.2 that has been addressed in 7.4? Or will the
upgrade fix other problems while leaving this one?
> Quite seriously, if you're still using 7.2.4 for production purposes
> you could justifiably be accused of negligence. There are three or four
> data-loss-grade bugs fixed in the later 7.2.x releases, not to mention
> security holes; and that was before we abandoned support for 7.2.
> You *really* need to be thinking about an update.
Thanks!
Steve
--
Steve Wampler -- [email protected]
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 00:10 Joshua Marsh <[email protected]>
parent: Steve Wampler <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Joshua Marsh @ 2005-11-15 00:10 UTC (permalink / raw)
To: Postgres-performance <[email protected]>
On 11/14/05, Steve Wampler <[email protected]> wrote:
> However, even that seems to have a much higher cost than I'd expect:
>
> lab.devel.configdb=# explain delete from "tmp_table2" where id in
> (select id from tmp_table2 where name='obsid' and value = 'oid080505');
> NOTICE: QUERY PLAN:
>
> Seq Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844 width=6)
> SubPlan
> -> Materialize (cost=42674.32..42674.32 rows=38 width=50)
> -> Seq Scan on tmp_table2 (cost=0.00..42674.32 rows=38 width=50)
>
For one reason or the other, the planner things a sequential scan is the
best solution. Try turning off seq_scan before the query and see if it
changes the plan (set enable_seqscan off;).
I've seen this problem with sub queries and that usually solves it.
--
This E-mail is covered by the Electronic Communications Privacy Act, 18
U.S.C. 2510-2521 and is legally privileged.
This information is confidential information and is intended only for the
use of the individual or entity named above. If the reader of this message
is not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 00:28 Steve Wampler <[email protected]>
parent: Joshua Marsh <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Steve Wampler @ 2005-11-15 00:28 UTC (permalink / raw)
To: Postgres-performance <[email protected]>
Joshua Marsh wrote:
>
>
> On 11/14/05, *Steve Wampler* <[email protected]
> <mailto:[email protected]>> wrote:
>
> However, even that seems to have a much higher cost than I'd expect:
>
> lab.devel.configdb=# explain delete from "tmp_table2" where id in
> (select id from tmp_table2 where name='obsid' and value =
> 'oid080505');
> NOTICE: QUERY PLAN:
>
> Seq Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844
> width=6)
> SubPlan
> -> Materialize (cost=42674.32..42674.32 rows=38 width=50)
> -> Seq Scan on tmp_table2 (cost=0.00..42674.32
> rows=38 width=50)
>
>
> For one reason or the other, the planner things a sequential scan is the
> best solution. Try turning off seq_scan before the query and see if it
> changes the plan (set enable_seqscan off;).
>
> I've seen this problem with sub queries and that usually solves it.
>
Hmmm, not only does it still use sequential scans, it thinks it'll take
even longer:
set enable_seqscan to off;
SET VARIABLE
explain delete from "tmp_table2" where id in
(select id from tmp_table2 where name='obsid' and value = 'oid080505');
NOTICE: QUERY PLAN:
Seq Scan on tmp_table2 (cost=100000000.00..160237039405992.50 rows=800836 width=6)
SubPlan
-> Materialize (cost=100043604.06..100043604.06 rows=45 width=26)
-> Seq Scan on tmp_table2 (cost=100000000.00..100043604.06 rows=45 width=26)
EXPLAIN
But the advice sounds like it *should* have helped...
--
Steve Wampler -- [email protected]
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 01:08 Scott Lamb <[email protected]>
parent: Steve Wampler <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Scott Lamb @ 2005-11-15 01:08 UTC (permalink / raw)
To: Steve Wampler <[email protected]>; +Cc: Postgres-performance <[email protected]>
On Nov 14, 2005, at 3:52 PM, Steve Wampler wrote:
> Scott Lamb wrote:
>> On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote:
>>
>>> # SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a
>>> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
>>
>>
>> Isn't this equivalent?
>>
>> select id from tmp_table2 where name = 'obsid' and value =
>> 'oid080505';
>
> Probably, the user based the above on a query designed to find
> all rows with the same id as those rows that have a.name='obsid' and
> a.value='oid080505'.
Well, this indirection is only significant if those two sets can
differ. If (A) you meant "tmp_table2" when you wrote "tmp_tabl2e", so
this is a self-join, and (B) there is a primary key on "id", I don't
think that can ever happen.
> It appears (to me) to be the Seq Scan on tmp_table2
> that is the killer here. If we put an index on, would it help?
On...tmp_table2.id? If it is a primary key, there already is one. If
not, yeah, I expect it would help.
--
Scott Lamb <http://www.slamb.org/;
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 01:18 Leigh Dyer <[email protected]>
parent: Steve Wampler <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Leigh Dyer @ 2005-11-15 01:18 UTC (permalink / raw)
To: Postgres-performance <[email protected]>
Steve Wampler wrote:
>
> Is the performance behavior that we're experiencing a known
> problem with 7.2 that has been addressed in 7.4? Or will the
> upgrade fix other problems while leaving this one?
I'm pretty sure that in versions earlier than 7.4, IN clauses that use a
subquery will always use a seqscan, regardless of what indexes are
available. If you try an IN using explicit values though, it should use
the index.
Thanks
Leigh
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-15 04:03 Steve Wampler <[email protected]>
parent: Scott Lamb <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Steve Wampler @ 2005-11-15 04:03 UTC (permalink / raw)
To: Scott Lamb <[email protected]>; +Cc: Postgres-performance <[email protected]>
Scott Lamb wrote:
> On Nov 14, 2005, at 3:52 PM, Steve Wampler wrote:
>
>> Scott Lamb wrote:
>>
>>> On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote:
>>>
>>>> # SELECT at.id FROM "tmp_table2" at, "tmp_tabl2e" a
>>>> # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
>>>
>>>
>>>
>>> Isn't this equivalent?
>>>
>>> select id from tmp_table2 where name = 'obsid' and value = 'oid080505';
>>
>>
>> Probably, the user based the above on a query designed to find
>> all rows with the same id as those rows that have a.name='obsid' and
>> a.value='oid080505'.
>
>
> Well, this indirection is only significant if those two sets can
> differ. If (A) you meant "tmp_table2" when you wrote "tmp_tabl2e", so
> this is a self-join, and (B) there is a primary key on "id", I don't
> think that can ever happen.
I wasn't clear. The original query was:
SELECT at.* FROM "tmp_table2" at, "tmp_table2" a
WHERE at.id=a.id and a.name='obsid' and a.value='oid080505';
which is significantly different than:
SELECT * FROM "tmp_table2" WHERE name='obsid' and value='oid080505';
The user had adapted that query for her needs, but it would have been
better to just use the query that you suggested (as the subselect in
the DELETE FROM...). Unfortunately, that only improves performance
slightly - it is still way too slow on deletes.
--
Steve Wampler -- [email protected]
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-16 20:00 Simon Riggs <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 2 replies; 24+ messages in thread
From: Simon Riggs @ 2005-11-16 20:00 UTC (permalink / raw)
To: pgsql-www; Tom Lane <[email protected]>; +Cc: Steve Wampler <[email protected]>; Postgres-performance <[email protected]>
On Mon, 2005-11-14 at 18:42 -0500, Tom Lane wrote:
> Steve Wampler <[email protected]> writes:
> > We've got an older system in production (PG 7.2.4).
>
> Update to 7.4 or later ;-)
>
> Quite seriously, if you're still using 7.2.4 for production purposes
> you could justifiably be accused of negligence. There are three or four
> data-loss-grade bugs fixed in the later 7.2.x releases, not to mention
> security holes; and that was before we abandoned support for 7.2.
> You *really* need to be thinking about an update.
Perhaps we should put a link on the home page underneath LATEST RELEASEs
saying
7.2: de-supported
with a link to a scary note along the lines of the above.
ISTM that there are still too many people on older releases.
We probably need an explanation of why we support so many releases (in
comparison to licenced software) and a note that this does not imply the
latest releases are not yet production (in comparison to MySQL or Sybase
who have been in beta for a very long time).
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 01:38 Christopher Kings-Lynne <[email protected]>
parent: Simon Riggs <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-17 01:38 UTC (permalink / raw)
To: Simon Riggs <[email protected]>; +Cc: pgsql-www; Tom Lane <[email protected]>; Steve Wampler <[email protected]>; Postgres-performance <[email protected]>
>>Update to 7.4 or later ;-)
>>
>>Quite seriously, if you're still using 7.2.4 for production purposes
>>you could justifiably be accused of negligence. There are three or four
>>data-loss-grade bugs fixed in the later 7.2.x releases, not to mention
>>security holes; and that was before we abandoned support for 7.2.
>>You *really* need to be thinking about an update.
>
>
> Perhaps we should put a link on the home page underneath LATEST RELEASEs
> saying
> 7.2: de-supported
>
> with a link to a scary note along the lines of the above.
I strongly support an explicit desupported notice for 7.2 and below on
the website...
Chris
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 01:40 Christopher Kings-Lynne <[email protected]>
parent: Simon Riggs <[email protected]>
1 sibling, 2 replies; 24+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-17 01:40 UTC (permalink / raw)
To: Simon Riggs <[email protected]>; +Cc: pgsql-www; Tom Lane <[email protected]>; Steve Wampler <[email protected]>; Postgres-performance <[email protected]>
> Perhaps we should put a link on the home page underneath LATEST RELEASEs
> saying
> 7.2: de-supported
>
> with a link to a scary note along the lines of the above.
>
> ISTM that there are still too many people on older releases.
>
> We probably need an explanation of why we support so many releases (in
> comparison to licenced software) and a note that this does not imply the
> latest releases are not yet production (in comparison to MySQL or Sybase
> who have been in beta for a very long time).
By the way, is anyone interested in creating some sort of online
repository on pgsql.org or pgfoundry where we can keep statically
compiled pg_dump/all for several platforms for 8.1?
That way if someone wanted to upgrade from 7.2 to 8.1, they can just
grab the latest dumper from the website, dump their old database, then
upgrade easily.
In my experience not many pgsql admins have test servers or the skills
to build up test machines with the latest pg_dump, etc. (Seriously.)
In fact, few realise at all that they should use the 8.1 dumper.
Chris
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 09:19 Magnus Hagander <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Magnus Hagander @ 2005-11-17 09:19 UTC (permalink / raw)
To: Christopher Kings-Lynne <[email protected]>; Simon Riggs <[email protected]>; +Cc: pgsql-www; Tom Lane <[email protected]>; Steve Wampler <[email protected]>; Postgres-performance <[email protected]>
> > Perhaps we should put a link on the home page underneath LATEST
> > RELEASEs saying
> > 7.2: de-supported
> >
> > with a link to a scary note along the lines of the above.
> >
> > ISTM that there are still too many people on older releases.
> >
> > We probably need an explanation of why we support so many
> releases (in
> > comparison to licenced software) and a note that this does
> not imply
> > the latest releases are not yet production (in comparison
> to MySQL or
> > Sybase who have been in beta for a very long time).
>
> By the way, is anyone interested in creating some sort of
> online repository on pgsql.org or pgfoundry where we can keep
> statically compiled pg_dump/all for several platforms for 8.1?
>
> That way if someone wanted to upgrade from 7.2 to 8.1, they
> can just grab the latest dumper from the website, dump their
> old database, then upgrade easily.
But if they're upgrading to 8.1, don't they already have the new
pg_dump? How else are they going to dump their *new* database?
> In my experience not many pgsql admins have test servers or
> the skills to build up test machines with the latest pg_dump,
I don't, but I still dump with the latest version - works fine both on
linux and windows for me...
> etc. (Seriously.) In fact, few realise at all that they
> should use the 8.1 dumper.
That most people don't know they should use the new one I understand
though. But I don't see how this will help against that :-)
//Magnus
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-17 11:19 Steinar H. Gunderson <[email protected]>
parent: Christopher Kings-Lynne <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Steinar H. Gunderson @ 2005-11-17 11:19 UTC (permalink / raw)
To: [email protected]
On Thu, Nov 17, 2005 at 09:40:42AM +0800, Christopher Kings-Lynne wrote:
> In my experience not many pgsql admins have test servers or the skills
> to build up test machines with the latest pg_dump, etc. (Seriously.)
> In fact, few realise at all that they should use the 8.1 dumper.
Isn't your distribution supposed to do this for you? Mine does these days...
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 15:05 Christopher Kings-Lynne <[email protected]>
parent: Magnus Hagander <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-17 15:05 UTC (permalink / raw)
To: Magnus Hagander <[email protected]>; +Cc: Simon Riggs <[email protected]>; pgsql-www; Tom Lane <[email protected]>; Steve Wampler <[email protected]>; Postgres-performance <[email protected]>
>>That way if someone wanted to upgrade from 7.2 to 8.1, they
>>can just grab the latest dumper from the website, dump their
>>old database, then upgrade easily.
>
> But if they're upgrading to 8.1, don't they already have the new
> pg_dump? How else are they going to dump their *new* database?
Erm. Usually when you install the new package/port for 8.1, you cannot
have both new and old installed at the same time man. Remember they
both store exactly the same binary files in exactly the same place.
>>In my experience not many pgsql admins have test servers or
>>the skills to build up test machines with the latest pg_dump,
>
> I don't, but I still dump with the latest version - works fine both on
> linux and windows for me...
So you're saying you DO have the skills to do it then...
>>etc. (Seriously.) In fact, few realise at all that they
>>should use the 8.1 dumper.
>
> That most people don't know they should use the new one I understand
> though. But I don't see how this will help against that :-)
It'll make it easy...
Chris
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-17 15:07 Christopher Kings-Lynne <[email protected]>
parent: Steinar H. Gunderson <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-17 15:07 UTC (permalink / raw)
To: Steinar H. Gunderson <[email protected]>; +Cc: [email protected]
> Isn't your distribution supposed to do this for you? Mine does these days...
A distribution that tries to automatically do a major postgresql update
is doomed to fail - spectacularly...
Chris
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 15:13 Steve Wampler <[email protected]>
parent: Christopher Kings-Lynne <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Steve Wampler @ 2005-11-17 15:13 UTC (permalink / raw)
To: Christopher Kings-Lynne <[email protected]>; +Cc: Magnus Hagander <[email protected]>; Simon Riggs <[email protected]>; pgsql-www; Tom Lane <[email protected]>; Postgres-performance <[email protected]>
Christopher Kings-Lynne wrote:
>> That most people don't know they should use the new one I understand
>> though. But I don't see how this will help against that :-)
>
> It'll make it easy...
As the miscreant that caused this thread to get started, let me
*wholeheartedly* agree with Chris. An easy way to get the pg_dump
for the upgrade target to run with the upgradable source
would work wonders. (Instructions included, of course.)
--
Steve Wampler -- [email protected]
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 15:39 Magnus Hagander <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Magnus Hagander @ 2005-11-17 15:39 UTC (permalink / raw)
To: Christopher Kings-Lynne <[email protected]>; +Cc: Simon Riggs <[email protected]>; pgsql-www; Tom Lane <[email protected]>; Steve Wampler <[email protected]>; Postgres-performance <[email protected]>
> >>That way if someone wanted to upgrade from 7.2 to 8.1, they
> can just
> >>grab the latest dumper from the website, dump their old
> database, then
> >>upgrade easily.
> >
> > But if they're upgrading to 8.1, don't they already have the new
> > pg_dump? How else are they going to dump their *new* database?
>
> Erm. Usually when you install the new package/port for 8.1,
> you cannot have both new and old installed at the same time
> man. Remember they both store exactly the same binary files
> in exactly the same place.
Urrk. Didn't think of that. I always install from source on Unix, which
doesn't have the problem. And the Windows port doesn't have this problem
- it will put the binaries in a version dependant directory.
One could claim the packages are broken ;-), but that's not gonig to
help here, I know...
(I always install in pgsql-<version>, and then symlink pgsql there..)
> >>etc. (Seriously.) In fact, few realise at all that they should use
> >>the 8.1 dumper.
> >
> > That most people don't know they should use the new one I
> understand
> > though. But I don't see how this will help against that :-)
>
> It'll make it easy...
You assume they know enough to download it. If they don't know to look
for it, they still won't find it.
But the bottom line: I can see how it would be helpful if you're on a
distro which packages postgresql in a way that prevents you from
installing more than one version at the same time.
//Magnus
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 15:56 Scott Marlowe <[email protected]>
parent: Christopher Kings-Lynne <[email protected]>
1 sibling, 0 replies; 24+ messages in thread
From: Scott Marlowe @ 2005-11-17 15:56 UTC (permalink / raw)
To: Christopher Kings-Lynne <[email protected]>; +Cc: Simon Riggs <[email protected]>; pgsql-www; Tom Lane <[email protected]>; Steve Wampler <[email protected]>; Postgres-performance <[email protected]>
On Wed, 2005-11-16 at 19:40, Christopher Kings-Lynne wrote:
> > Perhaps we should put a link on the home page underneath LATEST RELEASEs
> > saying
> > 7.2: de-supported
> >
> > with a link to a scary note along the lines of the above.
> >
> > ISTM that there are still too many people on older releases.
> >
> > We probably need an explanation of why we support so many releases (in
> > comparison to licenced software) and a note that this does not imply the
> > latest releases are not yet production (in comparison to MySQL or Sybase
> > who have been in beta for a very long time).
>
> By the way, is anyone interested in creating some sort of online
> repository on pgsql.org or pgfoundry where we can keep statically
> compiled pg_dump/all for several platforms for 8.1?
>
> That way if someone wanted to upgrade from 7.2 to 8.1, they can just
> grab the latest dumper from the website, dump their old database, then
> upgrade easily.
>
> In my experience not many pgsql admins have test servers or the skills
> to build up test machines with the latest pg_dump, etc. (Seriously.)
> In fact, few realise at all that they should use the 8.1 dumper.
I would especially like such a thing available as an RPM. A
pgsql-8.1-clienttools.rpm or something like that, with psql, pg_dump,
pg_restore, and what other command line tools you can think of that
would help.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [PERFORM] Help speeding up delete
@ 2005-11-17 16:26 Svenne Krap <[email protected]>
parent: Magnus Hagander <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Svenne Krap @ 2005-11-17 16:26 UTC (permalink / raw)
To: Magnus Hagander <[email protected]>; pgsql-www
>You assume they know enough to download it. If they don't know to look
>for it, they still won't find it.
>
>
I think there should be a big, fat warning in the release notes.
Something like:
WARNING: Upgrading to version X.Y requires a full dump/restore cycle.
Please download the appropriate dump-utility from http://postgresql.org/dumputils/X.Y/
and make a copy of your database before installing the new version X.Y.
And then link to a dir with the statically linked pg_dump (and -all) for
the most common platforms. I must admit, I did not know that one should
use the new tool in a cyclus (and I have used Pg almost exclusively
since 7.0). That could also be the place to add a line about version S.T
is now considered obsolete and unsupported.
/Svenne
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-17 17:25 Steinar H. Gunderson <[email protected]>
parent: Christopher Kings-Lynne <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Steinar H. Gunderson @ 2005-11-17 17:25 UTC (permalink / raw)
To: [email protected]
On Thu, Nov 17, 2005 at 11:07:42PM +0800, Christopher Kings-Lynne wrote:
>> Isn't your distribution supposed to do this for you? Mine does these
>> days...
> A distribution that tries to automatically do a major postgresql update
> is doomed to fail - spectacularly...
Automatically? Well, you can install the two versions side-by-side, and do
pg_upgradecluster, which ports your configuration to the new version and does
a pg_dump between the two versions; exactly what a system administrator would
do. Of course, stuff _can_ fail, but it works for the simple cases, and a
great deal of the not-so-simple cases. I did this for our cluster the other
day (130 wildly different databases, from 7.4 to 8.1) and it worked
flawlessly.
I do not really see why all the distributions could do something like this,
instead of mucking around with special statically compiled pg_dumps and the
like...
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-18 01:39 Ron Mayer <[email protected]>
parent: Christopher Kings-Lynne <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Ron Mayer @ 2005-11-18 01:39 UTC (permalink / raw)
To: Christopher Kings-Lynne <[email protected]>
Christopher Kings-Lynne wrote:
>>>
>>> Quite seriously, if you're still using 7.2.4 for production purposes
>>> you could justifiably be accused of negligence....
>>
>> Perhaps we should put a link on the home page underneath LATEST RELEASEs
>> saying
>> 7.2: de-supported
>> with a link to a scary note along the lines of the above.
>
> I strongly support an explicit desupported notice for 7.2 and below on
> the website...
I'd go so far as to say the version #s of supported versions
is one of pieces of information I'd most expect to see on
the main support page ( http://www.postgresql.org/support/ ).
Perhaps it'd be nice to even show a table like
Version Released On Support Ends
7.1 4 BC Sep 3 1752
7.2 Feb 31 1900 Jan 0 2000
7.4 2003-11-17 At least 2005-x-x
8.0 2005-01-19 At least 2006-x-x
with a footnote saying that only the most recent dot release
of each family is considered supported.
It also might be nice to have a footnote saying that any
of the commercical support companies might support the older
versions for longer periods of time.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Help speeding up delete
@ 2005-11-18 01:56 Christopher Kings-Lynne <[email protected]>
parent: Steinar H. Gunderson <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-18 01:56 UTC (permalink / raw)
To: Steinar H. Gunderson <[email protected]>; +Cc: [email protected]
> I do not really see why all the distributions could do something like this,
> instead of mucking around with special statically compiled pg_dumps and the
> like...
Contrib modules and tablespaces.
Plus, no version of pg_dump before 8.0 is able to actually perform such
reliable dumps and reloads (due to bugs). However, that's probably moot
these days.
Chris
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2005-11-18 01:56 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-14 22:07 Help speeding up delete Steve Wampler <[email protected]>
2005-11-14 23:20 ` Scott Lamb <[email protected]>
2005-11-14 23:52 ` Steve Wampler <[email protected]>
2005-11-15 00:10 ` Joshua Marsh <[email protected]>
2005-11-15 00:28 ` Steve Wampler <[email protected]>
2005-11-15 01:08 ` Scott Lamb <[email protected]>
2005-11-15 04:03 ` Steve Wampler <[email protected]>
2005-11-14 23:42 ` Tom Lane <[email protected]>
2005-11-15 00:00 ` Steve Wampler <[email protected]>
2005-11-15 01:18 ` Leigh Dyer <[email protected]>
2005-11-16 20:00 ` Simon Riggs <[email protected]>
2005-11-17 01:38 ` Christopher Kings-Lynne <[email protected]>
2005-11-18 01:39 ` Ron Mayer <[email protected]>
2005-11-17 01:40 ` Christopher Kings-Lynne <[email protected]>
2005-11-17 11:19 ` Steinar H. Gunderson <[email protected]>
2005-11-17 15:07 ` Christopher Kings-Lynne <[email protected]>
2005-11-17 17:25 ` Steinar H. Gunderson <[email protected]>
2005-11-18 01:56 ` Christopher Kings-Lynne <[email protected]>
2005-11-17 15:56 ` Scott Marlowe <[email protected]>
2005-11-17 09:19 Re: [PERFORM] Help speeding up delete Magnus Hagander <[email protected]>
2005-11-17 15:05 ` Christopher Kings-Lynne <[email protected]>
2005-11-17 15:13 ` Steve Wampler <[email protected]>
2005-11-17 15:39 Re: [PERFORM] Help speeding up delete Magnus Hagander <[email protected]>
2005-11-17 16:26 ` Svenne Krap <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox