Re: Does anyone know of any issues around ARRAY UNNEST

Hi Pavel,
I've just tried to reproduce the bug using your setup script and it doesn't
cause the seg fault.

We have copied one table: activity, from our code base on to a brand new db
(on a laptop so independent of any hardware issues), and we are using that
to test with.

I'm thinking there may some corrupted data or is it possible to be the
table size?

Again, thanks for your help.
regards
Belinda

On 25 November 2011 08:09, Belinda Cussen <belinda(dot)cussen(at)servian(dot)com(dot)au>wrote:

> 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(dot)stehule(at)gmail(dot)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(dot)cussen(at)servian(dot)com(dot)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
>

--
[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