Re: Suggestions for blocking user inserts during admin bulk loading.

"Woody Woodring" <george(dot)woodring(at)iglass(dot)net> writes:
> 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;

I suspect the reason it's not working is that a LOCK only lasts the
duration of the current transaction, which is only that statement itself
if you have no BEGIN block around it. What you want is something like

truncate ...
...
BEGIN;
LOCK master IN EXCLUSIVE MODE;
INSERT INTO master (mac, . . .) SELECT mac, . . . FROM temp_table WHERE mac
NOT IN (SELECT mac from master) ORDER BY mac;
COMMIT;

Whether this is the best solution is not clear, but at least it would
work like you expected.

regards, tom lane