Friday, May 16, 2008

Re: [PATCHES] libpq object hooks

Merlin Moncure wrote:
>
>> Also, even if varargs are safe they'd be notationally unpleasant
>> in the extreme. varargs are just a PITA to work with --- you'd have
>> to do all the decoding in the first-level hook routine, even for
>> items you weren't going to use. With something like the above
>> all you need is a switch() and some pointer casts.
>>
>
> Switch, plus struct (basically a union) will do the trick nicely. Can
> it be a formal union, or is it better as a void*?
>
> The main issue was how what we called the 'hook data' was passed back
> and forth. We'll get a patch up.
>
>
>

All of this is getting quite a long way from what was in the commitfest
queue. Do we still want to try to get this in this cycle, or should it
be marked returned to author for more work?

cheers

andrew

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

[COMMITTERS] pgsql: Add localization support to ecpg.

Log Message:
-----------
Add localization support to ecpg.

Author: Euler Taveira de Oliveira <euler@timbira.com>

Modified Files:
--------------
pgsql/src/interfaces/ecpg/compatlib:
informix.c (r1.54 -> r1.55)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/compatlib/informix.c?r1=1.54&r2=1.55)
pgsql/src/interfaces/ecpg/ecpglib:
connect.c (r1.50 -> r1.51)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ecpglib/connect.c?r1=1.50&r2=1.51)
data.c (r1.40 -> r1.41)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ecpglib/data.c?r1=1.40&r2=1.41)
descriptor.c (r1.29 -> r1.30)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ecpglib/descriptor.c?r1=1.29&r2=1.30)
error.c (r1.19 -> r1.20)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ecpglib/error.c?r1=1.19&r2=1.20)
execute.c (r1.77 -> r1.78)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ecpglib/execute.c?r1=1.77&r2=1.78)
misc.c (r1.41 -> r1.42)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ecpglib/misc.c?r1=1.41&r2=1.42)
prepare.c (r1.28 -> r1.29)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ecpglib/prepare.c?r1=1.28&r2=1.29)
pgsql/src/interfaces/ecpg/include:
ecpglib.h (r1.76 -> r1.77)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/include/ecpglib.h?r1=1.76&r2=1.77)
pgsql/src/interfaces/ecpg/preproc:
descriptor.c (r1.26 -> r1.27)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/preproc/descriptor.c?r1=1.26&r2=1.27)
ecpg.c (r1.104 -> r1.105)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/preproc/ecpg.c?r1=1.104&r2=1.105)
pgc.l (r1.164 -> r1.165)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/preproc/pgc.l?r1=1.164&r2=1.165)
preproc.y (r1.364 -> r1.365)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/preproc/preproc.y?r1=1.364&r2=1.365)
type.c (r1.78 -> r1.79)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/preproc/type.c?r1=1.78&r2=1.79)
variable.c (r1.44 -> r1.45)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/preproc/variable.c?r1=1.44&r2=1.45)
pgsql/src/interfaces/ecpg/test/expected:
compat_informix-rnull.stderr (r1.5 -> r1.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/compat_informix-rnull.stderr?r1=1.5&r2=1.6)
compat_informix-test_informix.stderr (r1.9 -> r1.10)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stderr?r1=1.9&r2=1.10)
compat_informix-test_informix.stdout (r1.4 -> r1.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stdout?r1=1.4&r2=1.5)
compat_informix-test_informix2.stderr (r1.9 -> r1.10)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/compat_informix-test_informix2.stderr?r1=1.9&r2=1.10)
connect-test2.stderr (r1.6 -> r1.7)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/connect-test2.stderr?r1=1.6&r2=1.7)
connect-test3.stderr (r1.6 -> r1.7)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/connect-test3.stderr?r1=1.6&r2=1.7)
connect-test4.stderr (r1.2 -> r1.3)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/connect-test4.stderr?r1=1.2&r2=1.3)
connect-test5.stderr (r1.6 -> r1.7)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/connect-test5.stderr?r1=1.6&r2=1.7)
pgtypeslib-dt_test.stderr (r1.9 -> r1.10)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/pgtypeslib-dt_test.stderr?r1=1.9&r2=1.10)
pgtypeslib-num_test.stderr (r1.8 -> r1.9)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/pgtypeslib-num_test.stderr?r1=1.8&r2=1.9)
preproc-array_of_struct.stderr (r1.4 -> r1.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/preproc-array_of_struct.stderr?r1=1.4&r2=1.5)
preproc-autoprep.stderr (r1.4 -> r1.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/preproc-autoprep.stderr?r1=1.4&r2=1.5)
preproc-comment.stderr (r1.2 -> r1.3)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/preproc-comment.stderr?r1=1.2&r2=1.3)
preproc-define.stderr (r1.5 -> r1.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/preproc-define.stderr?r1=1.5&r2=1.6)
preproc-init.stderr (r1.1 -> r1.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/preproc-init.stderr?r1=1.1&r2=1.2)
preproc-type.stderr (r1.4 -> r1.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/preproc-type.stderr?r1=1.4&r2=1.5)
preproc-variable.stderr (r1.6 -> r1.7)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/preproc-variable.stderr?r1=1.6&r2=1.7)
preproc-whenever.stderr (r1.4 -> r1.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/preproc-whenever.stderr?r1=1.4&r2=1.5)
sql-array.stderr (r1.4 -> r1.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-array.stderr?r1=1.4&r2=1.5)
sql-binary.stderr (r1.5 -> r1.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-binary.stderr?r1=1.5&r2=1.6)
sql-code100.stderr (r1.5 -> r1.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-code100.stderr?r1=1.5&r2=1.6)
sql-copystdout.stderr (r1.5 -> r1.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-copystdout.stderr?r1=1.5&r2=1.6)
sql-define.stderr (r1.6 -> r1.7)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-define.stderr?r1=1.6&r2=1.7)
sql-desc.stderr (r1.9 -> r1.10)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-desc.stderr?r1=1.9&r2=1.10)
sql-dynalloc.stderr (r1.9 -> r1.10)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-dynalloc.stderr?r1=1.9&r2=1.10)
sql-dynalloc2.stderr (r1.8 -> r1.9)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-dynalloc2.stderr?r1=1.8&r2=1.9)
sql-dyntest.stderr (r1.9 -> r1.10)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-dyntest.stderr?r1=1.9&r2=1.10)
sql-execute.stderr (r1.6 -> r1.7)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-execute.stderr?r1=1.6&r2=1.7)
sql-fetch.stderr (r1.5 -> r1.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-fetch.stderr?r1=1.5&r2=1.6)
sql-func.stderr (r1.5 -> r1.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-func.stderr?r1=1.5&r2=1.6)
sql-indicators.stderr (r1.6 -> r1.7)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-indicators.stderr?r1=1.6&r2=1.7)
sql-insupd.stderr (r1.4 -> r1.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-insupd.stderr?r1=1.4&r2=1.5)
sql-oldexec.stderr (r1.4 -> r1.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-oldexec.stderr?r1=1.4&r2=1.5)
sql-parser.stderr (r1.4 -> r1.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-parser.stderr?r1=1.4&r2=1.5)
sql-quote.stderr (r1.7 -> r1.8)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-quote.stderr?r1=1.7&r2=1.8)
sql-show.stderr (r1.7 -> r1.8)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/expected/sql-show.stderr?r1=1.7&r2=1.8)

Added Files:
-----------
pgsql/src/interfaces/ecpg:
nls.mk (r1.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/nls.mk?rev=1.1&content-type=text/x-cvsweb-markup)

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

Re: [pgadmin-support] problem with pgadmin 1.8.2 to keep connection to postgresql 8.2.6

"Dave Page" <dpage@pgadmin.org> wrote:
> On Fri, May 16, 2008 at 12:05 PM, Sebastian Reitenbach
> <sebastia@l00-bugdead-prods.de> wrote:
> > I also tried to run pgadmin3 --sync, as on another time when it crashed,
it
> > suggested to use this parameter. But then, the problem was not
reproducible,
> > because the query seems to take forever, the counter of miliseconds in
the
> > lower right corner was at about 50000 when I stopped it. The query run
from
> > pgadmin without --sync parameter took only about 40ms.
>
> Well that's, umm surprising. There is no --sync option in pgAdmin - in
> fact I get an error if I try to use it on Windows or Mac (I don't have
> a GTK system here atm). Can you get an exact copy of the text which
> recommended you use it?
Afaik the error message came from the X server, so yes, not a pgadmin3
parameter.
It said sth. like:
When an X application crashes, the error message is asynchron, and therefore
usually not shows the real cause of the problem. When you want to run the
application from within gdb, then use --sync, to get the right backtrace.
The --sync is I think automatically added to each X application, from the X
server, or window manager, whoever there is responsible.

When I get the error again, then I cut 'n past the output in here, I got
this only once.

Sebastian


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

Re: [GENERAL] move database from the default tablespace to a new one?

Anton Melser a écrit :
> Is this possible? I have a db that has been living in the default
> tablespace, and I am trying to separate out the transaction logs (and
> log archiving) and the data. It seems that tablespaces are the way to
> go... but the database exists and I need to separate them out. Any
> pointers?

Create a tablespace and move each individual relations of this database
in the new tablespace :
* ALTER TABLE ... SET TABLESPACE ...

http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html

* ALTER INDEX ... SET TABLESPACE ...

http://www.postgresql.org/docs/8.3/interactive/sql-alterindex.html

Regards.


--
Guillaume.

http://www.postgresqlfr.org

http://dalibo.com

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

Re: [PATCHES] libpq object hooks

On Fri, May 16, 2008 at 10:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
>>> typedef void *(*PGobjectEventProc)(PGobjectEventId evtId, ...);
>>> int PQregisterObjectEventProc(PGconn*, PGobjectEventProc);
>>> void *PQeventData(PGconn *, PGobjectEventProc);
>>> void *PQresultEventData(PGresult *, PGobjectEventProc);
>
>> This provides what we need...a key to lookup the hook data without
>> using a string. Also, it reduces the number of exports (it's a little
>> easier for us, while not essential, to not have to register each
>> callback individually). Also, this AFAICT does not have any ABI
>> issues (no struct), and adds less exports which is nice. We don't
>> have to 'look up' the data inside the callbacks..it's properly passed
>> through as an argument. While vararg callbacks may be considered
>> unsafe in some scenarios, I think it's a good fit here.
>
> I don't think varargs callbacks are a good idea at all. Please adjust
> this to something that doesn't do that. Also, haven't you forgotten
> the passthrough void *?

We didn't...one of the functions (the init) doesn't need it so we
didn't expose it to the fixed arguments...we would just va_arg it off
in the other cases. Regardless, your comments below make that moot:

> If you really want only one callback function, perhaps what would work
> is
>
> typedef void (*PGeventProc) (PGeventId eventId, const void *eventInfo,
> void *passthrough);
>
> int PQregisterEventProc(PGconn *conn, PGeventProc proc, void *passthrough);
>
> where eventInfo will point to one of several exported struct types
> depending on the value of eventId. With this approach, you can add
> more fields to the end of any one such struct type without creating
> ABI issues. I have little confidence in being able to make similar
> changes in a varargs environment.

this is fine.

> Also, even if varargs are safe they'd be notationally unpleasant
> in the extreme. varargs are just a PITA to work with --- you'd have
> to do all the decoding in the first-level hook routine, even for
> items you weren't going to use. With something like the above
> all you need is a switch() and some pointer casts.

Switch, plus struct (basically a union) will do the trick nicely. Can
it be a formal union, or is it better as a void*?

The main issue was how what we called the 'hook data' was passed back
and forth. We'll get a patch up.

merlin

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

[GENERAL] move database from the default tablespace to a new one?

Hi all,
Is this possible? I have a db that has been living in the default
tablespace, and I am trying to separate out the transaction logs (and
log archiving) and the data. It seems that tablespaces are the way to
go... but the database exists and I need to separate them out. Any
pointers?
Cheers
Anton

--
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] libpq thread-locking

Magnus Hagander wrote:
> Bruce Momjian wrote:
> > Bruce Momjian wrote:
> > > Magnus Hagander wrote:
> > > > Attached patch adds some error checking to the thread locking
> > > > stuff in libpq. Previously, if thread locking failed for some
> > > > reason, we would just fall through and do things without locking.
> > > > This patch makes us abort() instead. It's not the greatest thing
> > > > probably, but our API doesn't let us pass back return values...
> > >
> > > I have looked over the patch and it seems fine, though I am
> > > concerned about the abort() case with no output. I realize stderr
> > > might be going nowhere, but in fe-print.c we do an fprintf(stderr)
> > > for memory failures so for consistency I think we should do the
> > > same here. If there is concern about code bloat, I suggest a macro
> > > at the top of the file for thread failure exits:
> > >
> > > #define THEAD_FAILURE(str) \
> > > do { \
> > > fprintf(stderr, libpq_gettext("Thread failure:
> > > %s\n")); \ exit(1); \
> > > } while(0)
> >
> > Oh, this is Tom saying he doesn't like stderr and the added code lines
> > for failure:
> >
> >

http://archives.postgresql.org/pgsql-patches/2008-04/msg00254.php
> >
> > I think the macro and consistency suggest doing as I outlined.
>
> Does this one look like what you're suggesting?

Yep.

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

Re: [pgsql-es-ayuda] permisos

Si los usuarios poseen tablas diferentes, sería relativamente sencillo implementarlo con los privilegios sobre la base de datos (grant/revoke), pero si comparten datos en las mismas tablas, tal vez guardando un identificador del usuario por registro en esas tablas, y validando esa información mediante un trigger antes de modificar/eliminar datos.

También puedes plantearte el uso de esquemas, uno por usuario y así controlar con mucha mayor facilidad los permisos.

El día 16 de mayo de 2008 7:39, Rubén F. Santiago <rfs1986@gmail.com> escribió:
 
Hola, necesito dar permisos a un usuario para que pueda consultar, insertar, modificar y eliminar datos que solo le pertenecen a ese usuario. Tengo una tabla de usuarios, detalle y curso. El problema es que no se que utilizar para ello si una funcion, trigger o vista no tengo muy claros los conceptos por favor si alguien me puede ayudar gracias.

Re: [pgsql-es-ayuda] permisos

Si los usuarios poseen tablas diferentes, sería relativamente sencillo implementarlo con los privilegios sobre la base de datos (grant/revoke), pero si comparten datos en las mismas tablas, tal vez guardando un identificador del usuario por registro en esas tablas, y validando esa información mediante un trigger antes de modificar/eliminar datos.

También puedes plantearte el uso de esquemas, uno por usuario y así controlar con mucha mayor facilidad los permisos.

El día 16 de mayo de 2008 7:39, Rubén F. Santiago <rfs1986@gmail.com> escribió:
 
Hola, necesito dar permisos a un usuario para que pueda consultar, insertar, modificar y eliminar datos que solo le pertenecen a ese usuario. Tengo una tabla de usuarios, detalle y curso. El problema es que no se que utilizar para ello si una funcion, trigger o vista no tengo muy claros los conceptos por favor si alguien me puede ayudar gracias.

Re: [PATCHES] libpq object hooks

"Merlin Moncure" <mmoncure@gmail.com> writes:
>> typedef void *(*PGobjectEventProc)(PGobjectEventId evtId, ...);
>> int PQregisterObjectEventProc(PGconn*, PGobjectEventProc);
>> void *PQeventData(PGconn *, PGobjectEventProc);
>> void *PQresultEventData(PGresult *, PGobjectEventProc);

> This provides what we need...a key to lookup the hook data without
> using a string. Also, it reduces the number of exports (it's a little
> easier for us, while not essential, to not have to register each
> callback individually). Also, this AFAICT does not have any ABI
> issues (no struct), and adds less exports which is nice. We don't
> have to 'look up' the data inside the callbacks..it's properly passed
> through as an argument. While vararg callbacks may be considered
> unsafe in some scenarios, I think it's a good fit here.

I don't think varargs callbacks are a good idea at all. Please adjust
this to something that doesn't do that. Also, haven't you forgotten
the passthrough void *?

If you really want only one callback function, perhaps what would work
is

typedef void (*PGeventProc) (PGeventId eventId, const void *eventInfo,
void *passthrough);

int PQregisterEventProc(PGconn *conn, PGeventProc proc, void *passthrough);

where eventInfo will point to one of several exported struct types
depending on the value of eventId. With this approach, you can add
more fields to the end of any one such struct type without creating
ABI issues. I have little confidence in being able to make similar
changes in a varargs environment.

Also, even if varargs are safe they'd be notationally unpleasant
in the extreme. varargs are just a PITA to work with --- you'd have
to do all the decoding in the first-level hook routine, even for
items you weren't going to use. With something like the above
all you need is a switch() and some pointer casts.

regards, tom lane

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

[pgsql-es-ayuda] cache

Como se desctiva la cache de escritura o donde puedo ver eso???

El día 16 de mayo de 2008 8:32, Alvaro Herrera <alvherre@commandprompt.com> escribió:
MIGUEL CANCHAS escribió:
> Maestros estoy metido en un problemon, el servidor donde esta alojado mi BD
> se cayó.
>
> Cuando levantas el servidor sale :Error Failure disk, podriamos intentar
> recuperar los archivos, pero no creo que levante el Servidor solo el SO,
> se podria levantar la BD solo copiando los archivos de donde estaba a otro
> Disco ?

Claro.  Ya dijo otro listero qué copiar.  Si no funciona, haznos saber.

Para evitar esta clase de problemas en el futuro, te recomiendo
verificar que tus discos tengan el cache de escritura desactivado.  El
rendimiento será menor, pero la base de datos estará segura.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
   (envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)



--

-----------------------
MVJR

RE: [pgsql-es-ayuda] Se cayo mi servidor de BD

-----Mensaje original-----
De: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Enviado el: Viernes, 16 de Mayo de 2008 09:33 a.m.
Para: MIGUEL CANCHAS
CC: pgsql-es-ayuda@postgresql.org
Asunto: Re: [pgsql-es-ayuda] Se cayo mi servidor de BD


MIGUEL CANCHAS escribió:
> Maestros estoy metido en un problemon, el servidor donde esta alojado mi
BD
> se cayó.
>
> Cuando levantas el servidor sale :Error Failure disk, podriamos intentar
> recuperar los archivos, pero no creo que levante el Servidor solo el SO,
> se podria levantar la BD solo copiando los archivos de donde estaba a otro
> Disco ?

>>Claro. Ya dijo otro listero qué copiar. Si no funciona, haznos saber.

>>Para evitar esta clase de problemas en el futuro, te recomiendo
>>verificar que tus discos tengan el cache de escritura desactivado. El
>>rendimiento será menor, pero la base de datos estará segura.

>>--
>>Alvaro Herrera
http://www.CommandPrompt.com/
>>The PostgreSQL Company - Command Prompt, Inc.
*******************

Aclaro que mi SO es o era Win NT 4.0, estoy buscando en mis archivos
guardados y no encuentro ninguno al respecto salvo algunos sobre Linux.

Miguel Canchas
--
TIP 4: No hagas 'kill -9' a postmaster

[ODBC] psqlODBC X CodeGear (Borland)

The version 08.03.0200 still not compatible with products from CodeGear like Delphi.

From version psqlodbc-08_02_0100 up to psqlodbc-08_03_0200, when we mark:

 * Unknown Sizes = Don't Know;
 * Data Type Option = Text as LongVarChar, Unknowns as LongVarChar;

all TEXT fields don't get recognized by the application as MEMO as they did
before.

We are still using the psqlodbc-08_01_0200 version with no problem.

Re: [INTERFACES] pg_subtrans directory grows over 1 GB, is there a way to control it?

Dennis Wang <dennis_02_2002@hotmail.com> writes:
> I am using PostgreSQL database in a location recognition application, the files under postgresql/cluster is growing and over a few days it become very large as below.
> /var/opt/postgresql/cluster/data 166015 Kilobytes (the actual database data)
> /var/opt/postgresql/cluster/sys/pg_clog 74188 Kilobytes (transaction commit status data)
> /var/opt/postgresql/cluster/sys/pg_subtrans 1187032 Kilobytes (transaction status data)

I think the only way pg_subtrans can get that large is if you have a
client that's been sitting holding an open transaction for a very long
time. Take a look in pg_stat_activity.

The expected size of pg_clog varies depending on what PG version you're
using. In recent releases you can alter autovacuum_freeze_max_age to
trade off the size of pg_clog against the frequency of forced vacuums.

regards, tom lane

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

Re: [INTERFACES] pg_subtrans directory grows over 1 GB, is there a way to control it?

Dennis Wang wrote:
>
> I am using PostgreSQL database in a location recognition application, the files under postgresql/cluster is growing and over a few days it become very large as below.
> /var/opt/postgresql/cluster/data 166015 Kilobytes (the actual database data)
> /var/opt/postgresql/cluster/sys/pg_clog 74188 Kilobytes (transaction commit status data)
> /var/opt/postgresql/cluster/sys/pg_subtrans 1187032 Kilobytes (transaction status data)
>
> The pg_clog and pg_subtrans can grow when database contains more data and more queries come in. But these figures looks rather large to me because the queries goes in the database are mainly read operation rather than write one. I got a few question to ask here:
> Is there a way to control the size of pg_clog and pg_subtrans?
> Is the file size still be normal for the database of this size (166MB for cluster/data)?
> Does the larger pg_subtrans indicate some bad transaction happens in the database?

I think it can be an indication that you're leaving transactions running
for too long. Check pg_stat_activity, and if you see "<IDLE> in
transaction", start worrying about that.

--
Alvaro Herrera

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

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

Re: [GENERAL] problem with serial data type and access

On Thu, May 15, 2008 at 6:54 AM, Ottavio Campana <ottavio@campana.vi.it> wrote:
> I'm dealing with this scenario: access 97 is connected through odbc to a
> postgresql server. All tables are saved in postgresql and access is used
> only to generated the program interface.
>
> Everything works fines, but I'm having problems with the serial data type. I
> know a serial is an integer having as default the next value of a sequence.
> Since it is an integer, access does not recognize it as an autoincrement
> value, and it asks for is value.
>
> Did anyone of you already have this problem?

Back when I was using V95, I remember having this problem and finding
a simple way to get around it. The only problem is that I can't
remember what it was. MS-Access 2003 doesn't seem to suffer from this
problem.

One brute-force method to get around it is to create a function that
uses an ADO connection to call the PostgreSQL back-end for
nextval('sequence_name'). Next using the Before_insert() event in
access, you could manually set the new id for your primary key and
complete the record insertion.

--
Regards,
Richard Broersma Jr.

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

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

Re: [HACKERS] deadlock while doing VACUUM and DROP

Gregory Stark <stark@enterprisedb.com> writes:
> "Pavan Deolasee" <pavan.deolasee@gmail.com> writes:
>> Alternatively, we can just acquire AccessExclusiveLock on the main relation
>> before proceeding with the recursive deletion. That would solve this case,
>> but may be there are other similar deadlocks waiting to happen.

> Surely we should be locking the relation before even doing the dependency scan

Yeah. I think this is just another manifestation of the problem I was
noodling about a few days ago:
http://archives.postgresql.org/pgsql-hackers/2008-05/msg00301.php

As I said then, I don't want to think about it until after commitfest.
I foresee an invasive and not sanely back-patchable patch.

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: [PATCHES] libpq thread-locking

Andrew Chernow wrote:
> ! int
> pthread_mutex_init(pthread_mutex_t *mp, void *attr)
> {
> *mp = CreateMutex(0, 0, 0);
> + if (*mp == NULL)
> + return 1;
> + return 0;
> }
>
> Maybe it would be better to emulate what pthreads does. Instead of
> returing 1 to indicate an error, return an errno. In the above case,
> ENOMEM seems like a good fit.
>
> Also, maybe you should check the passed in mutex pointer. If its
> NULL, you could return EINVAL.

Given that we only call this stuff internally, I don't think it's a big
issue. But either way - that part of the code will be replaced with the
critical_section code later anyway - I just want to get the "generic"
changes through first.

//Magnus

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

Re: [pgsql-es-ayuda] Se cayo mi servidor de BD

MIGUEL CANCHAS escribió:
> Maestros estoy metido en un problemon, el servidor donde esta alojado mi BD
> se cayó.
>
> Cuando levantas el servidor sale :Error Failure disk, podriamos intentar
> recuperar los archivos, pero no creo que levante el Servidor solo el SO,
> se podria levantar la BD solo copiando los archivos de donde estaba a otro
> Disco ?

Claro. Ya dijo otro listero qué copiar. Si no funciona, haznos saber.

Para evitar esta clase de problemas en el futuro, te recomiendo
verificar que tus discos tengan el cache de escritura desactivado. El
rendimiento será menor, pero la base de datos estará segura.

--
Alvaro Herrera

http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [SQL] Find all instances of a column in the entire database.

am Sat, dem 17.05.2008, um 0:04:05 +1000 mailte Gavin 'Beau' Baumanis folgendes:
> Hi depesz,
>
> Thanks very much!
> That works wonderfully well...
>
> Is this listed in the manual anywhere? because after two hours of
> reading, I didn't find it anywhere!


Of course, the whole information schema:
http://www.postgresql.org/docs/current/static/information-schema.html

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA

http://wwwkeys.de.pgp.net

--
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] Arbitary file size limit in twophase.c

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Tom Lane wrote:
>> It might be worth checking at prepare that the file size doesn't exceed
>> MaxAllocSize, but any smaller limit strikes me as (a) unnecessarily
>> restrictive and (b) not actually creating any useful guarantee.

> Patch attached. I can't commit it myself right now, but will do so as
> soon as I can, unless there's objections.

Two bugs: "exceeed" -> "exceeded", please; and on the read side, you
should still have an upper-bound check, but it should be MaxAllocSize.

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: [PATCHES] libpq thread-locking

! int
pthread_mutex_init(pthread_mutex_t *mp, void *attr)
{
*mp = CreateMutex(0, 0, 0);
+ if (*mp == NULL)
+ return 1;
+ return 0;
}

Maybe it would be better to emulate what pthreads does. Instead of
returing 1 to indicate an error, return an errno. In the above case,
ENOMEM seems like a good fit.

Also, maybe you should check the passed in mutex pointer. If its NULL,
you could return EINVAL.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
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] problem with serial data type and access

On Thursday 15 May 2008 11:44 pm, Ottavio Campana wrote:
> Adrian Klaver ha scritto:
> >> The fact is that the serial data type is in pratice an integer, and when
> >> I also try to connect with pgadminIII I see an integer data type and not
> >> a serial. I think that since it sees an integer, it does not understand
> >> that it is a serial, and access does not recognize it as autoincrement.
> >>
> >> I'm stuck at this point...
> >
> > Did you mark this field as the primary key when you linked the table?
>
> yes I did

Just out of curiosity, create a test table using the WITH OIDS clause and a
serial data type column along with some test columns and try to INSERT a
record.

--
Adrian Klaver
aklaver@comcast.net

--
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] Se cayo mi servidor de BD

MIGUEL CANCHAS wrote:
> Maestros estoy metido en un problemon, el servidor donde esta alojado mi BD
> se cayó.
>
> Cuando levantas el servidor sale :Error Failure disk, podriamos intentar
> recuperar los archivos, pero no creo que levante el Servidor solo el SO,
> se podria levantar la BD solo copiando los archivos de donde estaba a otro
> Disco ?
>
> Por favor necesito toda la ayuda posible.
>
> Miguel Canchas
> --
> TIP 3: Si encontraste la respuesta a tu problema, publ�cala, otros te lo agradecer�n
>
Hola, debes copiar TODO por ejemploen mi caso el /var/lib/pgsql/....,
luego intenta.
Si tenes tablespaces, debes copiarlos tambien.
En caso de que no funcione, podes installar postgres, levantar el
unltimo pgdump y aplicarles los logs de la base anterior.

Saludos Fernando
--
TIP 7: no olvides aumentar la configuraci�n del "free space map"

Re: [HACKERS] deadlock while doing VACUUM and DROP

Pavan Deolasee escribió:

> >> Also I am not sure if the issue is big enough to demand the change.
> >
> > I think it is, effectively what we have now is "your DDL could fail randomly
> > for reasons that are out of your control" :(
>
> Yeah. I think we better fix this, especially given the above mentioned scenario.

The pg_shdepend code has code to grab a lock on the object being
dropped, which is also grabbed by someone who wants to add a dependency
on the object. Perhaps the pg_depend code should do the same.

I don't think this closes the original report though, unless we ensure
that the lock taken by vacuum conflicts with that one.

--
Alvaro Herrera

http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

[pgsql-es-ayuda] Se cayo mi servidor de BD

Maestros estoy metido en un problemon, el servidor donde esta alojado mi BD
se cayó.

Cuando levantas el servidor sale :Error Failure disk, podriamos intentar
recuperar los archivos, pero no creo que levante el Servidor solo el SO,
se podria levantar la BD solo copiando los archivos de donde estaba a otro
Disco ?

Por favor necesito toda la ayuda posible.

Miguel Canchas
--
TIP 3: Si encontraste la respuesta a tu problema, publ�cala, otros te lo agradecer�n

Re: [PATCHES] libpq thread-locking

Index: fe-connect.c
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.357
diff -c -r1.357 fe-connect.c
*** fe-connect.c 31 Mar 2008 02:43:14 -0000 1.357
--- fe-connect.c 16 May 2008 14:03:10 -0000
***************
*** 3835,3848 ****
while (InterlockedExchange(&mutex_initlock, 1) == 1)
/* loop, another thread own the lock */ ;
if (singlethread_lock == NULL)
! pthread_mutex_init(&singlethread_lock, NULL);
InterlockedExchange(&mutex_initlock, 0);
}
#endif
if (acquire)
! pthread_mutex_lock(&singlethread_lock);
else
! pthread_mutex_unlock(&singlethread_lock);
#endif
}

--- 3835,3857 ----
while (InterlockedExchange(&mutex_initlock, 1) == 1)
/* loop, another thread own the lock */ ;
if (singlethread_lock == NULL)
! {
! if (pthread_mutex_init(&singlethread_lock, NULL))
! PGTHREAD_ERROR("failed to initialize mutex");
! }
InterlockedExchange(&mutex_initlock, 0);
}
#endif
if (acquire)
! {
! if (pthread_mutex_lock(&singlethread_lock))
! PGTHREAD_ERROR("failed to lock mutex");
! }
else
! {
! if (pthread_mutex_unlock(&singlethread_lock))
! PGTHREAD_ERROR("failed to unlock mutex");
! }
#endif
}

Index: fe-secure.c
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-secure.c,v
retrieving revision 1.104
diff -c -r1.104 fe-secure.c
*** fe-secure.c 31 Mar 2008 02:43:14 -0000 1.104
--- fe-secure.c 16 May 2008 14:03:11 -0000
***************
*** 796,807 ****
pq_lockingcallback(int mode, int n, const char *file, int line)
{
if (mode & CRYPTO_LOCK)
! pthread_mutex_lock(&pq_lockarray[n]);
else
! pthread_mutex_unlock(&pq_lockarray[n]);
}
#endif /* ENABLE_THREAD_SAFETY */

static int
init_ssl_system(PGconn *conn)
{
--- 796,816 ----
pq_lockingcallback(int mode, int n, const char *file, int line)
{
if (mode & CRYPTO_LOCK)
! {
! if (pthread_mutex_lock(&pq_lockarray[n]))
! PGTHREAD_ERROR("failed to lock mutex");
! }
else
! {
! if (pthread_mutex_unlock(&pq_lockarray[n]))
! PGTHREAD_ERROR("failed to unlock mutex");
! }
}
#endif /* ENABLE_THREAD_SAFETY */

+ /*
+ * Also see similar code in fe-connect.c, default_threadlock()
+ */
static int
init_ssl_system(PGconn *conn)
{
***************
*** 817,827 ****
while (InterlockedExchange(&mutex_initlock, 1) == 1)
/* loop, another thread own the lock */ ;
if (init_mutex == NULL)
! pthread_mutex_init(&init_mutex, NULL);
InterlockedExchange(&mutex_initlock, 0);
}
#endif
! pthread_mutex_lock(&init_mutex);

if (pq_initssllib && pq_lockarray == NULL)
{
--- 826,840 ----
while (InterlockedExchange(&mutex_initlock, 1) == 1)
/* loop, another thread own the lock */ ;
if (init_mutex == NULL)
! {
! if (pthread_mutex_init(&init_mutex, NULL))
! return -1;
! }
InterlockedExchange(&mutex_initlock, 0);
}
#endif
! if (pthread_mutex_lock(&init_mutex))
! return -1;

if (pq_initssllib && pq_lockarray == NULL)
{
***************
*** 836,842 ****
return -1;
}
for (i = 0; i < CRYPTO_num_locks(); i++)
! pthread_mutex_init(&pq_lockarray[i], NULL);

CRYPTO_set_locking_callback(pq_lockingcallback);
}
--- 849,858 ----
return -1;
}
for (i = 0; i < CRYPTO_num_locks(); i++)
! {
! if (pthread_mutex_init(&pq_lockarray[i], NULL))
! return -1;
! }

CRYPTO_set_locking_callback(pq_lockingcallback);
}
Index: libpq-int.h
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/libpq/libpq-int.h,v
retrieving revision 1.129
diff -c -r1.129 libpq-int.h
*** libpq-int.h 1 Jan 2008 19:46:00 -0000 1.129
--- libpq-int.h 16 May 2008 14:03:11 -0000
***************
*** 439,444 ****
--- 439,451 ----
#ifdef ENABLE_THREAD_SAFETY
extern pgthreadlock_t pg_g_threadlock;

+ #define PGTHREAD_ERROR(msg) \
+ do { \
+ fprintf(stderr, "%s\n", msg); \
+ exit(1); \
+ } while (0)
+
+
#define pglock_thread() pg_g_threadlock(true)
#define pgunlock_thread() pg_g_threadlock(false)
#else
Index: pthread-win32.c
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/libpq/pthread-win32.c,v
retrieving revision 1.15
diff -c -r1.15 pthread-win32.c
*** pthread-win32.c 1 Jan 2008 19:46:00 -0000 1.15
--- pthread-win32.c 16 May 2008 14:03:11 -0000
***************
*** 32,51 ****
return NULL;
}

! void
pthread_mutex_init(pthread_mutex_t *mp, void *attr)
{
*mp = CreateMutex(0, 0, 0);
}

! void
pthread_mutex_lock(pthread_mutex_t *mp)
{
! WaitForSingleObject(*mp, INFINITE);
}

! void
pthread_mutex_unlock(pthread_mutex_t *mp)
{
! ReleaseMutex(*mp);
}
--- 32,58 ----
return NULL;
}

! int
pthread_mutex_init(pthread_mutex_t *mp, void *attr)
{
*mp = CreateMutex(0, 0, 0);
+ if (*mp == NULL)
+ return 1;
+ return 0;
}

! int
pthread_mutex_lock(pthread_mutex_t *mp)
{
! if (WaitForSingleObject(*mp, INFINITE) != WAIT_OBJECT_0)
! return 1;
! return 0;
}

! int
pthread_mutex_unlock(pthread_mutex_t *mp)
{
! if (!ReleaseMutex(*mp))
! return 1;
! return 0;
}
Bruce Momjian wrote:
> Bruce Momjian wrote:
> > Magnus Hagander wrote:
> > > Attached patch adds some error checking to the thread locking
> > > stuff in libpq. Previously, if thread locking failed for some
> > > reason, we would just fall through and do things without locking.
> > > This patch makes us abort() instead. It's not the greatest thing
> > > probably, but our API doesn't let us pass back return values...
> >
> > I have looked over the patch and it seems fine, though I am
> > concerned about the abort() case with no output. I realize stderr
> > might be going nowhere, but in fe-print.c we do an fprintf(stderr)
> > for memory failures so for consistency I think we should do the
> > same here. If there is concern about code bloat, I suggest a macro
> > at the top of the file for thread failure exits:
> >
> > #define THEAD_FAILURE(str) \
> > do { \
> > fprintf(stderr, libpq_gettext("Thread failure:
> > %s\n")); \ exit(1); \
> > } while(0)
>
> Oh, this is Tom saying he doesn't like stderr and the added code lines
> for failure:
>
>

http://archives.postgresql.org/pgsql-patches/2008-04/msg00254.php
>
> I think the macro and consistency suggest doing as I outlined.

Does this one look like what you're suggesting?

//Magnus

Re: [SQL] Find all instances of a column in the entire database.

Hi depesz,

Thanks very much!
That works wonderfully well...

Is this listed in the manual anywhere? because after two hours of
reading, I didn't find it anywhere!

None the less - thanks again.


- Beau


On 16/05/2008, at 11:56 PM, hubert depesz lubaczewski wrote:

> On Fri, May 16, 2008 at 11:51:20PM +1000, Gavin 'Beau' Baumanis wrote:
>> I am hoping that you might be able to give me some assistance with
>> the
>> following task!
>> I have a database with nearly 200 tables and I need to find all
>> tables
>> that contain a column of myColumnName.
>
> select * from information_schema.columns where column_name =
> 'myColumnName';
>
> depesz

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

Re: [SQL] Find all instances of a column in the entire database.

On Fri, May 16, 2008 at 11:51:20PM +1000, Gavin 'Beau' Baumanis wrote:
> I am hoping that you might be able to give me some assistance with the
> following task!
> I have a database with nearly 200 tables and I need to find all tables
> that contain a column of myColumnName.

select * from information_schema.columns where column_name = 'myColumnName';

depesz

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

Re: [GENERAL] Postgresql MVCC, Read Committed Isolation Level and taking "snapshot"

On Fri, May 16, 2008 at 09:06:11AM +0200, Ivan Sergio Borgonovo wrote:
> Is
> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
> what I'm looking for?

Yes.

A

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

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

[BUGS] BUG #4174: ECPG refuses datestyle SQL

The following bug has been logged online:

Bug reference: 4174
Logged by: Francisco Leovey
Email address: fleovey@jus.gov.ar
PostgreSQL version: 8.3.1
Operating system: SuSE Linux 9.2
Description: ECPG refuses datestyle SQL
Details:

if you write
EXEC SQL SET datestyle TO SQL, DMY;
you get ERROR: syntax error at or near "SQL"

But
EXEC SQL SET datestyle TO Postgres, DMY;
works fine

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

[SQL] Find all instances of a column in the entire database.

Hi Everyone,

I am hoping that you might be able to give me some assistance with the
following task!
I have a database with nearly 200 tables and I need to find all tables
that contain a column of myColumnName.

I was hoping there might be a built-in function for this task, but I
have been unable to find any information through our good friend Mr.
Google or by perusing the fine manual.

Thanks in advance for any thoughts you might have.


- Beau

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

Re: [GENERAL] SSL auth problem

Vitaliyi wrote:
> another error appeared:
>
> psql: SSL error: sslv3 alert bad certificate
>
> so I started from beginning:
> on CA:
> openssl genrsa -out our.key 2048
>
> creating self-signed serificate:
> openssl req -new -key our.key -out our.req
> openssl req -x509 -in our.req -text -key our.key -out root.crt

It does not cause an error, but omit -text.

> copied root.crt to client and postgres server
>
> on server:
> openssl genrsa -out server.key 2048

You forgot here:
openssl req -new -key server.key -out /tmp/server.req

> on CA:
> openssl x509 -req -in /tmp/server.req -CA ./root.crt -CAkey our.key
> -CAcreateserial -out server.crt
>
> on client:
> openssl genrsa -out postgresql.key 2048
> openssl req -new -key postgresql.key -out cl.req
>
> on CA:
> openssl x509 -req -in /tmp/cl.req -CA ./root.crt -CAkey our.key
> -CAcreateserial -out postgresql.crt
>
> files on client host:
> postgresql.crt (signed by CA, -- root.crt)
> postgresql.key (client private and public keys)

Did you make sure that postgresql.key has permissions 0600?

> root.crt
>
> files on postgresql server:
> server.key (priv and pub keys)

Did you make sure that server.key has permissions 0600?

> server.crt (signed by root CA)
> root.crt
>
> stopped postgresql and started again
>
> on client:
>
> psql "dbname=me sslmode=require host=postgresql_host user=me"
> psql: SSL error: sslv3 alert bad certificate

That means, I guess, that the client does not like its certificate files.

Check that they are ok, with something like

openssl x509 -noout -dates -issuer -subject -in root.crt
or
openssl x509 -noout -text -in root.crt

Same for root.crt.

Yours,
Laurenz Albe

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

Te dejo mi forma de trabajo .... no es la mejor supongo ... pero me va
bien....


Todos mis usuarios de la tabla usuarios .... existen en el
postgres ..... ahí le doy los privilegios que necesitan.... ahhh... yo
los tomo de un Ldap que tenemos implementado... y con ellos hago las
respectivas conexiones desde los módulos que hemos desarrollados.

Si te interesa seguir abundando en el tema .... me escribes a lo
interno.


Gilberto.

El vie, 16-05-2008 a las 14:39 +0200, Rubén F. Santiago escribió:
>
> Hola, necesito dar permisos a un usuario para que pueda consultar,
> insertar, modificar y eliminar datos que solo le pertenecen a ese
> usuario. Tengo una tabla de usuarios, detalle y curso. El problema es
> que no se que utilizar para ello si una funcion, trigger o vista no
> tengo muy claros los conceptos por favor si alguien me puede ayudar
> gracias.

--
TIP 4: No hagas 'kill -9' a postmaster

Re: [PATCHES] libpq object hooks

On Thu, May 15, 2008 at 8:38 PM, Andrew Chernow <ac@esilo.com> wrote:
> We need to add members to a conn and result, that's pretty much it. To do
> this, an api user can register callbacks to receive notifications about
> created/destroyed states of objects. PQhookData is just like PQerrorMessage
> in that both are public accessor functions to private object data. The
> difference is that there can be more than one hookData "dynamic struct
> member" on a conn/result at a time, unlike errorMessage; thus the need for
> an additional "lookup" value when getting hook data (what was hookName).

> typedef void *(*PGobjectEventProc)(PGobjectEventId evtId, ...);
> int PQregisterObjectEventProc(PGconn*, PGobjectEventProc);
> void *PQeventData(PGconn *, PGobjectEventProc);
> void *PQresultEventData(PGresult *, PGobjectEventProc);

This provides what we need...a key to lookup the hook data without
using a string. Also, it reduces the number of exports (it's a little
easier for us, while not essential, to not have to register each
callback individually). Also, this AFAICT does not have any ABI
issues (no struct), and adds less exports which is nice. We don't
have to 'look up' the data inside the callbacks..it's properly passed
through as an argument. While vararg callbacks may be considered
unsafe in some scenarios, I think it's a good fit here.

The most important part though is that it fits what we think is needed
to maintain the data we associate with the libpq with the proper
lifetime. I'm not sure that everyone was on the same page in terms of
how this works...we may not have explained ourselves properly. In our
defense, the interaction between libpq and a wrapping library like
libpqtypes is a bit involved and the 'best possible' way to link
things up did not necessarily suggest itself the first time out.

We would like to wrap this up into some form the community would
accept. The event proc style of doing this is better than our initial
approach...faster and cleaner. In fact, we are pleased with all the
changes that have come about due to community suggestions...there are
many positive results.

merlin

--
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] Installing debugger

On Fri, May 16, 2008 at 1:32 PM, Robert Fitzpatrick <lists@webtent.net> wrote:
> I have built posgresql 8.3.1 on a FreeBSD 6.1 via the ports system
> WITH_DEBUG=true and also on a Windows machine with the debugger
> selected. I see the plugin_debugger.dll under the lib/plugins folder on
> the Windows machine as well. But when I look for the procs, they are not
> there....
>
> template1=# select * from pg_proc where proname like 'pldbg%';
> proname | pronamespace | proowner | prolang | procost | prorows | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | prosrc | probin | proconfig | proacl
> ---------+--------------+----------+---------+---------+---------+----------+-----------+-------------+-----------+-------------+----------+------------+-------------+----------------+-------------+-------------+--------+--------+-----------+--------
> (0 rows)
>
> Can someone help or point me to some docs that can tell me what else I
> need to do to enable debugging plpgsql?

There is a README with the plugin which tells you how to modify
postgresql.conf to preload the libraries at server start (which you
must do). Once you've done that, just run the pldbgapi.sql script
that's also included to load the api functions into the database you
wish to debug.

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

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

Re: [pgadmin-support] problem with pgadmin 1.8.2 to keep connection to postgresql 8.2.6

On Fri, May 16, 2008 at 12:05 PM, Sebastian Reitenbach
<sebastia@l00-bugdead-prods.de> wrote:
> I also tried to run pgadmin3 --sync, as on another time when it crashed, it
> suggested to use this parameter. But then, the problem was not reproducible,
> because the query seems to take forever, the counter of miliseconds in the
> lower right corner was at about 50000 when I stopped it. The query run from
> pgadmin without --sync parameter took only about 40ms.

Well that's, umm surprising. There is no --sync option in pgAdmin - in
fact I get an error if I try to use it on Windows or Mac (I don't have
a GTK system here atm). Can you get an exact copy of the text which
recommended you use it?


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

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

Re: [GENERAL] How to create a function with multiple RefCursor OUT parameters

On Fri, May 16, 2008 at 2:17 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Chuck Bai wrote:
> CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer,
> OUT o_user refcursor, OUT o_name refcursor) RETURNS record AS
> $BODY$
> BEGIN
> o_user := 'o_user';
> o_name := 'o_name';
> tcount := tcount + 1;
> OPEN o_user FOR SELECT * FROM user_table;
> OPEN o_name FOR SELECT * FROM name_table;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE

also don't forget, the refcursors are only valid for the duration of
the transaction.

merlin

--
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-fr-generale] Problème d'update : résolu !!!

Encore rebonjour,

Je pense qu'on a trouvé notre problème : l'update est :
update diffusion_2008_05_13 set state = 3001 where state = 2101 and
next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a
+datos_a+21';

Or dans les conditions on compare un champ à une chaine ... qui contient
des "_" : et vlan... (on a mal lu : le "filter" est correct, mais le
index condition limite au début de chaine jusqu'au premier "_").


explain analyze update diffusion_2008_05_13 set state = 3001 where
state = 2101 and next_try_channel like 'FTP' and next_try_key like
'uatos_a+host_atos_a+datos_a+21';

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indx_diffusion_2008_05_13_channel on
diffusion_2008_05_13 (cost=0.00..10.41 rows=1 width=7379) (actual
time=12.712..169200.761 rows=48041 loops=1)
Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~
'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND
((next_try_key)::text ~<~ 'uatot'::text))
Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND
((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text))


Euh, désolée, j'ai posé la question trop rapidement... La prochaine fois
je détaillerai méticuleusement le plan d'exécution avant de poster...

Merci encore, Valérie.


Le vendredi 16 mai 2008 à 12:24 +0000, Valérie SCHNEIDER a écrit :
> Rebonjour,
> Je crois que je me suis mal exprimée : le comportement que je voudrais
> comprendre, c'est pourquoi, en exécutant plusieurs fois le même update
> (à la suite, l'un après l'autre, dans la même session psql), le temps
> d'exécution ne soit pas quasi-immédiat à partir du second update
> (puisque plus aucune ligne à updater et qu'on passe par l'index -ce que
> confirme les explain analyze).
> Valérie.
>
> Le vendredi 16 mai 2008 à 09:15 +0000, Valérie SCHNEIDER a écrit :
> > Bonjour,
> >
> > Nous observons un comportement curieux d'une série d'update sur une base
> > PG. Je suis preneur d'explication si vous en avez ...
> >
> > Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
> > bit avec 4 Go de RAM :
> >
> > Date: mer mai 14 09:38:14 GMT 2008
> > Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
> > Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
> > GNU/Linux
> > Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
> > Version Postgresql: 8.3.1
> >
> > Au niveau de postgresql .conf :
> > # - Memory -
> > shared_buffers = 1024MB # min 128kB or
> > max_connections*16kB
> >
> > # - Checkpoints -
> > checkpoint_segments = 10 # in logfile segments, min 1,
> > 16MB each
> >
> > # pour les vacuum
> > maintenance_work_mem = 256MB # min 1MB
> >
> > # Pour les operations de tri
> > work_mem = 16MB # min 64kB
> >
> > # memoire partagee utilisee par une transaction typique.
> > wal_buffers = 1024kB # min 32kB
> >
> > autovacuum = off # enable autovacuum subprocess?
> >
> >
> > Un cron effectue des analyze sur les tables à intervalles choisis.
> >
> > On effectue un update sur une table de 5 millions de lignes, de taille
> > environ 3Go, portant sur environ 50000 lignes, selon des critères
> > utilisant un index.
> > En exécutant plusieurs fois à la suite le même update (donc à partir du
> > second plus aucune ligne n'est mise à jour) on observe des temps très
> > longs pour finalement tomber à quelques millisecondes (qui est le
> > résultat attendu).
> >
> > Que se passe-t'il d'après vous ?
> >
> >
> > Ci-dessous en pièce jointe la description de la table, des index, et une
> > série d'explain analyze update.
> >
> >
> > Merci !
> > Valérie.
> >
> --
>
> ********************************************************************
> * Les points de vue exprimes sont strictement personnels et *
> * n'engagent pas la responsabilite de METEO-FRANCE. *
> ********************************************************************
> * Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
> * METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
> * 42, avenue G. Coriolis Email : Valerie.Schneider@meteo.fr *
> * 31057 TOULOUSE Cedex 1 - FRANCE

http://www.meteo.fr

*
> ********************************************************************
>
>
--

********************************************************************
* Les points de vue exprimes sont strictement personnels et *
* n'engagent pas la responsabilite de METEO-FRANCE. *
********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
* 42, avenue G. Coriolis Email : Valerie.Schneider@meteo.fr *
* 31057 TOULOUSE Cedex 1 - FRANCE

http://www.meteo.fr

*
********************************************************************


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

Re: [pgsql-fr-generale] Re: Problème d'update et de performance

Jean-Max Reymond a écrit :
> Valérie SCHNEIDER a écrit :
>> Bonjour,
>>
>> Nous observons un comportement curieux d'une série d'update sur une base
>> PG. Je suis preneur d'explication si vous en avez ...
>>
>> Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
>> bit avec 4 Go de RAM :
>>
>> Date: mer mai 14 09:38:14 GMT 2008
>> Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
>> Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
>> GNU/Linux
>> Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
>> Version Postgresql: 8.3.1
>>
>> Au niveau de postgresql .conf :
>> # - Memory -
>> shared_buffers = 1024MB # min 128kB or
>> max_connections*16kB
>>
>> # - Checkpoints -
>> checkpoint_segments = 10 # in logfile segments, min 1,
>> 16MB each
>>
>> # pour les vacuum
>> maintenance_work_mem = 256MB # min 1MB
>>
>> # Pour les operations de tri
>> work_mem = 16MB # min 64kB
>>
>> # memoire partagee utilisee par une transaction typique.
>> wal_buffers = 1024kB # min 32kB
>>
>> autovacuum = off # enable autovacuum subprocess?
>>
>>
>> Un cron effectue des analyze sur les tables à intervalles choisis.
>>
>> On effectue un update sur une table de 5 millions de lignes, de taille
>> environ 3Go, portant sur environ 50000 lignes, selon des critères
>> utilisant un index.
>> En exécutant plusieurs fois à la suite le même update (donc à partir du
>> second plus aucune ligne n'est mise à jour) on observe des temps très
>> longs pour finalement tomber à quelques millisecondes (qui est le
>> résultat attendu).
>>
>> Que se passe-t'il d'après vous ?
>
> la première fois, le job est fait avec beaucoup d'entrés-sorties
> correspondant à des delete suivi d'insert
> la 2e fois, toutes les lignes utiles sont ramenées dans le cache disque
> de l'OS
> la 3e fois, comme tout est monté dans la mémoire (il ne s'est rien apssé
> entretemps), c'est instantané.
>

À part que la troisième fois prend du temps. Si je reprends ici les
chiffres données :

1. 858616.959 ms
2. 11440.215 ms
3. 365160.313 ms
4. 2.954 ms
5. 2.680 ms

Je pense qu'on manque d'informations, notamment quelle est la durée
entre chaque exécution. Si chaque exécution se suit à une ou deux
secondes près, le 3 peut s'expliquer par le déclenchement de bgwriter.


--
Guillaume.

http://www.postgresqlfr.org

http://dalibo.com

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

Re: [pgsql-fr-generale] Problème d'update et de performance

Le Friday 16 May 2008, Valérie SCHNEIDER a écrit :
> Bonjour,

Bonjour,

voir réponses plus bas.

> Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
> bit avec 4 Go de RAM :
>
> Date: mer mai 14 09:38:14 GMT 2008
> Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
> Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
> GNU/Linux
> Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
> Version Postgresql: 8.3.1
>
> Au niveau de postgresql .conf :
> # - Memory -
> shared_buffers = 1024MB # min 128kB or
> max_connections*16kB
>
> # - Checkpoints -
> checkpoint_segments = 10 # in logfile segments, min 1,
> 16MB each
>
> # pour les vacuum
> maintenance_work_mem = 256MB # min 1MB
>
> # Pour les operations de tri
> work_mem = 16MB # min 64kB
>
> # memoire partagee utilisee par une transaction typique.
> wal_buffers = 1024kB # min 32kB
>
> autovacuum = off # enable autovacuum subprocess?
>
>
> Un cron effectue des analyze sur les tables à intervalles choisis.
>
> On effectue un update sur une table de 5 millions de lignes, de taille
> environ 3Go, portant sur environ 50000 lignes, selon des critères
> utilisant un index.
> En exécutant plusieurs fois à la suite le même update (donc à partir du
> second plus aucune ligne n'est mise à jour) on observe des temps très
> longs pour finalement tomber à quelques millisecondes (qui est le
> résultat attendu).
>
> Que se passe-t'il d'après vous ?

Plusieurs possibilités me viennent à l'esprit, je suppose que vous les avez
déjà écarté mais je les donne au cas où :

* la charge du serveur/des accès disques est la meme pendant chaque update ?

* requetes SQL concurrentielles ? (pas de verrous)

* mouvement du cache disque ou des shared_buffer.

Connaitre les volumes de l'index et de la table serait un plus.
Vous pourriez activer 'log_checkpoints'.
Et enfin des outils comme iostat/vmstat pourraient s'avérer utiles également.


--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

[pgsql-es-ayuda] permisos

 
Hola, necesito dar permisos a un usuario para que pueda consultar, insertar, modificar y eliminar datos que solo le pertenecen a ese usuario. Tengo una tabla de usuarios, detalle y curso. El problema es que no se que utilizar para ello si una funcion, trigger o vista no tengo muy claros los conceptos por favor si alguien me puede ayudar gracias.

[GENERAL] Installing debugger

I have built posgresql 8.3.1 on a FreeBSD 6.1 via the ports system
WITH_DEBUG=true and also on a Windows machine with the debugger
selected. I see the plugin_debugger.dll under the lib/plugins folder on
the Windows machine as well. But when I look for the procs, they are not
there....

template1=# select * from pg_proc where proname like 'pldbg%';
proname | pronamespace | proowner | prolang | procost | prorows | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | prosrc | probin | proconfig | proacl
---------+--------------+----------+---------+---------+---------+----------+-----------+-------------+-----------+-------------+----------+------------+-------------+----------------+-------------+-------------+--------+--------+-----------+--------
(0 rows)

Can someone help or point me to some docs that can tell me what else I
need to do to enable debugging plpgsql?

--
Robert


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

Re: [GENERAL] SSL auth problem

>> don't know where it looking for "root.crl", but it is in directory
>> with root.crt and server.key, server.crt
>
> That should be harmless...

removed root.crl. the same effect

> Let me reexamine your original mail:
>
>> generating another key on server:
> [...]
>> signing on CA:
>> openssl req -x509 -in server.req -text -key our.key -out server.crt
>
> That's the problem, I think.

> With this statement you generate a self signed certificate from server.req
> (check with "openssl x509 -in server.crt -text -noout").
>
> What you need is a certificate signed by root.crt.
>
> You can do it like this:
>
> openssl x509 -req -in server.req -CA root.crt -CAkey our.key -CAcreateserial -out server.crt
>
> See if that gets rid of the message!

another error appeared:

psql: SSL error: sslv3 alert bad certificate

so I started from beginning:
on CA:
openssl genrsa -out our.key 2048

creating self-signed serificate:
openssl req -new -key our.key -out our.req
openssl req -x509 -in our.req -text -key our.key -out root.crt

copied root.crt to client and postgres server

on server:
openssl genrsa -out server.key 2048

on CA:
openssl x509 -req -in /tmp/server.req -CA ./root.crt -CAkey our.key
-CAcreateserial -out server.crt

on client:
openssl genrsa -out postgresql.key 2048
openssl req -new -key postgresql.key -out cl.req

on CA:
openssl x509 -req -in /tmp/cl.req -CA ./root.crt -CAkey our.key
-CAcreateserial -out postgresql.crt

files on client host:
postgresql.crt (signed by CA, -- root.crt)
postgresql.key (client private and public keys)
root.crt

files on postgresql server:
server.key (priv and pub keys)
server.crt (signed by root CA)
root.crt

stopped postgresql and started again

on client:

psql "dbname=me sslmode=require host=postgresql_host user=me"
psql: SSL error: sslv3 alert bad certificate

on server in logs:
postgres[29299]: [3-1] LOG: could not accept SSL connection: no
certificate returned

where I was wrong? : (

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

[pgsql-fr-generale] Re: Problème d'update et de performance

Valérie SCHNEIDER a écrit :
> Bonjour,
>
> Nous observons un comportement curieux d'une série d'update sur une base
> PG. Je suis preneur d'explication si vous en avez ...
>
> Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
> bit avec 4 Go de RAM :
>
> Date: mer mai 14 09:38:14 GMT 2008
> Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
> Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
> GNU/Linux
> Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
> Version Postgresql: 8.3.1
>
> Au niveau de postgresql .conf :
> # - Memory -
> shared_buffers = 1024MB # min 128kB or
> max_connections*16kB
>
> # - Checkpoints -
> checkpoint_segments = 10 # in logfile segments, min 1,
> 16MB each
>
> # pour les vacuum
> maintenance_work_mem = 256MB # min 1MB
>
> # Pour les operations de tri
> work_mem = 16MB # min 64kB
>
> # memoire partagee utilisee par une transaction typique.
> wal_buffers = 1024kB # min 32kB
>
> autovacuum = off # enable autovacuum subprocess?
>
>
> Un cron effectue des analyze sur les tables à intervalles choisis.
>
> On effectue un update sur une table de 5 millions de lignes, de taille
> environ 3Go, portant sur environ 50000 lignes, selon des critères
> utilisant un index.
> En exécutant plusieurs fois à la suite le même update (donc à partir du
> second plus aucune ligne n'est mise à jour) on observe des temps très
> longs pour finalement tomber à quelques millisecondes (qui est le
> résultat attendu).
>
> Que se passe-t'il d'après vous ?

la première fois, le job est fait avec beaucoup d'entrés-sorties
correspondant à des delete suivi d'insert
la 2e fois, toutes les lignes utiles sont ramenées dans le cache disque
de l'OS
la 3e fois, comme tout est monté dans la mémoire (il ne s'est rien apssé
entretemps), c'est instantané.

--
Jean-Max Reymond
CKR Solutions http://www.ckr-solutions.com


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

Re: [pgsql-fr-generale] Problème d'update et de performance

Rebonjour,
Je crois que je me suis mal exprimée : le comportement que je voudrais
comprendre, c'est pourquoi, en exécutant plusieurs fois le même update
(à la suite, l'un après l'autre, dans la même session psql), le temps
d'exécution ne soit pas quasi-immédiat à partir du second update
(puisque plus aucune ligne à updater et qu'on passe par l'index -ce que
confirme les explain analyze).
Valérie.

Le vendredi 16 mai 2008 à 09:15 +0000, Valérie SCHNEIDER a écrit :
> Bonjour,
>
> Nous observons un comportement curieux d'une série d'update sur une base
> PG. Je suis preneur d'explication si vous en avez ...
>
> Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
> bit avec 4 Go de RAM :
>
> Date: mer mai 14 09:38:14 GMT 2008
> Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
> Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
> GNU/Linux
> Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
> Version Postgresql: 8.3.1
>
> Au niveau de postgresql .conf :
> # - Memory -
> shared_buffers = 1024MB # min 128kB or
> max_connections*16kB
>
> # - Checkpoints -
> checkpoint_segments = 10 # in logfile segments, min 1,
> 16MB each
>
> # pour les vacuum
> maintenance_work_mem = 256MB # min 1MB
>
> # Pour les operations de tri
> work_mem = 16MB # min 64kB
>
> # memoire partagee utilisee par une transaction typique.
> wal_buffers = 1024kB # min 32kB
>
> autovacuum = off # enable autovacuum subprocess?
>
>
> Un cron effectue des analyze sur les tables à intervalles choisis.
>
> On effectue un update sur une table de 5 millions de lignes, de taille
> environ 3Go, portant sur environ 50000 lignes, selon des critères
> utilisant un index.
> En exécutant plusieurs fois à la suite le même update (donc à partir du
> second plus aucune ligne n'est mise à jour) on observe des temps très
> longs pour finalement tomber à quelques millisecondes (qui est le
> résultat attendu).
>
> Que se passe-t'il d'après vous ?
>
>
> Ci-dessous en pièce jointe la description de la table, des index, et une
> série d'explain analyze update.
>
>
> Merci !
> Valérie.
>
--

********************************************************************
* Les points de vue exprimes sont strictement personnels et *
* n'engagent pas la responsabilite de METEO-FRANCE. *
********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
* 42, avenue G. Coriolis Email : Valerie.Schneider@meteo.fr *
* 31057 TOULOUSE Cedex 1 - FRANCE

http://www.meteo.fr

*
********************************************************************


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

Re: [pgsql-es-ayuda] como pasar variables de una pagina php a otra usando session (variables globales)

Google es nuestro mejor amigo..

http://www.google.pt/search?sourceid=navclient&hl=en-GB&ie=UTF-8&&q=pasar+variables+PHP+con+sessiones

Slds ojala sirva
J.

On Fri, May 16, 2008 at 12:46 PM, Ricardo Conde <ricardocondef@gmail.com> wrote:
> Hola ,
> tengo que pasar variables de una pagina a otra en php y quería hacerlo
> usando sesiones.
> alguien puede ayudarme ?
>

--
----------------------
Slds.
jchavez
linux User #397972 on http://counter.li.org/
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [SQL] Query tuning

Hi kapil,

Here you have specified 3 tables, does JOB_TYPE_FIRST and JOB_TYPE_SECOND both contain all the JOBID in the third table?

Maybe i can help you if you elaborate your problem a bit more.

Regards,
Moiz Kothari

On Thu, May 15, 2008 at 11:30 AM, <kapil.munish@wipro.com> wrote:

Hi,

 

 

I have a query which is run across 3 tables JOB_TYPE_FIRST, JOB_TYPE_SECOND and JOB_ALLOCATION_WORKLIST.

 

The column JOBID is referenced in JOB_ALLOCATION_WORKLIST table and primary key in both JOB_TYPE_FIRST, JOB_TYPE_SECOND tables.

 

There is one more column BOOK_ID which is supplied as the binding parameter to the query. The query looks like:

 

select count(distinct(j.JOBID)) as jobCount

from  JOB_TYPE_FIRST a, JOB_TYPE_SECOND b, JOB_ALLOCATION_WORKLIST j

where (( a.JOBID = j.JOBID)

and (a.BOOK_ID = :bookId))

or ((b.JOBID = j.JOBID)

and (b.BOOK_ID = :bookId));

 

As the records in the database are too large it is having huge cost and stalling the server and takes lot of time.

 

Can anyone suggest a better way to fetch the results for the query or tune it? Any help would be highly appreciated.

 

 

Thanks & Regards,

Kapil

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

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

www.wipro.com




--
Hobby Site : http://dailyhealthtips.blogspot.com