Обсуждение: can't figure string compare result (using also custom C function)

I have this query: (1 result)

SELECT idSpecie,nome  FROM specienomi
WHERE idspecie=37026 and nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
  idspecie |                  nome
----------+-----------------------------------------
     37026 | X Agropogon littoralis (Sm.) C.E. Hubb.

The same query but without one condition: no results.

SELECT idSpecie,nome  FROM specienomi
WHERE nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
  idspecie | nome
----------+------
(0 rows)

I can't figure why, can someone tell me how investigate?

specienomi is a view
idSpecie is a numeric field (the  key of another table)
nome is a text field generated by a custom C function (using 18 fields
(1 enumerate type, 1 boolean, 16 text).

The problem arises only with particular records, when the first
character of the string is generated by my function [1].
When the first character is copied from postgres parameter [2] all works
fine.

[1] buffer[0]='X'; buffer[1]=' ';
[2] memcpy(buffer,VARDATA(part),VARSIZE(part)-VARHDRSZ)

Thank you
Edoardo

On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote:
> The problem arises only with particular records, when the first
> character of the string is generated by my function [1].
> When the first character is copied from postgres parameter [2] all works
> fine.
>
> [1] buffer[0]='X'; buffer[1]=' ';
> [2] memcpy(buffer,VARDATA(part),VARSIZE(part)-VARHDRSZ)

I'm not much of an expert with extending PG in C; but my first
suggestion would be are you null terminating the string?

If you are, could you include a (cut down) portion of the code that
demonstrates the problem?

--
  Sam  http://samason.me.uk/

Sam Mason ha scritto:
> On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote:
>> The problem arises only with particular records, when the first
>> character of the string is generated by my function [1].
>> When the first character is copied from postgres parameter [2] all works
>> fine.
>>
>> [1] buffer[0]='X'; buffer[1]=' ';
>> [2] memcpy(buffer,VARDATA(part),VARSIZE(part)-VARHDRSZ)
>
> I'm not much of an expert with extending PG in C; but my first
> suggestion would be are you null terminating the string?
the code above is only a fragment, with a 0 at the end of the buffer
there are a lot more problems, I use SET_VARSIZE().
The problem does not affect all my records, only 6/25480.

> If you are, could you include a (cut down) portion of the code that
> demonstrates the problem?

the problem seems to be at the start of the string:

guidebook=> SELECT idSpecie,nome FROM specienomi
WHERE nome like 'X Agropogon littoralis (Sm.) C.E. Hubb.';
  idspecie | nome
----------+------
(0 rows)


SELECT idSpecie,nome FROM specienomi
WHERE nome like '%X Agropogon littoralis (Sm.) C.E. Hubb.';
  idspecie |                  nome
----------+-----------------------------------------
     37026 | X Agropogon littoralis (Sm.) C.E. Hubb.
(1 row)


guidebook=> SELECT idSpecie,nome FROM specienomi
WHERE nome like 'X Agropogon littoralis (Sm.) C.E. Hubb.%';
  idspecie | nome
----------+------
(0 rows)

The function is very ripetitive (and whith italian names for variables).
I did a try with a shorter one but can't obtain the same bug (sorry).
there is a "HERE" near the lines that seems to be problematic.

Edoardo


----------------------------- function ------------------------------
char *prefissoSottospecie="subsp. ";
#define LUNGHEZZA_PREF_SS 7
char *prefissoVarieta="var. ";
#define LUNGHEZZA_PREF_VAR 5
char *prefissoSottoVarieta="subvar. ";
#define LUNGHEZZA_PREF_SVAR 8
char *prefissoForma="f. ";
#define LUNGHEZZA_PREF_FO 3
char *prefissoRace="race ";
#define LUNGHEZZA_PREF_RACE 5
char *prefissoSublusus="sublusus ";
#define LUNGHEZZA_PREF_SUBLUSUS 9
char *prefissoCultivar="c.v. ";
#define LUNGHEZZA_PREF_CV 5
char *suffissoProParte="p.p. ";
#define LUNGHEZZA_POST_PP 5

#define TEST_IBRIDO(n)
{if(strcmp(ibrido,(n))==0){buffer[caratteriInseriti]='x';buffer[caratteriInseriti+1]='
';caratteriInseriti+=2;}}
#define INSERISCI_PARTE(parte)

{memcpy(buffer+caratteriInseriti,VARDATA(parte),VARSIZE(parte)-VARHDRSZ);caratteriInseriti+=VARSIZE(parte)-VARHDRSZ+1;buffer[caratteriInseriti-1]='

';}

// posizione degli ibridi
#define IBRIDO_GENERE "genus"
#define IBRIDO_SPECIE "specie"
#define IBRIDO_SOTTOSPECIE "subspecie"
#define IBRIDO_VARIETA "variety"
#define IBRIDO_SOTTOVARIETA "subvariety"
#define IBRIDO_FORMA "form"
#define IBRIDO_RACE "race"
#define IBRIDO_SUBLUSUS "sublusus"
#define IBRIDO_CULTIVAR "cultivar"

PG_FUNCTION_INFO_V1(esterna_nome);

Datum esterna_nome(PG_FUNCTION_ARGS){
    char buffer[300];
    int  caratteriInseriti=0; // tiene il conto dei caratteri presenti in
buffer
    Datum datumIbrido        = PG_GETARG_DATUM(0);
    bool proParte            = PG_GETARG_BOOL(1);
    text *genere              = (PG_ARGISNULL( 2) || VARSIZE(PG_GETARG_TEXT_P(
2))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(2));
    text *specieNome         = (PG_ARGISNULL( 3) ||
VARSIZE(PG_GETARG_TEXT_P( 3))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(3));
    text *specieAutore       = (PG_ARGISNULL( 4) ||
VARSIZE(PG_GETARG_TEXT_P( 4))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(4));
    text *sottospecieNome    = (PG_ARGISNULL( 5) ||
VARSIZE(PG_GETARG_TEXT_P( 5))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(5));
    text *sottospecieAutore  = (PG_ARGISNULL( 6) ||
VARSIZE(PG_GETARG_TEXT_P( 6))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(6));
    text *varietaNome        = (PG_ARGISNULL( 7) ||
VARSIZE(PG_GETARG_TEXT_P( 7))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(7));
    text *varietaAutore      = (PG_ARGISNULL( 8) ||
VARSIZE(PG_GETARG_TEXT_P( 8))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(8));
    text *sottoVarietaNome   = (PG_ARGISNULL( 9) ||
VARSIZE(PG_GETARG_TEXT_P( 9))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(9));
    text *sottoVarietaAutore = (PG_ARGISNULL(10) ||
VARSIZE(PG_GETARG_TEXT_P(10))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(10));
    text *formaNome          = (PG_ARGISNULL(11) ||
VARSIZE(PG_GETARG_TEXT_P(11))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(11));
    text *formaAutore        = (PG_ARGISNULL(12) ||
VARSIZE(PG_GETARG_TEXT_P(12))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(12));
    text *raceNome           = (PG_ARGISNULL(13) ||
VARSIZE(PG_GETARG_TEXT_P(13))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(13));
    text *raceAutore         = (PG_ARGISNULL(14) ||
VARSIZE(PG_GETARG_TEXT_P(14))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(14));
    text *sublususNome       = (PG_ARGISNULL(15) ||
VARSIZE(PG_GETARG_TEXT_P(15))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(15));
    text *sublususAutore     = (PG_ARGISNULL(16) ||
VARSIZE(PG_GETARG_TEXT_P(16))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(16));
    text *cultivar           = (PG_ARGISNULL(17) ||
VARSIZE(PG_GETARG_TEXT_P(17))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(17));
    text *nomeEsterno;
    Datum unrolled;
    char *ibrido;
    char debug[300];

    unrolled = DirectFunctionCall1(enum_out,datumIbrido);
    ibrido = DatumGetCString(unrolled);

    // genere
    if(strcmp(ibrido,IBRIDO_GENERE)==0){
        // HERE
        buffer[0]='X';
        buffer[1]=' ';
        caratteriInseriti=2;
    }
    INSERISCI_PARTE(genere);
    // specie
    TEST_IBRIDO(IBRIDO_SPECIE);
    if(specieNome!=NULL){
        INSERISCI_PARTE(specieNome);
        if(specieAutore!=NULL){
            INSERISCI_PARTE(specieAutore);
        }
    }
    // sottospecie
    if(sottospecieNome!=NULL){
        memcpy(buffer+caratteriInseriti,prefissoSottospecie,LUNGHEZZA_PREF_SS);
        caratteriInseriti+=LUNGHEZZA_PREF_SS;
        TEST_IBRIDO(IBRIDO_SOTTOSPECIE);
        INSERISCI_PARTE(sottospecieNome);
        if(sottospecieAutore!=NULL){
            INSERISCI_PARTE(sottospecieAutore);
        }
    }
    // varieta
    if(varietaNome!=NULL){
        memcpy(buffer+caratteriInseriti,prefissoVarieta,LUNGHEZZA_PREF_VAR);
        caratteriInseriti+=LUNGHEZZA_PREF_VAR;
        TEST_IBRIDO(IBRIDO_VARIETA);
        INSERISCI_PARTE(varietaNome);
        if(varietaAutore!=NULL){
            INSERISCI_PARTE(varietaAutore);
        }
    }
    // sotto varieta
    if(sottoVarietaNome!=NULL){
        memcpy(buffer+caratteriInseriti,prefissoSottoVarieta,LUNGHEZZA_PREF_SVAR);
        caratteriInseriti+=LUNGHEZZA_PREF_SVAR;
        TEST_IBRIDO(IBRIDO_SOTTOVARIETA);
        INSERISCI_PARTE(sottoVarietaNome);
        if(sottoVarietaAutore!=NULL){
            INSERISCI_PARTE(sottoVarietaAutore);
        }
    }
    // forma
    if(formaNome!=NULL){
        memcpy(buffer+caratteriInseriti,prefissoForma,LUNGHEZZA_PREF_FO);
        caratteriInseriti+=LUNGHEZZA_PREF_FO;
        TEST_IBRIDO(IBRIDO_FORMA);
        INSERISCI_PARTE(formaNome);
        if(formaAutore!=NULL){
            INSERISCI_PARTE(formaAutore);
        }
    }

    // race
    if(raceNome!=NULL){
        memcpy(buffer+caratteriInseriti,prefissoRace,LUNGHEZZA_PREF_RACE);
        caratteriInseriti+=LUNGHEZZA_PREF_RACE;
        TEST_IBRIDO(IBRIDO_RACE);
        INSERISCI_PARTE(raceNome);
        if(raceAutore!=NULL){
            INSERISCI_PARTE(raceAutore);
        }
    }
    // sublusus
    if(sublususNome!=NULL){
        memcpy(buffer+caratteriInseriti,prefissoSublusus,LUNGHEZZA_PREF_SUBLUSUS);
        caratteriInseriti+=LUNGHEZZA_PREF_SUBLUSUS;
        TEST_IBRIDO(IBRIDO_SUBLUSUS);
        INSERISCI_PARTE(sublususNome);
        if(sublususAutore!=NULL){
            INSERISCI_PARTE(sublususAutore);
        }
    }

    // cultivar
    if(cultivar!=NULL){
        memcpy(buffer+caratteriInseriti,prefissoCultivar,LUNGHEZZA_PREF_CV);
        caratteriInseriti+=LUNGHEZZA_PREF_CV;
        TEST_IBRIDO(IBRIDO_CULTIVAR);
        INSERISCI_PARTE(cultivar);
    }
    //pro parte
    if(proParte==true){
        memcpy(buffer+caratteriInseriti,suffissoProParte,LUNGHEZZA_POST_PP);
        caratteriInseriti+=LUNGHEZZA_POST_PP;
    }

    nomeEsterno=(text *)palloc(caratteriInseriti-1+VARHDRSZ);
    memcpy(VARDATA(nomeEsterno),buffer,caratteriInseriti-1);
    // VARATT_SIZEP(nomeEsterno)=caratteriInseriti-1+VARHDRSZ;  // per
postgreSQL <=8.2
    SET_VARSIZE(nomeEsterno, caratteriInseriti-1+VARHDRSZ); // per
postgreSQL 8.3
    PG_RETURN_TEXT_P(nomeEsterno);
}

On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote:
> SELECT idSpecie,nome  FROM specienomi
> WHERE idspecie=37026 and nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
>  idspecie |                  nome
> ----------+-----------------------------------------
>     37026 | X Agropogon littoralis (Sm.) C.E. Hubb.
>
> The same query but without one condition: no results.
>
> SELECT idSpecie,nome  FROM specienomi
> WHERE nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
>  idspecie | nome
> ----------+------
> (0 rows)
>
> I can't figure why, can someone tell me how investigate?

I've just looked back in the archives and noticed that you were asking
about functional indexes; you do know that if you change the definition
of a function that PG doesn't know to rebuild the index don't you?

That would exhibit the symptoms you're seeing; i.e. the first case is
using an index on "idspecie" and the second is using the (out-of-date)
functional index.

--
  Sam  http://samason.me.uk/

Sam Mason ha scritto:
> On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote:
>> SELECT idSpecie,nome  FROM specienomi
>> WHERE idspecie=37026 and nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
>>  idspecie |                  nome
>> ----------+-----------------------------------------
>>     37026 | X Agropogon littoralis (Sm.) C.E. Hubb.
>>
>> The same query but without one condition: no results.
>>
>> SELECT idSpecie,nome  FROM specienomi
>> WHERE nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
>>  idspecie | nome
>> ----------+------
>> (0 rows)
>>
>> I can't figure why, can someone tell me how investigate?
>
> I've just looked back in the archives and noticed that you were asking
> about functional indexes; you do know that if you change the definition
> of a function that PG doesn't know to rebuild the index don't you?
>
> That would exhibit the symptoms you're seeing; i.e. the first case is
> using an index on "idspecie" and the second is using the (out-of-date)
> functional index.
Tank you!

After you answer it is obvious (I have an IMMUTABLE at the and of function).

I was working with the same function using plpgsql but it is 2.5 times
slower, and plperl is slower.

REINDER TABLE specie;

and all works fine.

Is it reported on the documentation? (this is not a critic regarding
postgres very well done documentation!) I must read it with more attention.

Tank you again
Edoardo