Re: [HACKERS] table-level and row-level locks.
Jenny - [EMAIL PROTECTED] writes: htmldiv style='background-color:'DIVIam trying to acquire rowlevel locks in postgresql. I try doing this: /DIV DIVnbsp;'select * from students where name='Larry' for update;/DIV DIVBut by looking at the holding array of proclock , I've noticed that by doing this only DTAccessShareLock gets acquired which is a table level lock. /DT Please do not use HTML mail on this list ... Anyway, the answer to your question is that row locks are recorded on disk (by marking the tuple as locked). We'd soon run out of memory if we tried to record them in the shared lock table. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] agg/order-by question
Consider the explain for the following queries .. sample=# explain select a, count(*) from foo group by a order by a; QUERY PLAN - Aggregate (cost=69.83..77.33 rows=100 width=4) - Group (cost=69.83..74.83 rows=1000 width=4) - Sort (cost=69.83..72.33 rows=1000 width=4) Sort Key: a - Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4) (5 rows) sample=# explain select a, count(*) from foo group by a order by a desc; QUERY PLAN --- Sort (cost=80.65..80.90 rows=100 width=4) Sort Key: a - Aggregate (cost=69.83..77.33 rows=100 width=4) - Group (cost=69.83..74.83 rows=1000 width=4) - Sort (cost=69.83..72.33 rows=1000 width=4) Sort Key: a - Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4) (7 rows) In the first case pgsql doesn't have a Sort on top because the Sort below the Group produces the right interesting order (using the System-R term). In the second case however, since the order-by clause demands desc there is a Sort tagged on on top. Now, instead of doing this, isn't it better to just have a similar plan as in the first case, but just change the lower Sort to be descending ? It doesn't affect the Group and the Agg in any way .. -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Transaction handling in extended query mode and Sync
Hello: Sorry for late response... I could finally get Npgsql to talk protocol 3.0 version :) It is not 100% but it is near... Great !! I'm going to open my code at sourceforge :) I give it a try in a test similar to yours... I didn't send the create database commands just the row insertion. in both sequences, I could get the desired behaviour. I could send the begin transaction in simple query mode, send the insert in extended mode and send a commit or rollback in simple mode sending the sync message in the end of extended mode. Are you still having problems with it? I have it fixed sending the Sync message only for ALTER, CREATE AND DROP commands and seems to work as expected, iwill try to do more tests with transactions and the sync message soon, -- Best regards Carlos Guzmán Álvarez Vigo-Spain - Miembro del Proyecto FirebirdSQL. - Miembro honorario de la Fundación FirebirdSQL. http://www.firebirdsql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] initcap incompatibility issue
It sounds like Oracle is simply regexing for anything that ISN'T a letter to initcap right after it. If that's the case, you could just regex too. Or more likely, use the appropriate ctype.h function (isalpha, probably). Having tested it, Oracle capitalizes after all non-alphanumeric characters, so !isalnum() is the appropriate function. (That makes it a one-line patch on 7.3.3, which I've already tested.) AFAIK, our specification for this function is be like Oracle, so it's a bug fix and fair game for 7.4. Of course, the sooner you get it in the more likely we'll see it that way ;-). Later in beta, only critical bugfixes will be accepted, and this one surely ain't very critical. Now if I can just get CVS working on Redhat 8 and remember how to build a patch, even a one-liner. :-) -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] agg/order-by question
On Sat, Jul 12, 2003 at 00:39:06 -0700, Sailesh Krishnamurthy [EMAIL PROTECTED] wrote: Consider the explain for the following queries .. sample=# explain select a, count(*) from foo group by a order by a; QUERY PLAN - Aggregate (cost=69.83..77.33 rows=100 width=4) - Group (cost=69.83..74.83 rows=1000 width=4) - Sort (cost=69.83..72.33 rows=1000 width=4) Sort Key: a - Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4) (5 rows) sample=# explain select a, count(*) from foo group by a order by a desc; QUERY PLAN --- Sort (cost=80.65..80.90 rows=100 width=4) Sort Key: a - Aggregate (cost=69.83..77.33 rows=100 width=4) - Group (cost=69.83..74.83 rows=1000 width=4) - Sort (cost=69.83..72.33 rows=1000 width=4) Sort Key: a - Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4) (7 rows) In the first case pgsql doesn't have a Sort on top because the Sort below the Group produces the right interesting order (using the System-R term). In the second case however, since the order-by clause demands desc there is a Sort tagged on on top. Now, instead of doing this, isn't it better to just have a similar plan as in the first case, but just change the lower Sort to be descending ? It doesn't affect the Group and the Agg in any way .. You might try this in 7.4. I am pretty sure a change was made a couple of weeks ago to let group by work with either sort order. Also hash aggragates have been available for quite a while in 7.4. This is a better plan when there are only a small number of distinct values. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why are triggers semi-deferred?
At 11:51 PM 1/06/2003 -0400, Bruce Momjian wrote: Does anyone have answers for these? I read the thread and don't 100% understand it all. My belief is that at least ROW triggers need fixing (7.3 doesn't have statement, not sure about 7.4). Currently, if you write a plpgsql procedure which calls more than one insert/update/delete statements, the AFTER triggers for all of these statements will not fire until after the procedure exits. They should fire either just after each row is updated, or just after the most immediately enclosing statement executes. I think the thread wanted the latter. So, if we have a table with two rows, and a BEFORE and AFTER trigger, and a plpgsql procedure that updates all rows twice, then we should have: procedure called procedure executes first update before trigger fires(row 1) before trigger fires(row 2) row 1 updated row 2 updated after trigger fires(row 1) after trigger fires(row 2) procedure executes second update before trigger fires(row 1) before trigger fires(row 2) row 1 updated row 2 updated after trigger fires(row 1) after trigger fires(row 2) procedure exits What we have in 7.3 is: procedure called procedure executes first update before trigger fires(row 1) before trigger fires(row 2) row 1 updated row 2 updated procedure executes second update before trigger fires(row 1) before trigger fires(row 2) row 1 updated row 2 updated procedure exits after trigger fires(row 1) after trigger fires(row 2) after trigger fires(row 1) after trigger fires(row 2) IIRC, the thread did not really discuss whether do intersperse the BEFORE executions with the updates, but doing them all before seems consistent. Apologies is this has been covered elsewhere... Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] new src :
hi, now i writed support for writing and reading files by select. i created function like in stdio.h iv_fopen (cstring,cstring), iv_fread , etc the FILE * struct is replaced by OID value you can download src from http://www.psycho.pl/public/src/pgsql/files.tar.bz2 and sample to to have /etc/passwd in table Is nice way to have conf files in db, may be /etc/passwd its not so good idea, but apache conf, or bind , or ftp i think can be. I had to added one function to fd.h/c ( bool IsAllocated (FILE*) ) to check is file still allocated. This option maybe its not so goot way to use it as std but will be nice see it as contrib or option in ./configure what do you think ? (THIS IS EXPERIMENTAL) bye ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] agg/order-by question
Bruno == Bruno Wolff, Bruno writes: Bruno You might try this in 7.4. I am pretty sure a change was Bruno made a couple of weeks ago to let group by work with either Bruno sort order. Also hash aggragates have been available for Bruno quite a while in 7.4. This is a better plan when there are Bruno only a small number of distinct values. Gotcha ! Thanks. TelegraphCQ is still on the 7.3.2 code base .. after doing one hellish merge in March, I'm not too eager to do another, although merging more often is likely going to be less painful. I knew about the hash-aggregates - we had set spilling of hash-aggregates to disk for large number of distinct values (with a crude form of recursive partitioning) as a course project for our undergraduate database class at Berkeley. When I get some time, I want to clean up my solution code and contribute it as a patch. I don't think that will be before the end of summer though. BTW, some systems prefer sorted grouped-aggregates to hashed grouped-aggregates - even for small distinct values. How it works is to just update the running aggregates in place in the sort tournament tree. The only requirement is to be able to compute aggregates of aggregates, so that partial aggs for the same distinct values across different runs can be merged. The advantage is that you get sorted grouped aggregation for the same cost of unsorted hash-grouped agg. The disadvantage is that you lose the modularity of the sort. -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [GENERAL] PG crash on simple query, story continues
Maksim Likharev [EMAIL PROTECTED] writes: So following modification seems to fixed all PG (7.3/7.3.3)crashes on Solaris ( NON C LOCALE ) Given that the problem is Solaris' tendency to write more data than the specified output buffer length allows, I'd think this is still risking a core dump (due to null pointer dereference). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] php with postgres
what do you think about plphp ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] php with postgres
-Original Message- From: ivan [mailto:[EMAIL PROTECTED] Sent: 12 July 2003 22:34 To: [EMAIL PROTECTED] Subject: [HACKERS] php with postgres what do you think about plphp ? I know a few people that would probably welcome it. Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] php with postgres
On Sat, 12 Jul 2003, Dave Page wrote: -Original Message- From: ivan [mailto:[EMAIL PROTECTED] Sent: 12 July 2003 22:34 To: [EMAIL PROTECTED] Subject: [HACKERS] php with postgres what do you think about plphp ? I know a few people that would probably welcome it. Regards, Dave. ok, but php should build this lang for postgres i think so, we should talk with php group ? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Handling protocol not supported error message when working with protocol2 and 3
Hi all, I'm finishing the implementation of the protocol 3.0 and now I'm working in how to connect to server using protocol 3.0 when the server doesn't support it 7.3- . As suggested in a previous mail about how to handle with both protocol versions, I'm trying first to connect using version 3.0. The problem is if the server is a 7.3 or earlier, it would answer with a non supported protocol error message, but as I'm in the protocol 3.0 version mode, I would expect a error message in the new format and not in the 2.0 format. How I could handle it? I know this error happens early in the connection establishment, when I send the StartupPacket and I could use this info to do some choices about when to use the version 2 or 3. The problem is that I also could get a lot of errors when sending the startup packet, as password incorrect, database and others. I was thinking about examining the first 4 bytes ( as this is where the message length is located) and try if these bytes could be translated to FATA which would mean the FATAL: unsupported frontend protocol\n error. But I think this would be a big hack. Is there a more elegant solution? I know this question was alread raised some time ago, but I couldn't find it. Thanks in advance. -- Regards, Francisco Figueiredo Jr. -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Handling protocol not supported error message when working with protocol 2 and 3
Francisco Figueiredo Jr. [EMAIL PROTECTED] writes: I'm finishing the implementation of the protocol 3.0 and now I'm working in how to connect to server using protocol 3.0 when the server doesn't support it 7.3- . I'd suggest using the same methods libpq does. You do have to be prepared to accept an old-format error message. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] php with postgres
-Original Message- From: ivan [mailto:[EMAIL PROTECTED] Sent: 12 July 2003 23:02 To: Dave Page Cc: [EMAIL PROTECTED] Subject: RE: [HACKERS] php with postgres ok, but php should build this lang for postgres i think so, we should talk with php group ? I doubt they will do the work - you would have to (or persuade someone else to). Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] php with postgres
ivan wrote: ok, but php should build this lang for postgres i think so, we should talk with php group ? I have been talking with several people about this on-and-off for a while now. If I can find some time in the next few months, I will probably write it (if no one beats me to it). I'm thinking that it should be written using Postgres 7.5devel (once 7.4 stable is branched) and PHP5 (which is in alpha/early-beta testing). Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] weird problem
On Thu, 10 Jul 2003, Christopher Kings-Lynne wrote: Just saw this on our demo server: [EMAIL PROTECTED]:~/htdocs/webdb/conf# /usr/local/pgsql-7.3.3/bin/psql -p5473 phppgadmin psql: FATAL: cannot open segment 1 of relation pg_trigger_tgrelid_tgname_index (target block 8310112): No such file or directory There is clearly something wrong here. The block number is way too large for a system relation. A backtrace from the elog() would be helpful. Thanks, Gavin ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] vacuumdb can't be canceled
Vacuumdb command can't be canceled by Control-C and VACUUM is still running. When wrong database name is specified to vacuumdb, cancellation is required to stop VACUUM FULL which runs long. Option -c of psql forget to set signal handler for 7.3 or prior. Vacuumdb have no signal handler of cancellation for 7.4devel. Kenji Sugita ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [GENERAL] PG crash on simple query, story continues
Possible, but if before almost every tenth query crash the server now it stays, that's only I care about. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Saturday, July 12, 2003 2:05 PM To: Maksim Likharev Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [HACKERS] [GENERAL] PG crash on simple query, story continues Maksim Likharev [EMAIL PROTECTED] writes: So following modification seems to fixed all PG (7.3/7.3.3)crashes on Solaris ( NON C LOCALE ) Given that the problem is Solaris' tendency to write more data than the specified output buffer length allows, I'd think this is still risking a core dump (due to null pointer dereference). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why are triggers semi-deferred?
At 10:38 AM 12/07/2003 -0700, Stephan Szabo wrote: deferred after trigger row 1 deferred after trigger #2 row a deferred after trigger row 2 deferred after trigger #2 row b I'd vote for this; ie. make them execute in the same order they would execute if they were not deferred. Otherwise you open up all sorts of weird application errors if a trigger is deferred/not-deferred. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
