Re: Does anyone know of any issues around ARRAY UNNEST - Mailing list pgsql-sql

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.stehule@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.cussen@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 );

--

Belinda Cussen |  Servian Pty Ltd | m: 0466 309 169 | t: 02 9376 0700 | f: 02 9376 0730

--

Belinda Cussen |  Servian Pty Ltd | m: 0466 309 169 | t: 02 9376 0700 | f: 02 9376 0730