Re: [GENERAL] Auto-partitioning?
Hi Just a hint. We do this auto-partitioning with PgQ. Instead of writing records into table we push them into queue and use consumer called table_dispatcher to creates tartitioned tables as needed and put records into them. We have multiple destination databases where to write data and target tables have different structures so queue based solution is convenient for us. Asko On 8/21/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Steve Wampler wrote: > > Steve Wampler wrote: > >> ... Specifically, I'm wondering if it's > >> possible to add a default rule that would create a new partition > >> (with indices, etc.) and would add a new rule for this partition > >> to match the similar ones above (and, of course, then move the > >> INSERT into the new partition). > > > > I think I've answered my own question, but would love an expert > > to validate the answer: > > > > The answer is "no", apparently because 'name' in > > That answer is no but you could probably pull it off with a trigger. > > Sincerely, > > Joshua D. Drake > > > - -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 > PostgreSQL solutions since 1997 http://www.commandprompt.com/ > UNIQUE NOT NULL > 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 > > iD8DBQFGy1BNATb/zqfZUUQRAlhxAJsEpKJicoMkvFXS+T5DiCjroSYj9QCcCjmt > qaaXSe764ULKH5h3z8p6QUc= > =2xC8 > -END PGP SIGNATURE- > > ---(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] raw data into table process
novice wrote:
I am trying to record the following entries into a table. I'm curious
to know if there's an efficient/effective way of doing this? This
entries come from an ancient datalogger (note: separated by space and
uses YY/MM/DD format to record date)
Plain file sample.dat
3665 OK BS 07/08/16 07:28
3665 CC BS 07/08/16 07:29
3665 CS BS 07/08/16 07:29
3665 CS BS 07/08/16 07:29
4532 OK BS 07/08/16 07:34
4004 OK BS 07/08/16 07:51
3991 OK BS 07/08/16 07:54
This is the table that I'm adding the entries to
CREATE TABLE maintenance
(
maintenance_id SERIAL PRIMARY KEY,
meter_id integer,
status text,
inspector text,
inspection_date timestamp with time zone,
)
If your on unix, why not use those tools first?
awk '{print $1 "\t" $2 "\t" $3 "\t" $4 " " $5}' sample.dat > sample.tab
-- Begin SQL script
CREATE TEMP TABLE maintenance_tmp (
meter_id integer,
status text,
inspector text,
inspection_date timestamp with time zone
);
SET datestyle='ymd';
\copy maintenance_tmp FROM sample.tab
INSERT INTO maintenance (meter_id, status, inspector, inspection_date)
SELECT DISTINCT meter_id, status, inspector, inspection_date FROM
maintenance_tmp ORDER BY inpsection_date;
ANALYZE maintenance;
-- End SQL Script
[snip]
Any thoughts and suggestions welcome.
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PG Seg Faults Performing a Query
On Tue, Aug 21, 2007 at 04:38:42PM -0500, Scott Marlowe wrote: > On 8/21/07, Bill Thoen <[EMAIL PROTECTED]> wrote: > > How would you suggest I try to track down this problem? > > I run the following query: > > > > SELECT a.* FROM compliance_2006 a, ers_regions b > > WHERE a.fips_st_cd=b.fips_st > > AND a.fips_cnty_cd=b.fips_cou AND b.region =1 > > AND a.fips_st_cd='17' AND a.fips_cnty_cd='003'; > > > > and it works. But when I try this: > > > > SELECT a.* FROM compliance_2006 a, ers_regions b > > WHERE a.fips_st_cd=b.fips_st > > AND a.fips_cnty_cd=b.fips_cou AND b.region =1 > > AND a.fips_st_cd='17' ; > > > > psql dies with the message: > > Segmentation Fault. > > so the client psql is what's dieing right? In that case you likely > are getting too big a result set for psql to handle at once. Trying > declaring a cursor to hold your query and fetching 100 or 1000 or so > rows at a time. > > Just guessing. What's the exact text of the error message? > The exact message was: Segmentation Fault. But the table compliance_2006 is very big (18 million plus records) so I'll try that cursor idea. But even so, an error like that makes me think that something's broken. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] could not open file "pg_clog/0BFF"
Hi, While checking the logfiles of our postgresql server I noticed the error mentioned in the subject: ERROR: could not access status of transaction 3221180546 DETAIL: could not open file "pg_clog/0BFF": No such file or directory Searching on google told me that this could be solved by creating the file mentioned in the error using /dev/zero as input. After I have done that and taking another look at the login I also noticed that this error started to show up in the loggin since the 1st of August and from the same date the autovacuum was only vacuuming the 'template0' database. Right now I have created the "pg_clog/0BFF" file and manually started a vacuum. And now my questions: Are those mentioned steps the appropriate steps or should we do anything else? What could have caused the error "could not access status of transaction 3221180546" and is it more than just coincidence that since that moment the vacuum stopped running? Is there anything we can do to prevent this in future use? Besides we are using PostgreSQL 8.1.3. Any help would be appreciated. Regards, Patrick Lindeman ---(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] PG Seg Faults Performing a Query
On Wed, Aug 22, 2007 at 09:46:21AM +1200, Andrej Ricnik-Bay wrote: > On 8/22/07, Bill Thoen <[EMAIL PROTECTED]> wrote: > > How would you suggest I try to track down this problem? > > Any suggestions? > postgres version? > Operating system? > Anything in the log(s)? PostgreSQL Version is 8.1.5, running on Linux (Fedora Core 6). The last few lines in the Serverlog are: LOG: unexpected EOF on client connection LOG: transaction ID wrap limit is 1073746500, limited by database "postgres" LOG: transaction ID wrap limit is 1073746500, limited by database "postgres" (I ran VACUUM FULL after it crashed to make sure there was no loose disk space floating around, so that last line was probably from that.) I assume that bit about "transaction wrap limit" is informational and not related to this problem. My PostgreSQL is working great for small SQL queries even from my large table (18 million records). But when I ask it to retrieve anything that takes it more than 10 minutes to assemble, it crashes with this "Segmentation Fault" error. I get so little feedback and I'm still pretty unfamiliar with Postgresql that I don't even know where to begin. This version of PostgreSQL was compiled from source with support for various other packages needed for GIS support, but the tables I'm trying to extract data from contain no GIS information. So I believe that this operation is plain PostgreSQL. Any help you can offer as to how I can track down what's wrong would be greatly appreciated. If I can't get this to work and can only use small tables in PG, then its usefulnes to me will be pretty limited. - Bill Thoen ---(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] PG Seg Faults Performing a Query
On Wed, Aug 22, 2007 at 07:09:22AM -0600, Bill Thoen wrote: > PostgreSQL Version is 8.1.5, running on Linux (Fedora Core 6). The last few > lines in the Serverlog are: > LOG: unexpected EOF on client connection > LOG: transaction ID wrap limit is 1073746500, limited by database > "postgres" > LOG: transaction ID wrap limit is 1073746500, limited by database > "postgres" All indications are that your client is unable to hold the 18 million row result and crashing with out of memory. Nothing you do in the server or client is going to magic more memory for you, you need to avoid getting it in the first place. If you only want to display part of it, do a LIMIT . Or use a cursor to page through it. That said, it would be nice if it returned an error instead of crashing. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] PG Seg Faults Performing a Query
Martijn van Oosterhout escribió: > That said, it would be nice if it returned an error instead of > crashing. In my opinion it isn't just a matter of "would be nice". It is a possible bug that should be investigated. A look at a stack trace from the crashing process would be the first place to start. In order to do that, please set "ulimit -c unlimited" and rerun the query under psql. That should produce a core file. Then run gdb psql core and inside gdb, execute "bt". Please send that output our way. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] could not open file "pg_clog/0BFF"
On 8/22/07, Patrick Lindeman <[EMAIL PROTECTED]> wrote: > Hi, > > While checking the logfiles of our postgresql server I noticed the error > mentioned in the subject: > > ERROR: could not access status of transaction 3221180546 > DETAIL: could not open file "pg_clog/0BFF": No such file or directory What other files are there in the pg_clog directory? > Searching on google told me that this could be solved by creating the file > mentioned in the error using /dev/zero as input. > > After I have done that and taking another look at the login I also noticed > that this error started to show up in the loggin since the 1st of August > and from the same date the autovacuum was only vacuuming the 'template0' > database. OK, there's more going on here than what you're showing us so far. template0 is normally frozen and set to not allow connections. so, what's happened to the db to allow template0 to be connected to? > > Right now I have created the "pg_clog/0BFF" file and manually started a > vacuum. > > And now my questions: > > Are those mentioned steps the appropriate steps or should we do anything > else? > > What could have caused the error "could not access status of transaction > 3221180546" and is it more than just coincidence that since that moment > the vacuum stopped running? Maybe transaction wraparound? I'm not really sure. > Is there anything we can do to prevent this in future use? > > Besides we are using PostgreSQL 8.1.3. Upgrading your postgresql version would not be a bad idea. 8.1 is up to 8.1.9 or so by now. and I know there were some bugs in 8.1.3 or so that could cause things like this to happen. Not sure your exact problem is one of them, but you should really keep up to date on patchs for your pg version. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG Seg Faults Performing a Query
Bill Thoen <[EMAIL PROTECTED]> writes:
> My PostgreSQL is working great for small SQL queries even from my large
> table (18 million records). But when I ask it to retrieve anything that
> takes it more than 10 minutes to assemble, it crashes with this
> "Segmentation Fault" error. I get so little feedback and I'm still pretty
> unfamiliar with Postgresql that I don't even know where to begin.
Running the client under gdb and getting a stack trace would be a good
place to begin.
FWIW, when I deliberately try to read a query result that's too large
for client memory, I get reasonable behavior:
regression=# select x, y, repeat('xyzzy',200) from generate_series(1,1) x,
generate_series(1,100) y;
out of memory for query result
regression=#
If you're seeing a segfault in psql then it sounds like a PG bug. If
you're seeing a segfault in a homebrew program then I wonder whether
it's properly checking for an error return from libpq ...
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] could not open file "pg_clog/0BFF"
Thanks for your replies so far, it seems like the problem has been solved, for now! > On 8/22/07, Patrick Lindeman <[EMAIL PROTECTED]> wrote: >> Hi, >> >> While checking the logfiles of our postgresql server I noticed the error >> mentioned in the subject: >> >> ERROR: could not access status of transaction 3221180546 >> DETAIL: could not open file "pg_clog/0BFF": No such file or directory > > What other files are there in the pg_clog directory? > There are lots of files in the pg_clog directory running from 01FF until 0250. >> Searching on google told me that this could be solved by creating the >> file >> mentioned in the error using /dev/zero as input. >> >> After I have done that and taking another look at the login I also >> noticed >> that this error started to show up in the loggin since the 1st of August >> and from the same date the autovacuum was only vacuuming the 'template0' >> database. > > OK, there's more going on here than what you're showing us so far. > template0 is normally frozen and set to not allow connections. so, > what's happened to the db to allow template0 to be connected to? > I dont know what happened, but one of the solutions posted in the reply from Jeff pointed out that I should 'vacuum freeze' template0. While trying to connect I got the error that I couldn't connect since "template0" was not accepting connections. After manually executing the query: UPDATE pg_database SET datallowconn = true where datname='template0'; I was able to connect and execute 'vacuum freeze;' It seems like vacuum is running on all databases again so it seems like the fix works for now. >> >> Right now I have created the "pg_clog/0BFF" file and manually started a >> vacuum. >> >> And now my questions: >> >> Are those mentioned steps the appropriate steps or should we do anything >> else? >> >> What could have caused the error "could not access status of transaction >> 3221180546" and is it more than just coincidence that since that moment >> the vacuum stopped running? > > Maybe transaction wraparound? I'm not really sure. > >> Is there anything we can do to prevent this in future use? >> >> Besides we are using PostgreSQL 8.1.3. > > Upgrading your postgresql version would not be a bad idea. 8.1 is up > to 8.1.9 or so by now. and I know there were some bugs in 8.1.3 or so > that could cause things like this to happen. > Not sure your exact problem is one of them, but you should really keep > up to date on patchs for your pg version. > We already had in mind that we needed to upgrade to 8.1.9 or even 8.2. This problem just made this upgrade more urgent. Thank you both again ! Regards, Patrick Lindeman ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] could not open file "pg_clog/0BFF"
http://svr5.postgresql.org/pgsql-hackers/2006-03/msg01198.php http://www.mail-archive.com/pgsql-general@postgresql.org/msg90845.html This is related to an autovacuum bug and the freeze logic with template0and probably a corrupted pg_statistics table in template0 you should upgrade AND repair the template0 issue (I actually replaced template0 with a copy from a replicant database) ..But take heed, if you don't 'do' something about it, autovacuum is actually stopping with tempate 0 and never getting to the rest of your database (as I found out) Patrick Lindeman wrote: What could have caused the error "could not access status of transaction 3221180546" and is it more than just coincidence that since that moment the vacuum stopped running? Is there anything we can do to prevent this in future use? Besides we are using PostgreSQL 8.1.3. Any help would be appreciated. Regards, Patrick Lindeman ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
Hello.
We are trying to use HP CISS contoller (Smart Array E200i) with internal
cache memory (100M for write caching, built-in power battery) together with
Postgres. Typically under a heavy load Postgres runs checkpoint fsync very
slow:
checkpoint buffers dirty=16.8 MB (3.3%) write=24.3 ms sync=6243.3 ms
(If we turn off fsync, the speed increases greatly, fsync=0.) And
unfortunately it affects all the database productivity during the
checkpoint.
Here is the timing (in milliseconds) of a test transaction called multiple
times concurrently (6 threads) with fsync turned ON:
40.4
44.4
37.4
44.0
42.7
41.8
218.1
254.2
101.0
42.2
42.4
41.0
39.5
(you may see a significant slowdown during a checkpoint).
Here is dstat disc write activity log for that test:
0
0
284k
0
0
84k
0
0
276k
37M
208k
0
0
0
0
156k
0
0
0
0
I have written a small perl script to check how slow is fsync for Smart
Array E200i controller. Theoretically, because of write cache, fsync MUST
cost nothing, but in practice it is not true:
# cd /mnt/c0d1p1/
# perl -e 'use Time::HiRes qw(gettimeofday tv_interval); system "sync"; open
F, ">bulk"; print F ("a" x (1024 * 1024 * 20)); close F; $t0=[gettimeofday];
system "sync"; print ">>> fsync took " . tv_interval ( $t0, [gettimeofday])
. " s\n"; unlink "bulk"'
>>> fsync took 0.247033 s
You see, 50M block was fsynced for 0.25 s.
The question is: how to solve this problem and make fsync run with no delay.
Seems to me that controller's internal write cache is not used (strange,
because all configuration options are fine), but how to check it? Or, maybe,
there is another side-effect?
Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
On 8/22/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > Hello. > You see, 50M block was fsynced for 0.25 s. > > The question is: how to solve this problem and make fsync run with no delay. > Seems to me that controller's internal write cache is not used (strange, > because all configuration options are fine), but how to check it? Or, maybe, > there is another side-effect? I would suggest that either the controller is NOT configured fine, OR there's some bug in how the OS is interacting with it. What options are there for this RAID controller, and what are they set to? Specifically, the writeback / writethru type options for the cache, and it might be if it doesn't preoprly detect a battery backup module it refuses to go into writeback mode. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Converting non-null unique idx to pkey
On Tuesday 21 August 2007 11:40 pm, Tom Lane wrote: > "Ed L." <[EMAIL PROTECTED]> writes: > > Are there > > are any known or obvious gotchas associated with > > transforming a unique index on a non null column into a > > primary key via this sql? > > > > update pg_index > > set indisprimary = 't' > > where indexrelid = > > The problem with that is there won't be any pg_constraint > entry, nor any pg_depend entries linking to/from the > constraint... Thanks. Sounds messy enough, I'll try another route. Ed ---(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] "out of memory" error
Hi list, I am struggling with some "out of memory" errors in our PostgreSQL database which I do not understand. Perhaps someone can give me a hint. The application which causes the errors runs multi-threaded with 10 threads. Each of the threads performs several select statements on the database. Some of the statements are rather complicated (joins over mulitple tables etc.) From time to time some of the statements lead to "out of memory" errors. The errors are not reproducable and if I run the statements alone everything works fine. When I watch the output of "free -m" while the application runs, the used memory (without buffers) is always near 500 MB: total used free sharedbuffers cached Mem: 3954 3410543 0 0 2942 -/+ buffers/cache:467 3486 Swap: 2055556 1498 These are the current settings from the server configuration: shared_buffers = 3GB work_mem = 8MB maintenance_work_mem = 256MB max_stack_depth = 4MB The output of "ulimit -a" is as follows: core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited pending signals (-i) 38912 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) 3441565 open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 38912 virtual memory (kbytes, -v) 4922720 file locks (-x) unlimited The machine is a linux box with 4 GB memory running PostgreSQL 8.2.4. I can post the details about the memory status from the log file if it is needed. Are there any configuration parameters that influence the amount of available memory (besides "shared_buffers" which seems to be high enough)? Thanks for any help! Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer ---(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] "out of memory" error
On Wed, Aug 22, 2007 at 07:07:20PM +0200, Christian Schröder wrote: > These are the current settings from the server configuration: >shared_buffers = 3GB this is *way* to much. i would suggest lowering it to 1gig *at most*. >max memory size (kbytes, -m) 3441565 this looks like too close to shared_buffers. again - lower it. > The machine is a linux box with 4 GB memory running PostgreSQL 8.2.4. is it by any chance i386 architecture? depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
And here are results of built-in Postgres test script: Simple write timing: write0.006355 Compare fsync times on write() and non-write() descriptor: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 0.233793 write, close, fsync 0.227444 Compare one o_sync write to two: one 16k o_sync write 0.297093 two 8k o_sync writes 0.402803 Compare file sync methods with one 8k write: (o_dsync unavailable) write, fdatasync 0.228725 write, fsync,0.223302 Compare file sync methods with 2 8k writes: (o_dsync unavailable) open o_sync, write 0.414954 write, fdatasync 0.335280 write, fsync,0.327195 (Also, I tried to manually specify open_sync method in postgresql.conf, but after that Postgres database had completely crashed. :-) On 8/22/07, Dmitry Koterov <[EMAIL PROTECTED] > wrote: > > All settings seems to be fine. Mode is writeback. > > We temporarily (for tests only on test machine!!!) put pg_xlog into RAM > drive (to completely exclude xlog fsync from the statistics), but slowdown > during the checkpoint and 5-10 second fsync during the checkpoint are alive > yet. > > Here are some statistical data from the controller. Other report data is > attached to the mail. > > ACCELERATOR STATUS: >Logical Drive Disable Map: 0x >Read Cache Size: 24 MBytes >Posted Write Size: 72 MBytes >Disable Flag: 0x00 >Status:0x0001 >Disable Code: 0x >Total Memory Size: 128 MBytes >Battery Count: 1 >Battery Status:0x0001 >Parity Read Errors: >Parity Write Errors: >Error Log: N/A >Failed Batteries: 0x >Board Present: Yes >Accelerator Failure Map: 0x >Max Error Log Entries: 12 >NVRAM Load Status: 0x00 >Memory Size Shift Factor: 0x0a >Non Battery Backed Memory: 0 MBytes >Memory State: 0x00 > > > On 8/22/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > > On 8/22/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > > Hello. > > > You see, 50M block was fsynced for 0.25 s. > > > > > > The question is: how to solve this problem and make fsync run with no > > delay. > > > Seems to me that controller's internal write cache is not used > > (strange, > > > because all configuration options are fine), but how to check it? Or, > > maybe, > > > there is another side-effect? > > > > I would suggest that either the controller is NOT configured fine, OR > > there's some bug in how the OS is interacting with it. > > > > What options are there for this RAID controller, and what are they set > > to? Specifically, the writeback / writethru type options for the > > cache, and it might be if it doesn't preoprly detect a battery backup > > module it refuses to go into writeback mode. > > > > >
Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
Hi, On 23/08/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > And here are results of built-in Postgres test script: > Can you tell me how I can execute this script on my system? Where is this script? Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Adapter update.
Hello Group,
I have asked this question already on the NpgSql forum, but didn't get a
response so far. Sorry for cross posting, but want to check if any one had
any suggestions for my problem.
I am trying to do my updates through NpgsqlDataAdapter (I also tried with
Odbc driver with no luck) by passing in a Datatable with changes in it, this
would take forever to do the updates.
This is what I am doing, I am reading the data from SQL Server 2005 and
dumping to out to Postgresql 8.2 database.
using (SqlCommand cmd = new SqlCommand(t.SourceSelect, conn))
{
using (SqlDataReader r = cmd.ExecuteReader())
{
DataSet ds = new DataSet("postgis");
NpgsqlDataAdapter adp = new
NpgsqlDataAdapter(t.DestinationSelect, destConn);
NpgsqlCommandBuilder cmdBld = new
NpgsqlCommandBuilder(adp);
adp.Fill(ds, t.DestinationTable);
DataTable destTbl = ds.Tables[t.DestinationTable];
DataTable srcTblSchema = r.GetSchemaTable();
adp.FillSchema(ds, SchemaType.Mapped,
t.DestinationTable);
// My save method will check if the row exists or not
and would add or update accordingly to the datatable (destTbl). The whole
process
// of comparision is done under 2 mins on 60,000
records.
while (r.Read())
_save(r, srcTblSchema, destTbl, destConn);
r.Close();
// This is the where my application goes into lala land.
If I call this update in my while loop above, it took about two hours to
process
// the whole thing
adp.Update(destTbl);
}
}
I have around 6 records. I also have a geometry field on my table.
I have couple of questions.
1) What do I do to speed up the process? Any database configuration changes,
connection properties,
2) When I call the adapter.update does NpgsqlDataAdapter checks to see if
the column value really changed or not? I believe SQLDataAdapter does this
validation before it actually writes to the database.
Any suggestions and comments are greatly appreciated. Right now I am in dead
waters and can't get it to work on large datasets.
Thank you all.
Regards,
Murali K. Maddali
UAI, Inc.
[EMAIL PROTECTED]
"Always bear in mind that your own resolution to succeed is more important
than any one thing." - Abraham Lincoln
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender. This
message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail.
Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
This script is here: postgresql-8.2.3\src\tools\fsync\test_fsync.c On 8/22/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > > Hi, > > > On 23/08/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > And here are results of built-in Postgres test script: > > > > > > Can you tell me how I can execute this script on my system? Where is > this script? > > Thanks! >
[GENERAL] How to switch off Snowball stemmer for tsearch2?
Hello. We use ispell dictionaries for tsearch2 (ru_ispell_cp1251).. Now Snowball stemmer is also configured. How to properly switch OFF Snowball stemmer for Russian without turning off ispell stemmer? (It is really needed, because "Ivanov" is not the same as "Ivan".) Is it enough and correct to simply delete the row from pg_ts_dict or not? Here is the dump of pg_ts_dict table: dict_namedict_initdict_initoptiondict_lexizedict_comment en_ispellspell_init(internal) DictFile=/usr/lib/ispell/english.med,AffFile=/usr/lib/ispell/english.aff,StopFile=/usr/share/pgsql/contrib/english.stop spell_lexize(internal,internal,integer) en_stemsnb_en_init(internal)contrib/english.stop snb_lexize(internal,internal,integer)English Stemmer. Snowball. ispell_templatespell_init(internal) spell_lexize(internal,internal,integer)ISpell interface. Must have .dict and .aff files ru_ispell_cp1251spell_init(internal) DictFile=/usr/lib/ispell/russian.med,AffFile=/usr/lib/ispell/russian.aff,StopFile=/usr/share/pgsql/contrib/russian.stop.cp1251 spell_lexize(internal,internal,integer) ru_stem_cp1251snb_ru_init_cp1251(internal) contrib/russian.stop.cp1251snb_lexize(internal,internal,integer) Russian Stemmer. Snowball. WINDOWS (cp1251) Encoding ru_stem_koi8snb_ru_init_koi8(internal)contrib/russian.stop snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. KOI8 Encoding ru_stem_utf8snb_ru_init_utf8(internal)contrib/russian.stop.utf8 snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. UTF8 Encoding simpledex_init(internal)dex_lexize(internal,internal,integer) Simple example of dictionary. synonymsyn_init(internal) syn_lexize(internal,internal,integer)Example of synonym dictionary thesaurus_templatethesaurus_init(internal) thesaurus_lexize(internal,internal,integer,internal)Thesaurus template, must be pointed Dictionary and DictFile
Re: [GENERAL] How to switch off Snowball stemmer for tsearch2?
On Wed, 22 Aug 2007, Dmitry Koterov wrote: Hello. We use ispell dictionaries for tsearch2 (ru_ispell_cp1251).. Now Snowball stemmer is also configured. How to properly switch OFF Snowball stemmer for Russian without turning off ispell stemmer? (It is really needed, because "Ivanov" is not the same as "Ivan".) Is it enough and correct to simply delete the row from pg_ts_dict or not? Here is the dump of pg_ts_dict table: don't use dump, plain select would be better. In your case, I'd suggest to follow standard way - create synonym file like ivanov ivanov and use it before other dictionaries. Synonym dictionary will recognize 'Ivanov' and return 'ivanov'. dict_namedict_initdict_initoptiondict_lexizedict_comment en_ispellspell_init(internal) DictFile=/usr/lib/ispell/english.med,AffFile=/usr/lib/ispell/english.aff,StopFile=/usr/share/pgsql/contrib/english.stop spell_lexize(internal,internal,integer) en_stemsnb_en_init(internal)contrib/english.stop snb_lexize(internal,internal,integer)English Stemmer. Snowball. ispell_templatespell_init(internal) spell_lexize(internal,internal,integer)ISpell interface. Must have .dict and .aff files ru_ispell_cp1251spell_init(internal) DictFile=/usr/lib/ispell/russian.med,AffFile=/usr/lib/ispell/russian.aff,StopFile=/usr/share/pgsql/contrib/russian.stop.cp1251 spell_lexize(internal,internal,integer) ru_stem_cp1251snb_ru_init_cp1251(internal) contrib/russian.stop.cp1251snb_lexize(internal,internal,integer) Russian Stemmer. Snowball. WINDOWS (cp1251) Encoding ru_stem_koi8snb_ru_init_koi8(internal)contrib/russian.stop snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. KOI8 Encoding ru_stem_utf8snb_ru_init_utf8(internal)contrib/russian.stop.utf8 snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. UTF8 Encoding simpledex_init(internal)dex_lexize(internal,internal,integer) Simple example of dictionary. synonymsyn_init(internal) syn_lexize(internal,internal,integer)Example of synonym dictionary thesaurus_templatethesaurus_init(internal) thesaurus_lexize(internal,internal,integer,internal)Thesaurus template, must be pointed Dictionary and DictFile Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] How to switch off Snowball stemmer for tsearch2?
Suppose I cannot add such synonyms, because: 1. There are a lot of surnames, cannot take care about all of them. 2. After adding a new surname I have to re-calculate all full-text indices, it costs too much (about 10 days to complete the recalculation). So, I neet exactly what I ast - switch OFF stem guessing if a word is not in the dictionary. On 8/22/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote: > > On Wed, 22 Aug 2007, Dmitry Koterov wrote: > > > Hello. > > > > We use ispell dictionaries for tsearch2 (ru_ispell_cp1251).. > > Now Snowball stemmer is also configured. > > > > How to properly switch OFF Snowball stemmer for Russian without turning > off > > ispell stemmer? (It is really needed, because "Ivanov" is not the same > as > > "Ivan".) > > Is it enough and correct to simply delete the row from pg_ts_dict or > not? > > > > Here is the dump of pg_ts_dict table: > > don't use dump, plain select would be better. In your case, I'd > suggest to follow standard way - create synonym file like > ivanov ivanov > and use it before other dictionaries. Synonym dictionary will recognize > 'Ivanov' and return 'ivanov'. > > > > > dict_namedict_initdict_initoptiondict_lexizedict_comment > > en_ispellspell_init(internal) > > > DictFile=/usr/lib/ispell/english.med,AffFile=/usr/lib/ispell/english.aff,StopFile=/usr/share/pgsql/contrib/english.stop > > spell_lexize(internal,internal,integer) > > en_stemsnb_en_init(internal)contrib/english.stop > > snb_lexize(internal,internal,integer)English Stemmer. Snowball. > > ispell_templatespell_init(internal) > > spell_lexize(internal,internal,integer)ISpell interface. Must have > .dict > > and .aff files > > ru_ispell_cp1251spell_init(internal) > > > DictFile=/usr/lib/ispell/russian.med,AffFile=/usr/lib/ispell/russian.aff,StopFile=/usr/share/pgsql/contrib/russian.stop.cp1251 > > spell_lexize(internal,internal,integer) > > ru_stem_cp1251snb_ru_init_cp1251(internal) > > contrib/russian.stop.cp1251snb_lexize(internal,internal,integer) > > Russian Stemmer. Snowball. WINDOWS (cp1251) Encoding > > ru_stem_koi8snb_ru_init_koi8(internal)contrib/russian.stop > > snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. KOI8 > > Encoding > > ru_stem_utf8snb_ru_init_utf8(internal)contrib/russian.stop.utf8 > > snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. UTF8 > > Encoding > > > simpledex_init(internal)dex_lexize(internal,internal,integer) > > Simple example of dictionary. > > synonymsyn_init(internal) > > syn_lexize(internal,internal,integer)Example of synonym dictionary > > thesaurus_templatethesaurus_init(internal) > > thesaurus_lexize(internal,internal,integer,internal)Thesaurus > template, > > must be pointed Dictionary and DictFile > > > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > ---(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] How to switch off Snowball stemmer for tsearch2?
On Wed, 22 Aug 2007, Dmitry Koterov wrote: Suppose I cannot add such synonyms, because: 1. There are a lot of surnames, cannot take care about all of them. 2. After adding a new surname I have to re-calculate all full-text indices, it costs too much (about 10 days to complete the recalculation). So, I neet exactly what I ast - switch OFF stem guessing if a word is not in the dictionary. no problem, just modify pg_ts_cfgmap, which contains mapping token - dictionaries. if you change configuration you should rebuild tsvector and reindex. 10 days looks very suspicious. On 8/22/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote: On Wed, 22 Aug 2007, Dmitry Koterov wrote: Hello. We use ispell dictionaries for tsearch2 (ru_ispell_cp1251).. Now Snowball stemmer is also configured. How to properly switch OFF Snowball stemmer for Russian without turning off ispell stemmer? (It is really needed, because "Ivanov" is not the same as "Ivan".) Is it enough and correct to simply delete the row from pg_ts_dict or not? Here is the dump of pg_ts_dict table: don't use dump, plain select would be better. In your case, I'd suggest to follow standard way - create synonym file like ivanov ivanov and use it before other dictionaries. Synonym dictionary will recognize 'Ivanov' and return 'ivanov'. dict_namedict_initdict_initoptiondict_lexizedict_comment en_ispellspell_init(internal) DictFile=/usr/lib/ispell/english.med,AffFile=/usr/lib/ispell/english.aff,StopFile=/usr/share/pgsql/contrib/english.stop spell_lexize(internal,internal,integer) en_stemsnb_en_init(internal)contrib/english.stop snb_lexize(internal,internal,integer)English Stemmer. Snowball. ispell_templatespell_init(internal) spell_lexize(internal,internal,integer)ISpell interface. Must have .dict and .aff files ru_ispell_cp1251spell_init(internal) DictFile=/usr/lib/ispell/russian.med,AffFile=/usr/lib/ispell/russian.aff,StopFile=/usr/share/pgsql/contrib/russian.stop.cp1251 spell_lexize(internal,internal,integer) ru_stem_cp1251snb_ru_init_cp1251(internal) contrib/russian.stop.cp1251snb_lexize(internal,internal,integer) Russian Stemmer. Snowball. WINDOWS (cp1251) Encoding ru_stem_koi8snb_ru_init_koi8(internal)contrib/russian.stop snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. KOI8 Encoding ru_stem_utf8snb_ru_init_utf8(internal)contrib/russian.stop.utf8 snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. UTF8 Encoding simpledex_init(internal)dex_lexize(internal,internal,integer) Simple example of dictionary. synonymsyn_init(internal) syn_lexize(internal,internal,integer)Example of synonym dictionary thesaurus_templatethesaurus_init(internal) thesaurus_lexize(internal,internal,integer,internal)Thesaurus template, must be pointed Dictionary and DictFile Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PG Seg Faults Performing a Query
As you requested, here's what bt in gbd reports: (gdb) bt #0 0x003054264571 in fputc () from /lib64/libc.so.6 #1 0x0040dbd2 in print_aligned_text () #2 0x0040f10b in printTable () #3 0x0041020b in printQuery () #4 0x00407906 in SendQuery () #5 0x00409153 in MainLoop () #6 0x0040b16e in main () Please tell me what it means if you can and if I can fix this problem. Thanks, - Bill Thoen Alvaro Herrera wrote: Martijn van Oosterhout escribió: That said, it would be nice if it returned an error instead of crashing. In my opinion it isn't just a matter of "would be nice". It is a possible bug that should be investigated. A look at a stack trace from the crashing process would be the first place to start. In order to do that, please set "ulimit -c unlimited" and rerun the query under psql. That should produce a core file. Then run gdb psql core and inside gdb, execute "bt". Please send that output our way. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Adapter update.
Murali Maddali wrote: This is what I am doing, I am reading the data from SQL Server 2005 and dumping to out to Postgresql 8.2 database. while (r.Read()) _save(r, srcTblSchema, destTbl, destConn); r.Close(); // This is the where my application goes into lala land. If I call this update in my while loop above, it took about two hours to process // the whole thing adp.Update(destTbl); That's probably because it was doing each update in its own transaction. That'll require committing each row to disk. I have around 6 records. I also have a geometry field on my table. I have couple of questions. 1) What do I do to speed up the process? Any database configuration changes, connection properties, Well, if you're doing it all in its own transaction it should be fairly quick. You might also find the DBI-link project useful, if you know any Perl. That would let you reach out directly from PG to the SQL-Server database. http://pgfoundry.org/projects/dbi-link/ 2) When I call the adapter.update does NpgsqlDataAdapter checks to see if the column value really changed or not? I believe SQLDataAdapter does this validation before it actually writes to the database. Sorry, don't know - but you have the source, should be easy enough to check. If not, I'm sure the npgsql people would be happy of a patch. Any suggestions and comments are greatly appreciated. Right now I am in dead waters and can't get it to work on large datasets. Fastest way to load data into PG is via COPY, don't know if npgsql driver supports that. If not, you'd have to go via a text-file. Load the data into an import table (TEMPORARY table probably) and then just use three queries to handle deletion, update and insertion. Comparing one row at a time is adding a lot of overhead. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Audit-trail engine inner-workings
Hey Ted, thanks for the reply, In respect of web application architecture, I'm fully aware of session persistence mechanisms (I work as a developer of web apps after all). What I really would like to know is the inner-workings of the set_session_id and current_session_id as well as reset_session_id C functions. Our company uses a generator called GeneXus which is high level modeling enviroment that deployes to a variety of languages Java being one of them. Being a generator, we don't have much information about the way it generates the code becouse 1) it is a proprietary generator, 2) the code generated is propositally cryptic (don't make any sense at all, with weird var and method names and so on). However, I was given the mission to implement an audit-trail engine to this application. The discussion I sent in the first message of this thread was really helpful, the C functions sent by Manual were crucial. They work fine, **but** I don't have much knowledge in C nor PostgreSQL internal architecture **and** we need to know certain inner details on how this mechanism works in order to take some important decisions. I know that this PostgreSQL C module has a static var that in turn keeps the integer set by the function "set_session_id" - but is this var global to the server's service ? Does PostgreSQL mantain one "instance" of this var per requested connection ? I would like to know how this works. Take the following scenario: - user enters the web app; - web app takes a reference to a connection from the db connection pool; - web app does some db operation When the app takes a reference to a connection and does the operation, just after that, in the application, I set the session id. Then the db operation is performed and the audit stored procedure is ran. The audit-trail engine performs its work and logs the operation and modifications the user did as well as **which application user did it** (which is the purpose of the set_session_id technique - being able to pass the application user who did the operation to the server so that that the audit-trail can log it altogether). Once the operation is done and the connection is back to the pool, does PostgreSQL discart the associated var ? Does it mantain one "instance" per request made ? That's what I would like to know. Thanks, On 8/21/07, Ted Byers <[EMAIL PROTECTED]> wrote: > > > --- Marcelo de Moraes Serpa <[EMAIL PROTECTED]> > wrote: > > > Hello list, > > [snip] > > > > * Each http request gets an instance of a session_id > > ? Or is it > > per-connection ? > > It depends. There is no necessary correlation between > a session as defined within a web application and a > session as defined in the RDBMS. I routinely set up > web applications that may have multiple "sessions" as > seen by the RDBMS. Consider the idiom of doing > operations with the least priviledge required to get > the job done. The application might log into the > database as one databse user with very limited rights, > to authenticate the user and pass data to the web > application regarding what the user is permitted to do > (all server side, on the application server). Then, > the application may log in as a very different user > with limited rights to perform some operation the user > has initiated. So far, we have two sessions as far as > the database is concerned and only one as far as the > web application is concerned. > > If you're working with web applications, you must know > that multiple http requests can share a web > application session, as can multiple web applications > (if written to do so using the single sign-on idiom), > assuming you use technologies such as cookies or URL > rewriting or hidden form fields, or the magic of > ASP.NET, to set up and maintain sessions, and that the > session is restricted to a single http request if you > don't (plain old http/html is stateless, so there is > no useful concept of session without help from other > technologies). > > HTH > > Ted > >
Re: [GENERAL] How to switch off Snowball stemmer for tsearch2?
10 days is not suspicious at all if you need to pull out text for indexing using complex logic and/or schema (i.e. most of the time you retrieve text, not index it). Example: you index some tree leaves (i.e. table with 3 columns: id, parent_id and name) and want to have redundant text index. You therefore need to retrive all leaf's predecessors before doing to_tsvector(), something like that. On 8/22/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote: > On Wed, 22 Aug 2007, Dmitry Koterov wrote: > > > Suppose I cannot add such synonyms, because: > > > > 1. There are a lot of surnames, cannot take care about all of them. > > 2. After adding a new surname I have to re-calculate all full-text indices, > > it costs too much (about 10 days to complete the recalculation). > > > > So, I neet exactly what I ast - switch OFF stem guessing if a word is not in > > the dictionary. > > no problem, just modify pg_ts_cfgmap, which contains mapping > token - dictionaries. > > if you change configuration you should rebuild tsvector and reindex. > 10 days looks very suspicious. > > > > > > On 8/22/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote: > >> > >> On Wed, 22 Aug 2007, Dmitry Koterov wrote: > >> > >>> Hello. > >>> > >>> We use ispell dictionaries for tsearch2 (ru_ispell_cp1251).. > >>> Now Snowball stemmer is also configured. > >>> > >>> How to properly switch OFF Snowball stemmer for Russian without turning > >> off > >>> ispell stemmer? (It is really needed, because "Ivanov" is not the same > >> as > >>> "Ivan".) > >>> Is it enough and correct to simply delete the row from pg_ts_dict or > >> not? > >>> > >>> Here is the dump of pg_ts_dict table: > >> > >> don't use dump, plain select would be better. In your case, I'd > >> suggest to follow standard way - create synonym file like > >> ivanov ivanov > >> and use it before other dictionaries. Synonym dictionary will recognize > >> 'Ivanov' and return 'ivanov'. > >> > >>> > >>> dict_namedict_initdict_initoptiondict_lexizedict_comment > >>> en_ispellspell_init(internal) > >>> > >> DictFile=/usr/lib/ispell/english.med,AffFile=/usr/lib/ispell/english.aff,StopFile=/usr/share/pgsql/contrib/english.stop > >>> spell_lexize(internal,internal,integer) > >>> en_stemsnb_en_init(internal)contrib/english.stop > >>> snb_lexize(internal,internal,integer)English Stemmer. Snowball. > >>> ispell_templatespell_init(internal) > >>> spell_lexize(internal,internal,integer)ISpell interface. Must have > >> .dict > >>> and .aff files > >>> ru_ispell_cp1251spell_init(internal) > >>> > >> DictFile=/usr/lib/ispell/russian.med,AffFile=/usr/lib/ispell/russian.aff,StopFile=/usr/share/pgsql/contrib/russian.stop.cp1251 > >>> spell_lexize(internal,internal,integer) > >>> ru_stem_cp1251snb_ru_init_cp1251(internal) > >>> contrib/russian.stop.cp1251snb_lexize(internal,internal,integer) > >>> Russian Stemmer. Snowball. WINDOWS (cp1251) Encoding > >>> ru_stem_koi8snb_ru_init_koi8(internal)contrib/russian.stop > >>> snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. KOI8 > >>> Encoding > >>> ru_stem_utf8snb_ru_init_utf8(internal)contrib/russian.stop.utf8 > >>> snb_lexize(internal,internal,integer)Russian Stemmer. Snowball. UTF8 > >>> Encoding > >>> > >> simpledex_init(internal)dex_lexize(internal,internal,integer) > >>> Simple example of dictionary. > >>> synonymsyn_init(internal) > >>> syn_lexize(internal,internal,integer)Example of synonym dictionary > >>> thesaurus_templatethesaurus_init(internal) > >>> thesaurus_lexize(internal,internal,integer,internal)Thesaurus > >> template, > >>> must be pointed Dictionary and DictFile > >>> > >> > >> Regards, > >> Oleg > >> _ > >> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > >> Sternberg Astronomical Institute, Moscow University, Russia > >> Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > >> phone: +007(495)939-16-83, +007(495)939-23-83 > >> > >> ---(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 > >> > > > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://ww
Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
On Wed, 22 Aug 2007, Dmitry Koterov wrote: I have written a small perl script to check how slow is fsync for Smart Array E200i controller. Theoretically, because of write cache, fsync MUST cost nothing, but in practice it is not true That theory is fundamentally flawed; you don't know what else is in the operating system write cache in front of what you're trying to fsync, and you also don't know exactly what's in the controller's cache when you start. For all you know, the controller might be filled with cached reads and refuse to kick all of them out. This is a complicated area where tests are much more useful than trying to predict the behavior. You haven't mentioned any details yet about the operating system you're running on; Solaris? Guessing from the device name. There have been some comments passing by lately about the write caching behavior not being turned on by default in that operating system. -- * 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/
[GENERAL] Geographic High-Availability/Replication
Hey all, new postgres user here. We are trying to setup/research an HA/Replicated solution with Postrgresql between a datacenter in LA and a d.c. in NY. We have a private LAN link between the two D.C.'s with a max round-trip of 150ms. We will have a web server at each d.c. (among other servers) that will write/read to/from the local LAN DB. On writes, that data should be xmited to the other data center so that if, for whatever reason, my website request was sent to LA instead of NY, all my session information etc will still exist. I was looking into PGCluster 1.7rc5 but their website states: "...running one Cluster DB node in a different geographical location is not what PGCluster was built for..." Does anyone have an idea? We cannot possibly be the only company needing to do this. Thanks, Matthew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Geographic High-Availability/Replication
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matthew wrote: > Hey all, new postgres user here. We are trying to setup/research an > HA/Replicated solution with Postrgresql between a datacenter in LA and a > d.c. in NY. > > We have a private LAN link between the two D.C.'s with a max round-trip > of 150ms. > > We will have a web server at each d.c. (among other servers) that will > write/read to/from the local LAN DB. On writes, that data should be > xmited to the other data center so that if, for whatever reason, my > website request was sent to LA instead of NY, all my session information > etc will still exist. > > I was looking into PGCluster 1.7rc5 but their website states: > > "...running one Cluster DB node in a different geographical location is > not what PGCluster was built for..." > > Does anyone have an idea? We cannot possibly be the only company needing > to do this. No, but you are the only company that likely is trying to do it cross continent ;) :). You can not do multi master cross continent reliably. There are ways using custom caches etc, to pull all session info into a geo-redundant data store though. Sincerely, Joshua D. Drake > > Thanks, > Matthew > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL 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 iD8DBQFGzJ6HATb/zqfZUUQRAoSFAJ99sfzpgVjWYbtTKDIzC+EdChJRcQCgjTZQ nKVW3xmmvQvjBltykxz8Gco= =qv3s -END PGP SIGNATURE- ---(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] How to switch off Snowball stemmer for tsearch2?
Oh! Thanks!
delete from pg_ts_cfgmap where dict_name = ARRAY['ru_stem'];
solves the root of the problem. But unfortunately
russian.med(ru_ispell_cp1251) contains all Russian names, so "Ivanov"
is converted to
"Ivan" by ispell too. :-(
Now
select lexize('ru_ispell_cp1251', 'Дмитриев') -> "Дмитрий"
select lexize('ru_ispell_cp1251', 'Иванов') -> "Иван"
- it is completely wrong!
I have a database with all Russian name, is it possible to use it (how?) to
make lexize() not to convert "Ivanov" to "Ivan" even if the ispell
dicrionary contains an element for "Ivan"? So, this pseudo-code logic is
needed:
function new_lexize($string) {
$stem = lexize('ru_ispell_cp1251', $string);
if ($stem in names_database) return $string; else return $stem;
}
Maybe tsearch2 implements this logic already?
On 8/22/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote:
>
> On Wed, 22 Aug 2007, Dmitry Koterov wrote:
>
> > Suppose I cannot add such synonyms, because:
> >
> > 1. There are a lot of surnames, cannot take care about all of them.
> > 2. After adding a new surname I have to re-calculate all full-text
> indices,
> > it costs too much (about 10 days to complete the recalculation).
> >
> > So, I neet exactly what I ast - switch OFF stem guessing if a word is
> not in
> > the dictionary.
>
> no problem, just modify pg_ts_cfgmap, which contains mapping
> token - dictionaries.
>
> if you change configuration you should rebuild tsvector and reindex.
> 10 days looks very suspicious.
>
>
> >
> > On 8/22/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote:
> >>
> >> On Wed, 22 Aug 2007, Dmitry Koterov wrote:
> >>
> >>> Hello.
> >>>
> >>> We use ispell dictionaries for tsearch2 (ru_ispell_cp1251)..
> >>> Now Snowball stemmer is also configured.
> >>>
> >>> How to properly switch OFF Snowball stemmer for Russian without
> turning
> >> off
> >>> ispell stemmer? (It is really needed, because "Ivanov" is not the same
> >> as
> >>> "Ivan".)
> >>> Is it enough and correct to simply delete the row from pg_ts_dict or
> >> not?
> >>>
> >>> Here is the dump of pg_ts_dict table:
> >>
> >> don't use dump, plain select would be better. In your case, I'd
> >> suggest to follow standard way - create synonym file like
> >> ivanov ivanov
> >> and use it before other dictionaries. Synonym dictionary will recognize
> >> 'Ivanov' and return 'ivanov'.
> >>
> >>>
> >>>
> dict_namedict_initdict_initoptiondict_lexizedict_comment
> >>> en_ispellspell_init(internal)
> >>>
> >>
> DictFile=/usr/lib/ispell/english.med,AffFile=/usr/lib/ispell/english.aff,StopFile=/usr/share/pgsql/contrib/english.stop
> >>> spell_lexize(internal,internal,integer)
> >>> en_stemsnb_en_init(internal)contrib/english.stop
> >>> snb_lexize(internal,internal,integer)English Stemmer. Snowball.
> >>> ispell_templatespell_init(internal)
> >>> spell_lexize(internal,internal,integer)ISpell interface. Must have
> >> .dict
> >>> and .aff files
> >>> ru_ispell_cp1251spell_init(internal)
> >>>
> >>
> DictFile=/usr/lib/ispell/russian.med,AffFile=/usr/lib/ispell/russian.aff,StopFile=/usr/share/pgsql/contrib/russian.stop.cp1251
> >>> spell_lexize(internal,internal,integer)
> >>> ru_stem_cp1251snb_ru_init_cp1251(internal)
> >>> contrib/russian.stop.cp1251snb_lexize(internal,internal,integer)
> >>> Russian Stemmer. Snowball. WINDOWS (cp1251) Encoding
> >>> ru_stem_koi8snb_ru_init_koi8(internal)contrib/russian.stop
> >>> snb_lexize(internal,internal,integer)Russian Stemmer. Snowball.
> KOI8
> >>> Encoding
> >>>
> ru_stem_utf8snb_ru_init_utf8(internal)contrib/russian.stop.utf8
> >>> snb_lexize(internal,internal,integer)Russian Stemmer. Snowball.
> UTF8
> >>> Encoding
> >>>
> >>
> simpledex_init(internal)dex_lexize(internal,internal,integer)
> >>> Simple example of dictionary.
> >>> synonymsyn_init(internal)
> >>> syn_lexize(internal,internal,integer)Example of synonym dictionary
> >>> thesaurus_templatethesaurus_init(internal)
> >>> thesaurus_lexize(internal,internal,integer,internal)Thesaurus
> >> template,
> >>> must be pointed Dictionary and DictFile
> >>>
> >>
> >> Regards,
> >> Oleg
> >> _
> >> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> >> Sternberg Astronomical Institute, Moscow University, Russia
> >> Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
> >> phone: +007(495)939-16-83, +007(495)939-23-83
> >>
> >> ---(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
> >>
> >
>
> Regards,
> Oleg
> _
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> S
Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
> > > I have written a small perl script to check how slow is fsync for Smart > > Array E200i controller. Theoretically, because of write cache, fsync > MUST > > cost nothing, but in practice it is not true > > That theory is fundamentally flawed; you don't know what else is in the > operating system write cache in front of what you're trying to fsync, and > you also don't know exactly what's in the controller's cache when you > start. For all you know, the controller might be filled with cached reads > and refuse to kick all of them out. This is a complicated area where tests are much more useful than trying to predict the behavior. Nobody else writes, nobody reads. The machine is for tests, it is clean. I monitor dstat - for 5 minutes before there is no disc activity. So I suppose that the conntroller cache is already flushed before I am running the test. > tests are much more useful than trying to predict the behavior. You > haven't mentioned any details yet about the operating system you're > running on; Solaris? Guessing from the device name. There have been some > comments passing by lately about the write caching behavior not being > turned on by default in that operating system. > Linux CentOS x86_64. A lot of memory, 8 processors. Filesystem is ext2 (to reduce the journalling side-effects). OS write caching is turned on, turned off and also set to flush once per second (all these cases are tested, all these have no effect). The question is - MUST my test script report about a zero fsync time or not, if the controler has built-in and large write cache. If yes, something wrong with controller or drivers (how to diagnose?). If no, why? There are a lot of discussions in this maillist about fsync & battery-armed controller, people say that a controller with builtin cache memory reduces the price of fsync to zero. I just want to achieve this.
Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
Also, the controller is configured to use 75% of its memory for write caching and 25% - for read caching. So reads cannot flood writes. On 8/23/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > > I have written a small perl script to check how slow is fsync for Smart > > > Array E200i controller. Theoretically, because of write cache, fsync > > MUST > > > cost nothing, but in practice it is not true > > > > That theory is fundamentally flawed; you don't know what else is in the > > operating system write cache in front of what you're trying to fsync, > > and > > you also don't know exactly what's in the controller's cache when you > > start. For all you know, the controller might be filled with cached > > reads > > and refuse to kick all of them out. This is a complicated area where > > tests are much more useful than trying to predict the behavior. > > > Nobody else writes, nobody reads. The machine is for tests, it is clean. I > monitor dstat - for 5 minutes before there is no disc activity. So I suppose > that the conntroller cache is already flushed before I am running the test. > > > > tests are much more useful than trying to predict the behavior. You > > haven't mentioned any details yet about the operating system you're > > > running on; Solaris? Guessing from the device name. There have been some > > > > comments passing by lately about the write caching behavior not being > > turned on by default in that operating system. > > > Linux CentOS x86_64. A lot of memory, 8 processors. > Filesystem is ext2 (to reduce the journalling side-effects). > OS write caching is turned on, turned off and also set to flush once per > second (all these cases are tested, all these have no effect). > > The question is - MUST my test script report about a zero fsync time or > not, if the controler has built-in and large write cache. If yes, something > wrong with controller or drivers (how to diagnose?). If no, why? > > There are a lot of discussions in this maillist about fsync & > battery-armed controller, people say that a controller with builtin cache > memory reduces the price of fsync to zero. I just want to achieve this. > > >
[GENERAL] reporting tools
We are looking for a reporting tool that will enable users to generate their own reports. Something like Crystal Reports. Anyone using something like this with Postgresql? -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(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] ERROR: could not open relation with OID
We are trying to perform a 'reindex database' and it will fail at varying points with a message like: ERROR: could not open relation with OID 587495058 or ERROR: could not open relation with OID 587603875 When we queried pg_class we got no rows returned: select oid,* from pg_class where oid in (587603875, 587495058); oid | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl -+-+--+-+--+---+ -+---+--+---+---+--- +-+-+-+--+---+-- ---+--+--+-+++-- ---++ (0 rows) select oid,* from pg_class where oid>587603875; oid| relname| relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl ---+--+--+---+-- +---+-+---+--+---+-- -+---+-+-+-+ --+---+-+--+--+-+--- -++-++ 587656467 | reindex_audit_network_id_idx | 2200 | 0 | 10 | 403 | 587656467 | 0 |2 |65 | 0 | 0 | f | f | i |1 | 0 | 0 |0 |0 | 0 | f | f | f | f | 587656343 | reindex_audit| 2200 | 587656344 | 10 | 0 | 587656343 | 0 |1 |65 | 0 | 0 | t | f | r |8 | 0 | 0 |0 |0 | 0 | f | f | f | f | 587656468 | reindex_audit_audit_id_idx | 2200 | 0 | 10 | 403 | 587656468 | 0 |2 |65 | 0 | 0 | f | f | i |1 | 0 | 0 |0 |0 | 0 | f | f | f | f | (3 rows) There were no reindex processes running in pg_stat_activity and stopping/restarting postgres did not clear up these entries. What could be causing these errors?
Re: [GENERAL] reporting tools
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Geoffrey wrote: > We are looking for a reporting tool that will enable users to generate > their own reports. Something like Crystal Reports. > > Anyone using something like this with Postgresql? > Why not Crystal Reports? Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL 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 iD8DBQFGzMDnATb/zqfZUUQRAnFVAJ9DJuRS9fC9lxYLprFjJDksZooa0QCfTact hyg0jWPxREKbcYMblS/0fp4= =Z38E -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] CPU load high
Hello. I have a web-server with php 5.2 connected to postgres 8.0 backend. Most of the queries the users are doing are SELECTs (100-150 in a second for 100 concurrent users), with a 5-10 INSERTs/UPDATEs at the same time. There is also a demon running in the background doing some work once every 100ms. The problem is that after the number of concurrent users rises to 100, CPU becomes almost 100% loaded. How do I find out what's hogging the CPU? 'top' shows demon using 8% cpu on top, and some amount of postgres processes each using 2% cpu with some apache processes occassionally rising with 2% cpu also. Often the writer process is at the top using 10% cpu. And the second question is that over time demon and writer processes use more and more shared memory - is it normal? Thanks in advance. ---(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] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/22/07 17:45, Dmitry Koterov wrote: > Also, the controller is configured to use 75% of its memory for write > caching and 25% - for read caching. So reads cannot flood writes. That seems to be a very extreme ratio. Most databases do *many* times more reads than writes. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGzMNDS9HxQb37XmcRAgMLAJsGvA43MKrfRKoyf0W0Nv5/VWu5gACdG8qh oJbb6+7FbotnEXnf9PdYF+E= =Esfi -END PGP SIGNATURE- ---(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] reporting tools
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Geoffrey wrote: We are looking for a reporting tool that will enable users to generate their own reports. Something like Crystal Reports. Anyone using something like this with Postgresql? Why not Crystal Reports? My bad. We are looking for an open source reporting tool that will enable users to generate their own reports. Something like Crystal Reports. ;) -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(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] reporting tools
On 8/22/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Geoffrey wrote: > > We are looking for a reporting tool that will enable users to generate > > their own reports. Something like Crystal Reports. > > > > Anyone using something like this with Postgresql? > > > > Why not Crystal Reports? Yeah, I'm not the biggest fan of CR, but it's worked with PostgreSQL for quite some time now. We had it hitting a pg7.2 db back in the day, when hip kids road around in rag top roadsters and wore tshirts with cigarettes rolled in their sleeves. Also, look at Pentaho. It's open source and pretty good. ---(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] Problem with UPDATE and UNIQUE
On Aug 22, 2007, at 1:02 , Frank Millman wrote: I want to store data in a 'tree' form, with a fixed number of levels, so that each level has a defined role. First thought: fixed, predetermined levels, separate tables for each level. If a more general approach is desired, your options are generally adjacency list, nested sets, or contrib/ltree. Each has their own strengths and weaknesses. I have the following (simplified) table - CREATE TABLE treedata ( rowid serial primary key, levelno int not null, parentid int references treedata, seq int not null, code varchar not null, description varchar not null ); rowid + parentid looks like adjacency list to me. Note that you're storing redundant data (the levelno, which can be derived from the rowid/parentid relationships), which you may want to do for performance reasons, but does make things more complicated: you're essentially caching data which brings with it problems of cache invalidation. In this case, you need to make sure you're updating levelno whenever it needs to be updated. (Which I'm sure you've already thought of.) To describe each of the levels in the tree, I have the following table - CREATE TABLE treelevels ( levelno int primary key, code varchar unique not null, description varchar not null ); Having each level as its own table would make this redundant, but again, that might not fit with what you're modeling. Typical values for this table could be - (0,'Prod','Product code') (1,'Cat','Product category') (2,'*','All products') This makes me think you'll want to rethink your schema a bit, as you're mixing different types of data: categories and products. I'd at least separate this out into a products table and a categories table. The categories table may in fact still require some kind of tree structure, but I don't think products belongs as part of it. CREATE TABLE products ( product_code text PRIMARY KEY , product_name text NOT NULL UNIQUE , product_description TEXT NOT NULL ); CREATE TABLE categories ( category_id INTEGER PRIMARY KEY , category_name TEXT NOT NULL UNIQUE , category_description TEXT NOT NULL , parent_category_id INTEGER NOT NULL REFERENCES categories , level INTEGER NOT NULL , seq INTEGER NOT NULL ); One difference here is that I have a NOT NULL constraint on parent_category_id, and I'd define the root as a category_id that has a parent_category = category_id. You should be able to add a check constraint that makes sure only level = 0 has parent_category_id = category_id and a unique index so only one category can have level 0. Something like: -- check constraint CHECK ( CASE WHEN category_id = parent_category_id THEN level = 0 ELSE category_id <> parent_category_id END) -- unique index CREATE UNIQUE INDEX categories_root_uniq_idx ON categories (level) WHERE level = 0; These are untested, but I think they should work. Given that you'd like to determine the order of the items, I think you might be interested in using nested sets rather than adjacency list, as this information is automatically encoded for you. This would look something like: CREATE FUNCTION valid_nested_interval_bounds(INTEGER, INTEGER) RETURNS BOOLEAN IMMUTABLE LANGUAGE SQL AS $body$ SELECT $1 < $2 -- make sure they're properly ordered AND ($2 - $1 - 1) % 2 = 0 -- useful if using the more strict versions of nested set (where there no gaps) -- and you can easily calculate the total number of descendants -- from just the upper and lower bounds of the parent node $body$; I generally separate the validity test into a separate function as I often have a couple different nested set hierarchies in the same database, and it makes the table definition clearer. I believe the function is inlined in the check constraint, but I'm not sure. You can, of course, move the SQL in the function straight into table definition. CREATE TABLE categories ( , category_name TEXT PRIMARY KEY , category_description TEXT NOT NULL , category_lower INTEGER NOT NULL UNIQUE -- often called "left" , category_upper INTEGER NOT NULL UNIQUE -- often called "right" , CHECK (valid_nested_interval_bounds(category_lower, category_upper)) ); Another advantage of nested sets is that selecting all of the descendants of a particular node is very easy. The downside of the nested set model is that inserts and updates are a bit more complicated and db intensive. If your categories aren't going to change much, this shouldn't be a problem. Again, you could add level information into the categories table, but you'd need to make sure to update it appropriately. CREATE TABLE category_products ( category_name TEXT NOT NULL REFERENCES categories , product_code TEXT NOT NULL REFERENCES products , UNIQUE (category_name, product_code) ); My comments below refer to your or
Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
On 8/22/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > Also, the controller is configured to use 75% of its memory for write > caching and 25% - for read caching. So reads cannot flood writes. 128 Meg is a pretty small cache for a modern RAID controller. I wonder if this one is just a dog performer. Have you looked at things like the Areca or Escalade with 1g or more cache on them? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PG Seg Faults Performing a Query
Bill Thoen <[EMAIL PROTECTED]> writes: > As you requested, here's what bt in gbd reports: > (gdb) bt > #0 0x003054264571 in fputc () from /lib64/libc.so.6 > #1 0x0040dbd2 in print_aligned_text () > #2 0x0040f10b in printTable () > #3 0x0041020b in printQuery () > #4 0x00407906 in SendQuery () > #5 0x00409153 in MainLoop () > #6 0x0040b16e in main () Hmph. So it looks like it successfully absorbed the query result from the backend and is dying trying to print it. What this smells like to me is someplace failing to check for a malloc() failure result, but I don't see any such places in print.c. And I didn't have any luck reproducing the problem while exercising 8.1 psql on 64-bit Fedora 6. I got either "out of memory for query result" or plain "out of memory", nothing else. Can you install the postgresql debuginfo RPM, or reproduce this on a custom build with debugging enabled? Knowing just where the crash is might help more. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] reporting tools
On Aug 22, 2007, at 7:21 PM, Geoffrey wrote: We are looking for an open source reporting tool that will enable users to generate their own reports. Something like Crystal Reports. ;) I was looking at a couple the other day: iReport (part of Jasper), OpenRPT, and DataVision (http://datavision.sourceforge.net/). The DataVision page has some links to other report writers. Hopefully you'll do better than I did -- I also wanted something that works on OS X. All of the above meet that criteria by using Java or GTK, but the user interfaces are hard to take if you want a typical Mac application. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Array with Subselect / ANY - cast?
On Aug 21, 2007, at 14:35 , Josh Trutwin wrote: it's a simple example but the number of list-type fields is anticipated to be VERY high so I decided to deal with arrays instead of creating all sorts of normalized tables. Was it the right thing to do? No offense, but I doubt it. This doesn't seem to be a very good reason to move away from normal database practices. As I previously mentioned, you're throwing referential integrity out the window. Do things right unless you have a measurable reason to do things another way. Sounds like premature optimization to me, so all the normal rules of premature optimization apply. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] reporting tools
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 John DeSoi wrote: > > On Aug 22, 2007, at 7:21 PM, Geoffrey wrote: > >> We are looking for an open source reporting tool that will enable >> users to generate their own reports. Something like Crystal Reports. ;) > > I was looking at a couple the other day: iReport (part of Jasper), > OpenRPT, and DataVision (http://datavision.sourceforge.net/). The > DataVision page has some links to other report writers. Hopefully you'll > do better than I did -- I also wanted something that works on OS X. All > of the above meet that criteria by using Java or GTK, but the user > interfaces are hard to take if you want a typical Mac application. MS Access? Joshua D. Drake > > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL 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 iD8DBQFGzN5PATb/zqfZUUQRAgm0AKCCqXpYK0lj4nO6wgF/N4aiLqfHVwCglB1U mdN7zbzfOZ6/9SKE3xcLi/M= =LCrl -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] %TYPE
Checking out a bug in my code I find in my table definition: CREATE TABLE publications ( ... name character varying(60) NOT NULL, ... ); while in the function I use to populate the web page, which amalgamates info from several sources, I have: CREATE TYPE npc_page_details_type AS ( ... instance_name character varying(40), ... ); (where instances inherits name from publications). In other words, I changed one definition and forgot to change the other. I've been looking to see if postgresql allows type definitions like the ones I'm used to from Oracle, where I can say: CREATE TYPE npc_page_details_type AS ( ... instance_name instances.name%type, ... ); and it will pick up the current type of instances.name when the user type gets compiled. However I haven't been able to find anything. Is anything like that available? Or should I just update my definitions and grit my teeth and carry on? Cheers, G. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] pg_dump causes postgres crash
Fairly new (less than a week) install. "PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath)" database size around 43 gigabytes. 2 attempts at a pg_dump across the network caused the database to go down... The first time I thought it was because of mismatched pg_dump (was version 8.0.X)...but the second time it was definitely 8.2.4 version of pg_dump. My first thought was corruption...but this database has successfully seeded 2 slony subscriber nodes from scratch as well running flawlessly under heavy load for the past week. Even more odd is that a LOCAL pg_dump (from on the box) succeeded just fine tonight (after the second crash). Thoughts? First Crash--- backup-srv2 prod_backup # time /usr/bin/pg_dump --format=c --compress=9 --ignore-version --username=backup --host=prod_server prod > x pg_dump: server version: 8.2.4; pg_dump version: 8.0.13 pg_dump: proceeding despite version mismatch pg_dump: WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. pg_dump: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. pg_dump: SQL command to dump the contents of table "access_logs" failed: PQendcopy() failed. pg_dump: Error message from server: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. pg_dump: The command was: COPY public.access_logs (ip, username, "action", date, params) TO stdout; --Second Crash backup-srv2 ~ # time /usr/bin/pg_dump --format=c --compress=9 --username=backup --host=prod_server prod | wc -l pg_dump: Dumping the contents of table "audit" failed: PQgetCopyData() failed. pg_dump: Error message from server: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. pg_dump: The command was: COPY public.audit (audit_id, entity_id, table_name, serial_id, audit_action, when_ts, user_id, user_ip) TO stdout; Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games. http://sims.yahoo.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_dump causes postgres crash
>From the logs tonight when the second crash occurred.. Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848 local0.info] [6-1] 2007-08-22 20:45:12 CDT LOG: received smart shutdown request Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848 local0.info] [7-1] 2007-08-22 20:45:12 CDT LOG: server process (PID 20188) was terminated by signal 11 Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848 local0.info] [8-1] 2007-08-22 20:45:12 CDT LOG: terminating any other active server processes There was a core file created...but I believe I do not have postgresql compiled with debug info.(well, a pstack provided nothing useful) pstack core |more core 'core' of 20188: /usr/local/pgsql/bin/postgres -D /db fee8ec23 sk_value (10023d, 105d8b00, d2840f, 1c7f, f20f883, 10584) + 33 0c458b51 (0, 0, 511f1600, 2000400, ff001c09, 467f71ea) () Once again...a local pg_dump worked just fine 30 minutes later.. We have introduced some new network architecture which is acting odd lately (dell managed switches, netscreen ssgs, etc) and the database itself resides on a zfs partition on a Pillar SAN (connected via fibre channel) Any thoughts would be appreciated. Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.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] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
On Wed, 22 Aug 2007, Ron Johnson wrote: That seems to be a very extreme ratio. Most databases do *many* times more reads than writes. Yes, but the OS has a lot more memory to cache the reads for you, so you should be relying more heavily on it in cases like this where the card has a relatively small amount of memory. The main benefit for having a caching controller is fsync acceleration, the reads should pass right through the controller's cache and then stay in system RAM afterwards if they're needed again. -- * 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] pg_dump causes postgres crash
Jeff Amiel <[EMAIL PROTECTED]> writes: > Even more odd is that a LOCAL pg_dump (from on the > box) succeeded just fine tonight (after the second > crash). That seems to eliminate the theory of a crash due to data corruption ... unless the corruption somehow repaired itself in the intervening 30 minutes, which hardly seems likely. > First Crash--- > backup-srv2 prod_backup # time /usr/bin/pg_dump > --format=c --compress=9 --ignore-version > --username=backup --host=prod_server prod > x > pg_dump: server version: 8.2.4; pg_dump version: > 8.0.13 > pg_dump: proceeding despite version mismatch > pg_dump: WARNING: terminating connection because of > crash of another server process > DETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit, > because another server process exited abnormally and > possibly corrupted shared memory. Notice that pg_dump is showing that the crash was in some OTHER server process, not the one it was attached to. > --Second Crash > backup-srv2 ~ # time /usr/bin/pg_dump --format=c > --compress=9 --username=backup --host=prod_server > prod | wc -l > pg_dump: Dumping the contents of table "audit" failed: > PQgetCopyData() failed. > pg_dump: Error message from server: server closed the > connection unexpectedly >This probably means the server terminated > abnormally >before or while processing the request. > pg_dump: The command was: COPY public.audit (audit_id, This one looks more like it might have been the directly connected server process that crashed. However, your postmaster log from the other message: > From the logs tonight when the second crash occurred.. > Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848 > local0.info] [6-1] 2007-08-22 20:45:12 CDT LOG: > received smart shutdown request > Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848 > local0.info] [7-1] 2007-08-22 20:45:12 CDT LOG: > server process (PID 20188) was terminated by signal 11 > Aug 22 20:45:12 db-1 postgres[5805]: [ID 748848 > local0.info] [8-1] 2007-08-22 20:45:12 CDT LOG: > terminating any other active server processes raises still more questions: where the heck did the "smart shutdown request" (that is to say, a SIGTERM interrupt to the postmaster) come from? It's far too much of a coincidence for that to have occurred within a second of detecting the server process crash. > We have introduced some new network architecture which > is acting odd lately (dell managed switches, netscreen > ssgs, etc) and the database itself resides on a zfs > partition on a Pillar SAN (connected via fibre > channel) I can't help thinking you are looking at generalized system instability. Maybe someone knocked a few cables loose while installing new network hardware? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
On Wed, 22 Aug 2007, Dmitry Koterov wrote: We are trying to use HP CISS contoller (Smart Array E200i) There have been multiple reports of problems with general performance issues specifically with the cciss Linux driver for other HP cards. The E200i isn't from the same series, but I wouldn't expect that their drivers have gotten much better. Wander through the thread at http://svr5.postgresql.org/pgsql-performance/2006-07/msg00257.php to see one example I recall from last year; there are more in the archives if you search around a bit. I have written a small perl script to check how slow is fsync for Smart Array E200i controller. Theoretically, because of write cache, fsync MUST cost nothing, but in practice it is not true: fsync took 0.247033 s For comparision sake, your script run against my system with an Areca ARC-1210 card with 256MB of cache 20 times gives me the following minimum and maximum times (full details on my server config are at http://www.westnet.com/~gsmith/content/postgresql/serverinfo.htm ): fsync took 0.039676 s fsync took 0.041137 s And here's what the last set of test_fsync results look like on my system: Compare file sync methods with 2 8k writes: open o_sync, write 0.099819 write, fdatasync 0.100054 write, fsync,0.094009 So basically your card is running 3 (test_fsync) to 6 (your script) times slower than my Areca unit on these low-level tests. I don't know that it's possible to drive the fsync times completely to zero, but there's certainly a whole lot of improvement from where you are to what I'd expect from even a cheap caching controller like I'm using. I've got maybe $900 worth of hardware total in this box and it's way faster than yours in this area. (Also, I tried to manually specify open_sync method in postgresql.conf, but after that Postgres database had completely crashed. :-) This is itself a sign there's something really strange going on. There's something wrong with your system, your card, or the OS/driver you're using if open_sync doesn't work under Linux; in fact, it should be faster in practice even if it looks a little slower on test_fsync. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] %TYPE
On Aug 22, 2007, at 21:42 , Ged wrote: I've been looking to see if postgresql allows type definitions like the ones I'm used to from Oracle, where I can say: CREATE TYPE npc_page_details_type AS ( ... instance_name instances.name%type, ... ); and it will pick up the current type of instances.name when the user type gets compiled. I think you could use domains for something like this. CREATE DOMAIN instance_name_type AS character varying; And then use the domain in your types. CREATE TYPE npc_page_details_type AS ( instance_name instance_name_type ); CREATE table publications ( name instance_name_type PRIMARY KEY ); However, in this particular case I don't see the point. There is no performance advantage to using varchar(n) over just plain varchar or the PostgreSQL type text, which although nonstandard tends to be more common among developers used to PostgreSQL. Only if you have a specific business reason that these columns absolutely must not have more than x number of characters should you use varchar(n). Otherwise you're going to be changing data types every time you want to change the limit which is not all that much fun. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ERROR: could not open relation with OID
"Susy Ham" <[EMAIL PROTECTED]> writes: > We are trying to perform a 'reindex database' and it will fail at > varying points with a message like: > ERROR: could not open relation with OID 587495058 >or > ERROR: could not open relation with OID 587603875 Ugh :-( > When we queried pg_class we got no rows returned: > select oid,* from pg_class where oid in (587603875, 587495058); Indeed not, since the error message is complaining exactly that it couldn't find any such pg_class row. What you need to be looking into is where the reference to the OID came from. I'd try looking into pg_index and maybe pg_depend to see if there are rows linking to these OIDs. Also, in a message of this sort you really oughta expand a bit on your reasons for needing a 'reindex database' in the first place, and the procedure you are using for it. If you are trying to recover from system catalog corruption then there's a number of subtle tricks to use --- the main one being to run the backend with -P (which means "don't trust the system indexes"). While I'm asking for specifics: exactly which PG version is this? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] CPU load high
"Max Zorloff" <[EMAIL PROTECTED]> writes: > ... The problem is that after the number of concurrent users rises to > 100, CPU becomes almost 100% loaded. How do I find out what's hogging the > CPU? > 'top' shows demon using 8% cpu on top, and some amount of postgres > processes each using 2% cpu with some apache processes occassionally > rising with 2% cpu also. Often the writer process is at the top using 10% > cpu. IOW there's nothing particular hogging the CPU? Maybe you need more hardware than you've got, or maybe you could fix it by trying to optimize your most common queries. It doesn't sound like there'll be any quick single-point fix though. > And the second question is that over time demon and writer processes use > more and more shared memory - is it normal? This is probably an artifact. Many versions of "top" report a process as having used as many pages of shared memory as it's actually touched in its lifetime. So if you have lots of shared buffers, then any one Postgres process will show growth of reported memory usage as it randomly happens to access one buffer or another, eventually maxing out at whatever you've got the PG shared memory segment size set to. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] PostgreSQL vs Firebird feature comparison finished
Check it out here: http://www.amsoftwaredesign.com/pg_vs_fb When comparing in the grid the only major advantage FB has is probably BLOB support. PG only suppports 1 gb while FB supports 32gb. Bytea is pretty slow as well when compared to the FB BLOB support. The other area is Character sets and collation. They support it at a field level as well as the database. Other than that I would say PG kicks butt. If there is any interest I could also add MySQL 5.0 to the mix as the third column. Later, Tony ---(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] How to switch off Snowball stemmer for tsearch2?
On Thu, 23 Aug 2007, Dmitry Koterov wrote:
Oh! Thanks!
delete from pg_ts_cfgmap where dict_name = ARRAY['ru_stem'];
solves the root of the problem. But unfortunately
russian.med(ru_ispell_cp1251) contains all Russian names, so "Ivanov"
is converted to
"Ivan" by ispell too. :-(
Now
select lexize('ru_ispell_cp1251', 'Дмитриев') -> "Дмитрий"
select lexize('ru_ispell_cp1251', 'Иванов') -> "Иван"
- it is completely wrong!
I have a database with all Russian name, is it possible to use it (how?) to
if you have such database why just don't write special dictionary and
put it in front ?
make lexize() not to convert "Ivanov" to "Ivan" even if the ispell
dicrionary contains an element for "Ivan"? So, this pseudo-code logic is
needed:
function new_lexize($string) {
$stem = lexize('ru_ispell_cp1251', $string);
if ($stem in names_database) return $string; else return $stem;
}
Maybe tsearch2 implements this logic already?
sure, it's how text search mapping works. Dmitry, seems your company could be
my client :)
On 8/22/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote:
On Wed, 22 Aug 2007, Dmitry Koterov wrote:
Suppose I cannot add such synonyms, because:
1. There are a lot of surnames, cannot take care about all of them.
2. After adding a new surname I have to re-calculate all full-text
indices,
it costs too much (about 10 days to complete the recalculation).
So, I neet exactly what I ast - switch OFF stem guessing if a word is
not in
the dictionary.
no problem, just modify pg_ts_cfgmap, which contains mapping
token - dictionaries.
if you change configuration you should rebuild tsvector and reindex.
10 days looks very suspicious.
On 8/22/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote:
On Wed, 22 Aug 2007, Dmitry Koterov wrote:
Hello.
We use ispell dictionaries for tsearch2 (ru_ispell_cp1251)..
Now Snowball stemmer is also configured.
How to properly switch OFF Snowball stemmer for Russian without
turning
off
ispell stemmer? (It is really needed, because "Ivanov" is not the same
as
"Ivan".)
Is it enough and correct to simply delete the row from pg_ts_dict or
not?
Here is the dump of pg_ts_dict table:
don't use dump, plain select would be better. In your case, I'd
suggest to follow standard way - create synonym file like
ivanov ivanov
and use it before other dictionaries. Synonym dictionary will recognize
'Ivanov' and return 'ivanov'.
dict_namedict_initdict_initoptiondict_lexizedict_comment
en_ispellspell_init(internal)
DictFile=/usr/lib/ispell/english.med,AffFile=/usr/lib/ispell/english.aff,StopFile=/usr/share/pgsql/contrib/english.stop
spell_lexize(internal,internal,integer)
en_stemsnb_en_init(internal)contrib/english.stop
snb_lexize(internal,internal,integer)English Stemmer. Snowball.
ispell_templatespell_init(internal)
spell_lexize(internal,internal,integer)ISpell interface. Must have
.dict
and .aff files
ru_ispell_cp1251spell_init(internal)
DictFile=/usr/lib/ispell/russian.med,AffFile=/usr/lib/ispell/russian.aff,StopFile=/usr/share/pgsql/contrib/russian.stop.cp1251
spell_lexize(internal,internal,integer)
ru_stem_cp1251snb_ru_init_cp1251(internal)
contrib/russian.stop.cp1251snb_lexize(internal,internal,integer)
Russian Stemmer. Snowball. WINDOWS (cp1251) Encoding
ru_stem_koi8snb_ru_init_koi8(internal)contrib/russian.stop
snb_lexize(internal,internal,integer)Russian Stemmer. Snowball.
KOI8
Encoding
ru_stem_utf8snb_ru_init_utf8(internal)contrib/russian.stop.utf8
snb_lexize(internal,internal,integer)Russian Stemmer. Snowball.
UTF8
Encoding
simpledex_init(internal)dex_lexize(internal,internal,integer)
Simple example of dictionary.
synonymsyn_init(internal)
syn_lexize(internal,internal,integer)Example of synonym dictionary
thesaurus_templatethesaurus_init(internal)
thesaurus_lexize(internal,internal,integer,internal)Thesaurus
template,
must be pointed Dictionary and DictFile
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(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
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Regards,
Re: [GENERAL] "out of memory" error
hubert depesz lubaczewski wrote: On Wed, Aug 22, 2007 at 07:07:20PM +0200, Christian Schröder wrote: These are the current settings from the server configuration: shared_buffers = 3GB this is *way* to much. i would suggest lowering it to 1gig *at most*. Ok, I can do this, but why can more memory be harmful? max memory size (kbytes, -m) 3441565 this looks like too close to shared_buffers. again - lower it. What happens if I set shared_buffers higher than the ulimit? The machine is a linux box with 4 GB memory running PostgreSQL 8.2.4. is it by any chance i386 architecture? Linux db2 2.6.18.8-0.3-default #1 SMP Tue Apr 17 08:42:35 UTC 2007 x86_64 x86_64 x86_64 GNU/Linux Intel(R) Xeon(R) CPU 5130 @ 2.00GHz with 4 logical processors (2 physical) vm.overcommit_memory = 2 # No memory overcommit. Regards, Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] "out of memory" error
=?UTF-8?B?Q2hyaXN0aWFuIFNjaHLDtmRlcg==?= <[EMAIL PROTECTED]> writes: > hubert depesz lubaczewski wrote: >> On Wed, Aug 22, 2007 at 07:07:20PM +0200, Christian Schröder wrote: >>> These are the current settings from the server configuration: >>> shared_buffers = 3GB >> >> this is *way* to much. i would suggest lowering it to 1gig *at most*. >> > Ok, I can do this, but why can more memory be harmful? Because you've left no room for anything else? The kernel, the various other daemons, the Postgres code itself, and the local memory for each Postgres process all require more than zero space. Even more to the point, with such a large shared-buffer space, the kernel probably will be tempted to swap out whatever parts of it seem less used at the moment. That is far more harmful to performance than not having had the buffer in the first place --- it can easily triple the amount of disk I/O involved. (Thought experiment: dirty buffer is written to disk, versus dirty buffer is swapped out to disk, then later has to be swapped in so it can be written to wherever it should have gone.) Bottom line is that PG shared buffers are not so important as to deserve 3/4ths of your RAM. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
