[SQL] Does anyone know of any issues around ARRAY UNNEST

[Belinda Cussen]

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);

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 |
*m:* 0466 309 169 | *t:* 02 9376 0700 | f*:* 02 9376 0730

Re: [SQL] Does anyone know of any issues around ARRAY UNNEST

[Pavel Stehule]

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 

> 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 |
> *m:* 0466 309 169 | *t:* 02 9376 0700 | f*:* 02 9376 0730
>

[SQL] strange error message

[Uwe Bartels]

Hi,

I've got a strange error message in our java application.
the only reference to this message i find in
http://mingo.info-science.uiowa.edu/eichmann/courses/database/Postgres/programmer.pdf.
but I still don't know how to solve this or even what the source od this
error is.
currently I don't have the executed sql yet. I'm trying to get this.
Any Idea what that could be?

ERROR JDBCTransaction:124 - JDBC commit failed
*org.postgresql.util.PSQLException: ERROR: bogus varno: 65001*
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:671)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:691)
at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$ConnectionHandler.invoke(AbstractJdbc23PooledConnection.java:352)
at $Proxy24.commit(Unknown Source)
at
org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:139)
at
org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:115)
...

Thanks,
Uwe