Wednesday, June 25, 2008

Re: [PERFORM] Hardware vs Software Raid

"Also sprach Merlin Moncure:"
> write back: raid controller can lie to host o/s. when o/s asks

This is not what the linux software raid controller does, then. It
does not queue requests internally at all, nor ack requests that have
not already been acked by the components (modulo the fact that one can
deliberately choose to have a slow component not be sync by allowing
"write-behind" on it, in which case the "controller" will ack the
incoming request after one of the compionents has been serviced,
without waiting for both).

> integrity and performance. 'write back' caching provides insane burst
> IOPS (because you are writing to controller cache) and somewhat
> improved sustained IOPS because the controller is reorganizing writes
> on the fly in (hopefully) optimal fashion.

This is what is provided by Linux file system and (ordinary) block
device driver subsystem. It is deliberately eschewed by the soft raid
driver, because any caching will already have been done above and below
the driver, either in the FS or in the components.

> > However the lack of extra buffering is really deliberate (double
> > buffering is a horrible thing in many ways, not least because of the
>
> <snip>
> completely unconvincing.

But true. Therefore the problem in attaining conviction must be at your
end. Double buffering just doubles the resources dedicated to a single
request, without doing anything for it! It doubles the frequency with
which one runs out of resources, it doubles the frequency of the burst
limit being reached. It's deadly (deadlockly :) in the situation where
the receiving component device also needs resources in order to service
the request, such as when the transport is network tcp (and I have my
suspicions about scsi too).

> the overhead of various cache layers is
> completely minute compared to a full fault to disk that requires a
> seek which is several orders of magnitude slower.

That's aboslutely true when by "overhead" you mean "computation cycles"
and absolutely false when by overhead you mean "memory resources", as I
do. Double buffering is a killer.

> The linux software raid algorithms are highly optimized, and run on a

I can confidently tell you that that's balderdash both as a Linux author
and as a software RAID linux author (check the attributions in the
kernel source, or look up something like "Raiding the Noosphere" on
google).

> presumably (much faster) cpu than what the controller supports.
> However, there is still some extra oomph you can get out of letting
> the raid controller do what the software raid can't...namely delay
> sync for a time.

There are several design problems left in software raid in the linux kernel.
One of them is the need for extra memory to dispatch requests with and
as (i.e. buffer heads and buffers, both). bhs should be OK since the
small cache per device won't be exceeded while the raid driver itself
serialises requests, which is essentially the case (it does not do any
buffering, queuing, whatever .. and tries hard to avoid doing so). The
need for extra buffers for the data is a problem. On different
platforms different aspects of that problem are important (would you
believe that on ARM mere copying takes so much cpu time that one wants
to avoid it at all costs, whereas on intel it's a forgettable trivium).

I also wouldn't aboslutely swear that request ordering is maintained
under ordinary circumstances.

But of course we try.


Peter

--
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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses

Thanks for all yours suggestions, use cases and opinion about this
thread, I saw that there are more things to consider than I was
thinking and this make me consider that it is a hard work to do for
now.

--
[]s
Dickson S. Guedes
-------------------------------------
Projeto Colmeia - Curitiba - PR
+55 (41) 3254-7130 ramal: 27
http://makeall.wordpress.com/
http://pgcon.postgresql.org.br/
http://planeta.postgresql.org.br/

--
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] CVS Head psql bug?

> > The Linux distributions is "Vine Linux", which is a variant of Red Hat
> > Linux (I think).
>
> If it's rpm-based, could we see the RPM package version numbers for
> binutils, readline, and ncurses?

Sure.

binutils-2.15.92.0.2-5vl2
readline-5.1-0vl1
readline-devel-5.1-0vl1
ncurses-devel-5.4-0vl3
ncurses-5.4-0vl3
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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

Re: [pgadmin-support] openSUSE 11

On Wednesday 25 June 2008 03:26:40 pm Hiroshi Saito wrote:
> Yeah,
>
> We plan to start the preparation.
> However, it had a problem in Japanese a little.
> Please give time to us for a while.
>
> Regards,
> Hiroshi Saito
>
> >Has anyone been able to install pgadmin3 on openSUSE 11.0? I ask because
> > I am thinking about moving to the version 11.
> >--
> >John Fabiani
> >
> >--
> >Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
> >To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgadmin-support

Thanks - I'm sure you will announce when it's ready.

--
John Fabiani

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

Re: [HACKERS] Creating a VIEW with a POINT column

Tom Lane wrote:
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= <j.urbanski@students.mimuw.edu.pl> writes:   
All three try to sort the table first, and as there's no comparision  operator for the POINT datatype, they fail. Which seems to be wrong - if  there is no comparision operator, you still can do DISTINCT, only less  efficiently.     
 Type point has no btree opclass, no hash opclass, and not even an operator named "=" (it looks like the functionality is named ~= for some odd reason).  I'd be interested to hear either a proposal of a principled way to define DISTINCT, or a way to implement it that was better than comparing every element to every other element...   

I agree - a byte-wise comparison of the internal encoding might be inadequate (compare "0.0e+1" to "0.0e+2" is "not equal" for instance?). If the poster is referring to a translation to string before comparing, this could face similar issue. What if it's not a "point" but a "fraction" - does "2/4" = "1/2"? With an operator implementing "=", making any assumption may be making the wrong assumption, and I really like that PostgreSQL will refuse to do things rather than silently continue to do what may be the wrong thing (MySQL silent truncation when assigning into a varchar(8) for example).

The problem here seems to that "point" should have an equality operator?

Cheers,
mark

--  Mark Mielke <mark@mielke.cc> 

Re: [HACKERS] CVS Head psql bug?

Tatsuo Ishii <ishii@sraoss.co.jp> writes:
>> What platform is this exactly?

> The Linux distributions is "Vine Linux", which is a variant of Red Hat
> Linux (I think).

If it's rpm-based, could we see the RPM package version numbers for
binutils, readline, and ncurses?

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: [GENERAL] a question about data corruption

"Jacek Rembisz" <jacek.rembisz@gmail.com> writes:
> Postgresql started to log "could not access status of transaction"
> messages. Since the transaction IDs were far away from what
> server was using I took a look at data files i pgsql/base/ and I found
> a total garbage there.

> In five tables (of about 100) I have found a one to four blocks of random data.
> In two places it was data from other table instead of random data.
> All these blocks have sizes which are multiplications of 512 (for
> example 1, 9, 26) and
> starts at offsets which are also a multiplication of 512.

Substituting sector-size blocks of one file for another could easily
be a filesystem (kernel) bug ...

> The system is linux 2.4.31 filesystem XFS on RAID5

... and XFS on such an old kernel version doesn't seem like a very
good bet for stability.

> My question is: Is there any known bug in postgresql 8.0.3 that
> could lead to such a data corruption or is it rather a hardware problem?

No, nothing like that has ever been reported in any released PG
version. If the substituted blocks were from non-Postgres files
then I think you could write off the idea of a PG bug entirely.
It could still be a software issue though.

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] CVS Head psql bug?

> Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> > It seems configure only checks whether linking was successful. I think
> > it should check whether ./conftest was successful.
>
> Wouldn't work when cross-compiling.

But configure already does this, doesn't it?

configure:24466: checking for working memcmp
configure:24519: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -D_GNU_SOURCE conftest.c -lz -lreadline -lncurses -lcrypt -ldl -lm >&5
configure:24522: $? = 0
configure:24528: ./conftest
configure:24531: $? = 0
configure:24547: result: yes

> What platform is this exactly?

The Linux distributions is "Vine Linux", which is a variant of Red Hat
Linux (I think).

$ uname -a
Linux localhost.localdomain 2.6.16-0vl76.33 #1 SMP Fri Feb 15 11:06:36 JST 2008 i686 i686 i386 GNU/Linux
--
Tatsuo Ishii
SRA OSS, Inc. Japan

--
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] CVS Head psql bug?

Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> It seems configure only checks whether linking was successful. I think
> it should check whether ./conftest was successful.

Wouldn't work when cross-compiling. What platform is this exactly?

regards, tom lane

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

Re: [HACKERS] Creating a VIEW with a POINT column

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= <j.urbanski@students.mimuw.edu.pl> writes:
> All three try to sort the table first, and as there's no comparision
> operator for the POINT datatype, they fail. Which seems to be wrong - if
> there is no comparision operator, you still can do DISTINCT, only less
> efficiently.

Type point has no btree opclass, no hash opclass, and not even an
operator named "=" (it looks like the functionality is named ~=
for some odd reason). I'd be interested to hear either a proposal of
a principled way to define DISTINCT, or a way to implement it that
was better than comparing every element to every other element...

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: [NOVICE] yum repos problem

Hi,

On Thu, 2008-06-19 at 00:00 -0600, Ernesto Eduardo Medina Núñez wrote:
> But when I made a yum update, then:
>
> Package postgis-1.3.3-1.rhel5.i686.rpm is not signed

I found that some packages were not signed :( They are signed now. If
yum complains about checksum, then run:

yum makecache
yum install postgis

Please let me know if this does not work for you.

Regards,
--
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

Re: [HACKERS] CVS Head psql bug?

> Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> > So the difference seems:
> > --as-needed -Wl,
> > is added to CVS Head.
>
> There is code in configure that's supposed to check whether or not that
> breaks readline. Would you look at the portion of config.log where it
> tests that, and see why it failed to notice a problem?

It seems configure only checks whether linking was successful. I think
it should check whether ./conftest was successful.

configure:25642: checking if gcc supports -Wl,--as-needed
configure:25674: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -D_GNU_SOURCE -Wl,--as-needed conftest.c -lz -lreadline -lncurses -lcrypt -ldl -lm >&5
configure:25680: $? = 0
configure:25686: result: yes
configure:25765: using CFLAGS=-O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing
configure:25767: using CPPFLAGS= -D_GNU_SOURCE
configure:25769: using LDFLAGS= -Wl,--as-needed
configure:25911: creating ./config.status
--
Tatsuo Ishii
SRA OSS, Inc. Japan

--
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] Obtener el más cercano

Hola, parece que los mails que he enviado no han llegado, no lo he
visto en los archivos, así que pregunto de nuevo.

Tengo una tabla como esta:

CREATE TABLE t (x double precision primary key, ...);

y quiero obtener el elemento cuyo valor sea más cercano a un valor dado
x0. Lo que se me ocurre por el momento es:

SELECT * FROM t ORDER BY abs(x - x0) LIMIT 1;

Pero me tinca que podría estar calculando la función abs para todos los
registros de la tabla, lo que no aprovecharía el índice B-Tree en la
columna x.

¿Se les ocurre una idea mejor?

--
Daniel Hernández
http://www.scio.cl
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [HACKERS] CVS Head psql bug?

Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> So the difference seems:
> --as-needed -Wl,
> is added to CVS Head.

There is code in configure that's supposed to check whether or not that
breaks readline. Would you look at the portion of config.log where it
tests that, and see why it failed to notice a problem?

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

[GENERAL] IF ROW( NEW ) <> ROW( OLD )

Is it possible to "cast" a table type to a ROW so that Row-Wise
comparison can be achieved?

--
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] Creating a VIEW with a POINT column

Nick wrote:
> Nope, im not ordering by the POINT column. Heres an example...
>
> CREATE VIEW myview AS
> SELECT table1.title, table1.sorter, table1.xy FROM table1 UNION
> SELECT table2.title, table2.sorter, table2.xy FROM table2;

Hmm, the error seems to be coming from UNION. It's because Postgres
implements UNION by sorting both result sets merging them together.
Sample queries that also fail:

SELECT * FROM myview;
SELECT DISTINCT * FROM table1;
SELECT title, sorter, xy FROM table1 GROUP BY title, sorter, xy;

All three try to sort the table first, and as there's no comparision
operator for the POINT datatype, they fail. Which seems to be wrong - if
there is no comparision operator, you still can do DISTINCT, only less
efficiently.

The quick solution I'd propose is replacing UNION with UNION ALL. This
will not throw away duplicate entries present in both table1 and table2,
but if you can live with that, it will work. Remeber though, it changes
the semantic of that view, so think carefuly before doing that.

I guess some senior hacker should confirm, but I believe this is a bug.

Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
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] CVS Head psql bug?

> Tatsuo Ishii <ishii(at)postgresql(dot)org> writes:
> > I'm getting following error while envoking psql:
> > $ psql
> > psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC
>
> I believe this is the typical symptom of failing to link to the correct
> variant of curses/termcap needed by readline. We haven't changed the
> code around that in awhile --- is this a different platform than you've
> used before?
>
> regards, tom lane

Sorry for not follow the thread and posting from different account
than before. I'm currently having trouble with ishii@postgresql.org
account. Marc is kindly working for me and will be fixed soon but...

No the plaform is not changed. I compare the link command for psql 8.3
and current.

8.3:
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing command.o common.o help.o input.o stringutils.o mainloop.o copy.o startup.o prompt.o variables.o large_obj.o print.o describe.o psqlscan.o tab-complete.o mbprint.o dumputils.o ../../../src/backend/parser/keywords.o -L../../../src/port -lpgport -L../../../src/interfaces/libpq -lpq -L../../../src/port -Wl,-rpath,'/usr/local/pgsql/lib' -lpgport -lz -lreadline -lncurses -lcrypt -ldl -lm -o psql

CVS Head:
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing command.o common.o help.o input.o stringutils.o mainloop.o copy.o startup.o prompt.o variables.o large_obj.o print.o describe.o psqlscan.o tab-complete.o mbprint.o dumputils.o ../../../src/backend/parser/keywords.o -L../../../src/port -lpgport -L../../../src/interfaces/libpq -lpq -L../../../src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/src/pgsql/current/lib' -lpgport -lz -lreadline -lncurses -lcrypt -ldl -lm -o psql

So the difference seems:

--as-needed -Wl,

is added to CVS Head. Actually if I remove the option and link psql,
then everything is fine. Any idea?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

--
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] Hardware suggestions for high performance 8.3

25 jun 2008 kl. 17.45 skrev Greg Smith:

> On Wed, 25 Jun 2008, Henrik wrote:
>
>> Would you turn off fsync if you had a controller with BBU? =)
>
> Turning off fsync has some potential to introduce problems even in
> that environment, so better not to do that. The issue is that you
> might have, say, 1GB of OS-level cache but 256MB of BBU cache, and
> if you turn fsync off it won't force the OS cache out to the
> controller when it's supposed to and that can cause corruption.
>
> Also, if you've got a controller with BBU, the overhead of fsync for
> regular writes is low enough that you don't really need to turn it
> off. If writes are cached the fsync is almost free.
Thanks for a thoroughly answer. I guess I wont be turning of fsync. :)

Thanks Greg!

>
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com


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


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

Re: [pgadmin-support] openSUSE 11

Yeah,

We plan to start the preparation.
However, it had a problem in Japanese a little.
Please give time to us for a while.

Regards,
Hiroshi Saito

>Has anyone been able to install pgadmin3 on openSUSE 11.0? I ask because I am
>thinking about moving to the version 11.
>--
>John Fabiani
>
>--
>Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgadmin-support

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

[NOVICE] how to replicate a database from one machine to the other?

A total novice here!

Machine A: Kubuntu 8.04 has the required database under user userA
Machine B: RHEL 5 is the destination machine with userB

I want to transfer the entire database under userA from machine A to
user B under machine B. Is that possible?

--
Regards
PK
--------------------------------------
http://counter.li.org

#402424

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

Re: [pgadmin-support] Bigserial Sequence Name

Ryan Daniels a écrit :
>
> Is is possible to control the sequence name when creating a BIGSERIAL
> column?
>

AFAIK, no, it's not.

Regards.


--
Guillaume.

http://www.postgresqlfr.org

http://dalibo.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: [pgadmin-support] Wish List Request: Tabbed SQL Query Window

Ryan Daniels a écrit :
>
> I would like to request that the SQL query window have tabs so that you
> can have multiple queries/files open in the same window.
>

I think it could be a great idea. Don't know if it's really feasible but
I like the idea.


--
Guillaume.

http://www.postgresqlfr.org

http://dalibo.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: [HACKERS] Creating a VIEW with a POINT column

Nope, im not ordering by the POINT column. Heres an example...

CREATE TABLE table1 (
title character varying,
sorter integer,
xy point
);

CREATE TABLE table2 (
title character varying,
sorter integer,
xy point
);

INSERT INTO table1 VALUES ('one', 1, '(1,1)');
INSERT INTO table1 VALUES ('two', 2, '(2,2)');
INSERT INTO table1 VALUES ('three', 3, '(3,3)');
INSERT INTO table2 VALUES ('four', 4, '(4,4)');
INSERT INTO table2 VALUES ('five', 5, '(5,5)');
INSERT INTO table2 VALUES ('six', 6, '(6,6)');

CREATE VIEW myview AS
SELECT table1.title, table1.sorter, table1.xy FROM table1 UNION
SELECT table2.title, table2.sorter, table2.xy FROM table2;

SELECT title FROM myview ORDER BY sorter;

ERROR: could not identify an ordering operator for type point
HINT: Use an explicit ordering operator or modify the query.

In statement:
SELECT title FROM myview ORDER BY sorter

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

[pgadmin-support] openSUSE 11

Has anyone been able to install pgadmin3 on openSUSE 11.0? I ask because I am
thinking about moving to the version 11.
--
John Fabiani

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

Re: [JDBC] Data corruption while taking backup of database

Pushker Chaubey <pchaubey@vertex.co.in> writes:
> We took back up of a database using the following command
> pg_dump -a

> In the back up file that was generated we found some data that was
> violating foreign key constraints. And there fore we were not able to
> restore database back with this back up file.

Was the data actually erroneous, or just loaded in the wrong order
(ie, referencing table before referenced one)? pg_dump -a doesn't
guarantee to use a safe reload order.

regards, tom lane

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

Re: [HACKERS] CVS Head psql bug?

Tatsuo Ishii <ishii@postgresql.org> writes:
> I'm getting following error while envoking psql:
> $ psql
> psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC

I believe this is the typical symptom of failing to link to the correct
variant of curses/termcap needed by readline. We haven't changed the
code around that in awhile --- is this a different platform than you've
used before?

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: [BUGS] BUG #4264: Optimizer fails to use hash_aggregate when appropriate.

"Scott Carey" <scott@richrelevance.com> writes:
> The query optimizer fails to use a hash aggregate most of the time. This is
> an inconsistent behavior.

Hash aggregation is not used when a DISTINCT aggregate is specified.
DISTINCT forces a sort, so there wouldn't be any advantage.

> Of course DB hints would solve this.

No, they wouldn't.

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

[pgsql-es-ayuda] Cada cuanto reindexar la base de datos

Hola amigos de la lista,
Estoy usando una bd para unos reportes, y la tabla principal tiene
unos cuantos millones y ya está particionada.
En server tiene andando el autovacuum y tengo croneado un vaccumdb -a
cada hora...
Pero mirando de casualidad vi que los indices están muy grandes
"bloated" segun la doc... la duda es:
¿es correcto que reindexe todos los dias, por ejemplo a la noche?. Me
parece un método algo bruto ya que
reindexa muchas de tablas que no hay necesidad...¿Hay alguna forma de
reindexar solo las tablas que lo necesitan ? (que no sea
listandolas a mano :) ? ¿Por dónde encaro un análisis del tema ?
Gracias!
Jorge
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

[PERFORM] Query Planner not choosing hash_aggregate appropriately.

The query optimizer fails to use a hash aggregate most of the time.  This is an inconsistent behavior -- the queries below were happily using hash_aggregate on a previous pg_restore from the data.

On one particular class of tables this is especially painful.  The example table has 25 million rows, and when aggregating on a column that the optimizer expects only a few unique values, it chooses a full sort of those 25 million rows before a group aggregate, rather than using a hash aggregate that would be 2 to 4 orders of magnitude faster and use less memory.

The simple statement of this bug is the following EXPLAIN output and corresponding output from the statistics tables.  The actual query used has a more complicated GROUP BY and aggregation (and joins, etc), but if it can't get the most simple version of a sub query correct, of course the composite will be worse.

The condition will occur for any column used to group by regardless of the estimated # of unique items on that column.  Even one that has only two unique values in a 25 million row table.

rr=# explain SELECT  count(distinct v_guid) as view_count, p_type FROM p_log.creative_display_logs
_012_2008_06_15 GROUP BY p_type;                                                                                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=5201495.80..5395385.38 rows=7 width=47)
   ->  Sort  (cost=5201495.80..5266125.63 rows=25851932 width=47)
         Sort Key: p_type
         ->  Seq Scan on creative_display_logs_012_2008_06_15  (cost=0.00..1223383.32 rows=25851932 width=47)

rr=# select attname, null_frac, avg_width,n_distinct
,correlation from pg_stats where tablename='creative_display_logs_012_2008_06_15' and attname in ('g_id', 'p_type', 'strat', 'datetime', 'ext_s_id', 't_id');
    attname     | null_frac | avg_width | n_distinct | correlation
----------------+-----------+-----------+------------+--------------
 g_id       |         0 |         8 |         14 |     0.221548
 p_type |         0 |         4 |          7 |     0.350718
 datetime       |         0 |         8 |      12584 |     0.977156
 ext_s_id |     0.001 |        38 |      11444 | -0.000842848
 strat       |         0 |        13 |         11 |     0.147418
 t_id       |         0 |         8 |          2 |     0.998711

(5 rows)

I have dumped, dropped, and restored this table twice recently.  Both times followed by a full vacuum analyze.  And in both cases the query optimizer behaves differently.   In one case the poor plan only occures when using the partition table inheritance facade rather than the direct-to-table version above.  In the other case (the current condition), all variants on the query are bad.
This definitely occurs in general and its reproducibility is affected by partitioning  but not dependent on it as far as I can tell.

The database is tuned with the default optimizer settings for 8.3.3 plus constraint exclusion for the partition tables enabled.  Yes, hash_agg is on (actually, commented out so the default of on is active, verified in pg_settings)

The configuration has ample RAM and all the memory tuning parameters are generous (shared_mem 7g, temp space 200m, sort/agg space 500m -- I've tried various settings here with no effect on the plan, just the execution of it w.r.t. disk based sort or mem based sort).


The table definition is the following, if that helps:
       Column       |            Type             | Modifiers
--------------------+-----------------------------+-----------
 v_guid             | character varying(255)      |
 site_id            | bigint                      |
 c_id               | bigint                      |
 item_id            | bigint                      |
 creative_id        | bigint                      |
 camp_id            | bigint                      |
 p_type             | integer                     |
 datetime           | timestamp without time zone |
 date               | date                        |
 ext_u_id           | character varying(50)       |
 ext_s_id           | character varying(50)       |
 u_guid             | character varying(50)       |
 strat              | character varying(50)       |
 sub_p_type         | character varying(32)       |
 exp_id             | bigint                      |
 t_id               | bigint                      |
 htmlpi_id          | bigint                      |
 p_score            | double precision            |


Of course DB hints would solve this.  So would some sort of tuning parameter that lets you dial up or down the tendency to do a hash aggregate rather than a full sort followed by a group aggregate.  This is broken rather severely, especially in combination with partitions (where it is about 3x as likely to fail to use a hash_aggregate where appropriate in limited experiments so far -- there are a few thousand partition tables).

All I want is it to stop being brain-dead and deciding to sort large tables to produce aggregates.  In fact, given the rarity in which a sort is preferred over a hash_agg with large tables, and the tendancy for aggregates to reduce the count by a factor of 10 or more -- i'd turn off the group aggregate if possible!

Thanks for any help!

-Scott

Re: [PERFORM] Hardware vs Software RAID

On Wed, 25 Jun 2008, Merlin Moncure wrote:

> So, based on this and other experiences, i'm starting to be more partial
> to linux distributions with faster moving kernels, mainly because i
> trust the kernel drivers more than the vendor provided drivers.

Depends on how fast. I find it takes a minimum of 3-6 months before any
new kernel release stabilizes (somewhere around 2.6.X-5 to -10), and some
distributions push them out way before that. Also, after major changes,
it can be a year or more before a new kernel is not a regression either in
reliability, performance, or worst-case behavior.

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

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

Re: [PERFORM] Hardware vs Software RAID

On Wed, 25 Jun 2008, Peter T. Breuer wrote:

> I refrained from saying in my reply that I would set up a firewire-based
> link to ram in a spare old portable (which comes with a battery) if I
> wanted to do this cheaply.

Maybe, but this is kind of a weird setup. Not many people are going to
run a production database that way and us wandering into the details too
much risks confusing everybody else.

> The log is sync. Therefore it doesn't matter what the guarantees are, or
> at least I assume you are worrying about acks coming back before the
> write has been sent, etc. Only an actual net write will be acked by the
> firewire transport as far as I know.

That's exactly the issue; it's critical for database use that a disk not
lie to you about writes being done if they're actually sitting in a cache
somewhere. (S)ATA disks do that, so you have to turn that off for them to
be safe to use. Since the firewire enclosure is a black box, it's
difficult to know exactly what it's doing here, and history here says that
every type (S)ATA disk does the wrong in the default case. I expect that
for any Firewire/USB device, if I write to the disk, then issue a fsync,
it will return success from that once the data has been written to the
disk's cache--which is crippling behavior from the database's perspective
one day when you get a crash.

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

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

[pgsql-www] GIT repo broken

Hi,

I noticed the other day that the branches in the GIT postgresql.git
repository do not contain any patch after it was released. For example,
in

http://git.postgresql.org/?p=postgresql.git;a=shortlog;h=REL8_3_STABLE

the last commit is on 2008-02-12. For example this commit is not there:
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/nls-global.mk?rev=1.14

What's going on here?

--
Alvaro Herrera

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

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

Re: [GENERAL] 0xc3 error Text Search Windows French

Andrew wrote:
> I have a feeling that an issue I'm running into is related to this:
> http://archives.postgresql.org/pgsql-bugs/2008-06/msg00113.php
>
> On Windows XP running PgAdmin III 1.8.4 against either PostgreSQL 8.3.0
> or 8.3.3 DB, when attempting to do a:
>
> select * from ts_debug('french', 'catalogue');
>
> getting the following error:
>
> ERROR: invalid byte sequence for encoding "UTF8": 0xc3

This is probably a bug fixed after 8.3.3:

http://git.postgresql.org/?p=postgresql.git;a=commitdiff;h=6bcf5a85233f7a039648990d1037119efb61146d
(This is the HEAD version of the patch; 8.3 should be identically
patched.)

--
Alvaro Herrera

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

--
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] migrar base de datos de firebird a postgres

2008/6/25 Omar Lòpez Andraca <elendil.systems@gmail.com>:
> no, pues no.. no se si sea yo el que no puedo hacerlo.. o ke pex.. jejeje..
> perdonen mi lenguaje..jeje
> pero nada mas no puede...lo que hice fue lo sigueinte:

> hice el backup de la estructura a un archivo sql.. con una herramienta que
> tiene el Database Workbench Pro 3.. y tambien con el mismo hice el backup de
> los datos..
Y no probaste los links que te pase?

> pero me muestra inconsistencia en los tipos de datos..

> quise
> modificarlos manualmente.. pero el resultado fue desastrozo..jejejeje..

Crea unas tablas de prueba en tu firebird (que puedan reflejar todos
los tipos de datos que utilices)
y pàsalo para ver que tan diferente es el sql de firebird... a ver que
le podemos hacer... =)

> ya me desespere.. un poquito..pero voy a seguirle.. o si alguien encuentra
> algo.. pues se le agradece.

Fuerza!
--
§~^Calabaza^~§ from Villa Elisa, Paraguay
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

[GENERAL] POSIX Escape

Hello All,

Apologies if this is a bit off-topic. Any hints as to what character
sequences need escaping in Postgres's regular expressions?

Let me show you the problem.

If I want to find all strings that start with 'postgres' I use the regex

select str ~ '^postgres'.

However if str contains reserved meta characters, e.g. +(-)[.]{} etc
then I need to escape that sequence somehow? e.g. to find a string
starting 'fish + chips' I need to escape for regex and then postgres.

select str ~ '^fish\\s\\+\\schips''

Has anyone got a definitive list of replacements that are required for
Postgres's version of Regular Expressions?

Howard.

--
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] migrar base de datos de firebird a postgres

no, pues no.. no se si sea yo el que no puedo hacerlo.. o ke pex.. jejeje.. perdonen mi lenguaje..jeje
pero nada mas no puede...lo que hice fue lo sigueinte:
 
hice el backup de la estructura a un archivo sql.. con una herramienta que tiene el Database Workbench Pro 3.. y tambien con el mismo hice el backup de los datos.. pero me muestra inconsistencia en los tipos de datos.. quise modificarlos manualmente.. pero el resultado fue desastrozo..jejejeje..
ya me desespere.. un poquito..pero voy a seguirle.. o si alguien encuentra algo.. pues se le agradece.

2008/6/25 Calabaza <calalinux@gmail.com>:
El día 25 de junio de 2008 12:42, Omar Lòpez Andraca
<elendil.systems@gmail.com> escribió:
> Hola amigos hace tiempo que no escribia, tengo una duda o pregunta.. si
> alguien puede ayudarme a migrar o extraer tanto las tablas como los datos
> de una base de datos que esta en Firebird para pasarla a postgres.. ya que
> mi problema con firebird es el driver para el odbc que nada mas no me
> funciona,
> y como tengo algo de experiencia usando postgres.. pues se me facilita la
> cuestion del desarrollo...
> si alguien sabe como hacerlo o puede ayudarme.. se lo agradesco de
> antemano.. gracias

Mira estos links...
[1]http://oscarzeladapd.blogspot.com/2008/01/migracin-de-datos-firebird-ii.html
[2]http://jachguate.wordpress.com/2008/02/08/exportar-datos-de-firebird/

Los obtuve googleando, si no te funciona, nos avisas, y me instalo un
firebird para probar... =)
Un abrazo..
--
§~^Calabaza^~§ from Villa Elisa, Paraguay
----------------
A hendu hína: The Beatles - Sure To Fall (In Love With You
http://foxytunes.com/artist/the+beatles/track/sure+to+fall+(in+love+with+you
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

Re: [GENERAL] Probably been asked a hundred times before.

david@eclipsecat.com (David Siebert) writes:
> Well I am kind of stuck using OpenSuse. Not a bad distro and is the one
> we use in our office for production work.
> I like CentOS myself for database work and tend to use that for test
> systems here since I manage them myself.
> I was more wondering if someone had made a Postgres centric distro yet.
> Sort of FreeNAS, OpenFiler, or what ever the Asterisk distro is called
> these days.
> Seems like you could build a nice little distro that was database
> centric. Maybe use FreeBSD, Solaris, or Centos as the base.
> Sort of a plug and play solution.

A "pretty minimalist" approach would be...

- Install Debian base (~20MB of "install")

- Figure out packages needed for PostgreSQL
PKGS="postgresql-client-8.3 postgresql-8.3"

- Libraries, and such
PKGS="${PKGS} libpq5 libdbd-pg-perl"

- Some tools
PKGS="${PKGS} pgadmin3 pgadmin3-data"

- Some useful 3rd party bits
PKGS="${PKGS} cfengine2 ntp ssh vim"

Then install that...

$ apt-get install ${PKGS}

That's going to draw in some dependancies, but is still quite, quite
minimal, moreso than anything that wasn't *expressly* customized for
the purpose. That will, for instance, be *way* smaller than Centos.

You could do much the same using ports/openpkg on FreeBSD.
--
output = reverse("gro.mca" "@" "enworbbc")
http://cbbrowne.com/info/finances.html
"Computers are like air conditioners: They stop working properly if
you open windows."

--
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] 0xc3 error Text Search Windows French

Sorry one last detail.

All of my databases are in utf-8 format. My Windows XP is en_AU and
defaults to ISO-8859-1 character sets. My postgresql.conf is set to the
default for the client_encoding setting, which should then default to
the database utf-8 format.

