PostgreSQL

Re: [GENERAL] Why Does UPDATE Take So Long?

2008-09-30T13:51:00.000-07:00

Doesn't look like that's the problem. I moved my table over to another
Linux box running PG 8.3 and update performance was pretty bad there as
well. In the time that PG 8.3 was struggling with update there I created
a copy of my table on my PG 8.1 machine and inserted all columns with
one containing the altered values I wanted and that took less than two
minutes. Meanwhile, a half-hour later, my PG 8.3 machine was still
thrashing away trying to update that one column that's not even part of
any index..

Something is really wrong with UPDATE in PostgreSQL I think.

Scott Marlowe wrote:
> On Tue, Sep 30, 2008 at 1:00 PM, Bill Thoen <bthoen@gisnet.com> wrote:
>
>> Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a
>> very long time; 15 minutes so far and no end in sight. From the explain, it
>> doesn't seem like it should take that long, and this column is not indexed.
>> Sure, there's 2.7 million records but it only takes a few minutes to scan
>> the whole file. Is there some special overhead I should be aware of with an
>> UPDATE? I VACUUMed and ANALYZEd first, too.
>>
>> Or am I just expecting too much?
>>
>
> The problem is that on older versions of pgsql, the db had to update
> each index for each row updated as well as the rows. The latest
> version, with a low enough fill factor, can update non-indedexed
> fields by using the free space in each page and not have to hit the
> indexes. But on 8.1 you don't get that optimization.
>
>

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

[lapug] Attn:LAPUGers - Greenplum requesting Topics For Discussion

2008-09-30T13:43:00.001-07:00

On Tue, Sep 30, 2008 at 1:21 PM, Rui Maximo <rmaximo@greenplum.com> wrote:

> As we get closer to that date, could you share logistics (address
> and time)?

Rui,

A reservation was created for 11/19/08 at 7pm, in:

Steele 214 in Caltech.
370 South Holliston Avenue,
Pasadena, CA 91106

Google map:
http://maps.google.com/maps/ms?ie=UTF8&t=h&hl=en&msa=0&ll=34.139186,-118.123265&spn=0.001592,0.002403&z=19&msid=114588631836793408373.00045822d679f33626b04

Caltech map:
http://www.caltech.edu/map/Caltech-map-2007-11-14.pdf

LAPUGers,

> Since I have a rare opportunity to be able to connect directly with your
> audience beforehand, I would encourage you to please voice what you would
> like to hear about Greenplum. This will help me come as prepared to answer
> your questions.
>
> Thank you,
> Rui Maximo
> Senior Product Manager
> Greenplum Inc.

Please reply to Rui regarding any Data warehouse, greenplum, OLAP
question that you might like him to address.

--
Regards,
Richard Broersma Jr.

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

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

Re: [HACKERS] Block-level CRC checks

2008-09-30T13:41:00.000-07:00

Maybe a stupid question, but what I/O subsystems corrupt data and fail
to report it?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

--
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-www] Finding some bug statistics..

2008-09-30T13:36:00.001-07:00

Chander Ganesan wrote:
> Magnus Hagander wrote:
> >
> > We don't have a bug tracker, thus there is nothing to gather statistics
> > from. We have a web form that is really just a sequence in a database
> > that generates a bug id, and then remails the whole form to pgsql-bugs.
> >
> > For discussions of why we don't have one, see about a billion mails in
> > the archives over the past 10 years or so :-(
> >
> The general policy is a 72 hour bug fix, right? Do we have any
> ideas/numbers as to the currently "open" number of bugs (identified but
> not fixed yet)? That would be just as useful in some regard.

As far as I am concerned, the TODO list has every known bug against CVS
HEAD, with lots of feature requests in there too.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

[pgsql-es-ayuda] RE: [pgsql-es-ayuda] Validación de CUIT - Argentina

2008-09-30T13:36:00.000-07:00

Bueno gente he encontrado un código hecho en Oracle y lo adapte a plpsql

Les dejo el codigo de ejemplo:
----------------------------------------------------------------------------
--------------------
CREATE OR REPLACE FUNCTION "public"."validar_cuit" (bigint) RETURNS varchar
AS
$body$
DECLARE
RES BIGINT;
DIG BIGINT;
NUM BIGINT;
CUIT ALIAS FOR $1;

BEGIN
IF LENGTH(CUIT) != 11 OR SUBSTR(CUIT, 1, 2) = '00' THEN
RETURN 0;
END IF;
RES = 0;
FOR I IN 1..10 LOOP
NUM := (SUBSTR(CUIT, I, 1));
IF (I = 1 OR I = 7) THEN RES := RES + NUM * 5;
ELSIF (I = 2 OR I = 8) THEN RES := RES + NUM * 4;
ELSIF (I = 3 OR I = 9) THEN RES := RES + NUM * 3;
ELSIF (I = 4 OR I = 10) THEN RES := RES + NUM * 2;
ELSIF (I = 5) THEN RES := RES + NUM * 7;
ELSIF (I = 6) THEN RES := RES + NUM * 6;
END IF;
END LOOP;
DIG := 11 - MOD(RES,11);
IF DIG = 11 THEN
DIG := 0;
END IF;

IF DIG = (SUBSTR(CUIT,11,1)) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

COMMENT ON FUNCTION "public"."validar_cuit"(bigint)
IS 'Valida el CUIT segun formato 99999999999
Devuelve 0 para los CUIT No Válidos
Devuelve 1 para los CUIT Válidos';
----------------------------------------------------------------------------
--------------------

-----Mensaje original-----
De: pgsql-es-ayuda-owner@postgresql.org
[mailto:pgsql-es-ayuda-owner@postgresql.org] En nombre de Damian Culotta
Enviado el: Martes, 30 de Septiembre de 2008 05:28 p.m.
Para: pgsql-es-ayuda@postgresql.org
Asunto: Re: [pgsql-es-ayuda] Validación de CUIT - Argentina

2008/9/30 Daniel Ferrer <daniel.ferrer@ctd.com.ar>:
> Buenas Tardes a todos:
> Alguien tiene armada alguna función para validar el
> CUIT (Clave Única de Identificación Tributaria) de Argentina.
>
> Desde ya les agradezco.
>
> Atte.
> ___________________________
> ASC Daniel Ferrer
> Gerente de Sistemas - CTD SRL
> Rosario - Argentina
> mailto:daniel.ferrer@ctd.com.ar
> ___________________________
>
> --
> TIP 7: no olvides aumentar la configuración del "free space map"
>

Salvo que se haya actualizado el asunto, hasta donde recuerdo, no está
definida formalmente la función para calcular la validez de un CUIT.
Si bien hay funciones dando vueltas (yo no tengo encima ninguna en
este momento) que servirían para hacerlo, no son "oficiles".
Ojalá me esté equivocando y alquien pueda darte la referncia a un
instructivo de la AFIP.
Saludos.
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
http://archives.postgresql.org/pgsql-es-ayuda

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

Re: [pgsql-es-ayuda] Validación de CUIT - Argentina

2008-09-30T13:27:00.000-07:00

Re: [pgsql-es-ayuda] wxwidgets libpq error de codificacion de caracteres

2008-09-30T13:25:00.000-07:00

Podrias pegar la salida del error o el log?
Fijate el enconding de la base también, en especial si compilaste el codigo.

El día 30 de septiembre de 2008 17:21, Pedro Mateo
<pedromateoa@gmail.com> escribió:
> hola a todos
>
> en DEBIAN
> estoy haciendo un programa en c++ con wxwidgets y con librerias de C para
> conectarse a postgres (libpq )
>
> en vez de tomar como std::string estoy usando wxString en todo el programa
> pues tiene mas funcionalidades y es la clase que maneja las cadenas en las
> wxwidgets
>
> hay una funcion de libpq que se llama PQprepared que en uno de sus
> parametros recibe un arreglo de cadenas, con un arreglo int que contiene los
> diferentes tamanos de cada cadena
>
> yo creo el arreglo de cadenas y voy copiando de instancias de wxstring para
> llenarlos, cosa que me esta saliendo bien
>
> el programa compila correctamente, pero cuando lo ejecuto me da un error de
> codificacion de caracteres
>
> parece que wxstring no maneja el misma codificacion que trabaja postgres que
> usa uft8
>
> no se mucho de esos asuntos pero imaginaba que al instalar las librerias C
> para conectase a postgres y las librerias wxwidgets asi como el posgres
> estas utilizarian los locales de DEBIAN que son uno do_es.utf8 (para
> republica dominicana ) o algo asi
>
> en fin
> agradeceria la ayuda, pues estoy trancado
>
>
>
--
TIP 8: explain analyze es tu amigo

[pgsql-es-ayuda] wxwidgets libpq error de codificacion de caracteres

2008-09-30T13:21:00.000-07:00

hola a todos

en DEBIAN

estoy haciendo un programa en c++ con wxwidgets y con librerias de C para conectarse a postgres (libpq )

en vez de tomar como std::string estoy usando wxString en todo el programa pues tiene mas funcionalidades y es la clase que maneja las cadenas en las wxwidgets

hay una funcion de libpq que se llama PQprepared que en uno de sus parametros recibe un arreglo de cadenas, con un arreglo int que contiene los diferentes tamanos de cada cadena

yo creo el arreglo de cadenas y voy copiando de instancias de wxstring para llenarlos, cosa que me esta saliendo bien

el programa compila correctamente, pero cuando lo ejecuto me da un error de codificacion de caracteres

parece que wxstring no maneja el misma codificacion que trabaja postgres que usa uft8

no se mucho de esos asuntos pero imaginaba que al instalar las librerias C para conectase a postgres y las librerias wxwidgets asi como el posgres estas utilizarian los locales de DEBIAN que son uno do_es.utf8 (para republica dominicana ) o algo asi

en fin

agradeceria la ayuda, pues estoy trancado

Re: [pgsql-es-ayuda] Insertar imagen tipo bytea

2008-09-30T13:17:00.000-07:00

Claro como el agua! Gracias Alvaro!

El día 30 de septiembre de 2008 17:03, Alvaro Herrera
<alvherre@alvh.no-ip.org> escribió:
> postgres Emanuel CALVO FRANCO escribió:
>
>> entonces ... cual seria la funcion que inserta la imagen directamente
>> en la tabla?
>> o mejor dicho, que me permita leer una imagen y acoplarla la la query
>> de inserción.
>> o la respuesta es: 'tenes que insertarlo desde la aplicacion, parseas
>> la instrucción y listo'
>
> Efectivamente :-)
>
> Algunos lenguajes tienen soporte para expandir "placeholders" en la
> consulta, como mostraba el amigo que hizo la pregunta. En PHP (!) se ve
> algo así:
>
> $imagen = file_get_contents("/home/john/sarah_palin.png");
> pg_query("insert into friends (photo) values (?)", $imagen)
>
> (En realidad no sé si pg_query admite hacer eso; sé que PDO puede, pero
> espero que se entienda la idea).
>
> --
> Alvaro Herrera Valdivia, Chile ICBM: S 39º 48' 55.3", W 73º 15' 24.7"
> "La conclusión que podemos sacar de esos estudios es que
> no podemos sacar ninguna conclusión de ellos" (Tanenbaum)
>
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

Re: [pgsql-es-ayuda] Insertar imagen tipo bytea

2008-09-30T13:03:00.000-07:00

postgres Emanuel CALVO FRANCO escribió:

> entonces ... cual seria la funcion que inserta la imagen directamente
> en la tabla?
> o mejor dicho, que me permita leer una imagen y acoplarla la la query
> de inserción.
> o la respuesta es: 'tenes que insertarlo desde la aplicacion, parseas
> la instrucción y listo'

Efectivamente :-)

Algunos lenguajes tienen soporte para expandir "placeholders" en la
consulta, como mostraba el amigo que hizo la pregunta. En PHP (!) se ve
algo así:

$imagen = file_get_contents("/home/john/sarah_palin.png");
pg_query("insert into friends (photo) values (?)", $imagen)

(En realidad no sé si pg_query admite hacer eso; sé que PDO puede, pero
espero que se entienda la idea).

--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 48' 55.3", W 73º 15' 24.7"
"La conclusión que podemos sacar de esos estudios es que
no podemos sacar ninguna conclusión de ellos" (Tanenbaum)
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
http://archives.postgresql.org/pgsql-es-ayuda

Re: [pgsql-es-ayuda] Insertar imagen tipo bytea

2008-09-30T12:53:00.000-07:00

copio a la lista porque le erré a la direccion....

entonces ... cual seria la funcion que inserta la imagen directamente
en la tabla?
o mejor dicho, que me permita leer una imagen y acoplarla la la query
de inserción.
o la respuesta es: 'tenes que insertarlo desde la aplicacion, parseas
la instrucción y listo'
perdón si le estoy pifiando peor todavia... ;P

El día 30 de septiembre de 2008 16:52, postgres Emanuel CALVO FRANCO
<postgres.arg@gmail.com> escribió:
> enteonces ... cual seria la funcion que inserta la imagen directamente
> en la tabla?
> o mejor dicho, que me permita leer una imagen y acoplarla la la query
> de inserción.
> o la respuesta es: 'tenes que insertarlo desde la aplicacion, parseas
> la instrucción y listo'
> perdón si le estoy pifiando peor todavia... ;P
>
> 2008/9/30 Alvaro Herrera <alvherre@alvh.no-ip.org>:
>> postgres Emanuel CALVO FRANCO escribió:
>>> Entonces le quedaria usar
>>> lo_read(PGconn *conn, int fd, char *buf, size_t len);
>>> y lo_open ?
>>>
>>> por lo que si bien import lo que hace es devolver un oid, las funciones
>>> que levantan los large objects utilizan estas funciones. o le estoy
>>> pifiando fiero?
>>
>> Le estas pifiando, porque las funciones lo_* funcionan con large
>> objects (crean, abren, cierran, escriben). La clave de mi mensaje
>> anterior es que bytea no es "large objects". No necesitas abrir y
>> cerrar un bytea, por ejemplo.
>>
>> --
>> Alvaro Herrera http://www.amazon.com/gp/registry/3BP7BYG9PUGI8
>> "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)
>>
>
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [GENERAL] Why Does UPDATE Take So Long?

2008-09-30T12:42:00.000-07:00

On Tue, Sep 30, 2008 at 1:00 PM, Bill Thoen <bthoen@gisnet.com> wrote:
> Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a
> very long time; 15 minutes so far and no end in sight. From the explain, it
> doesn't seem like it should take that long, and this column is not indexed.
> Sure, there's 2.7 million records but it only takes a few minutes to scan
> the whole file. Is there some special overhead I should be aware of with an
> UPDATE? I VACUUMed and ANALYZEd first, too.
>
> Or am I just expecting too much?

The problem is that on older versions of pgsql, the db had to update
each index for each row updated as well as the rows. The latest
version, with a low enough fill factor, can update non-indedexed
fields by using the free space in each page and not have to hit the
indexes. But on 8.1 you don't get that optimization.

--
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] Common Table Expressions (WITH RECURSIVE) patch

2008-09-30T12:39:00.000-07:00

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Le 30 sept. 08 à 20:03, Tom Lane a écrit :
> set_read_position(tupstore, &local_read_position);
> tuple = tuplestore_gettuple(tupstore, ...);
> get_read_position(tupstore, &local_read_position);
>
> rather than just tuplestore_gettuple. The set/get functions will be
> cheap enough that this is no big deal. (Or maybe we should just
> provide a wrapper function that does this sequence?)

It seems to me to share some ideas with the MemoryContext concept:
what about a TupstoreContext associated with tuplestore, you get a
common default one if you don't register your own, and use
tuplestore_gettuple(MyTupstoreContext, ...);

Maybe some other API would benefit from the idea?

Regards,
- --
dim

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkjigF4ACgkQlBXRlnbh1bkycQCgqs/+JBOd0SiN4xvKwLgEgi9F
BOYAoLm0Se6zs8cEAnoTlH6de7pLLh/l
=kzm1
-----END PGP SIGNATURE-----

--
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] Alias name from subquery

2008-09-30T12:38:00.000-07:00

Thanks to Scott and Taras for pointing me to the crosstab functions. I
only had a quick look but they seem very helpful!

Kind regards,

Felix

--
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] Why Does UPDATE Take So Long?

2008-09-30T12:36:00.001-07:00

On Tue, 2008-09-30 at 13:00 -0600, Bill Thoen wrote:
> Working with PG 8.1 I'm trying to update a char(4) column, and it's
> taking a very long time; 15 minutes so far and no end in sight. From the
> explain, it doesn't seem like it should take that long, and this column
> is not indexed. Sure, there's 2.7 million records but it only takes a
> few minutes to scan the whole file. Is there some special overhead I
> should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too.
>

In PostgreSQL, think of an UPDATE as an INSERT+DELETE. It's got to write
the new versions of the tuples, and it has to keep the old versions
until there are no more transactions that might reference those old
versions. Imagine if you canceled the query halfway through, for
example. Also, it has to create new index entries for the same reason,
which is expensive.

There are some optimizations in 8.3 for when the same tuple gets updated
many times, but that won't help you in this case.

Regards,
Jeff Davis

--
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] Insertar imagen tipo bytea

2008-09-30T12:36:00.000-07:00

postgres Emanuel CALVO FRANCO escribió:
> Entonces le quedaria usar
> lo_read(PGconn *conn, int fd, char *buf, size_t len);
> y lo_open ?
>
> por lo que si bien import lo que hace es devolver un oid, las funciones
> que levantan los large objects utilizan estas funciones. o le estoy
> pifiando fiero?

