[GENERAL] Parallel DB architechture
Hello, I would like to implement parallel DB Architecture in postgres.I came across various parallel architectures shared memory,shared disk,shared nothing,hierarchical.But I am unaware of how to implement one of these in real time system.Can you guys help me in this aspect?
[GENERAL] pgstattuple triggered checkpoint failure and database outage?
I just had a brief outage on a production server running 8.3.6, which I suspect was triggered by me running a table bloat report making lots of pgstattuple calls. The first I got of it was the script I'd just kicked off died: could not open segment 1 of relation 1663/16409/11088101 (target block 131292): No such file or directory CONTEXT: writing block 131292 of relation 1663/16409/11088101 More alerts came in - looks like everything was failing with similar errors. Checking the logs the first indication of the problem is: @:6160 2009-03-30 06:49:27 BST LOG: checkpoint starting: time [...] @:6160 2009-03-30 06:49:58 BST ERROR: could not open segment 1 of relation 1663/16409/11088101 (target block 131072): No such file or directory @:6160 2009-03-30 06:49:58 BST CONTEXT: writing block 131072 of relation 1663/16409/11088101 @:6160 2009-03-30 06:49:59 BST LOG: checkpoint starting: time @:6160 2009-03-30 06:49:59 BST ERROR: could not open segment 1 of relation 1663/16409/11088101 (target block 134984): No such file or directory @:6160 2009-03-30 06:49:59 BST CONTEXT: writing block 134984 of relation 1663/16409/11088101 @:6160 2009-03-30 06:50:00 BST LOG: checkpoint starting: time @:6160 2009-03-30 06:50:01 BST ERROR: could not open segment 1 of relation 1663/16409/11088101 (target block 135061): No such file or directory @:6160 2009-03-30 06:50:01 BST CONTEXT: writing block 135061 of relation 1663/16409/11088101 Doing an immediate shutdown and restart seems to have brought everything back online. I don't think there is any corruption (not that I can tell easily...), and I'm not worried if I lost a transaction or three. Can anyone think what happened here? I suspect pgstattuple as it was the only unusual activity happening at that time and as far as I'm aware we have no hardware alerts and the box has been running smoothly for quite some time. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ -- 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] Parallel DB architechture
Hello We use plProxy (RUN ON ALL) to run queries in parallel. We split our database into 16 shards and distributed it over 4 servers. So now we are running queries on 16 cpu's in parallel :) regards, Asko On Mon, Mar 30, 2009 at 9:20 AM, aravind chandu avin_frie...@yahoo.comwrote: Hello, I would like to implement parallel DB Architecture in postgres.I came across various parallel architectures shared memory,shared disk,shared nothing,hierarchical.But I am unaware of how to implement one of these in real time system.Can you guys help me in this aspect?
Re: [GENERAL] running two servers on one machine
Thomas, When I start the servers, I use different ports via the -p option. Is that equivalent to using different listener ports in postgresql.conf? Thanks, Eric On Mar 28, 2009, at 3:51 PM, Thomas Kellerer wrote: Adrian Klaver wrote on 28.03.2009 23:37: On Saturday 28 March 2009 3:27:15 pm Eric Smith wrote: All, I'm trying to run two database clusters on a single machine, and am failing. I use initdb to create the two clusters... each has their own directory structure. I can start one server or the other with pg_ctl, but once the first starts, the second just hangs on waiting for server to start Are you pointing pg_ctl at the right DATA directory for each cluster using the -D switch? You also have to make sure you assign different listener ports for both servers in postgresql.conf Thomas -- 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] Installing PLPython - Version Problem
On Mon, Mar 30, 2009 at 1:51 AM, ray ray.jos...@cdicorp.com wrote: I am trying to get Python 2.6 and PostgreSQL 8.3 to work together under Windows 2000. When I try to add Python to PostgreSQL via: createlang plpythonu dbname But when doing so, I get an error: createlang: language installation failed: ERROR: could not load library C:/Program Files/PostgreSQL/8.3/lib/createlang: language installation failed: ERROR: could not load library C:/Program Files/ PostgreSQL/8.3/lib/plpython.dll: The specified module could not be found. So I Used depends to walk the references of plpython.dll, I found that it can not find python25.dll. Since I have 2.6, only python26.dll is in ...system32. 1) The first question is how do I get this stack to look for the 2.6 version? Recompile PostgreSQL to use Python 2.5. Any renaming etc. will lead to pain. The DLLs include the version number precisely because they are not compatible. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] New shapshot RPMs (Mar 27, 2009) are ready for testing
Devrim GÜNDÜZ, 28.03.2009 10:02: As we are moving very close to 8.4 beta, please join us for testing 8.4 release. I just released new RPM sets, which is based on Mar 27 CVS snapshot. Please note that these packages are **not** production ready. They are for Fedora 9,10 and RHEL/CentOS 5. I have no intention to push 8.4 development packages for older Fedora/RHEL/CentOS releases, and also currently I do not provide (Open)SuSE packages. EnterpriseDB used to provide 8.4 builds for Windows, but the page I bookmarked does not longer work. Does anybody know if EnterpriseDB stopped this service? Regards Thomas -- 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] New shapshot RPMs (Mar 27, 2009) are ready for testing
On Mon, Mar 30, 2009 at 7:56 AM, Thomas Kellerer spam_ea...@gmx.net wrote: Devrim GÜNDÜZ, 28.03.2009 10:02: As we are moving very close to 8.4 beta, please join us for testing 8.4 release. I just released new RPM sets, which is based on Mar 27 CVS snapshot. Please note that these packages are **not** production ready. They are for Fedora 9,10 and RHEL/CentOS 5. I have no intention to push 8.4 development packages for older Fedora/RHEL/CentOS releases, and also currently I do not provide (Open)SuSE packages. EnterpriseDB used to provide 8.4 builds for Windows, but the page I bookmarked does not longer work. Does anybody know if EnterpriseDB stopped this service? No - in fact we refreshed the builds just a few days ago: http://www.enterprisedb.com/products/pgdevdownload.do. There are also installers for Mac and Linux 32/64. FYI, all the known development builds are listed at http://www.postgresql.org/download/snapshots -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] [Q] rollback after select?
On Sun, Mar 29, 2009 at 08:44:59PM -0400, V S P wrote: The same problem was with my set client encoding (my other email) both were related problem to the fact that PG I have to issue commits even for the 'non sql' statements. That's something between you and your database driver. PostgreSQL by default commits between each statement unless you explicitly start a transaction. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Parallel DB architechture
On Behalf Of Asko Oja Hello We use plProxy (RUN ON ALL) to run queries in parallel. We split our database into 16 shards and distributed it over 4 servers. So now we are running queries on 16 cpu's in parallel :) Wow.. query time improved How many fold? Any idea? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Parallel Query Processing
Thanks for your reply guys.My aim is to run multiple queries.Will joins come under multiple queries? or it just mean two or more different queries like one select statement and another select statement? Does postgresql itself supports to run multiple queries or we have to use pgpool-II tool? From: Scott Marlowe scott.marl...@gmail.com To: aravind chandu avin_frie...@yahoo.com Cc: postgresql Forums pgsql-general@postgresql.org Sent: Wednesday, March 25, 2009 4:52:43 PM Subject: Re: [GENERAL] Parallel Query Processing On Wed, Mar 25, 2009 at 2:24 PM, aravind chandu avin_frie...@yahoo.com wrote: Hello, I have a few questions related to the parallel query processing.Can you guys tell me how to implement parallel query processing in postgresql database. Do you mean one query being parallelized, or multiple queries running at once? PostgreSQL provides no capability to parallelize one query into multiple processes. Multiple Queries run in parallel automatically Hello,
Re: [GENERAL] Accent insensitive search?
On Tue, Mar 24, 2009 at 4:53 PM, Jasen Betts ja...@xnet.co.nz wrote: On 2009-03-18, cifroes cifr...@netcabo.pt wrote: This is a multi-part message in MIME format. --_=_NextPart_001_01C9A7E6.B32BBA87 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi, I have a DB in utf-8 and postgres 8.3.x.=20 How can I do an accent insensitive search (like ...) ? use a posix regular expression that matches the string you want: select * from foo where colname ~ '^[CcĆćĈĉĊċČč][ĀāĂ㥹Aa][Pp][ŌōŎŏŐő]$'; you could write a function to do the translation. Unicode normalization is an interesting way to strip accents I've found: import unicodedata s = u'Björn' s = unicodedata.normalize(NFKD, s) s = ''.join(c for c in s if ord(c) 127) print s Bjorn You can also use the character names to map many more characters to the ascii equivalent. A large number of these can me smashed into ASCII using regular expressions and some manual mappings to map LETTER THORN - th, LETTER LATERAL CLICK - X etc. Just mapping CAPITAL LETTER XX - XX and SMALL LETTER XX - xx seems to get you most of europe if you special case SHARP S - ss and THORN - th. s = u'ァ' print unicodedata.name(s) KATAKANA LETTER SMALL A -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] debian package 8.4devel was:[Pkg-postgresql-public] Postgres major version support policy on Debian
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Cédric Villemain a écrit : Martin Pitt a écrit : Cédric Villemain [2009-03-15 23:58 +0100]: Any plan for 8.4 pre-beta package ? (Devrim Gunduz provide usefull rpm package, I'd like to have the same in debian). Can it be in the experimental repository ? So far I usually started packaging those with the first public beta version, but if we are close to that, sure. Packaging this is probably easy, it just needs some time to get all the bits in postgresql-common right (like correctly rewriting obsolete/removed/retyped configuration settings, and the like). I'll see to packaging a current snapshot soon. Martin Xcellent Martin. If I can help, ping me. Here is the announce from Devrim : http://archives.postgresql.org/pgsql-announce/2009-03/msg00012.php And more particulary about !production: http://yum.pgsqlrpms.org/news-8.4devel-ready-for-testing.php I have started some work on that. Old debian/ updated to 8.4 (and the patches). But I keep on trouble with the postgresql-doc. Upstream have updated to autoconf 2.61 (git 82cd478cf80f4a5a50d39cb9e90a48823679e6a1), it is perhaps the issue : I have replace the --with-docdir by the standard --docdir (new configure option). And dh_install fail (because no files in the doc-8.4/html/). I tryed manualy the suggested 'make -C doc all' as suggested by the GNUMakefile, but the doc is not built. Any tips are welcome. - -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAknQmicACgkQo/dppWjpEvxjMACfUobjmCq1XJla/ewHQ2EBNbEc CEwAoJFFEUC3rfBGbOLfnHkY2NDWRy8W =aedw -END PGP SIGNATURE- -- 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: [Pkg-postgresql-public] debian package 8.4devel was: Postgres major version support policy on Debian
Hello Cédric, Cédric Villemain [2009-03-30 12:08 +0200]: I have started some work on that. Whoops, sorry. So did I yesterday evening. I just fixed the branch enough to build now [1]. I just need to update postgresql-common to work with 8.4. Some things changed, e. g. pg_controldata doesn't report the default locale any more, thus I need to rework the locales checks on startup. Once the entire postgresql-common test suite passes, I'll upload this to experimental. Thanks, Martin [1] http://bazaar.launchpad.net/~pitti/postgresql/debian-8.4/files -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- 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] New shapshot RPMs (Mar 27, 2009) are ready for testing
Dave Page, 30.03.2009 10:34: EnterpriseDB used to provide 8.4 builds for Windows, but the page I bookmarked does not longer work. Does anybody know if EnterpriseDB stopped this service? No - in fact we refreshed the builds just a few days ago: http://www.enterprisedb.com/products/pgdevdownload.do. There are also installers for Mac and Linux 32/64. FYI, all the known development builds are listed at http://www.postgresql.org/download/snapshots OK, thanks. I received very strange error messages last week when I accessed that page. (Velocity Template not found and similar errors). But now it's working. Thanks! Thomas -- 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] Parallel Query Processing
On 30/03/2009 07:18, aravind chandu wrote: Thanks for your reply guys.My aim is to run multiple queries.Will joins come under multiple queries? or it just mean two or more Not sure what you're getting at herea query that includes a JOIN is still one query. different queries like one select statement and another select statement? Does postgresql itself supports to run multiple queries or we have to use pgpool-II tool? PostgreSQL spawns a new process for each connection, and runs these in parallel. Within each connection, a client can run as many queries as it likes; and these are going on at the same time as queries in other connections. You should read the part of the docs that talks about MVCC to understand how each process sees its own snapshot of the data. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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 indexes - OR search
Is it possible to have 2 gin indexes and OR the search of them? Problem is, I need to search 5 columns and there is limitation of only 4 weights, so I want to have one index of 4 vectors and second index of 1 vector and need to OR the search. When I try to search them separately, or use AND, it works, but for OR EXPLAIN ANALYZE shows me, it does seq scan. Thank you. -- View this message in context: http://www.nabble.com/-fulltext--GIN-indexes---OR-search-tp22781900p22781900.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] New shapshot RPMs (Mar 27, 2009) are ready for testing
Dave Page, 30.03.2009 14:28: On Mon, Mar 30, 2009 at 1:06 PM, Thomas Kellerer spam_ea...@gmx.net wrote: OK, thanks. I received very strange error messages last week when I accessed that page. (Velocity Template not found and similar errors). But now it's working. Yeah, we had a big website update and that page got messed up along the way somehow. Sorry 'bout that. No problem. I'm glad you are supplying those builds! Regards Thomas -- 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] New shapshot RPMs (Mar 27, 2009) are ready for testing
On Mon, Mar 30, 2009 at 1:06 PM, Thomas Kellerer spam_ea...@gmx.net wrote: OK, thanks. I received very strange error messages last week when I accessed that page. (Velocity Template not found and similar errors). But now it's working. Yeah, we had a big website update and that page got messed up along the way somehow. Sorry 'bout that. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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 split timestamps values by 24h slices ?
Bruno Baguette wrote: Hello ! I have a table that contains two timestamps (and some other fields that does not matter here). the_table -- pk_planning_id == INT8 (primary key) timestamp_start == (not null timestamp without time zone) timestamp_stop =+ (not null timestamp without time zone) I would like to do a SELECT of that table, but by splitting by 24h day : So, if I have one planning from 2009-03-30 14h50 to 2009-04-01 19h00, I would like to get three lines in my SELECT result : 123 | 2009-03-30 14h50 | 2009-03-30 24h00 123 | 2009-03-31 00h00 | 2009-03-31 24h00 123 | 2009-04-01 00h00 | 2009-04-01 19h00 I was thinking of doing that by playing with three UNION requests (beginning date, intermediate(s) date(s) and ending dates. Am i going in the right way or is there a cleanest (or more elegant) way to do that ? Thanks in advance ! Regards, I'd suggest a calendar table. Depending on needs, you may want to fields like day_of_week, quarter, term, moon_phase, whatever... CREATE TABLE calendar ( calendar_date date NOT NULL, CONSTRAINT calendar_pk PRIMARY KEY (calendar_date) ); -- populate your table with suitable date ranges INSERT INTO calendar SELECT '2000-01-01'::date + i FROM generate_series(0,1) i; Now change your original query like so: SELECT * FROM my_table JOIN calendar ON calendar_date BETWEEN TRUNC(timestamp_start) AND timestamp_end Artacus -- 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] Maximum transaction rate
Markus Wanner wrote: Hi, Martijn van Oosterhout wrote: And fsync better do what you're asking (how fast is just a performance issue, just as long as it's done). Where are we on this issue? I've read all of this thread and the one on the lvm-linux mailing list as well, but still don't feel confident. In the following scenario: fsync - filesystem - physical disk I'm assuming the filesystem correctly issues an blkdev_issue_flush() on the physical disk upon fsync(), to do what it's told: flush the cache(s) to disk. Further, I'm also assuming the physical disk is flushable (i.e. it correctly implements the blkdev_issue_flush() call). Here we can be pretty certain that fsync works as advertised, I think. The unanswered question to me is, what's happening, if I add LVM in between as follows: fsync - filesystmem - device mapper (lvm) - physical disk(s) Again, assume the filesystem issues a blkdev_issue_flush() to the lower layer and the physical disks are all flushable (and implement that correctly). How does the device mapper behave? I'd expect it to forward the blkdev_issue_flush() call to all affected devices and only return after the last one has confirmed and completed flushing its caches. Is that the case? I've also read about the newish write barriers and about filesystems implementing fsync with such write barriers. That seems fishy to me and would of course break in combination with LVM (which doesn't completely support write barriers, AFAIU). However, that's clearly the filesystem side of the story and has not much to do with whether fsync lies on top of LVM or not. Help in clarifying this issue greatly appreciated. Kind Regards Markus Wanner Well, AFAIK, the summary would be: 1) adding LVM to the chain makes no difference; 2) you still need to disable the write-back cache in IDE/SATA disks, for fsync() to work properly. 3) without LVM and with write-back cache enabled, due to current(?) limitations in the linux kernel, with some journaled filesystems (but not ext3 in data=write-back or data=ordered mode, I'm not sure about data=journal), you may be less vulnerable, if you use fsync() (or O_SYNC). less vulnerable means that all pending changes are commetted to disk, but the very last one. So: - write-back cache + EXT3 = unsafe - write-back cache + other fs = (depending on the fs)[*] safer but not 100% safe - write-back cache + LVM + any fs = unsafe - write-thru cache + any fs = safe - write-thru cache + LVM + any fs = safe [*] the fs must use (directly or indirectly via journal commit) a write barrier on fsync(). Ext3 doesn't (it does when the inode changes, but that happens once a second only). If you want both speed and safety, use a batter-backed controller (and write-thru cache on disks, but the controller should enforce it when you plug the disks in). It's the usual Fast, Safe, Cheap: choose two. This is an interesting article: http://support.microsoft.com/kb/234656/en-us/ note how for all three kinds of disk (IDE/SATA/SCSI) they say: Disk caching should be disabled in order to use the drive with SQL Server. They don't mention write barriers. .TM. -- 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] [fulltext] GIN indexes - OR search
esemba ese...@gmail.com writes: Is it possible to have 2 gin indexes and OR the search of them? Sure. They need to be fairly restrictive though, or the planner might decide that a seqscan is cheaper. 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] Installing PLPython - Version Problem
On Mon, Mar 30, 2009 at 2:52 PM, Joseph, Ray ray.jos...@cdicorp.com wrote: Dave, I am sorry that I was not clear. I want to use 2.6 but the stack seems to direct me to 2.5. That's exactly what I understood from your email - however my fingers weren't working properly at that time in the morning :-(. What I was trying to say was that you must compile your own build of PostgreSQL if you want to use *2.6*. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] running two servers on one machine
On Sunday 29 March 2009 7:59:19 pm Eric Smith wrote: Thomas, When I start the servers, I use different ports via the -p option. Is that equivalent to using different listener ports in postgresql.conf? Thanks, Eric I think you are going to have to show us the exact command you are using. If you are using postgres -p then that is the port switch. If you are doing pg_ctl -p then p designates the path to the executable. See below for more details: http://www.postgresql.org/docs/8.3/interactive/reference-server.html -- Adrian Klaver akla...@comcast.net -- 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] running two servers on one machine
Adrian Klaver akla...@comcast.net writes: I think you are going to have to show us the exact command you are using. What *I* want to see is the postmaster log, which should certainly contain an error message explaining why it failed to start. 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] [GENEAL] dynamically changing table
Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. They all use integers as datatype though.. One day, I get 2 new columns, a week later I loose one column, and so on in a random pattern. I will most likely have a few million rows of data so I just wonder if there are any problems with running alter table x add column . or alter table x drop column . Adding a column, will it place data far away on the disc so that select * from x where id=y will result in not quite optimal performance since it has to fetch columns from a lot of different places? Will deleting a column result in a lot of empty space that will anoy me later on? Are there any other clever solutions of this problem? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] i have a problem with judge some words contain chinese word!
Hello: I have a problem with juding some words contain chinese word. I found that with oracle.but i can't find it in postgresql. oracle? has acsiistr and lengthb to deal with it.i wonna to know postgresql how to ! ??? thx ! from eagle
Re: [GENERAL] Installing PLPython - Version Problem
On Mar 30, 2:55 am, dp...@pgadmin.org (Dave Page) wrote: On Mon, Mar 30, 2009 at 1:51 AM, ray ray.jos...@cdicorp.com wrote: I am trying to get Python 2.6 and PostgreSQL 8.3 to work together under Windows 2000. When I try to add Python to PostgreSQL via: createlang plpythonu dbname But when doing so, I get an error: createlang: language installation failed: ERROR: could not load library C:/Program Files/PostgreSQL/8.3/lib/createlang: language installation failed: ERROR: could not load library C:/Program Files/ PostgreSQL/8.3/lib/plpython.dll: The specified module could not be found. So I Used depends to walk the references of plpython.dll, I found that it can not find python25.dll. Since I have 2.6, only python26.dll is in ...system32. 1) The first question is how do I get this stack to look for the 2.6 version? Recompile PostgreSQL to use Python 2.5. Any renaming etc. will lead to pain. The DLLs include the version number precisely because they are not compatible. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com Dave, I am sorry for not providing a clear description of my problem. I want to us Python 2.6 but the PLPython seems to me looking for 2.5 which is not on my machine. Is there a way to have PostgreSQL work with Python 2.6? Ray -- 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] [GENEAL] dynamically changing table
Without knowing to much currently.. can you create one table with enough columns? Then create a view to query the table and 'reflect' the changes and correct column names. Using rule you could also even simulate the update to the view and update to the correct columns. This so that you don't have to drop/create columns over and over again. I hope I made myself clear... Ries On Mar 30, 2009, at 10:39 AM, A B wrote: Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. They all use integers as datatype though.. One day, I get 2 new columns, a week later I loose one column, and so on in a random pattern. I will most likely have a few million rows of data so I just wonder if there are any problems with running alter table x add column . or alter table x drop column . Adding a column, will it place data far away on the disc so that select * from x where id=y will result in not quite optimal performance since it has to fetch columns from a lot of different places? Will deleting a column result in a lot of empty space that will anoy me later on? Are there any other clever solutions of this problem? -- 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] [GENEAL] dynamically changing table
IMHO one of the possible solutions is to review the table and storage externaly the stable columns. So when you run 'alter table ' it was less expensive (i never test this, but maybe its true :P ) So the mutable columns will be separated from the others and the phisical structure could be more light to changes. Emanauel 2009/3/30, A B gentosa...@gmail.com: Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. They all use integers as datatype though.. One day, I get 2 new columns, a week later I loose one column, and so on in a random pattern. I will most likely have a few million rows of data so I just wonder if there are any problems with running alter table x add column . or alter table x drop column . Adding a column, will it place data far away on the disc so that select * from x where id=y will result in not quite optimal performance since it has to fetch columns from a lot of different places? Will deleting a column result in a lot of empty space that will anoy me later on? Are there any other clever solutions of this problem? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- 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] [GENEAL] dynamically changing table
On Mon, Mar 30, 2009 at 05:39:19PM +0200, A B wrote: Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. They all use integers as datatype though.. One day, I get 2 new columns, a week later I loose one column, and so on in a random pattern. I think you should think of something else. I will most likely have a few million rows of data so I just wonder if there are any problems with running alter table x add column . or alter table x drop column . Well, not as such. Except that deleting a column doesn't really delete it, it hides it, so it never really goes away. So the number of columns in your table will only go up and eventually you're going to reach the point (around 1600 IIRC, probably earlier) where it will simply stop working. Adding a column, will it place data far away on the disc so that select * from x where id=y will result in not quite optimal performance since it has to fetch columns from a lot of different places? Nope, no extra cost there. Will deleting a column result in a lot of empty space that will anoy me later on? Yes, the space isn't actually released until the next time you update that row. Are there any other clever solutions of this problem? If this is just for development where the actual space/columns used is just temporary, your trick might work. Otherwise I'd suggest normalising so the columns to become rows in another table. But you're going to have to be more specific as to what you're trying to do if you want proper answers. Or perhaps an array of integers? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] running two clusters on one machine
To run servers at diferent ports you must use postgres binary. postgres -D data1 -p 5432 -l logfile postgres -D data2 -p 5433 -l logfile I never used pg_ctl to run postgres and don't ask me why. 2009/3/28, Eric Smith esm...@haas-software.com: All, I'm trying to run two database clusters on a single machine, and am failing. I use initdb to create the two clusters... each has their own directory structure. I can start one server or the other with pg_ctl, but once the first starts, the second just hangs on waiting for server to start The two servers are using different ports. I'm running v 8.3 on a Mac running 10.5. What's the secret step I'm missing here? Thanks, Eric -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- 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] [GENEAL] dynamically changing table
Well, not as such. Except that deleting a column doesn't really delete it, it hides it, so it never really goes away. So the number of columns in your table will only go up and eventually you're going to reach the point (around 1600 IIRC, probably earlier) where it will simply stop working. Oh, that would be a not very plesant surprise. If this is just for development where the actual space/columns used is just temporary, your trick might work. Otherwise I'd suggest normalising so the columns to become rows in another table. But you're going to have to be more specific as to what you're trying to do if you want proper answers. Well, I want to store information about certain objects. Some columns will be fixed from the start, other columns will be added or removed (like when someone comes up with a brilliant idea of adding new information about the object, or removing something that is not needed) It's a little hard to specify what operations will be performed on the data, but mostly it will be fetch all data for object number X, or increase integer field nr 4 by 1 for object number X. And then I'll also have the operations: add a new field with default value 0 and remove integer field number 2 The fixed columns could be placed in a special table, as suggested above. Or perhaps an array of integers? That sounds to be a better way. I'll start reading about arrays. I have not used them earlier so I wasn't thinking about them. How would you handle fields of other datatype than integers? Have an array for timestamps, one for texts, one for varchar(50), one for floatingpoint numbers, etc.? -- 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] [GENEAL] dynamically changing table
On Mon, Mar 30, 2009 at 05:39:19PM +0200, A B wrote: Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. Stop right there. You need to get some sanity into your project, either by changing that requirement, or by not using an RDBMS for it. Cheers, David. They all use integers as datatype though.. One day, I get 2 new columns, a week later I loose one column, and so on in a random pattern. I will most likely have a few million rows of data so I just wonder if there are any problems with running alter table x add column . or alter table x drop column . Adding a column, will it place data far away on the disc so that select * from x where id=y will result in not quite optimal performance since it has to fetch columns from a lot of different places? Will deleting a column result in a lot of empty space that will anoy me later on? Are there any other clever solutions of this problem? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
FW: [GENERAL] [GENEAL] dynamically changing table
From: michaelblack75...@hotmail.com To: gentosa...@gmail.com Subject: RE: [GENERAL] [GENEAL] dynamically changing table Date: Mon, 30 Mar 2009 16:05:52 + The simplest way is to plan for the maximum number of columns that will be required (say 14 - 2 weeks of data assuming that is daily reporting numbers in the columns. You could have only a single data column and in the first record insert the number of columns that need to be processed, and build the data in the method to load an array to simulate a record object for that number. Then process cor the lenghth of the array. Or you could use the Drop table and Create table instead of Delete data and Alter Table. Also by varying the number of columns you have programming considerations in addition. The the input and process meths will need to check the meta data to determine how many columns it is dealing with. Those are just to options that come to mind. Michael Date: Mon, 30 Mar 2009 17:39:19 +0200 Subject: [GENERAL] [GENEAL] dynamically changing table From: gentosa...@gmail.com To: pgsql-general@postgresql.org Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. They all use integers as datatype though.. One day, I get 2 new columns, a week later I loose one column, and so on in a random pattern. I will most likely have a few million rows of data so I just wonder if there are any problems with running alter table x add column . or alter table x drop column . Adding a column, will it place data far away on the disc so that select * from x where id=y will result in not quite optimal performance since it has to fetch columns from a lot of different places? Will deleting a column result in a lot of empty space that will anoy me later on? Are there any other clever solutions of this problem? -- 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] running two clusters on one machine
- Emanuel Calvo Franco postgres@gmail.com wrote: To run servers at diferent ports you must use postgres binary. postgres -D data1 -p 5432 -l logfile postgres -D data2 -p 5433 -l logfile That is not true. You can set the port in the postgresql.conf for each cluster to be different and use pg_ctl to start the appropriate cluster. See: http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html I never used pg_ctl to run postgres and don't ask me why. 2009/3/28, Eric Smith esm...@haas-software.com: All, I'm trying to run two database clusters on a single machine, and am failing. I use initdb to create the two clusters... each has their own directory structure. I can start one server or the other with pg_ctl, but once the first starts, the second just hangs on waiting for server to start The two servers are using different ports. I'm running v 8.3 on a Mac running 10.5. What's the secret step I'm missing here? Thanks, Eric -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin Adrian Klaver akla...@comcast.net -- 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] PostgreSql with or without Plus?
I am sorry, but it seems that I replied to only one of you instead of to the mailing list. I am just about to install a version on my Server computer (Windows web server 2008 x64) and I am stuck on what I should go with. I have been developing on the standard postgreSQL but like that the EnterpriseDB webpage claims that Standard Plus is faster. Is there something that will work differently, more complex with that version? Not free, or similar? Something I should know? Why would they give out a better version, if both are free??? There is something fishy going on :P Thanks / Jennifer On Wed, Mar 18, 2009 at 4:49 PM, Joshua D. Drake j...@commandprompt.comwrote: On Wed, 2009-03-18 at 11:19 +0530, Ashish Karalkar wrote: Postgres Plus does support Linux and it includes all the PostgreSQL features as well as have many other additional useful features.One of which is its dyna tune module. Yes it does, but it doesn't use native packages (as far as I know). Meaning, it doesn't use RPM or DEB. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
Re: [GENERAL] [GENEAL] dynamically changing table
2009/3/30 David Fetter da...@fetter.org: On Mon, Mar 30, 2009 at 05:39:19PM +0200, A B wrote: Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. Stop right there. You need to get some sanity into your project, either by changing that requirement, or by not using an RDBMS for it. Cheers, David. Well, the requirement is: keep a lot of data stored, don't loose any of it, and you will not know what you will have to store (changing number of fields and of different types) But it is not all that bad, the fields will be integers, or text, or floatingpoint numbers. One option is, put it in a db as a huge text (or in textfiles, one per object) and parse it when you need it. That might also work. Why are you demanding sanity? I need crazy ideas to get this to work ;-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] string_to_array with empty input
I have a query that converts a string to an array with the
string_to_array function. Sometimes the input is an empty string (not a
null, but a string of zero-length). I had expected the result to be a
one-element array with an empty string as the first and only element but
instead it returned null. I looked at the docs and didn't find the
observed behavior documented.
I ran some tests:
string_to_array(null,',') -- expected and got null
string_to_array('a',',') -- expected and got one-element array {a}
string_to_array('',',') -- expected consistency with above (one-element
array with empty string) but got null
So I looked to see how array_to_string deals with such arrays:
array_to_string('{}'::text[],',') -- expected and got an empty string
I realize that array_to_string is not a perfect inverse of
string_to_array - especially due to the fact that the array-index is not
preserved. But it seems that the array data should match.
Is this a bug or am I missing the logic behind this behavior? As it
stands, it appears I will have to wrap the function in a case statement
as a work-around unless there is a better solution.
Cheers,
Steve
--
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] PostgreSql with or without Plus?
On Mon, Mar 30, 2009 at 7:36 PM, Jennifer Trey jennifer.t...@gmail.com wrote: I am sorry, but it seems that I replied to only one of you instead of to the mailing list. I am just about to install a version on my Server computer (Windows web server 2008 x64) and I am stuck on what I should go with. I have been developing on the standard postgreSQL but like that the EnterpriseDB webpage claims that Standard Plus is faster. Where does it claim that? Standard Server is pure community PostgreSQL, with a bunch of extra stuff in the installer. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] [GENEAL] dynamically changing table
On Mon, Mar 30, 2009 at 11:04:06AM -0700, David Fetter wrote: On Mon, Mar 30, 2009 at 05:39:19PM +0200, A B wrote: Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. Stop right there. You need to get some sanity into your project, either by changing that requirement, or by not using an RDBMS for it. I'd agree with that sentiment as well. It's very easy to add columns to relational databases; much harder and more time consuming is following this through the rest of the software; and harder still the fuzzier aspects of tying down what's actually needed, documenting and testing it. Databases aren't fixed in stone from the minute they're created; columns come and go, tables come and go. The more information you have at the beginning the better job you can do initially, but it's also very easy to over-design. In my experience over-design normally manifests itself in an over-complication of the design by making the database handle the few weird exceptions as though it's the normal data. Obviously some exceptions are useful for the database to know about, but there are some that it really doesn't matter---determining which is which up front isn't easy. This is where the KISS principle comes in; optimising a database design so it's easy to add/remove integer columns to/from tables doesn't sound like a good choice to be making in the long run. If you don't know what's going on; try leaving the data you're unsure about in a spreadsheet until you understand it better. It's much easier that way than rewriting user interfaces every day because somebody decides that something's changed. -- Sam http://samason.me.uk/ -- 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] [GENEAL] dynamically changing table
2009/3/31 A B gentosa...@gmail.com: One option is, put it in a db as a huge text (or in textfiles, one per object) and parse it when you need it. That might also work. Why are you demanding sanity? I need crazy ideas to get this to work ;-) Heh ... sorry, but dynamic table just SCREAMS design flaw!! ... as pointed out above, an approach with the new columns being rows in a separate table sounds quite sane. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- 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] [GENEAL] dynamically changing table
On Mon, Mar 30, 2009 at 08:50:59PM +0200, A B wrote: Well, the requirement is: keep a lot of data stored, don't loose any of it, and you will not know what you will have to store (changing number of fields and of different types) As you've not said anything about getting said data back; it doesn't seem to matter if it actually gets lost! Sounds as though the main unanswered constraint on the database at the moment is what people what to do with the data once they've handed it to you. When you've figured that out you may have a better idea of what to do. -- Sam http://samason.me.uk/ -- 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] PostgreSql with or without Plus?
Hmm, cant find it.. but.. if I install the regular (not plus), could I later upgrade easily to other versions ? Is that possible? In that case I could just start with the one I already are familiar with. Jen On Mon, Mar 30, 2009 at 9:03 PM, Dave Page dp...@pgadmin.org wrote: On Mon, Mar 30, 2009 at 7:36 PM, Jennifer Trey jennifer.t...@gmail.com wrote: I am sorry, but it seems that I replied to only one of you instead of to the mailing list. I am just about to install a version on my Server computer (Windows web server 2008 x64) and I am stuck on what I should go with. I have been developing on the standard postgreSQL but like that the EnterpriseDB webpage claims that Standard Plus is faster. Where does it claim that? Standard Server is pure community PostgreSQL, with a bunch of extra stuff in the installer. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Re: [GENERAL] PostgreSql with or without Plus?
On Mon, Mar 30, 2009 at 8:20 PM, Jennifer Trey jennifer.t...@gmail.com wrote: Hmm, cant find it.. but.. if I install the regular (not plus), could I later upgrade easily to other versions ? Is that possible? In that case I could just start with the one I already are familiar with. It's quite straightforward, yes. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] [GENEAL] dynamically changing table
Is it possible that what you want is a lookup table with a string index, i.e. attribute-value pairs? If so, that would be represented as a hash in Perl, or a map in C++. In a database, you could design a very simple schema for it. Or do you just have unclear requirements, as others have suggested? -Will -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of A B Sent: 30 March 2009 14:51 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] [GENEAL] dynamically changing table Well, the requirement is: keep a lot of data stored, don't loose any of it, and you will not know what you will have to store (changing number of fields and of different types) But it is not all that bad, the fields will be integers, or text, or floatingpoint numbers. One option is, put it in a db as a huge text (or in textfiles, one per object) and parse it when you need it. That might also work. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Point me to latest JDBC for pgSQL (plus) standard server?
Point me to latest JDBC for pgSQL (plus) standard server?
[GENERAL] Postgres startup processes on linux?
We're running PG 8.1 on CentOS 5.0. When postgres starts the following processes apparently required by the DB itself get created: postgres 23784 1 0 13:55 ?00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 23786 23784 0 13:55 ?00:00:00 postgres: logger process postgres 23788 23784 0 13:55 ?00:00:00 postgres: writer process postgres 23789 23784 0 13:55 ?00:00:01 postgres: stats buffer process postgres 23790 23789 0 13:55 ?00:00:00 postgres: stats collector process postgres 23802 23784 3 13:55 ?00:00:58 postgres: airwave airwave [local] idle postgres 23803 23784 0 13:55 ?00:00:12 postgres: airwave airwave [local] idle I understand what the first five are for. What are 23802 and 23803? These look like they might other processes started by the postmaster to serve connections. Tey have the property that they never go away, and seem to always follow *very closely* on the other pids, as though they were being created by the db itself, like the others just before them. However they the list information looks like a normal pg connection server created by postmaster. Our usual connection servers are identified in our own logs and do not include these two pids. In addition pid 23802 exhibits the following mysterious behavior in the pg log, which is logging duration for all SQL statements running over 100 ms : 2009-03-30 14:58:23.763 EDT 25284 LOG: autovacuum: processing database airwave 2009-03-30 14:58:30.072 EDT 23802 LOG: duration: 236.061 ms statement: commit 2009-03-30 14:58:47.985 EDT 23802 LOG: duration: 269.877 ms statement: commit 2009-03-30 14:59:23.774 EDT 25305 LOG: autovacuum: processing database airwave 2009-03-30 14:59:33.923 EDT 23802 LOG: duration: 256.340 ms statement: commit 2009-03-30 14:59:51.130 EDT 23802 LOG: duration: 316.898 ms statement: commit 2009-03-30 15:00:07.967 EDT 23802 LOG: duration: 266.485 ms statement: commit 2009-03-30 15:00:23.790 EDT 25324 LOG: autovacuum: processing database airwave 2009-03-30 15:00:23.925 EDT 23802 LOG: duration: 338.512 ms statement: commit 2009-03-30 15:00:25.744 EDT 23802 LOG: duration: 272.170 ms statement: commit 2009-03-30 15:00:42.612 EDT 23802 LOG: duration: 310.099 ms statement: commit 2009-03-30 15:01:04.570 EDT 23802 LOG: duration: 254.574 ms statement: commit 2009-03-30 15:01:21.400 EDT 23802 LOG: duration: 245.433 ms statement: commit 2009-03-30 15:01:23.925 EDT 25345 LOG: autovacuum: processing database airwave 2009-03-30 15:01:38.286 EDT 23802 LOG: duration: 231.673 ms statement: commit 2009-03-30 15:02:12.017 EDT 23802 LOG: duration: 413.221 ms statement: commit 2009-03-30 15:02:23.954 EDT 25366 LOG: autovacuum: processing database airwave 2009-03-30 15:02:46.843 EDT 23802 LOG: duration: 268.504 ms statement: commit You can see in the process list that process 23802 is building up a lot of cpu time, as confirmed in the pg log we run. Can this be some native pg process opened by the DB itself, or is it some rogue connection server that we have to track down? TIA, Jim HP ProCurve
Re: [GENERAL] Point me to latest JDBC for pgSQL (plus) standard server?
Jennifer Trey wrote on 30.03.2009 21:39: Point me to latest JDBC for pgSQL (plus) standard server? http://jdbc.postgresql.org -- 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] Postgres startup processes on linux? - resolved.
It's a normal pg connection server created by our own code. Nothing native to pg startup. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Eshelman, James Sent: Monday, March 30, 2009 3:29 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Postgres startup processes on linux? We're running PG 8.1 on CentOS 5.0. When postgres starts the following processes apparently required by the DB itself get created: postgres 23784 1 0 13:55 ?00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 23786 23784 0 13:55 ?00:00:00 postgres: logger process postgres 23788 23784 0 13:55 ?00:00:00 postgres: writer process postgres 23789 23784 0 13:55 ?00:00:01 postgres: stats buffer process postgres 23790 23789 0 13:55 ?00:00:00 postgres: stats collector process postgres 23802 23784 3 13:55 ?00:00:58 postgres: airwave airwave [local] idle postgres 23803 23784 0 13:55 ?00:00:12 postgres: airwave airwave [local] idle I understand what the first five are for. What are 23802 and 23803? These look like they might other processes started by the postmaster to serve connections. Tey have the property that they never go away, and seem to always follow *very closely* on the other pids, as though they were being created by the db itself, like the others just before them. However they the list information looks like a normal pg connection server created by postmaster. Our usual connection servers are identified in our own logs and do not include these two pids. In addition pid 23802 exhibits the following mysterious behavior in the pg log, which is logging duration for all SQL statements running over 100 ms : 2009-03-30 14:58:23.763 EDT 25284 LOG: autovacuum: processing database airwave 2009-03-30 14:58:30.072 EDT 23802 LOG: duration: 236.061 ms statement: commit 2009-03-30 14:58:47.985 EDT 23802 LOG: duration: 269.877 ms statement: commit 2009-03-30 14:59:23.774 EDT 25305 LOG: autovacuum: processing database airwave 2009-03-30 14:59:33.923 EDT 23802 LOG: duration: 256.340 ms statement: commit 2009-03-30 14:59:51.130 EDT 23802 LOG: duration: 316.898 ms statement: commit 2009-03-30 15:00:07.967 EDT 23802 LOG: duration: 266.485 ms statement: commit 2009-03-30 15:00:23.790 EDT 25324 LOG: autovacuum: processing database airwave 2009-03-30 15:00:23.925 EDT 23802 LOG: duration: 338.512 ms statement: commit 2009-03-30 15:00:25.744 EDT 23802 LOG: duration: 272.170 ms statement: commit 2009-03-30 15:00:42.612 EDT 23802 LOG: duration: 310.099 ms statement: commit 2009-03-30 15:01:04.570 EDT 23802 LOG: duration: 254.574 ms statement: commit 2009-03-30 15:01:21.400 EDT 23802 LOG: duration: 245.433 ms statement: commit 2009-03-30 15:01:23.925 EDT 25345 LOG: autovacuum: processing database airwave 2009-03-30 15:01:38.286 EDT 23802 LOG: duration: 231.673 ms statement: commit 2009-03-30 15:02:12.017 EDT 23802 LOG: duration: 413.221 ms statement: commit 2009-03-30 15:02:23.954 EDT 25366 LOG: autovacuum: processing database airwave 2009-03-30 15:02:46.843 EDT 23802 LOG: duration: 268.504 ms statement: commit You can see in the process list that process 23802 is building up a lot of cpu time, as confirmed in the pg log we run. Can this be some native pg process opened by the DB itself, or is it some rogue connection server that we have to track down? TIA, Jim HP ProCurve
Re: [GENERAL] Installing PLPython - Version Problem
ray wrote: I am sorry for not providing a clear description of my problem. I want to us Python 2.6 but the PLPython seems to me looking for 2.5 which is not on my machine. Is there a way to have PostgreSQL work with Python 2.6? Recompile PostgreSQL, or at least the PL/Python extension, to use Python 2.6. You don't want to do this on Windows. It's really no fun. Just download and install Python 2.5. It installs and runs just fine in parallel with Python 2.6, and it'll get everything working nice and quickly. When PostgreSQL 8.4 is released, the Windows builds will probably be against Python 2.6. -- Craig Ringer -- 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] Postgres startup processes on linux?
Eshelman, James wrote: We’re running PG 8.1 on CentOS 5.0. When postgres starts the following processes apparently required by the DB itself get created: postgres 23784 1 0 13:55 ? 00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 23786 23784 0 13:55 ? 00:00:00 postgres: logger process postgres 23788 23784 0 13:55 ? 00:00:00 postgres: writer process postgres 23789 23784 0 13:55 ? 00:00:01 postgres: stats buffer process postgres 23790 23789 0 13:55 ? 00:00:00 postgres: stats collector process postgres 23802 23784 3 13:55 ? 00:00:58 postgres: airwave airwave [local] idle postgres 23803 23784 0 13:55 ? 00:00:12 postgres: airwave airwave [local] idle I understand what the first five are for. What are 23802 and 23803? ... Do a bit of sleuthing. It appears you have two connections (user airwave, db airwave) and the connections are local. Try select * from pg_stat_activity (though they are both showing local in ps). Also look for the connecting process (say, lsof -U | grep 5432) and track down the source. Do you possibly have a connection pool that starts up along with the db? Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Read a CLOB data from an Oracle table and INSERT it into a BYTEA column in Postgres using jdbc?
Hi,
Im reading from an Oracle table and inserting the values to a postgres table
thro' jdbc. Everything works fine except for the BLOB data in Oracle.
My code snippet ...
while (rs1.next())
{
for (int m=1;m=colCount;m++)
{
pstmt.setObject(m, rs1.getObject(m));
}
pstmt.executeUpdate();
connection2.commit();
System.out.println(DONE);
}
This inserts the data into teh postgres table but gives this error for a BLOB
datatype
Exception in thread main org.postgresql.util.PSQLException: ERROR: column
columnXXX is of type bytea but expression is of type bigint
Any thoughts?
Thanks
Sharmila
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Optimization Wizard
Hi, During the development of my application I once, somehow through an installed postgresql software got me to a page where I could find information on how to tune my db. I can't seem to find to that link again. I think it was to a page within the EnterpriseDB but that page seems to have significantly changed. Could someone please help me with this issue? / Jennifer
Re: [GENERAL] Optimization Wizard
2009/3/30 Jennifer Trey jennifer.t...@gmail.com: Hi, During the development of my application I once, somehow through an installed postgresql software got me to a page where I could find information on how to tune my db. I can't seem to find to that link again. I think it was to a page within the EnterpriseDB but that page seems to have significantly changed. Could someone please help me with this issue? / Jennifer Try: http://wiki.postgresql.org/wiki/Performance_Optimization Osvaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Schema for J2EE project
Using Postgresql 8.2.11 on Ubuntu 7.10.I am writing a J2EE project but do not want user specific schemas because this application sits on a production floor and any production employee can walk to a workstation and access the database to punch work time. I know that the public schema is not supposed to be used. Is the best practice to make one schema for the database and then delete public? Will using a schema other than public cause problems for users accessing the database with ODBC from Windows?Thank you, Margaret.
Re: [GENERAL] Read a CLOB data from an Oracle table and INSERT it into a BYTEA column in Postgres using jdbc?
You are fetching an Oracle LOB locator. I am not a guru of OCI let alone JDBC but AFAIK you should create a LOB descriptor, bind it to the locator and load the LOB data that you can insert into postgres.
Re: [GENERAL] i have a problem with judge some words contain chinese word!
On Mar 30, 2009, at 10:14 , shiguoying wrote:
Hello:
I have a problem with juding some words contain chinese word.
I found that with oracle.but i can't find it in postgresql.
oracle? has acsiistr and lengthb to deal with it.i wonna to know
postgresql how to !
??? thx !
from
eagle
You can use regexp_replace to remove sets of charcters and then
compare lengths or even the strings themselves:
SELECT some_text, LENGTH(some_text), re, LENGTH(re), some_text = re as
str_eq, LENGTH(some_text) = LENGTH(re) AS len_eq
FROM (SELECT some_text, regexp_replace(some_text, $re$[^[:ascii:]]
$re$, '', 'g') as re
FROM (VALUES ('foo'), ('foo bar baz'), ('foo.bar,baz'), ('foo
案')) AS samples(some_text)) AS s;
some_text | length | re | length | str_eq | len_eq
-++-+++
foo | 3 | foo | 3 | t | t
foo bar baz | 11 | foo bar baz | 11 | t | t
foo.bar,baz | 11 | foo.bar,baz | 11 | t | t
foo案 | 4 | foo | 3 | f | f
(4 rows)
Michael Glaesemann
grzm seespotcode net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/PGSQL arithmetic errors
Hi all! I'm developing an algorithm with PL/PGSQL using statistical operations from a table. I have several differences between expected results and the results generated by the function. I want to know if there are differences in arithmetic operation sintax or if there's any special arithmetical operators precedence between pl/pgsql and other languages. My code looks like this: FOR iterator1 IN SELECT period, AVG(col1) AS avg_col1, STDDEV(col1) AS dev_col1, AVG(col2) AS avg_col2, STDDEV(col2) AS dev_col2 FROM scheme.table GROUP BY period ORDER BY period LOOP FOR iterator2 IN SELECT period, id, col2, col1 FROM scheme.table WHERE col1 IS NOT NULL LOOP IF grav = 0 THEN _standata := (iterator2.col1 - iterator1.avg_col1) / iterator1.dev_col1; ELSE _standata := (iterator2.col2 - iterator1.avg_col2) / iterator1.dev_col2; END IF; UPDATE scheme.table SET standata = _standata WHERE id = iterator2.id AND period=iterator2.period; _standata := 0.0; END LOOP; END LOOP; Thanks! -- View this message in context: http://www.nabble.com/PL-PGSQL-arithmetic-errors-tp22795583p22795583.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] running two servers on one machine
That's it! Log file says could not create shared memory segment. It also says that I should change max_connections or shared_buffers. Error message claims that max_connections is 23, but postgresql.conf has this listed as 20. Error message claims that shared_buffers is 300, but postgresql.conf has this listed as 2400kB. What the heck is going on here? (no, the lines in the conf file are not commented out). How do I enforce the conf file entries? Thanks, Eric On Mar 29, 2009, at 8:19 PM, Tom Lane wrote: Eric Smith eric_h_sm...@mac.com writes: Yes, if I start one, but not the other, the process works just great. It's only when trying to run both that I have a problem. Have you looked into the postmaster logfile? One likely idea is that you don't have shmall cranked up high enough for two servers, which'd result in something like FATAL: could not create shared memory segment: Cannot allocate memory in the postmaster log. 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] partial TEXT search on an index
I don't think I understand how PG implements fulltext searching or if my search actually needs to use fulltext search. basically, I have a btree index on a SERIAL_NUMBER which is of sort like ABC12345 or AAA123434 or AAB131441 I would like to have search on the specific text of the SERIAL_NUMBER eg: select * from table where serial_number LIKE 'AAA%' does not use the index. What Do i need to do to have it recognise the partial index? (is it called?) Thanks -- 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] pgstattuple triggered checkpoint failure and database outage?
Stuart Bishop stu...@stuartbishop.net writes: I just had a brief outage on a production server running 8.3.6, which I suspect was triggered by me running a table bloat report making lots of pgstattuple calls. The first I got of it was the script I'd just kicked off died: could not open segment 1 of relation 1663/16409/11088101 (target block 131292): No such file or directory CONTEXT: writing block 131292 of relation 1663/16409/11088101 ... Doing an immediate shutdown and restart seems to have brought everything back online. What's the actual size of that relation now? Is it growing rapidly? (I'm trying to figure out whether those writes *should* have succeeded, or whether the block numbers were corrupt in memory.) 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] running two servers on one machine
Eric Smith eric_h_sm...@mac.com writes: Log file says could not create shared memory segment. It also says that I should change max_connections or shared_buffers. Error message claims that max_connections is 23, but postgresql.conf has this listed as 20. The autovacuum workers get added on. Error message claims that shared_buffers is 300, but postgresql.conf has this listed as 2400kB. Same thing, different units. Anyway, you left out the interesting part of the error message, ie the kernel error code. If it's Cannot allocate memory then you probably need to increase SHMALL. 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] running two servers on one machine
Eric Smith eric_h_sm...@mac.com writes: Following the queue on the postgres web page, I created /etc/ sysctl.conf and added the following five entries: kern.sysv.shmmax=4194304 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024 But when I try to start two servers, I get the same error. Should I be bumping these numbers up? Yes, substantially. That's 4MB of shmem per server (SHMMAX) and also 4MB system-wide (SHMALL, which for some reason is measured in 4KB pages not bytes). The *minimum* recommendation for decent performance is 32MB per server, so you'd want shmmax = 32MB and shmall = at least 64MB, maybe more to allow for some other apps trying to use some shmem. It appeared from your log dumps that initdb has set up the parameters to fit into 4MB of shmem, which is what it's supposed to do when faced with draconian shmmax settings, but you really want to bump up shared_buffers and perhaps max_connections once you get this fixed. 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] running two servers on one machine
Ahhh! Can't seem to make this go away! Here is the log file entry: FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=5432001, size=4030464, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 4030464 bytes), reduce PostgreSQL's shared_buffers parameter (currently 300) and/or its max_connections parameter (currently 23). The PostgreSQL documentation contains more information about shared memory configuration. Following the queue on the postgres web page, I created /etc/ sysctl.conf and added the following five entries: kern.sysv.shmmax=4194304 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024 But when I try to start two servers, I get the same error. Should I be bumping these numbers up? Eric On Mar 30, 2009, at 7:18 PM, Tom Lane wrote: Eric Smith eric_h_sm...@mac.com writes: Log file says could not create shared memory segment. It also says that I should change max_connections or shared_buffers. Error message claims that max_connections is 23, but postgresql.conf has this listed as 20. The autovacuum workers get added on. Error message claims that shared_buffers is 300, but postgresql.conf has this listed as 2400kB. Same thing, different units. Anyway, you left out the interesting part of the error message, ie the kernel error code. If it's Cannot allocate memory then you probably need to increase SHMALL. 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] string_to_array with empty input
Steve Crawford scrawf...@pinpointresearch.com writes:
I have a query that converts a string to an array with the
string_to_array function. Sometimes the input is an empty string (not a
null, but a string of zero-length). I had expected the result to be a
one-element array with an empty string as the first and only element but
instead it returned null. I looked at the docs and didn't find the
observed behavior documented.
The behavior is pretty intentional according to the source code:
/* return NULL for empty input string */
if (inputstring_len 1)
{
text_position_cleanup(state);
PG_RETURN_NULL();
}
I agree this seems less than consistent though, especially seeing
that you *don't* get a null for a zero-length separator, which if
anything is a more poorly defined case.
I doubt it'd be a good idea to back-patch a change for this,
but I could see altering the definition for 8.4.
Does anyone want to argue for keeping it the same? Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string? (It doesn't seem
like it to me, but maybe somebody thinks so.)
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] PL/PGSQL arithmetic errors
Just because a result is unexpected does not mean its an incorrect result. No postgresql follows the order of operations as expected. Now looking at the 2 For loops the First does not have a where clause and the Second has a Where not null this could be the cause of the problem. Another note you don't need to do this in nested For loops it can be done in a single select statement using nested queries or by using a join clause Example of a Left Join Select period, id, (col2-avgResults.col2)/AvgResults.dev_col2, (col1 - AvgResults.col1) / AvgResults.dev_col1 FROM scheme.table, Left Join (SELECT period, AVG(col1) AS avg_col1, STDDEV(col1) AS dev_col1, AVG(col2) AS avg_col2, STDDEV(col2) AS dev_col2 FROM scheme.table GROUP BY period Where col1 is not null ) AvgResults On AvgResults.period = scheme.table.period WHERE col1 IS NOT NULL Assuming i don't have any typos this should give you the results you are looking for and be faster. You can throw in a Case statement in the select testing for grav to limit the result down and speed things up to a single column and do the update that why. jc_mich wrote: Hi all! I'm developing an algorithm with PL/PGSQL using statistical operations from a table. I have several differences between expected results and the results generated by the function. I want to know if there are differences in arithmetic operation sintax or if there's any special arithmetical operators precedence between pl/pgsql and other languages. My code looks like this: FOR iterator1 IN SELECT period, AVG(col1) AS avg_col1, STDDEV(col1) AS dev_col1, AVG(col2) AS avg_col2, STDDEV(col2) AS dev_col2 FROM scheme.table GROUP BY period ORDER BY period LOOP FOR iterator2 IN SELECT period, id, col2, col1 FROM scheme.table WHERE col1 IS NOT NULL LOOP IF grav = 0 THEN _standata := (iterator2.col1 - iterator1.avg_col1) / iterator1.dev_col1; ELSE _standata := (iterator2.col2 - iterator1.avg_col2) / iterator1.dev_col2; END IF; UPDATE scheme.table SET standata = _standata WHERE id = iterator2.id AND period=iterator2.period; _standata := 0.0; END LOOP; END LOOP; Thanks!
Re: [GENERAL] pgstattuple triggered checkpoint failure and database outage?
On Tue, Mar 31, 2009 at 8:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: Stuart Bishop stu...@stuartbishop.net writes: I just had a brief outage on a production server running 8.3.6, which I suspect was triggered by me running a table bloat report making lots of pgstattuple calls. The first I got of it was the script I'd just kicked off died: could not open segment 1 of relation 1663/16409/11088101 (target block 131292): No such file or directory CONTEXT: writing block 131292 of relation 1663/16409/11088101 ... Doing an immediate shutdown and restart seems to have brought everything back online. What's the actual size of that relation now? Is it growing rapidly? (I'm trying to figure out whether those writes *should* have succeeded, or whether the block numbers were corrupt in memory.) I can't seem to find a file on disk named 11088101 or an entry in pg_class where relfilenode = 11088101. Are the allocated table oids always increasing? If so, I can pretty much guarantee that the missing relation was a temporary table or the index on the temporary table. It had a single integer column and maybe 50million rows. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ signature.asc Description: OpenPGP digital signature
Re: [GENERAL] string_to_array with empty input
Tom Lane wrote: I agree this seems less than consistent though, especially seeing that you *don't* get a null for a zero-length separator, which if anything is a more poorly defined case. I doubt it'd be a good idea to back-patch a change for this, but I could see altering the definition for 8.4. Does anyone want to argue for keeping it the same? Or perhaps argue that a zero-element array is a more sensible result than a one-element array with one empty string? (It doesn't seem like it to me, but maybe somebody thinks so.) regards, tom lane I like the array to contain single zero length string. A string was passed in although empty, its still a string not a NULL. Returning an empty array implies nothing was passed to the function although something was. That seems kinda odd to me also, give back what was sent in broken into an array. I use this and split_part allot in our database to break apart part numbers -- 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] pgstattuple triggered checkpoint failure and database outage?
Stuart Bishop stu...@stuartbishop.net writes: On Tue, Mar 31, 2009 at 8:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: What's the actual size of that relation now? Â Is it growing rapidly? (I'm trying to figure out whether those writes *should* have succeeded, or whether the block numbers were corrupt in memory.) I can't seem to find a file on disk named 11088101 or an entry in pg_class where relfilenode = 11088101. Are the allocated table oids always increasing? If so, I can pretty much guarantee that the missing relation was a temporary table or the index on the temporary table. It had a single integer column and maybe 50million rows. The OIDs increase till they wrap around, so what this sounds like is a problem with somebody fetching temporary-table blocks into shared memory (where they should never be), and then things going wrong after the owning backend drops the temp table (without having cleared out shared buffers, which it won't do because it doesn't think it needs to). Can you say what was the exact command(s) you were using with pgstattuple? 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: [HACKERS] [GENERAL] string_to_array with empty input
On Mar 30, 2009, at 8:26 PM, Tom Lane wrote:
Does anyone want to argue for keeping it the same? Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string? (It doesn't seem
like it to me, but maybe somebody thinks so.)
Hrm. There seems to be some disagreement about this among some
languages:
% perl -le '@r = split /-/, ; print length @r; print qq{$r[0]}'
1
% irb
puts ''.split('-')
= nil
So Perl returns a single element as Steve had been expecting, while
Ruby returns nil. I'm used to the Perl way, but I guess there's room
for various interpretations, including the current implementation,
with which Ruby would seem to agree.
Best,
David
--
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] pgstattuple triggered checkpoint failure and database outage?
On Tue, Mar 31, 2009 at 11:10 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Stuart Bishop stu...@stuartbishop.net writes:
On Tue, Mar 31, 2009 at 8:59 AM, Tom Lane t...@sss.pgh.pa.us wrote:
What's the actual size of that relation now? Is it growing rapidly?
(I'm trying to figure out whether those writes *should* have succeeded,
or whether the block numbers were corrupt in memory.)
I can't seem to find a file on disk named 11088101 or an entry in pg_class
where relfilenode = 11088101.
Are the allocated table oids always increasing? If so, I can pretty much
guarantee that the missing relation was a temporary table or the index on
the temporary table. It had a single integer column and maybe 50million rows.
The OIDs increase till they wrap around, so what this sounds like is a
problem with somebody fetching temporary-table blocks into shared memory
(where they should never be), and then things going wrong after the
owning backend drops the temp table (without having cleared out shared
buffers, which it won't do because it doesn't think it needs to). Can
you say what was the exact command(s) you were using with pgstattuple?
Get a list of everything I'm interested in:
SELECT pg_class.oid, nspname, relname
FROM pg_class,pg_namespace
WHERE relnamespace = pg_namespace.oid
AND relkind IN ('r', 't', 'i') -- normal table, toast, index
AND nspname IN ('public', '_sl')
ORDER BY nspname, relname
For every result, get the pgstattuple information I'm interested in
for my reports:
SELECT
table_len,
pg_size_pretty(table_len),
dead_tuple_len,
pg_size_pretty(dead_tuple_len),
free_space,
pg_size_pretty(free_space)
FROM pgstattuple(%(oid)s)
I might be passing objects into pgstattuple that it can't handle - the
system exploded before I could tune the table selection criteria. I
notice that my query will have asked pgstattuple for information
about the temporary table. Which appears to work when testing locally,
but I'm not engineering any race conditions.
--
Stuart Bishop stu...@stuartbishop.net
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