Andrew wrote:
> One additional aspect. I just ran the create text search dictionary
> command without the stopfile declaration using the OO dictionaries,
> and it worked fine with the select ts_lexize('public.fr_ispell',
> 'catalogue'); command executing with no problems. However, after
> creating an associated catalogue based on a copy of the
> pg_catalog.french catalogue, calls to ts_debug against my custom
> French config result in the 0xc3 error. So it is looking like the
> problem is restricted to the parsing of the stop file.
> I ran through the other out of the box supplied stemmers, which I have
> not touched in anyway and it is also occurring with the portuguese
> catalogue.
>
> Cheers
>
> Andy
>
> Andrew wrote:
>> I have a feeling that an issue I'm running into is related to this:
>> http://archives.postgresql.org/pgsql-bugs/2008-06/msg00113.php
>>
>> On Windows XP running PgAdmin III 1.8.4 against either PostgreSQL
>> 8.3.0 or 8.3.3 DB, when attempting to do a:
>>
>> select * from ts_debug('french', 'catalogue');
>>
>> getting the following error:
>>
>> ERROR: invalid byte sequence for encoding "UTF8": 0xc3
>> HINT: This error can also happen if the byte sequence does not match
>> the encoding expected by the server, which is controlled by
>> "client_encoding".
>> CONTEXT: SQL function "ts_debug" statement 1
>>
>> I have replaced the french.stop file with the one from the snowball
>> web site
>> (http://snowball.tartarus.org/algorithms/french/stemmer.html) to see
>> if that would make any difference. But the same issue. I have also
>> attempted to load the French Hunspell dictionary from the Open Office
>> web site (http://wiki.services.openoffice.org/wiki/Dictionaries),
>> using the following command:
>>
>> CREATE TEXT SEARCH DICTIONARY public.fr_ispell (
>> TEMPLATE = pg_catalog.ispell,
>> DictFile = fr_FR,
>> AffFile = fr_FR,
>> StopWords = french
>> );
>>
>> But getting the same error. I have successfully loaded the English
>> and Arabic dictionaries and an Arabic stop file I sourced from
>> elsewhere, and they work fine with the various text search function
>> calls, so it appears to be specifically related to a French character
>> occurring in the stop file and the dictionaries. To use the French
>> OO dictionaries, I had to convert them from an ISO-8859-15 character
>> set encoding to UTF-8. As it still had the same result as with the
>> packaged stop file when converting on Windows, I downloaded them and
>> converted the encoding on a Linux machine before copying them across
>> to windows to see if that would help, but it didn't.
>>
>> However, if I run the ts_debug('french', 'catalogue'); against a
>> Linux version of PostgreSQL 8.3.1, it works fine. I have not tried
>> version 8.3.1 on Windows. While there are a lot more combinations to
>> exhaust before I can make a categorical statement, at this stage it
>> appears to be pointing towards an issue with the UTF-8 parser of
>> PostgreSQL on Windows.
>>
>> Is this an outstanding defect, or is there something that I'm doing
>> wrong in my environment? I have attempted to find anything related
>> on the Internet, but other than the introductory reference, I have
>> not found anything, which for what I would imagine to be, of the size
>> of the French user base surprises me. Hence, I'm thinking that
>> perhaps it may be something in my environment causing the issue. If
>> others could also reproduce the error on their XP machines, that
>> would indicate that the issue was not something specific just to me.
>>
>> At this stage, it is not that important to me, as I'm just playing
>> around with text search for my own curiosity and French was just a
>> language I have randomly picked, along with Arabic (for which I'm
>> lacking a snowball stemmer). I don't actually read, much less speak
>> those languages. However, it would still be nice to have them working.
>>
>> An additional related topic. OO have for some languages, thesaurus
>> files which are not in the same format as supported by Pg Full Text
>> Search. Are there any plans to support the OO thesaurus file
>> formats? They also have hyphenation files. Are there any plans to
>> extend the current dictionary files to include hyphenation rules as
>> captured in the OO hyphenation files? I'm not sure how, if at all
>> hyphenation rules would improve on indexing and searches, but I
>> thought as the files exist, I would pose the question.
>>
>> Thanks,
>>
>> Andy
>>
>>
>>
>>
>>
>
>


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

[HACKERS] Latest on CITEXT 2.0

Howdy,

I just wanted to report the latest on my pet project: implementing a
new case-insensitive text type, "citext", to be locale-aware and to
build and run on PostgreSQL 8.3. I'm not much of a C programmer (this
is only the second time I've written *anything* in C), so I also have
a few questions about my code, best practices, coverage, etc. You can
grab the latest here:

https://svn.kineticode.com/citext/trunk/

BTW, the tests in sql/citext.sql use the pgtap.sql file to run TAP
regression tests. So you can run them using `make installcheck` or
`make test`. The latter requires that pg_prove be installed; you can
get it here:

https://svn.kineticode.com/pgtap/trunk/

Anyway, I think I've got it pretty close to done. The tests cover a
lot of stuff -- nearly everything I could figure out, anyway. But
there are a few gaps.

As a result, I'd appreciate a little help with these questions, all in
the name of making this a solid data type suitable for use on
production systems:

* There seem to still be some implicit CASTS to text that I'd like to
duplicate. For example, select '192.168.1.2'::cidr::text;` works, but
`select '192.168.1.2'::cidr::citext;` does not. Where can I find the C
functions that do these casts for TEXT so that I can put them to work
for citext, too? The internal cast functions used in the old citext
distribution don't exist at all on 8.3.

* There are casts from text that I'd also like to harness for use by
citext, like `cidr(text)`. Where can I find these C functions as well?
(The upshot of this and the previous points is that I'd like citext to
be as compatible with TEXT as possible, and I just need to figure out
how to fill in the gaps in that compatibility.)

* Regular expression and LIKE comparisons using the the operators
properly work case-insensitively, but functions like replace() and
regexp_replace() do not. Should they? and if so, how can I make them
do so?

* The tests assume that LC_COLLATE is set to en_US.UTF-8. Does that
work well for standard PostgreSQL regression tests? How are locale-
sensitive tests run in core regression tests?

* As for my C programming, well, what's broken? I'm especially
concerned that I pfree variables appropriately, but I'm not at all
clear on what needs to be freed. Martijn mentioned before that btree
comparison functions free memory, but I'm such a C n00b that I don't
know what that actually means for my implementation. I'd actually
appreciate a bit of pedantry here. :-)

* Am I in fact getting an appropriate nul-terminated string in my
cilower() function using this code?

char * str = DatumGetCString(
DirectFunctionCall1( textout, PointerGetDatum( arg ) )
);

Those are all the questions I had about my implementation. I'd like to
get this thing done and released soon, so that I can be done with this
particular Yak and get back to what I'm *supposed* to be doing with my
time.

BTW, would there be any interest in this code going into contrib/ in
the distribution? I think that, if we can ensure that it works just
like LOWER() = LOWER(), but without requiring that code, then it would
be a great type to point people to to use instead of that SQL hack
(with all the usual caveats about it being locale-sensitive and not
canonically case-insensitive in the Unicode sense). If so, I'd be
happy to make whatever changes are necessary to make it fit in with
the coding and organization standards of the core and to submit it.

But please, don't expect a civarchar type from me anytime soon. ;-)

Many thanks,

David

--
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] 0xc3 error Text Search Windows French

One additional aspect. I just ran the create text search dictionary
command without the stopfile declaration using the OO dictionaries, and
it worked fine with the select ts_lexize('public.fr_ispell',
'catalogue'); command executing with no problems. However, after
creating an associated catalogue based on a copy of the
pg_catalog.french catalogue, calls to ts_debug against my custom French
config result in the 0xc3 error. So it is looking like the problem is
restricted to the parsing of the stop file.

I ran through the other out of the box supplied stemmers, which I have
not touched in anyway and it is also occurring with the portuguese
catalogue.

Cheers

Andy

Andrew wrote:
> I have a feeling that an issue I'm running into is related to this:
> http://archives.postgresql.org/pgsql-bugs/2008-06/msg00113.php
>
> On Windows XP running PgAdmin III 1.8.4 against either PostgreSQL
> 8.3.0 or 8.3.3 DB, when attempting to do a:
>
> select * from ts_debug('french', 'catalogue');
>
> getting the following error:
>
> ERROR: invalid byte sequence for encoding "UTF8": 0xc3
> HINT: This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding".
> CONTEXT: SQL function "ts_debug" statement 1
>
> I have replaced the french.stop file with the one from the snowball
> web site (http://snowball.tartarus.org/algorithms/french/stemmer.html)
> to see if that would make any difference. But the same issue. I have
> also attempted to load the French Hunspell dictionary from the Open
> Office web site
> (http://wiki.services.openoffice.org/wiki/Dictionaries), using the
> following command:
>
> CREATE TEXT SEARCH DICTIONARY public.fr_ispell (
> TEMPLATE = pg_catalog.ispell,
> DictFile = fr_FR,
> AffFile = fr_FR,
> StopWords = french
> );
>
> But getting the same error. I have successfully loaded the English
> and Arabic dictionaries and an Arabic stop file I sourced from
> elsewhere, and they work fine with the various text search function
> calls, so it appears to be specifically related to a French character
> occurring in the stop file and the dictionaries. To use the French OO
> dictionaries, I had to convert them from an ISO-8859-15 character set
> encoding to UTF-8. As it still had the same result as with the
> packaged stop file when converting on Windows, I downloaded them and
> converted the encoding on a Linux machine before copying them across
> to windows to see if that would help, but it didn't.
>
> However, if I run the ts_debug('french', 'catalogue'); against a Linux
> version of PostgreSQL 8.3.1, it works fine. I have not tried version
> 8.3.1 on Windows. While there are a lot more combinations to exhaust
> before I can make a categorical statement, at this stage it appears to
> be pointing towards an issue with the UTF-8 parser of PostgreSQL on
> Windows.
>
> Is this an outstanding defect, or is there something that I'm doing
> wrong in my environment? I have attempted to find anything related on
> the Internet, but other than the introductory reference, I have not
> found anything, which for what I would imagine to be, of the size of
> the French user base surprises me. Hence, I'm thinking that perhaps
> it may be something in my environment causing the issue. If others
> could also reproduce the error on their XP machines, that would
> indicate that the issue was not something specific just to me.
>
> At this stage, it is not that important to me, as I'm just playing
> around with text search for my own curiosity and French was just a
> language I have randomly picked, along with Arabic (for which I'm
> lacking a snowball stemmer). I don't actually read, much less speak
> those languages. However, it would still be nice to have them working.
>
> An additional related topic. OO have for some languages, thesaurus
> files which are not in the same format as supported by Pg Full Text
> Search. Are there any plans to support the OO thesaurus file
> formats? They also have hyphenation files. Are there any plans to
> extend the current dictionary files to include hyphenation rules as
> captured in the OO hyphenation files? I'm not sure how, if at all
> hyphenation rules would improve on indexing and searches, but I
> thought as the files exist, I would pose the question.
>
> Thanks,
>
> Andy
>
>
>
>
>


--
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] Creating a VIEW with a POINT column

Nick wrote:
> I have a VIEW that consists of two tables, of which contain a POINT
> column. When trying to select from the view I get an error...
>
> ERROR: could not identify an ordering operator for type point
> HINT: Use an explicit ordering operator or modify the query.
>
> Any suggestions??? -Nick

Does your view per chance do ORDER BY <point_column> ? You should then
do as told and use an explicit ordering operator.
See
http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-ORDERBY.

To get better feedback you really should post your view definition (and
the definitions of those two underlying tables).

Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
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] Hardware vs Software RAID

On Wed, Jun 25, 2008 at 01:07:25PM -0500, Kevin Grittner wrote:
>
> It doesn't have to be free software to cut that way. I've actually
> found the free software to waste less of my time.

No question. But one of the unfortunate facts of the
no-charge-for-licenses world is that many people expect the systems to
be _really free_. It appears that some people think, because they've
already paid $smallfortune for a license, it's therefore ok to pay
another amount in operation costs and experts to run the system. Free
systems, for some reason, are expected also magically to run
themselves. This tendency is getting better, but hasn't gone away.
It's partly because the budget for the administrators is often buried
in the overall large system budget, so nobody balks when there's a big
figure attached there. When you present a budget for "free software"
that includes the cost of a few administrators, the accounting people
want to know why the free software costs so much.

> If you depend on your systems, though, you should never deploy any
> change, no matter how innocuous it seems, without testing.

I agree completely.

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

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

[GENERAL] 0xc3 error Text Search Windows French

I have a feeling that an issue I'm running into is related to this:

http://archives.postgresql.org/pgsql-bugs/2008-06/msg00113.php

On Windows XP running PgAdmin III 1.8.4 against either PostgreSQL 8.3.0
or 8.3.3 DB, when attempting to do a:

select * from ts_debug('french', 'catalogue');

getting the following error:

ERROR: invalid byte sequence for encoding "UTF8": 0xc3
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".
CONTEXT: SQL function "ts_debug" statement 1

I have replaced the french.stop file with the one from the snowball web
site (http://snowball.tartarus.org/algorithms/french/stemmer.html) to
see if that would make any difference. But the same issue. I have also
attempted to load the French Hunspell dictionary from the Open Office
web site (http://wiki.services.openoffice.org/wiki/Dictionaries), using
the following command:

CREATE TEXT SEARCH DICTIONARY public.fr_ispell (
TEMPLATE = pg_catalog.ispell,
DictFile = fr_FR,
AffFile = fr_FR,
StopWords = french
);

But getting the same error. I have successfully loaded the English and
Arabic dictionaries and an Arabic stop file I sourced from elsewhere,
and they work fine with the various text search function calls, so it
appears to be specifically related to a French character occurring in
the stop file and the dictionaries. To use the French OO dictionaries,
I had to convert them from an ISO-8859-15 character set encoding to
UTF-8. As it still had the same result as with the packaged stop file
when converting on Windows, I downloaded them and converted the encoding
on a Linux machine before copying them across to windows to see if that
would help, but it didn't.

However, if I run the ts_debug('french', 'catalogue'); against a Linux
version of PostgreSQL 8.3.1, it works fine. I have not tried version
8.3.1 on Windows. While there are a lot more combinations to exhaust
before I can make a categorical statement, at this stage it appears to
be pointing towards an issue with the UTF-8 parser of PostgreSQL on Windows.

Is this an outstanding defect, or is there something that I'm doing
wrong in my environment? I have attempted to find anything related on
the Internet, but other than the introductory reference, I have not
found anything, which for what I would imagine to be, of the size of the
French user base surprises me. Hence, I'm thinking that perhaps it may
be something in my environment causing the issue. If others could also
reproduce the error on their XP machines, that would indicate that the
issue was not something specific just to me.

At this stage, it is not that important to me, as I'm just playing
around with text search for my own curiosity and French was just a
language I have randomly picked, along with Arabic (for which I'm
lacking a snowball stemmer). I don't actually read, much less speak
those languages. However, it would still be nice to have them working.

An additional related topic. OO have for some languages, thesaurus
files which are not in the same format as supported by Pg Full Text
Search. Are there any plans to support the OO thesaurus file formats?
They also have hyphenation files. Are there any plans to extend the
current dictionary files to include hyphenation rules as captured in the
OO hyphenation files? I'm not sure how, if at all hyphenation rules
would improve on indexing and searches, but I thought as the files
exist, I would pose the question.

Thanks,

Andy

--
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] Hardware vs Software RAID

>>> Andrew Sullivan <ajs@commandprompt.com> wrote:

> this is a blade that cuts both ways, and the key thing to do is
> to ensure you have good testing infrastructure in place to check
that
> things will work before you deploy to production. (The other way to
> say that, of course, is "Linux is only free if your time is worth
> nothing." Substitute your favourite free software for "Linux", of
> course. ;-) )

It doesn't have to be free software to cut that way. I've actually
found the free software to waste less of my time. If you depend on
your systems, though, you should never deploy any change, no matter
how innocuous it seems, without testing.

-Kevin

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

[COMMITTERS] pgbouncer - pgbouncer: tag 1.2rc1

Log Message:
-----------
tag 1.2rc1

Modified Files:
--------------
pgbouncer:
NEWS (r1.19 -> r1.20)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/NEWS.diff?r1=1.19&r2=1.20)
configure.ac (r1.35 -> r1.36)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/configure.ac.diff?r1=1.35&r2=1.36)
pgbouncer/debian:
changelog (r1.11 -> r1.12)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/debian/changelog.diff?r1=1.11&r2=1.12)
pgbouncer/doc:
todo.txt (r1.27 -> r1.28)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/doc/todo.txt.diff?r1=1.27&r2=1.28)

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

[HACKERS] Creating a VIEW with a POINT column

I have a VIEW that consists of two tables, of which contain a POINT
column. When trying to select from the view I get an error...

ERROR: could not identify an ordering operator for type point
HINT: Use an explicit ordering operator or modify the query.

Any suggestions??? -Nick

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

[BUGS] BUG #4264: Optimizer fails to use hash_aggregate when appropriate.

The following bug has been logged online:

Bug reference: 4264
Logged by: Scott Carey
Email address: scott@richrelevance.com
PostgreSQL version: 8.3.3
Operating system: Linux (CentOS)
Description: Optimizer fails to use hash_aggregate when appropriate.
Details:

The query optimizer fails to use a hash aggregate most of the time. This is
an inconsistent behavior.

On one particular table this is especially painful. This table has 24
million rows, and when aggregating on a column that the optimizer expects
only a few unique values, it chooses a full sort of those 24 million rows
before a group aggregate, rather than using a hash aggregate that would be 2
to 3 orders of magnitude faster and use less memory.

The simple statement of this bug is the following EXPLAIN output and
corresponding output from the statistics tables. The actual query used has
a more complicated GROUP BY and aggregation (and joins, etc).

The condition will occur for any column used to group by. Even one that has
only two unique values in a 25 million row table.

rr=# explain SELECT count(distinct v_guid) as view_count, p_type FROM
p_log.creative_display_logs_012_2008_06_15 GROUP BY p_type;

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------
GroupAggregate (cost=5201495.80..5395385.38 rows=7 width=47)
-> Sort (cost=5201495.80..5266125.63 rows=25851932 width=47)
Sort Key: p_type
-> Seq Scan on creative_display_logs_012_2008_06_15
(cost=0.00..1223383.32 rows=25851932 width=47)

rr=# select attname, null_frac, avg_width,n_distinct
,correlation from pg_stats where
tablename='creative_display_logs_012_2008_06_15' and attname in ('g_id',
'p_type', 'strat', 'datetime', 'ext_s_id', 't_id');
attname | null_frac | avg_width | n_distinct | correlation
----------------+-----------+-----------+------------+--------------
g_id | 0 | 8 | 14 | 0.221548
p_type | 0 | 4 | 7 | 0.350718
datetime | 0 | 8 | 12584 | 0.977156
ext_s_id | 0.001 | 38 | 11444 | -0.000842848
strat | 0 | 13 | 11 | 0.147418
t_id | 0 | 8 | 2 | 0.998711

(5 rows)

I have dumped, dropped, and restored this table twice recently. Both times
followed by a full vacuum analyze. And in both cases the query optimizer
behaves differently.
Additionally, this table is a partition table, and using the inheritance
facade instead of the table produces consistently worse plans -- at first
the direct-to-table query used the hash aggregate but not the one through
inheritance and I thought this was a partitioning bug. But it definitely
occurs in general and its reproducibility is affected by partitioning but
not dependent on it.

The database is tuned with the default optimizer settings for 8.3.3 +
constraint exclusion for the partition tables. Yes, hash_agg is ON. It
happens sometimes on some tables.

The configuration has ample RAM and all the memory tuning parameters are
generous (shared_mem 7g, temp space 200m, sort/agg space 500m -- I've tried
various settings here with no effect on the plan, just the execution of it
w.r.t. disk based sort or mem based sort).


The table definition is:
Column | Type | Modifiers
--------------------+-----------------------------+-----------
v_guid | character varying(255) |
site_id | bigint |
c_id | bigint |
item_id | bigint |
creative_id | bigint |
camp_id | bigint |
p_type | integer |
datetime | timestamp without time zone |
date | date |
ext_u_id | character varying(50) |
ext_s_id | character varying(50) |
u_guid | character varying(50) |
strat | character varying(50) |
sub_p_type | character varying(32) |
exp_id | bigint |
t_id | bigint |
htmlpi_id | bigint |
p_score | double precision |


Of course DB hints would solve this. So would some sort of tuning parameter
that lets you dial up or down the tendency to do a hash aggregate rather
than a full sort followed by a group aggregate. This is broken rather
severely, especially in combination with partitions (where it is about 3x as
likely to fail to use a hash_aggregate where appropriate in limited
experiments so far -- there are a few thousand partition tables).

All I want is it to stop being brain-dead and deciding to sort large tables
to produce aggregates. In fact, given the rarity in which a sort is
preferred over a hash_agg with large tables -- i'd turn off the group
aggregate if possible!

I have yet to look for a work-around by changing the statistics targets for
the table, but I consider this a bug because even with the default sample
size, the statistics given should PLAINLY lead to use of a hash_aggregate
rather than a full sort followed by group aggregate. The optimizer clearly
expects a small number of unique buckets after aggregation, and the number
of unique items in the result would have to approach the number of rows in
the table for a full sort to make any sense whatsoever.

Thanks!

-Scott

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

Re: [PERFORM] Hardware vs Software RAID

On Wed, Jun 25, 2008 at 01:35:49PM -0400, Merlin Moncure wrote:
> experiences, i'm starting to be more partial to linux distributions
> with faster moving kernels, mainly because i trust the kernel drivers
> more than the vendor provided drivers.

While I have some experience that agrees with this, I'll point out
that I've had the opposite experience, too: upgrading the kernel made
a perfectly stable system both unstable and prone to data loss. I
think this is a blade that cuts both ways, and the key thing to do is
to ensure you have good testing infrastructure in place to check that
things will work before you deploy to production. (The other way to
say that, of course, is "Linux is only free if your time is worth
nothing." Substitute your favourite free software for "Linux", of
course. ;-) )

A

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

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

[COMMITTERS] pgbouncer - pgbouncer: require libevent 1.2+

Log Message:
-----------
require libevent 1.2+

Modified Files:
--------------
pgbouncer:
configure.ac (r1.34 -> r1.35)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/configure.ac.diff?r1=1.34&r2=1.35)

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

Re: [pgsql-es-ayuda] migrar base de datos de firebird a postgres

El día 25 de junio de 2008 12:42, Omar Lòpez Andraca
<elendil.systems@gmail.com> escribió:
> Hola amigos hace tiempo que no escribia, tengo una duda o pregunta.. si
> alguien puede ayudarme a migrar o extraer tanto las tablas como los datos
> de una base de datos que esta en Firebird para pasarla a postgres.. ya que
> mi problema con firebird es el driver para el odbc que nada mas no me
> funciona,
> y como tengo algo de experiencia usando postgres.. pues se me facilita la
> cuestion del desarrollo...
> si alguien sabe como hacerlo o puede ayudarme.. se lo agradesco de
> antemano.. gracias

Mira estos links...
[1]http://oscarzeladapd.blogspot.com/2008/01/migracin-de-datos-firebird-ii.html
[2]http://jachguate.wordpress.com/2008/02/08/exportar-datos-de-firebird/

Los obtuve googleando, si no te funciona, nos avisas, y me instalo un
firebird para probar... =)
Un abrazo..
--
§~^Calabaza^~§ from Villa Elisa, Paraguay
----------------
A hendu hína: The Beatles - Sure To Fall (In Love With You
http://foxytunes.com/artist/the+beatles/track/sure+to+fall+(in+love+with+you
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

[pgsql-es-ayuda] Vistas Actualizables

Cordial Saludo compañeros.
 
No sé si me lo soñé pero me pareció que la versión 8.3 incorporaba vistas actualizables, pero por más que busco no lo encuentro.
 
Realmente me lo soñé y era sólo cursores actualizables?
 
Atentamente,
 
RAUL DUQUE
Bogotá, Colombia