Re: [mail] Re: [HACKERS] Native Win32 sources
On 27 Nov 2002 at 8:21, Al Sutton wrote: The problem I have with VMWare is that for the cost of a licence plus the additional hardware on the box running it (CPU power, RAM, etc.) I can buy a second cheap machine, using VMWare doesn't appear to save me my biggest overheads of training staff on Unix and cost of equipment (software and hardware). I've been looking at Bochs, but 1.4.1 wasn't stable enough to install RedHat, PostgreSQL, etc. reliably. I have been reading this thread all along and I have some suggestions. They are not any different than already made but just summerising them. 1) Move to linux. You can put a second linux box with postgresql on it. Anyway your app. is on windows so it does not make much of a difference because developers will be accessing database from their machines. Secondly if you buy a good enough mid-range machine, say with 40GB SCSI with 2G of RAM, each developer can develop on his/her own database. In case of performance testing, you can schedule it just like any other shared resource. It is very easy to run multiple isolated postgresql instances on a linux machine. Just change the port number and use a separate data directory. That's it.. Getting people familiarized with unix/.linux upto a point where they can use their own database is matter of half a day. 2) Do not bank too much on windows port yet. Will all respect to people developing native windows port of postgresql, unless you know the correct/stable behaviour of postgresql on unix, you might end up in a situation where you don't know whether a bug/problem is in postgresql or with postgresql/windows. I would not recommend getting into such a situation. Your contribution is always welcome in any branch but IMO it is not worth at the risk of slipping your own product development. Believe me, moving to linux might seem scary at first but it is no more than couple of days matter to get a box to play around. Untill you need a good machine for performance tests, a simple 512MB machie with enough disk would be sufficient for any development among the group.. HTH Bye Shridhar -- My father taught me three things: (1) Never mix whiskey with anything but water. (2) Never try to draw to an inside straight.(3) Never discuss business with anyone who refuses to give his name. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] How can i import database from MSSQL to Postgres?(NULL BODY)
thanks in advance _ Are you a Techie? Get Your Free Tech Email Address Now! Visit http://www.TechEmail.com _ Select your own custom email address for FREE! Get [EMAIL PROTECTED] w/No Ads, 6MB, POP more! http://www.everyone.net/selectmail?campaign=tag ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Need Quote for 7.3
I think it's great - but don't quote me on that. :) PostgreSQL. Because life's too short to learn Oracle. PostgreSQL. For those with more to do than babysit a database. Ah, better. More orthogonal. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] How can i import database from MSSQL to Postgres?(NULL BODY)
David, Most generic format you can get it out of SQL Server in is CSV by using a DTS ( Export Data ) wizard to text format. Another alternative is the bcp command line tool, which produces standardised formats These are documented in SQL Books Online ( free download from Microsoft ) http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp Regards AJ david luo [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... thanks in advance _ Are you a Techie? Get Your Free Tech Email Address Now! Visit http://www.TechEmail.com _ Select your own custom email address for FREE! Get [EMAIL PROTECTED] w/No Ads, 6MB, POP more! http://www.everyone.net/selectmail?campaign=tag ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] [HACKERS] Realtime VACUUM, was: performance of insert/delete/update
I always wandered if VACUUM is the right name for the porcess. Now, when PostgreSQL is actively challenging in Enterprise space, it might be a good idea to give it a more enterprise-like name. Try to think how it is looking for an outside person to see us, database professionals hold lenghty discussions about the ways we vacuum a database. Why should you need to vacuum a database? Is it dirty? In my personal opinion, something like space reclaiming daemon, free-list organizer, tuple recyle job or segment coalesce process would sound more business-like . Regards, Nick - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Curtis Faith [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED]; Ron Johnson [EMAIL PROTECTED]; PgSQL Performance ML [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 9:09 PM Subject: Re: [PERFORM] [HACKERS] Realtime VACUUM, was: performance of insert/delete/update Good ideas. I think the master solution is to hook the statistics daemon information into an automatic vacuum that could _know_ which tables need attention. -- - Curtis Faith wrote: tom lane wrote: Sure, it's just shuffling the housekeeping work from one place to another. The thing that I like about Postgres' approach is that we put the housekeeping in a background task (VACUUM) rather than in the critical path of foreground transaction commit. Thinking with my marketing hat on, MVCC would be a much bigger win if VACUUM was not required (or was done automagically). The need for periodic VACUUM just gives ammunition to the PostgreSQL opponents who can claim we are deferring work but that it amounts to the same thing. A fully automatic background VACUUM will significantly reduce but will not eliminate this perceived weakness. However, it always seemed to me there should be some way to reuse the space more dynamically and quickly than a background VACUUM thereby reducing the percentage of tuples that are expired in heavy update cases. If only a very tiny number of tuples on the disk are expired this will reduce the aggregate performance/space penalty of MVCC into insignificance for the majority of uses. Couldn't we reuse tuple and index space as soon as there are no transactions that depend on the old tuple or index values. I have imagined that this was always part of the long-term master plan. Couldn't we keep a list of dead tuples in shared memory and look in the list first when deciding where to place new values for inserts or updates so we don't have to rely on VACUUM (even a background one)? If there are expired tuple slots in the list these would be used before allocating a new slot from the tuple heap. The only issue is determining the lowest transaction ID for in-process transactions which seems relatively easy to do (if it's not already done somewhere). In the normal shutdown and startup case, a tuple VACUUM could be performed automatically. This would normally be very fast since there would not be many tuples in the list. Index slots would be handled differently since these cannot be substituted one for another. However, these could be recovered as part of every index page update. Pages would be scanned before being written and any expired slots that had transaction ID's lower than the lowest active slot would be removed. This could be done for non-leaf pages as well and would result in only reorganizing a page that is already going to be written thereby not adding much to the overall work. I don't think that internal pages that contain pointers to values in nodes further down the tree that are no longer in the leaf nodes because of this partial expired entry elimination will cause a problem since searches and scans will still work fine. Does VACUUM do something that could not be handled in this realtime manner? - Curtis ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] Realtime VACUUM, was: performance of insert/delete/update
Or just reorg. Am Mittwoch, 27. November 2002 15:02 schrieb Nicolai Tufar: I always wandered if VACUUM is the right name for the porcess. Now, when PostgreSQL is actively challenging in Enterprise space, it might be a good idea to give it a more enterprise-like name. Try to think how it is looking for an outside person to see us, database professionals hold lenghty discussions about the ways we vacuum a database. Why should you need to vacuum a database? Is it dirty? In my personal opinion, something like space reclaiming daemon, free-list organizer, tuple recyle job or segment coalesce process would sound more business-like . Regards, Nick - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Curtis Faith [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED]; Ron Johnson [EMAIL PROTECTED]; PgSQL Performance ML [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 9:09 PM Subject: Re: [PERFORM] [HACKERS] Realtime VACUUM, was: performance of insert/delete/update Good ideas. I think the master solution is to hook the statistics daemon information into an automatic vacuum that could _know_ which tables need attention. - - - Curtis Faith wrote: tom lane wrote: Sure, it's just shuffling the housekeeping work from one place to another. The thing that I like about Postgres' approach is that we put the housekeeping in a background task (VACUUM) rather than in the critical path of foreground transaction commit. Thinking with my marketing hat on, MVCC would be a much bigger win if VACUUM was not required (or was done automagically). The need for periodic VACUUM just gives ammunition to the PostgreSQL opponents who can claim we are deferring work but that it amounts to the same thing. A fully automatic background VACUUM will significantly reduce but will not eliminate this perceived weakness. However, it always seemed to me there should be some way to reuse the space more dynamically and quickly than a background VACUUM thereby reducing the percentage of tuples that are expired in heavy update cases. If only a very tiny number of tuples on the disk are expired this will reduce the aggregate performance/space penalty of MVCC into insignificance for the majority of uses. Couldn't we reuse tuple and index space as soon as there are no transactions that depend on the old tuple or index values. I have imagined that this was always part of the long-term master plan. Couldn't we keep a list of dead tuples in shared memory and look in the list first when deciding where to place new values for inserts or updates so we don't have to rely on VACUUM (even a background one)? If there are expired tuple slots in the list these would be used before allocating a new slot from the tuple heap. The only issue is determining the lowest transaction ID for in-process transactions which seems relatively easy to do (if it's not already done somewhere). In the normal shutdown and startup case, a tuple VACUUM could be performed automatically. This would normally be very fast since there would not be many tuples in the list. Index slots would be handled differently since these cannot be substituted one for another. However, these could be recovered as part of every index page update. Pages would be scanned before being written and any expired slots that had transaction ID's lower than the lowest active slot would be removed. This could be done for non-leaf pages as well and would result in only reorganizing a page that is already going to be written thereby not adding much to the overall work. I don't think that internal pages that contain pointers to values in nodes further down the tree that are no longer in the leaf nodes because of this partial expired entry elimination will cause a problem since searches and scans will still work fine. Does VACUUM do something that could not be handled in this realtime manner? - Curtis ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Dr. Eckhardt +
Re: [HACKERS] next value expression
On Wed, 2002-11-27 at 10:29, Manfred Koizar wrote: By accident I stumbled across the following paragraph in the August 2002 draft of SQL 2003: If there are multiple instances of next value expressions specifying the same sequence generator within a single SQL-statement, all those instances return the same value for a given row processed by that SQL-statement. Is this of any relevance to PG's nextval()? Somewhat -- SQL2003 defines sequence generators that are pretty much identical in functionality to PostgreSQL's sequences, although the syntax is a bit different. I submitted a patch for 7.4 that adjusts the CREATE SEQUENCE grammar to match SQL2003's CREATE SEQUENCE a little more closely, but there's a bunch more work that can be done, if we want to be fully SQL-compliant. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(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: [PERFORM] [HACKERS] Realtime VACUUM, was: performance of
In a similar vein, setting the way back machine to the mid 80s when I was in the USAF and teaching the computer subsystem of the A-10 INS test station, we had old reclaimed Sperry 1650 computers (the precursor to the 1750) that had come out of the 1960 era fire control systems on battleships like the Missouri and what not. When the OS went south, it would put up a message that said System Crash at address XXX or something very similar. A colonol saw that and insisted that the folks who wrote the OS change the word crash, since in the Air Force crash (as in plane crash) had such bad connotations. So, it got changed to System Fault at address x For the first month or two that happened, folks would ask what a system fault was and what to do with it. They new that a crash would need the machine to be power cycled but didn't know what to do with a system fault. Shortly after that, the manual for the test station had a little section added to it that basically said a system fault was a crash. :-) On Wed, 27 Nov 2002, Jim Beckstrom wrote: Just for the humor of it, as well as to confirm Nick's perspective, years ago on our inhouse developed Burroughs mainframe dbms, we had a process called garbage collect. Nicolai Tufar wrote: I always wandered if VACUUM is the right name for the porcess. Now, when PostgreSQL is actively challenging in Enterprise space, it might be a good idea to give it a more enterprise-like name. Try to think how it is looking for an outside person to see us, database professionals hold lenghty discussions about the ways we vacuum a database. Why should you need to vacuum a database? Is it dirty? In my personal opinion, something like space reclaiming daemon, free-list organizer, tuple recyle job or segment coalesce process would sound more business-like . ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] next value expression
Neil Conway [EMAIL PROTECTED] writes: On Wed, 2002-11-27 at 10:29, Manfred Koizar wrote: By accident I stumbled across the following paragraph in the August 2002 draft of SQL 2003: If there are multiple instances of next value expressions specifying the same sequence generator within a single SQL-statement, all those instances return the same value for a given row processed by that SQL-statement. Is this of any relevance to PG's nextval()? Somewhat -- SQL2003 defines sequence generators that are pretty much identical in functionality to PostgreSQL's sequences, although the syntax is a bit different. I would think his point is that the above paragraph specifies behavior that is very definitely NOT like Postgres'. I submitted a patch for 7.4 that adjusts the CREATE SEQUENCE grammar to match SQL2003's CREATE SEQUENCE a little more closely, Did we apply it? I'm inclined not to, until we nail down the semantic implications a little more. Conforming to the spec on syntax when we don't on semantics strikes me as a bad idea. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] next value expression
Neil Conway [EMAIL PROTECTED] writes: There's already a need to reform the way in which the next value of a sequence is produced (nextval() makes it difficult to get the dependancy information right); would it be a good idea to change it to be completely SQL compatible at the same time? What do you consider completely SQL compatible here? In particular, what is a statement? My initial reaction to this part of the SQL draft is that it's broken. Consider plpgsql functions invoked within an interactive statement --- if they invoke nextval() should it fail to increment across repeated attempts? Does your answer change if the functions are invoked as triggers, rather than directly in the text of the statement? How about queries inserted by rule rewriting; are those separate statements for this purpose? In any of these contexts I think you can construct examples that would favor either answer. ISTM that we will have all the same issues with this that we had with the question of when now() should increment... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib/ltree patches
Dan Langille wrote:
I have been looking at contrib/ltree in the PostgreSQL repository. I've
modified the code to allow / as a node delimiter instead of . which is the
default.
What is the reason for changing delimiter?
Below are the patches to make this change. I have also moved the
delimiter to a DEFINE so that other customizations are easily done. This
is a work in progress.
It's good.
My thanks to DarbyD for assistance.
cheers
--- ltree.h.orig Tue Nov 26 18:57:58 2002
+++ ltree.h Tue Nov 26 20:16:40 2002
@@ -6,6 +6,8 @@
#include utils/palloc.h
#include utils/builtins.h
+#define NODE_DELIMITER '/'
+
typedef struct
{
uint8 len;
@@ -88,7 +90,7 @@
#ifndef abs
#define abs(a) ((a) (0) ? -(a) : (a))
#endif
-#define ISALNUM(x) ( isalnum((unsigned int)(x)) || (x) == '_' )
+#define ISALNUM(x) ( isalnum((unsigned int)(x)) || (x) == '_' || (x) == NODE_DELIMITER )
It seems to me that it's mistake. ISALNUM shoud define correct character in
name of node (level). Try to test
with incorrect ltree value 'a..b'.
--
Teodor Sigaev
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [INTERFACES] Interface update for 7.3
Bruce Momjian [EMAIL PROTECTED] writes: I am working with David Wheeler on DBD:pg and hope to have a release packaged up tomorrow. Bruce, David, I'm updating all the test scripts to properly use the standard DBI testing env vars, e.g. DBI_DSN, DBI_USER, DBI_PASS, and to use Test::More. I've finished and committed half of them, I'll let you know when I'm done. Cheers, jas. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [INTERFACES] Interface update for 7.3
Jason E. Stewart [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] writes: I am working with David Wheeler on DBD:pg and hope to have a release packaged up tomorrow. Bruce, David, I'm updating all the test scripts to properly use the standard DBI testing env vars, e.g. DBI_DSN, DBI_USER, DBI_PASS, and to use Test::More. I've finished and committed half of them, I'll let you know when I'm done. Ok, all done. All tests now use standard DBI vars and Test::More. All tests pass. David, could you take a look at 11quoting.t? I'm pretty sure you wrote this test (didn't you?). I converted the array of tests to a hash table so that I could give them names. However, I could only give the tests names like 'one', 'two', etc. Perhaps you can think of something more descriptive. The names are a really nice feature of Test::More. When you run the test files individually it prints out: 1..8 ok 1 - connect with transaction ok 2 - three: \ - expected '\134' got '\134' ok 3 - five: \'?: - expected '\134\047?:' got '\134\047?:' ok 4 - one: ' - expected '\047' got '\047' ok 5 - two: '' - expected '\047\047' got '\047\047' ok 6 - four: \' - expected '\134\047' got '\134\047' ok 7 - SQL_BINARY ok 8 - disconnect So that you can find a test by name when it fails. Cheers, jas. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [PERFORM] [HACKERS] Realtime VACUUM, was: performance of insert/delete/update
-Original Message- From: Nicolai Tufar [mailto:[EMAIL PROTECTED]] Sent: 27 November 2002 14:02 To: [EMAIL PROTECTED]; PgSQL Performance ML Subject: Re: [PERFORM] [HACKERS] Realtime VACUUM, was: performance of insert/delete/update I always wandered if VACUUM is the right name for the porcess. Now, when PostgreSQL is actively challenging in Enterprise space, it might be a good idea to give it a more enterprise-like name. Try to think how it is looking for an outside person to see us, database professionals hold lenghty discussions about the ways we vacuum a database. Why should you need to vacuum a database? Is it dirty? In my personal opinion, something like space reclaiming daemon, free-list organizer, tuple recyle job or segment coalesce process would sound more business-like . As inspired by the SQL Server Enterprise Manager I've just been swearing at: Database Optimizer Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [mail] Re: [HACKERS] Native Win32 sources
I've posted an Email to the list as to why I'm avoiding a move to linux (cost of training -v- cost of database (free) + money saved from recycling current DB machines). My experience with PostgreSQL has always been good, and I beleive that we can test any potential bugs that we may beleive are in the database by running our app in our the QA environment against the Linux version of the database (to test platform specifics), and then the database version in production (to test version specifics). I'm quite happy to spend the time doing this to gain the cost benefit of freeing up the extra machines my developers currently have. Al. - Original Message - From: Shridhar Daithankar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 27, 2002 8:41 AM Subject: Re: [mail] Re: [HACKERS] Native Win32 sources On 27 Nov 2002 at 8:21, Al Sutton wrote: The problem I have with VMWare is that for the cost of a licence plus the additional hardware on the box running it (CPU power, RAM, etc.) I can buy a second cheap machine, using VMWare doesn't appear to save me my biggest overheads of training staff on Unix and cost of equipment (software and hardware). I've been looking at Bochs, but 1.4.1 wasn't stable enough to install RedHat, PostgreSQL, etc. reliably. I have been reading this thread all along and I have some suggestions. They are not any different than already made but just summerising them. 1) Move to linux. You can put a second linux box with postgresql on it. Anyway your app. is on windows so it does not make much of a difference because developers will be accessing database from their machines. Secondly if you buy a good enough mid-range machine, say with 40GB SCSI with 2G of RAM, each developer can develop on his/her own database. In case of performance testing, you can schedule it just like any other shared resource. It is very easy to run multiple isolated postgresql instances on a linux machine. Just change the port number and use a separate data directory. That's it.. Getting people familiarized with unix/.linux upto a point where they can use their own database is matter of half a day. 2) Do not bank too much on windows port yet. Will all respect to people developing native windows port of postgresql, unless you know the correct/stable behaviour of postgresql on unix, you might end up in a situation where you don't know whether a bug/problem is in postgresql or with postgresql/windows. I would not recommend getting into such a situation. Your contribution is always welcome in any branch but IMO it is not worth at the risk of slipping your own product development. Believe me, moving to linux might seem scary at first but it is no more than couple of days matter to get a box to play around. Untill you need a good machine for performance tests, a simple 512MB machie with enough disk would be sufficient for any development among the group.. HTH Bye Shridhar -- My father taught me three things: (1) Never mix whiskey with anything but water. (2) Never try to draw to an inside straight. (3) Never discuss business with anyone who refuses to give his name. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Fw: PostgreSQL 7.3 Platform Testing
Solaris tests from Martin. Sorry for taking so long to run the tests, but I was out sick on Monday and yesterday was busy in a meeting for most of the day. The results are the same as for RC1 - the geometry test fails with the last decimal digit being off by one. I have attached the results. The platform: Intel 2.4Ghz Pentium 4 with 1GB of memory running Solaris 8 GCC 3.2 with gas and gnu ld Martin regression.diffs Description: Binary data regression.out Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Fw: PostgreSQL 7.3 Platform Testing
More on solaris tests from Martin. I just looked a bit further and there is a regress/expected/geometry-solaris-i386.out file which diffs cleanly with the regress/results/geometry.out file I generated. Perhaps the regression test isn't comparing with the right file. Martin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] nested transactions
On Fri, 22 Nov 2002 00:32:46 -0500 (EST), Bruce Momjian [EMAIL PROTECTED] wrote: I am going to work on nested transactions for 7.4. [...] And finally, I must abort tuple changes made by the aborted subtransaction. One way of doing that is to keep all relation id's modified by the transaction, and do a sequential scan of the tables on abort, changing the transaction id's to a fixed aborted transaction id. However, this could be slow. (We could store tids if only a few rows are updated by a subtransaction. That would speed it up considerably.) Depends on your definition of few. I don't expect problems for up to several thousand tids. If there are more modified tuples, we could first reduce the list to page numbers, before finally falling back to table scans. Another idea is to use new transaction id's for the subtransactions, and [...] would increase the clog size per transaction from 2 bits to 4 bytes (two bits for status, 30 bits for offset to parent). Nice idea, this 30 bit offset. But one could argue that increased clog size even hurts users who don't use nested transactions at all. If parent/child dependency is kept separate from status bits (in pg_subtrans files), additional I/O cost is only paid if subtransactions are actually used. New status bits (XMIN_IS_SUB, XMAX_IS_SUB) in tuple headers can avoid unnecessary parent xid lookups. I also thought of subtransaction xids in tuple headers as short lived information. Under certain conditions they can be replaced with the parent xid as soon as the parent transaction has finished. I proposed this to be done on the next tuple access just like we set committed/aborted flags now, though I'm not sure anymore that it is safe to do this. Old pg_subtrans files can be removed by VACUUM. One more difference between the two proposals: The former (locally remember modified tuples) can be used for recovery after a failed command. The latter (subtrans tree) can only help, if we give a new xid to each command, which I'm sure we don't want to do. Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [INTERFACES] Interface update for 7.3
On Wednesday, November 27, 2002, at 02:46 AM, Jason E. Stewart wrote: David, could you take a look at 11quoting.t? I'm pretty sure you wrote this test (didn't you?). I converted the array of tests to a hash table so that I could give them names. However, I could only give the tests names like 'one', 'two', etc. Perhaps you can think of something more descriptive. No, I didn't write it. I just added the test to make sure that SQL_BINARY threw an exception. David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(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] contrib/ltree patches
On 27 Nov 2002 at 12:16, Teodor Sigaev wrote: Dan Langille wrote: I have been looking at contrib/ltree in the PostgreSQL repository. I've modified the code to allow / as a node delimiter instead of . which is the default. What is the reason for changing delimiter? My tree represents a file system. Here are some entries: # select id, pathname from element_pathnames order by pathname; 77024 | doc/de_DE.ISO8859-1 77028 | doc/de_DE.ISO8859-1/books 84590 | doc/de_DE.ISO8859-1/books/Makefile.inc 77029 | doc/de_DE.ISO8859-1/books/faq 84591 | doc/de_DE.ISO8859-1/books/faq/Makefile 77030 | doc/de_DE.ISO8859-1/books/faq/book.sgml 77691 | doc/de_DE.ISO8859-1/books/handbook 77704 | doc/de_DE.ISO8859-1/books/handbook/Makefile 110592 | doc/de_DE.ISO8859-1/books/handbook/advanced-networking Below are the patches to make this change. I have also moved the delimiter to a DEFINE so that other customizations are easily done. This is a work in progress. It's good. Thank you. More patches will follow as I get closer to my objective. -#define ISALNUM(x) ( isalnum((unsigned int)(x)) || (x) == '_' ) +#define ISALNUM(x) ( isalnum((unsigned int)(x)) || (x) == '_' || +#(x) == NODE_DELIMITER ) It seems to me that it's mistake. ISALNUM shoud define correct character in name of node (level). Try to test with incorrect ltree value 'a..b'. I just did some simple tests and I see what you mean: ltree_test=# select * from tree; id | pathname +-- 1 | /ports 2 | ports/security 2 | ports//security 2 | /ports//security 2 | a..b (5 rows) Then I removed NODE_DELIMITER from ISALNUM and tried again: ltree_test=# insert into tree values (2, '/ports//security'); ERROR: Syntax error in position 0 near '/' ltree_test=# insert into tree values (2, 'ports//security'); ERROR: Syntax error in position 6 near '/' ltree_test=# insert into tree values (2, 'ports/security'); INSERT 29955201 1 ltree_test=# insert into tree values (2, 'ports/security/'); ERROR: Unexpected end of line ltree_test=# insert into tree values (2, 'ports/security/things'); INSERT 29955202 1 ltree_test=# select * from tree; id | pathname +--- 1 | /ports 2 | ports/security 2 | ports//security 2 | /ports//security 2 | a..b 2 | ports/security 2 | ports/security/things (7 rows) Removing NODE_DELIMITER from ISALNUM makes sense. Thank you. Here is the reason why NODE_DELIMITER was added. My initial data sample was of the form /usr/local/ (i.e. it started with a NODE_DELIMITER). I have since changed my data so it does not start with a leading / because queries were not working. Based upon the sample data I was using (approximately 120,000 nodes as taken from a real file system), I had to change ISALNUM as I went along. Here is the current definition for ISALNUM: #define ISALNUM(x) ( isalnum((unsigned int)(x)) || (x) == '_' || (x) == '-' || (x) == '.' || (x) == '+' || (x) == ':' || (x) == '~' || (x) == '%' || (x) == ',' || (x) == '#') Given that I am trying to allow any valid filename, I think ISALNUM needs to allow any ASCII character. I also think I will need to modify the parsing within lquery_in to allow escaping of characters it recognizes but which may be part of a file name (e.g. :%~ may be part of a file name, but these are special characters to lquery_in). That I think will be the biggest change. Thank you for your interest and help. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] FreeBSD, Linux: select, select count(*) performance
On Wed, 27 Nov 2002, Tom Lane wrote: Achilleus Mantzios [EMAIL PROTECTED] writes: Linux q1 dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon; NOTICE: QUERY PLAN: Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual time=338.17..338.17 rows=1 loops=1) - Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual time=0.01..225.73 rows=108095 loops=1) Total runtime: 338.25 msec Linux q2 dynacom=# EXPLAIN ANALYZE SELECT * from noon; NOTICE: QUERY PLAN: Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1960) (actual time=1.22..67909.31 rows=108095 loops=1) Total runtime: 68005.96 msec You didn't say what was *in* the table, exactly ... but I'm betting there are a lot of toasted columns, and that the extra runtime represents the time to fetch (and perhaps decompress) the TOAST entries. Are there any reason to fetch (and perhaps decompress) the TOAST entries just to count(*) without any WHERE clause ? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Planning for improved versions of IN/NOT IN
I've been thinking about how to improve the performance of queries using WHERE x IN (subselect) and WHERE x NOT IN (subselect). In the existing implementation, the subquery result is rescanned to look for a match to x each time the WHERE clause is executed; this essentially makes it work like a nestloop join of the stupidest variety. (We do stick a Materialize node atop the subselect if it looks complicated, but that's not a big help in typical cases.) I've thought of three alternative implementations that would perform better in various scenarios. Each would be relatively simple to implement; the problem I'm having is figuring out how to get the planner to choose the best one. The alternatives are basically: 1. Add DISTINCT to the subquery, pull it up into the rangetable (as a subquery RT entry), and change the = SUBLINK WHERE clause to a simple = against the subquery output var(s). Essentially this transforms SELECT ... FROM foo WHERE foo.x IN (SELECT y FROM ...) to SELECT ... FROM foo, (SELECT DISTINCT y FROM ...) ss WHERE foo.x = ss.y This is not useful for NOT IN, and there are a few restrictions even for IN (no correlation variables in subquery, no LIMIT, maybe others)? Also I think it would only work correctly for IN appearing at the top level of WHERE, though that might be too conservative. The main case where it could be a win is where the subquery is expected to produce relatively few output rows, so we could run it as the outer side of some join plan. In particular, when x is an indexed column in a large table, fetching x as the inner side of an indexscan nestloop would be much better than scanning the whole of the outer table. 2. Hash-based implementations: read the subquery once, load its values into an in-memory hashtable (discarding duplicates), and then probe the hashtable for each execution of the WHERE clause. This works for both IN and NOT IN, though we still need an uncorrelated subquery (else the hashtable can't be reused from row to row). This probably wins for a moderate number of rows in the subquery result (not too many to hash in memory) and a fairly large number of outer rows (else building the hashtable is not repaid). 3. Inner indexscan: essentially, automatically do the IN-to-EXISTS transform that's presently recommended by the FAQ. This wins if the subquery result is large but pushing down an equality condition makes it cheap, and there aren't too many outer rows. There may also be cases where the existing implementation is still the best, or perhaps is the only usable one. The difficulty is that it's not clear how to choose one of these four ways, short of planning the *entire* query from scratch all four ways :-(. This seems pretty grim. Approaches #2 and #3 could be handled as local transformations of the WHERE clause, but we couldn't choose which to use very well if we don't yet know how many outer rows the WHERE clause will be executed for. Approach #1 is really planning a completely different query --- one with an extra FROM-item --- and there's not going to be all that much commonality in the computations, unless we restrict where the added FROM-item can be joined to the others, which'd more or less defeat the purpose. Anyone see a way around this difficulty? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Boolean casting in 7.3 - changed?
Does this mean that in the future '342' may not be valid as an insert into a numeric field and that we should be using 342 instead? On Wednesday 27 November 2002 05:07 pm, (Via wrote: Ian Barwick writes: Casting integers to boolean (for example, 0::bool) is no longer allowed, use '0'::bool instead. This advice would probably only cause more confusion, because we are now moving into the direction that character strings are no longer acceptable as numeric data. Note that x 0 is also a perfectly good way to convert integers to booleans, and a more portable one at that. Finally, you can always create your own cast. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] contrib/ltree patches
What is the reason for changing delimiter? My tree represents a file system. Here are some entries: Below are the patches to make this change. I have also moved the delimiter to a DEFINE so that other customizations are easily done. This is a work in progress. It's good. #define ISALNUM(x) ( isalnum((unsigned int)(x)) || (x) == '_' || (x) == '-' || (x) == '.' || (x) == '+' || (x) == ':' || (x) == '~' || (x) == '%' || (x) == ',' || (x) == '#') Given that I am trying to allow any valid filename, I think ISALNUM needs to allow any ASCII character. I also think I will need to modify the parsing within lquery_in to allow escaping of characters it recognizes but which may be part of a file name (e.g. :%~ may be part of a file name, but these are special characters to lquery_in). That I think will be the biggest change. Ok, I think it's a good extension. Let you prepare cumulative patch. Nevertheless, we have no chance to insert this to 7.3 release :(. Only for 7.3.1 or even 7.4. -- Teodor Sigaev [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] FreeBSD, Linux: select, select count(*) performance
Oleg Bartunov [EMAIL PROTECTED] writes: Are there any reason to fetch (and perhaps decompress) the TOAST entries just to count(*) without any WHERE clause ? It doesn't. That was my point... regards, tom lane ---(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] [INTERFACES] ANNOUNCE: DBD::Pg 1.20
Hackers, David Wheeler and I have released DBD:pg 1.20 to CPAN and
gborg. That completes the last moved interface I am involved with.
---
David Wheeler wrote:
The uploaded file
DBD-Pg-1.20.tar.gz
has entered CPAN as
file: $CPAN/authors/id/D/DW/DWHEELER/DBD-Pg-1.20.tar.gz
size: 75821 bytes
md5: ea9e217321fb62515b3723a86ecbfcdf
This is the first release under new management at GBorg. Here's a list
of changes in the new version, along with attributions:
1.20 Wed Nov 27 16:19:26 2002
- Maintenance transferred to GBorg,
http://gborg.postgresql.org/project/dbdpg/projdisplay.php.
Incremented
version number to reflect new management. [Bruce Momjian]
- README cleaned up. [Bruce Momjian]
- Added t/15funct.t, a series of tests that determine if the meta
data
is working. [Thomas Lowery]
- Added implementations of column_info() and table_info(), and
primary_key_info(). [Thomas Lowery]
- The POD formatting was cleaned up. [David Wheeler]
- The preparser was updated to better handle escaped characters.
[Rudy
Lippan]
- Removed redundant use of strlen() in pg_error() (Jason E.
Stewart).
- Test suite cleaned up, converted to use Test::More, and updated
to use
standard DBI environment variables for connecting to a test
database.
[Jason E. Stewart]
- Added eg/lotest.pl as a demonstration of using large objects in
buffers
rather than files. Contributed by Garth Webb.
- Added LISTEN/NOTIFY functionality. Congributed by Alex Pilosov.
- Added constants for common PostgreSQL data types, plus simple
tests to
make sure that they work. These are exportable via use DBD::Pg
qw(:pg_types);. [David Wheeler]
- Deprecatated the undocumented (and invalid) use of SQL_BINARY in
bind_param() and documented the correct approach: bind_param($num,
$val { pg_type = PG_BYTEA });. Use of SQL_BINARY in bind_param() will
now issue a warning if $h-{Warn} is true. [David Wheeler]
- Removed invalid (and broken) support for SQL_BINARY in quote().
[David
Wheeler]
- Added App::Info::RDBMS::PostgreSQL to the distribution (but it
won't
be installed) to help Makefile.PL find the PostgreSQL include
and
library files. [David Wheeler]
- Fixed compile-time warnings. [David Wheeler and Jason E.
Stewart]
Enjoy!
David
--
David Wheeler AIM: dwTheory
[EMAIL PROTECTED] ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: [EMAIL PROTECTED]
---(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
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] v7.3 Packaged ...
Okay folks, I just packaged her up, and fixed up the ftp site so that everything points properly to it ... this way the mirrors get a chance to pick it all up over night ... I will be sending out the press release first thing in the morning ... Please take a look around, and let me know if I've missed anything ... ---(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] Planning for improved versions of IN/NOT IN
The difficulty is that it's not clear how to choose one of these four ways, short of planning the *entire* query from scratch all four ways :-(. This seems pretty grim. Approaches #2 and #3 could be handled as local transformations of the WHERE clause, but we couldn't choose which to use very well if we don't yet know how many outer rows the WHERE clause will be executed for. Approach #1 is really planning a completely different query --- one with an extra FROM-item --- and there's not going to be all that much commonality in the computations, unless we restrict where the added FROM-item can be joined to the others, which'd more or less defeat the purpose. What about in the case of a scalar subquery eg. SELECT x IN (1,2,3,4,54,56,6), when there maybe hundreds of scalars? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib/ltree patches
On 27 Nov 2002 at 19:55, Teodor Sigaev wrote: Ok, I think it's a good extension. Let you prepare cumulative patch. Nevertheless, we have no chance to insert this to 7.3 release :(. Only for 7.3.1 or even 7.4. Thanks. As for the 7.3 release, yes, it would be nice, but that was not my goal. :) -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [spam] Re: [mail] Re: [HACKERS] Native Win32 sources
Hannu, Using a Win32 platform will allow them to perform relative metrics. I'm not looking for a statement saying things are x per cent faster than production, I'm looking for reproducable evidence that an improvement offers y per cent faster performance than another configuration on the same platform. The QA environment is designed to do final testing and compiling definitive metrics against production systems, what I'm looking for is an easy method of allowing developers to see the relative change on performance for a given change on the code base. I'm fully aware that they'll still have to use the config files of PostgreSQL on a Win32 port, but the ability to edit the config files, modify sql dumps to load data into new schema, transfer files between themselves, and perform day to day tasks such as reading Email and MS-Word formatted documents sent to us using tools that they are currently familiar with is a big plus for me. The bottom line is I can spend money training my developers on Linux and push project deadlines back until they become familiar with it, or I can obtain a free database on their native platform and reduce the number of machines needed per developer as well as making the current DB machines available as the main machine for new staff. The latter makes the most sense in the profit based business environment which I'm in. Al. - Original Message - From: Hannu Krosing [EMAIL PROTECTED] To: Al Sutton [EMAIL PROTECTED] Cc: scott.marlowe [EMAIL PROTECTED]; bpalmer [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 27, 2002 10:54 AM Subject: [spam] Re: [mail] Re: [HACKERS] Native Win32 sources On Wed, 2002-11-27 at 08:21, Al Sutton wrote: The problem I have with VMWare is that for the cost of a licence plus the additional hardware on the box running it (CPU power, RAM, etc.) I can buy a second cheap machine, using VMWare doesn't appear to save me my biggest overheads of training staff on Unix and cost of equipment (software and hardware). I've been looking at Bochs, but 1.4.1 wasn't stable enough to install RedHat, PostgreSQL, etc. reliably. The database in question holds order information for over 2000 other companies, and is growing daily. There is also a requirement to keep the data indefinatley. The developers are developing two things; 1- Providing an interface for the companies employees to update customer information and answer customer queries. 2- Providing an area for merchants to log into that allows them to generate some standardised reports over the order data, change passwords, setup repeated payment system, etc. Developing these solutions does include the possibilities of modify the database schema, the configuration of the database, and the datatypes used to represent the data (e.g. representing encyrpted data as a Base64 string or blob), and therefore the developers may need to make fundamental changes to the database and perform metrics on how they have affected performance. If you need metrics and the production runs on some kind of unix, you should definitely do the measuring on unix as well. A developers machine with different os and other db tuning parameters may give you _very_ different results from the real deployment system. Also, porting postgres to win32 wont magically make it into MS Access - most DB management tasks will be exactly the same. If your developer are afraid of command line, give them some graphical or web tool for managing the db. If they dont want to manage linux, then just set it up once and don't give them the root pwd ;) -- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fw: PostgreSQL 7.3 Platform Testing
Tom commmited fixes for i386 Solaris today. I assume it will be in 7.3.1. --- Christopher Kings-Lynne wrote: Solaris tests from Martin. Sorry for taking so long to run the tests, but I was out sick on Monday and yesterday was busy in a meeting for most of the day. The results are the same as for RC1 - the geometry test fails with the last decimal digit being off by one. I have attached the results. The platform: Intel 2.4Ghz Pentium 4 with 1GB of memory running Solaris 8 GCC 3.2 with gas and gnu ld Martin [ Attachment, skipping... ] [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Planning for improved versions of IN/NOT IN
Christopher Kings-Lynne [EMAIL PROTECTED] writes: What about in the case of a scalar subquery eg. SELECT x IN (1,2,3,4,54,56,6), when there maybe hundreds of scalars? Unrelated to my present problem. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fw: PostgreSQL 7.3 Platform Testing
Bruce Momjian [EMAIL PROTECTED] writes: Tom commmited fixes for i386 Solaris today. I assume it will be in 7.3.1. 7.3, since AFAICT Marc hasn't wrapped same yet ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Boolean casting in 7.3 - changed?
Ian Barwick writes: Casting integers to boolean (for example, 0::bool) is no longer allowed, use '0'::bool instead. This advice would probably only cause more confusion, because we are now moving into the direction that character strings are no longer acceptable as numeric data. Note that x 0 is also a perfectly good way to convert integers to booleans, and a more portable one at that. Finally, you can always create your own cast. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] How shall I start postgres on NT
28:11:2002 Hi, I am not able to install Postgres on NT. If anybody has installed postgres on NT, please give me steps to that. I have read documentation about it, but unable to do that. I want to know, how shall I start building source code on Windows NT. I have VC++ installed and while I tried to compile main.c file header files are not there in source code e.g. pwd.h etc. As these files are from unix env, can I use them directly? Catch all the cricket action. Download Yahoo! Score tracker
Re: [spam] Re: [mail] Re: [HACKERS] Native Win32 sources
On Wed, 27 Nov 2002, Al Sutton wrote: Hannu, Using a Win32 platform will allow them to perform relative metrics. I'm not looking for a statement saying things are x per cent faster than production, I'm looking for reproducable evidence that an improvement offers y per cent faster performance than another configuration on the same platform. So, does cygwin offer any win? I know it's still unix on windows but it's the bare minimum of unix, and it is easy to create one image of an install and copy it around onto other boxes in a semi-ready to go format. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Boolean casting in 7.3 - changed?
Peter Eisentraut [EMAIL PROTECTED] writes: Ian Barwick writes: Casting integers to boolean (for example, 0::bool) is no longer allowed, use '0'::bool instead. This advice would probably only cause more confusion, because we are now moving into the direction that character strings are no longer acceptable as numeric data. Yes, phrased that way it's just misleading. We do not and did not have a general int-to-bool cast (though it may be reasonable to add one, now that we could mark it explicit-only). The case that worked in 7.2 and before was only for numeric-looking *literals* being cast to bool (or any other type for that matter) --- parser_typecast_constant would essentially act as though the literal had quotes around it, whether it actually did or not. Thus in the old code, the validity of, say, 42::bool would depend on whether bool's input converter would accept the string '42'. In the new code, 42 is taken to be an int4 constant and the validity of the expression depends on whether there is an int4-to-bool cast. 7.2: regression=# select 42::bool; ERROR: Bad boolean external representation '42' Current: regression=# select 42::bool; ERROR: Cannot cast type integer to boolean regards, tom lane ---(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] v7.3 Packaged ...
Great! This represents two major achievements for our release process. First, we got the beta out in a relatively short period, and second, we started development on the next release _before_ we finished the previous one. Both are major achievements. --- Marc G. Fournier wrote: Okay folks, I just packaged her up, and fixed up the ftp site so that everything points properly to it ... this way the mirrors get a chance to pick it all up over night ... I will be sending out the press release first thing in the morning ... Please take a look around, and let me know if I've missed anything ... ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] nested transactions
Manfred Koizar wrote: On Fri, 22 Nov 2002 00:32:46 -0500 (EST), Bruce Momjian [EMAIL PROTECTED] wrote: I am going to work on nested transactions for 7.4. [...] And finally, I must abort tuple changes made by the aborted subtransaction. One way of doing that is to keep all relation id's modified by the transaction, and do a sequential scan of the tables on abort, changing the transaction id's to a fixed aborted transaction id. However, this could be slow. (We could store tids if only a few rows are updated by a subtransaction. That would speed it up considerably.) Depends on your definition of few. I don't expect problems for up to several thousand tids. If there are more modified tuples, we could first reduce the list to page numbers, before finally falling back to table scans. Yes, and the key point is that those are kept only in the backend local memory, so clearly thousands are possible. The outer transaction takes care of all the ACID issues. Another idea is to use new transaction id's for the subtransactions, and [...] would increase the clog size per transaction from 2 bits to 4 bytes (two bits for status, 30 bits for offset to parent). Nice idea, this 30 bit offset. But one could argue that increased clog size even hurts users who don't use nested transactions at all. If parent/child dependency is kept separate from status bits (in pg_subtrans files), additional I/O cost is only paid if subtransactions are actually used. New status bits (XMIN_IS_SUB, XMAX_IS_SUB) in tuple headers can avoid unnecessary parent xid lookups. I also thought of subtransaction xids in tuple headers as short lived information. Under certain conditions they can be replaced with the parent xid as soon as the parent transaction has finished. I proposed this to be done on the next tuple access just like we set committed/aborted flags now, though I'm not sure anymore that it is safe to do this. Old pg_subtrans files can be removed by VACUUM. One more difference between the two proposals: The former (locally remember modified tuples) can be used for recovery after a failed command. The latter (subtrans tree) can only help, if we give a new xid to each command, which I'm sure we don't want to do. The interesting issue is that if we could set the commit/abort bits all at the same time, we could have the parent/child dependency local to the backend --- other backends don't need to know the parent, only the status of the (subtransaction's) xid, and they need to see all those xid's committed at the same time. You could store the backend slot id in pg_clog rather than the parent xid and look up the status of the outer xid for that backend slot. That would allow you to use 2 bytes, with a max of 16k backends. The problem is that on a crash, the pg_clog points to invalid slots --- it would probably have to be cleaned up on startup. But still, you have an interesting idea of just setting the bit to be I am a child. The trick is allowing backends to figure out who's child you are. We could store this somehow in shared memory, but that is finite and there can be lots of xid's for a backend using subtransactions. I still think there must be a clean way, but I haven't figured it out yet. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] nested transactions
Ken Hirsch wrote: From: Bruce Momjian [EMAIL PROTECTED] And finally, I must abort tuple changes made by the aborted subtransaction. One way of doing that is to keep all relation id's modified by the transaction, and do a sequential scan of the tables on abort, changing the transaction id's to a fixed aborted transaction id. However, this could be slow. (We could store tids if only a few rows are updated by a subtransaction. That would speed it up considerably.) Are you sure you don't want to use the log for this? It does mean that the log can grow without bound for long-lived transactions, but it's very straightforward and fast. I don't think we want to have unlimited log file growth for long running transactions/subtransactions. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
[HACKERS] InitDB Failure - PostgreSQL 7.2, RedHat 7.3, compile from source
Hi, Sorry to ask this question straight out but I wasn't sure where else to get an answer! Due to the 32 character limit on column/table names, we needed to recompile PostgreSQL from the source with updated settings. It compiles fine, but on running initdb, we get the following output: The files belonging to this database system will be owned by user postgres. This user must also own the server process. Fixing permissions on existing directory /var/lib/pgsql/data... ok creating directory /var/lib/pgsql/data/base... ok creating directory /var/lib/pgsql/data/global... ok creating directory /var/lib/pgsql/data/pg_xlog... ok creating directory /var/lib/pgsql/data/pg_clog... ok creating template1 database in /var/lib/pgsql/data/base/1... /usr/bin/initdb: line 473: 23462 Broken pipe cat $POSTGRES_BKI 23463 | sed -e s/POSTGRES/$POSTGRES_SUPERUSERNAME/g -e s/ENCODING/$MULTIBYTEID/g 23464 Segmentation fault | $PGPATH/postgres -boot -x1 $PGSQL_OPT $BACKEND_TALK_ARG template1 initdb failed. - Can anyone shed some light on this just by looking at it? Two of us have wasted basically an entire day getting to this point and would appreciate any assistance. We may end up having to settle with 64 characters and install the latest RC :-) However ideally we'd like to be able to go beyond 64. Cheers, Chris -- Chris Cox B.IT.(Information Systems) Senior Software Engineer Creatop Interactive Media Level 1, 240 McCullough Street Sunnybank. Qld. 4109 Australia Ph: +61 7 3216 9755 Mobile: +61 412 416600 http://www.creatop.com.au/ [EMAIL PROTECTED] -- ---(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] Auto Vacuum Daemon (again...)
On 27 Nov 2002 at 13:01, Matthew T. O'Connor wrote: On Wed, 2002-11-27 at 01:59, Shridhar Daithankar wrote: I would not like postmaster forking into pgavd app. As far as possible, we should not touch the core. This is a client app. and be it that way. Once we integrate it into backend, we need to test the integration as well. Why bother? I understand and agree that a non-integrated version is simpler, but I think there is much to gain by integrating it. First, the non-integrated version has to constantly poll the server for stats updates this creates unnecessary over head. A more integrated version could be signaled, or gather the stats information in much the same manner as the stats system does. Also, having the postmaster control the AVD is logical since it doesn't make sense to have AVD running when the postmaster is not running, also, we what happens when multiple postmaster are running on the same machine, I would think each should have it's on AVD. Integrating it in I think would be much better. There are differences in approach here. The reason I prefer polling rather than signalig is IMO vacuum should always be a low priority activity and as such it does not deserve a signalling overhead. A simpler way of integrating would be writing a C trigger on pg_statistics table(forgot the exact name). For every insert/update watch the value and trigger the vacuum daemon from a separate thread. (Assuming that you can create a trigger on view) But Tom has earlier pointed out that even a couple of lines of trigger on such a table/view would be a huge performance hit in general.. I would still prefer polling. It would serve the need for foreseeable future.. I agree vacuum full should be left to admin, my version does the same. Good. I just wanted to confirm that we follow same policy. Thanks.. Well the way I have it running is that the AVD blocks and waits for the vacuum process to finish. This way you are guaranteed to never be running more than one vacuum process at a time. I can send you the code if you would like, I am interested in feedback. The reason I brought up issue of multiple processes/connection is starvation of a DB. Say there are two DBs which are seriously hammered. Now if a DB starts vacuuming and takes long, another DB just keeps waiting for his turn for vacuuming and by the time vacuum is triggered, it might already have suffered some performance hit. Of course these things are largely context dependent and admin should be abe to make better choice but the app. should be able to handle the worst situation.. The other way round is make AVD vacuum only one database. DBA can launch multiple instances of AVD for each database as he sees fit. That would be much simpler.. Please send me the code offlist. I would go thr. it and get back to you by early next week(bit busy, right now) Bye Shridhar -- union, n.: A dues-paying club workers wield to strike management. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [spam] Re: [mail] Re: [HACKERS] Native Win32 sources
It's an option, but I can see it being a bit of an H-Bomb to kill an ant if the Win32 source appears within the next 6 weeks. I've played used cygwin before and I've always been uncomfortable with the way it's integrated with Windows. It always came accross as something that isn't really for the windows masses, but more for techies who want Unix on an MS platform. My main dislikes about it are; - Changing paths. If my developers install something in c:\temp they expect to find it under /temp on cygwin. - Duplicating home directories. The users already have a home directory under MS, why does cygwin need to use a different location? My current plan is to use the Win32 native port myself when it first appears and thrash our app against it. Once I'm happy that the major functionality of our app works against the Win32 port, I'll introduce it to a limited number of developers who enjoy hacking code if it goes wrong and get them to note a log any problems the come accross. If nothing else it should mean a few more bodies testing the Win32 port (although I expect you'll find they'll be a large number of those as soon as it hits CVS). Al. - Original Message - From: scott.marlowe [EMAIL PROTECTED] To: Al Sutton [EMAIL PROTECTED] Cc: Hannu Krosing [EMAIL PROTECTED]; bpalmer [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 27, 2002 11:08 PM Subject: Re: [spam] Re: [mail] Re: [HACKERS] Native Win32 sources On Wed, 27 Nov 2002, Al Sutton wrote: Hannu, Using a Win32 platform will allow them to perform relative metrics. I'm not looking for a statement saying things are x per cent faster than production, I'm looking for reproducable evidence that an improvement offers y per cent faster performance than another configuration on the same platform. So, does cygwin offer any win? I know it's still unix on windows but it's the bare minimum of unix, and it is easy to create one image of an install and copy it around onto other boxes in a semi-ready to go format. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] FreeBSD, Linux: select, select count(*) performance
Hi, i run 2 queries on 2 similar boxes (one running Linux 2.4.7, redhat 7.1 and the other running FreeBSD 4.7-RELEASE-p2) The 2 boxes run postgresql 7.2.3. I get some performance results that are not obvious (at least to me) i have one table named noon with 108095 rows. The 2 queries are: q1: SELECT count(*) from noon; q2: SELECT * from noon; Linux q1 dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon; NOTICE: QUERY PLAN: Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual time=338.17..338.17 rows=1 loops=1) - Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual time=0.01..225.73 rows=108095 loops=1) Total runtime: 338.25 msec Linux q2 dynacom=# EXPLAIN ANALYZE SELECT * from noon; NOTICE: QUERY PLAN: Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1960) (actual time=1.22..67909.31 rows=108095 loops=1) Total runtime: 68005.96 msec FreeBSD q1 == dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon; NOTICE: QUERY PLAN: Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual time=888.93..888.94 rows=1 loops=1) - Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual time=0.02..501.09 rows=108095 loops=1) Total runtime: 889.06 msec FreeBSD q2 == dynacom=# EXPLAIN ANALYZE SELECT * from noon; NOTICE: QUERY PLAN: Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1975) (actual time=1.08..53470.93 rows=108095 loops=1) Total runtime: 53827.37 msec The pgsql configuration for both systems is identical (the FreeBSD system has less memory but vmstat dont show any paging activity so i assume this is not an issue here). The interesting part is that FreeBSD does better in select *, whereas Linux seem to do much better in select count(*). Paging and disk IO activity for both systems is near 0. When i run the select count(*) in Linux i notice a small increase (15%) in Context Switches per sec, whereas in FreeBSD i notice a big increase in Context Switches (300%) and a huge increase in system calls per second (from normally 9-10 to 110,000). (Linux vmstat gives no syscall info). The same results come out for every count(*) i try. Is it just the reporting from explain analyze?? Has any hacker some light to shed?? Thanx. == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] FreeBSD, Linux: select, select count(*) performance
Achilleus Mantzios [EMAIL PROTECTED] writes: Linux q1 dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon; NOTICE: QUERY PLAN: Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual time=338.17..338.17 rows=1 loops=1) - Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual time=0.01..225.73 rows=108095 loops=1) Total runtime: 338.25 msec Linux q2 dynacom=# EXPLAIN ANALYZE SELECT * from noon; NOTICE: QUERY PLAN: Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1960) (actual time=1.22..67909.31 rows=108095 loops=1) Total runtime: 68005.96 msec You didn't say what was *in* the table, exactly ... but I'm betting there are a lot of toasted columns, and that the extra runtime represents the time to fetch (and perhaps decompress) the TOAST entries. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
