[GENERAL] optimizer's cost formulas

Hello everybody! I'm trying to understand the the query planner's cost estimator. I was not able to find anywhere the formulas that estimate the cost of each operation that the optimizer produces. I only found this for the sequential scan: SEQ SCAN = ( cpu_tuple_cost * rows ) + ( number of pag

[GENERAL] how to get collation according to Unicode Collation Algorithm?

Hi, I have a problem with proper collation of UTF-8 strings using PostgreSQL version 9.2.4 under Debian Linux 6.0 with de_DE.utf8 (but en_US behaves the same) locale: CREATE TABLE test_collation ( q text ); INSERT INTO test_collation (q) VALUES ('aa'), ('ac'), ('a&b'); SELECT * FROM test_coll

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

Clemens Eisserer wrote: > Here is what I am trying to achieve: Set "synced" to false at any > update, except when it has been set explicitly to true. > This does not seem to be possible, without checking the value SET > by UPDATE? Right; since there is no way to check whether a 'true' value ther

[GENERAL] Source code and database object identifiers

Hi all, We are planning to add PostgreSQL database support to our application. We have run into the issue of where in PostgreSQL is converting all the database object identifiers into lower case. I understand that's the how PostgreSQL works and I can double quote the identifiers and preserve the

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

Kevin Grittner wrote: > Your BEFORE UPDATE trigger could leave the "synced" value in NEW > alone if force_sync was false, and set "synced" to false > otherwise.  It could then set NEW.force_sync to false, to leave you > ready for the next update. Sorry, that's the wrong way around.  I should hav

Re: [GENERAL] Source code and database object identifiers

brad st writes: > We are planning to add PostgreSQL database support to our application. We > have run into the issue of where in PostgreSQL is converting all the > database object identifiers into lower case. I understand that's the how > PostgreSQL works and I can double quote the identifiers

Re: [GENERAL] High CPU usage of stats collector

On Sun, Mar 31, 2013 at 2:44 PM, komunca wrote: > On my db server I have noticed a very high CPU usage of process: > *postgres: stats collector process* > > It is not below 50% of CPU. (Info from htop). > > What is best way to decrease CPU usage of this process? > This is a known issue when you

Re: [GENERAL] Using varchar primary keys.

On 2013-04-02, Joe Van Dyk wrote: > On Tue, Apr 2, 2013 at 11:16 AM, Merlin Moncure wrote: >> On Tue, Apr 2, 2013 at 10:34 AM, Joe Van Dyk wrote: >> > >> > I've been wishing for a smaller uuid type for a while. >> > I've been using a unique text column with a default of >> > random_characters(12

Re: [GENERAL] bug in COPY implementation (all versions of Postgres)?

On 2013-04-05, Konstantin Izmailov wrote: > > 2. Insert value 'Galaxy\040Tab' using command COPY TEST(description) FROM > stdin WITH DELIMITER '|' CSV. > > The following error is returned: value too long for type character > varying(10) CSV is essentially a binary format. there is no ecaping in C

Re: [GENERAL] Need advice to avoid ORDER BY

On 2013-04-04, Condor wrote: > Hello, > > I have one query in my postgresql 9.2.3 that took 137 ms to me executed > and looking a way > what I can do to optimize it. I have one table generated numbers from 1 > to 1 000 000 and > I need to get first free id, meanwhile id's when is taken can be fr

[GENERAL] REL/Centos4 release of 8.4.17?

I know Centos 4 is EOL, but will there be a REL/Centos 4 release of postgres 8.4.17? The latest here is 8.4.16: http://yum.postgresql.org/8.4/redhat/rhel-4-i386/repoview/ If not, thanks for the many Centos 4 packages over the years. -- Jared Beck - Singlebrook - (607) 330-1493 -- Sent via pg

Re: [GENERAL] how to get collation according to Unicode Collation Algorithm?

On 2013-04-06, rudolf wrote: > Hi, > > I have a problem with proper collation of UTF-8 strings using PostgreSQL > version 9.2.4 under Debian Linux 6.0 with de_DE.utf8 (but en_US behaves > the same) locale: > > CREATE TABLE test_collation ( q text ); > INSERT INTO test_collation (q) VALUES ('aa')

[GENERAL] Hosting PG on AWS in 2013

First I need to say that I'm asking this question on behalf of "a friend", who asked me what I thought on the subject -- I host all the databases important to me and my livelihood, on physical machines I own outright. That said, I'm curious as to the current thinking on a) whether it is wise,

[GENERAL] Building 3rd-party contrib/extension on Windows?

Hi, PostgreSQL has lots of useful modules/extentions even outside the core distribution. I'm trying to find out how I can build such 3rd-party contrib/extention on Windows. For example, I'm using PostgreSQL on Windows, bulit and distributed by EnterpriseDB, and I want to run pgTAP on it. AFAIK, t