Re: [GENERAL] Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 04:59:42PM +0200, Harald Armin Massa wrote:
> On Mon, May 19, 2008 at 04:50:18PM +0200, Bohdan Linda wrote:
> > I would like to ask an opinion on vacuuming general. Imagine situation
> > that you have single table with 5 fields (one varchar). This table has
> > during the day
> >
> > - cca 620 000 inserts
> > - 0 updates
> > - cca 620 000 deletes
> >
> > The table is vacuumed daily, but somehow after several months I got to
> > size of ~50GB
>
> do not vacuum DAILY. set up autovacuum to run AT LEAST every minute.

This will help if the changes to the database are evenly distributed
throughout the day, if they're very spiky then you may want to run
a vacuum after one of these bulk changes. I believe that on larger
databases for those tables that are incurring heavy modification the
admin would configure an associated session whose sole responsibility
would be to issue a never ending stream of VACUUMs. This tends to imply
larger RAID arrays that can tolerate multiple concurrent read/write
requests. I.e. something like the following, but with some error
checking:

for t in foo bar baz
do ( while echo "VACUUM $t;" && false ; do true ; done | psql ) &
done

As an ongoing thing, PG records partially filled pages in its so-called
"Free Space Map". The FSM gets updated by VACUUM and is used by the
data modification statements to find places where new data can be
written. If the FSM is too small then PG will grow the table even
though there is free space in the table (because it doesn't know it
exists). VACUUM will normally give error messages about the FSM map
being too small, that and the fact that you didn't mentioned this makes
me think you have already found an optimum value here.

Sam

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

[BUGS] Win32: Postgre 8.3.1 does not work with Active perl 5.10

1. Install Active perl 5.8
2. Install PostgreSQL 8.3.1
3. CREATE plperl FUNCTION 
4. Call this FUNCTION TO CHECK that plperl.dll IS loaded AND FUNCTION executed
5. Deinstall Active perl 5.8
6. Install Active perl 5.10
7. When you call plperl FUNCTION you get next error:
can NOT LOAD plperl.dll library

The problem is that PostgreSQL search for C:/Perl/Bin/Perl58.dll but I have Perl510.dll

Re: [PERFORM] I/O on select count(*)

> The real question in my mind is why this turns into a bottleneck before
> the similar task of cleaning the 16MB XLOG segment does. I expected
> that one would need to be cracked before the CLOG switch time could
> possibly be an issue, but reports from the field seem to suggest
> otherwise.

Hm, on current CPUs zeroing 8kB of RAM should take less than 2 us... now
if it has to be written to disk, that's another story !

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

[GENERAL] Using "ident sameuser" with Mac OS X Leopard

Hi

I can't seem to make the "ident sameuser" authentication method
work on Mac OS X Leopard (or "ident" with a specified mapping). It
seems Apple removed identd from the system, I can't find it and
nothing is listening on port 113. Any ideia how to make this work on
Leopard?

Yours

Miguel Arroz

Miguel Arroz
http://www.terminalapp.net
http://www.ipragma.com

Re: [pgsql-es-ayuda] Saber permisos de usuarios sobre tablas y vistas.

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

Hola de nuevo.

Gracias a vuestra ayuda he conseguido saber como conocer los privilegios
de un usuario en las tablas y vistas, mediante la función
has_table_privilege()

Sin ánimo de ser pesado, os voy a contar el porqué de mi interés sobre
este tema, por si estoy equivocado o alguien puede recomendarme un mejor
tratamiento.

He realizado recientemente una aplicación con PHP+POSTGRESQL 8.2.

Para hacer la conexión a la bd desde php, he usado un script con los
datos del superusuario y la función pg_connect(). Esto implica que cada
vez que desde php se ejecuta un query siempre es con el mismo usuario.

Para gestionar el resto de los usuarios, lo he hecho creando una tabla
para almacenar los datos y al iniciar el programa y tener que meter los
datos el usuario para logearse, el superusuario consulta los datos
(nombre y contraseña) en dicha tabla.

Esto hasta ahora me ha servido, pero siempre he tenido la sensación de
que no es lo mas adecuado, pues para algo tiene postgres los usuarios.

Si por ejemplo quisiera usar un trigger de auditoría (como el que viene
en la documentación de plpgsql), no podría recoger el usuario que
ejecuta el query, pues siempre sería el mismo.

Viendo la documentación, me ha parecido (no tengo un buen nivel de
ingles) que el crear grupos y/o usuarios es lo mismo que crear roles.
¿Cierto?. Luego, he observado que los grupos y usuarios son para todo el
cluster, no por base de datos.

Supongo que en la lista habrá mas de uno que trabaje con php y postgres,
y me gustaría saber como gestionan los usuarios, con usuarios de
postgres o con datos almacenados en tablas.

Saludos y muchas gracias.

- --

