On Dec 6, 2004, at 8:51 PM, Marc G. Fournier wrote:

> after the problems with my install on the dns table, and learning 
> of/using slony_setup.pl, I'm curious as to how ppl are dealing with a 
> database that contains tables with no primary keys?
>

I had this issue, but I solved it externally since the tables are HUGE 
and letting slony do it would just bloat my tables incredibly, and 
would happen whenever slony got around to it, not when I could control 
the application access to the table.

For small tables I did this type of thing:

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

Now, for the big tables, the update to set the new PKEY values takes 
forever (literally).  So the only option was to create a new table with 
a SERIAL column and no constraints or triggers or FKs or indexes, copy 
the data into it and then add the necessary constraints, triggers, FKs 
and indexes.  Finally drop the old table -- all within a transaction 
while access to that table is turned off from the application.


-------------- 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/20041207/eeb22481/smime.bin