What are my options to avoid a Row Exclusive/RowShareLock conflict / is there a better way?

[rox <rox@xxxxxxxxxxx>]

I have a long running stored procedure that pulls data from 2-3 tables, 
updates columns in a row in the IMPORT_STATUS table, creates a couple of 
temp tables, and then deletes/inserts/updates back into 3-4 tables... 
but never once does anything directly with the USER table.

The long running stored procedure is run in the background from php via 
cron... "a queued event" which the user can cause to happen at just 
about any time, but which may not run for some time, and then runs for 
as short as a few seconds, usually averaging minutes, and occasionally 
goes for up to an hour or so.

I have a logon process that selects from the USER table, then does an 
update on it and inserts into an EVENT table.

If the stored procedure is accessing records that belong to the user, 
the user can not log in because there is a RowShareLock against the USER 
table from the stored procedure and the login process does an UPDATE 
which attempts to grab a Row Exclusive lock.

I know at this point that if I remove the Foreign Key constraint from 
the IMPORT_STATUS table, the RowShareLock is not grabbed on the USER 
table when the stored procedure runs.  Nominally, that fixes my problem.

However I'm curious, so here goes... I know the stored procedure isn't 
referencing anything related to that foreign key relationship.  Does it 
always grab a ShareLock on all FK constraints even if the columns are 
not referenced?  What if the column is allowed NULL?

Is my best option just to remove the FK constraint on IMPORT_STATUS and 
then ensure that the application otherwise enforces the requirement that 
the user_id exists in the User table on insert/update?

Is there a better way to capture active running "status"  information?  
Our approach leaves no visibility to progress until the procedure ends.

thanks,

Roxanne
"PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc 
(Ubuntu/Linaro 4.6.3-1ubuntu2) 4.6.3, 64-bit"


CREATE OR REPLACE FUNCTION buildGeom (_collection_id integer, 
_import_status_id integer, _import_status_order integer)
  RETURNS SETOF collection_points AS
{
     ...
     UPDATE import_status SET ppa_cleaned[_import_status_order] = 
_i_count_points WHERE import_status_id = _import_status_id;
     ...
     UPDATE import_status SET ppa_deleted[_import_status_order] = 
_i_count_pointsdeleted WHERE import_status_id = _import_status_id;
     ....
}

CREATE TABLE import_status
(
  import_status_id integer NOT NULL DEFAULT 
nextval('import_status_import_status_id_seq'::regclass), -- Artificial 
primary key
  queue_id integer,
  files_id integer,
  user_id integer NOT NULL,
  import_status_type_id integer NOT NULL,
  point_count integer,
  start_time timestamp without time zone,
  end_time timestamp without time zone,
  ppa_parsed integer[],
  ppa_saved integer[],
  ppa_cleaned integer[],
  ppa_deleted integer[],
  time_queued timestamp without time zone[],
  time_started timestamp without time zone[],
  time_parsed timestamp without time zone[],
  time_saved timestamp without time zone[],
  time_processed timestamp without time zone[],
  time_ended timestamp without time zone[],
  CONSTRAINT import_status_id_pk PRIMARY KEY (import_status_id ),
  CONSTRAINT import_status_files_id_fkey FOREIGN KEY (files_id)
      REFERENCES public.files (files_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT import_status_import_status_type_id_fkey FOREIGN KEY 
(import_status_type_id)
      REFERENCES public.import_status_type (import_status_type_id) 
MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT import_status_queue_id_fkey FOREIGN KEY (queue_id)
      REFERENCES public.queue (queue_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT import_status_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES public."user" (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (  OIDS=FALSE );

CREATE TABLE "user"
(
  user_id integer NOT NULL DEFAULT 
nextval('user_user_id_seq'::regclass),
  user_type_id integer NOT NULL,
  name character varying(255) NOT NULL,
  password character varying(255) NOT NULL,
  status boolean NOT NULL DEFAULT true,
  address1 character varying(100),
  address2 character varying(100),
  city character varying(50),
  state character varying(20),
  postal_code character varying(20),
  email_address character varying(254),
  full_name character varying(75),
  session_id character varying(40),
  CONSTRAINT user_pkey PRIMARY KEY (user_id ),
  CONSTRAINT user_user_type_id_fkey FOREIGN KEY (user_type_id)
      REFERENCES public.user_type (user_type_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (  OIDS=FALSE );



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general