Eike Dierks wrote
> The following bug has been logged on the website:
>
> Bug reference: 10591
> Logged by: Eike Dierks
> Email address:
> eike@
> PostgreSQL version: 9.2.1
> Operating system: OSX 10.9, centos on virtuozzo linux-2.6.18-028stab
> Description:
>
> - given demo_table with some amount of data
> - add new_column (works instantely)
> - add a unique contraint on new_column
> - update demo_table set new_colum=null; (quite slow)
> -> takes a long time: seems to rewrite the whole table
>
> I guess that adding a new column does not yet reserve space for that new
> column on disk, but that the on-disk layout is only changed as soon as I
> do
> the update (aka making the column physically present on disk)
>
> I fully understand that this is a sensible tradeoff and that this is the
> intended behaviour.
As you say - this is not a bug...
> However from my uneducated guess (not tested) I believe that a vacuum full
> followed by the update would have been much faster.
Doubtful and not without its own problems. While the UPDATE could perform
faster the entire transaction will definately take longer as a whole.
> I somehow believe that my first sequence of commands did make the
> new_column
> virtual first and then physically for every row in turn, thereby taking a
> long time to complete.
Yes, this is how MVCC (multi-version concurrency control) works.
> I'd like to suggest to add to the documentation (if it's not already in
> there)
> It should be stated that a vacuum full might speed up further access to a
> newly introduced column.
This is not a bug and while I understand your confusion if you are going to
suggest that changes are needed is it very helpful to actually suggest what
you think needs to change.
Or, at least, tell us what parts of the documentation you did read so that
we may try and better understand how users are using the documentation and
also point to you anything you may have missed.
> But again I did not fully test that scenario, it's just my guess.
If you are not going to actually test your theories you should then at least
provide logical support for your reasoning.
As a final note, a better wording for the message would be: "setting newly
added columns to null is pointless" (or maybe- "is not optimized"). But any
optimization to make this pointless use-case faster would likely slow down
real-world use-cases...
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-10591-setting-newly-added-columns-to-null-is-slow-tp5806732p5806737.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.