Modified Files:
--------------
libpqtypes/src:
handler.c (r1.4 -> r1.5)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/handler.c.diff?r1=1.4&r2=1.5)
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
Modified Files:
--------------
libpqtypes/src:
events.c (r1.1 -> r1.2)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/events.c.diff?r1=1.1&r2=1.2)
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
http://archives.postgresql.org/pgsql-hackers/2008-09/msg00153.php
for details. The only change I didn't make yet is the event 'name'. I
have put it in and taken it out twice now, so a firm 'put it in there'
would be appreciated.
Go here for libpqtypes using events. pgfoundry is still using the older
object hooks version.
http://libpqtypes.esilo.com/libpqtypes-events.tar.gz
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
Hi folks,
I’m wondering why the postgres planner is not capable of determining the correct partition for a simple select for the following partitioning scheme, in which I’d like to automatically divide rows into four sub-tables, ie, a simple form of hash partitioning.
Any ideas why this doesn’t work, or a work around to make it work? I would have expected the query plan below to only query the test_1 table.
Regards
David
CREATE TABLE test (
id int not null primary key
);
CREATE TABLE test_0 ( CHECK ( id % 4 = 0) ) INHERITS (test);
CREATE TABLE test_1 ( CHECK ( id % 4 = 1) ) INHERITS (test);
CREATE TABLE test_2 ( CHECK ( id % 4 = 2) ) INHERITS (test);
CREATE TABLE test_3 ( CHECK ( id % 4 = 3) ) INHERITS (test);
CREATE RULE test_0 AS ON INSERT TO test WHERE ( id % 4 = 0 ) DO INSTEAD INSERT INTO test_0 VALUES ( NEW.id );
CREATE RULE test_1 AS ON INSERT TO test WHERE ( id % 4 = 1 ) DO INSTEAD INSERT INTO test_1 VALUES ( NEW.id );
CREATE RULE test_2 AS ON INSERT TO test WHERE ( id % 4 = 2 ) DO INSTEAD INSERT INTO test_2 VALUES ( NEW.id );
CREATE RULE test_3 AS ON INSERT TO test WHERE ( id % 4 = 3 ) DO INSTEAD INSERT INTO test_3 VALUES ( NEW.id );
insert into test values(1);
explain analyse select * from test;
"Result (cost=0.00..170.00 rows=12000 width=4) (actual time=0.027..0.042 rows=1 loops=1)"
" -> Append (cost=0.00..170.00 rows=12000 width=4) (actual time=0.020..0.032 rows=1 loops=1)"
" -> Seq Scan on test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.002..0.002 rows=0 loops=1)"
" -> Seq Scan on test_0 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1)"
" -> Seq Scan on test_1 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.007..0.009 rows=1 loops=1)"
" -> Seq Scan on test_2 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1)"
" -> Seq Scan on test_3 test (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1)"
"Total runtime: 0.115 ms"
What would be easiest from this end is a SQL script to create the
tables, insert any test data needed, and then reproduce the problem,
starting from an empty database. A "pg_dump -s" script is usually
a good starting place for making that.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Is there any way to create a case sensitive full text index? My target
is to make case sensitive full text searches but I don't know how.
I could create a configurario for full text searching:
CREATE TEXT SEARCH CONFIGURATION public.myconfiguration (PARSER =
pg_catalog.default);
CREATE TEXT SEARCH DICTIONARY public.my_dict (
TEMPLATE = pg_catalog.simple,
STOPWORDS = my_stops
);
ALTER TEXT SEARCH CONFIGURATION myconfiguration
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
word, hword, hword_part
WITH my_dict;
And then create the full text index:
CREATE INDEX textindexsensible ON documento
USING gin(to_tsvector('myconfiguration',texto));
But default behaviour of Simple Dictionary returns the lower-cased form
of the word if it isn't in stopwords list. Is there any way to change
this behaviour?
Thanks in advance,
Mario Barcala
P.S. I am using PostgreSQL 8.3
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Inscribete en las listas de APESOL
http://www.apesol.org/listas.php
Visita
http://www.eqsoft.net
Manuales, noticias, foros, etc.
--
TIP 5: ¿Has leído nuestro extenso FAQ?
http://www.postgresql.org/docs/faqs.FAQ.html
Beth, It turns out that LAPUGs normally scheduled Meeting falls on
Halloween. We would like to reschedule this meeting for some time in
November. Would this be a problem?
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
--
Sent via lapug mailing list (lapug@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/lapug
I was kind of afraid of that. So, how could one implement such a function
set?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
> > We have had this discussion before, I even submitted a patch to
> > make them case insensitive. In retrospect I was wrong to submit
> > that patch. SQL may be case insensitive but units are not. MB !=
> > Mb != mb ,
>
> For most people they are equal, and all mean MEGABYTE(S) though
> http://en.wikipedia.org/wiki/MB has lots of other possible meanings
> for each.
O.k. there is an interesting point being made here, "For most people".
Which people exactly? Are we talking about the general populous? If so
I would argue that "most people" don't have a clue what MB, Mb, or mb
is except to say I think that means some kind of speed or capacity.
The above is not our target.
If our definition of most people is, "those who are reasonably
technically adept and will be deploying PostgreSQL in production on
some level".
If someone doesn't know the difference between Mb and MB on a
production system, I would not want them anywhere near any instance of
a production system.
If we are going to make sweeping statements (anyone on this thread)
about user-hostile and most people, then we better define what those
mean. This whole argument about making something easier (and incorrect)
for someone who doesn't exist and has not been defined.
I would be hung on this list if I made a similar argument about any
other feature.
>
> > I don't think we should encourage in any way for users to do the
> > wrong thing.
>
> Can you see any scenario where accepting case insensitive units does
> more damage than just ignoring the conf line with "incorrect" casing ?
Generally speaking, no I can't think of any damage that could be done
from mixed casing. Especially since we would only accept certain
possibilities, e.g; Mb would equal MB.
It just strikes me as really bad that a project that prides itself on
"doing it right" is willing to make this type of sacrifice. This isn't
about usability. This is about doing it wrong and actively encouraging
our users that "wrong is ok". It could also misinform the user about
what the meaning of the value means.
>
> Or do you mean we should discourage people from editing
> postgresql.conf manually and have them use some tool which prevents
> them entering "kb" ?
Well that is a whole other argument :P. I would be happy to have that
one on another thread.
Sincerely,
Joshua D. Drake
--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Sinceramente me acostumbre a leer en ingles pero al mismo tiempo me da
bronca que tengamos que recurrir a documentación en otro lenguaje ( en especial
para los cursos y para utilizar para otras personas)
Emanuel Calvo Franco
/* 3manuek */
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda
Ah, thanks. It's my mistake. Both of SQL spec and my implementation in
pg_proc say it is row_number, not rownumber.
Regards,
--
Hitoshi Harada
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
> CFLAGS="-Os -arch x86_64" LDFLAGS="-arch x86_64" ./configure
> --disable-depend --with-pam --with-openssl
> --prefix=/Users/rleitgeb/pq_x86_64
I don't think it's really as "easy" as all that with the 8.3 source tree.
This might possibly work with CVS HEAD due to recent changes in our
build process, but I'll bet that you simply aren't getting fully 64-bit
executables this way in 8.3.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Modified Files:
--------------
libpqtypes/src:
utils.c (r1.3 -> r1.4)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/utils.c.diff?r1=1.3&r2=1.4)
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
You're going to need to show a complete example if you want any help
with this. (My bet is that you've overlooked a trigger someplace...)
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Euler Taveira de Oliveira
http://www.timbira.com/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
There is *no* exit path from the PG server that does not spit out an
error message someplace. Re-examine the logging setup. I don't know
how FreeBSD's package sets it up exactly, but there have been packages
in the past that just sent the postmaster's stderr to /dev/null :-(.
See here for some documentation about the settings that determine where
messages go:
http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE
regards, tom lane
--
Sent via pgsql-ru-general mailing list (pgsql-ru-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-ru-general
The whole point of this discussion is, that mostly people expect
MB == Mb = mb == mB, especially if they see weird constructs like kB
used (k for Kilo, or actually Kibi).
> nor is there anything user-hostile behind the idea of doing it the right way.
I was not trying to expose some sinister plan, just pointing out that
users seldom expect that kind of surprise.
--------------
Hannu
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
For most people they are equal, and all mean MEGABYTE(S) though
http://en.wikipedia.org/wiki/MB has lots of other possible meanings for
each.
> I don't think we should encourage in any way for users to do the wrong thing.
Can you see any scenario where accepting case insensitive units does
more damage than just ignoring the conf line with "incorrect" casing ?
Or do you mean we should discourage people from editing postgresql.conf
manually and have them use some tool which prevents them entering "kb" ?
----------------
Hannu
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I noticed the folowing typo in the doc sgml:
'rownumber()', instead of 'row_number()' ( 2x )
hth
Erik Rijkers
*** doc/src/sgml/func.sgml.orig 2008-09-03 17:20:28.130229027 +0200
--- doc/src/sgml/func.sgml 2008-09-03 17:21:01.331907454 +0200
***************
*** 10092,10100 ****
<row>
<entry>
<indexterm>
! <primary>rownumber()</primary>
</indexterm>
! <function>rownumber() OVER (ORDER BY <replaceable
class="parameter">expression</replaceable>)</function>
</entry>
<entry>
<type>bigint</type>
--- 10092,10100 ----
<row>
<entry>
<indexterm>
! <primary>row_number()</primary>
</indexterm>
! <function>row_number() OVER (ORDER BY <replaceable
class="parameter">expression</replaceable>)</function>
</entry>
<entry>
<type>bigint</type>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I think it'd make more sense to do it incrementally rather than in one
big-bang patch ...
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
# cat /etc/yum.repos.d/pgdg-82-redhat.repo
[pgdg82]
name=PostgreSQL 8.2 $releasever - $basearch
baseurl=http://yum.pgsqlrpms.org/8.2/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
[pgdg82-source]
name=PostgreSQL 8.2 $releasever - $basearch - Source
failovermethod=priority
baseurl=http://yum.pgsqlrpms.org/srpms/8.2/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
the clean version:
--------------------------------------------------------------
#include "utils/builtins.h"
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
Datum datumIbrido = PG_GETARG_DATUM(0);
Datum labelDatum;
char *label;
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = DatumGetCString(labelDatum);
if(strcmp(label,("label_constant"))==0){
...
}
...
}
--------------------------------------------------------------
thank you again!
Edoardo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
There is no arguing that MB != Mb; nor is there anything user-hostile
behind the idea of doing it the right way.
Sincerely,
Joshua D. Drake
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Well, not basing such a sweeping statement on a single query example
would be a good start ;-). This particular plan might have got a little
worse but I'm sure some further investigation would show other cases
that got better.
If you want to nudge it back towards the indexscan plan, reducing
random_page_cost a bit would probably do the trick. But that's
definitely not a parameter you want to adjust on the basis of only
one test case.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I don't know what ANSI says, nor do I know what PostgreSQL exactly does
at the moment, but, the only thing you can reasonably count on is that
the WHERE clause is evaluated before the SELECT-result-rows and the
ORDER BY clause (in any SQL database).
You cannot depend on any left to right order, and you cannot depend on
ORDER BY being evaluated after the SELECT-result-rows.
--
Sincerely,
Stephen R. van den Berg.
"<Clarions sounding> *No one* expects the Spanish inquisition!"
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
> select *, score(1) from mytable where contains(mytable.title, 'Winding
> Road', 1) order by score(1);
> The "contains" function does a match against mytable.title for the term
> 'Winding Road' and both returns and saves an integer score which may be
> retrieved later using the "score(...)" function.
This is just a bad, bad idea. Side-effects in a WHERE-clause function
are guaranteed to cause headaches. When (not if) it breaks, you get
to keep both pieces.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Just FYI, preferred style for the second line would be
label = DatumGetCString(labelDatum);
Nearly all standard data types have DatumGetFoo and FooGetDatum
macros to hide the conversion details (even if it's only a cast).
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> Are you really afraid that someone would want to use mb to mean
> millibits ?
>
> As SQL is generally case insensitive, it is quite surprising to most
> people that GUC units are not.
We have had this discussion before, I even submitted a patch to make
them case insensitive. In retrospect I was wrong to submit that patch.
SQL may be case insensitive but units are not. MB != Mb != mb , I don't
think we should encourage in any way for users to do the wrong thing.
Sincerely,
Joshua D. Drake
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
"Oleg Serov" <serovov@gmail.com> writes:I couldn't reproduce that here, at least not with versions newer than
> But if there are some records in t_table and we romove WHERE 1=0, we will
> have
> ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function
> "t_func" line 9 at RETURN NEXT
8.0. Maybe you were testing a case that also involved dropped columns?
regards, tom lane
Bug reference: 4397
Logged by: Rudolf Leitgeb
Email address: r.leitgeb@x-pin.com
PostgreSQL version: 8.3.3
Operating system: OSX Leopard
Description: crash in tab-complete.c
Details:
When I compiled postgresql for different targets (i386, x86_64, ppc, ppc64)
on my Mac, tab-complete.c gave me a number of warnings, that integers are
converted to pointers. Sure enouugh, psql bombs as soon as I hit the TAB key
...
How to reproduce:
- Compile psql for x86_64. This can be easily done with the following
commands:
CFLAGS="-Os -arch x86_64" LDFLAGS="-arch x86_64" ./configure
--disable-depend --with-pam --with-openssl
--prefix=/Users/rleitgeb/pq_x86_64
make
make install
- Then connect to a database with psql and hit tab. gdb will output the
following:
postgres=#
Program received signal EXC_BAD_ACCESS, Could not access memory.
Reason: KERN_INVALID_ADDRESS at address: 0x0000000000809000
0x00000001001fa1dc in fn_complete ()
(gdb) bt
#0 0x00000001001fa1dc in fn_complete ()
#1 0x00000001001fbb26 in rl_complete ()
#2 0x00000001001fbb38 in rl_complete ()
#3 0x00000001001ef07b in el_gets ()
#4 0x00000001001fc0be in readline ()
#5 0x0000000100006aaa in gets_interactive ()
#6 0x000000010000725e in MainLoop ()
#7 0x000000010000a070 in main ()
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Modified Files:
--------------
libpqtypes:
Makefile.am (r1.6 -> r1.7)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/Makefile.am.diff?r1=1.6&r2=1.7)
Makefile.win32 (r1.1 -> r1.2)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/Makefile.win32.diff?r1=1.1&r2=1.2)
win32.mak (r1.1 -> r1.2)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/win32.mak.diff?r1=1.1&r2=1.2)
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
Daniel escribió:
Usa cron.> Buenas a todos, resulta que quiero hacer que un query se ejecute cada dia
> primero del mes, para realizar respaldo de los datos a otra bd en este caso
> de historicos, me gustaria si postgres lo trae o hay que usar a el cron de
> linux (Uso Ubuntu)
--
Alvaro Herrera http://www.amazon.com/gp/registry/3BP7BYG9PUGI8
"XML!" Exclaimed C++. "What are you doing here? You're not a programming
language."
"Tell that to the people who use me," said XML.
Can someone confirm that an event 'name' should be re-introduced, as
suggested by Alvaro?
Can I get a happy or sad face in regards to below?
New options which add instead of remove.
#define PG_COPYRES_ATTRS 0x01
#define PG_COPYRES_TUPLES 0x02 /* Implies PG_COPYRES_ATTRS */
#define PG_COPYRES_EVENTS 0x04
#define PG_COPYRES_NOTICEHOOKS 0x08
// tuples implies attrs, you need the attrs to copy the tuples.
if(options & PG_COPYRES_TUPLES)
options |= PG_COPYRES_ATTRS; // auto set option
In regards to copying the attrs, the PQcopyResult still needs the
ability to copy the source result's attrs. Although, it doesn't need
the ability to provide custom attrs (which I removed). New prototype
for copyresult:
PGresult *
PQcopyResult(const PGresult *src, int options);
I then added a PQsetResultAttrs. copyresultattrs didn't seem like the
correct name because you are no longer copying attrs from a source
result. You are providing the attrs to 'set'.
int
PQsetResultAttrs(PGresult *res, int numAttributes,
PGresAttDesc *attDescs);
If the result provided to setattrs already contains attributes, I have
the function failing (can't overwrite existing attrs). I think this is
good behavior....
When PQcopyResult needs to copy the source result's attrs, it calls
PQsetResultAttrs.
/* Wants attrs */
if((options & PG_COPYRES_ATTRS) &&
!PQsetResultAttrs(dest, src->numAttributes, src->attDescs))
{
PQclear(dest);
return NULL;
}
So, there is some nice code reuse which indicates to me the code is
segmented well (copyres & setattrs).
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
J'utilise le language de procédure PL/sh pour une fonction (il m'est
impératif de l'utiliser, je n'ai pas le choix...). Je souhaiterais
savoir, si quelqu'un connais bien ce langage, est-ce que je peux
retourner une erreur de la même manière qu'un RAISE EXCEPTION dans
plpgsql ?
Enfait, la fonction doit retourner un integer seulement, en cas
d'erreur, je fait un :
echo "Erreur: Description de l'erreur"
Seulement, quand j'éxécute la fonction en provoquant une erreur (ou
quand l'erreur est détectée), PostgreSQL me donne cette erreur :
ERREUR: syntaxe en entrée invalide pour l'entier : « Erreur:
Description de l'erreur »
C'est vrai que j'ai quand même l'erreur mais si ça pouvais être plus
explicite comme erreur, ça serais bien...
Merci d'avance à tout le monde !
Cordialement, Samuel ROZE.
--
Sent via pgsql-fr-generale mailing list (pgsql-fr-generale@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-fr-generale
Merci beaucoup, ça marche sans problèmes avec la deuxième méthode qui
est :
SELECT variable::timestamptz
(et non "::timestamp with zone" :) )
Merci !
Le mardi 02 septembre 2008 à 21:19 +0200, Guillaume Lelarge a écrit :
> Samuel ROZE a écrit :
> > Bonjour à tous (encore une fois :) ),
> >
> > Je suis actuellement confronté à un problème que je trouve assez bizare,
> > lors de la substitution de variables dans une requête SELECT :
> >
> > ----------
> > [...]
> > SELECT (timestamp with time zone "v_new_time" - timestamp with time zone
> > "v_temps") INTO v_interval;
> > [...]
> > ----------
> >
> > Cette simple commande SELECT ... INTO donne cette erreur :
> >
> > ----------
> > 7-statistiques-triggers.sql:116: ERREUR: erreur de syntaxe sur ou près
> > de « $1 » at character 37
> > QUERY: SELECT (timestamp with time zone $1 - timestamp with time zone
> > $2 )
> > CONTEXT: SQL statement in PL/PgSQL function "parse_donnees_brutes" near
> > line 66
> > ----------
> >
> > Je ne comprend pas... J'ai essayer avec des guillements simple, ça
> > marche mais après lors de l'exécution il y a un problème logique qui me
> > dit par exemple que "v_new_time n'est pas du type timestamptz"...
> >
>
> Logique car un guillemet simple indique une chaîne de caractères, et la
> chaîne de caractères 'v_new_time' ne ressemble en rien à une valeur de
> type timestamp.
>
> > Pouvez-vous m'aider ou me donner des pistes ?
> >
>
> Ton problème vient de la façon de faire ton cast. À mon avis, c'est pas
> standard du tout. Bref, tu as deux possibilités :
>
> * soit la syntaxe "::"
>
> select d1::"timestamp with time zone" - d2::timestamp with time zone
> into v;
>
> * soit utiliser cast
>
> Quelque soit ton choix, pense à placer le INTO après le SELECT,
> autrement dit :
>
> select into v d1::timestamp with time zone
> - d2::timestamp with time zone ;
>
>
> --
> Guillaume.
> http://www.postgresqlfr.org
> http://dalibo.com
>
--
Sent via pgsql-fr-generale mailing list (pgsql-fr-generale@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-fr-generale
Bug reference: 4396
Logged by: Stephen Cuppett
Email address: stephen.cuppett@sas.com
PostgreSQL version: 8.3.3
Operating system: RHEL5.2 x86_64
Description: Trigger event fired "UPDATE" when "DELETE" happening via
foreign key
Details:
I have the following chain of tables:
release -> feature -> testcase -> execution -> execution_history
All tables have a parent_id column to the previous table with a foreign key
specified as ON DELETE CASCADE. One to Many the whole way down.
When I delete a release, the whole chain gets deleted.
I have this trigger defined:
CREATE OR REPLACE FUNCTION execution_history()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO execution_history (
execution_id, reporter, complete_units,
failed_units, blocked_units,
attempted_units, created, remote_rpt, remote_addr,
reported
) VALUES (
OLD.id, OLD.reporter, OLD.complete_units,
OLD.failed_units, OLD.blocked_units,
OLD.attempted_units, OLD.updated, OLD.remote_rpt, OLD.remote_addr,
OLD.reported
);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
CREATE TRIGGER execution_history
AFTER UPDATE
ON executions
FOR EACH ROW
EXECUTE PROCEDURE execution_history();
Yet, when I go to delete a release, I get the following error:
Query: DELETE FROM "releases" WHERE "id" IN ('3')
Warning: SQL Error: ERROR: insert or update on table "execution_history"
violates foreign key constraint "execution_history_execution_id_fkey"
DETAIL: Key (execution_id)=(5830) is not present in table "executions".
CONTEXT: SQL statement "INSERT INTO execution_history ( execution_id,
reporter, complete_units, failed_units, blocked_units, attempted_units,
created, remote_rpt, remote_addr, reported ) VALUES ( $1 , $2 , $3 , $4 , $5
, $6 , $7 , $8 , $9 , $10 )" PL/pgSQL function "execution_history" line 3 at
SQL statement
I previously didn't have the TG_OP comparison, so I added the one to ensure
it was an "UPDATE". This doesn't appear to be correct behavior?
On the release and feature tables I have both an UPDATE for every row and
INSERT for every row trigger and they are *not* also firing. So I can't
figure out why this one would?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
this is a code fragment:
--------------------------------------------------------------
#include "utils/builtins.h"
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
Datum datumIbrido = PG_GETARG_DATUM(0);
Datum labelDatum;
char *label;
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = (char *) DatumGetPointer(labelDatum);
if(strcmp(label,("label_constant"))==0){
...
}
...
}
--------------------------------------------------------------
I don't know why but I need <<#include "utils/builtins.h">>
The line "label = (char *) DatumGetPointer(labelDatum);" is essential to
use the information in strcmp() if I use directly labelDatum it does not
works (but it works inside a sprintf(buffer,"%s",labelDatum)).
thank you again
Edoardo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello, I have a postgresql installed with encoding “WIN1252”.
But I need to create a DB with LATIN1 encoding, when I try I got the error: “Encoding Latin1 does not match server’s locale Spanish_Spain.1252. The servers’s LC_CTYPE setting require encoding WIN1252
Is possible to change the server encoding to LATIN1?
Thanks for your help!
I am implementing an autosuggest-style text input for my site, where a
user can start typing the name of a thing (call it a 'Foo'), and get a
list of all things whose name starts with the string the user typed.
For example, if the user types 'car', the database might return the
names 'Car', 'Caramel', 'Carbon', etc. I want the search to be case-
insensitive.
Just to have some code, here's some bare-bones info:
CREATE TABLE foo (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL UNIQUE,
other_info VARCHAR
);
Initially, I just did a naive match using ilike:
SELECT name FROM foo WHERE name ilike 'car%';
This worked alright when the table was small, but now my foo table has
millions of rows, and the query takes far too long. So I created a
new index on name:
CREATE INDEX lower_name_idx ON foo (lower(name));
Now I can use the query:
SELECT name FROM foo WHERE lower(name) like 'car%';
which runs in 2ms, which is exactly what I want.
My problem comes when I execute this query from my application. I'm
using Hibernate to generate the query, the code for which looks like
this:
return session()
.createCriteria( Foo.class )
.add( Restrictions.like( "name", queryString,
MatchMode.START ).ignoreCase() )
.list();
This generates the proper query... almost. It creates a parameterized
query like this:
SELECT id, name, other_info FROM foo WHERE lower(name) like $1;
where "$1" gets substituted with (for example) 'car%'. This query
runs very slowly and doesn't use the index. I think what's happening
is that when the parameterized query gets created, the planner doesn't
know that it's going to be matching at the beginning of the string
(indicated by the trailing percent character), and so it doesn't use
the index. That information comes into play when the parameter gets
substituted, but by then the plan has been set.
I can get it to work if I directly create an HQL query like this:
String hql = "from Foo where lower(name) like '" +
queryString.toLowerCase() + "%'";
I could also generate a SQL query that does the same thing; the fact
that I used HQL for this example is incidental. The point is that the
query isn't parameterized, so it generates the plan I want. This
works fine, but I don't like doing string manipulations like this.
Plus I have to now take care to escape the string to avoid injection
attacks.
Is there any SQL or PostgreSQL string function I could use that would
allow me to have a parameterized query that uses my index?
Alternatively, is there another index definition that would work? Is
there any Hibernate-functionality I've overlooked that would do what I
want without having to do string manipulations? Is there something
simple I've missed?
Also, if string manipulation is the best way to do this, what all
would I have to make sure I've escaped? Quotes, single quotes, and
backslashes, but anything else I'm overlooking?
Thanks for any suggestions,
Chris
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
OK, so first tests and internal docs/comments, then comes trying to
catch API , finally docs.
BTW, I think it is better to put together the discussion points we
have done as "general roadmap to complete window functions". It is not
about the features for the next release but is the complete tasks.
Where to go? Wiki, or my design docs?
Regards,
--
Hitoshi Harada
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tuesday 02 September 2008 17:21:12 Asko Oja wrote:Yes, but it is also far more complex to install, configure, and use, compared
> On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan <htfoot@gmail.com> wrote:
> > Oracle handles connecting to multiple databases (even on multiple/remote
> > computers) fairly seamlessly, PG does not (yet.)
>
> Stuff we do with plProxy on PostgreSQL is in some respects more advanced
> than anything Oracle has to offer :) We have hundreds of databases in quite
> complex network of remote calls and replication.
>
to something simple like oracle's dblink, which comes pre-installed, is
simple to set-up, and has a much more straight-forward syntax for use in day
to day query work.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
I have problems to setup SSL for PostgreSQL server. I did all the steps
which described in the documentation (17.8. Secure TCP/IP Connections
with SSL), but when I try to start the PostgreSQL server the pg_ctl gave
me: "could not start server". And nothing in the logs (I enabled all of
them). I googled around but did not find much.
My spec:
FreeBSD 7.0-RELEASE-p3 amd64
PostgreSQL 8.3.3 (installed from ports):
WITH_NLS=true
WITHOUT_PAM=true
WITHOUT_LDAP=true
WITHOUT_MIT_KRB5=true
WITHOUT_HEIMDAL_KRB5=true
WITHOUT_OPTIMIZED_CFLAGS=true
WITH_XML=true
WITHOUT_TZDATA=true
WITHOUT_DEBUG=true
WITH_ICU=true
WITH_INTDATE=true
Please help.
Andriy
--
Sent via pgsql-ru-general mailing list (pgsql-ru-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-ru-general
For example I'll use the Oracle "contains" function, though this is not
exactly what I'm doing, it just illustrates the issue clearly.
select *, score(1) from mytable where contains(mytable.title, 'Winding
Road', 1) order by score(1);
The "contains" function does a match against mytable.title for the term
'Winding Road' and both returns and saves an integer score which may be
retrieved later using the "score(...)" function. The integer used as a
parameter in score(...) and contains(...) is an index to reference which
score you need as more than one contains(...) call may be used in single
query.
This sets up an interesting issue, how can one ensure that "contains()" is
called prior to any "score()" function on each row? Is this possible? Is
there a specific order on which you can count?
Would it be something like: "where" clause first, left to right, followed
by select terms, left to right, and lastly the "order by" clause?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
