intarray index

On Mon, 28 Nov 2005, Marek Lewczuk wrote:

Hello,
I have a question about IntArray contrib package. The docs says:
"...current implementation provides index support for one-dimensional array
of
int4's - gist__int_ops, suitable for small and medium size of arrays (used on
default), and gist__intbig_ops for indexing large arrays (we use superimposed
signature with length of 4096 bits to represent sets)..."

How many elements within single array is suggested within small/medium/large
array ? I have arrays with up to 200 elements - which index I should use ?

gist__intbig_ops

Thanks.

ML

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Hi all,

I just migrated from 8.0 to 8.1 and one of my stored procecure just
hang when trying to create a temp table

Here is my call:

select * from usp_Comptabilite_AgeDeCompteClient('M', null,
'2005-01-28', '1', '1', '0', null)

If you look below in the log you will see that the function just hang
when creating T_CP1 table.

Is that a know bug .. this code worked with 8.0

Thanks for your help
Best Regards
/David

--- FUNCTION
CREATE OR REPLACE FUNCTION usp_Comptabilite_AgeDeCompteClient(VARCHAR, 
VARCHAR, DATE, BOOLEAN, BOOLEAN, INT, VARCHAR) RETURNS refcursor  AS $$
DECLARE

companyId ALIAS FOR $1;
Compte ALIAS FOR $2;
DateRef ALIAS FOR $3;

DateType ALIAS FOR $4;
Tri ALIAS FOR $5;
NBJour ALIAS FOR $6;
BORRNUMR ALIAS FOR $7;
DateRef_ DATE;

ref refcursor;
statement varchar(4000);
temp RECORD;

BEGIN

IF (DateRef IS NOT NULL) THEN
DateRef_:=DateRef;
ELSE
DateRef_:=CURRENT_DATE;
END IF;

-- Toutes les ressources présentes (incluant le regroupement comptable)
EXECUTE '
CREATE TEMP TABLE T_RR (
RRNUM VARCHAR(10) PRIMARY KEY
) ON COMMIT DROP';

EXECUTE '
CREATE TEMP TABLE T_CR1 (
CRNUM INT PRIMARY KEY,
CRYPNUM VARCHAR(10),
CRMONT DECIMAL,
CRDATE DATE,
CRRRNUM VARCHAR(10),
CRACNUM VARCHAR(10),
GLNUM VARCHAR(10),
GLDESC_PRI varchar (100),
GLDESC_SEC varchar (100),
RRRRNUM VARCHAR(10),
RRGROUP INT
) ON COMMIT DROP';

EXECUTE '
CREATE TEMP TABLE T_CP1 (
CRNUM INT PRIMARY KEY,
Paye DECIMAL
) ON COMMIT DROP';

EXECUTE '
CREATE TEMP TABLE T_RA (
RRNUM VARCHAR(10),
RANUM INTEGER
) ON COMMIT DROP';

EXECUTE '
CREATE TEMP TABLE T_CR2 (
CRNUM INT,
CRMONT NUMERIC,
CRDATE DATE,
CRRRNUM VARCHAR(10),
CRACNUM VARCHAR(10),
Solde NUMERIC,
GLNUM VARCHAR(10),
GLDESC_PRI VARCHAR(100),
GLDESC_SEC VARCHAR(100),
RRRRNUM VARCHAR(10),
RRGROUP INT
) ON COMMIT DROP';

-- Sélection des infos des comptes à recevoir
statement := ' INSERT INTO T_CR1 ( CRNUM, CRYPNUM, CRMONT, CRDATE,
CRRRNUM, CRACNUM,
GLNUM, GLDESC_PRI, GLDESC_SEC,
RRRRNUM,
RRGROUP)

----LOG-----------------
OG: statement: select * from usp_Comptabilite_AgeDeCompteClient('M',
null, '2005-01-28', '1', '1', '0', null)
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t_rr_pkey" for table "t_rr"
CONTEXT: SQL statement "

CREATE TEMP TABLE T_RR (

RRNUM VARCHAR(10) PRIMARY KEY

) ON COMMIT DROP"
PL/pgSQL function "usp_comptabilite_agedecompteclient" line 28 at
execute statement
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t_cr1_pkey" for table "t_cr1"
CONTEXT: SQL statement "

CREATE TEMP TABLE T_CR1 (

CRNUM INT PRIMARY KEY,

CRYPNUM VARCHAR(10),

CRMONT DECIMAL,

CRDATE DATE,

CRRRNUM VARCHAR(10),

CRACNUM VARCHAR(10),

GLNUM VARCHAR(10),

GLDESC_PRI varchar (100),

GLDESC_SEC varchar (100),

RRRRNUM VARCHAR(10),

RRGROUP INT

) ON COMMIT DROP"
PL/pgSQL function "usp_comptabilite_agedecompteclient" line 33 at
execute statement
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t_cp1_pkey" for table "t_cp1"
CONTEXT: SQL statement "

CREATE TEMP TABLE T_CP1 (

CRNUM INT PRIMARY KEY,

Paye DECIMAL

) ON COMMIT DROP"
PL/pgSQL function "usp_comptabilite_agedecompteclient" line 48 at
execute statement

The log was not written I think

Sorry for the noise
/David

David Gagnon wrote: