[pgadmin-hackers] First public pre-alpha release of GQB (Graphical Query Builder) for pgAdmin

Hi everyone,

I have been working on improve my Google Summer of Code Project: http://wiki.postgresql.org/wiki/Gsoc08-querybuilder  for a while, and today I'm going to release what I believe it's the first public pre-alpha release, as you notice this release is for testing only an i'ts provided with no warranty :)

Now with the project you can:

0. Create a graphical model of a query sentence. (to add a table just double click on it on the left tree, tables are added at same place please move to other position by drag & drop from the title)
1. Create some simple queries involving joins (only equality joins right now), only drag and drop column from one table to another
2. Order the columns in the projection part of sentence (Select x,y,z...), and add columns alias in a grid
3. Add some restrictions (not validate content yet, a parser should be done in next days)
4. Create synonymous for tables just double click on table title an write synonymous on dialog.

I'm hoping for feedback, any ideas, suggestions or bug reporst are important to improve next releases.

Mini-FAQ
Where is located GQB inside pgAdmin?
At SQL Editor in a top tab you can choose between normal editor & gqb.

How looks GQB?
You can check this post at my project blog.
http://gsoc2008-gqb.blogspot.com/2008/07/first-public-pre-alpha-release-of-gqb.html

Can I try it without applying a patch and compiling it?
Yes, download binary (sorry binary for windows only this time) at rapidshare:
http://rapidshare.com/files/132757130/prealpha-test-pgadmin-gqb-july-27.patch.zip.html

Where is located the patch?
http://svn.assembla.com/svn/vsqlbuilder/Jul/27/prealpha-test-gqb-july-27.patch

Regards,
Luis.

Re: [HACKERS] Regd: TODO Item : Have EXPLAIN ANALYZE issue NOTICE messages ...

Regarding: Optimizer To Do Item: Have EXPLAIN ANALYZE issue NOTICE messages when the estimated and actual row counts differ by a specified percentage.

 

Hi,

 

After going through the thread related to the above mentioned to do Item, my understanding is that:

 

Issuing notices when a problematic node occurs is useful in the case of long running queries. It can avoid having the user wait a couple of hours to solve the problem.

 

A possible solution from the archives was summarized as below:

Bruce Momjian   Wrote : 

I was thinking we could issue NOTICE when the estimates differed from

the actual by a specified percentage, and that NOTICE could be issued

while the query is still processing, assuming the stage completes before

the query does.  This seems much easier than doing protocol changes.

 

 

Looking forward to your inputs on this.

 

Thank you,

Ramya

On Thu, Jun 26, 2008 at 10:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Ramya Chandrasekar" <csekar.ramya@gmail.com> writes:
> Regd: TODO Item: Optimizer:: Have EXPLAIN ANALYZE issue NOTICE messages when
> the estimated and actual row counts differ by a specified percentage.

I think that TODO item hasn't been thought through very carefully.
NOTICEs that are dissociated from the actual explain output seem pretty
useless, or at least painful to use.  You might want to go back in the
archives and read the whole thread that led up to the TODO item to see
what ideas were kicked around.

                       regards, tom lane

Re: Fwd: [pgsql-es-ayuda] Replicacion

*Fabio Arias* Escribió: <fharias@gmail.com <mailto:fharias@gmail.com>>
> Buenas Tardes Rodrigo, actualmente tengo replicacion de mi base de datos.
>
> 1. Hasta el momento se ha comportado bien solo por un problema de
> hardware que tengo en la maquina de replica y es que esta tiene
> problemas de reloj y cuando la hora cambia bruscamente deja de replicar.
> 2. Utilizo Slonik super facil de configurar y con pgAdmin te deja
> hacer varias cosas para que no te metas con codigo directo.
> 3. Super hasta el momento.
>
Hola Fabio.
Soluciona tu problema de reloj consultando cada hora o cada 30 min a un
servidor NTP (Network Time Protocol).
En http://www.ntp.org/ tenes todo lo necesario.-

========================
WOJCIK, MARIO A.
========================
Leandro N Alem -Misiones
------------------------
A R G E N T I N A
------------------------
mariowojcik@yahoo.com.ar

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

Re: [HACKERS] Search for version-numbered tclsh?

Tom Lane wrote:
> I notice that the recent patch to enforce finding a tclsh broke a couple
> of buildfarm machines. On reflection, I remember that recent Tcl
> versions don't automatically create a 'tclsh' symlink, only a
> version-numbered program such as 'tclsh8.3'. I suggest that maybe we
> ought to allow that without forcing people to set TCLSH manually.
> Perhaps have PGAC_PATH_TCLSH do
>
> AC_PATH_PROGS(TCLSH, [tclsh tcl tclsh8.5 tclsh8.4 tclsh8.3])
>
> or some such.
>
>
>

Bleah. I guess we'll have to.

cheers

andrew

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

Re: [PATCHES] pg_dump additional options for performance

On Sat, Jul 26, 2008 at 01:56:14PM -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > I want to dump tables separately for performance reasons. There are
> > documented tests showing 100% gains using this method. There is no gain
> > adding this to pg_restore. There is a gain to be had - parallelising
> > index creation, but this patch doesn't provide parallelisation.
>
> Right, but the parallelization is going to happen sometime, and it is
> going to happen in the context of pg_restore. So I think it's pretty
> silly to argue that no one will ever want this feature to work in
> pg_restore.
>
> To extend the example I just gave to Stephen, I think a fairly probable
> scenario is where you only need to tweak some "before" object
> definitions, and then you could do
>
> pg_restore --schema-before-data whole.dump >before.sql
> edit before.sql
> psql -f before.sql target_db
> pg_restore --data-only --schema-after-data -d target_db whole.dump
>
> which (given a parallelizing pg_restore) would do all the time-consuming
> steps in a fully parallelized fashion.

