Hi Pavel, Thanks for you help. The code works ok on my database too when I call the procedure only once sequentially. I hit the segmentation fault consistently when I try to call the proc concurrently.
This is the actual code I am calling from within the procedure: UPDATE activity SET media_uri = a.media_uri ,record_last_updated_tstamp = CURRENT_TIMESTAMP FROM (SELECT col1.SOURCE_ID[gs.ser] source_id ,col2.MEDIA_URI[gs.ser] media_uri FROM (SELECT v_activity_source_id_list) As COL1(source_id) ,(select v_large_media_uri_list) AS COL2(media_uri) ,generate_series(1,v_uri_count) AS gs(ser)) a WHERE activity_source_id_value = a.source_id AND activity_source_key = v_source_key; -- v_large_media_uri_list and v_activity_source_id_list - both (TEXT ARRAY) are passed into the proc. Again this code works fine when I am calling it only once. I hit the problem when it is called twice at the same time. The previous code snippet causes a seg fault also. Interestingly when write: UPDATE activity SET activity_recency_timestamp = CURRENT_TIMESTAMP WHERE venue_id IN (SELECT UNNEST(v_venue_id_list)); It takes ages to run - as you point out it's not efficient code :) - but it doesn't cause a crash. Any ideas? regards Belinda On 24 November 2011 21:57, Pavel Stehule <pavel.steh...@gmail.com> wrote: > Hello > > This is not known bug - there should be bug in PostgreSQL or your database > (data files) can be broken. > > 2011/11/24 Belinda Cussen <belinda.cus...@servian.com.au> > >> Hi there, >> We're having segmentation faults on our postgres 9.1.1 db. It seems to >> happen when we use ARRAY unnesting eg: >> >> UPDATE activity SET activity_recency_timestamp = CURRENT_TIMESTAMP WHERE >> venue_id IN (SELECT venue_id FROM UNNEST(v_venue_id_list) venue_id); >> >> > This is not effective code > > try to use > > UPDATE activity SET activity_recency_timestamp = CURRENT_TIMESTAMP WHERE > venue_id = ANY(v_venue_id_list) > > Regards > > Pavel Stehule > > p.s. It working on my comp > > postgres=# CREATE TABLE foo (id int primary key, t timestamp, l int[]); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" > for table "foo" > CREATE TABLE > postgres=# INSERT INTO foo(id, l) SELECT i, ARRAY(SELECT * FROM > generate_series(i-10, i)) FROM generate_series(1,1000) g(i); > INSERT 0 1000 > postgres=# UPDATE foo SET t = CURRENT_TIMESTAMP WHERE id = ANY(l); > UPDATE 1000 > postgres=# UPDATE foo SET t = CURRENT_TIMESTAMP WHERE id IN (SELECT x FROM > unnest(l) x); > UPDATE 1000 > > Regards > > Pavel Stehule > > >> We are working on a getting a core dump but I was just wondering if there >> are any known issues around this construct - especially the aliasing? >> Alternatively could there be an issue trying to write or access tmp files? >> >> >> FYI: >> v_venue_id_list is an array passed in to the procedure containing 100,000 >> INTEGER elements >> ? IS THIS TOO MANY ELEMENTS TO PASS? >> >> table activity has around 3,000,000 rows >> CREATE TABLE activity >> ( >> activity_id serial NOT NULL, >> activity_type_key integer NOT NULL, >> media_type_key integer NOT NULL, >> activity_source_key integer NOT NULL, >> venue_id integer NOT NULL, >> poster_id integer NOT NULL, >> event_id integer, >> activity_source_id_value text NOT NULL, >> uri text, >> media_uri text, >> activity_comment text, >> posted_dttm timestamp with time zone, >> photo_format_code character varying(10), >> video_format_code character varying(10), >> public_yn character varying(1), >> content_reported_yn character varying(1), >> last_scored_tstamp timestamp with time zone, >> record_expiry_tstamp timestamp with time zone, >> record_created_tstamp timestamp with time zone DEFAULT now(), >> record_last_updated_tstamp timestamp with time zone DEFAULT now(), >> initial_broadcast_to_text text, >> image_id integer, >> large_media_uri text, >> CONSTRAINT activity_pkey PRIMARY KEY (activity_id ), >> CONSTRAINT activity_activity_source_key_activity_source_id_value_key >> UNIQUE (activity_source_key , activity_source_id_value ) >> ); >> >> >> CREATE INDEX activity_poster_ie >> ON activity (poster_id ); >> >> >> CREATE INDEX activity_venue_ie >> ON activity (venue_id ); >> >> >> -- >> [image: Servian Logo] *Belinda Cussen* | Servian Pty >> Ltd<http://www.servian.com.au/> | >> *m:* 0466 309 169 | *t:* 02 9376 0700 | f*:* 02 9376 0730 >> > > -- [image: Servian Logo]*Belinda Cussen* | Servian Pty Ltd<http://www.servian.com.au/> | *m:* 0466 309 169 | *t:* 02 9376 0700 | f*:* 02 9376 0730
