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 >
