Re: [HACKERS] Again, sorry, caching.
On Sat, Mar 16, 2002 at 09:01:28AM -0500, mlw wrote: If it is mostly static data, why not just make it a static page? Because a static page is a maintenance nightmare. One uses a database in a web site to allow content to be changed and upgraded dynamically and with a minimum of work. It's ugly argumentation for DB cache. What generate web page after data change and next time use it as static? I was thinking that it could be implemented as a keyword or comment in a query. Such as: select * from table where column = 'foo' cacheable You can insert mostly static data into temp table and in next queries use this temp table. After update/delete/insert can your application rebuild temp table (or by trigger?). Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] Again, sorry, caching.
Karel Zak wrote: On Sat, Mar 16, 2002 at 09:01:28AM -0500, mlw wrote: If it is mostly static data, why not just make it a static page? Because a static page is a maintenance nightmare. One uses a database in a web site to allow content to be changed and upgraded dynamically and with a minimum of work. It's ugly argumentation for DB cache. What generate web page after data change and next time use it as static? I was thinking that it could be implemented as a keyword or comment in a query. Such as: select * from table where column = 'foo' cacheable You can insert mostly static data into temp table and in next queries use this temp table. After update/delete/insert can your application rebuild temp table (or by trigger?). Yes, I could, as could most of the guys reading these messages. I am thinking about a feature in PostgreSQL that would make that easier for the average DBA or web producer. Lets face it, MySQL wins a lot of people because they put in features that people want. All the ways people have suggested to compete with MySQL's caching have been ugly kludges. I understand the there is an amount of work involved with doing caching, and the value of caching is debatable by some, however, it is demonstrable that caching can improve a very common, albeit specific, set of deployments. Also, managing data is the job of the database, not the application. It does belong in PostgreSQL, if someone is forced to write a caching scheme around PostgreSQL, it is because PostgreSQL lacks that feature. ---(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] Again, sorry, caching.
Le Lundi 18 Mars 2002 13:23, mlw a écrit : Lets face it, MySQL wins a lot of people because they put in features that people want. MySQL is very interested in benchmarks. It does not really care for data consistency. Cheers, Jean-Michel POURE ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Again, sorry, caching.
Yes. EVERY person that I've ever known which runs MySQL run for two very simple reasons. First, they believe it to be wicked fast. Second, they don't understand what ACID is, what a transaction is, or why running a single session against a database to perform a benchmark is a completely bogus concept. In case it's not obvious, these are usually people that are trying to take a step up from Access. While I do believe MySQL, from a performance perspective, is a step up from Access I always tell my clients...if you wouldn't use an Access database for this project, you shouldn't use MySQL either. To me, this means we need better advertising, PR, and education rather than a result set cache. :P Speaking of which, I'm wondering if there are any design patterns we can look at which would address client side caching...well, at least make it easier to implement as well as implement it in a consistent manner. Greg On Mon, 2002-03-18 at 07:32, Jean-Michel POURE wrote: Le Lundi 18 Mars 2002 13:23, mlw a écrit : Lets face it, MySQL wins a lot of people because they put in features that people want. MySQL is very interested in benchmarks. It does not really care for data consistency. Cheers, Jean-Michel POURE ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Again, sorry, caching.
Jean-Michel POURE wrote: Le Lundi 18 Mars 2002 13:23, mlw a écrit : Lets face it, MySQL wins a lot of people because they put in features that people want. MySQL is very interested in benchmarks. It does not really care for data consistency. In no way am I suggesting we avoid ACID compliance. In no way am I suggesting that PostgreSQL change. All I am suggesting is that tables which change infrequently can and should be cached. select * from table where foo = 'bar' Need not be executed twice if the table has not changed. select * from table1, (select * from table2 where foo='bar' cacheable) as subset were subset.col1 = table1.col1; In the above query, if table two changes 4 times a day, and it queried a couple times a minute or second, the caching of the subset could save a huge amount of disk I/O. This sort of query could improve many catalog based implementations, from music, to movies, to books. A library could implement a SQL query for book lookups like this: select * from authors, (select * from books where genre = 'scifi' cacheable) as subset where authors.id = subset.auhorid and authors.id in () Yes it is arguable that index scans may work better, and obviously, summary tables may help, etc. but imagine a more complex join which produces fewer records, but is executed frequently. Caching could help the performance of PostgreSQL in some very real applications. MySQL's quest for benchmarking numbers, I agree, is shameful because they create numbers which are not really applicable in the real world. This time, however, I think they may be on to something. (1) PostgreSQL use a cacheable or iscacheable keyword. (2) If the query uses functions which are not marked as iscacheable, then it is not cached. (3) If any table contained within the cacheable portion of the query is modified, the cache is marked as dirty. (4) No provisions are made to recreate the cache after an insert/update/delete. (5) The first query marked as iscacheable that encounters a dirty flag in a table, does an exhaustive search on the cache and removes all entries that are affected. As far as I can see, if the above parameters are used to define caching, it could improve performance on sites where a high number of transactions are made, where there is also a large amount of static data, i.e. a ecommerce site, library, etc. If the iscacheable keyword is not used, PostgreSQL will not incur any performance degradation. However, if he iscacheable keyword is used, the performance loss could very well be made up by the benefits of caching. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Again, sorry, caching.
Mattew T. O'Connor wrote: My big problem with putting the cache outside of the database is that it is now incumbent on the applications programmer to write a cache. A database should manage the data, the application should handle how the data is presented. Forcing the application to implement a cache feels wrong. I believe someone suggested a possible solution that was in the pg client using NOTICE and triggers. The argument given against it, was that it would not be ACID compliant. I say, who cares. I would think that the select cachable would only be allowed for simple selects, it would not be used for select for update or anything else. Anytime you are given the result of a simple select, you are not guaranteed that the data won't change underneath you. Not true, if you begin a transaction, you can be isolated of changes made to the database. The primary use that you have suggested is for web sites, and they certainly won't mind of the cache is 0.3seconds out of date. Again, if they don't care about accuracy, then they will use MySQL. PostgreSQL is a far better system. Making PostgreSQL less accurate, less correct takes away, IMHO, the very reasons to use it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgres is not using tas
Luis Alberto Amigo Navarro [EMAIL PROTECTED] writes: i'm working with pg7.2 on irix6.5 platform and i've realized that postgres = is using semop instead of tas, pg_config_os.h has define HAVE_TEST_AND_SET,= and i don't kwow where could be the mistake. s_lock.h seems to think that __sgi is predefined on IRIX. Perhaps that is not true in your setup? What compiler are you using? 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] Again, sorry, caching.
On Mon, 2002-03-18 at 08:15, mlw wrote: Mattew T. O'Connor wrote: [snip] The primary use that you have suggested is for web sites, and they certainly won't mind of the cache is 0.3seconds out of date. Again, if they don't care about accuracy, then they will use MySQL. PostgreSQL is a far better system. Making PostgreSQL less accurate, less correct takes away, IMHO, the very reasons to use it. If you are using a web site and you need real time data within 0.3s, you've implemented on the wrong platform. It's as simple as that. In the web world, there are few applications where a 0.3s of a window is notable. After all, that 0.3s of a window can be anywhere within the system, including the web server, network, any front end caches, dns resolutions, etc. I tend to agree with Mettew. Granted, there are some application domains where this can be critical...generally speaking, web serving isn't one of them. That's why all of the solutions I offered were pointedly addressing a web server scenario and not a generalized database cache. I completely agree with you on that. In a generalized situation, the database should be managing and caching the data (which it already does). Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Again, sorry, caching.
Greg Copeland wrote: On Mon, 2002-03-18 at 08:15, mlw wrote: Mattew T. O'Connor wrote: [snip] The primary use that you have suggested is for web sites, and they certainly won't mind of the cache is 0.3seconds out of date. Again, if they don't care about accuracy, then they will use MySQL. PostgreSQL is a far better system. Making PostgreSQL less accurate, less correct takes away, IMHO, the very reasons to use it. If you are using a web site and you need real time data within 0.3s, you've implemented on the wrong platform. It's as simple as that. In the web world, there are few applications where a 0.3s of a window is notable. After all, that 0.3s of a window can be anywhere within the system, including the web server, network, any front end caches, dns resolutions, etc. This is totally wrong! An out of date cache can cause errors by returning results that are no longer valid, thus causing lookup issues. That is what ACID compliance is all about. I tend to agree with Mettew. Granted, there are some application domains where this can be critical...generally speaking, web serving isn't one of them. That's why all of the solutions I offered were pointedly addressing a web server scenario and not a generalized database cache. I completely agree with you on that. In a generalized situation, the database should be managing and caching the data (which it already does). But it does not cache a query. An expensive query which does an index range scan and filters by a where clause could invalidate a good number of buffers in the buffer cache. If this or a number of queries like it are frequently repeated, verbatim, in a seldom changed table, why not cache them within PostgreSQL? It would improve overall performance by preserving more blocks in the buffer cache and eliminate a number of queries being executed. I don't see how caching can be an argument of applicability. I can understand it from a time/work point of view, but to debate that it is a useful feature seems ludicrous. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgres is not using tas
hi tom It is compiled with mips pro compilers I've tried to remove if defined in s_lock.h, but it's still using semop, is there any other side it could be defined. thanks and regards. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fw: [HACKERS] bad performance on irix
- Original Message - From: Robert E. Bruccoleri [EMAIL PROTECTED] To: Luis Alberto Amigo Navarro [EMAIL PROTECTED] Sent: Monday, March 18, 2002 4:08 PM Subject: Re: [HACKERS] bad performance on irix Dear Luis, Dear Bob: I've removed ifdefs from s_lock.h trying if semop using was define problem, but it's still using semop any suggest? No, I see the same compilation as you do with 7.2. It's using the spinlocks for some locks, but semaphores for others. I don't know what to do next. Alas... --Bob +-++ | Robert E. Bruccoleri, Ph.D. | email: [EMAIL PROTECTED]| | P.O. Box 314| URL: http://www.congen.com/~bruc | | Pennington, NJ 08534|| +-++ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Fw: [HACKERS] bad performance on irix
Luis Alberto Amigo Navarro [EMAIL PROTECTED] forwards: It's using the spinlocks for some locks, but semaphores for others. That doesn't make any sense to me. For one thing, if HAS_TEST_AND_SET is defined in the config header, the executable will just plain fail to build if there's no tas implementation, because lmgr/spin.c won't be compiled. And I sure don't see how some of the locks might be implemented one way and some the other. Which ones do you think are being implemented as semaphores, and what's your evidence? 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] postgres is not using tas
As i know, it's only using semop, even with TAS_AND_SET defined, this is an extract from postmaster's process registry 2515.934mS(+ 5914uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 a9 6e 10 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2520.497mS(+ 4562uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 a9 9a 18 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2526.496mS(+ 5998uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 a9 c6 38 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2527.115mS(+ 619uS)[ 4]postgres(38089): semop(1568, 0x7fff1c70, 1) OK 2527.314mS(+ 198uS)[ 4]postgres(38089): semop(1568, 0x7fff1c70, 1) OK 2527.390mS(+ 76uS)[ 4]postgres(38089): semop(1568, 0x7fff1c70, 1) OK 2532.199mS(+ 4809uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 a9 f2 40 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2537.896mS(+ 5696uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 aa 1e 48 00 00 00 22 00 a8 00 c8..., 8192) = 8192 2543.147mS(+ 5251uS)[ 4]postgres(38089): read(25, 00 00 00 00 68 aa 4a 68 00 00 00 22 00 a8 00 c8..., 8192) = 8192 Thanks and regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Again, sorry, caching.
On Mon, 2002-03-18 at 10:08, mlw wrote: Greg Copeland wrote: On Mon, 2002-03-18 at 08:15, mlw wrote: Mattew T. O'Connor wrote: [snip] If you are using a web site and you need real time data within 0.3s, you've implemented on the wrong platform. It's as simple as that. In the web world, there are few applications where a 0.3s of a window is notable. After all, that 0.3s of a window can be anywhere within the system, including the web server, network, any front end caches, dns resolutions, etc. This is totally wrong! An out of date cache can cause errors by returning results that are no longer valid, thus causing lookup issues. That is what ACID compliance is all about. I understand what ACID is about. Question. Was the result set valid when it was cached? Yes. So will it be valid when it's returned as a cached result set? Yes. Might it be an out of date view. Sure...with a horribly small window for becoming out of date. Will it cause look up problems? Might. No more than what you are proposing. In the mean time, the FE cached result set, performance wise, is beating the pants off of the database cached solution on both a specific work load and over all system performance. I should point out that once the FE cache has been notified that it's cache is invalid, the FE would no longer return the invalidated result set. I consider that to be a given, however, from some of your comments I get the impression that you think the invalid result set would continue to be served. Another way of thinking about that is...it's really not any different from the notification acting as the result returned result set...from a validity perspective. That is...if that had been the returned result set (the notification) from the database...it would be accurate (which in the case means the FE cache is now dirty and treated as such)...if the query is refreshed because it is now invalid..the result set is once again accurate and reflective of the database. Example... Database cache Query result set Result set returned (cached on database) local change to database (result set cache invalid) new query based on out of date queried result set Application cache Query result set (cached) Result set returned local change to database (app cache invalid and signaled) new query based on out of date queried result set Both have that problem since transactional boundaries are hard to keep across HTTP requests. This again, is why for web applications, a FE cache is perfectly acceptable for *most* needs. Also notice that your margin for error is more or less the same. [snip] I don't see how caching can be an argument of applicability. I can understand it from a time/work point of view, but to debate that it is a useful feature seems ludicrous. I don't think I'm arguing if it's applicable or useful. Rather, I'm saying that faster results can be yielded by implementing it in the client with far less effort than it would take to implement in the BE. I am arguing that it's impact on overall system performance (though I really didn't do more than just touch on this topic) is questionable...granted, it may greatly enhance specific work loads...at the expense of others. Which shouldn't be too surprising as trade offs of some type are pretty common. At this point in time, I think we've both pretty well beat this topic up. Obviously there are two primary ways of viewing the situation. I don't think anyone is saying it's a bad idea...I think everyone is saying that it's easier to address elsewhere and that overall, the net returns may be at the expense of some other work loads. So, unless there are new pearls to be shared and gleaned, I think the topics been fairly well addressed. Does more need to said? Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] insert statements
Vince Vielhaber wrote: Looks like Sybase ignores the bar: 1 create table foo(a int) 2 go 1 insert into foo(bar.a) values(1) 2 go (1 row affected) 1 select * from foo 2 go a --- 1 (1 row affected) 1 This looks like a parser error to me. It probably only takes the last bit of the name and ignores all the qualifiers... -- Fernando Nasser Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] insert statements
Tom Lane wrote: I'd want it to error out on INSERT foo (bar.col), though ;-) And on INSERT foo (bar.foo.col) as well. This means we will have to take this check down to the analyze phase (where the schema where foo is located is finally known, if it was not specified explicitly). We could easily take INSERT bar.foo (bar.foo.col) but the above one is trouble. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] insert statements
Vince Vielhaber wrote: On Thu, 14 Mar 2002, Rod Taylor wrote: Out of curiosity, does SyBase allow you to qualify it with schema.table.column? Just tried it... Yes. What if you give it a bogus schema name? Does it error out or just ignore it? -- Fernando Nasser Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] insert statements
On Mon, 18 Mar 2002, Fernando Nasser wrote: Vince Vielhaber wrote: On Thu, 14 Mar 2002, Rod Taylor wrote: Out of curiosity, does SyBase allow you to qualify it with schema.table.column? Just tried it... Yes. What if you give it a bogus schema name? Does it error out or just ignore it? If I get a few mins before I leave I'll try it, but I would guess that it ignores it because when I tried INSERT INTO foo(bar.a), bar didn't exist and Sybase still accepted it. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] postgres is not using tas
here is the execution of one backend summary: System call summary: Average Total Name #Calls Time(ms) Time(ms) - semop 39305 0.06 2497.53 select 7 19.86139.01 unlink 1 22.96 22.96 close 49 0.04 2.06 recv1 0.72 0.72 send1 0.11 0.11 fsync 1 0.07 0.07 prctl 1 0.01 0.01 exit1 0.00 0.00 As u can see it's amazing Thanks and regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] My only post with regard to query caching
After reading way too many threads on this (probably too many on pgsql-* in general) I'll just go over how I feel about the caching issue. It seems that MySQL has implemented a system that allows the database to cache queries that are frequently used and reduce latency for them. This, to me, seems like a very nice low-hanging fruit optimization, especially for web systems. === Examples Search Pages: I implented a bug database. The main entry point was a Define your search page which presented quite a few options. Many of them were drop-down lists. This page did five or six queries to do things like find the list of engineers in the company, categories for bugs, and versions of software. The results of those queries probably changed once per month, but were done several times/day. While they are simple and may not have cost much, I can see how a simple cache would make them cost less. Home Pages: Frequently, in the 'blog case (such as my home page), a lookup is done every time the page is hit. I update that table every couple of days, but it is accessed much more often. Once again, this is a fairly common usage pattern in the web environment that /may/ be a good candiate for this sort of caching. These are two frequently-used design patterns which I think would benefit from this optimization. MySQL, and some of their customers seem to think so, too. === Common Arguments This shouldn't be in the database! Arguably, yes. This is something that might be better handled by the application server. The app server may or may not have a unified connection pool to the database and can better organize the queries and caching. On the other hand, for the case of a database that is not on the same machine as the webserver, this is a good chance to reduce bandwidth. This is going to make things ugly/hard to implement/etc... Personally, I feel that too many of PostgreSQL's potential features get rejected out-of-hand on the grounds that implementation will be difficult or that it will make things gross (as though parts of PostgreSQL aren't gross already). While I've not looked /too/ closely, it seems that if one were to create a way for the system to maintain the results of a query, keyed by the text of the query itself, it would be easy for something in the query sequence to check and see if the query has already been done, and access it. We already hold resultsets between queries in order to handle cursors, so most of the framework must already be in there. Just keep each 'cacheable' query. NOTE: This probably implies that in the simple case, the cache cannot be used between different connections. The other issue is the expiration of the cache entries. Once again, for the Home Pages case above, I would be perfectly satisified if the cache was entirely blown away every time any UPDATE query was executed. This would handle most cases, except for triggers on non-UPDATE queries. Otherwise, we would need to less simple-case the issue by tracking when tables are actually updated, and for even more bonus points, track which tables affect which cache entries. === Editorial: PostgreSQL seems to spend a lot of time stressing ACID, and I believe this is a very good thing. I simply don't trust MySQL any more then I trust any other SQL interface to a flat datafile. Also, PostgreSQL has some very handy features involving datatypes, triggers, and stored procedures. But you all know that. MySQL is doing some things right. They are providing useful documentation. They are adding features that target the web market (they may be adding them incorrectly, however). If we expect PostgreSQL to beat MySQL in anything but My database is transactionally secure and We have a GECO optimizer pissing wars, we'll need to start becoming a little more competitive in the raw speed arena. I feel that this optimization, while it may not be trivial, is fairly low-hanging fruit that can help. I may even try to implement it, but I make no guarantees. -- Adam Haberlach | Who buys an eight-processor machine and then [EMAIL PROTECTED]| watches 30 movies on it all at the same time? http://newsnipple.com | Beats me. They told us they could sell it, so | we made it. -- George Hoffman, Be Engineer ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] postgres is not using tas
Luis Alberto Amigo Navarro [EMAIL PROTECTED] writes: As i know, it's only using semop, even with TAS_AND_SET defined, this is an extract from postmaster's process registry The fact that there are some semops in the strace doesn't prove anything. We do use semaphores when we have to block the current process. 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] postgres is not using tas
hi tom could you please tell me where to find info on when and why is semop used, this thread began because i had excessive sem usage as u can see thanks and regards ---(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] My only post with regard to query caching
Adam Haberlach [EMAIL PROTECTED] writes: MySQL is doing some things right. They are providing useful documentation. They are adding features that target the web market (they may be adding them incorrectly, however). If we expect PostgreSQL to beat MySQL in anything but My database is transactionally secure and We have a GECO optimizer pissing wars, we'll need to start becoming a little more competitive in the raw speed arena. I feel that this optimization, while it may not be trivial, is fairly low-hanging fruit that can help. I may even try to implement it, but I make no guarantees. Looks like the onus is on you and mlw to come up with a design for the query cache mechanism, based on knowledge of PG internals, that intelligently addresses ACID and MVCC issues, and propose it. I think the core developers would certainly be willing to look at such a design proposal. Then, if they like it, you get to implement it. ;) In other words, and I say this in the nicest possible way, talk is cheap. -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD... ---(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] postgres is not using tas
Luis Alberto Amigo Navarro [EMAIL PROTECTED] writes: could you please tell me where to find info on when and why is semop used, It's used when we need to block the current process (or to unblock another process that had been waiting). Look for calls to IpcSemaphoreLock and IpcSemaphoreUnlock. A large number of semops may mean that you have excessive contention on some lockable resource, but I don't have enough info to guess what resource. Have you tried doing profiling of the backend? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgres is not using tas
hi tom If i track a single backend during an 8 read-only queries parallel execution these are the results( System call summary: Average Total Name #Calls Time(ms) Time(ms) - semop3803 0.20774.03 select 4 19.58 78.33 recv1 2.41 2.41 brk 6 0.08 0.48 close 1 0.14 0.14 send1 0.14 0.14 semctl 1 0.05 0.05 prctl 1 0.01 0.01 exit1 0.00 0.00 I think it's a bit excessive for a 8 SMP what do u think? thanks and regards ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgres is not using tas
Luis Alberto Amigo Navarro [EMAIL PROTECTED] writes: If i track a single backend during an 8 read-only queries parallel execution these are the results( System call summary: Average Total Name #Calls Time(ms) Time(ms) - semop3803 0.20774.03 select 4 19.58 78.33 recv1 2.41 2.41 brk 6 0.08 0.48 close 1 0.14 0.14 send1 0.14 0.14 semctl 1 0.05 0.05 prctl 1 0.01 0.01 exit1 0.00 0.00 Considering that there are no read() or write() calls listed, and that 8 client queries would surely require at least one send() and one recv() apiece, I don't think I believe a word of those stats. Well, maybe the 1 exit() is correct ;-) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] postgres is not using tas
hi tom how may we have believable statistics? what do u think about the graph i've sent to you, there are retrieved using hardware counters, i believe they are exact. Any idea? Thanks and regards ---(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] insert statements
I'd want it to error out on INSERT foo (bar.col), though ;-) And on INSERT foo (bar.foo.col) as well. Why accept above at all ? Seems much too error prone, I would eighter accept table with schema or without schema, mixing both cases seems unnecessarily confusing and error prone to me. If at all, I would allow: INSERT bar.foo (bar.foo.col) INSERT foo (foo.col) Would that be enough for the initial problem case ? Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Fw: Fw: [HACKERS] bad performance on irix
Dear Tom, The evidence is from the Process Activity Recorder, an Irix utility similar to strace the reports syscall usage. A number of semop's are performed in the operation of backend. Luis can send you specifics. --Bob Luis Alberto Amigo Navarro writes: - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Luis Alberto Amigo Navarro [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Robert E. Bruccoleri [EMAIL PROTECTED] Sent: Monday, March 18, 2002 5:36 PM Subject: Re: Fw: [HACKERS] bad performance on irix Luis Alberto Amigo Navarro [EMAIL PROTECTED] forwards: It's using the spinlocks for some locks, but semaphores for others. That doesn't make any sense to me. For one thing, if HAS_TEST_AND_SET is defined in the config header, the executable will just plain fail to build if there's no tas implementation, because lmgr/spin.c won't be compiled. And I sure don't see how some of the locks might be implemented one way and some the other. Which ones do you think are being implemented as semaphores, and what's your evidence? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html +-++ | Robert E. Bruccoleri, Ph.D. | email: [EMAIL PROTECTED]| | P.O. Box 314| URL: http://www.congen.com/~bruc | | Pennington, NJ 08534|| +-++ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Platform comparison ...
Hello Group, I need your help, in putting together a list of comparisons, and good solid technical reasons, to why to use PostgreSQL over using Microsoft SQL Server. Right now, we are using PostgreSQL for a back-end for some of our web stuff. A couple of our developers, which are Microsoft VB developers, are complaining about not being able to use proprietary MS stuff with PostgreSQL. I have told them to use standard SQL92 compliant programming techniques, and all will work just fine. They just don't seem to understand why a person wouldn't use SQL Server. If I could put together a list of good solid technical arguments, (Performance, Support, Reliability, ETC.), as to why PostgreSQL is better, I think I can make a good case in keeping PostreSQL. I just don't have any SQL Server experience to compare with. If any of you, who have SQL Server experience could send me good technical comparisons of SQL Server vs PostgreSQL, I would greatly appreciate it. Thanks in advance, Dale Anderson. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Platform comparison ...
Dale Anderson [EMAIL PROTECTED] writes: Hello Group, [snip: why would PG be better than MSSQL?] Better isn't meaningful except in the context of the problem you're trying to solve. There will be some problems where PG is right, some where MSSQL works better, and some where neither is the best choice. Reasons you might prefer PG: * No licensing costs, period * Runs on free operating systems * Runs on Unix, if you prefer that as a server environment * Object-relational technology * Extensibility (not only functions, but datatypes, index types, etc) * Open Source (no vendor lockin) Reasons you might prefer MSSQL: * Need for MS extensions * Easier setup (perhaps) for non-DBA/sysadmin types * Management's desire for single-source * Performance advantages for some workloads * Windows server environment (PG runs on Windows, but only through a Unix emulation layer--I personally wouldn't run it in production, but then again I wouldn't run Windows in production:) Both offer commercial support, ACID compliance, stored procedures/functions, and the other stuff that people expect from a real database. Hope this helps... -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Time for 7.2.1?
OK, I have branded 7.2.1 and updated HISTORY/release.sgml. Do we want any special text about the sequence bug fix, or just mention in the announcement that all 7.2 people should upgrade? --- Bruce Momjian wrote: Tom Lane wrote: I believe we've now committed fixes for all the must fix items there were for 7.2.1. Does anyone have any reasons to hold up 7.2.1 more, or are we ready to go? I need to brand 7.2.1 --- will do tomorrow. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] plsql as an officially supported language?
We have this in the TODO: o Add plsh server-side shell language (Peter E) This is Peter's language that allows shell calls. I think Peter wants to add it for 7.3 and I think it is a good idea. --- Rod Taylor wrote: This is probably a language looking for a purpose before adding it to the core. Here's what I use it for; probably abusively too! Could use untrusted perl to spawn system calls, but shell scripts are much nicer for shell work not to mention transactional updates of structure and control scripts make for minimall impact upgrade periods. - On demand PDFs as generated by Docbook for offline reports initiated by the database. Ie. Inventory updates to management every N sales made or when stock is running low. - Updating static HTML pages with Docbook HTML output when the stored data changes. - System provisioning initiation. Rollbacks don't work, but it's not really important that things are undone immediatly, just that they're initiated immediatly. Using DB for this removes requirement of middleware. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ---(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) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Time for 7.2.1?
Bruce Momjian [EMAIL PROTECTED] writes: OK, I have branded 7.2.1 and updated HISTORY/release.sgml. Do we want any special text about the sequence bug fix, or just mention in the announcement that all 7.2 people should upgrade? The first change item should maybe be more explicit, say Ensure that sequence counters do not go backwards after a crash Otherwise I think it's fine. BTW, the bug exists in 7.1 as well. 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] Time for 7.2.1?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, I have branded 7.2.1 and updated HISTORY/release.sgml. Do we want any special text about the sequence bug fix, or just mention in the announcement that all 7.2 people should upgrade? The first change item should maybe be more explicit, say Ensure that sequence counters do not go backwards after a crash Otherwise I think it's fine. BTW, the bug exists in 7.1 as well. Done. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Again, sorry, caching.
On Sat, 2002-03-16 at 09:01, mlw wrote: On a web site, a few specific queries get executed, unchanged, repeatedly. Think about an ecommerce site, most of the time it is just a handful of basic queries. These basic queries are usually against pretty large product tables. A caching mechanism would make these queries pretty light weight. The arguments against caching: It is an application issue This is completely wrong. Caching can not be done against a database without knowledge of the database, i.e. when the data changes. But can't this be achieved by using a LISTEN/NOTIFY model, with user-created rules to NOTIFY the appropriate listener when a table changes? With a good notification scheme like this, you don't need to continually poll the DB for changes. You don't need to teach your cache a lot of things about the database, since most of that knowledge is encapsulated inside the rules, and supporting tables. My impression (I could be wrong) is that LISTEN/NOTIFY doesn't get the press that it deserves. If this model isn't widely used because of some deficiencies in the LISTEN/NOTIFY implementation, IMHO our time would be better spent fixing those problems than implementing the proposed caching scheme. If we're looking to provide a quick and easy caching scheme for users attracted to MySQL's query cache, why not provide this functionality through another application? I'm thinking about a generic caching layer that would sit in between Postgres and the database client. It could speak the FE/BE protocol as necessary; it would use LISTEN/NOTIFY to allow it to efficiently be aware of database changes; it would create the necessary rules for the user, providing a simple interface to enabling query caching for a table or a set of tables? What does everyone think? OK, let me have it, tell me how terrible an idea this is. tell me how wrong I am. I think your goals are laudable (and I also appreciate the effort that you and everyone else puts into Postgres); I just think we could get most of the benefits without needing to implement potentially complex changes to Postgres internals. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] psql and output from \?
Patch applied. Thanks. --- Ian Barwick wrote: On Thursday 14 March 2002 22:40, Bruce Momjian wrote: I guess some of these weren't introduces by you, but if someone is going to fix this, he might as well take care of these. Will submit another patch in the morning (it's late here). Ian, do you have another version of this patch ready? Patch attached (diff against CVS, replacing previous patch). Ian Barwick [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Again, sorry, caching.
On Mon, 2002-03-18 at 20:35, Neil Conway wrote: [snip] My impression (I could be wrong) is that LISTEN/NOTIFY doesn't get the press that it deserves. If this model isn't widely used because of some deficiencies in the LISTEN/NOTIFY implementation, IMHO our time would be better spent fixing those problems than implementing the proposed caching scheme. If we're looking to provide a quick and easy caching scheme for users attracted to MySQL's query cache, why not provide this functionality through another application? I'm thinking about a generic caching layer that would sit in between Postgres and the database client. It could speak the FE/BE protocol as necessary; it would use LISTEN/NOTIFY to allow it to efficiently be aware of database changes; it would create the necessary rules for the user, providing a simple interface to enabling query caching for a table or a set of tables? What does everyone think? Yes...I was thinking that a generic library interface with a nice design pattern might meet this need rather well. Done properly, I think we can make it where all that, more or less, would be needed is application hooks which accept the result set to be cached and a mechanism to signal invalidation of the current cacheobviously that's not an exhaustive list... :) I haven't spent much time on this, but I'm fairly sure some library routines can be put together which would greatly reduce the effort of application coders to support fe-data caches and still be portable for even the Win32 port. Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Time zone questions
australia=# select '2002-03-18 00:00:00' at time zone 'Australia/Sydney'; ERROR: Time zone 'australia/sydney' not recognized australia=# set time zone 'Australia/Sydney'; SET VARIABLE australia=# select '2002-03-18 00:00:00'; ?column? - 2002-03-18 00:00:00 Why can't I use 'australia/sydney' as a time zone in 'at time zone' notation? Has it been fixed in 7.2? Not fixed, because not broken ;) PostgreSQL recognizes specific time zones such as GMT, PST, or, in your case, EST (is that right? My zinc database on my Linux box seems to identify both daylight and standard times as EST). But for input it only uses the zoneinfo database (or equivalent) if no time zone is specified. Then it uses the system to obtain the local time zone. select '2002-03-18 00:00:00' at time zone 'AEST'; That will give me aussie eastern time quite happily, but what if I don't know when summer time starts? I don't want to have to manually choose between 'AEST' and 'AESST'??? To me, the way to do this would be to use 'Australia/Sydney' as the time zone, but this doesn't work. Right. To do what you suggest is probably *very* expensive, but I actually haven't tried it to confirm. It could require changing the default time zone every time a timestamp is evaluated, which would require file opens/closes, environment variable setting, etc etc. afaik there is no direct API to access time zone info; if there was we could more easily think about supporting this. Presumably you are interested in this for an application where you want to support multiple time zones. But why is a combination of SET TIME ZONE 'Australia/Sydney'; and SELECT '2002-03-18 00:00:00' not adequate for this kind of thing? btw, SQL9x only specifies numeric time zones, which of course have no concept of time zone rules at all :( - Tom ---(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
[HACKERS] Broken code in gram.y
The OWNER production rules added to DROP DATABASE:
DropdbStmt: DROP DATABASE database_name
{
DropdbStmt *n =
makeNode(DropdbStmt);
n-dbname = $3;
$$ = (Node *)n;
}
| OWNER opt_equal name
{
$$ = lconsi(4, makeList1($3));
}
| OWNER opt_equal DEFAULT
{
$$ = lconsi(4, makeList1(NULL));
}
;
Cause compiler warnings and are clearly returning the wrong type
(a List, instead of a Node).
(...)/pgsql/src/backend/parser/gram.y: In function
`yyparse':/home/fnasser/DEVO/pgsql/pgsql/src/backend/parser/gram.y:3205:
warning: assignment from incompatible pointer type
(...)/pgsql/src/backend/parser/gram.y:3209: warning: assignment from
incompatible pointer type
--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED]
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
