Обсуждение: renaming a table, and its primary key constraint

Hello,

Forgive me if this is a dopey question.  I'm running a web app with a
dB that takes me a half hour to regenerate.  Instead of closing down
every day, I'd like insert the data into a temp table "stuff_tmp" and
then rename that to the permanent table "stuff", so the application is
not off-line for so long.

The table "stuff_tmp" has a primary key constraint.  When I rename the
table, this constraint does not get renamed-- it continues to have the
name "stuff_tmp_pkey"-- and (you guessed it) the next time I run the
script pg complains that it can't make "stuff_tmp" because the
constraint already exists.

That I can see, I can't rename the constraint.  Do I have that correct?

So I thought to drop the constraint.  That I can see I can't add a
primary key constraint "stuff_pkey".  Is that correct?

Can I simulate (sort of) a primary key constraint by adding a UNIQUE
index, and a NOT NULL check?  That is, if I add those two, do I lose
anything compared with the original primary key constraint?

Thank you,
Jim

On Sat, Jul 30, 2005 at 02:02:16PM -0700, Jim wrote:
> Hello,
>
> Forgive me if this is a dopey question.  I'm running a web app with a
> dB that takes me a half hour to regenerate.  Instead of closing down
> every day, I'd like insert the data into a temp table "stuff_tmp" and
> then rename that to the permanent table "stuff", so the application is
> not off-line for so long.
>
> The table "stuff_tmp" has a primary key constraint.  When I rename the
> table, this constraint does not get renamed-- it continues to have the
> name "stuff_tmp_pkey"-- and (you guessed it) the next time I run the
> script pg complains that it can't make "stuff_tmp" because the
> constraint already exists.
>
> That I can see, I can't rename the constraint.  Do I have that correct?

Well, you can. Something like:

BEGIN:
ALTER TABLE stuff_tmp_pkey RENAME TO stuff_pkey;
ALTER TABLE stuff ALTER pkey SET DEFAULT nextval('stuff_pkey');
COMMIT;

> Can I simulate (sort of) a primary key constraint by adding a UNIQUE
> index, and a NOT NULL check?  That is, if I add those two, do I lose
> anything compared with the original primary key constraint?

Only that foreign key references don't automatically find the right
column by default, but you can still specify directly...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

"Jim" <jhefferon@smcvt.edu> writes:
> The table "stuff_tmp" has a primary key constraint.  When I rename the
> table, this constraint does not get renamed-- it continues to have the
> name "stuff_tmp_pkey"-- and (you guessed it) the next time I run the
> script pg complains that it can't make "stuff_tmp" because the
> constraint already exists.

If you can update to PG 8.0, this problem should pretty much go away,
since it chooses nonconflicting names by default:

regression=# create table stuff_tmp(f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "stuff_tmp_pkey" for table "stuff_tmp"
CREATE TABLE
regression=# alter table stuff_tmp rename to stuff;
ALTER TABLE
regression=# create table stuff_tmp(f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "stuff_tmp_pkey1" for table "stuff_tmp"
CREATE TABLE
regression=#

> That I can see, I can't rename the constraint.  Do I have that correct?

Yes, but you can rename the underlying index (use ALTER TABLE for this).

> So I thought to drop the constraint.  That I can see I can't add a
> primary key constraint "stuff_pkey".  Is that correct?

No.  Try "alter table t add constraint foo primary key(f1)"

> Can I simulate (sort of) a primary key constraint by adding a UNIQUE
> index, and a NOT NULL check?  That is, if I add those two, do I lose
> anything compared with the original primary key constraint?

It doesn't create a default REFERENCES target for foreign keys; which
may or may not be important to you.  If it is, the whole idea won't
work at all, because dropping a table and renaming another one into
its place isn't going to cause foreign key references to transfer over.

There are other hazards involved in the idea, too, due to the fact that
cached plans won't transfer over.  Which in particular means that
plpgsql functions using the table are likely to fail.

Depending on how big the table is, you might be better off with
    ... compute new data in stuff_tmp ...
    BEGIN;
    TRUNCATE TABLE stuff;  -- acquires exclusive lock
    INSERT INTO stuff SELECT * FROM stuff_tmp;
    COMMIT;
Because of the lock, the intermediate state with no data isn't
visible to other transactions; the only effect will be a delay
until they can get at the table.  (Note that the equivalent effect
would be hard to get in a RENAME-based solution, because in that
case you don't have a single continuously-existing table you can
use a lock on.)

            regards, tom lane

Thank you both for the replies.  I obviously have some things wrong,
and I'll have to masticate on the answers.

AIUI, the foreign key references are not an issue for me since I always
specify the column name.

Thanks again,
Jim