On Sep 21, 2004, at 6:17 AM, Ryszard Lach wrote:

> Second: what should I do with tables containing primary key  based on 
> multiple
> columns and what without primary key at all? Is 'table add key' enough 
> for
> them?
>

For multicolumn PK's you're all set.

For tables without PK's you have to add one.  I just went through this. 
  It can be painful for large tables, especially if they have FKs on 
them, as the FK triggers will queue up and make the update *incredibly* 
slow.  Letting slony do it will probably do the simple way that works 
well for small tables.

For the small tables (under 1M rows), I just added an integer column 
and created a corresponding sequence to use as the default, then 
updated that column to nextval(), then added the PK.  The names of 
things are chosen carefully so that the column can be represented as

   baz_log_id SERIAL PRIMARY KEY

in the table schema for recreating the database from scratch.

BEGIN;
CREATE SEQUENCE baz_log_baz_log_id_seq;
GRANT INSERT,UPDATE,DELETE,SELECT on baz_log_baz_log_id_seq to www;
ALTER TABLE ONLY baz_log ADD COLUMN baz_log_id INTEGER ;
ALTER TABLE ONLY baz_log ALTER COLUMN baz_log_id
  SET DEFAULT nextval('baz_log_baz_log_id_seq');
UPDATE baz_log SET baz_log_id=nextval('baz_log_baz_log_id_seq');
ALTER TABLE ONLY baz_log ADD CONSTRAINT baz_log_pkey
  PRIMARY KEY (baz_log_id);
COMMIT;
VACUUM FULL VERBOSE baz_log;

for the really big table (65M rows) I had to create a temp table with a 
SERIAL PK field, and copy the data into it, then add the FK checks to 
the new table.  For good measure, I added the indexes at the end of the 
data copy as well.


BEGIN;
DROP INDEX foo_track_foo_id;
DROP INDEX foo_track_user_id;
ALTER TABLE foo_track DROP CONSTRAINT "$1";
ALTER TABLE foo_track DROP CONSTRAINT "$2";
ALTER TABLE foo_track RENAME TO foo_track_old;

CREATE TABLE foo_track (
   foo_track_id SERIAL,
   foo_id integer NOT NULL,
   user_id integer NOT NULL,
   action_time timestamp default NOW() NOT NULL
) WITHOUT OIDS;

GRANT INSERT,UPDATE,SELECT on foo_track TO www;
GRANT INSERT,UPDATE,DELETE,SELECT on foo_track_foo_track_id_seq to www;

INSERT INTO foo_track (foo_id,user_id,click_time)
  SELECT foo_id,user_id,action_time FROM foo_track_old;

ALTER TABLE ONLY foo_track ADD CONSTRAINT foo_track_pkey
  PRIMARY KEY (foo_track_id);
CREATE INDEX foo_track_foo_id ON foo_track (foo_id);
CREATE INDEX foo_track_user_id ON foo_track (user_id);
ALTER TABLE ONLY foo_track ADD CONSTRAINT "$1" FOREIGN KEY (user_id)
  REFERENCES user_list(user_id) ON DELETE CASCADE DEFERRABLE;
ALTER TABLE ONLY foo_track ADD CONSTRAINT "$2" FOREIGN KEY (foo_id)
  REFERENCES foos(foo_id) ON DELETE CASCADE DEFERRABLE;
COMMIT;
ANALYZE foo_track;

DROP TABLE foo_track_old;



Vivek Khera, Ph.D.
+1-301-869-4449 x806

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2476 bytes
Desc: not available
Url : http://gborg.postgresql.org/pipermail/slony1-general/attachments/20040921/b5038fea/smime.bin