[HACKERS] 8.0.2 beta announcement??
Magnus I have been looking out for the official 8.0.2 beta announcement before we announce the win32 binaries that were built last week. Did we miss it (I can't see anythign in the archives), or have plans changed? Regards, Dave. ---(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] 8.0.2 beta announcement??
Dave Page wrote: Magnus I have been looking out for the official 8.0.2 beta announcement before we announce the win32 binaries that were built last week. Did we miss it (I can't see anythign in the archives), or have plans changed? http://archives.postgresql.org/pgsql-general/2005-03/msg01311.php -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 8.0.2 beta announcement??
-Original Message- From: Neil Conway [mailto:[EMAIL PROTECTED] Sent: 06 April 2005 10:57 To: Dave Page Cc: pgsql-hackers@postgresql.org; Marc G. Fournier Subject: Re: [HACKERS] 8.0.2 beta announcement?? Dave Page wrote: Magnus I have been looking out for the official 8.0.2 beta announcement before we announce the win32 binaries that were built last week. Did we miss it (I can't see anythign in the archives), or have plans changed? http://archives.postgresql.org/pgsql-general/2005-03/msg01311.php Bah, and there was me expecting to see it in -announce. Whatever next... Thanks Neil :-) /D ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] prepared statements don't log arguments?
Hi! When setting log_statement = all, and using JDBC PreparedStatements, I get $n in the log where the real arguments used to be in previous versions of postgresql: postgres[30059]: [97-1] LOG: statement: INSERT INTO group_data (this_group_id, item_text, link_path) VALUES ($1, $2, $3) I really need to know the *real* arguments... How can I get them? Is it a bug? /Palle ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Two phase commit
On Tue, 5 Apr 2005, Alvaro Herrera wrote: What happenned to your two phase commit patch? Are you still working on it? Have you got something done from the last time we heard from you? I've kept it up-to-date by doing cvs update every now and then and fixing possible conflicts. It would be nice if you hackers could take a serious look at it and tell what needs to be done to get it finally committed. I've been busy with other things and haven't had the time to push it. I try to update my project page every time I update the patch: http://www.hut.fi/~hlinnaka/pgsql/ There isn't any big issues left as far as I know. - Heikki ---(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] prepared statements don't log arguments?
Palle Girgensohn [EMAIL PROTECTED] writes: When setting log_statement = all, and using JDBC PreparedStatements, I get $n in the log where the real arguments used to be in previous versions of postgresql: postgres[30059]: [97-1] LOG: statement: INSERT INTO group_data (this_group_id, item_text, link_path) VALUES ($1, $2, $3) I really need to know the *real* arguments... How can I get them? Is it a bug? The bug was that prepared statements didn't work properly in the past. That is the statement you're actually running. You might want to look into JDBC options to disable use of prepared statements. The old emulation code must still be there in case it runs against a =7.4 database so perhaps there's an option to use it. -- greg ---(end of broadcast)--- TIP 3: 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: [HACKERS] 8.0.2 beta announcement??
beta was packaged, and announced, last Friday/Saturday *scratch head* I didn't put it on pgsql-announce, only pgsql-general, so that might be the mix up? On Wed, 6 Apr 2005, Dave Page wrote: Magnus I have been looking out for the official 8.0.2 beta announcement before we announce the win32 binaries that were built last week. Did we miss it (I can't see anythign in the archives), or have plans changed? Regards, Dave. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Another trip for me
I am on a trip again. This time I am in Kansas City, Missouri doing training from yesterday until Friday, and I am back again next week from Tuesday to Thursday. This of course delays my reading of email. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.0.2 beta announcement??
-Original Message- From: Marc G. Fournier [mailto:[EMAIL PROTECTED] Sent: 06 April 2005 16:07 To: Dave Page Cc: pgsql-hackers@postgresql.org Subject: Re: 8.0.2 beta announcement?? beta was packaged, and announced, last Friday/Saturday *scratch head* I didn't put it on pgsql-announce, only pgsql-general, so that might be the mix up? Yup, I don't do -general. :-) /D ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)
I wrote: Arjen van der Meijden [EMAIL PROTECTED] writes: SELECT COUNT(*) FROM data_main AS dm, postcodes AS p WHERE dm.range BETWEEN p.range_from AND p.range_till Planner error ... because it doesn't have any good way to estimate the number of matching rows, it thinks that way is a bit more expensive than data_main as the outside, but in reality it seems a good deal cheaper: BTW, it would get the right answer if it had recognized the WHERE clause as a range restriction --- it still doesn't know exactly what fraction of rows will match, but its default estimate is a great deal tighter for WHERE x something AND x somethingelse than it is for two unrelated inequality constraints. Enough tighter that it would have gone for the correct plan. The problem is that it doesn't recognize the WHERE as a range constraint on dm.range. I thought for a moment that this might be a recently-introduced bug, but actually the code is operating as designed: clauselist_selectivity says * See if it looks like a restriction clause with a pseudoconstant * on one side. (Anything more complicated than that might not * behave in the simple way we are expecting.) Pseudoconstant in this context means a constant, parameter symbol, or non-volatile functions of these ... so comparisons against values from another table don't qualify. It seems like we're missing a bet though. Can anyone suggest a more general rule? Do we need for example to consider whether the relation membership is the same in two clauses that might be opposite sides of a range restriction? It seems like a.x b.y AND a.x b.z probably can be treated as a range restriction on a.x for this purpose, but I'm much less sure that the same is true of a.x b.y AND a.x c.z Thoughts? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)
On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote: Can anyone suggest a more general rule? Do we need for example to consider whether the relation membership is the same in two clauses that might be opposite sides of a range restriction? It seems like a.x b.y AND a.x b.z In a case like this, you could actually look at the data in b and see what the average range size is. If you wanted to get really fancy, the optimizer could decide how best to access a based on each row of b. probably can be treated as a range restriction on a.x for this purpose, but I'm much less sure that the same is true of a.x b.y AND a.x c.z Well, this could end up being much trickier, since who knows how b and c are related. Though thinking about it, although I threw out the row-by-row analysis idea to be glib, that would actually work in this case; you could take a look at what b and c look like each time 'through the loop'. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)
Jim C. Nasby [EMAIL PROTECTED] writes: On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote: Can anyone suggest a more general rule? Do we need for example to consider whether the relation membership is the same in two clauses that might be opposite sides of a range restriction? It seems like a.x b.y AND a.x b.z In a case like this, you could actually look at the data in b and see what the average range size is. Not with the current statistics --- you'd need some kind of cross-column statistics involving both y and z. (That is, I doubt it would be helpful to estimate the average range width by taking the difference of independently-calculated mean values of y and z ...) But yeah, in principle it would be possible to make a non-default estimate. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)
John A Meinel [EMAIL PROTECTED] writes: Actually, I think he was saying do a nested loop, and for each item in the nested loop, re-evaluate if an index or a sequential scan is more efficient. I don't think postgres re-plans once it has started, though you could test this in a plpgsql function. It doesn't, and in any case that's a microscopic view of the issue. The entire shape of the plan might change depending on what we think the selectivity is --- much more than could be handled by switching scan types at the bottom level. Also, I anticipate that bitmap-driven index scans will change things considerably here. The range of usefulness of pure seqscans will drop drastically... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for
On Wed, 2005-04-06 at 18:09 -0400, Tom Lane wrote: I wrote: Arjen van der Meijden [EMAIL PROTECTED] writes: SELECT COUNT(*) FROM data_main AS dm, postcodes AS p WHERE dm.range BETWEEN p.range_from AND p.range_till Planner error ... because it doesn't have any good way to estimate the number of matching rows, it thinks that way is a bit more expensive than data_main as the outside, but in reality it seems a good deal cheaper: BTW, it would get the right answer if it had recognized the WHERE clause as a range restriction --- it still doesn't know exactly what fraction of rows will match, but its default estimate is a great deal tighter for WHERE x something AND x somethingelse than it is for two unrelated inequality constraints. Enough tighter that it would have gone for the correct plan. The problem is that it doesn't recognize the WHERE as a range constraint on dm.range. Can anyone suggest a more general rule? Do we need for example to consider whether the relation membership is the same in two clauses that might be opposite sides of a range restriction? It seems like a.x b.y AND a.x b.z Not sure we need a more general rule. There's only three ways to view this pair of clauses: i) its a range constraint i.e. BETWEEN ii) its the complement of that i.e. NOT BETWEEN iii) its a mistake, but we're not allowed to take that path Arjen's query and your generalisation of it above is a common type of query - using a lookup of a reference data table with begin/end effective dates. It would be very useful if this was supported. probably can be treated as a range restriction on a.x for this purpose, but I'm much less sure that the same is true of a.x b.y AND a.x c.z I can't think of a query that would use such a construct, and might even conclude that it was very poorly normalised model. I would suggest that this is much less common in practical use. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: 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: [HACKERS] prepared statements don't log arguments?
postgres[30059]: [97-1] LOG: statement: INSERT INTO group_data (this_group_id, item_text, link_path) VALUES ($1, $2, $3) I really need to know the *real* arguments... How can I get them? Is it a bug? The bug was that prepared statements didn't work properly in the past. That is the statement you're actually running. You might want to look into JDBC options to disable use of prepared statements. The old emulation code must still be there in case it runs against a =7.4 database so perhaps there's an option to use it. I think he has a really excellent point. It should log the parameters as well. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Shared row locking, revisited
Hackers, Again I come back to this topic. Now I have a better hold on an idea that is hopefully implementable and doesn't have expensive performance penalties. Forget the idea of using the regular lock manager directly on tuples. It's folly because we can't afford to have that many locks. Instead we will lock on the transactions that are involved in a shared row lock. We will have a cluster-wide counter, call it MultiXactId for the sake of this explanation. When someone wants to share lock an (unlocked) tuple, he creates a new MultiXactId, a bit is set in t_infomask (HEAP_XMAX_FOR_SHARE, say), and the tuple's Xmax is set to the new MultiXactId. We will have two additional SLRU areas; one will contain offsets to the other, so that the second can store variable length arrays. In the first (call it pg_offsets) we will use the MultiXactId as key, and the last offset of the second as value. The second SLRU area (call it pg_multixact_members) is accessed using the offset from pg_offsets, and contains the members of the respective MultiXactId. So, returning to the locker: it grabbed the MultiXactId; it records an offset of (previous offset + 1) in pg_offsets, and its own TransactionId in that offset of pg_multixact_members. This was a locker of an unlocked tuple. But if it finds that the tuple is already locked, it gets the MultiXactId from the tuple, goes to pg_offsets and from there to pg_multixact_members; it then knows what transactions are locking this tuple. It can check which of those are still running and discard those that aren't. So at this point it has a list of TransactionIds, to which it adds itself, and stores it as a new MultiXactId following the procedure outlined above. Possible optimization: keep a list of the MultiXactIds the current TransactionId is member of (this can be done in local memory). If at some point it is going to create a new MultiXactId first check this list to see if the same members can be found, and reuse the MultiXactId if so. Thus we save a MultiXactId (useful trick for when somebody wants to lock a whole table, for example). Sleeping is done using XactLockTableWait() on each of the members of the MultiXactId. If it wakes after sleeping on all of them only to find that the MultiXactId has changed, it will have to sleep again. This can cause starvation if more shared lockers come while it is sleeping. Not sure how to solve this (and I think it's an important problem to solve.) One way would be locking the MultiXactId itself so that nobody can change it. Note that we do this only for shared locks. For exclusive lock, using the TransactionId as is done in the current code is OK. At transaction start, the current value of the current MultiXactId variable is saved. For cleanup, we can truncate the pg_offsets table at the MultiXactId previous to the youngest one saved; and pg_multixact_members, at whatever pg_offsets has in the truncating position. Because we can't reuse MultiXactIds at system crash (else we risk taking an Id which is already stored in some tuple), we need to XLog it. Not at the locking operation, because we don't want to log that one (too expensive.) We can log the current value of MultiXactId counter once in a while; say, one each (say) 1000 acquisitions. Following a crash, the value is restored to the last one logged + 1000. (I think this can be a problem if we log one acquisition, then write some tuples, and then crash, without flushing the acquisition logged. Maybe a solution is to force a flush after logging an acquisition.) Comments are welcome. -- Alvaro Herrera ([EMAIL PROTECTED]) El miedo atento y previsor es la madre de la seguridad (E. Burke) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] prepared statements don't log arguments?
Christopher Kings-Lynne wrote: I think he has a really excellent point. It should log the parameters as well. neilc=# prepare foo(int, int) as select $1 + $2; PREPARE neilc=# execute foo(5, 10); ... neilc=# execute foo(15, 20); ... % tail /usr/local/pgsql/postmaster.log LOG: statement: prepare foo(int, int) as select $1 + $2; LOG: statement: execute foo(5, 10); LOG: statement: execute foo(15, 20); -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] prepared statements don't log arguments?
At 2005-04-07 12:14:19 +1000, [EMAIL PROTECTED] wrote: % tail /usr/local/pgsql/postmaster.log LOG: statement: prepare foo(int, int) as select $1 + $2; LOG: statement: execute foo(5, 10); LOG: statement: execute foo(15, 20); If you send a v3 protocol execute message instead of an SQL EXECUTE statement, the parameters don't get logged. -- ams ---(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] prepared statements don't log arguments?
On Thu, Apr 07, 2005 at 12:14:19PM +1000, Neil Conway wrote: Christopher Kings-Lynne wrote: I think he has a really excellent point. It should log the parameters as well. neilc=# prepare foo(int, int) as select $1 + $2; PREPARE neilc=# execute foo(5, 10); ... neilc=# execute foo(15, 20); ... % tail /usr/local/pgsql/postmaster.log LOG: statement: prepare foo(int, int) as select $1 + $2; LOG: statement: execute foo(5, 10); LOG: statement: execute foo(15, 20); Yeah, but I think he mentioned JDBC which (I think) uses the low-level protocol and probably doesn't log the parameters as well (I notice that his example has INSERT as the query, not PREPARE nor EXECUTE.) -- Alvaro Herrera ([EMAIL PROTECTED]) I call it GNU/Linux. Except the GNU/ is silent. (Ben Reiter) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] prepared statements don't log arguments?
Neil Conway wrote: Christopher Kings-Lynne wrote: I think he has a really excellent point. It should log the parameters as well. neilc=# prepare foo(int, int) as select $1 + $2; PREPARE neilc=# execute foo(5, 10); ... neilc=# execute foo(15, 20); ... % tail /usr/local/pgsql/postmaster.log LOG: statement: prepare foo(int, int) as select $1 + $2; LOG: statement: execute foo(5, 10); LOG: statement: execute foo(15, 20); Query-level EXECUTE is logged, but Bind/Execute via the V3 extended query protocol (which is what the JDBC driver does) isn't. In fact, the logging for the extended query protocol really sucks: the server logs only the Parse, and is silent about Bind/Execute, so there are all sorts of strange cases where your statement logs do not reflect what was actually executed at all. For example, the JDBC driver issues a Parse (but no Execute!) when an application asks for type metadata from a query, and it can issue multiple Bind/Executes for a single Parse. I've raised this before on -hackers but haven't had time to do anything about it myself yet. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] prepared statements don't log arguments?
Oliver Jowett wrote: Query-level EXECUTE is logged, but Bind/Execute via the V3 extended query protocol (which is what the JDBC driver does) isn't. Ah, I see. Yes, that certainly needs to be fixed. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] prepared statements don't log arguments?
Greg Stark wrote: Palle Girgensohn [EMAIL PROTECTED] writes: When setting log_statement = all, and using JDBC PreparedStatements, I get $n in the log where the real arguments used to be in previous versions of postgresql: You might want to look into JDBC options to disable use of prepared statements. The old emulation code must still be there in case it runs against a =7.4 database so perhaps there's an option to use it. You can do this by appending '?protocolVersion=2' to the JDBC URL you use (or 'protocolVersion=2' if you already have other URL parameters). If you do this you also lose any features that need V3 protocol support (e.g. query parameter metadata and some resultset metadata). -O ---(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] Did this issue ever get resolved?
http://archives.postgresql.org/pgsql-hackers/2005-03/msg00260.php I don't see any other discussions about it in the archives. I too get these from time to time on my W2K box. Mostly the permission denied entries. If 1663/17269/1677179: Permission denied refers to a file under the data directory (I have found a folder 17269 that contains similiar names to 1677179) then it would seem postgres is trying to write data to a file that does not exist. Mike ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] prepared statements don't log arguments?
Oliver Jowett [EMAIL PROTECTED] writes: In fact, the logging for the extended query protocol really sucks: Without doubt. Someone has to sit down and think about exactly what we should log, where when and how ... proposals welcome ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
