From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Belinda Cussen <belinda(dot)cussen(at)servian(dot)com(dot)au> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Does anyone know of any issues around ARRAY UNNEST |
Date: | 2011-11-24 10:57:39 |
Message-ID: | CAFj8pRCRUtQrrdYZEAyMRs-T2GrkKXj8uoNF6_WPJPf_B4JaGQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Belinda Cussen | 2011-11-24 21:09:43 | Re: Does anyone know of any issues around ARRAY UNNEST |
Previous Message | Belinda Cussen | 2011-11-23 23:47:35 | Does anyone know of any issues around ARRAY UNNEST |