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

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

On Wed, Mar 11, 2009 at 8:01 AM, Woody Woodring
<george.woodring@iglass.net> wrote:
> 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.

Yeah, you need to first deny access rights to all users that aren't
the one doing the loading, then kick off all the users that are
already connected.  To remove the rights of anyone else to connect,
the easiest way is to edit pg_hba.conf / have a special pg_hba.conf to
copy into place, then reload the db.  After that, from a shell script
or something, you need to kill the backends that are still connected.

"Woody Woodring" <george.woodring@iglass.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