PostgreSQL 8.2 Now Available

In response to Josh Berkus <josh@postgresql.org>:

-- Online index builds

I'm particularly curious about this feature. Does this mean that
PostgreSQL 8.2 can perform a REINDEX without blocking the relevant
table from writes?

If so, the 8.2 docs are a bit out of date:
http://www.postgresql.org/docs/8.2/static/sql-reindex.html

--
Bill Moran
Collaborative Fusion Inc.

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************

Bill Moran wrote:

In response to Josh Berkus <josh@postgresql.org>:

-- Online index builds

I'm particularly curious about this feature. Does this mean that
PostgreSQL 8.2 can perform a REINDEX without blocking the relevant
table from writes?

If so, the 8.2 docs are a bit out of date:
http://www.postgresql.org/docs/8.2/static/sql-reindex.html

No, it means you can do CREATE INDEX CONCURRENTLY.

http://www.postgresql.org/docs/8.2/static/sql-createindex.html

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tue, 2006-12-05 at 16:06 -0500, Bill Moran wrote:

In response to Josh Berkus <josh@postgresql.org>:

-- Online index builds

I'm particularly curious about this feature. Does this mean that
PostgreSQL 8.2 can perform a REINDEX without blocking the relevant
table from writes?

I don't know about reindex, but it is possible to drop and create the
index.

Sincerely,

Joshua D. Drake

If so, the 8.2 docs are a bit out of date:
http://www.postgresql.org/docs/8.2/static/sql-reindex.html

--

=== 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

In response to Alvaro Herrera <alvherre@commandprompt.com>:

Bill Moran wrote:

In response to Josh Berkus <josh@postgresql.org>:

-- Online index builds

I'm particularly curious about this feature. Does this mean that
PostgreSQL 8.2 can perform a REINDEX without blocking the relevant
table from writes?

If so, the 8.2 docs are a bit out of date:
http://www.postgresql.org/docs/8.2/static/sql-reindex.html

No, it means you can do CREATE INDEX CONCURRENTLY.

http://www.postgresql.org/docs/8.2/static/sql-createindex.html

Ahh ... and the text there specifically states that REINDEX does
_not_ work concurrently.

Thanks.

--
Bill Moran
Collaborative Fusion Inc.

Ragnar,

Now that this has been announced, should not
http://www.postgresql.org/docs/current/ and co be
redirected to http://www.postgresql.org/docs/8.1/
instead of http://www.postgresql.org/docs/8.2/

in particular, the press release's link to the
Release Notes brought me to
http://www.postgresql.org/docs/current/static/release.html
which showed the 8.1 Release Notes.

Fixing, thanks.

--Josh

On �ri, 2006-12-05 at 14:41 -0500, Josh Berkus wrote:

After eight months of development and five months of integration and
testing, the PostgreSQL Global Development Group now announces the
availability of PostgreSQL version 8.2 (our 14th public release).

...

For highlights of the release, please see the press kit:
http://www.postgresql.org/about/press/presskit82.html.en

Now that this has been announced, should not
http://www.postgresql.org/docs/current/ and co be
redirected to http://www.postgresql.org/docs/8.1/
instead of http://www.postgresql.org/docs/8.2/

in particular, the press release's link to the
Release Notes brought me to
http://www.postgresql.org/docs/current/static/release.html
which showed the 8.1 Release Notes.

gnari

On Dec 5, 2006, at 5:07 PM, Josh Berkus wrote:

Ragnar,

Now that this has been announced, should not
http://www.postgresql.org/docs/current/ and co be
redirected to http://www.postgresql.org/docs/8.1/
instead of http://www.postgresql.org/docs/8.2/
in particular, the press release's link to the Release Notes
brought me to
http://www.postgresql.org/docs/current/static/release.html
which showed the 8.1 Release Notes.

Fixing, thanks.

http://www.postgresql.org/docs/whatsnew tells about 8.1 still, as well.

wmoran@collaborativefusion.com (Bill Moran) writes:

In response to Alvaro Herrera <alvherre@commandprompt.com>:

Bill Moran wrote:

In response to Josh Berkus <josh@postgresql.org>:

-- Online index builds

I'm particularly curious about this feature. Does this mean that
PostgreSQL 8.2 can perform a REINDEX without blocking the relevant
table from writes?

If so, the 8.2 docs are a bit out of date:
http://www.postgresql.org/docs/8.2/static/sql-reindex.html

No, it means you can do CREATE INDEX CONCURRENTLY.

http://www.postgresql.org/docs/8.2/static/sql-createindex.html

Ahh ... and the text there specifically states that REINDEX does
_not_ work concurrently.

Thanks.

Let me add another question to this; this might possibly be worthy of
a TODO for 8.3 or so...

