I have a table that users can update if the data is old. Once a day I
update every entry in the table. However I get primary key violations
occasionally which it seems a user inserted into the table while the bulk
insert is going on.
The following is the procedure I use for updating the entire table, mac is
the primary key:
truncate master;
create temp_table;
COPY "temp_table" (mac, . . .) FROM stdin WITH DELIMITER AS '|';
UPDATE master SET mac=temp_table.mac . . . FROM temp_table WHERE
master.mac=temp_table.mac;
LOCK master IN EXCLUSIVE MODE; -- Added this step to keep user out to avoid
conflicts, not really working
INSERT INTO master (mac, . . .) SELECT mac, . . . FROM temp_table WHERE mac
NOT IN (SELECT mac from master) ORDER BY mac;
Any suggestions would be appreciated,
Woody
----------------------------------------
iGLASS Networks
3300 Green Level Rd. West
Cary NC 27519
(919) 387-3550 x813
www.iglass.net