A few thoughts about pg_restore performance:

To take advantage of non-logged copy, the create and load should be in
the same transaction.

To take advantage of file and buffer cache, it would be be good to do
indexes immediately after table data. Many tables will be small enough
to fit in cache and this will avoid re-reading them for index builds. This
effect becomes stronger with more indexes on one table. There may also be
some filesytem placement benefit to building the indexes for a table
immediately after loading the data.

The buffer fan file cache advantage also applies to constraint creation,
but this is complicated by the need for indexes and data in the referenced
tables.

It seems that a high performance restore will want to proced in a different
order than the current sort order or that proposed by the before/data/after
patch.

- The simplest unit of work for parallelism may be the table and its
"decorations", eg indexes and relational constraints.

- Sort tables by foreign key dependency so that referenced tables are
loaded before referencing tables.

- Do table creation and data load together in one transaction to use
non-logged copy. Index builds, and constraint creation should follow
immediately, either as part of the same transaction, or possibly
parallelized themselves.

Table creation, data load, index builds, and constraint creation could
be packaged up as the unit of work to be done in a subprocess which either
completes or fails as a unit. The worker process would be called with
connection info, a file pointer to the data, and the DDL for the table.
pg_restore would keep a work queue of tables to be restored in FK dependency
order and also do the other schema operations such as functions and types.

-dg

--
David Gould daveg@sonic.net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.

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

Re: [GENERAL] PostgreSQL vs FreeBSD 7.0 as regular user

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIi8p5ldnAQVacBcgRAor6AJ9N9GsaZz5Idg6uZuxiZ+6faVd1dQCgnm4b
WLyBpWSxq6AzOsbM7LubS5Q=
=7K4s
-----END PGP SIGNATURE-----
Greg Smith wrote:
> On Sat, 26 Jul 2008, Zoltan Boszormenyi wrote:
>
>> Zoltan Boszormenyi �rta:
>>>>> These three settings were also set with "sysctl -w ..." to take effect
>>>>> immediately.
>> Rebooting FreeBSD solved it. Huh? Is it really like W#&@$#&?
>
> Looks like the PostgreSQL documentation here (
> http://www.postgresql.org/docs/current/static/kernel-resources.html ) is
> now outdated. From http://www.manpages.info/freebsd/sysctl.8.html :
>
> "The -w option has been deprecated and is silently ignored."

It's not ignored as in "the whole command will have no effect". Only the
switch itself is ignored. So,

sysctl -w a.b=c

is 100% equivalent to

sysctl a.b=c

> Looks like the correct thing to do here now is to edit the
> /etc/sysctl.conf file, then issue:
>
> /etc/rc.d/sysctl reload
>
> to use those values without needing to reboot. (I don't actually have
> such a machine to check for sure).

Yes, that will work. Sysctl.conf will not reload itself :)

Re: [pgsql-es-ayuda] Como cambiar la posicion de un campo

--- El vie 25-jul-08, Alvaro Herrera <alvherre@alvh.no-ip.org> escribió:

> De: Alvaro Herrera <alvherre@alvh.no-ip.org>
> Asunto: Re: [pgsql-es-ayuda] Como cambiar la posicion de un campo
> A: "Cesar Erices" <caerices@gmail.com>
> Cc: "foro postgresql" <pgsql-es-ayuda@postgresql.org>
> Fecha: viernes, 25 julio, 2008, 10:55 am
> Cesar Erices escribió:
> > Estimados amigos, me acuerdo que hace un tiempo atras
> se converso sobre este
> > tema pero no logre encontrarlo, les digo:
> > Necesito cambiar de posición un campo de una tabla a
> traves e codigo ¿Se
> > puede?, se que se puede lograr a travez de SQL Manager
> for postgres, pero
> > como ls indique la idea es poder hacerlo por codigo...
>
> No se puede (aun).
>
Ademas que no es del todo cierto que en SQL Manager se pueda, se puede si solo si, no tenga vistas y funciones dependientes, si no te quiero ver.

Atte.
Gabriel Colina

> --
> Alvaro Herrera Developer,
> http://www.PostgreSQL.org/
> "Tiene valor aquel que admite que es un cobarde"
> (Fernandel)
> --
> TIP 1: para suscribirte y desuscribirte, visita
> http://archives.postgresql.org/pgsql-es-ayuda

____________________________________________________________________________________
Yahoo! MTV Blog & Rock &gt;¡Cuéntanos tu historia, inspira una canción y gánate un viaje a los Premios MTV! Participa aquí http://mtvla.yahoo.com/
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

RE: [pgsql-es-ayuda] normalizacion

--- El vie 25-jul-08, José Fermín Francisco Ferreras <josefermin54@hotmail.com> escribió:

> De: José Fermín Francisco Ferreras <josefermin54@hotmail.com>
> Asunto: RE: [pgsql-es-ayuda] normalizacion
> A: "Sam" <samanta.fernandez@gmail.com>
> Cc: "foro postgresql" <pgsql-es-ayuda@postgresql.org>
> Fecha: viernes, 25 julio, 2008, 12:29 pm
> Gracias a todos por responder.
> Según lo q he leido, hay dos posibles respuestas para este
> asunto:
>
> 1. La q dijo alvaro herrera, d q los programadores no
> tienen idea d como diseñar una base d datos.
> 2. La q dijo Sam y otros acerca d esto:
>
> 'Esto puede ser debido a que la factura, boleta son
> documentos
> contables, y se requiere saber conque nombre fue impreso,
> si fue
> impreso con un nombre equivocado, esta factura se anula ,
> se cambia la
> información en el maestro, mas no en la factura
> anulada.'

Mas o menos, en mi pais eso no es necesario, si se anula, lo importante
es que se registre en 0, si no lo importante es que mantenga los importes
y los impuestos y la fecha.

Igualmente el numero de factura va a llevar al documento del cual queda guardada una copia en la empresa.

>
> 'Una explicacion posible es que cuando se guarda
> registro de documentos
> impresos con valor legal, los datos se deben guardar tal
> cual al
> momento de la impresion, por esto es que a veces esta el
> requerimiento
> de replicarlos en cada registro, si posteriormente las
> tablas de
> entidades cambian, cada documento se mantiene tal cual
> cuando fue
> generado.'
>
Discrepo con el punto, salvo que sea una reglamentacion legal insolsayable, puesto que las empresas guardan una copia del comprobante fisico, para lo cual incluso ahora se me ocurre otro modelo que lo contempla sin hacer eso.

Atte.
Gabriel Colina

____________________________________________________________________________________
Yahoo! MTV Blog & Rock &gt;¡Cuéntanos tu historia, inspira una canción y gánate un viaje a los Premios MTV! Participa aquí http://mtvla.yahoo.com/
--
TIP 5: ¿Has leído nuestro extenso FAQ?
http://www.postgresql.org/docs/faqs.FAQ.html

Re: [PERFORM] Using ENUM with huge NAMEDATALEN

"David Andersen" <david@andersen.gs> writes:
> I am attempting to alter pg_enum.enumlabel to Text, but I seem to run into a
> strange permission problem with regards to system tables. I am not allowed
> to modify them even if I am a superuser.

ALTER TABLE is hardly gonna be sufficient on a system catalog anyway, as
knowledge of its rowtype is generally hardwired into the C code. You'd
have to modify src/include/catalog/pg_enum.h and then go around and find
all the references to enumlabel and fix them to know it's text not name.
Fortunately, this being not a widely used catalog, there shouldn't be
too many places to fix. Right offhand, it looks like the indexing.h
definition of its index and about three places in pg_enum.c would be all
that have to change.

Note that this would be an initdb-forcing change and so you should also
bump the catversion number.

regards, tom lane

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

Re: [pgsql-es-ayuda] normalizacion

> Si crearas otra entidad cliente, ¿cómo harías para
> ponerles el mismo
> identificador natural, el identificador tributario que cada
> empresa/persona debe tener? ¿No deberías tener un UNIQUE
> en esa
> columna?
>
> --
> Alvaro Herrera

Si es verdad, pero tambien es cierto que el operador se puede equivocar y crear un nuevo cliente con el RUT en mi caso, diferente.

Tambien e visto dejarlo sin UNIQUE para poder mantener distintas sucursales, sin tener que hacer lo correcto que seria mantener una tabla de sucursales y definir si llevan cuentas corrientes separadas o juntas, pero como esto es mas engorroso, tratan a las empresas con sucursales y cuentas unicas como unas sola y a las sucursales con el mismo ruc, pero cuentas diferentes.

____________________________________________________________________________________
Yahoo! MTV Blog & Rock &gt;¡Cuéntanos tu historia, inspira una canción y gánate un viaje a los Premios MTV! Participa aquí http://mtvla.yahoo.com/
--
TIP 5: ¿Has leído nuestro extenso FAQ?
http://www.postgresql.org/docs/faqs.FAQ.html

[HACKERS] Search for version-numbered tclsh?

I notice that the recent patch to enforce finding a tclsh broke a couple
of buildfarm machines. On reflection, I remember that recent Tcl
versions don't automatically create a 'tclsh' symlink, only a
version-numbered program such as 'tclsh8.3'. I suggest that maybe we
ought to allow that without forcing people to set TCLSH manually.
Perhaps have PGAC_PATH_TCLSH do

AC_PATH_PROGS(TCLSH, [tclsh tcl tclsh8.5 tclsh8.4 tclsh8.3])

or some such.

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

Re: [pgsql-es-ayuda] normalizacion

--- El vie 25-jul-08, Alvaro Herrera <alvherre@alvh.no-ip.org> escribió:

> De: Alvaro Herrera <alvherre@alvh.no-ip.org>
> Asunto: Re: [pgsql-es-ayuda] normalizacion
> A: "José Fermín Francisco Ferreras" <josefermin54@hotmail.com>
> Cc: "foro postgresql" <pgsql-es-ayuda@postgresql.org>
> Fecha: viernes, 25 julio, 2008, 10:55 am
> José Fermín Francisco Ferreras escribió:
> >
> > Hola, quisiera saber su opinión sobre el asunto q
> hacen muchos
> > programadores (los cuales tambien diseñan las base d
> datos) y es q en
> > la tabla factura guardan tanto el codigo de cliente,
> como el nombre y
> > apellidos del cliente. Cómo todos ustedes saben eso
> rompen con las
> > reglas d normalización.
> >
> > Q opinan uds. sobre ese asunto, a q se deberá esto??
>
> A que la mayoría de los programadores no tiene idea de
> diseñar
> correctamente una base de datos :-)
>
> --
> Alvaro Herrera