What if I wanted to:
ALTER TABLE distributors ADD PRIMARY KEY CONCURRENTLY (dist_id);
?

We have a number of cases where there isn't a true primary key on
tables. It would be very attractive to have a non-blocking way of
getting one, perhaps to be combined with letting Slony-I know about
it...

Or is it a better answer to look more deeply into the index
configuration, creating a suitably named UNIQUE index on NOT NULL
fields, and fiddling it into being the primary key?
--
let name="cbbrowne" and tld="linuxfinances.info" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/advocacy.html
"Marketing Division, Sirius Cybernetics Corp: A bunch of mindless
jerks who'll be the first against the wall when the revolution comes."
-- The Hitchhiker's Guide to the Galaxy

On Wed, 2006-12-06 at 15:00 -0500, Chris Browne wrote:

Let me add another question to this; this might possibly be worthy of
a TODO for 8.3 or so...

What if I wanted to:
ALTER TABLE distributors ADD PRIMARY KEY CONCURRENTLY (dist_id);
?

We have a number of cases where there isn't a true primary key on
tables. It would be very attractive to have a non-blocking way of
getting one, perhaps to be combined with letting Slony-I know about
it...

Or is it a better answer to look more deeply into the index
configuration, creating a suitably named UNIQUE index on NOT NULL
fields, and fiddling it into being the primary key?

Interesting, I was just thinking about this today as well. I am thinking
it would be nice if we could:

ALTER TABLE SET PRIMARY KEY INDEX foo_pkey;

If it's already got a primary key we switch the primary key to be the
new primary key (throwing an error if the columns don't match up to the
existing primary key, or if it's not unique). If not, the primary key
attribute is added to the existing index and the columns in the index
now make up the primary key (throwing an error if the index is not
unique).

It makes CREATE INDEX CONCURRENTLY more useful for reindexing a primary
key on a live database: you could just create the new index, switch it
to be the primary key, and drop the old index.

Regards,
Jeff Davis

On mi�, 2006-12-06 at 18:22 -0800, Jeff Davis wrote:

On Wed, 2006-12-06 at 15:00 -0500, Chris Browne wrote:

Let me add another question to this; this might possibly be worthy of
a TODO for 8.3 or so...

What if I wanted to:
ALTER TABLE distributors ADD PRIMARY KEY CONCURRENTLY (dist_id);

Interesting, I was just thinking about this today as well. I am thinking
it would be nice if we could:

ALTER TABLE SET PRIMARY KEY INDEX foo_pkey;

If it's already got a primary key we switch the primary key to be the
new primary key

(throwing an error if the columns don't match up to the
existing primary key,

not sure what you mean by this

or if it's not unique).

must also be NOT NULL

If not, the primary key
attribute is added to the existing index and the columns in the index
now make up the primary key (throwing an error if the index is not
unique).

What about existing foreign key constraints ?
as the only function of the PRIMARY key property of an
index is making it the default target of a foreign key
reference, you would have to decide what implications
this has. Possibly none, as I am not sure the foreign
key constraint remembers if the target was a primary key
or not.

also, your proposed syntax muddies the relationship
between the PRIMARY KEY constraint and the existence
of an INDEX. There is no such relationship in the SQL
standards.

possibly more appropriate would be

ALTER TABLE SET PRIMARY KEY (columns)
and an error issued if no UNIQUE NOT NULL index
is found on the relevant columns

one other question is what shuld happen to the original index that was
implicitly created. should it be dropped
automatically ?

gnari

On Thu, 2006-12-07 at 12:26 +0000, Ragnar wrote:

On mið, 2006-12-06 at 18:22 -0800, Jeff Davis wrote:

On Wed, 2006-12-06 at 15:00 -0500, Chris Browne wrote:

Let me add another question to this; this might possibly be worthy of
a TODO for 8.3 or so...

What if I wanted to:
ALTER TABLE distributors ADD PRIMARY KEY CONCURRENTLY (dist_id);

Interesting, I was just thinking about this today as well. I am thinking
it would be nice if we could:

ALTER TABLE SET PRIMARY KEY INDEX foo_pkey;

If it's already got a primary key we switch the primary key to be the
new primary key

(throwing an error if the columns don't match up to the
existing primary key,

not sure what you mean by this

In my suggestion, if the table already has a primary key, then you can
only set the primary key index to be an index with exactly the same
columns as the existing primary key index.

or if it's not unique).

must also be NOT NULL

Indexes can't be NOT NULL; NOT NULL is a constraint. You're right
though, if it was a new primary key, the column must already have the
NOT NULL constraint on it.

If not, the primary key
attribute is added to the existing index and the columns in the index
now make up the primary key (throwing an error if the index is not
unique).

What about existing foreign key constraints ?
as the only function of the PRIMARY key property of an
index is making it the default target of a foreign key
reference, you would have to decide what implications
this has. Possibly none, as I am not sure the foreign
key constraint remembers if the target was a primary key
or not.

Doesn't matter. Foreign keys don't reference an index, they reference a
set of attributes. I am just trying to provide an ability to change the
underlying unique index that is used to implement the unique constraint
that is necessary for all primary keys.

also, your proposed syntax muddies the relationship
between the PRIMARY KEY constraint and the existence
of an INDEX. There is no such relationship in the SQL
standards.

The index is an important implementation detail of a primary key,
because it is necessary to implement the UNIQUE constraint. Many PG DBAs
need to reindex the primary key on a large table as part of regular
maintenance. I am trying to provide a way to do this without locking our
reads or writes, using the already-existing CREATE INDEX CONCURRENTLY.

possibly more appropriate would be

ALTER TABLE SET PRIMARY KEY (columns)
and an error issued if no UNIQUE NOT NULL index
is found on the relevant columns

That doesn't solve the problem, because that doesn't allow you to choose
the index that the primary key will use, which was the whole point of my
suggestion.

one other question is what shuld happen to the original index that was
implicitly created. should it be dropped
automatically ?

Good question. Either way should be fine, as long as it is documented.
It should probably not be automatically dropped, but maybe issue a
NOTICE, like when the index is implicitly created.

Regards,
Jeff Davis

On fim, 2006-12-07 at 09:27 -0800, Jeff Davis wrote:

On Thu, 2006-12-07 at 12:26 +0000, Ragnar wrote:

On mi�, 2006-12-06 at 18:22 -0800, Jeff Davis wrote:

Interesting, I was just thinking about this today as well. I am thinking
it would be nice if we could:

ALTER TABLE SET PRIMARY KEY INDEX foo_pkey;

If it's already got a primary key we switch the primary key to be the
new primary key

(throwing an error if the columns don't match up to the
existing primary key,

not sure what you mean by this

In my suggestion, if the table already has a primary key, then you can
only set the primary key index to be an index with exactly the same
columns as the existing primary key index.

Why would you do that?

I saw the use-case of when you have a primary key and a
surrogate key , and decided you wanted the surrogate key to be the
primary key after all, maybe because the
natural key you had used turned out not to be a good
candidate.

or if it's not unique).

must also be NOT NULL

Indexes can't be NOT NULL; NOT NULL is a constraint.

Sorry, I got confused by the UNIQUE in the create index syntax:

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

...
What about existing foreign key constraints ?
as the only function of the PRIMARY key property of an
index is making it the default target of a foreign key
reference, you would have to decide what implications
this has. Possibly none, as I am not sure the foreign
key constraint remembers if the target was a primary key
or not.

Doesn't matter. Foreign keys don't reference an index, they reference a
set of attributes. I am just trying to provide an ability to change the
underlying unique index that is used to implement the unique constraint
that is necessary for all primary keys.

I was still imagining here that you would want a
different set of attributes froyour primary key.

gnari

On Thu, 2006-12-07 at 20:07 +0000, Ragnar wrote:

On fim, 2006-12-07 at 09:27 -0800, Jeff Davis wrote:

On Thu, 2006-12-07 at 12:26 +0000, Ragnar wrote:

On mið, 2006-12-06 at 18:22 -0800, Jeff Davis wrote:

Interesting, I was just thinking about this today as well. I am thinking
it would be nice if we could:

ALTER TABLE SET PRIMARY KEY INDEX foo_pkey;

If it's already got a primary key we switch the primary key to be the
new primary key

(throwing an error if the columns don't match up to the
existing primary key,

not sure what you mean by this

In my suggestion, if the table already has a primary key, then you can
only set the primary key index to be an index with exactly the same
columns as the existing primary key index.

Why would you do that?

I saw the use-case of when you have a primary key and a
surrogate key , and decided you wanted the surrogate key to be the
primary key after all, maybe because the
natural key you had used turned out not to be a good
candidate.

You've got a valid use-case, but it's completely different from the one
I suggested. I wanted to be able to build an index concurrently (with
the new functionality in 8.2) and then switch the primary key to use
that new index, and then drop the old index.

The reason is because that allows a 0-downtime index rebuild on a
primary key's index without losing it's primary key status.

I think all you need to do what you want is something like:
ALTER TABLE foo DROP CONSTRAINT foo_pkey KEEP INDEX;

Because then you could drop the primary key status on a column without
affecting the column or the index, then use my suggested syntax to
switch the primary key status to a different index like so:
ALTER TABLE foo SET PRIMARY KEY INDEX foo_othercolumn_index;

Regards,
Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:

I think all you need to do what you want is something like:
ALTER TABLE foo DROP CONSTRAINT foo_pkey KEEP INDEX;

Because then you could drop the primary key status on a column without
affecting the column or the index, then use my suggested syntax to
switch the primary key status to a different index like so:
ALTER TABLE foo SET PRIMARY KEY INDEX foo_othercolumn_index;

That seems like an awful lot of uglification simply to let the index be
marked as "primary key" rather than just "unique".

regards, tom lane

On Thu, 2006-12-07 at 18:11 -0500, Tom Lane wrote:

Jeff Davis <pgsql@j-davis.com> writes:

I think all you need to do what you want is something like:
ALTER TABLE foo DROP CONSTRAINT foo_pkey KEEP INDEX;

Because then you could drop the primary key status on a column without
affecting the column or the index, then use my suggested syntax to
switch the primary key status to a different index like so:
ALTER TABLE foo SET PRIMARY KEY INDEX foo_othercolumn_index;

That seems like an awful lot of uglification simply to let the index be
marked as "primary key" rather than just "unique".

Agreed. It's just a thought.

The reason it came to my mind is because some applications, like Slony,
use the primary key by default.

After reading through the archives, it looks like Gregory Stark
suggested a REINDEX CONCURRENTLY, which would certainly solve the
awkwardness of maintenance on a primary key. I didn't see much
objection, maybe it's worth consideration for 8.3?

Regards,
Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:

After reading through the archives, it looks like Gregory Stark
suggested a REINDEX CONCURRENTLY, which would certainly solve the
awkwardness of maintenance on a primary key. I didn't see much
objection, maybe it's worth consideration for 8.3?

That idea was bounced on the grounds that it requires a DROP INDEX to
occur somewhere, and that can't be concurrent, and you'd surely not like
to go through all the work of a CONCURRENTLY rebuild only to get a
deadlock failure at the very end.

regards, tom lane

On fim, 2006-12-07 at 13:57 -0800, Jeff Davis wrote:

On Thu, 2006-12-07 at 20:07 +0000, Ragnar wrote:

On fim, 2006-12-07 at 09:27 -0800, Jeff Davis wrote:

On Thu, 2006-12-07 at 12:26 +0000, Ragnar wrote:

On mi�, 2006-12-06 at 18:22 -0800, Jeff Davis wrote:

Interesting, I was just thinking about this today as well. I am thinking
it would be nice if we could:

ALTER TABLE SET PRIMARY KEY INDEX foo_pkey;

You've got a valid use-case, but it's completely different from the one
I suggested. I wanted to be able to build an index concurrently (with
the new functionality in 8.2) and then switch the primary key to use
that new index, and then drop the old index.

The reason is because that allows a 0-downtime index rebuild on a
primary key's index without losing it's primary key status.

my point was just that 'primary key' is really just
a property of a set of attributes, and it is just
incidental that postgres enforces this property
with an index.

so if if a ALTER TABLE SET PRIMARY KEY is implemented,
it should involve a set of attributes, but not an index.

in your use case, the ALTER should not really be needed.
lets say you have PRIMARY KEY (a,b) on some table.
you decide you want to rebuild the primary key concurrently. just build
a new index on (a,b).
if you then drop the old index, the primary key constraint can still be
enforced by the new index, so
the DROP should be allowed to proceed, without affecting
the constraint.

on the other hand, the PRIMARY KEY property is really
only there because the standards say so, but does not
have a great value in my opinion, so the ability to
alter it would not be high on my priority lists.

gnari

On Thu, 2006-12-07 at 18:51 -0500, Tom Lane wrote:

Jeff Davis <pgsql@j-davis.com> writes:

After reading through the archives, it looks like Gregory Stark
suggested a REINDEX CONCURRENTLY, which would certainly solve the
awkwardness of maintenance on a primary key. I didn't see much
objection, maybe it's worth consideration for 8.3?

That idea was bounced on the grounds that it requires a DROP INDEX to
occur somewhere, and that can't be concurrent, and you'd surely not like
to go through all the work of a CONCURRENTLY rebuild only to get a
deadlock failure at the very end.

I don't understand. CREATE INDEX CONCURRENTLY can't be run in a
transaction block anyway, so a REINDEX CONCURRENTLY wouldn't either. So
how (or when) would you deadlock?

I see it as the following logical operations:
(1) CREATE INDEX CONCURRENTLY tmp;
(2) swap the relfilenode of the old index and new index
(3) DROP INDEX tmp;

If this was all already hashed out on -hackers, you can point me to the
discussion if it's easier.

Regards,
Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:

I don't understand. CREATE INDEX CONCURRENTLY can't be run in a
transaction block anyway, so a REINDEX CONCURRENTLY wouldn't either. So
how (or when) would you deadlock?

The problem is you need to upgrade from a nonexclusive table lock to an
exclusive one before you could drop the old index. If someone else
is waiting to get a conflicting lock, boom ...

regards, tom lane