Re: [GENERAL] UPDATES hang every 5 minutes
Thanks for all the replies. As of right now I think I have it narrowed down to checkpoints based on the iostat activity I see when the hangs occur as well as the checkpoint_timeout defaulting to 5 min. I've upped checkpoint_warnings to 3600 to confirm but also made a few other changes. I moved the pg_xlog dir to another disk (unfortunately it is the root volume) as well as made changes to the bgwriter settings as shown below (taken from a post in the pgsql-performance list) bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers scanned/round bgwriter_lru_maxpages = 200 # 0-1000 buffers max written/round bgwriter_all_percent = 10.0 # 0-100% of all buffers scanned/round bgwriter_all_maxpages = 600 # 0-1000 buffers max written/round I won't know until tomorrow if this solves anything but will post back to this thread for others who may experience the same problem in the future. Thanks again, Marc - Original Message From: Greg Smith <[EMAIL PROTECTED]> Cc: mr19 <[EMAIL PROTECTED]>; pgsql-general@postgresql.org Sent: Thursday, August 9, 2007 11:10:42 PM Subject: Re: [GENERAL] UPDATES hang every 5 minutes On Thu, 9 Aug 2007, Scott Marlowe wrote: > Wouldn't that be the other way around, set checkpoint_warning to 1 so > it triggers every time the checkpoint happens? The log message appears if the checkpoints happen more frequently than the value, so setting to 1 would only trigger a warning if you got a checkpoint more than once a second. Using the max of 3600 will spit out a warning every time there's a checkpoint as long as they happen more than once per hour, which means in any normal configurations you'll get every one of them logged. > Note you don't have to stop / restart, just reload, and the default > checkpoint timeout is 5 minutes. I suggested a couple of other changes as well which is why I suggested touching things during a maintenance window, but as you and Tom have pointed out you can adjust checkpoint_warning without taking the server down. The fact that the default timeout matches exactly when he's seeing the slowdowns, once every five minutes, is almost certainly the smoking gun that this is a checkpoint issue, but Marc should confirm that before just assuming it's the case. > Would increasing the checkpoint_timeout and adjusting the bgwriter > settings help here? That's why I suggested he give some more configuration info before anyone could say how to solve the problem. For all we know, moving checkpoint_timeout upward will just shift the period between pauses to one based on checkpoint_segments instead, which might not be a big difference. Also, increasing the timeout has the potential to make the spikes even longer when they finally do happen. Engineering the pauses out using the background writer in this sort of situation (very frequent updates) can be very hard to do. There's new code coming in 8.3 that addresses this issue head-on, it can be tricky to accomplish in the current production releases. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Internal Postgre SQL documentation
Cantor wrote: On Aug 7, 1:26 pm, Arthernan <[EMAIL PROTECTED]> wrote: I want to learn how a real database works. And I am about to start reading the Postgre source code. Are there any online documents that may document the code? Even if it was a general guideline. Any information will be greatly appreciated. Arturo Hernandez OK, I did find doc/FAQ_DEV inside the source tree. And it did include these two links for question "1.6) Where can I learn more about the code?" http://www.postgresql.org/developer http://neilconway.org/talks/hacking/ http://doxygen.postgresql.org/ is probably usefull too. -- Tommy Gildseth ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Confusing performance of specific query
Adam Endicott <[EMAIL PROTECTED]> writes: > Here's the output from explain analyze. Wow, so the differential is all in the sort step. 8.2 does have improved sorting code, but I don't think that explains the difference, especially not for a mere 16000 rows to be sorted. Do you have comparable work_mem settings on both machines? Another thing to look at, if any of the sort key columns are textual, is whether the lc_collate settings are the same. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Confusing performance of specific query
Here's the output from explain analyze.
My desktop:
-
Unique (cost=6732.86..7380.50 rows=504 width=677) (actual
time=844.345..1148.705 rows=65 loops=1)
-> Sort (cost=6732.86..6773.34 rows=16191 width=677) (actual
time=844.341..1099.446 rows=16191 loops=1)
Sort Key: movies_movie.title, movies_movie.id,
movies_movie.cinemasource_id, movies_movie.mpaa_rating_id,
movies_movie.advisory, movies_movie.teaser, movies_movie.review_id,
movies_movie.runtime, movies_movie.studio_url,
movies_movie.distributor_id, movies_movie.synopsis,
movies_movie.stars, movies_movie.main_image_id, movies_movie.trailer,
movies_movie.editors_pick
-> Hash Join (cost=56.34..700.88 rows=16191 width=677)
(actual time=6.871..53.314 rows=16191 loops=1)
Hash Cond: (movies_movie__moviescreening.movie_id =
movies_movie.id)
-> Seq Scan on movies_moviescreening
movies_movie__moviescreening (cost=0.00..421.91 rows=16191 width=4)
(actual time=0.098..19.090 rows=16191 loops=1)
Filter: (id IS NOT NULL)
-> Hash (cost=50.04..50.04 rows=504 width=677)
(actual time=6.676..6.676 rows=504 loops=1)
-> Seq Scan on movies_movie (cost=0.00..50.04
rows=504 width=677) (actual time=0.055..2.717 rows=504 loops=1)
Total runtime: 1270.106 ms
(10 rows)
Production server:
--
Unique (cost=7412.10..8059.74 rows=507 width=679) (actual
time=14465.619..14559.558 rows=65 loops=1)
-> Sort (cost=7412.10..7452.58 rows=16191 width=679) (actual
time=14465.613..14503.436 rows=16191 loops=1)
Sort Key: movies_movie.title, movies_movie.id,
movies_movie.cinemasource_id, movies_movie.mpaa_rating_id,
movies_movie.advisory, movies_movie.teaser, movies_movie.review_id,
movies_movie.runtime, movies_movie.studio_url,
movies_movie.distributor_id, movies_movie.synopsis,
movies_movie.stars, movies_movie.main_image_id, movies_movie.trailer,
movies_movie.editors_pick
-> Hash Join (cost=53.34..740.11 rows=16191 width=679)
(actual time=3.470..94.064 rows=16191 loops=1)
Hash Cond: ("outer".movie_id = "inner".id)
-> Seq Scan on movies_moviescreening
movies_movie__moviescreening (cost=0.00..443.91 rows=16191 width=4)
(actual time=0.040..34.362 rows=16191 loops=1)
Filter: (id IS NOT NULL)
-> Hash (cost=52.07..52.07 rows=507 width=679)
(actual time=3.366..3.366 rows=507 loops=1)
-> Seq Scan on movies_movie (cost=0.00..52.07
rows=507 width=679) (actual time=0.009..1.638 rows=507 loops=1)
Total runtime: 14568.621 ms
(10 rows)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] UPDATES hang every 5 minutes
On Thu, 9 Aug 2007, Scott Marlowe wrote: Wouldn't that be the other way around, set checkpoint_warning to 1 so it triggers every time the checkpoint happens? The log message appears if the checkpoints happen more frequently than the value, so setting to 1 would only trigger a warning if you got a checkpoint more than once a second. Using the max of 3600 will spit out a warning every time there's a checkpoint as long as they happen more than once per hour, which means in any normal configurations you'll get every one of them logged. Note you don't have to stop / restart, just reload, and the default checkpoint timeout is 5 minutes. I suggested a couple of other changes as well which is why I suggested touching things during a maintenance window, but as you and Tom have pointed out you can adjust checkpoint_warning without taking the server down. The fact that the default timeout matches exactly when he's seeing the slowdowns, once every five minutes, is almost certainly the smoking gun that this is a checkpoint issue, but Marc should confirm that before just assuming it's the case. Would increasing the checkpoint_timeout and adjusting the bgwriter settings help here? That's why I suggested he give some more configuration info before anyone could say how to solve the problem. For all we know, moving checkpoint_timeout upward will just shift the period between pauses to one based on checkpoint_segments instead, which might not be a big difference. Also, increasing the timeout has the potential to make the spikes even longer when they finally do happen. Engineering the pauses out using the background writer in this sort of situation (very frequent updates) can be very hard to do. There's new code coming in 8.3 that addresses this issue head-on, it can be tricky to accomplish in the current production releases. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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] Parrallel query execution for UNION ALL Queries
On Jul 18, 11:50 am, [EMAIL PROTECTED] ("Jim C. Nasby") wrote:
> On Wed, Jul 18, 2007 at 11:30:48AM -0500, Scott Marlowe wrote:
> > EnterpriseDB, a commercially enhanced version of PostgreSQL can do
> > query parallelization, but it comes at a cost, and that cost is making
> > sure you have enough spindles / I/O bandwidth that you won't be
> > actually slowing your system down.
>
> I think you're thinking ExtendDB. :)
Well, now they are one and the same - seems that EnterpriseDB bought
ExtenDB and are calling it GridSQL.
Now that it's a commercial endeavor competing with Greenplum, Netezza
and Teradata I'd be very interested in some real world examples of
ExtenDB/GridSQL.
- Luke
---(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] UPDATES hang every 5 minutes
On 8/9/07, Greg Smith <[EMAIL PROTECTED]> wrote: > On Tue, 7 Aug 2007, mr19 wrote: > > > I have a process that updates ~ 1500 rows in a table once a second. Every 5 > > minutes (almost exactly) the update takes ~ 15 seconds (normally < 1). > > Lots of updates will trigger checkpoints and, if you have auto-vacuum > turned on, regular vacuum activity--either of which could contribute to > your long delays. A 15 second long pause sounds more like checkpoints to > me. Try increasing checkpoint_warning in your postgresql.conf file to its > maximum of 3600 and restart the server when you can tolerate a small > service disruption; that will get you a note in the logs every time one > happens so you can see if they line up with the slowdowns. Wouldn't that be the other way around, set checkpoint_warning to 1 so it triggers every time the checkpoint happens? Note you don't have to stop / restart, just reload, and the default checkpoint timeout is 5 minutes. Would increasing the checkpoint_timeout and adjusting the bgwriter settings help here? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [PERFORM] Dell Hardware Recommendations
oops On 8/9/07, Decibel! <[EMAIL PROTECTED]> wrote: > You forgot the list. :) > > On Thu, Aug 09, 2007 at 05:29:18PM -0500, Scott Marlowe wrote: > > On 8/9/07, Decibel! <[EMAIL PROTECTED]> wrote: > > > > > Also, a good RAID controller can spread reads out across both drives in > > > each mirror on a RAID10. Though, there is an argument for not doing > > > that... it makes it much less likely that both drives in a mirror will > > > fail close enough to each other that you'd lose that chunk of data. > > > > I'd think that kind of failure mode is pretty uncommon, unless you're > > in an environment where physical shocks are common. which is not a > > typical database environment. (tell that to the guys writing a db for > > a modern tank fire control system though :) ) > > > > > Speaking of failures, keep in mind that a normal RAID5 puts you only 2 > > > drive failures away from data loss, > > > > Not only that, but the first drive failure puts you way down the list > > in terms of performance, where a single failed drive in a large > > RAID-10 only marginally affects performance. > > > > > while with RAID10 you can > > > potentially lose half the array without losing any data. > > > > Yes, but the RIGHT two drives can kill EITHER RAID 5 or RAID10. > > > > > If you do RAID5 > > > with multiple parity copies that does change things; I'm not sure which > > > is better at that point (I suspect it matters how many drives are > > > involved). > > > > That's RAID6. The primary advantages of RAID6 over RAID10 or RAID5 > > are two fold: > > > > 1: A single drive failure has no negative effect on performance, so > > the array is still pretty fast, especially for reads, which just suck > > under RAID 5 with a missing drive. > > 2: No two drive failures can cause loss of data. Admittedly, by the > > time the second drive fails, you're now running on the equivalent of a > > degraded RAID5, unless you've configured >2 drives for parity. > > > > On very large arrays (100s of drives), RAID6 with 2, 3, or 4 drives > > for parity makes some sense, since having that many extra drives means > > the RAID controller (SW or HW) can now have elections to decide which > > drive might be lying if you get data corruption. > > > > Note that you can also look into RAID10 with 3 or more drives per > > mirror. I.e. build 3 RAID-1 sets of 3 drives each, then you can lose > > any two drives and still stay up. Plus, on a mostly read database, > > where users might be reading the same drives but in different places, > > multi-disk RAID-1 makes sense under RAID-10. > > > > While I agree with Merlin that for OLTP a faster drive is a must, for > > OLAP, more drives is often the real key. The high aggregate bandwidth > > of a large array of SATA drives is an amazing thing to watch when > > running a reporting server with otherwise unimpressive specs. > > > > -- > Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] > Give your computer some brain candy! www.distributed.net Team #1828 > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Interpreting statistics collector output
On Aug 8, 2007, at 6:08 PM, Decibel! wrote: Something else I like to look at is pg_stat_all_tables seq_scan and seq_tup_read. If seq_scan is a large number and seq_tup_read/ seq_scan is also large, that indicates that you could use an index on that table. If seq_tup_read / seq_scan is large relative to the number of rows in the table, wouldn't that imply that those sequential scans are often returning most of the rows in the table? In that case, would an index help much or is a sequential scan the expected result? -- Steve Madsen <[EMAIL PROTECTED]> Light Year Software, LLC http://lightyearsoftware.com ZingLists: Stay organized, and share lists online. http://zinglists.com ---(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] UPDATES hang every 5 minutes
Greg Smith <[EMAIL PROTECTED]> writes:
> Try increasing checkpoint_warning in your postgresql.conf file to its
> maximum of 3600 and restart the server when you can tolerate a small
> service disruption;
You don't need a server restart to change checkpoint_warning --- SIGHUP
("pg_ctl reload") should be enough.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] UPDATES hang every 5 minutes
mr19 <[EMAIL PROTECTED]> writes: > I have a process that updates ~ 1500 rows in a table once a second. Every 5 > minutes (almost exactly) the update takes ~ 15 seconds (normally < 1). Checkpoints? > I have run htop/top on the machine during this time period and do not see > anything unusual. Try watching "vmstat 1" --- your machine is probably I/O bound not CPU bound. 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] UPDATES hang every 5 minutes
On Tue, 7 Aug 2007, mr19 wrote: I have a process that updates ~ 1500 rows in a table once a second. Every 5 minutes (almost exactly) the update takes ~ 15 seconds (normally < 1). Lots of updates will trigger checkpoints and, if you have auto-vacuum turned on, regular vacuum activity--either of which could contribute to your long delays. A 15 second long pause sounds more like checkpoints to me. Try increasing checkpoint_warning in your postgresql.conf file to its maximum of 3600 and restart the server when you can tolerate a small service disruption; that will get you a note in the logs every time one happens so you can see if they line up with the slowdowns. You may want to crank up log_min_messages to see what's going on as well, at least to INFO as a start. If you can tolerate the verbosity for a bit while testing, you'll probably find out more than ever wanted to know about what internals are happening during your slowdown point if you increase that to DEBUG2, then set log_min_duration_statement to 2 seconds so it logs all the long updates. Probably want to put "%m" somewhere in your log_line_prefix as well so you can see timestamps on all that data. To get more specific advice, report if you see anything interesting in your log files and send some details about the PostgreSQL version you're using and the settings in the postgresql.conf file that you've changed from their defaults. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UPDATES hang every 5 minutes
mr19 wrote: > I have a process that updates ~ 1500 rows in a table once a second. Every 5 > minutes (almost exactly) the update takes ~ 15 seconds (normally < 1) autovacuum_naptime perhaps? Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Confusing performance of specific query
Adam Endicott <[EMAIL PROTECTED]> writes: > When I run EXPLAIN ANALYZE on this query, it takes something like > 1200ms on my desktop (Dual 2GHz G5 Mac - 1.5 GB RAM for reference) and > about 14000ms on the production server (quad processor, 8 GB RAM, > running Ubuntu). There are about 500 rows in the movies_movie table, > and about 16k rows in the movies_moviescreening table. The data is the > same on both machines. My desktop is running PostgreSQL 8.2.3, and the > production server is running 8.1.9. > Since I don't know much about how to diagnose this, I don't know what > other information to give, so let me know if I've left out something > crucial. Uh ... the actual outputs from the two EXPLAIN ANALYZEs, perhaps? The most likely bets are (1) something different about the postgresql.conf settings on the two machines, or (2) the 8.2 planner is smarter than the 8.1 planner and is choosing a better plan. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] UPDATES hang every 5 minutes
Keep an eye on pg_stat_activity and pg_locks to see if any lock contention is going on. mr19 wrote: I have a process that updates ~ 1500 rows in a table once a second. Every 5 minutes (almost exactly) the update takes ~ 15 seconds (normally < 1). I have run htop/top on the machine during this time period and do not see anything unusual. I am running postgres 8.1.8 on a FC6 box. Any type of internal accounting/logging set to 5 minute intervals by default within postgres? Right now I am looking at pg_stats activity to see if there is anything there but so far have had no luck. TIA Marc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Internal Postgre SQL documentation
I want to learn how a real database works. And I am about to start reading the Postgre source code. Are there any online documents that may document the code? Even if it was a general guideline. Any information will be greatly appreciated. Arturo Hernandez ---(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] Internal Postgre SQL documentation
On Aug 7, 1:26 pm, Arthernan <[EMAIL PROTECTED]> wrote: > I want to learn how a real database works. And I am about to > start reading the Postgre source code. > > Are there any online documents that may document the code? Even > if it was a general guideline. > > Any information will be greatly appreciated. > > Arturo Hernandez OK, I did find doc/FAQ_DEV inside the source tree. And it did include these two links for question "1.6) Where can I learn more about the code?" http://www.postgresql.org/developer http://neilconway.org/talks/hacking/ I think they will keep me busy for some time. And since I already started a thread, it's an opportinuty to add to the list. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] truncate transaction log
On Aug 7, 9:57 pm, [EMAIL PROTECTED] ("Simon Riggs") wrote:
> On Sun, 2007-08-05 at 03:45 -0700, Sergei Shelukhin wrote:
> > Is there any way to truncate WAL log in postgres?
> > We want to use full-backup strategy where we stop the server and copy
> > the data directory, however WAL log is taking dozens gigabytes of
> > spaces.
> > Is there any way to remove it while keeping the database operational/
> > restore-able by copying it back?
>
> Reduce the setting of checkpoint_segments to something more realistic.
>
Hmm. Reduced to 60, during the db conversion from MySQL Postgres used
to complain about this setting so I increased it to the sky. Thanks :)
---(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] finding out vacuum completion %, and vacuum VS vacuum full
> If not, dump and restore the table. Unfortunately we do not have adequate disk space, we wanted to reduce the database size in order to back it up, cause there is no more space for backups either 0_o Is there any way to prevent Dump & restore - you mean pg_dump? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] How I can know a back up database is up to date
Hello My database is restored from a dump file every day. How I know that this database is up to date (as it has no timestamp in any table). If I create a file, I can know when I created it by seeing its property. How I can do the same thing with a back up database. Ta. ---(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] Connecting from one computer to another over lan
I've installed postgresql 8.2 on a windows vista machine and are trying to connect to it from another one. the server has ip 192.168.1.100 and the client 192.168.1.102 As I understand it, I should make some configuration changes in pg_hba.conf to make this happen. Both machines has both ipv4 and ipv6 installed (Vista standard I guess). Ive tried some different settings in the pg_hba.conf (trying one at a time, commenting out the rest): # IPv4 local connections: #hostall all 127.0.0.1/32 md5 #hostall all 192.168.0.0/16md5 hostall all 0.0.0.0/0 md5 # IPv6 local connections: #hostall all ::1/128 md5 I've also changed listten_addresses in postgresql.conf to: listen_addresses = '*' - I've installed VmWare player on the server. I guess that it's why it have so many adapters: Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7dba:9969:999b:5bfe%11 IPv4 Address. . . . . . . . . . . : 192.168.1.100 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.1.1 Wireless LAN adapter Wireless Network Connection: Media State . . . . . . . . . . . : Media disconnected Connection-specific DNS Suffix . : Ethernet adapter Local Area Connection 2: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::440f:5179:acc9:5799%14 IPv4 Address. . . . . . . . . . . : 192.168.243.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : Ethernet adapter Local Area Connection 3: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::d48:1d79:2215:543a%16 IPv4 Address. . . . . . . . . . . : 192.168.11.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : Tunnel adapter Local Area Connection* 6: Media State . . . . . . . . . . . : Media disconnected Connection-specific DNS Suffix . : Tunnel adapter Local Area Connection* 7: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::5efe:192.168.1.100%12 Default Gateway . . . . . . . . . : Tunnel adapter Local Area Connection* 10: Media State . . . . . . . . . . . : Media disconnected Connection-specific DNS Suffix . : Tunnel adapter Local Area Connection* 11: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::5efe:192.168.243.1%15 Default Gateway . . . . . . . . . : Tunnel adapter Local Area Connection* 12: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::5efe:192.168.11.1%17 Default Gateway . . . . . . . . . : -- And the client: Ethernet-anslutning Anslutning till lokalt nätverk: Anslutningsspecifika DNS-suffix . : IPv4-adress . . . . . . . . . . . : 192.168.1.102 Nätmask . . . . . . . . . . . . . : 255.255.255.0 Standard-gateway. . . . . . . . . : 192.168.1.1 Tunnelanslutning: Anslutning till lokalt nätverk*: Tillstånd . . . . . . . . . . . . : Frånkopplad Anslutningsspecifika DNS-suffix . : Tunnelanslutning: Anslutning till lokalt nätverk* 2: Anslutningsspecifika DNS-suffix . : Länklokal IPv6-adress . . . . . . : fe80::5efe:192.168.1.102%10 Standard-gateway. . . . . . . . . : (sorry for the swedish ;) -- Any suggestions? -- View this message in context: http://www.nabble.com/Connecting-from-one-computer-to-another-over-lan-tf4237412.html#a12056368 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Unable to connect to PostgreSQL server via PHP
Hi I'm trying to set up a new webserver running php and pgsql. PHP was connecting to postgres but I needed to install the php-gd module and now I get the error... "PHP Warning: pg_connect() [href='function.pg-connect'>function.pg-connect]: Unable to connect to PostgreSQL server: could not connect to server: Permission denied\n\tIs the server running on host "localhost" and accepting\n\tTCP/IP connections on port 5432?" and I'm at a loss can anyone tell me why it's not connecting? Thanks This bit's I know are... http://xyala.cap.ed.ac.uk/php_info.php say's php's configured for pgsql [EMAIL PROTECTED] telnet localhost 5432 Trying 127.0.0.1... Connected to localhost.localdomain (127.0.0.1). Escape character is '^]'. Connection closed by foreign host. [EMAIL PROTECTED] [EMAIL PROTECTED] less /var/lib/pgsql/data/pg_hba.conf # TYPE DATABASEUSERCIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only #local all all ident sameuser local all all trust # IPv4 local connections: #hostall all 127.0.0.1/32 ident sameuser hostall all 127.0.0.1/32 trust # IPv6 local connections: #hostall all ::1/128 ident sameuser hostall all ::1/128 trust [EMAIL PROTECTED] grep 'listen' /var/lib/pgsql/data/postgresql.conf # "pg_ctl reload". Some settings, such as listen_address, require #listen_addresses = 'localhost' # what IP interface(s) to listen on; listen_addresses = '*' [EMAIL PROTECTED] [EMAIL PROTECTED] less /etc/php.d/pgsql.ini ; Enable pgsql extension module extension=pgsql.so the server I'm going to replace is running the same versions of PHP and postgres http://zeldia.cap.ed.ac.uk/php_info.php The /etc/php.ini files on the two machines are the same and the /var/lib/pgsql/data/postgresql.conf files are only different because I've set listen_addresses = '*' on the new server (xyala) to see if I can make it work. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] List tables in load order
Thanks for the tip -- I'll check into it. Sorry for top-posting but my email reader is challenged. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.) -Original Message- From: Kristo Kaiv [mailto:[EMAIL PROTECTED] Sent: Tue 8/7/2007 3:58 PM To: Gregory Williamson Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] List tables in load order On 02.08.2007, at 4:16, Gregory Williamson wrote: > I am not sure if this is the appropriate list -- please point me at > the correct one if not. > > I'm trying to create a procedure that would let me retrieve a list > of tables and views in a database that will be used to control the > order in which lookup data is created/loaded. So, much simplified, > if table references table B, which in turn references table A, we > want output to list table A, B and C in that order. > > I'm sure that this exists -- the pg_dump command must use some > similar algorithm to decide in which order to load tables, but I > can't see to puzzle this out. > > Can anyone provide me with some clues, appropriate RTFM references, > etc. ? > > Apologies for any duplicate postings -- had issues with my sign up. > This is currently one of the main topics in skytools list. Check the archives: http://pgfoundry.org/pipermail/skytools-users/2007-July/74.html http://pgfoundry.org/pipermail/skytools-users/2007-August/thread.html AFAIK they don't have it quite ready yet but shouldn't take long... Kristo Kaiv http://kaiv.wordpress.com (PostgreSQL blog)
[GENERAL] Solved: Getting the field names for a given table
For those who need to know the fields that a certain table has in a postgresql database, here is the SQL statement: SELECT DISTINCT attname, relname FROM pg_attribute pa, pg_class pc, pg_tables pt WHERE pa.attrelid=pc.oid AND pc.relname=pt.tablename AND pt.schemaname='public' AND attstattarget=-1 AND relname IN (SELECT tablename FROM pg_tables WHERE schemaname='public') ORDER BY relname; Notice that you can replace ***relname IN (SELECT tablename FROM pg_tables WHERE schemaname='public')*** with ***relname='your_desired_tablename'*** and will do the work only for an specific table. For the newbies: you can specify the schemaname if needed. Daniel ---(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] UPDATES hang every 5 minutes
I have a process that updates ~ 1500 rows in a table once a second. Every 5 minutes (almost exactly) the update takes ~ 15 seconds (normally < 1). I have run htop/top on the machine during this time period and do not see anything unusual. I am running postgres 8.1.8 on a FC6 box. Any type of internal accounting/logging set to 5 minute intervals by default within postgres? Right now I am looking at pg_stats activity to see if there is anything there but so far have had no luck. TIA Marc -- View this message in context: http://www.nabble.com/UPDATES-hang-every-5-minutes-tf4231199.html#a12037580 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Are these two creation commands functionally identical?
I want to to know if these two are functionally equivalent. Is this:
Create table "users"
(
"userid" BigSerial NOT NULL,
"name" Varchar(20),
primary key ("userid")
) Without Oids;
Create table "sales"
(
"saleid" BigSerial NOT NULL,
"userid" Bigint NOT NULL,
"parent_saleid" Bigint NOT NULL,
primary key ("saleid")
) Without Oids;
Alter table "sales" add foreign key ("userid") references
"users" ("userid") on update restrict on delete restrict;
Alter table "sales" add foreign key ("parent_saleid") references
"sales" ("saleid") on update restrict on delete restrict;
Is the above functionally identical to:
Create table "users"
(
"userid" BigSerial NOT NULL,
"name" Varchar(20),
primary key ("userid")
) Without Oids;
Create table "sales"
(
"saleid" BigSerial NOT NULL,
"userid" bigint references users(userid),
"parent_saleid" bigint references sales(saleid),
primary key ("saleid")
) Without Oids;
Using postgreSQL 8.1 if it matters, thanks.
---(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] Using function like where clause
On Aug 6, 3:44 pm, [EMAIL PROTECTED] (Ranieri Mazili) wrote: > Hello, > > I have 2 questions. > 1) Can I use a function that will return a string in a where clause like > bellow? > > select * > from table > where my_function_making_where() > and another_field = 'another_think' > > 2) Can I use a function that will return a string to return the list of > columns that I want to show like below? > > select my_function_making_list_of_columns() > from table > where field_test = 'mydatum' > > Thanks Look up the quote_ident() function in the docs, you'll likely need that for both cases. Kev ---(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] Permission ALTER PASSWORD
I have problem with permission, I need to use a user no SUPERUSER. I use commands: CREATE ROLE $USER LOGIN; ALTER user $USER noCREATEDB NOCREATEROLE noCREATEUSER NOINHERIT; ALTER USER $USER with password 'XX'; REVOKE create on SCHEMA public from public; revoke all on schema PUBLIC FROM $USER; With these commands MY user $USER don't have permission to create table and another thing. But I need that $USER can't have permission to change your password with: psql -d $BD -h $HOST -U $USER # ALTER $USER maluco with password 'YYY'; After user $USER execute this ALTER, it get change PASSWORD. Could I block command ALTER password to user $USER?
[GENERAL] Installing Postgresql 8.2 on Windows Vista
Hi I am having problems installing Postgressql 8.2 on Windows Vista. The first problem I had was related to the UAC which I now have turned off. But the last problem is that the installer stops when it can't runt initdb. At this stage it rolls back an removes any possibilities to run initdb manually. Is it even possible to install it with the Windows-installer in the 8.2.4 binary. It worked fine on Windows XP. If it is possible, how should it be done? Does anyone have a step-by-step guide? I would appreciate any constructive help on this. Thanks /Johan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Confusing performance of specific query
I'm having an issue with a specific query, and I don't really know
where to start figuring out what's going on. I'm pretty new to
PostgreSQL in specific, and I'm not much of a database/SQL guru in
general. I've got one query that is consistently taking 10X longer to
run on a production machine than on my desktop. I haven't noticed
other queries suffering similar performance issues on the production
machine. Here's the query in question:
--
SELECT DISTINCT
"movies_movie"."id","movies_movie"."title","movies_movie"."cinemasource_id","movies_movie"."mpaa_rating_id","movies_movie"."advisory","movies_movie"."teaser","movies_movie"."review_id","movies_movie"."runtime","movies_movie"."studio_url","movies_movie"."distributor_id","movies_movie"."synopsis","movies_movie"."stars","movies_movie"."main_image_id","movies_movie"."trailer","movies_movie"."editors_pick"
FROM "movies_movie" INNER JOIN "movies_moviescreening" AS
"movies_movie__moviescreening" ON "movies_movie"."id" =
"movies_movie__moviescreening"."movie_id" WHERE
("movies_movie__moviescreening"."id" IS NOT NULL) ORDER BY
"movies_movie"."title" ASC;
--
I'm using a web frame work with an ORM (Django), so that's where this
query originates from - so it might not be the best way to do what I'm
trying to accomplish, but right now I'm more interested in the
performance difference between the two machines than I am in changing
this query to something better.
When I run EXPLAIN ANALYZE on this query, it takes something like
1200ms on my desktop (Dual 2GHz G5 Mac - 1.5 GB RAM for reference) and
about 14000ms on the production server (quad processor, 8 GB RAM,
running Ubuntu). There are about 500 rows in the movies_movie table,
and about 16k rows in the movies_moviescreening table. The data is the
same on both machines. My desktop is running PostgreSQL 8.2.3, and the
production server is running 8.1.9.
Since I don't know much about how to diagnose this, I don't know what
other information to give, so let me know if I've left out something
crucial.
Any help would be greatly appreciated.
Thanks,
Adam
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Internal Postgre SQL documentation
On Aug 7, 1:26 pm, Arthernan <[EMAIL PROTECTED]> wrote: > I want to learn how a real database works. And I am about to > start reading the Postgre source code. > Are there any online documents that may document the code? Even > if it was a general guideline. > Any information will be greatly appreciated. > Arturo Hernandez OK, I did find doc/FAQ_DEV inside the source tree. And it did include these two links for question "1.6) Where can I learn more about the code?" http://www.postgresql.org/developer http://neilconway.org/talks/hacking/ I think they will keep me busy for some time. And since I already started a thread, it's an opportinuty to add to the list. ---(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] clustering failover... ala Oracle Parallel server
clustering fail over... ala Oracle Parallel server How can the server be setup in a cluster for load-balancing and failover like perhaps OPS? How does the Postges solution compare to an Oracle? MSSQL? MySQL solution? Thank! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Allowing LAN connections
On 8/9/07, Jonas Gauffin <[EMAIL PROTECTED]> wrote: > I've installed postgresql 8.2 on a windows vista machine and are trying to > connect to it from another one. > the server has ip 192.168.1.100 and the client 192.168.1.102 Before people start wrecking their brains on the postgres end have you established that the VMs can see each other on the network at all? And ruled out Vista security playing tricks on you? Cheers, Andrej ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Bytea question with \208
On Thu, Aug 09, 2007 at 04:16:15PM -0400, Woody Woodring wrote: > My bad, the table I was looking (8.7) at had the first column as the > decimal representation and I did notice that the numbers changed as they > moved right. > > Is there a way for bytea to take a hex number, or do I need to convert the > bit stream to octal numbers? to_hex()? > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: Thursday, August 09, 2007 2:14 PM > To: Woody Woodring > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Bytea question with \208 > > "Woody Woodring" <[EMAIL PROTECTED]> writes: > > Could someone explain why \208 is not a valid syntax for bytea? > > Aren't those escapes octal? > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpFsLPGQCFcG.pgp Description: PGP signature
Re: [GENERAL] Modified FIFO queue and insert rule
On Thu, Aug 09, 2007 at 06:14:43PM +0200, Leif B. Kristensen wrote:
> On Wednesday 8. August 2007 15:12, Alban Hertroys wrote:
> >You should probably use a trigger (a before one maybe) instead of a
> > rule.
>
> I tried that too, but I'm still quite shaky on how to write triggers,
> and the same thing happened there: the inserted record was immediately
> deleted. I solved the problem temporarily with two lines in PHP:
You have to use a BEFORE trigger for this to work, unless you're careful
about how you build your where clause. The AFTER trigger is going to see
the row that you just inserted, so you'd have to explicitly exclude it
from the DELETE.
> function set_last_selected_place($place) {
> pg_query("DELETE FROM recent_places WHERE place_fk = $place");
> pg_query("INSERT INTO recent_places (place_fk) VALUES ($place)");
> }
>
> As my application is single-user, and everything is already wrapped up
> in a transaction anyway, there's no real problem with this. But I'd
> still like to understand how to do it 'properly' inside the DB.
Better than what you're doing right now would be to wrap everything into
a function and just call that. Depending on your design, that could be
more (or less) "correct" than trying to do it with a trigger.
--
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
pgpDGQcJuNi9q.pgp
Description: PGP signature
Re: [GENERAL] Time for Vacuum vs. Vacuum Full
On Thu, Aug 09, 2007 at 10:22:57AM -0400, Vivek Khera wrote: > > On Aug 9, 2007, at 9:38 AM, Brad Nicholson wrote: > > >I have the times that it takes to to do a regular > >vacuum on the clusters, will vacuum full take longer? > > almost certainly it will, since it has to move data to compact pages > rather than just tagging the rows as reusable. > > you can speed things up by dropping your indexes first, then running > vacuum full, then re-creating your indexes. this will make for > better (more compact) indexes too. > > as for how much longer, I don't know how to estimate that. A generally easier approach would be to cluster the tables on an appropriate index. That does re-write the table from scratch, but in cases of bad bloat that can actually be a lot faster. One thing you can do to test this out is to setup another copy of the database using PITR or some other file-based copy mechanism and try running VACUUM FULL vs CLUSTER. Note that a copy obtained via pg_dump obviously won't work for this. :) -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpIHUNsPsIZw.pgp Description: PGP signature
Re: [GENERAL] Sylph Searcher
Is there a way to get this to work remotely? IE: is there an indexing part that can be run on the mail server that you'd connect to remotely? On Thu, Aug 09, 2007 at 05:30:13PM +0900, Tatsuo Ishii wrote: > Hi, > > I made a small demonstration for Sylph Searcher at Linux World at SF > and was asked by Josh Berkus where he can download it. I would like to > share the info with PostgreSQL users. Here is the URL: > > http://sylpheed.sraoss.jp/en/download.html#searcher > > Those who are not familiar with Syph Searcher, here are brief > explanation: > > Sylph-Searcher is a PostgreSQL+tsearch2 application that enables fast > full-text search of messages stored in mailboxes of Sylpheed, or > normal MH folders. > > Sylph-Searcher requires the following programs: > > GLib 2.4.0 or later (http://www.gtk.org/) > GTK+ 2.4.0 or later (http://www.gtk.org/) > MeCab 0.93 or later + mecab-ipadic (http://mecab.sourceforge.net/) > PostgreSQL 8.2 or later + tsearch2 (http://www.postgresql.org/) > LibSylph 1.0.0 or later (http://sylpheed.sraoss.jp/) > > The license of Sylph-Searcher follows the BSD license. > > I'm using sylph searcher on my Linux laptop regulary with mew (a mail > program running inside emacs) and has fairly large tsearch2 data base > on it. The database size is ~1.7GB, and the number of messages are more > than 300,000. Note that sylph-searcher can run on Windows too. > > Sylph searcher was developed by Hiroyuki Yamamoto, who is the author > of Sylpheed, a mail client. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > > ---(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 > -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpzNdsypMi5A.pgp Description: PGP signature
Re: [GENERAL] Bytea question with \208
Thanks, My bad, the table I was looking (8.7) at had the first column as the decimal representation and I did notice that the numbers changed as they moved right. Is there a way for bytea to take a hex number, or do I need to convert the bit stream to octal numbers? Thanks again, Woody -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, August 09, 2007 2:14 PM To: Woody Woodring Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Bytea question with \208 "Woody Woodring" <[EMAIL PROTECTED]> writes: > Could someone explain why \208 is not a valid syntax for bytea? Aren't those escapes octal? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Reordering columns, will this ever be simple?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Erik Jones wrote: > > On Aug 9, 2007, at 1:14 PM, Greg Smith wrote: > >> On Thu, 9 Aug 2007, Erik Jones wrote: >> >>> Perhaps we could have a nice, friendly discussion on using surrogate >>> primary keys v. string based keys? Or, I think the body of the >>> "nulls are bad" dead horse is collecting flies if anyone wants to >>> take a swing at it... >> >> Following the handbook for dead horse riding[1], obviously technique >> #12 "Harnessing several dead horses together for increased speed" >> would allow faster progress were all these addressed at once. >> >> [1] http://soli.inav.net/~catalyst/Humor/dhorse.htm >> > > That's awesome. And very sad at the same time. Joshua D. Drkae > > Erik Jones > > Software Developer | Emma® > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGu13jATb/zqfZUUQRAgquAKCC6kw2tLdnxP5wzoQ5iZu+mElw2gCgoj8l CRPzhYI8FoBjWvNwN8LTsvs= =HuQm -END PGP SIGNATURE- ---(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] Interesting abilities of substring
Kenneth Downs <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Implicit casts to text are evil, and are mostly going to be gone in 8.3. >> So try not to rely on this behavior ... > Based on general principle, or on specific bad things like unexpected or > ill-defined results? Both. Check the archives, and you'll see reports every few months from people who got bit by unexpected misinterpretations of queries. The typical case is someone who tries to compare values of two different datatypes, and the most plausible interpretation the parser can find is to implicitly cast them both to text and use text comparison :-(. Another thing we've seen happen is that someone thinks he's invoking substring(text,int), but his second argument isn't int but float or numeric, and instead of an error he gets substring(text,text) which does Something Completely Different from what he expects. The problem with an implicit cast is basically that it'll kick in when you didn't want or expect it. So in CVS HEAD most casts to text are not implicit anymore, and we've put in some much more limited flexibility: the || operator will take any data type and do an implicit coercion to text, so long as at least one of the inputs is of a string type. (We had some problems not breaking the array interpretation of ||, but surmounted those.) It might be possible to do something similar for substring, but I'm not sure how to put any flexibility there without re-introducing the problem alluded to above. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Reordering columns, will this ever be simple?
On Aug 9, 2007, at 1:14 PM, Greg Smith wrote: On Thu, 9 Aug 2007, Erik Jones wrote: Perhaps we could have a nice, friendly discussion on using surrogate primary keys v. string based keys? Or, I think the body of the "nulls are bad" dead horse is collecting flies if anyone wants to take a swing at it... Following the handbook for dead horse riding[1], obviously technique #12 "Harnessing several dead horses together for increased speed" would allow faster progress were all these addressed at once. [1] http://soli.inav.net/~catalyst/Humor/dhorse.htm That's awesome. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Bytea question with \208
"Woody Woodring" <[EMAIL PROTECTED]> writes: > Could someone explain why \208 is not a valid syntax for bytea? Aren't those escapes octal? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Reordering columns, will this ever be simple?
On Thu, 9 Aug 2007, Erik Jones wrote: Perhaps we could have a nice, friendly discussion on using surrogate primary keys v. string based keys? Or, I think the body of the "nulls are bad" dead horse is collecting flies if anyone wants to take a swing at it... Following the handbook for dead horse riding[1], obviously technique #12 "Harnessing several dead horses together for increased speed" would allow faster progress were all these addressed at once. [1] http://soli.inav.net/~catalyst/Humor/dhorse.htm -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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] Interesting abilities of substring
Tom Lane wrote: Kenneth Downs <[EMAIL PROTECTED]> writes: Basically, it "knows what you mean" when you do substrings on dates and numbers, doing an implicit cast for you. Implicit casts to text are evil, and are mostly going to be gone in 8.3. So try not to rely on this behavior ... Based on general principle, or on specific bad things like unexpected or ill-defined results? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Kenneth Downs Secure Data Software, Inc. www.secdat.comwww.andromeda-project.org 631-689-7200 Fax: 631-689-0527 cell: 631-379-0010
Re: [GENERAL] Interesting abilities of substring
Kenneth Downs <[EMAIL PROTECTED]> writes: > Basically, it "knows what you mean" when you do substrings on dates and > numbers, doing an implicit cast for you. Implicit casts to text are evil, and are mostly going to be gone in 8.3. So try not to rely on this behavior ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] failed to unlink, Permission denied
M S <[EMAIL PROTECTED]> writes: > I can't think of any programs which would be locking the files (antivirus o= > r other), but I'll have a look. Since it's a temporary file, no other Postgres process would be touching it. I strongly suspect an antivirus or similar tool is touching the file just as we try to delete it. 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] Allowing LAN connections
Jonas Gauffin wrote: > I've installed postgresql 8.2 on a windows vista machine and are trying to > connect to it from another one. > the server has ip 192.168.1.100 and the client 192.168.1.102 >... > Any suggestions? Yes. Let us know what client you are using to connect and post the error message you get on the client and any errors from the server log file. I don't use Windows anywhere, but I presume Vista could have some firewall software running that might interfere with external connections as well. Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Reordering columns, will this ever be simple?
On 8/9/07, Erik Jones <[EMAIL PROTECTED]> wrote: > > On Aug 8, 2007, at 7:14 PM, Martijn van Oosterhout wrote: > > > > But this is a thoroughly dead horse, lets not beat it up again. > > Hah! Perhaps we could have a nice, friendly discussion on using > surrogate primary keys v. string based keys? Or, I think the body of > the "nulls are bad" dead horse is collecting flies if anyone wants to > take a swing at it... I'll stop now. :-) Neighh! I can't believe you're trying to stirrup trouble here. I don't want to seem like a nag, but we don't need to saddle the mailing list with even more flame wars. People reading the list might think we've been sniffing glue to get our kicks! OK, I'm gonna go before I make a horse's arse of myself anymare. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Reordering columns, will this ever be simple?
On Aug 8, 2007, at 7:14 PM, Martijn van Oosterhout wrote: But this is a thoroughly dead horse, lets not beat it up again. Hah! Perhaps we could have a nice, friendly discussion on using surrogate primary keys v. string based keys? Or, I think the body of the "nulls are bad" dead horse is collecting flies if anyone wants to take a swing at it... I'll stop now. :-) Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(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] Modified FIFO queue and insert rule
On Wednesday 8. August 2007 15:12, Alban Hertroys wrote:
>You should probably use a trigger (a before one maybe) instead of a
> rule.
I tried that too, but I'm still quite shaky on how to write triggers,
and the same thing happened there: the inserted record was immediately
deleted. I solved the problem temporarily with two lines in PHP:
function set_last_selected_place($place) {
pg_query("DELETE FROM recent_places WHERE place_fk = $place");
pg_query("INSERT INTO recent_places (place_fk) VALUES ($place)");
}
As my application is single-user, and everything is already wrapped up
in a transaction anyway, there's no real problem with this. But I'd
still like to understand how to do it 'properly' inside the DB.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Bytea question with \208
Could someone explain why \208 is not a valid syntax for bytea? I am getting the following: test=> select E'\\207'::bytea; bytea --- \207 (1 row) test=> select E'\\208'::bytea; ERROR: invalid input syntax for type bytea test=> select E'\\209'::bytea; ERROR: invalid input syntax for type bytea test=> select E'\\210'::bytea; bytea --- \210 (1 row) This all started when our version(older) of bugzilla trying to open a new bug with a word attachment via email: Aug 9 11:43:15 brain01 postgres[8631]: [5-1] ERROR: invalid byte sequence for encoding "UTF8": 0xd0cf The first two bytes of the word document are 0xd0 0xcf and I am assuming they are putting the two together since 0xd0 is invalid 0xd0 == \208 Thanks, Woody iGLASS Networks 211-A S. Salem St Apex NC 27502 (919) 387-3550 x813 www.iglass.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [JDBC] Restore database from zipped textfile (.sql) created by pg_dumpall
=?ISO-8859-1?Q?H=E5kan_Jacobsson?= <[EMAIL PROTECTED]> writes: > The table indexes aren't restored when I run this command: > gunzip -c /filename/.gz | psql dbname Since you haven't shown us what commands are in that file or what output you get, it's impossible to make any intelligent response to this. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
On 8/9/07, Louis-David Mitterrand <[EMAIL PROTECTED]> wrote: > Hi, > > After our 7.4 to 8.2 upgrade using debian tools, we realized that some > of our timestamps with tz had shifted: > > For example '2007-04-01 00:00:00+02' became '2007-03-31 23:00:00+01' > which is on a different month. Some of our applications were severely > disturbed by that. > > Has anyone noticed that? Is there a way that would could have avoided > it? Since timestamptz is stored as a GMT time, and then an offset is applied on retrieval, I'd guess that with 8.2 you're using up to date timezone files, and with 7.4 they were out of date and therefore returning the wrong time. I.e. they had the wrong offset for a given date. Not sure how you could avoid it off the top of my head, besides keeping your 7.4 db tz data up to date. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] failed to unlink, Permission denied
I have reproduced this. I'll upgrade to 8.2.4 and report back after my long weekend. Cheers. - Original Message From: M S <[EMAIL PROTECTED]> To: pgsql-general@postgresql.org Sent: Thursday, 9 August, 2007 1:54:17 PM Subject: Re: [GENERAL] failed to unlink, Permission denied > no, but (IMO) 8.2.4 is a required upgradeso you should be testing that. Understood, I'll try an upgrade after my repeat tests have finished. > The server is unable to delete a file (specifically, a temporary > table created for sorting). Have you considered any running services > that may acquire a lock on the table (windows is famous for this). There is the possibility of other threads inserting into the same table, but wouldn't that be a problem according to my understanding of MVCC. There are no LOCK TABLE statements on this table. I can't think of any programs which would be locking the files (antivirus or other), but I'll have a look. > This may also suggest you need to look at the work_mem setting, but > this is not the issue at hand. Interesting, I'll pass that on. > is the error repeatable? I'm running the tests again with 8.2.3 to get an idea. Cheers for the help! ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] timestamp skew during 7.4 -> 8.2 upgrade
Hi, After our 7.4 to 8.2 upgrade using debian tools, we realized that some of our timestamps with tz had shifted: For example '2007-04-01 00:00:00+02' became '2007-03-31 23:00:00+01' which is on a different month. Some of our applications were severely disturbed by that. Has anyone noticed that? Is there a way that would could have avoided it? Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Data Mart with Postgres
Decibel! escreveu: On Wed, Aug 08, 2007 at 08:56:47AM -0300, Andr? Volpato wrote: Hello everybody, I�m working with a small project to a client, using Postgres to store data in a dimensional model, fact-oriented, e.g., a Datamart. At this time, all I have is a populated database, with the "star schemma" common relations (PK�s / FK�s). Below is a list of the main goals of this project : 1. Front-end app (PHP5) 2. Transactional database for this app (Postgres) 3. Datamart in Postgres (described above) 4. ROLAP server that supports Postgres (Java - Mondrian) 5. Front-end app to manage querys to the ROLAP server (JSP - JPivot) Users will have web access to (1), and will be enable to create views using (5). It seems like I�m trying to reinvent the wheel, but the point here is that the client can�t afford to use proprietary BI solutions, nor proprietary OS. What I have read all across the internet is that Postgres does not support this kind of application, wich demands materialyzed views, built-in bitmap index, and so on. In the open-source world, I find those missing features with Mondrian/JPivot. Does anyone has ever used this structure before ? At least Mondrian and JPivot ? I haven't but it's certainly possible to build a datamart without bitmap indexes or mviews, it's just a question of performance. MViews you can build yourself easily enough; as for bitmap indexes, IIRC you can get those in Bizgres. That´s right, I´m pretty sure Postgres could be used as ROLAP server in this case. Performance is not a problem in this project. The reason why I suggest using Mondrian [1] is the ability to have a tool such as JPivot [2], wich implements tipicaly OLAP navigations, like drill-down and slice/dice. Can anyone point out another OLAP tool, that works with Postgres ? [1] http://mondrian.pentaho.org/ [2] http://jpivot.sourceforge.net -- []´s, André Volpato ECOM Tecnologia Ltda [EMAIL PROTECTED] (41) 3014 2322
Re: [GENERAL] Time for Vacuum vs. Vacuum Full
On Aug 9, 2007, at 9:38 AM, Brad Nicholson wrote: I have the times that it takes to to do a regular vacuum on the clusters, will vacuum full take longer? almost certainly it will, since it has to move data to compact pages rather than just tagging the rows as reusable. you can speed things up by dropping your indexes first, then running vacuum full, then re-creating your indexes. this will make for better (more compact) indexes too. as for how much longer, I don't know how to estimate that. ---(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] Interesting abilities of substring
Sure, we use a user interface widget called "Ajax Dynamic List" from www.dhtmlgoodies.com. This replaces the HTML SELECT element. When a user is sitting on a foreign-key field, such as a PATIENT or CUSTOMER field, the user can just start typing letters or numbers. An AJAX call is made to the back end which searches all columns in a pre-defined list. If the list is first_name,last_name,dob, and they type "ken" they will get everybody whose first name or last name begins with 'ken', and the date is ignored. If they type '1991' they will get everybody born in 1991, and of course '1991-10' returns everybody born in October 1991. So it turns out that both string concatenation and substring do some nifty implicit typing. This means making the generalized lookup requires you only to know the list of columns, without also needing to know their types. SELECT patient as _value ,patient || ' - ' || namlst || ' - ' || nam1st || ' - ' || phone || ' - ' || ssn || ' - ' || dob as _display FROM patients WHERE ( SUBSTRING(LOWER(patient) FROM 1 FOR 2)='ke' OR SUBSTRING(LOWER(namlst) FROM 1 FOR 2)='ke' OR SUBSTRING(LOWER(nam1st) FROM 1 FOR 2)='ke' OR SUBSTRING(LOWER(phone) FROM 1 FOR 2)='ke' OR SUBSTRING(LOWER(ssn) FROM 1 FOR 2)='ke' OR SUBSTRING(LOWER(dob) FROM 1 FOR 2)='ke') ORDER BY patient Limit 20 Eventually I will be driven to optimize that, but on tables < 10,000 rows it is still well within the threshhold of the human attention span of < .5 second or so. Naz Gassiep wrote: This may be useful to me and others, care to paste an example of what you mean? Thanks, - Naz. Kenneth Downs wrote: Here is something cool that I did not realize postgres's substring() could do. Basically, it "knows what you mean" when you do substrings on dates and numbers, doing an implicit cast for you. This is really nice if you happen to be writing a generalized search system, as it makes the code significantly simpler... That's all, now back to our regularly scheduled mailing list. -- Kenneth Downs Secure Data Software, Inc. www.secdat.comwww.andromeda-project.org 631-689-7200 Fax: 631-689-0527 cell: 631-379-0010 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Time for Vacuum vs. Vacuum Full
I have a couple of database clusters that need a vacuum full, and I would like to estimate how long it will take, as it will need to be in a maintenance window. I have the times that it takes to to do a regular vacuum on the clusters, will vacuum full take longer? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] failed to unlink, Permission denied
> no, but (IMO) 8.2.4 is a required upgradeso you should be testing that. Understood, I'll try an upgrade after my repeat tests have finished. > The server is unable to delete a file (specifically, a temporary > table created for sorting). Have you considered any running services > that may acquire a lock on the table (windows is famous for this). There is the possibility of other threads inserting into the same table, but wouldn't that be a problem according to my understanding of MVCC. There are no LOCK TABLE statements on this table. I can't think of any programs which would be locking the files (antivirus or other), but I'll have a look. > This may also suggest you need to look at the work_mem setting, but > this is not the issue at hand. Interesting, I'll pass that on. > is the error repeatable? I'm running the tests again with 8.2.3 to get an idea. Cheers for the help! ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Interesting abilities of substring
Here is something cool that I did not realize postgres's substring() could do. Basically, it "knows what you mean" when you do substrings on dates and numbers, doing an implicit cast for you. This is really nice if you happen to be writing a generalized search system, as it makes the code significantly simpler... That's all, now back to our regularly scheduled mailing list. -- Kenneth Downs Secure Data Software, Inc. www.secdat.comwww.andromeda-project.org 631-689-7200 Fax: 631-689-0527 cell: 631-379-0010 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] failed to unlink, Permission denied
On 8/9/07, M S <[EMAIL PROTECTED]> wrote: > I left our app on soak test overnight, it ran fine for some time but after > a few hours I noticed the following messages repeated in the log (the tmp > filename changes, but the PlPgSql function which causes it does not). > 2007-08-08 17:25:57 LOG: failed to unlink > "pg_tblspc/16403/16404/pgsql_tmp/pgsql_tmp5260.0": Permission denied > > 2007-08-08 17:25:57 CONTEXT: PL/pgSQL function "JobTargetNextUnsentGet" > line 40 at for over select rows > The PlPgSql function is just a SELECT with 2 INNER JOINS, an integer > comparison WHERE clause, with an ORDER BY time and a LIMIT 1. > > > > Could anyone please suggest what could be behind this error? > > I have checked the Changelog from 8.2.3-8.2.4 and nothing look relevant. no, but (IMO) 8.2.4 is a required upgradeso you should be testing that. The server is unable to delete a file (specifically, a temporary table created for sorting). Have you considered any running services that may acquire a lock on the table (windows is famous for this). This may also suggest you need to look at the work_mem setting, but this is not the issue at hand. is the error repeatable? merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Multiple operations when updating a view - works in Postgres, doesn't in the app
Hello, I'm fairly new to the more advanded functionality of PostgreSQL, especially writing functions in PL/pgSQL and have something of a design question, which doesn't seem to be answered anywhere I can google to. I've a view created in my schema, for which I'm adding rules for updating and inserting data. When data is updated or inserted into that view, two tables in the database need to have inserted data into. Unfortunately my first attempt of putting two INSERT statements into the view rule's definition din't seem to be accepted by PostgreSQL. So, my next idea was to put both these INSERTs into a function, declared this way: CREATE OR REPLACE FUNCTION create_trade_material(arg_diameter integer, arg_material_length real, arg_weight real, arg_loss real, arg_bar_type_id integer, arg_metal_type_id integer) RETURNS void AS $BODY$DECLARE new_material_id INT; BEGIN INSERT INTO material (diameter, material_length, weight, loss, bar_type_id, metal_type_id) VALUES (arg_diameter, arg_material_length, arg_weight, arg_loss, arg_bar_type_id, arg_metal_type_id) RETURNING "id" INTO new_material_id; INSERT INTO trade_material (material_id) VALUES (new_material_id); RETURN; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION create_trade_material(arg_diameter integer, arg_material_length real, arg_weight real, arg_loss real, arg_bar_type_id integer, arg_metal_type_id integer) OWNER TO hefajstos; and inside the view rule's definition I placed: DO SELECT create_trade_material(new.diameter, new.material_length, new.weight, new.loss, new.bar_type_id, new.metal_type_id) It works as expected. Both INSERTing and UPDATE-ing the view does modifications on both said tables, the return value though is an empty record set (or actually a single null-tuple). The problem is when I try to run INSERT or UPDATE with Hibernate in my Java application. I get one of these ugly stack traces from the exception: javax.transaction.RollbackException: Transaction marked for rollback. and nowhere to see any explanation. Since the Java code is perfectly correct, my guess is, that updating or inserting into the view the way I did it doesn't return an appropriate value marking success, since the function returns void and the INSERT or UPDATE returns that null tuple. What am I missing? Should I change the value returned by the create* function? If yes, than two what? Or is calling the function through a DO SELECT create* incorrect? -- Best regards, Michal mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] failed to unlink, Permission denied
Hi, I left our app on soak test overnight, it ran fine for some time but after a few hours I noticed the following messages repeated in the log (the tmp filename changes, but the PlPgSql function which causes it does not). 2007-08-08 17:25:57 LOG: failed to unlink "pg_tblspc/16403/16404/pgsql_tmp/pgsql_tmp5260.0": Permission denied 2007-08-08 17:25:57 CONTEXT: PL/pgSQL function "JobTargetNextUnsentGet" line 40 at for over select rows The PlPgSql function is just a SELECT with 2 INNER JOINS, an integer comparison WHERE clause, with an ORDER BY time and a LIMIT 1. Could anyone please suggest what could be behind this error? Postgres version 8.2.3 Windows 2003 SP2 I have checked the Changelog from 8.2.3-8.2.4 and nothing look relevant. Thanks. ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] [JDBC] Restore database from zipped textfile (.sql) created by pg_dumpall
Hi, The table indexes aren't restored when I run this command: gunzip -c /filename/.gz | psql dbname / Should I use another cmd? Or am I missing a parameter? /regards, Håkan Jacobsson ---(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] tsearch2: plainto_tsquery() with OR?
Thanks for your response! Let me try to elaborate what I meant with my
original post.
If R is the set of words in the tsvector for a given table row and S is
the set of keywords to search for (entered by e.g. a website user) I
would like to receive all rows for which the intersection between R and
S is nonempty. That is: The row should be return if just there is SOME
match. S does not necessarily need to be a subset of R.
Furthermore I would like a measure for how "nonempty" the intersection
is (we would call this measure "the rank").
Example:
For R = "three big houses" and S = "three small houses" the rank should
be higher than for R = "three big houses" and S = "four small houses" as
the first case has two words in common while the second case has only one.
A version of plainto_tsquery() with a simple OR operator instead of AND
would solve this problem somewhat elegant:
1) I can now use the conventional "tsvector @@ tsquery" syntax in my
WHERE clause as the "@@" operator will return true and thus include the
row in the result. Example:
select to_tsvector('simple', 'three small houses')
@@ 'four|big|houses'::tsquery;
would return "true".
2) The rank() of the @@ operator is automatically higher when there is a
good match.
An example where this OR-version of plainto_tsquery() could be useful is
for websites using tags. Each website entry is associated with some tags
and each user has defined some "tags of interest". The search should
then return all website entries where there is a match (not necessarily
complete) with the users tags of interest. Of course the best matching
entries should be displayed top most.
I find it important that this function is a part of tsearch2 itself as:
1) The user can input arbitrary data. Also potentially harmful data if
they are not escaped right.
2) Special characters should be stripped in just the same way as
to_tsvector() does it. E.g. stripping the dot in "Hi . there" but
keeping it in "web 2.0". Only tsearch2 can do that in a clean consistent
way - it would be fairly messy if some thirdparty or especially some
website-developer-homecooked stripping functionality is used for this.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Sylph Searcher
Hi, I made a small demonstration for Sylph Searcher at Linux World at SF and was asked by Josh Berkus where he can download it. I would like to share the info with PostgreSQL users. Here is the URL: http://sylpheed.sraoss.jp/en/download.html#searcher Those who are not familiar with Syph Searcher, here are brief explanation: Sylph-Searcher is a PostgreSQL+tsearch2 application that enables fast full-text search of messages stored in mailboxes of Sylpheed, or normal MH folders. Sylph-Searcher requires the following programs: GLib 2.4.0 or later (http://www.gtk.org/) GTK+ 2.4.0 or later (http://www.gtk.org/) MeCab 0.93 or later + mecab-ipadic (http://mecab.sourceforge.net/) PostgreSQL 8.2 or later + tsearch2 (http://www.postgresql.org/) LibSylph 1.0.0 or later (http://sylpheed.sraoss.jp/) The license of Sylph-Searcher follows the BSD license. I'm using sylph searcher on my Linux laptop regulary with mew (a mail program running inside emacs) and has fairly large tsearch2 data base on it. The database size is ~1.7GB, and the number of messages are more than 300,000. Note that sylph-searcher can run on Windows too. Sylph searcher was developed by Hiroyuki Yamamoto, who is the author of Sylpheed, a mail client. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(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] Allowing LAN connections
I've installed postgresql 8.2 on a windows vista machine and are trying to connect to it from another one.the server has ip 192.168.1.100 and the client 192.168.1.102As I understand it, I should make some configuration changes in pg_hba.conf to make this happen.Both machines has both ipv4 and ipv6 installed (Vista standard I guess).Ive tried some different settings in the pg_hba.conf (trying one at a time, commenting out the rest):# IPv4 local connections:#host all all 127.0.0.1/32 md5#hostall all 192.168.0.0/16md5hostall all 0.0.0.0/0 md5# IPv6 local connections:#hostall all ::1/128 md5I've also changed listten_addresses in postgresql.conf to:listen_addresses = '*'-I've installed VmWare player on the server. I guess that it's why it have so many adapters:Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7dba:9969:999b:5bfe%11 IPv4 Address. . . . . . . . . . . : 192.168.1.100 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.1.1Wireless LAN adapter Wireless Network Connection: Media State . . . . . . . . . . . : Media disconnected Connection-specific DNS Suffix . :Ethernet adapter Local Area Connection 2: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::440f:5179:acc9:5799%14 IPv4 Address. . . . . . . . . . . : 192.168.243.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :Ethernet adapter Local Area Connection 3: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::d48:1d79:2215:543a%16 IPv4 Address. . . . . . . . . . . : 192.168.11.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :Tunnel adapter Local Area Connection* 6: Media State . . . . . . . . . . . : Media disconnected Connection-specific DNS Suffix . :Tunnel adapter Local Area Connection* 7: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::5efe:192.168.1.100%12 Default Gateway . . . . . . . . . :Tunnel adapter Local Area Connection* 10: Media State . . . . . . . . . . . : Media disconnected Connection-specific DNS Suffix . :Tunnel adapter Local Area Connection* 11: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::5efe:192.168.243.1%15 Default Gateway . . . . . . . . . :Tunnel adapter Local Area Connection* 12: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::5efe:192.168.11.1%17 Default Gateway . . . . . . . . . :--And the client:Ethernet-anslutning Anslutning till lokalt nätverk: Anslutningsspecifika DNS-suffix . : IPv4-adress . . . . . . . . . . . : 192.168.1.102 Nätmask . . . . . . . . . . . . . : 255.255.255.0 Standard-gateway. . . . . . . . . : 192.168.1.1Tunnelanslutning: Anslutning till lokalt nätverk*: Tillstånd . . . . . . . . . . . . : Frånkopplad Anslutningsspecifika DNS-suffix . :Tunnelanslutning: Anslutning till lokalt nätverk* 2: Anslutningsspecifika DNS-suffix . : Länklokal IPv6-adress . . . . . . : fe80::5efe:192.168.1.102%10 Standard-gateway. . . . . . . . . :(sorry for the swedish ;)--Any suggestions? _ Ta en titt på de nya onlinetjänsterna på Windows Live Betas -- så nya att de ännu inte har lanserats officiellt. http://get.live.com/betas
