pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedALTER TABLE x ALTER COLUMN y TYPE z
6+ messages / 3 participants
[nested] [flat]
* ALTER TABLE x ALTER COLUMN y TYPE z
@ 2006-03-22 17:58 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2006-03-22 18:36 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 19:00 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 2 replies; 6+ messages in thread
From: Kevin Grittner @ 2006-03-22 17:58 UTC (permalink / raw)
To: pgsql-docs
On this page:
http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html
there is this user comment:
--------------------
To change the data type of a column, do this:
BEGIN;
ALTER TABLE tab ADD COLUMN new_col new_data_type;
UPDATE tab SET new_col = CAST(old_col AS new_data_type);
ALTER TABLE tab RENAME old_col TO temp_name;
ALTER TABLE tab RENAME new_col TO old_col;
ALTER TABLE tab DROP COLUMN temp_name;
COMMIT;
You might then want to do VACUUM FULL tab to reclaim the disk space
used by the expired rows.
--------------------
The 8.1 release (and the 8.0 release) support the same functionality
with a single line:
ALTER TABLE tab ALTER COLUMN old_col TYPE new_data_type;
I think the user comment should be removed, unless there is some
benefit to using the multi-step process. If there is some benefit, I
think it should be described, so that users know when to use it instead
of the simpler technique.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ALTER TABLE x ALTER COLUMN y TYPE z
2006-03-22 17:58 ALTER TABLE x ALTER COLUMN y TYPE z Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2006-03-22 18:36 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2006-03-22 18:36 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-docs
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> On this page:
> http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html
> ...
> I think the user comment should be removed, unless there is some
> benefit to using the multi-step process.
There isn't, and I agree it should go. The comment seems to have been
attached to both 7.4 and 8.1 at the same time --- it is useful for 7.4,
but not later versions.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ALTER TABLE x ALTER COLUMN y TYPE z
2006-03-22 17:58 ALTER TABLE x ALTER COLUMN y TYPE z Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2006-03-22 19:00 ` Robert Treat <xzilla@users.sourceforge.net>
2006-03-22 20:01 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 6+ messages in thread
From: Robert Treat @ 2006-03-22 19:00 UTC (permalink / raw)
To: pgsql-docs; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Tom Lane <tgl@sss.pgh.pa.us>
On Wednesday 22 March 2006 12:58, Kevin Grittner wrote:
> On this page:
>
> http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html
>
> there is this user comment:
>
> --------------------
>
> To change the data type of a column, do this:
>
> BEGIN;
> ALTER TABLE tab ADD COLUMN new_col new_data_type;
> UPDATE tab SET new_col = CAST(old_col AS new_data_type);
> ALTER TABLE tab RENAME old_col TO temp_name;
> ALTER TABLE tab RENAME new_col TO old_col;
> ALTER TABLE tab DROP COLUMN temp_name;
> COMMIT;
>
> You might then want to do VACUUM FULL tab to reclaim the disk space
> used by the expired rows.
>
> --------------------
>
> The 8.1 release (and the 8.0 release) support the same functionality
> with a single line:
>
> ALTER TABLE tab ALTER COLUMN old_col TYPE new_data_type;
>
> I think the user comment should be removed, unless there is some
> benefit to using the multi-step process. If there is some benefit, I
> think it should be described, so that users know when to use it instead
> of the simpler technique.
>
I believe Tom's comments in this email apply similarly here.
http://archives.postgresql.org/pgsql-general/2006-03/msg00891.php
Feel free to submit an additional doc comment.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ALTER TABLE x ALTER COLUMN y TYPE z
2006-03-22 17:58 ALTER TABLE x ALTER COLUMN y TYPE z Kevin Grittner <Kevin.Grittner@wicourts.gov>
2006-03-22 19:00 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Robert Treat <xzilla@users.sourceforge.net>
@ 2006-03-22 20:01 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2006-03-22 20:14 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2006-03-22 20:01 UTC (permalink / raw)
To: pgsql-docs; Robert Treat <xzilla@users.sourceforge.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
>>> On Wed, Mar 22, 2006 at 1:00 pm, in message
<200603221400.33513.xzilla@users.sourceforge.net>, Robert Treat
<xzilla@users.sourceforge.net> wrote:
>
> I believe Tom's comments in this email apply similarly here.
> http://archives.postgresql.org/pgsql- general/2006- 03/msg00891.php
The user comment's recommended technique includes this line:
ALTER TABLE tab DROP COLUMN temp_name;
Would this cause a table rewrite? (Not a rhetorical question. I
really don't know.)
> Feel free to submit an additional doc comment.
I did, but it was rejected -- presumably because it included a
question. Once I have a better handle on the issue, if it seems like it
needs it, I'll try again.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ALTER TABLE x ALTER COLUMN y TYPE z
2006-03-22 17:58 ALTER TABLE x ALTER COLUMN y TYPE z Kevin Grittner <Kevin.Grittner@wicourts.gov>
2006-03-22 19:00 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Robert Treat <xzilla@users.sourceforge.net>
2006-03-22 20:01 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2006-03-22 20:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 21:50 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-03-22 20:14 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-docs@postgresql.org, "Robert Treat" <xzilla@users.sourceforge.net>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> The user comment's recommended technique includes this line:
> ALTER TABLE tab DROP COLUMN temp_name;
> Would this cause a table rewrite? (Not a rhetorical question. I
> really don't know.)
No, it wouldn't. The UPDATE is the only part that modifies the table
contents.
However, the arguments Robert pointed to had to do with concurrent
access to the table, and somehow I am not seeing the use-case for ALTER
COLUMN TYPE on a table that's being actively used by other clients.
It seems unlikely that you could do that without needing to also update
your client software. I'd be a tad worried about stale-cached-plan
problems too, in current PG releases. Finally, since the first ALTER
will take an exclusive lock that won't be released until COMMIT, this
approach doesn't avoid the problem of holding exclusive lock for a long
time.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ALTER TABLE x ALTER COLUMN y TYPE z
2006-03-22 17:58 ALTER TABLE x ALTER COLUMN y TYPE z Kevin Grittner <Kevin.Grittner@wicourts.gov>
2006-03-22 19:00 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Robert Treat <xzilla@users.sourceforge.net>
2006-03-22 20:01 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Kevin Grittner <Kevin.Grittner@wicourts.gov>
2006-03-22 20:14 ` Re: ALTER TABLE x ALTER COLUMN y TYPE z Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-22 21:50 ` Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 6+ messages in thread
From: Robert Treat @ 2006-03-22 21:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-docs
On Wednesday 22 March 2006 15:14, Tom Lane wrote:
> However, the arguments Robert pointed to had to do with concurrent
> access to the table, and somehow I am not seeing the use-case for ALTER
> COLUMN TYPE on a table that's being actively used by other clients.
> It seems unlikely that you could do that without needing to also update
> your client software.
Altering between char and text or some such? Or something like INET to TEXT.
(Not that it matters for the things being discussed here, but I think there
are cases)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-03-22 21:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-22 17:58 ALTER TABLE x ALTER COLUMN y TYPE z Kevin Grittner <Kevin.Grittner@wicourts.gov>
2006-03-22 18:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 19:00 ` Robert Treat <xzilla@users.sourceforge.net>
2006-03-22 20:01 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2006-03-22 20:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 21:50 ` Robert Treat <xzilla@users.sourceforge.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox