Monday, June 30, 2008

Re: [HACKERS] Remove redundant extra_desc info for enum GUC variables?

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> Does this patch look like what you meant? It should split them apart,
>> and it also hides the undocumented levels, but still accept it (now that
>> we have the ability to hide GUC vars)
>
> Seems reasonable, although I'm still dissatisfied with the handling of
> the "debug" alias for debug2. I think if it's not hidden then it has
> to be placed in correct sort position. Since it's not documented in
> config.sgml, I think marking it hidden would be fine.

Good point, and thanks for the quick review. Will fix and apply.

//Magnus

--
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] Remove redundant extra_desc info for enum GUC variables?

Magnus Hagander <magnus@hagander.net> writes:
> Does this patch look like what you meant? It should split them apart,
> and it also hides the undocumented levels, but still accept it (now that
> we have the ability to hide GUC vars)

Seems reasonable, although I'm still dissatisfied with the handling of
the "debug" alias for debug2. I think if it's not hidden then it has
to be placed in correct sort position. Since it's not documented in
config.sgml, I think marking it hidden would be fine.

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: [PERFORM] Does max size of varchar influence index size

On Mon, 2008-06-30 at 18:57 +0200, Franck Routier wrote:
> Hi,
>
> I have problems with my database becoming huge in size (around 150 GB
> right now, and 2/3 for only three tables, each having around 30 millions
> tuples. Space is spent mainly on indices.).
>
> I have a lot of multi-column varchar primary keys (natural keys), and
> lot of foreign keys on these tables and thus a lot of indices.
>
> When using VARCHAR, we defaulted to VARCHAR(32) (because on _some_ of
> the identifiers, we have to apply md5).
>
> We assumed that using VARCHAR(32) but having values at most 4 characters
> long (for example) wouldn't influence indices size, ie it would be the
> same as using VARCHAR(4) to keep the example.
>
> Now I really doubt if we were right :)
>
> So, what should we expect ? And are there other factors influencing
> indices size ?
>
> Thanks,
> Franck

Is there any particular reason that you're not using a surrogate key? I
found that switching from natural to surrogate keys in a similar
situation made the indexes not only smaller, but faster.

It really only became an issue after our individual tables got larger
than 20-25G, but I think we got lucky and headed the issue off at the
pass.

I think it should be fairly trivial* to set up a test case using
pg_total_relation_size() to determine whether your suspicions are
correct.

-Mark

* It may not be as trivial as I say, or I'd have done it in the 5
minutes it took to write this email.


--
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] insert data into partitioned tables via Java application

Craig,

Thanks for your reply. I found a work around for this. I
created an insert procedure that will directly insert the data into
partitioned tables based on partition key instead off base table. On the
application side they are calling these procedures to perform inserts.

Thanks,
Cap20.


Craig Ringer wrote:
>
> cap20 wrote:
>> I am trying to do an insert data into EDB partitioned tables via front
>> end
>> application (Java) but the application failing to do an insert. The
>> problem
>> I identified is since the insert happening into partitioned tables in the
>> background when you do an insert into base table, so the insert returning
>> returning Zero rows affected message back to application. So it is
>> failing,
>> is there any work-around for this.
>
> This is somewhat of a frequently asked question, and should probably go
> into the published FAQ. See, for example, the recent thread
> "Partitioning: INSERT 0 0 but want INSERT 0 1".
>
> As far as I can tell there's no way to alter Pg's behaviour so it
> returns the number of rows inserted. This is because there really were
> zero rows inserted into the master table; the rows were inserted into
> other tables instead.
>
> It'd be nice to be able to mark a rule so that it added the number of
> rows affected to the total returned by the query, but this is AFAIK not
> currently possible.
>
> Your app needs to either insert into the real table rather than the
> partition master table or it needs to ignore the number of rows
> affected. If you're using a Java persistence layer it probably offers
> the latter as a configuration option. Alternately, as described by Neil
> Peter Braggio just recently on one of the Pg lists:
>
> Neil Peter Braggio wrote:
>> I have the same problem in PG 8.2
>>
>> To resolve this issue I had to create a new table with the same
>> structure than the partitioned table with a trigger for insert and
>> update. All the operations the application have to do are directed to
>> this new table.
>>
>> When a new record is inserted in the new table the trigger insert a
>> new record with the same values into the partitioned table and then
>> delete all records from this new table. In updates operations the
>> trigger redirect the operation to the partitioned table too.
>>
>> With this _not elegant_ solution our Java application is able to do its
>> job.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

--
View this message in context: http://www.nabble.com/insert-data-into-partitioned-tables-via-Java-application-tp17957653p18203492.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


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

[GENERAL] Max array size

Hi,

Does anyone know the maximum number of elements allowed in a
one-dimensional array? It looks like my script may add at least a
couple million, not sure if it will hit a limit...

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

Re: [ODBC] SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE

On Mon, Jun 30, 2008 at 8:42 AM, Pam Driscoll <pamd@us.ibm.com> wrote:
> I'm having the same problem you reported, but only when connecting to an
> Oracle 10.2 database. Our code works fine on earlier versions of Oracle as
> well as DB2 and SQL Server. Did you ever figure out what the problem was and
> how to resolve it?

Yes. The cause was unexpected. The short answer was that while
inside a transaction I was re-assigning a recordset object variable to
point to a newly instantiated recordset object without first closing
the recordset object that previously pointed to. ADO didn't like
this, so it purposely passed an invalid SQL statement to the server so
that the server would be forced a rollback of the entire transaction.

The statement: "SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER
TO UNPREPARED STATE;" is what ADO passed to the server to force an
exception.

Although I have only experienced this project with reassigning opened
recordset object references, I would expect and reassigning other
opened ADODB object would produce the same result.
--
Regards,
Richard Broersma Jr.

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

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

Re: [ADMIN] Proposal for restoring a dump into a database with a different owner

Bruce Momjian <bruce@momjian.us> writes:
> I see you didn't get a response this request. I am thinking it would be
> better to implement some form of massive change ownership option that
> can be done to change ownership after the dump is restored.

We already have REASSIGN OWNED, though that doesn't entirely do the job
because it doesn't worry about reassigning grants. Reassigning grants
seems like a big headache for the --map-users proposal too.

regards, tom lane

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

Re: [ADMIN] Proposal for restoring a dump into a database with a different owner

On Mon, Jun 30, 2008 at 1:50 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> I see you didn't get a response this request. I am thinking it would be
> better to implement some form of massive change ownership option that
> can be done to change ownership after the dump is restored.

Currently the way I do that is to dump with the -O option, and then
restore to the user account I want everything to belong to.

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

Re: [GENERAL] php + postgresql website ?

On Mon, Jun 30, 2008 at 1:38 PM, paragasu <paragasu@gmail.com> wrote:
> i am very surprise to see skype use postgresql database at their database.
> it really make me confident about postgresql. the only problem with
> postgresql is that not
> many $5/month hosting out there preinstalled with postgresql.

Be sure and look here:

http://www.postgresql.org/support/professional_hosting

for companies that host postgresql. There are a few in there that are
in the $10 a month range.

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

[pgsql-es-ayuda] SRF en lenguaje C.... Jerarquia

Que tal lista

tengo una relación jerárquica en una tabla implementada con
nested-interval utilizando secuencias de farey. Utilice la
implementación que tiene a modo de ejemplo en la documentación de
postgres para los números racionales; para obtener la ascendencia
directa de un nodo utilizo un algoritmo que esta implementado en la
siguiente SRF en lenguaje C

Datum
obtAscendencia(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;

intervaloFarey *intF; //intervalo de Farey que subexiste
durente
//la gneracion de lo jerarqui
Racional *r = NULL;//el resultado de obtPadre

Racional *pi; //padre inicial

TupleDesc tupdesc;
AttInMetadata *attinmeta;

/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
{
MemoryContext oldcontext;

intF = (intervaloFarey *)
palloc(sizeof(intervaloFarey));//fjmb

//guardamos el padre
pi = (Racional *) PG_GETARG_POINTER(0);
intF->izq.num = pi->num;
intF->izq.den = pi->den;

pi = (Racional *) PG_GETARG_POINTER(1);
intF->der.num = pi->num;
intF->der.den = pi->den;


/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();

//memeoria fija durante la generacion de la descendencia directa
funcctx->user_fctx = intF;

/* switch to memory context appropriate for multiple function
calls */
oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);


/* Build a tuple descriptor for our result type */
if (get_call_result_type(fcinfo, NULL, &tupdesc) !=
TYPEFUNC_COMPOSITE)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("function returning record called in context
"
"that cannot accept type record")));

/*
* generate attribute metadata needed later to produce tuples
from raw
* C strings
*/
attinmeta = TupleDescGetAttInMetadata(tupdesc);
funcctx->attinmeta = attinmeta;

MemoryContextSwitchTo(oldcontext);
}

/* stuff done on every call of the function */
funcctx = SRF_PERCALL_SETUP();

intF = funcctx->user_fctx;//fjmb

attinmeta = funcctx->attinmeta;

r = obtPadre( &intF->izq, &intF->der );

if ( ( r->num != 1 ) &&
( r->den != 2 ) )
{
char **strRel;
HeapTuple tuple;
Datum result;

/* elog(INFO, "Enviando izq: %d,%d der: %d,%d ",
intF->izq.num,
intF->izq.den,
intF->der.num,
intF->der.den
);*/


//actualizamos la memoria fija durante
//el retorno de los nodos generado
//(ver documentacion de funciones SRF)
intF->der.num = intF->izq.num;
intF->der.den = intF->izq.den;
intF->izq.num = r->num;
intF->izq.den = r->den;


strRel = (char **) palloc(1 * sizeof(char *));
strRel[0] = (char *) palloc( 100 );

snprintf(strRel[0], 100, "(%d,%d)", r->num, r->den);

tuple = BuildTupleFromCStrings(attinmeta, strRel );

/* make the tuple into a datum */
result = HeapTupleGetDatum(tuple);

/* clean up (this is not really necessary) */
pfree(strRel[0]);
pfree(strRel);

SRF_RETURN_NEXT(funcctx, result);
}
else /* do when there is no more left */
{
SRF_RETURN_DONE(funcctx);
}
}

la declaración de esta función es la siguiente:


CREATE TYPE reg_racional AS ( izq Racional );

CREATE OR REPLACE FUNCTION obtAscendencia( racional, racional )
RETURNS SETOF reg_racional
AS '/usr/lib/postgresql/farey', 'obtAscendencia'
LANGUAGE C IMMUTABLE STRICT;


Todo funciona correctamente con la siguiente sentencia en psql

pba=# select obtascendencia ('(11099,15685)','(6808,9621)');

obtascendencia
------------------
("(4291, 6064)")
("(1774, 2507)")
("(1031, 1457)")
("(288, 407)")
("(121, 171)")
("(75, 106)")
("(29, 41)")
("(12, 17)")
("(7, 10)")
("(2, 3)")


pero si ejecuto:

pba=# select * from obtascendencia ('(11099,15685)','(6808,9621)');

se queda colgada la consulta ... (tengo que matar el proceso desde otra
terminal) ... ¿cómo puede resolver este problema?

Estoy investigando ... si alguien puede orientarme se los agradeceré


Saludos cordiales

PD. si a alguien le interesa el código completo con gusto se los envío

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

Re: [HACKERS] Remove redundant extra_desc info for enum GUC variables?

Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.457
diff -c -r1.457 guc.c
*** src/backend/utils/misc/guc.c 30 Jun 2008 10:58:47 -0000 1.457
--- src/backend/utils/misc/guc.c 30 Jun 2008 19:50:24 -0000
***************
*** 172,178 ****
/*
* Options for enum values defined in this module.
*/
! static const struct config_enum_entry message_level_options[] = {
{"debug", DEBUG2, false},
{"debug5", DEBUG5, false},
{"debug4", DEBUG4, false},
--- 172,183 ----
/*
* Options for enum values defined in this module.
*/
!
! /*
! * We have different sets for client and server message level options because
! * they sort slightly different (see "log" level)
! */
! static const struct config_enum_entry client_message_level_options[] = {
{"debug", DEBUG2, false},
{"debug5", DEBUG5, false},
{"debug4", DEBUG4, false},
***************
*** 180,189 ****
--- 185,211 ----
{"debug2", DEBUG2, false},
{"debug1", DEBUG1, false},
{"log", LOG, false},
+ {"info", INFO, true},
+ {"notice", NOTICE, false},
+ {"warning", WARNING, false},
+ {"error", ERROR, false},
+ {"fatal", FATAL, true},
+ {"panic", PANIC, true},
+ {NULL, 0, false}
+ };
+
+ static const struct config_enum_entry server_message_level_options[] = {
+ {"debug", DEBUG2, false},
+ {"debug5", DEBUG5, false},
+ {"debug4", DEBUG4, false},
+ {"debug3", DEBUG3, false},
+ {"debug2", DEBUG2, false},
+ {"debug1", DEBUG1, false},
{"info", INFO, false},
{"notice", NOTICE, false},
{"warning", WARNING, false},
{"error", ERROR, false},
+ {"log", LOG, false},
{"fatal", FATAL, false},
{"panic", PANIC, false},
{NULL, 0, false}
***************
*** 2449,2461 ****
{
{"client_min_messages", PGC_USERSET, LOGGING_WHEN,
gettext_noop("Sets the message levels that are sent to the client."),
! gettext_noop("Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, "
! "DEBUG1, LOG, NOTICE, WARNING, and ERROR. Each level includes all the "
! "levels that follow it. The later the level, the fewer messages are "
! "sent.")
},
&client_min_messages,
! NOTICE, message_level_options,NULL, NULL
},

{
--- 2471,2481 ----
{
{"client_min_messages", PGC_USERSET, LOGGING_WHEN,
gettext_noop("Sets the message levels that are sent to the client."),
! gettext_noop("Each level includes all the levels that follow it. The later"
! " the level, the fewer messages are sent.")
},
&client_min_messages,
! NOTICE, client_message_level_options,NULL, NULL
},

{
***************
*** 2480,2491 ****
{
{"log_min_messages", PGC_SUSET, LOGGING_WHEN,
gettext_noop("Sets the message levels that are logged."),
! gettext_noop("Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, "
! "INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. Each level "
! "includes all the levels that follow it.")
},
&log_min_messages,
! WARNING, message_level_options, NULL, NULL
},

{
--- 2500,2509 ----
{
{"log_min_messages", PGC_SUSET, LOGGING_WHEN,
gettext_noop("Sets the message levels that are logged."),
! gettext_noop("Each level includes all levels that follow it.")
},
&log_min_messages,
! WARNING, server_message_level_options, NULL, NULL
},

{
***************
*** 2495,2501 ****
"specified level or a higher level are logged.")
},
&log_min_error_statement,
! ERROR, message_level_options, NULL, NULL
},

{
--- 2513,2519 ----
"specified level or a higher level are logged.")
},
&log_min_error_statement,
! ERROR, server_message_level_options, NULL, NULL
},

{
Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> Tom Lane wrote:
>>> Yeah: LOG level sorts differently in the two cases; it's fairly high
>>> priority for server log output and much lower for client output.
>
>> Ok, easy fix if we break them apart. Should we continue to accept
>> values that we're not going to care about, or should I change that at
>> the same time? (for example, client_min_messages doesn't use INFO,
>> but we do accept that in <= 8.3 anyway)
>
> I'd be inclined to keep the actual behavior the same as it was.
> We didn't document INFO for this variable, perhaps, but it's accepted
> and has a well-defined behavior.

Sorry for not getting back to this one sooner, it ended up in the wrong
end of the queue.

Does this patch look like what you meant? It should split them apart,
and it also hides the undocumented levels, but still accept it (now that
we have the ability to hide GUC vars)

//Magnus

Re: [GENERAL] php + postgresql website ?

On Tue, 1 Jul 2008, paragasu wrote:

> the only problem with postgresql is that not many $5/month hosting out
> there preinstalled with postgresql.

There is a list of hosting providers at

http://www.postgresql.org/support/professional_hosting

I clicked on the first North American one there, A2 Hosting, and found
plans starting at $7/month. hub.org has them starting at $8, there's a
lot of places to poke through there. You might not get all the way down
to $5 but there are options close to that.

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

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

Re: [PATCHES] [NOVICE] encoding problems

Cliff Nieuwenhuis wrote:
> On Fri, 9 May 2008 08:38:01 -0400
> Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> > Bruce Momjian escribi?:
> > > Guillaume Smet wrote:
> > > > On Thu, May 8, 2008 at 9:11 PM, Bruce Momjian <bruce@momjian.us>
> > > > wrote:
> >
> > > > As I mentioned it before, is there any chance for this fix to be
> > > > backported to 8.3 branch? IMHO it's a usability regression.
> > >
> > > No, we don't change behaviors in back branches unless we get lots of
> > > complaints, and we haven't in this case.
> >
> > complaints++
> >
>
> I suppose this a "Me Too" post, but Bruce Momjian invites it. You
> folks take this to a level way beyond me, but I can tell you that the
> idea of using spaces instead of the terminal hard tabs would solve my
> problem -- I'd prefer _any_ choice of whitespace over seeing "\x09" on
> the terminal.

FYI, this was corrected in 8.3.3.

--
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: [ADMIN] Proposal for restoring a dump into a database with a different owner

I see you didn't get a response this request. I am thinking it would be
better to implement some form of massive change ownership option that
can be done to change ownership after the dump is restored.

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

postgresql.20.j_random_hacker@spamgourmet.com wrote:
> Hi,
>
> I have the same problem as Andreas Haumer did in this thread:
> http://archives.postgresql.org/pgsql-admin/2008-01/msg00128.php -- I want to
> be able to easily (i.e. programmatically) copy a database from one place to
> another, changing the owners of all contained objects in the process.
>
> While I very much appreciate Tom Lane's fast and helpful responses to
> Andreas on that thread, it doesn't quite address my problem: there is no
> simple, automatable 1- or 2-step process that can accomplish this (without
> Andreas's (admittedly neat) trick of temporarily changing the destination
> user to superuser status). The best I've been able to do is hack up a Perl
> script that parses the output of pg_restore -l, directing
> superuser-requiring operations to one file and non-superuser-requiring
> operations to another; but afterwards the superuser-requiring operations
> still have to have the owners of the objects they produce manually
> reassigned.
>
> My instincts (which could be wrong...) tell me that this is actually a
> fairly common problem. So, I suggest the following enhancement to
> pg_restore: add a --map-users command-line option that accepts the name of a
> file containing two usernames on each line, <from> and <to>. Then (provided
> -O was not specified) when producing ALTER ... OWNER TO commands, simply
> replace every <from> user listed in this file with the corresponding <to>
> user.
>
> Another niggle is that the COMMENT ON DATABASE command, produced by
> pg_restore when run without the -d option, always refers to the name of the
> original database, which will cause an error if the new DB has a different
> name. It would be nice to have an option (or other means) to remedy this.
>
> It seems to me that these things would be pretty simple to implement and
> sufficiently general to tackle this problem neatly, without opening up any
> security holes (you would still need to be *some* DB superuser for the ALTER
> ... OWNER TO commands to work).
>
> Does this sound sensible? If Tom or another high-ranking PostgreSQLer okays
> it in principle, I suppose I could try developing a patch for pg_restore
> myself. (Never done this before but there's a first time for everything...)
>
> TIA,
> Tim White
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

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

Re: [GENERAL] "out of balance" result on select from suspected index corruption [RESOLVED]

"Henry - Zen Search SA" <henry@zen.co.za> writes:
> The problem was this: a silly SQL error (misuse of OR and missing
> parentheses) resulted in a massive result set which resulted in OOM - if
> the select is run manually (no funny "out of balance" strings).

> If it's run in a function (which uses a FOR loop, which uses implicit
> cursors), then the "out of balance" string is returned for the TEXT column
> almost immediately.

Is it possible the "out of balance" is actually an expected result from
the query --- ie, it's in a table somewhere? In the first case you'd
not see it because of the OOM failure, but with a cursor you'd be able
to process (at least some of) the query output ...

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] psql wrapped format default for backslash-d commands

Bruce Momjian wrote:
> Also, about the format 'auto' idea that uses expanded display, I am
> thinking because expanded is a separate setting and not a format, I
> should just add a possible 'auto' value to the expanded format that
> could be triggered by either 'aligned' or 'wrapped' formats when the
> output doesn't fit on the screen.
>
> (In hindsight, I think we should have had pager be on/auto/off rather
> than always/on/off; the later is kind of confusing and would be
> inconsistent if I add 'auto' to expanded.)

Added to psql TODO:

o Add "auto" expanded mode that outputs in expanded format if
"wrapped" mode can't wrap the output to the screen width

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

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

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

[COMMITTERS] pgsql: Add psql TODO: > o Add "auto" expanded mode that outputs in

Log Message:
-----------
Add psql TODO:

> o Add "auto" expanded mode that outputs in expanded format if
> "wrapped" mode can't wrap the output to the screen width
>
>

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00417.php
>

Modified Files:
--------------
pgsql/doc:
TODO (r1.2478 -> r1.2479)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/TODO?r1=1.2478&r2=1.2479)
pgsql/doc/src/FAQ:
TODO.html (r1.984 -> r1.985)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/FAQ/TODO.html?r1=1.984&r2=1.985)

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

Re: [HACKERS] psql wrapped format default for backslash-d commands

Bryce Nesbitt wrote:
>
> Brendan Jurd wrote:
> > I really like the idea of wrapping, but after playing with the format
> > a bit myself, I have to agree with Tom that breaking in the middle of
> > words produces some very nasty output.
> >
> > If the format could be improved to only wrap on word boudaries, that
> > would increase its appeal dramatically.
> >
> > Anybody got a rough idea how difficult it would be to add
> > word-awareness int o the wrapping code?
>
> I chose not to, when writing the patch, because it makes the result flow
> over many more lines.
> And regardless, it pretty much has to cut long "words", of which there
> are many in typical SQL output.
> And, I hardly ever read actual large blocks of English at the SQL
> prompt, so: designing for that use case did
> not float my boat at the time.
> And, word wrapping messes with the cell contents more than I was
> comfortable doing. With the current wrapping all whitespace is preserved
> and apparent.
>
> But, if your boat is floated, then by all means go for it.

Added to TODO:

o Add option to wrap column values at whitespace boundaries,
rather than chopping them at a fixed width.

Currently, "wrapped" format chops values into fixed
widths. Perhaps the word wrapping could use the same
algorithm documented in the W3C specification.

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00404.php

http://www.w3.org/TR/CSS21/tables.html#auto-table-layout

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

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

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

[COMMITTERS] pgsql: Add psql TODO item: > o Add option to wrap column values at

Log Message:
-----------
Add psql TODO item:

> o Add option to wrap column values at whitespace boundaries,
> rather than chopping them at a fixed width.
> Currently, "wrapped" format chops values into fixed
> widths. Perhaps the word wrapping could use the same
> algorithm documented in the W3C specification.
>

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00404.php
>

http://www.w3.org/TR/CSS21/tables.html#auto-table-layout

Modified Files:
--------------
pgsql/doc:
TODO (r1.2477 -> r1.2478)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/TODO?r1=1.2477&r2=1.2478)
pgsql/doc/src/FAQ:
TODO.html (r1.983 -> r1.984)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/FAQ/TODO.html?r1=1.983&r2=1.984)

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

Re: [GENERAL] php + postgresql website ?

i am very surprise to see skype use postgresql database at their database.
it really make me confident about postgresql. the only problem with
postgresql is that not
many $5/month hosting out there preinstalled with postgresql.

i only have access to postgresql database after i buy a vps hosting somewhere =)
thank.. thus website really help me a lot with my decision..

On 6/30/08, Chandra ASGI Tech <chandraasgi@gmail.com> wrote:
> Skype uses PostgreSQL as its backend.
> http://highscalability.com/skype-plans-postgresql-scale-1-billion-users
>
> C.S.Chandrasekkar
>
> On Mon, Jun 30, 2008 at 8:06 AM, paragasu <paragasu@gmail.com> wrote:
>
>> i can name a lot of website written in mysql and php.
>> currently, i am planning to use postgresql database for my next
>> project. i know there is a lot of testimonial about postgresql is
>> better than mysql.
>> can anyone please give me an example of website using postgresql database?
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>

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

Re: [BUGS] Recovery failed on a backup with " lock AccessShareLock on object 16477/244169/0 is already held"

"John Smith" <sodgodofall@gmail.com> writes:
> 2008-06-17 23:53:53.206 Local time zone must be set--see zic manual
> page PANIC: failed to re-find shared lock object
> 2008-06-17 23:53:53.207 Local time zone must be set--see zic manual
> page STATEMENT: commit prepared '148969' ;

> I believe this panic is probably bug #3245 based on the description of
> that bug - http://archives.postgresql.org/pgsql-bugs/2007-04/msg00075.php

Yeah, looks like it to me too.

> At this point I attempted to do a recovery using the continuous
> archive backup on the warm standby system. Instead of recovering
> correctly it encountered this FATAL error where a AccessSharedLock was
> already held.
> 2008-06-18 00:05:34.298 Local time zone must be set--see zic manual
> page FATAL: lock AccessShareLock on object 16477/244169/0 is already
> held
> 2008-06-18 00:05:34.299 Local time zone must be set--see zic manual
> page LOG: startup process (PID 17377) exited with exit code 1
> 2008-06-18 00:05:34.299 Local time zone must be set--see zic manual
> page LOG: aborting startup due to startup process failure

> Is this FATAL error seen on recovery a different bug or is it just a
> direct result of bug #3245?

It probably is the same bug. The underlying cause of that bug is
explained here:
http://archives.postgresql.org/pgsql-bugs/2007-04/msg00129.php
I think what you are seeing is just a variant case caused by the same
lock being written out to the twophase file twice. In any case there's
probably little point in digging further until you've updated to a
version with that fix --- if you still see the problem afterward,
we can look closer.

BTW, what's with the bizarre "Local time zone must be set--see zic
manual" where the timezone should be? Are you intentionally selecting
the "Factory" zone?

regards, tom lane

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

Re: [ADMIN] Configuring Shared Buffers

On Mon, Jun 30, 2008 at 04:01:14PM -0300, Rafael Domiciano wrote:
> The Postgres version is 8.3.3 and I am using Fedora Core 8.
> I have in the actual server around 70 connections the same time. I am
> assigning for this 100.

And what does the server do? Mainly SELECT / UPDATE / INSERT / DELETE,
how is workload distributed, are queries very complex or pretty simple
are there huge joins across many tables?

How many tables do you have and what are the largest ones? Are we talking
about thousands of rows or millions or lots of millions?

Tino.

> > > Folks, I am configuring a new Postgres Server, that's gonna substitute
> > the
> > > critical server of the enterprise.
> > > I have a good machine:
> > > Quad-Core 2.5 Ghz
> > > 4 Gb RAM
> > > 1 Dedicated HD 300 Gb Sata for the PostgreSQL Directory
> >
> > If it's the "critical server", you'd want to use a RAID1 for the disk so
> > your server and data survives a harddisk crash.
> >
> > > My question is:
> > > How much I could assign for the "Shared Buffers" parameter?
> > >
> > > The default assigned now is 24 Mb.
> >
> > That's rather low. I suppose, you may safely say 200Mb here (check that
> > you don't run into OS limits, they might need raising). You may also
> > want to increase work_mem, but it depends on what the DB does, how many
> > concurrent connections you have etc. pp. maintenance_work_mem may safely
> > be set rather high - it speeds up vacuum etc. and is only used during
> > such operations. Also look for effective_cache_size and max_fsm_pages.
> >
> > BTW: What version are you going to use?
> >
> > Tino.
> >
> > --
> > "What we nourish flourishes." - "Was wir nähren erblüht."
> >
> > www.craniosacralzentrum.de
> > www.forteego.de
> >
> > --
> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-admin
> >

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

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

[HACKERS] Bucket and batch

Hi!

I saw that functions which use hash join, like as ExecHashGetBucketAndBatch and ExecScanHashBucket, have bucket and batch concepts.
So, I would like to know the differences between bucket and batch.
I dont understand tables partitioning mechanism in hash join.


Thanks,
Ana Carolina

Re: [HACKERS] [GENERAL] Fragments in tsearch2 headline

> 1. Respects ShortWord and MinWords
> 2. Uses hlCover instead of Cover
> 3. Does not store norm (or lexeme) for headline marking
> 4. Removes ts_rank.h
> 5. Earlier it was counting even NONWORDTOKEN in the headline. Now it
> only counts the actual words and excludes spaces etc.
>
> I have also changed NumFragments option to MaxFragments as there may not
> be enough covers to display NumFragments.

Nice. But it will be good to resolve following issues:
1) Patch contains mistakes, I didn't investigate or carefully read it. Get

http://www.sai.msu.su/~megera/postgres/fts/apod.dump.gz and load in db.

Queries
# select ts_headline(body, plainto_tsquery('black hole'), 'MaxFragments=1') from
apod where to_tsvector(body) @@ plainto_tsquery('black hole');

and

# select ts_headline(body, plainto_tsquery('black hole'), 'MaxFragments=1') from
apod;

crash postgresql :(

2) pls, include in your patch documentation and regression tests.

>
> Another change that I was thinking:
>
> Right now if cover size > max_words then I just cut the trailing words.
> Instead I was thinking that we should split the cover into more
> fragments such that each fragment contains a few query words. Then each
> fragment will not contain all query words but will show more occurrences
> of query words in the headline. I would like to know what your opinion
> on this is.

Agreed.

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

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

Re: [PERFORM] VACUUM ANALYZE blocking both reads and writes to a table

Tom Lane wrote:

> (2) If it's autovacuum we're talking about, it will get kicked off the
> table if anyone else comes along and wants a conflicting lock.

Not on 8.2 though.

--
Alvaro Herrera

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

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

Re: [ADMIN] Configuring Shared Buffers

The Postgres version is 8.3.3 and I am using Fedora Core 8.
I have in the actual server around 70 connections the same time. I am assigning for this 100.


2008/6/30 Tino Schwarze <postgresql@tisc.de>:
On Mon, Jun 30, 2008 at 02:52:05PM -0300, Rafael Domiciano wrote:

> Folks, I am configuring a new Postgres Server, that's gonna substitute the
> critical server of the enterprise.
> I have a good machine:
> Quad-Core 2.5 Ghz
> 4 Gb RAM
> 1 Dedicated HD 300 Gb Sata for the PostgreSQL Directory

If it's the "critical server", you'd want to use a RAID1 for the disk so
your server and data survives a harddisk crash.

> My question is:
> How much I could assign for the "Shared Buffers" parameter?
>
> The default assigned now is 24 Mb.

That's rather low. I suppose, you may safely say 200Mb here (check that
you don't run into OS limits, they might need raising). You may also
want to increase work_mem, but it depends on what the DB does, how many
concurrent connections you have etc. pp. maintenance_work_mem may safely
be set rather high - it speeds up vacuum etc. and is only used during
such operations. Also look for effective_cache_size and max_fsm_pages.

BTW: What version are you going to use?

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.craniosacralzentrum.de
www.forteego.de

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

Re: [PERFORM] VACUUM ANALYZE blocking both reads and writes to a table

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Peter Schuller wrote:
>> Even with the fix the lock is held. Is the operation expected to be
>> "fast" (for some definition of "fast") and in-memory, or is this
>> something that causes significant disk I/O and/or scales badly with
>> table size or similar?

> It is fast.

Well, it's *normally* fast. In a situation where there are a whole lot
of empty pages at the end of the table, it could be slow. That's
probably not very likely on a heavily used table. One should also note
that

(1) The only way vacuum will be able to obtain an exclusive lock in the
first place is if there are *no* other transactions using the table at
the time.

(2) If it's autovacuum we're talking about, it will get kicked off the
table if anyone else comes along and wants a conflicting lock.

regards, tom lane

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

RE: [pgsql-es-ayuda] [OT] Cubanos en la lista

Hola a todos creo que es un poco tarde para el pos pero soy estudiante cubano 5 años en la carrera de informática en la uci, tengo 2 años de experiencia en el desarrollo con bases de datos y me gustaría formar parte del proyecto, sobre todo desarrollar sobre postgres

-----Original Message-----
From: pgsql-es-ayuda-owner@postgresql.org [mailto:pgsql-es-ayuda-owner@postgresql.org] On Behalf Of Danier Marante Jacas
Sent: lunes, 30 de junio de 2008 13:25
To: pgsql-es-ayuda@postgresql.org
Subject: RE: [pgsql-es-ayuda] [OT] Cubanos en la lista

también soy cubano, soy estudiante de 5 año de ingeniería informática en la UCI, tengo 2 años de experiencia como desarrollador de una db bastante grande, me encanta postgres aunque nunca he desarrollado fuerte sobre el,
me encantaría colaborar.
mi gtalk es dmjacas@gmail.com
________________________________

From: pgsql-es-ayuda-owner@postgresql.org on behalf of Luis Esteban de Dios Núñez
Sent: Sun 29.06.2008 09:54 am
To: pgsql-es-ayuda@postgresql.org
Subject: Re: [pgsql-es-ayuda] [OT] Cubanos en la lista

Hola Juan Carlos, Gabriely Reynier,

También yo estoy dispuesto a colaborar. He aprendido alguito sobre
PostgreSQL y PHP aunque me gusta más Python. Soy de Ciego de Avila pero
vivo hace ya algo en Pinar del Río. Trabajo en una SUM de Ciencias Médicas
en el municipio La Palma como Metodólogo de Investigaciones. Uso SuSE
Linux desda hace algún tiempo. El software libre es mi delirio.

Hoy lunes, cuando vi el primer mensaje de Edwin respondí anticipadamente,
imagínense tenía 181 email en mi bandeja. Esoty subscrito a varias listas:
opensuse-es, pgsql-es-ayuda, python-es, pyar, prog, mono-hispano, etc...

Que desastre, je je je.


--
Saludos

Luis Esteban de Dios Núñez
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán


--
TIP 7: no olvides aumentar la configuración del "free space map"
--
TIP 5: ¿Has leído nuestro extenso FAQ?

http://www.postgresql.org/docs/faqs.FAQ.html

Re: [PERFORM] VACUUM ANALYZE blocking both reads and writes to a table

Alvaro Herrera wrote:
> Peter Schuller wrote:
> > Actually, while on the topic:
> >
> > > date: 2007-09-10 13:58:50 -0400; author: alvherre; state: Exp; lines: +6 -2;
> > > Remove the vacuum_delay_point call in count_nondeletable_pages, because we hold
> > > an exclusive lock on the table at this point, which we want to release as soon
> > > as possible. This is called in the phase of lazy vacuum where we truncate the
> > > empty pages at the end of the table.
> >
> > Even with the fix the lock is held. Is the operation expected to be
> > "fast" (for some definition of "fast") and in-memory, or is this
> > something that causes significant disk I/O and/or scales badly with
> > table size or similar?
>
> It is fast.

To elaborate: it scans the relation backwards and makes note of how many
are unused. As soon as it finds a non-empty one, it stops scanning.
Typically this should be quick. It is not impossible that there are a
lot of empty blocks at the end though, but I have never heard a problem
report about this.

It could definitely cause I/O though.

> > I.e., is this enough that, even without the .4 bug, one should not
> > really consider VACUUM ANALYZE non-blocking with respect to other
> > transactions?
>
> You should consider it non-blocking.

The lock in conditionally acquired: as I said earlier, the code would
rather skip this part than block. So if there's some other operation
going on, there's no lock held at all. If this grabs the lock, then
other operations are going to block behind it, but the time holding the
lock should be short. Note, however, that sleeping for 20ms or more
because of vacuum_delay (the bug fixed above) clearly falls out of this
category, and easily explains the behavior you're seeing with 8.2.4.

--
Alvaro Herrera

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

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

Re: [HACKERS] WIP patch: reducing overhead for repeat de-TOASTing

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

> After playing with it for a little bit, I'm not convinced that it buys
> enough performance win to be worth applying --- the restriction of cache
> lifespan to one tuple cycle of a TupleTableSlot is awfully restrictive.
> (For example, sorts that involve toasted sort keys continue to suck,
> because the tuples being sorted aren't in Slots.) It would probably
> fix the specific case that the PostGIS hackers were complaining of,
> but I think we need something more.
>
> Still, I wanted to get it into the archives because the idea of indirect
> toast pointers might be useful for something else.

I do like that it handles even inline-compressed cases. What I didn't like
about the managed cache was that it couldn't handle such cases. I could easily
imagine the PostGIS case arising for inline compressed data structures. I
wonder if it isn't worthwhile just for that case even if there's a further
cache behind it for repeated fetches of out-of-line data.

--
Gregory Stark
EnterpriseDB

http://www.enterprisedb.com

Ask me about EnterpriseDB's RemoteDBA services!

--
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] "out of balance" result on select from suspected index corruption [RESOLVED]

On Mon, June 30, 2008 4:51 pm, Tom Lane wrote:
> The string "out of balance" appears nowhere in the PG 8.2.x sources.
> So I suppose it must have come from some add-on code, or perhaps got
> inserted on the client side. What data type is that column, and what
> non-core code is involved?

I have no idea where this comes from - I've grepped globally for that
silly string and can't find it. The installation is pretty standard -
pg+plsql. Must be something deep which I've forgotten about.

The problem was this: a silly SQL error (misuse of OR and missing
parentheses) resulted in a massive result set which resulted in OOM - if
the select is run manually (no funny "out of balance" strings).

If it's run in a function (which uses a FOR loop, which uses implicit
cursors), then the "out of balance" string is returned for the TEXT column
almost immediately.

Weird. Anyway, I'm just relieved the solution was fixing my stupid SQL.

Regards
Henry


--
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] Planner should use index on a LIKE 'foo%' query

Am 28.06.2008 um 21:19 schrieb Steinar H. Gunderson:

> On Sat, Jun 28, 2008 at 06:24:42PM +0200, Moritz Onken wrote:
>> SELECT distinct url from item where url like 'http://www.micro%'
>> limit
>> 10;
>
> Here, the planner knows the pattern beforehand, and can see that
> it's a
> simple prefix.
>> select *
>> from result
>> where exists
>> (select * from item where item.url LIKE result.url || '%' limit 1)
>> limit 10;
>
> Here it cannot (what if result.url was '%foo%'?).

That's right. Thanks for that hint. Is there a Postgres function which
returns a constant (possibly an escape function)?
>
>
> Try using something like (item.url >= result.url && item.url <=
> result.url ||
> 'z'), substituting an appropriately high character for 'z'.
>
>> The only explaination is that I don't use a constant when comparing
>> the
>> values. But actually it is a constant...

I created a new column in "item" where I store the shortened url which
makes "=" comparisons possible.

the result table has 20.000.000 records and the item table 5.000.000.
The query

select count(1) from result where url in (select shorturl from item
where shorturl = result.url);

will take about 8 hours (still running, just guessing). Is this
reasonable on a system with 1 GB of RAM and a AMD Athlon 64 3200+
processor? (1 SATA HDD)

regards,

moritz

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

Re: [PERFORM] Planner should use index on a LIKE 'foo%' query

Anfang der weitergeleiteten E-Mail:

> Von: Moritz Onken <onken@houseofdesign.de>
> Datum: 30. Juni 2008 09:16:06 MESZ
> An: Steinar H. Gunderson <sgunderson@bigfoot.com>
> Betreff: Re: [PERFORM] Planner should use index on a LIKE 'foo%' query
>
>
> Am 28.06.2008 um 21:19 schrieb Steinar H. Gunderson:
>
>> On Sat, Jun 28, 2008 at 06:24:42PM +0200, Moritz Onken wrote:
>>> SELECT distinct url from item where url like 'http://www.micro%'
>>> limit
>>> 10;
>>
>> Here, the planner knows the pattern beforehand, and can see that
>> it's a
>> simple prefix.
>>> select *
>>> from result
>>> where exists
>>> (select * from item where item.url LIKE result.url || '%' limit 1)
>>> limit 10;
>>
>> Here it cannot (what if result.url was '%foo%'?).
>
> That's right. Thanks for that hint. Is there a Postgres function
> which returns a constant (possibly an escape function)?
>>
>>
>> Try using something like (item.url >= result.url && item.url <=
>> result.url ||
>> 'z'), substituting an appropriately high character for 'z'.
>>
>>> The only explaination is that I don't use a constant when
>>> comparing the
>>> values. But actually it is a constant...
>
> I created a new column in "item" where I store the shortened url
> which makes "=" comparisons possible.
>
> the result table has 20.000.000 records and the item table 5.000.000.
> The query
>
> select count(1) from result where url in (select shorturl from item
> where shorturl = result.url);
>
> will take about 8 hours (still running, just guessing). Is this
> reasonable on a system with 1 GB of RAM and a AMD Athlon 64 3200+
> processor? (1 SATA HDD)
>
> regards,
>
> moritz
>


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

[ADMIN] Windows xp initdb POSTGRES_SUPERUSERNAME

Hello,

I wonder what this variable in initdb is about: POSTGRES_SUPERUSERNAME
Apparently the default should be 'postgres'. However, the value changes with the user that is logged in whenever I use 'initdb --show'. If I login as 'xy' the value is 'xy'. Shouldn't this be a fixed value in the cluster?
Could you tell me, please?

Thank you very much,

Peter Seifert
--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger

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

Re: [GENERAL] re-using cluster

Fernando Dominguez wrote:
> Hello,
>
> One of my hd failed recently, so I has to reinstall my system, but my data
> where on other hd that did
> not fail.
>
> So I want to use that data , I tried initd -D /storage/pgCluster but I get
> a "directory not empty" message, of course I
> want to use that cluster.
>
> How could I use that data?

First, take a copy of the cluster before doing anything else.

Then: if your new OS has the same version of PostgreSQL installed, and
is on the same architecture (ie both old and new are 32 bit) then you
should be able to simply start the postmaster with the old cluster using
pg_ctl then dump the database.

You don't need the same point release of PostgreSQL - for example, 8.2.7
can read a cluster created by 8.2.1, but 8.3.1 cannot read it.

If you now have a newer version of PostgreSQL you will need to compile
an older version to read your cluster, dump it, and then load it into
the new version of PostgreSQL.

--
Craig Ringer

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

Sunday, June 29, 2008

Re: [PERFORM] Out of memory for Select query.

On Jun 29, 2008, at 10:20 PM, Nimesh Satam wrote:

> All,
>
> While running a Select query we get the below error:
>
> ERROR: out of memory
> DETAIL: Failed on request of size 192.
>
> Postgres Conf details:
> shared_buffers = 256000
> work_mem =150000
> max_stack_depth = 16384
> max_fsm_pages = 400000
> version: 8.1.3
>
> We are using 8gb of Primary memory for the server which is used as a
> dedicated database machine.
>
> The data log shows the below message after getting the Out of memory
> error. Also attached the explain for the query. Can someone let us
> know , if have some worng parameter setup or any solution to the
> problem?
>
> Regards,
> Nimesh.
>


Hi Nimesh,

I'd try decreasing work_mem (try something smaller like 16384 and work
up if you'd like), since you have lots of hashes being built for this
query, you may simply be running into a limit on process size
depending on your platform. Also look at "ulimit -a" as the postgres
user to make sure you aren't running into any administrative limits.

Cheers,

Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com


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

Re: [HACKERS] Join Removal/ Vertical Partitioning

On Fri, 2008-06-27 at 17:50 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote:
> >> It might be possible to treat "ignore the RHS" as a join strategy and
> >> try to apply it while forming join relations, which would be late enough
> >> to have all the needed info available.
>
> > Oh, actually have a join node that is a no-op, with a path cost of zero?
>
> Not even that: just return the best path(s) for the LHS as the paths for
> the joinrel.

Much neater. Cool.

--
Simon Riggs

www.2ndQuadrant.com

PostgreSQL Training, Services and Support


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

[GENERAL] re-using cluster

Hello,

One of my hd failed recently, so I has to reinstall my  system, but my data where on other hd that did
not fail.

So I want to use that data , I tried initd -D /storage/pgCluster but I get  a "directory not empty" message, of course I
want to use that cluster.

How could I use that data?

Thanks.


Re: [HACKERS] VirtualXactLockTableInsert

On Fri, 2008-06-27 at 17:44 +0200, Florian G. Pflug wrote:
> Simon Riggs wrote:
> > When we move from having a virtual xid to having a real xid I don't
> > see any attempt to re-arrange the lock queues. Surely if there are
> > people waiting on the virtual xid, they must be moved across to wait
> > on the actual xid? Otherwise the locking queue will not be respected
> > because we have two things on which people might queue. Anybody
> > explain that?
>
> Locks on real xids serve a different purpose than locks on virtual xids.
> Locks on real xids are used to wait for transaction who touched a
> certain tuple (in which case they certainly must have acquired a real
> xid) to end. Locks on vxids on the other hand are used to wait for the
> ending of transactions which either hold a certain lock or use a
> snapshot with a xmin earlier than some point in time.
>
> indexcmds.c is the only place where VirtualXactLockTableWait() is used -
> the concurrent index creation needs to wait for all transactions to end
> which either might not know about the index (after phase 1 and 2), or
> who might still see tuples not included in the index (before marking the
> index valid).

Thanks,

So there is no attempt to migrate the vxid lock queue onto the xid lock
queue because it doesn't matter now/yet. That seems fragile, but as long
as we know about it we're OK.

--
Simon Riggs

www.2ndQuadrant.com

PostgreSQL Training, Services and Support


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

Re: [PERFORM] Subquery WHERE IN or WHERE EXISTS faster?

On Jun 28, 2008, at 4:07 PM, Ulrich wrote:

> Hi,
> I have added a bit of dummy Data, 100000 processors, 10000 users,
> each user got around 12 processors.
>
> I have tested both queries. First of all, I was surprised that it is
> that fast :) Here are the results:
>
>
> EXPLAIN ANALYZE SELECT speed FROM processors WHERE id IN (SELECT
> processorid FROM users_processors WHERE userid=4040) ORDER BY speed
> ASC LIMIT 10 OFFSET 1;
>
> Limit (cost=113.73..113.75 rows=7 width=5) (actual
> time=0.335..0.340 rows=10 loops=1)
> -> Sort (cost=113.73..113.75 rows=8 width=5) (actual
> time=0.332..0.333 rows=11 loops=1)
> Sort Key: processors.speed
> Sort Method: quicksort Memory: 17kB
> -> Nested Loop (cost=47.22..113.61 rows=8 width=5) (actual
> time=0.171..0.271 rows=13 loops=1)
> -> HashAggregate (cost=47.22..47.30 rows=8 width=4)
> (actual time=0.148..0.154 rows=13 loops=1)
> -> Bitmap Heap Scan on users_processors
> (cost=4.36..47.19 rows=12 width=4) (actual time=0.074..0.117 rows=13
> loops=1)
> Recheck Cond: (userid = 4040)
> -> Bitmap Index Scan on
> users_processors_userid_index (cost=0.00..4.35 rows=12 width=0)
> (actual time=0.056..0.056 rows=13 loops=1)
> Index Cond: (userid = 4040)
> -> Index Scan using processors_pkey on processors
> (cost=0.00..8.28 rows=1 width=9) (actual time=0.006..0.007 rows=1
> loops=13)
> Index Cond: (processors.id =
> users_processors.processorid)
> Total runtime: 0.471 ms
> (13 rows)
>
> ___________
>
> EXPLAIN ANALYZE SELECT speed FROM processors WHERE EXISTS (SELECT 1
> FROM users_processors WHERE userid=4040 AND
> processorid=processors.id) ORDER BY speed ASC LIMIT 10 OFFSET 1;
>
> Limit (cost=831413.86..831413.89 rows=10 width=5) (actual
> time=762.475..762.482 rows=10 loops=1)
> -> Sort (cost=831413.86..831538.86 rows=50000 width=5) (actual
> time=762.471..762.473 rows=11 loops=1)
> Sort Key: processors.speed
> Sort Method: quicksort Memory: 17kB
> -> Seq Scan on processors (cost=0.00..830299.00 rows=50000
> width=5) (actual time=313.591..762.411 rows=13 loops=1)
> Filter: (subplan)
> SubPlan
> -> Index Scan using users_processors_pkey on
> users_processors (cost=0.00..8.29 rows=1 width=0) (actual
> time=0.006..0.006 rows=0 loops=100000)
> Index Cond: ((userid = 4040) AND (processorid =
> $0))
> Total runtime: 762.579 ms
> (10 rows)
>
>
>
>
> As you can see the second query is much slower. First I thought
> "Just a difference of 0.3ms?", but then I realized that it was 762ms
> not 0.762 ;-).
> Both queries return the same result, so I will use #1 and count(*)
> takes just 0.478ms if I use query #1.
>


This is what I've found with tables ranging in the millions of rows.

Using IN is better when you've got lots of rows to check against the
IN set and the IN set may be large and possibly complicated to
retrieve (i.e. lots of joins, or expensive functions).

Postgres will normally build a hash table of the IN set and just
search that hash table. It's especially fast if the entire hash table
that is built can fit into RAM. The cpu/io cost of building the IN
set can be quite large because it needs to fetch every tuple to hash
it, but this can be faster then searching tuple by tuple through
possibly many indexes and tables like EXISTS does. I like to increase
work_mem a lot (512mb and up) if I know I'm going to be doing a lot of
matches against a large IN set of rows because I'd prefer for that
hash table to never to be written to disk.

EXISTS is better when you're doing fewer matches because it will pull
the rows out one at a time from its query possibly using indexes, its
main advantage is that it doesn't pull all of the tuples before it
starts processing matches.

So in summary both are good to know how to use, but choosing which one
to use can really depend on your data set and resources.

Cheers,

Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com

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

Re: [HACKERS] VirtualXactLockTableInsert

On Fri, 2008-06-27 at 18:00 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > In cases where we know we will assign a real xid, can we just skip the
> > assignment of the virtual xid completely?
>
> Even if we could do this I doubt it would be a good idea. It'd destroy
> the invariant that all transactions have a vxid, which at the very least
> would create naming problems.

Ahh, no, I meant go straight to assigning a real xid, to avoid the
wasted effort in inserting a vxid *and* a real xid.

--
Simon Riggs

www.2ndQuadrant.com

PostgreSQL Training, Services and Support


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

Re: [GENERAL] tsearch strategy for incremental search

Pierre,

you, probably, can use custom configuration, which uses pg_3chars
dictionary.

Oleg
On Mon, 30 Jun 2008, Pierre Thibaudeau wrote:

> 2008/6/30 Oleg Bartunov <oleg@sai.msu.su>:
>> tsearch will have prefix search support in 8.4.
>
> Thanks Oleg! That's fantastic news!
>
> In the meantime, carrying on with my earlier idea, here's the little
> function I came up with for extracting the 3-char-lexeme tsvector, in
> case anyone's interested:
>
> CREATE OR REPLACE FUNCTION public.to_three_char_tsvector(str text)
> RETURNS tsvector AS
> $BODY$declare
> somerow record;
> shortened_text text := '';
> BEGIN
> FOR somerow IN SELECT regexp_split_to_table(trim(both '\'' from
> strip(to_tsvector('simple',str))::text),'\'') AS item LOOP
> shortened_text := shortened_text || ' ' ||
> COALESCE(substring(somerow.item for 3), '');
> END LOOP;
> RETURN strip(to_tsvector(shortened_text));
> END;$BODY$
> LANGUAGE 'plpgsql' IMMUTABLE;
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

--
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] tsearch strategy for incremental search

2008/6/30 Oleg Bartunov <oleg@sai.msu.su>:
> tsearch will have prefix search support in 8.4.

Thanks Oleg! That's fantastic news!

In the meantime, carrying on with my earlier idea, here's the little
function I came up with for extracting the 3-char-lexeme tsvector, in
case anyone's interested:

CREATE OR REPLACE FUNCTION public.to_three_char_tsvector(str text)
RETURNS tsvector AS
$BODY$declare
somerow record;
shortened_text text := '';
BEGIN
FOR somerow IN SELECT regexp_split_to_table(trim(both '\'' from
strip(to_tsvector('simple',str))::text),'\'') AS item LOOP
shortened_text := shortened_text || ' ' ||
COALESCE(substring(somerow.item for 3), '');
END LOOP;
RETURN strip(to_tsvector(shortened_text));
END;$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;

--
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] Probably been asked a hundred times before.

I'm using Ubuntu for my development server. The live update updated
postgres either the day of or the day after 8.3.3 came out. Can't
complain about that.

Artacus

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

[ANNOUNCE] == PostgreSQL Weekly News - June 29 2008 ==

== PostgreSQL Weekly News - June 29 2008 ==

July's commit-fest is starting soon.

== PostgreSQL Product News ==

pgsnap 0.4 released.
http://pgsnap.projects.postgresql.org/

PL/Proxy 2.0.6 released.
http://pgfoundry.org/projects/plproxy/

pyreplica 1.0 released.
http://pgfoundry.org/projects/pyreplica/

== PostgreSQL Jobs for June ==

http://archives.postgresql.org/pgsql-jobs/2008-06/threads.php

== PostgreSQL Local ==

pgDay Portland is July 20, just before OSCON.
http://pugs.postgresql.org/node/400

PGCon Brazil 2008 will be on September 26-27 at Unicamp in Campinas.
http://pgcon.postgresql.org.br/index.en.html

PGDay.IT 2008 will be October 17 and 18 in Prato.
http://www.pgday.org/it/

== PostgreSQL in the News ==

Planet PostgreSQL: http://www.planetpostgresql.org/

General Bits, Archives and occasional new articles:
http://www.varlena.com/GeneralBits/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to david@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.

== Applied Patches ==

Tom Lane committed:

- Fix Gen_fmgrtab.sh to not rely on hard-wired knowledge of the column
numbers in pg_proc. Also make it not emit duplicate extern
declarations, and make it a bit more bulletproof in some other small
ways. Likewise fix the equally hard-wired, and utterly
undocumented, knowledge in the MSVC build scripts. For testing
purposes and perhaps other uses in future, pull out that portion of
the MSVC scripts into a standalone perl script equivalent to
Gen_fmgrtab.sh, and make it generate actually identical output,
rather than just more-or-less-the-same output. Motivated by looking
at Pavel's variadic function patch. Whether or not that gets
accepted, we can be sure that pg_proc's column set will change again
in the future; it's time to not have to deal with this gotcha.

- Oops, make the MSVC build put fmgroids.h where it needs to be. Per
buildfarm results.

- Reduce the alignment requirement of type "name" from int to char,
and arrange to suppress zero-padding of "name" entries in indexes.
The alignment change is unlikely to save any space, but it is really
needed anyway to make the world safe for our widespread practice of
passing plain old C strings to functions that are declared as taking
Name. In the previous coding, the C compiler was entitled to assume
that a Name pointer was word-aligned; but we were failing to
guarantee that. I think the reason we'd not seen failures is that
usually the only thing that gets done with such a pointer is
strcmp(), which is hard to optimize in a way that exploits
word-alignment. Still, some enterprising compiler guy will probably
think of a way eventually, or we might change our code in a way that
exposes more-obvious optimization opportunities. The padding change
is accomplished in one-liner fashion by declaring the "name" index
opclasses to use storage type "cstring" in pg_opclass.h. Normally
btree and hash don't allow a nondefault storage type, because they
don't have any provisions for converting the input datum to another
type. However, because name and cstring are effectively the same
thing except for padding, no conversion is needed --- we only need
index_form_tuple() to treat the datum as being cstring not name, and
this is sufficient. This seems to make for about a one-third
reduction in the typical sizes of system catalog indexes that
involve "name" columns, of which we have many. These two changes
are only weakly related, but the alignment change makes me feel
safer that the padding change won't introduce problems, so I'm
committing them together.

- Modify the recently-added probe for -Wl,--as-needed some more,
because RHEL-4 vintage Linux is even more broken than we realized: a
link to libreadline will succeed, and fail only at runtime. It
seems that an AC_TRY_RUN test is the only reliable way to check
whether this is really safe. Per report from Tatsuo Ishii.

- In pgsql/doc/src/sgml/plpgsql.sgml, clarify plpgsql documentation by
not treating IF THEN ELSE IF ... as a truly distinct version of IF.
Per suggestion from Marko Kreen.

- Improve planner's estimation of the size of an append relation:
rather than taking the maximum of any child rel's width, we should
weight the widths proportionally to the number of rows expected from
each child. In hindsight this is obviously correct because row
width is really a proxy for the total physical size of the relation.
Per discussion with Scott Carey (bug #4264).

- Consider a clause to be outerjoin_delayed if it references the
nullable side of any lower outer join, even if it also references
the non-nullable side and so could not get pushed below the outer
join anyway. We need this in case the clause is an OR clause: if it
doesn't get marked outerjoin_delayed, create_or_index_quals() could
pull an indexable restriction for the nullable side out of it,
leading to wrong results as demonstrated by today's bug report from
toruvinn. (See added regression test case for an example.) In
principle this has been wrong for quite a while. In practice I
don't think any branch before 8.3 can really show the failure,
because create_or_index_quals() will only pull out indexable
conditions, and before 8.3 those were always strict. So though we
might have improperly generated null-extended rows in the outer
join, they'd get discarded from the result anyway. The gating
factor that makes the failure visible is that 8.3 considers "col IS
NULL" to be indexable. Hence I'm not going to risk back-patching
further than 8.3.

- If pnstrdup is going to be promoted to a generally available
function, it ought to conform to the rest of palloc.h in using Size
for sizes.

- In pgsql/src/backend/access/gin/ginbulk.c, remove unnecessary
coziness of GIN code with datum copying. Now that space is tracked
via GetMemoryChunkSpace, there's really no advantage to duplicating
datumCopy's innards here. This is one bit of my toast indirection
patch that should go in anyway.

Bruce Momjian committed:

- Merge duplicate upper/lower/initcap() routines in oracle_compat.c
and formatting.c to use common code; remove duplicate functions and
support routines that are no longer needed.

- Add TODO: "Consider whether duplicate keys should be sorted by
block/offset."

- Add libpq comment about how to determine the format used for passing
binary values. Add comments to libpq C function for parameter
passing.

- Add to TODO: "Implement a module capability for loading
/contrib-style extensions."

- Add to TODO: "Allow custom variables to appear in pg_settings()."

- In pgsql/doc/src/sgml/backup.sgml, backup wording improvement.
Joshua D. Drake.

- Add URL for Merge TODO.

- Add to TODO: "Allow COPY to report errors sooner."

- Mark TODO as done: "Prevent pg_dump/pg_restore from being affected
by statement_timeout."

- Remove use of postmaster.opts.default by pg_ctl.

- Use SYSTEMQUOTE as concatentation to strings, rather than %s printf
patterns, for clarity.

- Fix 'pg_ctl restart' to preserve command-line arguments.

- Add to TODO: "Improve LDAP authentication configuration options."

- Update FAQ URLs as suggested by Curtis Gallant.

- Simplify 'pg_ctl restart' detection of first argument in
postmaster.opts.

- More FAQ URL updates from Curtis Gallant.

- Add TODO about security: "Improve server security options."

- In pgsql/src/bin/pg_ctl/pg_ctl.c, fix pg_ctl bug where detection of
binary location from postmaster.opts wasn't working.

- Add MERGE TODO URL.

- In pgsql/doc/src/sgml/syntax.sgml, use SGML table to show backslash
string escapes, rather than have them appear in a paragraph. Andy
Anderson.

- Fix 'pg_ctl reload' to properly preserve postmaster commend-line
arguments on restart. Patch to releases 8.0 - 8.3.X.

- Add to TODO: "Fix system views like pg_stat_all_tables to use
set-returning functions, rather than views of per-column functions."

- In pgsql/doc/src/sgml/libpq.sgml, mention actual function names in
documentation of how to pass binary values to libpq.

Michael Meskes committed:

- Synced parser for ecpg.

Teodor Sigaev committed:

- In pgsql/src/backend/utils/adt/formatting.c, fix bug "select
lower('asd') = 'asd'" returns false with multibyte encoding and
non-C locale. Fix is just to use correct source's length for
char2wchar call.

Magnus Hagander committed:

- In pgsql/src/interfaces/libpq/win32.mak, fix standalone libpq build
on win32. Hiroshi Saito.

== Rejected Patches (for now) ==

Raneyt AT cecs.pdx.edu's patch implementing XML output for EXPLAIN.
No documentation.

== Pending Patches ==

Heikki Linnakangas sent in another revision of his "relation forks"
patch and another that rewrites the FSM.

David Wheeler sent in two revisions of a patch to add a
case-insensitive text types to PostgreSQL.

Thomas Lee sent in two revisions of a patch to add a new GUC called
track_activity_query_size per discussion
http://archives.postgresql.org/pgsql-hackers/2008-06/msg00814.php

ITAGI Takahiro sent in two revisions of a patch to fix the
archive/restore docs for Windows.

ITAGI Takahiro sent in a patch to create an executore hook for logging
SQL statements.


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

pgsql-announce-unsubscribe@postgresql.org

[HACKERS] A new take on the foot-gun meme

[ after recovering from choking... ]

Tom "spot" Callaway presents a vivid new image in this line:

> What you're doing is analogous to using a loaded shotgun as a golf club,
> and what you're suggesting is that we take the safety off, because it
> interferes with your golf game.

https://www.redhat.com/archives/fedora-devel-list/2008-June/msg01501.html

So is that a "golf club gun"?

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: [PERFORM] Subquery WHERE IN or WHERE EXISTS faster?

"Jaime Casanova" <jcasanov@systemguards.com.ec> writes:
> i've made some queries run faster using EXISTS instead of large IN
> clauses... actually, it was NOT EXISTS replacing a NOT IN

That's just about entirely unrelated ...

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: [PERFORM] Subquery WHERE IN or WHERE EXISTS faster?

On Sat, Jun 28, 2008 at 10:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ulrich <ulrich.mierendorff@gmx.net> writes:
>> People say that [EXISTS is faster]
>
> People who say that are not reliable authorities, at least as far as
> Postgres is concerned. But it is always a bad idea to extrapolate
> results on toy tables to large tables --- quite aside from measurement
> noise and caching issues, the planner might pick a different plan when
> faced with large tables. Load up a realistic amount of data and then
> see what you get.
>

i've made some queries run faster using EXISTS instead of large IN
clauses... actually, it was NOT EXISTS replacing a NOT IN

while i'm not telling EXISTS is better i actually know in some cases is better

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157

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

[PERFORM] Out of memory for Select query.

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=2552680.87..2553501.76 rows=54726 width=135)
-> Hash Join (cost=1008.35..2550628.65 rows=54726 width=135)
Hash Cond: ("outer".s_key = "inner"."key")
-> Hash Join (cost=803.07..2548507.96 rows=54726 width=108)
Hash Cond: ("outer".cp_key = "inner"."key")
-> Hash Join (cost=687.96..2547161.51 rows=54726 width=82)
Hash Cond: ("outer".ch_key = "inner"."key")
-> Hash Join (cost=601.21..2545843.43 rows=54726 width=60)
Hash Cond: ("outer".cr_key = "inner"."key")
-> Hash Join (cost=265.72..2544002.98 rows=54726 width=62)
Hash Cond: ("outer".go_key = "inner"."key")
-> Hash Join (cost=23.12..2541844.97 rows=54726 width=62)
Hash Cond: ("outer".adv_key = "inner"."key")
-> Nested Loop (cost=0.00..2541000.08 rows=54901 width=48)
-> Nested Loop (cost=0.00..4.14 rows=2 width=16)
-> Seq Scan on r_n (cost=0.00..1.06 rows=1 width=4)
Filter: (id = 607)
-> Index Scan using r_d_sqldt_idx on r_da (cost=0.00..3.05 rows=2 width=12)
Index Cond: ((sqldate >= '2008-01-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2008-01-31 00:00:00'::timestamp without time zone))
-> Index Scan using r_m_nw_date_idx on r_m (cost=0.00..1259386.12 rows=740790 width=48)
Index Cond: ((r_m.nw_key = "outer"."key") AND (r_m.date_key = "outer"."key"))
-> Hash (cost=20.77..20.77 rows=939 width=22)
-> Seq Scan on r_adv (cost=0.00..20.77 rows=939 width=22)
Filter: ((name)::text <> 'SYSTEM'::text)
-> Hash (cost=218.28..218.28 rows=9728 width=8)
-> Seq Scan on rg (cost=0.00..218.28 rows=9728 width=8)
-> Hash (cost=302.39..302.39 rows=13239 width=6)
-> Seq Scan on rc (cost=0.00..302.39 rows=13239 width=6)
-> Hash (cost=77.20..77.20 rows=3820 width=30)
-> Seq Scan on r_c (cost=0.00..77.20 rows=3820 width=30)
-> Hash (cost=104.89..104.89 rows=4089 width=34)
-> Seq Scan on r_cm (cost=0.00..104.89 rows=4089 width=34)
-> Hash (cost=181.42..181.42 rows=9542 width=35)
-> Seq Scan on r_s (cost=0.00..181.42 rows=9542 width=35)
(34 rows)
All,
 
While running a Select query we get the below error:
 
ERROR:  out of memory
DETAIL:  Failed on request of size 192.
 
Postgres Conf details:
shared_buffers = 256000
work_mem =150000
max_stack_depth = 16384
max_fsm_pages = 400000
version: 8.1.3
 
We are using 8gb of Primary memory for the server which is used as a dedicated database machine.
 
The data log shows the below message after getting the Out of memory error. Also attached the explain for the query. Can someone let us know , if have some worng parameter setup or any solution to the problem?
 
Regards,
Nimesh.
 

TopMemoryContext: 57344 total in 6 blocks; 9504 free (12 chunks); 47840 used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks); 6328 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
MessageContext: 1040384 total in 7 blocks; 263096 free (4 chunks); 777288 used
JoinRelHashTable: 8192 total in 1 blocks; 3888 free (0 chunks); 4304 used
smgr relation table: 8192 total in 1 blocks; 1840 free (0 chunks); 6352 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 856 free (0 chunks); 168 used
ExecutorState: 122880 total in 4 blocks; 51840 free (6 chunks); 71040 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 2089044 total in 8 blocks; 573232 free (12 chunks); 1515812 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 2080768 total in 7 blocks; 749448 free (11 chunks); 1331320 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 245760 total in 4 blocks; 109112 free (4 chunks); 136648 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 1032192 total in 6 blocks; 504104 free (8 chunks); 528088 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 1032192 total in 6 blocks; 474456 free (8 chunks); 557736 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 2080768 total in 7 blocks; 783856 free (11 chunks); 1296912 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
.
.
.

AggContext: 941613056 total in 129 blocks; 13984 free (154 chunks); 941599072 used
TupleHashTable: 113303576 total in 24 blocks; 1347032 free (74 chunks); 111956544 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
CacheMemoryContext: 516096 total in 6 blocks; 12080 free (0 chunks); 504016 used
rg_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
rg_idx: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
rg_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
rc_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_c_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_c_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_ch_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_ch_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_ch_cd: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_cm_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_c_m_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_s_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_p_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_p_cd_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_a_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_a_v_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_d_sqldt_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_da_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_nw_key_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_n_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
r_m_network_date_idx: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_class_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_cast_source_target_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
MdSmgr: 8192 total in 1 blocks; 5584 free (0 chunks); 2608 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
Timezones: 47592 total in 2 blocks; 5968 free (0 chunks); 41624 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
2008-06-29 20:48:25 PDT [13980]: [5-1] ERROR:  out of memory
2008-06-29 20:48:25 PDT [13980]: [6-1] DETAIL:  Failed on request of size 192.