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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] (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])
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] [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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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
[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] 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]
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] Vote totals for SET in aborted transaction
Bruce Momjian wrote: Marc G. Fournier wrote: Just curious here, but has anyone taken the time to see how others are doing this? For instance, if we go with 1, are going against how everyone else handles it? IMHO, its not a popularity contest ... Yes, good point. I don't know that they use SET, but if they do, we should find out how they handle it, though I doubt they have thought through their SET handling as well as we have. My guess is that they do 3, honor all SETs. Connected to: Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SQL SELECT TO_CHAR(SYSDATE) FROM DUAL; TO_CHAR(S - 25-APR-02 SQL COMMIT; Commit complete. SQL ALTER SESSION SET NLS_DATE_FORMAT = ' MM DD'; Session altered. SQL ROLLBACK; Rollback complete. SQL SELECT TO_CHAR(SYSDATE) FROM DUAL; TO_CHAR(SY -- 2002 04 25 Of course, with Oracle, the only operations which can be rolled back are INSERTs, UPDATEs, and DELETEs (DML statements). A long time ago, on a planet far, far away, I argued that PostgreSQL should follow Oracle's behavior in this regard. I stand corrected. The ability to rollback DROP TABLE is a very nice feature Oracle doesn't have, and to remain consistent, I agree with all of those that have voted for #1. 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] Operator Comments
Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: Looks like CommentOperator goes to quite a bit of work (5 lines) to accomplish fetching the procedure and states specifically it's not a bug. Yeah, someone once thought it was a good idea, but I was wondering about the wisdom of it just the other day. Currently this feature presents a hole in the security of comments on functions: anyone can make an operator referencing a function, and then they'll be allowed to set the function's comment :-(. I can see the value in having the function comment shown when there is no comment specifically for the operator ... but perhaps that ought to be implemented in the client requesters, rather than wired into the catalog representation. In which case RemoveOperator needs to drop comments by the procID as well. No, because the comment really belongs to the function and should go away only when the function does. But I'd vote for giving operators their own comments. Here's the history, FWIW: I implemented COMMENT ON for just TABLES and COLUMNS, like Oracle. Bruce requested it for all objects I extended for all objects - including databases (my bad) ;-) Peter E. was rewriting psql and wanted the COMMENT on operators to reflect a COMMENT on the underlying function I submitted a patch to do that - I just do what I'm told ;-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Feature request: Truncate table
Christopher Kings-Lynne wrote: Hrm - last time I checked it did... Two questions : When was the last time ? 7.1 It did what ? Drops triggers and stuff. OK, I did a check and it looks like it's fixed in 7.2 at least. Sorry for the false alarm... It has never dropped triggers and stuff, so there was nothing to fix. All TRUNCATE TABLE has ever done, since the patch was submitted, was to truncate the underlying relation file and the associated index files, and reinitialize the indexes. It has been changed to be disallowed in transactions involving tables not created in the same transaction, but that's about it. People have argued that if there are *RI* triggers on a table, that TRUNCATE should be disallowed, as in Oracle. But TRUNCATE from inception to date has never dropped triggers... 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] Non-standard feature request
I know you guys love subject lines like this, but I have a humble
request. Would it be possible to have either a GUC setting or a grammar
change to allow TEMPORARY tables to be dropped at transaction commit? I
know the standard defines the lifetimes of temporary tables to be that
of the session. However, I have CORBA middleware which generates a
transient session object per client. The object connects to the database
at instantiation time and services requests by CORBA's remote method
invocation. Often, the methods invoked on the object cause the object to
create temporary tables. Each method invocation is a single transaction.
But the lifetime of a user's session can be quite long. Worse, CORBA
doesn't permit the application to detect when the client disconnects -
the object (and therefore the database connection) remains unless told
explicitly to die. I currently have an evictor pattern remove objects
upon which no method invocation has taken place over a given time. But
in the meantime, dozens of temporary tables have built up. The idea kind
of falls along the same lines as the SET discussion previously. As a
test, it took me about 8 lines of code to implement the change. Of
course, it was a hack, but it worked nicely.
Would a patch to the grammar be accepted? Along the lines of:
CREATE TEMPORARY TABLE
...
ON COMMIT DROP;
pseudo-compatible with the SQL-standard of:
ON COMMIT { DELETE | PRESERVE } ROWS;
so one day PostgreSQL's grammar would look like:
...
ON COMMIT { DROP | { DELETE | PRESERVE } ROWS };
I suppose I could just change the code to query the catalogue for those
temporary tables created during the transaction and issue DROP TABLEs by
hand. But I thought it might be an idea of value to others.
Mike Mascari
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Non-standard feature request
Bruce Momjian wrote: Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: ... Would it be possible to have either a GUC setting or a grammar change to allow TEMPORARY tables to be dropped at transaction commit? This seems like a not unreasonable idea; but the lack of other responses suggests that the market for such a feature isn't there. Perhaps you should try to drum up some interest on pgsql-general and/or pgsql-sql. I was wondering if it made sense to remove temp tables on transaction finish if the temp table was created in the transaction? That wouldn't require any syntax change. Seems non-standard though, and I can imagine a few cases where you wouldn't want it. That is what I want to do, except by extending the grammar. I must admit to actually being surprised that a TEMP table created inside a transaction lived after the transaction completed. That's when I looked at the standard and saw that PostgreSQL's implementation was correct. I would think for most people session-long temp tables are more the exception than the rule. But I guess SQL92 doesn't think so. Regardless, a couple of other people have shown some interest in the idea. I'll post it to general as well as Tom suggests... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Non-standard feature request
Rocco Altier wrote: On Fri, 14 Jun 2002, Mike Mascari wrote: That is what I want to do, except by extending the grammar. I must admit to actually being surprised that a TEMP table created inside a transaction lived after the transaction completed. That's when I looked at the standard and saw that PostgreSQL's implementation was correct. I would think for most people session-long temp tables are more the exception than the rule. But I guess SQL92 doesn't think so. Regardless, a couple of other people have shown some interest in the idea. I'll post it to general as well as Tom suggests... Actually, we needed to use temp tables that live beyond the transaction, because there are no session variables in postgres. So I did an implementation that used temp tables instead. Having the temp table not live for the life of the session would be a big problem for me. Sure, which is why I'm proposing to extend the grammar. Only if you created the temporary table with CREATE TEMPORARY TABLE ... ON COMMIT DROP; would it drop the temporary table at transaction commit. It should be 100% compatible with existing code. 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: First Win32 Contribution (Was: Re: [HACKERS] Democracy and
Jan Wieck wrote: Marc G. Fournier wrote: ... IMHO, that is actually their problem ... without meaning to sound crass about it, but its not like we haven't discussed it extensively here, and openly ... hell, we've even tried to break down the whole project into smaller components to make the whole easier to merge in :) The problem with this kind of project is that you have a big stumbling block at the beginning, which has to be done before you can rollout and integrate the help of developers scattered around the globe. This was the case with the foreign key project, where the trigger queue and one set of triggers where working, and then Stephan did all the others and I forgot who else helped to do the utility commands and CREATE TABLE syntax and tried to decrypt the SQL definitions? In the Windows port case it is to get it as far that you at least can fire up a postmaster, get past the startup process, connect to the database and do a few queries before the thing blows up. Before this everybody has exactly the same problem, It doesn't startup, so the likelyhood of everyone stomping over each others work every single night is about 99.9%! It would be nice to also have it fire up under Windows CE as well ;-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Non-standard feature request
Gavin Sherry wrote: Slight bug in the previous patch. Logically (and according to SQL99's treatment of ON COMMIT), it can be specified only for CREATE TEMP TABLE. The patch throws an error if only CREATE TABLE has been specified. ... Attached is a patch implementing this. The patch is against 7.2.1 source. The grammar introduced is of the form: CREATE TEMP TABLE ... ON COMMIT DROP; Is this a desirable feature? Seems pretty useful to me. Great! I'm give this a try. 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] [PATCHES] prepareable statements
Rod Taylor wrote:
On Tue, 2002-07-23 at 11:34, Tom Lane wrote:
[EMAIL PROTECTED] (Neil Conway) writes:
Regarding the syntax for EXECUTE, it occurs to me that it could be made
to be more similar to the PREPARE syntax -- i.e.
PREPARE foo(text, int) AS ...;
EXECUTE foo('a', 1);
(rather than EXECUTE USING -- the effect being that prepared statements
now look more like function calls on a syntactical level, which I think
is okay.)
Hmm, maybe *too* much like a function call. Is there any risk of a
conflict with syntax that we might want to use to invoke stored
procedures? If not, this is fine with me.
Stored procedures would use PERFORM would they not?
I like the function syntax. It looks and acts like a temporary 'sql'
function.
FWIW, Oracle uses EXECUTE to execute stored procedures. It is not apart
of the SQL language, but a SQL*Plus command:
EXECUTE my_procedure();
The Oracle call interface defines a function to call stored procedures:
OCIStmtExecute();
Likewise, the privilege necessary to execute a stored procedure is
'EXECUTE' as in:
GRANT EXECUTE ON my_procedure TO mascarm;
Again, FWIW.
Mike Mascari
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why is MySQL more chosen over PostgreSQL?
Bruce Momjian wrote: Curt Sampson wrote: I'm still waiting to find out just what advantage table inheritance offers. I've asked a couple of times here, and nobody has even started to come up with anything. We inherited inheritance from Berkeley. I doubt we would have added it ourselves. It causes too much complexity in other parts of the system. ... As for why PostgreSQL is less popular than MySQL, I think it is all momentum from 1996 when MySQL worked and we sometimes crashed. Looking forward, I don't know many people who choose MySQL _if_ they consider both PostgreSQL and MySQL, so the discussions people have over MySQL vs. PostgreSQL are valuable because they get people to consider MySQL alternatives, and once they do, they usually choose PostgreSQL. As for momentum, we still have a smaller userbase than MySQL, but we are increasing our userbase at a fast rate, perhaps faster than MySQL at this point. Its all due to sort-order. If Oracle was open source MySQL would still be more popular. ;-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [SECURITY] DoS attack on backend possible (was: Re:
Christopher Kings-Lynne wrote: Hey yep, good point. Is this the only way that we know of non postgresql-superusers to be able to take out the server other than by extremely non-optimal, resource wasting queries? If we release a 7.2.2 because of this, can we be pretty sure we have a no known vulnerabilities release, or are there other small holes which should be fixed too? What about that select cash_out(2) crashes because of opaque entry in the TODO? That really needs to be fixed. I was talking to a CS lecturer about switching to postgres from oracle when 7.3 comes out and all he said was how easily is it hacked?. He says their systems are the most constantly bombarded in universities. What could I say? That any unprivileged user can just go 'select cash_out(2)' to DOS the backend? If he's using Oracle already, he ought to check out: http://www.cert.org/advisories/CA-2002-08.html I'd still think it would be a good policy to make a security release. However, without user resource limits in PostgreSQL, anyone can make a machine useless with a query like: SELECT * FROM pg_class a, pg_class b, pg_class c, pg_class d, pg_class e, ... ; Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Open 7.3 items
Joe Conway wrote: Hannu Krosing wrote: What about functions 1. split(text,text,int) returns text 2. split(text,text) returns text[] and why not 3. split(text,text,text) returns text which returns text from $1 delimited by $2 and $3 Given the time remaining before beta, I'll be happy just to get #1 done. I can see the utility of #2 (or perhaps even a table function which breaks the string into individual rows). I'm not sure I understand #3. I am concerned about the name though -- only in that there are usually objections raised to function names that are too likely to conflict with user created function names. But split is good from the standpoint that it is used in other languages, so people should find it familiar. Anyone have comments on the name? Actually, I've been wondering if it wouldn't be a good idea with schemas coming to think now about how to divide up namespaces for all sorts of things, including PostgreSQL's built in functions, the contrib code, etc. I think a naming scheme with which both PostgreSQL and the community would comply, a la Java's reverse DNS scheme for namespaces would be neat. So when a database is installed, the following schemas are automatically created: org.postgresql.system - System tables and core functions org.postgresql.text - Text related functions org.postgresql.math - Math related functions org.postgresql.fts - Full-Text schema or perhaps: org.postgresql.contrib.fts - Full-Text schema etc. I don't even know if . is allowed in the schema names, but you get the idea. Then, a users search_path (or whatever it's called, I haven't used the development version in a while), would be the equivalent of Java's import statement, or C++'s using statement. So split would be a function in the org.postgresql.text schema. How about them apples? If this is an insane idea, its 3:32 A.M. my time ;-) Mike Mascari [EMAIL PROTECTED] Joe ---(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] PGXLOG variable worthwhile?
scott.marlowe wrote: On Fri, 13 Sep 2002, Justin Clift wrote: Would it be correct to say that the 'ln' command in the MS Resource Kit creates this kind of shortcut too, as the Reparse Points feature doesn't seem to be possible under NT4? I wouldn't assume that. It's been years since I tested it, but back then, the command line and all program I used could see the link created by ln that came with the resource kit. They were distinctly different from the shortcut type of links, in that they seems transparent like short cuts in unix generally are. Do you have the resource kit or the gnu utils from it? The situation appears to be this: 1. Soft links are available on NTFS 5 (2K/XP) as Reparse Points via the DeviceIoControl() function for any application using the standard C library routines. 2. Soft links are available on any filesystem under 95/98/ME/NT4/2K/XP as OLE streams (.lnk files) for Shell-aware applications. 3. Hard links are available on NTFS 5 (2K/XP) via the CreateHardLink() API. See: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/createhardlink.asp 4. Hard links are available on NTFS (NT3.1/NT4) via the BackupWrite() API by writing a special stream to the NTFS. Example: http://www.mvps.org/win32/ntfs/lnw.cpp The cygwin implementation of link(): http://sources.redhat.com/cgi-bin/cvsweb.cgi/src/winsup/cygwin/syscalls.cc?rev=1.149.2.23content-type=text/x-cvsweb-markupcvsroot=src 1. Will use CreateHardLink() if on 2K/XP 2. Will try to use the BackupWrite() method 3. Failing #2 will just copy the file See how fun Microsoft makes things? Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGXLOG variable worthwhile?
I wrote: scott.marlowe wrote: I wouldn't assume that. It's been years since I tested it, but back then, the command line and all program I used could see the link created by ln that came with the resource kit. They were distinctly different from the shortcut type of links, in that they seems transparent like short cuts in unix generally are. Do you have the resource kit or the gnu utils from it? The situation appears to be this: 1. Soft links are available on NTFS 5 (2K/XP) as Reparse Points via the DeviceIoControl() function for any application using the standard C library routines. 2. Soft links are available on any filesystem under 95/98/ME/NT4/2K/XP as OLE streams (.lnk files) for Shell-aware applications. 3. Hard links are available on NTFS 5 (2K/XP) via the CreateHardLink() API. snip 4. Hard links are available on NTFS (NT3.1/NT4) via the BackupWrite() API by writing a special stream to the NTFS. I also believe (I could be wrong) that for directories, the only two methods of links are the Soft link methods above. So PGXLOG cannot use soft links on a non-XP/2K machine unless it is Shell-Aware. For example, in a cygwin bash command window: mkdir dir1 ln dir1 dir2 - Error using Cygwin implementation ln -s dir1 dir2 - Creates a Shell short-cut (NT4) echo Hello dir1/test.txt cat dir2/test.txt Hello - Cygwin's cat(bash?) is shell short-cut aware Now, in a Windows NT command prompt: notepad dir2\test.txt - Notepad can't find file notepad dir2.lnk - Displays link contents That means for a native port with a different PGXLOG directory running on NT4, the only choice *using links* is to make the native port shell short-cut aware. I could be wrong but I don't think so. 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] Win32 rename()/unlink() questions
Bruce Momjian wrote: I am working with several groups getting the Win32 port ready for 7.4 and I have a few questions: What is the standard workaround for the fact that rename() isn't atomic on Win32? Do we need to create our own locking around the reading/writing of files that are normally updated in place using rename()? Visual C++ comes with the source to Microsoft's C library: rename() calls MoveFile() which will error if: 1. The target file exists 2. The source file is in use MoveFileEx() (not available on 95/98) can overwrite the target file if it exists. The Apache APR portability library uses MoveFileEx() to rename files if under NT/XP/2K vs. a sequence of : 1. CreateFile() to test for target file existence 2. DeleteFile() to remove the target file 3. MoveFile() to rename the old file to new under Windows 95/98. Of course, some other process could create the target file between 2 and 3, so their rename() would just error out in that situation. I haven't tested it, but I recall reading somewhere that MoveFileEx() has the ability to rename an opened file. I'm 99% sure MoveFile() will fail if the source file is open. Second, when you unlink() a file on Win32, do applications continue accessing the old file contents if they had the file open before the unlink? unlink() just calls DeleteFile() which will error if: 1. The target file is in use CreateFile() has the option: FILE_FLAG_DELETE_ON_CLOSE which might be able to be used to simulate traditional unlink() behavior. Hope that helps, 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
Christopher Kings-Lynne wrote: On Wed, Sep 18, 2002 at 08:01:42PM -0400, Bruce Momjian wrote: Second, when you unlink() a file on Win32, do applications continue accessing the old file contents if they had the file open before the unlink? I'm pretty sure it errors with 'file in use'. Pretty ugly, huh? Yeah - the windows filesystem is pretty poor when it comes to multiuser access. That's why even as administrator I cannot delete borked files and people's profiles and stuff off our NT server - the files are always 'in use'. Even if you kick all users off, reboot the machine, do whatever. It's terrible. Chris Yep. That's why often it requires rebooting to uninstall software. How can the installer remove itself? Under Windows 95/98/ME, you have to manually add entries to WININIT.INI. With Windows NT/XP/2K, MoveFileEx() with a NULL target and the MOVEFILE_DELAY_UNTIL_REBOOT flag will add the appropriate entries into the system registry so that the next time the machine reboots it will remove the files specified. Its a real pain and a real hack of an OS. 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: Mike Mascari wrote: I will do some testing with concurrency and let you know. But don't get your hopes up. This is one of the many advantages that TABLESPACEs have when more than one relation is stored in a single DATAFILE. There was Oracle for MS-DOS, after all.. I was focusing on handling of pg_pwd and other config file that are written by various backend while other backends are reading them. The actual data files should be OK because we have an exclusive lock when we are adding/removing them. OK. So you want to test: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 renames foo to bar 4. Process 2 can safely read from its open file handle Is that what you want tested? I have a small Win32 app ready to test. Just let me know the scenarios... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
Mike Mascari wrote: Bruce Momjian wrote: Mike Mascari wrote: I will do some testing with concurrency and let you know. But don't get your hopes up. This is one of the many advantages that TABLESPACEs have when more than one relation is stored in a single DATAFILE. There was Oracle for MS-DOS, after all.. I was focusing on handling of pg_pwd and other config file that are written by various backend while other backends are reading them. The actual data files should be OK because we have an exclusive lock when we are adding/removing them. OK. So you want to test: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 renames foo to bar 4. Process 2 can safely read from its open file handle Actually, looking at the pg_pwd code, you want to determine a way for: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 creates bar 4. Process 1 renames bar to foo 5. Process 2 can continue to read data from the open file handle and get the original foo data. Is that correct? 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] Win32 rename()/unlink() questions
Bruce Momjian wrote: Mike Mascari wrote: Actually, looking at the pg_pwd code, you want to determine a way for: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 creates bar 4. Process 1 renames bar to foo 5. Process 2 can continue to read data from the open file handle and get the original foo data. Yep, that's it. So far, MoveFileEx(foo, bar, MOVEFILE_REPLACE_EXISTING) returns Access Denied when Process 1 attempts the rename. But I'm continuing to investigate the possibilities... 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: Bruce Momjian wrote: Mike Mascari wrote: Actually, looking at the pg_pwd code, you want to determine a way for: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 creates bar 4. Process 1 renames bar to foo 5. Process 2 can continue to read data from the open file handle and get the original foo data. Yep, that's it. So far, MoveFileEx(foo, bar, MOVEFILE_REPLACE_EXISTING) returns Access Denied when Process 1 attempts the rename. But I'm continuing to investigate the possibilities... Does a sequence like Process 1 opens foo Process 2 opens foo Process 1 creates bar Process 1 renames foo to something - where something is generated to not overlap an existing file Process 1 renames bar to foo Process 2 continues reading let you do the replace and keep reading (at the penalty that you've now got to have a way to know when to remove the various somethings) Yes! Indeed that does work. 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: Bruce Momjian wrote: unlink() just calls DeleteFile() which will error if: 1. The target file is in use CreateFile() has the option: FILE_FLAG_DELETE_ON_CLOSE which might be able to be used to simulate traditional unlink() behavior. No, that flag isn't going to help us. I wonder what MoveFileEx does if the target file exists _and_ is open by another user? I don't see any loop in that Win32 rename() routine, and I looked at the Unix version of apr_file_rename and its just a straight rename() call. This says that if the target is in use, it is overwritten: http://support.microsoft.com/default.aspx?scid=KB;EN-US;q140570; I read the article and did not come away with that conclusion. The article describes using the MOVEFILE_DELAY_UNTIL_REBOOT flag, which was created for the express purpose of allowing a SETUP.EXE to remove itself, or rather tell Windows to remove it on the next reboot. Also, if you want the Win32 port to run in 95/98/ME, you can't rely on MoveFileEx(), you have to use MoveFile(). I will do some testing with concurrency and let you know. But don't get your hopes up. This is one of the many advantages that TABLESPACEs have when more than one relation is stored in a single DATAFILE. There was Oracle for MS-DOS, after all.. 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] 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
[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] Truncation of char, varchar types
This is what I get in Oracle 8:
SQL CREATE TABLE test (value VARCHAR (10));
Table created.
SQL INSERT INTO test VALUES ('Mike Mascari');
INSERT INTO test VALUES ('Mike Mascari')
*
ERROR at line 1:
ORA-01401: inserted value too large for column
SQL quit
Of course, if the standard is ambiguous, retaining backwards
compatibility sure would be nice.
FWIW,
Mike Mascari
[EMAIL PROTECTED]
-Original Message-
From: Zeugswetter Andreas SB [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, April 10, 2001 6:47 AM
To: 'Peter Eisentraut'; PostgreSQL Development
Subject:AW: [HACKERS] Truncation of char, varchar types
Excessively long values are currently silently truncated when they
are
inserted into char or varchar fields. This makes the entire notion
of
specifying a length limit for these types kind of useless, IMO.
Needless
to say, it's also not in compliance with SQL.
To quote Tom "paragraph and verse please" :-)
How do people feel about changing this to raise an error in this
situation?
Can't do.
Does anybody rely on silent truncation?
Yes, iirc the only thing you are allowed to do is issue a warning,
but the truncation is allowed and must succeed.
(checked in Informix and Oracle)
The appropriate SQLSTATE is: "01004" String data, right truncation
note that class 01 is a "success with warning".
Andreas
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
RE: [HACKERS] Truncation of object names
The ability to place database objects into a logical partitioning of data. For example, in Oracle, each user creates tables, views, sequences, synonyms, and snapshots in their own schema. So if I were to create a table called 'Employees', I could query it as: SELECT * FROM employees; But another user would have to query it as: SELECT * FROM mascarm.employees; A common case for this is to logically divide schema by departments. You could do that now in PostgreSQL in the form of multiple databases, but you couldn't query across them. For example, you might have an "Accounting" schema, and an "Inventory" schema. Occassionally, the accountants need to join tables from accounting w/inventory. The inventory people (or the dba) would then grant appropriate privileges for the accountants to do that, but the accounts would have to fully qualify their queries: SELECT * FROM inventory.orders; So, if you want a logical division that also contain some shared tables, views, or sequences (and hopefully snapshots, some day), in Oracle, you can create public synonyms for the shared objects: CREATE PUBLIC SYNONYM employees FOR mascarm.employees; Now, anyone can query this table as: SELECT * FROM employees; Its a namespace thing, basically. Hope that helps, Mike Mascari [EMAIL PROTECTED] -Original Message- From: Christopher Kings-Lynne [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 16, 2001 10:17 PM To: [EMAIL PROTECTED] Subject:RE: [HACKERS] Truncation of object names Call me thick as two planks, but when you guys constantly refer to 'schema support' in PostgreSQL, what exactly are you referring to? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] System catalog representation of access privileges
First, let me say that just because Oracle does it this way doesn't make
it better but...
Oracle divides privileges into 2 categories:
Object privileges
System privileges
The Object privileges are the ones you describe. And I agree
fundamentally with your design. Although I would have (a) used a bitmask
for the privileges and (b) have an additional bitmask which determines
whether or not the Grantee could turn around and grant the same
permission to someone else:
pg_objprivs {
priobj oid,
prigrantor oid,
prigrantee oid,
priprivileges int4,
priadmin int4
};
Where priprivileges is a bitmask for:
0 ALTER - tables, sequences
1 DELETE - tables, views
2 EXECUTE - procedures, functions
3 INDEX - tables
4 INSERT - tables, views
5 REFERENCES - tables
6 SELECT - tables, views, sequences
7 UPDATE - tables, views
8 HIERARCHY - tables
9 UNDER - tables
And the priadmin is a bitmask to determine whether or not the Grantee
could grant the same privilege to another user. Since these are Object
privileges, 32 bits should be enough (and also 640K RAM ;-)).
The System privileges are privileges granted to a user or role (a.k.a
group) which are not associated with any particular object. This is one
area where I think PostgreSQL needs a lot of work and thought,
particularly with schemas coming down the road. Some example Oracle
System privileges are:
Typical User Privileges:
---
CREATE SESSION - Allows the user to connect
CREATE SEQUENCE - Allows the user to create sequences in his schema
CREATE SYNONYM - Allows the user to create private synonyms
CREATE TABLE - Allows the user to create a table in his schema
CREATE TRIGGER - Allows the user to create triggers on tables in his
schema
CREATE VIEW - Allows the user to create views in his schema
Typical Power-User Privileges:
-
ALTER ANY INDEX - Allows user to alter an index in *any* schema
ALTER ANY PROCEDURE - Allows user to alter a procedure in *any* schema
ALTER ANY TABLE - Allows user to alter a table in *any* schema
...
CREATE ANY TABLE - Allows user to create a table in *any* schema
COMMENT ANY TABLE - Allows user to document any table in *any* schema
...
Typical DBA-Only Privileges:
---
ALTER USER - Allows user to change password, quotas, etc. for *any* user
CREATE USER - Allows user to create a new user
DROP USER - Allows user to drop a new user
GRANT ANY PRIVILEGE - Allows user to grant any privilege to any user
ANALYZE ANY - Allows user to analyze any table in *any* schema
There are, in fact, many, many more System Privileges that Oracle
defines. You may want someone to connect to a database and query one
table and that's it. Or you may want someone to have no other abilities
except to document the database design via the great COMMENT ON command
;-), etc.
So for System Privileges, I would have something like:
pg_sysprivs {
prigrantee oid,
priprivilege oid,
prigroup bool,
priadmin bool
};
So each System privilege granted to a user (or group) would be its own
record. The priprivilege would be the OID of one of the many System
privileges defined in the same way types are defined, if prigroup is
false. If prigroup is true, however, then priprivilege is not a System
privilege, but a group id. And then PostgreSQL will have to examine the
privileges recursively for that group. Of course, you might not want to
allow for the GRANTing of group privileges to other groups initially,
which simplifies the implementation tremendously. But its a neat (if not
complicated) Oracle-ism.
Unfortunately, this means that the permission might require 2 lookups.
But these lookups are only if the previous lookup failed:
SELECT * FROM employees.foo;
1. Am I a member of the employees schema? Yes - Done
2. Have I been GRANTed the Object Privilege of:
SELECT on employees.foo? Yes - Done
3. Have I been GRANTed the System Privilege of:
SELECT ANY TABLE? Yes - Done
So the number of lookups does potentially increase, but only for those
users that have been granted access through greater and greater layers
of authority.
I just think that each new feature added to PostgreSQL opens up a very
large can of worms. Schemas are such a feature and the security system
should be prepared for it.
FWIW,
Mike Mascari
[EMAIL PROTECTED]
Peter Eisentraut wrote:
It would make sense to split privileges on tables from privileges on
schemas/databases from privileges on, say, functions, etc. E.g.,
pg_privtable-- like proposed
pg_privschema (
priobj oid, prigrantor oid, prigrantee oid,
char pritarget, -- 't' = any table, 'v' = any view, ...
char priselect,
char priupdate,
/* etc */
)
But this would mean that a check like "can I select from this table"
would possibly require lookups in two tables. Not sure how much of a
tradeoff that is, but the "shoehorn factor&
[HACKERS] Any optimizations to the join code in 7.1?
Hello. I have a particular query which performs a 15-way join; I believe in normalization ;-). Under 7.0.3, using the defaults where GEQO is enabled after 11, the query (which returns 1 row) takes 10 seconds. With GEQO turned off, it takes 18 seconds. Naturally I intend to upgrade as soon as possible, but I looked through the change log and didn't see anything specific WRT large joins. I was wondering if any work had been done in that area for 7.1. I realize you can only squeeze so much blood from stone, but Thanks for any info, 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] Any optimizations to the join code in 7.1?
Sorry for the delay in the response. It took be a day to get everything upgraded to 7.1. To restate the problem - in 7.0 with GEQO enabled, a 15-way join took 10 seconds. With GEQO disabled it took 18 seconds. 7.1 out of the box took only 2 seconds! I was amazed and shocked at this damned impressive improvement in planning speeduntil I actually used the explicit JOIN syntax described in 11.2. Instanteous results! Instantaneous. Thanks a bunch, (still in shock) Mike Mascari [EMAIL PROTECTED] -Original Message- From: Tom Lane [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, April 25, 2001 12:42 PM To: [EMAIL PROTECTED] Cc: '[EMAIL PROTECTED]' Subject:Re: [HACKERS] Any optimizations to the join code in 7.1? Mike Mascari [EMAIL PROTECTED] writes: I have a particular query which performs a 15-way join; You should read http://www.postgresql.org/devel-corner/docs/postgres/explicit-join s.html regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
RE: [HACKERS] Re: Any optimizations to the join code in 7.1?
What would be nice, and I don't know how it would be done or what the syntax would be, would be a feature that allows PostgreSQL to skip not only the parsing stage, but the planning stage as well. Then, when the data has changed dramatically enough to warrant it, as you point out, a command can be issued to 'refresh' the query plan. My 15-way join has expanded to a 19-way join and is still instantaneous, albeit on a very small set of data. Before 7.1, the query would simply have taken far too long, and I would have had to denormalize the database for performance purposes. With the explicit join syntax, it allows me to design the database 'the right way'. I basically used EXPLAIN SELECT... to determine the explicit join order, so as the data changes, its something I'll have to do on occassion to ensure good performance, but at least its now possible. :-) Mike Mascari [EMAIL PROTECTED] -Original Message- From: Thomas Lockhart [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 27, 2001 9:49 PM To: [EMAIL PROTECTED]; 'Tom Lane' Cc: '[EMAIL PROTECTED]' Subject:[HACKERS] Re: Any optimizations to the join code in 7.1? ... 7.1 out of the box took only 2 seconds! I was amazed and shocked at this damned impressive improvement in planning speeduntil I actually used the explicit JOIN syntax described in 11.2. Instanteous results! Instantaneous. But it is possible, under many circumstances, for query optimization to be a benefit for a many-table query. The docs indicate that explicit join syntax bypasses that, even for inner joins, so you may find that this syntax is a net loss in performance depending on the query and your choice of table order. Presumably we will be interested in making these two forms of inner join equivalent in behavior in a future release. Tom, what are the impediments we might encounter in doing this? - Thomas ---(end of broadcast)- -- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Possible feature?
Hello. I was just curious if you guys would accept a feature which would allow for the generation of non-standard messages for the violation of index, check, and referential integrity constraints. I understand that Peter E's proposal regarding error messages would allow clients to determine in greater detail the cause of an elog(). However, I think it might be of value to implement something which would allow the user to override the default message sent by the backend. An example situation would be like this: CREATE TABLE employees ( employeeid integer not null, ssnumber text not null ); CREATE UNIQUE INDEX i_employees on employees(ssnumber); MESSAGE ON INDEX i_employees IS 'An employee with a matching Social Security number already exists'; Then, when the UNIQUE constraint of the index is violated, instead of the message: 'Cannot insert a duplicate key into a unique index i_test1' the client application would receive: 'An employee with a matching Social Security number already exists' The benefit to a feature like this is that each client application doesn't need to handle the generation of the appropriate error messages themselves, but instead can rely on the database to do so. In fact, it wouldn't be too hard to have a SET command to set the client language (much like CLIENT_ENCODING) that would return the message appropriate for the language of the client. Another example: CREATE TABLE cars ( model integer not null, make integer not null, color text not null constraint check_color check (color = 'Red' or color = 'Blue') ); MESSAGE ON CONSTRAINT check_color IS 'Only Red or Blue cars are valid. Please refer to page 12 of the User''s Guide'; Of course, its quite probable that all of this belongs in each of the clients, but it seems trivial to do, much like pg_description and COMMENT ON. This is obviously an informal suggestion to determine if the idea should be rejected out-of-hand. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Is there a way to drop and restore an index?
Just off the top of my head, Couldn't you write a little PL/PGSQL procedure which queries the system tables and builds statements to execute with the new EXECUTE command for each record returned that would drop and recreate the indexes? It would take a little work but would be generic enough to automatically reindex your entire DB. Just a thought, Mike Mascari [EMAIL PROTECTED] Tony Reina wrote: I recall seeing a message by Tom Lane stating that dropping and re-creating a primary index may speed up db performance. Is there a SQL command that will do this? My current method is to use pg_dump -s to dump out the schema. Then I go through and cut out everything but the CREATE INDEX lines. Then, I have to add a DROP INDEX line before that. I run this through with the psql command line program. Is there a better way? Thanks. -Tony ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Planned change in initdb-time OID allocation
Tom Lane wrote: Presently, we have hand-assigned OIDs running up to about 1950 (according to the unused_oids script). The range up to 16K is reserved for hand-assigned OIDs, and the OID counter starts at 16384 at initdb. A peek in pg_database shows datlastsysoid = 18931 in current sources, so a total of about 2550 OIDs are machine-assigned during initdb. ... There are a couple of possible ways to attack this, but the one that seems best to me is to allow genbki.sh itself to assign OIDs to DATA lines that don't have one. It could start at, say, 1, staying well clear of both the hand-assigned OIDs and the ones that will be generated on-the-fly by the backend. Then it would know the correct OID to associate with any DESCR macro. Comments, objections? I was wondering in the past if it would simply be better to have an .SQL script which is submitted to the template1 database at post-initdb time with COMMENT ON statements to document built-in types, functions, system relations, etc. I should, after all, be able to issue a \d+ pg_class in psql and get a description of the columns. The .SQL script could potentially contain COMMENT ON statements localized to the language in which the database is installed, but it wouldn't care what OIDs were assigned (if any) to the various objects being documented. 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] [GENERAL] To Postgres Devs : Wouldn't changing the select
Bruce Momjian wrote: (switched thread to hackers) ... If the 'tip' is localized to a few lines, usually in gram.y, I don't see a reason not to help people find the right answer. It helps them and reduces redundant bug repots. I can't imagine a reason not to do it unless it starts to make our code more complex. I'm with Peter on this one. I'd like to *not* clutter up the code and error reporting with hints and suggestions which may or may not be to the point. We *should* have docs which list error messages and possible solutions, and throwing that info into code is a poor second choice imho. Is it really clutter to add a clause and elog(). I am not advocating adding stuff like crazy, but when we see people having the same problem, it seems worth adding it. Our docs are pretty big and most people who have this type of problem are not going to know where to look in the docs. If the elog pointed them to the proper section in the docs, that would be even better, but then again, you are doing the elog at that point. What do others think? It would be good to have a specific example to discuss. FWIW, Oracle has its oerr utility which takes the arguments: oerr facility error-code So the RDBMS generates an error code with a single line message less than or equal to 76 characters in length, prefixed by the facility and error code: ORA-01034: ORACLE not available The user can then get detailed information through the oerr utility. It would be nice, when we have error codes (are they apart of the new NLS support?), we have a pgerr utility to serve the same purpose. And of course the message files shipped with Oracle contain localized messages. Example output: $oerr ora 12203 12203, 0, TNS:unable to connect to destination // *Cause: Invalid TNS address supplied or destination is not listening. // This error can also occur because of underlying network transport // problems. // *Action: Verify that the service name you entered on the command line // was correct. Ensure that the listener is running at the remote node and // that the ADDRESS parameters specified in TNSNAMES.ORA are correct. // Finally, check that all Interchanges needed to make the connection are // up and running. It would then be nice to have both a command-line version of the PostgreSQL equivalent and a web-based version on postgresql.org for users to use. Just my 2 cents, of course, Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
Bruce Momjian wrote: Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These features are needed for pgAdmin II (we could also provide a patch for PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for pseudo-modification solutions (which is definitely not a good solution). Our current CREATE OR REPLACE FUNCTION perserves the OID of the function. Is there similar functionality you need where a simple DROP (ignore the error), CREATE will not work? If possible, it's nice to not have commands whose error codes you ignore. That way if you see an error, you know you need to do something about it. Folks, is this a valid reason for adding OR REPLACE to all CREATE object commands? Well, Oracle has CREATE OR REPLACE for: Views Functions Procedures Triggers Types Packages but not for (at least 8.0.5): Tables Indexes Sequences At first glance, I'm not sure why Oracle doesn't allow for the replacement of the non-compiled objects. Perhaps the complexities involved in enforcing RI was too much. The *major* advantage to allowing a REPLACE in Oracle is to preserve permissions granted to various users and groups (aka ROLES). Oracle automatically recompiles views, functions, procedures, etc. if their underlying dependencies change: SQL CREATE TABLE employees (key integer, salary float); Table created. SQL CREATE VIEW salaries AS SELECT * FROM employees WHERE salary 15000; View created. SQL SELECT * FROM salaries; no rows selected SQL DROP TABLE employees; Table dropped. SQL SELECT * FROM salaries; SELECT * FROM salaries * ERROR at line 1: ORA-04063: view MASCARM.SALARIES has errors SQL CREATE TABLE employees (key integer, salary float); Table created. SQL SELECT * FROM salaries; no rows selected So it seems to me that the major reason is to preserve GRANT/REVOKE privileges issues against the object in question. FWIW, 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] What about CREATE OR REPLACE FUNCTION?
Christopher Kings-Lynne wrote: I seem to recall that Oracle has all sorts of fancy resource limits that can be applied to users. If such resource limits were implemented, then maybe the DBA could have the power to limit someone to a maximum of 20% cpu and a few transactions per second or something. Chris I was hoping that after completing the current project I'm working on I might be able to contribute this feature. Oracle calls them PROFILEs which are a set of resource limits associated with a user. They can limit: No. of simultaneous connections No. of blocks read per query No. of blocks read per connection CPU time per query CPU time per connection Idle time as well as a few more esoteric others. I haven't looked at the new system resource reporting system that Jan wrote, but I suspect some of the statistics he gathers might already be available. Limiting simultaneous connections by a user might take a little effort. Limiting idle time might as well. Both have been a requested feature in the past, but have pitfalls associated with them. But right now denial of service for a user with database access is easy: soak up all available connections. Like Jan's resource statistics collector, Oracle's profiles must be enabled in the initSID.ora configuration file since it takes a few cycles to actually account for user activity. Mike Mascari [EMAIL PROTECTED] Tom Lane writes: I believe the primary reason why PL languages aren't installed by default is security considerations Well, that argumentation seems to be analogous to giving someone login access on a multiuser computer system but not letting him execute, say, perl because he might write recursive functions with it. Such setups exist (perhaps with something else instead of perl and recursive functions) but they are not the norm and usually fine-tuned by the administrator. ... Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Setting Password
You need to change the pg_hba.conf file in your PostgreSQL installation so that password authentication is used. Check out: http://www.postgresql.org/idocs/index.php?client-authentication.html for details. Hope that helps, Mike Mascari [EMAIL PROTECTED] Balaji Venkatesan wrote: Hi List, Iam pretty new to this list as well as PostgreSQL. I hope to find some crucial info from here. Thnx in advance to all those who would contribute to it. Iam basically an Oracle Consultant. At first i would like to clarify how to enforce password for a user i have created. I use the psql client to access the database and unless and until the -U option (psql template1 -U user ) is used, iam not prompted to enter any password. Even thou i enter a wrong password iam still allowed to log in. Is there any property needs to be altered to enforce the same ? Looking forward for some favourable responses. Regards Balaji ---(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] 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] 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] 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] 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] 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] 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
