Re: ALTER TABLE name RENAME TO new_name; does notworkimmediately

"Alvaro Herrera" <alvherre(at)commandprompt(dot)com> writes:

>> > I could not reproduce it here, but didn't try very hard.
>>
>> Did you try running the script he provided? It happened for me first try.
>
> Yep. No luck. I didn't try changing the args in generate_series though.
>
> How many CPUs are you running this on? My system has 2.

Huh, I've been able to simplify the reproducing script somewhat and still
trigger the bug. It is *not* necessary to run the table rename and the column
rename in the same transaction. That means it's *not* a problem with a missing
CommandCounterIncrement() or anything like that. Even at transaction end the
cache entry isn't being invalidated up if that's the cause.

Also, as expected the size of the table is irrelevant.

On the other hand the problem does not occur if the CREATE TABLE is in the
same session. Nor if the REINDEX DATABASE is skipped.

#!/bin/sh

/usr/local/pgsql/bin/dropdb bug
/usr/local/pgsql/bin/createdb bug

/usr/local/pgsql/bin/psql -X bug << EOF

CREATE TABLE xxx (id SERIAL, col1 TEXT, col2 TEXT);

EOF

/usr/local/pgsql/bin/psql -e -X bug << EOF

REINDEX DATABASE bug;

ALTER TABLE xxx RENAME TO yyy;
ALTER TABLE yyy RENAME COLUMN col1 TO colA;
ALTER TABLE yyy RENAME COLUMN col2 TO colB;

EOF

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!