Обсуждение: intarray index

Поиск
Список
Период
Сортировка

intarray index

От
Marek Lewczuk
Дата:
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 ?

Thanks.

ML

Re: intarray index

От
Oleg Bartunov
Дата:
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

Re: BUG when migrating from 8.0 to 8.1 with create temp table:SORRY MY

От
David Gagnon
Дата:
The log was not written I think

Sorry for the noise
/David


David Gagnon wrote:

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

BUG when migrating from 8.0 to 8.1 with create temp table

От
David Gagnon
Дата:
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