Re: Updated ALTER TABLE ... SET TABLESPACE patch

#1

On Thu, 1 Jul 2004, Gavin Sherry wrote:

Quote:

Hi all,

Attached is an updated ALTER TABLE ... SET TABLESPACE patch.

It uses the block by block copy mechanism proposed by Tom and handles i)
ALTER TABLE <index> .... and ii) Copying of TOAST tables and the TOAST
table's index.

It doesn't handle copying of system tables (pg_largeobject) and, in the
interests of code reuse, the patch fiddles with the code used by CLUSTER.
This isn't great but I wanted to get a patch in before 1 July since I
think the feature is very important -- even for the first release.

Oh, and it doesn't handle WAL. Tom mentioned/agreed that btree's method of
dumping whole pages into WAL would be the best/most efficient way to
journaling this and that the btree code should be generalised. I haven't
had time to look at this yet.

Quote:

Thanks,

Gavin

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#2

On Wed, 2004-06-30 at 22:29, Gavin Sherry wrote:

Quote:

On Thu, 1 Jul 2004, Gavin Sherry wrote:

Oh, and it doesn't handle WAL. Tom mentioned/agreed that btree's method of
dumping whole pages into WAL would be the best/most efficient way to
journaling this and that the btree code should be generalised. I haven't
had time to look at this yet.

Not sure whether it is really desirable that it does. Could be some damn
big tablespaces moved and do we really want all of that in the log?

I think we can leave it for now, maybe add a "dont log" option later,
and then change the default to logging.

Best Regards, Simon Riggs

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#3

Quote:

Attached is an updated ALTER TABLE ... SET TABLESPACE patch.

It uses the block by block copy mechanism proposed by Tom and handles i)
ALTER TABLE <index> .... and ii) Copying of TOAST tables and the TOAST
table's index.

It doesn't handle copying of system tables (pg_largeobject) and, in the
interests of code reuse, the patch fiddles with the code used by CLUSTER.
This isn't great but I wanted to get a patch in before 1 July since I
think the feature is very important -- even for the first release.

Does this patch allow setting the tablespace of sequences as well? If
so, then you will need to modify pg_dump of SERIAL sequences. Perhaps
output a ALTER TABLE/SET TABLESPACE command after the CREATE TABLE
definition to move the SERIAL sequence.

The same argument applies if it allows moving indexes. (Unique and
Primary Keys)

Chris

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#4

I realize this still need WAL work, but we should get this in.

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

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

Gavin Sherry wrote:

Quote:

Hi all,

Attached is an updated ALTER TABLE ... SET TABLESPACE patch.

It uses the block by block copy mechanism proposed by Tom and handles i)
ALTER TABLE <index> .... and ii) Copying of TOAST tables and the TOAST
table's index.

It doesn't handle copying of system tables (pg_largeobject) and, in the
interests of code reuse, the patch fiddles with the code used by CLUSTER.
This isn't great but I wanted to get a patch in before 1 July since I
think the feature is very important -- even for the first release.

Thanks,

Gavin

Content-Description:

[ Attachment, skipping... ]

Quote:

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#5

Christopher Kings-Lynne <chriskl (AT) familyhealth (DOT) com.au> writes:

Quote:

Attached is an updated ALTER TABLE ... SET TABLESPACE patch.

Does this patch allow setting the tablespace of sequences as well? If
so, then you will need to modify pg_dump of SERIAL sequences. Perhaps
output a ALTER TABLE/SET TABLESPACE command after the CREATE TABLE
definition to move the SERIAL sequence.
The same argument applies if it allows moving indexes. (Unique and
Primary Keys)

Sequences no, toast tables no, indexes yes. So we need the
latter part of the above-mentioned patch. Anyone?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#6

Applied by Tom.

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

Gavin Sherry wrote:

Quote:

Hi all,

Attached is an updated ALTER TABLE ... SET TABLESPACE patch.

It uses the block by block copy mechanism proposed by Tom and handles i)
ALTER TABLE <index> .... and ii) Copying of TOAST tables and the TOAST
table's index.

It doesn't handle copying of system tables (pg_largeobject) and, in the
interests of code reuse, the patch fiddles with the code used by CLUSTER.
This isn't great but I wanted to get a patch in before 1 July since I
think the feature is very important -- even for the first release.

Thanks,

Gavin

Content-Description:

[ Attachment, skipping... ]

Quote:

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#7

Tom Lane wrote:

Quote:

Christopher Kings-Lynne <chriskl (AT) familyhealth (DOT) com.au> writes:
Attached is an updated ALTER TABLE ... SET TABLESPACE patch.

Does this patch allow setting the tablespace of sequences as well? If
so, then you will need to modify pg_dump of SERIAL sequences. Perhaps
output a ALTER TABLE/SET TABLESPACE command after the CREATE TABLE
definition to move the SERIAL sequence.
The same argument applies if it allows moving indexes. (Unique and
Primary Keys)

Sequences no, toast tables no, indexes yes. So we need the
latter part of the above-mentioned patch. Anyone?

Added to TODO;

* Allow moving sequences and toast tables to other tablespaces

in case no one does it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#8

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:

Quote:

Added to TODO;
* Allow moving sequences and toast tables to other tablespaces
in case no one does it.

Please remove that; if I thought either one was a good idea, I would
have allowed it in the committed patch.

Sequences are too small to be worth moving around, and may someday be
reimplemented in a fashion that doesn't use up a separate disk file for
each one. If we allow SET TABLESPACE on them we will be limiting our
future flexibility for no useful gain.

Toast tables are bound to their parent tables because (a) pg_dump isn't
nearly smart enough to handle moving them, and (b) I've got concerns
about how you decide whether a person is authorized to move one.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#9

OK, removed.

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

Tom Lane wrote:

Quote:

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
Added to TODO;
* Allow moving sequences and toast tables to other tablespaces
in case no one does it.

Please remove that; if I thought either one was a good idea, I would
have allowed it in the committed patch.

Sequences are too small to be worth moving around, and may someday be
reimplemented in a fashion that doesn't use up a separate disk file for
each one. If we allow SET TABLESPACE on them we will be limiting our
future flexibility for no useful gain.

Toast tables are bound to their parent tables because (a) pg_dump isn't
nearly smart enough to handle moving them, and (b) I've got concerns
about how you decide whether a person is authorized to move one.

regards, tom lane

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#10

Quote:

Please remove that; if I thought either one was a good idea, I would
have allowed it in the committed patch.

Sequences are too small to be worth moving around, and may someday be
reimplemented in a fashion that doesn't use up a separate disk file for
each one. If we allow SET TABLESPACE on them we will be limiting our
future flexibility for no useful gain.

Why do we allow them to be created in tablespaces in the first place
then? Seems like a bit of a misfeature? I mean we don't allow views in
tablespaces...

Chris

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend