[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 important index. - Some time much later the tables that aren't needed anymore are DROPped. So far, so fine. 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_freeze_max_age" to 1G and restarted the server, which worked as before (and didn't ran the autovacuum processes). As a next idea we changed the cluster/reindex script to set "vacuum_freeze_min_age=0" before the CLUSTER call, hoping that this would solve our transaction ID wraparound problem. We don't know yet whether that's enough (is it?), but we're seeing another problem - the btree indizes aren't cleaned up. By this I mean that for two compareable tables (with about the same amount of data, one done before the "vacuum_freeze_min_age=0" and one with that), have about the same size for the GIST/GIN-, but about 30-50% difference for the btree indizes (after the ALTER INDEX/CLUSTER). So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return space to the filesystem. Now I'd like to ask whether that's a known problem, and maybe even solved for 8.4 (which we'd like to use because of the "FOR UPDATE" across inherited tables). Regards, Phil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 pgpk6D5rERnEP.pgp Description: PGP Digital Signature
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 file. Many rows with 1 column are returned. I want no header, n= > o footer, no blank lines at the top or bottom, no initial space before each= > record. This is what I'm trying... > > psql -P tuples_only=3Don,footer=3Doff,border=3D0 mydb > > This gets rid of the header and footer OK. But there is still a blank line= > as the first line in stdout. Also, each record has a preceding space befo= > re the column value. > > Is there a way to do what I want? use "copy (select ...) to stdout" instead of "select ..." requires 8.3 or greater. as a bonus nulls and control characters re represented unambiguously. -- 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] 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, >> because the natural ordering results are effectively randomized. A >> class of subtle bugs has been made obvious. :) > > Not really, because the syncscan behavior only kicks in when your table > gets large ... you'll never see it during devel testing on toy tables ... > > regards, tom lane > Yeap. If you see one of the test i made, you'll see this switch over the ~100 regs. -- Emanuel Calvo Franco Sumate al ARPUG ! ( www.arpug.com.ar) ArPUG / AOSUG Member -- 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] 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 office about 90 miles = > away. what's that in milliseconds? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 thing is, that when I drop the index, or disable index scans, it
works. Why can't the planner just use full scans instead of index scans on
such the queries? Thanks for help.
Lukas
--
View this message in context:
http://www.nabble.com/-fulltext-Gin-index-full-scan-tp23482754p23482754.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[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 table with col1: integer, col2: varchar, col3 varchar, I will get: pages = (col1width + col2width + 4 + col3width + 4 + 28)* no. of rows / block size The problem is that I have some problems for some tables where i have numeric and varchar columns. I tested on TPC-H database. - for table t1(c_custkey, int, c_nationkey int, c_acctbal numeric) i got similar result with the real no of pages. Here c_acctbal has 8 byte, and i added the extra 8 bytes. - for table t2(l_orderkey int, l_partkey int, l_quantiy, l_tax, l_extendedprice, l_discount) I got an error of 42 %. The last 4 columns are numeric and i added an extra 8 bytes for each of them -> 32 bytes. (colwidths + 32 + 28)*no.of rows/ block size I would have got a correct value, if i had added only 4 total bytes.. instead of the 32: (colwidths + 4 + 28)*no.of rows/ block size I read on the documentation about some alligment bytes that can be substracted or added, so maybe that is the problem, but I don`t know how One more question. I do not understand how to use the aligment value property. Does it depend on the position of attribute in the table? I am using Postgres 8.3 Thank you very much for any help in this regard, Cristina
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 to FILLFACTOR=100, and > - the tables get CLUSTERed by the most important index. > - Some time much later the tables that aren't needed anymore are DROPped. > > So far, so fine. > > > A few days before we found the machine much slower, because of the autovacuum > processes that were started automatically ["autovacuum: VACUUM ... (to prevent > wraparound)"]. Try increasing autovacuum_vacuum_cost_delay to 20 or 30 milliseconds and see if that helps during autovacuum. > After several days we killed that, and, as a quick workaround, changed > "autovacuum_freeze_max_age" to 1G and restarted the server, which worked as > before (and didn't ran the autovacuum processes). It will still have to eventually run, just less often. > As a next idea we changed the cluster/reindex script to set > "vacuum_freeze_min_age=0" before the CLUSTER call, hoping that this would > solve our transaction ID wraparound problem. No, only vacuuming will solve it. It has to happen eventually. If you put it off too far, and the database can't get the vacuum to reset the txids to the magical frozentxid, then the db will shut down and demand that you vacuum it in single user mode. Which will definitely make it run slower than if autovacuum is doing the job. > We don't know yet whether that's enough (is it?), but we're seeing another > problem - the btree indizes aren't cleaned up. > By this I mean that for two compareable tables (with about the same amount of > data, one done before the "vacuum_freeze_min_age=0" and one with that), have > about the same size for the GIST/GIN-, but about 30-50% difference for the > btree indizes (after the ALTER INDEX/CLUSTER). Not sure about all this part. Could it just be index bloat due to updates and / or delete insert cycles? > So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree > indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return space > to the filesystem. I'm not sure that's the issue here. Cluster doesn't return index space. reindex returns index space. vacuum makes dead index space available for reuse. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 (it's submitted using DBD::Pg with the default flags)---I would have to turn on logging to discover this, and I'm somewhat reluctant to do so. I guess the OID refers to a temporary table because I can't find it in pg_class, and the cause is a race between listing the tables and applying the permission checks to them (which I don't need anyway, I think) because tables in the system catalog are not subject to MVCC. That suggests the problem should go away when I query pg_tables instead, but I haven't tried that yet. This happens with 8.2.6 and 8.2.13, and only while there is significant CREATE TEMPORARY TABLE/DROP TABLE activity in an other backend process. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] 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 cause is a race between listing the tables and > applying the permission checks to them (which I don't need anyway, I > think) because tables in the system catalog are not subject to MVCC. Yeah, that's what I guess too. There is a change in 8.4 that should prevent this class of failures: 2008-12-15 13:09 tgl * src/: backend/catalog/namespace.c, backend/utils/adt/acl.c, test/regress/expected/privileges.out: Arrange for the pg_foo_is_visible and has_foo_privilege families of functions to return NULL, instead of erroring out, if the target object is specified by OID and we can't find that OID in the catalogs. Since these functions operate internally on SnapshotNow rules, there is a race condition when using them in user queries: the query's MVCC snapshot might "see" a catalog row that's already committed dead, leading to a failure when the inquiry function is applied. Returning NULL should generally provide more convenient behavior. This issue has been complained of before, and in particular we are now seeing it in the regression tests due to another recent patch. 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
[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,,,1, // LOG: database system was interrupted while in recovery at log time 2009-04-27 06:55:08 GMT 2009-04-27 07:11:21.213 GMT8261,,,2, // HINT: If this has occurred more than once some data may be corrupted and you may need to choose an earlier recovery target. 2009-04-27 07:11:21.213 GMT8261,,,3, // LOG: starting archive recovery the log message did not appear again until the instance was bounced again. 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? How can I prevent this error from occurring ? Thanks, -Said -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 Version Needed Section: .SUNW_version fileversion libsocket.so.1 SUNW_1.4 SUNWprivate_1.1 libnsl.so.1 SUNW_1.9.1 SUNWprivate_1.4 libc.so.1 SUNW_1.22 SUNWprivate_1.1 Version Definition Section: .SUNW_version index version dependency [1] libresolv.so.2 [ BASE ] [2] SUNW_2.2.2 SUNW_2.2.1 [3] SUNW_2.2.1 SUNW_2.2 [4] SUNW_2.2SUNW_2.1 [5] SUNW_2.1 [6] SUNWprivate_2.2 SUNWprivate_2.1 [7] SUNWprivate_2.1 -- Same check on the installation host: % /usr/ccs/bin/elfdump -v libresolv.so.2 Version Needed Section: .SUNW_version fileversion libsocket.so.1 SUNW_1.4 SUNWprivate_1.1 libnsl.so.1 SUNW_1.7 SUNWprivate_1.4 libc.so.1 SUNW_1.22 SUNWprivate_1.1 Version Definition Section: .SUNW_version index version dependency [1] libresolv.so.2 [ BASE ] [2] SUNW_2.2.1 SUNW_2.2 [3] SUNW_2.2SUNW_2.1 [4] SUNW_2.1 [5] SUNWprivate_2.2 SUNWprivate_2.1 [6] SUNWprivate_2.1 -- So it appears the problem is that when I build Postgres SUNW_2.2.2 is available, and somehow it gets registered with the binary (maybe though configure ?) On the target machine the highest version of libresolv is SUNW_2.2.1, so initdb fails. I've got this far, but I don't know how to deal with this problem. Ideally I'd like to continue running builds on this new machine, but I cannot assume that every installation host will have this particular version of libresolv. Is there a reasonably good way of handling this situation? If it matters this is Postgresql 7.3.10 (Yeah, I know, don't ask me why...) Thank you, Michael. -- 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] 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 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 access to data blocks to allow them to be read and checked, though that is also possible with an external utility to some extent. It probably isn't practical with any replication system to confirm the exact contents of both nodes while replication is running at reasonable speed. Some heuristics may be possible. Do you have anything in mind, other than "detect corruption"? > How can I prevent this > error from occurring ? You haven't shown us the error, just what happens afterwards. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] 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 access to data blocks to allow them to be read and checked, though that is also possible with an external utility to some extent. Do you have a link to documentation on how to do this? It probably isn't practical with any replication system to confirm the exact contents of both nodes while replication is running at reasonable speed. Some heuristics may be possible. agreed Do you have anything in mind, other than "detect corruption"? Really what I am after, is being able to say 'yes our replication is as error-free as it can be' with the most amount of certainty as possible. How can I prevent this error from occurring ? You haven't shown us the error, just what happens afterwards. I might have written too fast. I am curious to know what causes the message to appear in the logs. It only appears when a instance is shutdown and then restarted again. Is there some thing I can do so that the statement isn't triggered when I restart the warm-standby instance? could it be a setting that I have missed? For reference, here is the head of the 2 log files created when the instance was restarted $ ggrep -A 1 -B 1 HINT * edb-2009-04-07_012241.log-2009-04-07 01:22:41.361 GMT1750,,,1, // LOG: database system was interrupted while in recovery at log time 2009-04-02 17:04:54 GMT edb-2009-04-07_012241.log:2009-04-07 01:22:41.361 GMT1750,,,2, // HINT: If this has occurred more than once some data may be corrupted and you may need to choose an earlier recovery target. edb-2009-04-07_012241.log-2009-04-07 01:22:41.362 GMT1750,,,3, // LOG: starting archive recovery -- edb-2009-04-07_013609.log-2009-04-07 01:36:09.424 GMT1920,,,1, // LOG: database system was interrupted while in recovery at log time 2009-04-02 17:04:54 GMT edb-2009-04-07_013609.log:2009-04-07 01:36:09.424 GMT1920,,,2, // HINT: If this has occurred more than once some data may be corrupted and you may need to choose an earlier recovery target. edb-2009-04-07_013609.log-2009-04-07 01:36:09.424 GMT1920,,,3, // LOG: starting archive recovery -- edb-2009-04-27_071121.log-2009-04-27 07:11:21.213 GMT8261,,,1, // LOG: database system was interrupted while in recovery at log time 2009-04-27 06:55:08 GMT edb-2009-04-27_071121.log:2009-04-27 07:11:21.213 GMT8261,,,2, // HINT: If this has occurred more than once some data may be corrupted and you may need to choose an earlier recovery target. edb-2009-04-27_071121.log-2009-04-27 07:11:21.213 GMT8261,,,3, // LOG: starting archive recovery -- edb-2009-04-27_071747.log-2009-04-27 07:17:47.819 GMT8328,,,1, // LOG: database system was interrupted while in recovery at log time 2009-04-27 06:55:08 GMT edb-2009-04-27_071747.log:2009-04-27 07:17:47.819 GMT8328,,,2, // HINT: If this has occurred more than once some data may be corrupted and you may need to choose an earlier recovery target. edb-2009-04-27_071747.log-2009-04-27 07:17:47.819 GMT8328,,,3, // LOG: starting archive recovery Thanks, -Said -- 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] 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_freeze_max_age" to 1G and restarted the server, which worked as > before (and didn't ran the autovacuum processes). Several days? How large is your vacuum_cost_delay and autovacuum_vacuum_cost_delay parameters? > As a next idea we changed the cluster/reindex script to set > "vacuum_freeze_min_age=0" before the CLUSTER call, hoping that this would > solve our transaction ID wraparound problem. REINDEX? What are you doing REINDEX for? -- Alvaro Herrerahttp://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
[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 Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- 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] 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 via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 question as above ) 4) HP-UX 11i+=> aCC and gcc ( same question as above ) 5) AIX 5.3 + => xlC and gc ( same question as above ). I was able to compile on all of the platforms but I want to know about minimum and recommended version requirements for these systems. I am concerned about performance of these binaries and looking to achieve performance of these binary as close as possible to binaries provided at PostgreSQL website. Or if somebody can point out which binaries EnterpriseDB uses for their binaries it will be great. Thanks & Regards, Vikram
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 generate_series(0,100) i; > > 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 > -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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]); Although documentation says: 9.20.4. ANY/SOME [...] SOME is a synonym for ANY. IN is equivalent to = ANY. [...] I thought that if IN is equivalent to = any, both queries above should work. Am I missing something? Thanks in advance. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://fxjr.blogspot.com http://twitter.com/franciscojunior -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 ppl in each town
- stddev # of ppl in each town (stdev calc already uses 2 extra columns
for # of ppl squared and qty of ppl)
- count of ppl
- count of # of ppl is < 100 (to get a percentage of population)
- count of # of ppl is < 500
Hence, I'm seeing a 1:5 column growth here if I put them as column
based.
eg:
| sum of count | sum_of_count_squared | qty | qty < 100 | qty < 500 |
I'm thinking of lumping them into 1 column via an array instead of into
5 different columns. Not sure how to go about this, hence the email to
the list.
something like {244,455,1234,43,23}
query can be done like
sum_of_count / qty = Ave
(sum_of_count_squared * sum_qty ) / (qty * (qty-1)) = STDEV
(sum_qty<100 / sum_qty) = % < 100
(sum_qty<500 / sum_qty) = % < 500
Then there's the issue of speed/responsiveness on doing it.
Help would be appreciated in this.
--
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] 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 Postgresql via DBI
interface. RAISE_ERROR is on.
For some reason (unlike with MySQL), when a Perl script dies from
Postgresql error, the line number of Perl script where the error
occurred is not reported, just the SQL statement line number is
given.
In a longer script looking it may become a tedious task to guess the
line in script that caused the problem. Is that an expected behavior
or am I missing something?
It works for me:
$ cat dberr.pl
use CGI::Carp;
use DBI;
my $dbh=DBI->connect("dbi:Pg:dbname=test") or die;
$dbh->do("bogus SQL");
$ perl dberr.pl
[Fri May 1 15:05:08 2009] dberr.pl: DBD::Pg::db do failed: ERROR:
syntax error at or near "bogus"
[Fri May 1 15:05:08 2009] dberr.pl: LINE 1: bogus SQL
[Fri May 1 15:05:08 2009] dberr.pl: ^ at dberr.pl line 4.
If you run that trivial program in your environment, what output do
you get?
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org
--
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: [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 killed that, and, as a quick workaround, changed > > "autovacuum_freeze_max_age" to 1G and restarted the server, which worked > > as before (and didn't ran the autovacuum processes). > > Several days? How large is your vacuum_cost_delay and > autovacuum_vacuum_cost_delay parameters? They're set to 0 and 20ms resp. > > As a next idea we changed the cluster/reindex script to set > > "vacuum_freeze_min_age=0" before the CLUSTER call, hoping that this would > > solve our transaction ID wraparound problem. > > REINDEX? What are you doing REINDEX for? Some tables get CLUSTERed; I put an option in the script to just do a REINDEX, if wanted. That's just the name of the script, it normally doesn't run REINDEX. Regards, Phil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 the the doubled digits. Here's a corrected version that passes tests against other implementations in other languages. -- -- Luhn algorithm implementation by Craig Ringer -- in pure SQL (PostgreSQL function dialect, but -- should be easily adapted to other DBMSs). -- Note that this implementation is purely -- arithmetic; it avoids string manipulation entirely. -- -- See: http://en.wikipedia.org/wiki/Luhn_algorithm -- CREATE OR REPLACE FUNCTION luhn_verify(int8) RETURNS boolean AS $$ -- Take the sum of the -- doubled digits and the even-numbered undoubled digits, and see if -- the sum is evenly divisible by zero. SELECT -- Doubled digits might in turn be two digits. In that case, -- we must add each digit individually rather than adding the -- doubled digit value to the sum. Ie if the original digit was -- `6' the doubled result was `12' and we must add `1+2' to the -- sum rather than `12'. MOD(SUM(doubled_digit / INT8 '10' + doubled_digit % INT8 '10'), 10) = 0 FROM -- Double odd-numbered digits (counting left with -- least significant as zero). If the doubled digits end up -- having values -- > 10 (ie they're two digits), add their digits together. (SELECT -- Extract digit `n' counting left from least significant --as zero MOD( ( $1::int8 / (10^n)::int8 ), 10::int8) -- Double odd-numbered digits * (MOD(n,2) + 1) AS doubled_digit FROM generate_series(0, ceil(log( $1 ))::integer - 1) AS n ) AS doubled_digits; $$ LANGUAGE 'SQL' IMMUTABLE STRICT; COMMENT ON FUNCTION luhn_verify(int8) IS 'Return true iff the last digit of the input is a correct check digit for the rest of the input according to Luhn''s algorithm.'; CREATE OR REPLACE FUNCTION luhn_generate_checkdigit(int8) RETURNS int8 AS $$ SELECT -- Add the digits, doubling even-numbered digits (counting left -- with least-significant as zero). Subtract the remainder of -- dividing the sum by 10 from 10, and take the remainder -- of dividing that by 10 in turn. ((INT8 '10' - SUM(doubled_digit / INT8 '10' + doubled_digit % INT8 '10') % INT8 '10') % INT8 '10')::INT8 FROM (SELECT -- Extract digit `n' counting left from least significant\ -- as zero MOD( ($1::int8 / (10^n)::int8), 10::int8 ) -- double even-numbered digits * (2 - MOD(n,2)) AS doubled_digit FROM generate_series(0, ceil(log($1))::integer - 1) AS n ) AS doubled_digits; $$ LANGUAGE 'SQL' IMMUTABLE STRICT; COMMENT ON FUNCTION luhn_generate_checkdigit(int8) IS 'For the input value, generate a check digit according to Luhn''s algorithm'; CREATE OR REPLACE FUNCTION luhn_generate(int8) RETURNS int8 AS $$ SELECT 10 * $1 + luhn_generate_checkdigit($1); $$ LANGUAGE 'SQL' IMMUTABLE STRICT; COMMENT ON FUNCTION luhn_generate(int8) IS 'Append a check digit generated according to Luhn''s algorithm to the input value. The input value must be no greater than (maxbigint/10).'; CREATE OR REPLACE FUNCTION luhn_strip(int8) RETURNS int8 AS $$ SELECT $1 / 10; $$ LANGUAGE 'SQL' IMMUTABLE STRICT; COMMENT ON FUNCTION luhn_strip(int8) IS 'Strip the least significant digit from the input value. Intended for use when stripping the check digit from a number including a Luhn''s algorithm check digit.'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
