Re: Does anyone know of any issues around ARRAY UNNEST - Mailing list pgsql-sql
Hello
This is not known bug - there should be bug in PostgreSQL or your database (data files) can be broken.
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>
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
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 rowsCREATE 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_ieON activity (poster_id );CREATE INDEX activity_venue_ieON activity (venue_id );--
Belinda Cussen | Servian Pty Ltd | m: 0466 309 169 | t: 02 9376 0700 | f: 02 9376 0730