[GENERAL] What is pg_toast_temp_* in my DB?
What is pg_toast_temp_* in my DB?
Re: [GENERAL] What is pg_toast_temp_* in my DB?
Date: Tue, 4 Jan 2011 15:53:30 +0600 Subject: [GENERAL] What is pg_toast_temp_* in my DB? From: rumman...@gmail.com To: pgsql-general@postgresql.org What is pg_toast_temp_* in my DB? Hello! pg_toast_temp_* are some special schemas where temporary tables are created. Having special schemas for temporary tables allows low-level code to recognize such tables as temp ones, so we have many optimizations. I believe that in the documentation there's more explanations about it. Regards, Andre Fernandes.
[GENERAL] Updated Latch Patch
Hi, I would like to know where I could find the latest available Latch patch. If anyone knows, please share this information. Thanks in advance -- BRs Shoma
Re: [GENERAL] WAL Archiving Stopped
Em 3/1/2011 18:39, Filip Rembiałkowski escreveu: archiver process will retry later; it never stops trying, sleep time is just longer. 2011/1/3, Norberto Delle: Hi all I have a PostgreSQL 9.0.1 instance, with WAL Archiving. Today, after some failed tries to archive a WAL file, it stopped trying to archive the files, but the number of logfiles in the pg_xlog directory keep growing. Any ideas of what is going on? Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Hi Filip It was taking too long to retry, a matter of hours. I had to restart the service to it start trying to archive the wal files. Norberto -- 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] Date Parameter To Query Confusing Optimizer
"By the very definition of a prepared statement the query plan gets stored before the parameter values are known" Is this true for all databases? It would seem to me that this approach would always lead to the wrong query plan, especially in the case I am testing where the selectivity is very low for the BETWEEN clause I am using. The trouble is, the BETWEEN clause can also yield a highly selective result--which is basically the case as the database "ages". See, the date/timestamp column in this case is a good choice as it is a database activity table, a temporal database. The trouble is at times there is a huge amount of activity, yielding very low selectivity. Maybe it would be best for me to just disable the indices on the timestamp fields and just use/expect my join would yield the best results. But, back on topic, to me it seems wrong that choice of prepared vs. non-prepared, and protocol 2 vs. 3, would influence the optimizer so profoundly. I would think it's got to be something I can tune, that prepared statement parameters be considered for execution plan. Is there such a setting? >>> Alban Hertroys 1/4/2011 2:21 AM >>> On 3 Jan 2011, at 23:48, Kurt Westerfeld wrote: > I have a JDBC-based application which passes date/time parameters using JDBC > query parameters, which is performing very badly (ie. doing full table > scans). In an effort to try to narrow down the problem, I am taking the > query and running it in interactive SQL mode, but changing the date > parameters (which are BETWEEN ? and ? clauses) and placing a date literal > instead, using the "date '2011-01-01' syntax. When I do this, the query runs > instantly, obviously using indices on the tables involved. > > Now, I suspect the optimizer is taking the wrong path based on the usage of > query parameters. I'm pretty surprised by this, because I would think the > optimizer would do the same thing for any query parameter, however it > arrived. Unfortunately for this situation, the code which forms the query The problem here is that JDBC uses prepared statements for parameterised queries. By the very definition of a prepared statement the query plan gets stored before the parameter values are known, which forces the database to use a query plan that would work for every possible value of those parameters. Thus you end up with a generic query plan. This isn't often a problem, but if a significant number of your possible parameter values exist in a high percentage of your table rows, then chances are you'll end up with a plan with a sequential scan. You didn't tell what version of Postgres you're using - I recall recent versions (since 8.3?) are smarter about this particular scenario. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:1213,4d22ca9211544532215324!
Re: [GENERAL] Date Parameter To Query Confusing Optimizer
Can You try "...BETWEEN ?::date and ?::date ..." syntax or send statement causing problems? Kind regards, Radosław Smogura On Tue, 04 Jan 2011 07:27:42 -0700, "Kurt Westerfeld" wrote: "By the very definition of a prepared statement the query plan gets stored before the parameter values are known" Is this true for all databases? It would seem to me that this approach would always lead to the wrong query plan, especially in the case I am testing where the selectivity is very low for the BETWEEN clause I am using. The trouble is, the BETWEEN clause can also yield a highly selective result--which is basically the case as the database "ages". See, the date/timestamp column in this case is a good choice as it is a database activity table, a temporal database. The trouble is at times there is a huge amount of activity, yielding very low selectivity. Maybe it would be best for me to just disable the indices on the timestamp fields and just use/expect my join would yield the best results. But, back on topic, to me it seems wrong that choice of prepared vs. non-prepared, and protocol 2 vs. 3, would influence the optimizer so profoundly. I would think it's got to be something I can tune, that prepared statement parameters be considered for execution plan. Is there such a setting? Alban Hertroys 1/4/2011 2:21 AM >>> On 3 Jan 2011, at 23:48, Kurt Westerfeld wrote: I have a JDBC-based application which passes date/time parameters using JDBC query parameters, which is performing very badly (ie. doing full table scans). In an effort to try to narrow down the problem, I am taking the query and running it in interactive SQL mode, but changing the date parameters (which are BETWEEN ? and ? clauses) and placing a date literal instead, using the "date '2011-01-01' syntax. When I do this, the query runs instantly, obviously using indices on the tables involved. Now, I suspect the optimizer is taking the wrong path based on the usage of query parameters. I'm pretty surprised by this, because I would think the optimizer would do the same thing for any query parameter, however it arrived. Unfortunately for this situation, the code which forms the query The problem here is that JDBC uses prepared statements for parameterised queries. By the very definition of a prepared statement the query plan gets stored before the parameter values are known, which forces the database to use a query plan that would work for every possible value of those parameters. Thus you end up with a generic query plan. This isn't often a problem, but if a significant number of your possible parameter values exist in a high percentage of your table rows, then chances are you'll end up with a plan with a sequential scan. You didn't tell what version of Postgres you're using - I recall recent versions (since 8.3?) are smarter about this particular scenario. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:1213,4d22ca9211544532215324! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Warning: database postgres must be vacuumed within 1000000 transactions
I got a big problem: Warning: database 'postgres' must be vacuumed within 100 transactions HINT: To avoid database shutdown, execute a full database vacuum in 'postgres' Warning: database 'postgres' must be vacuumed within 99 transactions HINT: To avoid database shutdown, execute a full database vacuum in 'postgres' Warning: database 'postgres' must be vacuumed within 98 transactions HINT: To avoid database shutdown, execute a full database vacuum in 'postgres' Warning: database 'postgres' must be vacuumed within 97 transactions HINT: To avoid database shutdown, execute a full database vacuum in 'postgres' backend> vacuum Warning: database 'postgres' must be vacuumed within 96 transactions HINT: To avoid database shutdown, execute a full database vacuum in 'postgres' Warning: database 'postgres' must be vacuumed within 95 transactions HINT: To avoid database shutdown, execute a full database vacuum in 'postgres' its so slow!, i dont know what else to do, it will take 20 days to do it all, and i cant wait, help me please! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Warning-database-postgres-must-be-vacuumed-within-100-transactions-tp3326896p3326896.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
Re: [GENERAL] What is pg_toast_temp_* in my DB?
On Tuesday 04 January 2011 3:04:29 am André Fernandes wrote: > Date: Tue, 4 Jan 2011 15:53:30 +0600 > Subject: [GENERAL] What is pg_toast_temp_* in my DB? > From: rumman...@gmail.com > To: pgsql-general@postgresql.org > > What is pg_toast_temp_* in my DB? > > Hello! > > pg_toast_temp_* are some special schemas where temporary tables are > created. Having > special schemas for temporary tables allows low-level code to recognize > such tables as temp ones, so we have many optimizations. > I believe that in the documentation there's more explanations about it. > > Regards, > Andre Fernandes. To get technical, temporary tables are put in pg_temp_* And per the release notes for 8.3: Place temporary tables' TOAST tables in special schemas named pg_toast_temp_nnn (Tom) This allows low-level code to recognize these tables as temporary, which enables various optimizations such as not WAL-logging changes and using local rather than shared buffers for access. This also fixes a bug wherein backends unexpectedly held open file references to temporary TOAST tables. -- Adrian Klaver adrian.kla...@gmail.com -- 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] Warning: database postgres must be vacuumed within 1000000 transactions
On Tuesday 04 January 2011 4:06:48 am asti1987 wrote: > I got a big problem: > Warning: database 'postgres' must be vacuumed within 100 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > Warning: database 'postgres' must be vacuumed within 99 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > Warning: database 'postgres' must be vacuumed within 98 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > Warning: database 'postgres' must be vacuumed within 97 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > backend> vacuum > > > Warning: database 'postgres' must be vacuumed within 96 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > Warning: database 'postgres' must be vacuumed within 95 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > its so slow!, i dont know what else to do, it will take 20 days to do it > all, and i cant wait, > help me please! > What is slow, the vacuum? Are you doing the vacuum on the 'postgres' database? -- Adrian Klaver adrian.kla...@gmail.com -- 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] Warning: database postgres must be vacuumed within 1000000 transactions
> > I got a big problem: > Warning: database 'postgres' must be vacuumed within 100 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > Warning: database 'postgres' must be vacuumed within 99 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > Warning: database 'postgres' must be vacuumed within 98 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > Warning: database 'postgres' must be vacuumed within 97 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > backend> vacuum > > > Warning: database 'postgres' must be vacuumed within 96 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > Warning: database 'postgres' must be vacuumed within 95 transactions > HINT: To avoid database shutdown, execute a full database vacuum in > 'postgres' > > its so slow!, i dont know what else to do, it will take 20 days to do it > all, and i cant wait, > help me please! 1) What version of PostgreSQL is this? 2) Have you been running autovacuum? If not, have you been running VACUUM regularly? If both answers are "no" then there may ne a lot of "garbage" that has to be cleaned up by VACUUM. 3) If the VACUUM is slow (although I'm not sure what exactly you mean by this), you can try to change several parameters. a) maintenance_work_mem - default is 16MB, increasing it may improve the VACUUM performance significantly b) vacuum_cost_delay / vacuum_cost_limit - there are several cost variavbles related to vacuuming, it's usually enough to modify just these two so that the VACUUM acts more aggresively (e.g. set the cost_delay to zero, and it won't sleep at all) regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table Vacuum (to prevent wraparound)
PostgreSQL 8.4.4 on x86_64-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit 2 large tables (around a billion rows each) recently received attention from autovacuum. They both currently show up in my currently running queries as "autovacuum: VACUUM tablename (to prevent wraparound)". These tables are insert-only audit tables….no updates, no deletes. Autovacuum settings are default with no overrides for these specific tables (the only override is to set 10 max_workers) I will admit that the "Preventing Transaction ID Wraparound Failures" chapter in the manual (and the subsequent autovacuum daemon chapter) make my head spin. Is there some specific query related to relfrozenxid, datfrozenxid that I should be utilizing to do some analysis? Obviously SOME threshhold has been reached…hence autovacuum stepping in. My two real questions are: 1. Am I in any real danger here? I have run autovacuum continuously since the dump-restore that created this database 6 months ago. 2. These autovacuums have been running for 5 days now. I have a database maintenance window tonight where I have to take down the server. I assume autovacuum will re-awaken and decide to start vacuuming these again. I assume I am effecting performance with these needless? vacuums. Any tweak I can/should make to any of the freeze_age params to prevent 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] Date Parameter To Query Confusing Optimizer
First, sorry I didn't mention that I am using PostgreSQL 9, but the problem existed also on 8.4. As for the BETWEEN clause, I'm using hibernate and don't want to put a database-specific SQL query in place. I also decided to drop a few indexes, which were probably causing the optimizer to choose the wrong plan. This "fixes" the problem also, but I think in general the "bug" here is that the query plan is bound too early in prepared statement execution. >>> Radosław Smogura 1/4/2011 9:48 AM >>> Can You try "...BETWEEN ?::date and ?::date ..." syntax or send statement causing problems? Kind regards, Radosław Smogura On Tue, 04 Jan 2011 07:27:42 -0700, "Kurt Westerfeld" wrote: > "By the very definition of a prepared statement the query plan gets > stored before the parameter values are known" > > Is this true for all databases? It would seem to me that this > approach would always lead to the wrong query plan, especially in the > case I am testing where the selectivity is very low for the BETWEEN > clause I am using. The trouble is, the BETWEEN clause can also yield > a > highly selective result--which is basically the case as the database > "ages". See, the date/timestamp column in this case is a good choice > as it is a database activity table, a temporal database. The trouble > is at times there is a huge amount of activity, yielding very low > selectivity. > > Maybe it would be best for me to just disable the indices on the > timestamp fields and just use/expect my join would yield the best > results. > > But, back on topic, to me it seems wrong that choice of prepared vs. > non-prepared, and protocol 2 vs. 3, would influence the optimizer so > profoundly. I would think it's got to be something I can tune, that > prepared statement parameters be considered for execution plan. Is > there such a setting? > Alban Hertroys 1/4/2011 2:21 AM >>> > On 3 Jan 2011, at 23:48, Kurt Westerfeld wrote: > >> I have a JDBC-based application which passes date/time parameters > using JDBC query parameters, which is performing very badly (ie. > doing > full table scans). In an effort to try to narrow down the problem, I > am taking the query and running it in interactive SQL mode, but > changing the date parameters (which are BETWEEN ? and ? clauses) and > placing a date literal instead, using the "date '2011-01-01' syntax. > When I do this, the query runs instantly, obviously using indices on > the tables involved. >> >> Now, I suspect the optimizer is taking the wrong path based on the > usage of query parameters. I'm pretty surprised by this, because I > would think the optimizer would do the same thing for any query > parameter, however it arrived. Unfortunately for this situation, the > code which forms the query > > The problem here is that JDBC uses prepared statements for > parameterised queries. By the very definition of a prepared statement > the query plan gets stored before the parameter values are known, > which forces the database to use a query plan that would work for > every possible value of those parameters. > > Thus you end up with a generic query plan. > > This isn't often a problem, but if a significant number of your > possible parameter values exist in a high percentage of your table > rows, then chances are you'll end up with a plan with a sequential > scan. > > You didn't tell what version of Postgres you're using - I recall > recent versions (since 8.3?) are smarter about this particular > scenario. > > Alban Hertroys > > -- > Screwing up is an excellent way to attach something to the ceiling. > > !DSPAM:1213,4d22ca9211544532215324!
Re: [GENERAL] Date Parameter To Query Confusing Optimizer
In response to "Kurt Westerfeld" : > >>> Radosław Smogura 1/4/2011 9:48 AM >>> > Can You try "...BETWEEN ?::date and ?::date ..." syntax or send > statement causing problems? > > As for the BETWEEN clause, I'm using hibernate and don't want to put a > database-specific SQL query in place. You could do "BEWTEEN CAST(? AS DATE) AND CAST(? AS DATE)" and it wouldn't be database-specific. You might also want to consider top-posting. I'm not sure why I read enough of this to understand it, as I usually get confused an frustrated when things have been top posted and stop reading. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Table Vacuum (to prevent wraparound)
> I will admit that the "Preventing Transaction ID Wraparound Failures" > chapter in the manual (and the subsequent autovacuum daemon chapter) make > my head spin. The basic idea is pretty simple, really. You have a limited amount of transaction IDs, so you arrange them into a circle, and you are moving the 'start' from time to time. And of course you must not skip too far the past suddenly turns into future. All that 'technical details' about modulo-2^31 arithmetic is just about this basic idea. Start is equal to the frozen XID. > Is there some specific query related to relfrozenxid, datfrozenxid that I > should be utilizing to do some analysis? Obviously SOME threshhold has > been reached…hence autovacuum stepping in. I guess the threshold that triggered this is autovacuum_freeze_max_age, with a default value of 200 million. So if it took you 6 months to reach this threshold, it shouln't be a problem in the next few years (until you reach one or two billion XIDs, I'm not sure right now). But if the vacuum fixes this, then you're safe. You can see the current frozenxid/age for each database or relation SELECT datname, age(datfrozenxid) FROM pg_database; SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r'; > My two real questions are: > > 1. Am I in any real danger here? I have run autovacuum continuously > since the dump-restore that created this database 6 months ago. I don't think so. The autovacuum starts this VACUUM FREEZE behavior after one billion XIDs, and there are two billion XIDs available. So if it took 6 months to trigger this, you have about another 6 months to solve it. And the autovacuum should do that in 6 months I guess. > 2. These autovacuums have been running for 5 days now. I have a > database maintenance window tonight where I have to take down the > server. I assume autovacuum will re-awaken and decide to start > vacuuming these again. I assume I am effecting performance with these > needless? vacuums. Any tweak I can/should make to any of the freeze_age > params to prevent this? Well, you could play a bit with the vacuum_freeze_min_age/ (increase or decrease it), but I guess it's not worth it (especially in case of these growing tables). What are the (auto)vacuum cost parameters? If there's not a significant I/O load, you could try to make it more aggresive, so that it finishes sooner. Just decrease the autovacuum_vacuum_cost_delay to 10 and increase the autovacuum_vacuum_cost_limit (to 1000 or something like that). regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to build plperl with PostgreSQL 9 on Windows
I has set config.pl to
$config->{perl};
1;
and run build.bat, everything is ok, there is plpgsql.dll but don't found
plperl.dll.
I use strawberry perl 5.10 or must I use ActiveState Perl.
Please help me. Thank in advance
Tuan Hoang ANh
Re: [GENERAL] WAL Archiving Stopped
W dniu 4 stycznia 2011 14:55 użytkownik Norberto Delle napisał: > Em 3/1/2011 18:39, Filip Rembiałkowski escreveu: > > archiver process will retry later; it never stops trying, sleep time >> is just longer. >> >> 2011/1/3, Norberto Delle: >> >>> Hi all >>> >>> I have a PostgreSQL 9.0.1 instance, with WAL Archiving. >>> Today, after some failed tries to archive a WAL file, it stopped trying >>> to archive the files, >>> but the number of logfiles in the pg_xlog directory keep growing. >>> Any ideas of what is going on? >>> >>> Norberto >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >>> Hi Filip > > It was taking too long to retry, a matter of hours. > I had to restart the service to it start trying to archive the wal files. > > that's strange; from the source code I would think that it will retry indefinitely. ( http://doxygen.postgresql.org/pgarch_8c-source.html ) I have also tested this with archive_command = "/bin/false" and it works as expected. archiver does 3 retries, then sleep for 60 seconds hardcoded timeout. 2011-01-04 18:11:41.407 CET [2375]WARNING: transaction log file "00010008" could not be archived: too many failures 2011-01-04 18:12:41.474 CET [2375]LOG: archive command failed with exit code 1 2011-01-04 18:12:41.474 CET [2375]DETAIL: The failed archive command was: /bin/false 2011-01-04 18:12:42.478 CET [2375]LOG: archive command failed with exit code 1 2011-01-04 18:12:42.478 CET [2375]DETAIL: The failed archive command was: /bin/false 2011-01-04 18:12:43.484 CET [2375]LOG: archive command failed with exit code 1 2011-01-04 18:12:43.484 CET [2375]DETAIL: The failed archive command was: /bin/false 2011-01-04 18:12:43.484 CET [2375]WARNING: transaction log file "00010008" could not be archived: too many failures (and so on...) > Norberto >
[GENERAL] Re: Warning: database postgres must be vacuumed within 1000000 transactions
8.1.23 Im typing ¨vacuum¨ since yesterday in the backend and stop the vacuuming each 30 minutes, and I try again typing vacuum, but im by the number 890271/100 and its too slow!, i dont know what else to do to make it faster. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Warning-database-postgres-must-be-vacuumed-within-100-transactions-tp3326896p3327374.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
Re: [GENERAL] Re: Warning: database postgres must be vacuumed within 1000000 transactions
> > 8.1.23 > Im typing ¨vacuum¨ since yesterday in the backend and stop the vacuuming > each 30 minutes, and I try again typing vacuum, but im by the number > 890271/100 and its too slow!, i dont know what else to do to make it > faster. Why are you stopping it each 30 minutes? And the fact that something runs for a long time does not mean it's slow ... the vacuum is usually configured to sleep from to time, not to case problems to the other processes. Is it causing a lot of I/O? If not, try to tune the parameters I've already mentioned in the previous e-mail. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Warning: database postgres must be vacuumed within 1000000 transactions
im not stopping, it stops by it self, what parameters i must to change?, how i do that? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Warning-database-postgres-must-be-vacuumed-within-100-transactions-tp3326896p3327508.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
Re: [GENERAL] Re: Warning: database postgres must be vacuumed within 1000000 transactions
effected a "vacuum" away from your "pgadmin" 2011/1/4 asti1987 > > im not stopping, it stops by it self, what parameters i must to change?, > how > i do that? > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Warning-database-postgres-must-be-vacuumed-within-100-transactions-tp3326896p3327508.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 > -- ISC Miguel Angel Hernandez Moreno
Re: [GENERAL] HNY-2011
I just want to say I apologize for over reacting yesterday As someone guessed I am not even religious so I shouldn't bother. The reason for my reaction though is that I really like those open source communities. People are working together collaborating to make things better for everyone. Then, to me it is very important that everyone feels welcome. It is a little like an oasis in a world where people put a lot of effort in protecting, hiding, defending and amongst other things fighting about religions. But to make this work, the most important thing is to treat each other with respect, and that is why I have to apologize because my answer yesterday was not respectful. It was more like an instinctive reaction. So to Adarsh Sharma, I am sorry and I apologize Regards Nicklas Avén On Mon, 2011-01-03 at 13:47 +0100, Nicklas Avén wrote: > Happy new year > but spare me from any blessings, please > > /Nicklas > > 2011-01-03 skrev Adarsh Sharma : > > Dear all, > > > > > >A very-very Happy New Year 2011 to all. May God Bless all of us to > solve > >future problems. > > > > > >Thanks and Regards > > > >Adarsh Sharma > > > > > > > >-- > >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] Re: Warning: database postgres must be vacuumed within 1000000 transactions
On 01/04/2011 09:01 AM, asti1987 wrote: 8.1.23 Im typing ¨vacuum¨ since yesterday in the backend and stop the vacuuming each 30 minutes, and I try again typing vacuum, but im by the number 890271/100 and its too slow!, i dont know what else to do to make it faster. Per the error message you need to VACUUM the 'postgres' database. Either connect to it with psql and run VACUUM or use the command line utility vacuumdb as follows: vacuumdb -U postgres postgres Where -U is the superuser usually postgres. -- Adrian Klaver adrian.kla...@gmail.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] UUID column as pimrary key?
I haven't been able to find anywhere, easily, in the documentation using google where a list of allowed data types for primary keys is. So, UUIDs can be primary keys? Any issues wtih them on sorting or paging of index tables, etc.? Also, the documentation says that UUIDs are 128 bit value, but never explicitly says that's how it's stored. Nor does it use one of the nice, blue headered tables for UUID (or ENUM) showing storage and other attributes as it does for numeric, character,boolean, date/time, binary, monetary, geometric, or network types. Dennis Gearon Signature Warning It is always a good idea to learn from your own mistakes. It is usually a better idea to learn from others’ mistakes, so you do not have to make them yourself. from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036' EARTH has a Right To Life, otherwise we all die. -- 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] UUID column as pimrary key?
On Tue, 2011-01-04 at 11:07 -0800, Dennis Gearon wrote: > I haven't been able to find anywhere, easily, in the documentation using > google > where a list of allowed data types for primary keys is. Anything that can be UNIQUE NOT NULL > > So, UUIDs can be primary keys? Yes. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] UUID column as pimrary key?
We're using UUID for primary keys in PG 8.4 without any issues. I have no real insights into the details or performance issues, but always figured it was stored as a binary 128-bit value, but with added benefits of being able to enter and view them using a standard string format. We don't sort them as they have no real meaning for us. On 1/4/2011 11:07 AM, Dennis Gearon wrote: I haven't been able to find anywhere, easily, in the documentation using google where a list of allowed data types for primary keys is. So, UUIDs can be primary keys? Any issues wtih them on sorting or paging of index tables, etc.? Also, the documentation says that UUIDs are 128 bit value, but never explicitly says that's how it's stored. Nor does it use one of the nice, blue headered tables for UUID (or ENUM) showing storage and other attributes as it does for numeric, character,boolean, date/time, binary, monetary, geometric, or network types. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Como usar interval con un variable
Hola gente: Estoy haciendo un funcion que recibe como parametro fechainicial, fechafin, horainicial,horafin, y frecuencia. Esta funcion debe generar fechas que esten dentro del rango de fechas que paso como parametros, y por cada fecha, debe generar un rango de horas con minutos, pero segun la frecuencia. Bueno vamos a lo que me interesa,para generar las horas uso la frecuencia para ir incrementando la hora, este es un pedazo de codigo: por ej: frec=5; -- declarado como integer v_horaini=8 --variable declarada como timestamp v_horafin= 12 -- variable declarada como timestamp v_hora1:=v_horaini; WHILE v_hora1<=v_horafin LOOP v_hora_actual:=v_hora1; v_hora1:=v_hora1 + interval || ' '' ' || frec || ' minute '' '; --***Aca me genera error END LOOP; Porque me genera error? y como podria ir haciendo para que v_hora se vaya incrementando segun la frecuencia, en el caso del ejemplo seria: 8, 8:05, 8:10,etc Saludos y gracias
Re: [GENERAL] Default to pager off
Hello, Dnia Tue, Jan 04, 2011 at 03:05:03PM -0600, Michael Satterwhite napisał: > I'm not fond of the pager function. Is there a way to tell postgresql to > either default to '\pset pager off' or to have it startup with the pager > turned > off? >From `psql --help': -P VAR[=ARG]set printing option VAR to ARG (see \pset command) -- Dominik Żyła -- 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] Default to pager off
Dominik =?utf-8?B?xbt5xYJh?= writes: > Dnia Tue, Jan 04, 2011 at 03:05:03PM -0600, Michael Satterwhite napisaÅ: >> I'm not fond of the pager function. Is there a way to tell postgresql to >> either default to '\pset pager off' or to have it startup with the pager >> turned >> off? > From `psql --help': > -P VAR[=ARG]set printing option VAR to ARG (see \pset command) Setting it off in ~/.psqlrc would likely be more convenient. 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: [GENERAL] Como usar interval con un variable
Hola, hay una portal en espanol sobre postgres con listas de correo y IRC: http://www.postgresql-es.org/comunidad Aqui hablamos ingles. Sorry. saludos desde Alemana. Matthias Am Dienstag, 4. Januar 2011, um 21:26:33 schrieb liliana lazarte: > Hola gente: > Estoy haciendo un funcion que recibe como parametro fechainicial, fechafin, > horainicial,horafin, y frecuencia. Esta funcion debe generar fechas que > esten dentro del rango de fechas que paso como parametros, y por cada > fecha, debe generar un rango de horas con minutos, pero segun la > frecuencia. Bueno vamos a lo que me interesa,para generar las horas uso la > frecuencia para ir incrementando la hora, este es un pedazo de codigo: > > por ej: > frec=5; -- declarado como integer > v_horaini=8 --variable declarada como timestamp > v_horafin= 12 -- variable declarada como timestamp > > v_hora1:=v_horaini; > WHILE v_hora1<=v_horafin LOOP > v_hora_actual:=v_hora1; > v_hora1:=v_hora1 + interval || ' '' ' || frec || ' minute '' '; > --***Aca me genera error > END LOOP; > > Porque me genera error? y como podria ir haciendo para que v_hora se vaya > incrementando segun la frecuencia, en el caso del ejemplo seria: 8, 8:05, > 8:10,etc > Saludos y gracias -- 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] UUID column as pimrary key?
d.w...@computer.org (David Wall) writes:
> We're using UUID for primary keys in PG 8.4 without any issues. I
> have no real insights into the details or performance issues, but
> always figured it was stored as a binary 128-bit value, but with added
> benefits of being able to enter and view them using a standard string
> format. We don't sort them as they have no real meaning for us.
In principle, this might be a reason to want to do the long-outstanding
work on hash indexes; with UUIDs, it mayn't be useful to sort the
values, but you *do* want to be able to validate that they're unique.
--
output = ("cbbrowne" "@" "gmail.com")
http://www3.sympatico.ca/cbbrowne/
"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] seg fault crashed the postmaster
I'm putting this on this thread, since it could be related to the issue. I'm now seeing this in the log on the HSB/SR server. It's happened about 4 times in the past 2 days. 23964 2011-01-04 05:23:00 EST [47]LOG: invalid record length at 6E53/46E8A010 23535 2011-01-04 05:23:00 EST [2]FATAL: terminating walreceiver process due to administrator command cp: cannot stat `/data23/wal_sync/00016E530046': No such file or directory 23964 2011-01-04 05:23:00 EST [48]LOG: invalid record length at 6E53/46E8A010 cp: cannot stat `/data23/wal_sync/00016E530046': No such file or directory 2486 2011-01-04 05:23:00 EST [1]LOG: streaming replication successfully connected to primary 23964 2011-01-04 15:47:59 EST [49]LOG: invalid record length at 6E6B/F8222010 2486 2011-01-04 15:47:59 EST [2]FATAL: terminating walreceiver process due to administrator command cp: cannot stat `/data23/wal_sync/00016E6B00F8': No such file or directory 23964 2011-01-04 15:48:00 EST [50]LOG: invalid record length at 6E6B/F8222010 cp: cannot stat `/data23/wal_sync/00016E6B00F8': No such file or directory The word FATAL sounds very ominous. -- View this message in context: http://postgresql.1045698.n5.nabble.com/seg-fault-crashed-the-postmaster-tp3323117p3328138.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
Re: [GENERAL] How to build plperl with PostgreSQL 9 on Windows
On Tue, Jan 4, 2011 at 10:12, tuanhoanganh wrote:
> I has set config.pl to
> $config->{perl};
> 1;
I don't know anything about the windows build system, but shouldn't
that be something like:
$config->{perl} = 1; ?
--
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] How to build plperl with PostgreSQL 9 on Windows
Thanks for answer me but it have error
Detected Visual Studio version 8.00
Detected hardware platform: Win32
Generating win32ver.rc for src\backend
Building src\pl\plperl\SPI.c...
'1' is not recognized as an internal or external command,
operable program or batch file.
Failed to create SPI.c.
Please help me.
Tuan Hoang ANh
On Wed, Jan 5, 2011 at 9:14 AM, Alex Hunsaker wrote:
> On Tue, Jan 4, 2011 at 10:12, tuanhoanganh wrote:
> > I has set config.pl to
> > $config->{perl};
> > 1;
>
> I don't know anything about the windows build system, but shouldn't
> that be something like:
> $config->{perl} = 1; ?
>
Re: [GENERAL] How to build plperl with PostgreSQL 9 on Windows
On Tue, Jan 4, 2011 at 19:27, tuanhoanganh wrote:
> Thanks for answer me but it have error
>
> Detected Visual Studio version 8.00
> Detected hardware platform: Win32
> Generating win32ver.rc for src\backend
> Building src\pl\plperl\SPI.c...
> '1' is not recognized as an internal or external command,
Instead of 1, try the path to the perl directory (such that
$config->{'perl'} . "\bin\perl" is the perl binary). For example:
$config->{perl} = "C:\strawberry\perl\";
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Disable Postgresql startup on boot (Windows XP)
Hi. Everytime I boot Windows XP a number of postgres.exe services start and run in the background. As I am only an occasional user of postgresql on my laptop I was wondering how (a) I could disable the auto-start of the service on boot and (b) how to manually start/stop postgresql when needed? Thanks. Ken -- 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] How to build plperl with PostgreSQL 9 on Windows
Thanks you very much
Here is my config.pl
$config->{perl} = "c:\\perl";
1;
Strawberry Perl don't work because It don't have perl lib in
c:\Perl\lib\CORE\.
I swiched to ActivePerl to build plperl.
Tuan Hoang ANh
On Wed, Jan 5, 2011 at 9:52 AM, Alex Hunsaker wrote:
> On Tue, Jan 4, 2011 at 19:27, tuanhoanganh wrote:
> > Thanks for answer me but it have error
> >
> > Detected Visual Studio version 8.00
> > Detected hardware platform: Win32
> > Generating win32ver.rc for src\backend
> > Building src\pl\plperl\SPI.c...
> > '1' is not recognized as an internal or external command,
>
> Instead of 1, try the path to the perl directory (such that
> $config->{'perl'} . "\bin\perl" is the perl binary). For example:
> $config->{perl} = "C:\strawberry\perl\";
>
Re: [GENERAL] Disable Postgresql startup on boot (Windows XP)
On 1/4/2011 10:25 PM, Ken wrote: Hi. Everytime I boot Windows XP a number of postgres.exe services start and run in the background. As I am only an occasional user of postgresql on my laptop I was wondering how (a) I could disable the auto-start of the service on boot and (b) how to manually start/stop postgresql when needed? Thanks. Ken In the Services MMC Console (Control Panel -> Administrative Tools -> Services I think, it's been a while) you can change the startup type to "Manual" instead of "Automatic", then the service will only run when told to. After doing that, you can create batch files that use the NET START and NET STOP commands (you'll have to find out the service name from the above step to use them). Then run these batch files when you to start and stop the PostgreSQL service (or just type the commands in the command-line each time I suppose). -- Stephen -- 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] Disable Postgresql startup on boot (Windows XP)
On 01/04/11 7:25 PM, Ken wrote: Hi. Everytime I boot Windows XP a number of postgres.exe services start and run in the background. As I am only an occasional user of postgresql on my laptop I was wondering how (a) I could disable the auto-start of the service on boot and (b) how to manually start/stop postgresql when needed? A) go into Control Panel -> Administrative Tools -> Computer Managment -> Services, find the postgresql service, and set its start type to 'manual' B) either, go to the same place above, and right click->start (or stop), or at a command line shell prompt run the commands NET START postgresql-8.4 or NET STOP postgresql-8.4 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
