[GENERAL] Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

Hello everybody, we're using postgresql 8.3 for some logging framework. There are several tables for each day (which are inherited from a common base), which - are filled during the day, - after midnight the indizes are changed to FILLFACTOR=100, and - the tables get CLUSTERed by the most import

Re: FW: [GENERAL] how many connections can i use????

Quoting "Craig Ringer" : ... I have a SCO OpenServer 5.0.5 VM ... business critical application ... compiled for Microsoft Xenix, ... source code ... long-lost, ... OpenServer's Xenix emulation mode. triple egad; otherwise known as Good Lord Almighty, better you than me. :)) Henry pgpk6D

Re: [GENERAL] Controlling psql output

On 2009-05-08, Gauthier, Dave wrote: > --_000_482E80323A35A54498B8B70FF2B87980040106E94Bazsmsx504amrc_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > Hi: > > Using a single psql command to generate stdout in linux that will be redire= > cted to a f

Re: [GENERAL] limit-offset different result sets with same query

2009/5/9 Tom Lane : > Merlin Moncure writes: >> On Fri, May 8, 2009 at 5:40 PM, Alvaro Herrera >> wrote: >>> Yeah, we went over this on the spanish list, turned out that I couldn't >>> remember about syncscan :-) > >> I like the new behavior.  It really encourages proper use of order by, >> becau

Re: [GENERAL] Remote access

On 2009-05-08, George Weaver wrote: > This is a multi-part message in MIME format. > > --=_NextPart_000_003C_01C9CFB8.5F323DB0 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Hi, > > I have a client with a main office and a branch offi

[GENERAL] [fulltext]Gin index full scan

I've a table with tsvector column named meta_vector. Over this column there is a gin index. When I execute query like: select id from theses where meta_vector @@@ to_tsquery('cs', 'foo | (!bar)') I get an errror message: query requires full scan, which is not supported by GIN indexes. The weird

[GENERAL] Problem with estimating pages for a table

Hello, I am trying to compute the no of pages of a table. I am using the formula : pages = ( columns width + 28) * no. of rows / block size For each varchar column - I add an extra 4 bytes For each numeric column - I add an extra 8 bytes Add a 28 bytes row overhead. For example if i have a tabl

Re: [GENERAL] Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

On Mon, May 11, 2009 at 12:20 AM, Philipp Marek wrote: > Hello everybody, > > we're using postgresql 8.3 for some logging framework. > > There are several tables for each day (which are inherited from a common > base), which > - are filled during the day, > - after midnight the indizes are changed

[GENERAL] Apparent race in information_schema.tables

This query: SELECT 1 FROM information_schema.tables WHERE table_schema = $1 AND table_name = $2; fails sporadically with the error "relation with OID does not exist". The query is run by a non-superuser, and the table/schema combination exists in the database. The query may have been PREPAREd

Re: [GENERAL] Apparent race in information_schema.tables

Florian Weimer writes: > This query: > SELECT 1 FROM information_schema.tables WHERE table_schema = $1 AND > table_name = $2; > fails sporadically with the error "relation with OID does not > exist". > I guess the OID refers to a temporary table because I can't find it in > pg_class, and the c

[GENERAL] warm-standby errors

Hello, We have a warm-standby of one of our databases, and by this I mean a server in constant recovery mode applying logs being shipped from a primary to the warm-standby. Recently we had to bounce the standby instance and I saw this error in our logs: 2009-04-27 07:11:21.213 GMT8261,

[GENERAL] Build and install - how to avoid dependency on specific library version

Folks, I ran the build on a different Solaris machine and installation immediately failed with this message: ld.so.1: postgres: fatal: libresolv.so.2: version `SUNW_2.2.2' not found (required by file ...//postgres) Looking at the build machine: /usr/lib> /usr/ccs/bin/elfdump -v libresolv.so.2

Re: [GENERAL] warm-standby errors

On Mon, 2009-05-11 at 13:50 -0400, sramirez wrote: > Short of copying the data files elsewhere and doing a row-level > comparison of the data, is there any way I can check to see if there is > actual corruption in the warm standby server? Right now, Warm Standby has same functionality as equi

Re: [GENERAL] warm-standby errors

> Right now, Warm Standby has same functionality as equivalent Oracle feature - i.e. no way to confirm absence of corruption. However, WAL records contain CRC checks that ensure the transferred data is correct, which is more than most other replication techniques posess. Hot Standby will allow

Re: [GENERAL] Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

Philipp Marek wrote: > A few days before we found the machine much slower, because of the autovacuum > processes that were started automatically ["autovacuum: VACUUM ... (to > prevent > wraparound)"]. > > After several days we killed that, and, as a quick workaround, changed > "autovacuum_fre

[GENERAL] Getting a list of encodings.

Is it possible to query the database system tables and get a list of available database encodings? i.e UTF8, LATIN2, SQL_ASCII ... etc. I don't know what view or table to use. Thanks for any help Andrew -- ___ Andrew J. P. Maclean Centre for Autonomous

Re: [GENERAL] Getting a list of encodings.

Andrew Maclean writes: > Is it possible to query the database system tables and get a list of > available database encodings? The pg_encoding_to_char() function might help you, eg select pg_encoding_to_char(i) from generate_series(0,100) i; regards, tom lane -- Sent vi

[GENERAL] Compiler versions on different platforms

Hello All, I am looking to compile PostgreSQL 8.3.7 on following operation systems with their corresponding compilers 1) Windows => Cl.exe from visual studio 2) Linux => gcc ( What is the oldest version which is okay with this setup ? 3) Solaris 9/10+ => cc and gcc ( same ques

Re: [GENERAL] Getting a list of encodings.

Thankyou very much. On Tue, May 12, 2009 at 9:37 AM, Tom Lane wrote: > Andrew Maclean writes: >> Is it possible to query the database system tables and get a list of >> available database encodings? > > The pg_encoding_to_char() function might help you, eg > > select pg_encoding_to_char(i) from

[GENERAL] Difference between "in (...)" and "= any(...)" queries when using arrays

Hi all! I was playing with "in (...)" and "= any (...)" queries and found a difference between them and I wonder: why this works: select * from table_of_integers where integer_column = any (array[5,6]); and this doesn't: select * from table_of_integers where integer_column in (array[5,6]); A

[GENERAL] Putting many related fields as an array

Hi, Currently doing some level of aggregrate tables for some data. These data will be used for slice/dice activity and we want to be able to play/manipulate the data such that I can get means and stddev data. Eg: For each Original Column eg: population_in_town : (I get derivatives) - mean # of

Re: [GENERAL] Pgsql errors, DBI and CGI::Carp

My mistake was using DBD::PgPP (as suggested in "Beginning Databases with PostgreSQL" by Neil Matthew and Richard stones, Apress) instead of DBD::Pg. Thanks for help! On May 1, 2009, at 4:07 PM, Daniel Verite wrote: Toomas Vendelin wrote: I'm writing CGI scripts in Perl using Pos

Re: [GENERAL] Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

Hello Alvaro, On Montag, 11. Mai 2009, Alvaro Herrera wrote: > Philipp Marek wrote: > > A few days before we found the machine much slower, because of the > > autovacuum processes that were started automatically ["autovacuum: VACUUM > > ... (to prevent wraparound)"]. > > > > After several days we

[GENERAL] Luhn algorithm (credit card verify / check) implementation - FIX

The Luhn algorithm implemention I posted earlier (upthread) is internally consistent and will verify checksums it created, but it is actually not a correct implementation of the Luhn algorithm. The earlier code added the doubled digits directly to the checksum, rather than adding each digit of