Tuesday, July 29, 2008

Re: [pgsql-fr-generale] Petit conseil

Bonjour à tous

Sébastien Lardière wrote:
> Stéphane Bunel a écrit :
>>> Je pense que je ne vais pas me mettre à migrer tout les sites que
>>> j'ai réaliser, mais je pense que le prochain "gros site" que je vais
>>> réaliser me permettra de me familiariser avec PostGreSQL.
>>
>> Mise en garde contre les risques d'addiction au produit: L'usage
>> prolongé de PostgreSQL peut rendre l'utilisateur dépendant d'un
>> certain niveau d'exigence, tant techniques que fonctionnelles. Un
>> effet secondaire connu est la rigolite (ou au contraire la tristesse
>> chez certain individu) aigu à la simple évocation de produits
>> prétendument "concurrent".
>
> :)
>
> C'est très vrai, ça. Faut faire attention, quand même, faudrait pas
> que ça devienne pathologique ...
>
Je crois que ça commence :)
Juste une dernière question sur une partie un peux plus "hardware"
32 ou 64bits? est-ce que ça change quelque chose sur la capacité de
connections simultanées de PG?
J'ai un vieux Proliant avec 2 Pentium 667mhz, 2go de SDRam ECC et 4
Disque SCSI en Raid5. Je pense l'utiliser pour mes tests mais d'après
vous, pour combien de connections simultanée il conviendrai? 20? 30?

Merci
Thibaut

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

Re: [HACKERS] TABLE-function patch vs plpgsql

Hello

2008/7/30 Hannu Krosing <hannu@krosing.net>:
> On Wed, 2008-07-30 at 07:29 +0200, Pavel Stehule wrote:
>> 2008/7/29 Hannu Krosing <hannu@krosing.net>:
>> > On Thu, 2008-07-17 at 19:13 -0400, Tom Lane wrote:
>> >> I've been working on the TABLE-function patch, and I am coming to the
>> >> conclusion that it's really a bad idea for plpgsql to not associate
>> >> variables with output columns --- that is, I think we should make
>> >> RETURNS TABLE columns semantically just the same as OUT parameters.
>> >
>> > I just looked at recent cahnges in pl/python, and found out that RETURNS
>> > TABLE is _NOT_ semantically just the same as OUT parameters, at least at
>> > API level.
>> >
>> > Why can't it be ?
>> >
>> > Why is PROARGMODE_TABLE needed at all ?
>>
>> because I need to separate classic OUT args from table args.
>
> I read your explanation, and I still don't understand, why can't TABLE
> and SETOF RECORD + OUT args be just different spellings of the same
> thing.
>
> Is there a scenario, where both are needed in the same function ?
>
>> TABLE function has more clean syntax, then our SRF functions,
>
> True. But why is separation on C API level needed ?

do you know any better way? I need to carry result description, and
using proargmodes is natural. In other case I needed add column to
pg_proc with result descriptor.

>
>> and it isn't
>> related only to SQL/PSM. It works nice together with SQL language.
>> Actually TABLE variables are exactly same as OUT variables (in
>> plpgsq), that is possible, but I am not sure, if it's best too.
>
> Still I have the same question - What is the difference ?
>

* remove varname and colname colisions
* solve unclean result type rules (one column .. specific type, two
and more .. record)

>> I have prototype where is possible declare variables derivated from
>> function return type
>> create function foo(..) returns table(x int, y int) as $$
>> declare result foo%rowtype; resx foo.x%type; ....
>
> I still don't see, why the same thing can't work on
>
> create function foo(OUT x int, OUT y int) returns setof record as $$
> declare result foo%rowtype; resx foo.x%type; ...

no it isn't. In this case you has local variables x, y - it's one from
typical postgresql bug

create function foo(out x int, out y iny)
returns setof record as $$
begin
for x,y in select x,y from tab loop -- it's wrong!!
return next;
end loop;
...

create function foo(out x int, out y int)
returns setof record as $$
begin
return query select x, y from tab; -- it's wrong too !

>
>> all this has to minimalist risk of variables and sql object name collisions.
>
> Are there any cases, where TABLE functions and OUT + returns SETOF
> RECORD functions are _called_ differently ?

no

Regards
Pavel Stehule

>
> ------------------
> Hannu
>
>
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TABLE-function patch vs plpgsql

On Wed, 2008-07-30 at 07:29 +0200, Pavel Stehule wrote:
> 2008/7/29 Hannu Krosing <hannu@krosing.net>:
> > On Thu, 2008-07-17 at 19:13 -0400, Tom Lane wrote:
> >> I've been working on the TABLE-function patch, and I am coming to the
> >> conclusion that it's really a bad idea for plpgsql to not associate
> >> variables with output columns --- that is, I think we should make
> >> RETURNS TABLE columns semantically just the same as OUT parameters.
> >
> > I just looked at recent cahnges in pl/python, and found out that RETURNS
> > TABLE is _NOT_ semantically just the same as OUT parameters, at least at
> > API level.
> >
> > Why can't it be ?
> >
> > Why is PROARGMODE_TABLE needed at all ?
>
> because I need to separate classic OUT args from table args.

I read your explanation, and I still don't understand, why can't TABLE
and SETOF RECORD + OUT args be just different spellings of the same
thing.

Is there a scenario, where both are needed in the same function ?

> TABLE function has more clean syntax, then our SRF functions,

True. But why is separation on C API level needed ?

> and it isn't
> related only to SQL/PSM. It works nice together with SQL language.
> Actually TABLE variables are exactly same as OUT variables (in
> plpgsq), that is possible, but I am not sure, if it's best too.

Still I have the same question - What is the difference ?

> I have prototype where is possible declare variables derivated from
> function return type
> create function foo(..) returns table(x int, y int) as $$
> declare result foo%rowtype; resx foo.x%type; ....

I still don't see, why the same thing can't work on

create function foo(OUT x int, OUT y int) returns setof record as $$
declare result foo%rowtype; resx foo.x%type; ...

> all this has to minimalist risk of variables and sql object name collisions.

Are there any cases, where TABLE functions and OUT + returns SETOF
RECORD functions are _called_ differently ?

------------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TABLE-function patch vs plpgsql

2008/7/29 Hannu Krosing <hannu@krosing.net>:
> On Thu, 2008-07-17 at 19:13 -0400, Tom Lane wrote:
>> I've been working on the TABLE-function patch, and I am coming to the
>> conclusion that it's really a bad idea for plpgsql to not associate
>> variables with output columns --- that is, I think we should make
>> RETURNS TABLE columns semantically just the same as OUT parameters.
>
> I just looked at recent cahnges in pl/python, and found out that RETURNS
> TABLE is _NOT_ semantically just the same as OUT parameters, at least at
> API level.
>
> Why can't it be ?
>
> Why is PROARGMODE_TABLE needed at all ?

because I need to separate classic OUT args from table args. TABLE
function has more clean syntax, then our SRF functions, and it isn't
related only to SQL/PSM. It works nice together with SQL language.
Actually TABLE variables are exactly same as OUT variables (in
plpgsq), that is possible, but I am not sure, if it's best too.

I have prototype where is possible declare variables derivated from
function return type
create function foo(..) returns table(x int, y int) as $$
declare result foo%rowtype; resx foo.x%type; ....

all this has to minimalist risk of variables and sql object name collisions.

Regards
Pavel Stehule

>
>> 4. It's a whole lot easier to explain things if we can just say that
>> OUT parameters and TABLE parameters work alike. This is especially
>> true when they actually *are* alike for all the other available PLs.
>
> It would be nice, if they were the same at API level as well.
>
> --------------------
> Hannu
>
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[BUGS] Segfault manifesting in libm from cost_sort

Our pg keeps going into recovery mode after segfaulting. This is a compiled 8.3.3 on Ubuntu 6.04 with Slony.

Some ideas from IRC:
RhodiumToad: 1) probably least likely, something corrupt in the math libs; the fact that it's not reproducible makes this improbable
RhodiumToad: 2) more likely: a register or memory stomp in a signal handler, which could be the result of an OS bug or a pg miscompile
RhodiumToad: 3) slightly less likely: a memory stomp somewhere else in pg that happens to be clobbering something in the math library

Here is some gdb output from a core dump:

