AW: [HACKERS] LIMIT in DECLARE CURSOR: request for comments
After thinking some more about yesterday's discussions, I propose that we adopt the following planning behavior for cursors: 1. If DECLARE CURSOR does not contain a LIMIT, continue to plan on the basis of 10%-or-so fetch (I'd consider anywhere from 5% to 25% to be just as reasonable, if people want to argue about the exact number; perhaps a SET variable is in order?). 10% seems to be a reasonable compromise between delivering tuples promptly and not choosing a plan that will take forever if the user fetches the whole result. Imho that was a wrong assumption in the first place. The default assumption imho needs to be 100 %. Especially if you fixed the limit clause enabling people to optimize the few rows fetched case. 3. If DECLARE CURSOR contains "LIMIT ALL", plan on the assumption that all tuples will be fetched, ie, select lowest-total-cost plan. (Note: LIMIT ALL has been in the grammar right along, but up to now it has been entirely equivalent to leaving out the LIMIT clause. This proposal essentially suggests allowing it to act as a planner hint that the user really does intend to fetch all the tuples.) Comments? Imho an explicit statement to switch optimizer mode from all rows to first rows would be a lot easier to understand and is what other DB vendors do. Andreas
[HACKERS] Current CVS broken?
I've just tried to checkout a clean copy of the cvs tree, and it seems that configure is missing a substitutions in Makefile.global.in, ie: make: *** No rule to make target `@abs_top_srcdir@/src/Makefile.global.in', needed by `../../../src/Makefile.global'. Stop. Any ideas? Peter -- Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
[HACKERS] Transaction costs?
Just wondering what the cost of begin/end transaction is. This is for pg_dump which, when restoring BLOBs, inserts multiple rows into a temporary xref table. The sequence of events is: Conn1: Begin Conn1: lo_create/lo_close/lo_write.../lo_close Conn2: Insert into xref table (which does an implicit begin/end, I think). Conn1: Commit; Would I get substantially better performance by doing a begin/end every 10/100/1000 rows in each connection, or is the transaction overhead low? Or is this something I just need to test? [eg. in Dec/RDB TX begin/end is expensive, but writing more than 1000 rows in a TX can also be costly, so a compromise is useful] Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
[Since I've rested over the weekend, I hope I don't come across this morning as an angry old snarl, like some of my previous posts on this subject unfortunately have been.] Bruce Momjian wrote: * Location-agnostic installation. Documentation (which I'll be happy to contribute) on that. Peter E is already working in this area. Getting the installation that 'make install' spits out massaged into an FHS compliant setup is the majority of the RPM's spec file. Well, we certainly don't want to make changes that make things harder or more confusing for non-RPM installs. How are they affected here? They wouldn't be. Peter E has seemingly done an excellent job in this area. I say seemingly because I haven't built an RPM from the 7.1 branch yet, but from what he has posted, he seems to understand the issue. Many thanks, Peter. * Upgrades that don't require an ASCII database dump for migration. This can either be implemented as a program to do a pg_dump of an arbitrary version of data, or as a binary migration utility. Currently, I'm I really don't see the issue here. At the risk of being redundant, here goes. As I've explained before, the RPM upgrade environment, thanks to our standing with multiple distributions as being shipped as a part of the OS, could be run as part of a general-purpose OS upgrade. In the environment of the general purpose OS upgrade, the RPM's installation scripts cannot fire up a backend, nor can it assume one is running or is not running, nor can the RPM installation scripts fathom from the run-time environment whether they are being run from a command line or from the OS upgrade (except on Linux Mandrake, which allows such usage). Thus, if a system administrator upgrades a system, or if an end user who has a pgaccess-customized data entry system for things as mundane as an address list or recipe book, there is no opportunity to do a dump. The dump has to be performed _after_ the RPM upgrade. Now, this is far from optimal, I know. I _know_ that the user should take pains with their data. I know that there should be a backup. I also know that a user of PostgreSQL should realize that 'this is just the way it is done' and do things Our Way. I also know that few new users will do it 'Our Way'. No other package that I am aware of requires the manual intervention that PostgreSQL does, with the possible exception of upgrading to a different file system -- but that is something most new users won't do, and is something that is more difficult to automate. However, over the weekend, while resting (I did absolutely NO computer work this weekend -- too close to burnout), I had a brainstorm. A binary migration tool does not need to be written, if a concession to the needs of some users who just simply want to upgrade can be made. Suppose we can package old backends (with newer network code to connect to new clients). Suppose further that postmaster can be made intelligent enough to fire up old backends for old data, using PG_VERSION as a key. Suppose a NOTICE can be fired off warning the user that 'The Database is running in Compatibility Mode -- some features may not be available. Please perform a dump of your data, reinitialize the database, and restore your data to access new features of version x.y'. I'm highly considering doing just that from a higher level. It will not be nearly as smooth, but doable. Of course, that increases maintenance work, and I know it does. But I'm trying to find a middle ground here, since providing a true migration utility (even if it just produces a dump of the old data) seems out of reach at this time. We are currently forcing something like a popular word processing program once did -- it's proprietary file format changed. It was coded so that it could not even read the old files. But both the old and the new versions could read and write an interchange format. People who blindly upgraded their word processor were hit with a major problem. There was even a notice in the README -- which could be read after the program was installed. While the majority of us use PostgreSQL as a server behind websites and other clients, there will be a large number of new users who want to use it for much more mundane tasks. Like address books, or personal information management, or maybe even tax records. Frontends to PostgreSQL, thanks to PostgreSQL's advanced features, are likely to span the gamut -- we already have OnShore TimeSheet for time tracking and payroll, as one example. And I even see database-backed intranet-style web scripts being used on a client workstation for these sorts of things. I personally do just that with my home Linux box -- I have a number of AOLserver dynamic pages that use PostgreSQL for many mundane tasks (a multilevel sermon database is one). While I don't need handholding in the upgrade process, I have provided support to users that do -- who are astonished at the way we upgrade.
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Peter Eisentraut wrote: Lamar Owen writes: Getting the installation that 'make install' spits out massaged into an FHS compliant setup is the majority of the RPM's spec file. ./configure --prefix=/usr --sysconfdir=/etc Off you go... (I'll refrain from commenting further on the FHS.) I know alot of people don't like LSB/FHS, but, like it or not, I have to work with it. And, many many thanks for putting in the work on the configuration as you have. * Upgrades that don't require an ASCII database dump for migration. Let me ask you this question: When any given RPM-based Linux distribution will update their system from ext2 to, say, ReiserFS across the board, how are they going to do it? Sincere question. Like the TRS-80 model III, whose TRSDOS 1.3 could not read the TRS-80 Model I's disks, written on TRSDOS 2.3 (TRSDOS's versioning was absolutely horrendous). TRSDOS 1.3 included a CONVERT utility that could read files from the old filesystem. I'm sure that the newer distributions using ReiserFS as the primary filesystem will include legacy Ext2/3 support, at least for read-only, for many versions to come. And that's my big beef -- a newer version of PostgreSQL can't even pg_dump an old database. If that single function was supported, I would have no problem with the upgrade whatsoever. * A less source-centric mindset. Let's see, how to explain? The regression tests are a good example. You need make. You need the source installed, configured, and built in the usual location. This is not an excuse, but almost every package behaves this way. Test suites are designed to be run after "make all" and before "make install". When you ship a binary package then you're saying to users "I did the building and installation (and presumably everything else that the authors recommend along the way) for you." Yes, and I do just that. Regression testing is a regular part of my build process here. RPM packages usually don't work very well on systems that are not exactly like the one they were built on Boy, don't I know it.~;-/ Getting the regression tests to work from anywhere is not very hard, but it's not the most interesting project for most people. :-) I know. I'll probably do it myself, as that is something I _can_ do. I think I may have a solution for the library versioning problem. Rather than symlink libpq.so-libpq.so.2-libpq.so.2.x, I'll copy libpq.so.2.1 to libpq.so.2 and symlink libpq.so to that. I'd still claim that if RPM thinks it's smarter than the dynamic loader, then it's broken. All the shared libraries on Linux have a symlink from more general to more specific names. PostgreSQL can't be the first to hit this problem. RPM is getting it's .so dependency list straight from the mouth of the dynamic loader itself. RPM uses shell scripts, customizable for each system on which RPM runs, to determine the automatic dependencies -- those shell scripts run the dynamic loader to get the list of requires. So, the dynamic loader itself is providing the list. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [HACKERS] Current CVS broken?
Peter Mount writes: I've just tried to checkout a clean copy of the cvs tree, and it seems that configure is missing a substitutions in Makefile.global.in, ie: make: *** No rule to make target `@abs_top_srcdir@/src/Makefile.global.in', needed by `../../../src/Makefile.global'. Stop. Any ideas? Run './config.status --recheck'. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[HACKERS] Re: CC not getting -O passed?
Larry Rosenman writes: BUT, we default C to -O, why not C++? Basically because we haven't done it yet. I'm not sure whether we're going beta anytime soon, if not it'll probably get implemented. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[HACKERS] Unicode conversion (Re: [COMMITTERS] pgsql (configure.in))
Add new configure option "--enable-uniconv" that enables automatic code conversion between Unicode and other encodings. Note that this option requires --enable-multibyte also. The reason why this is optional is that the feature requires huge mapping tables and I don't think every user need the feature. Can you explain what this does? Does it mean frontends can use Unicode as their character set? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[HACKERS] CommandCounterIncrement
Hello, Small technical question: what exactly CommandCounterIncrement do? And what exactly it should be used for? I use it to see data which is changed in current transaction. If to be more exact when I write BLOB in transaction each time I write additional piece I do CommandCounterIncrement. I ask this question because I found out that when I run postgres with verbose=4 I see lot's of StartTransactionCommand CommitTransactionCommand pair in the place where BLOB is written. And I have a feeling that something is wrong. Looks like explicitly commit all changes. That's really bad... -- Sincerely Yours, Denis Perchine -- E-Mail: [EMAIL PROTECTED] HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 --
Re: AW: [HACKERS] LIMIT in DECLARE CURSOR: request for comments
On Mon, 30 Oct 2000, Zeugswetter Andreas SB wrote: After thinking some more about yesterday's discussions, I propose that we adopt the following planning behavior for cursors: 1. If DECLARE CURSOR does not contain a LIMIT, continue to plan on the basis of 10%-or-so fetch (I'd consider anywhere from 5% to 25% to be just as reasonable, if people want to argue about the exact number; perhaps a SET variable is in order?). 10% seems to be a reasonable compromise between delivering tuples promptly and not choosing a plan that will take forever if the user fetches the whole result. Imho that was a wrong assumption in the first place. The default assumption imho needs to be 100 %. Especially if you fixed the limit clause enabling people to optimize the few rows fetched case. But what if you're doing fetch 10 rows, fetch 10 rows, ... You're not limiting, because you want all of them, but you are only pulling a small number at a time to say do expensive front end processing. It might make sense to actually pull a plan which is lower startup and higher per row. Although the full cost is higher, you get a better turnaround time on the first set and the cost difference per set may be unnoticeable (it would depend on the particulars).
Re: [HACKERS] Current CVS broken?
On Mon, 30 Oct 2000, Peter Eisentraut wrote: Peter Mount writes: Run './config.status --recheck'. No still has the problem. I'm currently having to edit it manually to get round the problem. Oh, you need to run './config.status' as well. './config.status --recheck' figures out the new value of @abs_top_srcdir@, and './config.status' substitutes it. Did that, and it still doesn't substitute @abs_top_srcdir@ Peter -- Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
Re: [HACKERS] Current CVS broken?
Peter Mount writes: Run './config.status --recheck'. No still has the problem. I'm currently having to edit it manually to get round the problem. Oh, you need to run './config.status' as well. './config.status --recheck' figures out the new value of @abs_top_srcdir@, and './config.status' substitutes it. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] WAL status update
Vadim Mikheev writes: WAL todo list looks like: So what's the latest on going beta? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Unicode conversion (Re: [COMMITTERS] pgsql(configure.in))
Add new configure option "--enable-uniconv" that enables automatic code conversion between Unicode and other encodings. Note that this option requires --enable-multibyte also. The reason why this is optional is that the feature requires huge mapping tables and I don't think every user need the feature. Can you explain what this does? Does it mean frontends can use Unicode as their character set? Yes. Here are some examples: (1) both backend/frontend uses Unicode(actually UTF-8) $ createdb -E unicode unicode $ psql unicode [some sessions follow using UTF-8] : : Note that this is not a new functionality as opposite to (2), (3). (2) backend is ISO8859-2 but frontend is UNICODE $ createdb -E LATIN2 latin2 $ psql latin2 \encoding UNICODE [some sessions follows using UTF-8] : : Note that if you type in a wrong ISO8859-2 character that could not be converted to UTF-8, you would get notices something like: NOTICE: local_to_utf: could not convert (0x00b4) LATIN2 to UTF-8. Ignored (3) backend is Unicode but frontend is ISO8859-2 $ createdb -E unicode unicode $ psql unicode \encoding LATIN2 [some sessions follow using ISO8859-2] : : Same note above... -- Tatsuo Ishii
Re: [HACKERS] relation ### modified while in use
Tom Lane wrote: Alex Pilosov [EMAIL PROTECTED] writes: I think this happens after I create/modify tables which reference this table. This is spontaneous, and doesn't _always_ happen... Um. I was hoping it was something more easily fixable :-(. What's causing the relcache to decide that the rel has been modified is the addition or removal of foreign-key triggers on the rel. Which seems legitimate. (It's barely possible that we could get away with allowing triggers to be added or deleted mid-transaction, but that doesn't feel right to me.) There are two distinct known bugs that allow the error to be reported. These have been discussed before, but to recap: 1. relcache will complain if the notification of cache invalidation arrives after transaction start and before first use of the referenced rel (when there was already a relcache entry left over from a prior transaction). In this situation we should allow the change to occur without complaint, ISTM. But the relcache doesn't currently have any concept of first reference versus later references. Do we have a conclusion about this thread ? If no,how about changing heap_open(r) so that they allocate Relation descriptors after acquiring a lock on the table ? We would use LockRelation() no longer. Comments ? Regards. Hiroshi Inoue
[HACKERS] Data definition for aclitem Datatype
Hi Sirs. What is the data definition for the aclitem datatype, I'm not able to found it in the sources, I know is there but I was not able to find it. Thank you. -- Luis Magaña Gnovus Networks Software www.gnovus.com Tel. +52 (7) 4422425 [EMAIL PROTECTED]
AW: [HACKERS] regression failure/UnixWare7.1.1/current sources
Hmm. I wonder why cc and gcc are doing different math. Wierd. Not only that, but you get different results with the same compiler depending on different optimization settings. The joys of binary floating point... Same on AIX. Andreas
Re: [HACKERS] WAL status update
I believe that its just resting on Vadim again to give us the go ahead
... which I believe its always been on his shoulders, no? :)
Vadim?
On Mon, 30 Oct 2000, Peter Eisentraut wrote:
Vadim Mikheev writes:
WAL todo list looks like:
So what's the latest on going beta?
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
