[HACKERS] 7.4 Wishlist
SHOW
I think 7.4 could and really should implement SHOW command similar to
MySQL. Listing tables/foreign keys/views and so isn't just psql problem,
but common to many interface implementations and maintenance tools. I
think it's wrong to rely on pg_* system tables on these.
If you think of some probable new user (changing from MySQL) who asks how
to query list of tables in PostgreSQL and gets the answer (from psql -E)
that is:
SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as Type,
u.usename as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
opposed to something like
SHOW TABLES_AND_VIEWS
as in MySQL, which DBMS would this user prefer?
I further suggest that these SHOW command parameters (like tables and
views) could be views in special system schema, so it could be easy to
update them just by changing templates. Maybe 7.5 (if it had introduced
new features) could provide downward compatibility to 7.4 ready clients by
allowing the use of SHOW-views from different schema (like
pg_show_743_compat)
:)
Stored procedures used in implementing new syntax
-
This is an implementation detail suggestion. Would it be possible that
new syntax in SQL could be implemented in different languages than C.
We
---(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] 7.4 Wishlist
Ups... i sent an early draft of my post by accident, sorry... -- Antti Haapala +358 50 369 3535 ICQ: #177673735 ---(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] 7.4 Wishlist
On Wed, 2003-01-22 at 04:55, Antti Haapala wrote: SHOW I think 7.4 could and really should implement SHOW command similar to MySQL. Listing tables/foreign keys/views and so isn't just psql problem, Actually, in 7.4 I'd tell them to: select * from information_schema.tables; This is a far more portable method. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] 7.4 Wishlist
SHOW I think 7.4 could and really should implement SHOW command similar to MySQL. Listing tables/foreign keys/views and so isn't just psql problem, Actually, in 7.4 I'd tell them to: select * from information_schema.tables; This is a far more portable method. Yep. You know it was just a draft which wasn't actually meant to be sent... I think the contents of that information schema could and should be user modifiable views... I needed to administer one of my dbs on 7.3 from another computer with psql 7.2.3 -- for the computer had PG 7.2.3 running, and \d on for example views failed gratuituosly with error relation pg_relcheck not found. Of course the dumps, sql commands and tsv data etc. I needed were only on that 7.2.3 machine... Many interfaces need to enumerate tables databases etc. I'm not interested in having different versions of for example DBD::Pg... one, the most up-to-date, version should do the job. Against whatever version of postmaster I want to use it. So maybe backwards compatibility could be introduced also (older clients applications against newer DBMS) by having different information schemas for different client versions... don't know, how it should be actually done. Maybe there would be schemas info_compat_74, info_compat_75 and information_schema_80 in PostgreSQL 8.0 :P and information_schema could be alias to most appropriate of these, depending on client version :) (dunno). -- Antti Haapala +358 50 369 3535 ICQ: #177673735 ---(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] 7.4 Wishlist
Hi, if I may add to the wishlist for 7.4 in order of importance. Some items may have been mentioned or disputed already but I think they are quite important: 1. Avoid needing REINDEX after large insert/deletes or make REINDEX not use exclusive lock on table. 2. Automate VACUUM in background and make database more interactive/responsive during long VACUUMs 3. Replication 4. Point-in-time recovery 5. Maintain automatic clustering (CLUSTER) even after subsequent insert/updates. 6. Compression between client/server interface like in MySQL Thanks, Stephen jleelim(at)hotmail.com ---(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] 7.4 Wishlist
On Tue, 2002-12-10 at 09:36, Stephen L. wrote: 6. Compression between client/server interface like in MySQL Mammoth is supposed to be donating their compression efforts back to this project, or so I've been told. I'm not exactly sure of their time-line as I've slept since my last conversation with them. The initial feedback that I've gotten back from them on this subject is that the compression has been working wonderfully for them with excellent results. IIRC, in their last official release, they announced their compression implementation. So, I'd think that it would be available for 7.4 of 7.5 time frame. -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [mail] Re: [HACKERS] 7.4 Wishlist
Would it be possible to make compression an optional thing, with the default being off? I'm in a position that many others may be in where the link between my app server and my database server isn't the bottleneck, and thus any time spent by the CPU performing compression and decompression tasks is CPU time that is in effect wasted. If a database is handling numerous small queries/updates and the request/response packets are compressed individually, then the overhead of compression and decompression may result in slower performance compared to leaving the request/response packets uncompressed. Al. - Original Message - From: Greg Copeland [EMAIL PROTECTED] To: Stephen L. [EMAIL PROTECTED] Cc: PostgresSQL Hackers Mailing List [EMAIL PROTECTED] Sent: Tuesday, December 10, 2002 4:56 PM Subject: [mail] Re: [HACKERS] 7.4 Wishlist On Tue, 2002-12-10 at 09:36, Stephen L. wrote: 6. Compression between client/server interface like in MySQL Mammoth is supposed to be donating their compression efforts back to this project, or so I've been told. I'm not exactly sure of their time-line as I've slept since my last conversation with them. The initial feedback that I've gotten back from them on this subject is that the compression has been working wonderfully for them with excellent results. IIRC, in their last official release, they announced their compression implementation. So, I'd think that it would be available for 7.4 of 7.5 time frame. -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [mail] Re: [HACKERS] 7.4 Wishlist
On Tue, 2002-12-10 at 11:25, Al Sutton wrote: Would it be possible to make compression an optional thing, with the default being off? I'm not sure. You'd have to ask Command Prompt (Mammoth) or wait to see what appears. What I originally had envisioned was a per database and user permission model which would better control use. Since compression can be rather costly for some use cases, I also envisioned it being negotiated where only the user/database combo with permission would be able to turn it on. I do recall that compression negotiation is part of the Mammoth implementation but I don't know if it's a simple capability negotiation or part of a larger scheme. The reason I originally imagined a user/database type approach is because I would think only a subset of a typical installation would be needing compression. As such, this would help prevent users from arbitrarily chewing up database CPU compressing data because: o datasets are uncompressable or poorly compresses o environment cpu is at a premium o is in a bandwidth rich environment I'm in a position that many others may be in where the link between my app server and my database server isn't the bottleneck, and thus any time spent by the CPU performing compression and decompression tasks is CPU time that is in effect wasted. Agreed. This is why I'd *guess* that Mammoth's implementation does not force compression. If a database is handling numerous small queries/updates and the request/response packets are compressed individually, then the overhead of compression and decompression may result in slower performance compared to leaving the request/response packets uncompressed. Again, this is where I'm gray on their exact implementation. It's possible they implemented a compressed stream even though I'm hoping they implemented a per packet compression scheme (because adaptive compression becomes much more capable and powerful; in both algorithmically and logistical use). An example of this would be to avoid any compression on trivially sized result sets. Again, this is another area where I can imagine some tunable parameters. Just to be on the safe side, I'm cc'ing Josh Drake at Command Prompt (Mammoth) to see what they can offer up on it. Hope you guys don't mind. Greg - Original Message - From: Greg Copeland [EMAIL PROTECTED] To: Stephen L. [EMAIL PROTECTED] Cc: PostgresSQL Hackers Mailing List [EMAIL PROTECTED] Sent: Tuesday, December 10, 2002 4:56 PM Subject: [mail] Re: [HACKERS] 7.4 Wishlist On Tue, 2002-12-10 at 09:36, Stephen L. wrote: 6. Compression between client/server interface like in MySQL Mammoth is supposed to be donating their compression efforts back to this project, or so I've been told. I'm not exactly sure of their time-line as I've slept since my last conversation with them. The initial feedback that I've gotten back from them on this subject is that the compression has been working wonderfully for them with excellent results. IIRC, in their last official release, they announced their compression implementation. So, I'd think that it would be available for 7.4 of 7.5 time frame. -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [mail] Re: [HACKERS] 7.4 Wishlist
Greg Copeland wrote: On Tue, 2002-12-10 at 11:25, Al Sutton wrote: Would it be possible to make compression an optional thing, with the default being off? I'm not sure. You'd have to ask Command Prompt (Mammoth) or wait to see what appears. What I originally had envisioned was a per database and user permission model which would better control use. Since compression can be rather costly for some use cases, I also envisioned it being negotiated where only the user/database combo with permission would be able to turn it on. I do recall that compression negotiation is part of the Mammoth implementation but I don't know if it's a simple capability negotiation or part of a larger scheme. I haven't heard anything about them contributing it. Doesn't mean it will not happen, just that I haven't heard it. I am not excited about per-db/user compression because of the added complexity of setting it up, and even set up, I can see cases where some queries would want it, and others not. I can see using GUC to control this. If you enable it and the client doesn't support it, it is a no-op. We have per-db and per-user settings, so GUC would allow such control if you wish. Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO, meaning it would determine if there was value in the compression and do it only when it would help. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [mail] Re: [HACKERS] 7.4 Wishlist
On Tue, 2002-12-10 at 13:38, Bruce Momjian wrote: I haven't heard anything about them contributing it. Doesn't mean it will not happen, just that I haven't heard it. This was in non-mailing list emails that I was told this by Joshua Drake at Command Prompt. Of course, that doesn't have to mean it will be donated for sure but nonetheless, I was told it will be. Here's a quote from one of the emails. I don't think I'll be too far out of line posting this. On August 9, 2002, Joshua Drake said, One we plan on releasing this code to the developers after 7.3 comes out. We want to be good members of the community but we have to keep a slight commercial edge (wait to you see what we are going to do to vacuum). Obviously, I don't think that was official speak, so I'm not holding them to the fire, nonetheless, that's what was said. Additional follow ups did seem to imply that they were very serious about this and REALLY want to play nice as good shared source citizens. I am not excited about per-db/user compression because of the added complexity of setting it up, and even set up, I can see cases where some queries would want it, and others not. I can see using GUC to control this. If you enable it and the client doesn't support it, it is a no-op. We have per-db and per-user settings, so GUC would allow such control if you wish. I never thought beyond the need for what form an actual implementation of this aspect would look like. The reason for such a concept would be to simply limit the number of users that can be granted compression. If you have a large user base all using compression or even a small user base where very large result sets are common, I can imagine your database server becoming CPU bound. The database/user thinking was an effort to allow the DBA to better manage the CPU effect. Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO, meaning it would determine if there was value in the compression and do it only when it would help. Yes, that makes sense and was something I had originally envisioned. Simply stated, some installations may never want compression while others may want it for every connection. Beyond that, I believe there needs to be something of a happy medium where a DBA can better control who and what is taking his CPU away (e.g. only that one remote location being fed via ISDN). If GUC can fully satisfy, I certainly won't argue against it. -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [mail] Re: [HACKERS] 7.4 Wishlist
Yes, the issue was that give our TODO list, compressed transfer wasn't very high, and it was unknown how valuable it would be. However, if it were contributed, we could easily test its value with little work on our part and include the code if it were a win. --- Joshua D. Drake wrote: Hello, We would probably be open to contributing it if there was interest. There wasn't interest initially. Sincerely, Joshua Drake Bruce Momjian wrote: Greg Copeland wrote: On Tue, 2002-12-10 at 11:25, Al Sutton wrote: Would it be possible to make compression an optional thing, with the default being off? I'm not sure. You'd have to ask Command Prompt (Mammoth) or wait to see what appears. What I originally had envisioned was a per database and user permission model which would better control use. Since compression can be rather costly for some use cases, I also envisioned it being negotiated where only the user/database combo with permission would be able to turn it on. I do recall that compression negotiation is part of the Mammoth implementation but I don't know if it's a simple capability negotiation or part of a larger scheme. I haven't heard anything about them contributing it. Doesn't mean it will not happen, just that I haven't heard it. I am not excited about per-db/user compression because of the added complexity of setting it up, and even set up, I can see cases where some queries would want it, and others not. I can see using GUC to control this. If you enable it and the client doesn't support it, it is a no-op. We have per-db and per-user settings, so GUC would allow such control if you wish. Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO, meaning it would determine if there was value in the compression and do it only when it would help. -- COMPANYCommandPrompt- http://www.commandprompt.com /COMPANY CONTACT PHONE+1.503.222-2783/PHONE /CONTACT -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 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: [mail] Re: [HACKERS] 7.4 Wishlist
I'd like to show/register interest. I can see it being very useful when combined with replication for situations where the replicatiant databases are geographically seperated (i.e. Disaster Recover sites or systems maintaining replicants in order to reduce the distance from user to app to database). The bandwidth cost savings from compressing the replication information would be immensly useful. Al. - Original Message - From: Joshua D. Drake [EMAIL PROTECTED] To: Bruce Momjian [EMAIL PROTECTED] Cc: Greg Copeland [EMAIL PROTECTED]; Al Sutton [EMAIL PROTECTED]; Stephen L. [EMAIL PROTECTED]; PostgresSQL Hackers Mailing List [EMAIL PROTECTED] Sent: Tuesday, December 10, 2002 8:04 PM Subject: Re: [mail] Re: [HACKERS] 7.4 Wishlist Hello, We would probably be open to contributing it if there was interest. There wasn't interest initially. Sincerely, Joshua Drake Bruce Momjian wrote: Greg Copeland wrote: On Tue, 2002-12-10 at 11:25, Al Sutton wrote: Would it be possible to make compression an optional thing, with the default being off? I'm not sure. You'd have to ask Command Prompt (Mammoth) or wait to see what appears. What I originally had envisioned was a per database and user permission model which would better control use. Since compression can be rather costly for some use cases, I also envisioned it being negotiated where only the user/database combo with permission would be able to turn it on. I do recall that compression negotiation is part of the Mammoth implementation but I don't know if it's a simple capability negotiation or part of a larger scheme. I haven't heard anything about them contributing it. Doesn't mean it will not happen, just that I haven't heard it. I am not excited about per-db/user compression because of the added complexity of setting it up, and even set up, I can see cases where some queries would want it, and others not. I can see using GUC to control this. If you enable it and the client doesn't support it, it is a no-op. We have per-db and per-user settings, so GUC would allow such control if you wish. Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO, meaning it would determine if there was value in the compression and do it only when it would help. -- COMPANYCommandPrompt - http://www.commandprompt.com /COMPANY CONTACT PHONE+1.503.222-2783/PHONE /CONTACT ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
Bruce Momjian wrote: Kevin Brown wrote: I'd also like to see (if this is even possible) a transaction isolation mode that would make it possible for multiple concurrent updates to the same row to happen without blocking each other (I imagine one way to make this possible would be for the last transaction to commit to be the one that wins. Each transaction that commits gets its updates written so that other transactions that begin after they commit will see them, of course). Neither read committed nor serialized modes offer this. Don't know if it's possible, but it would be nice (such that a transaction sees the database as if it has it all to itself and doesn't block on updates)... How would you do the update if you don't know of the transaction commits or aborts? Maybe I should explain what I'm thinking a little further. What I'm proposing is a transaction isolation model where each transaction thinks that it's the only one making changes to the database. That assumption obviously fails *outside* of a transaction, but inside a transaction it should be possible to maintain the illusion. We already get this with serialized transaction isolation mode, with one caveat: when an update conflicts with that of another transaction that committed while the transaction of interest is in progress, the transaction of interest gets rolled back immediately with a serialization error. What I'm proposing is to extend the illusion to updates. A transaction running in this new isolation mode (call it full isolation or something) never sees updates that other committed transactions perform, whether they would conflict or not. The view the running transaction sees of the database is a snapshot in time, as it were, plus any updates the transaction itself has made. Now, there are a couple of approaches we can take at transaction commit that I can think of right now: 1. If there were any serialization errors, abort the transaction at commit time. This allows transactions to safely make changes to their local view of the database without compromising serialization. This probably wouldn't yield any benefits over the serializable isolation level except that it would make it possible to perform experiments on a database that currently can't be performed (since serializable isolation aborts the transaction at the first serialization error). Applications would be easier to write since there would be only one point during a transaction that the application would have to check for unexpected errors: the commit. 2. We commit the changed rows. Updates only happen to rows that exist at commit time. Rows which the transaction deleted and which still exist at commit time are deleted. Referential integrity rules are enforced, of course, so it's possible for the database to retain some of its sanity even in the face of this model. But the overall state of the database will probably be unpredictable (in this scenario, the last concurrent transaction to commit wins, more or less). 3. We do something more sophisticated than 1 or 2. Perhaps something analogous to the branch merge functions that CVS and other concurrent version control systems can perform, where multiple branches are finally merged into a single unified source snapshot. I have no idea how this would work for real, or if it's even possible (I believe CVS requires manual intervention to resolve branch conflicts during a merge, an option that would obviously not be available to us). How useful would it be? Beats me. Like I said, you could perform some what if games with a database this way that you currently can't, but I don't know how useful that would be. On thinking about it a bit, it seems option 1 would be the most useful and perhaps the most sensible. Of course, perhaps the whole thing is just another stupid idea... ---(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] 7.4 Wishlist
Tom Lane wrote: Magnus Naeslund(f) [EMAIL PROTECTED] writes: Mysql is planning on making this work: SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id. We're supposed to spend our time emulating nonstandard features that don't even exist yet? I think I have better things to do ... MySQL doesn't have it, but I believe MS SQL does (and thus Sybase probably does as well). I agree with others that variables would be quite handy, especially if they persist between statements (and they might be even handier if they persist between transactions). That's not to say that you don't have better things to work on, though. :-) -- Kevin Brown [EMAIL PROTECTED] This is your .signature virus: begin 644 .signature (9V]T8VAA(0K0z end This is your .signature virus on drugs: Any questions? ---(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] 7.4 Wishlist
Kevin Brown wrote: How useful would it be? Beats me. Like I said, you could perform some what if games with a database this way that you currently can't, but I don't know how useful that would be. On thinking about it a bit, it seems option 1 would be the most useful and perhaps the most sensible. Of course, perhaps the whole thing is just another stupid idea... We would need to have some people who want this first. We don't add stuff of questionable value because then the feature set becomes confusing to end users. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Broadcast replication (Was Re: [HACKERS] 7.4 Wishlist)
- Original Message - From: Kevin Brown [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 03, 2002 8:49 PM Subject: [mail] Re: [HACKERS] 7.4 Wishlist Al Sutton wrote: Point to Point and Broadcast replication With point to point you specify multiple endpoints, with broadcast you can specify a subnet address and the updates are broadcast over that subnet. The difference being that point to point works well for cross network replication, or where you have a few replicants. I have multiple database servers which could have a deadicated class C network that they are all on, by broadcasting updates you can cutdown the amount of traffic on that net by a factor of n minus 1 (where n is the number of servers involved). Yech. Now you can't use TCP anymore, so the underlying replication code has to handle all the issues that TCP deals with transparently, like error checking, retransmits, data windows, etc. I don't think it's wise to assume that your transport layer is 100% reliable. Further, this doesn't even address the problem of bringing up a leaf server that's been down a while. It can be significantly out of date relative to the other servers on the subnet. I suspect you'll be better off implementing a replication protocol that has the leaf nodes keeping each other up to date, to minimize the traffic coming from the next level up. Then you can use TCP for the connections but minimize the traffic generated by any given node. I wasn't saying that ALL replication traffic must be broadcast, if a specific server needs a refresh when it comes then point to point is fine because only one machine needs the data, and thus broadcasting it to all would load machines with data they didn't need. The aim of using broadcast is to cut down the ongoing traffic, say, for example, I have a cluster of ten database servers I can connect them onto a dedicated LAN shared only by database servers and I would see 10% of the traffic I would get if I were using point to point (this is assuming that the addition of error checking, retransmits, etc. to the broadcast protocol adds a similiar overhead per packet as TCP point to point). If others wish to know more about this I can prepare an overview for how I see it working. [Other points snipped] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? Well, on top of the oft-requested replication support and savepoint support, I'd like to see UPDATE, er, updated to be able to make use of cursors. I'd also like to see (if this is even possible) a transaction isolation mode that would make it possible for multiple concurrent updates to the same row to happen without blocking each other (I imagine one way to make this possible would be for the last transaction to commit to be the one that wins. Each transaction that commits gets its updates written so that other transactions that begin after they commit will see them, of course). Neither read committed nor serialized modes offer this. Don't know if it's possible, but it would be nice (such that a transaction sees the database as if it has it all to itself and doesn't block on updates)... - Kevin ---(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] 7.4 Wishlist
Kevin Brown wrote: Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? Well, on top of the oft-requested replication support and savepoint support, I'd like to see UPDATE, er, updated to be able to make use of cursors. I think this could be easily done by using the tid of the cursor row for the update, assuming there is a clear tid for the SELECT. Jan has talked about doing that. I'd also like to see (if this is even possible) a transaction isolation mode that would make it possible for multiple concurrent updates to the same row to happen without blocking each other (I imagine one way to make this possible would be for the last transaction to commit to be the one that wins. Each transaction that commits gets its updates written so that other transactions that begin after they commit will see them, of course). Neither read committed nor serialized modes offer this. Don't know if it's possible, but it would be nice (such that a transaction sees the database as if it has it all to itself and doesn't block on updates)... How would you do the update if you don't know of the transaction commits or aborts? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
I'm certainly interested! I am working here on Geographical Information Systems with PostgreSQL/PostGIS with the Minnesota MapServer, with a lot of regular database work thrown in. PostgreSQL has great potential for teaching databases and SQL, and when the native Windows port is ready, it will also be ideal for smaller, individual teaching projects (e.g. at home). Jan Hartmann Department of Geography University of Amsterdam [EMAIL PROTECTED] Gavin Sherry wrote: On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote: I've given a talk in the 2002 honours lecture series at UWA about Postgres and some of the things it can do. All of those guys were interested. Especially since the deptartment does a lot of work in genetic algoriithms. Excellent. Can you put that talk online somewhere? Tell me when you start working on a document - I'm happy to help. Since I'm only just out of Uni, I'd like to write a set of possible assignments and learning outcomes and how you can use postgres to support them. My girlfriend is a PhD student at UWA CS dept :) plus I won the honours scholarship there a year or two back, so I can get interest from the dept, including the databases lecturer. Might help for another point of view and feedback. Excellent. Are there any other people involved in PostgreSQL and universities or educational institutions? If so we could put something together about experiences for the advocacy Web site. That's tragic. Teaching kids to admin oracle is something you do in Tafe, or an Oracle course, not a university. Anyway, what kind of course teaches you about how to admin oracle as opposed to teaching you about ACID properties, MVCC, distributed transactions and partitioning? Most of which can be demonstrated with Postgres. We learnt about relational model, algebra and calculus well before learning about SQL! Your interest in this is clearly the same as mine: Universities (should) teach concept not product. I'm disgusted that this is not the case. If other people are interested we could work on this in January when I am over your way, as discussed in private email. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] 7.4 Wishlist
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
Something like this could work:
select *
from (select t.id, date_part('days',now()-t.stamp) AS d
from table_name t) AS t1
where t1.d 20;
That aside I also would like some sort of local names. Something like the
let construct used in many functional languages (not exaclty what you want
above, but still):
let t1 = select * from foo;
t2 = select * from bar;
in select * from t1 natural join t2;
But even though I would like to give name to subexpressions like above, I
still think postgresql should stick to standards as close as possible.
--
/Dennis
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
On Mon, Dec 02, 2002 at 12:48:38PM -0800, Christopher Kings-Lynne wrote: But if there is, then the sum/count(*) is nonsensical anyway. You must to use it in SERIALIZABLE transaction isolation. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] 7.4 Wishlist
On Tue, 2002-12-03 at 09:20, Dennis Björklund wrote:
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
Something like this could work:
select *
from (select t.id, date_part('days',now()-t.stamp) AS d
from table_name t) AS t1
where t1.d 20;
That aside I also would like some sort of local names. Something like the
let construct used in many functional languages (not exaclty what you want
above, but still):
let t1 = select * from foo;
t2 = select * from bar;
in select * from t1 natural join t2;
But even though I would like to give name to subexpressions like above, I
still think postgresql should stick to standards as close as possible.
the standard way of doing it would be SQL99's WITH :
with t1 as (select * from foo)
t2 as (select * from bar)
select * from t1 natural join t2;
you can even use preceeding queries
with t1 as (select a,b from foo)
t1less as (select a,b from t1 where a 0)
t1zero as (select a,b from t1 where a = 0)
select * from t1zero, t1less, where t1zero.b = t1less.a;
Having working WITH clause is also a prerequisite to implementing SQL99
recursive queries (where each query in WITH clause sees all other
queries in the WITH clause)
I sent a patch to this list recently that implements the above syntax,
but I currently dont have knowledge (nor time to aquire it), so if
someone else does not do it it will have to wait until January.
OTOH, I think that turning my parsetree to a plan would be quite easy
for someone familiar with turning parestrees into plans ;)
I offer to check if it works in current (and make it work again if it
does not) if someone would be willing to hold my hand in implementation
parsetree--plan part ;).
I think that for non-recursive queries this is all that needs to be
done, i.e. the plan would not care if the subqueries were from FROM,
from WITH or from separately defined views.
--
Hannu Krosing [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] 7.4 Wishlist
On 3 Dec 2002, Hannu Krosing wrote: the standard way of doing it would be SQL99's WITH : Great! I havn't looked too much at sql99 yet so I've missed this. It's exactly what I want. Now I know what I will use in the future (when it's all implemented). -- /Dennis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
On Tue, 3 Dec 2002, Justin Clift wrote: Excellent. Are there any other people involved in PostgreSQL and universities or educational institutions? If so we could put something together about experiences for the advocacy Web site. Is this the kind of thing that the Techdocs Guides area would be good for? (http://techdocs.postgresql.org/guides) Seems that any discussions about experiences belongs on Advocacy, no? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
On Tue, 3 Dec 2002, Marc G. Fournier wrote: On Tue, 3 Dec 2002, Justin Clift wrote: Excellent. Are there any other people involved in PostgreSQL and universities or educational institutions? If so we could put something together about experiences for the advocacy Web site. Is this the kind of thing that the Techdocs Guides area would be good for? (http://techdocs.postgresql.org/guides) Seems that any discussions about experiences belongs on Advocacy, no? Where have you been? The lines of distinction between all of the lists have gotten so blurred it hardly makes a difference. Vince. -- http://www.meanstreamradio.com http://www.unknown-artists.com Internet radio: It's not file sharing, it's just radio. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
On Tue, 3 Dec 2002, Vince Vielhaber wrote: On Tue, 3 Dec 2002, Marc G. Fournier wrote: On Tue, 3 Dec 2002, Justin Clift wrote: Excellent. Are there any other people involved in PostgreSQL and universities or educational institutions? If so we could put something together about experiences for the advocacy Web site. Is this the kind of thing that the Techdocs Guides area would be good for? (http://techdocs.postgresql.org/guides) Seems that any discussions about experiences belongs on Advocacy, no? Where have you been? The lines of distinction between all of the lists have gotten so blurred it hardly makes a difference. Actually, there are lines, Justin just occasionally appears to 'blur' them until I get a chance to refresh them ... eh Justin?:) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
Al Sutton wrote: Point to Point and Broadcast replication With point to point you specify multiple endpoints, with broadcast you can specify a subnet address and the updates are broadcast over that subnet. The difference being that point to point works well for cross network replication, or where you have a few replicants. I have multiple database servers which could have a deadicated class C network that they are all on, by broadcasting updates you can cutdown the amount of traffic on that net by a factor of n minus 1 (where n is the number of servers involved). Yech. Now you can't use TCP anymore, so the underlying replication code has to handle all the issues that TCP deals with transparently, like error checking, retransmits, data windows, etc. I don't think it's wise to assume that your transport layer is 100% reliable. Further, this doesn't even address the problem of bringing up a leaf server that's been down a while. It can be significantly out of date relative to the other servers on the subnet. I suspect you'll be better off implementing a replication protocol that has the leaf nodes keeping each other up to date, to minimize the traffic coming from the next level up. Then you can use TCP for the connections but minimize the traffic generated by any given node. Ability to use raw partitions I've not seen an install of PostgreSQL yet that didn't put the database files onto a filesystem, so I'm assuming it's the only way of doing it. By using the filesystem the files are at the mercy of filesystem handler code as to where they end up on the disk, and thus the speed of access will always have some dependancy on the speed of the filesystem. With a raw partition it would be possible to use two devices (e.g. /dev/hde and /dev/hdg on an eight channel ide linux box), and PostgreSQL could then ensure the WALs were located on one the disk with the entries running sequentally, and that the database files were located on the other disk in the most appropriate location (e.g. index data starting near the center of the disk, and user table data starting near the outside). Yeah, but now you have to worry about optimizing placement of blocks, optimizing writes, etc. These are things the OS should worry about, not the database server. If you're really that concerned about these issues, store the WAL on one (empty) filesystem and the tables on another (empty and separate) filesystem. With any reasonable filesystem you'll get reasonably close to optimal performance, especially if the filesystem code is capable of analyzing the write patterns and adapting itself accordingly. In short, I'd much rather spend the effort improving the filesystem (where everyone can benefit) than improving PostgreSQL (where only PostgreSQL users can benefit) for this item. The one good reason for making it possible to use raw partitions is to make it possible to use the PostgreSQL engine as a filesystem! :-) Win32 Port I've explained the reasons before. Apart from that it's always useful to open PostgreSQL up to a larger audience. Agreed. - Kevin Brown ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
On 3 Dec 2002 at 15:08, Vince Vielhaber wrote: Where have you been? The lines of distinction between all of the lists have gotten so blurred it hardly makes a difference. So consider this a wake up call. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
Is WITH a TODO item?
---
Hannu Krosing wrote:
On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
Something like this could work:
select *
from (select t.id, date_part('days',now()-t.stamp) AS d
from table_name t) AS t1
where t1.d 20;
That aside I also would like some sort of local names. Something like the
let construct used in many functional languages (not exaclty what you want
above, but still):
let t1 = select * from foo;
t2 = select * from bar;
in select * from t1 natural join t2;
But even though I would like to give name to subexpressions like above, I
still think postgresql should stick to standards as close as possible.
the standard way of doing it would be SQL99's WITH :
with t1 as (select * from foo)
t2 as (select * from bar)
select * from t1 natural join t2;
you can even use preceeding queries
with t1 as (select a,b from foo)
t1less as (select a,b from t1 where a 0)
t1zero as (select a,b from t1 where a = 0)
select * from t1zero, t1less, where t1zero.b = t1less.a;
Having working WITH clause is also a prerequisite to implementing SQL99
recursive queries (where each query in WITH clause sees all other
queries in the WITH clause)
I sent a patch to this list recently that implements the above syntax,
but I currently dont have knowledge (nor time to aquire it), so if
someone else does not do it it will have to wait until January.
OTOH, I think that turning my parsetree to a plan would be quite easy
for someone familiar with turning parestrees into plans ;)
I offer to check if it works in current (and make it work again if it
does not) if someone would be willing to hold my hand in implementation
parsetree--plan part ;).
I think that for non-recursive queries this is all that needs to be
done, i.e. the plan would not care if the subqueries were from FROM,
from WITH or from separately defined views.
--
Hannu Krosing [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
Marc G. Fournier wrote: On Tue, 3 Dec 2002, Justin Clift wrote: Excellent. Are there any other people involved in PostgreSQL and universities or educational institutions? If so we could put something together about experiences for the advocacy Web site. Is this the kind of thing that the Techdocs Guides area would be good for? (http://techdocs.postgresql.org/guides) Seems that any discussions about experiences belongs on Advocacy, no? Good point. Have put a *really basic* Zwiki framework at: http://advocacy.postgresql.org/documents It's the same collaborative software used for the PostgreSQL Guides section, but without the look+feel added. If you want to start editing stuff right away, then feel free to use it. If you'd like it to look better first though, then it'll be a few days... :-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
Marc G. Fournier wrote: snip Actually, there are lines, Justin just occasionally appears to 'blur' them until I get a chance to refresh them ... eh Justin?:) [innocent whistle] + Justin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
On Tue, 2002-12-03 at 16:00, Bruce Momjian wrote:
Is WITH a TODO item?
It is disguised as
Exotic Features
===
* Add sql3 recursive unions
Which was added at my request in dark times, possibly when PostgreSQL
was called postgres95 ;)
This should be changed to two items
* Add SQL99 WITH clause to SELECT
* Add SQL99 WITH RECURSIVE to SELECT
---
Hannu Krosing wrote:
On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
Something like this could work:
select *
from (select t.id, date_part('days',now()-t.stamp) AS d
from table_name t) AS t1
where t1.d 20;
That aside I also would like some sort of local names. Something like the
let construct used in many functional languages (not exaclty what you want
above, but still):
let t1 = select * from foo;
t2 = select * from bar;
in select * from t1 natural join t2;
But even though I would like to give name to subexpressions like above, I
still think postgresql should stick to standards as close as possible.
the standard way of doing it would be SQL99's WITH :
with t1 as (select * from foo)
t2 as (select * from bar)
select * from t1 natural join t2;
you can even use preceeding queries
with t1 as (select a,b from foo)
t1less as (select a,b from t1 where a 0)
t1zero as (select a,b from t1 where a = 0)
select * from t1zero, t1less, where t1zero.b = t1less.a;
Having working WITH clause is also a prerequisite to implementing SQL99
recursive queries (where each query in WITH clause sees all other
queries in the WITH clause)
I sent a patch to this list recently that implements the above syntax,
but I currently dont have knowledge (nor time to aquire it), so if
someone else does not do it it will have to wait until January.
OTOH, I think that turning my parsetree to a plan would be quite easy
for someone familiar with turning parestrees into plans ;)
I offer to check if it works in current (and make it work again if it
does not) if someone would be willing to hold my hand in implementation
parsetree--plan part ;).
I think that for non-recursive queries this is all that needs to be
done, i.e. the plan would not care if the subqueries were from FROM,
from WITH or from separately defined views.
--
Hannu Krosing [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
--
Hannu Krosing [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
TODO updated. Thanks for the clarification.
---
Hannu Krosing wrote:
On Tue, 2002-12-03 at 16:00, Bruce Momjian wrote:
Is WITH a TODO item?
It is disguised as
Exotic Features
===
* Add sql3 recursive unions
Which was added at my request in dark times, possibly when PostgreSQL
was called postgres95 ;)
This should be changed to two items
* Add SQL99 WITH clause to SELECT
* Add SQL99 WITH RECURSIVE to SELECT
---
Hannu Krosing wrote:
On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
Something like this could work:
select *
from (select t.id, date_part('days',now()-t.stamp) AS d
from table_name t) AS t1
where t1.d 20;
That aside I also would like some sort of local names. Something like the
let construct used in many functional languages (not exaclty what you want
above, but still):
let t1 = select * from foo;
t2 = select * from bar;
in select * from t1 natural join t2;
But even though I would like to give name to subexpressions like above, I
still think postgresql should stick to standards as close as possible.
the standard way of doing it would be SQL99's WITH :
with t1 as (select * from foo)
t2 as (select * from bar)
select * from t1 natural join t2;
you can even use preceeding queries
with t1 as (select a,b from foo)
t1less as (select a,b from t1 where a 0)
t1zero as (select a,b from t1 where a = 0)
select * from t1zero, t1less, where t1zero.b = t1less.a;
Having working WITH clause is also a prerequisite to implementing SQL99
recursive queries (where each query in WITH clause sees all other
queries in the WITH clause)
I sent a patch to this list recently that implements the above syntax,
but I currently dont have knowledge (nor time to aquire it), so if
someone else does not do it it will have to wait until January.
OTOH, I think that turning my parsetree to a plan would be quite easy
for someone familiar with turning parestrees into plans ;)
I offer to check if it works in current (and make it work again if it
does not) if someone would be willing to hold my hand in implementation
parsetree--plan part ;).
I think that for non-recursive queries this is all that needs to be
done, i.e. the plan would not care if the subqueries were from FROM,
from WITH or from separately defined views.
--
Hannu Krosing [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
--
Hannu Krosing [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 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: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
Hi: We at the Department of Information Technology of the Mindanao State University-Iligan Institute of Technology (MSU-IIT) in Iligan City, Philippines had been using PostgreSQL since 1998 in teaching courses in Databases, SQL, and as a support tool in teaching Software Engineering and Web Application Development. We are even utilizing it as our database backend in all applications we developed in-house like Payroll, Student Enrollment, Financial Applications, etc. At the rate PostgreSQL is performing, we are not for any reason tempted to migrate to another database for the next ten years. THANKS TO THE POSTGRESQL DEVELOPMENT TEAM. We Salute YOU! PROF. MANUEL C. CABIDO Chair Department of Information Technology MSU-IIT Iligan City 9200 Philippines ---(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] 7.4 Wishlist
Joe Conway writes: That is one thing I'd like to take a look at. I think the problem is that certain byte-sequence/multibyte-encoding combinations are illegal, so it's not as simple an issue as it might first appear. The bytea type really shouldn't come even close to having to care about this. Actually, if you want to improve the ugly bytea literal syntax, implement the standard BLOB type. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 7.4 Wishlist
Christopher Kings-Lynne writes: Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? Well judging by the hoards on Slashdot, it would appear that replication is the hot enhancement... Slashdot | PostgreSQL 7.3 Released http://developers.slashdot.org/article.pl?sid=02/11/30/1815200 Lee. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
Native Windows port Plz don't forget poor victims of Microsoft !!!
Re: [HACKERS] 7.4 Wishlist
How about giving OLAP (Dimension / Measure) functionality to PG. Catch all the cricket action. Download Yahoo! Score tracker
Re: [HACKERS] 7.4 Wishlist
On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? This is interesting discussion.. my wish: * error codes. It's very interesting that nobody other wants it... Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
-Original Message- From: Karel Zak [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 11:26 To: Christopher Kings-Lynne Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] 7.4 Wishlist On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? This is interesting discussion.. my wish: * error codes. It's very interesting that nobody other wants it... I do :-) Regards, Dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
my wish: * error codes. It's very interesting that nobody other wants it... I do :-) Me too. It is a must in my opinion.. Regards, Nic. ---(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] 7.4 Wishlist
Mysql is planning on making this work:
SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.
Do we have anything like it (After a discussion with Tom i figure no).
User variables is nice, especially in these kind of queries.
Nice would be to be able to use them as in C (almost everywhere):
SELECT id, @x FROM table_name t where (@x := date_part('days'. now() -
f.created)) 100;
As Tom said in earlier mail, it might not be that big of a win in this
case, but if uses expensive functions, it will.
Magnus
---(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] 7.4 Wishlist
Mysql is planning on making this work: SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id. Do we have anything like it (After a discussion with Tom i figure no). User variables is nice, especially in these kind of queries. Well of course they have to make that work - they don't have subselects :P Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Mysql is planning on making this work: SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id. Do we have anything like it (After a discussion with Tom i figure no). User variables is nice, especially in these kind of queries. Well of course they have to make that work - they don't have subselects :P Chris Yeah, but there is a point about running count(*) one time too many. Say if i would like to get a prettyprinting query like this: SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name; That would be DAMN expensive doing with a subselect: SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM table_name; I know this example suck eggs, but you get the point where it hurts, right? Magnus - sorry for the dupe, chris ---(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] 7.4 Wishlist
Yeah, but there is a point about running count(*) one time too many. Say if i would like to get a prettyprinting query like this: SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name; That would be DAMN expensive doing with a subselect: SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM table_name; I know this example suck eggs, but you get the point where it hurts, right? Are you sure that postgres evaluates that subselect more than once? It looks to me like it returns a constant result for every row and hence it will be evaluated once per statement, not once per row. I'm no expert tho. Can someone answer this? And if the subselect changes for each row (ie. it's a correlated subquery) then you cannot use the variable anyway. It seems to me that if postgres doesn't consider count(*) as a constant then perhaps it should be taught to? Should be safe shouldn't it? I guess if a function in your select statemnt is inserting a row then there's trouble. But if there is, then the sum/count(*) is nonsensical anyway. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Are you sure that postgres evaluates that subselect more than once? It looks to me like it returns a constant result for every row and hence it will be evaluated once per statement, not once per row. I'm no expert tho. Can someone answer this? And if the subselect changes for each row (ie. it's a correlated subquery) then you cannot use the variable anyway. It seems to me that if postgres doesn't consider count(*) as a constant then perhaps it should be taught to? Should be safe shouldn't it? I guess if a function in your select statemnt is inserting a row then there's trouble. But if there is, then the sum/count(*) is nonsensical anyway. Chris It looks like it (7.2.x): # time psql genline -c select id from /dev/null real0m0.694s user0m0.147s sys 0m0.025s # time psql genline -c select id,id||'/'||(select count(*) from ) as x from /dev/null real0m2.202s user0m0.263s sys 0m0.040s # time psql genline -c select id,(select count(*) from bildsekvens) as x from /dev/null real0m1.479s user0m0.254s sys 0m0.047s They were taken from a busy system, but i ran the several times showing about the same result. Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
Stephan Szabo [EMAIL PROTECTED] wrote: If you use a scalar subquery, yes, but I think a subselect in from would help, maybe something like (if you want the total count) select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select count(*) as count from table_name) as t2 group by table_name.id,t2.count; or (if you want each count the counter per group) either select id, sum(sum_col)||'/'||count(*) from table_name group by id; or select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select id, count(*) as count from table_name group by id) as t2 where table_name.id=t2.id group by table_name.id,t2.count; Give it up already, i was MAKING A POINT, not trying to make an optimized count(*) thing :) There are other examples that you cannot get around, that will be evaluated more than once when a local user variable would make it not need to. Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote: Stephan Szabo [EMAIL PROTECTED] wrote: If you use a scalar subquery, yes, but I think a subselect in from would help, maybe something like (if you want the total count) select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select count(*) as count from table_name) as t2 group by table_name.id,t2.count; or (if you want each count the counter per group) either select id, sum(sum_col)||'/'||count(*) from table_name group by id; or select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select id, count(*) as count from table_name group by id) as t2 where table_name.id=t2.id group by table_name.id,t2.count; Give it up already, i was MAKING A POINT, not trying to make an optimized count(*) thing :) There are other examples that you cannot get around, that will be evaluated more than once when a local user variable would make it not need to. For most cases sticking an expression in a subselect in from works to not re-evaluate it (unless it's correlated in which case I don't think local variables help). It might not be as optimal in all cases, but probably is sufficient in most cases. ---(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] 7.4 Wishlist
Joe Conway wrote: David Wheeler wrote: My understanding is that the nul character is legal in a byte sequence, but if it's not properly escaped, it'll be parsed as the end of the statement. Unfortunately, I think that it's a very tough problem to solve. No question wrt '\0' bytes -- they would have to be escaped when casting from bytea to text. The harder issue is that there are apparently many other multiple byte sequences that, while valid in an ASCII encoding, are not valid in one or more multibyte encodings. See this thread: http://archives.postgresql.org/pgsql-hackers/2002-04/msg00236.php This is why currently all non printable characters are escaped (which I think is all bytes 127). Text on the other hand is already known to be valid for a particular encoding, so it doesn't need escaping. I'm not sure what happens when the backend encoding and client encoding don't match -- I'd guess there is some probability of invalid byte sequences in that case too. I think there is some idea of changing the frontend/backend protocol to prevent the need for escaping \127 characters. I believe it is currently only required when the frontend/backend protocol have different encodings. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
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 ... ii) Auditing. Who accessed what, how, when and for how long with the ability to store queries, planner stats, executor stats and what ever else we can tie to a query. Very useful for debugging and security. You can get this from the logs but it is non trivial to perform adhoc reporting and statistical analysis. Why not store it.. in a database? :-) iii) SQL99 error code iv) Updatable and insertable-into views (per SQL99). Perhaps a two stage implementation: i) rules ii) have the planner/executor handle it, instead of the rewriter. The latter will take more coding, and might touch too much of the code, considering the other significant changes planned for 7.4. v) Better PL/PgSQL parser and memory handling vi) A larger number of case studies on the advocacy site, with a larger degree of financial and ROI analysis, all that jazz vii) Collections of information about migrating from other significant platforms: oracle, db2, sybase, interbase, SQL server -- a very popular request on irc.openprojects.net viii) General advocacy, particularly in pushing mainstream IT media coverage, conferences and university usage -- both for teaching SQL and for teach database engineering concepts for senior undergrads. I've no idea how much time I can put into these, but they're on my TODO list. Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
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 ... Hehehe - yeah this would be neat. Would be somewhat better than MySQL's MAX_QUERIES_PER_HOUR setting :P vii) Collections of information about migrating from other significant platforms: oracle, db2, sybase, interbase, SQL server -- a very popular request on irc.openprojects.net There's lots of good information on this on techdocs.postgresql.org. BTW, what happened to the PostgreSQL portal site that someone was working on? It'd be very interested in taking that on... viii) General advocacy, particularly in pushing mainstream IT media coverage, conferences and university usage -- both for teaching SQL and for teach database engineering concepts for senior undergrads. Definitely. How about a resource for college/uni professors on how to use PostgreSQL in their courses? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote: viii) General advocacy, particularly in pushing mainstream IT media coverage, conferences and university usage -- both for teaching SQL and for teach database engineering concepts for senior undergrads. Definitely. How about a resource for college/uni professors on how to use PostgreSQL in their courses? I might get together with some of the lecturers I've worked with in Sydney to give such a document some weight. I must say, the problem is not a technical one though. I've given talks to 3rd and 4th year students about PostgreSQL -- technical, conceptual, political talks... you name it. Out of 200 odd students, only about 5-10 actually seem interested. Its terrible. Why aren't they interested? They think that if they study Oracle (instead) for 6 months they'll walk straight into a job with an extremely high salary. Its a myth, but I cannot shake that perception. In fact, things got very heated when two universities in Sydney moved their SQL courses from Oracle and Sybase to PostgreSQL. Enrollments will be down next year for the courses and Australian universities are heavily geared toward bums on seats not facilitation of education. Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
I might get together with some of the lecturers I've worked with in Sydney to give such a document some weight. I must say, the problem is not a technical one though. I've given talks to 3rd and 4th year students about PostgreSQL -- technical, conceptual, political talks... you name it. Out of 200 odd students, only about 5-10 actually seem interested. Its terrible. I've given a talk in the 2002 honours lecture series at UWA about Postgres and some of the things it can do. All of those guys were interested. Especially since the deptartment does a lot of work in genetic algoriithms. Tell me when you start working on a document - I'm happy to help. Since I'm only just out of Uni, I'd like to write a set of possible assignments and learning outcomes and how you can use postgres to support them. My girlfriend is a PhD student at UWA CS dept :) plus I won the honours scholarship there a year or two back, so I can get interest from the dept, including the databases lecturer. Might help for another point of view and feedback. Why aren't they interested? They think that if they study Oracle (instead) for 6 months they'll walk straight into a job with an extremely high salary. Its a myth, but I cannot shake that perception. That's tragic. Teaching kids to admin oracle is something you do in Tafe, or an Oracle course, not a university. Anyway, what kind of course teaches you about how to admin oracle as opposed to teaching you about ACID properties, MVCC, distributed transactions and partitioning? Most of which can be demonstrated with Postgres. We learnt about relational model, algebra and calculus well before learning about SQL! Hell, my Uni (UWA) actually uses MS Access for crying out loud! We learn heaps of theory for 'real' databases (as above), but then our semester project is to implement in MS Access a bunch of tables and queries for a UN aid mission, for example. Not once do you have to use SQL - you just use the query builder. How lame! I have friends who have worked with people who've gone thru the oracle course. They say it's frustrating because they only understand what they've been told to understand and have a lack of knowledge about basic, database principles. In fact, things got very heated when two universities in Sydney moved their SQL courses from Oracle and Sybase to PostgreSQL. Enrollments will be down next year for the courses and Australian universities are heavily geared toward bums on seats not facilitation of education. Universities are supposed to have a tradition of open source support. Just imagine if the professors could not only teach about how to do SQL, but ALSO teach kids how a parser and executor and rewriter work by looking at the actual source code! Imagine those kids who go on to do honours, masters and PHD's in database theory, indexing or whatever who could end up contributing to Postgres? ;) What a sell! (For a real uni, that is ;) ) Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote: I've given a talk in the 2002 honours lecture series at UWA about Postgres and some of the things it can do. All of those guys were interested. Especially since the deptartment does a lot of work in genetic algoriithms. Excellent. Can you put that talk online somewhere? Tell me when you start working on a document - I'm happy to help. Since I'm only just out of Uni, I'd like to write a set of possible assignments and learning outcomes and how you can use postgres to support them. My girlfriend is a PhD student at UWA CS dept :) plus I won the honours scholarship there a year or two back, so I can get interest from the dept, including the databases lecturer. Might help for another point of view and feedback. Excellent. Are there any other people involved in PostgreSQL and universities or educational institutions? If so we could put something together about experiences for the advocacy Web site. That's tragic. Teaching kids to admin oracle is something you do in Tafe, or an Oracle course, not a university. Anyway, what kind of course teaches you about how to admin oracle as opposed to teaching you about ACID properties, MVCC, distributed transactions and partitioning? Most of which can be demonstrated with Postgres. We learnt about relational model, algebra and calculus well before learning about SQL! Your interest in this is clearly the same as mine: Universities (should) teach concept not product. I'm disgusted that this is not the case. If other people are interested we could work on this in January when I am over your way, as discussed in private email. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
Gavin Sherry wrote: On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote: I've given a talk in the 2002 honours lecture series at UWA about Postgres and some of the things it can do. All of those guys were interested. Especially since the deptartment does a lot of work in genetic algoriithms. Excellent. Can you put that talk online somewhere? Tell me when you start working on a document - I'm happy to help. Since I'm only just out of Uni, I'd like to write a set of possible assignments and learning outcomes and how you can use postgres to support them. My girlfriend is a PhD student at UWA CS dept :) plus I won the honours scholarship there a year or two back, so I can get interest from the dept, including the databases lecturer. Might help for another point of view and feedback. Excellent. Are there any other people involved in PostgreSQL and universities or educational institutions? If so we could put something together about experiences for the advocacy Web site. Is this the kind of thing that the Techdocs Guides area would be good for? (http://techdocs.postgresql.org/guides) :-) Regards and best wishes, Justin Clift snip Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Magnus Naeslund(f) kirjutas T, 03.12.2002 kell 03:18: It looks like it (7.2.x): # time psql genline -c select id from /dev/null real0m0.694s user0m0.147s sys 0m0.025s # time psql genline -c select id,id||'/'||(select count(*) from ) as x from /dev/null real0m2.202s user0m0.263s sys 0m0.040s # time psql genline -c select id,(select count(*) from bildsekvens) as x from /dev/null real0m1.479s user0m0.254s sys 0m0.047s what is the time for select id,x from , (select count(*) as x from bildsekvens) c ; They were taken from a busy system, but i ran the several times showing about the same result. Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Hannu Krosing [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] 7.4 Wishlist
Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote: Now convert this query so that it only evaluates the date_part thing ONCE: That's not a good idea as long as t.stamp varies from row to row. ;) Perhaps once per row, maybe... :) I give up ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
Hannu Krosing [EMAIL PROTECTED] wrote: Magnus Naeslund(f) kirjutas T, 03.12.2002 kell 03:18: It looks like it (7.2.x): # time psql genline -c select id from /dev/null real0m0.694s user0m0.147s sys 0m0.025s # time psql genline -c select id,id||'/'||(select count(*) from ) as x from /dev/null real0m2.202s user0m0.263s sys 0m0.040s # time psql genline -c select id,(select count(*) from bildsekvens) as x from /dev/null real0m1.479s user0m0.254s sys 0m0.047s what is the time for select id,x from , (select count(*) as x from bildsekvens) c ; time psql genline -c select id,x from , (select count(*) as x from ) c ; /dev/null real0m1.354s user0m0.268s sys 0m0.028s The and the other table is the same table, sorry i screwed up in the last cut'n'paste operation. As i said it's a loaded system, the figures vary a little bit between runs. Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Gavin Sherry wrote: I want to see: i) proper resource management a-la Oracle. This would allow a DBA to limited the amount of time any given user spends in the parser, planner or executor. It would be limited with a more sophisticated user system, including things like CREATE USER PROFILE ... Amen: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=3DB99C0A.70900%40mascari.comrnum=1prev=/groups%3Fq%3DCREATE%2BPROFILE%2BMike%2BMascari%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8 To avoid unnecessary cycles being spent on loading the profile after session authorization, we could have a GUC as was suggested to turn the feature on or off. This mirrors Oracle, where you have to set RESOURCE_LIMIT in your init[SID].ora file before PROFILEs are enforced. Some people like sticking everything in postgresql.conf though, including resource limits. I'm not sure how remote administration is supposed to work under such a scenario though... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4 Wishlist
Magnus Naeslund(f) wrote: select id, sum(sum_col)||'/'||count(*) from table_name group by id; or select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select id, count(*) as count from table_name group by id) as t2 where table_name.id=t2.id group by table_name.id,t2.count; Give it up already, i was MAKING A POINT, not trying to make an optimized count(*) thing :) There are other examples that you cannot get around, that will be evaluated more than once when a local user variable would make it not need to. Here's an even slimmer query that makes a var: test= select var1.* from (select 1) as var1; ?column? -- 1 (1 row) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
Magnus Naeslund(f) [EMAIL PROTECTED] writes: Mysql is planning on making this work: SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id. We're supposed to spend our time emulating nonstandard features that don't even exist yet? I think I have better things to do ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
Bruce Momjian [EMAIL PROTECTED] wrote:
Magnus Naeslund(f) wrote:
select id, sum(sum_col)||'/'||count(*) from table_name
group by id;
or
select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
(select id, count(*) as count from table_name group by id) as t2
where table_name.id=t2.id group by table_name.id,t2.count;
Give it up already, i was MAKING A POINT, not trying to make an
optimized count(*) thing :)
There are other examples that you cannot get around, that will be
evaluated more than once when a local user variable would make it
not need to.
Here's an even slimmer query that makes a var:
test= select var1.* from (select 1) as var1;
?column?
--
1
(1 row)
Good!
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
I hope you all are kidding me in not seeing the real issue i'm trying to
show here.
Cheers
Magnus
---(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] 7.4 Wishlist
Good!
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
Yes, it's a bit of a bugger that one.
I hope you all are kidding me in not seeing the real issue i'm trying to
show here.
I see, yes. I guess the MySQL idea is to make it explicit to the parser
which parts of the expression are constant and equivalent.
Still, if the parser was smart enough, it could handle the above example
automatically. However, I think it would be an O(n^2) problem to solve -
matching all subexpressions against all other subexpressions to find the
ones that match...so the MySQL idea is a valid one.
One trick that many people don't know is that you can do it fast in the
GROUP BY clause, just not the WHERE clause:
select t.id, date_part('days',now()-t.stamp) from table_name t group by 2;
Which is something I just discovered recently.
Chris
---(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] 7.4 Wishlist
Magnus Naeslund(f) wrote:
Good!
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
I hope you all are kidding me in not seeing the real issue i'm trying to
show here.
Cheers
Magnus
Does this work?
SELECT t.id, x.date_part
FROM table_name t, (select date_part('days',now()-t.stamp)) as x
WHERE x.date_part 20;
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
Now convert this query so that it only evaluates the date_part thing
ONCE:
That's not a good idea as long as t.stamp varies from row to row. ;)
Perhaps once per row, maybe... :)
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
Potentially I think something like this would do it:
select t.id, t.foo from (select id, date_part('days', now()-stamp)
as foo from table_name except select null, null) as t where foo20;
It's not really an optimization given the required except, but if there
was some way to tell the system not to push clauses down into a subselect
you wouldn't even need that.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
On Tue, 3 Dec 2002, Bruce Momjian wrote:
Magnus Naeslund(f) wrote:
Good!
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) 20;
I hope you all are kidding me in not seeing the real issue i'm trying to
show here.
Does this work?
SELECT t.id, x.date_part
FROM table_name t, (select date_part('days',now()-t.stamp)) as x
WHERE x.date_part 20;
No, because the values in x are correlated to the particular row in
table_name, so I think you have to make it one big subselect in from. In
addition the optimizer is smart enough to push the condition down in
most cases which I think will force the function to be called twice unless
you trigger one of its cases that prevent it from doing so. That's an
optimizer hint I'd like (don't push conditions into this subquery,
really...). :)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On 30 Nov 2002, Neil Conway wrote: On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote: check constraints with subselects. Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? You'd have to either do it in all cases or come up with something that was smart enough to limit the cases to some extent based on the expression. I doubt that it'd perform terribly well, especially at first. I can't see any justification for doing it as insert/update on main table only since by my reading of the spec the constraint is logically checked at the end of each statement (or transaction) even if we would normally not do so in practice when we know the constraint shouldn't be violated. Of course this was in the general set of, if I had months and months and nothing else to do (like work) then I'd want to look at it because I think it'd be useful. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
Matthew T. O'Connor [EMAIL PROTECTED] writes: pg_dump, our upgrade process is painful enough having to do a dump, reload. I think we should be able to guarantee (or at least let much closer to it) that the process works in all cases. I would already be happy if pg_dump backed up my databases correctly, so that I dont have to reorder SQL statements manually in the dump before psql can execute it. -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898 ---(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] 7.4 Wishlist
Stephan Szabo [EMAIL PROTECTED] writes: On 30 Nov 2002, Neil Conway wrote: Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? You'd have to either do it in all cases or come up with something that was smart enough to limit the cases to some extent based on the expression. I doubt that it'd perform terribly well, especially at first. Note that you can get the stupid semantics (run the subselect only when the constrained table changes) today: just hide the subselect in a user-defined function that's called from the constraint expression. Or put the whole check in a trigger instead of using a constraint. I don't think we should bother with direct support of subselects in constraints unless we can come up with an implementation that is significantly better than what you can accomplish with these workarounds. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
On Sun, 1 Dec 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On 30 Nov 2002, Neil Conway wrote: Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? You'd have to either do it in all cases or come up with something that was smart enough to limit the cases to some extent based on the expression. I doubt that it'd perform terribly well, especially at first. Note that you can get the stupid semantics (run the subselect only when the constrained table changes) today: just hide the subselect in a user-defined function that's called from the constraint expression. Or put the whole check in a trigger instead of using a constraint. I don't think we should bother with direct support of subselects in constraints unless we can come up with an implementation that is significantly better than what you can accomplish with these workarounds. Well, the problem is that user defined triggers trying to do the real semantics for update/insert on the other tables of the constraint seem to me like they'll have the same issues as foreign keys do currently, either you'll be forced to write something too strong and deadlock alot, or you'll write something too weak and end up with constraint violations with concurrent transactions unless you basically write a very low level C function to do it for you. I guess this, since in general, the non-action foreign keys really are just check constraints with a subselect effectively. ---(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] 7.4 Wishlist
David Wheeler wrote: My understanding is that the nul character is legal in a byte sequence, but if it's not properly escaped, it'll be parsed as the end of the statement. Unfortunately, I think that it's a very tough problem to solve. No question wrt '\0' bytes -- they would have to be escaped when casting from bytea to text. The harder issue is that there are apparently many other multiple byte sequences that, while valid in an ASCII encoding, are not valid in one or more multibyte encodings. See this thread: http://archives.postgresql.org/pgsql-hackers/2002-04/msg00236.php This is why currently all non printable characters are escaped (which I think is all bytes 127). Text on the other hand is already known to be valid for a particular encoding, so it doesn't need escaping. I'm not sure what happens when the backend encoding and client encoding don't match -- I'd guess there is some probability of invalid byte sequences in that case too. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On Fri, 2002-11-29 at 18:06, Daniele Orlandi wrote: - Better granularity of security and access control, like in mysql. Can you be more specific on exactly what features you'd like to see? - Ability to reset the state of an open backend, including aborting open transaction to allow for better connection pooling and reusing IIRC, it's been suggested that we can implement this by passing back the transaction state as part of the FE/BE protocol -- if we're doing a protocol change for 7.4, this could be part of it. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
On Fri, 2002-11-29 at 13:51, Christopher Kings-Lynne wrote: Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? Here's some of my current TODO list: * FOR EACH STATEMENT triggers (already done) * column lists for UPDATE triggers (will be done soon) * Improve the buffer manager's replacement algorithm (LRU-K, perhaps?) * Implement support for hugetlb pages on linux 2.5 * Finish off PITR (if no one else does ...) * Asynchronous notification improvements (optional message, store notifications in shared memory) * Rowtype assignment in PL/PgSQL Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
What I'd like to have in future versions of PostgreSQL: - replication, replication, ... (you have seen that before). i guess most people would like to see that. - a dblink like system for connecting to remote database systems (not just PostgreSQL???) something like CREATE REMOTE VIEW would be damn good. it would solve many problem when it comes to migration - tablespaces (the directory based stuff which has been discussed on the list) - somebody has mentioned XML before. the XPath stuff would be really cool - PL/Sh should be in contrib. i know that the core team has decided not to put it in the core but contrib would be fine (I keep forgetting the URL of Peters website :( ...) - packages: is there a way to define a set of functions as a package so that they can be removed using just one DROP PACKAGE or so? would be nice for huge projects - urgent: being able to use PL/Perl in combination with SPI (There is a Pg-SPI but it is 0.01 - see http://search.cpan.org/author/APILOS/DBD-PgSPI-0.01/PgSPI.pm). a full and reliable implementation would be fine. - preforking for faster startup - declare MySQL as evil *g*. Thanks a lot :) Hans ---(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] 7.4 Wishlist
Oops, there is something I have forgotten: - nicing backends: this would be nice for administration tasks - CREATE DATABASE ... WITH MAXSIZE (many providers would like to see that; quotas are painful in this case - especially when porting the database to a different or a second server) Hans ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
My list is; Point to Point and Broadcast replication With point to point you specify multiple endpoints, with broadcast you can specify a subnet address and the updates are broadcast over that subnet. The difference being that point to point works well for cross network replication, or where you have a few replicants. I have multiple database servers which could have a deadicated class C network that they are all on, by broadcasting updates you can cutdown the amount of traffic on that net by a factor of n minus 1 (where n is the number of servers involved). Ability to use raw partitions I've not seen an install of PostgreSQL yet that didn't put the database files onto a filesystem, so I'm assuming it's the only way of doing it. By using the filesystem the files are at the mercy of filesystem handler code as to where they end up on the disk, and thus the speed of access will always have some dependancy on the speed of the filesystem. With a raw partition it would be possible to use two devices (e.g. /dev/hde and /dev/hdg on an eight channel ide linux box), and PostgreSQL could then ensure the WALs were located on one the disk with the entries running sequentally, and that the database files were located on the other disk in the most appropriate location (e.g. index data starting near the center of the disk, and user table data starting near the outside). Win32 Port I've explained the reasons before. Apart from that it's always useful to open PostgreSQL up to a larger audience. - Original Message - From: Daniele Orlandi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 29, 2002 11:06 PM Subject: [mail] Re: [HACKERS] 7.4 Wishlist Christopher Kings-Lynne wrote: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Well, jokes apart, I think this is one of the most needed features to me. Currently I'm using strange voodoo to replicate some tables on other machines in order to spread load and resilency. Compared to what I am doing now a good master to slave replication would be heaven. I understand that a good replication is painful but in my experience, if you start by integrating some rude, experimental implementation in the mainstream PostgreSQL the rest will come by itself. For example, RI was something I wouldn't consider production level in 7.2, but was a start, now in 7.3 is much much better, probably complete in the most important parts. Other wishes (not as important as the replication issue) are: - Better granularity of security and access control, like in mysql. - Ability to reset the state of an open backend, including aborting open transaction to allow for better connection pooling and reusing, maybe giving the client the ability to switch between users... Bye! -- Daniele Orlandi Planet Srl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
And that's all ;) Hannu Krosing - and what will you do after January? ;-) Just kidding. I hope you have a big fat bank account if you want to finish all that! -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 12.00-18.00Email: [EMAIL PROTECTED] 2000 FrederiksbergLørdag 12.00-16.00 Web: www.suse.dk ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Hi, although I'm just a novice in this mailing list I'd like to give my contribution to the 7.4 wishlist. I'd like to add to the PostgreSQL code some new low-level, primitive fuctions in order to give native support to FP-based algorithms for rule mining (Frequent Pattern Growth and extensions such as CLOSET and so on). As a matter of fact, this is more than just a wish to me... this is the task I have to accomplish for my thesis (I'm going to degree in Informatics Engineering at the Politecnico di Torino, Italy on next July), and so I can assure you that this will be done (and working) by the end of June. Obviously, any kind of hint and suggestion by you guruz is welcome! :) Bye, alice - Original Message - From: Christopher Kings-Lynne [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 29, 2002 7:51 PM Subject: [HACKERS] 7.4 Wishlist Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? My ones are: * Compliant ADD COLUMN * Integrated full text indexes * pg_dump dependency ordering What would you guys do? Even if it isn't feasible right now... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html __ Scarica il nuovo Yahoo! Messenger: con webcam, nuove faccine e tante altre novità. http://it.yahoo.com/mail_it/foot/?http://it.messenger.yahoo.com/ ---(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] 7.4 Wishlist
On Friday 29 November 2002 06:51 pm, Christopher Kings-Lynne wrote: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? My ones are: * Compliant ADD COLUMN * Integrated full text indexes * pg_dump dependency ordering What would you guys do? Even if it isn't feasible right now... Chris My wishlist : - savepoint - cursor out of a transaction - distributed databases and replication (two phase commit) - only share lock in foreign keys - prepare/execute on backend level - error in a statement break a statement, not complete transaction regards Haris Peco ---(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] 7.4 Wishlist
On Sat, 2002-11-30 at 05:55, Alvaro Herrera wrote: On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote: Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? Well, nobody is paying me, but I want to - fix the btree problem leaking unused pages (I think I'm getting near, I just haven't had free time during the last month). This one is a must to me. - try different regexp algorithms, compare efficiency. Both Henry Spencer's new code for Tcl, and Baeza-Navarro shift-or approach (can be much faster than traditional regex engines) Perhaps bigger effect could be possible if we could could make LIKE/REGEXP use indexes - perhaps some approach based on trigrams could be usable here ? (do people care for allowing search with errors, similar to what agrep and nrgrep do?) Yes, especially if integrated with some full text index scheme. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Hans-Jürgen Schönig wrote: What I'd like to have in future versions of PostgreSQL: - PL/Sh should be in contrib. i know that the core team has decided not to put it in the core but contrib would be fine (I keep forgetting the URL of Peters website :( ...) I like PL/Sh too, but too many people are concerned it isn't transaction-safe and has poor performance. I want it in /contrib, but Peter, the author, doesn't want it in there, so there isn't much we can do. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On Sat, 2002-11-30 at 16:13, Bruce Momjian wrote: Hans-Jürgen Schönig wrote: What I'd like to have in future versions of PostgreSQL: - PL/Sh should be in contrib. i know that the core team has decided not to put it in the core but contrib would be fine (I keep forgetting the URL of Peters website :( ...) You could put the URL in /contrib I like PL/Sh too, but too many people are concerned it isn't transaction-safe and has poor performance. I want it in /contrib, but Peter, the author, doesn't want it in there, so there isn't much we can do. perhaps the URL and a file WARNING.TXT ;) -- Hannu Krosing [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] 7.4 Wishlist
Hannu Krosing wrote: On Sat, 2002-11-30 at 16:13, Bruce Momjian wrote: Hans-J?rgen Sch?nig wrote: What I'd like to have in future versions of PostgreSQL: - PL/Sh should be in contrib. i know that the core team has decided not to put it in the core but contrib would be fine (I keep forgetting the URL of Peters website :( ...) You could put the URL in /contrib The URL used to be main site under: http://www.us.postgresql.org/interfaces.html but I don't see it there anymore. In fact, that page needs updating because some projects have moved. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote: Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? What would you guys do? Even if it isn't feasible right now... Hmm, mine would probably be fixing foreign keys (along with making it work with inheritance and match partial) and check constraints with subselects. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Hi All, here is my wishlist /very short/ : * Oracle syntax support in OUTER JOIN Thanks, Gabor Hans-Jrgen Schnig wrote: What I'd like to have in future versions of PostgreSQL: - PL/Sh should be in contrib. i know that the core team has decided not to put it in the core but contrib would be fine (I keep forgetting the URL of Peters website :( ...) I like PL/Sh too, but too many people are concerned it isn't transaction-safe and has poor performance. I want it in /contrib, but Peter, the author, doesn't want it in there, so there isn't much we can do. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Me and Teodor hope to work on contrib/ltree to add support for sort of
xml. Any ideas are welcome !
Regards,
Oleg
On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote:
Wow Hannu - your list puts mine to shame!
Application server support
* better XML integration
- XML(*) aggregate function returning XML representation of subquery
- XML input/output to/from tables
- XML searchable/indexable in fields)
I've had thoughts about XML too. Since XML is hierachical, imagine being
able to index xml using contrib/ltree or something!
ie. We create a new 'xml' column type.
We create a new indexing scheme for it based on ltree gist.
You index the xml column.
Then you can do sort of XPath queries:
SELECT * FROM requests WHERE xml_xpath('/request/owner/name', datafield) =
'Bob';
And it would be indexed. Imaging being able to pull up all XML documents
that had certain properties, etc.
MS-SQL has a SELECT ... FOR XML clause, but we could always just create
function called xml_select() or something now that we can return recordsets.
Chris
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 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] 7.4 Wishlist
* Compliant ADD COLUMN I've missed the thread (if there was one), how is it non-compliant? Thanks, - Brandon c: 917-697-8665h: 201-798-4983 b. palmer, [EMAIL PROTECTED] pgp:crimelabs.net/bpalmer.pgp5 ---(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] 7.4 Wishlist
On Sat, 2002-11-30 at 15:06, bpalmer wrote: * Compliant ADD COLUMN I've missed the thread (if there was one), how is it non-compliant? ALTER TABLE .. ADD COLUMN colname integer DEFAULT 42 NOT NULL CHECK(colname = 42) REFERENCES tab2 ON DELETE CASCADE; Can't do the above in a single statement. It takes five statements. It's something I'd like to see added as well. -- Rod Taylor [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [HACKERS] 7.4 Wishlist
Can you see this tying in with my recent hack of contrib/ltree to work with a wider range of node names? On Sat, 30 Nov 2002, Oleg Bartunov wrote: Me and Teodor hope to work on contrib/ltree to add support for sort of xml. Any ideas are welcome ! ---(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] 7.4 Wishlist
Christopher Kings-Lynne wrote: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? My ones are: * Compliant ADD COLUMN * Integrated full text indexes * pg_dump dependency ordering What would you guys do? Even if it isn't feasible right now... Well, I might as well join in the fun. Here's my personal TODO for 7.4 (with much wishing that I'll actually have the time to do most if not all of it ;-)) * continue to improve usability of bytea datatype - easier explicit casting between bytea and text * stored procedures (procedures not functions) - no return value, but allow projection of results similar to SHOW ALL (i.e. also similar to MSSQL and Sybase) - CREATE PROCEDURE sp_my_stored_proc() AS '...' LANGUAGE '...'; - CALL sp_my_stored_proc; * array related improvements (note: some of this may exist in contrib as I haven't looked too close yet, but I'm aiming for these to be internal backend functions) - function to return users in a group as rows instead of as an array - generic table function to unspool an array into rows [and columns for 2 dim array] - split -- split string into array on delimiter - implode -- join array elements into a string using given string delimiter - array_contains -- Return TRUE if a value exists in an array - array_search -- Searches the array for a given value and returns the corresponding key if successful * PL/R - new PL interface to R (statistical analysis package based on the S language) * improvements to contrib/tablefunc - enhanced crosstab functionality - possibly enhanced connectby functionality (may not be worth it if RECURSIVE JOIN functionality makes it into 7.4) * improvements to dblink - see details on other recently sent message (ability to connect to non-PostgreSQL databases) * revisit table function scan issues (i.e. tuplestore vs streaming vs portal based) Things not on my list, but that I'm keenly interested in (in no particular order): - native win32 port - two-phase commit - PITR - replication - recursive joins (CONNECT BY PRIOR ... START WITH ...) Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On Saturday, November 30, 2002, at 01:44 PM, Joe Conway wrote: * continue to improve usability of bytea datatype - easier explicit casting between bytea and text This wouldn't happen to include the idea of somehow eliminating the difference between how text strings are delimited and how bytea strings are delimited, would it? Best, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 Wishlist
Cross-db queries. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On Saturday, November 30, 2002, at 04:14 PM, Joe Conway wrote: Not quite sure what you mean by delimiter -- are you referring to double escaping vs single escaping? Oh crap, yes, that's exactly what I meant. s/delimited/escaped/g; Sorry. :-) David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
My ones are: * Compliant ADD COLUMN * Integrated full text indexes * pg_dump dependency ordering What would you guys do? Even if it isn't feasible right now... Actually - I think I might add MODIFY COLUMN to that list. Just look at the list of poor buggers in the interactive docs who can't change their column types. Guess that means I'd need to bring in attlognum's tho. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote: check constraints with subselects. Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 Wishlist
David Wheeler wrote: On Saturday, November 30, 2002, at 04:14 PM, Joe Conway wrote: Not quite sure what you mean by delimiter -- are you referring to double escaping vs single escaping? Oh crap, yes, that's exactly what I meant. s/delimited/escaped/g; That is one thing I'd like to take a look at. I think the problem is that certain byte-sequence/multibyte-encoding combinations are illegal, so it's not as simple an issue as it might first appear. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
Joe Conway [EMAIL PROTECTED] writes: - possibly enhanced connectby functionality (may not be worth it if RECURSIVE JOIN functionality makes it into 7.4) Several of my Red Hat cohorts are pretty interested in making the RECURSIVE query stuff work for 7.4. (The fact that they're ex-DB2 folk might explain their preference for the SQL99 syntax, nee DB2 syntax, over Oracle's CONNECT BY ... but I'm with them ;-(. Oracle's recursive-join syntax is nearly as bad as their outer-join syntax.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 7.4 Wishlist
Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? My ones are: * Compliant ADD COLUMN * Integrated full text indexes * pg_dump dependency ordering What would you guys do? Even if it isn't feasible right now... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
Christopher Kings-Lynne wrote: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Well, jokes apart, I think this is one of the most needed features to me. Currently I'm using strange voodoo to replicate some tables on other machines in order to spread load and resilency. Compared to what I am doing now a good master to slave replication would be heaven. I understand that a good replication is painful but in my experience, if you start by integrating some rude, experimental implementation in the mainstream PostgreSQL the rest will come by itself. For example, RI was something I wouldn't consider production level in 7.2, but was a start, now in 7.3 is much much better, probably complete in the most important parts. Other wishes (not as important as the replication issue) are: - Better granularity of security and access control, like in mysql. - Ability to reset the state of an open backend, including aborting open transaction to allow for better connection pooling and reusing, maybe giving the client the ability to switch between users... Bye! -- Daniele Orlandi Planet Srl ---(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] 7.4 Wishlist
Christopher Kings-Lynne kirjutas R, 29.11.2002 kell 23:51: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? My ones are: * Compliant ADD COLUMN * Integrated full text indexes * pg_dump dependency ordering What would you guys do? Even if it isn't feasible right now... As I don't have a permanent job starting next year (my main employer went bust), I'm planning to do more on postgreSQL anyway (at least until I run out of money ;) I have done some (or sometimes a lot of) brain-twitching on items in the following list, but very little actual useful coding on most. My personal todo list is: Application server support * better XML integration - XML(*) aggregate function returning XML representation of subquery - XML input/output to/from tables - XML searchable/indexable in fields) * Overhaul of OO features (moving closer to SQL99) - type/table inheritance, table inheritance would be done using SQL99's UNDER and would be single inheritance, stored in single logical table, possibly subdivided in physical tables reusing our current huge table 1GB split mechanisms type inheritance would be done using SQL99's LIKE and would be multiple inheritance and would reuse as much as possible the current code for ADD/DROP/RENAME column - check constraints would apply to both type and table inheritance - pk/fk constraints would apply only to table inheritance - types as base of tables, - study feasibility of reference types, - dynamic invocation of table function on queries over hierarchies * WITH (as part of query/view) * WITH RECURSIVE for recursive queries * better NOTIFY (with optional argument, using shared memory instead of tables) General stuff - * making array types btree-indexable in a general way * study feasibility of using SQL99's ARRAY syntax for arrays Data warehousing * bitmap indexes, - using bitmap indexes internally for star joins - real bitmap indexes - clustered multiple bitmap indexes especially clustering on group of bitmap indexes * clustering in general - specifying pages to be filled only to a certain percentage in clustered tables so that updated tuples can be placed near original ones if needed and parallel vacuum can then reclaim the space and keep table clustered with less shuffling. * OLAP features - WINDOW clause, PARTITION BY - GROUPING SETS, ROLLUP, CUBE, () WAL-based master-slave replication -- * if someone is not doing it (which I hope is not true ;) UNICODE / Localization -- * UTEXT, UCHAR, UVARCHAR types using IBM's ICU, stored in UTF-16 or SCSU * fast LIKE, ILIKE, REGEX code for UTF-16, possibly lifted from python2 * field-level localization, again using ICU FE/BE protocol -- all can be worked on independently * try to find a better wire protocol from existing ones (X-window system seems simple enough, perhaps DB2's DRDA) or fix the existing one for high performance (mainly make sure that as big chunks as possible have preceeding length), make it easy to send out-of-band/optional data (Notifications, info on actual query performance (so one can visualize it for user), ...) * standardize a fire-level binary protocol for field types (currently whatever is stored is sent) * work on making python use this protocol and port some postgres datatypes (initially timestamp/date/time and varbit)to python Really Dark Arts -- * making backend internals available to a scripting language (for me it means python ;) for making more parts (especially planner/optimizer) more easily hackable * using the stuff from previous point ;) And that's all ;) Hannu Krosing ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
