Re: [GENERAL] regarding join
On Sat, Mar 25, 2006 at 12:06:34PM +0530, AKHILESH GUPTA wrote: > hi all, > below I have created two tables in pgsql with field name as 'name' and 'id' > as their datatype 'varchar(15)' and 'integer'. Looks like: select * from test1 UNION ALL select * from test3; would be a very good start. -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] inheridt tables from db
On 3/25/06, nik600 <[EMAIL PROTECTED]> wrote: > hi > > i am considering to port an important web applications to postgres, > this applications is made of php and i reuse the same code for many > customer, due to have a clean structure and simple updates... > > now i have one code and many databases in mysql... > > i know that with postgres i can inheridt some properties...can i have > a main database, called A and then many databases, Customer1, > Customer2, Customer3 and if i made a change in A the changes is > replicated to Customer1,2 and 3? Databases don't have inherited properties, but tables do. http://www.postgresql.org/docs/8.1/static/tutorial-inheritance.html http://www.postgresql.org/docs/8.1/static/ddl-inherit.html -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgadmin
Am Freitag, 24. März 2006 18:48 schrieb A. Kretschmer: > am 24.03.2006, um 19:13:56 + mailte Mary Adel folgendes: > > Is the server running on host "xxx.xxx.xxx.xxx" and accepting > > TCP/IP connections on port 5432? > My guess: you should check your pg_hab.conf. If that were the problem, then the error message would be different. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Advantages of PostgreSQL over MySQL 5.0
Hello, I have with great interrest been following this thread. We have a (small) flame war in house about this and I'm very happy about all the arguments I have seen. I'm a long time user of PostgreSQL (which possibly makes me a bit biased ;-) ) and I think it's great. I'm not a big database expert, but I try to make things as good and standard as I can. In this respect I have 3 questions: 1) I wonder that no one has mentioned anything about security issues in those two. I know that I'm a novice and that I didn't use MySql very much, but it seems to me that the PostgreSQL security is much better than MySql !? 2) I don't know the latest SQL standard (I did say I'm a novice), but how close to some standard is embedded SQL in C in PostgreSQL, Oracle, and MySql ? 3) We are using mambo (the homepage management system), which is based on MySql. I would love to make it use PostgreSQL instead (to ease maintenance, backup, administration, etc.) Does anyone know where to get help/info on this ? Thanks for a good product and a good discusion, Leif On Wed, 22 Mar 2006, Jimbo1 wrote: > Hello there, > > I'm a freelance Oracle Developer by trade (can almost hear the boos now > ;o)), and am looking into developing my own Snowboarding-related > website over the next few years. Anyway, I'm making some decisions now > about the site architecture, and the database I'm going to need is > obviously included. If my site works out, I'm expecting reasonably > heavy traffic, so want a database that I'm confident can cope with it. > > It is out of the question for me to use Oracle, although I am a > (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper > route, and to that end I'm looking at either MySQL or PostgreSQL. > > Regarding MySQL, I've been put off by Oracle's recent purchase of > InnoDB and realise this could badly impact the latest version of the > MySQL database. I can almost hear Larry Ellison's laughter from here > (allegedly)! I've also been put off by the heavy marketing propaganda > on the MySQL website. > > Recently, I've been taking a look at PostgreSQL, and am very impressed > by what I've read, although I've not yet investigated the database > first-hand. To cut to the chase, I would be interested in anybody's > feedback on the advantages that PostgreSQL has over MySQL. > > Also, I've recently read the "Inside MySQL 5.0" (marketing propaganda) > document, and it makes the following claim: > > "With MySQL, customers across all industries are finding they can > easily handle nearly every type of database workload, with performance > and scalability outpacing every other open source rival. As Los Alamos > lab (who uses MySQL to manage their terabyte data warehouse) said, "We > chose MySQL over PostgreSQL primarily because it scales better and has > embedded replication.".". > > If any PostgreSQL devotees on this group can comment on the above and > its accuracy/inaccuracy, I'd really appreciate it. > > Thanks in advance. > > James > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Practical limit on number of tables ina single database
On Fri, Mar 24, 2006 at 06:56:19PM -0500, Tom Lane wrote: > "Just Someone" <[EMAIL PROTECTED]> writes: > > Actually, most table are VERY small. On each schema I would expect 4 - > > 5 tables to go over 1000 records. The rest will be much smaller, with > > most at under 100. And aside from 2-3 tables, the activity will be > > pretty low (few records a day at the most extreme). > > > Can I use this to optimize the fsm part in a different way? > > Seems like a one-page table hardly needs an FSM entry at all: there's > only one place to look for free space anyway. Maybe we should have a > threshold for the minimum size a table must be before it gets an FSM > entry. Well, it makes absolutely no sense to trouble the FSM with any relation that's only a single page. I'm not sure we'd want the limit any higher, though. IIRC when vacuum attempts to register free space in the map, if we've bumped into max_fsm_relations it will pick the smallest relation currently in FSM and drop it, and there is some value in keeping info for 2 page relations. Whats unfortunate is that a 2 page relation would end up with 16 pages in the FSM. Perhaps it would be a lot better if smaller relations didn't get CHUNKPAGES allocated to them, but only what they needed (though that could dramatically complicate FSM management...). Actually, reading the comments, I'm wondering if there's other bad assumptions going on. For example, if we're not running into max_fsm_pages, ISTM that we shouldn't be as stringent about pruning pages based on averege request size. And the comment about using a moving average on RRFS counts might make sense, though I'm not really sure how RRFS causes thrashing of the storage allocations (the reason given for using CHUNKPAGES in the first place). Perhaps an interum fix might be to ignore CHUNKPAGES for any relation that would only be getting one chunk, and just give it the exact number of pages it needs. -- 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] pgadmin
am 25.03.2006, um 14:12:16 +0100 mailte Peter Eisentraut folgendes: > Am Freitag, 24. März 2006 18:48 schrieb A. Kretschmer: > > am 24.03.2006, um 19:13:56 + mailte Mary Adel folgendes: > > > Is the server running on host "xxx.xxx.xxx.xxx" and accepting > > > TCP/IP connections on port 5432? > > > My guess: you should check your pg_hab.conf. > > If that were the problem, then the error message would be different. Yes, you are right. Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Advantages of PostgreSQL over MySQL 5.0
On Sat, Mar 25, 2006 at 14:30:54 +0100, Leif Jensen <[EMAIL PROTECTED]> wrote: > > 1) I wonder that no one has mentioned anything about security issues in > those two. I know that I'm a novice and that I didn't use MySql very much, > but it seems to me that the PostgreSQL security is much better than MySql > !? This may be because you are supposed to limit access to your database servers such that unauthorized users don't get direct access to them easily. So you don't have the same level of concern that you do for services generally exposed to the whole internet, such as for web and mail servers. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prepared SELECT and placeholders with NULL values
On Thu, Mar 23, 2006 at 15:24:16 -0600,
Michael Lea <[EMAIL PROTECTED]> wrote:
>
> $s = $db->prepare('SELECT id FROM person WHERE lname = ? AND fname = ?');
>
> But this does not, returning an empty list:
> $s->execute('Cher', undef);
= NULL will return NULL for every row and no rows will be selected by the
WHERE clause. This is how things are supposed to work according to the
standard.
It is hard to say what you might do without knowing more about what you
are doing, but a simple possibility may be to use the empty string ('') instead
of NULL in your data.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Advantages of PostgreSQL over MySQL 5.0
Leif Jensen wrote: Hello, I have with great interrest been following this thread. We have a (small) flame war in house about this and I'm very happy about all the arguments I have seen. I'm a long time user of PostgreSQL (which possibly makes me a bit biased ;-) ) and I think it's great. I'm not a big database expert, but I try to make things as good and standard as I can. In this respect I have 3 questions: 1) I wonder that no one has mentioned anything about security issues in those two. I know that I'm a novice and that I didn't use MySql very much, but it seems to me that the PostgreSQL security is much better than MySql !? Most people on the list only grudgingly use MySQL and so most are not so well aware of the limitations of MySQL's security model. MySQL has no concept of group memberships or group permissions (or the more complex role permissions). The permissions are simply at the level of the individual user. When I have coded complex apps on MySQL, I have sometimes found it necessary to emulate this level of permission so that the permissions can be "compiled" down to individual permissions on the tables. It is a real pain sometimes. Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] tel;work:509-888-0220 tel;cell:509-630-9974 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] experiences needed - how does Itanium2/1.5GHz(4MB) compare to AMD and Intel CPUs as far as Postgresql is concerned
Hello! First tried some searching around, but did not find anything useful so I gave up and decided to ask here... I am wondering how do pair of 1.5GHz Itanium2(4MB cache) stack up against pair of AMD or Intel server CPUs as far as postgresql performance is concerned? Is it worthy or not? Thanks in advance. Tomaž ---(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] Planet PostgreSQL : Do you want to blog?
Hi, We currently have 28 bloggers in Planet PostgreSQL (http://PlanetPostgreSQL.org ), and we hope to grow more. For the people who want to blog on PostgreSQL, but does not have a blogging area, we provide a space: http://people.PlanetPostgreSQL.org . If you think you are interested, please drop me an e-mail and we can setup a new blog for you. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/ ---(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] A place to post pgbench results
After exchanging a few emails regarding pgbench in the list, I was thinking it'll be cool to have a place to post pgbench resulats from all over the place. Just so people can get an idea of what others are getting. If more people think it's a good idea, I'll be glad to host it as part of my blog. I'll create a section for that and will update it everytime results are submitted. If that picks up steam I'll create a small database and app to make it more automatic. Let me know if there's interest and I'll take care of it. Bye, Guy. -- Family management on rails: http://www.famundo.com - coming soon! My development related blog: http://devblog.famundo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problems running regression tests
On 03/24/2006 10:07:24 PM, Tom Lane wrote: "Karl O. Pinc" <[EMAIL PROTECTED]> writes: > The Makefile has Does it work better if you change that to [ -x /usr/bin/chcon ] && /usr/bin/chcon -u user_u -r object_r -t postgresql_db_t testtablespace results Nope. The other thing to keep in mind here is that I'm using a binary rpm I rebuilt from the offical pg srpm. Could rebuilding without SELinux have disappeared required SELinux-ness in the offical binary rpm? Wouldn't think so, but this could well be rpm related. postgresql-8.0.7-1PGDG (Also, I did a 'cp -a' on regress as root before trying gmake check, as postgres, just to keep from ruining my original. FYI.) bash-3.00$ gmake check rm -rf testtablespace results mkdir testtablespace results [ -x /usr/bin/chcon ] && /usr/bin/chcon -u user_u -r object_r -t postgresql_db_t testtablespace results /usr/bin/chcon: can't apply partial context to unlabeled file testtablespace /usr/bin/chcon: can't apply partial context to unlabeled file results gmake: *** [cleandirs] Error 1 bash-3.00$ ls -Z results Sorry, this option can only be used on a SELinux kernel. Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(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] PANIC: heap_update_redo: no block
> Hrm ok ill see about doing either the patch or setting wal_debug to > true (or both). However im currently on vacation till Saturday so ill > do this first thing then and report the results back. Thank you very > much! Ok here is the output with wal_debug set to 1 in the config and the final supplied patch by Qingqing. (@)<2006-03-25 20:54:17.509 MST>[26571]LOG: database system was interrupted while in recovery at 2006-03-25 20:51:58 MST (@)<2006-03-25 20:54:17.509 MST>[26571]HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. (@)<2006-03-25 20:54:17.509 MST>[26571]LOG: checkpoint record is at D/1919D5F0 (@)<2006-03-25 20:54:17.509 MST>[26571]LOG: redo record is at D/191722C8; undo record is at 0/0; shutdown FALSE (@)<2006-03-25 20:54:17.509 MST>[26571]LOG: next transaction ID: 81148900; next OID: 16566476 (@)<2006-03-25 20:54:17.509 MST>[26571]LOG: next MultiXactId: 1; next MultiXactOffset: 0 (@)<2006-03-25 20:54:17.509 MST>[26571]LOG: database system was not properly shut down; automatic recovery in progress (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: redo starts at D/191722C8 (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: REDO @ D/191722C8; LSN D/19172678: prev D/191722A0; xid 81148908: Heap - update: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: REDO @ D/19172678; LSN D/191726B8: prev D/191722C8; xid 81148908: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: REDO @ D/191726B8; LSN D/19172788: prev D/19172678; xid 81148908: Heap - update: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: REDO @ D/19172788; LSN D/191727C8: prev D/191726B8; xid 81148908: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: REDO @ D/191727C8; LSN D/19172A30: prev D/19172788; xid 81148908: Heap - update: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: REDO @ D/19172A30; LSN D/19172A70: prev D/191727C8; xid 81148908: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: REDO @ D/19172A70; LSN D/19172F68: prev D/19172A30; xid 81148908: Heap - update: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: REDO @ D/19172F68; LSN D/19172FA8: prev D/19172A70; xid 81148908: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.524 MST>[26571]LOG: REDO @ D/19172FA8; LSN D/19172FD0: prev D/19172F68; xid 81148908: Transaction - commit: 2006-03-18 22:51:5 (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/19172FD0; LSN D/19173088: prev D/19172FA8; xid 81148942: Heap - insert: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/19173088; LSN D/191730C8: prev D/19172FD0; xid 81148942: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/191730C8; LSN D/191731E8: prev D/19173088; xid 81148942: Heap - insert: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/191731E8; LSN D/19173228: prev D/191730C8; xid 81148942: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/19173228; LSN D/19173250: prev D/191731E8; xid 81148942: Transaction - commit: 2006-03-18 22:51:5 (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/19173250; LSN D/19173414: prev D/19173228; xid 81148944: Heap - update: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/19173414; LSN D/19173454: prev D/19173250; xid 81148944: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/19173454; LSN D/19173760: prev D/19173414; xid 81148944: Heap - update: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/19173760; LSN D/191737A0: prev D/19173454; xid 81148944: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/191737A0; LSN D/191737C8: prev D/19173760; xid 81148944: Transaction - commit: 2006-03-18 22:51:5 (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/191737C8; LSN D/1917380C: prev D/191737A0; xid 81148946: Heap - update: rel 1663/16386/16865; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/1917380C; LSN D/19173834: prev D/191737C8; xid 81148946: Transaction - commit: 2006-03-18 22:51:5 (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/19173834; LSN D/191739B8: prev D/1917380C; xid 81148948: Heap - insert: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/191739B8; LSN D/191739F8: prev D/19173834; xid 81148948: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/191739F8; LSN D/19173AE4: prev D/191739B8; xid 81148948: Heap - insert: rel 1663/16386/2619; tid (@)<2006-03-25 20:54:17.525 MST>[26571]LOG: REDO @ D/19173AE4; LSN D/19173B24: prev D/191739F8; xid 81148948: Btree - insert: rel 1663/16386/2696; tid (@)<2006-03-25 20:54:17.525 MST>[26571]L
[GENERAL] pg 8.1.2 performance issue
I have a performance riddle, hoping someone can point me in a helpful direction. We have a pg 8.1.2 cluster using Apache::Sessions and experiencing simple UPDATEs taking sometimes 30+ seconds to do a very simply update, no foreign keys, no triggers: Table "public.sessions" Column | Type | Modifiers ---+---+--- id| character(32) | not null a_session | text | Indexes: "sessions_pkey" PRIMARY KEY, btree (id) This is on an HP ia64 11.23 box with what appears to be gobs of surplus CPU, I/O (it's on a SAN), and RAM, pretty high query volume from 180 concurrent client connections. Wondering if it is a locking issue, I set up logging to capture existing locks every 10s with this query: SELECT now(), dbu.usename as locker, l.mode as locktype, CASE l.granted WHEN true THEN 'granted' ELSE 'pending' END as status, pg_stat_get_backend_pid(S.backendid) as pid, l.transaction as xid, db.datname||'.'||n.nspname||'.'|| r.relname as relation, case l.mode when 'AccessShareLock' then 1 when 'RowShareLock' then 2 when 'Row ExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4 when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6 when 'ExclusiveLock' then 7 else 100 end as exclusivity, pg_stat_get_backend_activity(S.backendid) as query FROM pg_user dbu, (SELECT pg_stat_get_backend_idset() AS backendid) AS S, pg_database db, pg_locks l, pg_class r, pg_namespace n WHERE db.oid = pg_stat_get_backend_dbid(S.backendid) AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid) AND l.pid = pg_stat_get_backend_pid(S.backendid) AND l.relation = r.oid AND l.database = db.oid AND r.relnamespace = n.oid ORDER BY exclusivity DESC, db.datname, n.nspname, r.relname, l.mode;" I see what appear to be many single transactions holding RowExclusiveLocks for sometimes 40-50 seconds while their query shows " in transaction". 2006-03-25 20:04:01.063873-08 | www | RowExclusiveLock | granted | 17192 | 270205914 | db1.public.sessions | 3 | in transaction 2006-03-25 20:04:11.128632-08 | www | RowExclusiveLock | granted | 17192 | 270205914 | db1.public.sessions | 3 | in transaction 2006-03-25 20:04:21.215896-08 | www | RowExclusiveLock | granted | 17192 | 270205914 | db1.public.sessions | 3 | in transaction I'm thinking that means the client is simply tweaking a row and then failing to commit the change for 40-50 seconds. Is that consistent? Is there something else obvious here to explain the delays? Does this sound like a browser stop button issue where they may be aborting the query that has the lock, and then issuing another that waits on the first? Other suggestions? Thanks, Ed ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg 8.1.2 performance issue
On Saturday March 25 2006 9:36 pm, Ed L. wrote: > I have a performance riddle, hoping someone can point me in a > helpful direction. We have a pg 8.1.2 cluster using > Apache::Sessions and experiencing simple UPDATEs taking > sometimes 30+ seconds to do a very simply update, no foreign > keys, no triggers: > > Table "public.sessions" > Column | Type | Modifiers > ---+---+--- > id| character(32) | not null > a_session | text | > Indexes: > "sessions_pkey" PRIMARY KEY, btree (id) The table has 6800 rows over 18000 pages, and is getting a minimum of many tens of thousands of updates per day with queries like this: UPDATE sessions SET a_session = ? WHERE id = ? Ed > > > This is on an HP ia64 11.23 box with what appears to be gobs > of surplus CPU, I/O (it's on a SAN), and RAM, pretty high > query volume from 180 concurrent client connections. > > Wondering if it is a locking issue, I set up logging to > capture existing locks every 10s with this query: > > SELECT now(), dbu.usename as locker, l.mode as locktype, CASE > l.granted WHEN true THEN 'granted' ELSE 'pending' END as > status, pg_stat_get_backend_pid(S.backendid) > as pid, l.transaction as xid, > db.datname||'.'||n.nspname||'.'|| r.relname as relation, case > l.mode when 'AccessShareLock' then 1 when 'RowShareLock' then > 2 when 'Row > ExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4 > when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6 > when 'ExclusiveLock' then 7 else 100 end as > exclusivity, pg_stat_get_backend_activity(S.backendid) as > query FROM pg_user dbu, > (SELECT pg_stat_get_backend_idset() AS backendid) AS S, > pg_database db, pg_locks l, pg_class r, pg_namespace n > WHERE db.oid = pg_stat_get_backend_dbid(S.backendid) > AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid) > AND l.pid = pg_stat_get_backend_pid(S.backendid) > AND l.relation = r.oid > AND l.database = db.oid > AND r.relnamespace = n.oid > ORDER BY exclusivity DESC, db.datname, n.nspname, r.relname, > l.mode;" > > > I see what appear to be many single transactions holding > RowExclusiveLocks for sometimes 40-50 seconds while their > query shows " in transaction". > > 2006-03-25 20:04:01.063873-08 | www | RowExclusiveLock | > granted > > | 17192 | 270205914 | db1.public.sessions | 3 | > | > > in transaction > 2006-03-25 20:04:11.128632-08 | www | RowExclusiveLock | > granted > > | 17192 | 270205914 | db1.public.sessions | > | 3 in transaction > > 2006-03-25 20:04:21.215896-08 | www | RowExclusiveLock | > granted > > | 17192 | 270205914 | db1.public.sessions | > | 3 in transaction > > I'm thinking that means the client is simply tweaking a row > and then failing to commit the change for 40-50 seconds. Is > that consistent? Is there something else obvious here to > explain the delays? Does this sound like a browser stop > button issue where they may be aborting the query that has the > lock, and then issuing another that waits on the first? Other > suggestions? > > Thanks, > Ed ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg 8.1.2 performance issue
On Saturday March 25 2006 9:49 pm, Ed L. wrote: > On Saturday March 25 2006 9:36 pm, Ed L. wrote: > > I have a performance riddle, hoping someone can point me in > > a helpful direction. We have a pg 8.1.2 cluster using > > Apache::Sessions and experiencing simple UPDATEs taking > > sometimes 30+ seconds to do a very simply update, no foreign > > keys, no triggers: > > > > Table "public.sessions" > > Column | Type | Modifiers > > ---+---+--- > > id| character(32) | not null > > a_session | text | > > Indexes: > > "sessions_pkey" PRIMARY KEY, btree (id) > > The table has 6800 rows over 18000 pages, and is getting a > minimum of many tens of thousands of updates per day with > queries like this: > > UPDATE sessions SET a_session = ? WHERE id = ? Sorry, it's getting late. Yes, the table has been analyzed and explain shows an index scan is occurring. 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
Re: [GENERAL] pg 8.1.2 performance issue
On 3/26/06, Ed L. <[EMAIL PROTECTED]> wrote: > On Saturday March 25 2006 9:36 pm, Ed L. wrote: > > I have a performance riddle, hoping someone can point me in a > > helpful direction. We have a pg 8.1.2 cluster using > > Apache::Sessions and experiencing simple UPDATEs taking > > sometimes 30+ seconds to do a very simply update, no foreign > > keys, no triggers: > > > > Table "public.sessions" > > Column | Type | Modifiers > > ---+---+--- > > id| character(32) | not null > > a_session | text | > > Indexes: > > "sessions_pkey" PRIMARY KEY, btree (id) > > The table has 6800 rows over 18000 pages, and is getting a > minimum of many tens of thousands of updates per day with > queries like this: If you're updating that much, how often are you running 'analyze'? Are you running autovacuum? How often? -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
