Re: [HACKERS] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[Bernd Helmle]

--On 25. Januar 2010 11:39:21 +0900 KaiGai Kohei <kai...@ak.jp.nec.com> wrote:

(echo "CREATE TABLE t (a int);"
 for i in `seq 0 9`; do
     echo "CREATE TABLE s$i (b int) INHERITS(t);"
     for j in `seq 0 9`; do
         echo "CREATE TABLE v$i$j (c int) INHERITS(s$i);"
         for k in `seq 0 9`; do
             echo "CREATE TABLE w$i$j$k (d int) INHERITS(v$i$j);"
             for l in `seq 0 9`; do
                 echo "CREATE TABLE x$i$j$k$l (e int) INHERITS(w$i$j$k);"
             done
         done
     done
 done) | psql test

A1, A2, A3, ..., Am
B1 INHERITS(A1...A10), B2 INHERITS(A1...A10, B3 INHERITS(A1...A10), ...Bn
C1 INHERITS(B1...B10), C2 INHERITS(B1...B10), ... Co
D1 INHERITS(C1...C10), ..., Dp

m = 10
n = 10
o = 10
p = 1000

Repeating this on my MacBook gives:

ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;

-HEAD:

Time: 382,427 ms
Time: 375,974 ms
Time: 385,478 ms
Time: 371,067 ms
Time: 410,834 ms
Time: 386,382 ms

Recent V4 patch:

Time: 6065,673 ms
Time: 3823,206 ms
Time: 4037,933 ms
Time: 3873,029 ms
Time: 3899,607 ms
Time: 3963,308 ms



pg_ctl -o '--checkpoint-segments=32 --max-locks-per-transaction=128' start

--
Thanks

        Bernd

Well, each table inherits one table in your test. In my test, I inherit from multiple tables for each table. My script generates the following inheritance tree (and wins a price of copy & paste ugliness, see attachment): Note that you have to increase max_locks_per_transaction to run the script, i used

test_rename.sql
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers