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] Postgres 7.3 announcement on postgresql.org
On Fri, 2002-11-29 at 23:32, Justin Clift wrote: Vince Vielhaber wrote: snip Yes it would. But while on the subject, why did you only mention it's availability being on the advocacy site? *We* mentioned it's availability being on the Advocacy site, because it gives people a single place to go that has both PostgreSQL itself *and* a site that's dedicated to giving a clear list of features, advantages, case studies, etc. But why duplicate the download PostgreSQL page on advocacy? ISTM a link to the appropriate page on the main website would be fine -- and if the download PostgreSQL stuff on the main website isn't perfect, then we should improve it (and fix the underlying problem), rather than duplicating content on advocacy.postgresql.org Are the ftp and website mirrors now irrelevant to you? Not sure what you mean here. He probably means that on http://advocacy.postgresql.org/download/, you only have direct links to [www|ftp].postgresql.org, not any of the mirror sites. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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] [GENERAL] One SQL to access two databases.
On Friday 29 November 2002 17:14, Joe Conway wrote: [EMAIL PROTECTED] wrote: Does anybody know if postgres support a SQL statement that handles two diferent databases (in the same server)? Have a look at the contrib/dblink directory in the standard postgresql distro. Didnt find such a directory (or a similar one) in my 7.1.3-distri and this would be very helpful to save db-handles in persistent programs. dblink was first released with PostgreSQL 7.2. But as of yesterday, a new and improved PostgreSQL 7.3 was released with a much improved dblink. If you can, upgrade to 7.3. Joe Hi Joe ! Now when the 7.3 release is out,can we get back to plpq ? I did send You sources before vacation, and You said that You will take a look. I hope I am not disturbing You. If You think that this is bad Idea, I give up hope that we merge this functions into dblink, an I will do it manually for my projects as I did before(I must say that this is a frustration for me because I must tweak the code with every new release of postgres). I am not using new plpq functions jet, so even if You do not want to merge, maybe You can give me some comments(as I said before, I do not understand memory management and memory contests to well) ? Thank You in advance. Regards ! ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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] Planning for improved versions of IN/NOT IN
Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: I curious if any of the rewriting of EXISTS and NOT EXISTS would address the problem described by Date: That should read I'm curious... http://www.firstsql.com/iexist.htm We are not here to redefine the SQL spec ... and especially not to eliminate its concept of NULL, which is what Date would really like ;-) From what I've read of Date's so far, I think he'd like to junk SQL altogether. The above-quoted screed is based on a claimed logical equivalence between NOT EXISTS() and NOT IN() that is just plain wrong when you consider the possibility of NULLs. Rather than FirstSQL correctly processes this query, you should read FirstSQL deliberately violates the SQL spec. (There may be grounds to argue that the spec behavior could be improved, but that's an argument to be making to the standards committee, not here.) Okay. I knew there was talk in the past that IN be rewritten as EXISTS, which is not what you propose doing, but would have exposed the odd behavior NOT EXISTS exhibits according to the SQL spec. I was also curious to know which path PostgreSQL development prefers to take when the SQL spec and the Relational Model part ways, as they often do. Maybe someday RedHat will have a voting member on the ANSI X3H2/NCITS committee. ;-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Locale-dependent case conversion in {identifier}
On Sat, 2002-11-30 at 01:40, Nicolai Tufar wrote: And I happen to have bad luck to use PostgreSQL with Turkish locale. And, as you may know our I is not your I: pgsql=# create table a(x char(1)); CREATE TABLE pgsql=# grant SELECT ON a to PUBLIC; ERROR: user public does not exist pgsql=# Oracle, the second best database I have does seem to convert relation names in locale-dependent fassion: SQL alter session set NLS_LANGUAGE='TURKISH'; Session altered. SQL create table a(x char(1)); Table created. SQL grant select on a to PUBLIC; Grant succeeded. could it just be that we store identifiers in lower case, whereas most others (including SQL spec IIRC)have them in upper case ? Could you try the grant in both databases also in lower case ? i.e.: grant select on a to public; -- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] about ODBC
Someone know any ODBC driver without problem with UPPER CASE ? -- Saludos Horacio Miranda. [EMAIL PROTECTED] PostgreSQL. Because life's too short to learn Oracle.:) Billy O'Connor IBM -- Immer Backup Machen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Locale-dependent case conversion in {identifier}
On Sat, 2002-11-30 at 07:57, Nicolai Tufar wrote: With this, no matter what kind of I you used in names, it is always going to end up a valid ASCII character. Would it be acceptable if I submit a path that applies this special logic in src/backend/parser/scan.l if the locale is tr_TR? Because for many folks setting locale to Turkish would render their database unusable. For, god forbid, if your sql has a column name written in capitlas including I. It is not working. So I deeply believe that PostgreSQL community have to provide a workaround for this problem. So what should I do? In SQL in general and in postgreSQL in particular, you can always use quoted names and thus escape the stupidities of case conversion: grant SELECT ON a to public; should work everywhere (except Oracle and other DB's where it should be grant SELECT ON A to PUBLIC; ) I can't help you on Win32/VMS filenames ;) --- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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] eWeek Article
On Fri, 2002-11-29 at 20:21, Christopher Kings-Lynne wrote: Looks like the eWeek article has been published: http://www.eweek.com/article2/0,3959,732789,00.asp Sorry for sounding like such a dork :) A very good article, imho, for psql: The one thing people will remember about mysql: it costs 395$ :-) cheers -- vbi -- this email is protected by a digital signature: http://fortytwo.ch/gpg NOTE: keyserver bugs! get my key here: https://fortytwo.ch/gpg/92082481 signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Locale-dependent case conversion in {identifier}
Nicolai Tufar [EMAIL PROTECTED] writes: Historically programs that operate in Turkish locale have chosen to hardcode the capitalisation of i in system messages and identifier names like this: Lower: I - i and Y' - i. Upper: y' - I and i - I. If that's the behavior you want, why don't you set up a variant locale definition that does it that way? That would fix *all* your locale- dependent programs, not just Postgres ... Would it be acceptable if I submit a path that applies this special logic in src/backend/parser/scan.l if the locale is tr_TR? It really seems like an inappropriate wart to me :-( Because for many folks setting locale to Turkish would render their database unusable. For, god forbid, if your sql has a column name written in capitlas including I. It is not working. I am not seeing why this is any worse than the universal problems of using upper-case letters without double-quoting 'em. If you consistently spell the name the same way, you will not have a problem; if you don't, you might have a problem, but why is it worse than anyone else's? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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] Postgres 7.3 announcement on postgresql.org
Neil Conway wrote: On Fri, 2002-11-29 at 23:32, Justin Clift wrote: Vince Vielhaber wrote: snip Yes it would. But while on the subject, why did you only mention it's availability being on the advocacy site? *We* mentioned it's availability being on the Advocacy site, because it gives people a single place to go that has both PostgreSQL itself *and* a site that's dedicated to giving a clear list of features, advantages, case studies, etc. But why duplicate the download PostgreSQL page on advocacy? ISTM a link to the appropriate page on the main website would be fine -- and if the download PostgreSQL stuff on the main website isn't perfect, then we should improve it (and fix the underlying problem), rather than duplicating content on advocacy.postgresql.org Why does our master web site still have 7.2.3 listed as the most recent release? http://www.ca.postgresql.org/sitess.html In fact, the link mentioned on the main web page points to the mirror page, not to any place to download it. If I choose FTP mirrors, that works. -- 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
[HACKERS] Updated biography page
Here is an updated version of our developers biography page. I am interested in locations for: Christopher Kings-Lynne Barry Lind Dave Page Rod Taylor The format is usually large city, state/province, country. Also, I would like Company, Company URL for anyone interested in adding that to their entry in the list. I will send out individualized emails about that as well. Of course, corrections/improvements are encouraged. -- 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 ?php include(top.php); ? trtdnbsp;/td td colspan=3 bgcolor=white height=500 align=left valign=topbr centerimg src=images/pgdev.png border=0/center brbrcentertable border=0 width=600trtd br These are the fine people that make PostgreSQL what it is today!P table border=0 width=100%trtd align=left DIV CLASS=main H2 Steering /H2 ul liMarc G. Fournier in Wolfville, Nova Scotia, Canada, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a, A href=http://www.pgsql.com;PostgreSQL, Inc./A) coordinates the entire effort, provides the server, and administers the primary Web site, mailing lists, ftp site, and source code repository. liTom Lane in Pittsburgh, Pennsylvania, USA, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a, A href=http://www.redhat.com;Red Hat/A) is involved in all aspects of PostgreSQL, including bug evaluation and fixes, performance improvements, and major new features, like schemas. He is also responsible for the optimizer. liVadim B. Mikheev in San Fransisco, California, USA, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a, A href=http://sb.sectorbase.com/sectorbase2/sbforms/sbforms/www/home/_home; Sector Data/a) does large projects, like vacuum, subselects, triggers, Write Ahead Log (WAL) and multi-version concurrency control (MVCC). liBruce Momjian in Philadelphia, Pennsylvania, USA, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a, A href=http://osb.sra.co.jp/PostgreSQL/index-en.php; Software Research Associates/A) maintains FAQ and TODO lists, codes, applies patches, and does training. liJan Wieck in Boston, Massachusetts, USA, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/A , A href=http://www.peerdirect.com/;PeerDirect/a) overhauled the query rewrite rule system, wrote our procedural languages PL/pgSQL and PL/Tcl, and added the NUMERIC data type. /ul H2 Major Developers /H2 ul liOleg Bartunov in Moscow, Russia, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a) introduced the locale support and is improving GIST. liD#39Arcy J. M. Cain in Toronto, Ontario, Canada, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a) worked on the TCL interface, PyGreSQL, and the INET data type. liJustin Clift in Melbourne, Australia, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a) created and maintains the Technical Documentation and Advocacy web sites. liJoe Conway in San Diego, California, USA, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a ) has added functions returning table sets, /contrib/dblink, and other nifty features. liNeil Conway in Toronto, Ontario, Canada, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a) has completed many important TODO items. liDave Cramer in Toronto, Ontario, Canada, (a href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]/a) maintains the jdbc driver with Barry Lind. liPeter Eisentraut in Dresden, Germany, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a) is heavily involved in improving the build system and documentation, as well as many port-specific and privilege improvements. He also overhauled ipsql/i. liOliver Elphick in Newport, Isle of Wight, United Kingdom, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a) maintains the PostgreSQL package for Debian Linux. liHiroshi Inoue in Fukui, Japan, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a) improved btree index access and maintains the ODBC driver. liTatsuo Ishii in Zushi, Kanagawa, Japan, (a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a, A href=http://osb.sra.co.jp/PostgreSQL/index-en.php; Software Research Associates/A) handles multi-byte foreign language support and porting issues. liChristopher Kings-Lynne in ??, (a href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]/a) has made major improvements to the SMALLALTER TABLE/SMALL command. liBarry Lind in ??, (a
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] about ODBC
http://odbc.postgresql.org/ Regards, Dave. -Original Message- From: Horacio Miranda [mailto:[EMAIL PROTECTED]] Sent: 30 November 2002 14:54 To: [EMAIL PROTECTED] Subject: [HACKERS] about ODBC Someone know any ODBC driver without problem with UPPER CASE ? -- Saludos Horacio Miranda. [EMAIL PROTECTED] PostgreSQL. Because life's too short to learn Oracle.:) Billy O'Connor IBM -- Immer Backup Machen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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 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] Boolean casting in 7.3 - changed?
On Wednesday, November 27, 2002, at 04:34 PM, David Walker wrote:
Does this mean that in the future '342' may not be valid as an insert
into a
numeric field and that we should be using 342 instead?
I didn't see an answer to this question, but I sincerely hope that the
answer is
no. Otherwise, dynamic interfaces are going to have a much harder
time.
Take DBI (and DBD::Pg), for example. Most DBI users don't specify a
data type when using placeholders. Therefore, DBD::Pg (and other DBDs,
including DBD::Oracle) assume that the data types are strings. So it's
not unusual for DBD::Pg to execute a query like this:
INSERT INTO foo (numtype, varchartype, datetime, inttype)
VALUES ('23.4', 'string', '2002-11-30 00:00:00', '12');
In order to allow the flexibility to remain, AFAICT PostgreSQL has to
continue to allow strings to be converted to numbers on the back end.
Regards,
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
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] Boolean casting in 7.3 - changed?
-Original Message- From: David Wheeler [mailto:[EMAIL PROTECTED]] Sent: 30 November 2002 20:18 To: David Walker Cc: PostgreSQL Development Subject: Re: [HACKERS] Boolean casting in 7.3 - changed? On Wednesday, November 27, 2002, at 04:34 PM, David Walker wrote: Does this mean that in the future '342' may not be valid as an insert into a numeric field and that we should be using 342 instead? I didn't see an answer to this question, but I sincerely hope that the answer is no. Otherwise, dynamic interfaces are going to have a much harder time. pgAdmin will have similar problems. I can work round it for standard types, but how will I tell whether a custom type will reject quoted values? Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Boolean casting in 7.3 - changed?
I didn't see an answer to this question, but I sincerely hope that the
answer is
no. Otherwise, dynamic interfaces are going to have a much harder
time.
Take DBI (and DBD::Pg), for example. Most DBI users don't specify a
data type when using placeholders. Therefore, DBD::Pg (and other DBDs,
including DBD::Oracle) assume that the data types are strings. So it's
not unusual for DBD::Pg to execute a query like this:
INSERT INTO foo (numtype, varchartype, datetime, inttype)
VALUES ('23.4', 'string', '2002-11-30 00:00:00', '12');
In order to allow the flexibility to remain, AFAICT PostgreSQL has to
continue to allow strings to be converted to numbers on the back end.
I have to agree with david on this one. It's essential that quoted numbers
be allowed into number fields. I have no problem with putting numbers in
boolean fields though.
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Postgres 7.3 announcement on postgresql.org
On Sat, 30 Nov 2002, Bruce Momjian wrote: Neil Conway wrote: On Fri, 2002-11-29 at 23:32, Justin Clift wrote: Vince Vielhaber wrote: snip Yes it would. But while on the subject, why did you only mention it's availability being on the advocacy site? *We* mentioned it's availability being on the Advocacy site, because it gives people a single place to go that has both PostgreSQL itself *and* a site that's dedicated to giving a clear list of features, advantages, case studies, etc. But why duplicate the download PostgreSQL page on advocacy? ISTM a link to the appropriate page on the main website would be fine -- and if the download PostgreSQL stuff on the main website isn't perfect, then we should improve it (and fix the underlying problem), rather than duplicating content on advocacy.postgresql.org Why does our master web site still have 7.2.3 listed as the most recent release? http://www.ca.postgresql.org/sitess.html In fact, the link mentioned on the main web page points to the mirror page, not to any place to download it. If I choose FTP mirrors, that works. Rather than bitch about it, why don't you just ask? 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 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] [GENERAL] One SQL to access two databases.
Darko Prenosil wrote: Now when the 7.3 release is out,can we get back to plpq ? I did send You sources before vacation, and You said that You will take a look. I hope I am not disturbing You. If You think that this is bad Idea, I give up hope that we merge this functions into dblink, an I will do it manually for my projects as I did before(I must say that this is a frustration for me because I must tweak the code with every new release of postgres). I am not using new plpq functions jet, so even if You do not want to merge, maybe You can give me some comments(as I said before, I do not understand memory management and memory contests to well) ? Thank You in advance. I'm still interested in merging the plpq functions into dblink. As I said before, particularly now that plpgsql can returns sets, I think these functions are very useful. There are several other changes I'd like to make to dblink at the same time. I've recently been getting at least one email a week, off-list, from someone interested in using dblink against *other* RDBMSs (e.g. Oracle, Sybase, etc). Here's what I'm thinking about doing (in very loose terms -- comments, pointers, etc very much welcome): - split dblink into a set of front-end user accessible functions (e.g. dblink, dblink_exec, etc) and a loadable library of libpq based functions (a connection library) that implement the front-end ones. The plpq functions would be part of the libpq connection library, with more generic front-end user functions. - use the libpq connection library as the model api for other types of connection libraries (JDBC, ODBC, oracle, freetds sybase, mssql, mysql, etc). - create an in-memory hash table of loaded connection libraries, and perhaps a table for registering the library paths, etc. - create an in memory hash table of persistent connections, and perhaps a table to register connections for reuse. As I said, this is all very preliminary; comments, suggestions, requests are all welcome. I'm not quite sure how to do the loadable library part, but I envision it being similar to how PLs are loaded when needed, and used when already loaded. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Locale-dependent case conversion in {identifier}
- Original Message -
From: Hannu Krosing [EMAIL PROTECTED]
To: Nicolai Tufar [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Saturday, November 30, 2002 5:41 PM
Subject: Re: [HACKERS] Locale-dependent case conversion in {identifier}
[ ... ]
could it just be that we store identifiers in lower case, whereas most
others
(including SQL spec IIRC)have them in upper case ?
That seem to be the case. All the databases I used, automaticaly convert
identifiers to upper case.
And they all do it using ASCII-only conversion.
Could you try the grant in both databases also in lower case ?
i.e.:
grant select on a to public;
The statement works in both databases. But the problem is that it was
pg_dumpall who created SQL statements with PUBLIC. Why
pg_dumpall does not enclose identifiers in quotes, like:
REVOKE ALL ON TABLE tamara2 FROM public;
insted of
REVOKE ALL ON TABLE tamara2 FROM PUBLIC;
as it does now.
I will make an attempt to modify pg_dump accordingly, and will send a
patch to the list.
Regards,
Nic
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
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
[HACKERS] Wishlist for 7.4: Plan stability
Ok, someone else posted their data warehousing wishlist, I want to post my single item OLP wishlist: Plan stability. This is the natural conclusion of having prepared queries. This is one area where it would be possible to totally beat Oracle's implementation by a huge margin. And it's something that's critical to web sites and other applications that handle many short queries and need to reliably provide rapid response. What I want to do would actually go way beyond what Oracle provides. I have a clear Idea what I would want to make the database truly manageable under heavy OLP load. I would want a queries to be a first class object in the postgres world. There could be a system table that lists all the prepared queries the database knows about. Every query's current plan and performance statistics could be linked from there. This would allow a sysadmin to at least have a clue what queries are running and how without having to open up the application source. Then I would want an acl system to restrict under what circumstances postgres allows new queries to be added, new plans to be analyzed, and old queries to be removed. While in a development server or a DSS server you want any user to be able to create new queries and you want the database to optimize every query using the most up to date information, in an OLP server that's not what you want. It's more important to be consistently fast than it is to be as fast as possible. Ideally I would expect every query to be manually loaded when a new version of the application is loaded. A DBA could check at that point every plan and ensure that they're all reasonable. Then the user that the web server connects as would be prohibited from running any new queries or generating any new plans for existing queries. The DBA could go to sleep at night confident that the database isn't suddenly going to hit some formerly unreached section of code or decide to optimize a query differently and suddenly come to a crashing halt. It would also mean that the query written by the new junior programmer you just hired can't slip by QA and thrash the query cache of your database by inserting constants directly into the query. And finally, it would also mean that the query written by your other junior programmer that inserts unchecked data can't become a gaping security hazard because when the hacker submits subqueries in the form the resulting query is rejected. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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
[HACKERS] Newbie hacker looking to get started
I was thinking of hacking on postgres a bit. I want to start with filling out the operations list for data types that seem to be missing obvious operators. In particular I'm thinking of things like . an aggregate function for cidr that would find the smallest enclosing netblock. . an aggregate function for box, point, etc for the bounding box I had some other ideas but I don't remember them now. Anyways, I'm a bit stumped where to start. Looking at the existing operations there seems to be a bit of magic using macros involved in creating accumulators and result sets that I don't get. Is there a walk-through of a typical datatype and how to code an operator somewhere? -- greg ---(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] Bad query optimisation
This is weird, it seems like min and max aren't being optimised symmetrically. It seems like both of these should result in similar plans and run equally fast. Instead the first is actually really slow and the second is perfectly quick. foo=# explain select max(postalcode) from postalcodes where postalcode 'K0C1N2'; Aggregate (cost=123.59..123.59 rows=1 width=10) - Index Scan using postalcodes_pkey on postalcodes (cost=0.00..120.50 rows=1234 width=10) foo=# explain select min(postalcode) from postalcodes where postalcode 'K0C1N2'; Aggregate (cost=10373.45..10373.45 rows=1 width=10) - Seq Scan on postalcodes (cost=0.00..9697.11 rows=270535 width=10) -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bad query optimisation
This is weird, it seems like min and max aren't being optimised symmetrically. It seems like both of these should result in similar plans and run equally fast. Instead the first is actually really slow and the second is perfectly quick. Without knowing anything about your data, if Postgres knows (from its stats tables) that 90% of the values in your column are above 'K0C1N2' then it will of course do a seq scan for the second query. If that is incorrect, then have your gone 'ANALYZE postalcodes' recently? Cheers, Chris foo=# explain select max(postalcode) from postalcodes where postalcode 'K0C1N2'; Aggregate (cost=123.59..123.59 rows=1 width=10) - Index Scan using postalcodes_pkey on postalcodes (cost=0.00..120.50 rows=1234 width=10) foo=# explain select min(postalcode) from postalcodes where postalcode 'K0C1N2'; Aggregate (cost=10373.45..10373.45 rows=1 width=10) - Seq Scan on postalcodes (cost=0.00..9697.11 rows=270535 width=10) -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bad query optimisation
Christopher Kings-Lynne [EMAIL PROTECTED] writes: This is weird, it seems like min and max aren't being optimised symmetrically. It seems like both of these should result in similar plans and run equally fast. Instead the first is actually really slow and the second is perfectly quick. Without knowing anything about your data, if Postgres knows (from its stats tables) that 90% of the values in your column are above 'K0C1N2' then it will of course do a seq scan for the second query. Oops, you're right that was a bad diagnosis. When I use the midpoint of the data set they both get optimized into the same plan. However I still think there's something wrong. It looks like postgres doesn't know that it's possible to calculate min and max without scanning every record. When I run the queries below there's a big variance between the 2.3s required to find the minimum for the whole dataset, and the .098s required to find the maximum of the small subset below K0C1N2. Because there's an index on postalcode it should be possible to find the minimum of any range in a single index lookup. It seems like this should be an important optimization given the number of applications that request max(foo) in a broken attempt to implement sequences. Occasionally it's not even a broken attempt too. Incidentally, this is Postgres 7.2. Is this improved in 7.3? bash-2.05b$ time psql -d salesoutlook -c select min(postalcode) from postalcodes min K0A1A0 (1 row) real0m2.334s user0m0.040s sys 0m0.010s bash-2.05b$ time psql -d salesoutlook -c select max(postalcode) from postalcodes where postalcode 'K0C1N2' max K0C1N0 (1 row) real0m0.098s user0m0.030s sys 0m0.020s bash-2.05b$ time psql -d salesoutlook -c select max(postalcode) from postalcodes where postalcode 'L9J1J2' max L9J1J1 (1 row) real0m2.128s user0m0.030s sys 0m0.010s -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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
[HACKERS] tsearch thoughts
Is there any reason why the tseach indexes couldn't be modified to just work on TEXT fields and not TXTIDX fields. Is there really a reason to have the TXTIDX type? I mean, when the index is created over the text column, instead of just indexing the text as-is, index the txt2txtidx'd version...? That would vastly reduce the complexity of tsearch, and would make the indexed text invisible, as it is in most other fti implementations...? I tried to simulate this myself, although ideally it would be invisible to the user: test=# create table test (a text); CREATE test=# CREATE INDEX my_idx ON test USING gist(txt2txtidx(a)); ERROR: DefineIndex: index function must be marked iscachable So the index isn't iscachable - why's that? Say it was marked iscachable, then I'd be able to query like this: SELECT * FROM test WHERE txt2txtidx(test) ## 'apple'; This would mean that the index on-disk file would be large, but the table file would stay small. It would also vastly reduce the size of pg_dumps... Could we move towards something like: CREATE FULLTEXT INDEX my_idx ON test (a); Or something? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Odd optimiser behaviour
I'm seeing this: I have indexed the suspended_off column. usa=# explain analyze UPDATE users_users SET suspended=false, suspended_on=NULL, suspended_off=NULL WHERE suspended_off='2002-02-02'; NOTICE: QUERY PLAN: Index Scan using users_users_susp_off_idx on users_users (cost=0.00..1005.91 rows=266 width=248) (actual time=0.36..0.36 rows=0 loops=1) Total runtime: 0.60 msec EXPLAIN usa=# explain analyze UPDATE users_users SET suspended=false, suspended_on=NULL, suspended_off=NULL WHERE suspended_off=CURRENT_DATE; NOTICE: QUERY PLAN: Seq Scan on users_users (cost=0.00..2922.14 rows=266 width=248) (actual time=237.38..237.38 rows=0 loops=1) Total runtime: 237.60 msec EXPLAIN Why does using CURRENT_DATE instead of a literal date make a difference? So then I tried using a partial index, since 99% of the rows will have NULL values in suspended_off: usa=# create index users_users_susp_off_idx on users_users(suspended_off) where suspended_off is not null; CREATE usa=# explain analyze UPDATE users_users SET suspended=false, suspended_on=NULL, suspended_off=NULL WHERE suspended_off='2002-02-02'; NOTICE: QUERY PLAN: Seq Scan on users_users (cost=0.00..2793.55 rows=267 width=248) (actual time=301.51..301.51 rows=0 loops=1) Total runtime: 301.90 msec EXPLAIN usa=# explain analyze UPDATE users_users SET suspended=false, suspended_on=NULL, suspended_off=NULL WHERE suspended_off=CURRENT_DATE; NOTICE: QUERY PLAN: Seq Scan on users_users (cost=0.00..2927.26 rows=267 width=248) (actual time=466.76..466.76 rows=0 loops=1) Total runtime: 467.02 msec EXPLAIN And now I'm always getting sequential scans. What gives? I analyze the table between runs. 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] Odd optimiser behaviour
Christopher Kings-Lynne wrote: EXPLAIN usa=# explain analyze UPDATE users_users SET suspended=false, suspended_on=NULL, suspended_off=NULL WHERE suspended_off=CURRENT_DATE; NOTICE: QUERY PLAN: Seq Scan on users_users (cost=0.00..2927.26 rows=267 width=248) (actual time=466.76..466.76 rows=0 loops=1) Total runtime: 467.02 msec EXPLAIN And now I'm always getting sequential scans. What gives? I analyze the table between runs. In gram.y I see that CURRENT_DATE is transformed to 'now'::text::date. Here's the comment: * We cannot use 'now'::date because coerce_type() will * immediately reduce that to a constant representing * today's date. We need to delay the conversion until * runtime, else the wrong things will happen when * CURRENT_DATE is used in a column default value or rule. So I'm guessing that the optimizer sees this as volatile and therefore not something it can use an index for. Try using now()::date instead, or maybe wrap the call to CURRENT_DATE in a function of your own and mark it stable. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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]
[HACKERS] Hard-coded PUBLIC in pg_dump
src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.
It completly breaks dumps when run with Turksh locale setting. In my
opinion making it lower-case would do much good and no harm. A mini
patch is given below.
On the other hand, I was thinking about wrapping all the identifiers in
dump files in single quotes. It is done in SET SESSION AUTHORIZATION
clause. Is there a reason for not doing this with table and colum names?
Regards,
Nic
*** ./src/bin/pg_dump/pg_dump.c.origSun Dec 1 03:23:56 2002
--- ./src/bin/pg_dump/pg_dump.c Sun Dec 1 03:24:48 2002
***
*** 4918,4924
* wire-in knowledge about the default public privileges for different
* kinds of objects.
*/
! appendPQExpBuffer(sql, REVOKE ALL ON %s %s FROM PUBLIC;\n,
type, name);
/* Make a working copy of acls so we can use strtok */
--- 4918,4924
* wire-in knowledge about the default public privileges for different
* kinds of objects.
*/
! appendPQExpBuffer(sql, REVOKE ALL ON %s %s FROM public;\n,
type, name);
/* Make a working copy of acls so we can use strtok */
***
*** 4980,4986
if (eqpos == tok)
{
/* Empty left-hand side means PUBLIC */
! appendPQExpBuffer(sql, PUBLIC;\n);
}
else if (strncmp(tok, group , strlen(group )) == 0)
appendPQExpBuffer(sql, GROUP %s;\n,
--- 4980,4986
if (eqpos == tok)
{
/* Empty left-hand side means PUBLIC */
! appendPQExpBuffer(sql, public;\n);
}
else if (strncmp(tok, group , strlen(group )) == 0)
appendPQExpBuffer(sql, GROUP %s;\n,
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name. It completly breaks dumps when run with Turksh locale setting. In my opinion making it lower-case would do much good and no harm. A mini patch is given below. H...does putting double quotes (eg. PUBLIC) around the public word fix it? On the other hand, I was thinking about wrapping all the identifiers in dump files in single quotes. It is done in SET SESSION AUTHORIZATION clause. Is there a reason for not doing this with table and colum names? You can't put single quotes around table and column names. You need to use double quotes as they are identifiers rather than literals. Bear in mind that some improvements have been made in Postgres 7.3 with regards to quoting, so have you checked 7.3? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] eWeek Article
We made SlashDot: http://developers.slashdot.org/developers/02/11/30/1815200.shtml?tid=99 Greg - could you forward to advocacy? 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] Wishlist for 7.4: Plan stability
Is someone keeping a hopeful todo list? - Brandon c: 917-697-8665h: 201-798-4983 b. palmer, [EMAIL PROTECTED] pgp:crimelabs.net/bpalmer.pgp5 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
- Original Message -
From: Christopher Kings-Lynne [EMAIL PROTECTED]
To: Nicolai Tufar [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, December 01, 2002 4:05 AM
Subject: Re: [HACKERS] Hard-coded PUBLIC in pg_dump
H...does putting double quotes (eg. PUBLIC) around the public word
fix
it?
No:
apb= GRANT SELECT ON TABLE maras2.esya TO PUBLIC;
ERROR: user PUBLIC does not exist
apb= GRANT SELECT ON TABLE maras2.esya TO 'PUBLIC';
ERROR: parser: parse error at or near 'PUBLIC' at character 38
apb= GRANT SELECT ON TABLE maras2.esya TO public;
GRANT
apb=
The problem here is case conversion from capital I to lower-case i.
In Turkish locale tolower('I') is not equal to 'i'. So, since public role
is lower-case internally, why would we not make it lower-case in dump file.
You can't put single quotes around table and column names. You need to
use
double quotes as they are identifiers rather than literals.
Bear in mind that some improvements have been made in Postgres 7.3 with
regards to quoting, so have you checked 7.3?
I stand corrected. It is indeed has to be double-quoted.
7.3 is quoting only SET SESSION AUTHORIZATION 'role' clause in my dump.
Possibly,
because it has been added recently. Old code does not quote anything.
Chris
Regards,
Nic.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Wishlist for 7.4: Plan stability
On Sat, 2002-11-30 at 21:22, bpalmer wrote: Is someone keeping a hopeful todo list? Nearly every one of the items brought up could / should be on the standard todo list. http://developer.postgresql.org/todo.php -- Rod Taylor [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
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] Wishlist for 7.4: Plan stability
Rod Taylor wrote: -- Start of PGP signed section. On Sat, 2002-11-30 at 21:22, bpalmer wrote: Is someone keeping a hopeful todo list? Nearly every one of the items brought up could / should be on the standard todo list. http://developer.postgresql.org/todo.php Does anyone have additions for the list. I didn't see anything discussed that jumped out at me, though I didn't see Conquer the world on there. ;-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Wishlist for 7.4: Plan stability
On Sat, 2002-11-30 at 23:02, Bruce Momjian wrote: Rod Taylor wrote: -- Start of PGP signed section. On Sat, 2002-11-30 at 21:22, bpalmer wrote: Is someone keeping a hopeful todo list? Nearly every one of the items brought up could / should be on the standard todo list. http://developer.postgresql.org/todo.php Does anyone have additions for the list. I didn't see anything discussed that jumped out at me, though I didn't see Conquer the world on there. ;-) I didn't see anything that wasn't on either the TODO list or in the 'Unsupported Features' page in the docs aside from the ltree/XML items and MODIFY COLUMN support. -- Rod Taylor [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Wishlist for 7.4: Plan stability
Greg Stark [EMAIL PROTECTED] writes: Ok, someone else posted their data warehousing wishlist, I want to post my single item OLP wishlist: Plan stability. That seems to me to translate to I want the system to fail to react to changes in data statistics and all other variables relevant to query planning. You can pretty much get that by never doing [VACUUM] ANALYZE, but I'm quite lost as to why it's really a good idea. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 To Do
We recently finished some evaluations of Oracle, and one of the things which jumped out at me, PostgreSQL booster that I am, was that in 9i Oracle has finally surpassed PostgreSQL in some elements of object-relational technology. Among the things you can do are: - Create new compound object types on the SQL command line. So for example, the imaginary numbers example in the PostgreSLQ manual could all be done on the command line. - Address components of objects using dot-notation. (select employee.salary from employees) - Index objects based on their components (create index blah_idx on employees (employee.last_name)) In combination with ARRAY data types, and references, you can do pretty fancy things without ever creating a C library. PostgreSQL seems to have most of the underpinning already. I even did some experimenting with the tables as a datatype stuff. However, it's definately incomplete. In the tables-as-datatype example, you cannot get the objects to reconstruct themselves, you have to do it by hand. And while oid's make handy reference holders, there does not seem to be any particular performance benefit to doing object-oriented modelling over relational modelling in PostgreSQL at this point. By contrast, OORDBMS like Informix can be blazingly fast when used with the proper OO model. (Oracle performance enhancement for OO models is mixed at best right now.) Bruce Momjian wrote: Does anyone have additions for the list. I didn't see anything discussed that jumped out at me, though I didn't see Conquer the world on there. ;-) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
Nicolai Tufar [EMAIL PROTECTED] writes:
src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.
As it should. I think the real problem here is the hack in gram.y:
grantee:ColId
{
PrivGrantee *n = makeNode(PrivGrantee);
/* This hack lets us avoid reserving PUBLIC as a keyword*/
if (strcmp($1, public) == 0)
n-username = NULL;
else
n-username = $1;
n-groupname = NULL;
$$ = (Node *)n;
}
If the parser treated PUBLIC as an actual keyword, you'd not be having
this problem, because keywords are case-folded on an ASCII-only basis
(which is consistent with the SQL99 spec, amazingly enough).
We put in the above hack after someone complained that PUBLIC didn't use
to be a reserved word ... but considering that SQL92 clearly lists it as
a reserved word, there's not a lot of ground for that complaint to stand
on.
I'd prefer shifting PUBLIC back to the true-keyword category over any
of the other workarounds you've suggested ...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 To Do
Paul Ramsey [EMAIL PROTECTED] writes: Oracle has finally surpassed PostgreSQL in some elements of object-relational technology. Among the things you can do are: - Address components of objects using dot-notation. (select employee.salary from employees) Cool. How do they resolve the conflict against schema notation (ie, is employee a table reference or a schema name here)? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Odd optimiser behaviour
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Why does using CURRENT_DATE instead of a literal date make a difference? In 7.3 it doesn't. Prior versions do not understand that CURRENT_DATE can be treated like a constant in this context. Time to update ;-) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
- Original Message - From: Tom Lane [EMAIL PROTECTED] ... but considering that SQL92 clearly lists it as a reserved word, there's not a lot of ground for that complaint to stand on. I'd prefer shifting PUBLIC back to the true-keyword category over any of the other workarounds you've suggested ... It will work for me. But why not change PUBLIC in pg_dump output to lower-case as well? regards, tom lane Nic. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
Someone asked earlier about how to change a bunch of existing tables int the
PUBLIC schema to some other schema. For grins I tried:
regression=# select oid,* from pg_namespace ;
oid | nspname | nspowner | nspacl
++--+
11 | pg_catalog |1 | {=U}
99 | pg_toast |1 | {=}
2200 | public |1 | {=UC}
16766 | pg_temp_1 |1 |
556829 | bar|1 |
(5 rows)
regression=# update pg_class set relnamespace=556829 where relname = 'foo' and
relnamespace=2200;
UPDATE 1
and it seemed to work fine (i.e. moved foo from schema public to schema bar).
But it made me wonder if we shouldn't have:
ALTER TABLE table SCHEMA TO new_schema
as a supported method to do this?
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
Joe Conway [EMAIL PROTECTED] writes: Someone asked earlier about how to change a bunch of existing tables int the PUBLIC schema to some other schema. For grins I tried: regression=# update pg_class set relnamespace=556829 where relname = 'foo' and relnamespace=2200; UPDATE 1 and it seemed to work fine (i.e. moved foo from schema public to schema bar). But it didn't fix the pg_depend entries linking the table to its schema :-( But it made me wonder if we shouldn't have: ALTER TABLE table SCHEMA TO new_schema I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y I don't see anything in the SQL spec about this; anyone know what precedent is in Oracle or other DBMSes? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: Someone asked earlier about how to change a bunch of existing tables int the PUBLIC schema to some other schema. For grins I tried: regression=# update pg_class set relnamespace=556829 where relname = 'foo' and relnamespace=2200; UPDATE 1 and it seemed to work fine (i.e. moved foo from schema public to schema bar). But it didn't fix the pg_depend entries linking the table to its schema :-( Yeah, I knew there was something I was forgetting. That's why I didn't actually offer it up as a solution to anyone. But it made me wonder if we shouldn't have: ALTER TABLE table SCHEMA TO new_schema I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y I don't see anything in the SQL spec about this; anyone know what precedent is in Oracle or other DBMSes? Good question. I can't find anything in the Oracle docs indicating it is even possible. We should probably just go with your suggestion. Anything else beyond the relnamespace and pg_depend entries that need to be dealt with? Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] ExecMakeTableFunctionResult vs. pre-evaluated functions
I've spent today messing with making the planner substitute inline definitions of simple SQL functions, per the comment in src/backend/optimizer/util/clauses.c: * XXX Possible future improvement: if the func is SQL-language, and its * definition is simply SELECT expression, we could parse and substitute * the expression here. This would avoid much runtime overhead, and perhaps * expose opportunities for constant-folding within the expression even if * not all the func's input args are constants. It'd be appropriate to do * that here, not in the parser, since we wouldn't want it to happen until * after rule substitution/rewriting. It seems to work 99%, but I'm seeing this failure in the regression tests: CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1; ! ERROR: ExecMakeTableFunctionResult: expression is not a function call which of course happens because the table-function expression has been reduced to just a constant 1 by the time the executor sees it. A grotty answer is to not apply constant-expression folding to table function RTE entries. A better answer would be to make ExecMakeTableFunctionResult more flexible, but I'm not quite sure what it should do if presented a non-function-call expression tree. Any thoughts? regards, tom lane PS: another little problem is regression=# explain SELECT * FROM getfoo(1) AS t1; server closed the connection unexpectedly but I'm sure that's just a lack of flexibility in explain.c ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ExecMakeTableFunctionResult vs. pre-evaluated functions
Tom Lane wrote: It seems to work 99%, but I'm seeing this failure in the regression tests: CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1; ! ERROR: ExecMakeTableFunctionResult: expression is not a function call which of course happens because the table-function expression has been reduced to just a constant 1 by the time the executor sees it. A grotty answer is to not apply constant-expression folding to table function RTE entries. A better answer would be to make ExecMakeTableFunctionResult more flexible, but I'm not quite sure what it should do if presented a non-function-call expression tree. Any thoughts? If presented with a non-function-call expression tree, can we always evaluate it to produce a scalar constant (if it isn't already)? If so, why not do that, create a one row, one column tuplestore, and exit? It's really no different than a function call that does the same, is it? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Segmentation fault while COPY in 7.3
- Original Message - From: Tom Lane [EMAIL PROTECTED] Ohhh ... Nicolai, are you running with a client encoding different from server encoding? Got it! Gentlemen, thank you very much for assistance. The body of evidence was slowly growing, then, finaly Tom Lan's message have enlightened me. It all started with initdb's warning that it can not set client's connection to 'LATIN5'. Okay, I said, maybe some system files are not installed. Then I tried to create a database with ENCODING='LATIN5'. I did not pay much attention either since my template1 and template0 are in LATIN5. Then on Tom's suggestion I tried to change client encoding: aaa=# \encoding LATIN5 aaa=# \encoding SQL_ASCII SQL_ASCII: invalid encoding name or conversion procedure not found aaa=# \encoding LATIN5 LATIN5: invalid encoding name or conversion procedure not found aaa=# \encoding SQL_ASCii aaa=# \encoding SQL_ASCII aaa=# \encoding LATiN5 aaa=# \encoding LATIN5 aaa=# So it all falls back to Turkish I problem I mentioned earlier. To perform COPY successfully I had to set client locale to 'LATiN5' (note quotes and lower-case i). Any other combinations result in segmentation fault. Now, would it be right to change locale name handling to use ASCII-only lower and upper-case conversions? Many thanks to Tom Lane and Joe Conway. I've got to get some sleep :-) Regards, Nic. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