Quiero expresar mi humilde opinion en este theread.

Digo humilde, por que me considero un Pato, no por aquello de un paso una caga..., si no por el hecho de que el Pato no es un crack en el Agua en eso le gana un pez, pero el pato nada y vuela, no como un aguila, pero tambien anda en la tierra, no como la libre, ahora todos son crack en su medio, pero lo sacan de el y no sobreviven.

Yo como el pato, oficio de DBA, Programador, trabajo en redes y salvo en hardware en lo demas creo tener solidos conocimiientos. NO soy un crack.

Alvaro me parece que lo que hace a un buen programador es justamente lo contrario, por lo menos yo que soy unipersonal en mi empresa, para mi lo mas importante es un buen MER, el otro es un escupe codigo, pero no un progamador.

Y lo que dice mi amigo solo lo explico por tres motivos.

1 El programador que hace eso POR IGNORANCIA, ese que deberia dedicarse a tomar un curso de carpintero, mecanica automotirz o cualquier otra cosa que lo aleje de su profesion.

Si me decis que en visual fox con DBF haces esto por que la herramienta es mala, no es escusa cambia de herramienta y almacena tus datos en una base de datos y si queres programa en Visual Fox, pero no uses DBF, por lo menos no para algo serio que para sacar buen rendimiento tenes que ser chancho.

2 Repite esos datos, en tablas que le sirven como vistas materializadas, para usarlas quiza despues de manera eficiente en consultas cartesianas o referencias cruzadas como gusten llamarlas. Bien pero eso lo hacen en tablas que no son operativas en el sistema funcional, si no que son reservadas para tareas de consulta estadistica o con otros propositos posteriores a las funciones basicas del sistema.

3 Un mal entendimiento de la desnormalizacion, puesto que para desnormalizar algo, primero hay que saber mucho de normalizacion, y desnormalizar es el ultimo recurso para dar una solucion ultima, requiere mas estudio que la anterior normalizacion. por que si no no estamos desnormalizando, si no que estamos haciendo cosas anormales.

Para que usamos PostgreSQL que es una excelente herramienta para no tener que hacer chanchadas, si lo que vamos hacer son chanchadas.

Una cosa que me encanta de PostgreSQL, es justamente su solvencia que me permite con buen rendimiento, mantener en excelente estado la integridad referencial, sin obviar nada. Incluso algo que me parece genial, no tiene vistas actualizables, cosa que en principio critique ya que en SQL Server si la tienen, pero me da la ventaja de crear una vista que se pueda borrar un registro o insertarlo, pero no permitir un update, cosa que resuelve uno de los puntos del theread donde alguien expresa que no quiere modificar los datos de una factura emitida, bueno que use un tipo de vista asi en sus sistemas y nadie va a poder modificar ese dato.

Otra cuestion que toca el theread es que en una universidad estan educando a sus alumnos en el sentido de que no importa el modelo de datos si no el de negocios.

Esto me parece que esta planeteado como una contradiccion absurda, puesto que el modelo de datos debe representar el modelo de negocio y del modelo de datos el negocio debe aprender.

Creo que realizando un MER, se pueden detectar procedimientos ineficientes en los negocios.

Ademas los procedimientos son mas peregnes que los datos, si uno se basa para programar en la necesidad que de los procedimientos de un determinado comercio, seguro que va a repetir con su sistemas los mismos errores con mayor velocidad y ademas va a tener que reprogramar cuando un procedimiento del comercio cambia, en cambio los datos que se manejan en una empresa son mas duraderos que sus procedimientos, por eso es mejor basarse en el MER y despues si se quiere enfrentarlo al diagrama de fuljo, antes de iniciar el desarrollo.

No hay modelo de datos que bien elaborado tenga que dejar la normalizacion para cumplir con algo, lo que lleva a la desnormalizacion son otras consideraciones, consideraciones que no deberian partir de los escasos recursos que tengo, o del lenguaje que decidi utilizar.

Espero que me perdonen la chachara, pero el hilo me intereso bastante y me parece que para usar PostgreSQL con malas practicas, mejor usen mysql, access o dbf que por lo menos tendran una justificacion, pero no usen cosas serias como PostgreSQL, Oracle o el mismo SqlServer.

Atte.
Gabriel Colina

____________________________________________________________________________________
Yahoo! MTV Blog & Rock &gt;¡Cuéntanos tu historia, inspira una canción y gánate un viaje a los Premios MTV! Participa aquí http://mtvla.yahoo.com/
--
TIP 4: No hagas 'kill -9' a postmaster

[COMMITTERS] pgscript - pgScript: New Directory

Update of /cvsroot/pgscript/pgScript/files/tests/dictionaries
In directory pgfoundry.org:/tmp/cvs-serv24791/files/tests/dictionaries

Log Message:
Directory /cvsroot/pgscript/pgScript/files/tests/dictionaries 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

[COMMITTERS] pgscript - pgScript: New Directory

Update of /cvsroot/pgscript/pgScript/files
In directory pgfoundry.org:/tmp/cvs-serv24791/files

Log Message:
Directory /cvsroot/pgscript/pgScript/files 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

[COMMITTERS] pgscript - pgScript: New Directory

Update of /cvsroot/pgscript/pgScript/files/inputs
In directory pgfoundry.org:/tmp/cvs-serv24791/files/inputs

Log Message:
Directory /cvsroot/pgscript/pgScript/files/inputs 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

[COMMITTERS] pgscript - pgScript: New Directory

Update of /cvsroot/pgscript/pgScript/files/tests
In directory pgfoundry.org:/tmp/cvs-serv24791/files/tests

Log Message:
Directory /cvsroot/pgscript/pgScript/files/tests 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

[COMMITTERS] pgscript - pgScript: New Directory

Update of /cvsroot/pgscript/pgScript/doc
In directory pgfoundry.org:/tmp/cvs-serv24791/doc

Log Message:
Directory /cvsroot/pgscript/pgScript/doc 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

[COMMITTERS] pgscript - pgScript: New Directory

Update of /cvsroot/pgscript/pgScript
In directory pgfoundry.org:/tmp/cvs-serv24520/pgScript

Log Message:
Directory /cvsroot/pgscript/pgScript 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

Re: [HACKERS] Adding WHERE clause to pg_dump

On Sat, 2008-07-26 at 09:08 -0400, Andrew Dunstan wrote:

> So, IMNSHO, making a full database backup is still pg_dump's principal
> function.

Making copies for development databases is also a common use case, and
if not more common than backups, at least not far behind. This was my
stated use case.

>From my perspective, this should be fairly simple
* do we agree the use case is a problem we care about?
* do we agree the proposal would help that use case?
* whats the best way to package that feature?

If we wish to protect pg_dump's role, then lets have another utility or
some packaging that can be used for its other hidden roles. That sounds
like we might all agree on that. pg_dev_dump? How should it look?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

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

[SQL] Efficiently determining the number of bits set in the contents of a VARBIT field

Hi all,
Am looking for a fast and efficient way to count the number of bits set
(to 1) in a VARBIT field. I am currently using
"LENGTH(REGEXP_REPLACE(CAST(a.somefield_bit_code AS TEXT),'0','','g'))".

Allan.

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

Re: [HACKERS] Adding WHERE clause to pg_dump

Simon Riggs wrote:
> In a world
> where PITR exists the role and importance of pg_dump has waned
> considerably. What *is* its principal function? Does it have just one?
>
>
>

I think that's probably a rather narrow perspective.

PITR doesn't work across versions or architectures or OSes. And if
you're using it for failover, then using it for standalone backups as
well means you will need a custom archive_command which can be a bit
tricky to get right. And a custom dump is almost always far smaller than
a PITR dump, even when it's compressed.

I suspect that the vast majority of our users are still using pg_dump to
make normal backups, and that it works quite happily for them. It's
really only when databases get pretty large that this becomes
unmanageable. I think using pg_dump for backups and PITR for failover is
a good combination for a great many users.

So, IMNSHO, making a full database backup is still pg_dump's principal
function.

cheers

andrew

--
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] Adding WHERE clause to pg_dump

On Sat, 2008-07-26 at 07:47 -0400, Andrew Dunstan wrote:
>
> Simon Riggs wrote:

> > As a dev tool it makes sense.
> >

> I think we have yet another case for moving the core bits of pg_dump
> into a library that can then be used by lots of clients. Until we do
> that we're going to get continual pressure to add extra cases to pg_dump
> unrelated to its principal functionality.

That's a good idea and I support that.

I'm slightly suprised at the "principal functionality" bit. In a world
where PITR exists the role and importance of pg_dump has waned
considerably. What *is* its principal function? Does it have just one?

One man's dev system is another man's data warehouse, or another man's
backup. The meaning of a dump is defined by the user making the data
dump, not the tool used.

Is this one option sufficient to make us invent pg_make_dev_database?
(With all pg_dump options, plus -w). If that's what we need, fine by me.
I'm always interested in the capability not the structure/naming.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and 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: [HACKERS] Adding WHERE clause to pg_dump

Simon Riggs wrote:
> On Fri, 2008-07-25 at 12:38 -0700, Joshua D. Drake wrote:
>
>
>> Gained. Code complexity.
>>
>
> Hardly, patch is very small. I would recognise that as a factor
> otherwise.
>
>
>> What I see is a recipe for inconsistent, un-restorable backups without a
>> user realizing what they have done.
>>
>
> I agree on the backup side, but then who would extract just a portion of
> their data for backup? It would be no backup at all.
>
> If you did use this as part of an incremental backup scheme, then they
> would have to test it (just like any backup method). Incremental backups
> rarely have self-consistency except as part of a greater whole.
>
> As a dev tool it makes sense.
>
>

I think we have yet another case for moving the core bits of pg_dump
into a library that can then be used by lots of clients. Until we do
that we're going to get continual pressure to add extra cases to pg_dump
unrelated to its principal functionality.

cheers

andrew

--
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] php + postgresql

Leif B. Kristensen wrote:
> On Friday 25. July 2008, Christophe wrote:
>
...
> My 2 cents: The prime reason for the popularity of PHP is probably the
> very gentle learning curve. You can start with a static HTML page, and
> introduce a few PHP snippets to show dynamic content. For us
> self-taught people, that means that you get instant results with
> minimal work.

Seems you never used a decent template engine, such as TAL
http://www.owlfish.com/software/simpleTAL/tal-guide.html

Which really is "code by example" instead of intermixing language
constructs with HTML which is incredibly hard to maintain.

> If any language want to compete with PHP in popularity, I believe that
> it must be just as easy to mingle with HTML. $DEITY, I would love to be
> able to include Perl code in a HTML page inside a pair of <?pl and ?>
> tags.

Most if not all other languages which are used for the web do have
those ways, which does not mean its recommended to do so.

> Now, I don't write PHP scripts like that anymore. I like to have every
> single character served as HTML to be generated by a function. And I

Which is for sure very performant ;)

> realize that Perl would do that even better than PHP. But as I have
> become quite proficient with PHP, I tend to keep using that. It surely
> does the job.

And hope that you arent bitten by nasty bugs in the language
implementation or your security configuration of it :-)

Ok, enough PHP bashing. Sun is shining here and so I invite everybody
to enjoy the weekend :-)

T.

Re: [GENERAL] php + postgresql

Joshua D. Drake wrote:
> On Sat, 2008-07-26 at 11:13 +0930, admin wrote:
>
>> Anyway, while I'm quite happy to continue banging out things that "just
>> work" in PHP for the time being, you suggest (in a subsequent post) that
>> there is one scripting language in particular that you'd use ... might I
>> enquire which language that is, and why? Just curious, I'm definitely
>> not looking for an ideological debate.
>
> You do realize that you just opened one of the longest, loudest and most
> inherently beer inducing arguments known to man since Emacs vs Vi?
> (answer: Joe) So why not! I use Python. I love Python. Although I
> guarantee you that others will say ruby, perl, java (well maybe not
> java).

I'd say python too but I intentionally left that out in the discussion
just to avoid that usual foo vs. bar discussion which isn't to win.

> The answer to your question is:
>
> Use what works for you.
But this might as well include that you know if that really works for
you instead of beeing something that you stumble over and hope it will
work (because it seems to work for so many others)

> I used PHP for years, I actually used Perl before PHP but got tired of
> the Perl oddness. I moved on to Python and love it. There are things in
> it I don't like (just see subprocess) but for the most part, its
> gorgeous.

Yeah, I used C (for the web), i tried perl and came to python. Whenever
I checked PHP I found it so bad designed (if at all) that it really
hurted. And occassionally I'm asked for help on PHP questions so I see
nothing essentially has changed on the matters for the last 10 years.
Its still confusing naming of functions (hello namespaces), not really
a type system (think '1' + 2 ) and the like. PHP5 didn't change much
because if you want to adopt OOP you could as well just use a language
which does this for years (even Ecmascript) or - as most seem to do -
just continue to code old style. This horrible mixing of code and HTML
is even found in JSP code these days.

T.

Re: [BUGS] BUG #4324: Default value for a column is not returned in select when column has not been explicitly set

Hello,

can you send any samples? This works for me:

postgres=# select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20070502 (Red Hat 4.1.2-12)
(1 row)
postgres=# create table g1(a bool default true null, c varchar);
CREATE TABLE
postgres=# insert into g1(c) VALUES ('ahoj');
INSERT 0 1
postgres=# select * from g1;
a | c
---+------
t | ahoj
(1 row)

postgres=# select version();
version
--------------------------------------------------------------------------------
PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070
(1 row)
postgres=# create table g1(a bool default true null, c varchar);
CREATE TABLE
postgres=# insert into g1(c) VALUES ('ahoj');
INSERT 0 1
postgres=# select * from g1;
a | c
---+------
t | ahoj
(1 row)

regards
Pavel Stehule
2008/7/26 Jeff Galyan <jeff@richrelevance.com>:
>
> The following bug has been logged online:
>
> Bug reference: 4324
> Logged by: Jeff Galyan
> Email address: jeff@richrelevance.com
> PostgreSQL version: 8.3.3
> Operating system: Linux
> Description: Default value for a column is not returned in select
> when column has not been explicitly set
> Details:
>
> When a column does not have a value explicitly set, v8.3.3 is not returning
> the default value for the column, as 8.2.3 used to (per section 11.5 of the
> SQL specification). The purpose of setting a default value for a column is
> so a value will be returned if the column has not been explicitly set. If a
> nullable column has no value but does have a default, the specification
> requires that the default value be returned. If the column's value has been
> explicitly set, then the value in the column must be returned. Further,
> when a default is specified in the column descriptor, INSERTs which omit
> setting a value for the column should automatically insert the default value
> into the column. Again, the behavior in 8.2 conformed with the SQL
> specification, section 11.5. 8.3 is not behaving per the spec.
>
> Example:
> Take an existing table with some data in it and add a nullable column of
> type boolean with default value true. In 8.2, 'select bool_column from
> my_table' would have returned 'true' for all rows where the column had not
> been explicitly set (which should be all of them at this point). Subsequent
> inserts would have the value automatically set to 'true' if no value was
> specified, or whatever value is explicitly specified. In 8.3, this case
> will return NULL for all rows where the value has not been explicitly
> specified. Per sec. 11.5 of the SQL spec, the behavior of v8.2 is correct.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
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] php + postgresql

Hi,

Aarni Ruuhimäki wrote:
> On Friday 25 July 2008 15:33, you wrote:
>> I would avoid that in favour of using $HOME/.pgpass
>>
>> http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html
>>
>> HTH
>> Tino
>
> Hi,
>
> Quite right you are. Or something like this?
>
> require("/eg/unknown_path/deep_somewhere_else/dbconnect_app_name.php")

Well this would be reinventing the wheel and also can really
cause accidently checking that into your version control system
which should be avoided for credentials holding files.

T.

Re: [HACKERS] Research/Implementation of Nested Loop Join optimization

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.com> writes:
>> "Manoel Henrique" <mhenriquesgbd@gmail.com> writes:
>>> Yes, I'm relying on the assumption that backwards scan has the same cost as
>>> forward scan, why shouldn't it?
>
>> Because hard drives only spin one direction
>
> Good joke, but to be serious: we expect that forward scans will result
> in the kernel doing read-ahead, which will allow overlapping of
> CPU work to process one page with the I/O to bring in the next page.

Well it wasn't a joke but you're right that it's not the whole picture. But
then neither is considering interleaving of I/O with CPU work.

Hard drives spin in a particular direction which means if you stream I/O
requests to them in that direction you can stream data off the hard drive as
fast as it passes under the read head. That's going to be 50-60MB/s for a
single modern 7200rpm drive.

On the other hand if you send an I/O request for the previous block then you
have to wait a whole rotation before it passes under the head. On a 7200rpm
drive that's over 8ms which is a *lot* of CPU work to interleave. The most
bandwidth you'll be able to get is under 1MB/s.

However there's another reason fadvise helps -- the kernel or the drive gets a
chance to reorder the I/O. If we read-ahead a whole track's worth of I/O
backwards then during the first 8ms latency the kernel has a chance to notice
that it should reorder the queued up requests and do them the right way
around.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres 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: [HACKERS] Adding WHERE clause to pg_dump

On Fri, 2008-07-25 at 12:38 -0700, Joshua D. Drake wrote:

> Gained. Code complexity.

Hardly, patch is very small. I would recognise that as a factor
otherwise.

> What I see is a recipe for inconsistent, un-restorable backups without a
> user realizing what they have done.

I agree on the backup side, but then who would extract just a portion of
their data for backup? It would be no backup at all.

If you did use this as part of an incremental backup scheme, then they
would have to test it (just like any backup method). Incremental backups
rarely have self-consistency except as part of a greater whole.

As a dev tool it makes sense.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and 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: [HACKERS] Transaction-controlled robustness for replication

On Sat, 2008-07-26 at 10:17 +0200, Markus Wanner wrote:
> What I still don't understand is, why you are speaking about
> "logical"
> replication. It rather sounds like an ordinary log shipping approach,
> where the complete WAL is sent over the wire. Nothing wrong with
> that,
> it certainly fits many needs and I welcome such a solution for
> Postgres.

Yes, first stage is log shipping. Second stage is either physical or
logical apply.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and 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: [HACKERS] Transaction-controlled robustness for replication

On Sat, 2008-07-26 at 10:17 +0200, Markus Wanner wrote:
>
> > Expensive as in we need to parse and handle each statement
> separately.
> > If we have a single parameter then much lower overhead.
>
> Is that really much of a concern when otherwise caring about network
> and i/o latency?

I believe so. Jens-Wolfhard has provided the solution it seems.

> And what about sane default settings per session and
> database, so you won't even need to explicitly set them for the
> majority of transactions?

Session pools.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and 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: [PATCHES] pg_dump additional options for performance

On Fri, 2008-07-25 at 19:16 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > [ pg_dump_beforeafter.v6.patch ]

> Unfortunately there's still a lot of work to do, and I don't feel like
> doing it so I'm bouncing this patch back for further work.

Fair enough. Thanks for the review.

> The key problem is that pg_restore is broken: it emits nearly the same
> output for --schema-before-data and --schema-after-data, because it
> doesn't have any way to distinguish which objects in a full dump file
> belong where. This is because the filtering logic was put in the wrong
> place, namely in the ArchiveEntry creation routines in pg_dump.c, when
> where it really needs to happen is while scanning the TocEntry list in
> RestoreArchive(). (Note: it is perhaps worth keeping the pg_dump.c
> filters so as to avoid doing extra server queries for objects that we
> aren't going to dump anyway, but the core logic has to be in
> RestoreArchive.)

My feeling is that this would take the patch off-track.

The key capability here is being able to split the dump into multiple
pieces. The equivalent capability on restore is *not* required, because
once the dump has been split the restore never needs to be. It might
seem that the patch should be symmetrical with respect to pg_dump and
pg_restore, but I see no use case for the pg_restore case.

The title of this email confirms that as original intention.

> I looked over this patch a bit. I have a proposal for a slightly
> different way of defining the new switches:
>
> * --schema-before-data, --data-only, and --schema-after-data can be
> specified in any combination to obtain any subset of the full dump.
> If none are specified (which would in itself be a useless combination)
> then the default is to dump all three sections, just as if all three
> were specified.
>
> * --schema-only is defined as equivalent to specifying both
> --schema-before-data and --schema-after-data.
>
> The patch as submitted enforces what seem largely arbitrary restrictions
> on combining these switches. It made some sense before to treat
> specifying both --schema-only and --data-only as an error, but it's not
> clear to me why you shouldn't be able to write both --schema-before-data
> and --schema-after-data, especially when there's a switch right beside
> them that appears to be equivalent to that combination. So let's just
> allow all the combinations.

I had it both ways at various points in development. I'm happy with what
you propose.

> The attached updated patch implements and documents this behavior,
> and gets rid of the special linkage between --disable-triggers and
> --data-only as previously discussed.

OK

> Another issue is that the rules for deciding which objects are "before
> data" and which are "after data" are wrong. In particular ACLs are after
> data not before data, which is relatively easy to fix.

OK

> Not so easy to fix
> is that COMMENTs might be either before or after data depending on what
> kind of object they are attached to.

Is there anything to fix? Comments are added by calls to dumpComment,
which are always made in conjunction with the dump of an object. So if
you dump the object you dump the comment. As long as objects are
correctly split out then comments will be also.

> (BTW, what about BLOB COMMENTS? They definitely can't be "before data".
> ISTM you could make a case for them being "after data", if you think that
> comments are always schema. But there is also a case for considering
> them as data, because the objects they are attached to are data. I kind
> of like the latter approach because it would create an invariant that
> comments appear in the same dump section as the object commented on.
> Thoughts?)

Yes, data. I'll look at this.

> Implementing the filtering by examining the type of a TocEntry in
> RestoreArchive is a bit of a PITA, but it's probably possible. The
> main bad thing about that is the need to make an explicit list of every
> type of TocEntry that exists now or ever has been emitted by any past
> version of pg_dump. The design concept was that the type tags are
> mainly documentation, and while we've had to bend that in places (mostly
> for backward-compatibility reasons) this would be the first place we'd
> have to throw it overboard completely.
>
> And there's yet another issue here, which is that it's not entirely clear
> that the type of an object uniquely determines whether it's before or
> after data. This might be an emergent property of the object sorting
> rules, but there is certainly not anything positively guaranteeing that
> the dependency-driven topological sort will produce such a result, and
> especially not that that will always be true in the future. So the
> approach seems a bit fragile.

Don't understand that. Objects are sorted in well-defined order,
specified in pg_dump_sort.c. Essentially we are saying that (according
to current numbering)

--schema-before-data priority 1-8
--data-only priority 9-11
--schema-after-data priority 12+

So the sort is explicitly defined, not implicit. I can add comments to
ensure that people changing the priority of objects across those
boundaries would be causing problems.

> We could perhaps get rid of that problem, as well as the need to implement
> object-type-determination logic, if we were to make RestoreArchive define
> the groupings according to position in the TocEntry list: everything
> before the first TABLE DATA or BLOB (and BLOB COMMENT?) entry is "before"
> data, everything after the last one is "after" data, everything in between
> is data. Then we only need to identify object types that are considered
> "data", which we already have a rule for (whether hadDumper is true).
> This is pretty attractive until you stop to consider the possibility
> that there aren't any data entries in an archive (ie, it was made with
> --schema-only): then there's no way to identify the boundary points.
>
> We could solve that problem by inserting a "dummy data" TOC entry where
> the data would have appeared, but this will only work in new archive
> files. With an implementation like this, pg_restore with
> --schema-before-data or --schema-after-data won't behave very nicely on a
> pre-8.4 --schema-only archive file. (Presumably it would act as though
> all the objects were "before" data.) Is that a small enough corner case
> to live with in order to gain implementation simplicity and robustness?

All of the above makes me certain I want to remove these options from
pg_restore.

> BTW, another incomplete item is that pg_dumpall should probably be taught
> to accept and pass down --schema-before-data and --schema-after-data
> switches.

OK

I'm conscious that the major work proposed will take weeks to complete
and we don't know what other problems it will cause (but I'm pretty
certain it will cause some). With regard to the use case, I see little
or no benefit from either of us doing that and regret I won't be able to
complete that.

Can we prune down to the base use case to avoid this overhead? i.e. have
these options on pg_dump only?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

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

Re: [HACKERS] Transaction-controlled robustness for replication

Hi,

Simon Riggs wrote:
> There is no sync() during WAL apply when each individual transaction
> hits commit. This is because there is "no WAL" i.e. changes comes from
> WAL to the database, so we have no need of a second WAL to protect the
> changes being made.

Aha, that makes much more sense to me now. So you are not just using the
WAL to transfer changes, but the remote WAL replaces the one on the
standby completely.

Do you have replication to multiple slaves on the radar?

What I still don't understand is, why you are speaking about "logical"
replication. It rather sounds like an ordinary log shipping approach,
where the complete WAL is sent over the wire. Nothing wrong with that,
it certainly fits many needs and I welcome such a solution for Postgres.

In another thread, you are saying that application of the WAL on the
standby is "logical". This sounds like one could choose to skip certain
parts of the WAL on the standby, but still the complete WAL would have
to be sent. So this would only save a bit of i/o on the standby, right?

> Expensive as in we need to parse and handle each statement separately.
> If we have a single parameter then much lower overhead.

Is that really much of a concern when otherwise caring about network and
i/o latency? And what about sane default settings per session and
database, so you won't even need to explicitly set them for the majority
of transactions?

Regards

Markus

--
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] PostgreSQL vs FreeBSD 7.0 as regular user

Zoltan Boszormenyi írta:
> Joshua D. Drake írta:
>
>> On Fri, 2008-07-25 at 22:39 +0200, Zoltan Boszormenyi wrote:
>>
>>
>>> Hi,
>>>
>>> is there anyone using PostgreSQL on FreeBSD 7.0 starting from scratch?
>>> I compiled 8.3.3 and wanted to run initdb in my home directory but
>>> it fails with the error below.
>>>
>>>
>>
>>
>>> I configured the shared memory settings in advance according to
>>> the PostgreSQL 8.3 online docs:
>>>
>>> $ cat /etc/sysctl.conf
>>> ...
>>> kern.ipc.shmall=32768
>>> kern.ipc.shmmax=134217728
>>> kern.ipc.semmap=256
>>>
>>> These three settings were also set with "sysctl -w ..." to take effect
>>> immediately.
>>>
>>>
>> Are you buy chance in a jail?
>>
>> Joshua D. Drake
>>
>>
>
> I don't know. How to determine? Running this as my own user:
> $ sysctl -a | grep "^kern.ipc"
> shows the same settings as above.
>
> Thanks.
>

Rebooting FreeBSD solved it. Huh? Is it really like W#&@$#&?
Anyway, thanks for the help.

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

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