Обсуждение: Does anyone know of any issues around ARRAY UNNEST

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

Belinda Cussen <belinda.cussen@servian.com.au> writes:
> 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.

What do you mean by "concurrently"?  There is no multithreading within
Postgres backends, and it's really hard to believe that unnest would be
affected by what's happening in other server processes.

                       regards, tom lane