(gdb) bt
#0  0x00002aaaaae09a32 in fegetexcept () from /lib/libm.so.6
#1  0x00002aaaaae1e7e4 in log () from /lib/libm.so.6
#2  0x000000000055250d in cost_sort ()
#3  0x0000000000554757 in cost_mergejoin ()
#4  0x0000000000570673 in create_mergejoin_path ()
#5  0x000000000055a337 in add_paths_to_joinrel ()
#6  0x000000000055b650 in make_join_rel ()
#7  0x000000000055bb61 in join_search_one_level ()
#8  0x000000000055145d in standard_join_search ()
#9  0x0000000000563329 in query_planner ()
#10 0x0000000000563e98 in grouping_planner ()
#11 0x0000000000564d08 in subquery_planner ()
#12 0x000000000056511a in standard_planner ()
#13 0x00000000005a7271 in pg_plan_query ()
#14 0x00000000005a7877 in pg_plan_queries ()
#15 0x00000000005a7b2e in exec_simple_query ()
#16 0x00000000005a9495 in PostgresMain ()
#17 0x000000000057ec88 in ServerLoop ()
#18 0x000000000057f7cb in PostmasterMain ()
#19 0x00000000005370ee in main ()
(gdb) frame
#0  0x00002aaaaae09a32 in fegetexcept () from /lib/libm.so.6
(gdb) info reg
rax            0xa2dd80    10673536
rbx            0x3ffc000000000000    4610560118520545280
rcx            0x2b0    688
rdx            0x3fec000000000000    4606056518893174784
rsi            0x2aaaaae55968    46912499964264
rdi            0x6900    26880
rbp            0x2aaaaae55960    0x2aaaaae55960
rsp            0x7fffff877510    0x7fffff877510
r8             0x2aaaaae54e08    46912499961352
r9             0x2aaaaae54e00    46912499961344
r10            0x2aaaaae542a0    46912499958432
r11            0x6900    26880
r12            0x684c    26700
r13            0xffffffe0    4294967264
r14            0x2aaaaae53ce0    46912499956960
r15            0xa0a448    10527816
rip            0x2aaaaae09a32    0x2aaaaae09a32 <fegetexcept+48178>
eflags         0x10206    66054
cs             0x33    51
ss             0x2b    43
ds             0x0    0
es             0x0    0
fs             0x0    0
gs             0x0    0
(gdb) disass 0x2aaaaae09a00 0x2aaaaae09b00
Dump of assembler code from 0x2aaaaae09a00 to 0x2aaaaae09b00:
0x00002aaaaae09a00 <fegetexcept+48128>:    and    $0x8,%al
0x00002aaaaae09a02 <fegetexcept+48130>:    mov    0x8(%rsp),%r11
0x00002aaaaae09a07 <fegetexcept+48135>:    movlpd 317329(%rip),%xmm6        # 0x2aaaaae571a0 <__signbitl+131008>
0x00002aaaaae09a0f <fegetexcept+48143>:    sar    $0x20,%r11
0x00002aaaaae09a13 <fegetexcept+48147>:    mulsd  %xmm11,%xmm6
0x00002aaaaae09a18 <fegetexcept+48152>:    and    $0xfffff,%r11d
0x00002aaaaae09a1f <fegetexcept+48159>:    addsd  %xmm14,%xmm5
0x00002aaaaae09a24 <fegetexcept+48164>:    sar    $0x4,%r11d
0x00002aaaaae09a28 <fegetexcept+48168>:    lea    0xffffffffffffff4c(%r11),%r12d
0x00002aaaaae09a2f <fegetexcept+48175>:    movslq %r11d,%rdi
0x00002aaaaae09a32 <fegetexcept+48178>:    mulsd  (%r10,%rdi,8),%xmm8   #########         <------ where it segfaults
0x00002aaaaae09a38 <fegetexcept+48184>:    shl    $0x4,%rdi
0x00002aaaaae09a3c <fegetexcept+48188>:    cvtsi2sd %r12d,%xmm9
0x00002aaaaae09a41 <fegetexcept+48193>:    movlpd (%rdi,%rbp,1),%xmm3
0x00002aaaaae09a46 <fegetexcept+48198>:    movlpd (%rdi,%rsi,1),%xmm13
0x00002aaaaae09a4c <fegetexcept+48204>:    ucomisd %xmm3,%xmm12
0x00002aaaaae09a51 <fegetexcept+48209>:    mulsd  317246(%rip),%xmm9        # 0x2aaaaae57198 <__signbitl+131000>
0x00002aaaaae09a5a <fegetexcept+48218>:    addsd  %xmm14,%xmm9

The value of r10 is the same every time, but rdi changes.

Andrew

Re: [HACKERS] Review: DTrace probes (merged version) ver_03

Updated patch attached.

This patch addresses all of Alvaro's feedback except the new probes for
v3 protocol which will be submitted later along with the rest of the probes.

It also incorporates Tom's feedback as explained inline below.

I hope this patch is good to go for this commit fest. Will take care of
the rest in the next fest.

Tom Lane wrote:
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>
>> I performed review and I prepared own patch which contains only probes
>> without any issue. I suggest commit this patch because the rest of
>> patch is independent and it can be committed next commit fest after
>> rework.
>>
>
> I looked at this patch a little bit. In addition to the comments Alvaro
> made, I have a couple more issues:
>
> * The probes that pass buffer tag elements are already broken by the
> pending "relation forks" patch: there is soon going to be another field
> in buffer tags. Perhaps it'd be feasible to pass the buffer tag as a
> single probe argument to make that a bit more future-proof? I'm not
> sure if that would complicate the use of the probe so much as to be
> counterproductive.
>
>
>
Took out the buffer tag argument for now. Will figure out how to best
solve this after this relation forks patch is committed.
>
> What I suggest might be a reasonable compromise is to copy needed
> typedefs directly into the probes.d file:
>
> typedef unsigned int LocalTransactionId;
>
> provider postgresql {
>
> probe transaction__start(LocalTransactionId);
>
> This at least makes it possible to declare the probes cleanly,
> and it's fairly obvious what to fix if the principal definition of
> LocalTransactionId ever changes. I don't have Solaris to test on, but
> on OS X this seems to behave the way we'd like: the typedef itself isn't
> copied into the emitted probes.h file, but the emitted extern
> declarations use it.
>
>
Implemented this suggestion. There are some weirdness with the OS X
compiler causing some of the probe declarations not to compile (see
comments in probe.d). The compiler spits out some warnings because the
types don't show up in the function prototype in probes.h, but the
probes work okay. I think we can safely ignore the warnings.

--
Robert Lor Sun Microsystems
Austin, USA http://sun.com/postgresql

Re: [pgsql-es-ayuda] Nuevas presentaciones en la wiki

On Tue, Jul 29, 2008 at 3:31 PM, Julio Cesar Rodriguez Dominguez
<jurasec@gmail.com> wrote:
> Algo anda mal con los pdf's, no los pude abrir.
>

yo las abri (desde la wiki claro), en un ubuntu y en *cof* windows *cof*


--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [HACKERS] WITH RECUSIVE patches 0723

On Mon, Jul 28, 2008 at 02:49:01PM -0400, Tom Lane wrote:
> Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> > "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> > Tom> That whole business of using the EState to pass tuplestores back
> > Tom> and forth looks fundamentally broken to me anyway; there's just
> > Tom> no way it'll be certain to link the right nodes together in
> > Tom> complicated cases with multiple recursions.
>
> > Mutual recursion is not allowed;
>
> Well, the spec allows it, so we're going to have to fix this kluge
> sooner or later, even if it chances not to fail on the subset we
> currently support.

Any ideas how to approach this?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: 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

[pgsql-es-ayuda] Fw: Fw: Urgentiiiiiiiiiiiiiisimooooooooooo!!!!!!!!!!



--- El lun 28-jul-08, info-apartho dean funes <info@aparthodeanfunes.com.ar> escribió:
De: info-apartho dean funes <info@aparthodeanfunes.com.ar>
Asunto: Fw: Fw: Urgentiiiiiiiiiiiiiisimooooooooooo!!!!!!!!!!
Para: fabiogvoigt@yahoo.com.ar
Fecha: lunes, 28 de julio de 2008, 6:53 pm

 
APARTHO dean funes
( 0387 ) 4011122 / 33
( 0387 ) 4321198 / 90
www.aparthodeanfunes.com.ar
www.hotelapartho.todowebsalta.com.ar
----- Original Message -----
Sent: Monday, July 28, 2008 2:34 PM
Subject: Fwd: Fw: Urgentiiiiiiiiiiiiiisimooooooooooo!!!!!!!!!!



--- El lun 28-jul-08, Guillermo Bernardi <ghbernardi@gmail.com> escribió:
De: Guillermo Bernardi <ghbernardi@gmail.com>
Asunto: Fwd: Fw: Urgentiiiiiiiiiiiiiisimooooooooooo!!!!!!!!!!
Para:
Fecha: lunes, 28 de julio de 2008, 10:00 am


ES POSIBLE Y CASI CON SEGURIDAD QUE NO SEA CIERTO PERO POR LAS DUDAS 

 



POR FAVOR, HAZ CIRCULAR ESTE AVISO A TUS AMISTADES, FAMILIA, CONTACTOS !!!  
En los próximos días, debes estar atent@: No abras ningún mensaje con un archivo anexo llamado:  Invitación de hi 5,  independientemente de quien te lo envíe. Es un virus que abre   una antorcha olímpica que quema todo el disco duro C de la computadora. Este virus vendrá de una persona conocida que te  
tenia en su lista de direcciones.. Es por eso que debes enviar este mensaje a todos tus contactos.

Es preferible recibir 25 veces este correo que recibir el virus y abrirlo.. Si recibes el mensaje llamado: Invitación de hi 5, aunque sea enviado por un amigo, no lo abras y apaga tu maquina inmediatamente. Es el peor virus anunciado por CNN.  Un nuevo virus ha sido descubierto recientemente que ha sido clasificado por Microsoft como el virus mas destructivo que haya existido . Este virus fue descubierto ayer por la tarde por Mc Afee. Y no hay arreglo aun para esta clase de virus. Este virus destruye simplemente el Sector Zero del Disco Duro, donde la información vital de su función es guardada.


ENVIA ESTE E-MAIL A QUIENES CONOZCAS. COPIA ESTE CORREO A UNO NUEVO Y MANDALO A TODOS TUS AMIGOS.

RECUERDA:
SI LO ENVIAS A ELLOS, NOS BENEFICIAS A TODOS.

 



--
LA SOLEDAD SOLO ENTRA EN ELCORAZON QUE HA PERDIDO SUS RECUERDOS.-
Saludos.-




















































































.



¡Buscá desde tu celular! Yahoo! oneSEARCH ahora está en Claro
http://ar.mobile.yahoo.com/onesearch



¡Buscá desde tu celular! Yahoo! oneSEARCH ahora está en Claro
http://ar.mobile.yahoo.com/onesearch

[HACKERS] modifying views

I've been thinking a little more about modifying views:

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00694.php

AFAICS, out of all of the things that ALTER TABLE is capable of doing,
there are only four that make any sense in the context of views:

(1) add a column
(2) drop a column
(3) change the type of an existing column
(4) renaming a column

The tricky part of course is that the underlying query has to change
along with the type definition. It's fairly obvious what the
semantics of dropping a column should be, but the other cases are
trickier. New columns could possibly be returned as NULL (pending a
subsequent CREATE OR REPLACE VIEW), but changing the type of a column
just seems crazy as a standalone operation. The bigger problem though
is that I'm pretty sure that ALTER TABLE <viewname> ADD COLUMN <name>
<type> is not a command that anyone is eager to be typing. What we
really want to be able to do is a CREATE OR REPLACE VIEW command and
have the system worry about what columns needed to be added, dropped,
or retyped, and giving us an error if we've tried to (for example)
remove a column that some other view depends on. (Apart from CREATE
OR REPLACE VIEW, the only operation that looks independently useful to
me is RENAME COLUMN.)

Unfortunately, it looks to me like a fully general implementation of
this feature would be Really Hard, because a CREATE OR REPLACE VIEW
command, beyond attempting to add, drop, or retype columns, could also
attempt to reorder them. A cursory inspection of the situation
suggests this would require modifying the attnum values in
pg_attribute, which looks like a nightmare. (Anyone want to argue
otherwise? Tom? I'd love to be wrong about this...) However, it
might be possible to allow some or all of the following: (1) dropping
columns from anywhere, (2) adding new columns at the end, and (3)
changing the type of existing columns. (I think (1) and (2) are
clearly more useful than (3).)

Thoughts?

...Robert

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[pgsql-advocacy] 1 week until LinuxWorld SF: volunteers, registration

Folks,

Just a reminder: there's only one week until LinuxWorld and its associated
pgDay on Tuesday August 5th.

First of all, we still need some volunteers, especially:
-- invitation-checkers for the party
-- booth duty during the morning of the pgDay
-- Thursday Morning booth volunteer
-- Thursday afternoon pick-up (with car)
Volunteers should e-mail me their t-shirt sizes.

Second, this is a reminder that all attendees and volunteers (except
speakers) must register with LinuxWorld:
http://www.linuxworldexpo.com/live/12/ehall//SN460564

Select the "PostgreSQL Community Day" option *only*; the system errors if
you try to select multiple options. You'll still be able to get in.

Thanks, and we're going to have a great time.

--
--Josh

Josh Berkus
PostgreSQL
San Francisco

--
Sent via pgsql-advocacy mailing list (pgsql-advocacy@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-advocacy

Re: [GENERAL] Cursor

On Jul 29, 2008, at 4:51 PM, Klint Gore wrote:
> It's different in PL/pgSQL.

Ah, yes, sorry, didn't catch that it was a PL/pgSQL function.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Date index not used when selecting a date range

> Have you run analyze on the table?
>
Yes
> How selective is the condition node = '1010101010' and the date range. In
> particular, do you have an idea what percentange of the table fits into
> that date range?
>
There are around 1000 nodes and there is data for two years, so it must
be around 1/4000 of all rows
> What about the "type" column? You have it in the index, but not in your
> query. Have you tried adding type to the query? Will that make it more
> selective?
>
>
Why haven't I tried that ... That did the trick, thanks!

Poul


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[ODBC] compiling dlg_specific.c file on Sun Solaris 5.8

I've detected problem with compiling dlg_specific.c file on Sun Solaris 5.8 using compiler 'cc: Sun C 5.8 2005/10/13'.
To resolve this problem I've changed line 896 to
qlog(" conn_settings='%s',conn_encoding='%s'\n",
ci->conn_settings, enc ? enc : (UCHAR*)"(null)");

I've added cast to (UCHAR*), because compiler can not automatically cast to correct type.

Regards
Marcin


----------------------------------------------------------------------
Tanie rozmowy!
Sprawdz >>> http://link.interia.pl/f1e91


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [JDBC] numeric type

>> For numeric types (with no explicit scale and precision) JDBC returns 0
for
>> both precision and scale (ResultSetMetaData.getPrecision and getScale
>> methods). This is breaking my app and IMO does not reflect true state of
>> things since Postgres docs state: "NUMERIC without any precision or scale
>> creates a column in which numeric values of any precision and scale can
be
>> stored, up to the implementation limit on precision".
>>
>> Shouldn't PG JDBC driver return maximum possible values for precision and
>> scale in such cases?
>>
>
> There are a couple of issues here:
>
> 1) While PG supports larger numeric values, the maximum precision you can
> constrain it to is 1000.
>
> jurka=# create temp table tt (a numeric (1001, 1));
> ERROR: NUMERIC precision 1001 must be between 1 and 1000
>
> So do you return 1000 as the maximum precision because that's the maximum
> that they can pass into create table even if they might actually receive a

> value back that has a higher precision? Or do you return something larger

> (the internal pg limit which I don't know offhand) even though they can't
> create a table column with that limit?


> What we do now certainly isn't all that great, but I haven't heard a
> concrete suggestion on what's better.

OK, I hear ya. How about allowing user to set the default precision and
scale values for "numeric" type? Not sure how this would be done in JDBC
world but should be doable.

IMO a reasonable default maxing out both precision and scale (1000,500)
should work for most applications. Numeric type is not all that common
anyway.

Peter


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[ODBC] resolution for bug 1010313

I've resolved bug number 1010313 (it was neccessary for me because I use psqlodbc driver
on Sun Solaris Sparc with UTF-8 characters).

Top resolve this bug I've added is_big_endian() that determines whether platform supports
big endian or little endian bytes order.
This method is also used becuase it is not possible to use any standard define,
that is "standard define" and works for different compiler and operating systems.

Additionaly, becuase I don't want to calculate every time in ucs2_to_utf8 function
platform endianness I've used two static variables, first big_endian stores endianness status
and second is_endian_calc determines whether endianness was calculated.

And finally, in ucs2_to_utf8 I've added swapping bytes for big_endian platform.

Below is source code that I've implemented
(I've send all ucs2_to_utf8 function source code)

int is_big_endian()
{
union { long l; char c[sizeof (long)]; } u;
u.l = 1;
return (u.c[sizeof (long) - 1] == 1);
}

char *ucs2_to_utf8(const SQLWCHAR *ucs2str, SQLLEN ilen, SQLLEN *olen, BOOL lower_identifier)
{
char * utf8str;
/*mylog("ucs2_to_utf8 %p ilen=%d ", ucs2str, ilen);*/

/*
* is this a bigendian machine ?
*/
static int is_endian_calc = 0;
static int big_endian = 0;
if(!is_endian_calc)
{
big_endian = is_big_endian();
is_endian_calc = 1;
}

if (!ucs2str)
{
*olen = SQL_NULL_DATA;
return NULL;
}
if (SQL_NTS == ilen)
ilen = ucs2strlen(ucs2str);
/*mylog(" newlen=%d", ilen);*/
utf8str = (char *) malloc(ilen * 4 + 1);
if (utf8str)
{
int i, len = 0;
union { UInt2 i; char c[sizeof (UInt2)]; } byte2code;
union { Int4 i; char c[sizeof (Int4)]; } byte4code, surrd1, surrd2;
const SQLWCHAR *wstr;

for (i = 0, wstr = ucs2str; i < ilen; i++, wstr++)
{
if (!*wstr)
break;
else if (0 == (*wstr & 0xffffff80)) /* ASCII */
{
if (lower_identifier)
utf8str[len++] = (char) tolower(*wstr);
else
utf8str[len++] = (char) *wstr;
}
else if ((*wstr & byte3check) == 0)
{
byte2code.i = byte2_base |
((byte2_mask1 & *wstr) >> 6) |
((byte2_mask2 & *wstr) << 8);
if(big_endian)
{
memcpy(utf8str + len, (char *) &byte2code.c[1], 1);
memcpy(utf8str + len + 1, (char *) &byte2code.c[0], 1);
}
else
{
memcpy(utf8str + len, (char *) &byte2code.i, sizeof(byte2code.i));
}
len += sizeof(byte2code.i);
}
/* surrogate pair check for non ucs-2 code */
else if (surrog1_bits == (*wstr & surrog_check))
{
surrd1.i = (*wstr & ~surrog_check) + surrogate_adjust;
wstr++;
i++;
surrd2.i = (*wstr & ~surrog_check);
byte4code.i = byte4_base |
((byte4_sr1_mask1 & surrd1.i) >> 8) |
((byte4_sr1_mask2 & surrd1.i) << 6) |
((byte4_sr1_mask3 & surrd1.i) << 20) |
((byte4_sr2_mask1 & surrd2.i) << 10) |
((byte4_sr2_mask2 & surrd2.i) << 24);
if(big_endian)
{
memcpy(utf8str + len, (char *) &byte2code.c[3], 1);
memcpy(utf8str + len + 1, (char *) &byte2code.c[2], 1);
memcpy(utf8str + len + 2, (char *) &byte2code.c[1], 1);
memcpy(utf8str + len + 3, (char *) &byte2code.c[0], 1);
}
else
{
memcpy(utf8str + len, (char *) &byte4code.i, sizeof(byte4code.i));
}
len += sizeof(byte4code.i);
}
else
{
byte4code.i = byte3_base |
((byte3_mask1 & *wstr) >> 12) |
((byte3_mask2 & *wstr) << 2) |
((byte3_mask3 & *wstr) << 16);
if(big_endian)
{
memcpy(utf8str + len, (char *) &byte2code.c[3], 1);
memcpy(utf8str + len + 1, (char *) &byte2code.c[2], 1);
memcpy(utf8str + len + 2, (char *) &byte2code.c[1], 1);
}
else
{
memcpy(utf8str + len, (char *) &byte4code.i, 3);
}
len += 3;
}
}
utf8str[len] = '\0';
if (olen)
*olen = len;
}
/*mylog(" olen=%d %s\n", *olen, utf8str ? utf8str : "");*/
return utf8str;
}

----------------------------------------------------------------------
W kosciele tez zdarzaja sie wpadki!
Smieszny filmik >>> http://link.interia.pl/f1e61


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [HACKERS] gsoc, oprrest function for text search

Heikki Linnakangas wrote:
> Jan Urbański wrote:
>> Here's a WIP patch implementing an oprrest function for tsvector @@
>> tsquery and tsquery @@ tsvector.
>>
>> The idea is (quoting a comment)
>> /*
>> * Traverse the tsquery preorder, calculating selectivity as:
>> *
>> * selec(left_oper) * selec(right_oper) in AND nodes,
>> *
>> * selec(left_oper) + selec(right_oper) -
>> * selec(left_oper) * selec(right_oper) in OR nodes,
>> *
>> * 1 - select(oper) in NOT nodes
>> *
>> * freq[val] in VAL nodes, if the value is in MCELEM
>> * min(freq[MCELEM]) / 2 in VAL nodes, if it is not
>
> Seems reasonable.
>
>> *
>> * Implementation-wise, we sort the MCELEM array to use binary
>> * search on it.
>> */
>
> Would it be possible to store the array in sorted order, to avoid
> sorting it on every invocation of tssel?

It's being stored sorted on frequencies, like so:
[('dog', 0.9), ('cat', 0.8), ('sheep', 0.7)]
and I need it sorted on elements for bsearch().

I don't know if it's OK to break the rule that statistical data is
stored sorted on freqneucies. If so, then ts_typanalyze() would have to
change and do one more qsort() before storing the result.

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [GENERAL] Date index not used when selecting a date range

>> This is the index:
>> CREATE INDEX idx_stat_date_node_type
>> ON public.stat
>> USING btree
>> (date, node, "type");
>>
>>
>> explain SELECT * FROM public.stat WHERE node = '1010101010' AND
>> ("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date)
>>
>
> Try changing the index order to node, date rather than date, node. You
> need the column on which you'll be doing range checking to be the
> furthest to the right in the index column list.
>
>
Then it works. Unfortunately the production database is always in use
and it contains more than 100 mill. rows,
so creating an index is not an option.

Poul

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] gsoc, oprrest function for text search

Jan Urbański wrote:
> Here's a WIP patch implementing an oprrest function for tsvector @@
> tsquery and tsquery @@ tsvector.
>
> The idea is (quoting a comment)
> /*
> * Traverse the tsquery preorder, calculating selectivity as:
> *
> * selec(left_oper) * selec(right_oper) in AND nodes,
> *
> * selec(left_oper) + selec(right_oper) -
> * selec(left_oper) * selec(right_oper) in OR nodes,
> *
> * 1 - select(oper) in NOT nodes
> *
> * freq[val] in VAL nodes, if the value is in MCELEM
> * min(freq[MCELEM]) / 2 in VAL nodes, if it is not

Seems reasonable.

> *
> * Implementation-wise, we sort the MCELEM array to use binary
> * search on it.
> */

Would it be possible to store the array in sorted order, to avoid
sorting it on every invocation of tssel?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [GENERAL] How to give input a file for a stored procedure

aravind chandu wrote:
> Hi,
>
> I am writing a stored procedure where the input to it is a file.I did not have any idea of how to give input as a file for a stored procedure.could you please help me.

I assume that by "a file" you mean a file in the filesystem, outside the
database, rather than BLOB in the database.

If so, then there are functions in one of the contrib modules that
provide filesystem access from the PostgreSQL server. They're only
available to superusers, but if you were REALLY careful you could use
them in a SECURITY DEFINER stored procedure. This is a VERY DANGEROUS
thing to do unless you absolutely trust all users of the database. If
you do not know what the above means, then do not do it.

Read the PostgreSQL documentation to find out more. Start here:

http://www.postgresql.org/docs/8.3/static/index.html

Pay particular attention to the section "Additional Supplied Modules" in
the appendices.

Note that those functions do not get you access to the *client*
filesystem. Access is to the server file system with the rights of the
user the postgresql server runs under.

If you want to access a file from the client with the rights of the user
the client is running under, you will need to have your client
application load the file and send it to the server. You might load the
file into the database as a BLOB, or just pass it as a bytea field.

Perhaps if you explained the purpose of what you were attempting you
might get more useful answers.

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Date index not used when selecting a date range

> You can prevent postgres from using the index on node by changing the reference in the WHERE clause to an expression, like so:
>
> SELECT * FROM public.stat WHERE node||'' = '1010101010' AND
> ("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date);
>
> Perhaps this will lead the optimizer to choose the index on date. However, I have noticed reluctance in the postgres optimizer to use multi-column indexes, presumably because the increased size of the indexed values lowers expectations for performance of the index.
>
>
>
I think you are right about the multi-column usage.
When I use node||'' instead of node, it will do a seq scan.

Poul


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general