< ¡¡Nos vemos!!
----------------------------
\
\
.::!!!!!!!:.
.!!!!!:. .:!!!!!!!!!!!!
~~~~!!!!!!. .:!!!!!!!!!UWWW$$$
:$$NWX!!: .:!!!!!!XUWW$$$$$$$$$P
$$$$$##WX!: .<!!!!UW$$$$" $$$$$$$$#
$$$$$ $$$UX :!!UW$$$$$$$$$ 4$$$$$*
^$$$B $$$$\ $$$$$$$$$$$$ d$$R"
"*$bd$$$$ '*$$$$$$$$$$$o+#"
"""" """""""
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIMbK8K7lGsMchFswRAjJrAJ9hn2lT9pLJDZVJHplXFoVML80IuwCgn4mE
dbpLMwgMhBVPSSn4I1Yo9x4=
=rIQg
-----END PGP SIGNATURE-----
--
TIP 8: explain analyze es tu amigo

[pgsql-es-ayuda] De nuevo con el script de respaldo.

Saludos a la lista.

Tengo el mismo script en dos maquinas una con PostgreSQL 7.14.9 y otra
con 8.1 obviamente en el que tengo la version 8.1 tengo una base de
datos llamada postgres y en la del 7.14.9 no. En la de 8.1 traabaja
muy bien, hace un pg_dump y lo envia por correo inmediatamente - (por
cierto no me jala con pg_dump -Fc algo estare haciendo mal) pero en
la maquina de la version del 7.14.9 me da el siguiente error:

pg_dump: [archivador (bd)] Falló la conexión a la base de datos
"postgres": FATAL: no existe la base de datos "postgres"

y el script es el siguiente:

#!/bin/sh

cd /home/postgres/novos

D=`date +%y%m%d%H%M%S`

su -l postgres -c pg_dump solargi > solargi$D.sql

mutt -s "resp. solargi" -a "solargi$D.sql" "mi@mail" < /dev/null

#clear

exit

¿la primera pregunat que me surge es ¿ porque tiene que estar dada de
alta una base postgres si no la estoy llamando ?
¿tiene que ver con las versiones ?

¿alguna idea?

--
Ricardo Granados Tiznado
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?

http://archives.postgresql.org/pgsql-es-ayuda

Re: [HACKERS] triggers on prepare, commit, rollback... ?

Fabien COELHO wrote:
>
> Dear Tom,
>
>>> trigger on "prepare", "commit", "rollback", "savepoint",
>> Yup, and there won't be.
>
> That's a definite answer!
>
>> This has been suggested and rejected before. See the archives.
>
> I'll check into that.
>
>

This is a sufficiently frequently asked question that I wish someone
would add an entry to the FAQ about it, or add it to the TODO list's
"Features we don't want" section.

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: [pgsql-es-ayuda] Saber permisos de usuarios sobre tablas y vistas.

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

Fernando Moreno escribió:
> Si es para consulta interactiva desde psql, con \dp (o \z) te bastará,
> pero si deseas acceder a esos datos de manera programada, échale un
> vistazo a la sección 9.22 del manual de postgresql, donde se describen
> las funciones del tipo has_X_privilege()

Efectivamente de eso se trataba.

Muchas gracias.
- --

< ¡¡Nos vemos!! >
----------------------------
\
\
.::!!!!!!!:.
.!!!!!:. .:!!!!!!!!!!!!
~~~~!!!!!!. .:!!!!!!!!!UWWW$$$
:$$NWX!!: .:!!!!!!XUWW$$$$$$$$$P
$$$$$##WX!: .<!!!!UW$$$$" $$$$$$$$#
$$$$$ $$$UX :!!UW$$$$$$$$$ 4$$$$$*
^$$$B $$$$\ $$$$$$$$$$$$ d$$R"
"*$bd$$$$ '*$$$$$$$$$$$o+#"
"""" """""""
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIMaTrK7lGsMchFswRAmErAKDBGK1Wn2AI3NgNjsM4Yb1tG7IWgwCfW1cD
0fyrTijrv4xWtEwb9rOu3e4=
=Kg5q
-----END PGP SIGNATURE-----
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

[pgsql-es-ayuda] Cursos?! Mejor charlas y eventos :)

Todos,

Mi opinion es que por ahora no creo que haya chances de cursos que
vayan a perdurar en constancia y tomen algun valor como tales, pero si
de juntarnos y contarnos experiencias entre nosotros. Creo que eso va
a ser mas provechoso que un curso.

Por ejemplo, podemos juntarnos para algun evento. Sugiero el de
Software Libre en Argentina, creo que es en Agosto. Armamos un stand y
seguro podemos ofrecer alguna que otra charla. Si lo piensan, ademas
de vernos entre nosotros y contarnos nuestras propias experiencias,
podemos llegar a otras personas que aun no pasaron por la *experiencia
postgresql*.

Por supuesto escribo con animos de lograr relacionarnos a todos, y
si no es Argentina que sea en el lugar que todos decidan. Solo tiro
una piedra.

Buena semana para todos,
gb.-

--
Guido Barosio
-----------------------
http://www.globant.com
guido.barosio@globant.com
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [JDBC] SET client_encoding = 'UTF8'

On Mon, 19 May 2008, Tom Lane wrote:

> Oliver Jowett <oliver@opencloud.com> writes:
>> So we could add a special case in the driver to allow exactly "UTF8" as
>> well as "UNICODE", if that's the canonical way the server spells it
>> these days.
>
> +1 for that in any case, because UNICODE hasn't been the canonical
> spelling since 8.1.
>

OK, I'll make this happen. A work around for the immediate problem is to
use the URL parameter allowEncodingChanges=true.

Kris Jurka

http://jdbc.postgresql.org/documentation/83/connect.html#connection-parameters

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

[ODBC] [ psqlodbc-Bugs-1010397 ] SQLForeignKeys no rows returned

Bugs item #1010397, was opened at 2008-05-19 11:58
You can respond by visiting:

http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010397&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Farid Zidan (faridz)
Assigned to: Nobody (None)
Summary: SQLForeignKeys no rows returned

Initial Comment:
PostgreSQL ANSI 8.03.02.00 Windows XP.

This was not an issue with 8.02 driver

SQLRETURN rc = ::SQLForeignKeys(
pSQL->Gethstmt(),
NULL, 0,
NULL, 0,
NULL, 0,
( SQLCHAR * ) ( *szTableCat ? szTableCat : NULL ),
( SQLSMALLINT ) cbTableCat,
( SQLCHAR * ) ( *szTableSchem ? szTableSchem : NULL ),
( SQLSMALLINT ) cbTableSchem,
( SQLCHAR * ) szTableName,
( SQLSMALLINT ) cbTableName );

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

You can respond by visiting:

http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010397&group_id=1000125

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

Re: [pgsql-es-ayuda] Saber permisos de usuarios sobre tablas y vistas.

Si es para consulta interactiva desde psql, con \dp (o \z) te bastará, pero si deseas acceder a esos datos de manera programada, échale un vistazo a la sección 9.22 del manual de postgresql, donde se describen las funciones del tipo has_X_privilege()

2008/5/19 Pablo Braulio <brulics@gmail.com>:

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

Hola a todos.

Estoy iniciandome en el tema de los permisos de los usuarios en las
bases de datos.

El tema es, que no se como poder listar los permisos que tiene un
determinado usuario para cada tabla y vista.

Se que haciendo un select en pg_tables y pg_views, puedo listar las
tablas y vistas, pero no he encontrado ninguna tabla de postgres que
contenga los permisos de los usuarios.

Igual ya se ha hablado de esto en la lista, pero no lo he encontrado.

Gracias a todos.
- --


< ¡¡Nos vemos!!  >
 ----------------------------
   \
    \
                                  .::!!!!!!!:.
 .!!!!!:.                        .:!!!!!!!!!!!!
 ~~~~!!!!!!.                 .:!!!!!!!!!UWWW$$$
     :$$NWX!!:           .:!!!!!!XUWW$$$$$$$$$P
     $$$$$##WX!:      .<!!!!UW$$$$"  $$$$$$$$#
     $$$$$  $$$UX   :!!UW$$$$$$$$$   4$$$$$*
     ^$$$B  $$$$\     $$$$$$$$$$$$   d$$R"
       "*$bd$$$$      '*$$$$$$$$$$$o+#"
            """"          """""""
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIMZ3PK7lGsMchFswRAoOpAJ4lt2L+p3H08sMrwmQaHH92BjaJcgCfUtLu
AzImSlNqEEBU1DZTZBQlzwM=
=TxIr
-----END PGP SIGNATURE-----
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
              http://archives.postgresql.org/pgsql-es-ayuda

Re: [PERFORM] I/O on select count(*)

Greg Smith <gsmith@gregsmith.com> writes:
> The real question in my mind is why this turns into a bottleneck before
> the similar task of cleaning the 16MB XLOG segment does.

Because we do the latter off-line, or at least try to.

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: [JDBC] SET client_encoding = 'UTF8'

Oliver Jowett <oliver@opencloud.com> writes:
> It sounds like the root cause might be something like "let's feed
> pg_dump output to JDBC". So we could add a special case in the driver to
> allow exactly "UTF8" as well as "UNICODE", if that's the canonical way
> the server spells it these days.

+1 for that in any case, because UNICODE hasn't been the canonical
spelling since 8.1.

regards, tom lane

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

Re: [pgsql-es-ayuda] backup de base de datos

Leyendo la documentación de pg_dumpall, veo que su opción -r te podría servir.

El día 19 de mayo de 2008 10:03, Gabriel Ferro <gabrielrferro@yahoo.com.ar> escribió:

Masters, tengo que hacer un backup de un base de datos con dos esquemas, algunos usarios y algunos grupos, desde windows accedo al servidor con pgadmin III, la cosa es que cuando hago un restore de la DB me da errores en los GRANT ya que los usuarios y grupos aun no existen, hacer un un backup del server no puedo porque hay bd a las que no tengo acceso.
hay alguna manera de hacer un backup de los usuarios y los grupos? se me ocurre que asi restaurando estos primero podre hacerlo luego con las BD...

saludos.

---

Yahoo! Deportes Beta
¡No te pierdas lo último sobre el torneo clausura 2008!
Enterate aquí http://deportes.yahoo.com

Re: [PERFORM] I/O on select count(*)

On Mon, 19 May 2008, Matthew Wakeling wrote:

> Does it really take that long to zero out 8kB of RAM? I thought CPUs were
> really quick at doing that!

You don't get the whole CPU--you get time slices of one. Some of the
cases complaints have come in about have over a thousand connections all
fighting for CPU time, and making every one of them block for one guy who
needs to fiddle with memory for a while can be a problem. If you're
unlucky you won't even be on the same CPU you started on each time you get
a little check of time, and you'll run at the speed of RAM rather than
that of the CPU--again, fighting for RAM access with every other process
on the server.

The real question in my mind is why this turns into a bottleneck before
the similar task of cleaning the 16MB XLOG segment does. I expected that
one would need to be cracked before the CLOG switch time could possibly be
an issue, but reports from the field seem to suggest otherwise.

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

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

[pgadmin-hackers] SVN Commit by dpage: r7304 - branches/REL-1_8_0_EDB/pgadmin3/pgadmin/frm

Author: dpage

Date: 2008-05-19 16:42:33 +0100 (Mon, 19 May 2008)

New Revision: 7304

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7304&view=rev

Log:
Set the password correctly for plugins

Modified:
branches/REL-1_8_0_EDB/pgadmin3/pgadmin/frm/plugins.cpp

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

Re: [JDBC] SET client_encoding = 'UTF8'

Tom Lane wrote:
> Daniel Migowski <dmigowski@ikoffice.de> writes:
>> Kris Jurka schrieb:
>>> On Sun, 18 May 2008, Daniel Migowski wrote:
>>>> The command SET client_encoding = 'UTF8'
>>> throws an exception in the driver, because the driver expects UNICODE.
>>> This has been discussed before and the problem is that there are a too
>>> many ways to say UTF8 [1]. You can say UTF8, UTF-8, UTF -- 8, and so
>>> on. Perhaps we should strip all spaces and dashes prior to comparison?
>
> Perhaps we should make the backend return the values of client_encoding
> and server_encoding in canonical form (ie, "UTF8") regardless of the
> spelling variant the user used. I'm not thrilled with having JDBC
> thinking it knows the conversion algorithm the backend uses.
>
> Of course, such a change would break code relying on the older behavior
> :-(

Not sure if this is a big enough issue to warrant a server change. It
only happens when a JDBC client issues a manual SET client_encoding to
an encoding that's UTF8 but isn't spelled "UNICODE". That's going to be
a no-op anyway, so I'm not entirely clear why the client needs to be
sending it in the first place.

It sounds like the root cause might be something like "let's feed
pg_dump output to JDBC". So we could add a special case in the driver to
allow exactly "UTF8" as well as "UNICODE", if that's the canonical way
the server spells it these days.

-O

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

[pgadmin-hackers] SVN Commit by dpage: r7303 - in trunk/pgadmin3: . pgadmin/db pgadmin/frm pgadmin/include/db plugins

Author: dpage

Date: 2008-05-19 16:39:34 +0100 (Mon, 19 May 2008)

New Revision: 7303

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7303&view=rev

Log:
Allow external utilities such as pg_dump & pg_restore to work with SSL connections.

Modified:
trunk/pgadmin3/CHANGELOG
trunk/pgadmin3/pgadmin/db/pgConn.cpp
trunk/pgadmin3/pgadmin/frm/frmBackup.cpp
trunk/pgadmin3/pgadmin/frm/frmBackupGlobals.cpp
trunk/pgadmin3/pgadmin/frm/frmBackupServer.cpp
trunk/pgadmin3/pgadmin/frm/frmRestore.cpp
trunk/pgadmin3/pgadmin/frm/plugins.cpp
trunk/pgadmin3/pgadmin/include/db/pgConn.h
trunk/pgadmin3/plugins/utilities.ini

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

Re: [PERFORM] I/O on select count(*)

Bruce Momjian <bruce@momjian.us> writes:
> Matthew Wakeling wrote:
>> Does it really take that long to zero out 8kB of RAM? I thought CPUs were
>> really quick at doing that!

> Yea, that was my assumption too.

You have to write the page (to be sure there is space for it on disk)
not only zero it.

This design is kind of a holdover, though, from back when we had one
ever-growing clog file. Today I'd be inclined to think about managing
it more like pg_xlog, ie, have some background process pre-create a
whole segment file at a time.

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

[pgsql-es-ayuda] Saber permisos de usuarios sobre tablas y vistas.

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

Hola a todos.

Estoy iniciandome en el tema de los permisos de los usuarios en las
bases de datos.

El tema es, que no se como poder listar los permisos que tiene un
determinado usuario para cada tabla y vista.

Se que haciendo un select en pg_tables y pg_views, puedo listar las
tablas y vistas, pero no he encontrado ninguna tabla de postgres que
contenga los permisos de los usuarios.

Igual ya se ha hablado de esto en la lista, pero no lo he encontrado.

Gracias a todos.
- --

< ¡¡Nos vemos!! >
----------------------------
\
\
.::!!!!!!!:.
.!!!!!:. .:!!!!!!!!!!!!
~~~~!!!!!!. .:!!!!!!!!!UWWW$$$
:$$NWX!!: .:!!!!!!XUWW$$$$$$$$$P
$$$$$##WX!: .<!!!!UW$$$$" $$$$$$$$#
$$$$$ $$$UX :!!UW$$$$$$$$$ 4$$$$$*
^$$$B $$$$\ $$$$$$$$$$$$ d$$R"
"*$bd$$$$ '*$$$$$$$$$$$o+#"
"""" """""""
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIMZ3PK7lGsMchFswRAoOpAJ4lt2L+p3H08sMrwmQaHH92BjaJcgCfUtLu
AzImSlNqEEBU1DZTZBQlzwM=
=TxIr
-----END PGP SIGNATURE-----
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?

