Re: [GENERAL] POLL: Women-sized t-shirts for PostgreSQL
On Fri, 8 Feb 2008 21:48:39 -0300 Alvaro Herrera [EMAIL PROTECTED] wrote: Hmm, did this go anywhere? I still look for the shiny new Pg polos on the mail every day, only to be disappointed. Yes, they will be part of the larger run of shirts we do after SCALE. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit signature.asc Description: PGP signature
Re: [GENERAL] standby questions
On Feb 9, 2008 5:50 AM, Greg Smith [EMAIL PROTECTED] wrote:
On Fri, 8 Feb 2008, David Wall wrote:
Does pg_standby take care of this by checking file sizes or the like? In my
testing with scp, we never experienced any problems, but I wonder if we were
somehow just lucky.
pg_standby only processes files of exactly the length they're supposed to
be. On Windows it even sleeps a bit after that to give time for things to
settle.
The main risky situation you could end up in is if you were using a copy
program that created the whole file at its full size first then wrote the
data to it. I don't think there are many programs that operate like that
around and certainly scp doesn't do that.
atomic tool:
The reason rsync is used in the archive_command is that rsync features
an 'atomic copy' - that is, the in-progress destination file is
created as a temp file, and then renamed when the copy is complete. In
the situation above, where segments are archived straight to the
directory that the slave reads from, 'cp' can cause an error whereby
the slave attempts to process a partially-copied WAL segment. If this
happens, postgres will emit an error like:
PANIC: archive file 00010031 has wrong size:
1810432 instead of 16777216
LOG: startup process (PID 11356) was terminated by signal 6
LOG: aborting startup due to startup process failure
taken from http://archives.postgresql.org/sydpug/2006-10/msg1.php
thanks everybody!!
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] standby questions
Hi Roberto, -a WAL segment file is the same that a log file segment? A WAL (Write Ahead Log) file is one of the numbered files in the pg_xlog directory. Keep in mind that you'll be archiving (in some cases) more than just WAL files, for example you might see other files appear in your archive directory when you do a PITR backup. -what are the log file segments referenced by checkpoint_segments? are the binary logs where postgres stores lasts transactions, the ones in $DATA/pg_xlog dir? if this is true, then: what means Maximum distance between automatic WAL checkpoints??? this is how often, in log file segments, postgres will perform a checkpoint, generating a special checkpoint record from which to start the redo operation, dont? A single WAL file isn't indicative of a checkpoint. Rather, PostgreSQL will fill checkpoint segments WAL files and then checkpoint (in normal operation - though there are exceptions to this). So if checkpoint_segments is set to 3, then you'll see 48 MB of WAL files be generated prior to a checkpoint (3 * 16 MB WAL files). In the event of a crash, if PostgreSQL auto-recovers (not the warm-standby server taking over, the same instance that crashed re-starting) then it processes from the last checkpoint forward. Simply put, the WAL files contain the differences between what is in the PostgreSQL shared buffer pool and what is in the files that represent the database on disk. When a checkpoint occurres, the buffers sync to disk, so there are no differences between what is in memory and on disk (so the old WAL files would then be obsolete for automatic crash recovery, and a new set can be started). -what is the restartpoint named in the Warm Standby Servers for High Availability page? (http://www.postgresql.org/docs/8.2/static/warm-standby.html) i cant find a definition in any other doc... The restart point would be the time when you issued a pg_start_backup() to take a PITR backup of your main server. -how often a new WAL file is generated? this depends on the server load? Yes. Server load and checkpoint timeout values would influence this. Also any manual file switches that you do... -in one WAL file i could have one, more than one and even an incomplete transaction?? (where is the doc i need to read?!) When replay occurs only completed transactions will be replayed. Of course, transactions won't span a checkpoint in the WAL files. -if i have incomplete transactions on a WAL, how the standby server processes that? what if the rest of the transaction never reaches the standby server? Any transactions that are not completed will be discarded. -how do i know exactly at which point in time (in transactions) my standby server is, if i have to switch to primary role? You really don't. You can read your log files to find the last WAL that has been replayed, and if you know the last WAL generated on the primary (pg_controldata) then you should be able to figure out how many WAL files away you are. There are techniques to avoid the loss of any WAL files in the event of a crash (synchronous warm standby), that we teach in our performance tuning course...but I'm sure you can find directions on how to implement these online someplace. -how many archive files is safe to keep in the standby server? right now, i have -k 100 in the pg_standby opts but in the pg_standby README says: You should be wary against setting this number too low, since this may mean you cannot restart the standby. This is because the last restartpoint marked in the WAL files may be many files in the past and can vary considerably. This should be set to a value exceeding the number of WAL files that can be recovered in 2*checkpoint_timeout seconds, according to the value in the warm standby postgresql.conf. It is wholly unrelated to the setting of checkpoint_segments on either primary or standby. This number would indicate the number of files you need to keep to ensure that if recovery is re-started it can continue successfully. I believe this is the number of WAL files since the last checkpoint, but if that is so it is a variable value. That's because a long-running transaction could end up exceeding checkpoint_segments, in which case PostgreSQL will exceed that number. The easiest thing is to use the '%r' parameter so PostgreSQL can tell pg_standby what to do If not, I would just leave it at 0 and periodically prune old files. I'm not 100% certain on this (but it certainly makes logical sense). I don't use pg_standby, I typically use a shell script to do this...which affords me a greater deal of customization. The main advantage to pg_standby is that it's a more-or-less hands off approach that is cross-platform compatible (Windows and Unix variants). Signalling components could be added to pg_standby at some point... i cant use the %r because im in 8.2 and not thinking in upgrade by now... this is related to the restartpoint in
Re: [GENERAL] standby questions
On Sat, 2008-02-09 at 07:49 -0500, Chander Ganesan wrote: Signalling components could be added to pg_standby at some point... What sort of thing are you looking for? pg_standby accepts a trigger file as well as various types of signal. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] standby questions
Simon Riggs wrote: On Sat, 2008-02-09 at 07:49 -0500, Chander Ganesan wrote: Signalling components could be added to pg_standby at some point... What sort of thing are you looking for? pg_standby accepts a trigger file as well as various types of signal I didn't see anything about signals in the documentation at (http://www.postgresql.org/docs/8.3/static/pgstandby.html). We use signals in shell scripts to trigger (in some cases) a WAL copy, and in others an immediate wake-from-sleep to copy a final WAL file to trigger a failover. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com
Re: [GENERAL] Empty to NULL conversion - Ruby - Postgres ?
Steve/Jeff, Thanks for your replies. I am using the latest pg module but I don't know if there is any way to handle this without SQL. I am manually taking care of it using SQL functions to convert empty strings to NULL. It would be nice if there is a setting that could take care of this issue. PS: This issue is similar to COPY where by default is considered empty and not NULL and so you can't load this data into a NUMERIC data type defined as NULL. COPY has a WITH NULL AS syntax which addresses this issue. On Feb 5, 2008 5:47 PM, Jeff Davis [EMAIL PROTECTED] wrote: On Fri, 2008-02-01 at 20:33 -0500, Venks wrote: Hi, I am trying to copy some data from MySQL to Postgres using Ruby. This is NOT a MySQL to PostgreSQL conversion project. I need to read the data from a MySQL database and load it into PostgreSQL database. How do I handle nil in ruby and convert them into NULL so that I can insert NULL into INT columns instead of empty values? In short I am getting the following popular error: Can you install the latest module from the ruby-pg project (or just gem install pg). If you have this problem still, send an email to the ruby-pg-general mailing list (or post in the forum, or email me directly). Regards, Jeff Davis ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Is PG a moving target?
I acknowledge that from time to time we must accept changes in the 3rd party software that will break our apps if we (or customers) ever upgrade them (a compounded issue if we have heavily-used deployments in the field and not just in-house ones to maintain). But given the recent and dramatic example of 8.3's on-by-default stricter typing in functions (now not-autocasting), I worry that kind of change could happen in every minor version (8.4 etc). Sure the strict-typing (and other compatibility-breaking changes) is a good thing in the long run, but it discourages anyone trying to: a) port apps from another database b) upgrade PG to get other features, or port apps written against from a PG version that's 1 year older The type-strictness change, as an example, also creates pragmatic vs academic (polarizing) debates around rtrim(intype) being innocuous vs sloppy. And database XYZ is better/worse, e.g balance of ease of use, TCO, vs ACID, strictness etc). The word 'balance' is key. Is there anything now, or in the works, for compatibility emulation? For example to setup my session to act like 8.2 and allow less-strict typing. Or can one write an app against 8.3 and safely assume that 8.4 *could* also add more behavior changes (e.g even more strict-ness in functions where even 8.3 could be *validly argued* as being too loose)?... Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is PG a moving target?
Ken, * Ken Johanson ([EMAIL PROTECTED]) wrote: But given the recent and dramatic example of 8.3's on-by-default stricter typing in functions (now not-autocasting), I worry that kind of change could happen in every minor version (8.4 etc). 8.3 isn't a minor version. Enjoy, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Is PG a moving target?
Stephen Frost wrote: * Ken Johanson ([EMAIL PROTECTED]) wrote: But given the recent and dramatic example of 8.3's on-by-default stricter typing in functions (now not-autocasting), I worry that kind of change could happen in every minor version (8.4 etc). 8.3 isn't a minor version. PG uses a different versioning system than this one?: http://en.wikipedia.org/wiki/Software_versioning#Numeric Or do you mean the changes are not minor? :-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is PG a moving target?
Ken Johanson wrote: Stephen Frost wrote: * Ken Johanson ([EMAIL PROTECTED]) wrote: But given the recent and dramatic example of 8.3's on-by-default stricter typing in functions (now not-autocasting), I worry that kind of change could happen in every minor version (8.4 etc). 8.3 isn't a minor version. PG uses a different versioning system than this one?: http://en.wikipedia.org/wiki/Software_versioning#Numeric Or do you mean the changes are not minor? :-) Yes, we use the one stated on our site, not wikipedia ;) See: http://www.postgresql.org/support/versioning It's also in our press FAQ (http://www.postgresql.org/about/press/faq), but I can see how that's not the most natural place to look for it... //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Is PG a moving target?
Magnus Hagander wrote: PG uses a different versioning system than this one?: http://en.wikipedia.org/wiki/Software_versioning#Numeric Or do you mean the changes are not minor? :-) Yes, we use the one stated on our site, not wikipedia ;) See: http://www.postgresql.org/support/versioning Thank you, I understand now. A major release is numbered by increasing either the first or second part of the version number, e.g. 8.1 to 8.2. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Is PG a moving target?
Ken Johanson [EMAIL PROTECTED] writes: Is there anything now, or in the works, for compatibility emulation? Sure: keep using the same major release. This is one of the reasons that we keep updating back release branches for so long. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is PG a moving target?
On Sat, Feb 09, 2008 at 10:54:38AM -0700, Ken Johanson wrote: Magnus Hagander wrote: PG uses a different versioning system than this one?: http://en.wikipedia.org/wiki/Software_versioning#Numeric Or do you mean the changes are not minor? :-) Yes, we use the one stated on our site, not wikipedia ;) See: http://www.postgresql.org/support/versioning Thank you, I understand now. A major release is numbered by increasing either the first or second part of the version number, e.g. 8.1 to 8.2. Josh has a great write up explenation as well http://blogs.ittoolbox.com/database/soup/archives/guide-to-postgresql-version-numbers-19177 -- Curtis Gallant [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Is PG a moving target?
Ken Johanson wrote: Magnus Hagander wrote: PG uses a different versioning system than this one?: http://en.wikipedia.org/wiki/Software_versioning#Numeric Or do you mean the changes are not minor? :-) Yes, we use the one stated on our site, not wikipedia ;) See: http://www.postgresql.org/support/versioning Thank you, I understand now. A major release is numbered by increasing either the first or second part of the version number, e.g. 8.1 to 8.2. Good. That's not to say that your concerns aren't valid, btw. To answer your original question, I haven't heard of a way to make it act like 8.2 wrt the casting, because most people feel it's better to fix the issues in the application than to apply band-aid. And yes, similar things may happen for 8.4, but there's nothing out there yet that we *know* will make such a change. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] pg_restore seems slow
I'm trying to restore my database from 8.26 into 8.3 (win32) but find the process to be exceedingly slow. The database has about 60M records. I realize there will be differences based on hardware, available memory, complexity of records but when I first tried a restore with the verbose option I was able to calculate based on the index incrementing that it was inserting about 6500 records per minute. At that rate it would take 153 hours to restore my db. I then tried minimizing the verbosity window and would open it only after a minute and the speed was improved to about 2 records per minute. I'm hoping without the verbose option that the speed increases to at least 20 records per minute which would be a fairly reasonable 5 hours. So is there any way besides using verbose to calculate the speed at which pg_restore is inserting records? It would be great to have a 'progress' option so that a person could time going out for a sail in the morning and then return at just the right time. Guess you know what I'd rather be doing instead of staring at the command prompt :) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is PG a moving target?
On Sat, 09 Feb 2008 10:20:51 -0700 Ken Johanson [EMAIL PROTECTED] wrote: I acknowledge that from time to time we must accept changes in the 3rd party software that will break our apps if we (or customers) ever upgrade them (a compounded issue if we have heavily-used deployments in the field and not just in-house ones to maintain). But given the recent and dramatic example of 8.3's on-by-default stricter typing in functions (now not-autocasting), I worry that kind of change could happen in every minor version (8.4 etc). 8.4 is a major release. 8.3.1 would be a minor release. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit signature.asc Description: PGP signature
[GENERAL] fsync=off shutdown = crash?
Hello, I was reading the docuementation for 8.3 (http://www.postgresql.org/docs/8.3/static/wal-async-commit.html) and it states An immediate-mode shutdown is equivalent to a server crash, and will therefore cause loss of any unflushed asynchronous commits.. Does this mean that doing a shutdown (centos: /sbin/service postgresql stop) will be considered a crash? Benjamin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] fsync=off shutdown = crash?
Benjamin Arai [EMAIL PROTECTED] writes: I was reading the docuementation for 8.3 (http://www.postgresql.org/docs/8.3/static/wal-async-commit.html) and it states An immediate-mode shutdown is equivalent to a server crash, and will therefore cause loss of any unflushed asynchronous commits.. Does this mean that doing a shutdown (centos: /sbin/service postgresql stop) will be considered a crash? Not unless the initscript uses an immediate-mode shutdown signal, which would be a fairly unfriendly thing for it to do. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Set server behaviors on a per-session basis?
(re-post, last seemed not to get on-list) Hi all, In the link below we asked if it was possible to set, in this case, the standard_conforming_strings behavior, on a per-connection basis. http://www.grokbase.com/topic/2006/07/27/backslash-as-ordinary-char-vs-not-set-via-a-connection-session-variable/PwunvQ3dQxAVOpprZ606aKELsYU The responses were good to see -- the ability to set it on a role or database context is great.. however in some hosting / shared environments, one-role per behavior isn't always practical, even though it's arguably the ideal approach. A first question: will the current server design easily allow some change that lets us set behaviors (like standard_conforming_strings) on a per-connection basis? Or are per-connection behaviors a substantial rework? A second question: is support for a general notion of behavior on the roadmap for any of the three contexts (roles, database, sessions)? Not just standard_conforming_strings, but both conformant and non behaviors. Say accepting relaxed typing (quoted ints etc), implicit AS-keyword in SELECT lists (someday and in sacrifice of regclass), etc. Even to allow newer PGs to behave as old ones. These two features would ease migration *from* non-compliant/3rd party DBs -- and also to ease PG itself *into* standard-compliant modes (e.g 'implicit AS-keyword in SELECT lists'). Also may allow some blackbox apps to migrate from 3rd party DBs. -Ken ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] help optimizing query
Hi all, The situation: there are users in one table, and their access statistics in the other. Now I want to find users whose last access time was more than one month ago. As I've only had to write quite simple queries involving no sub-selects so far, I'd like to ask your opinion if this one scales at all or not. SELECT u.login,last_use_time FROM users u JOIN (SELECT user_id, MAX(stop_time) AS last_use_time FROM stats GROUP BY user_id) AS s ON (u.id=s.user_id) WHERE status='3' AND next_plan_id IS NULL AND last_use_time now() - interval '1 month' ORDER BY last_use_time; It seems to do the job, but how good is it in the long run? Any way I could tweak it? Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [OT] advanced database design (long)
In article [EMAIL PROTECTED], Alex Turner [EMAIL PROTECTED] wrote: % % Im not a database expert, but wouldn't % % create table attribute ( % attribute_id int % attribute text % ) % % create table value ( % value_id int % value text % ) % % create table attribute_value ( % entity_id int % attribute_id int % value_id int % ) % % give you a lot less pages to load than building a table with say 90 columns % in it that are all null, which would result in better rather than worse % performance? Suppose you want one row of data. Say it's one of the ones where the columns aren't all nulls. You look up 90 rows in attribute_value, then 90 rows in attribute, then 90 rows in value. You're probably looking at 3-6 pages of index data, and then somewhere between 3 and 270 pages of data from the database, for one logical row of data. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [OT] advanced database design (long)
On Feb 3, 2008 11:14 PM, Alex Turner [EMAIL PROTECTED] wrote: Im not a database expert, but wouldn't create table attribute ( attribute_id int attribute text ) create table value ( value_id int value text ) create table attribute_value ( entity_id int attribute_id int value_id int ) give you a lot less pages to load than building a table with say 90 columns in it that are all null, which would result in better rather than worse performance? Definitely not. 90 null values will require about 12 bytes of memory to represent their absence in the all in one table. That's not very much space. In contrast, if you need to join out to 80 tables, possibly folded into some smaller number, you'll *at least* have an index scan, reading a few pages of data from the secondary table, and then need to read the pages containing those values that *are* joined in. That quickly grows to way more than 12 bytes :-) -- http://linuxfinances.info/info/linuxdistributions.html The definition of insanity is doing the same thing over and over and expecting different results. -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Continual uptime while loading data ... COPY vs INSERTS within a transaction.
On Feb 9, 2008 6:30 PM, Benjamin Arai [EMAIL PROTECTED] wrote: Hello, We are running a system which requires continual uptime while loading data. Currently one particular table receives a large number of inserts per commit (about 1 inserts). This process works well allowing both end users to access the data as well as loading reasonably quickly. We are thinking of modifying our system to use COPY to replace these large INSERT transactions but we are concerned that it will greatly impact the user experience (i.e., exclusively lock the table during the copy process). First, does COPY grab an exclusive lock? Second, is there a better way to load data? No, COPY does not take an exclusive lock, so this optimization should be a helpful one. COPY has been fairly regularly enhanced over the last few years to make it faster, and there is no reason to think that this progression is ending at PG 8.3, so this should indeed be a near-optimal way to load data. -- http://linuxfinances.info/info/linuxdistributions.html The definition of insanity is doing the same thing over and over and expecting different results. -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Continual uptime while loading data ... COPY vs INSERTS within a transaction.
Benjamin Arai [EMAIL PROTECTED] writes: We are thinking of modifying our system to use COPY to replace these large INSERT transactions but we are concerned that it will greatly impact the user experience (i.e., exclusively lock the table during the copy process). First, does COPY grab an exclusive lock? Second, is there a better way to load data? No, and no. Use COPY. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Continual uptime while loading data ... COPY vs INSERTS within a transaction.
Hello, We are running a system which requires continual uptime while loading data. Currently one particular table receives a large number of inserts per commit (about 1 inserts). This process works well allowing both end users to access the data as well as loading reasonably quickly. We are thinking of modifying our system to use COPY to replace these large INSERT transactions but we are concerned that it will greatly impact the user experience (i.e., exclusively lock the table during the copy process). First, does COPY grab an exclusive lock? Second, is there a better way to load data? Benjamin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
On Feb 4, 2008, at 11:31 AM, Joshua D. Drake wrote: I don't agree in the least, I was actually going to suggest we add a new one for relational design questions. I like many lists that are contextually specific. IMO, general should be removed for example. I think this makes sense for a web-based forum, not for mailing lists to which you need to subscribe (and in my case set up auto-filers to move the stuff out of my inbox). Joshua D. Draek Is this your alternete evil twin? ;-) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
Lewis Cunningham [EMAIL PROTECTED] writes: If a person is interested in all the groups, is it hard to subscribe? No. If all groups are in one, is it hard to filter out? Yes. Some people like to filter PG mail into different folders for different lists, so that they can read with more focus. That would get significantly harder if we merged the lists into just a couple. On the other hand, if you see the lists as one big discussion, you can have them all arrive in one folder (and set your subscription to filter dups from cross-posted messages). I happen to fall in the latter camp but I don't want to make life hard for the former camp, especially not when it wouldn't really buy anything for me. I agree with the original complaint about not creating new lists without significant evidence that one is needed, but that doesn't translate into wanting to smash everything down to a couple of lists. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Set server behaviors on a per-session basis?
Ken Johanson [EMAIL PROTECTED] writes: A first question: will the current server design easily allow some change that lets us set behaviors (like standard_conforming_strings) on a per-connection basis? See the SET command. Or maybe you should read the overview here: http://www.postgresql.org/docs/8.3/static/config-setting.html regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Where is the system-wide psqlrc on RHEL4?
I've tried various places, and none seem to work. I've even done a strings `which psql` | grep psqlrc to no avail. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] help optimizing query
It seems to do the job, but how good is it in the long run? Any way I could tweak it? I think this form will work the best: SELECT u.login, MAX(s.stop_time) AS last_use_time FROM users u, stats s WHERE u.id=s.user_id AND u.status='3' AND u.next_plan_id IS NULL GROUP BY u.login HAVING MAX(s.stop_time) (now() - interval '1 month') ORDER BY last_use_time; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where is the system-wide psqlrc on RHEL4?
Dean Gibson (DB Administrator) [EMAIL PROTECTED] writes: I've tried various places, and none seem to work. I've even done a strings `which psql` | grep psqlrc to no avail. pg_config --sysconfdir would tell you. I agree the documentation on this is less than clear. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Where is the system-wide psqlrc on RHEL4?
-bash: pg_config: command not found It's not installed in the base/server/libs RPMs. I had to search the uninstalled PostgreSQL RPMs for it, and then (temporarily) install the devel RPM to run it. For CentOS 4.4 RHEL4, the system-wide psqlrc is in /etc/sysconfig/pgsql/ -- Dean On 2008-02-09 18:45, Tom Lane wrote: Dean Gibson (DB Administrator) [EMAIL PROTECTED] writes: I've tried various places, and none seem to work. I've even done a strings `which psql` | grep psqlrc to no avail. pg_config --sysconfdir would tell you. I agree the documentation on this is less than clear. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Lockless pg_buffercache
Hi,
I want to use the pg_buffercache contrib module for monitoring our
server. It takes a lock on all buffers and then on each buffer header
in order to get a consistent picture of the buffers. I would be
running the function provided by the module once every 5 minutes.
I'm worrying about the performance hit of that - a comment in the code
says it's horrible for concurrency. Additionally, as I don't use this
for debugging, but just for monitoring, I don't need a 100% consistent
picture, just rough numbers how much of the buffer cache is used for
what relation.
Does removing all locking as in the attached patch have any negative
impact other than the non-consistency of the results?
Thanks
Markus
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index b1c3fbc..fe5c880 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -108,23 +108,11 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
MemoryContextSwitchTo(oldcontext);
/*
- * To get a consistent picture of the buffer state, we must lock all
- * partitions of the buffer map. Needless to say, this is horrible
- * for concurrency. Must grab locks in increasing order to avoid
- * possible deadlocks.
- */
- for (i = 0; i NUM_BUFFER_PARTITIONS; i++)
- LWLockAcquire(FirstBufMappingLock + i, LW_SHARED);
-
- /*
* Scan though all the buffers, saving the relevant fields in the
* fctx-record structure.
*/
for (i = 0, bufHdr = BufferDescriptors; i NBuffers; i++, bufHdr++)
{
- /* Lock each buffer header before inspecting. */
- LockBufHdr(bufHdr);
-
fctx-record[i].bufferid = BufferDescriptorGetBuffer(bufHdr);
fctx-record[i].relfilenode = bufHdr-tag.rnode.relNode;
fctx-record[i].reltablespace = bufHdr-tag.rnode.spcNode;
@@ -142,19 +130,7 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
fctx-record[i].isvalid = true;
else
fctx-record[i].isvalid = false;
-
- UnlockBufHdr(bufHdr);
}
-
- /*
- * And release locks. We do this in reverse order for two reasons:
- * (1) Anyone else who needs more than one of the locks will be trying
- * to lock them in increasing order; we don't want to release the
- * other process until it can get all the locks it needs. (2) This
- * avoids O(N^2) behavior inside LWLockRelease.
- */
- for (i = NUM_BUFFER_PARTITIONS; --i = 0;)
- LWLockRelease(FirstBufMappingLock + i);
}
funcctx = SRF_PERCALL_SETUP();
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] pg_restore seems slow
On Feb 9, 2008 10:42 AM, Willem Buitendyk [EMAIL PROTECTED] wrote: I'm trying to restore my database from 8.26 into 8.3 (win32) but find the process to be exceedingly slow. The database has about 60M records. I realize there will be differences based on hardware, available memory, complexity of records but when I first tried a restore with the verbose option I was able to calculate based on the index incrementing that it was inserting about 6500 records per minute. At that rate it would take 153 hours to restore my db. I then tried minimizing the verbosity window and would open it only after a minute and the speed was improved to about 2 records per minute. I'm hoping without the verbose option that the speed increases to at least 20 records per minute which would be a fairly reasonable 5 hours. So is there any way besides using verbose to calculate the speed at which pg_restore is inserting records? It would be great to have a 'progress' option so that a person could time going out for a sail in the morning and then return at just the right time. Guess you know what I'd rather be doing instead of staring at the command prompt :) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq By any chance, are you using -d or -D option while doing pg_dump? Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad 18° 32' 57.25N, 73° 56' 25.42E - Pune 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco * http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [GENERAL] Lockless pg_buffercache
Markus Bertheau [EMAIL PROTECTED] writes: Does removing all locking as in the attached patch have any negative impact other than the non-consistency of the results? Removing the LockBufHdr bit is definitely a pretty foolish idea. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] help optimizing query
On Feb 9, 2008 8:04 PM, Adam Rich [EMAIL PROTECTED] wrote: It seems to do the job, but how good is it in the long run? Any way I could tweak it? I think this form will work the best: SELECT u.login, MAX(s.stop_time) AS last_use_time FROM users u, stats s WHERE u.id=s.user_id AND u.status='3' AND u.next_plan_id IS NULL If only ba small number of fields have next_plan as null, an they correlate to the status normally, then an index on state where next_plan_id is null might help here. GROUP BY u.login HAVING MAX(s.stop_time) (now() - interval '1 month') ORDER BY last_use_time; ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