Le estas pifiando, porque las funciones lo_* funcionan con large
objects (crean, abren, cierran, escriben). La clave de mi mensaje
anterior es que bytea no es "large objects". No necesitas abrir y
cerrar un bytea, por ejemplo.

--
Alvaro Herrera http://www.amazon.com/gp/registry/3BP7BYG9PUGI8
"Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)
--
TIP 7: no olvides aumentar la configuración del "free space map"

Re: [pgsql-es-ayuda] Insertar imagen tipo bytea

2008-09-30T12:29:00.001-07:00

Entonces le quedaria usar
lo_read(PGconn *conn, int fd, char *buf, size_t len);
y lo_open ?

por lo que si bien import lo que hace es devolver un oid, las funciones
que levantan los large objects utilizan estas funciones. o le estoy
pifiando fiero?

El día 30 de septiembre de 2008 12:18, Alvaro Herrera
<alvherre@alvh.no-ip.org> escribió:
> Linder Poclaba Lazaro escribió:
>
> Hola!
>
>> [TopLink Warning]: 2008.09.30 02:23:41.962--UnitOfWork(11595950)--Exception
>> [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b09d-fcs
>> (12/06/2007))): oracle.toplink.essentials.exceptions.DatabaseException
>> Internal Exception: org.postgresql.util.PSQLException: ERROR: column
>> "imagen" is of type bytea but expression is of type oid
>> Error Code: 0
>> Call: INSERT INTO public.joya (estado, cliente_idcliente, cantidad,
>> peso_otros, imagen, descripcion, peso_bruto, inscripciones, tasacion,
>> idpreforma_joya, kilataje_idkilataje, tipo_joya_idtipo_joya) VALUES (?, ?,
>> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
>> bind => [muy bueno, 0, 1, 1.3, null, anillo de oro, 44.0, regalo,
>> 4.0, 13, 1, 1]
>
> Estás mezclando las cosas. No debes confundir el concepto de un large
> object con una columna de tipo bytea.
>
> Una columna de tipo bytea lleva la imagen misma, es decir la secuencia
> bytes que la componen. Una columna de tipo oid lleva un OID (es decir
> un identificador numérico), que es una referencia a un "large object".
> En este último caso los bytes que forman la imagen se guardan en otra
> tabla (pg_largeobject), y debes importarlos usando lo_import.
>
> Básicamente, ese "null" que tienes ahí debe ir declarado como tipo
> bytea, no como oid.
>
> --
> Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
> "La realidad se compone de muchos sueños, todos ellos diferentes,
> pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos)
> --
> TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
> http://archives.postgresql.org/pgsql-es-ayuda
>
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
http://archives.postgresql.org/pgsql-es-ayuda

Re: [ADMIN] What process clears the logs?

2008-09-30T12:26:00.000-07:00

Carol Walter wrote:
> Greetings,
>
> As you may be aware, we experienced a problem last week with pg_clogs
> that had been deleted, through human error it appears. What process
> will clear or delete the pg_clogs? I've been all over the
> documentation and I'm not finding a reference to this.
>
PostgreSQL takes care of this itself. I don't know where it is
documented but IIRC from old messages, the number of files in pg_xlog
should level out at about two-times your checkpoint_settings value.

I'd have to do some digging as I don't recall for sure, but I think that
if you are using archive_command setting to archive WAL files the
archive command does not return "success", then the WAL file will not be
removed/reused.

Cheers,
Steve

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

Re: [HACKERS] Block-level CRC checks

2008-09-30T12:23:00.000-07:00

On Tue, 30 Sep 2008, Heikki Linnakangas wrote:

> Doesn't some filesystems include a per-block CRC, which would achieve the
> same thing? ZFS?

Yes, there is a popular advoacy piece for ZFS with a high-level view of
why and how they implement that at
http://blogs.sun.com/bonwick/entry/zfs_end_to_end_data The guarantees are
stronger than what you can get if you just put a CRC in the block itself.
I'd never really thought too hard about putting this in the database
knowing that ZFS is available for environments where this is a concern,
but it certainly would be a nice addition.

The best analysis I've ever seen that makes a case for OS or higher level
disk checksums of some sort, by looking at the myriad ways that disks and
disk arrays fail in the real world, is in
http://www.usenix.org/event/fast08/tech/full_papers/bairavasundaram/bairavasundaram.pdf
(there is a shorter version that hits the high points of that at
http://www.usenix.org/publications/login/2008-06/openpdfs/bairavasundaram.pdf
)

One really interesting bit in there I'd never seen before is that they
find real data that supports the stand that enterprise drives are
significantly more reliable than consumer ones. While general failure
rates aren't that different, "SATA disks have an order of magnitude higher
probability of developing checksum mismatches than Fibre Channel disks. We
find that 0.66% of SATA disks develop at least one mismatch during the
first 17 months in the field, whereas only 0.06% of Fibre Channel disks
develop a mismatch during that time."

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] Why Does UPDATE Take So Long?

2008-09-30T12:20:00.000-07:00

Bill Thoen <bthoen@gisnet.com> schrieb:

> Working with PG 8.1 I'm trying to update a char(4) column, and it's
> taking a very long time; 15 minutes so far and no end in sight. From the
> explain, it doesn't seem like it should take that long, and this column
> is not indexed. Sure, there's 2.7 million records but it only takes a
> few minutes to scan the whole file. Is there some special overhead I
> should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too.
>
> Or am I just expecting too much?
>
> Here's the explain:
> explain UPDATE farms SET prog_year='2007';
> QUERY PLAN
> ----------------------------------------------------------------
> Seq Scan on farms (cost=0.00..59144.07 rows=2772207 width=54)
> (1 row)

Please provide us an EXPLAIN ANALYSE. But without a WHERE-condition a
seq-scan are logical, and PG has to rewrite the whole table and the
transaction-log.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
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] Block-level CRC checks

2008-09-30T12:17:00.000-07:00

> A customer of ours has been having trouble with corrupted data for some
> time. Of course, we've almost always blamed hardware (and we've seen
> RAID controllers have their firmware upgraded, among other actions), but
> the useful thing to know is when corruption has happened, and where.

That is an important statement, to know when it happens not necessarily to
be able to recover the block or where in the block it is corrupt. Is that
correct?

>
> So we've been tasked with adding CRCs to data files.

CRC or checksum? If the objective is merely general "detection" there
should be some latitude in choosing the methodology for performance.

>
> The idea is that these CRCs are going to be checked just after reading
> files from disk, and calculated just before writing it. They are
> just a protection against the storage layer going mad; they are not
> intended to protect against faulty RAM, CPU or kernel.

It will actually find faults in all if it. If the CPU can't add and/or a
RAM location lost a bit, this will blow up just as easily as a bad block.
It may cause "false identification" of an error, but it will keep a bad
system from hiding.

>
> This code would be run-time or compile-time configurable. I'm not
> absolutely sure which yet; the problem with run-time is what to do if
> the user restarts the server with the setting flipped. It would have
> almost no impact on users who don't enable it.

CPU capacity on modern hardware within a small area of RAM is practically
infinite when compared to any sort of I/O.
>
> The implementation I'm envisioning requires the use of a new relation
> fork to store the per-block CRCs. Initially I'm aiming at a CRC32 sum
> for each block. FlushBuffer would calculate the checksum and store it
> in the CRC fork; ReadBuffer_common would read the page, calculate the
> checksum, and compare it to the one stored in the CRC fork.

Hell, all that is needed is a long or a short checksum value in the block.
I mean, if you just want a sanity test, it doesn't take much. Using a
second relation creates confusion. If there is a CRC discrepancy between
two different blocks, who's wrong? You need a third "control" to know. If
the block knows its CRC or checksum and that is in error, the block is
bad.

>
> A buffer's io_in_progress lock protects the buffer's CRC. We read and
> pin the CRC page before acquiring the lock, to avoid having two buffer
> IO operations in flight.
>
> I'd like to submit this for 8.4, but I want to ensure that -hackers at
> large approve of this feature before starting serious coding.
>
> Opinions?

If its fast enough, its a good idea. It could be very helpful in
protecting users data.

>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
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] Why Does UPDATE Take So Long?

2008-09-30T12:14:00.000-07:00

On Tuesday 30 September 2008, Bill Thoen <bthoen@gisnet.com> wrote:
> Working with PG 8.1 I'm trying to update a char(4) column, and it's
> taking a very long time; 15 minutes so far and no end in sight. From the
> explain, it doesn't seem like it should take that long, and this column
> is not indexed. Sure, there's 2.7 million records but it only takes a
> few minutes to scan the whole file. Is there some special overhead I
> should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too.

update creates new rows for all affected rows. If the table is indexed, it
creates new index rows for all affected rows in every index. Slow updates
is a common PostgreSQL complaint.

--
Alan

--
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] Block-level CRC checks

2008-09-30T12:00:00.001-07:00

Alvaro Herrera wrote:
> Initially I'm aiming at a CRC32 sum
> for each block. FlushBuffer would calculate the checksum and store it
> in the CRC fork; ReadBuffer_common would read the page, calculate the
> checksum, and compare it to the one stored in the CRC fork.

There's one fundamental problem with that, related to the way our hint
bits are written.

Currently, hint bit updates are not WAL-logged, and thus no full page
write is done when only hint bits are changed. Imagine what happens if
hint bits are updated on a page, but there's no other changes, and we
crash so that only one half of the new page version makes it to disk (=
torn page). The CRC would not match, even though the page is actually valid.

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

[GENERAL] Why Does UPDATE Take So Long?

2008-09-30T12:00:00.000-07:00

Working with PG 8.1 I'm trying to update a char(4) column, and it's
taking a very long time; 15 minutes so far and no end in sight. From the
explain, it doesn't seem like it should take that long, and this column
is not indexed. Sure, there's 2.7 million records but it only takes a
few minutes to scan the whole file. Is there some special overhead I
should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too.

Or am I just expecting too much?

Here's the explain:
explain UPDATE farms SET prog_year='2007';
QUERY PLAN
----------------------------------------------------------------
Seq Scan on farms (cost=0.00..59144.07 rows=2772207 width=54)
(1 row)

TIA,
- Bill Thoen

--
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] Block-level CRC checks

2008-09-30T11:56:00.000-07:00

Hello Alvaro,

some random thoughts while reading your proposal follow...

Alvaro Herrera wrote:
> So we've been tasked with adding CRCs to data files.

Disks get larger and relative reliability shrinks, it seems. So I agree
that this is a worthwhile thing to have. But shouldn't that be the job
of the filesystem? Think of ZFS or the upcoming BTRFS.

> The idea is that these CRCs are going to be checked just after reading
> files from disk, and calculated just before writing it. They are
> just a protection against the storage layer going mad; they are not
> intended to protect against faulty RAM, CPU or kernel.

That sounds reasonable if we do it from Postgres.

> This code would be run-time or compile-time configurable. I'm not
> absolutely sure which yet; the problem with run-time is what to do if
> the user restarts the server with the setting flipped. It would have
> almost no impact on users who don't enable it.

I'd say calculating a CRC is close enough to be considered "no impact".
A single core of a modern CPU easily reaches way above 200 MiB/s
throughput for CRC32 today. See [1].

Maybe consider Adler-32 which is 3-4x faster [2], also part of zlib and
AFAIK about equally safe for 8k blocks and above.

Huh? Aren't CRCs normally stored as part of the block they are supposed
to protect? Or how do you expect to ensure the data from the CRC
relation fork is correct? How about crash safety (a data block written,
but not its CRC block or vice versa)?

Wouldn't that double the amount of seeking required for writes?

> I'd like to submit this for 8.4, but I want to ensure that -hackers at
> large approve of this feature before starting serious coding.

Very cool!

Regards

Markus Wanner

[1]: Crypto++ benchmarks:
http://www.cryptopp.com/benchmarks.html

[2]: Wikipedia about hash functions:
http://en.wikipedia.org/wiki/List_of_hash_functions#Computational_costs_of_CRCs_vs_Hashes

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

Re: [ADMIN] turning of pg_xlog

2008-09-30T11:55:00.000-07:00

Tom Lane wrote:
> Chander Ganesan <chander@otg-nc.com> writes:
>
>> You should also understand that if you set checkpoint_segments to a
>> small number, its still possible that PostgreSQL might use more than
>> that number for a large transaction.
>>
>
> "Large transactions" have nothing to do with this. You are confusing
> Postgres' implementation with Oracle's.
>
> regards, tom lane
Okay. I guess my use of the word "transaction" here was erroneous -
probably comes from my oracle background :-( . However, the fact
remains that its possible to exceed checkpoint_segments in certain
scenarios (I think the docs refer to it as a peak in log creation or
something like that), which - if it fills up the disk that the WALs are
on, can result in issues... And one should be wary of that when trying
to limit the number of WALs in order to meet limited disk space
requirements.

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

Re: [HACKERS] Block-level CRC checks

2008-09-30T11:51:00.000-07:00

On Tue, Sep 30, 2008 at 2:49 PM, Joshua Drake <jd@commandprompt.com> wrote:
> On Tue, 30 Sep 2008 14:33:04 -0400
> "Jonah H. Harris" <jonah.harris@gmail.com> wrote:
>
>> > I'd like to submit this for 8.4, but I want to ensure that -hackers
>> > at large approve of this feature before starting serious coding.
>>
>> IMHO, this is a functionality that should be enabled by default (as it
>> is on most other RDBMS). It would've prevented severe corruption in
>
> What other RDMS have it enabled by default?

Oracle and (I belive) SQL Server >= 2005

--
Jonah H. Harris, Senior DBA
myYearbook.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: [ADMIN] turning of pg_xlog

2008-09-30T11:49:00.001-07:00

I might have to disagree here. I personally experienced a scenario
where I had 500 MB of file space defined for my WAL log files and then
attempted (not thinking it through thoroughly) to perform a COPY of a
very large (1.8 GB) dataset and kept adding WAL files until my file
system filled up and then the instance crashed.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, September 30, 2008 1:45 PM
To: Chander Ganesan
Cc: Jonny; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] turning of pg_xlog

Chander Ganesan <chander@otg-nc.com> writes:
> You should also understand that if you set checkpoint_segments to a
> small number, its still possible that PostgreSQL might use more than
> that number for a large transaction.

"Large transactions" have nothing to do with this. You are confusing
Postgres' implementation with Oracle's.

regards, tom lane

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

Re: [HACKERS] Block-level CRC checks

2008-09-30T11:49:00.000-07:00

On Tue, 30 Sep 2008 14:33:04 -0400
"Jonah H. Harris" <jonah.harris@gmail.com> wrote:

> > I'd like to submit this for 8.4, but I want to ensure that -hackers
> > at large approve of this feature before starting serious coding.
>
> IMHO, this is a functionality that should be enabled by default (as it
> is on most other RDBMS). It would've prevented severe corruption in

What other RDMS have it enabled by default?

Sincerely,

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/

--
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] Block-level CRC checks

2008-09-30T11:48:00.000-07:00

Alvaro Herrera wrote:
> A customer of ours has been having trouble with corrupted data for some
> time. Of course, we've almost always blamed hardware (and we've seen
> RAID controllers have their firmware upgraded, among other actions), but
> the useful thing to know is when corruption has happened, and where.
>
> So we've been tasked with adding CRCs to data files.
>
> The idea is that these CRCs are going to be checked just after reading
> files from disk, and calculated just before writing it. They are
> just a protection against the storage layer going mad; they are not
> intended to protect against faulty RAM, CPU or kernel.

This has been suggested before, and the usual objection is precisely
that it only protects from errors in the storage layer, giving a false
sense of security.

Doesn't some filesystems include a per-block CRC, which would achieve
the same thing? ZFS?

Yeah, seems like it would need to be compile-time or initdb-time
configurable.

Surely it would be much simpler to just add a field to the page header.

--
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: [ADMIN] turning of pg_xlog

2008-09-30T11:44:00.000-07:00

"Large transactions" have nothing to do with this. You are confusing
Postgres' implementation with Oracle's.

regards, tom lane

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

Re: [HACKERS] Block-level CRC checks

2008-09-30T11:43:00.000-07:00

Alvaro Herrera <alvherre@commandprompt.com> writes:
> The implementation I'm envisioning requires the use of a new relation
> fork to store the per-block CRCs.

That seems bizarre, and expensive, and if you lose one block of the CRC
fork you lose confidence in a LOT of data. Why not keep the CRCs in the
page headers?

> A buffer's io_in_progress lock protects the buffer's CRC.

Unfortunately, it doesn't. See hint bits.

regards, tom lane

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

[pgsql-es-ayuda] Validación de CUIT - Argentina

2008-09-30T11:41:00.000-07:00

Buenas Tardes a todos:
Alguien tiene armada alguna función para validar el
CUIT (Clave Única de Identificación Tributaria) de Argentina.

Desde ya les agradezco.

Atte.
___________________________
ASC Daniel Ferrer
Gerente de Sistemas - CTD SRL
Rosario - Argentina
mailto:daniel.ferrer@ctd.com.ar
___________________________

--
TIP 7: no olvides aumentar la configuración del "free space map"

[HACKERS] pg_hba options parsing

2008-09-30T11:36:00.000-07:00

*** a/src/backend/libpq/auth.c
--- b/src/backend/libpq/auth.c
***************
*** 126,134 **** char *pg_krb_realm = NULL;
* MIT Kerberos authentication system - protocol version 5
*----------------------------------------------------------------
*/
- static int pg_krb5_recvauth(Port *port);
-
#ifdef KRB5

#include <krb5.h>
/* Some old versions of Kerberos do not include <com_err.h> in <krb5.h> */
--- 126,133 ----
* MIT Kerberos authentication system - protocol version 5
*----------------------------------------------------------------
*/
#ifdef KRB5
+ static int pg_krb5_recvauth(Port *port);

#include <krb5.h>
/* Some old versions of Kerberos do not include <com_err.h> in <krb5.h> */
***************
*** 150,163 **** static krb5_principal pg_krb5_server;
* GSSAPI Authentication
*----------------------------------------------------------------
*/
- static int pg_GSS_recvauth(Port *port);
-
#ifdef ENABLE_GSS
#if defined(HAVE_GSSAPI_H)
#include <gssapi.h>
#else
#include <gssapi/gssapi.h>
#endif
#endif /* ENABLE_GSS */

--- 149,162 ----
* GSSAPI Authentication
*----------------------------------------------------------------
*/
#ifdef ENABLE_GSS
#if defined(HAVE_GSSAPI_H)
#include <gssapi.h>
#else
#include <gssapi/gssapi.h>
#endif
+
+ static int pg_GSS_recvauth(Port *port);
#endif /* ENABLE_GSS */

***************
*** 165,176 **** static int pg_GSS_recvauth(Port *port);
* SSPI Authentication
*----------------------------------------------------------------
*/
- static int pg_SSPI_recvauth(Port *port);
-
#ifdef ENABLE_SSPI
typedef SECURITY_STATUS
(WINAPI * QUERY_SECURITY_CONTEXT_TOKEN_FN) (
PCtxtHandle, void **);
#endif

--- 164,174 ----
* SSPI Authentication
*----------------------------------------------------------------
*/
#ifdef ENABLE_SSPI
typedef SECURITY_STATUS
(WINAPI * QUERY_SECURITY_CONTEXT_TOKEN_FN) (
PCtxtHandle, void **);
+ static int pg_SSPI_recvauth(Port *port);
#endif

***************
*** 236,251 **** auth_failed(Port *port, int status)
case uaPassword:
errstr = gettext_noop("password authentication failed for user \"%s\"");
break;
- #ifdef USE_PAM
case uaPAM:
errstr = gettext_noop("PAM authentication failed for user \"%s\"");
break;
- #endif /* USE_PAM */
- #ifdef USE_LDAP
case uaLDAP:
errstr = gettext_noop("LDAP authentication failed for user \"%s\"");
break;
- #endif /* USE_LDAP */
default:
errstr = gettext_noop("authentication failed for user \"%s\": invalid authentication method");
break;
--- 234,245 ----
***************
*** 316,333 **** ClientAuthentication(Port *port)
--- 310,339 ----
}

case uaKrb5:
+ #ifdef KRB5
sendAuthRequest(port, AUTH_REQ_KRB5);
status = pg_krb5_recvauth(port);
+ #else
+ Assert(false);
+ #endif
break;

case uaGSS:
+ #ifdef ENABLE_GSS
sendAuthRequest(port, AUTH_REQ_GSS);
status = pg_GSS_recvauth(port);
+ #else
+ Assert(false);
+ #endif
break;

case uaSSPI:
+ #ifdef ENABLE_SSPI
sendAuthRequest(port, AUTH_REQ_SSPI);
status = pg_SSPI_recvauth(port);
+ #else
+ Assert(false);
+ #endif
break;

case uaIdent:
***************
*** 377,393 **** ClientAuthentication(Port *port)
status = recv_and_check_password_packet(port);
break;

- #ifdef USE_PAM
case uaPAM:
pam_port_cludge = port;
status = CheckPAMAuth(port, port->user_name, "");
break;
#endif /* USE_PAM */

- #ifdef USE_LDAP
case uaLDAP:
status = CheckLDAPAuth(port);
break;
#endif

case uaTrust:
--- 383,403 ----
status = recv_and_check_password_packet(port);
break;

case uaPAM:
+ #ifdef USE_PAM
pam_port_cludge = port;
status = CheckPAMAuth(port, port->user_name, "");
+ #else
+ Assert(false);
break;
#endif /* USE_PAM */

case uaLDAP:
+ #ifdef USE_LDAP
status = CheckLDAPAuth(port);
break;
+ #else
+ Assert(false);
#endif

case uaTrust:
***************
*** 713,731 **** pg_krb5_recvauth(Port *port)
return STATUS_ERROR;
}

! if (pg_krb_caseins_users)
! ret = pg_strncasecmp(port->user_name, kusername, SM_DATABASE_USER);
! else
! ret = strncmp(port->user_name, kusername, SM_DATABASE_USER);
! if (ret)
! {
! ereport(LOG,
! (errmsg("unexpected Kerberos user name received from client (received \"%s\", expected \"%s\")",
! port->user_name, kusername)));
! ret = STATUS_ERROR;
! }
! else
! ret = STATUS_OK;

krb5_free_ticket(pg_krb5_context, ticket);
krb5_auth_con_free(pg_krb5_context, auth_context);
--- 723,730 ----
return STATUS_ERROR;
}

! ret = check_usermap(port->hba->usermap, port->user_name, kusername,
! pg_krb_caseins_users);

krb5_free_ticket(pg_krb5_context, ticket);
krb5_auth_con_free(pg_krb5_context, auth_context);
***************
*** 733,748 **** pg_krb5_recvauth(Port *port)

return ret;
}
- #else
-
- static int
- pg_krb5_recvauth(Port *port)
- {
- ereport(LOG,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("Kerberos 5 not implemented on this server")));
- return STATUS_ERROR;
- }
#endif /* KRB5 */

--- 732,737 ----
***************
*** 1020,1057 **** pg_GSS_recvauth(Port *port)
return STATUS_ERROR;
}

! if (pg_krb_caseins_users)
! ret = pg_strcasecmp(port->user_name, gbuf.value);
! else
! ret = strcmp(port->user_name, gbuf.value);
!
! if (ret)
! {
! /* GSS name and PGUSER are not equivalent */
! elog(DEBUG2,
! "provided username (%s) and GSSAPI username (%s) don't match",
! port->user_name, (char *) gbuf.value);
!
! gss_release_buffer(&lmin_s, &gbuf);
! return STATUS_ERROR;
! }

gss_release_buffer(&lmin_s, &gbuf);

return STATUS_OK;
}
-
- #else /* no ENABLE_GSS */
-
- static int
- pg_GSS_recvauth(Port *port)
- {
- ereport(LOG,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("GSSAPI not implemented on this server")));
- return STATUS_ERROR;
- }
-
#endif /* ENABLE_GSS */

--- 1009,1021 ----
return STATUS_ERROR;
}

! ret = check_usermap(port->hba->usermap, port->user_name, gbuf.value,
! pg_krb_caseins_users);

gss_release_buffer(&lmin_s, &gbuf);

return STATUS_OK;
}
#endif /* ENABLE_GSS */

***************
*** 1328,1357 **** pg_SSPI_recvauth(Port *port)
* We have the username (without domain/realm) in accountname, compare to
* the supplied value. In SSPI, always compare case insensitive.
*/
! if (pg_strcasecmp(port->user_name, accountname))
! {
! /* GSS name and PGUSER are not equivalent */
! elog(DEBUG2,
! "provided username (%s) and SSPI username (%s) don't match",
! port->user_name, accountname);
!
! return STATUS_ERROR;
! }
!
! return STATUS_OK;
}
-
- #else /* no ENABLE_SSPI */
-
- static int
- pg_SSPI_recvauth(Port *port)
- {
- ereport(LOG,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("SSPI not implemented on this server")));
- return STATUS_ERROR;
- }
-
#endif /* ENABLE_SSPI */

--- 1292,1299 ----
* We have the username (without domain/realm) in accountname, compare to
* the supplied value. In SSPI, always compare case insensitive.
*/
! return check_usermap(port->hba->usermap, port->user_name, accountname, true);
}
#endif /* ENABLE_SSPI */

***************
*** 1795,1808 **** authident(hbaPort *port)
return STATUS_ERROR;
}

! ereport(DEBUG2,
! (errmsg("Ident protocol identifies remote user as \"%s\"",
! ident_user)));
!
! if (check_ident_usermap(port->hba->usermap, port->user_name, ident_user))
! return STATUS_OK;
! else
! return STATUS_ERROR;
}

--- 1737,1743 ----
return STATUS_ERROR;
}

! return check_usermap(port->hba->usermap, port->user_name, ident_user, false);
}

***************
*** 1913,1920 **** CheckPAMAuth(Port *port, char *user, char *password)
* not allocated */

/* Optionally, one can set the service name in pg_hba.conf */
! if (port->hba->auth_arg && port->hba->auth_arg[0] != '\0')
! retval = pam_start(port->hba->auth_arg, "pgsql@",
&pam_passw_conv, &pamh);
else
retval = pam_start(PGSQL_PAM_SERVICE, "pgsql@",
--- 1848,1855 ----
* not allocated */

/* Optionally, one can set the service name in pg_hba.conf */
! if (port->hba->pamservice && port->hba->pamservice[0] != '\0')
! retval = pam_start(port->hba->pamservice, "pgsql@",
&pam_passw_conv, &pamh);
else
retval = pam_start(PGSQL_PAM_SERVICE, "pgsql@",
***************
*** 2000,2075 **** static int
CheckLDAPAuth(Port *port)
{
char *passwd;
- char server[128];
- char basedn[128];
- char prefix[128];
- char suffix[128];
LDAP *ldap;
- bool ssl = false;
int r;
int ldapversion = LDAP_VERSION3;
- int ldapport = LDAP_PORT;
char fulluser[NAMEDATALEN + 256 + 1];

! if (!port->hba->auth_arg || port->hba->auth_arg[0] == '\0')
{
ereport(LOG,
! (errmsg("LDAP configuration URL not specified")));
return STATUS_ERROR;
}

! /*
! * Crack the LDAP url. We do a very trivial parse:
! *
! * ldap[s]://<server>[:<port>]/<basedn>[;prefix[;suffix]]
! *
! * This code originally used "%127s" for the suffix, but that doesn't
! * work for embedded whitespace. We know that tokens formed by
! * hba.c won't include newlines, so we can use a "not newline" scanset
! * instead.
! */
!
! server[0] = '\0';
! basedn[0] = '\0';
! prefix[0] = '\0';
! suffix[0] = '\0';
!
! /* ldap, including port number */
! r = sscanf(port->hba->auth_arg,
! "ldap://%127[^:]:%d/%127[^;];%127[^;];%127[^\n]",
! server, &ldapport, basedn, prefix, suffix);
! if (r < 3)
! {
! /* ldaps, including port number */
! r = sscanf(port->hba->auth_arg,
! "ldaps://%127[^:]:%d/%127[^;];%127[^;];%127[^\n]",
! server, &ldapport, basedn, prefix, suffix);
! if (r >= 3)
! ssl = true;
! }
! if (r < 3)
! {
! /* ldap, no port number */
! r = sscanf(port->hba->auth_arg,
! "ldap://%127[^/]/%127[^;];%127[^;];%127[^\n]",
! server, basedn, prefix, suffix);
! }
! if (r < 2)
! {
! /* ldaps, no port number */
! r = sscanf(port->hba->auth_arg,
! "ldaps://%127[^/]/%127[^;];%127[^;];%127[^\n]",
! server, basedn, prefix, suffix);
! if (r >= 2)
! ssl = true;
! }
! if (r < 2)
! {
! ereport(LOG,
! (errmsg("invalid LDAP URL: \"%s\"",
! port->hba->auth_arg)));
! return STATUS_ERROR;
! }

sendAuthRequest(port, AUTH_REQ_PASSWORD);

--- 1935,1954 ----
CheckLDAPAuth(Port *port)
{
char *passwd;
LDAP *ldap;
int r;
int ldapversion = LDAP_VERSION3;
char fulluser[NAMEDATALEN + 256 + 1];

! if (!port->hba->ldapserver|| port->hba->ldapserver[0] == '\0')
{
ereport(LOG,
! (errmsg("LDAP server not specified")));
return STATUS_ERROR;
}

! if (port->hba->ldapport == 0)
! port->hba->ldapport = LDAP_PORT;

sendAuthRequest(port, AUTH_REQ_PASSWORD);

***************
*** 2077,2083 **** CheckLDAPAuth(Port *port)
if (passwd == NULL)
return STATUS_EOF; /* client wouldn't send password */

! ldap = ldap_init(server, ldapport);
if (!ldap)
{
#ifndef WIN32
--- 1956,1962 ----
if (passwd == NULL)
return STATUS_EOF; /* client wouldn't send password */

! ldap = ldap_init(port->hba->ldapserver, port->hba->ldapport);
if (!ldap)
{
#ifndef WIN32
***************
*** 2100,2106 **** CheckLDAPAuth(Port *port)
return STATUS_ERROR;
}

! if (ssl)
{
#ifndef WIN32
if ((r = ldap_start_tls_s(ldap, NULL, NULL)) != LDAP_SUCCESS)
--- 1979,1985 ----
return STATUS_ERROR;
}

! if (port->hba->ldaptls)
{
#ifndef WIN32
if ((r = ldap_start_tls_s(ldap, NULL, NULL)) != LDAP_SUCCESS)
***************
*** 2155,2161 **** CheckLDAPAuth(Port *port)
}

snprintf(fulluser, sizeof(fulluser), "%s%s%s",
! prefix, port->user_name, suffix);
fulluser[sizeof(fulluser) - 1] = '\0';

r = ldap_simple_bind_s(ldap, fulluser, passwd);
--- 2034,2042 ----
}

snprintf(fulluser, sizeof(fulluser), "%s%s%s",
! port->hba->ldapprefix?port->hba->ldapprefix:"",
! port->user_name,
! port->hba->ldapsuffix?port->hba->ldapsuffix:"");
fulluser[sizeof(fulluser) - 1] = '\0';

r = ldap_simple_bind_s(ldap, fulluser, passwd);
***************
*** 2165,2171 **** CheckLDAPAuth(Port *port)
{
ereport(LOG,
(errmsg("LDAP login failed for user \"%s\" on server \"%s\": error code %d",
! fulluser, server, r)));
return STATUS_ERROR;
}

--- 2046,2052 ----
{
ereport(LOG,
(errmsg("LDAP login failed for user \"%s\" on server \"%s\": error code %d",
! fulluser, port->hba->ldapserver, r)));
return STATUS_ERROR;
}

*** a/src/backend/libpq/hba.c
--- b/src/backend/libpq/hba.c
***************
*** 565,570 **** check_db(const char *dbname, const char *role, char *param_str)
--- 565,606 ----

/*
+ * Macros used to check and report on invalid configuration options.
+ * INVALID_AUTH_OPTION = reports when an option is specified for a method where it's
+ * not supported.
+ * REQUIRE_AUTH_OPTION = same as INVALID_AUTH_OPTION, except it also checks if the
+ * method is actually the one specified. Used as a shortcut when
+ * the option is only valid for one authentication method.
+ * MANDATORY_AUTH_ARG = check if a required option is set for an authentication method,
+ * reporting error if it's not.
+ */
+ #define INVALID_AUTH_OPTION(optname, validmethods) do {\
+ ereport(LOG, \
+ (errcode(ERRCODE_CONFIG_FILE_ERROR), \
+ errmsg("authentication option '%s' is only valid for authentication methods '%s'", \
+ optname, validmethods), \
+ errcontext("line %d of configuration file \"%s\"", \
+ line_num, HbaFileName))); \
+ goto hba_other_error; \
+ } while (0)
+
+ #define REQUIRE_AUTH_OPTION(methodval, optname, validmethods) \
+ if (parsedline->auth_method != methodval) \
+ INVALID_AUTH_OPTION("ldaptls", "ldap")
+
+ #define MANDATORY_AUTH_ARG(argvar, argname, authname) \
+ if (argvar == NULL) {\
+ ereport(LOG, \
+ (errcode(ERRCODE_CONFIG_FILE_ERROR), \
+ errmsg("authentication method '%s' requires argument '%s' to be set", \
+ authname, argname), \
+ errcontext("line %d of configuration file \"%s\"", \
+ line_num, HbaFileName))); \
+ goto hba_other_error; \
+ } while (0);
+
+
+ /*
* Parse one line in the hba config file and store the result in
* a HbaLine structure.
*/
***************
*** 801,838 **** parse_hba_line(List *line, int line_num, HbaLine *parsedline)
goto hba_other_error;
}

! /* Get the authentication argument token, if any */
! line_item = lnext(line_item);
! if (line_item)
{
token = lfirst(line_item);
- parsedline->auth_arg= pstrdup(token);
- }

! /*
! * Backwards compatible format of ident authentication - support "naked" ident map
! * name, as well as "sameuser"/"samerole"
! */
! if (parsedline->auth_method == uaIdent)
! {
! if (parsedline->auth_arg && strlen(parsedline->auth_arg))
{
! if (strcmp(parsedline->auth_arg, "sameuser\n") == 0 ||
! strcmp(parsedline->auth_arg, "samerole\n") == 0)
{
! /* This is now the default */
! pfree(parsedline->auth_arg);
! parsedline->auth_arg = NULL;
! parsedline->usermap = NULL;
}
else
{
! /* Specific ident map specified */
! parsedline->usermap = parsedline->auth_arg;
! parsedline->auth_arg = NULL;
}
}
}

return true;

--- 837,938 ----
goto hba_other_error;
}

! /* Parse remaining arguments */
! while ((line_item = lnext(line_item)) != NULL)
{
+ char *c;
+
token = lfirst(line_item);

! c = strchr(token, '=');
! if (c == NULL)
{
! /*
! * Got something that's not a name=value pair.
! *
! * XXX: attempt to do some backwards compatible parsing here?
! */
! ereport(LOG,
! (errcode(ERRCODE_CONFIG_FILE_ERROR),
! errmsg("authentication option not in name=value format: %s", token),
! errcontext("line %d of configuration file \"%s\"",
! line_num, HbaFileName)));
! goto hba_other_error;
! }
! else
! {
! *c++ = '\0'; /* token now holds "name", c holds "value" */
! if (strcmp(token, "map") == 0)
! {
! if (parsedline->auth_method != uaIdent &&
! parsedline->auth_method != uaKrb5 &&
! parsedline->auth_method != uaGSS &&
! parsedline->auth_method != uaSSPI)
! INVALID_AUTH_OPTION("map", "ident, krb5, gssapi and sspi");
! parsedline->usermap = pstrdup(c);
! }
! else if (strcmp(token, "pamservice") == 0)
! {
! REQUIRE_AUTH_OPTION(uaPAM, "pamservice", "pam");
! parsedline->pamservice = pstrdup(c);
! }
! else if (strcmp(token, "ldaptls") == 0)
! {
! REQUIRE_AUTH_OPTION(uaLDAP, "ldaptls", "ldap");
! if (strcmp(c, "1") == 0)
! parsedline->ldaptls = true;
! else
! parsedline->ldaptls = false;
! }
! else if (strcmp(token, "ldapserver") == 0)
! {
! REQUIRE_AUTH_OPTION(uaLDAP, "ldapserver", "ldap");
! parsedline->ldapserver = pstrdup(c);
! }
! else if (strcmp(token, "ldapport") == 0)
! {
! REQUIRE_AUTH_OPTION(uaLDAP, "ldapport", "ldap");
! parsedline->ldapport = atoi(c);
! if (parsedline->ldapport == 0)
! {
! ereport(LOG,
! (errcode(ERRCODE_CONFIG_FILE_ERROR),
! errmsg("invalid ldap port '%s'", c),
! errcontext("line %d of configuration file \"%s\"",
! line_num, HbaFileName)));
! goto hba_other_error;
! }
! }
! else if (strcmp(token, "ldapprefix") == 0)
{
! REQUIRE_AUTH_OPTION(uaLDAP, "ldapprefix", "ldap");
! parsedline->ldapprefix = pstrdup(c);
! }
! else if (strcmp(token, "ldapsuffix") == 0)
! {
! REQUIRE_AUTH_OPTION(uaLDAP, "ldapsuffix", "ldap");
! parsedline->ldapsuffix = pstrdup(c);
}
else
{
! ereport(LOG,
! (errcode(ERRCODE_CONFIG_FILE_ERROR),
! errmsg("unknown authentication option name '%s'", token),
! errcontext("line %d of configuration file \"%s\"",
! line_num, HbaFileName)));
! goto hba_other_error;
}
}
}
+
+ /*
+ * Check if the selected authentication method has any mandatory arguments that
+ * are not set.
+ */
+ if (parsedline->auth_method == uaLDAP)
+ {
+ MANDATORY_AUTH_ARG(parsedline->ldapserver, "ldapserver", "ldap");
+ }

return true;

***************
*** 1018,1025 **** free_hba_record(HbaLine *record)
pfree(record->database);
if (record->role)
pfree(record->role);
! if (record->auth_arg)
! pfree(record->auth_arg);
}

/*
--- 1118,1131 ----
pfree(record->database);
if (record->role)
pfree(record->role);
! if (record->pamservice)
! pfree(record->pamservice);
! if (record->ldapserver)
! pfree(record->ldapserver);
! if (record->ldapprefix)
! pfree(record->ldapprefix);
! if (record->ldapsuffix)
! pfree(record->ldapsuffix);
}

/*
***************
*** 1150,1156 **** read_pg_database_line(FILE *fp, char *dbname, Oid *dboid,
static void
parse_ident_usermap(List *line, int line_number, const char *usermap_name,
const char *pg_role, const char *ident_user,
! bool *found_p, bool *error_p)
{
ListCell *line_item;
char *token;
--- 1256,1262 ----
static void
parse_ident_usermap(List *line, int line_number, const char *usermap_name,
const char *pg_role, const char *ident_user,
! bool case_insensitive, bool *found_p, bool *error_p)
{
ListCell *line_item;
char *token;
***************
*** 1183,1192 **** parse_ident_usermap(List *line, int line_number, const char *usermap_name,
file_pgrole = token;

/* Match? */
! if (strcmp(file_map, usermap_name) == 0 &&
! strcmp(file_pgrole, pg_role) == 0 &&
! strcmp(file_ident_user, ident_user) == 0)
! *found_p = true;

return;

--- 1289,1308 ----
file_pgrole = token;

/* Match? */
! if (case_insensitive)
! {
! if (strcmp(file_map, usermap_name) == 0 &&
! pg_strcasecmp(file_pgrole, pg_role) == 0 &&
! pg_strcasecmp(file_ident_user, ident_user) == 0)
! *found_p = true;
! }
! else
! {
! if (strcmp(file_map, usermap_name) == 0 &&
! strcmp(file_pgrole, pg_role) == 0 &&
! strcmp(file_ident_user, ident_user) == 0)
! *found_p = true;
! }

return;

***************
*** 1210,1231 **** ident_syntax:
* file. That's an implied map where "pgrole" must be identical to
* "ident_user" in order to be authorized.
*
! * Iff authorized, return true.
*/
! bool
! check_ident_usermap(const char *usermap_name,
const char *pg_role,
! const char *ident_user)
{
bool found_entry = false,
error = false;

if (usermap_name == NULL || usermap_name[0] == '\0')
{
! if (strcmp(pg_role, ident_user) == 0)
! found_entry = true;
! else
! found_entry = false;
}
else
{
--- 1326,1357 ----
* file. That's an implied map where "pgrole" must be identical to
* "ident_user" in order to be authorized.
*
! * Iff authorized, return STATUS_OK, otherwise return STATUS_ERROR.
*/
! int
! check_usermap(const char *usermap_name,
const char *pg_role,
! const char *auth_user,
! bool case_insensitive)
{
bool found_entry = false,
error = false;

if (usermap_name == NULL || usermap_name[0] == '\0')
{
! if (case_insensitive)
! {
! if (pg_strcasecmp(pg_role, auth_user) == 0)
! return STATUS_OK;
! }
! else {
! if (strcmp(pg_role, auth_user) == 0)
! return STATUS_OK;
! }
! ereport(LOG,
! (errmsg("provided username (%s) and authenticated username (%s) don't match",
! auth_user, pg_role)));
! return STATUS_ERROR;
}
else
{
***************
*** 1235,1247 **** check_ident_usermap(const char *usermap_name,
forboth(line_cell, ident_lines, num_cell, ident_line_nums)
{
parse_ident_usermap(lfirst(line_cell), lfirst_int(num_cell),
! usermap_name, pg_role, ident_user,
&found_entry, &error);
if (found_entry || error)
break;
}
}
! return found_entry;
}

--- 1361,1380 ----
forboth(line_cell, ident_lines, num_cell, ident_line_nums)
{
parse_ident_usermap(lfirst(line_cell), lfirst_int(num_cell),
! usermap_name, pg_role, auth_user, case_insensitive,
&found_entry, &error);
if (found_entry || error)
break;
}
}
! if (!found_entry && !error)
! {
! ereport(LOG,
! (errmsg("no match in usermap for user '%s' authenticated as '%s'",
! pg_role, auth_user),
! errcontext("usermap '%s'", usermap_name)));
! }
! return found_entry?STATUS_OK:STATUS_ERROR;
}

*** a/src/backend/libpq/pg_hba.conf.sample
--- b/src/backend/libpq/pg_hba.conf.sample
***************
*** 9,18 ****
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
! # local DATABASE USER METHOD [OPTION]
! # host DATABASE USER CIDR-ADDRESS METHOD [OPTION]
! # hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
! # hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
#
# (The uppercase items must be replaced by actual values.)
#
--- 9,18 ----
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
! # local DATABASE USER METHOD [OPTIONS]
! # host DATABASE USER CIDR-ADDRESS METHOD [OPTIONS]
! # hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTIONS]
! # hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
***************
*** 38,44 ****
# "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords
# in clear text; "md5" is preferred since it sends encrypted passwords.
#
! # OPTION is the ident map or the name of the PAM service, depending on METHOD.
#
# Database and user names containing spaces, commas, quotes and other special
# characters must be quoted. Quoting one of the keywords "all", "sameuser" or
--- 38,47 ----
# "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords
# in clear text; "md5" is preferred since it sends encrypted passwords.
#
! # OPTIONS are a set of options for the authentication in the format
! # NAME=VALUE. The available options depend on the different authentication
! # methods - refer to the "Client Authentication" section in the documentation
! # for a list of which options are available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other special
# characters must be quoted. Quoting one of the keywords "all", "sameuser" or
*** a/src/backend/libpq/pg_ident.conf.sample
--- b/src/backend/libpq/pg_ident.conf.sample
***************
*** 5,22 ****
# Authentication" for a complete description. A short synopsis
# follows.
#
! # This file controls PostgreSQL ident-based authentication. It maps
! # ident user names (typically Unix user names) to their corresponding
# PostgreSQL user names. Records are of the form:
#
! # MAPNAME IDENT-USERNAME PG-USERNAME
#
# (The uppercase quantities must be replaced by actual values.)
#
# MAPNAME is the (otherwise freely chosen) map name that was used in
! # pg_hba.conf. IDENT-USERNAME is the detected user name of the
# client. PG-USERNAME is the requested PostgreSQL user name. The
! # existence of a record specifies that IDENT-USERNAME may connect as
# PG-USERNAME. Multiple maps may be specified in this file and used
# by pg_hba.conf.
#
--- 5,22 ----
# Authentication" for a complete description. A short synopsis
# follows.
#
! # This file controls PostgreSQL username mapping. It maps
! # external user names to their corresponding
# PostgreSQL user names. Records are of the form:
#
! # MAPNAME SYSTEM-USERNAME PG-USERNAME
#
# (The uppercase quantities must be replaced by actual values.)
#
# MAPNAME is the (otherwise freely chosen) map name that was used in
! # pg_hba.conf. SYSTEM-USERNAME is the detected user name of the
# client. PG-USERNAME is the requested PostgreSQL user name. The
! # existence of a record specifies that SYSTEM-USERNAME may connect as
# PG-USERNAME. Multiple maps may be specified in this file and used
# by pg_hba.conf.
#
***************
*** 28,35 ****
# Put your actual configuration here
# ----------------------------------
#
! # No map names are defined in the default configuration. If all ident
# user names and PostgreSQL user names are the same, you don't need
# this file.

! # MAPNAME IDENT-USERNAME PG-USERNAME
--- 28,35 ----
# Put your actual configuration here
# ----------------------------------
#
! # No map names are defined in the default configuration. If all system
# user names and PostgreSQL user names are the same, you don't need
# this file.

! # MAPNAME SYSTEM-USERNAME PG-USERNAME
*** a/src/include/libpq/hba.h
--- b/src/include/libpq/hba.h
***************
*** 25,37 **** typedef enum UserAuth
uaCrypt,
uaMD5,
uaGSS,
! uaSSPI
! #ifdef USE_PAM
! ,uaPAM
! #endif /* USE_PAM */
! #ifdef USE_LDAP
! ,uaLDAP
! #endif
} UserAuth;

typedef enum ConnType
--- 25,33 ----
uaCrypt,
uaMD5,
uaGSS,
! uaSSPI,
! uaPAM,
! uaLDAP
} UserAuth;

typedef enum ConnType
***************
*** 51,58 **** typedef struct
struct sockaddr_storage addr;
struct sockaddr_storage mask;
UserAuth auth_method;
char *usermap;
! char *auth_arg;
} HbaLine;

typedef struct Port hbaPort;
--- 47,60 ----
struct sockaddr_storage addr;
struct sockaddr_storage mask;
UserAuth auth_method;
+
char *usermap;
! char *pamservice;
! bool ldaptls;
! char *ldapserver;
! int ldapport;
! char *ldapprefix;
! char *ldapsuffix;
} HbaLine;

typedef struct Port hbaPort;
***************
*** 64,71 **** extern void load_role(void);
extern int hba_getauthmethod(hbaPort *port);
extern bool read_pg_database_line(FILE *fp, char *dbname, Oid *dboid,
Oid *dbtablespace, TransactionId *dbfrozenxid);
! extern bool check_ident_usermap(const char *usermap_name,
! const char *pg_role, const char *ident_user);
extern bool pg_isblank(const char c);

#endif /* HBA_H */
--- 66,74 ----
extern int hba_getauthmethod(hbaPort *port);
extern bool read_pg_database_line(FILE *fp, char *dbname, Oid *dboid,
Oid *dbtablespace, TransactionId *dbfrozenxid);
! extern int check_usermap(const char *usermap_name,
! const char *pg_role, const char *auth_user,
! bool case_sensitive);
extern bool pg_isblank(const char c);

#endif /* HBA_H */
This patch changes the options field of pg_hba.conf to take name/value
pairs instead of a fixed string. This makes it a lot nicer to deal with
auth methods that need more than one parameter, such as LDAP.

While at it, it also adds map support to kerberos, gssapi and sspi and
not just ident - basically all methods where the username comes from an
outside source (lmk if I missed one).

Also in passing, changes the methods in auth.c to deal with "unsupported
auth method on this platform" errors the same way for all authentication
methods.

I intend to build on this patch to support setting some
Kerberos/GSSAPI/SSPI parameters on a per-connection base, but wanted to
get the basics in first.

Obviously, documentation still pending. I'm working on that in parallel.

So, comments? Both in general, and specifically on if we need to do
backwards compatible parsing of LDAP options (doing it of all the other
options would be trivial, but LDAP would be harder)

//Magnus

Re: [HACKERS] Block-level CRC checks

2008-09-30T11:33:00.000-07:00

On Tue, Sep 30, 2008 at 2:02 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> A customer of ours has been having trouble with corrupted data for some
> time. Of course, we've almost always blamed hardware (and we've seen
> RAID controllers have their firmware upgraded, among other actions), but
> the useful thing to know is when corruption has happened, and where.

Agreed.

> So we've been tasked with adding CRCs to data files.

Awesome.

This is the common case.

I've supported this forever!

> The implementation I'm envisioning requires the use of a new relation
> fork to store the per-block CRCs. Initially I'm aiming at a CRC32 sum
> for each block. FlushBuffer would calculate the checksum and store it
> in the CRC fork; ReadBuffer_common would read the page, calculate the
> checksum, and compare it to the one stored in the CRC fork.
>
> A buffer's io_in_progress lock protects the buffer's CRC. We read and
> pin the CRC page before acquiring the lock, to avoid having two buffer
> IO operations in flight.

If the CRC gets written before the block, how is recovery going to
handle it? I'm not too familiar with the new forks stuff, but
recovery will pull the old block, compare it against the checksum, and
consider the block invalid, correct?

> I'd like to submit this for 8.4, but I want to ensure that -hackers at
> large approve of this feature before starting serious coding.

IMHO, this is a functionality that should be enabled by default (as it
is on most other RDBMS). It would've prevented severe corruption in
the 20 or so databases I've had to fix, and other than making it
optional, I don't see the reasoning for a separate relation fork
rather than storing it directly on the block (as everyone else does).
Similarly, I think Greg Stark was playing with a patch for it
(http://archives.postgresql.org/pgsql-hackers/2007-02/msg01850.php).

--
Jonah H. Harris, Senior DBA
myYearbook.com

--
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] Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Encuesta para análisis para el proyecto de creación de una certificación para PostgreSQL

2008-09-30T11:31:00.000-07:00

----- Original Message -----
From: "Jaime Casanova" <jcasanov@systemguards.com.ec>
To: "Edwin Quijada" <listas_quijada@hotmail.com>
Cc: <hermeszambra@yahoo.com>; "POSTGRES" <pgsql-es-ayuda@postgresql.org>
Sent: Tuesday, September 30, 2008 12:50 PM
Subject: Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Encuesta para análisis
para el proyecto de creación de una certificación para PostgreSQL

2008/9/30 Edwin Quijada <listas_quijada@hotmail.com>:
>
> Bueno, a pesar de todo creo que este es un buen paso para la comunidad.
> Por lo pronto quiero esa certificacion y
> estoy dispuesto hasta viajar para hacerla.
>
>la idea es que no haya necesidad de viajar
>

Que bueno !!!

Desde hace tiempo la comunidad venía pidiéndolo. Tan pronto me enteré
realicé la encuenta.

Yo sé que tener una certificación no es garantía de que se sabe sobre algo,
pero por lo menos a los empresarios les da algo de confianza. No es camisa
de fuerza certificarse quine no le guste simplemente no lo hace.

Atentamente,

RAUL DUQUE
Bogotá, Colombia

>--
>Atentamente,
>Jaime Casanova
>Soporte y capacitación de PostgreSQL
>Asesoría y desarrollo de sistemas
>Guayaquil - Ecuador
>Cel. +59387171157
>--
>TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo
>agradecerán

--
TIP 7: no olvides aumentar la configuración del "free space map"

Re: [GENERAL] Alias name from subquery

2008-09-30T11:19:00.000-07:00

I think you should look at crosstab contrib module.

Regards,
Taras Kopets

On 9/30/08, Felix Homann <fexpop@onlinehome.de> wrote:
> Hi Sam,
>
> Sam Mason wrote:
>> I think you may be trying to solve the wrong problem, what are you
>> really trying to do?
>
> Here you go. I have some tables created like this:
>
> CREATE TABLE player(
> id INTEGER PRIMARY KEY,
> name varchar(20)
> );
>
> CREATE TABLE transfer(
> id SERIAL PRIMARY KEY,
> fromID INTEGER REFERENCES player(id),
> toID INTEGER REFERENCES player(id),
> amount numeric
> );
>
> Now, let's fill them with some data:
>
> INSERT INTO player VALUES ('1', 'Peter'), ('2','David'), ('3','Fritz');
> INSERT INTO transfer(fromID, toID, amount) VALUES ('1','2','3'), ('1',
> '3', '1'), ('2','1','60');
>
> I would now like to have something like a view that transforms the table
> "transfer" from this:
>
> test=# SELECT * from transfer;
> id | fromid | toid | amount
> ----+--------+------+--------
> 1 | 1 | 2 | 3
> 2 | 1 | 3 | 1
> 3 | 2 | 1 | 60
>
>
> into this:
>
> id | Peter | David | Fritz | ...even more Names from player table
> ---+-------+-------+-------+-------------------------------------
> 1 | -3 | 3 | 0 | 0
> 2 | -1 | 0 | 1 | 0
> 3 | 60 | -60 | 0 | 0
>
>
> In other words, I would like to have a named column for every Name in
> the player table. I _can_ create such a view manually if I know each
> player.name beforehand, but I don't know how to automate it for any
> given number of players. (Think of something like a sparse interaction
> matrix representation.)
>
> Maybe it's the "wrong" problem I'm trying to solve, but at least I would
> like to know whether it's possible or not.
>
> Kind regards,
>
> Felix
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Sent from Gmail for mobile | mobile.google.com

--
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] Modificar WHERE en rule

2008-09-30T11:14:00.000-07:00

Finalmente he conseguido solucionarlo con una funcion.

Gracias a todos los que me contestaron.

--
*****************************************
Oswaldo Hernández
oswaldo (@) soft-com (.) es
*****************************************
PD:
Antes de imprimir este mensaje, asegúrese de que es necesario.
El medio ambiente está en nuestra mano.
--
TIP 8: explain analyze es tu amigo

Re: [ADMIN] turning of pg_xlog

2008-09-30T11:13:00.000-07:00

Jonny wrote:
> Hi,
>
> I have installes Postgres 8.0.15 on a embedded Linux and have only 130
> MB for Postgres.
> Is it possible to turn off the comlete (Wal) pg_xlog? Because this is
> the biggest part.
> I found an Entry how to minimize the count of stored xlogs.
> Is it possible to store it to /dev/null or something else?
> Something like ln -s /dev/null pg_xlog/ (I know this does not work
> :-) ....
You should also understand that if you set checkpoint_segments to a
small number, its still possible that PostgreSQL might use more than
that number for a large transaction. As such, be careful about limiting
the space available to your xlogs, lest it cause transactions to fail
due to insufficient space for the xlogs.

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

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

2008-09-30T11:03:00.000-07:00

Greg Stark <greg.stark@enterprisedb.com> writes:
> On 24 Sep 2008, at 02:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The next big
>> thing seems to be to figure out exactly how to do multiple references
>> to CTE outputs, so that we can de-bogotify the planner.

> I've looked and don't seem to still have the source tree where I
> worked on this. I remember how I made the changes to tuplestore which
> was mostly mechanical. The trick I think will be in adding a special
> purpose executor method which passes the call site to the node below.
> This depends on the observation that if we always memoize results then
> each call site can only have one active call. That is, we don't need
> to maintain a full stack tree.

I looked at the tuplestore code a bit and decided that this actually
doesn't need to be hard at all. Tuplestore already has a notion of a
write position and an independent read position, and we don't need more
than one write position. So what I think we should do is add "get read
position" and "set read position" functions to tuplestore.c, and have
each of the reader nodes remember its own read position. That is,
each reader has to do

set_read_position(tupstore, &local_read_position);
tuple = tuplestore_gettuple(tupstore, ...);
get_read_position(tupstore, &local_read_position);

rather than just tuplestore_gettuple. The set/get functions will be
cheap enough that this is no big deal. (Or maybe we should just
provide a wrapper function that does this sequence?)

regards, tom lane

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

[HACKERS] Block-level CRC checks

2008-09-30T11:02:00.000-07:00

A customer of ours has been having trouble with corrupted data for some
time. Of course, we've almost always blamed hardware (and we've seen
RAID controllers have their firmware upgraded, among other actions), but
the useful thing to know is when corruption has happened, and where.

So we've been tasked with adding CRCs to data files.

The idea is that these CRCs are going to be checked just after reading
files from disk, and calculated just before writing it. They are
just a protection against the storage layer going mad; they are not
intended to protect against faulty RAM, CPU or kernel.

This code would be run-time or compile-time configurable. I'm not
absolutely sure which yet; the problem with run-time is what to do if
the user restarts the server with the setting flipped. It would have
almost no impact on users who don't enable it.

The implementation I'm envisioning requires the use of a new relation
fork to store the per-block CRCs. Initially I'm aiming at a CRC32 sum
for each block. FlushBuffer would calculate the checksum and store it
in the CRC fork; ReadBuffer_common would read the page, calculate the
checksum, and compare it to the one stored in the CRC fork.

A buffer's io_in_progress lock protects the buffer's CRC. We read and
pin the CRC page before acquiring the lock, to avoid having two buffer
IO operations in flight.

I'd like to submit this for 8.4, but I want to ensure that -hackers at
large approve of this feature before starting serious coding.

Opinions?

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

--
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] Can't cast from char to integer...

2008-09-30T11:00:00.000-07:00

On Tuesday 30 September 2008 10:46:46 am Merlin Moncure wrote:
> On Mon, Sep 29, 2008 at 8:02 PM, Mike Diehl <mdiehl@diehlnet.com> wrote:
> > That fixed it. If you are ever in Albuquerque, NM., let me know. I'll
> > be happy to buy you a beer.
>
> Tom probably has enough beers coming to him that he could found a new
> software company with money from returning the bottles for the
> deposit.
>
> merlin

Yes, and rightfully so. Offer still stands.

Take care and have fun.
--
Mike Diehl

--
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] Re: [pgsql-es-ayuda] Encuesta para análisis para el proyecto de creación de una certificación para PostgreSQL

2008-09-30T10:54:00.001-07:00

Quieras o no algun día abra que hacerlo cuando la capacitación quede corta.
A mi me gusta viajar :)

2008/9/30 Jaime Casanova <jcasanov@systemguards.com.ec>:
> 2008/9/30 Edwin Quijada <listas_quijada@hotmail.com>:
>>
>> Bueno, a pesar de todo creo que este es un buen paso para la comunidad. Por lo pronto quiero esa certificacion y
>> estoy dispuesto hasta viajar para hacerla.
>
> la idea es que no haya necesidad de viajar
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
> --
> TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán
>
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
http://archives.postgresql.org/pgsql-es-ayuda

Re: [BUGS] BUG #4444: Wrong formated pdf manual

2008-09-30T10:54:00.000-07:00

Dmitry Orlov wrote:
> when I run evince to read manual i got error
> evince /home/dorlov/Books/Postgresql/postgresql-8.3-A4.pdf
> **
> **
> ERROR:(/build/buildd/evince-2.22.2/./shell/ev-sidebar-thumbnails.c:248):add_
> range: assertion failed: (start_page <= end_page)
> Aborted

This looks like a bug in evince, please report to the Evince team.

It's possible that there's something wrong in the PDF that triggers that
bug in evince, but an assertion failure shouldn't nevertheless happen.

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

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

Re: [GENERAL] Alias name from subquery

2008-09-30T10:53:00.000-07:00

On Tue, Sep 30, 2008 at 11:45 AM, Felix Homann <fexpop@onlinehome.de> wrote:
> Hi Sam,
> In other words, I would like to have a named column for every Name in the
> player table. I _can_ create such a view manually if I know each player.name
> beforehand, but I don't know how to automate it for any given number of
> players. (Think of something like a sparse interaction matrix
> representation.)

Ahhh, you might do better with crosstab functions then. look up the
tablefunc contrib module.

--
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] Re: [pgsql-es-ayuda] Encuesta para análisis para el proyecto de creación de una certificación para PostgreSQL

2008-09-30T10:50:00.000-07:00

2008/9/30 Edwin Quijada <listas_quijada@hotmail.com>:
>
> Bueno, a pesar de todo creo que este es un buen paso para la comunidad. Por lo pronto quiero esa certificacion y
> estoy dispuesto hasta viajar para hacerla.

la idea es que no haya necesidad de viajar

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [GENERAL] Alias name from subquery

2008-09-30T10:45:00.000-07:00

Hi Sam,

Sam Mason wrote:
> I think you may be trying to solve the wrong problem, what are you
> really trying to do?

Here you go. I have some tables created like this:

CREATE TABLE player(
id INTEGER PRIMARY KEY,
name varchar(20)
);

CREATE TABLE transfer(
id SERIAL PRIMARY KEY,
fromID INTEGER REFERENCES player(id),
toID INTEGER REFERENCES player(id),
amount numeric
);

Now, let's fill them with some data:

INSERT INTO player VALUES ('1', 'Peter'), ('2','David'), ('3','Fritz');
INSERT INTO transfer(fromID, toID, amount) VALUES ('1','2','3'), ('1',
'3', '1'), ('2','1','60');

I would now like to have something like a view that transforms the table
"transfer" from this:

test=# SELECT * from transfer;
id | fromid | toid | amount
----+--------+------+--------
1 | 1 | 2 | 3
2 | 1 | 3 | 1
3 | 2 | 1 | 60

into this:

id | Peter | David | Fritz | ...even more Names from player table
---+-------+-------+-------+-------------------------------------
1 | -3 | 3 | 0 | 0
2 | -1 | 0 | 1 | 0
3 | 60 | -60 | 0 | 0

In other words, I would like to have a named column for every Name in
the player table. I _can_ create such a view manually if I know each
player.name beforehand, but I don't know how to automate it for any
given number of players. (Think of something like a sparse interaction
matrix representation.)

Maybe it's the "wrong" problem I'm trying to solve, but at least I would
like to know whether it's possible or not.

Kind regards,

Felix

--
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] Can't cast from char to integer...

2008-09-30T10:39:00.000-07:00

On Tue, Sep 30, 2008 at 10:46 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Sep 29, 2008 at 8:02 PM, Mike Diehl <mdiehl@diehlnet.com> wrote:
>> That fixed it. If you are ever in Albuquerque, NM., let me know. I'll be
>> happy to buy you a beer.
>>
>
> Tom probably has enough beers coming to him that he could found a new
> software company with money from returning the bottles for the
> deposit.

Tom will never lack for pizza or beer when he's visiting Denver...

--
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] MySQL to Postgresql schema conversion

2008-09-30T10:37:00.000-07:00

On Tue, Sep 30, 2008 at 1:18 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Sep 30, 2008 at 12:48 PM, Sean Davis <sdavis2@mail.nih.gov> wrote:
>> On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis <sdavis2@mail.nih.gov> wrote:
>>> There are a number of mysql to postgresql converters available, but
>>> many of them have significant shortcomings. Has anyone found a tool
>>> that works well? I am trying to convert a couple of relatively large,
>>> public schema to postgresql.
>>
>> I started playing with sqlalchemy (python) which can reflect a schema
>> to python objects. Those objects can then be used to instantiate
>> another schema in a different database dialect. Works like a charm
>> after modifying a couple of column names. It mirrors about 4000
>> tables in about 45 seconds (of course, without the data).
>
>
> Does it get all the various constraints and stuff (if any)? Simple
> field to field copy techniques only tends to work if the database only
> uses a small subset of common features. Great for you if it works
> though.

To the extent that the MySQL databases used anything interesting
(defaults, basically), it seems to, yes. I have used it for other
projects as an ORM and it seems to support pretty much anything I can
dream up on the postgres side for DDL.

Sean

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

[ANNOUNCE] PostgreSQL Conference West: Tentative Schedule Released!

2008-09-30T09:56:00.000-07:00

The tentative schedule for PostgreSQL Conference West can be found here:

http://www.pgcon.us/west08/schedule

If you haven't registered, now is the time:

http://www.postgresqlconference.org/west08/register

And of course, thanks to our sponsors!

Command Prompt: http://www.commandprompt.com/
EnterpriseDB: http://www.enterprisedb.com/

Afilias : http://www.afilias.info/
HP: http://www.hp.com/

Emma : http://www.myemma.com/

Continuent : http://www.continuent.com/
Endpoint : http://www.endpoint.com/
OTG : http://www.otg-nc.com/

EFF: http://www.eff.org/
Google: http://www.google.com/

Joshua D. Drake

---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:

pgsql-announce-unsubscribe@postgresql.org

Re: [NOVICE] plpgsql functions vs. embedded queries

2008-09-29T16:07:00.000-07:00

"Wright, George" <George.Wright@infimatic.com> writes:
>> Are funtions in plpgsql always slower than embedding sql queries in
>> string form in code like PHP?
> The functions took on average more than 10 times as long.

That suggests that you're getting a radically different, less efficient
plan for the "same" query inside a function. The exact reasons why are
hard to diagnose without a concrete example, but usually the story has
to do with comparing parameterized queries inside a function to
not-parameterized queries elsewhere. There are various workarounds
but the best choice depends on details you've not shown us.

regards, tom lane

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

Re: [GENERAL] pg_start_backup() takes too long

2008-09-29T16:06:00.000-07:00

Simon Riggs wrote:
> > If it is a bug then I'd vote for just making it do an immediate
> > checkpoint --- that might cause big I/O load but it's hardly likely to
> > be worse than what will happen when you start taking the subsequent
> > filesystem backup.
>
> It was a clear intention for it to *not* cause a spike if we could avoid
> it. The idea was if you wanted it to happen quickly then you could do a
> checkpoint command first... oh well.
>
> People might want to I/O limit the backup also, which they can do
> without needing to let us know.
>
> I'm happy to put an option in for this, so we have another function:
> pg_start_backup(label text, immediate_chkpt boolean). I'll not be
> rushing to do this though given my current TODO.

I agree with Tom; either we make the pg_start_backup() checkpoint
immediate or leave the behavior unchanged.

Personally I think immediate makes more sense because issuing
pg_start_backup() seems like it should behave like a manual CHECKPOINT
command.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

Re: [ADMIN] Do we need vacuuming when tables are regularly dropped?

2008-09-29T15:45:00.000-07:00

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> Also, there was a time when you couldn't do vacuum full on system
> tables do to locking issues, and had to take the db down to single
> user mode to do so.

There was a short period when *concurrent* vacuum fulls on just the
wrong combinations of system catalogs could deadlock (because they both
needed to look up stuff in the other one). AFAIK we fixed that. It's
never been the case that it didn't work at all.

regards, tom lane

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

[pgsql-jobs] Recruiting Consulting - Web 2.0 & Online Casual Games Specialist

2008-09-29T15:42:00.001-07:00

Hi,

I am looking for top LAMP Developers & Architects (front-end/UI and back-end) for exciting online casual game companies and Web 2.0 companies located in SF & San Mateo. All companies offer top pay/benefits and opportunities.

If you would like to know more, please email me or call.

p.s. I bring lots of recruiting expertise having staffed Stormfront Studios award-winning Dev Team for 10+ years and most recently working with Technorati, as an in-house contract recruiter, in hiring for their dev team.

Best,

Marta Daglow
mdaglow@daglowconsulting.com

Cell 415-461-5845

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

[pgsql-es-ayuda] Encuesta para análisis para el proyecto de creación de una certificación para PostgreSQL

2008-09-29T15:42:00.000-07:00

Anuncio original (en inglés):
http://archives.postgresql.org/pgsql-advocacy/2008-09/msg00059.php

--------

Hola,

La comunidad de PostgreSQL está trabajando para crear una
certificación de PostgreSQL manejada y respaldada por la comunidad.
Este esfuerzo está encabezado por el Proyecto de Certificación
PostgreSQL (http://www.postgresqlcertification.org). El objetivo
principal del proyecto es el desarrollo y apoyo de una serie de
certificaciones en niveles que proporcionen un método normalizado de
identificación de los conocimientos y aptitudes sobre bases de datos
de los profesionales que trabajan con PostgreSQL.

En este momento, el proyecto de certificación de PostgreSQL se
complace en anunciar la disponibilidad de una encuesta que ayudará a
conformar el contenido y la forma inicial en que se llevaran las
certificaciones. Esta encuesta está a disposición del público y se les
anima a todos a participar.

Para participar en la encuesta, por favor registrese en:
http://www.postgresqlcertification.org/jta

El proceso de registro tomá solo un par de minutos. Una vez que este
registrado y haya ingresado, haga clic en el menú JTA o dirigase al
enlace marcado como "participate in the survey"
(http://www.postgresqlcertification.org/job_task_analysis). La
encuesta toma unos 30 minutos.

Pedimos que por favor, se tomen el tiempo de completar la encuesta y
que refieran esta encuesta a otro empleador, administrador, colega, o
a cualquiera que depende de algún modo de una base de datos PostgreSQL
que tome tambien la encuesta. Con la ayuda de la comunidad podremos
llegar a una audiencia lo mas amplia posible.

Si usted está interesado en unirse al Proyecto de Certificación para
PostgreSQL, por favor visite
http://lists.postgresqlcertification.org/mailman/listinfo/cert/ y
suscribase a la lista de correos despues de tomar la encuesta.

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
TIP 5: ¿Has leído nuestro extenso FAQ?
http://www.postgresql.org/docs/faqs.FAQ.html

[COMMITTERS] npgsql - Npgsql2: [npgsql-help][1004020] Backend sent unrecognized

2008-09-29T15:41:00.001-07:00

Log Message:
-----------

[npgsql-help][1004020] Backend sent unrecognized response type: q

References for previous patch: http://pgfoundry.org/forum/message.php?msg_id=1004022.

Modified Files:
--------------
Npgsql2/src/Npgsql:
NpgsqlError.cs (r1.5 -> r1.6)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/Npgsql/NpgsqlError.cs.diff?r1=1.5&r2=1.6)

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

[COMMITTERS] npgsql - Npgsql2: Fixed error message: Backend sent unrecognized

2008-09-29T15:38:00.000-07:00

Log Message:
-----------

Fixed error message: Backend sent unrecognized response type: q. There was an error on Error message handling where we were letting some fields without processing. Thanks Eric Montague (eric @nospam@ nuws.com) for heads up and tests.

Modified Files:
--------------
Npgsql2/src/Npgsql:
NpgsqlError.cs (r1.4 -> r1.5)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/Npgsql/NpgsqlError.cs.diff?r1=1.4&r2=1.5)

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

Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches

2008-09-29T15:26:00.000-07:00

KaiGai Kohei wrote:
> As I repeated several times, SE-PostgreSQL applies the seuciry policy
> of SELinux to achieve consistency in access controls. This feature
> enables to restrict client's privileges on accesses to database objects,
> as if it accesses to filesystem objects. Its background is our nightmare
> for web application flaws.
>
> The major purpose of this feature is to provide the most important
> component to run enterprise class web application with least privilege
> set which is consistent at whole of the system.

How important is this consistency goal in reality? We typically
recommend that database applications run entirely in the database, for
transaction integrity reasons and so on. Unless you are doing wild and
fun things with server-side copy or untrusted procedural languages,
there really shouldn't be that much use for consistency of access
control between PostgreSQL and something else. In fact, on top of the
transactional integrity criterion, having consistent access control is
one of the reasons to have all your production data in the database
system and nowhere else.

Of coure, this is an ideal state, and we all of to break that once in a
while. But hence the honest question, how often does that really happen
and to what extent, and does that justify the significant investment
that is being proposed here?

> In recent years, web application flaws are nightmare for us.
> The recent report said 95% of significant incidents on the first
> half of 2008, and 75% of them were SQL injection in Japan.
> My ultimate goal is to help the situation with mandatory access
> control and least priviled set for whole of LAPP stack.

As I had previously mentioned, we have to distinguish these two goals:
consistent access controls and mandatory access controls.

Then, how does MAC help with SQL injections? Using the existing
role-based system you can already define least-privilege users that are
essentially powerless even if SQL injections were to happen. I am not
aware that important flaws or gaps in our role-based access control
system have been pointed out that would make it impossible to create
applications with security levels similar to those achievable with a MAC
system.

Now, if you come back to your original goal of consistency in access
control, then it may in fact turn out that an
FLASK/SELinux/SE-PostgreSQL-based system is the best common ground for
such a system, but I am unconvinced that MAC by itself is necessary.

--
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] database question

2008-09-29T15:14:00.000-07:00

On Mon, 29 Sep 2008, john.crawford@sirsidynix.com wrote:

> What are these files and why have they suddenly started to be created
> and why so large?

They're the contents of the database and they get created every time there
is another 1GB worth of data in there. Note that the database will use
more space if data is being UPDATEd and you don't vacuum it regularly.
Without the vacuum going it's as if you'd added a new row instead when you
update something.

While it's possible to decode what those files are by using oid2name or
pg_class, what you probably want to know instead is what the big tables
and indexes in your database are to figure out what is gobbling space.
The script at http://wiki.postgresql.org/wiki/Disk_Usage will give you
that.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] Sample databases

2008-09-29T15:11:00.000-07:00

Tommy Gibbons wrote:

>I would like some pointers as to how to install the dbsamples so that I can use them in Postgres. These .tar.qz files seem to contain *.sql files. These seem to be text files but I do not understand how to > import to postgres or if there is some command line command to run.

> These samples are on http://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/

You can use psql to import .sql files. Once your logged into psql you can \i <filename> to import the files. Have a look at the other options too with \?

You may wish to create a database before importing the files, see http://www.postgresql.org/docs/8.3/interactive/sql-createdatabase.html

David.

Re: [GENERAL] Sample databases

2008-09-29T15:09:00.000-07:00

On 29/09/2008 23:03, Tommy Gibbons wrote:

> I would like some pointers as to how to install the dbsamples so that I
> can use them in Postgres. These .tar.qz files seem to contain *.sql
> files. These seem to be text files but I do not understand how to
> import to postgres or if there is some command line command to run.

You use psql to load them:

psql -U <user> -f <file> <database>

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

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

[GENERAL] Sample databases

2008-09-29T15:03:00.001-07:00

Hi,
I would like some pointers as to how to install the dbsamples so that I can use them in Postgres. These .tar.qz files seem to contain *.sql files. These seem to be text files but I do not understand how to import to postgres or if there is some command line command to run.

These samples are on http://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/

Tommy.

Re: [GENERAL] subquery in FROM must have an alias

2008-09-29T14:58:00.000-07:00

Thanks to Stephan and Hubert for their replies. Using your answers I
was able to solve the problem. It turned out that its a natural join
that I wanted.

Thanks for quick help,
Ashutosh

On Sun, Sep 28, 2008 at 10:18, Stephan Szabo
<sszabo@megazone.bigpanda.com> wrote:
> On Sun, 28 Sep 2008, Ashutosh Chauhan wrote:
>
>> Hi all,
>>
>> This has been asked before and answered as well.
>> http://archives.postgresql.org/pgsql-sql/2007-12/msg00002.php but I
>> still cant figure out why postgres throws this error message even when
>> I have provided the aliases. My query:
>>
>> select a,b
>> from (billing.item JOIN (
>> select *
>> from ( billing.invoice JOIN billing.customer
>> on (id_customer_shipped = customer_uid and
>> address = 'pgh' ))
>> as temp2 ))
>> as temp;
>>
>> I have two from clauses so I have provided two corresponding alias
>> names for those two from clauses.
>
> If you break the above down a bit, you have:
>
> select a,b
> from
> (
> billing.item join
> (select * from
> (
> billing.invoice join
> billing.customer
> on (id_customer_shipped = customer_uid and address='pgh')
> )
> as temp2
> )
> )
> as temp;
>
> What the system is complaining about is the subselect (select * from ... )
> not having an alias. You've aliased the billing.invoice join
> billing.customer one and (billing.item join (...)) one, but not the
> subselect. In fact, I believe the two aliases you're using aren't strictly
> necessary. Also, the above appears to be missing the condition for the
> outermost join.
>
> Maybe something like the following will work with a filled in on
> condition:
>
> select a,b
> from
> (
> billing.item join
> (select * from
> (
> billing.invoice join
> billing.customer
> on (id_customer_shipped = customer_uid and address='pgh')
> )
> )
> as temp
> on (...)
> )
>
>
>

--
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] PostgreSQL Cache

2008-09-29T14:53:00.000-07:00

On Mon, 29 Sep 2008, Sam Mason wrote:

> On Mon, Sep 29, 2008 at 02:55:52AM -0400, Greg Smith wrote:
>> 3) sudo echo 3 > /proc/sys/vm/drop_caches
>
> I'm not sure about the rest, but shouldn't this be:
> echo 3 | sudo tee /proc/sys/vm/drop_caches

I couldn't think of any reason to actually include the tee in there and
just optimized displaying the "3" out as script noise.

> As an aside, it would be nicer if there was a more appropriately program
> than tee but I've yet to find one.

What are you trying to accomplish here that tee isn't quite right for?

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] pg_start_backup() takes too long

2008-09-29T14:49:00.000-07:00

On Mon, 29 Sep 2008, Simon Riggs wrote:

> I'm surprised that checkpoint smoothing moves slowly even when it has so
> little to do. ISTM checkpoint completion target should set its write
> rate according to the thought that if shared_buffers were all dirty it
> would write them out in checkpoint_timeout *
> checkpoint_completion_target seconds. However, what it does is write
> them *all* out in that time, no matter how many dirty blocks there are.
> If there is just a few blocks to write, we take the *same* time to write
> them as if it were all dirty.

The checkpoint smoothing code that made it into 8.3 missed a couple of
nice to have features that just didn't make the schedule cut-off.
Enforcing a minimum rate was one, another was smoothing fsync calls.

Back when we were talking about the patch to sort writes at checkpoint
time, someone (I think you actually) commented that it might be worthwile
to create some sort of hook for making behavior of checkpoint-time dirty
buffer processing easy to change with a custom strategy. The sorted
behavior would then be the first such strategy available. Another one I
was thinking of was something that specified min+max write writes, which
would make this problem go away--might even auto-tune checkpoint_segments
or replace it altogether with an implementation based on those inputs.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] ERROR: unexpected data beyond EOF in block XXXXX of relation "file"

2008-09-29T14:47:00.000-07:00

David Fetter wrote:
> On Sun, Sep 28, 2008 at 11:51:49AM -0700, austijc wrote:
>> That's going to be a problem for the continued viability of
>> Postgres.
>
> Funny, I thought running a DBMS over a known-unreliable storage system
> was a problem for the continued viability of Oracle. When, not if,
> people lose enough data to this silliness, they'll be thinking hard
> about how to get Oracle out and something reliable in.

NFS is not "unreliable", it is just different in some respects from
other file systems. That paired with some poor NFS implementations in
certain operating systems and this evident general misunderstanding make
it a poor fit for PostgreSQL.

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

Re: [GENERAL] [HACKERS] PostgreSQL future ideas

2008-09-29T14:27:00.000-07:00

2008/9/27 Douglas McNaught <doug@mcnaught.org>:
> On Sat, Sep 27, 2008 at 12:13 PM, Mark Mielke <mark@mark.mielke.cc> wrote:
>> If
>> some parts of PostgreSQL are not performance bottlenecks, and they are
>> extremely complicated to write in C, and very easy to write in something
>> else common and simple (I've never used LUA myself?), I imagine it would be
>> acceptable to the community.
>
> As long as they can expose their interfaces using the standard PG
> function call interface, and use the documented SPI mechanism to talk
> to the rest of the back end. Stuff that hooks into undocumented or
> unstable parts of the code would be much less viable.
>
> -Doug
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

I think that C is the best language for Postgresql. C++ has a little
thinks that make not good for performance.
Why people want to make more understable code touching the language?.
Simplify documentation for programmmers. Thats was the idea in the beggining.

--
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] PostgreSQL Spanish Documentation Project

2008-09-29T14:17:00.002-07:00

aca en bs as me fue imposible por razones que a esa hora estaba durmiendo....
jueguense! haganlo a las 13:00 hs UTC por lo menos! eran las 7 de la
madrugada aca... jjj
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

[pgeu-general] Fw: [Cert] Announcing PostgreSQL Certification Project Job Task Analysis Survey

2008-09-29T14:17:00.001-07:00

An FYI for our friends across the pond!

Begin forwarded message:

Date: Mon, 29 Sep 2008 11:19:05 -0700
From: "Gregory S. Youngblood" <greg@tcscs.com>
To: <cert@lists.postgresqlcertification.org>
Subject: [Cert] Announcing PostgreSQL Certification Project Job Task
Analysis Survey

Hello!

The PostgreSQL Community is working to create a community driven and
endorsed PostgreSQL Certification. This effort is spearheaded by the
PostgreSQL Certification Project
(http://www.postgresqlcertification.org). The primary focus of the
project is the development and support of a series of tiered
certifications that provide a standardized method of identifying the
knowledge and skills of database professionals working with PostgreSQL.

At this time, the PostgreSQL Certification Project is pleased to
announce the availability of a Job Task Analysis survey that will help
shape the content and form the initial certifications will take. This
survey is publicly available and all are encouraged to participate.

To take the survey, please register at:

http://www.postgresqlcertification.org/jta

The registration process takes just a couple of minutes. Once
registration is complete and you are logged in, click JTA in the menu
followed by "participate in the survey"
(http://www.postgresqlcertification.org/job_task_analysis). The survey
takes approximately 30 minutes to complete.

We ask everyone to please take the time to complete the survey and to
refer an employer, manager, colleague, or anyone else that may rely on a
PostgreSQL database in some manner to take it as well. With the help of
the community we will be able to reach as wide an audience as possible.

If you are interested in joining the PostgreSQL Certification Project,
please visit
http://lists.postgresqlcertification.org/mailman/listinfo/cert/ and
subscribe to the mailing list after taking the survey.

Fwd: [pgsql-es-ayuda] Tipo de dato para almacenar moneda

2008-09-29T14:11:00.000-07:00

---------- Forwarded message ----------
From: el-PRiNCiPiTo <el-PRiNCiPiTo@terra.es>
Date: 2008/9/29
Subject: Re: [pgsql-es-ayuda] Tipo de dato para almacenar moneda
To:
Cc: pgsql-es-ayuda@postgresql.org

Utilizo Npgsql.

postgres Emanuel CALVO FRANCO escribió::
>
> Estas utilizando OBDC u OLEDB?
> --
> TIP 8: explain analyze es tu amigo
>
>

--
TIP 8: explain analyze es tu amigo

Postgresql Type NpgsqlDbType System.DbType Enum .Net System Type
(..)
money Money Decimal Decimal

pgsql soporta al money como decimal, fijate si en la conversion con el
lenguaje no estas casteando a integer o a numeric (n,0).
esto es de la documentacion del 2.0, no se que version estas
utilizando de los conectores.
http://npgsql.projects.postgresql.org/docs/manual/UserManual.html
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [pgsql-es-ayuda] Sintaxis??: DROP TABLE IF EXISTS tablename

2008-09-29T14:06:00.000-07:00

Fernando Flores Barraza escribió:

> Lo cual me arroja el siguiente error:
> *ERROR: syntax error at or near "EXISTS" at character 15
> LINE 1: DROP TABLE IF EXISTS /tablename/;*
>
>
> La expresión *IF EXISTS* es solo para evitar un retorno de error, y en
> el manual aparece como funcional. Alguien sabe que pasa ?

Estas leyendo un manual que no corresponde a tu version de Postgres. IF
EXISTS sólo funciona de 8.2 en adelante.

--
Alvaro Herrera http://www.amazon.com/gp/registry/3BP7BYG9PUGI8
"Las mujeres son como hondas: mientras más resistencia tienen,
más lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith)
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

RE: [pgsql-es-ayuda] Sintaxis??: DROP TABLE IF EXISTS tablename

2008-09-29T14:00:00.000-07:00

-----Mensaje original-----
De: Fernando Flores Barraza [mailto:fernafb@gmail.com]
Enviado el: Lunes, 29 de Septiembre de 2008 03:44 p.m.
Para: pgsql-es-ayuda@postgresql.org.
Asunto: [pgsql-es-ayuda] Sintaxis??: DROP TABLE IF EXISTS tablename

Hola, hace una semana que estoy suscrito a esta lista y es bastante útil
por su rapidez a las respuestas.

Vamos al punto. Tengo una consulta sobre esta sintaxis:
*DROP TABLE IF EXISTS /tablename/;*

Lo cual me arroja el siguiente error:
*ERROR: syntax error at or near "EXISTS" at character 15
LINE 1: DROP TABLE IF EXISTS /tablename/;*

La expresión *IF EXISTS* es solo para evitar un retorno de error, y en
el manual aparece como funcional. Alguien sabe que pasa ?

( Si esta pregunta ha salido antes disculpen la redundancia.. )

Gracias

Fernando Flores B.
Coquimbo/Santiago - Chile
--
TIP 4: No hagas 'kill -9' a postmaster

ESTA ES LA SINTAXIS

DROP TABLE IF EXISTS table1

mIGUEL cANCHAS

--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

[pgsql-es-ayuda] Sintaxis??: DROP TABLE IF EXISTS tablename

2008-09-29T13:43:00.000-07:00

Hola, hace una semana que estoy suscrito a esta lista y es bastante útil
por su rapidez a las respuestas.

Vamos al punto. Tengo una consulta sobre esta sintaxis:
*DROP TABLE IF EXISTS /tablename/;*

Lo cual me arroja el siguiente error:
*ERROR: syntax error at or near "EXISTS" at character 15
LINE 1: DROP TABLE IF EXISTS /tablename/;*

La expresión *IF EXISTS* es solo para evitar un retorno de error, y en
el manual aparece como funcional. Alguien sabe que pasa ?

( Si esta pregunta ha salido antes disculpen la redundancia.. )

Gracias

Fernando Flores B.
Coquimbo/Santiago - Chile
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [GENERAL] Counting unique rows as an aggregate.

2008-09-29T13:25:00.000-07:00

On Mon, Sep 29, 2008 at 12:12 PM, r_musta <zepolen@gmail.com> wrote:
> However, this is starting to become too slow (as there are about 10 of
> these queries), and therefore I need to write an aggregate function
> which lets me do:
>
>>SELECT count_unique(make), count_unique(color) from table WHERE >criteria<;
>
> After reading about aggregate functions, this should be possible, as
> long as I can use a dictionary/hashmap type for the state<STYPE>
> argument.

This might be a nice fit for materialized views. While they're not
built in, PostgreSQL's extensibility allows you to build them prettily
easily.

http://jonathangardner.net/tech/w/PostgreSQL/Materialized_Views

Jonathan Gardner's web page on it is fantastic.

--
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] database question

2008-09-29T13:18:00.000-07:00

On Mon, Sep 29, 2008 at 8:21 AM, <john.crawford@sirsidynix.com> wrote:
> Hi all I have been experiencing some strange behaviour on my postgres
> DB. I am VERY new to PG so bear with me as what I am going to ask is
> all probably very basic to you guys.
> First off over the last couple of weeks we have been seeing in the
> dir /var/lib/pgsql/data/base/16450 some large file creations, so for
> example
>
> -rw------- 1 postgres postgres 1073741824 Sep 29 15:15 2683
> -rw------- 1 postgres root 1073741824 Sep 29 15:15 2613.77
> -rw------- 1 postgres root 1073741824 Sep 29 15:15 2613.83
>
> What are these files and why have they suddenly started to be created
> and why so large?

PostgreSQL automatically splits table files into 1G chunks so it can
run on OSes with file size limits. These are part of the table
identified by the oid 2613. You can find it by looking in pg_class.
Run psql -E and do \d and you'll see the queries that psql uses to
create its output, and you can muck about with them to see which are
which.

Also, the contrib module oid2name will tell you these things from the
shell / CLI.

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

Re: [JDBC] [GENERAL] need help of getting PK after insertRow in JDBC

2008-09-29T13:16:00.000-07:00

Good Afternoon

add the CLOSE_CURSORS_AT_COMMIT Option to the Statements capabilities e.g.
Statement st = db.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE.ResultSet.CLOSE_CURSORS_AT_COMMIT);

//then tell the connection handle to commit the DML to the DB
db.commit();

Martin

Control your own destiny or someone else will -- Jack Welch
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

Subject: [GENERAL] need help of getting PK after insertRow in JDBC
Date: Fri, 26 Sep 2008 10:14:01 +0800
From: Dongdong.Chen@ge.com
To: pgsql-general@postgresql.org; pgsql-jdbc@postgresql.org

Hi:

I am a software engineer from GE. I am using JDBC to operate PostgreSQL8.3 in Ubuntu8.04. The develop environment is Eclipse3.2 My problem is:

There is a PostgreSQL table XX containing 5 fields: AA, BB, CC, DD, EE, AA is primary key and auto-generated type, BB, CC, DD and EE is string type.

I want to get the value of AA immediately after insert a row into the table. the code is like this:

Statement st = db.creatStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs=st.executeQuery("SELECT * FROM XX");

rs.moveToInsertRow();

rs.updateString(BB, "b");

rs.updateString(CC, "c");

rs.updateString(DD, "d");

rs.updateString(EE, "e");

rs.insertRow();

rs.moveToCurrentRow();

int index = rs.getInt("AA");

System.out.println(index);

in the last sentence I always get 0 no matter how many records I insert. I tried other ways of moving the cursor including next(), last() and so on, and also cannot get the correct value. I tried the drivers both postgresql-8.3-603.jdbc3.jar and postgresql-8.3-603.jdbc4.jar.

But when I use pdadminIII to check the table XX, the AA field is already auto-generated with the correct value.

I found a way to solve this: close resultset and statement after moveToCurrentRow() and re-open them, and rs.last(), then run int index=rs.getInt("AA"), I can get the correct value. I think this method is sort of awkward, anyone knows a better way and the correct operations?

I am not sure it is proper to send this mail to this mail list. Sorry if bring you any inconvenience.

Thanks a lot!

Best Regards

Kevin Chen/ChenDongdong

+8613810644051

See how Windows connects the people, information, and fun that are part of your life. See Now

Re: [GENERAL] Re: Is there a parameter to tell postgresql to not attempt to open an IPv6 socket?

2008-09-29T13:15:00.000-07:00

On Mon, Sep 29, 2008 at 7:41 AM, <yann.dubost@gmail.com> wrote:
> On 16 sep, 23:04, a...@commandprompt.com (Andrew Sullivan) wrote:
>> On Tue, Sep 16, 2008 at 04:46:37PM -0400, Reid.Thompson wrote:
>> > PostgreSQL appears to all intents to startup fine on the UNIX and IPv4
>> > sockets. Is there a parameter to tell postgresql tonotattempt to open
>> > anIPv6socket?
>>
>> Specify the specific TCP/IP interfaces in the postmaster.conf file.
>> Otherwise, Postgres will try tobindto all the sockets. There's
>> something hinkey about theIPv6support in AIX, IIRC, so that you end
>> up with this symptom.
>>
>
> Hello,
>
> I have the same pb. I have looked for a postmaster.conf file but there
> is none on the server.
> Apart from the doc and src files, the only files on my server
> containing the word "postmaster" are :
> /usr/local/pgsql/bin/postmaster
> $PGDATA/postmaster.opts
> $PGDATA/postmaster.pid
>
> Do you have an idea why and how then to solve this pb ?

Look for postgresql.conf

--
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] parallel pg_restore - WIP patch

2008-09-29T12:54:00.000-07:00

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> Tom Lane wrote:
>>> Um, FKs could conflict with each other too, so that by itself isn't
>>> gonna fix anything.
>
>> Good point. Looks like we'll need to make a list of "can't run in
>> parallel with" items as well as strict dependencies.
>
> Yeah, I was just thinking about that. The current archive format
> doesn't really carry enough information for this. I think there
> are two basic solutions we could adopt:
>
> * Extend the archive format to provide some indication that "restoring
> this object requires exclusive access to these dependencies".
>
> * Hardwire knowledge into pg_restore that certain types of objects
> require exclusive access to their dependencies.
>
> The former seems more flexible, as well as more in tune with the basic
> design assumption that pg_restore shouldn't have a lot of knowledge
> about individual archive object types. But it would mean that you
> couldn't use parallel restore with any pre-8.4 dumps. In the long run
> that's no big deal, but in the short run it's annoying.

hmm not sure how much of a problem that really is - we usually recommend
to use the pg_dump version of the target database anyway.

Stefan

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

[HACKERS] pg_upgrade performance test

2008-09-29T12:42:00.000-07:00

I run performance test on in-place patch prototype which I sent for review and I
got nice result:

Original:
---------
MQThL (Maximum Qualified Throughput LIGHT): 2202.12 tpm
MQThM (Maximum Qualified Throughput MEDIUM): 4706.60 tpm
MQThH (Maximum Qualified Throughput HEAVY): 3956.64 tpm

TRANSACTION MIX

Total number of transactions = 330457
TYPE TX. COUNT MIX
---- --------- ---
Light: 55053 16.66%
Medium: 117665 35.61%
DSS: 36825 11.14%
Heavy: 98916 29.93%
Connection: 21998 6.66%

RESPONSE TIMES AVG. MAX. 90TH

Light 0.093 1.080 0.400
Medium 0.096 1.663 0.400
DSS 0.092 0.990 0.020
Heavy 0.095 1.262 2.000
Connections 0.092 1.030 0.400
Number of users = 60
Sum of Avg. RT * TPS for all Tx. Types = 17.154543

pg_upgrade:
-----------
MQThL (Maximum Qualified Throughput LIGHT): 2185.16 tpm
MQThM (Maximum Qualified Throughput MEDIUM): 4674.04 tpm
MQThH (Maximum Qualified Throughput HEAVY): 3915.28 tpm

TRANSACTION MIX

Total number of transactions = 327811
TYPE TX. COUNT MIX
---- --------- ---
Light: 54629 16.66%
Medium: 116851 35.65%
DSS: 36367 11.09%
Heavy: 97882 29.86%
Connection: 22082 6.74%

RESPONSE TIMES AVG. MAX. 90TH

Light 0.095 1.073 0.400
Medium 0.098 1.167 0.400
DSS 0.093 1.049 0.010
Heavy 0.098 1.173 2.000
Connections 0.097 1.115 0.400
Number of users = 60
Sum of Avg. RT * TPS for all Tx. Types = 17.385362

--------------------------

It looks like it has only 1% performance gap. And good to mention that it is not
optimized version. It would be good if somebody will run different performance
test on it and verify my results.

I used iGen OLTP test with 60 concurrent users and run it for 30minutes.

Zdenek

--
Zdenek Kotala Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql

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

[GENERAL] Can't cast from char to integer...

2008-09-29T12:31:00.000-07:00

Hi all.

I'm trying to cut data from one table and put it in another table. The
problem comes from the fact that the first table has a field that is a
char(x) and the destination table needs that data in an integer.

For example, I'm trying to do this:

insert into data
select cast('666' as integer) as block_number, phone as phone_number, name
from demo_q;

The data table has a field called block_number that is an integer. I'm trying
to populate that field with the INTEGER, 666. (I use 666 for testing since I
would never assign that number/id to an actuall customer.)

When I run this query, I get:

ERROR: column "block_number" is of type integer but expression is of type
character varying

What am I doing wrong?

TIA,
--
Mike Diehl

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

Re: [lapug] Conference call re: LAPUG Meeting

2008-09-29T12:17:00.000-07:00

On Mon, Sep 29, 2008 at 11:15 AM, Beth Kreitzer <bkreitzer@greenplum.com> wrote:

> Are you still available this morning to speak with us about the upcoming
> LAPUG meeting? I had this call scheduled for 11am this morning.
Sorry about that Beth. I spoke with Rui and touched bases:

To review the points. I would expect about 12 to 15 people to show
since there is a lot of interests in a presentation from green plum.

The meeting will be held at Cal Tech mid November. ( I will follow up
with more details at a later date ). Would it be possible to hold the
meeting on a Wednesday Evening rather than a Friday since many
LAPUGers are not available on Friday evenings? (LAPUGers: Is
Wednesday okay? Please let me know so that we can finalize a date.)

This is the email thread listing the points desired for discussion.
http://archives.postgresql.org/lapug/2008-07/msg00010.php

Also, I've cc'd the LAPUG users group so that they would weigh in on
any further points that they would like to have brought out.

--
Regards,
Richard Broersma Jr.

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

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

[pdxpug] [JOB] Fwd: PostgreSQL DBA job - contract to hire

2008-09-29T12:15:00.001-07:00

This just in. Contact me if you're interested. Via a recruiter, and I don't know which company this actually is.

---------- Forwarded message ----------
Date: Tue, Sep 23, 2008 at 3:27 PM
Subject: PostgreSQL DBA job - contract to hire
To: selenamarie@gmail.com

Here is a copy of the job description:

Job Description:

Title: PostgreSQL DBA

Duration: 6 Months - FTE

Location: Lake Oswego, OR

Minimum 5 years of progressive experience performing PostgreSQL administration production environment; experience should include three or more years of database administration in an enterprise level environment with very large (> 1 terabyte) databases

Responsibilities:

Collaborate with others to determine and deploy database solutions to business prioritized requirements, including business continuance and information security along with functional requirements

Install, configure, and test new PostgreSQL databases, perform version upgrades of existing databases

Install, upgrade and maintain all PostgreSQL-related server and administration software

Perform DB profiling and optimization; analyze and propose schema changes to PostgreSQL databases as needed

Proactively monitor activity, utilization, exceptions and database health; propose and deploy appropriate improvements; automate repetitive tasks

Collaborate with team members to create, publish and maintain database deployment methods and procedures for PostgreSQL databases

Collaborate with the Infrastructure Architect to create and maintain database technology roadmaps and deployment plans

Perform database backup and recovery duties; establish standards and schedules for database backups; develop and routinely test recovery procedures for each database; ensure that backup

schedules meet the recovery requirements and conforms to all database and data quality policies and standards

Work closely with project teams to ensure project success; coordinate product releases, data structure evolution, and manage and synchronize data promotions between development, test and production environments

Act as the final point of escalation and resolution for data related issues

Provide advanced technical support for database developers

Provide 24x7 database support as part of a rotation of DBAs

Design, implement and maintain archival and high availability strategies

Skills and abilities

Exceptional knowledge of the PostgreSQL database platform

Mastery of physical database design

Expertise in configuring, implementing and supporting high availability strategies with database systems including clustering, and replication

Mastery of ANSI SQL and/or PL-SQL

Experience in software development using Perl, Shell, or other scripting languages

Solution focused and methodical, careful attention to detail

Solid background using UNIX operating systems – Ubuntu Linux administration a plus

Experience with SQL Server and/or Oracle database platforms is desirable

Ability to perform capacity planning which supports the forecasting and purchasing processes

Ability to implement Database security safeguards and audit them

Consistently learning about trends and features in DB solutions, able to bring best practices to bear at solving DB and overall application problems

Ability to work in a high availability production environment where unplanned downtime is not acceptable

Ability to communicate ideas and designs to all stakeholders, including non-technical audiences

--
Selena Deckelmann
PDXPUG - http://pugs.postgresql.org/pdx
Me - http://www.chesnok.com/daily

Re: [ADMIN] PID file

2008-09-29T11:59:00.000-07:00

On Mon, Sep 29, 2008 at 12:43:54PM -0500, Ing. Jorge S Alanís Garza wrote:
> shutting down cleanly. Is there a way to recover the non-working postgres
> instance? Is this a very corruption-prone environment?

It's sure corruption-prone if you delete the pidfile.

If your iSCSI system keeps dropping out on you, then you need to fix
that. Otherwise, things are going to break in a way you'll be unhappy
with later.

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

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

Re: [GENERAL] inserting to a multi-table view

2008-09-29T11:52:00.000-07:00

On Sun, Sep 28, 2008 at 9:57 PM, Seb <spluque@gmail.com> wrote:

>> Well, I was able to get PostgreSQL Update-able views to work nearly as
>> well as the update-able queries did in Access.
> Would you mind sharing a sample schema?

I'll see what I can do. I did post some sample schema a while back
but I can't seem of find them in the archive. I do have some
production tables and views, but there is too much non related
attributes to make a useful example.

>> As a side note, you'll notice that MS-Access will not allow
>> update-able queries based on ODBC linked table like it does on its
>> native tables for this reason.
>
> That's right, I did find that out once but didn't know whether it was
> due to ODBC limitations or something else.

MS-Access Implements Optimistic locking with all ODBC data sources.
The basic differences with an ODBC data source is that MS-Access's Jet
Engine can't put a file lock on it like it can with other file type db
like access, excel, flat files et.al.

Optimistic locking means that every time Access issues an update to
the ODBC server, it includes the all old values of a record (that
ms-access is aware of) in the update statement's where clause. So if
your MS-Access client was only aware of a stale version of the record,
its update count will be zero, thereby access with throw an exception
saying that the update could not be completed as the underlying table
was changed by another user.

>> Natural Primary key/Foreign key CASCADE UPDATEs don't work well with
>> update-able views. Choose one or the other.
>
> Not sure what you mean; can you please tell more about what doesn't work
> well with update-able views what the choice is?

This one is kind of hard to explain. Basically it deals with the
order of operation between Referential Integrity updates versus client
side or update-able view updates.

Lets say your client app or update-able view wants to update a single
row in a view. However, in addition to changing the fields from each
table, you also want to update the natural primary key. This sounds
simple but its not.

1) The record changes made on the client application are not instantly
committed and refreshed for each field change that the user makes.
Basically, the whole row is updated with an update statement once when
the user commits the change.

2) The view redirects the update statement to its underlying rules
(usually on rule for each joined table update). First of all the
primary table fields are changed (lets call it tableA) with the rule
update including its natural primary key. Lets say the primary key
was changed from 'OLDVALUE' to 'NEWVALUE' on tableA.

3) Now tableB that has a foreign key referencing tableA with its
foreign key set to ON UPDATE CASCADE. Declarative Referential
Integrity (DRI) such as ON UPDATE CASCADE have a much higher priority
over the PostgreSQL Rule system. So before the rule system can
perform updates on tableB, tableB has it foreign key changed from
'OLDVALUE' to 'NEWVALUE'.

4) Here is where the problem occurs. When the next update-able view
rule is executed to update tableB its where clause still thinks that
tableB foreign key is 'OLDVALUE'. And because 'OLDVALUE' is in the
where clause of the rule's update statement instead of 'NEWVALUE', no
record is found to match and so the remaining field updates fail.

So the end result is that all of tableA updates are successful,
TableB's foreign key is updated by DRI but the rest of the field
updates are not. So you are left with an inconsistent update from the
perspective of the view. By the way, this really confuses MS-Access.
It doesn't know what to do when this happens.

That's why I says that "Natural Primary key/Foreign key CASCADE
UPDATEs don't work well with update-able views."

Also, if you have concurrent users on the same updateable view, update
anomolies like this can still occur just from problems with user
concurrent updates. Thats the reason I decided to abandon join tabled
updateable views.

--
Regards,
Richard Broersma Jr.

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

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

[pgsql-advocacy] Announcing PostgreSQL Certification Project Job Task Analysis Survey

2008-09-29T11:19:00.001-07:00

Hello!

The PostgreSQL Community is working to create a community driven and endorsed PostgreSQL Certification. This effort is spearheaded by the PostgreSQL Certification Project (http://www.postgresqlcertification.org). The primary focus of the project is the development and support of a series of tiered certifications that provide a standardized method of identifying the knowledge and skills of database professionals working with PostgreSQL.

At this time, the PostgreSQL Certification Project is pleased to announce the availability of a Job Task Analysis survey that will help shape the content and form the initial certifications will take. This survey is publicly available and all are encouraged to participate.

To take the survey, please register at:

http://www.postgresqlcertification.org/jta

The registration process takes just a couple of minutes. Once registration is complete and you are logged in, click JTA in the menu followed by “participate in the survey” (http://www.postgresqlcertification.org/job_task_analysis). The survey takes approximately 30 minutes to complete.

We ask everyone to please take the time to complete the survey and to refer an employer, manager, colleague, or anyone else that may rely on a PostgreSQL database in some manner to take it as well. With the help of the community we will be able to reach as wide an audience as possible.

If you are interested in joining the PostgreSQL Certification Project, please visit http://lists.postgresqlcertification.org/mailman/listinfo/cert/ and subscribe to the mailing list after taking the survey.

[GENERAL] Multiple querys

2008-09-29T11:19:00.000-07:00

Hi everyone,

I have multiple query´s, and i´m trying to optimize my queries by creating a temporary table x(contains the field id_product).

The queries that utilize the temp table x, must use the order of the rows at x.
My problem: I have a query that do a limit and offset on table x. I would like to this query obey the order of x.

I thought these solution: create a field order_id_product, to use order by order_id_product on other queries.

Can anyone help me?

Thanks a lot.

Re: [PERFORM] Identical DB's, different execution plans

2008-09-29T11:18:00.000-07:00

Setting enable_nestloop = off did result in a hash join, so I also set enable_hashjoin = off.

The new plan from the slower db:
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=20195.54..46442.99 rows=7876 width=47) (actual time=136.531..478.708 rows=8437 loops=1)
   Merge Cond: ((t1.bn)::text = "inner"."?column3?")
   -> Index Scan using t1_uc2 on t1 (cost=0.00..25604.74 rows=204906 width=32) (actual time=0.061..327.285 rows=8438 loops=1)
         Filter: active
   -> Sort (cost=20195.54..20222.79 rows=10898 width=22) (actual time=136.461..138.621 rows=12204 loops=1)
         Sort Key: (t2.sn)::text
         -> Bitmap Heap Scan on t2 (cost=145.12..19464.74 rows=10898 width=22) (actual time=7.580..120.144 rows=12204 loops=1)
               Recheck Cond: (eff_dt = ('now'::text)::date)
               -> Bitmap Index Scan on t2_nu1 (cost=0.00..142.40 rows=10898 width=0) (actual time=4.964..4.964 rows=24483 loops=1)
                     Index Cond: (eff_dt = ('now'::text)::date)
Total runtime: 480.344 ms
(11 rows)

And the faster one:

                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=21371.63..21720.78 rows=7270 width=47) (actual time=60.435..80.604 rows=8437 loops=1)
   Merge Cond: ("outer"."?column6?" = "inner"."?column3?")
   -> Sort (cost=8988.56..9100.55 rows=44794 width=32) (actual time=30.498..33.093 rows=8438 loops=1)
         Sort Key: (t1.bn)::text
         -> Seq Scan on t1 (cost=0.00..5528.00 rows=44794 width=32) (actual time=0.010..17.950 rows=8439 loops=1)
               Filter: active
   -> Sort (cost=12383.07..12409.32 rows=10500 width=22) (actual time=29.928..33.658 rows=12204 loops=1)
         Sort Key: (t2.sn)::text
         -> Index Scan using t2_nu1 on t2 (cost=0.00..11681.77 rows=10500 width=22) (actual time=0.062..13.356 rows=12204 loops=1)
               Index Cond: (eff_dt = ('now'::text)::date)
Total runtime: 83.054 ms
(11 rows)

And the query again:

explain analyze
select
    t1.bn,
    t2.mu,
    t1.nm,
    t1.root,
    t1.suffix,
    t1.type
from
     t1,
     t2
where
    t2.eff_dt = current_date
    and t1.active = true
    and t1.bn = t2.sn;

Thanks.

----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Doug Eck <deck1@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Monday, September 29, 2008 11:42:01 AM
Subject: Re: [PERFORM] Identical DB's, different execution plans

Doug Eck <deck1@yahoo.com> writes:
> Any ideas as to what could the first db to opt for the slower subquery rather than the merge?

Not from the information given. Presumably db1 thinks that the
mergejoin plan would be slower, but why it thinks that isn't clear yet.
Try setting enable_nestloop = off (and enable_hashjoin = off if it then
wants a hashjoin) and then post the EXPLAIN ANALYZE results.

regards, tom lane

[GENERAL] Counting unique rows as an aggregate.

2008-09-29T11:12:00.000-07:00

My current solution is to issue a bunch of queries:

> SELECT make, count(*) FROM table WHERE >criteria< GROUP BY make ORDER BY count(*) DESC LIMIT 3;
make count
----------+---------
audi | 50
bmw | 40
vw | 30

SELECT color, count(*) FROM table WHERE >criteria< GROUP BY color
ORDER BY count(*) DESC LIMIT 3;

color count
-----------+------
red | 400
blue | 200
green | 100

Which will give me the top 3 counts of each column im interested in
for the >criteria< specified.

However, this is starting to become too slow (as there are about 10 of
these queries), and therefore I need to write an aggregate function
which lets me do:

>SELECT count_unique(make), count_unique(color) from table WHERE >criteria<;

After reading about aggregate functions, this should be possible, as
long as I can use a dictionary/hashmap type for the state<STYPE>
argument.

Is there such a type in postgresql that can be used in an aggregate
function, and if there isn't, how would it be possible to make one?

Also, do you think I'm going about this the wrong way, and there is a
much better solution that's I've neglected?

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

Re: [torontopug] Ontario Linux Fest

2008-09-29T10:56:00.000-07:00

I think that's a great idea, Steve, thanks for asking them to add us.

I should be able to make the conference, are there any others out
there that might want to make it?

~Ian Bailey

On Fri, Sep 26, 2008 at 7:52 PM, Steve Singer <ssinger_pg@sympatico.ca> wrote:
>
> This year's Ontario Linux fest will be held on Saturday, 25 October 2008 at
> Days Hotel and Conference Centre - Toronto Airport East. The Ontario Linux
> Fest is a regional conference on Linux and other open source projects. You
> can find more at http://www.onlinux.ca/
>
> They have room in the schedule to host a birds of feather session for
> PostgreSQL users (I don't have the exact time yet).
>
> Unless there is an objection I'm going to ask them to list the BoF as being
> hosted by the TorontoPUG (This was sort of discussed at the august meeting).
> I am also going to hope that a few others make it to the conference.
>
> Steve
>
>
>
>
>
> --
> Sent via torontopug mailing list (torontopug@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/torontopug
>

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

Re: [NOVICE] plpgsql functions vs. embedded queries

2008-09-29T10:45:00.000-07:00

I did neither but will try both, thank you.

The functions took on average more than 10 times as long.

-----Original Message-----
From: John DeSoi [mailto:desoi@pgedit.com]
Sent: Monday, September 29, 2008 1:16 PM
To: Wright, George
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] plpgsql functions vs. embedded queries

On Sep 29, 2008, at 9:50 AM, Wright, George wrote:

> Are funtions in plpgsql always slower than embedding sql queries in
> string form in code like PHP?
> I ran several tests and they seemed to always perform that way. I
> would have thought the pre-planning would have made them faster.
> Would re-writing the functions C make them faster than the embedded
> queries?

I'd say no, they should not always be slower. Did you try preparing
your PHP call to the function? Did you mark your function as STABLE if
you are not modifying the database?

John DeSoi, Ph.D.

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

[ADMIN] PID file

2008-09-29T10:43:00.000-07:00

Hello,

We are operating Postgresql 8.2 on an ISCSI environment. Sometimes there are issues with the ISCSI so Postgresql refuses to shutdown properly, or start, because of the pid file. My question is, what is the correct thing to do with this pid file? On some test-environments I have deleted the pid and then when starting up, I see postgres complain about the database not shutting down cleanly. Is there a way to recover the non-working postgres instance? Is this a very corruption-prone environment?

Thanks,

Jorge Santiago Alanís Garza
Innovación y Desarrollo
jorge.alanis@blocknetworks.com.mx

Tel: (81) 4444.4044
Cel: (811) 243-6570

www.blocknetworks.com.mx
Av. Lázaro Cárdenas 4000, L-17
Col. Valle de las Brisas
Monterrey, Nuevo León, CP 64790
Tel: +52 (81) 4444 4044

[pgsql-www] pgsql-ports retirement party was [PORTS] libpq windows 64bit support

2008-09-29T10:28:00.000-07:00

Is there a date for when pgsql-ports will be officially retired?

---------- Forwarded Message ----------

Subject: [PORTS] libpq windows 64bit support
Date: Monday 29 September 2008
From: "Pierre Joye" <pierre.php@gmail.com>
To: pgsql-ports@postgresql.org

hi,

Is there any plan to support windows 64bit? I tested the latest stable
release but it fails to build (I created my own configuration using
the existing sln).

Cheers,
--
Pierre

http://blog.thepimp.net | http://www.libgd.org

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

-------------------------------------------------------

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

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

Re: [ADMIN] Do we need vacuuming when tables are regularly dropped?

2008-09-29T10:26:00.000-07:00

On Mon, Sep 29, 2008 at 11:12 AM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
>
>>> What it sounds like to me is that you're not vacuuming the system
>>> catalogs, which are getting bloated with dead rows about all those
>>> dropped tables.
>>>
>>
>> Wow, great!
>>
>> It is not immediately clear from the documentation, but the VACUUM
>> command also deals with the system catalogs as well, correct?
>>
>>
>
> To expand on Tom's answer, rows in system tables are created not only for
> tables but for each column in the table, rules, indexes, etc. You can end
> up with a lot more row creation than you suspect. And temporary tables bloat
> the system tables just like regular tables. We discovered that cron scripts
> using temporary tables can cause very rapid system-table blotage.

Also, there was a time when you couldn't do vacuum full on system
tables do to locking issues, and had to take the db down to single
user mode to do so.

Tom, is that still the case?

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

Re: [BUGS] ERROR: unexpected data beyond EOF in block XXXXX of relation "file"

2008-09-29T10:23:00.000-07:00

Okay, I see the maturity level is too low here. I'll take this elsewhere.
If anyone has a similar problem and would like to know the status please
email me.

David Fetter wrote:
>
> On Sun, Sep 28, 2008 at 11:51:49AM -0700, austijc wrote:
>>
>> That's going to be a problem for the continued viability of
>> Postgres.
>
> Funny, I thought running a DBMS over a known-unreliable storage system
> was a problem for the continued viability of Oracle. When, not if,
> people lose enough data to this silliness, they'll be thinking hard
> about how to get Oracle out and something reliable in.
>
>> Clustered systems using a NAS for data is a pretty common
>> configuration these days. Oracle specifically supports it and even
>> complains if your NFS mount options are not correct. Our Oracle
>> DBs run great in this same configuration and are a good 10-20 times
>> faster than the local disk performance along with the quick
>> take-over capability if a system goes belly up.
>
> Oracle stores more state to the disk than PostgreSQL does, which has
> significant down sides. There are more effective ways of handling
> uptime requirements than jamming NFS into the picture. Maybe it's
> just my failure of imagination, but I can't think of a *less*
> effective one.
>
>> I'll try to isolate this problem with a simple C program to tell me
>> what software layer to look at. Hopefully it's just a configuration
>> issue.
>
> It's not. The issue is that NFS is broken garbage from a DBMS, and,
> it's pretty easy to argue, just about any other perspective.
>
> Cheers,
> David.
>
>>
>> Tom Lane-2 wrote:
>> >
>> > austijc <jaustin@jasononthe.net> writes:
>> >> The question is can anyone more familiar with this tell me what's
>> going
>> >> on
>> >> here? I don't know if this is a Postgres, Sun, or NetApp issue.
>> Could
>> >> it
>> >> be a work around for an old Linux bug causing an issue with acceptable
>> >> behavior of the NetApp device?
>> >
>> > People who try to run databases over NFS usually regret it eventually
>> ;-)
>> >
>> > All I can say is that this error message has never before been reported
>> > by anyone who wasn't exposed to that lseek-inconsistency kernel bug.
>> > I am not finding it too hard to believe that NFS might be vulnerable to
>> > similar misbehavior.
>> >
>> > 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
>> >
>> >
>>
>> --
>> View this message in context:
>> http://www.nabble.com/ERROR%3A--unexpected-data-beyond-EOF-in-block-XXXXX-of-relation-%22file%22-tp19680438p19713228.html
>> Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>
> --
> 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-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>

--
View this message in context: http://www.nabble.com/ERROR%3A--unexpected-data-beyond-EOF-in-block-XXXXX-of-relation-%22file%22-tp19680438p19728120.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

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

[ANNOUNCE] West: 2nd call for lightning talks

2008-09-29T10:14:00.000-07:00

The PostgreSQL Conference: West is being held at Portland State
University on October 10th - 12th. The West Coast PostgreSQL
conference is currently seeking more Lightning Talks.

Lightning talks are an exciting way to get involved in the conference
with very little commitment on the speakers end. Assuming you can stand
in front of an audience for 5 minutes; you can speak about anything
PostgreSQL or Open Source related.

To submit your lightning talk please visit:

http://www.pgcon.us/west08/talk_submission/

If you have not yet registered for the event please visit:

http://www.postgresqlconference.org/west08/register

Lastly thank you to this years sponsors:

Command Prompt: http://www.commandprompt.com/
EnterpriseDB: http://www.enterprisedb.com/

Afilias : http://www.afilias.info/
HP: http://www.hp.com/

Emma : http://www.myemma.com/

Continuent : http://www.continuent.com/
Endpoint : http://www.endpoint.com/
OTG : http://www.otg-nc.com/

EFF: http://www.eff.org/
Google: http://www.google.com/

---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:

pgsql-announce-unsubscribe@postgresql.org

[GENERAL] database question

2008-09-29T07:21:00.000-07:00

Hi all I have been experiencing some strange behaviour on my postgres
DB. I am VERY new to PG so bear with me as what I am going to ask is
all probably very basic to you guys.
First off over the last couple of weeks we have been seeing in the
dir /var/lib/pgsql/data/base/16450 some large file creations, so for
example

-rw------- 1 postgres postgres 1073741824 Sep 29 15:15 2683
-rw------- 1 postgres root 1073741824 Sep 29 15:15 2613.77
-rw------- 1 postgres root 1073741824 Sep 29 15:15 2613.83
-rw------- 1 postgres root 65347584 Sep 29 15:16 2613.88
-rw------- 1 postgres root 1073741824 Sep 29 15:16 2613.86
-rw------- 1 postgres root 1073741824 Sep 29 15:17 2613.82
-rw------- 1 postgres root 1073741824 Sep 29 15:17 2613.81
-rw------- 1 postgres postgres 380346368 Sep 29 15:17 16451.1
-rw------- 1 postgres postgres 217710592 Sep 29 15:18 33820
-rw------- 1 postgres root 119046144 Sep 29 15:18 2683.1
-rw------- 1 postgres root 1073741824 Sep 29 15:18 2613.84

What are these files and why have they suddenly started to be created
and why so large?

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

[GENERAL] Re: Is there a parameter to tell postgresql to not attempt to open an IPv6 socket?

2008-09-29T06:41:00.000-07:00

On 16 sep, 23:04, a...@commandprompt.com (Andrew Sullivan) wrote:
> On Tue, Sep 16, 2008 at 04:46:37PM -0400, Reid.Thompson wrote:
> > PostgreSQL appears to all intents to startup fine on the UNIX and IPv4
> > sockets. Is there a parameter to tell postgresql tonotattempt to open
> > anIPv6socket?
>
> Specify the specific TCP/IP interfaces in the postmaster.conf file.
> Otherwise, Postgres will try tobindto all the sockets. There's
> something hinkey about theIPv6support in AIX, IIRC, so that you end
> up with this symptom.
>
> A
>
> --
> Andrew Sullivan
> a...@commandprompt.com
> +1 503 667 4564 x104http://www.commandprompt.com/
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Hello,

I have the same pb. I have looked for a postmaster.conf file but there
is none on the server.
Apart from the doc and src files, the only files on my server
containing the word "postmaster" are :
/usr/local/pgsql/bin/postmaster
$PGDATA/postmaster.opts
$PGDATA/postmaster.pid

Do you have an idea why and how then to solve this pb ?

Thanks,

Yann.

--
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] inserting to a multi-table view

2008-09-28T21:57:00.000-07:00

On Sun, 28 Sep 2008 21:43:49 -0700,
"Richard Broersma" <richard.broersma@gmail.com> wrote:

[...]

> Well, I was able to get PostgreSQL Update-able views to work nearly as
> well as the update-able queries did in Access.

Would you mind sharing a sample schema?

> As a side note, you'll notice that MS-Access will not allow
> update-able queries based on ODBC linked table like it does on its
> native tables for this reason.

That's right, I did find that out once but didn't know whether it was
due to ODBC limitations or something else.

> Natural Primary key/Foreign key CASCADE UPDATEs don't work well with
> update-able views. Choose one or the other.

Not sure what you mean; can you please tell more about what doesn't work
well with updateable views what the choice is?

Thanks for the feedback,

--
Seb

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

[COMMITTERS] uint - uint: Initial checking for the unsigned integer data type.

2008-09-28T21:54:00.001-07:00

Log Message:
-----------
Initial checking for the unsigned integer data type.

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

[COMMITTERS] uint - uint: New Directory

2008-09-28T21:50:00.002-07:00

Update of /cvsroot/uint/uint/sql
In directory pgfoundry.org:/tmp/cvs-serv98957/sql

Log Message:
Directory /cvsroot/uint/uint/sql added to the repository

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