Re: [HACKERS] Call for 7.5 feature completion
Rod Taylor wrote: On Thu, 2005-08-25 at 21:27 -0400, Andrew Dunstan wrote: Rod Taylor wrote: * Multi-CPU sorts. Take a large single sort like an index creation and split the work among multiple CPUs. This really implies threading, doesn't it? And presumably it would have many possible uses besides this one for doing parallel work, e.g. maybe the planner could evaluate several alternative plans in parallel. I don't think threading is needed. I pictured PostgreSQL spawning one process per CPU explicitly for sorting which standard backends could use as required to do batch work. This is one area where PostgreSQL needs a lot of work to catch up to the competition. Oracle, DB2, Ingres, even SQL Server Enterprise edition all have parallel query capabilities. I have an older 8-processor Sun Enterprise 3500, as an example. It still has use with other vendors' database products due to their parallel feature set (make -j 9 is nice too), but behaves like the boat-anchor it is w.r.t. PostgreSQL. Mike Mascari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Must be owner to truncate?
Stephen Frost wrote: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate; -- New transactions use the empty file -- Once all transactions using the old file have completed, the old file can be deleted. -- Old transactions which insert rows would need to use the new file or scan the old file for rows which they added, I suppose. And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? Mike Mascari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pl/pgsql enabled by default
Neil Conway wrote: Andrew Sullivan wrote: This is not really analogous, because those are already on Security (in the limited sense of disabling features by default) is not free; there is a tradeoff between security and convenience, security and administrative simplicity, and so on. Given that I have yet to see a single substantive argument for pl/pgsql being a security risk that has withstood any scrutiny, I don't see that the security side of the tradeoff has a lot of merit. People who use views to achieve row security, which is a rather common paradigm, cannot allow users to create functions with side effects. Mike Mascari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pl/pgsql enabled by default
Neil Conway wrote: Mike Mascari wrote: People who use views to achieve row security, which is a rather common paradigm, cannot allow users to create functions with side effects. Can you elaborate? I'm not sure I follow you. (I'll note anyway that (1) SQL functions can have side effects: CREATE FUNCTION foo() RETURNS VOID AS 'DELETE FROM ...', for example Wow. That's a problem IMHO. Many people use views to allow userA to query his salary without seeing userB's. If userA can just: 1. Create a SQL function which inserts into another table its arguments 2. Issue a query like: SELECT * FROM view_of_salaries_based_on_current_user WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary, employee); and that function is writable with just 'SQL', then many, many people have a serious security risk on their hands. Perhaps this is why Oracle's standard UDFs cannot perform inserts, updates, or deletes. Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pl/pgsql enabled by default
Andrew Dunstan wrote: Mike Mascari wrote: but the side effect function will only run (unless you set it with security definer) with the privileges of the caller - it won't grant visibility to things that user can't otherwise see. If the visibility is determined by view definitions, such as using CURRENT_USER, which is an exceedingly common practice, then the caller will be able to record tuples before they are filtered by the executor. In any case, you should define your security setup with the capabilities / limitations of the db engine in mind. If there is any security problem in your scenario, it is that you appear to have made unwarranted assumptions about how postgres works, rather than that postgres has a problem. I think most people coming from any other enterprise-class RDBMS environment will be surprised that they cannot use VIEWs to provide user-specific views on data. I could be wrong, but I'd put money on it... Either way, this does not illustrate how enabling plpgsql by default is a security risk. Correct, as the vulnerability exists within the 'SQL' language as well. The only difference is that enabling plpgsql by default changes it from a leak to a full blown flood. Mike Mascari ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] psql 8.0 final not working on NT 4.0sp6
Magnus Hagander wrote:
According to this: http://support.microsoft.com/kb/q165695/ Windows
Desktop update was included with IE 4, but not with IE 5 or later.
Further, if you want to install Windows Desktop Update you have to
first remove IE 5 or later. And finally it says that Windows Desktop
Update can only be installed using the IE 4 setup, but this is no
longer available from Microsoft. What a mess.
Yikes. that's certainly a mess. I see the following options
Hello, Magnus. I read the -bugs thread that resulted in this code and
choose not to comment since I thought that perhaps my understanding of
the implications of using SHFolder.dll v. Shell32.dll was in error.
However, installer code that I had authored before that works on both
98, XP, and NT does:
module = LoadLibrary(SHFolder.dll);
if (module != NULL) {
getfolderv1 = GetProcAddress(module, SHGetFolderPathA);
...
invoke function, deal with ANSI path
...
FreeLibrary(module);
} else {
module = LoadLibrary(shell32.dll);
if (module != NULL) {
getfolderv2 = GetProcAddress(module, SHGetSpecialFolderLocation);
...
invoke function, deal with UNICODE path
...
FreeLibrary(module);
} else {
throw an exception here...
}
}
I think the way to guarantee success is to ship the redistributable dll,
shfolder.dll with the application, which would eliminate the need to try
and fall back to shell32.dll. shfolder.dll is redistributable:
http://www.microsoft.com/downloads/details.aspx?FamilyID=6ae02498-07e9-48f1-a5d6-dbfa18d37e0fDisplayLang=en
This article explains what needs to be done to write an installer for
older platforms:
http://support.microsoft.com/default.aspx?scid=kb%3BEN-US%3B227051
Note:
Important: SHGetFolderPath is new to the Windows 2000 API. If you call
SHGetFolderPath from an application that can be installed on a previous
version of Windows, then you will need to redistribute the file
SHFolder.dll with your application.
as does this one:
http://support.microsoft.com/default.aspx?scid=kb%3BEN-US%3BQ241733
My code expects to find an shfolder.dll on Windows 2000 systems and a
shell32.dll on = Windows 2000 systems. As I said, I *believe* you can
guarantee success by just shipping shfolder.dll with the application.
Hope that helps,
Mike Mascari
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 8.0RC1 tomorrow
Tom Lane wrote: The core committee has agreed that it's about time to advance to Release Candidate status (which we define as code is frozen, but not docs nor message translation work). Barring surprises, 8.0RC1 will be wrapped tomorrow (Friday). We never really issued a call for port reports as has been past practice. I think that Andrew Dunstan's build farm has partially obsoleted that custom, but if you have access to a platform that is not represented in the build farm, please do give it a try soon. Tom, Will ANALYZE continue to ignore columns whose data is composed entirely of NULL in 8.0? http://archives.postgresql.org/pgsql-performance/2004-11/msg00363.php Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] making pdf of docs
Dennis Bjorklund wrote: Is there something wrong that makes it impossible to build the doc as a pdf? I started a build 4 hours ago, and it has still not finished (stuck at 100% CPU on my old 800Mhz 1G RAM machine). I know that openjade is very slow so for the first 3 hours I didn't worry. Now I'm starting to think that it will never finish. I've never tried building PDF from PostgreSQL DocBook source. However, in other DocBook documents, I've found that if there is an embedded image that is too large to fit on a single page, various PDF renderers will paginate the image onto the next page, discover it is too large to fit on the next page, generate a page break, and the process continues ad infinitum. Maybe a recent large image was added to the docs? FWIW, Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Wow - I always thought Peter WAS on the core committee Who is on it? See http://developer.postgresql.org/bios.php What ever happened to the idea of specially recognizing Thomas Lockhart and Vadim Mikheev in a Hackers Emeritus section? Eh? Mike Mascari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] monetary bug
Dennis Bjorklund wrote: On Sun, 22 Aug 2004, Peter Eisentraut wrote: To me, this seems completely wrong-headed. Data types should be defined by what operations you can do on them, not by what output format they have. I totally agree, lets get rid of money all together. If not, what makes money so special? Do we want other numeric types like hexnumber, octalnamber, weight, length, ... All of these are different ways to format a number in a user interface. MONEY seems odd because it is interpreting its internal representation based upon locale and the locale is also determining its possible representation, so one database's MONEY isn't really the same type as another database's MONEY. However, Date Darwen's type model suggests that a database should have support for types like WEIGHT, LENGTH, and TEMPERATURE, although they could certainly be left for the user to define. They define possible representations and THE_ functions as the means to support multiple units (among other purposes.) For example, a LENGTH type would have the following selector functions: LENGTH LENGTH_IN_INCHES(NO_OF_INCHES RATIONAL); LENGTH LENGTH_IN_FEET(NO_OF_FEET RATIONAL); LENGTH LENGTH_IN_CM(NO_OF_CM RATIONAL); Its internal representation would be irrelevant to the user, although the way PostgreSQL's type extensibility system works, it would need to have a default unit. It would also have THE_ functions like: RATIONAL THE_NO_OF_INCHES(LENGTH); RATIONAL THE_NO_OF_FEET(LENGTH); RATIONAL THE_NO_OF_CM(LENGTH); A DISPLAY() function is invoked to display the type in its default representation and if one is not defined, an error occurs in D D's model. If there must be one, then it would generate unambiguous output like: '8.13 inches' And of course, the various types would be constrained appropriately. One couldn't have a negative LENGTH or a TEMPERATURE under absolute zero, as examples. I think it would be neat to have an external library supporting a large set of types like these. Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Tom in Doom3?
Christopher Kings-Lynne wrote: Hey Tom, Did you rate a mention in the Doom 3 readme file? :) --- 4. COPYRIGHT INFORMATION DOOM 3 is linked with the JpegLib, copyright (c)1991-1998 Thomas G. Lane/Independent JPEG Group. All rights reserved. --- Cool :) I remember Lamar Owen had found some site which determined the major contributors to open source software and it read something like: 1. UC Berkeley 2. MIT 3. Tom Lane 4. Carnegie Mellon 5. IBM I wish I had the link... Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Preliminary PITR documentation available
Christopher Kings-Lynne wrote: People have been complaining (not without reason) that without any documentation they can hardly test the new PITR code. I have hacked up and committed some rough docs that should be enough for testing. They're on-line now at http://candle.pha.pa.us/main/writings/pgsql/sgml/backup-online.html and should within a few hours be visible at http://developer.postgresql.org/docs/postgres/backup-online.html (but right now the latter page contains only an introductory blurb). I love this: The ability to restore the database to a previous point in time creates some complexities that are akin to science-fiction stories about time travel and parallel universes. Is it science-fiction, or just relativity? Mike Mascari ---(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] ffunc called multiple for same value
Tom Lane wrote: So I'm rather inclined to define this behavior as not a bug. The fact that you're complaining seems to indicate that your ffunc scribbles on its input, which is bad programming practice in any case. Ordinarily I would not think that an ffunc should have any problem with being executed repeatedly on the same final transvalue. (If you really want to do things that way, maybe your code should take responsibility for keeping a flag to execute just once, rather than pushing the cost onto everybody.) Comments anyone? As someone who makes use of C language aggregate functions, I agree with your analysis, so long as the fact that an ffunc may be invoked more than once is well documented, (i.e. an SGML note section might be nice.) Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Anoncvs down?
Marc G. Fournier wrote: temporarily while I figure out what I screwed up that allowed a hacker to make use of he anoncvs account :( and, no, anoncvs doesn't have access to the core cvsroot ... Did it have anything to do with this CERT advisory? http://groups.google.com/groups?hl=enlr=ie=UTF-8selm=40B74B73.6080702%40mascari.com Mike Mascari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Recovery Features
Simon Riggs wrote: On Mon, 2004-07-05 at 23:40, Mike Mascari wrote: hmmm...not sure I know what you mean. It is very-very-close-to-impossible to edit the transaction logs manually, unless some form of special-format editor were written for the purpose. Is it clear that the PITR features are completely different from pg_dump? (Which would allow a manual edit and recover). The xlogs are binary files that refer to all changes to all tables in a cluster ordered by time, rather than by table. What I meant by hand-restore was 1. A total backup occurrs on Monday morning 2. Transactions occur on Monday, Tuesday and Wednesday, with PITR archiving enabled 3. Intern deletes everyting from obscure_table on Thursday afternoon and wants to restore it as it was on Thursday morning 4. On some other machine, the total backup is restored into a new cluster, the transaction logs replayed to that point-in-time where intern deleted everything from obscure_table 5. The table is dumped manually and restored in the production database, because it is known that this table has no logicial implications for the consisetency of other tables. That's what I meant by hand-restore. Mike Mascari ---(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] LinuxTag wrapup
Jeroen T. Vermeulen wrote: That about covers the important stuff. Some more for the other bucket (although they all came repeatedly): - so how do I pronounce Postgre? ... On the Postgre point, I remarked to some friendly people (who are developing a content management system based on postgres, by the way) that we ought to have something like just call me Postgres posters in our booth. It turned out they had the gear to cut stickers in letter shapes, so a little while later we actually had those words plastered over our booth walls. I think we got most interested passers-by before they had a chance to read it, though. I've argued for years that postgresql.org's front banner should read: Postgres + SQL = PostgreSQL The fact that novices can't pronounce the name correctly is a problem. People will be afraid to raise the possibility as a solution in the enterprise if they think they'll look like a fool pronouncing the name aloud. I remember back in '94 being corrected when talking about Linux in the enterprise - and I was corrected in the wrong direction. Someone needs to poke the propaganda minister with a stick. Mike Mascari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
Thomas Swan wrote: Alvaro Herrera wrote: Yes, I was thinking about this because the current code behaves wrong if a BEGIN is issued and not inside a transaction block. So we'd need to do something special in SPI -- not sure exactly what, but the effect would be that the function can't issue BEGIN at all and can only issue SUBBEGIN. Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT should be sufficient regardless of the level. If you are inside a current transaction those commands start a new transaction inside of the current transaction level, just like pushing on and popping off elements on a stack. How about this radical idea: Use SAVEPOINT to begin a subtransaction and ROLLBACK TO SAVEPOINT to abort that subtransaction. Normally, in Oracle, I would write code like: SAVEPOINT foo; do work IF (error) THEN ROLLBACK TO SAVEPOINT foo; END IF; Could we not treat a subtransaction as an anonymous savepoint until savepoints are added? So the above in PostgreSQL would read: SAVEPOINT; do work IF (error) THEN ROLLBACK TO SAVEPOINT; END IF; My old SQL3 draft EBNF reads: savepoint statement ::= SAVEPOINT savepoint specifier savepoint specifier ::= savepoint name | simple target specification savepoint name ::= identifier and rollback statement ::= ROLLBACK [ WORK ] [ AND[ NO ] CHAIN ] [ savepoint clause ] savepoint clause ::= TO SAVEPOINT savepoint specifier Mike Mascari ---(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] warning missing
Joshua D. Drake wrote: Hello, You all are behind... Python is king. Just to throw more fuel on the fire. Relvar inheritance is, according to Chris Date, one of the two Great Blunders in database engineering over the past twenty years. Multiple Domain Inheritance: Yes Relation Variable Inheritance: No I think it'd be a fair statement that Date Darwen would have the relvar inheritance ripped out of PostgreSQL as an experiment gone bad... Mike Mascari P.S.: D is the language of the future: http://www.digitalmars.com/d Ha! ---(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] placeholder syntax
Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Abhijit Menon-Sen [EMAIL PROTECTED] writes: Should Postgres accept ? as a placeholder? In short, I think this notation sucks and I don't want to emulate it. Certainly it sucks. Unfortunately it's the supported ODBC API which is emulated by everyone else, including JDBC and DBI. So the world's pretty much stuck with it. However this isn't Postgres's problem. If you want to write code that works with multiple databases then you're going to want to be using something like ODBC or JDBC or DBI anyways. In which case it's the driver's responsibility to provide the standard API which includes translating ? into appropriate syntax for the database. This brings back memories. This is how the whole Access hack for the parse-time transformation of '= NULL' to 'IS NULL' came about. IIRC, older versions of Access would invoke SQLPrepare() with a statement like: SELECT * FROM employees WHERE employeeid = ? then invoke SQLBindParameter() with NULL as the value, followed by SQLExecute() and the backend would receive: SELECT * FROM employees WHERE employeeid = NULL Later versions of one of the Access components (jet, mdac, access.exe - who knows where) changed its behavior and never performed similarly... Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] I just got it: PostgreSQL Application Server -- a new
Thomas Hallgren wrote: Carl E. McMillin [EMAIL PROTECTED] writes: My name is Carl E. McMillin and I'm still establishing my balance in this particular knowledge domain with its nomenclature and entities. Ok, I was thinking more the name behind [EMAIL PROTECTED] ;-) Exactly. I think it's Bill Gates leading a secret life... Mike Mascari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Win32, PITR, nested transactions, tablespaces
Marc G. Fournier wrote: Right now, I'd say feature freeze is looking more like next Friday (June 4th), and we're evaluate it then ... that should hopefully give the above time to flesh out and get into CVS ... Speaking of CVS, a CERT advisory was issued yesterday documenting a vulnerability: http://www.us-cert.gov/cas/techalerts/TA04-147A.html For what it's worth, Mike Mascari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Call for 7.5 feature completion
Mario Weilguni wrote: Interesting. We have made COMPLETELY different experiences. There is one question people ask me daily: When can we have sychronous replication and PITR?. Performance is not a problem here. People are more interested in stability and enterprise features such as those I have mentioned above. I doubt that. Having deployed several 7.4 databases, the first customers ask (of course not in technical speech, but in the meaning) when the problem with checkpoint hogging system down is solved. This is a really serious issue, especially when using drbd + ext3. The system will become really unresponsive when checkpoint is running. I heavily await 7.5 because of the background writer. This thread reminds me of Andrew Sullivan's signature: The plural of anecdote is not data - Roger Brinner Of course, once the sample size becomes sufficiently large, it does become data. Has the advocacy group performed any polling in this area that might shed some light as to what users and potential users might want? Mike Mascari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Call for 7.5 feature completion
Greg Stark wrote: Simon Riggs [EMAIL PROTECTED] writes: I can't complete by 1 June. Think worse of me if you choose. ... So in my perfect world I picture 7.5 freezing June 1 and releasing in July or so, giving a nice reliable simple upgrade for people who just want a safe 7.x series to upgrade to even after 8.0 comes out. PITR, nested transactions going into the CVS tree sometime in June or July and being frozen as 8.0 towards the end of the year. A quick google of 7.4 Win32 release will reveal that the above was precisely what was said about 7.4: it would be released to not hold up important features like the IN optimization and a quick 7.5 would have Win32 and PITR. It's almost as if a cron job reposts this thread every 6 - 12 months. For those of us that are desirous of PITR, it's a 6 month reposting that is becoming painful to read... Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Call for 7.5 feature completion
Marc G. Fournier wrote: On Mon, 17 May 2004, Mike Mascari wrote: A quick google of 7.4 Win32 release will reveal that the above was precisely what was said about 7.4: it would be released to not hold up important features like the IN optimization and a quick 7.5 would have Win32 and PITR. It's almost as if a cron job reposts this thread every 6 - 12 months. For those of us that are desirous of PITR, it's a 6 month reposting that is becoming painful to read... k, let's think this through ... 7.4 was released, what, 6 months ago? And 6 months later, PITR still isn't ready? Is there some logic here that if 7.4 wasn't released, PITR would have been done any sooner? Not being the author, I don't know. And in the case of PITR, the pre-7.4 author is different than the post-7.4 author. However, if I was personally responsible for holding up the release of a project due to a feature that I had vowed to complete, I would feel morally compelled to get it done. If I had then asked for, and was granted, an extra 15-30 days I would feel even more personally responsible and under greater pressure. If, however, the project made the release without waiting, I would feel simultaneously relieved and possibly a little bitter. Possibly a little bitter in that either what I was working on wasn't perceived as sufficiently valuable to hold up a release for 15-30 days, or that my word regarding the completion status was insufficient for the project to trust me. Let me reiterate the words possibly and little. But in open source projects, a developer willing to contribute hundreds, possibly thousands of hours of his own time is particularly invaluable. I can tell you that, in economic models that have studied human behavior with respect to unemployment insurance, for example, the re-employment rates are clustered at the tails: when someone is first unemployed and when the insurance is about to expire. It's an inappropriate analogy because the project lives on from release to release, instead of having a drop-dead date at which point no future changes would be made ad infinitum, but it paints a useful picture. I'm willing to bet that CVS commit rates mirror the above behavior. Unlike unemployment benefits, releasing the software without the feature essentially just extends the development period another 6 months, the work will intensify at the new perceived tails, and the process repeated. There are probably econometric papers that model the software development release cycle that could give quantitative arguments. I'm not arguing I'm right and your wrong, btw. I'm just pointing out some of the possibilities. In fact, for one developer it might be the code production maximizing condition to give them another 6 months and for another, creating the pressure associated with a 15-30 day extension where the world is standing still awaiting their patch... Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SPI and bytea columns
Wolfgang Reichart wrote: hello! i want to insert jpeg pictures into a bytea field and i implemented a SPI extension. this function reads the jpeg-file from the filesystem, converts it into a octal-coded string to pass the sql-parser, and then exec's the insert statement. this is rather slow, and i'd like to insert using lower level postgres functions that handle binary data without base64- or escaped en-/decoding. did anyone something like this in the past? Does it necessarily have to be stored in a bytea field? Why not create your own type (CREATE TYPE) with utility functions to read the image file from disk? PostgreSQL really needs a maintained type library as a single project where people can contribute types, functions, operators, and aggregates, such as the recently discussed email type. Mike Mascari joke Just be sure not to actually compress/decompress the JPEG or you might get sued: http://www.technewsworld.com/perl/story/33518.html /joke ---(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] The features I'm waiting for.
David Garamond wrote: scott.marlowe wrote: I'm sure everybody has their own favorite feature. But I can say quite confidently that the upcoming release contains the most number of highly anticipated features ever. Nested transaction, 2-phase commit, Windows port... I mean these are all major stuffs. They are paving the way of deployments of Postgres in new areas and applications. Plus don't forget all the other sweet goodies like autovacuum and PITR. But the next release could also be the buggies version ever, due to the number of these new features. :-) If you randomly flipped 20% of the bits in the postgres binary you'd not find it to be more buggy than the Postgres95/early 6.x series... Mike Mascari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Phase 2 - Design Planning
Simon Riggs wrote: On Thu, 2004-04-29 at 16:09, Peter Eisentraut wrote: Perhaps that was the inspiration, but no, I definitely meant a CHECKPOINT. But now you come to mention it, it would be better just to have a command that simply wrote a named record to the xlog, so it can be searched for later... COMMENT [IN TRANSACTION LOG] 'starting payroll Feb04' FWFW, Oracle's COMMIT syntax has an optional COMMENT specifier which is used for documenting a distributed transaction. In-doubt transactions can then be manually committed or aborted by referencing the transaction associated with the comment. Example: COMMIT WORK COMMENT 'A complex distributed Tx'; Perhaps there is some common ground between the 2PC implementation and PITR? Mike Mascari ---(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] Function to kill backend
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: Killing backends with runaway queries is a routine administrative task. Cancelling runaway queries is a routine task. I'm less convinced that a remote kill (ie SIGTERM) facility is such a great idea. Of course, cancelling runaway queries on Oracle is only a necessity if the DBA hasn't made use of resource limits - PROFILEs. ;-) Mike Mascari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fuzzy cost comparison to eliminate redundant planning
Tom Lane wrote: I've been looking at the planner performance problem exhibited by Eric Brown: http://archives.postgresql.org/pgsql-performance/2004-03/msg00273.php While a nine-way join is inherently going to take some time to plan (if you don't constrain the search space with JOIN), it seemed to me that this particular query was taking even longer than I'd expect. ... I found that this reduced the planning time of Eric's query by about 40%, without changing the resulting plan. More great news, as always. IIRC you recently bumped the default GEQO threshold from eleven to twelve. With your new fuzzy comparison patch is twelve still the appropriate number? Or does the fuzzy comparison scale all planning time down and therefore the default threshold should remain where it is? Mike Mascari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] COMMENT ON [GROUP/USER]
Andrew Dunstan wrote: Bruce Momjian wrote: Another problem is that pg_description is per-database, while pg_user/group are global for all databases. databases are also per cluster, but we have comments on those. Could we keep the user/group comments in those tables instead of in pg_description? The comments are stored only in the database's pg_description where the COMMENT ON took place. This caused dump/reload problems. I believe Rod Taylor added the new warning: [EMAIL PROTECTED] select count(*) from pg_description; count --- 1541 (1 row) [EMAIL PROTECTED] COMMENT ON DATABASE test IS 'Hello'; WARNING: database comments may only be applied to the current database COMMENT [EMAIL PROTECTED] select count(*) from pg_description; count --- 1541 (1 row) [EMAIL PROTECTED] COMMENT ON DATABASE estore IS 'A good comment'; COMMENT [EMAIL PROTECTED] select count(*) from pg_description; count --- 1542 (1 row) [EMAIL PROTECTED] select count(*) from pg_description; count --- 1541 (1 row) Mike Mascari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] COMMENT ON [GROUP/USER]
Bruce Momjian wrote: This doesn't look good. If we throw a WARNING, why do we not insert anything into pg_description. Seems we should throw an error, or do the insert with a warning. It essentially makes the behavior deprecated and allows dumps to be restored properly (without the extra-database comments.) Here's a thread on the topic: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=bf1obi%24u7k%241%40FreeBSD.csie.NCTU.edu.twrnum=7prev=/groups%3Fq%3D%2522COMMENT%2BON%2BDATABASE%2522%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den I don't know if Rod has plans to change attempts to COMMENT ON non-local databases to an ERROR in 7.5 or not. It was my fault from the beginning - but once I'd implemented COMMENT ON for tables and columns I just couldn't stop... :-) Mike Mascari Mike Mascari wrote: .. The comments are stored only in the database's pg_description where the COMMENT ON took place. This caused dump/reload problems. I believe Rod Taylor added the new warning: [EMAIL PROTECTED] select count(*) from pg_description; count --- 1541 (1 row) [EMAIL PROTECTED] COMMENT ON DATABASE test IS 'Hello'; WARNING: database comments may only be applied to the current database COMMENT [EMAIL PROTECTED] select count(*) from pg_description; count --- 1541 (1 row) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Is indexing broken for bigint columns?
Dann Corbit wrote: http://www.phpbuilder.com/columns/smith20010821.php3?page=3 bigint indexes work fine. The queries probably referenced 32-bit integer constants that were neither quoted nor CAST. I always start bigint sequences at 5 billion. This ensures that client applications aren't assuming 32-bit quantities that will break once ~4.2 billion is reached and I get index scans without quoting or casting free. But IIRC there's a change in the development tree to jettison the requirement for quoting/casting... Mike Mascari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Is indexing broken for bigint columns?
Dann Corbit wrote: PostgreSQL is the only database that requires casts to do an index lookup. Possibly (quite probably) true, but you don't show any evidence that SQL*Server, Oracle, or MySQL uses indexes either. Like I said before, Tom (of course) already has a fix is already in the development branch: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=29832.1068682253%40sss.pgh.pa.usrnum=1prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26scoring%3Dd%26q%3Dbigint%2Bindex%2Bhackers%2Bpostgresql This is SQL*Server syntax: == ... select * from foo where bar = 1 ... This is Oracle syntax: == SQL select * from foo where bar = 1; ... mysql select * from foo where bar = 1; Mike Mascari ---(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] Summary of Changes since last release (7.4.1)
Simon Riggs wrote: - All operations on TEMP relations are no longer logged in WAL, nor are they involved in checkpoints, thus improving performance. (Tom) That is great news! Looking forward to 7.5 already, Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Summary of Changes since last release (7.4.1)
Christopher Kings-Lynne wrote: - All operations on TEMP relations are no longer logged in WAL, nor are they involved in checkpoints, thus improving performance. (Tom) That is great news! Looking forward to 7.5 already, I could have sworn that the above was done in 7.4, by Tom...? Yeah. 7.4 performed a lot better for me. I was a little too greedy in my hopes that the temp table issue hadn't been addressed and would see further temp table-related performance gains in 7.5. I see the CVS commit Aug. 6 before the pgindent run Sept. 4: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/storage/smgr/smgr.c?rev=1.58content-type=text/x-cvsweb-markup Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Summary of Changes since last release (7.4.1)
I wrote: Yeah. 7.4 performed a lot better for me. I was a little too greedy in my hopes that the temp table issue hadn't been addressed and would see further temp table-related performance gains in 7.5. I see the CVS commit Aug. 6 before the pgindent run Sept. 4: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/storage/smgr/smgr.c?rev=1.58content-type=text/x-cvsweb-markup Actually, that was an Aug 6, 2002 commit, not 2003 which would make it 7.3, right? So Simon, my I humbly ask from where you culled this change in CVS tip? Mike Mascari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] lock related issues...
Chris Bowlby wrote: Hi Simon, Thanks for the confirmation, I just wanted to make sure I was not going ape over it and getting confused. At 08:04 PM 1/28/04, Simon Riggs wrote: Chris Bowlby writes I'm looking for some details on how the locking system works in relation to transactions dealing with INSERTs and UPDATEs. The version of PostgreSQL is 7.3.2 p.152 of the 7.3.2 Users Guide, section 9.2.1 Read Committed Isolation Level applies to your situation as described A great description of concurrency issues is Tom Lane's O'Reilly presentation. After installing PostgreSQL, a message should be output to read it: http://conferences.oreillynet.com/presentations/os2002/lane_tom.tar.gz Mike Mascari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] returning PGresult as xml
Peter Eisentraut wrote: Brian Moore wrote: i feel badly that i have not been able to use any existing standards. xmlrpc, i found, was not type-rich enough, and that made it difficult or impossible to use. in particular, the only way to represent a matrix is as a struct of structs. this makes it very verbose for one to encode a PGresult. i found SOAP too difficult for compliance. so my result was to create a schema, which results in a DTD. Let me point out an implementation I made last time this subject was discussed: http://developer.postgresql.org/~petere/xmltable.tar.bz2 Also last time this subject was dicussed, I believe it was Mike Mascari who proposed and implemented another solution which is more client-side oriented. I humbly confess it wasn't me. We use CORBA Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CTTAS w/ DISTINCT ON crashes backend
Tom Lane wrote: The crash I'm getting can be boiled down to this: regression=# create table fooey(f1 int) without oids; CREATE TABLE regression=# insert into fooey values(11); INSERT 0 1 regression=# create temp table fooey2 as select distinct * from fooey; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ! I'm getting an Assert failure, which no doubt detects the problem much sooner than you were doing. The problem is in adding OIDs to rows that initially did not have 'em when returned from the SELECT DISTINCT plan. Okay. So your best immediate workaround is to create the first temp table with oids, or create the second one without. Thanks! Mike Mascari ---(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] Preventing stack-overflow crashes (improving on max_expr_depth)
Bruce Momjian wrote: Sounds like a great approach to me. If it doesn't work, we will find out during beta testing. Would it make sense to also have a nice little global function and/or macro available for the author of C-language recursive functions to perform a depth test before recursing? Mike Mascari [EMAIL PROTECTED] Tom Lane wrote: It occurred to me today that it would not be difficult to implement a direct check on the physical size of the execution stack. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] IEEE 754
Sai Hertz And Control Systems wrote: Dear all , I would like to share my concerns about the IEEE 754 specification and floating point handling by PostgreSQL . Also I would like to learn how professional users of PostgreSQL work with rounding of monetary terms . If you would like to know whats IEEE 754 read this http://docs.sun.com/source/806-3568/ncg_goldberg.html No sane human being would use floating point for monetary values. NUMERIC is an arbitrary precision type capable of effectively limitless scale and precision, although it is currently defined as having a limit of 1000: #define NUMERIC_MAX_PRECISION 1000 As far as rounding is concerned, it depends on the application. For example, in the United States at least, each state has devised their own rounding rules with respect to sales and use taxes. For a look at how very bad sausage is made: http://www.ecommercetax.com/official_docs/SSTP%20-%20Rounding.pdf Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] initdb should create a warning message [was Re: [ADMIN]
Bruce Momjian wrote: Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: On Sun, 2003-11-30 at 23:18, Neil Conway wrote: I do agree that we could stand to document the purpose of pg_clog and pg_xlog more clearly. However, this information belongs in the standard documentation, not scattered throughout $PGDATA. Then it needs to be stated very prominently. But the place to put a sign saying Dangerous cliff edge is beside the path that leads along it. How about changing the names of those directories? I thought about that, but what would we call them? We could change xlog to wal, I guess. That might actually be clearer. xlog could become xstatus or xactstatus or just xact. active_xdata active_cdata Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Is there going to be a port to Solaris 9 x86 in the
Robert Treat wrote: On Tue, 2003-11-18 at 17:31, Sailesh Krishnamurthy wrote: One step at a time :-) Actually a big problem is figuring out new pieces for the projects. Most of the items in the TODO list are way too much for a class project - we gave 'em 3 weeks to make the Hash GroupedAgg work for large numbers of unique values (by using a form of hybrid hashing). Another thing I toyed with was having an implementation of a Tid-List-Fetch .. sorting a TID-list from an index and fetching the records of the relation off the sorted list for better IO performance. AFAICT something like this isn't present yet .. can pgsql do this already ? While some form of bitmapped indexing would be cool, other ideas might be to implement different buffer manager strategies. I was impressed by how quickly Jan was able to implement ARC over LRU, but there are a host of other strategies that could also be implemented. Remember that interview with Jim Gray: http://www.acmqueue.org/modules.php?name=Contentpa=showpagepid=43 Certainly we have to convert from random disk access to sequential access patterns. Disks will give you 200 accesses per second, so if you read a few kilobytes in each access, you're in the megabyte-per-second realm, and it will take a year to read a 20-terabyte disk. If you go to sequential access of larger chunks of the disk, you will get 500 times more bandwidthyou can read or write the disk in a day. So programmers have to start thinking of the disk as a sequential device rather than a random access device. Isn't a TID-List-Fetch implementation a crucial first step in the right direction? Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is there going to be a port to Solaris 9 x86 in the
Robert Treat wrote: http://www-inst.eecs.berkeley.edu/~cs186/hwk0/index.html Are these screenshots of PgAccess on Mac OSX? It's pretty sad that Mike Stonebraker only has a salary of $15,000. ;-) I also thought this SIGMOD article was a nice read: http://www.acm.org/sigmod/record/issues/0309/4.JHdbcourseS03.pdf How about extra credit for PITR? Mike Mascari [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
Re: [HACKERS] [pgsql-advocacy] Not 7.5, but 8.0 ?
Joshua D. Drake wrote: Hello, If Win32 actually makes it into 7.5 then yes I believe 8.0 would be appropriate. It might be interesting to track Oracle's version number viz. its feature list. IOW, a PostgreSQL 8.0 database would be feature equivalent to an Oracle 8.0 database. That would mean: 1) PITR 2) Distributed Tx 3) Replication 4) Nested Tx 5) PL/SQL Exception Handling IMHO, a major version number jump should at least match the delta in features one finds in the commercial segment with their major version number bumps. Otherwise, I suspect it would be viewed as window dressing... Could be wrong, though... Mike Mascari [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] Some thoughts about i/o priorities and throttling vacuum
Greg Stark wrote: Christopher Browne [EMAIL PROTECTED] writes: VACUUM is like putting an extra few transport trucks onto the highway. It may only go from one highway junction to the next, and be fairly brief, if traffic is moving well. But if traffic is heavy, it adds to the congestion. (And that's as far as the analogy can go; I can't imagine a way of drawing the GUC parameter into this...) Ooh strained metaphors. This game is always fun. So I think of it the other way around. A busy database is like downtown traffic with everyone going every which way for short trips. Running vacuum is like having a few trucks driving through your city streets for through traffic. Having a parameter to slow down the through traffic is like, uh, having express lanes for local traffic. er, yeah, that's the ticket. Except who ever heard of having express lanes for local traffic. Hm. All I know is that Jan Wieck would have each car filled to the brim with spikes Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 2-phase commit
Bruce Momjian wrote: Peter Eisentraut wrote: Andrew Sullivan writes: Does the proposal of allowing dbas to run that risk, provided there's a mechanism to tell them about it, satisfy the objection (assuming, of course, 2PC can be turned off)? Why would you spent time on implementing a mechanism whose ultimate benefit is supposed to be increasing reliability and performance, when you already realize that it will have to lock up at the slightest sight of trouble? There are better mechanisms out there that you can use instead. If you want cross-server transactions, what other methods are there that are more reliable? It seems network unreliability is going to be a problem no matter what method you use. What is the stated goal of distributed transactions in PostgreSQL? 1) XA-compatibility/interoperability or 2) Robustness in the face of network failure The implementation choosen depends upon the answer, does it not? Is there an implementation (e.g. 3PC) that can simulate 2PC behavior for interoperability purposes and satisfy both requirements? Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Index/Function organized table layout
Hannu Krosing wrote: Greg Stark kirjutas P, 05.10.2003 kell 00:17: I've never seen anyone use this feature, and I never seriously considered it myself. It sort of has the feel of an antiquated feature that traded too much flexibility and abstraction for raw performance on very slow disk hardware. Read A Conversation with Jim Gray referenced from this slashdot article: http://slashdot.org/article.pl?sid=03/09/17/1246255mode=threadtid=126 for info on how disk drives are slower than ever (relatively), and how one should treat them as such, especially for large data volumes. Too bad PostgreSQL is misspelled (Postgress) and MySQL dominates the open source discussion. And the MySQL questions are coming from: David Patterson, who holds the Pardee Chair of Computer Science at the University of California at Berkeley. Outrageous! :-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Dreaming About Redesigning SQL
Christopher Browne wrote: After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Seun Osewa) belched out...: This is for relational database theory experts on one hand and imlementers of real-world alications on the other hand. If there was a chance to start again and design SQL afresh, for best cleaness/power/performance what changes would you make? What would _your_ query language (and the underlying database concept) look like? There are two notable 'projects' out there: 1. There's Darwen and Date's Tutorial D language, defined as part of their Third Manifesto about relational databases. I read the Third Manifesto. There are many ideas in the TTM that have strong arguments, although I most confess I haven't read any critiques. A few (of many) points: 1) Strict adherence to the relational model, where all of SQL's short-comings are addressed: A) No attribute ordering B) No tuple ordering (sets aren't ordered) C) No duplicate tuples (relations are sets) D) No nulls (2VL sufficient. Missing information is meta-data) E) No nullogical mistakes (ex: SUM of an empty relation is zero, AVG is an error) F) Generalized transitive closure G) Declared attribute, relation variable, and database constraints, including transition constraints H) Candidate keys required (this has positive logical consequences for the DBMS implementor) I) Tuple and relation-valued attributes J) No tuple-level operations a bunch more... 2) The query language should be computationally complete. The user should be able to author complete applications in the language, rather than the language being a sublanguage. This reverses Codd's query sublanguage proposed in A Relational Model of Data for Large Shared Data Banks http://www.acm.org/classics/nov95/s1p5.html sarcasm Thanks ACM for just putting part of the paper on-line, complete with broken links and spelling errors! /sarcasm 3) The language (a D implementation) would ensure a separation between the logical design of the application and the physical implementation. The programmer should think in terms of the evaluation of relational algebraic expressions, not manipulating physical records in disk blocks in a file. 4) The type system should separate the actual, internal representation from the possible representation, of which there might be many. For example, a POINT may be internally expressed in cartesian coordinates but may supply both polar and cartensian THE_ operators. 5) The type system should implement D D's view of multiple inheritance, where read-operators are inherited but write-operators aren't. This eliminates the Is a Circle an Ellipse? dilemma imposed by C++, for example. IOW, in a D language, a Circle is an Ellipse. They reject Stonebreaker's ideas of OIDs and relation variable inheritance, which of course, are in PostgreSQL. It's a very provocative read. At a minimum, one can learn what to avoid with SQL. The language looks neat on paper. Perhaps one day someone will provide an open source implementation. One could envision a D project along the same lines as the same sort of project that added SQL to Postgres... But I'd rather have PITR :-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 2-phase commit
Marc G. Fournier wrote: On Fri, 26 Sep 2003, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: You're not considering the possibility of a transient communication failure. Can't the master re-send the request after a timeout? Not it can, but it has to. The master *must* keep hold of that request forever (or until the slave responds, or until we reconfigure the system not to consider that slave valid anymore). Similarly, the slave cannot forget the maybe-committed transaction on pain of not being a valid slave anymore. Hr ... is there no way of having part of the protocol being a message sent back that its a valid/invalid slave? ie. slave has an uncommitted transaction, never hears back from master to actually do the commit, so after x-secs * y-retries any messages it does try to send to the master have a bit flag set to 'invalid'? If I understand Andrew Sullivan's request, the purpose for integration of 2-PC into PostgreSQL, is more for distributed query than replication via an XA interface: http://sybooks.sybase.com/onlinebooks/group-xsarc/xsge/xatuxedo/@ebt-link;pt=61?target=%25N%13_446_START_RESTART_N%25 If that is the desire (XA-compatibility) then PostgreSQL might be talking to an Oracle database or a BEA Tuxedo TPM acting as the coordinator. So PostgreSQL won't have an opportunity to modify the protocol in any meaningful way if it wishes to interoperate with XA-based transaction managers. If it is being used only amongst other PostgreSQL backends for replication, then why not use one of the optimistic replication protocols: http://www.inf.ethz.ch/personal/alonso/PAPERS/commit-fast.pdf Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 2-phase commit
Bruce Momjian wrote: I haven't seen any comment on this email. From our previous discussion of 2-phase commit, there was concern that the failure modes of 2-phase commit were not solvable. However, I think multi-master replication is going to have similar non-solvable failure modes, yet people still want multi-master replication. We have had several requests for 2-phase commit in the past month. I think we should encourage the Japanese group to continue on their 2-phase commit patch to be included in 7.5. Yes, it will have non-solvable failure modes, but let's discuss them and find an appropriate way to deal with the failures. FWIW, Oracle 8's manual for the recovery of a distributed tx where the coordinator never comes back on line is: https://www.ifi.uni-klu.ac.at/Public/Documentation/oracle/product/8.0.3/doc/server803/A54643_01/ch_intro.htm#7783 If a database must be recovered to a point in the past, Oracle's recovery facilities allow database administrators at other sites to return their databases to the earlier point in time also. This ensures that the global database remains consistent. So it seems, for Oracle 8 at least, PITR is the method of recovery for cohorts after unrecoverable coordinator failure. Ugly and yet probably a prerequisite. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Single-file DBs WAS: Need concrete Why Postgres
Josh Berkus wrote: Jan, In my experience (a lot of MS SQL, more MS Access than I want to talk about, and a little Oracle) corruption failures on single-file databases are more frequent than databases which depend on the host OS, and such failures are much more severe when the occur. Vadim seemed to think differently: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=00030722102200.00601%40lorc.wgcr.orgrnum=9prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DVadim%2Bsingle%2Bfile%2Bpostgres In addition to Jan's points, using a single pre-allocated file also reduces file descriptor consumption, although I don't know what the costs are regarding maintaining the LRU of file descriptors, the price of opens and closes, the price of having a high upper limit of file descriptors, etc. Just because Oracle and MS do something doesn't necessary make it wrong. :-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Adjustment of spinlock sleep delays
Tom Lane wrote: I've been thinking about Ludwig Lim's recent report of a stuck spinlock failure on a heavily loaded machine. Although I originally found this hard to believe, there is a scenario which makes it plausible. Suppose that we have a bunch of recently-started backends as well as one or more that have been running a long time --- long enough that the scheduler has niced them down a priority level or two. Now suppose that one of the old-timers gets interrupted while holding a spinlock (an event of small but nonzero probability), and that before it can get scheduled again, several of the newer, higher-priority backends all start trying to acquire the same spinlock. The acquire code looks like try to grab the spinlock a few times, then sleep for 10 msec, then try again; give up after 1 minute. If there are enough backends trying this that cycling through all of them takes at least 10 msec, then the lower-priority backend will never get scheduled, and after a minute we get the dreaded stuck spinlock. To forestall this scenario, I'm thinking of introducing backoff into the sleep intervals --- that is, after first failure to get the spinlock, sleep 10 msec; after the second, sleep 20 msec, then 40, etc, with a maximum sleep time of maybe a second. The number of iterations would be reduced so that we still time out after a minute's total delay. Comments? Should there be any correlation between the manner by which the backoff occurs and the number of active backends? Mike Mascari [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] Farewell
D'Arcy J.M. Cain wrote: On Monday 11 August 2003 00:36, Vadim Mikheev wrote: FarewellIt's time for formal acknowledgement that I'm not in The Project any more. I'm not interested in small features/fixes and have no time for big ones. It was this way for very long time and I don't see how/when that could change. My participation in The Project was one of the greatest adventures in my life. Thanks to everyone! I know that a lot of what makes PostgreSQL what it is today is can be directly atttributed to your work. Thank you. And thank you for the personal help back when I was working on the PostgreSQL trigger documentation. It's hard to imagine PostgreSQL with out MVCC, WAL, subselects, etc. You know, maybe on the Developer's page there should be a PostgreSQL Hall of Fame with Vadim and Thomas Lockhart being the first two inductees. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] how do i turn off the html tags??
Jenny - wrote: http://www.expita.com/nomime.html Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] this is in plain text (row level locks)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Sailesh Krishnamurthy wrote: Does pgsql only record X locks on the individual tuples on-disk or does it do so for S locks as well ? We don't need to shared lock individual rows because of MVCC --- well, we sort of do by recording our xid in our proc structure, so folks don't change things underneath us. We prevent expired rows from disappearing from the disk by others looking at our proc start xid. This is actually an issue though. Row-level shared locks would be really nice to have for foreign-key handling. Right now we have to use X locks for those, and that leads to deadlocking problems for applications. Yes! Yes! It's the last big hurdle for an otherwise excellent RI implementation... Just wanted Joe-user's enthusiasm for row-level S locks registered somewhere... :-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Two weeks to feature freeze
I wrote: Tom Lane wrote: Basically, the subordinate must be willing to hold its breath *forever*. Yep. And if the cohort crashes while waiting for the coordinator to come back on-line, if I understand the world correctly, it must be capable of committing the database changes associated with the COMMIT-VOTE response it supplied to the coordinator's PREPARE. It seems this would require REDO? And yet there are thousands of installed distributed databases running enterprises every day. Please ignore the REDO remark. It's late where I am... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Two Phase Commit WAS: Re: Two weeks to feature freeze
Rod Taylor wrote: Perhaps the people on this list who are pushing 2PC could do the ground work? - 2PC is better than a standard transaction when dealing with multiple servers as it can recover in some circumstances (but not all). - 2PC (XA support as described by the X/Open group) is the only implementation of distributed transactions supported by many third party components -- that I'm aware of -- to the point where it is a part of the Java Spec on dealing with distributed transactions. - 2PC isn't very good in a number of circumstances, as such PostgreSQL should avoid its use when PostgreSQL has a choice in the matter -- like communication with other PostgreSQL servers. This is a case of learning to speak Japanese because all of the people you want to talk with only speak Japanese. It simply doesn't matter how good Esperanto is. I don't think it could have been said any better. There are a host of improvements on the standard 2PC protocol, including 3PC, multi-cast 2PC, and other variants both synchronous and asynchronous. But if PostgreSQL is going to work with XA, then it doesn't get to choose the TM or the protocol. The only relevance of this thread, as I see it, is whether or not core will stomach an XA-compatible 2PC implementation in the backend. If not, then is Satoshi Nagayasu in vain? That was what I sensed in the original thread 6 months ago, that the 2PC work being done by Satoshi Nagayasu was going to be allowed to die on the vine. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Two weeks to feature freeze
- Original Message - From: Bruce Momjian [EMAIL PROTECTED] Tom said that our low-hanging fruit is gone and only hard items are left. This is certainly true. What is hard to accept is that those big items take _weeks_ of focused development, and we just don't have enough full-time developers who can spend that amount of time to do them. The sad truth is that there is alway something _else_ to do, rather than block out weeks to code a complex feature. And these are usually features that can't be done incrementally, but require a huge input of time before there is any payback. I tried with Win32, and spent a few weeks getting us closer, but my other work of housecleaning (email/patches/cleanup), and marketing (speaking and tutorial preparation) just make it impossible to spend the time needed to complete a big item. And people were rightly upset that the patches weren't getting applied or cleanup done in a timely manner. It is depressing. I was disappointed that Satoshi Nagayasu's two-phase commit patches seemed to be implicitly rejected by lack of an enthusiastic response by any of the core members. Distributed query (not replication) would have been a very nice feature. It's what separates, in part, Oracle Enterprise Edition from the Standard Edition, and it appeared someone (Satoshi Nagayasu) was more than willing to get the ball rolling. But the flight path bothered some I guess so we got nothin' Mike Mascari [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] Two weeks to feature freeze
The Hermit Hacker wrote: On Sun, 22 Jun 2003, Bruce Momjian wrote: Mike Mascari wrote: I was disappointed that Satoshi Nagayasu's two-phase commit patches seemed to be implicitly rejected by lack of an enthusiastic response by any of the core members. Distributed query (not replication) would have been a very nice feature. It's what separates, in part, Oracle Enterprise Edition from the Standard Edition, and it appeared someone (Satoshi Nagayasu) was more than willing to get the ball rolling. But the flight path bothered some I guess so we got nothin' I sure want two-phase commit. I don't remember it as being rejected, and we certainly need it, independent of replication. I don't recall the patch itself :( Mike, do you recall the date(s) for this? Reasons for rejections? I choose my words poorly. A discussion arose regarding the 7.4 protocol changes. I suggested looking forward to allow for a 2PC implementation. Satoshi Nagayasu remarked about the work done on 2PC and posted a link to patches: http://snaga.org/pgsql/pgsql-20021025.tgz The thread was here: http://archives.postgresql.org/pgsql-hackers/2002-11/msg00143.php Various people critiqued the work that had been done - protocol change instead of a purely statement-driven implementation, the use of 2PC for sync. replication, etc. And that was the last (and first, IIRC) post from Satoshi Nagayasu. I was worried that PostgreSQL lost the opportunity to have a 2PC implementation, because no one followed up, allowing it to die on the vine. I have learned from Rod Taylor that lack of posts on hackers doesn't mean lack of work: They weren't ready to be committed at the time, nor are they now. The hardest parts are still to come (resume, forget, etc.). I believe he is still working on the third phase: http://snaga.org/pgsql/ -- Rod Taylor [EMAIL PROTECTED] So I stand corrected. Mike Mascari [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
Re: [HACKERS] Two weeks to feature freeze
Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Hrmmm, I see Tom's point (I think!) ... but what if, for instance, the co-ordinator crashes? Or you just lose the network connection for awhile. The worst case scenario I think is where the co-ordinator got everyone's promise to commit, and told some of the subordinates to commit, but your own response gets lost due to network failure. Now what? If you time out and decide to abort, you're inconsistent with the other subordinates. On the other hand, you can't commit after a timeout either, because that loses in the other scenario (where the coordinator didn't decide to commit). Basically, the subordinate must be willing to hold its breath *forever*. Yep. And if the cohort crashes while waiting for the coordinator to come back on-line, if I understand the world correctly, it must be capable of committing the database changes associated with the COMMIT-VOTE response it supplied to the coordinator's PREPARE. It seems this would require REDO? And yet there are thousands of installed distributed databases running enterprises every day. A paper on a A New Presumed Commit Optimization for Two Phase Commit describes the cohort as: If a prepared cohort does not receive a transaction outcome message promptly, or crashes without remembering the outcome, the cohort asks the coordinator for the outcome. It keeps on asking until it gets an answer. (This is the blocking aspect of 2PC.) I'd just like to point out that: 1) The XA interface defines a 2PC protocol library which allows transaction managers, such as BEAS Tuxedo (and Oracle, for that matter) to use the database in a distributed transaction. Lack of an XA interface for PostgreSQL prohibits its use in major enterprise applications. BEAS Tuxedo can talk to PostgreSQL, but won't allow it to participate in a distributed tx. 2) The users of distributed databases will/should/can know that a cohort will block waiting for the coordinator. We're not talking asynchronous multi-master replication of 4 databases distributed over low-speed communication lines across the country. We're talking about the sales dept. database having a few linked tables to the accounting dept. database, where inserts into the one result in inserts into the other. Mike Mascari [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] Broken RR?
I wrote: Rasmus Resen Amossen wrote: Does Postgres garantee repeatable-read (RR) during transactions? And does it implement ARIES/KVL? If so, why is the following possible? T1: begin; T1: select * from table; (notice the row with id = X) T2: begin; T2: delete from table where id = X; T1: select * from table; (notice the row with id = X suddenly is gone) http://www.postgresql.org/docs/view.php?version=7.3idoc=0file=transaction-iso.html#XACT-SERIALIZABLE Whoops. Sorry. I though this was confusion regarding phantom rowsand READ COMMITTED vs. SERIALIZABLE. Nevertheless, I cannot repeat the above... Mike Mascari [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
Re: [HACKERS] Broken RR?
Rasmus Resen Amossen wrote: Does Postgres garantee repeatable-read (RR) during transactions? And does it implement ARIES/KVL? If so, why is the following possible? T1: begin; T1: select * from table; (notice the row with id = X) T2: begin; T2: delete from table where id = X; T1: select * from table; (notice the row with id = X suddenly is gone) http://www.postgresql.org/docs/view.php?version=7.3idoc=0file=transaction-iso.html#XACT-SERIALIZABLE This should probably have been posted to the novice, sql, or general mailing as well, BTW. But that is also mentioned in the on-line pages: ;-) http://www.postgresql.org/lists.html This list is for the discussion of current development issues, problems and bugs and the discussion of proposed new features. If people in the other lists don't know the answer to a question and it is likely that only a developer will know the answer, you may re-post that question here. You must try elsewhere first! HTH, Mike Mascari [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
[HACKERS] A few notes
Hello. I thought I'd just toss out a few thoughts: 1) Should a link to the release changes for 7.3.3 be on the website? I had to look into the web-interface of CVS to see what was actually changed. 2) It would be nice if some regular performance tests could be done upon every release on some stock machine whose configuration never changes to give some numerical hints as to the value of an upgrade. 3) I got bit by using the explicit join syntax just like Thomas Lockhart had predicted. I then removed the syntax to let the planner do its job. Queries which took around 10 seconds took 5 minutes. I then disabled GEQO and the queries ran in around a second. I noticed that the explicit join syntax will no longer confine planning choices in 7.4, but is it possible the GEQO threshold, as a default, is too low? Mike Mascari [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
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Hannu Krosing wrote: Tom Lane kirjutas K, 12.03.2003 kell 18:19: Actually, my hope is to eliminate that business entirely by standardizing the on-the-wire representation for binary data; note the reference to send/receive routines in the original message. For integer data this is simple enough: network byte order will be it. I'm not sure yet what to do about float data. Use IEEE floats or just report the representation in startup packet. the X11 protocol does this for all data, even integers - the client expresses a wish what it wants and the server tells it what it gets (so two intel boxes need not to convert to network byte order at both ends). IIOP/CDR behaves similarly for performance reasons- receiver makes it right. It also defines a representation for all of the CORBA idl basic types, wide characters, fixed-point types, structures, etc. A far-reaching, wild suggestion would be to replace the postmaster with a CORBA-based server process with a well defined interface. At a minimum, if a binary protocol is the ultimate destination, perhaps some of the mapping of various types could be borrowed from the specs. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Odd subselect in target list behavior WRT aggregates
Tom Lane wrote:
Mike Mascari [EMAIL PROTECTED] writes:
Hello. I have some code which generates subselects in the target
list of a query and then aggregates the results. The code allows
the user to determine the attributes of the aggregation. If the
user chooses to aggregate on the same value twice, I get the
Sub-SELECT error. If the user chooses a different second
attribute of aggregation, no error occurs. Is that correct
behavior?
This seems to be fixed as of 7.3, though I do not recall a previous
bug report like it.
Thanks, Tom. I should have tried the current version before posting.
However, I wonder why you are doing it like that, and not with a join:
SELECT SUM(p.dstqty) as agg,
date_trunc('hour', sales.active) as field1,
date_trunc('day', sales.active) as field2
FROM purchases p, sales
WHERE p.purchase = sales.purchase
AND ...
GROUP BY 2,3;
The multiple-sub-select approach will require a separate probe into
sales to retrieve each of the fields; there's no optimization across
different subselects.
Normally, the grouping is done on two or more distantly related
pieces of data:
How many widgets were sold by John on Mondays?
What is the most popular hour for sales by quarter?
etc.
So the nature of the data is such that to dynamically generate
the proper joins in the FROM/WHERE clause was too complex (for
me). :-)
Thanks again,
Mike Mascari
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Odd subselect in target list behavior WRT aggregates
Hello. I have some code which generates subselects in the target
list of a query and then aggregates the results. The code allows
the user to determine the attributes of the aggregation. If the
user chooses to aggregate on the same value twice, I get the
Sub-SELECT error. If the user chooses a different second
attribute of aggregation, no error occurs. Is that correct
behavior? The only difference between Query #1 and Query #2 is
that the second subselect in the target list of Query #2
aggregates on the 'day' of a sale as opposed to the 'hour':
Query #1
SELECT SUM(p.dstqty) as agg,
(SELECT date_trunc('hour', sales.active)
FROM sales
WHERE p.purchase = sales.purchase) as field1,
(SELECT date_trunc('hour', sales.active)
FROM sales
WHERE p.purchase = sales.purchase) as field2
FROM purchases p
WHERE ...
GROUP BY 2,3;
ERROR: Sub-SELECT uses un-GROUPed attribute p.purchase from
outer query
Query #2
SELECT SUM(p.dstqty) as agg,
(SELECT date_trunc('hour', sales.active)
FROM sales
WHERE p.purchase = sales.purchase) as field1,
(SELECT date_trunc('day', sales.active)
FROM sales WHERE p.purchase = sales.purchase) as field2
FROM purchases p
WHERE ...
GROUP BY 2,3;
agg | field1 | field2
---++
1. | 2002-12-27 18:00:00-05 | 2002-12-27 00:00:00-05
I also failed to mention in the original post that this is
PostgreSQL version 7.2.1.
Any help or instruction would be greatly appreciated.
Mike Mascari
[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] copying perms to another user
From: Rod Taylor [EMAIL PROTECTED] While I haven't thought about it very hard, it seems to me that a role might be equivalent or nearly so to a group. If so, we might be able to support roles with little more than some syntactic-sugar work ... A few other changes, like allowing ownership of an object to be a group (role) rather than strictly a user. Also, at least in Oracle, one can grant ROLEs to other ROLEs. I don't know if that is what the SQL standard says though: GRANT role1 TO role2; Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] help with PL/PgSQL bug
- Original Message - From: Tom Lane [EMAIL PROTECTED] Mike Mascari [EMAIL PROTECTED] writes: From: Tom Lane [EMAIL PROTECTED] That's a rowtype variable, though, not a record variable. I believe our code will work the same as Oracle for that case. 4 TYPE EmpRec IS RECORD ( 5 id NUMBER, 6 name VARCHAR(20) 7 ); 8 emp_rec EmpRec; behaves similarly by returning a NULL value for an unmatched row. Hm, that's interesting --- does Oracle not think that record means what our plpgsql think it means? I thought we'd stolen all those semantics straight from Oracle. In plpgsql, you can declare a variable like so: foo RECORD; and that means that it's an unspecified rowtype, whose fields will be determined on-the-fly to match the query that assigns to it. It's this case that I'm concerned about, because right now it behaves differently from the case where the variable's rowtype is predetermined. I searched through the Oracle 8 PL/SQL docs pretty thoroughly and couldn't find an example of a variable whose type was determined at run-time. Maybe the pgPL/SQL RECORD implementor can shed some more light on the issue, but as far as I can tell, Oracle's PL/SQL is strongly typed. Mike Mascari [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] help with PL/PgSQL bug
- Original Message - From: Tom Lane [EMAIL PROTECTED] Mike Mascari [EMAIL PROTECTED] writes: Does Oracle's PL/SQL have a concept of record variables? If so, what do they do in this situation? In Oracle 8, a row of NULLs: 1 CREATE OR REPLACE FUNCTION foo(t IN NUMBER) 2 RETURN NUMBER 3 IS 4 emp_rec employees%ROWTYPE; That's a rowtype variable, though, not a record variable. I believe our code will work the same as Oracle for that case. 1 CREATE OR REPLACE FUNCTION foo(t IN NUMBER) 2 RETURN NUMBER 3 IS 4 TYPE EmpRec IS RECORD ( 5 id NUMBER, 6 name VARCHAR(20) 7 ); 8 emp_rec EmpRec; 9 BEGIN 10 SELECT * 11 INTO emp_rec 12 FROM employees 13 WHERE id = t; 14 RETURN (emp_rec.id); 15* END; behaves similarly by returning a NULL value for an unmatched row. FWIW, Mike Mascari [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] help with PL/PgSQL bug
- Original Message - From: Tom Lane [EMAIL PROTECTED] Neil Conway [EMAIL PROTECTED] writes: On Fri, 2003-01-10 at 20:28, Tom Lane wrote: Clearly, RETURN NEXT with an undefined record variable shouldn't dump core, but what should it do? Raise an error, or perhaps be a no-op? I'd vote for making it a no-op. Raising an error is too severe for a fairly routine occurence, IMHO. If we make it a no-op, it's consistent with how I understand a SELECT INTO of 0 rows -- it doesn't produce an undefined value, but an empty result set (like the difference between and a NULL pointer). There's a consistency issue here, though. If the SELECT INTO target is non-record variable(s), the behavior is to set them to NULL. Then if you do RETURN NEXT on that, you'd emit a row full of NULLs. It seems inconsistent that SELECT INTO a record variable produces an undefined result rather than a row of NULLs, when there are no rows in the SELECT result. This would be an easy change to make, I think. We do have a tupledesc available for the SELECT, we're just not using it. Does Oracle's PL/SQL have a concept of record variables? If so, what do they do in this situation? In Oracle 8, a row of NULLs: 1 CREATE OR REPLACE FUNCTION foo(t IN NUMBER) 2 RETURN NUMBER 3 IS 4 emp_rec employees%ROWTYPE; 5 BEGIN 6 SELECT employees.* INTO emp_rec 7 FROM employees 8 WHERE employees.id = t; 9 RETURN(emp_rec.id); 10* END; SQL / Function created. SQL select * from employees; no rows selected SQL insert into employees values (1, 'Mike'); 1 row created. SQL select foo(1) from dual; FOO(1) -- 1 SQL select foo(2) from dual; FOO(2) -- SQL select nvl(foo(2), 0) from dual; NVL(FOO(2),0) - 0 Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] user defined settings (aka user defined guc variables)
- Original Message - From: Gavin Sherry [EMAIL PROTECTED] To: Joe Conway [EMAIL PROTECTED] On Wed, 18 Dec 2002, Joe Conway wrote: I've been playing around with making it possible to create user defined guc variables. This has been discussed, at least in passing, before. And it is even anticipated in guc.c as a possible future feature: /* * Build the sorted array. This is split out so that it could be * re-executed after startup (eg, we could allow loadable modules to * add vars, and then we'd need to re-sort). */ It is a feature that would be nice to have, so that, for example, a user defined variable named my_classpath could be created to point to the java CLASSPATH needed by a custom C function. Hmm. Is GUC really the best place for something like that? (not that there is any other place :-)). Gavin Maybe GUC should be stored in a Berkeley DB? ;-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Big 7.4 items
Bruce Momjian wrote: I wanted to outline some of the big items we are looking at for 7.4: Win32 Port: Katie Ward and Jan are working on contributing their Win32 port for 7.4. They plan to have a patch available by the end of December. Point-In-Time Recovery (PITR) J. R. Nield did a PITR patch late in 7.3 development, and Patrick MacDonald from Red Hat is working on merging it into CVS and adding any missing pieces. Patrick, do you have an ETA on that? Replication I have talked to Darren Johnson and I believe 7.4 is the time to merge the Postgres-R source tree into our main CVS. Most of the replication code will be in its own directory, with only minor changes to our existing tree. They have single-master replication working now, so we may have that feature in some capacity for 7.4. I know others are working on replication solutions. This is probably the time to decide for certain if this is the direction we want to go for replication. Most who have have studied Postgres-R feel it is the most promising multi-master replication solution for reliably networked hosts. Comments? What about distributed TX support: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=20021106111554.69ae1dcd.pgsql%40snaga.orgrnum=2prev=/groups%3Fq%3DNAGAYASU%2BSatoshi%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Big 7.4 items
Okay. But please keep in mind that a 2-phase commit implementation is used for more than just replication. Any distributed TX will require a 2PC protocol. As an example, for the DBLINK implementation to ultimately be transaction safe (at least amongst multiple PostgreSQL installations), the players in the distributed transaction must all be participants in a 2PC exchange. And a participant whose communications link is dropped needs to be able to recover by asking the coordinator whether or not to complete or abort the distributed TX. I am 100% ignorant of the distributed TX standard Tom referenced earlier, but I'd guess there might be an assumption of 2PC support in the implementation. In other words, I think we still need 2PC, regardless of the method of replication. And if Satoshi Nagayasu has an implementation ready, why not investigate its possibilities? Mike Mascari [EMAIL PROTECTED] - Original Message - From: Bruce Momjian [EMAIL PROTECTED] Mike Mascari wrote: What about distributed TX support: OK, yes, that is Satoshi's 2-phase commit implementation. I will address 2-phase commit vs Postgres-R in my next email about spread. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Big 7.4 items
- Original Message - From: Bruce Momjian [EMAIL PROTECTED] Mike Mascari wrote: Okay. But please keep in mind that a 2-phase commit implementation is used for more than just replication. This is a good point. I don't want to push Postgres-R as our solution. Rather, I have looked at both and like Postgres-R, but others need to look at both and decide so we are all in agreement when we move forward. After having read your post regarding Spread, I see that it is an alternative to 2PC as a distributed TX protocol. If I understand you correctly, a DBLINK implementation built atop Spread would also be possible. Correct? The question then is, do other RDBMS expose a 2PC implementation which could not then be leveraged at a later time? For example imagine: 1. 7.4 includes a native 2PC protocol with: CREATE DATABASE LINK accounting CONNECT TO accounting.acme.com:5432 IDENTIFIED BY mascarm/mascarm; SELECT * FROM employees@accounting; INSERT INTO employees@accounting VALUES (1, 'Mike', 'Mascari'); That would be great, allowing PostgreSQL servers running in different departments to participate in a distributed tx. 2. 7.5 includes a DBLINK which supports PostgreSQL participating in a heterogenous distributed transaction (with say, an Oracle database): CREATE DATABASE LINK finance CONNECT TO oracle names entry IDENTIFIED BY mascarm/mascarm USING INTERFACE 'pg2oracle.so'; INSERT INTO employees@finance VALUES (1, 'Mike', 'Mascari'); I guess I'm basically asking: 1) Is it necessary to *choose* between support for 2PC and Spread (Postgres-R) or can't we have both? Spread for Replication, 2PC for non-replicating distributed TX? 2) Do major SQL DBMS vendors which support distributed options expose a callable interface into a 2PC protocol that would allow PostgreSQL to participate? I could check on this... 3) Are there any standards (besides ODBC, which, the last time I looked just had COMMIT/ABORT APIs), that have been defined and adopted by the industry for distributed tx? Again, I'd guess most people want: 1) High performance Master/Master replication *and* (r.e. Postgres-R) 2) Ability to participate in distrubuted tx's (r.e. 2PC?) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Big 7.4 items
I wrote: I guess I'm basically asking: 1) Is it necessary to *choose* between support for 2PC and Spread (Postgres-R) or can't we have both? Spread for Replication, 2PC for non-replicating distributed TX? 2) Do major SQL DBMS vendors which support distributed options expose a callable interface into a 2PC protocol that would allow PostgreSQL to participate? I could check on this... 3) Are there any standards (besides ODBC, which, the last time I looked just had COMMIT/ABORT APIs), that have been defined and adopted by the industry for distributed tx? Answer: The Open Group's Open/XA C193 specificiation for API for distributed transactions: http://www.opengroup.org/public/pubs/catalog/c193.htm I couldn't find any draft copies on the web, but a good description at the Sybase site: http://manuals.sybase.com/onlinebooks/group-xs/xsge/xatuxedo/@ebt-link;pt=61?target=%25N%13_446_START_RESTART_N%25 The standard is 2PC based. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Gavin Sherry wrote: I want to see: i) proper resource management a-la Oracle. This would allow a DBA to limited the amount of time any given user spends in the parser, planner or executor. It would be limited with a more sophisticated user system, including things like CREATE USER PROFILE ... Amen: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=3DB99C0A.70900%40mascari.comrnum=1prev=/groups%3Fq%3DCREATE%2BPROFILE%2BMike%2BMascari%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8 To avoid unnecessary cycles being spent on loading the profile after session authorization, we could have a GUC as was suggested to turn the feature on or off. This mirrors Oracle, where you have to set RESOURCE_LIMIT in your init[SID].ora file before PROFILEs are enforced. Some people like sticking everything in postgresql.conf though, including resource limits. I'm not sure how remote administration is supposed to work under such a scenario though... Mike Mascari [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] Planning for improved versions of IN/NOT IN
Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: I curious if any of the rewriting of EXISTS and NOT EXISTS would address the problem described by Date: That should read I'm curious... http://www.firstsql.com/iexist.htm We are not here to redefine the SQL spec ... and especially not to eliminate its concept of NULL, which is what Date would really like ;-) From what I've read of Date's so far, I think he'd like to junk SQL altogether. The above-quoted screed is based on a claimed logical equivalence between NOT EXISTS() and NOT IN() that is just plain wrong when you consider the possibility of NULLs. Rather than FirstSQL correctly processes this query, you should read FirstSQL deliberately violates the SQL spec. (There may be grounds to argue that the spec behavior could be improved, but that's an argument to be making to the standards committee, not here.) Okay. I knew there was talk in the past that IN be rewritten as EXISTS, which is not what you propose doing, but would have exposed the odd behavior NOT EXISTS exhibits according to the SQL spec. I was also curious to know which path PostgreSQL development prefers to take when the SQL spec and the Relational Model part ways, as they often do. Maybe someday RedHat will have a voting member on the ANSI X3H2/NCITS committee. ;-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Planning for improved versions of IN/NOT IN
Joe Conway wrote: Tom Lane wrote: I've been thinking about how to improve the performance of queries using WHERE x IN (subselect) and WHERE x NOT IN (subselect). How about starting with a rule-based method to make the choice? 1. If uncorrelated: use hash-based approach - ISTM this might address a large percentage of the problem cases -- it could even handle the IN (list-of-scalars) case. Could it fall back to a tuplesort/binary-search for the too many to hash in memory case? 2. If correlated: use an inner indexscan 3. If you come up with a pattern where none of the approaches produce a correct answer, use the existing implementation You could always get fancier later if needed, but something along these lines would be a great start. I curious if any of the rewriting of EXISTS and NOT EXISTS would address the problem described by Date: http://www.firstsql.com/iexist.htm Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] performance regression, 7.2.3 - 7.3b5 w/ VIEW
Ross J. Reedstrom wrote: Hey Hackers - I was testing beta5 and found a performance regression involving application of constraints into a VIEW - I've got a view that is fairly expensive, involving a subselet and an aggregate. When the query is rewritten in 7.2.3, the toplevel constraint is used to filter before the subselect - in 7.3b5, it comes after. For this query, the difference is 160 ms vs. 2 sec. Any reason for this change? I could be way off base, but here's a shot in the dark: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=3D0885E1.8F369ACA%40mascari.comrnum=3prev=/groups%3Fq%3DMike%2BMascari%2Bsecurity%2BTom%2BLane%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den At the time I thought PostgreSQL was doing something naughty by allowing user functions to be invoked on data that would ultimately not be returned. Now I know how Oracle uses VIEWS for row security: Oracle functions invoked in DML statements can't record any changes to the database. So if the above is the cause, I wouldn't have any problems with the patch being reversed. Maybe separate privileges for read-only vs. read-write functions are in order at some point in the future though... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] protocol change in 7.4
Neil Conway wrote: There has been some previous discussion of changing the FE/BE protocol in 7.4, in order to fix several problems. I think this is worth doing: if we can resolve all these issues in a single release, it will lessen the upgrade difficulties for users. snip If I've missed anything or if there is something you think we should add, please let me know. Is there any thought about changing the protocol to support two-phase commit? Not that 2PC and distributed transactions would be implemented in 7.4, but to prevent another protocol change in the future? Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] protocol change in 7.4
Neil Conway wrote: Mike Mascari [EMAIL PROTECTED] writes: Is there any thought about changing the protocol to support two-phase commit? Not that 2PC and distributed transactions would be implemented in 7.4, but to prevent another protocol change in the future? My understanding is that 2PC is one way to implement multi-master replication. If that's what you're referring to, then I'm not sure I see the point: the multi-master replication project (pgreplication) doesn't use 2PC, due to apparent scalability problems (not to mention that it also uses a separate channel for communications between backends on different nodes). Actually, I was thinking along the lines of a true CREATE DATABASE LINK implementation, where multiple databases could participate in a distributed transaction. That would require the backend in which the main query is executing to act as the coordinator and each of the other participating databases to act as cohorts. And would require a protocol change to support the PREPARE, COMMIT-VOTE/ABORT-VOTE reply, and an ACK message following the completion of the distributed COMMIT or ABORT. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] idle connection timeout ...
Karel Zak wrote: On Fri, Oct 25, 2002 at 03:31:22PM -0400, Mike Mascari wrote: Bruce Momjian wrote: Added to TODO: * Allow limits on per-db/user connections Could I suggest that such a feature falls under the category of resource limits, and that the TODO should read something like: Implement the equivalent of Oracle PROFILEs. Yes! Please it's better than all discussions about some ugly variables. The PROFILE is better extendable and it's user specific and in the system with ROLEs it really cool and simple set user's system options. I talked about it more times, but is still ignore :-) I don't want to maintain my databases by SET command. It seems we are in the minority. :-( Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] idle connection timeout ...
Bruce Momjian wrote: Andrew Sullivan wrote: On Fri, Oct 25, 2002 at 11:02:48AM -0400, Tom Lane wrote: So? If it hits the installation-wide limit, you'll have the same problem; and at that point the (presumably runaway) app would have sucked up all the connections, denying service to other apps using other databases. I think Marc's point here is to limit his exposure to misbehavior of any one client app, in a database server that is serving multiple clients using multiple databases. That would indeed be a useful item. The only way to avoid such exposure right now is to run another back end. Added to TODO: * Allow limits on per-db/user connections Could I suggest that such a feature falls under the category of resource limits, and that the TODO should read something like: Implement the equivalent of Oracle PROFILEs. I think this would be a good project for 7.4. I'm not yet volunteering, but if I can wrap up my current project, I might be able to do it, depending upon the 7.4 target date. It would be: 1. A new system table: pg_profile 2. The attributes of the profiles would be: profname session_per_user cpu_per_session cpu_per_call connect_time idle_time logical_reads_per_session logical_reads_per_call 3. A new field would be added to pg_user/pg_shadow: profileid 4. A 'default' profile would be created when a new database is created with no resource limits. CREATE/ALTER user would be modified to allow for the specification of the profile. If no profile is provided, 'default' is assumed. 5. A new CREATE PROFILE/ALTER PROFILE/DROP PROFILE command set would be implemented to add/update/remove the tuples in pg_profiles. And according modification of pg_dump for dump/reload and psql for appropriate \ command. Example: CREATE PROFILE clerk IDLE_TIME 30; ALTER USER john PROFILE clerk; ALTER USER bob PROFILE clerk; or, for an ISP maybe: ALYTER PROFILE default IDLE_TIME 30; It seems like a nice project, particularly since it wouldn't affect anyone that doesn't want to use it. And whenever a new resource limitation issue arrises, such as PL/SQL recursion depth, a new attribute would be added to pg_profile to handle the limitation... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] autocommit vs TRUNCATE et al
Gavin Sherry wrote: On Fri, 18 Oct 2002, Tom Lane wrote: Anyone see a way out of this catch-22? If not, which is the least bad alternative? Ultimately, fix TRUNCATE to be transaction safe. This is non-trivial, I know :-). Regardless, the first option seems the less of the two evils. Even though TRUNCATE was modeled after Oracle's TRUNCATE and Oracle's TRUNCATE commits the running tx, truncates the relation, and starts a new tx, regardless of whether or not TRUNCATE is the first statement of the tx? Mike Mascari [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] Postgres-based system to run .org registry?
Karl DeBisschop wrote: On Mon, 2002-10-14 at 16:14, scott.marlowe wrote: It's on Slashdot, but there's only one post there that mentions the use of Postgresql. On 14 Oct 2002, Robert Treat wrote: Yep, that's them. This is a big win from a PostgreSQL advocacy position, especially since oracle pr made an official statement against the use of PostgreSQL. Has this info hit any of the linux oriented news sites (linux-today, slashdot, etc...) If not someone from the PostgreSQL marketing dept. (wink wink) should come up with a press release. Anybody have a link where I can find the /. or the Oracle statement? Here's the Oracle statement: http://forum.icann.org/org-eval/gartner-report/msg0.html Hope that helps, Mike Mascari [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] Transactions through dblink_exec()
Masaru Sugawara wrote:
Hi, all
While trying dblink_exec(), one of dblink()'s functions, I noticed there was an
odd situation: case 1 and case 2 worked well, but case 3 didn't(see below).
I hadn't been aware of it so that I only executed BEGIN and END in
dblink_exec() at first . This time, however, I noticed it by executing ROLLBACK.
I'm hoping that dblink_exec() returns something like warning if those who intend
to do transactions make a declaration of blink_exec('dbname=some', 'begin') by mistake.
for example
WARNING :You should declare dblink_exec('dbname=some', 'BEGIN; some queries;
COMMIT/ROLLBACK/END;') or use dblink_exec('BEGIN/COMMIT/ROLLBACK/END')
around dblink_exec('some queries')s. If not, your transactions won't work.
How can dblink() possibly be used safely for non-readonly
transactions without a full implementation of a two-phase commit
protocol? What happens when the remote server issues the COMMIT
and then the local server crashes?
Mike Mascari
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] MySQL vs PostgreSQL.
scott.marlowe wrote: On Fri, 11 Oct 2002, Jeff Davis wrote: I agree with your message except for that statement. MySQL alter table provides the ability to change column types and cast the records automatically. I remember that feature as really the only thing from MySQL that I've ever missed. Of course, it's not that wonderful in theory. During development you can easily drop/recreate the tables and reload the test data; during production you don't change the data types of your attributes. But in practice, during development it's handy sometimes. I still remember a post from somebody on the phpbuilder site that had changed a field from varchar to date and all the dates he had got changed to -00-00. He most unimpressed, especially since he (being typical of a lot of MySQL users) didn't have a backup. Couldn't he just do ROLLBACK? ;-) (for the humor impaired, that's a joke...) Mike Mascari [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] (Fwd) Re: Any Oracle 9 users? A test please...
Tom Lane wrote: Yury Bokhoncovich [EMAIL PROTECTED] writes: As reported by my friend: Oracle 8.1.7 (ver.9 behaves the same way): [ to_char(sysdate) advances in a transaction ] Now I'm really confused; this directly contradicts the report of Oracle 8's behavior that we had earlier from Roland Roberts. Can someone explain why the different results? Roland used an anonymous PL/SQL procedure: SQL begin 2 insert into rbr_foo select sysdate from dual; [...wait about 10 seconds...] 3 insert into rbr_foo select sysdate from dual; 4 end; 5 / PL/SQL procedure successfully completed. SQL select * from rbr_foo; Oracle isn't processing those statements interactively. SQL*Plus is waiting on the / to send the PL/SQL block to the database. I suspect its not going to take Oracle more than a second to insert a row... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] (Fwd) Re: Any Oracle 9 users? A test please...
Bruce Momjian wrote: Mike Mascari wrote: Oracle isn't processing those statements interactively. SQL*Plus is waiting on the / to send the PL/SQL block to the database. I suspect its not going to take Oracle more than a second to insert a row... Oh, I understand now. He delayed when entering the function body, but that has no effect when he sends it. Can someone add an explicit sleep in the function body and try that? SQL create table foo (a date); Table created. SQL begin 2 insert into foo select sysdate from dual; 3 dbms_lock.sleep(5); 4 insert into foo select sysdate from dual; 5 end; 6 / PL/SQL procedure successfully completed. SQL select to_char(a, 'HH24:MI:SS') from foo; TO_CHAR( 11:31:02 11:31:07 Mike Mascari [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] (Fwd) Re: Any Oracle 9 users? A test please...
Bruce Momjian wrote: OK, two requests. First, would you create a _named_ PL/SQL function with those contents and try it again. Also, would you test CURRENT_TIMESTAMP too? SQL CREATE TABLE foo(a date); Table created. As a PROCEDURE: SQL CREATE PROCEDURE test 2 AS 3 BEGIN 4 INSERT INTO foo SELECT SYSDATE FROM dual; 5 dbms_lock.sleep(5); 6 INSERT INTO foo SELECT SYSDATE FROM dual; 7 END; 8 / Procedure created. SQL execute test; PL/SQL procedure successfully completed. SQL select to_char(a, 'HH24:MI:SS') from foo; TO_CHAR( 12:01:07 12:01:12 As a FUNCTION: SQL CREATE FUNCTION mydiff 2 RETURN NUMBER 3 IS 4 time1 DATE; 5 time2 DATE; 6 c NUMBER; 7 BEGIN 8 SELECT SYSDATE 9 INTO time1 10 FROM DUAL; 11 SELECT COUNT(*) 12 INTO c 13 FROM bar, bar, bar, bar, bar, bar, bar, bar; 14 SELECT SYSDATE 15 INTO time2 16 FROM DUAL; 17 RETURN (time2 - time1); 18 END; 19 / Function created. SQL select mydiff FROM dual; MYDIFF -- .34722 I can't test the use of CURRENT_TIMESTAMP because I have Oracle 8, not 9. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] (Fwd) Re: Any Oracle 9 users? A test please...
Michael Paesold wrote: What about NOW()? It should be available in Oracle 8? Is it the same as SYSDATE? Unless I'm missing something, NOW() neither works in Oracle 8 nor appears in the Oracle 9i online documentation: http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/functions2.htm#80856 Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] (Fwd) Re: Any Oracle 9 users? A test please...
Bruce Momjian wrote: It is not clear to me; is this its own transaction or a function call? That looks like an anonymous PL/SQL procedure to me. Another question might be, given: more than one reference to one or more datetime value functions, then all such references are effectively evaluated simultaneously under what conditions does Oracle report *the same* value for CURRENT_TIMESTAMP? So far, in this discussion, we have the following scenarios: 1. RDBMS start: No one 2. Session start: No one 3. Transaction start: PostgreSQL 4. Statement start: ??? 5. CURRENT_TIMESTAMP evaluation: Oracle 9, ??? Given what Tom has posted regarding the standard, I think Oracle is wrong. I'm wondering how the others handle multiple references in CURRENT_TIMESTAMP in a single stored procedure/function invocation. It seems to me that the lower bound is #4, not #5, and the upper bound is implementation dependent. Therefore PostgreSQL is in compliance, but its compliance is not very popular. Mike Mascari [EMAIL PROTECTED] Dan Langille wrote: DECLARE time1 TIMESTAMP; time2 TIMESTAMP; sleeptime NUMBER; BEGIN sleeptime := 5; SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL; DBMS_LOCK.SLEEP(sleeptime); SELECT CURRENT_TIMESTAMP INTO time2 FROM DUAL; DBMS_OUTPUT.PUT_LINE(TO_CHAR(time1)); DBMS_OUTPUT.PUT_LINE(TO_CHAR(time2)); END; / 30-SEP-02 11.54.09.583576 AM 30-SEP-02 11.54.14.708333 AM PL/SQL procedure successfully completed. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] (Fwd) Re: Any Oracle 9 users? A test please...
Bruce Momjian wrote: Hannu Krosing wrote: It can be, as during the SQL statement can mean either the single statement inside the PL/SQL function (SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL;) or the whole invocation of the Pl/SQL funtion (the / command in Mikes sample, i believe) Which is what Oracle may have done. SQL99 talks about triggers seeing the same date/time, but then again if your trigger is a function, it has to see the same values for all of its calls. This doesn't match Oracle, unless they have some switch that returns consistent values when the function is called as a trigger (yuck). I think there is a #6 level in that chart. For example: INSERT INTO foo(field1, field2, field3) SELECT CURRENT_TIMESTAMP, (some time-intensive subquery), CURRENT_TIMESTAMP FROM bar; I'd bet Oracle inserts the same value for CURRENT_TIMESTAMP for both fields for every row. And that is what they view as a SQL Statement. I've only got 8, so I can't test. Also, as you point out, Oracle may distinguish between PL/SQL created anonymously or with CREATE PROCEDURE vs. PL/SQL code created with CREATE FUNCTION. It may be that UDFs return a single CURRENT_TIMESTAMP for the life of the invocation, while stored procedures don't. It is PostgreSQL, after all, that has merged the two concepts into one. Maybe someone could test version 9 with a FUNCTION that executes the same PL/SQL code and returns the difference between the two times. Mike Mascari [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])
[HACKERS] Temp tables and LRU-K caching
Hello. I'm just curious as to the 7.3 status of a couple of things: 1. Back in Feb. I wrote (in regards to Oracle behavior): Unlike normal queries where blocks are added to the MRU end of an LRU list, full table scans add the blocks to the LRU end of the LRU list. I was wondering, in the light of the discussion of using LRU-K, if PostgreSQL does, or if anyone has tried, this technique? Bruce wrote: Yes, someone from India has a project to test LRU-K and MRU for large table scans and report back the results. He will implement whichever is best. Did this make it into 7.3? 2. Gavin Sherry had worked up a patch so that temporary relations could be dropped automatically upon transaction commit. Did any of those patches it make it? I notice that whenever I create a temporary table in a transaction, my HD light blinks. Is this a forced fsync() causes by the fact that the SQL standard defines temporary relations as surviving across transactions? If so, I'd bet those of us who use transaction-local temporary tables could get few drops more of performance from an ON COMMIT drop patch w/o fsync. Any thoughts? Mike Mascari [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] Temp tables and LRU-K caching
Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: Bruce wrote: Yes, someone from India has a project to test LRU-K and MRU for large table scans and report back the results. He will implement whichever is best. Did this make it into 7.3? No, we never heard back from that guy. It is still a live topic though. One of the Red Hat people was looking at it over the summer, and I think Neil Conway is experimenting with LRU-2 code right now. Okay. 2. Gavin Sherry had worked up a patch so that temporary relations could be dropped automatically upon transaction commit. Did any of those patches it make it? No they didn't; I forget whether there was any objection to his last try or it was just too late to get reviewed before feature freeze. Nuts. Oh well. Hopefully for 7.4... I notice that whenever I create a temporary table in a transaction, my HD light blinks. Is this a forced fsync() causes by the fact that the SQL standard defines temporary relations as surviving across transactions? A completely-in-memory temp table is not really practical in Postgres, for two reasons: one being that its schema information is stored in the definitely-not-temp system catalogs, and the other being that we request allocation of disk space for each page of the table, even if it's temp. I knew what I was asking made no sense two seconds after clicking 'Send'. Unfortunately, there's no undo on my mail client ;-). Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Win32 rename()/unlink() questions
Stephan Szabo wrote: On Fri, 20 Sep 2002, Mike Mascari wrote: Yes! Indeed that does work. Thinking back, I think that may still fail on Win95 (using MoveFile). Once in the past I had to work on (un)installers for Win* and I vaguely remember Win95 being more strict than Win98 but that may just have been with moving the executable you're currently running. Well, here's the test: foo.txt contains This is FOO! bar.txt contains This is BAR! Process 1 opens foo.txt Process 2 opens foo.txt Process 1 sleeps 7.5 seconds Process 2 sleeps 15 seconds Process 1 uses MoveFile() to rename foo.txt to foo2.txt Process 1 uses MoveFile() to rename bar.txt to foo.txt Process 1 uses DeleteFile() to remove foo2.txt Process 2 awakens and displays This is FOO! On the filesystem, we then have: foo.txt containing This is BAR! The good news is that this works fine under NT 4 using just MoveFile(). The bad news is that it requires the files be opened using CreateFile() with the FILE_SHARE_DELETE flag set. The C library which ships with Visual C++ 6 ultimately calls CreateFile() via fopen() but with no opportunity through the standard C library routines to use the FILE_SHARE_DELETE flag. And the FILE_SHARE_DELETE flag cannot be used under Windows 95/98 (Bad Parameter). Which means, on those platforms, there still doesn't appear to be a solution. Under NT/XP/2K, AllocateFile() will have to modified to call CreateFile() instead of fopen(). I'm not sure about ME, but I suspect it behaves similarly to 95/98. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
Bruce Momjian wrote: I don't think we are not going to be supporting Win9X so there isn't an issue there. We will be supporting Win2000/NT/XP. I don't understand FILE_SHARE_DELETE. I read the description at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/createfile.asp but I don't understand it: FILE_SHARE_DELETE - Windows NT/2000/XP: Subsequent open operations on the object will succeed only if delete access is requested. I think that's a rather poor description. I think it just means that if the file is opened once via CreateFile() with FILE_SHARE_DELETE, then any subsequent CreateFile() calls will fail unless they too have FILE_SHARE_DELETE. In other words, if one of us can delete this file while its open, any of us can. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32 rename()/unlink() questions
Stephan Szabo wrote: On Fri, 20 Sep 2002, Mike Mascari wrote: I think that's a rather poor description. I think it just means that if the file is opened once via CreateFile() with FILE_SHARE_DELETE, then any subsequent CreateFile() calls will fail unless they too have FILE_SHARE_DELETE. In other words, if one of us can delete this file while its open, any of us can. The question is, what happens if two people have the file open and one goes and tries to delete it? Can the other still read from it? Yes. I just tested it and it worked. I'll test Bruce's scenario as well: foo contains: FOO bar contains: BAR 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 calls MoveFile(foo, foo2); 4. Process 3 opens foo - Successful? 5. Process 1 calls MoveFile(bar, foo); 6. Process 4 opens foo - Successful? 7. Process 1 calls DeleteFile(foo2); 8. Process 1, 2, 3, 4 all read from their respective handles. I think the thing to worry about is a race condition between the two MoveFile() attempts. A very ugly hack would be to loop in a CreateFile() in an attempt to open foo, giving up if the error is not a NOT EXISTS error code. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
I wrote: Stephan Szabo wrote: The question is, what happens if two people have the file open and one goes and tries to delete it? Can the other still read from it? Yes. I just tested it and it worked. I'll test Bruce's scenario as well: foo contains: FOO bar contains: BAR 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 calls MoveFile(foo, foo2); 4. Process 3 opens foo - Successful? 5. Process 1 calls MoveFile(bar, foo); 6. Process 4 opens foo - Successful? 7. Process 1 calls DeleteFile(foo2); 8. Process 1, 2, 3, 4 all read from their respective handles. Process 1: FOO Process 2: FOO Process 3: Error - File does not exist Process 4: BAR Its interesting in that it allows for Unix-style rename() and unlink() behavior, but with a race condition. Without Stephan's two MoveFile() trick and the FILE_SHARE_DELETE flag, however, the result would be Access Denied. Are the places in the backend that use rename() and unlink() renaming and unlinking files that are only opened for a brief moment by other backends? Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
