Monday, September 1, 2008

[JDBC] UPDATE ... CURRENT OF cursorname

I looking for an explination of how named cursor updates actually
work. For example from the following link:
http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/resultset.html


the code seems to imply that all rows from a name cursor will be update.

...
stmt.setCursorName("x");
ResultSet rs = stmt.executeQuery(
"SELECT . . . FROM . . . WHERE . . . FOR UPDATE . . .")
String cursorName = rs.getCursorName;
int updateCount = stmt2.executeUpdate(
"UPDATE . . . WHERE CURRENT OF " + cursorName);
...

However from the postgresql manual, the verbase seems to indicate that
only the record currently pointed to in the cursor is updated.
http://www.postgresql.org/docs/8.3/interactive/sql-update.html

...
cursor_name
The name of the cursor to use in a WHERE CURRENT OF condition. The
->row<- to be updated is the one most recently fetched from this
cursor.
...

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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

Re: [BUGS] BUG #4389: FATAL: could not reattach to shared memory (key=1804, addr=018E0000): 487

could not reattach to shared memory napsal(a):
> The following bug has been logged online:
>
> Bug reference: 4389
> Logged by: could not reattach to shared memory
> Email address: diesel_den@ukr.net
> PostgreSQL version: 8.3.3-1
> Operating system: any 8.3.*
> Description: FATAL: could not reattach to shared memory (key=1804,
> addr=018E0000): 487
> Details:
>
> This error came week ago.
> From that 'black' day I can not use Postgre.
> I have reinstalled several 8.3.* versions (including last version with
> vcredist_x86.exe) and nothing helps me.
>

try to remove postgesql.pid file in the data directory.

Zdenek

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

Re: [pgsql-es-ayuda] Cómo cambio el plan de ejecución de un query

"Carlos Beltrán V." escribió:

> Alvaro, como realmente los selects de este tipo, se están usando en
> muchos scripts y son planos, ¿debo agruparlos como está el ejemplo o
> podría existir otra opción?

¿Probaste a darle un COST a la función?

> Y por último ¿Cuál fue la motivación para que implementaran este nuevo
> comportamiento en la versión 8.3.3?

Mejorar el optimizador.

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
Renaming ReiserFS to NinaFS is such an amazingly stupid suggestion, in so many
ways, that it ought to qualify for some kind of award. Or perhaps we should
name an award after it: the "NinaFS award" for outstanding crassness.
(edmundo, http://lwn.net/Articles/203846/)
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [pgsql-es-ayuda] Problema insertando datos (TIME)

Cuando t ocurren esos problemas deberias dar una vuelta para que despejes la mente.. xD


--
Joseph Cesar Mena S.
+51 (1) 991526548
L1nuX R3g1$t3r3d U53r #412302
.~.
( 0 0 )
/ V \
// \\
/(( _ ))\

[pgsql-es-ayuda] Exprimir a Postgre

Amigos como estan, acudo ante ustedes para pedirles sus consejos, estoy desarrollando una aplicacion con punto net en windows forms y  postgre, ahora mi idea es explotar todas las virtudes de la base de dtos, ya que necesito hacer la aplicacion lo mas rapido posible, porque estamos migrando de un sistenma antiguo hecho en fox  a la nueva base y lenguaje, entonces quisiera que me ayuden con que cosas tengo que tener en consideracion para sacarle el jugo a la base de datos y hacer mi aplicacion lo mas rapido posible,  tambien necesito manejar replicas, y grandes volumenes de informacion, de pronto como deberia manejar las conexiones, si el crear indices, y llaves me ayudara, donde deberia manejar la concurrencia, la integridad referencial en punto net o directo con las llaves en postgre, bueno como veran soy nuevo en esto ahi que espero me puedas dar sus consejos segun las experieancias que han tenido.

De antemano agradesco cualquier ayuda que me puedan dar,

Saludos


Get more from your digital life. Find out how.

Re: [pgadmin-support] Table Constraints Bug

Matthew Arp a écrit :
> pgAdminIII will not allow you to add a self-referential constraint while
> using the GUI table editor, see the below example:
>
> CREATE TABLE "role"
> (
> uid integer NOT NULL,
> "name" character varying(32) NOT NULL,
> parent integer NOT NULL DEFAULT 1,
> CONSTRAINT role_pkey PRIMARY KEY (uid),
> CONSTRAINT role_parent_fkey FOREIGN KEY (parent)
> REFERENCES "role" (uid) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE RESTRICT
> )
> WITH (OIDS=FALSE);
> ALTER TABLE "role" OWNER TO glasshook_admin;

Can we have more details? because I can add a foreign key which refers
to the same table with the GUI table editor.


--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

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

[GENERAL] immutable functions and enumerate type casts in indexes

Hello,

I have a problem with enumerated types in functions parameters.

my enumerated type is (this is a subset)
CREATE TYPE hibridation AS ENUM('none','genus','specie');

function declaration
CREATE FUNCTION ename(text,boolean,text,text RETURNS text AS
'funzioniGDB.so' LANGUAGE C IMMUTABLE;

index creation (the type of ibrido is hibridation)
CREATE INDEX i_specie_nome_specie ON specie
(esterna_nome(ibrido::text,proParte,genere,specie));

the result is
ERROR: functions in index expression must be marked IMMUTABLE

Searching on google I found some explanation: the problem arises with
"non immutable" typea as "data" but I can't figure the problem (or
better, the solution) with enumerate types.

What can I do?

thank you
Edoardo

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

Re: [BUGS] Bug in RETURN QUERY

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 2008/9/1 Oleg Serov <serovov@gmail.com>:
>> ERROR: structure of query does not match function result type
>> CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/

> this isn't bug, it's feature.

No, it's a bug, and it's not related to plan caching at all --- even if
you start a fresh session the error persists. The problem is that
plpgsql isn't very good at dealing with rowtypes that contain dropped
columns. Unfortunately Oleg shouldn't hold his breath waiting for a
fix, because it's not trivial. In this example, the function would need
to return a three-column tuple (id, dropped-column, buggy_enum_field)
but the SELECT is only giving it two columns. There isn't anything in
plpgsql that has the ability to convert a tuple to add dropped columns
in the right places. I think we'd consider adding such functionality
as a new feature not a back-patchable bug fix.

The best near-term workaround would be to handle changes like this by
means of ALTER COLUMN TYPE rather than dropping and re-adding columns.

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

Re: [pgsql-www] Download links

On Mon, Sep 1, 2008 at 4:54 PM, Chander Ganesan <chander@otg-nc.com> wrote:

> All I'm saying is that there is a major decrease in usability of the site
> when it's not clearly documented where someone can get the latest patch
> version for their release.

It never has been clearly documented, nor was it ever particularly
usable due to the large number of places and ways that people get
their upgrades, most of which didn't actually come from us. What is
there now is designed to help people drill down to the distribution
that is right for them (which the old method failed to do completely
unless they ran Windows or built from source), from where they can
find the distribution-specific upgrade path.

For example, If you run Solaris, you Click Downloads -> Solaris ->
Download and then pick the version number and architecture you need.

If you run Debian, you click Downloads -> Linux where you'll see a
section for Debian/APT, with links to the Debian website and Debian
packages database.

Just to re-iterate, some time back I did ask for contributions to
improve the wording for each platform/distro. Iirc, Devrim and Peter
both kindly helped out. Further suggestions are welcome.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [BUGS] Bug with FOR ... LOOP and composite types

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 2008/9/1 Oleg Serov <serovov@gmail.com>:
>> Seems there is an error when I try to use a table with one field - composite
>> type, when SELECT QUERY in FOR ... LOOP instruction returns empty result.

> ROWTYPE is problem.

I think it actually is a bug. exec_for_query tries to set the target to
null this way:

exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);

and exec_move_row thinks it doesn't have to present a valid "valtype"
to exec_assign_value when it's assigning a made-up null, and that fails
when the target is of PLPGSQL_DTYPE_ROW type (looks like it'd fail for
REC type too, but ROW is the case here).

We could work around the particular issue by moving the
type_is_rowtype() tests down so they're not done for a null source
value, but I think that's just a hack. A cleaner fix would be to teach
exec_move_row to present the correct column type in all cases.

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

Re: [pgsql-es-ayuda] default current_time

José Fermín Francisco Ferreras escribió:
>
> Hola, como muchos d ustedes saben soy d República Dominicana, y
> necesito ver insertar la hora en un camo de una tabla.
>
> Este ejemplito :
>
> create table tabla
> (
> codigo integer primary key,
> hora time default current_time
> );

El problema seguramente no es el DEFAULT, sino que current_time no está
retornando la hora correcta. Prueba haciendo lo siguiente:

select current_time;

y fíjate si la hora concuerda con la del sistema o no.

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"El día que dejes de cambiar dejarás de vivir"
--
TIP 5: ¿Has leído nuestro extenso FAQ?
http://www.postgresql.org/docs/faqs.FAQ.html

Re: [pgsql-www] Download links

Alvaro Herrera wrote:
>> I had to spend a bit of time hunting around to find the latest release
>> of 8.2.x, in fact, it's not really "indicated" anywhere that "old"
>> versions are there. Looking at the /download page, I cannot find a
>> single reference to older versions (the big advantage to the old
>> format). One has to hunt around until they find the "source code" link
>> to locate an older version of postgresql.
>>
>
> There's no usefulness whatsoever to the old versions, so why should they
> be linked to?
>
>
There are many organizations that have PostgreSQL's older versions
deployed, and have yet to upgrade to the latest major release...but
still have need to download the latest patch release to upgrade their
installation. IMHO, for this reason the older versions are extremely
important. Hence the need to link to them.

All I'm saying is that there is a major decrease in usability of the
site when it's not clearly documented where someone can get the latest
patch version for their release.

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
919-463-0999/877-258-8987
http://www.otg-nc.com


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

Re: [GENERAL] refcursor

mike stanton escribió:
>
> Hello all,
> I've got a problem I can't get round. I want to declare a refcursor variable in a C program and call a function which returns a refcursor portal to the main C program.
> When I compile the C program, I get a refcursor doesn´t exist message.
> Has anyone got a C program which uses a refcursor variable?

Make sure the C program opens a transaction before calling the function,
otherwise the cursor is automatically closed as soon as the function ends.

--
Alvaro Herrera Valdivia, Chile Geotag: -39,815 -73,257
"Ah, spring... when a young penguin's fancy lightly turns to thoughts of ...
Beta testing!" (Fedora 9 beta announcement)

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

Re: [pgsql-es-ayuda] Manual Postgresql 8.x

Lo ideal sería que leas la documentación oficial, es la más actual y completa al momento, lamentablemente no esta disponible en español.

Saludos

On Sun, Aug 31, 2008 at 3:45 PM, Matias Parodi <mparodilabs@gmail.com> wrote:
Hola! acabo de registrarme en la lista.

Hace algunos días busco un manual reciente de Postgresql en español, todo lo que he conseguido es de versiones anteriores a la 8. Podrían recomendarme o indicarme algo para empezar ? no quiero entrar a leer la documentación oficial sin antes tener una base.

Gracias. Matías.



--

Casiva Agustin

Mail/Msn: casivaagustin@gmail.com
http://www.casivaagustin.com.ar

Re: [SQL] MAY I HAVE YOUR ASSISTANCE

> -----Mensaje original-----
> De: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] En nombre de Gary Chambers
> Enviado el: Lunes, 01 de Septiembre de 2008 11:31
> Para: D'Arcy J.M. Cain
> CC: jameskitambara@yahoo.co.uk; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] MAY I HAVE YOUR ASSISTANCE
>
> >> I have one problem with the user table. I want to hide the
> password for the users.
>
> Here's what I did, which requires using the contrib/pgcrypto
> extension:
>
> CREATE FUNCTION encryptpw() RETURNS TRIGGER AS $encryptpw$ BEGIN
> NEW.password = CRYPT(NEW.password, GEN_SALT('md5'));
> RETURN NEW;
> END;
> $encryptpw$
>
> CREATE TRIGGER trg_encryptpw BEFORE INSERT OR UPDATE ON
> assignees FOR EACH ROW EXECUTE PROCEDURE encryptpw();
>
> Comments, suggestions, criticisms?
>
> -- Gary Chambers
>

The weakness of this solution is that your password might be send in the
clear through the network as the encription ocurrs in the database. I
suggest the encryption be enforced at the application or secure the
connection with ssl.


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

Re: [pgsql-es-ayuda] traduccion documentacion

Alvaro Herrera escribió:
> Calabaza escribió:
>
>
>> Bueno, les cuento lo que hice, empecé a investigar sobre docbook
>> (porque no tengo ni la más pálida idea de que es), me baje algunos
>> tutoriales, agregue unos tantos links a los marcadores, y luego me
>> baje el código fuente de postgres del 8.3.3, para revisar en que
>> formato esta la documentación, que esta en sgml, hasta ahí todo bien,
>> ahora la pregunta es en que versión de docbook esta?
>>
>
> Uff ... y vamos reinventando la rueda *otra vez*.
>
> Les cuento que ya tenemos una plataforma para pasar de docbook a POT, y
> una aplicacion web para apoyar la traduccion de POTs. Incluso tenemos
> bastante gente que aportó a esa traducción. El conjunto está en un
> proyecto en pgfoundry.org/projects/webtranslator (la instalación que
> estaba funcionando antes ya no está al aire, pero confío en que Mario
> tenga un respaldo de la base de datos por ahí)
>
> Lo que nos hace falta es alguien que se haga cargo de administrar y
> mantener eso funcionando.
>
>
Me ofresco como voluntario para mantener...

--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [pgsql-es-ayuda] Linea de Comandos

Hola, Gracias por responder...

Yo veo que tengo el paquete "libreadline.so.5" en   "/lib", pero creo que el postgres no fue compilado con ella pq en la linea de comandos no me funciona...

Sé que tengo que revisar el ./configure (pero no se por donde lo encuentro) y sé que eventualmente tengo que volver a compilar el portgres (pero no se como hacerlo)...  te agradeceria si me puedes dar unas indicaciones...

Mauricio




 

2008/7/25 Alvaro Herrera <alvherre@alvh.no-ip.org>
Mauricio Lopez escribió:
> Mandrake 7

Uhhhhh eso es mas viejo que los calzones que tejía mi abuela ...  ¿No
tienes algo mas moderno?

Debería funcionar.  Imagino que lo estás compilando tú mismo.  En ese
caso lo único que debes asegurarte es que al hacer el "./configure" no
le des el --disable-readline.

A todo esto, ¿qué versión de Postgres estás usando?

--
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"I must say, I am absolutely impressed with what pgsql's implementation of
VALUES allows me to do. It's kind of ridiculous how much "work" goes away in
my code.  Too bad I can't do this at work (Oracle 8/9)."       (Tom Allison)
          http://archives.postgresql.org/pgsql-general/2007-06/msg00016.php



[BUGS] BUG #4389: FATAL: could not reattach to shared memory (key=1804, addr=018E0000): 487

The following bug has been logged online:

Bug reference: 4389
Logged by: could not reattach to shared memory
Email address: diesel_den@ukr.net
PostgreSQL version: 8.3.3-1
Operating system: any 8.3.*
Description: FATAL: could not reattach to shared memory (key=1804,
addr=018E0000): 487
Details:

This error came week ago.
From that 'black' day I can not use Postgre.
I have reinstalled several 8.3.* versions (including last version with
vcredist_x86.exe) and nothing helps me.

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

[JDBC] Table Alias posing problem in Update statements

Hi experts,

I am facing a problem using a Alias on a table while updating that table.

I am trying something like

UPDATE Table1 T1 SET = (SELECT DISTINCT col2 FROM Table2 T2 WHERE
T2.col3 = T1.col1)

T1 and T2 are table aliases.

Here I am trying to update a column in all the rows in table "Table1"
depending on value of another column for the same row.
But the query is not working. It is not allowing alias in the update
query (i.e. alias T1 on table "Table1").

Am I making a mistake somewhere in the syntax? Or, is it just not
supported in postgresql?
I am using postgreSQL version 8.2.

I have observed similar problems with delete statements also.
But these types of queries run successfully on Oracle.

Thanks in advance.
Pushker Chaubey


The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy the original message all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

Please do not print this email unless it is absolutely necessary.

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

Re: [GENERAL] Oracle and Postgresql

On Mon, Sep 01, 2008 at 04:39:09PM +0200, Thomas Kellerer wrote:
> David Fetter, 01.09.2008 16:08:
>>> because we are making extensive usage of Oracle's windowing
>>> functions
>>
>> http://umitanuki.net/pgsql/wfv04/design.html
>
> I knew there was work going on regarding this, but I didn't know how
> definite the decision was to integrate that into 8.4
>
> It's too bad lead(), lag() won't make it

It's not certain they won't make it, only that they're not in the
current patch.

>> It's even more fun with CTEs.
>> <http://wiki.postgresql.org/wiki/CTEReadme>
>
> Recursive CTE are quite cool, and a valid "replacement" for CONNECT
> BY. I've used it already with SQL Server 2005 and once you get the
> idea it's really nice.

It's also standard, where Oracle's syntax is proprietary. In
PostgreSQL, when there's a choice between the two, the standard wins.

>> Those will both be fixed, at least to a large degree, in 8.4 :)
>
> What exactly means "large degree" :)

It means we may not get everything the SQL:2008 standard specifies for
CTEs and windowing functions on the first pass.

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-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgsql-es-ayuda] Consulta función

Ana Smail escribió:
> Tengo un problemita que creo se debe al casteo de tipos , esto:
> select ENCODE(DIGEST('admin', 'md5'),'hex');
> Funciona bien en la versión 8.2, pero ya no funciona en la 8.3 y no puedo
> corregir el problema.

Mi bola de cristal dice: La funcion digest() no está instalada. Instala
el modulo pgcrypto.

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"El número de instalaciones de UNIX se ha elevado a 10,
y se espera que este número aumente" (UPM, 1972)
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [pgsql-es-ayuda] Consulta función



On Mon, Sep 1, 2008 at 9:45 AM, Ana Smail <anasmail@unnoba.edu.ar> wrote:
Tengo un problemita que creo se debe al casteo de tipos , esto:
select ENCODE(DIGEST('admin', 'md5'),'hex');
Funciona bien en la versión 8.2, pero ya no funciona en la 8.3 y no puedo
corregir el problema.

ANA

--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Pues a mi me funciono bien desde PgAdmin, tengo la 8.3.3.

--
Joseph Cesar Mena S.
+51 (1) 991526548
L1nuX R3g1$t3r3d U53r #412302
.~.
( 0 0 )
/ V \
// \\
/(( _ ))\

[pgsql-es-ayuda] Consulta función

Tengo un problemita que creo se debe al casteo de tipos , esto:
select ENCODE(DIGEST('admin', 'md5'),'hex');
Funciona bien en la versión 8.2, pero ya no funciona en la 8.3 y no puedo
corregir el problema.

ANA

--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [PERFORM] limit clause breaks query planner?

Thanks for your suggestion but the result is the same.

Here is the explain analyse output from different queries.
Select * from my_table where A is null and B = '21' limit 15

"Limit (cost=0.00..3.68 rows=15 width=128) (actual time=85837.043..85896.140 rows=15 loops=1)"
" -> Seq Scan on my_table this_ (cost=0.00..258789.88 rows=1055580 width=128) (actual time=85837.038..85896.091 rows=15 loops=1)"
" Filter: ((A IS NULL) AND ((B)::text = '21'::text))"
"Total runtime: 85896.214 ms"

As you can see the estimated cost was 3.68: a long way from the true value.

Doing 'set enable_seqscan=false' and repeating the select:
"Limit (cost=0.00..5.58 rows=15 width=128) (actual time=4426.438..4426.834 rows=15 loops=1)"
" -> Index Scan using idx_A on my_table this_ (cost=0.00..392956.76 rows=1055970 width=128) (actual time=4426.433..4426.768 rows=15 loops=1)"
" Index Cond: (A IS NULL)"
" Filter: ((B)::text = '21'::text)"
"Total runtime: 4426.910 ms"

Probably some caching made this query faster, but it's still too slow, and using the wrong index.

Deleting index A gives:
"Limit (cost=0.00..56.47 rows=15 width=128) (actual time=10.298..10.668 rows=15 loops=1)"
" -> Index Scan using idx_B on my_table this_ (cost=0.00..3982709.15 rows=1057960 width=128) (actual time=10.293..10.618 rows=15 loops=1)"
" Index Cond: ((B)::text = '21'::text)"
" Filter: (A IS NULL)"
"Total runtime: 10.735 ms"
Much better. However I need index A for another query so I can't just delete it.

Looking at the estimated cost, you can see why it's choosing the order that it is choosing, but it just doesn't seem to reflect reality at all.

Now here's the result of the query, with both indexes in place and sequential scan enabled
Select * from my_table where A is null and B = '21'
"Bitmap Heap Scan on my_table this_ (cost=20412.89..199754.37 rows=1060529 width=128) (actual time=470.772..7432.062 rows=1020062 loops=1)"
" Recheck Cond: ((B)::text = '21'::text)"
" Filter: (A IS NULL)"
" -> Bitmap Index Scan on idx_B (cost=0.00..20147.76 rows=1089958 width=0) (actual time=466.545..466.545 rows=1020084 loops=1)"
" Index Cond: ((B)::text = '21'::text)"
"Total runtime: 8940.119 ms"

In this case it goes for the correct index. It appears that the query planner makes very simplistic assumptions when it comes to LIMIT?

Thanks
David

-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: 01 September 2008 13:53
To: David West
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] limit clause breaks query planner?

Hello

you should partial index

create index foo(b) on mytable where a is null;

regards
Pavel Stehule

2008/9/1 David West <david.west@cusppoint.com>:
> Hi,
>
>
>
> I have a single table with about 10 million rows, and two indexes. Index A
> is on a column A with 95% null values. Index B is on a column B with about
> 10 values, ie. About a million rows of each value.
>
>
>
> When I do a simple query on the table (no joins) with the following
> condition:
>
> A is null AND
>
> B = '21'
>
>
>
> it uses the correct index, index B. However, when I add a limit clause of
> 15, postgres decides to do a sequential scan :s. Looking at the results
> from explain:
>
>
>
> "Limit (cost=0.00..3.69 rows=15 width=128)"
>
> " -> Seq Scan on my_table this_ (cost=0.00..252424.24 rows=1025157
> width=128)"
>
> " Filter: ((A IS NULL) AND ((B)::text = '21'::text))"
>
>
>
> It appears that postgres is (very incorrectly) assuming that it will only
> have to retrieve 15 rows on a sequential scan, and gives a total cost of
> 3.69. In reality, it has to scan many rows forward until it finds the
> correct value, yielding very poor performance for my table.
>
>
>
> If I disable sequential scan (set enable_seqscan=false) it then incorrectly
> uses the index A that has 95% null values: it seems to incorrectly apply the
> same logic again that it will only have to retrieve 15 rows with the limit
> clause, and thinks that the index scan using A is faster than index scan B.
>
>
>
> Only by deleting the index on A and disabling sequential scan will it use
> the correct index, which is of course by far the fastest.
>
>
>
> Is there an assumption in the planner that a limit of 15 will mean that
> postgres will only have to read 15 rows? If so is this a bad assumption?
> If a particular query is faster without a limit, then surely it will also be
> faster with the limit.
>
>
>
> Any workarounds for this?
>
>
>
> Thanks
>
> David


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

Re: [GENERAL] Oracle and Postgresql

David Fetter, 01.09.2008 16:08:
>> because we are making extensive usage of Oracle's windowing
>> functions
>
> http://umitanuki.net/pgsql/wfv04/design.html

I knew there was work going on regarding this, but I didn't know how definite the decision was to integrate that into 8.4

It's too bad lead(), lag() won't make it

> It's even more fun with CTEs.
> <http://wiki.postgresql.org/wiki/CTEReadme>

Recursive CTE are quite cool, and a valid "replacement" for CONNECT BY.
I've used it already with SQL Server 2005 and once you get the idea it's really nice.

> Those will both be fixed, at least to a large degree, in 8.4 :)

What exactly means "large degree" :)


Regards
Thomas


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

Re: [pgsql-es-ayuda] Linea de Comandos Postgres

Mauricio Lopez escribió:
> Hola...
>
> Alguien me puede decir de donde se puede descargar la libreria libreadline,
> para poder usar el autocompletar en la linea de comandos de postgres...
>
> Uso Linux Mandriva

urpmi readline5-devel

(o algo por el estilo ... la version seguramente es distinta, y el
nombre exacto del paquete. Pero lo importante es que tienes que usar la
herramienta de paquetes de la distribucion)

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"A wizard is never late, Frodo Baggins, nor is he early.
He arrives precisely when he means to." (Gandalf, en LoTR FoTR)
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