http://archives.postgresql.org/pgsql-es-ayuda

[HACKERS] DTrace probes.

Howdy,

I just saw Robert Lor's patch w.r.t. dtrace probes. It looks very
similar in what we've done. We run a nice set of probes in production
here that allow us to track the details of checkpointing and statement
execution. I've given a few presentations around these probes and
have had very positive feedback. They've been available for a while
now, but I never got around to sending them to the list:

https://labs.omniti.com/trac/project-dtrace/browser/trunk/postgresql/8.3.1.patch?format=txt

Documentation is in wiki format, but I'd be happy to convert it to
something else:

https://labs.omniti.com/trac/project-dtrace/wiki/Applications#PostgreSQL

Best regards,

Theo

--
Theo Schlossnagle
Esoteric Curio -- http://lethargy.org/
OmniTI Computer Consulting, Inc. -- http://omniti.com/

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

Re: [GENERAL] Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 04:59:42PM +0200, Harald Armin Massa wrote:
> do not vacuum DAILY. set up autovacuum to run AT LEAST every minute.
> autovacuum will flag the "deleted" rows as to be reusable by next
> insert. Make sure to use 8.3.<latest>, it's much more easy to setup
> autovacuum then before.

Hello Harald,

Thank you, will look at that. My problem is I have to use 8.0.x, but it
should be supported.

Regards,
Bohdan

--
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] Postgres se pasma!!

----------------------------------------
> Date: Fri, 16 May 2008 23:47:29 -0500
> From: systemguards@gmail.com
> To: alejandro.dburne@gmail.com
> Subject: Re: [pgsql-es-ayuda] Postgres se pasma!!
> CC: rev_angel@hotmail.com; yourpadre@gmail.com; pgsql-es-ayuda@postgresql.org
>
> 2008/5/16 Alejandro D. Burne :
>> El día 16 de mayo de 2008 21:11, Miguel Angel .
>> escribió:
>>>
>>> Disculpen lo que uso es
>>>
>>> SO: Linux Debian con nucleo 2.6.18-6-686-bigmem
>>>
>>> Memoria: 4 GB
>>>
>>
>> El comando "top" que muestra
>>
>
> Seria interesante ver ademas el postgresql.conf
>
> y este servidor funcionaba antes? que le hiciste antes que deje de funcionar?
>
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Guayaquil - Ecuador
> Cel. (593) 087171157

El servidor funcionaba perfectamente y los únicos cambios que se han realizado son dos:
- Una actualización a los cubos los cuales no tocarón ninguna base de datos solo fueron archivos de configuración de los mismos.
- Una actualización para el ERP que usamos en el cual si se tuvo que cargar la nueva base quitando la anterior, lo cual fue
un procedimiento de lo más inocente (respaldar base anterior, borrar, crear y cargar la nueva información en esta base).

Adjunto mi archivo de postgresql.conf, el log de postgres (como estaba muy grande solo he dejado las últimas partes) y finalmente un archivo el cual contiene las estadísticas del disco duro, el comando top y parte de un script que uso para respaldar las bases de datos.
El procesador es xeon pero por ahora no recuerdo bien la velocidad aunque puedo garantizar que es uno bastante poderoso.

Pudieran decirme eué significa entrar en modo interactivo, para poder probar si se puede acceder así?

_________________________________________________________________
Tecnología, moda, motor, viajes,…suscríbete a nuestros boletines para estar siempre a la última
http://newsletters.msn.com/hm/maintenanceeses.asp?L=ES&C=ES&P=WCMaintenance&Brand=WL&RU=http%3a%2f%2fmail.live.com

Re: [GENERAL] Vacuuming on heavily changed databases

Apart from reinterating what someone else: you're not vacuuming
anywhere near often often. Normal vacuum takes no locks.

On Mon, May 19, 2008 at 04:50:18PM +0200, Bohdan Linda wrote:
> 3) There were suggestions (in archives) doing dump and then restore on
> dropped database, but still requires downtime.

CLUSTER will rebuild the table with only the actually used records in
it. It also requires an exclusive lock so try to keep it small :)

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org>

http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: [ADMIN] Global / cluster-wide functions

Joshua D. Drake wrote:

On Fri, 16 May 2008 15:12:02 -0600 kevin kempter <kevin@kevinkempterllc.com> wrote:    

Hi List;  Can I install a PL/pgSQL function so it's global to all databases   within a cluster or do I need to install per database ?      

 Install it into template1 then each database created after that will have the function.   

As a corollary to that, you'll need to create the function in all existing databases, since changes to the template database only apply to new databases.

--  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 Ask me about OTG's expert PostgreSQL, PostGIS, and other Open Source training offerings. 

[pgsql-es-ayuda] backup de base de datos

Masters, tengo que hacer un backup de un base de datos con dos esquemas, algunos usarios y algunos grupos, desde windows accedo al servidor con pgadmin III, la cosa es que cuando hago un restore de la DB me da errores en los GRANT ya que los usuarios y grupos aun no existen, hacer un un backup del server no puedo porque hay bd a las que no tengo acceso.
hay alguna manera de hacer un backup de los usuarios y los grupos? se me ocurre que asi restaurando estos primero podre hacerlo luego con las BD...

saludos.

---

Yahoo! Deportes Beta
¡No te pierdas lo último sobre el torneo clausura 2008!
Enterate aquí http://deportes.yahoo.com

Re: [HACKERS] triggers on prepare, commit, rollback... ?

Dear Tom,

>> trigger on "prepare", "commit", "rollback", "savepoint",
> Yup, and there won't be.

That's a definite answer!

> This has been suggested and rejected before. See the archives.

I'll check into that.

>> It seems to me that such triggers would be useful to help implement a
>> "simple" (hmmm...) synchroneous replication system,
>
> That argument has no credibility whatever.

If you say so.

> We have not even been able to get the replication projects to agree on a
> common set of custom hooks; the chance that they could get by with
> triggers on SQL-visible events is nil.

That is indeed an issue.

On the other hand, there are several possible strategies to implement
replication, but ISTM that all should require a hook (whether SQL visible
or not) at the prepare/commit levels to play around with the 2PC.

Well, thanks for your answer anyway,

--
Fabien.

--
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] Vacuuming on heavily changed databases

Hello,

> I would like to ask an opinion on vacuuming general. Imagine situation
> that you have single table with 5 fields (one varchar). This table has
> during the day
>
> - cca 620 000 inserts
> - 0 updates
> - cca 620 000 deletes
>
> The table is vacuumed daily, but somehow after several months I got to
> size of ~50GB

do not vacuum DAILY. set up autovacuum to run AT LEAST every minute.
autovacuum will flag the "deleted" rows as to be reusable by next
insert. Make sure to use 8.3.<latest>, it's much more easy to setup
autovacuum then before.

best wishes,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pidgeon
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

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

Re: [PERFORM] I/O on select count(*)

Matthew Wakeling wrote:
> On Fri, 16 May 2008, Tom Lane wrote:
> > One additional point: this means that one transaction in every 32K
> > writing transactions *does* have to do extra work when it assigns itself
> > an XID, namely create and zero out the next page of pg_clog. And that
> > doesn't just slow down the transaction in question, but the next few
> > guys that would like an XID but arrive on the scene while the
> > zeroing-out is still in progress.
> >
> > This probably contributes to the behavior that Simon and Josh regularly
> > complain about, that our transaction execution time is subject to
> > unpredictable spikes. I'm not sure how to get rid of it though.
>
> Does it really take that long to zero out 8kB of RAM? I thought CPUs were
> really quick at doing that!

Yea, that was my assumption 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-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[pgadmin-hackers] SVN Commit by dpage: r7302 - trunk/www/development

Author: dpage

Date: 2008-05-19 15:53:47 +0100 (Mon, 19 May 2008)

New Revision: 7302

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7302&view=rev

Log:
Roadmap update

Modified:
trunk/www/development/roadmap.php

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

[pgadmin-hackers] SVN Commit by dpage: r7301 - in trunk/pgadmin3: . pgadmin/frm pgadmin/ui

Author: dpage

Date: 2008-05-19 15:51:48 +0100 (Mon, 19 May 2008)

New Revision: 7301

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7301&view=rev

Log:
Add support for pg_restore's --clean option. Fix single object restores while we're at it.

Modified:
trunk/pgadmin3/CHANGELOG
trunk/pgadmin3/pgadmin/frm/frmRestore.cpp
trunk/pgadmin3/pgadmin/ui/frmRestore.xrc
trunk/pgadmin3/pgadmin/ui/xrcDialogs.cpp

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

[GENERAL] Vacuuming on heavily changed databases

Hello,

I would like to ask an opinion on vacuuming general. Imagine situation
that you have single table with 5 fields (one varchar). This table has
during the day

- cca 620 000 inserts
- 0 updates
- cca 620 000 deletes

The table is vacuumed daily, but somehow after several months I got to
size of ~50GB

Result of VACUUM FULL VERBOSE ANALYZE is:

Nonremovable row versions range from 102 to 315 bytes long.
There were 218253801 unused item pointers.
Total free space (including removable row versions) is 40627058888 bytes.
4850610 pages are or will become empty, including 0 at the end of the
table.
5121624 pages containing 40625563500 free bytes are potential move
destinations.
CPU 161.85s/35.51u sec elapsed 1191.17 sec.

This means 80% wasted space that could be reused. Right now, I am doing
vacuum full but this requires exclusive lock. During that time the
database is locked so I am missing "inserts and deletes" ;-)

I would like to avoid this in future, so I would like to prepare strategy
how to do it next time or avoid. Basically I have the follwing
limitations:

1) sometimes deletes vs vacuum analyze does not help, extra space is not
relcaimed. Do not know why this is happening, but maybe vacuum cannot get
lock

2) manualy evoked vacuum full requires bringing database long-time offline

3) There were suggestions (in archives) doing dump and then restore on
dropped database, but still requires downtime.

What would be your strategy for the database maintenance like this? What
tweaking of vacuuming can I make, so I do not get those "forgotten"
records?

Thank you,
Bohdan

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

[pgsql-www] Korean Postgres web site is a bad URL

I am getting a 404 from the Korean Postgres web site listed on our web
site:

http://www.postgresql.org/community/international

The bad link is:

http://www.ebcom.co.kr/ebcom/case3.html

I hear the site can be accessed from some places outside the USA.

Is this a better URL?

http://postgresql.kr/

Does anyone have a Korean contact we can ask?

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

Re: [pgsql-es-ayuda] backup con pg_dump

Diego Ayala escribió:
> Si, funciona, por que lo hago con otros bases de datos, mas pequeños, que se
> guardan sin problemas..!!! , alguien me comento que podria ser el formato
> tar, que tiene una limitante, ahora voy a investigar sobre el uso del
> mismo..

No uses el formato tar. Tiene bugs. Usa -Fc.

--
Alvaro Herrera

http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
TIP 7: no olvides aumentar la configuración del "free space map"

Re: [PATCHES] [HACKERS] WITH RECURSIVE patch V0.1

"Martijn van Oosterhout" <kleptog@svana.org> writes:

> From an implementation point of view, the only difference between
> breadth-first and depth-first is that your tuplestore needs to be LIFO
> instead of FIFO.

I think it's not so simple. How do you reconcile that concept with the join
plans like merge join or hash join which expect you to be able to be able to
process the records in a specific order?

It sounds like you might have to keep around a stack of started executor nodes
or something but hopefully we can avoid anything like that because, well, ick.

--
Gregory Stark
EnterpriseDB

http://www.enterprisedb.com

Ask me about EnterpriseDB's PostGIS 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: [GENERAL] pgxs question - linking c-functions to external libraries

johnduffy@f2s.com writes:
> My functions compile fine using PGXS, but don't link against the GSL libraries.
> How do I specify the GSL libraries as external libraries to link against in the
> PGXS Makfile? I have tried the following which don't work:

> PG_CPPFLAGS = -lgsl -lgslcblas
> PG_LIBS = -lgsl -gslcblas
> SHLIB_LINK = -lgsl -gslcblas

> My PGXS makefile is:

> MODULES pg_gsl
> PGXS := $(shell $(PG_CONFIG) --pgxs)
> include $(PGXS)

SHLIB_LINK is the right thing for MODULES. Maybe you forgot a -L
switch?

regards, tom lane

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

Re: [HACKERS] triggers on prepare, commit, rollback... ?

Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I've played with triggers a bit, and I have noticed that there seem to be
> no way to add a trigger on events such as "prepare", "commit", "rollback",
> "savepoint",

Yup, and there won't be. This has been suggested and rejected before.
See the archives.

> It seems to me that such triggers would be useful to help implement a
> "simple" (hmmm...) synchroneous replication system,

That argument has no credibility whatever. We have not even been able
to get the replication projects to agree on a common set of custom
hooks; the chance that they could get by with triggers on SQL-visible
events is nil.

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: [JDBC] SET client_encoding = 'UTF8'

Daniel Migowski <dmigowski@ikoffice.de> writes:
> Kris Jurka schrieb:
>> On Sun, 18 May 2008, Daniel Migowski wrote:
>>> The command SET client_encoding = 'UTF8'
>
>> throws an exception in the driver, because the driver expects UNICODE.
>> This has been discussed before and the problem is that there are a too
>> many ways to say UTF8 [1]. You can say UTF8, UTF-8, UTF -- 8, and so
>> on. Perhaps we should strip all spaces and dashes prior to comparison?

Perhaps we should make the backend return the values of client_encoding
and server_encoding in canonical form (ie, "UTF8") regardless of the
spelling variant the user used. I'm not thrilled with having JDBC
thinking it knows the conversion algorithm the backend uses.

Of course, such a change would break code relying on the older behavior
:-(

regards, tom lane

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

[pgsql-es-ayuda] Inquietud sobre mi estado en la Lista

Buenos días:

Desde hace yá bastante tiempo, no he recibido a mi correo ninguno de los post de la lista, lo que me hace pensar que fuí retirado de la misma, pero en ningún caso yo he hecho tal solicitud. Solicito por favor verificar mi estado, ya que me considero activo y así deseo seguir.

Gracias por su colaboración


William Enrique Parra Alba
Ingeniero De Sistemas
Universidad Pedagógica y Tecnológica de Colombia
/\ /\
/ //\\ \
\ \\// /
/ / \ \
\/ \/

---

Yahoo! Deportes Beta
¡No te pierdas lo último sobre el torneo clausura 2008!
Entérate aquí http://deportes.yahoo.com

Re: [HACKERS] notification information functions

Hannu Krosing <hannu@krosing.net> writes:
> How will we know then that all listeners have received their events ?

We won't, but we don't know that now. In both the current
implementation and this proposed one, the most you can tell is whether a
backend has absorbed an event notification, not whether it has passed it
on to its client. ISTM the timing of the first event is an
implementation artifact and not interesting for users.

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: [HACKERS] WITH RECURSIVE patch V0.1

On Sun, 2008-05-18 at 22:17 -0700, David Fetter wrote:
> On Mon, May 19, 2008 at 12:21:20AM -0400, Gregory Stark wrote:
> > "Zoltan Boszormenyi" <zb@cybertec.at> writes:
> > > Also, it seems there are no infinite recursion detection:
> > >
> > > # with recursive x(level, parent, child) as (
> > > select 1::integer, * from test_connect_by where parent is null
> > > union all
> > > select x.level + 1, base.* from test_connect_by as base, x where base.child
> > > = x.child
> > > ) select * from x;
> > > ... it waits and waits and waits ...
> >
> > Well, psql might wait and wait but it's actually receiving rows. A
> > cleverer client should be able to deal with infinite streams of
> > records.
>
> That would be a very good thing for libpq (and its descendants) to
> have :)
>
> > I think DB2 does produce a warning if there is no clause it can
> > determine will bound the results. But that's not actually reliable.
>
> I'd think not, as it's (in some sense) a Halting Problem.
>
> > It's quite possible to have clauses which will limit the output but
> > not in a way the database can determine. Consider for example a
> > tree-traversal for a binary tree stored in a recursive table
> > reference. The DBA might know that the data contains no loops but
> > the database doesn't.
>
> I seem to recall Oracle's implementation can do this traversal on
> write operations, but maybe that's just their marketing.

It may be possible to solve at least some of it by doing something
similar to hash version of DISTINCT by having an hashtable of tuples
already returned and not descending branches where you have already
been.

> Cheers,
> David.
> --
> David Fetter <david@fetter.org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david.fetter@gmail.com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>

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

[COMMITTERS] libpqtypes - libpqtypes: bug fix in datetime.c, out-of-range time_t

Log Message:
-----------
bug fix in datetime.c, out-of-range time_t values not 0-basing month value for use with struct tm

Modified Files:
--------------
libpqtypes:
configure.ac (r1.3 -> r1.4)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/configure.ac.diff?r1=1.3&r2=1.4)
libpqtypes/src:
datetime.c (r1.3 -> r1.4)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/libpqtypes/libpqtypes/src/datetime.c.diff?r1=1.3&r2=1.4)

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

[pgadmin-hackers] SVN Commit by dpage: r7300 - in trunk/pgadmin3/pgadmin: debugger dlg frm

Author: dpage

Date: 2008-05-19 14:21:06 +0100 (Mon, 19 May 2008)

New Revision: 7300

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7300&view=rev

Log:
Use wxLogInfo, not wxLogDebug (which doesn't seem to work all the time)

Modified:
trunk/pgadmin3/pgadmin/debugger/dbgPgThread.cpp
trunk/pgadmin3/pgadmin/debugger/dlgDirectDbg.cpp
trunk/pgadmin3/pgadmin/dlg/dlgClasses.cpp
trunk/pgadmin3/pgadmin/frm/plugins.cpp

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

[pgadmin-hackers] SVN Commit by dpage: r7299 - trunk/www/development

Author: dpage

Date: 2008-05-19 14:16:17 +0100 (Mon, 19 May 2008)

New Revision: 7299

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7299&view=rev

Log:
Update roadmap

Modified:
trunk/www/development/roadmap.php

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

[pgadmin-hackers] SVN Commit by dpage: r7298 - in trunk/pgadmin3/pgadmin: . agent dlg frm include include/utils slony utils

Author: dpage

Date: 2008-05-19 14:00:20 +0100 (Mon, 19 May 2008)

New Revision: 7298

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7298&view=rev

Log:
Cleanup some externs

Modified:
trunk/pgadmin3/pgadmin/agent/pgaJob.cpp
trunk/pgadmin3/pgadmin/agent/pgaStep.cpp
trunk/pgadmin3/pgadmin/dlg/dlgEditGridOptions.cpp
trunk/pgadmin3/pgadmin/frm/events.cpp
trunk/pgadmin3/pgadmin/frm/frmBackup.cpp
trunk/pgadmin3/pgadmin/frm/frmBackupGlobals.cpp
trunk/pgadmin3/pgadmin/frm/frmBackupServer.cpp
trunk/pgadmin3/pgadmin/frm/frmHint.cpp
trunk/pgadmin3/pgadmin/frm/frmRestore.cpp
trunk/pgadmin3/pgadmin/include/pgAdmin3.h
trunk/pgadmin3/pgadmin/include/utils/sysSettings.h
trunk/pgadmin3/pgadmin/pgAdmin3.cpp
trunk/pgadmin3/pgadmin/slony/dlgRepCluster.cpp
trunk/pgadmin3/pgadmin/utils/misc.cpp
trunk/pgadmin3/pgadmin/utils/pgconfig.cpp

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

Re: [HACKERS] ignore $PostgreSQL lines in regression tests?

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
> > Recently while adding $PostgreSQL markers to a bunch of .c and .h files
> > I ran into trouble with the ecpg regression tests and had to revert the
> > change for a handful of files. However, it occurred to me that we could
> > have pg_regress tell diff to ignore such lines, by passing it the
> > arguments "-I '\$PostgreSQL:' ", which would tell it to ignore
> > additions or deletions of lines matching that regex.
>
> > Would this be a good thing to do?
>
> I'm inclined to think not. It's easy to think of scenarios where such
> a switch would mask errors.

I tend to agree with this, though if people decide they want it, you
could almost certainly tighten up the regexp some to reduce the chance
of it masking things (eg: I assume a starting anchor ('^') would be
correct here, and you could almost certainly add the rest of the columns
which are included in the $Id$ format using appropriately-typed
wildcards, etc...).

Thanks,

Stephen

Re: [HACKERS] Link requirements creep

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Greg Smith <gsmith@gregsmith.com> writes:
> > When we noticed this recently, my digging suggested you'll be hard pressed
> > to have a RedHat system now without those two installed.
>
> Indeed, I've not heard any squawks from the field yet. It's still
> wrong though ...

Unsuprisingly, half the world in Debian also depends on libxml2, but I
agree 110% w/ Tom- it's wrong, and I feel it really ought to be fixed
regardless. It's entirely likely that there will come a time when it's
a less used library getting pulled in, too. I also personally hate
useless clutter in dependencies as it can cause package management
headaches..

After poking around a bit I did find a box that only pulled in libxml2
for subversion, and we've been talking about moving to a different SCM
(which don't appear to depend on libxml2), so it might eventually only
be pulled in by psql for us. Not a show-stopper, but it's also not
completely out of the question that it'll get pulled in unnecessairly.

Thanks,

Stephen

[GENERAL] pgxs question - linking c-functions to external libraries

Hi

I have written a number of C-Function which compile and link against the GNU
Scientific Library libraries 'libgsl' and 'libgslcblas' when using a hand
written Makefile, but I would like to use PGXS.

My functions compile fine using PGXS, but don't link against the GSL libraries.
How do I specify the GSL libraries as external libraries to link against in the
PGXS Makfile? I have tried the following which don't work:

PG_CPPFLAGS = -lgsl -lgslcblas
PG_LIBS = -lgsl -gslcblas
SHLIB_LINK = -lgsl -gslcblas

My PGXS makefile is:

MODULES pg_gsl
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Many thanks
John

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

Re: [PATCHES] [HACKERS] WITH RECURSIVE patch V0.1

On Sun, 2008-05-18 at 22:17 -0700, David Fetter wrote:
> On Mon, May 19, 2008 at 12:21:20AM -0400, Gregory Stark wrote:
> > "Zoltan Boszormenyi" <zb@cybertec.at> writes:
> > > Also, it seems there are no infinite recursion detection:
> > >
> > > # with recursive x(level, parent, child) as (
> > > select 1::integer, * from test_connect_by where parent is null
> > > union all
> > > select x.level + 1, base.* from test_connect_by as base, x where base.child
> > > = x.child
> > > ) select * from x;
> > > ... it waits and waits and waits ...
> >
> > Well, psql might wait and wait but it's actually receiving rows. A
> > cleverer client should be able to deal with infinite streams of
> > records.
>
> That would be a very good thing for libpq (and its descendants) to
> have :)
>
> > I think DB2 does produce a warning if there is no clause it can
> > determine will bound the results. But that's not actually reliable.
>
> I'd think not, as it's (in some sense) a Halting Problem.
>
> > It's quite possible to have clauses which will limit the output but
> > not in a way the database can determine. Consider for example a
> > tree-traversal for a binary tree stored in a recursive table
> > reference. The DBA might know that the data contains no loops but
> > the database doesn't.
>
> I seem to recall Oracle's implementation can do this traversal on
> write operations, but maybe that's just their marketing.

It may be possible to solve at least some of it by doing something
similar to hash version of DISTINCT by having an hashtable of tuples
already returned and not descending branches where you have already
been.

> Cheers,
> David.
> --
> David Fetter <david@fetter.org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david.fetter@gmail.com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>

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