[GENERAL] back references using regex
Hi. I'm trying to do a slice directly from a table so I can get a brief preview of the articles content by counting \s (spaces), not new paragraphs. Anyone know how it could be done using regular expressions natively? I read the doc but it didn't help me much. Many thanks. MP __ Click here to donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Shared disk storage
Jim C. Nasby wrote: > On Wed, Sep 07, 2005 at 12:19:19AM -0400, Tom Lane wrote: >> Having said that, I'm not sure I believe in filesystem locks as doing >> much to improve security in the case of multiple hosts attached to a SAN >> filesystem. Does the locking work at all across hosts, and if it does, >> does the lock get released reasonably promptly if the owning host >> crashes? This seems like a there's-no-free-lunch situation. > > The way I see it, it will work fine for some setups, and not work for > others. That means it won't help everyone, but it will help some. ISTM > like it would be pretty easy to do, so why not help those who could make > use of it? On SUSE Linux (Tested on 10.0beta but I am fairly sure my servers running SLES9 are the same) I have: # ls -l /var/lib/pgsql/data/postmaster.* -rw--- 1 postgres postgres 41 Sep 7 09:09 /var/lib/pgsql/data/postmaster.opts -rw--- 1 postgres postgres 45 Sep 7 09:09 /var/lib/pgsql/data/postmaster.pid Both of these files are updated when Postgres starts, with postmaster.pid being removed when it stops, but postmaster.opts stays. If I have a shared disk between 2 machines with Postgres running on them is this enough to protect my data or not? Has anyone ever done anything like this? TIA -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Shared disk storage
On Wed, Sep 07, 2005 at 12:19:19AM -0400, Tom Lane wrote: > Having said that, I'm not sure I believe in filesystem locks as doing > much to improve security in the case of multiple hosts attached to a SAN > filesystem. Does the locking work at all across hosts, and if it does, > does the lock get released reasonably promptly if the owning host > crashes? This seems like a there's-no-free-lunch situation. The way I see it, it will work fine for some setups, and not work for others. That means it won't help everyone, but it will help some. ISTM like it would be pretty easy to do, so why not help those who could make use of it? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Shared disk storage
On Wed, Sep 07, 2005 at 00:19:19 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > > Well, if you know any vendors who move postmaster.pid out of the PGDATA > directory, let us know so we can knock some sense into their heads. > postmaster.pid is specifically a lock on the directory, and moving it > seriously weakens the strength of the lock. I thought Fedora did, but there are actually two files, one in /var/run and another in /var/lib/pgsql/data . I had vaguely remembered some discussions about there being two files a while back, but looked in the wrong place for the second one and figured I had misremembered things. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] fix pg_autovacuum
On 9/6/05, Alvaro Herrera <[EMAIL PROTECTED]> wrote: On Tue, Sep 06, 2005 at 06:34:44AM -0500, Ben Grimm wrote:> Hi Alvaro,>> Is there any chance of backporting the integrated version to 8.0? We have> about 50,000 tables and the autovacuum daemon churns at 100% cpu for long > stretches... any improvement over that would be welcome.Nope, about zero. Your best bet seems to be to help on the beta testingso 8.1 comes out quickly. That's alright -- I'll definitely be testing 8.1 when time permits, but since we only just now upgraded to 8.0... I doubt that any further upgrades will be in my near future. But I was thinking about it this morning I ended up writing a replacement in perl. It follows essentially the same rules that pg_autovacuum does, but only one db at a time (since that meets my needs) and requires a table to snapshot stats for the incremental comparisons, and I used a view just to make the logic a bit simpler. It's been running for 12 hours and my database is just as vacuumed/analyzed as it ever was, and essentially zero load - so no complaints here. It's attached if you're interested -- I'm guessing that it works much the same way as what you've done for 8.1. -- Ben autovacuum Description: Binary data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Shared disk storage
Bruno Wolff III <[EMAIL PROTECTED]> writes: > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: >> Maybe it would be better to keep this in PGDATA (or even a duplicate >> copy). Holding a write lock on the file should also help ensure that you >> can tell if it's stale or not. > And the end user can do that if they know to look for it. What you get by > default is going to come from the distro vender and they may decide to > put it in /var/run no matter what the default is when you build from source. Well, if you know any vendors who move postmaster.pid out of the PGDATA directory, let us know so we can knock some sense into their heads. postmaster.pid is specifically a lock on the directory, and moving it seriously weakens the strength of the lock. Having said that, I'm not sure I believe in filesystem locks as doing much to improve security in the case of multiple hosts attached to a SAN filesystem. Does the locking work at all across hosts, and if it does, does the lock get released reasonably promptly if the owning host crashes? This seems like a there's-no-free-lunch situation. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to write jobs in postgresql
[EMAIL PROTECTED] (Roman Neuhauser) writes: > # [EMAIL PROTECTED] / 2005-08-15 20:25:20 -0500: >> On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote: >> > chiranjeevi.i wrote: >> > > Hi Team Members, >> > > >> > > Is it possible to write jobs in postgresql & if possible how >> > > should I write .please help me. >> > >> > See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/. It's in >> > the planning stages. >> >> Actually, it's currently in the going nowhere stage since no one's >> expressed any interest in it. Anyone who's interested is encouraged to >> join the mailing list and post what they'd like to see from the project. > > What's the advantage over system-native (cron etc) means? You could doubtless do things with "pgcron" you cannot readily do with cron, such as: 1. Expressing dependancies between jobs (e.g. - run A after running B, don't start C if B is still running) 2. More specific time increments than cron offers (e.g. - heading nearer to granularity of seconds) 3. An integrated way of logging what ran when (rather than either stuffing logging code into each cron job or rummaging thru cron logs) 4. You could use SQL queries to request information about jobs. -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://www.ntlug.org/~cbbrowne/sap.html "LISP car-and-cdr worlds are a more reasonable representation of the things that make life interesting than fixed decimal(15) or FILE OLDMSTR RECORD IS PAYROLL." -- Bernie Greenberg. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Shared disk storage
On Tue, Sep 06, 2005 at 17:01:41 -0500, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > Maybe it would be better to keep this in PGDATA (or even a duplicate > copy). Holding a write lock on the file should also help ensure that you > can tell if it's stale or not. And the end user can do that if they know to look for it. What you get by default is going to come from the distro vender and they may decide to put it in /var/run no matter what the default is when you build from source. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] fix pg_autovacuum
On Tue, Sep 06, 2005 at 06:34:44AM -0500, Ben Grimm wrote: > Hi Alvaro, > > Is there any chance of backporting the integrated version to 8.0? We have > about 50,000 tables and the autovacuum daemon churns at 100% cpu for long > stretches... any improvement over that would be welcome. Nope, about zero. Your best bet seems to be to help on the beta testing so 8.1 comes out quickly. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica" (Irulan) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Basic locking question
On Tue, Sep 06, 2005 at 04:25:38PM -0700, Ben wrote:
> So I'm looking for "lock in exclusive mode"?
What version of PostgreSQL are you using? In 8.0 and later a
PL/pgSQL function could trap a unique constraint violation and issue
a SELECT query instead. If that sounds ugly then I'd say locking
the entire table is even uglier.
Here's a possible solution (only minimally tested):
CREATE FUNCTION getkey(k text) RETURNS integer AS $$
DECLARE
retval integer;
BEGIN
LOOP
SELECT INTO retval id FROM foo WHERE keyval = k;
EXIT WHEN FOUND;
BEGIN
INSERT INTO foo (keyval) VALUES (k);
RETURN currval(pg_get_serial_sequence('foo', 'id'));
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
NULL;
END;
END LOOP;
RETURN retval;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;
This function should handle race conditions, and it should only
block when multiple transactions try to insert the same key. If
the key already exists then the expensive exception-handling code
won't be entered. Alternatively, you could try the INSERT first
and then do the SELECT if the INSERT failed.
--
Michael Fuhr
---(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] SLOOOOOOOW
On Tue, 2005-09-06 at 16:09, Jürgen Rose wrote: > Sorry, but I better use this email address, I just hate to use Outlook > for this stuff. > > To Peter Eisentraut > > Yes, I've read the chapter in the manual. > > To Michael Glaesemann > > locally I run the database on my laptop (Dell D800) 1 GB Ram, but there > within VMWARE with 512MB assigned RAM. But the target platform is a dual > processor machine with 2 GB. > > But, and thats the big but here, I don't care. For me a database has to > work satisfying in the first place without twiddeling some obscure knobs > or push levers to get just accaptable performance if I only have a small > set of data. Heck, I'm talking about maybe in the whole 45.000 > records!!! I mean I used Interbase, MySQL, SQLite, SQLServer before, and > for this project postgres was set, so I had to use it. Which is fine, I > wouldn't mind, if I would not have such troubles. A couple of points: 1: You wouldn't buy the QE II (a big luxery liner) and complain that it doesn't work well for water skiing and is too complex. It's the QE II. 2: You've given us absolutely nothing we can go on to help you make postgresql work better for you. Nothing. Just one explain analyze output. 3: If you're running one OS on top of another, and then a database on top of that, and all you've got is 512 Meg of RAM, don't expect stellar performance, especially from a database that uses shared memory like postgresql does. 4: Don't compare PostgreSQL to those other databases unless you're going to give it a chance. So far, you haven't done so, you've only complained. 5: I have tested a properly tuned PostgreSQL server that was on about 1/2 the hardware (CPU speed, memory, RAID array) as a MSSQL server and easily out ran it. But, I took my time, read the docs, and tuned the server OS and PostgreSQL 6: Databases may appear simple, they are not, and the more complex they are, the more you'll have to do to make full use of them. So, have you been running vacuum and analyze, do you have the right indexes, are you using queries that can use those indexes, have you turned up sort_mem and a few other easily tweakable settings. PostgreSQL's use of shared memory, combined with many older Operating systems have VERY conservative settings for such, combined further with the need for PostgreSQL to run on dang near anything, mean that, often, out of the box, it's not as fast as some other servers. OTOH, it coexists well with other software. If you've ever tried to build a MSSQL or ORacle box that did anything else, you know how those two database engines just consume memory and CPU without really asking. Here's an explain analyze on one of the production pgsql servers I work on: explain analyze select count(*) from sometable QUERY PLAN - Aggregate (cost=6209.99..6209.99 rows=1 width=0) (actual time=339.200..339.201 rows=1 loops=1) -> Seq Scan on sometable (cost=0.00..5856.19 rows=141519 width=0) (actual time=0.025..202.636 rows=162427 loops=1) Total runtime: 339.262 ms (3 rows) Admitted, the rows aren't that big, but that's a seq scan of 160,000 rows. Not bad really. And it's quite fast at our more esoteric reporting generation queries as well. Now, we can sit here and argue about how nice it would be if PostgreSQL just configured itself for maximum performance on installation, or you can tell us what runs slow, and let us help you fix it. The ball is in your court. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Basic locking question
Right, I understand the badness of the situation, but unfortunately the keys are externally generated and I have no control over them. So I'm looking for "lock in exclusive mode"? Tom Lane wrote: Pretty much the only thing you can do about that is to take an exclusive table-level lock. This is simple but pretty awful for concurrency (since the lock blocks everyone else from inserting ANY key, not only the one you are inserting). Consider using a sequence object to generate the keys, instead. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Basic locking question
Ben <[EMAIL PROTECTED]> writes: > Doesn't select ... for update only lock the rows returned in the select? Right. > In my case, I'm worried about situations when no rows will be returned > and two separate transactions will try to insert the same key. Pretty much the only thing you can do about that is to take an exclusive table-level lock. This is simple but pretty awful for concurrency (since the lock blocks everyone else from inserting ANY key, not only the one you are inserting). Consider using a sequence object to generate the keys, instead. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Basic locking question
Doesn't select ... for update only lock the rows returned in the select? In my case, I'm worried about situations when no rows will be returned and two separate transactions will try to insert the same key. Joshua D. Drake wrote: Ben wrote: I'm not very familiar with explicitly locking things in SQL, so this may be a dumb question, but. I've got a stored proc that takes a text key and returns an ID. If the key isn't in a lookup table, it adds it and will return the auto-generated serial number. If it's already there, it simply returns the existing serial number. I'm concerned about multiple sessions calling this at the same time with the same key. While I *could* just put in a unique constraint and let things fail, that's hardly graceful. It seems I should be able to lock the lookup table, but I don't really know what mode to lock that table in. Will ROW EXCLUSIVE keep the same key from being entered twice? You are probably looking for select for update: http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to write jobs in postgresql
On Tue, Sep 06, 2005 at 04:29:31PM -0500, Guy Rouillier wrote: > Roman Neuhauser wrote: > > # [EMAIL PROTECTED] / 2005-08-15 20:25:20 -0500: > >> On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote: > >>> chiranjeevi.i wrote: > Hi Team Members, > > Is it possible to write jobs in postgresql & if possible how > should I write .please help me. > >>> > >>> See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/. It's > >>> in the planning stages. > >> > >> Actually, it's currently in the going nowhere stage since no one's > >> expressed any interest in it. Anyone who's interested is encouraged > >> to join the mailing list and post what they'd like to see from the > >> project. > > > > What's the advantage over system-native (cron etc) means? > > Search the archives, you'll find numerous discussions on this topic, > including the one that prompted Jim to create the project. As of now, > the project is pre-concept stage, making it impossible to identify its > advantages. One possible advantage would be recording job schedules in > the database where they can be easily managed, but that's small. A > bigger advantage can be seen in the approach that Oracle takes, where > authentication happens when the job is created. So you don't need to > provide credentials at run time, which in the case of cron jobs would > mean putting passwords into shell scripts. As Guy points out, this is all in a very formative stage right now (although someone is supposed to be sending me some code), but here's some other advantages: This would be platform-independant, which is important now that we support windows natively. The interface would be in SQL (probably a set of functions), making it much easier to control programatically. Scheduling modes that are either difficult or impossible to do with cron become available, such as sub-minute scheduling (ie: every 30 seconds), running something at server start-up/shut-down, running something based on a notify, etc. I encourage anyone who's interested in this to join the mailing list at http://lists.pgfoundry.org/mailman/listinfo/pgjob-devel -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Help with row locks on 7.4 to 8.0 migration
Kevin Barnard <[EMAIL PROTECTED]> writes: > FATAL: terminating connection due to administrator command > CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."client" x WHERE > "division" = $1 FOR UPDATE OF x" > That's what happens when I kill the SQL that was locking everything. The > only problem is that's not the SQL statement that was being run. It's a foreign-key trigger, and your problem is probably a quasi deadlock on a foreign key's master row --- someone who has the row locked is waiting for someone else who is just sitting on an open transaction, and then everyone else trying to insert the same FK value stacks up behind that lock. I'm not sure why you are seeing this more in 8.0 than 7.4, though possibly the different timing for firing AFTER triggers has something to do with it. Do you use stored procedures to do your updates? If so, 8.0 will fire the FK triggers sooner (within the procedure rather than after it exits), thus holding the locks longer, thus possibly causing the problem. Perhaps making the problematic FKs DEFERRED would help. FWIW, 8.1 should be a lot better on this because it will use shared row locks for foreign key references. 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] Shared disk storage
On Tue, Sep 06, 2005 at 02:58:52PM -0500, Bruno Wolff III wrote: > On Tue, Sep 06, 2005 at 13:47:42 -0500, > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > > > On Mon, Sep 05, 2005 at 12:20:24 +0300, > > > Peter Nixon <[EMAIL PROTECTED]> wrote: > > > > Hi List > > > > > > > > Does anyone have any comments, HOWTOs and experience running multiple > > > > Postgres servers with a shared disk (SAN) in a Hot standby > > > > configuration? > > > > > > > > Can someone please point me in the direction of any docs on this > > > > subject? > > > > > > Be sure to have some failsafe to prevent two servers from running at the > > > same time on the same data. If that ever happens your database will be > > > hosed. > > > > I thought PostgreSQL already had such a safeguard? Or is it only against > > starting two backends against the same PGDATA on the same machine? > > Yes, but it is more likely to have problems when there are two machines > involved. One is that the file may not be on the cross mounted file > system (on FC4 it is in /var/run) and even if it is on the cross mounted > file system, there is a good chance the lock file will appear to be stale > because the process id is for the other machine. I am not sure if there are > other gotchas, but you definitely want to be careful, since a mistake is > going to defeat the purpose of having the hot spares. Maybe it would be better to keep this in PGDATA (or even a duplicate copy). Holding a write lock on the file should also help ensure that you can tell if it's stale or not. I realize this probably still isn't perfect, but it's probably better than forcing users to find an external means of locking out the other backend. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SLOOOOOOOW
=?ISO-8859-1?Q?J=FCrgen_Rose?= <[EMAIL PROTECTED]> writes: > enough of ranting, but I'm totally frustrated So are we, because you haven't provided nearly enough detail to let anyone help you. A complete test case would be good, for instance. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Basic locking question
Ben wrote: I'm not very familiar with explicitly locking things in SQL, so this may be a dumb question, but. I've got a stored proc that takes a text key and returns an ID. If the key isn't in a lookup table, it adds it and will return the auto-generated serial number. If it's already there, it simply returns the existing serial number. I'm concerned about multiple sessions calling this at the same time with the same key. While I *could* just put in a unique constraint and let things fail, that's hardly graceful. It seems I should be able to lock the lookup table, but I don't really know what mode to lock that table in. Will ROW EXCLUSIVE keep the same key from being entered twice? You are probably looking for select for update: http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SLOOOOOOOW
On 9/6/05, Jürgen Rose <[EMAIL PROTECTED]> wrote: > I did some serious stuff with SQLServer and Interbase, and I had > **never** those performance problems. > On a laptop? Under VMWare? I have used MSSQL Server too, and find PostgreSQL to compare favorably in most cases. You may have found a case where it does not. I have found PostgreSQL to work fine out of the box in most cases, with the main 'obscure knob' that needs to be twiddled being sort_mem (or work_mem for 8.0 and up.) If you give specifics of your database structure and migration functions I am sure someone on the list will be able to help you get reasonable performance. > enough of ranting, but I'm totally frustrated It will work out. - Ian > with best regards > Jürgen > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Help with row locks on 7.4 to 8.0 migration
I've just upgraded a 7.4 install to 8.0.3 and we've suddenly run into lock issues that were not present in 7.4. I'm look for help on this matter, because I'm a little confused. Downgrading is really not an option at this point, we really really need the 8.0 features on the DB and a dump/restore takes way to long. Here is what happens I get a call from are call agents saying the system is slow. Almost all of there queries are WAITING for a lock. So I query pg_locks for not granted locks. Everything is waiting on a single transaction. OK fine I look at the transaction in question and look at it's query using pg_stat_activity. I reconize the query, kill the process and boom everything is back to life. OK this is great because I know this is the problem. Simple enough but the error message I get is a tad bit confusing. FATAL: terminating connection due to administrator commandCONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."client" x WHERE "division" = $1 FOR UPDATE OF x" That's what happens when I kill the SQL that was locking everything. The only problem is that's not the SQL statement that was being run. OK this is probably a trigger or something is my next though. I've searched through the entire schema and don't find this query anywhere or anything that resembles it. I've got to rewrite these query or what ever requires this query. Locking the client table is bad because just about every query needs to reference this table. The statement that is holding everything up is DELETE FROM ONLY demand_sum; INSERT INTO demand_sum (field1...; This is a simple materialized view that is updated every 5 minutes. Is the SELECT 1 statement a rewrite of something or is this a statement that the DELETE statement is waiting for (that wouldn't make sense because the delete has the lock granted)? I am sure that I'm being an absolute idiot over this and missing the obvious. Please copy me on this. I've suppresed getting emails from the list. (I check google groups, but a copy is a tad bit faster) Thank you-- Kevin Barnard"Great Beauty, great strength, and great Riches, are really and truly of no great Use; a right Heart exceeds all." -- Benjamin Franklin
Re: [GENERAL] How to write jobs in postgresql
Roman Neuhauser wrote: > # [EMAIL PROTECTED] / 2005-08-15 20:25:20 -0500: >> On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote: >>> chiranjeevi.i wrote: Hi Team Members, Is it possible to write jobs in postgresql & if possible how should I write .please help me. >>> >>> See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/. It's >>> in the planning stages. >> >> Actually, it's currently in the going nowhere stage since no one's >> expressed any interest in it. Anyone who's interested is encouraged >> to join the mailing list and post what they'd like to see from the >> project. > > What's the advantage over system-native (cron etc) means? Search the archives, you'll find numerous discussions on this topic, including the one that prompted Jim to create the project. As of now, the project is pre-concept stage, making it impossible to identify its advantages. One possible advantage would be recording job schedules in the database where they can be easily managed, but that's small. A bigger advantage can be seen in the approach that Oracle takes, where authentication happens when the job is created. So you don't need to provide credentials at run time, which in the case of cron jobs would mean putting passwords into shell scripts. -- Guy Rouillier ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] SLOOOOOOOW
Sorry, but I better use this email address, I just hate to use Outlook for this stuff. To Peter Eisentraut Yes, I've read the chapter in the manual. To Michael Glaesemann locally I run the database on my laptop (Dell D800) 1 GB Ram, but there within VMWARE with 512MB assigned RAM. But the target platform is a dual processor machine with 2 GB. But, and thats the big but here, I don't care. For me a database has to work satisfying in the first place without twiddeling some obscure knobs or push levers to get just accaptable performance if I only have a small set of data. Heck, I'm talking about maybe in the whole 45.000 records!!! I mean I used Interbase, MySQL, SQLite, SQLServer before, and for this project postgres was set, so I had to use it. Which is fine, I wouldn't mind, if I would not have such troubles. Which I'm working on is just a redesign of a database which has some hysterically grown tables. Not much in it, but there are some tables which should be merged together and some others have to split up. No big deal. So my basic idea was to use the flexibility of rules to provide a transparent interface to the frontend, which has the big advantage of not having to change the frontend in most places at all. We have a bit of a homegrown framework (PHP) to show and manipulate the data on the frontend side. Unfortunately it is only easy if you access 1 table, and don't have to update several tables. So my idea was to use the rule system as well to put the data into the database and distribute it on the underlying tables. To have views which separate the physical model from the logical model. This is best practice isn't it? Unfortunately it seems no way to create triggers on views, which is what I need. Some insert rules are not enough, because I'm using data which is just created, so this is not an option. Ok as a workaround I create a table which is just there to have a insert trigger on it to distribute the data on the tables. For selecting, updating, and deleting the rules are sufficient. So I actually merging some tables with appr. 8000 + 14,000 + 30,000 records in it, so we talking about a small database. The performance of selecting data from the views is slow, I mean there are only around 5 records in there in the whole. It can take up to several seconds to get the data from the views, which is just not fast enough. The update is even slower, for just updating 1 record it takes ages. The actual migration process, of moving the old data to the new tables is just agonizing slow. To move tha data from the small table (8000 entries) it takes somewhere (not deterministic) between a few minutes and 40 minutes to move it. Essentually it is just a select from one table to the compatibility view of the new table. For me it seems that each additional row makes the database slower. It occured to me that either table (8000 or 14000 entries) is faster migrated if it happens to be the first of both. Then migrating the 3 entries (and it has to be the last one) takes **hours**!!! The migration of the tables itself consists of two parts, first move the data from the table, than update all the linked tables (I had to remove the joins, they have to point afterwards to the new tables), at this point I'm using a lot of subselects (which are slow but there is no other way). And I actually vacuum and analyze the database after each step, all usefull indices are set and also used. I did some serious stuff with SQLServer and Interbase, and I had **never** those performance problems. enough of ranting, but I'm totally frustrated with best regards Jürgen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Basic locking question
I'm not very familiar with explicitly locking things in SQL, so this may be a dumb question, but. I've got a stored proc that takes a text key and returns an ID. If the key isn't in a lookup table, it adds it and will return the auto-generated serial number. If it's already there, it simply returns the existing serial number. I'm concerned about multiple sessions calling this at the same time with the same key. While I *could* just put in a unique constraint and let things fail, that's hardly graceful. It seems I should be able to lock the lookup table, but I don't really know what mode to lock that table in. Will ROW EXCLUSIVE keep the same key from being entered twice? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to write jobs in postgresql
# [EMAIL PROTECTED] / 2005-08-15 20:25:20 -0500: > On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote: > > chiranjeevi.i wrote: > > > Hi Team Members, > > > > > > Is it possible to write jobs in postgresql & if possible how > > > should I write .please help me. > > > > See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/. It's in > > the planning stages. > > Actually, it's currently in the going nowhere stage since no one's > expressed any interest in it. Anyone who's interested is encouraged to > join the mailing list and post what they'd like to see from the project. What's the advantage over system-native (cron etc) means? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(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] "select ..... for update of ..." doesn't support full qualified table name?
Vlad <[EMAIL PROTECTED]> writes: > Though question is - doesn't it seem logical to be able to use full > table names in FOR UPDATE part like I can use them in WHERE part (if I > don't need/want to use an alias)? Is it something postgresql speciffic > or it's SQL standard (pardon my ignorance)? The entire construct is Postgres-specific, so you can't really point to the spec and say it's wrong. I don't see any merit whatever in the "I shouldn't need to use an alias" argument. If you don't have unique aliases then you're going to have problems anyway. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] "select ..... for update of ..." doesn't support full qualified table name?
Tom, yes, this part is not well documented - specially double checked before sendin email to the list. Though question is - doesn't it seem logical to be able to use full table names in FOR UPDATE part like I can use them in WHERE part (if I don't need/want to use an alias)? Is it something postgresql speciffic or it's SQL standard (pardon my ignorance)? > > yes, we actually use table alias as a workaround, I thought that it's > > actually looks like error in postgresql parser (or deeper) that needs > > to be reported. > > No, it's supposed to be that way: FOR UPDATE items are table aliases. > Perhaps this isn't adequately documented... -- Vlad ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Bug or ?
Keary Suska <[EMAIL PROTECTED]> writes: > on 9/5/05 6:51 PM, [EMAIL PROTECTED] purportedly said: >> In any case, AFAICS this would only be an issue if you do >> "su postgres -c postmaster" rather than "su -l postgres -c postmaster" >> and the former is to be avoided for many reasons besides this one. > I guess this becomes a bug report for the Darwin startup script in contrib, > which is broken not only because it does what you recommend against (above), > but it is also broken in other ways. How does one go about reporting this? I > am not sure who is responsible for contrib stuff. Whoever's using it ;-). Feel free to send a patch to pgsql-patches. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] "select ..... for update of ..." doesn't support full qualified table name?
Vlad <[EMAIL PROTECTED]> writes: > yes, we actually use table alias as a workaround, I thought that it's > actually looks like error in postgresql parser (or deeper) that needs > to be reported. No, it's supposed to be that way: FOR UPDATE items are table aliases. Perhaps this isn't adequately documented... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Shared disk storage
On Tue, Sep 06, 2005 at 13:47:42 -0500, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > > On Mon, Sep 05, 2005 at 12:20:24 +0300, > > Peter Nixon <[EMAIL PROTECTED]> wrote: > > > Hi List > > > > > > Does anyone have any comments, HOWTOs and experience running multiple > > > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > > > > > Can someone please point me in the direction of any docs on this subject? > > > > Be sure to have some failsafe to prevent two servers from running at the > > same time on the same data. If that ever happens your database will be > > hosed. > > I thought PostgreSQL already had such a safeguard? Or is it only against > starting two backends against the same PGDATA on the same machine? Yes, but it is more likely to have problems when there are two machines involved. One is that the file may not be on the cross mounted file system (on FC4 it is in /var/run) and even if it is on the cross mounted file system, there is a good chance the lock file will appear to be stale because the process id is for the other machine. I am not sure if there are other gotchas, but you definitely want to be careful, since a mistake is going to defeat the purpose of having the hot spares. ---(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] Shared disk storage
On Tue, Sep 06, 2005 at 01:47:42PM -0500, Jim C. Nasby wrote: > On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > > On Mon, Sep 05, 2005 at 12:20:24 +0300, > > Peter Nixon <[EMAIL PROTECTED]> wrote: > > > Hi List > > > > > > Does anyone have any comments, HOWTOs and experience running multiple > > > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > > > > > Can someone please point me in the direction of any docs on this subject? > > > > Be sure to have some failsafe to prevent two servers from running at the > > same time on the same data. If that ever happens your database will be > > hosed. > > I thought PostgreSQL already had such a safeguard? Or is it only against > starting two backends against the same PGDATA on the same machine? Only in the same machine. I've seen people report corruption because the failover kicked in when it wasn't actually needed. He had to restore from backup AFAIR ... -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "Porque francamente, si para saber manejarse a uno mismo hubiera que rendir examen... ¿Quién es el machito que tendría carnet?" (Mafalda) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Setting up a database for 10000 concurrent users
10GB of RAM isn't that farfetched nowadays. However I/O might be a problem. A single drive can typically write/read about 10MB a second (64KB chunks random access - not sure if you'd want to bet on getting sequential throughput ;) ). Anyway, it'll be something interesting to see ;). Link. The database server is started now with max_connections = 1 and 100MB RAM is used /dev/sda: Timing buffered disk reads: 162 MB in 3.04 seconds = 53.32 MB/sec It is not that bad :) Yes I know there should be more disk arms. Now I'm curious to see how the many threads will be handled in Java ... Poul ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL and XML support
I have XML support in PostgreSQL in my Todo http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo Hopefully, we'll have something for 8.2 Oleg On Tue, 6 Sep 2005, Doug Bloebaum wrote: On 9/6/05, Andrey Fomichev <[EMAIL PROTECTED]> wrote: - Are there any of you who need to store and query XML data? - Do you already use PostgreSQL to store XML data or you are just thinking about it? The project I'm currently working on uses XML for both data extraction from Postgres and as a means to transmit data to Postgres for storage. - If you store XML data in PostgreSQL, how do you do it? What tool do you use? We're using Oracle's XSQL servlet ( http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10794/adx09xsq.htm) with Apache tomcat (http://jakarta.apache.org/tomcat/) as its servlet container. The only change we've made to XSQL is the addition of a custom tag () in order to make use of Postgres functions returning REF CURSOR. Aside from that, we're using XSQL out-of-the-box. Granted, we're not really manipulating XML within the database, rather we're using XML as a sort of database-neutral interface. - Do you need some advanced query capabilities? Like XQuery, XPath 2.0 No. - Do you need some advanced update capabilities? Like node-level updates No. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)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
[GENERAL] GUID / UUID RFC
For those who were debating the GUID issue on here a while back, I thought I'd point out: http://www.ietf.org/rfc/rfc4122.txt ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Debug plpgSQL stored procedures
On Mon, Sep 05, 2005 at 05:17:41PM -0400, Daniel Morgan wrote: > Richard Huxton wrote: > > >Daniel Morgan wrote: > > > >>Does PostgreSQL provide a way to step-debug into plpgSQL stored > >>procedures? > > > It is a shame. PostgreSQL is really rocking these days. Especially > with 8.0 on Windows. I was really impressed how far it has come since > the 6.x days running on Cygwin. 3rd hit on google: http://www.sqlmanager.net/products/postgresql/manager/documentation/hs20160.html -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL and XML support
On 9/6/05, Andrey Fomichev <[EMAIL PROTECTED]> wrote: - Are there any of you who need to store and query XML data?- Do you already use PostgreSQL to store XML data or you are just thinking about it? The project I'm currently working on uses XML for both data extraction from Postgres and as a means to transmit data to Postgres for storage. - If you store XML data in PostgreSQL, how do you do it? What tool do you use? We're using Oracle's XSQL servlet (http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10794/adx09xsq.htm) with Apache tomcat (http://jakarta.apache.org/tomcat/) as its servlet container. The only change we've made to XSQL is the addition of a custom tag () in order to make use of Postgres functions returning REF CURSOR. Aside from that, we're using XSQL out-of-the-box. Granted, we're not really manipulating XML within the database, rather we're using XML as a sort of database-neutral interface. - Do you need some advanced query capabilities? Like XQuery, XPath 2.0 No. - Do you need some advanced update capabilities? Like node-level updates No.
Re: [GENERAL] Bug or ?
on 9/5/05 6:51 PM, [EMAIL PROTECTED] purportedly said: >> FYI, it appears that you cannot start the postmaster when your current >> working directory is not accessible by the postgres user: > > Is there a good reason that this case should work? I don't think it's > very easy to fix given that we have to resolve the actual location > of the postmaster executable before we chdir to the data directory. I'll have to take your word for it as I don't know enough to discuss why/why not. It's just that this is the first time I have come across an executable with this particular "restriction". > In any case, AFAICS this would only be an issue if you do > "su postgres -c postmaster" rather than "su -l postgres -c postmaster" > and the former is to be avoided for many reasons besides this one. I guess this becomes a bug report for the Darwin startup script in contrib, which is broken not only because it does what you recommend against (above), but it is also broken in other ways. How does one go about reporting this? I am not sure who is responsible for contrib stuff. Thanks, Keary Suska Esoteritech, Inc. "Demystifying technology for your home or business" ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Shared disk storage
On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > On Mon, Sep 05, 2005 at 12:20:24 +0300, > Peter Nixon <[EMAIL PROTECTED]> wrote: > > Hi List > > > > Does anyone have any comments, HOWTOs and experience running multiple > > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > > > Can someone please point me in the direction of any docs on this subject? > > Be sure to have some failsafe to prevent two servers from running at the > same time on the same data. If that ever happens your database will be hosed. I thought PostgreSQL already had such a safeguard? Or is it only against starting two backends against the same PGDATA on the same machine? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Insert Ignore or something similar...
On Tue, Sep 06, 2005 at 08:55:26AM -0600, Cristian Prieto wrote: > Thanks a lot! > > Well, I just want to avoid a begin...exception when... end block in > plpgsql, just do it in a few lines of code without a sp... SAVEPOINT foo; INSERT ... ; if it fails ROLLBACK TO foo; else RELEASE foo This is pretty much the same that plpgsql begin/exception/end does. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "La conclusión que podemos sacar de esos estudios es que no podemos sacar ninguna conclusión de ellos" (Tanenbaum) ---(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] "select ..... for update of ..." doesn't support
On Tue, 2005-09-06 at 13:45 -0400, Vlad wrote: > SELECT * FROM one.aa, two.bb WHERE one.aa.a = two.bb.b FOR UPDATE OF > one.aa; > > ERROR: syntax error at or near "." at character 73 (points to the > last instance of "one.aa" in SQL query Try using a table alias, and reference that alias in the "for update of" clause. ---(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] tsearch2 & unicode
Michal,
tsearch2 doesn't supports UTF-8. It might works though.
full support of UTF-8 and other features are planned
http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo
Oleg
On Tue, 6 Sep 2005, Michal Hlavac wrote:
hello,
I have suse 9.3 with installed postgresql 8.0.3 from rpm.
All my databases are unicode with locale sk_SK.UTF-8.
I installed slovak ispell dictionary and tsearch2 is working very well.
m13=# SELECT lexize('sk_ispell','Ivana');
lexize
--
{ivana,ivan}
(1 row)
On the other side I have FreeBSD also with postgresql 8.0.3 and with
unicode database. I also installed slovak ispell dictionary and tsearch2
is working very well.
but result:
hlk=# SELECT lexize('sk_ispell','Ivana');
ERROR: Regex error in 'ia[tЪЪina$': brackets [] not balanced
where is the problem??? thanks, miso
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] "select ..... for update of ..." doesn't support full qualified table name?
yes, we actually use table alias as a workaround, I thought that it's actually looks like error in postgresql parser (or deeper) that needs to be reported. thanks. On 9/6/05, Matt Miller <[EMAIL PROTECTED]> wrote: > On Tue, 2005-09-06 at 13:45 -0400, Vlad wrote: > > SELECT * FROM one.aa, two.bb WHERE one.aa.a = two.bb.b FOR UPDATE OF > > one.aa; > > > > ERROR: syntax error at or near "." at character 73 (points to the > > last instance of "one.aa" in SQL query > > Try using a table alias, and reference that alias in the "for update of" > clause. > -- Vlad ---(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] tsearch2 & unicode
Oleg Bartunov wrote: > Michal, > > tsearch2 doesn't supports UTF-8. It might works though. > full support of UTF-8 and other features are planned > http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo hello Oleg, thanks for reply... is there any time plan for new release of tsearch2? miso ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] "select ..... for update of ..." doesn't support full qualified table name?
CREATE SCHEMA one; CREATE TABLE one.aa ( a INT ); CREATE SCHEMA two; CREATE TABLE two.bb ( b INT ); SELECT * FROM one.aa, two.bb WHERE one.aa.a = two.bb.b FOR UPDATE OF one.aa; ERROR: syntax error at or near "." at character 73 (points to the last instance of "one.aa" in SQL query) p.s. in our application we actually have the same table names but in different schemas, so avoiding using of schema name in table reference is not possible, so actual select looks like this: CREATE TABLE one.t ( a INT ); CREATE TABLE two.t ( b INT ); SELECT * FROM one.t, two.t WHERE one.t.a = two.t.b FOR UPDATE OF one.t; -- Vlad ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Setting up a database for 10000 concurrent users
At 09:45 PM 9/5/2005 +0100, Richard Huxton wrote: Poul Møller Hansen wrote: I'm trying to setup a database for 1 concurrent users for a test. I have a system with 1GB of RAM where I will use 512MB for PostgreSQL. It is running SuSE 9.3 I think you're being horribly optimistic if you actually want 1 concurrent connections, with users all doing things. Even if you only allow 1MB for each connection that's 10GB of RAM you'd want. Plus a big chunk more to actually cache your database files and do work in. Then, if you had 10,000 concurrent queries you'd probably want a mainframe to handle all the concurrency, or perhaps a 64-CPU box would suffice... 10GB of RAM isn't that farfetched nowadays. However I/O might be a problem. A single drive can typically write/read about 10MB a second (64KB chunks random access - not sure if you'd want to bet on getting sequential throughput ;) ). Anyway, it'll be something interesting to see ;). Link. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] tsearch2 & unicode
hello,
I have suse 9.3 with installed postgresql 8.0.3 from rpm.
All my databases are unicode with locale sk_SK.UTF-8.
I installed slovak ispell dictionary and tsearch2 is working very well.
m13=# SELECT lexize('sk_ispell','Ivana');
lexize
--
{ivana,ivan}
(1 row)
On the other side I have FreeBSD also with postgresql 8.0.3 and with
unicode database. I also installed slovak ispell dictionary and tsearch2
is working very well.
but result:
hlk=# SELECT lexize('sk_ispell','Ivana');
ERROR: Regex error in 'ia[t�ina$': brackets [] not balanced
where is the problem??? thanks, miso
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] long transactions, SAVEPOINTS, performance and memory consumption
Frank Kardel <[EMAIL PROTECTED]> writes: > As we need to recover from failed statements on statement level we > encapsulate > our statements with SAVEPOINT/RELEASE savepoint statements. How are you doing that exactly? The style begin; savepoint x; ... release x; savepoint x; ... release x; savepoint x; ... release x; savepoint x; ... release x; ... commit; works with minimal leakage AFAICT. If you are nesting the savepoints then of course it's going to bloat: it has to be able to recover to any one of the open savepoints, so there has to be some amount of state associated with each one. If you think that's what you are doing, let's see a self-contained example. You might be invoking some specific feature that has a memory leak. It's also a good idea to mention the exact PG version you are using in such complaints ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Shared disk storage
On Mon, Sep 05, 2005 at 12:20:24 +0300, Peter Nixon <[EMAIL PROTECTED]> wrote: > Hi List > > Does anyone have any comments, HOWTOs and experience running multiple > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > Can someone please point me in the direction of any docs on this subject? Be sure to have some failsafe to prevent two servers from running at the same time on the same data. If that ever happens your database will be hosed. ---(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] Insert Ignore or something similar...
Thanks a lot! Well, I just want to avoid a begin...exception when... end block in plpgsql, just do it in a few lines of code without a sp... Thanks a lot again! - Original Message - From: "Thomas F. O'Connell" <[EMAIL PROTECTED]> To: "Alvaro Herrera" <[EMAIL PROTECTED]> Cc: "Cristian Prieto" <[EMAIL PROTECTED]>; Sent: Monday, September 05, 2005 10:01 PM Subject: Re: [GENERAL] Insert Ignore or something similar... On Sep 5, 2005, at 10:51 PM, Alvaro Herrera wrote: On Mon, Sep 05, 2005 at 10:35:49PM -0500, Thomas F. O'Connell wrote: I don't think any such behavior exists in PostgreSQL, and based on a reading of the behavior in MySQL, I can't imagine it ever existing considering the preference of PostgreSQL developers for correct (and sane) behavior. INSERT IGNORE seems like a foot-cannon... What is it supposed to do? If ignoring errors is the behavior Cristian wants, it's possible to do with savepoints (or PL/pgSQL exceptions). Yeah, I think savepoints might be his best bet. It sounded like he was explicitly trying to avoid PL/pgSQL. Cristian, here's a pointer to the SAVEPOINT docs: http://www.postgresql.org/docs/8.0/static/sql-savepoint.html I was mostly remarking that I couldn't envision a flipped switch for incorrect/non-standard INSERT behavior. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL and XML support
Hello! I would like to raise a discussion about XML support in PostgreSQL. I'm a person who is quite experienced in XML data management and new to PostgreSQL. So, excuse me if I ask trivial questions about PostgreSQL... I tried to find something about support for XML in PostrgreSQL. As far as I understand, PosgreSQL does not have native support for XML. But I've found several works around. 1. XML databases build on top of PostgreSQL. They are XpSQL and XDB (sorry, if I missed something). Are they alive and functionable? 2. Contribution made by John Gray (xml and xml2). This is a tool that uses 'shredding' for storing XML in relational tables (another words, it decomposes XML document into nodes and places these nodes into tables in such a way that we can reconstruct this document back). What do you think about this tool? In general, I have the following questions to PostgreSQL users. - Are there any of you who need to store and query XML data? - Do you already use PostgreSQL to store XML data or you are just thinking about it? - If you store XML data in PostgreSQL, how do you do it? What tool do you use? - Do you need some advanced query capabilities? Like XQuery, XPath 2.0 - Do you need some advanced update capabilities? Like node-level updates And I have the following question to PostgreSQL developers. Do you have any plans for native support for XML in PostgreSQL? By native support I understand persistent data structured specially developed for storing XML on disk and query/update it efficiently. As far as I know, native support for XML is a way other database vendors (Oracle, Microsoft, IBM) go. Best regards, Andrey ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installation problem
am 06.09.2005, um 20:13:57 +1000 mailte Peter Cook folgendes: > My installation has stalled with the following message: > "User 'postgres' could not be created. The user account already exists!" Which OS? Maybe it is useful to delete this account first. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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] Installation problem
My installation has stalled with the following message:"User 'postgres' could not be created. The user account already exists!" Initially I had problems providing a suitable domain name and password. So, I have made several attempts at installation. How do I now proceed to install successfully? I used postgreSQL during a recent contract position, and I am impressed by its capability and the maturity of the product. I hope that I can resume my expereince soon.
[GENERAL] long transactions, SAVEPOINTS, performance and memory consumption
Hi *, for our project we use big transactions for application coordination. As we need to recover from failed statements on statement level we encapsulate our statements with SAVEPOINT/RELEASE savepoint statements. So far this seems to be the only way to recover from failed statements and to keep the enclosing transaction intact. Things we observed: 1) For transactions with many statements we observe continuously increasing memory consumption in the backend. 2) Along with it the throughput decreases during the course of statement processing. The processing is CPU bound during that time until the transaction finishes or rather aborts. 3) We seldom see the entire transaction commit as it is ABORTED due to an out of memory condition before the application can commit. 4) The backend server log shows MANY (in our case 15805) entries of the form: CurTransactionContext: 8192 total in 1 blocks; 8176 free (2 chunks); 16 used This is a resource utilization of 0.2%. Of the 128Mb allocated data size the the backend process 96% is used for an effective data amount of 252880 bytes. 5) Looking into the source READMEs (/backend/utils/mmgr/README, backend/access/transam/README) it looks like it is intentional that the backend allocates and keeps memory for each started and commited sub-transaction until the transaction is finally commited at top level. Questions: 1) Is there another way to cope with failed statements without using savepoint and without aborting the entire transaction? 2) Would it be possible to let the parent subtransaction adopt the state that is currently being saved in the memory allocated for the commited child subtransaction? If so, it would dramatically enhance resource efficiency and possibly improve performance by reducing adminitrative overhead. Regards and thanks in advance, Frank Kardel & Ansgar Seiter ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Query questions
Look into inheritance. It makes this easier. However, I don't care which RDBMS you use, management of 1000 identical tables is going to be a real pain and I think that everyone here will probably suggest that it is not exactly a sane thing to do. Thank you, Chris. I have omitted two important points. One is: The database needs no maintenance. Once created nothing in it will be modified - there'll only be SELECT queries to look up data satisfying certain criteria. To summarize the task: I have ~500,000 data files containing ~1,000 records each. The database should then contain: 1) A detailed table (~1,000 rows, ~15 columns) for each file 2) A small table with file summary (1 row, ~30 columns) for each file The typical query should then check file summaries to identify which detailed tables/subtables to check for further conditions. The other important point I forgot about (much worse): The detailed table for each file is created with a number of columns which for most files have the same value for all records. I had planned to delete columns containing only a single value and push it to the file summary instead. Hence, the 500,000 detailed tables wouldn't all be identical. The initial check on the file summary would determine whether the detailed table for a given file should be searched and, if so, what columns are found in it. I guess I could either: 1) Add a lot of redundant data to the database so tables can be combined, allowing single query searches. Judging from the size of my test database I would end up with ~200 GB without redundant data, so I do consider this a problem. 2) Write code (e.g. in C) with a loop to do separate queries for every file - I imagine this would be terribly inefficient compared to the single query option. Question: If I create a table with all the columns needed to combine the 500,000 tables and I simply omit the redundant columns when entering the subtable for a given file, will PostgreSQL fill in values, not allowing me to save any space? Would I have to make an inheritance hierarchy to save the space? If so, all columns not in the parent table can only be accessed via separate, child-table specific queries which is troublesome... :-| Help! Poul ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] fix pg_autovacuum
Hi Alvaro, Is there any chance of backporting the integrated version to 8.0? We have about 50,000 tables and the autovacuum daemon churns at 100% cpu for long stretches... any improvement over that would be welcome. Thanks, Ben On 7/29/05, Alvaro Herrera <[EMAIL PROTECTED]> wrote: On Fri, Jul 29, 2005 at 03:54:07PM -0500, Jeff Lund wrote:Hi,In the future please post to a list instead of asking me privately. Ihave picked a list arbitrarily to post the response.> I am a DBA using Postgres 8.0.3, and it is great. Could someone> tell me what is wrong or what the problems are with fix pg_autovacuum> O(n^2) behavior.The problem is that pg_autovacuum uses a query against the pg_classsystem catalog, and has to compare the results with its internal table list. This was done using a O(n^2) algorithm. I'm not sure if a fixwas developed for this problem, but I suspect not.The integrated autovacuum process that has been developed for 8.1 doesnot have this problem because it uses a radically different approach to obtaining/keeping information. In fact, each iteration is a newprocess, so there's no table list kept in memory.I might add that I completely rewrote the autovacuum daemon, and I don'tthink a single line of the original code is present in the new version. The ideas, of course, are mostly the same. (For the time being at least-- there's no saying where the new code will evolve to.)--Alvaro Herrera (ip.org >)"Las mujeres son como hondas: mientras más resistencia tienen, más lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith)---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] remote connection, web hosting, IP adress
ok, thanks - Original Message - From: "A. Kretschmer" <[EMAIL PROTECTED]> To: Sent: Monday, September 05, 2005 6:15 PM Subject: Re: [GENERAL] remote connection, web hosting, IP adress am 05.09.2005, um 15:49:23 +0200 mailte Zlatko Mati? folgendes: How can I connect to remote server from a remote personal computer without its own IP adress ? You can connect to the remote server via ssh. Then you are a local user. SSH is a highly secure protocol, i suggest, use ssh with PublicKey-AUTH. If you connect to the server, than you can use 'psql -h localhost'. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