Re: [PERFORM] too many clog files

Duan Ligong wrote:
> Hi, there.
>
> I have encountered an issue that there are too many
> clog file under the .../pg_clog/ directory. Some of them
> were even produced one month ago.

If you're going to repost a question, it is only polite that you link to
the answers already provided. Particularly so when some of your
questions were already answered.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

[pgsql-es-ayuda] Linea de Comandos Postgres

Hola...

Alguien me puede decir de donde se puede descargar la libreria libreadline, para poder usar el autocompletar en la linea de comandos de postgres...

Uso Linux Mandriva

Gracias

 Mauricio López Benítez

[GENERAL] refcursor

 
Hello all,
I've got a problem I can't get round. I want to declare a refcursor variable in a  C program and call a function which returns a refcursor portal to the main C program.
When I compile the C program, I get a refcursor doesn´t exist message. Has anyone got a  C program which uses a refcursor variable?
Hope you can help.
 
Cheers
Mike Stanton, Santiago Chile
 
 
 
 
Michael Stanton W.
Depto. Informática

Re: [SQL] MAY I HAVE YOUR ASSISTANCE

>> I have one problem with the user table. I want to hide the password for the users.

Here's what I did, which requires using the contrib/pgcrypto extension:

CREATE FUNCTION encryptpw() RETURNS TRIGGER AS
$encryptpw$
BEGIN
NEW.password = CRYPT(NEW.password, GEN_SALT('md5'));
RETURN NEW;
END;
$encryptpw$

CREATE TRIGGER trg_encryptpw BEFORE INSERT OR UPDATE ON assignees
FOR EACH ROW EXECUTE PROCEDURE encryptpw();

Comments, suggestions, criticisms?

-- Gary Chambers

/* Nothing fancy and nothing Microsoft! */

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

Re: [GENERAL] Oracle and Postgresql

On Mon, Sep 01, 2008 at 10:31:25AM +0200, Thomas Kellerer wrote:
> Scott Marlowe, 31.08.2008 22:44:
>> I think some of it is inertia. We've always used Oracle, let's
>> just keep on using it. The more conservative the IT department is,
>> the less likely they are to take chances with new technology.
>>
>> It used to be there was about an 80/20 split between what things
>> you could do with either postgresql or oracle, and the other 20%
>> was oracle only land. I think that number is dropping quickly, and
>> we're into the 1 or 2% club of what Oracle can do that PostgreSQL
>> isn't fast enough for.
>
> For me (personally) the ratio is more like 70/30,

It's about to increase :)

> because we are making extensive usage of Oracle's windowing
> functions

http://umitanuki.net/pgsql/wfv04/design.html

> (and ability to easily deal with hierarchical data using CONNECT BY).

It's even more fun with CTEs.
<http://wiki.postgresql.org/wiki/CTEReadme>

> The lack of windowing functions is a bit frustrating as I'm
> otherwise a big Postgres fan!

Those will both be fixed, at least to a large degree, in 8.4 :)

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-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SQL] MAY I HAVE YOUR ASSISTANCE

On Mon, 1 Sep 2008 07:03:51 +0000 (GMT)
James Kitambara <jameskitambara@yahoo.co.uk> wrote:
>  
> Please members of the PGSQL-SQL,
>  
> I have one problem with the user table. I want to hide the password for the users.

Look in the contrib directory for the chkpass type. It uses DES
encryption but if you need MD5 or Blowfish it wouldn't be very hard to
modify the code.

--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

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

Re: [pgsql-es-ayuda] traduccion documentacion

Calabaza escribió:

> Bueno, les cuento lo que hice, empecé a investigar sobre docbook
> (porque no tengo ni la más pálida idea de que es), me baje algunos
> tutoriales, agregue unos tantos links a los marcadores, y luego me
> baje el código fuente de postgres del 8.3.3, para revisar en que
> formato esta la documentación, que esta en sgml, hasta ahí todo bien,
> ahora la pregunta es en que versión de docbook esta?

Uff ... y vamos reinventando la rueda *otra vez*.

Les cuento que ya tenemos una plataforma para pasar de docbook a POT, y
una aplicacion web para apoyar la traduccion de POTs. Incluso tenemos
bastante gente que aportó a esa traducción. El conjunto está en un
proyecto en pgfoundry.org/projects/webtranslator (la instalación que
estaba funcionando antes ya no está al aire, pero confío en que Mario
tenga un respaldo de la base de datos por ahí)

Lo que nos hace falta es alguien que se haga cargo de administrar y
mantener eso funcionando.

--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"It takes less than 2 seconds to get to 78% complete; that's a good sign.
A few seconds later it's at 90%, but it seems to have stuck there. Did
somebody make percentages logarithmic while I wasn't looking?"
http://smylers.hates-software.com/2005/09/08/1995c749.html
--
TIP 5: ¿Has leído nuestro extenso FAQ?
http://www.postgresql.org/docs/faqs.FAQ.html

Re: [pgsql-es-ayuda] Consulta sobre rules

>
>
>Hola todos!
>Mi consulta es conceptual, para que usan las rules en postgresql, puedo
>implementar triggers con rules; cuál es la diferencia entra ambos?
>
>Gracias por su ayuda
>

Los triggers se ejecutan una acción antes o despues de una operación con la
DB. Las reglas MODIFICAN el plan de ejecucción original para incluir la
regla que definiste. Por ejemplo si realizas un update que va a modificar
1000 registros, el trigger se ejecuta por cada uno de esos 1000 registros.
En cambio la regla se va a ejecutar una única vez (con un plan modificado).

Atentamente,

RAUL DUQUE
Bogotá, Colombia

>ANA
>
>--
>TIP 2: puedes desuscribirte de todas las listas simultáneamente
> (envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [BUGS] Bug in RETURN QUERY

Hello

2008/9/1 Oleg Serov <serovov@gmail.com>:
> Hello all SQL BUG CODE:
> BEGIN;
> SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by
> GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
> CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );
>
> CREATE TABLE "bug_table" (
> "id" BIGINT NOT NULL,
> "buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first NOT
> NULL,
> CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
>
> CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
> AS $$
> BEGIN
> -- @todo hide password
> RETURN QUERY (
> SELECT *
> FROM bug_table
> );
> END;
> $$
> LANGUAGE plpgsql STRICT SECURITY DEFINER;
>
> SELECT * FROM buggy_procedure(); -- All Okey
> DROP TYPE buggy_enum_first CASCADE;
> CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
> ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
> SELECT * FROM buggy_procedure(); -- Bug
> ROLLBACK;
> /*NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "test_table_pkey" for table "bug_table"
>
> NOTICE: drop cascades to default for table bug_table column
> buggy_enum_fieldNOTICE: drop cascades to table bug_table column
> buggy_enum_field
> ERROR: structure of query does not match function result type
> CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/

this isn't bug, it's feature. Informations stored in execution plan
are broken, when some references to removed objects are stored in plan
- objects aren't stored by name, but by object id. So, when you drop
any object, then you have to finish session. Note: actually only drop
of table emits plan cache invalidation signal.

Regards
Pavel Stehule

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

Re: [NOVICE] Max OID

See below.
I'm at a loss here.
I've checked the lo_import / lo_export and I think that I am doing all this correctly.

The original import uses the server side lo_import in an sql statement. The large object appears to have been created ok and the oid value inserted into the document field below. All good so far. The users could then get the large object out - this used a call to psql and a \lo_export to get that OID referred to by the document field. This has been working fine for 2-3 years.
On saturday the live server crashed and was replaced with the backup server. The live data was pg_dump'd (from an 8.0.3 database) prior to the crash and pg_restored (into 8.0.8) onto the backup server. Again all seemed well - no errors reported into the restore and the data looked good. BUT the document field now contains values of >2billion. Is it possible that the dump or the restore 'altered' not only the document field, but also the actual OID of the large object? The data all looks good, in as much as say the document field points to large object 3123456789 and large object oid 3123456789 is correct (ie it does tie up to that record). The problem being is that the program that handles the exporting of the object has never handled OID's bigger than 2billion - so the document field and its related large object would have to hve been <=2billion prior to the dump/restore. Does that make sense and is that feasible?

I have also restored the data into an 8.1.10 database and I see the same results, ie the document field contains values only >3billion - but they do seem to correctly correlate to the large objects as retrieved by \lo_export.

On Mon, 2008-09-01 at 11:01 +0100, Steve T wrote:
I have a table that is used to control documents:
recno                | integer                | not null default nextval('document_recno_seq'::regclass)
foreigntablename     | character varying(255) | not null
foreignrecno         | integer                | not null
docforeigntablename  | character varying(255) | not null
docforeignrecno      | integer                | not null
docforeigntext       | character varying(255) | not null
documenttyperecno    | integer                | not null
version              | character varying(20)  | not null
loadeddate           | date                   | not null
loadedtime           | time without time zone | not null
usersrecno           | integer                | not null
title                | character varying(40)  | not null
description          | character varying(255) |
doculookupcodesrecno | integer                | not null
document             | oid                    | not null
suffix               | character varying(255) |

Each document is loaded using the large object commands and the OID of the load then inserted as a ref number on the document table.
This has been working fine, but I had a major crash on the server and it had to be rebuilt.
The database had been exported  (pg_dump) - so I reimported (pg_restore) the database.
If I now look at the oid (ie document.document) of the blob on the numbers are in the range:
3159553408
3159553409
3159553410
3159553411
3159553412
3159553413
3159553414
3159553415
these numbers are above the field I use for the OID in the code (ie an Integer - catering for 2 billion) - so the program no longer finds the document to export.
Any ideas as to why did these OID's become so large after reimporting (I am assuming here that they must have been under 2 billion before or else the process could not have worked before)? And what limit is ther then on this OID?

[PHP] pg_query_params and SQL injection

Hi all
I am just doing some playing around with PHP to learn how to avoid SQL injection attacks.
It has been mentioned in a few places that pg_query_params is supposed to protect from sql injection without needing to mess around escaping quotes and things.

However, I was still able to get it to drop a table by feeding in this input "1; drop table results" to the following statement:
$r = pg_query_params($p, 'select * from results where res_id = $1', array($input));

Everyone keeps repeating the same "pg_query_params is safe from SQL injection", but surely someone else must have actually tried it? Where am I going wrong?

I am using Postgresql 8.3 for OS X on 10.5.2, and MAMP which has PHP Version 5.2.5.

Thanks
Kevin


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

Re: [HACKERS] Extending grant insert on tables to sequences

* Jaime Casanova (jcasanov@systemguards.com.ec) wrote:
> On Fri, Aug 22, 2008 at 10:19 PM, Bruce Momjian <bruce@momjian.us> wrote:
> >
> > Added to September commit fest.
> >
>
> updating the patch with one that only extends inserts. though, i
> haven't look at the col level privs patch yet.

At least initially I wasn't planning to support column-level privileges
for sequences, so I don't think it will affect you much. Do people
think it makes sense to try and support that?

As your patch appears more ready-for-commit than the column-level
privileges patch, I wouldn't worry about what code might have to move
around, that'll be for me to deal with in a re-sync with HEAD once your
patch is committed.

Thanks,

Stephen

Re: [GENERAL] ERROR: relation . . . does not exist

Albretch Mueller wrote:
>> PostgreSQL has 60+ types and many look like eachother. How do you propose to differentiate?
> ~
> Data Types are basically about value ranges (how many bits do you
> need to hold the value) and formatting.

That is exactly wrong, at least in the PostgreSQL approach to the type
system. Data types are about functions and operators associated with
them. So it is not about what the data looks like, but what kind of
processing you want to do with it. Guessing that information from a
dump of data seems pretty much impossible.


--
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] determining existence of database and language

Chris Sano wrote:
> I'm trying to build a SQL script that will create a database if it
> doesn't already exist. I've looked everywhere and haven't been able to
> find anything. Am I missing something? Thanks.

SELECT * FROM pg_database will tell you what databases exist.


--
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] SQL optimization - WHERE SomeField STARTING WITH ...

Bill wrote:
> The SQL database servers I have worked with cannot use and index for a
> SELECT of the form
>
> SELECT * FROM ATABLE
> WHERE AFIELD LIKE ?
>
> because there is no way to know the location of the wild card until the
> parameter value is known. InterBase and Firebird allow
>
> SELECT * FROM ATABLE
> WHERE AFIELD STARTING WITH ?
>
> which is equivalent to LIKE 'ABC%' and will use an index on AFIELD. Is
> there a similar syntax in PostgreSQL?

One workaround might be to rewrite the query using some string function
(substring, position, or write your own) and index over that 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: [HACKERS] [PATCH] Cleanup of GUC units code

diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 4ca25dc..b10be22 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -4182,7 +4182,7 @@ parse_int(const char *value, int *result, int flags, const char **hintmsg)
#error XLOG_BLCKSZ must be between 1KB and 1MB
#endif

- if (strncmp(endptr, "kB", 2) == 0)
+ if (pg_strncasecmp(endptr, "kB", 2) == 0)
{
endptr += 2;
switch (flags & GUC_UNIT_MEMORY)
@@ -4195,7 +4195,7 @@ parse_int(const char *value, int *result, int flags, const char **hintmsg)
break;
}
}
- else if (strncmp(endptr, "MB", 2) == 0)
+ else if (pg_strncasecmp(endptr, "MB", 2) == 0)
{
endptr += 2;
switch (flags & GUC_UNIT_MEMORY)
@@ -4211,7 +4211,7 @@ parse_int(const char *value, int *result, int flags, const char **hintmsg)
break;
}
}
- else if (strncmp(endptr, "GB", 2) == 0)
+ else if (pg_strncasecmp(endptr, "GB", 2) == 0)
{
endptr += 2;
switch (flags & GUC_UNIT_MEMORY)
@@ -4234,7 +4234,7 @@ parse_int(const char *value, int *result, int flags, const char **hintmsg)
if (hintmsg)
*hintmsg = gettext_noop("Valid units for this parameter are \"ms\", \"s\", \"min\", \"h\", and \"d\".");

- if (strncmp(endptr, "ms", 2) == 0)
+ if (pg_strncasecmp(endptr, "ms", 2) == 0)
{
endptr += 2;
switch (flags & GUC_UNIT_TIME)
@@ -4247,7 +4247,7 @@ parse_int(const char *value, int *result, int flags, const char **hintmsg)
break;
}
}
- else if (strncmp(endptr, "s", 1) == 0)
+ else if (pg_strncasecmp(endptr, "s", 1) == 0)
{
endptr += 1;
switch (flags & GUC_UNIT_TIME)
@@ -4260,7 +4260,7 @@ parse_int(const char *value, int *result, int flags, const char **hintmsg)
break;
}
}
- else if (strncmp(endptr, "min", 3) == 0)
+ else if (pg_strncasecmp(endptr, "min", 3) == 0)
{
endptr += 3;
switch (flags & GUC_UNIT_TIME)
@@ -4273,7 +4273,7 @@ parse_int(const char *value, int *result, int flags, const char **hintmsg)
break;
}
}
- else if (strncmp(endptr, "h", 1) == 0)
+ else if (pg_strncasecmp(endptr, "h", 1) == 0)
{
endptr += 1;
switch (flags & GUC_UNIT_TIME)
@@ -4289,7 +4289,7 @@ parse_int(const char *value, int *result, int flags, const char **hintmsg)
break;
}
}
- else if (strncmp(endptr, "d", 1) == 0)
+ else if (pg_strncasecmp(endptr, "d", 1) == 0)
{
endptr += 1;
switch (flags & GUC_UNIT_TIME)
On 9/1/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Marko Kreen <markokr@gmail.com> writes:
> > - In attempt to preserve maximum range of values for INT64_IS_BUSTED
> > systems, the code is written rather non-obvious way.
>
> I do not personally object a bit to making the units comparisons
> case-insensitive (I think it's mainly Peter who wants to be strict
> about it). I don't think there are any other good ideas in this
> patch, however, and exposing ourselves to intermediate overflows in
> the name of simplicity is definitely not one.

For all practical purposes, the overflow is insignificant when int64
works. I'll look if I can avoid it on INT64_IS_BUSTED case.

In the meantime, here is simple patch for case-insensivity.

--
marko

Re: [GENERAL] indexes on functions and create or replace function

Matthew Dennis wrote:
> Given table T(c1 int) and function F(arg int) create an index on T using
> F(c1). It appears that if you execute "create or replace function F"
> and provide a different implementation that the index still contains the
> results from the original implementation, thus if you execute something
> like "select * from T where F(c1)" after replacing the function that it
> now misses rows that should be returned. In other words, the index
> isn't aware the function is now returning different values. That's not
> the correct/expected behavior is it? I would have expected that
> replacing the function would have caused any indexes that depend on that
> function to be reindexed/recreated with the new function implementation.

I have added a Todo item about this. But as you see, the problem is
quite large and involved.

--
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] [PATCH] Make gram.y use palloc/pfree for memory management

First a correction, overriding malloc/free seems dangerous they
seems to leak out, so correct would be to use YYMALLOC/YYFREE.
This leaves 1.875 potentially leaking, but danger seems small.

On 9/1/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Marko Kreen <markokr@gmail.com> writes:
> > This means gram.y can leak memory if error is throws in
> > the middle of parsing.
>
> Please offer some evidence for that claim.

The leak occurs when
1. bison does allocation.
2. error is thrown.

Now, normally bison does not do allocation as it has initially 200-item
stack allocated in stack. When this is full it does allocate.

But I'm not familial enough with bison internals and Postgres parser
structure, on how cause the stack fill up. It may be that Postgres
parser avoids recursive stack allocations, thus in practice the
leak cannot occur.

--
marko

--
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] Consulta sobre rules

Hola todos!
Mi consulta es conceptual, para que usan las rules en postgresql, puedo
implementar triggers con rules; cuál es la diferencia entra ambos?

Gracias por su ayuda

ANA

--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [BUGS] libpq does not manage SSL callbacks properly when other libraries are involved.

Russell Smith wrote:
> Alvaro Herrera wrote:
> > PoolSnoopy wrote:
> >
> >> this bug is really some annoyance if you use automatic build environments.
> >> I'm using phpunit to run tests and as soon as postgres is involved the php
> >> cli environment segfaults at the end. this can be worked around by disabling
> >> ssl but it would be great if the underlying bug got fixed.
> >>
> >
> > This is PHP's bug, isn't it? Why are you complaining here
> No, this is a problem with the callback/exit functions used by
> PostgreSQL. We setup callback functions when we use SSL, if somebody
> else uses SSL we can create a problem.

Ok, so it seems you're correct; there is more evidence to be found by
searching other projects' mailing lists, for example as a starting point
http://markmail.org/search/?q=+CRYPTO_set_locking_callback%28NULL%29

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [PERFORM] Best hardware/cost tradoff?

> -----Mensaje original-----
> De: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] En nombre de cluster
> Enviado el: Sábado, 30 de Agosto de 2008 07:21
> Para: pgsql-performance@postgresql.org
> Asunto: Re: [PERFORM] Best hardware/cost tradoff?
>
> We are now leaning towards just buying 4 SAS disks.
>
> So should I just make one large RAID-10 partition or make two
> RAID-1's having the log on one RAID and everything else on
> the second RAID?
> How can I get the best read/write performance out of these four disks?
> (Remember, that it is a combined web-/database server).
>

Make a single RAID 10. It´s simpler and it will provide you better write
performance which is where your bottleneck will be. I think you should
minimize the web server role in this equation as it should mostly work on
cached data.


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

[BUGS] Bug in RETURN QUERY

Hello all SQL BUG CODE:
BEGIN;
SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );

CREATE TABLE "bug_table" (
  "id" BIGINT NOT NULL,
  "buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first NOT NULL,
  CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;


CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
    AS $$
BEGIN
    -- @todo hide password
    RETURN QUERY (
        SELECT *
        FROM bug_table
    );
END;
$$
    LANGUAGE plpgsql STRICT SECURITY DEFINER;

SELECT * FROM buggy_procedure(); -- All Okey
DROP TYPE buggy_enum_first CASCADE;
CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
SELECT * FROM buggy_procedure(); -- Bug
ROLLBACK;
/*NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_table_pkey" for table "bug_table"

NOTICE:  drop cascades to default for table bug_table column buggy_enum_fieldNOTICE:  drop cascades to table bug_table column buggy_enum_field
ERROR:  structure of query does not match function result type
CONTEXT:  PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/

Re: [HACKERS] Is this really really as designed or defined in some standard

On Mon, 2008-09-01 at 11:15 +0200, Pavel Stehule wrote:
> 2008/9/1 Magnus Hagander <magnus@hagander.net>:
> > Pavel Stehule wrote:
> >> Hello
> >>
> >> 2008/8/31 Hannu Krosing <hannu@2ndquadrant.com>:

> >>>
> >>> hannu=# create or replace function ffa(a int, a int) returns int
> >>> language plpgsql as $$begin return a + a; end;$$;
> >>> CREATE FUNCTION
> >>> hannu=# select ffa(1,2);
> >>> ffa
> >>> -----
> >>> 2
> >>> (1 row)
> >>>
> >>> Is this defined by some standard or just an oversight ?
> >>>
> >>
> >> what is problem? You have two diferent functions. I don't see anything wrong.
> >
> > Take a look at the second function again. It's certainly not behaviour
> > that I would expect :-) (I would expect a syntax error)
>
> I see it now - it's really bug

There are a few places, where repeating labels are allowed, for example
select can produce such record

hannu=# select 1 as a, 2 as a;
a | a
---+---
1 | 2
(1 row)

But it is not allowed in TYPE or table definitions

hannu=# create type aa as (a int, a int);
ERROR: column "a" specified more than once

hannu=# create table aa (a int, a int);
ERROR: column "a" specified more than once

It probably is also not allowed in function/procedure argument list, but
I was not sure that any standard would not require it.

So, should this be fixed at calling / SQL side (by not allowing
repeating argument names) or at pl side for each pl separately ?

--------------
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: [pgsql-es-ayuda] traduccion documentacion

> Bueno, les cuento lo que hice, empecé a investigar sobre docbook
> (porque no tengo ni la más pálida idea de que es), me baje algunos
> tutoriales, agregue unos tantos links a los marcadores, y luego me
> baje el código fuente de postgres del 8.3.3, para revisar en que
> formato esta la documentación, que esta en sgml, hasta ahí todo bien,
> ahora la pregunta es en que versión de docbook esta?
> urgando por el archivo: reference.ced
> en una parte dice:
> (sgml-saved-dtd-version 7)
> ("/usr/share/sgml/docbook/sgml-dtd-3.1/dbgenent.mod"
>
> Según las especificaciones de docbook.org[2] va por la version 5 así
> que no entiendo porque el dtd es version 7, pero bueno, luego veo que
> abajo dice smgl-dtd-3.1 o sea version del dtd 3.1 y en qué quedamos?
> [2] http://www.docbook.org/specs/
>
> Realmente puedo empezar a pelearme con los archivos sgml pero creo que
> alguno que haya trabajado con estos documentos puede tener alguna
> herramienta que haga más fácil la vida?
>
> Y luego también esta el problema de quién hace qué...
>
> Vi algunos editores que interpretan los códigos del dtd para hacerlo
> más visual, de todos modos creo que casi ninguna etiqueta voy a
> modificar, pero bueno, alguien me da alguna iluminación sobre que
> herramienta utilizar para editar los sgml?
>
> Mi SO es un WinXP, posiblemente lo haga con pspad y su soporte de xml,
> pero preferiría algo más visual, ah! he visto que se puede hacer
> también con openoffice, mientras tanto seguiré investigando.
>

Hasta donde he leido no hay una herramienta WYSWYG para docbook. Pensaba que
se seguía utilizando Latex para documentación técnica :-(

Encontré este link que habla de cómo utilizar docbook sobre windows, pero
parece que para la edición lo máximo que vas a tener es un buen editor XML.

http://www.codeproject.com/KB/winhelp/docbook_howto.aspx

Atetamente,

RAUL DUQUE
Bogotá, Colombia


> Al que le interese aquí hay tutoriales para mirar:
> http://wiki.docbook.org/topic/DocBookTutorials#head-b6bf09ce90154dbda12d6c4e7a2d856c461baafa
>
> Hasta mañana amigos, revisaré el correo por la noche, así que no se
> molesten conmigo si no contesto rápido, :)
>
> Un abrazo.
> --
> §~^Calabaza^~§ from Villa Elisa, Paraguay
> --
> TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo
> agradecerán
>
> __________ Information from ESET Smart Security, version of virus
> signature database 3401 (20080829) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán