Re: a bug in plpgsql

Folks,

This is a new one on me, but possibly because it's my first time working on
Gentoo:

Version: 8.0b4
Platform: Gentoo Linux 2.6.8
Severity: Unknown
Message:
LOG: could not open directory "/usr/share/timezone": No such file or
directory

What's it mean? Am I missing a package?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> LOG: could not open directory "/usr/share/timezone": No such file or
> directory

> What's it mean? Am I missing a package?

Hmmm ... where is that coming from exactly? PG itself should not be
referring to /usr/share/timezone --- we have our own TZ database now.
And glibc-based platforms don't keep their info there either (it's in
/usr/share/zoneinfo, at least on my Fedora machine). So there's
something awfully odd here.

regards, tom lane

Tom,

> Hmmm ... where is that coming from exactly? PG itself should not be
> referring to /usr/share/timezone --- we have our own TZ database now.
> And glibc-based platforms don't keep their info there either (it's in
> /usr/share/zoneinfo, at least on my Fedora machine). So there's
> something awfully odd here.

That's postmaster's feedback on startup. Here's a clue: when I tried to
enable automated log rotation (using the default filenaming scheme) I got the
same error but it was fatal.

Let me know if you want a trace.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Hmmm ... where is that coming from exactly? PG itself should not be
>> referring to /usr/share/timezone --- we have our own TZ database now.

> Let me know if you want a trace.

Please. Also, what nondefault configuration or postgresql.conf settings
are you using?

regards, tom lane

Tom,

> Please. Also, what nondefault configuration or postgresql.conf settings
> are you using?

Will have to get the trace tommorrow. Config options are --with-perl and
--with-odbc

--
Josh Berkus
Aglio Database Solutions
San Francisco

Tom,

> Please. Also, what nondefault configuration or postgresql.conf settings
> are you using?

Sorry for delay. Attached.

And --with-perl --with-odbc

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

>> Please. Also, what nondefault configuration or postgresql.conf settings
>> are you using?

> Sorry for delay. Attached.

> And --with-perl --with-odbc

As best I can tell, this is coming out because pgtz.c thinks that
/usr/share/timezone is where Postgres' own timezone files are; which
implies that get_share_directory() is returning /usr/share; which does
not make a lot of sense. I think you must have used nondefault
configuration settings for the installation location (--prefix and
friends) ... so the above answer is not very helpful.

regards, tom lane

Tom,

> As best I can tell, this is coming out because pgtz.c thinks that
> /usr/share/timezone is where Postgres' own timezone files are; which
> implies that get_share_directory() is returning /usr/share; which does
> not make a lot of sense. I think you must have used nondefault
> configuration settings for the installation location (--prefix and
> friends) ... so the above answer is not very helpful.

Nope:
$ ./configure --with-perl --with-odbc

One nonstandard thing about the target locations was that /usr/local/pgsql is
a mount and not an ordinary directory. Dunno how that could make PG lose
the libdir, though.

Will send you the full config.log when I'm back in my office and can retrieve
it.

--Josh

--
__Aglio Database Solutions_______________
Josh Berkus Consultant
josh(at)agliodbs(dot)com www.agliodbs.com
Ph: 415-752-2500 Fax: 415-752-2387
2166 Hayes Suite 200 San Francisco, CA

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> As best I can tell, this is coming out because pgtz.c thinks that
>> /usr/share/timezone is where Postgres' own timezone files are; which
>> implies that get_share_directory() is returning /usr/share; which does
>> not make a lot of sense.

> Nope:
> $ ./configure --with-perl --with-odbc

> One nonstandard thing about the target locations was that /usr/local/pgsql is
> a mount and not an ordinary directory. Dunno how that could make PG lose
> the libdir, though.

What it looks like is that the postmaster was executed out of /usr/bin.
Have you got any symlinks you aren't telling us about? Where does that
mount point to, anyway?

regards, tom lane

Hi guys,

i'm testing a v8.0beta4 in windows.

Welcome to psql 8.0.0beta4, the PostgreSQL interactive
terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

Warning: Console codepage (850) differs from windows
codepage (1252)
8-bit characters will not work correctly. See
PostgreSQL
documentation "Installation on Windows" for
details.

template1=# \c uescc
Ahora estß conectado a la base de datos "uescc".

***

This is a script i'm trying to run to replace an
existing function

**
-- begin script func.sql

BEGIN WORK;

DROP FUNCTION recaudaciones.rec_f_aperturarcaja(int2,
int2);

CREATE OR REPLACE FUNCTION
recaudaciones.rec_f_aperturarcaja(int2, int2)
RETURNS void AS '
declare
rs rec_t_actividadcaja%ROWTYPE;
rs_ctran rec_t_transaccion%ROWTYPE;
rs_dtran rec_t_detalletransaccion%ROWTYPE;
rs_ttran rec_m_tipotransaccion%ROWTYPE;
valor_ef DECIMAL(9,2);
valor_ch DECIMAL(9,2);
begin
SELECT INTO rs * FROM rec_t_actividadcaja
WHERE ent_codigo = $1
AND caj_codigo = $2
AND acj_fechaapertura = current_date;

IF rs.ent_codigo IS NOT NULL THEN
RETURN;
END IF;

SELECT INTO rs * FROM rec_t_actividadcaja
WHERE ent_codigo = $1
AND caj_codigo = $2
ORDER BY acj_fechaapertura DESC LIMIT 1;

IF rs.ent_codigo IS NULL THEN
valor_ef := 0;
valor_ch := 0;
ELSE
valor_ef := rs.acj_valorefapertura;
valor_ch := rs.acj_valorchapertura;

FOR rs_ctran IN SELECT * FROM rec_t_transaccion
WHERE ent_codigo =
rs.ent_codigo
AND caj_codigo =
rs.caj_codigo
AND DATE(tra_fechaingreso) >=
rs.acj_fechaapertura
LOOP

SELECT INTO rs_ttran * FROM rec_m_tipotransaccion
WHERE ent_codigo = rs_ctran.ent_codigo
AND tra_codigo = rs_ctran.tra_codigo;

FOR rs_dtran IN SELECT * FROM
rec_t_detalletransaccion
WHERE ent_codigo = rs_ctran.ent_codigo
AND tra_anio = rs_ctran.tra_anio
AND tra_codigo = rs_ctran.tra_codigo
AND tra_numero = rs_ctran.tra_numero
AND fpg_codigo IN (''EF'', ''CH'')
LOOP
CASE rs_dtran.fpg_codigo
WHEN ''EF'' THEN
IF rs_ttran.tra_tipo = ''+'' THEN
valor_ef := valor_ef + rs_dtran.dtr_valor;
ELSE
valor_ef := valor_ef - rs_dtran.dtr_valor;
END IF;
WHEN ''CH'' THEN
IF rs_ttran.tra_tipo = ''+'' THEN
valor_ch := valor_ch + rs_dtran.dtr_valor;
ELSE
valor_ch := valor_ch - rs_dtran.dtr_valor;
END IF;
END;
END LOOP;
END LOOP;
END IF;

INSERT INTO rec_t_actividadcaja
VALUES ($1, $2, current_date, current_time, valor_ef,
valor_ch);

RETURN;

end; '
LANGUAGE 'plpgsql' VOLATILE;

COMMIT WORK;

-- end script func.sql

***

These are the answers from psql

***

uescc=# \i c:/func.sql
BEGIN
DROP FUNCTION
psql:c:/func.sql:77: ERROR: syntax error at or near
"ELSE" en el carßcter 1720
psql:c:/func.sql:77: LINE 53: ELSE
psql:c:/func.sql:77: ^
ROLLBACK
uescc=#

***

but in that line there is a valid IF .. THEN .. ELSE
block.

is it a bug? or i'm totally wrong?

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

Jaime Casanova <systemguards(at)yahoo(dot)com> writes:
> CASE rs_dtran.fpg_codigo
> WHEN ''EF'' THEN
> IF rs_ttran.tra_tipo = ''+'' THEN
> valor_ef := valor_ef + rs_dtran.dtr_valor;
> ELSE
> valor_ef := valor_ef - rs_dtran.dtr_valor;
> END IF;
> WHEN ''CH'' THEN
> IF rs_ttran.tra_tipo = ''+'' THEN
> valor_ch := valor_ch + rs_dtran.dtr_valor;
> ELSE
> valor_ch := valor_ch - rs_dtran.dtr_valor;
> END IF;
> END;

> but in that line there is a valid IF .. THEN .. ELSE
> block.

if/then/else is a statement, not a component of an expression.
CASE is an expression construct, not a statement. I think
you need to rewrite the CASE as an if/then/elsif statement.

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribió:
> Jaime Casanova <systemguards(at)yahoo(dot)com> writes:
> > CASE rs_dtran.fpg_codigo
> > WHEN ''EF'' THEN
> > IF rs_ttran.tra_tipo = ''+'' THEN
> > valor_ef := valor_ef + rs_dtran.dtr_valor;
> > ELSE
> > valor_ef := valor_ef - rs_dtran.dtr_valor;
> > END IF;
> > WHEN ''CH'' THEN
> > IF rs_ttran.tra_tipo = ''+'' THEN
> > valor_ch := valor_ch + rs_dtran.dtr_valor;
> > ELSE
> > valor_ch := valor_ch - rs_dtran.dtr_valor;
> > END IF;
> > END;
>
> > but in that line there is a valid IF .. THEN ..
> ELSE
> > block.
>
> if/then/else is a statement, not a component of an
> expression.
> CASE is an expression construct, not a statement. I
> think
> you need to rewrite the CASE as an if/then/elsif
> statement.
>
> regards, tom lane
>

I will try but this work in v7.4.2 that's why i think
is an error.

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

Jaime Casanova <systemguards(at)yahoo(dot)com> writes:
> --- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribi:
>> if/then/else is a statement, not a component of an
>> expression.
>> CASE is an expression construct, not a statement. I
>> think
>> you need to rewrite the CASE as an if/then/elsif
>> statement.

> I will try but this work in v7.4.2 that's why i think
> is an error.

It most certainly did not work in 7.4.2, or any other PG release.
plpgsql doesn't have a CASE statement.

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribió:
> Jaime Casanova <systemguards(at)yahoo(dot)com> writes:
> > --- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribió:
> >> if/then/else is a statement, not a component of
> an
> >> expression.
> >> CASE is an expression construct, not a statement.
> I
> >> think
> >> you need to rewrite the CASE as an if/then/elsif
> >> statement.
>
> > I will try but this work in v7.4.2 that's why i
> think
> > is an error.
>
> It most certainly did not work in 7.4.2, or any
> other PG release.
> plpgsql doesn't have a CASE statement.
>
> regards, tom lane
>

This is a production server... and the same script...
(It was an error in v7.4.2 to permit this?)

[postgres(at)nautilus bin]$ ./psql uescc
Welcome to psql 7.4.2, the PostgreSQL interactive
terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

uescc=# \i func.sql
BEGIN
DROP FUNCTION
CREATE FUNCTION
ROLLBACK
uescc=#

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

Jaime Casanova <systemguards(at)yahoo(dot)com> writes:
> --- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribi:
>> It most certainly did not work in 7.4.2, or any
>> other PG release.
>> plpgsql doesn't have a CASE statement.

> This is a production server... and the same script...
> (It was an error in v7.4.2 to permit this?)

Are you sure the function has ever been executed? 7.4 gives me

regression=# select recaudaciones.rec_f_aperturarcaja(1::int2,2::int2);
ERROR: syntax error at or near "ELSE"
CONTEXT: compile of PL/pgSQL function "rec_f_aperturarcaja" near line 51

8.0 is just reporting the error when the function is defined instead
of at first call.

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribió:
> Jaime Casanova <systemguards(at)yahoo(dot)com> writes:
> > --- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribió:
> >> It most certainly did not work in 7.4.2, or any
> >> other PG release.
> >> plpgsql doesn't have a CASE statement.
>
> > This is a production server... and the same
> script...
> > (It was an error in v7.4.2 to permit this?)
>
> Are you sure the function has ever been executed?
> 7.4 gives me
>
> regression=# select
> recaudaciones.rec_f_aperturarcaja(1::int2,2::int2);
> ERROR: syntax error at or near "ELSE"
> CONTEXT: compile of PL/pgSQL function
> "rec_f_aperturarcaja" near line 51
>
> 8.0 is just reporting the error when the function is
> defined instead
> of at first call.
>

Ok, you are right. I was sure i have ran this but
maybe i ran one older.

sorry for the trouble.

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

Tom,

> What it looks like is that the postmaster was executed out of /usr/bin.
> Have you got any symlinks you aren't telling us about?  Where does that
> mount point to, anyway?

Yes, there are symlinks in /usr/bin to /usr/local/pgsql/bin. I've never had
it cause issues before, though. Hmmmm ... did I maybe do hardlinks instead
of symlinks? Better check.

The mount points to a SAN filesystem.

--Josh

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco