[BUGS] Bug in pgAdminIII or in pg 8 beta3 ?
Hi I think that is can be a bug... When I create a new database in pg8 beta3 (win32) like this CREATE DATABASE test WITH OWNER = admin ENCODING = 'UNICODE'; System objects became visible in public schema : tables like pg_ts_cfg , pg_ts_cfgmap, pg_ts_dict ... functions , operators etc. but if I create a database from template0 this objects are not visibles My installation is with problem or this is a bug ? Thanks in advance ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug in pgAdminIII or in pg 8 beta3 ?
On Thu, 2004-11-04 at 13:06, Luiz K. Matsumura wrote: > Hi > > I think that is can be a bug... When I create a new database in pg8 > beta3 (win32) like this > > CREATE DATABASE test > WITH OWNER = admin >ENCODING = 'UNICODE'; > > System objects became visible in public schema : > tables like pg_ts_cfg , pg_ts_cfgmap, pg_ts_dict ... > functions , operators etc. I don't think those are system objects, unless they are specific to the Win32 version. I believe they are created when you use tsearch2 in a database. You must have done that some time in template1. > but if I create a database from template0 this objects are not visibles > > My installation is with problem or this is a bug ? No bug; these objects exist in the template1 database - someone created them there - so they get copied into every new database. This is a _feature_. It lets you ensure that every new database contains certain objects. If you don't want them in new databases, delete them from template1. Oliver Elphick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] could not find /usr/local/timezone
Josh Berkus <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Concatenating strings within a function definition
I'm trying to create a function like so: create function get_lid_prefix (text) returns setof int as 'select lid from ilemma where lemma ~ \'^\' + upper($1)' language 'sql'; I've tried several variations, including 'select lid from ilemma where lemma ~ ' || '^' || ' upper ($1)' 'select lid from ilemma where lemma ~ (cast ''^'' as text) || upper ($1)' but nothing works. Can I do this at all? The idea is that if $1 is "foo", the query should be select lid from ilemma where lemma ~ '^FOO' I'm running PostgreSQL 7.4 on sparc-sun-solaris2.8, compiled by GCC 3.0.4. Thanks, Nishad -- "Underneath the concrete, the dream is still alive" -- Talking Heads ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Concatenating strings within a function definition
Nishad Prakash <[EMAIL PROTECTED]> writes: > I'm trying to create a function like so: > create function get_lid_prefix (text) returns setof int as > 'select lid from ilemma where lemma ~ \'^\' + upper($1)' > language 'sql'; 'select lid from ilemma where lemma ~ (\'^\' || upper($1))' regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] a bug in plpgsql
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_ctranrec_t_transaccion%ROWTYPE;
rs_dtranrec_t_detalletransaccion%ROWTYPE;
rs_ttranrec_m_tipotransaccion%ROWTYPE;
valor_efDECIMAL(9,2);
valor_chDECIMAL(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
---(end of broadcast)---
TIP 3: if post
Re: [BUGS] a bug in plpgsql
Jaime Casanova <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] a bug in plpgsql
--- Tom Lane <[EMAIL PROTECTED]> escribió: > Jaime Casanova <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] a bug in plpgsql
Jaime Casanova <[EMAIL PROTECTED]> writes: > --- Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] a bug in plpgsql
--- Tom Lane <[EMAIL PROTECTED]> escribió: > Jaime Casanova <[EMAIL PROTECTED]> writes: > > --- Tom Lane <[EMAIL PROTECTED]> 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?) [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] a bug in plpgsql
Jaime Casanova <[EMAIL PROTECTED]> writes: > --- Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] a bug in plpgsql
--- Tom Lane <[EMAIL PROTECTED]> escribió: > Jaime Casanova <[EMAIL PROTECTED]> writes: > > --- Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug in pgAdminIII or in pg 8 beta3 ?
On Thu, 2004-11-04 at 13:49, Oliver Elphick wrote: > On Thu, 2004-11-04 at 13:06, Luiz K. Matsumura wrote: > > Hi > > > > I think that is can be a bug... When I create a new database in pg8 > > beta3 (win32) like this > > > > CREATE DATABASE test > > WITH OWNER = admin > >ENCODING = 'UNICODE'; > > > > System objects became visible in public schema : > > tables like pg_ts_cfg , pg_ts_cfgmap, pg_ts_dict ... > > functions , operators etc. > > I don't think those are system objects, unless they are specific to the > Win32 version. I believe they are created when you use tsearch2 in a > database. You must have done that some time in template1. > > > but if I create a database from template0 this objects are not visibles > > > > My installation is with problem or this is a bug ? > > No bug; these objects exist in the template1 database - someone created > them there - so they get copied into every new database. This is a > _feature_. It lets you ensure that every new database contains certain > objects. > I believe tsearch2 is part of the install in Magnus' .mxi Windows Installer version. As Olly says, not a bug. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Bug in pgAdminIII or in pg 8 beta3 ?
>> No bug; these objects exist in the template1 database - >someone created >> them there - so they get copied into every new database. This is a >> _feature_. It lets you ensure that every new database >contains certain >> objects. >> > >I believe tsearch2 is part of the install in Magnus' .mxi Windows >Installer version. It is indeed installed. The activation of the module in 'template1' is optional and *not* enabled by default, though. But there is a checkbox for it... //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] could not find /usr/local/timezone
"Josh Berkus" <[EMAIL PROTECTED]> writes: >> What it looks like is that the postmaster was executed out of >> /usr/bin. > dropping symlinks to the pg binaries in /usr/bin or > /usr/local/bin or /sbin/ is something I've done for ages, on PostgreSQL > versions 7.1 -> 7.4. Is there a problem with this now? This is fundamentally broken by the changes to support relocatable installs: PG now attempts to find the support files by relative paths from the place where the executable was found. It might be worth making find_my_exec able to detect that it found a symlink, but I'm not sure how much code would have to be added to resolve the symlink, nor how portable it would be. It would fail anyway if someone did this via hard linking rather than symlink. You could work around it by configuring the installation correctly, ie, make --exec-prefix not be a sibling of the other install directories. That would defeat the relocatable-install logic and make it fall back to hardwired paths. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] could not find /usr/local/timezone
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. H ... 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] could not find /usr/local/timezone
Tom, > This is fundamentally broken by the changes to support relocatable > installs: PG now attempts to find the support files by relative paths > from the place where the executable was found. OK, I can see the tradeoff.Hmmm ... this means that we need something in the Release Notes, I would think: -- Symlinking the PostgreSQL binaries may cause problems with PostgreSQL locating libraries and files, due to changes to make moving Postgres directories easier. It is recommended that you use --exec-prefix when compiling if you think you will need symlinks. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] could not find /usr/local/timezone
Tom Lane wrote: > This is fundamentally broken by the changes to support relocatable > installs: PG now attempts to find the support files by relative paths > from the place where the executable was found. I recall that on some systems the "normal" method of installation is installing everything in private directory trees and then symlinking the relevant parts to shared locations. Also, sites using AFS file systems do things of that kind. I hope these systems aren't going to be broken completely. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] could not find /usr/local/timezone
Is this an open 8.0 item? --- Peter Eisentraut wrote: > Tom Lane wrote: > > This is fundamentally broken by the changes to support relocatable > > installs: PG now attempts to find the support files by relative paths > > from the place where the executable was found. > > I recall that on some systems the "normal" method of installation is > installing everything in private directory trees and then symlinking > the relevant parts to shared locations. Also, sites using AFS file > systems do things of that kind. I hope these systems aren't going to > be broken completely. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] could not find /usr/local/timezone
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> This is fundamentally broken by the changes to support relocatable >> installs: PG now attempts to find the support files by relative paths >> from the place where the executable was found. > I recall that on some systems the "normal" method of installation is > installing everything in private directory trees and then symlinking > the relevant parts to shared locations. Also, sites using AFS file > systems do things of that kind. I hope these systems aren't going to > be broken completely. [ itch... ] Maybe we had better do something about chasing symlinks, then. ISTM that find_my_exec could resolve a symbolic link down to the actual executable, and then we could assume that the support files are located relative to that. I was worried about portability but it looks like lstat() and readlink() are defined in the Single Unix Spec ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
