Re: [HACKERS] Extensions, patch v16
David E. Wheeler da...@kineticode.com writes: On Dec 9, 2010, at 12:34 PM, Dimitri Fontaine wrote: - add support for 'relocatable' boolean property in the control file, as discussed on list This still isn't ideal, but I think it's a big improvement. Thanks. Glad you like it :) If you see any way to manage that better, please do tell. Just be sure to review the past 18 months of on-list discussion about the topic before to go thinking extension vs search_path is easy to solve, or even possible to solve. \dx lists only installed extensions \dx+ extension lists the objects, calling pg_extension_objects() \dX lists available extensions (and installed too) +1 I think that's much more like existing psql commands. Good, I'll have that in the next patch version, waiting for until your review of the new one :) So: * If $extension.control.in exists, use that * If it doesn't, generate $extension.control from the Makefile variables What if $extension.control exists? Is it a byproduct of the .in file from previous `make` run or a user file? What if we have both the .in and the make variable because people are confused? Or both the make variables and a .control and not .control.in? Etc... * Always remove $extension.control in the `clean` targets Hell no, as you can bypass the .in mechanism and provide directly the .control file. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] To Signal The postmaster
On Fri, Dec 10, 2010 at 3:51 PM, aaliya zarrin aaliya.zar...@gmail.com wrote: Can anybody tell after finding the trigger file what steps does postgres follow? The server applies all the WAL records available, performs checkpoint, renames recovery.conf, and gets out of recovery mode. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
On 12/09/2010 03:36 PM, BRUSSER Michael wrote: No, we do not use --with-system-tzdata option. I looked at the makefile and at the output of pg_config. We may need to do some cleanup there, but I did not pick any clues. The problem occurs on all our UNIX platforms. Is there anything I could do to shed more light on it? I can post the output of pg_config if it helps, or try to run initdb with truss or strace. Yes, please show us the pg_config (and please don't top-post). cheers andrew --- I tried to build Postgres with minimal options outside of our normal build framework to isolate possible interference, but got the same result. configure \ --prefix=$prefix \ --without-readline \ --without-zlib \ --without-tcl \ --without-perl \ --disable-rpath \ 21 | tee _configure.log - gmake, gmake check, gmake install -- all clean - initdb: initdb -D /home/local_path/pgdata -L /home/local_path/share -E UTF8 --locale=C \ -d 2 log2 log1 log1 ends with line creating template1 database in /home/local_path/pgdata/base/1 ... In log2 I got the same errors as I mentioned earlier: LOG: could not open directory /home/build-path/.../share/timezone: No such file or directory ... ... Directories timezone and timezonesets exist in /home/local_path/share, pointed to by the -L option The local share dir has the same contents as the build share dir. % pg_config BINDIR = /home/build-path/build/Linux/qe_x86_64/bin DOCDIR = /home/build-path/build/Linux/qe_x86_64/share/doc HTMLDIR = /home/build-path/build/Linux/qe_x86_64/share/doc INCLUDEDIR = /home/build-path/build/Linux/qe_x86_64/include PKGINCLUDEDIR = /home/build-path/build/Linux/qe_x86_64/include INCLUDEDIR-SERVER = /home/build-path/build/Linux/qe_x86_64/include/server LIBDIR = /home/build-path/build/Linux/qe_x86_64/lib PKGLIBDIR = /home/build-path/build/Linux/qe_x86_64/lib LOCALEDIR = /home/build-path/build/Linux/qe_x86_64/share/locale MANDIR = /home/build-path/build/Linux/qe_x86_64/share/man SHAREDIR = /home/build-path/build/Linux/qe_x86_64/share SYSCONFDIR = /home/build-path/build/Linux/qe_x86_64/etc PGXS = /home/build-path/build/Linux/qe_x86_64/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/home/build-path/build/Linux/qe_x86_64' '--without-readline' '--without-zlib' CC = gcc CPPFLAGS = -D_GNU_SOURCE CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv CFLAGS_SL = -fpic LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/home/build-path/build/Linux/qe_x86_64/lib' LDFLAGS_SL = LIBS = -lpgport -lcrypt -ldl -lm VERSION = PostgreSQL 8.4.4 This is probably not helpful, but just in case % strings initdb | grep build-path /home/build-path/build/Linux/qe_x86_64/bin /home/build-path/build/Linux/qe_x86_64/share/man /home/build-path/build/Linux/qe_x86_64/share/doc/ /home/build-path/build/Linux/qe_x86_64/share/locale /home/build-path/build/Linux/qe_x86_64/lib /home/build-path/build/Linux/qe_x86_64/include/server /home/build-path/build/Linux/qe_x86_64/include /home/build-path/build/Linux/qe_x86_64/etc /home/build-path/build/Linux/qe_x86_64/share Thank you, Michael. This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be confidential and/or privileged. If you are not one of the named recipients or have received this email in error, (i) you should not read, disclose, or copy it, (ii) please notify sender of your receipt by reply email and delete this email and all attachments, (iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this email.For other languages, go to http://www.3ds.com/terms/email-disclaimer. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] To Signal The postmaster
Hi , I want to stop the recovery forcefully what shall I do? Rather say, after finding the trigger file I want postgres to switch over? Which function shall I call? Where the flow is transfer exactly after finding the trigger file? On Fri, Dec 10, 2010 at 2:36 PM, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Dec 10, 2010 at 3:51 PM, aaliya zarrin aaliya.zar...@gmail.com wrote: Can anybody tell after finding the trigger file what steps does postgres follow? The server applies all the WAL records available, performs checkpoint, renames recovery.conf, and gets out of recovery mode. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Thanks Regards, Aaliya Zarrin (+91)-9160665888
Re: [HACKERS] initdb failure with Postgres 8.4.4
On Fri, Dec 10, 2010 at 4:08 AM, BRUSSER Michael michael.brus...@3ds.com wrote: - initdb: initdb -D /home/local_path/pgdata -L /home/local_path/share -E UTF8 --locale=C \ -d 2 log2 log1 log1 ends with line creating template1 database in /home/local_path/pgdata/base/1 ... In log2 I got the same errors as I mentioned earlier: LOG: could not open directory /home/build-path/.../share/timezone: No such file or directory ... ... Does it really have a pathname with three dots here? I'd have more confidence we knew what we were looking at if you sent the actual log, rather than an edited version of the lines you think are most important. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
On Fri, Dec 10, 2010 at 1:39 AM, Vaibhav Kaushal vaibhavkaushal...@gmail.com wrote: Most of you already know I am new to this list and newer to any OSS development. However, while browsing the source code (of 9.0.1) I find that there is only one way to store relations on disk - the magnetic disk. This came suddenly in my mind so I am asking the experts here. Considering the fact that SSDs will be common (at least for the enterprise) in the coming years because of (of course you know the reason) their less seek time and higher transfer rates per second, is there someone trying for a ssd.c? In almost all cases even using md.c, the kernel will handle it effectively but would it not be better that we are well prepared to ask kernel for more? Or has such an attempt already begun? Questions about using SSDs with PostgreSQL would be more appropriate on pgsql-performance, rather than here. If you search, you'll find that the topic has been covered extensively in the archives. But as far as the code goes, there doesn't seem to be any reason why SSDs would require any changes to md.c, or an alternate implementation. The interface the operating system presents is the same. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
On 12/10/2010 07:32 AM, Robert Haas wrote: On Fri, Dec 10, 2010 at 4:08 AM, BRUSSER Michael michael.brus...@3ds.com wrote: - initdb: initdb -D /home/local_path/pgdata -L /home/local_path/share -E UTF8 --locale=C \ -d 2 log2 log1 log1 ends with line creating template1 database in /home/local_path/pgdata/base/1 ... In log2 I got the same errors as I mentioned earlier: LOG: could not open directory /home/build-path/.../share/timezone: No such file or directory ... ... Does it really have a pathname with three dots here? I'd have more confidence we knew what we were looking at if you sent the actual log, rather than an edited version of the lines you think are most important. And why the heck use initdb's -L? initdb knows quite well where to get its files from, if you haven't mucked up the installation. I don't think I've ever seen a case in production where it's been necessary. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BufFreelistLock
Excerpts from Jim Nasby's message of jue dic 09 16:54:24 -0300 2010: Ideally, the clock sweep would be run by bgwriter and not individual backends. In that case it shouldn't matter much what the performance of the sweep is. To do that I think we'd want the bgwriter to target there being X number of buffers on the free list instead of (or in addition to) targeting how many dirty buffers need to be written. This would mirror what operating systems do; they strive to keep X number of pages on the free list so that when a process needs memory it can get it quickly. Isn't it what it does if you set bgwriter_lru_maxpages to some very large value? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
In log2 I got the same errors as I mentioned earlier: LOG: could not open directory /home/build-path/.../share/timezone: No such file or directory ... ... Does it really have a pathname with three dots here? I'd have more confidence we knew what we were looking at if you sent the actual log, rather than an edited version of the lines you think are most important. And why the heck use initdb's -L? initdb knows quite well where to get its files from, if you haven't mucked up the installation. I don't think I've ever seen a case in production where it's been necessary. - 1) The pathnames referenced in the log do not have any dots, I just truncated them - they are absolute pathnames to where the timezone files are locates in the source or build tree I'll post the entire log if it is still needed after this ... 2) We have been using 'initdb -L' since Postgres 7.2 The way it works here we build Postgres and it gets bundled as a part of a larger application, which can be installed anywhere. Perhaps this makes it difficult for initdb to know quite well where to get its files from. If I remove the -L switch initdb breaks very early before it gets to the timezones: share_path points back to the build location, which is not available, so it cannot locate postgres.bki and other input files. The timezones piece seems to be the only problem - if I compile with --with-system-tzdata=/usr/share/zoneinfo and then use initdb -L installation succeeds when the source/build tree is not accessible. Not claiming any knowledge in this area - would it be reasonable to expect that if -L option works for other input files it should also work for timezones? Thanks for all your help This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be confidential and/or privileged. If you are not one of the named recipients or have received this email in error, (i) you should not read, disclose, or copy it, (ii) please notify sender of your receipt by reply email and delete this email and all attachments, (iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this email.For other languages, go to http://www.3ds.com/terms/email-disclaimer. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
On Fri, Dec 10, 2010 at 9:00 AM, BRUSSER Michael michael.brus...@3ds.com wrote: 1) The pathnames referenced in the log do not have any dots, I just truncated them - they are absolute pathnames to where the timezone files are locates in the source or build tree I'll post the entire log if it is still needed after this ... Well, why are you configuring the prefix to be inside your build tree? This is an odd thing to do. The prefix should be set to where you expect to install the files, not where you're building them. CONFIGURE = '--prefix=/home/build-path/build/Linux/qe_x86_64' '--without-readline' '--without-zlib' Still: Not claiming any knowledge in this area - would it be reasonable to expect that if -L option works for other input files it should also work for timezones? ...this seems reasonable. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] serializable read only deferrable
Dan Ports wrote: On Tue, Dec 07, 2010 at 10:14:24AM -0600, Kevin Grittner wrote: The only thing I'm worried about here is how much risk of starvation remains. You'd need to wait until there are no running r/w transactions accessing overlapping data sets; for some applications that might not be any better than waiting for the system to be idle. But I think there's no way around that, it's just the price you have to pay to get a snapshot that can never see an anomaly. Right -- this can't be a default behavior because of that, but it rounds out the options for backups and big reports. Without it you have the choice between the potential for other transactions to cancel because a cycle was completed by the READ ONLY transaction or getting a view of data which may not be consistent with the later state of the database[1]. This guarantees consistency without causing rollbacks, with the additional benefit of faster runtime by skipping SSI logic. Pseudo-code of idea (conveniently ignoring locking issues and non-serializable transactions): This seems reasonable to me. Let me know if you need help implementing it; I have some spare cycles right now. That would be great. I'm getting on a train today to go spend a week on vacation in New Orleans, and I've been fretting about where this patch is at compared to the release cycle. :-( I can suck down my hurricanes with a calmer mind if I know you're on this. :-) In conjunction with this feature, it would be great if you could take a look at how to recognize these conditions for a READ ONLY transaction which is running under SSI, and back it out of SSI when it hits that condition. SIRead predicate locks, conflicts, and other structures can be released, and we can stop checking the MVCC data on reads. Basically, we should be able to get to the DEFERRABLE type of state while running -- it's just that we might cause some number of transactions to cancel along the way before we hit that state. (These two seem likely to be less work if done at the same time.) -Kevin [1] It has struck me that the receipting example is one case of a more general pattern which I've frequently seen in business software which is vulnerable to this sort of anomaly -- batch processing. Basically, any time you have a control record which controls the batch into which detail is placed, if the control information is updated and that is committed while detail is still in flight, you can have this class of anomaly. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BufFreelistLock
On Fri, Dec 10, 2010 at 5:45 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Jim Nasby's message of jue dic 09 16:54:24 -0300 2010: Ideally, the clock sweep would be run by bgwriter and not individual backends. In that case it shouldn't matter much what the performance of the sweep is. Lock contention between the bgwriter and the individual backends would matter very much. This might actually make things worse. Now you need two BufFreelistLocks, one to stick it on the freelist, and one to take it off. To do that I think we'd want the bgwriter to target there being X number of buffers on the free list instead of (or in addition to) targeting how many dirty buffers need to be written. This would mirror what operating systems do; they strive to keep X number of pages on the free list so that when a process needs memory it can get it quickly. Isn't it what it does if you set bgwriter_lru_maxpages to some very large value? As far as I can tell, bgwriter never adds things to the freelist. That is only done at start up, and when a relation or a database is dropped. The clock sweep does the vast majority of the work. But I could be wrong. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
On 12/10/2010 09:55 AM, Robert Haas wrote: On Fri, Dec 10, 2010 at 9:00 AM, BRUSSER Michael michael.brus...@3ds.com wrote: 1) The pathnames referenced in the log do not have any dots, I just truncated them - they are absolute pathnames to where the timezone files are locates in the source or build tree I'll post the entire log if it is still needed after this ... Well, why are you configuring the prefix to be inside your build tree? This is an odd thing to do. The prefix should be set to where you expect to install the files, not where you're building them. CONFIGURE = '--prefix=/home/build-path/build/Linux/qe_x86_64' '--without-readline' '--without-zlib' Still: Not claiming any knowledge in this area - would it be reasonable to expect that if -L option works for other input files it should also work for timezones? ...this seems reasonable. OK, this has nothing at all to do with the absence of the build path. It has to do with using a non-standard sharedir.I have reproduced it thus: [and...@aurelia blurflinst]$ mv share/ foo [and...@aurelia blurflinst]$ bin/initdb -L `pwd`/foo/postgresql data3 The files belonging to this database system will be owned by user andrew. This user must also own the server process. The database cluster will be initialized with locale en_US.utf8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to english. creating directory data3 ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 400kB creating configuration files ... ok creating template1 database in data3/base/1 ... WARNING: could not open directory /home/andrew/pgl/blurflinst/share/postgresql/timezonesets: No such file or directory HINT: This may indicate an incomplete PostgreSQL installation, or that the file /home/andrew/pgl/blurflinst/bin/postgres has been moved away from its proper location. FATAL: invalid value for parameter timezone_abbreviations: Default child process exited with exit code 1 initdb: removing data directory data3 [and...@aurelia blurflinst]$ I will dig a bit further. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
Well, why are you configuring the prefix to be inside your build tree? This is an odd thing to do. The prefix should be set to where you expect to install the files, not where you're building them. CONFIGURE = '--prefix=/home/build-path/build/Linux/qe_x86_64' '--without-readline' '--without-zlib' Still: Not claiming any knowledge in this area - would it be reasonable to expect that if -L option works for other input files it should also work for timezones? ...this seems reasonable. - It is odd, but this only reflects some quick hack I did to have a test case outside of our standard build framework. I wanted to make sure the odd problem with timezones is not coming from our overloaded build environment. Production makefile does it much better. If I don't find a better option - do you see any disadvantage in using --with-system-tzdata and OS timezone info on Solaris and Linux? This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be confidential and/or privileged. If you are not one of the named recipients or have received this email in error, (i) you should not read, disclose, or copy it, (ii) please notify sender of your receipt by reply email and delete this email and all attachments, (iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this email.For other languages, go to http://www.3ds.com/terms/email-disclaimer. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
Robert Haas robertmh...@gmail.com writes: On Fri, Dec 10, 2010 at 1:39 AM, Vaibhav Kaushal vaibhavkaushal...@gmail.com wrote: Most of you already know I am new to this list and newer to any OSS development. However, while browsing the source code (of 9.0.1) I find that there is only one way to store relations on disk - the magnetic disk. But as far as the code goes, there doesn't seem to be any reason why SSDs would require any changes to md.c, or an alternate implementation. The interface the operating system presents is the same. The fact that it's called md.c is a hangover from the '80s. These days, the logic that the Berkeley guys envisioned being at that code level is generally in kernel device drivers. md.c can drive anything that behaves as a block device + filesystem, which is pretty much everything of interest. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
Dimitri Fontaine dimi...@2ndquadrant.fr writes: David E. Wheeler da...@kineticode.com writes: What if $extension.control exists? Is it a byproduct of the .in file from previous `make` run or a user file? What if we have both the .in and the make variable because people are confused? Or both the make variables and a .control and not .control.in? Etc... * Always remove $extension.control in the `clean` targets Hell no, as you can bypass the .in mechanism and provide directly the .control file. Are there any actual remaining use-cases for that sed step? It's certainly vestigial as far as the contrib modules are concerned: it would be simpler and more readable to replace MODULE_PATHNAME with $libdir in the sources. Unless somebody can point to a real-world use-case, I'd just as soon get rid of the .in files altogether while we're having this flag day. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
Tom Lane t...@sss.pgh.pa.us writes: Are there any actual remaining use-cases for that sed step? The goal here is to allow extension authors to maintain their version number in the Makefile rather than in the Makefile and in the control file separately. Having the same version number in more than one place never eases maintenance. Oh and in PostgreSQL sources cases, that would add like 36 spots where to manually maintain our major version string. I'm not eager to do that. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
On 12/10/2010 10:25 AM, Andrew Dunstan wrote: Not claiming any knowledge in this area - would it be reasonable to expect that if -L option works for other input files it should also work for timezones? ...this seems reasonable. OK, this has nothing at all to do with the absence of the build path. It has to do with using a non-standard sharedir.I have reproduced it thus: [snip] I will dig a bit further. Here's my understanding. It's not initdb that's really complaining. The timezone files are not inputs to initdb. It's the postgres that initdb invokes that's complaining. Postges will look for the share file in two places: the configured install directory or a share directory whose path is calculated relative to its own location. initdb's -L flag doesn't override that, it only overrides where initdb itself looks for files (such as the BKI file). The bottom line I think is that if you intend to use a non-standard layout you need to specify the paths for everything and then not move them after installation. If you want the installation to be movable, just specify --prefix, but then if you move it you have to move the whole thing together. You can't just relocate one directory and expect it to work. It won't. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
On Fri, Dec 10, 2010 at 10:54 AM, Andrew Dunstan and...@dunslane.net wrote: Here's my understanding. It's not initdb that's really complaining. The timezone files are not inputs to initdb. It's the postgres that initdb invokes that's complaining. That was my impression, too, from the log that was sent. Postges will look for the share file in two places: the configured install directory or a share directory whose path is calculated relative to its own location. initdb's -L flag doesn't override that, it only overrides where initdb itself looks for files (such as the BKI file). The bottom line I think is that if you intend to use a non-standard layout you need to specify the paths for everything and then not move them after installation. If you want the installation to be movable, just specify --prefix, but then if you move it you have to move the whole thing together. You can't just relocate one directory and expect it to work. It won't. So in theory we could have a GUC under file locations to override this, similarly to data_directory or hba_file or ident_file. But since it's been like this for a really long time (I think), I wouldn't be inclined to go monkeying with it unless more than one person complains. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: Extended query protocol and exact types matches.
On Fri, Dec 10, 2010 at 2:53 AM, Dmitriy Igrishin dmit...@gmail.com wrote: [ message that was forwarded to three mailing lists in an 12 hour period ] Come on, give me a break! How quickly do you expect people to answer your questions? It's reasonable to follow up if you haven't heart anything in a few days, but this is ridiculous. And you might want to follow up to the original list first, rather than a different one. Like this: I see that no one responded to this email... can anyone help me out on this? If there's additional information that I should provide, please let me know what would be helpful. Thanks! If you want people to give a higher priority to your questions than they're willing to do for free, you need a commercial support contract. If you want answers to routine questions within a handful of hours, you probably need an *expensive* commercial support contract. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
Robert Haas robertmh...@gmail.com writes: So in theory we could have a GUC under file locations to override this, similarly to data_directory or hba_file or ident_file. But since it's been like this for a really long time (I think), I wouldn't be inclined to go monkeying with it unless more than one person complains. Even if we did see more than one request, I'd be inclined not to change it. The installation layout stuff is too complicated and fragile already; and AFAICS it does cover every *reasonable* use case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Tom Lane t...@sss.pgh.pa.us writes: Are there any actual remaining use-cases for that sed step? The goal here is to allow extension authors to maintain their version number in the Makefile rather than in the Makefile and in the control file separately. Having the same version number in more than one place never eases maintenance. Why is it in the makefile at all? If the makefile does need to know it, why don't we have it scrape the number out of the control file? Or even more to the point, since when do we need version numbers in extensions? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
Tom Lane t...@sss.pgh.pa.us writes: Why is it in the makefile at all? If the makefile does need to know it, why don't we have it scrape the number out of the control file? Or even more to the point, since when do we need version numbers in extensions? It's in the Makefile so that you find it in the control file later, then in the extension catalog. We need the version number just because I'm not able to name a single software that's not letting you know about its version number once installed. Well in fact I know about one, and I wish the situation would be quite different there. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BufFreelistLock
Excerpts from Jeff Janes's message of vie dic 10 12:24:34 -0300 2010: On Fri, Dec 10, 2010 at 5:45 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Jim Nasby's message of jue dic 09 16:54:24 -0300 2010: To do that I think we'd want the bgwriter to target there being X number of buffers on the free list instead of (or in addition to) targeting how many dirty buffers need to be written. This would mirror what operating systems do; they strive to keep X number of pages on the free list so that when a process needs memory it can get it quickly. Isn't it what it does if you set bgwriter_lru_maxpages to some very large value? As far as I can tell, bgwriter never adds things to the freelist. That is only done at start up, and when a relation or a database is dropped. The clock sweep does the vast majority of the work. AFAIU bgwriter runs the clock sweep most of the time (BgBufferSync). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: Extended query protocol and exact types matches.
Hey Robert, 2010/12/10 Robert Haas robertmh...@gmail.com On Fri, Dec 10, 2010 at 2:53 AM, Dmitriy Igrishin dmit...@gmail.com wrote: [ message that was forwarded to three mailing lists in an 12 hour period ] Come on, give me a break! Please sorry ! But I don't mail to you personally. How quickly do you expect people to answer your questions? It's reasonable to follow up if you haven't heart anything in a few days, but this is ridiculous. Where is it written how many time I should wait before reask/forward ? But sorry for this anyway ! And you might want to follow up to the original list first, rather than a different one. Where is it written ? Like this: I see that no one responded to this email... can anyone help me out on this? If there's additional information that I should provide, please let me know what would be helpful. Thanks! Thanks for the advice ! If you want people to give a higher priority to your questions than they're willing to do for free, you need a commercial support contract. If you want answers to routine questions within a handful of hours, you probably need an *expensive* commercial support contract. Thanks for the offer ! As soon as there will be money without fail we will think over it. It would be quicker to answer my question and help than to teach me the alphabet of communication. Although, thank you, and for that :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- // Dmitriy.
Re: [HACKERS] BufFreelistLock
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Jeff Janes's message of vie dic 10 12:24:34 -0300 2010: As far as I can tell, bgwriter never adds things to the freelist. That is only done at start up, and when a relation or a database is dropped. The clock sweep does the vast majority of the work. AFAIU bgwriter runs the clock sweep most of the time (BgBufferSync). I think bgwriter just tries to write out dirty buffers so they'll be clean when the clock sweep reaches them. It doesn't try to move them to the freelist. There might be some advantage in having it move buffers to a freelist that's just protected by a simple spinlock (or at least, a lock different from the one that protects the clock sweep). The idea would be that most of the time, backends just need to lock the freelist for long enough to take a buffer off it, and don't run clock sweep at all. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: Extended query protocol and exact types matches.
Dmitriy Igrishin wrote: Where is it written ? http://wiki.postgresql.org/wiki/Guide_to_reporting_problems#Things_Not_To_Do -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: Extended query protocol and exact types matches.
Hey Kevin, Oh, I am sorry! Thanks! 2010/12/10 Kevin Grittner kevin.gritt...@wicourts.gov Dmitriy Igrishin wrote: Where is it written ? http://wiki.postgresql.org/wiki/Guide_to_reporting_problems#Things_Not_To_Do -Kevin -- // Dmitriy.
Re: [HACKERS] Extensions, patch v16
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Tom Lane t...@sss.pgh.pa.us writes: Why is it in the makefile at all? If the makefile does need to know it, why don't we have it scrape the number out of the control file? Or even more to the point, since when do we need version numbers in extensions? It's in the Makefile so that you find it in the control file later, then in the extension catalog. This doesn't answer my question of why it couldn't be done the other way. Why does the makefile need to know it? If it does need to know it, couldn't it get it out of the control file instead of vice versa? We need the version number just because I'm not able to name a single software that's not letting you know about its version number once installed. I'm not convinced that this is actually a requirement, or that doing it this specific way is a good solution. In particular, keeping the version number in the system catalogs seems pretty dubious. The common method for upgrading an already-installed contrib module just involves dropping in a new .so --- that's not going to change the system catalogs. It would likely be better to keep the version ID inside the .so file. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
On Dec 10, 2010, at 12:26 AM, Dimitri Fontaine wrote: What if $extension.control exists? Is it a byproduct of the .in file from previous `make` run or a user file? What if we have both the .in and the make variable because people are confused? Or both the make variables and a .control and not .control.in? Etc... There are ways to deal with those issue, I'm sure. * Always remove $extension.control in the `clean` targets Hell no, as you can bypass the .in mechanism and provide directly the .control file. I'm saying disallow the .control file, only allow the control.in file. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
On Dec 10, 2010, at 7:32 AM, Tom Lane wrote: Are there any actual remaining use-cases for that sed step? It's certainly vestigial as far as the contrib modules are concerned: it would be simpler and more readable to replace MODULE_PATHNAME with $libdir in the sources. Unless somebody can point to a real-world use-case, I'd just as soon get rid of the .in files altogether while we're having this flag day. I've made extensive use of them in pgTAP, but they don't depend on PGXS's doing its bit. So no. Unless we require control.in and not .control. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why percent_rank is so slower than rank?
2010/12/10 Tom Lane t...@sss.pgh.pa.us: I wrote: We're throwing away one tuple at a time as we advance forward through the tuplestore, and moving 10+ tuple pointers each time. Ugh. This code was all right when written, because (IIRC) the mergejoin case was actually the only caller. But it's not all right for WindowAgg's less-predictable usage patterns. I thought for a bit about changing things around so that the first-used tuple slot isn't necessarily state-memtuples[0], but just like the comment says, that complicates a lot of other logic. And there isn't any easy place to reclaim the wasted slots later. What seems like the best bet is to put in a heuristic to make tuplestore_trim simply not do anything until nremove reaches some reasonably large amount, perhaps 10% of the number of stored tuples. This wastes up to 10% of the alloted memory, but that seems tolerable. On reflection I think just not doing anything isn't a very good idea. The problem with that is that a mis-coded caller could try to fetch tuples that it had already told the tuplestore could be trimmed away; and this would work, most of the time, until you got unlucky and the trim operation had actually deleted them. I think it's pretty important for bug-catching purposes that the tuplestore enforce that those tuples are not available anymore. I see it's too late now that you've committed it, but it seems there was another way to avoid it by not trimming from percent_rank() individually. Once the whole partition is fit to the memory, you don't need to trim it since it never grows. The trimming logic is for something like moving aggregates and (simple) rank(), which grows tuplestore content as it advances. percent_rank() doesn't seem to match the optimization. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why percent_rank is so slower than rank?
Hitoshi Harada umi.tan...@gmail.com writes: I see it's too late now that you've committed it, Patches can always be reverted... but it seems there was another way to avoid it by not trimming from percent_rank() individually. Once the whole partition is fit to the memory, you don't need to trim it since it never grows. The trimming logic is for something like moving aggregates and (simple) rank(), which grows tuplestore content as it advances. percent_rank() doesn't seem to match the optimization. I don't think this idea leads to a robust solution. When you have a combination of different window functions being used in the same scan, you can't expect any one of them to know the global situation. Having percent_rank lie about its requirements in order to avoid bad behavior in the tuplestore infrastructure is just going to create more problems down the road. We need to have the individual functions tell the truth and then do any optimization hacking in the WindowAgg code or infrastructure. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: Extended query protocol and exact types matches.
On Fri, Dec 10, 2010 at 11:46 AM, Dmitriy Igrishin dmit...@gmail.com wrote: It would be quicker to answer my question and help than to teach me the alphabet of communication. Although, thank you, and for that :-) It would be quicker still to ignore your email altogether, but I'm guessing you're not going to recommend that I take that approach. I did look briefly at whether I could also answer the question while I was replying, but found that I wasn't really sure what you were asking (which may be why no one else responded either). To the best of my ability to determine what you were actually asking, it was something along the lines of What will happen if I tell libpq that a parameter is a bigint when the server is expecting an int?. I don't happen to know the answer to that question without writing a 25-line program, compiling it, and testing it. Which wouldn't be very hard, but on the other hand it wouldn't be very hard for you to do it either. Best guess without testing? It'll work if the value is within the range that can be represented by an int and throw an error otherwise. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why percent_rank is so slower than rank?
2010/12/11 Tom Lane t...@sss.pgh.pa.us: Hitoshi Harada umi.tan...@gmail.com writes: I see it's too late now that you've committed it, Patches can always be reverted... but it seems there was another way to avoid it by not trimming from percent_rank() individually. Once the whole partition is fit to the memory, you don't need to trim it since it never grows. The trimming logic is for something like moving aggregates and (simple) rank(), which grows tuplestore content as it advances. percent_rank() doesn't seem to match the optimization. I don't think this idea leads to a robust solution. When you have a combination of different window functions being used in the same scan, you can't expect any one of them to know the global situation. Having percent_rank lie about its requirements in order to avoid bad behavior in the tuplestore infrastructure is just going to create more problems down the road. We need to have the individual functions tell the truth and then do any optimization hacking in the WindowAgg code or infrastructure. Hm? Once percent_rank() scans to the partition end, any other window functions that scans row by row don't need to care the memory reduction, aren't they? Or more generally, if the partition was scanned to the end, we don't need to trim tuplestore anymore. Am I misunderstanding? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SynchRep; wait-forever and shutdown
3. Shutdown should abort all the blocking transactions? * Problem is that a client thinks that those transactions have been aborted even though those WAL records have been written on the master. But this is very common problem for DBMS, so we don't need to worry about this in the context of replication. Hmmm. The WAL records are written as commited ... this is why people get into 2PC if they want full synchrnous. Short of using 2PC, there is simply no way we can guarentee that the master and the standby won't get out of sync. And even 2PC isn't perfect. I think the best we can do is have the master abort the sessions and shutdown for a -fast. Yes, the clients are confused about what's been committed, but frequently that's the case with a -fast anyway. However, we need to give the user more information. I'd say that we need to have a specific error message associated with a synchronization failure around shutdown time. This error should be both returned to the clients, and logged. That way the DBA can decide what to do about the error, if anything. So, I'd say this is the way to go: Shutdown Smart: Wait for all pending standby transaction to clear. After 60 seconds, emit an error message on the shutdown console: NOTICE: pending replication transactions still waiting ... that way the DBA knows to move on to -fast Shutdown Fast: Wait for 1 second for all pending standby transactions to clear. If they don't clear, emit an error to both the shutdown console and the client consoles: WARNING: some transactions not replicated Send a commit message on the client consoles Shutdown. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] On-the-fly index tuple deletion vs. hot_standby
On Thu, Dec 09, 2010 at 09:48:25AM +, Simon Riggs wrote: On Fri, 2010-12-03 at 21:43 +0200, Heikki Linnakangas wrote: On 29.11.2010 08:10, Noah Misch wrote: I have a hot_standby system and use it to bear the load of various reporting queries that take 15-60 minutes each. In an effort to avoid long pauses in recovery, I set a vacuum_defer_cleanup_age constituting roughly three hours of the master's transactions. Even so, I kept seeing recovery pause for the duration of a long-running query. In each case, the culprit record was an XLOG_BTREE_DELETE arising from on-the-fly deletion of an index tuple. The attached test script demonstrates the behavior (on HEAD); the index tuple reclamation conflicts with a concurrent SELECT pg_sleep(600) on the standby. Since this inserting transaction aborts, HeapTupleSatisfiesVacuum reports HEAPTUPLE_DEAD independent of vacuum_defer_cleanup_age. We go ahead and remove the index tuples. On the standby, btree_xlog_delete_get_latestRemovedXid does not regard the inserting-transaction outcome, so btree_redo proceeds to conflict with snapshots having visibility over that transaction. Could we correctly improve this by teaching btree_xlog_delete_get_latestRemovedXid to ignore tuples of aborted transactions and tuples inserted and deleted within one transaction? @Noah Easily the best bug reported submitted in a long time. Thanks. Seems reasonable. HeapTupleHeaderAdvanceLatestRemovedXid() will need similar treatment. Actually, btree_xlog_delete_get_latestRemovedXid() could just call HeapTupleHeaderAdvanceLatestRemoveXid(). Yes, it applies to other cases also. Thanks for the suggestion. Fix committed. Please double-check my work, committed early since I'm about to jump on a plane. Thanks for making that change. For my understanding, why does the xmin == xmax special case in HeapTupleHeaderAdvanceLatestRemoveXid not require !HEAP_UPDATED, as the corresponding case in HeapTupleSatisfiesVacuum requires? I can neither think of a recipe for triggering a problem as the code stands, nor come up with a sound explanation for why no such recipe can exist. nm pgp96AuWNtkhu.pgp Description: PGP signature
Re: [HACKERS] Fwd: Extended query protocol and exact types matches.
2010/12/10 Robert Haas robertmh...@gmail.com On Fri, Dec 10, 2010 at 11:46 AM, Dmitriy Igrishin dmit...@gmail.com wrote: It would be quicker to answer my question and help than to teach me the alphabet of communication. Although, thank you, and for that :-) It would be quicker still to ignore your email altogether, but I'm guessing you're not going to recommend that I take that approach. Sure not ! I did look briefly at whether I could also answer the question while I was replying, but found that I wasn't really sure what you were asking (which may be why no one else responded either). I've got one useful respond before this post in gene...@. To the best of my ability to determine what you were actually asking, it was something along the lines of What will happen if I tell libpq that a parameter is a bigint when the server is expecting an int?. I don't happen to know the answer to that question without writing a 25-line program, compiling it, and testing it. Which wouldn't be very hard, but on the other hand it wouldn't be very hard for you to do it either. Best guess without testing? It'll work if the value is within the range that can be represented by an int and throw an error otherwise. Yes, I've already investigated it, compiled and tested. The first line of my initial post says: To be assured and just for calmness.. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- // Dmitriy.
Re: [HACKERS] initdb failure with Postgres 8.4.4
On 12/10/2010 11:19 AM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: So in theory we could have a GUC under file locations to override this, similarly to data_directory or hba_file or ident_file. But since it's been like this for a really long time (I think), I wouldn't be inclined to go monkeying with it unless more than one person complains. Even if we did see more than one request, I'd be inclined not to change it. The installation layout stuff is too complicated and fragile already; and AFAICS it does cover every *reasonable* use case. I agree, although I think Robert's really long time is in fact since 8.0, before which we didnt provide for relocation at all. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why percent_rank is so slower than rank?
Hitoshi Harada umi.tan...@gmail.com writes: Hm? Once percent_rank() scans to the partition end, any other window functions that scans row by row don't need to care the memory reduction, aren't they? Or more generally, if the partition was scanned to the end, we don't need to trim tuplestore anymore. Am I misunderstanding? Giving back the memory as we do the scan is still a good thing IMO; there might be other uses for it. In any case I don't see where you're going to put such a heuristic without breaking potentially interesting uses elsewhere. The tuplestore doesn't know anything about partitions being read to the end; and WindowAgg doesn't (or shouldn't) know about whether the tuplestore is all in memory. Furthermore, the performance problem would exist for any situation where the window functions had read far beyond the frame start, whether that was all the way to partition end or not. Consider a frame like ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING. In the end this is a local problem inside tuplestore, and kluging its callers to work around it is the wrong approach. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
On Fri, Dec 10, 2010 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not convinced that this is actually a requirement, or that doing it this specific way is a good solution. In particular, keeping the version number in the system catalogs seems pretty dubious. The common method for upgrading an already-installed contrib module just involves dropping in a new .so --- that's not going to change the system catalogs. It would likely be better to keep the version ID inside the .so file. This is an interesting point. There are really two things here: the .so version, and the version of the system catalog entries. For example, imagine that an extension provides a single function, called foo(). So we load up the .so and CREATE FUNCTION statement to match. Later, the extension is so successful that the author writes a second function, bar(). The new .so can (at least possibly) be used with the old schema definitions, but the new schema definitions aren't compatible with the old .so. The logical upgrade process is to swap out the .so first, and then add update the catalog definitions. On the other hand, if you were dropping a deprecated function, you'd need to do the steps in reverse order. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
Robert Haas robertmh...@gmail.com writes: On Fri, Dec 10, 2010 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: ... In particular, keeping the version number in the system catalogs seems pretty dubious. The common method for upgrading an already-installed contrib module just involves dropping in a new .so --- that's not going to change the system catalogs. It would likely be better to keep the version ID inside the .so file. This is an interesting point. There are really two things here: the .so version, and the version of the system catalog entries. True. Consider a situation like an RPM upgrade: it's going to drop in a new .so version, *and nothing else*. It's pure fantasy to imagine that the RPM script is going to find all your databases and execute some SQL commands against them. Since a large number of bug-fix cases do require only a .so update, not being able to track the .so version seems like it's missing most of the argument for having version tracking at all. (In the RPM case, the RPM infrastructure would be able to tell you which version you had installed, so I'm not sold that PG needs to duplicate that.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
On Dec 10, 2010, at 10:20 AM, Tom Lane wrote: True. Consider a situation like an RPM upgrade: it's going to drop in a new .so version, *and nothing else*. It's pure fantasy to imagine that the RPM script is going to find all your databases and execute some SQL commands against them. Since a large number of bug-fix cases do require only a .so update, not being able to track the .so version seems like it's missing most of the argument for having version tracking at all. Sometimes there will be changes to the SQL, too. How does that work with CREATE EXTENSION? Do I install the upgrade, then run CREATE EXTENSION to get the latest SQL script to run? But then all the objects already exist… Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
Tom Lane t...@sss.pgh.pa.us writes:
This doesn't answer my question of why it couldn't be done the other
way. Why does the makefile need to know it? If it does need to know
it, couldn't it get it out of the control file instead of vice versa?
Well the Makefile support is just a facility to fill in the control file
automatically for you, on the grounds that you're probably already
maintaining your version number in the Makefile. Or that it's easy to
get it there, as in:
EXTVERSION = $(shell dpkg-parsechangelog | awk -F '[:-]' '/^Version:/ { print
substr($$2, 2) }')
That comes from a real world example that's yet to be adapted to being
an extension in 9.1, but still:
https://github.com/dimitri/pgfincore/blob/debian/Makefile
I'm not convinced that this is actually a requirement, or that doing it
this specific way is a good solution. In particular, keeping the
version number in the system catalogs seems pretty dubious. The common
method for upgrading an already-installed contrib module just involves
dropping in a new .so --- that's not going to change the system
catalogs. It would likely be better to keep the version ID inside the
.so file.
Upgrade are left for a future patch, did we decide. Still, it seems to
me that we will support some upgrade scripts so that author can decide
what to do knowing current and next version, and yes, knowing that the
module has already been taken care of by the OS-level packaging.
That means some extensions upgrades will break the database between the
OS-level package upgrade and the sql upgrade (support to come), but in
my experience that's seldom the case. And not by chance.
So in the case that only the module (.so) needs upgrading, we would
still provide for an upgrade path in the script / sql support so that
the version number has a chance of being upgraded too. As you say in
another mail, of course, the OS packaging system will not forcibly be
willing to care for that all by itself. I can imagine debian offering
the choice to the users and acting accordingly, though.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb failure with Postgres 8.4.4
On 12/10/2010 11:19 AM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: So in theory we could have a GUC under file locations to override this, similarly to data_directory or hba_file or ident_file. But since it's been like this for a really long time (I think), I wouldn't be inclined to go monkeying with it unless more than one person complains. Even if we did see more than one request, I'd be inclined not to change it. The installation layout stuff is too complicated and fragile already; and AFAICS it does cover every *reasonable* use case. I agree, although I think Robert's really long time is in fact since 8.0, before which we didnt provide for relocation at all. cheers andrew -- Thanks, everyone!!! I'm still not sure I completely understand how to package all components to allow independent installation, but I guess this is what this weekend is good for :( Michael This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be confidential and/or privileged. If you are not one of the named recipients or have received this email in error, (i) you should not read, disclose, or copy it, (ii) please notify sender of your receipt by reply email and delete this email and all attachments, (iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this email.For other languages, go to http://www.3ds.com/terms/email-disclaimer. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
On Dec 10, 2010, at 11:28 AM, Dimitri Fontaine wrote:
Well the Makefile support is just a facility to fill in the control file
automatically for you, on the grounds that you're probably already
maintaining your version number in the Makefile. Or that it's easy to
get it there, as in:
EXTVERSION = $(shell dpkg-parsechangelog | awk -F '[:-]' '/^Version:/ { print
substr($$2, 2) }')
That comes from a real world example that's yet to be adapted to being
an extension in 9.1, but still:
https://github.com/dimitri/pgfincore/blob/debian/Makefile
I use that in pgTAP, too (line 23):
https://github.com/theory/pgtap/blob/master/Makefile
But I don't need core to support that. Frankly, if we're not going to generate
the control file from Makefile variables, then I'd rather not have any control
file Makefile variables at all.
Upgrade are left for a future patch, did we decide. Still, it seems to
me that we will support some upgrade scripts so that author can decide
what to do knowing current and next version, and yes, knowing that the
module has already been taken care of by the OS-level packaging.
Yeah, this will be needed ASAP.
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
David E. Wheeler da...@kineticode.com writes: On Dec 10, 2010, at 11:28 AM, Dimitri Fontaine wrote: Upgrade are left for a future patch, did we decide. Still, it seems to me that we will support some upgrade scripts so that author can decide what to do knowing current and next version, and yes, knowing that the module has already been taken care of by the OS-level packaging. Yeah, this will be needed ASAP. I don't mind if we don't have an implementation of upgrade cases in hand. But we had better have a design in hand, to make sure what we're doing now doesn't foreclose upgrade cases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Tom Lane t...@sss.pgh.pa.us writes: This doesn't answer my question of why it couldn't be done the other way. Why does the makefile need to know it? If it does need to know it, couldn't it get it out of the control file instead of vice versa? Well the Makefile support is just a facility to fill in the control file automatically for you, on the grounds that you're probably already maintaining your version number in the Makefile. Why would you choose to maintain it in the Makefile? In most cases makefiles are the least likely thing to be changing during a minor update. I would think that the right place for it is in the C code (if we're trying to version .so files) or the .sql file, if we're trying to version the SQL objects. In particular, if the only reason the makefile needs to know it is to inject it into the control file, it seems completely silly to not just maintain it in the control file instead. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions, patch v16
On Dec 10, 2010, at 11:47 AM, Tom Lane wrote: Why would you choose to maintain it in the Makefile? In most cases makefiles are the least likely thing to be changing during a minor update. I would think that the right place for it is in the C code (if we're trying to version .so files) or the .sql file, if we're trying to version the SQL objects. In particular, if the only reason the makefile needs to know it is to inject it into the control file, it seems completely silly to not just maintain it in the control file instead. +1 David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
ALTER EXTENSION ... UPGRADE; (was: [HACKERS] Extensions, patch v16)
Tom Lane t...@sss.pgh.pa.us writes: Why would you choose to maintain it in the Makefile? In most cases makefiles are the least likely thing to be changing during a minor update. I must have a packager skewed view of things here, but ok, point noted. I would think that the right place for it is in the C code (if we're trying to version .so files) or the .sql file, if we're trying to version the SQL objects. In particular, if the only reason the makefile needs to know it is to inject it into the control file, it seems completely silly to not just maintain it in the control file instead. If we are after the a bare feature set, sure, the Makefile facility is only about trying to be nice to the user. I accept that you think it's counter productive rather than helping. In the next patch, I will rename the control files to be straight .control files, remove the implicit rule in the pgxs.mk file, remove the note in the documentation and apply the following: sed -i 's/EXTVERSION/9.1devel/' contrib/*/*.control Or do we want contrib's specific version numbers that are not all the same as the current PostgreSQL version number? On to your question about the upgrade design, in order not to paint ourselves into a corner. What I now have in mind is the following: When there's an extension upgrade the user will have to install the new files (.so, .sql, .control) and run an upgrade command in his databases: ALTER EXTENSION pair UPGRADE; The version we upgrade from is known from the catalog, the version we upgrade to is read in the control file. So we are able to call the sql script and offer a way for it to know about the versions. The simplest way seems to be a new pair of functions: pg_extension_upgrade() returns bool pg_extension_versions() returns table(current text, next text) Those are to be run only from the extension's script. The first returns false when the user did CREATE EXTENSION and true when the user did ALTER EXTENSION UPGRADE, which are the only two commands that will run the script. The second will return the versions we detailed above, and the extension's author is free to compare them however he wants to and decide what to do now. It's cool that we have DO blocks here, and pg_execute_sql_file() to offer the same facility as \i for psql scripts. Of course if calling the script succeeds, then the version number in the pg_extension catalog is changed to the next one. Now, it would be better if it were easy to compare version numbers, for example with a -core datatype that handles that. Do we already want to open this can of worms? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
Most of you already know I am new to this list and newer to any OSS development. However, while browsing the source code (of 9.0.1) I find that there is only one way to store relations on disk - the magnetic disk. The fact that it's called md.c is a hangover from the '80s. These days, the logic that the Berkeley guys envisioned being at that code level is generally in kernel device drivers. md.c can drive anything that behaves as a block device + filesystem, which is pretty much everything of interest. I believe that PostgreSQL was been developed and optimized for sequential access. To get full advantage of SSDs its necessary to rewrite almost the whole project - there are so much code written with the sequential mechanism in mind. -- Daniel Loureiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On 12/10/10 12:17 PM, Dimitri Fontaine wrote: Or do we want contrib's specific version numbers that are not all the same as the current PostgreSQL version number? I think that each contrib needs its own version numbers. The reason being that most minor updates don't touch contrib. Also, once extensions and pgxn are operating full swing, I see contrib going away anyway ... -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] create tablespace fails silently, or succeeds improperly
I'm finally getting around to something that's been on my todo list for a couple of months. I wrote: Basically, I'm thinking that given CREATE TABLESPACE LOCATION '/foo/bar' the creation and properties of /foo/bar/PG_9.0_201004261 ought to be handled *exactly* the way that the -D target directory of initdb is. We have more than ten years experience behind the assertion that we're dealing with that case in a good way. We should transfer that behavior over to tablespace directories rather than inventing something that works a shade differently. Barring objections, I'll go make it work that way in HEAD and 9.0. Looking at initdb, there's a couple of hundred lines worth of code involved here. Some of it is not directly sharable with the backend because of the way it manages error cases, but at least the two functions mkdir_p() and check_data_dir() could conceivably be put into src/port/. The former is about 100 lines and the latter about 50. Is sharing them worth doing, or should I just copy-and-paste into commands/tablespace.c? If we're not sharing mkdir_p in toto, I'd be inclined to not bother with duplicating initdb's willingness to create parent directories --- it's not clear to me that that's very sensible for a tablespace creation command anyway. Another question is whether we're really hot enough about this to back-patch the change into 9.0. Given the lack of other complaints since October, maybe we shouldn't take any risk here. Messing around with new modules in src/port/ would be more appetizing if it's HEAD only. Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED - file_fdw
On 12/04/2010 11:11 PM, Itagaki Takahiro wrote: On Sun, Dec 5, 2010 at 07:24, Andrew Dunstanand...@dunslane.net wrote: Looking at file_parser.c, it seems to be largely taken from copy.c. Wouldn't it be better to call those functions, or refactor them so they are callable if necessary? We could export private functions and structs in copy.c, though details of the implementation should be kept in copy.c. How about splitting the file_fdw patch into two pieces? One exports the copy functions from the core, and another implements file_fdw using the infrastructure. Who is actually going to do this split? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
Josh Berkus j...@agliodbs.com writes: On 12/10/10 12:17 PM, Dimitri Fontaine wrote: Or do we want contrib's specific version numbers that are not all the same as the current PostgreSQL version number? I think that each contrib needs its own version numbers. The reason being that most minor updates don't touch contrib. Certainly extensions that aren't part of contrib would need separate version numbers. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
Josh Berkus j...@agliodbs.com writes: I think that each contrib needs its own version numbers. The reason being that most minor updates don't touch contrib. Fair enough. What are the version numbers of each current contribs? Also, once extensions and pgxn are operating full swing, I see contrib going away anyway ... No, not all of them. Most of them are in the tree as show cases or for core developers to easily check they just didn't break an important part of the system from an external viewpoint, or to give examples on how to upgrade external extension code between major releases. The part that will drop in interest is the one where customers are not trusting the extension mechanism and third-party software enough to grant them landing into their production environments. Maybe. Given some years and a good track record. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On 12/10/2010 03:24 PM, Josh Berkus wrote: Also, once extensions and pgxn are operating full swing, I see contrib going away anyway ... We've heard this before, but I'm still quite skeptical about it. Quite apart from anything else we should keep enough extensions in core to test the extension mechanism, as well as to provide examples as part of the base distribution. Some (e.g. hstore and citext) should probably move into core. Others like pgcrypto are probably in just the right place as they are. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Fri, 2010-12-10 at 15:42 -0500, Andrew Dunstan wrote: On 12/10/2010 03:24 PM, Josh Berkus wrote: Also, once extensions and pgxn are operating full swing, I see contrib going away anyway ... We've heard this before, but I'm still quite skeptical about it. Quite apart from anything else we should keep enough extensions in core to test the extension mechanism, as well as to provide examples as part of the base distribution. Some (e.g. hstore and citext) should probably move into core. Others like pgcrypto are probably in just the right place as they are. I hope that contrib goes away. I agree with your assertion that things like hstore and citext shoudl be in core but it is my hope that with extensions and pgxn, there will be no reason for contrib to exist at all. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER EXTENSION ... UPGRADE; (was: [HACKERS] Extensions, patch v16)
Dimitri Fontaine dimi...@2ndquadrant.fr writes: On to your question about the upgrade design, in order not to paint ourselves into a corner. What I now have in mind is the following: When there's an extension upgrade the user will have to install the new files (.so, .sql, .control) and run an upgrade command in his databases: ALTER EXTENSION pair UPGRADE; The version we upgrade from is known from the catalog, the version we upgrade to is read in the control file. So we are able to call the sql script and offer a way for it to know about the versions. The simplest way seems to be a new pair of functions: pg_extension_upgrade() returns bool pg_extension_versions() returns table(current text, next text) Those are to be run only from the extension's script. The first returns false when the user did CREATE EXTENSION and true when the user did ALTER EXTENSION UPGRADE, which are the only two commands that will run the script. The second will return the versions we detailed above, and the extension's author is free to compare them however he wants to and decide what to do now. It's cool that we have DO blocks here, and pg_execute_sql_file() to offer the same facility as \i for psql scripts. Hmm ... I don't believe that extension SQL scripts should rely on DO blocks. There is no requirement that plpgsql be installed, and we're not going to create one as part of this feature. What this means is that the design you offer above doesn't work at all, since it fundamentally assumes that the SQL script can do conditional logic. What's more, it fundamentally assumes that the script WILL do conditional logic and support (in one lump) every possible combination of versions. That's going to turn into buggy spaghetti-code very quickly. I think that something that could work is more along the lines of the extension containing different upgrade scripts for whatever set of cases the author feels like supporting; for example the foo extension might provide both foo_upgrade.11.13.sql foo_upgrade.12.13.sql if the author is willing to support one-step upgrades from two preceding versions to version 13. It would then be the responsibility of the ALTER EXTENSION code to select and execute the correct upgrade script. A missing script would be reported as an upgrade failure by ALTER EXTENSION. (Actually, we could probably assume that the target version is implicitly the current version, as identified from the control file, and omit that from the script file names. That would avoid ambiguity if version numbers can have more than one part.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
Joshua D. Drake j...@commandprompt.com writes: On Fri, 2010-12-10 at 15:42 -0500, Andrew Dunstan wrote: On 12/10/2010 03:24 PM, Josh Berkus wrote: Also, once extensions and pgxn are operating full swing, I see contrib going away anyway ... We've heard this before, but I'm still quite skeptical about it. Quite apart from anything else we should keep enough extensions in core to test the extension mechanism, as well as to provide examples as part of the base distribution. Some (e.g. hstore and citext) should probably move into core. Others like pgcrypto are probably in just the right place as they are. I hope that contrib goes away. I agree with your assertion that things like hstore and citext shoudl be in core but it is my hope that with extensions and pgxn, there will be no reason for contrib to exist at all. I agree with Andrew --- we're going to need a collection of standard extensions if only for testing purposes. It may someday not be called contrib, but it'll still be there. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
Tom Lane t...@sss.pgh.pa.us writes:
I don't believe that extension SQL scripts should rely on DO blocks.
There is no requirement that plpgsql be installed, and we're not going
to create one as part of this feature. What this means is that the
design you offer above doesn't work at all, since it fundamentally
assumes that the SQL script can do conditional logic. What's more,
it fundamentally assumes that the script WILL do conditional logic
and support (in one lump) every possible combination of versions.
That's going to turn into buggy spaghetti-code very quickly.
Yeah, I was picturing a main script that calls other ones by means of
PERFORM pg_execute_from_file('upgrade_script.sql');
Of course if plpgsql is not to be a requirement, the DO blocks hosting
the CASE logic won't fly and all that blows away.
I think that something that could work is more along the lines of the
extension containing different upgrade scripts for whatever set of cases
the author feels like supporting; for example the foo extension might
provide both
foo_upgrade.11.13.sql
foo_upgrade.12.13.sql
if the author is willing to support one-step upgrades from two preceding
versions to version 13. It would then be the responsibility of the
ALTER EXTENSION code to select and execute the correct upgrade script.
A missing script would be reported as an upgrade failure by ALTER
EXTENSION.
(Actually, we could probably assume that the target version is
implicitly the current version, as identified from the control file,
and omit that from the script file names. That would avoid ambiguity
if version numbers can have more than one part.)
I don't think we can safely design around one part version numbers here,
because I'm yet to see that happening in any extension I've had my hands
on, which means a few already, as you can imagine.
Now, what about having the control file host an 'upgrade' property where
to put the script name? We would have to support a way for this filename
to depend on the already installed version, I'm thinking that %v might
be the easiest here (read: I want to avoid depending on any version
scheme).
version = '13'
script = 'foo.sql'
upgrade = 'foo_upgrade.%v.13.sql'
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On 12/10/10 12:34 PM, Dimitri Fontaine wrote: Josh Berkus j...@agliodbs.com writes: I think that each contrib needs its own version numbers. The reason being that most minor updates don't touch contrib. Fair enough. What are the version numbers of each current contribs? I'd say that for anything in /contrib, it gets a new version with each major version of postgresql, but not with each minor version. Thus, say, dblink when 9.1.0 is release would be dblink 9.1-1. If in 9.1.4 we fix a bug in dblink, then it becomes dblink 9.1-2. This is confusing from a version number perpsective, but it prevents admins from having to run extension upgrades when nothing has changed. The alternative would be to match postgresql minor version numbering exactly, and then come up with some way to have a no-op upgrade in the frequent cases where the contrib module isn't changed during a minor release. This would also require some kind of upgrade all command for contrib. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
Josh Berkus j...@agliodbs.com writes: The alternative would be to match postgresql minor version numbering exactly, and then come up with some way to have a no-op upgrade in the frequent cases where the contrib module isn't changed during a minor release. This would also require some kind of upgrade all command for contrib. That's as easy as having non-continuous version numbering. In your example, we get from dblink version 9.1.0 to 9.1.4, but the 3 releases before that it remains dblink 9.1.0. Would it cut it? -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Fri, Dec 10, 2010 at 4:50 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Now, what about having the control file host an 'upgrade' property where to put the script name? We would have to support a way for this filename to depend on the already installed version, I'm thinking that %v might be the easiest here (read: I want to avoid depending on any version scheme). version = '13' script = 'foo.sql' upgrade = 'foo_upgrade.%v.13.sql' If I was linking of putting bundling my utiliites up as an extension (yes, I would that from a packaging/DB management perspective), I think I'ld like a control like that, but with a bit of a wildcard version matching, something like: version = '3.12' upgrade-1. = 'utils-upgrade-1.0.sql' upgrade-2. = 'utils-upgrade-2..0.sql upgrade-3. = 'nothing' I'm thinking of a scheme where the upgrade-$VERSION uses a prefix match, so 1.1, 1.2, 1.3 would all be matched by 1.. The 3.=nothing is some way of specifing you don't need to do anything, becuase my n.X release are all compatible sql-so wise. They would only be bug fixes if I did something wrong in my stuff.. Anything not compatible woudl bump the first number. If it's a prefix type match, then the PG versionins woudl work too, for intsance: upgrade-9.0.=... would match any pg 9.0.* I guess you could use SQL like if that' more consitent... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
On Fri, Dec 10, 2010 at 12:21 PM, Daniel Loureiro loureir...@gmail.com wrote: Most of you already know I am new to this list and newer to any OSS development. However, while browsing the source code (of 9.0.1) I find that there is only one way to store relations on disk - the magnetic disk. The fact that it's called md.c is a hangover from the '80s. These days, the logic that the Berkeley guys envisioned being at that code level is generally in kernel device drivers. md.c can drive anything that behaves as a block device + filesystem, which is pretty much everything of interest. I believe that PostgreSQL was been developed and optimized for sequential access. To get full advantage of SSDs its necessary to rewrite almost the whole project - there are so much code written with the sequential mechanism in mind. I don't think that that is true at all. If you tell the planner that a random page and a sequential page have the same cost, does it not believe you? Of course if you do a full table scan because their are no better options, then it scans sequentially. But you have to scan the pages in *some* order, and it is hard to see how something other than sequential would be systematically better. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Dec 10, 2010, at 1:55 PM, Josh Berkus wrote: I'd say that for anything in /contrib, it gets a new version with each major version of postgresql, but not with each minor version. Thus, say, dblink when 9.1.0 is release would be dblink 9.1-1. If in 9.1.4 we fix a bug in dblink, then it becomes dblink 9.1-2. Please don't add - to version numbers. This is confusing from a version number perpsective, but it prevents admins from having to run extension upgrades when nothing has changed. The alternative would be to match postgresql minor version numbering exactly, and then come up with some way to have a no-op upgrade in the frequent cases where the contrib module isn't changed during a minor release. This would also require some kind of upgrade all command for contrib. +1 David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Dec 10, 2010, at 1:50 PM, Dimitri Fontaine wrote: (Actually, we could probably assume that the target version is implicitly the current version, as identified from the control file, and omit that from the script file names. That would avoid ambiguity if version numbers can have more than one part.) I don't think we can safely design around one part version numbers here, because I'm yet to see that happening in any extension I've had my hands on, which means a few already, as you can imagine. Why not? Simplest thing, to my mind, is to have upgrade/foo-1.12.sql upgrade/foo-1.13.sql upgrade/foo-1.15.sql Since you know the existing version number, you just run all that come after. For example, if the current version is 1.12, then you know to run foo-1.13.sql and foo-1.15.sql. Now, what about having the control file host an 'upgrade' property where to put the script name? We would have to support a way for this filename to depend on the already installed version, I'm thinking that %v might be the easiest here (read: I want to avoid depending on any version scheme). version = '13' script = 'foo.sql' upgrade = 'foo_upgrade.%v.13.sql' I think that's way more complicated than necessary. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
David E. Wheeler da...@kineticode.com writes: On Dec 10, 2010, at 1:50 PM, Dimitri Fontaine wrote: I don't think we can safely design around one part version numbers here, because I'm yet to see that happening in any extension I've had my hands on, which means a few already, as you can imagine. Why not? Simplest thing, to my mind, is to have upgrade/foo-1.12.sql upgrade/foo-1.13.sql upgrade/foo-1.15.sql Since when is 1.12 a one part version number? :) Since you know the existing version number, you just run all that come after. For example, if the current version is 1.12, then you know to run foo-1.13.sql and foo-1.15.sql. I don't think imposing what version numbers must look like and what the separators in the file names should be is a good idea. version = '13' script = 'foo.sql' upgrade = 'foo_upgrade.%v.13.sql' I think that's way more complicated than necessary. It's just moving the complexity from the rules for the user to obey to having them explain us by which rules they're playing. I personally very much prefer the later, as you can imagine. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Dec 10, 2010, at 2:32 PM, Dimitri Fontaine wrote: David E. Wheeler da...@kineticode.com writes: On Dec 10, 2010, at 1:50 PM, Dimitri Fontaine wrote: I don't think we can safely design around one part version numbers here, because I'm yet to see that happening in any extension I've had my hands on, which means a few already, as you can imagine. Why not? Simplest thing, to my mind, is to have upgrade/foo-1.12.sql upgrade/foo-1.13.sql upgrade/foo-1.15.sql Since when is 1.12 a one part version number? :) What difference does it make how many parts there are? If it's a naming convention, you just match /$extension-(.+?)\.sql$/. Simple. Since you know the existing version number, you just run all that come after. For example, if the current version is 1.12, then you know to run foo-1.13.sql and foo-1.15.sql. I don't think imposing what version numbers must look like and what the separators in the file names should be is a good idea. The version numbers can be anything, so long as there *are* version numbers. And the rest of the file name should be just like the extension. It's just moving the complexity from the rules for the user to obey to having them explain us by which rules they're playing. I personally very much prefer the later, as you can imagine. You keep making extension authors have to do more work. I keep trying to make it so they can do less. We want the barrier to be as low as possible, which means a lot of DRY. Make it *possible* to do more complicated things, but don't *require* it. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
David E. Wheeler da...@kineticode.com writes: On Dec 10, 2010, at 1:50 PM, Dimitri Fontaine wrote: (Actually, we could probably assume that the target version is implicitly the current version, as identified from the control file, and omit that from the script file names. That would avoid ambiguity if version numbers can have more than one part.) I don't think we can safely design around one part version numbers here, because I'm yet to see that happening in any extension I've had my hands on, which means a few already, as you can imagine. Why not? Simplest thing, to my mind, is to have upgrade/foo-1.12.sql upgrade/foo-1.13.sql upgrade/foo-1.15.sql Since you know the existing version number, you just run all that come after. For example, if the current version is 1.12, then you know to run foo-1.13.sql and foo-1.15.sql. If we assume the target is the current version, then we only need the old-version number in the file name, so it doesn't matter how many parts it has. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
David E. Wheeler da...@kineticode.com writes: You keep making extension authors have to do more work. I keep trying to make it so they can do less. We want the barrier to be as low as possible, which means a lot of DRY. Make it *possible* to do more complicated things, but don't *require* it. Sorry, imposing that - ain't part of the version number string won't make any impression on me as far as getting simple is concerned. Go find a single debian package not having - in its version number, and that's a native software (developed to build debian). For details, see the following, then explain me how RPM is so differently simple, and then why I should care. http://www.debian.org/doc/debian-policy/ch-controlfields.html#s-f-Version So really, using %v to say put the current version number here does not seem like a problem for me, it allows me not to have to think about *any* files naming rules nor version numbering scheme. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
Josh Berkus j...@agliodbs.com writes: I'd say that for anything in /contrib, it gets a new version with each major version of postgresql, but not with each minor version. Thus, say, dblink when 9.1.0 is release would be dblink 9.1-1. If in 9.1.4 we fix a bug in dblink, then it becomes dblink 9.1-2. ... The alternative would be to match postgresql minor version numbering exactly, and then come up with some way to have a no-op upgrade in the frequent cases where the contrib module isn't changed during a minor release. This would also require some kind of upgrade all command for contrib. 99% of the time, fix a bug just means some C code changes. We should not force DBAs to go through special upgrade commands unless there is some change in the SQL objects created by the extension --- and just as we discourage changes in the SQL objects created by the core during minor releases, we should discourage such changes in minor extension updates. So the case where ALTER EXTENSION UPGRADE is needed will be the exception not the rule. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Dec 10, 2010, at 2:40 PM, Tom Lane wrote: Since you know the existing version number, you just run all that come after. For example, if the current version is 1.12, then you know to run foo-1.13.sql and foo-1.15.sql. If we assume the target is the current version, then we only need the old-version number in the file name, so it doesn't matter how many parts it has. Exactly. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
Jeff Janes jeff.ja...@gmail.com writes: Of course if you do a full table scan because their are no better options, then it scans sequentially. But you have to scan the pages in *some* order, and it is hard to see how something other than sequential would be systematically better. In fact, if sequential *isn't* the best order for reading the whole file, the filesystem has lost its marbles completely; because that is the order in which most files are read, so files ought to be laid out on disk (or whatever storage device) to be read most quickly that way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Dec 10, 2010, at 2:43 PM, Dimitri Fontaine wrote: David E. Wheeler da...@kineticode.com writes: You keep making extension authors have to do more work. I keep trying to make it so they can do less. We want the barrier to be as low as possible, which means a lot of DRY. Make it *possible* to do more complicated things, but don't *require* it. Sorry, imposing that - ain't part of the version number string won't make any impression on me as far as getting simple is concerned. Go find a single debian package not having - in its version number, and that's a native software (developed to build debian). I'm making no such imposition. I'd rather it not be in contrib version numbers, because they should adhere to PostgreSQL-standard version numbering IMHO. YOu can use any characters you want in the version string. The upgrade file names simply start with $extension-, so the format is $extension-$version.sql. That's it. For details, see the following, then explain me how RPM is so differently simple, and then why I should care. http://www.debian.org/doc/debian-policy/ch-controlfields.html#s-f-Version So really, using %v to say put the current version number here does not seem like a problem for me, it allows me not to have to think about *any* files naming rules nor version numbering scheme. It's just not necessary. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] create tablespace fails silently, or succeeds improperly
On Fri, Dec 10, 2010 at 3:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm finally getting around to something that's been on my todo list for a couple of months. I wrote: Basically, I'm thinking that given CREATE TABLESPACE LOCATION '/foo/bar' the creation and properties of /foo/bar/PG_9.0_201004261 ought to be handled *exactly* the way that the -D target directory of initdb is. We have more than ten years experience behind the assertion that we're dealing with that case in a good way. We should transfer that behavior over to tablespace directories rather than inventing something that works a shade differently. Barring objections, I'll go make it work that way in HEAD and 9.0. Looking at initdb, there's a couple of hundred lines worth of code involved here. Some of it is not directly sharable with the backend because of the way it manages error cases, but at least the two functions mkdir_p() and check_data_dir() could conceivably be put into src/port/. The former is about 100 lines and the latter about 50. Is sharing them worth doing, or should I just copy-and-paste into commands/tablespace.c? If we're not sharing mkdir_p in toto, I'd be inclined to not bother with duplicating initdb's willingness to create parent directories --- it's not clear to me that that's very sensible for a tablespace creation command anyway. +1 for src/port. Another question is whether we're really hot enough about this to back-patch the change into 9.0. Given the lack of other complaints since October, maybe we shouldn't take any risk here. Messing around with new modules in src/port/ would be more appetizing if it's HEAD only. Thoughts? At the moment, I'm not feeling hot to back-patch this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
Tom Lane t...@sss.pgh.pa.us writes: If we assume the target is the current version, then we only need the old-version number in the file name, so it doesn't matter how many parts it has. IIUC, that puts even more work on the shoulders of the extension authors, because the file named foo-1.12.sql is the one used to upgrade from 1.12. That means that at each release, it's a different file content, it's there to upgrade to a newer release. Well it works too, of course, and we don't care how many dashes we find in the filename, it's extension-version.sql. I'd be ok with that too. So, we have a sound proposal for the ALTER EXTENSION UPGRADE command, which comes later. So we keep version numbers in the CREATE EXTENSION patch and the control files, and remove the facility to get this number from the Makefile. Is that right? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
Dimitri Fontaine dimi...@2ndquadrant.fr writes: David E. Wheeler da...@kineticode.com writes: You keep making extension authors have to do more work. I keep trying to make it so they can do less. We want the barrier to be as low as possible, which means a lot of DRY. Make it *possible* to do more complicated things, but don't *require* it. So really, using %v to say put the current version number here does not seem like a problem for me, it allows me not to have to think about *any* files naming rules nor version numbering scheme. Maybe I misread David's meaning, but I thought he was saying that there's no value in inventing all those control file entries in the first place. Just hard-wire in ALTER EXTENSION UPGRADE the convention that the name of an upgrade script to upgrade from prior version VVV is EXTNAME-upgrade.VVV.sql (or any variant spelling of that you care for). What is the point of letting/making extension authors invent their own naming schemes? That has no benefit that I can perceive, and the disadvantage that lack of uniformity will confuse users. As for the question of what characters should be expected in version numbers, +1 for digits and dots only. There's no good reason for something else. Even the Debian document you quote points out that hyphens in upstream version numbers give them problems, and Red Hat style packaging rules flat out disallow hyphens. (hyphen-something is for the packager to use, not the upstream software.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Dec 10, 2010, at 2:55 PM, Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: If we assume the target is the current version, then we only need the old-version number in the file name, so it doesn't matter how many parts it has. IIUC, that puts even more work on the shoulders of the extension authors, because the file named foo-1.12.sql is the one used to upgrade from 1.12. That means that at each release, it's a different file content, it's there to upgrade to a newer release. Yeah, it should be *to* 1.12. FWIW, this is how Bricolage upgrade scripts are handled: version-string-named directories with the appropriate scripts to upgrade *to* the named version number. So, we have a sound proposal for the ALTER EXTENSION UPGRADE command, which comes later. So we keep version numbers in the CREATE EXTENSION patch and the control files, and remove the facility to get this number from the Makefile. Is that right? Yes. No new variables in Makefile at all IIUC. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Dec 10, 2010, at 2:58 PM, Tom Lane wrote: Maybe I misread David's meaning, but I thought he was saying that there's no value in inventing all those control file entries in the first place. Just hard-wire in ALTER EXTENSION UPGRADE the convention that the name of an upgrade script to upgrade from prior version VVV is EXTNAME-upgrade.VVV.sql (or any variant spelling of that you care for). What is the point of letting/making extension authors invent their own naming schemes? That has no benefit that I can perceive, and the disadvantage that lack of uniformity will confuse users. Yes, except that the version number in the file name should be the version it upgrades *to*, not *from*. As for the question of what characters should be expected in version numbers, +1 for digits and dots only. There's no good reason for something else. Even the Debian document you quote points out that hyphens in upstream version numbers give them problems, and Red Hat style packaging rules flat out disallow hyphens. (hyphen-something is for the packager to use, not the upstream software.) I've mandated semantic versions for PGXN, mainly because it's simple and because it's close enough to the version numbers used in core. http://semver.org/ If we're going to be comparing version strings in file names, we'll need *something* to use to compare what's higher than another number. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] create tablespace fails silently, or succeeds improperly
Robert Haas robertmh...@gmail.com writes: +1 for src/port. ... At the moment, I'm not feeling hot to back-patch this. Yeah, that squares with my feelings. Will go do it that way, unless other people object. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
David E. Wheeler da...@kineticode.com writes: Yeah, it should be *to* 1.12. FWIW, this is how Bricolage upgrade scripts are handled: version-string-named directories with the appropriate scripts to upgrade *to* the named version number. But you still have to know what you're upgrading *from*. If we use subdirectories then it'd work to put one number in the subdir name and the other in the file name. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
I believe that PostgreSQL was been developed and optimized for sequential access. To get full advantage of SSDs its necessary to rewrite almost the whole project - there are so much code written with the sequential mechanism in mind. You can believe whatever you want, that doesn't make it true. Unless you have some kind of hard data that SSD data access is somehow *qualitatively* different from SAS data access, then you're just engaging in idle water-cooler speculation. Plenty of vendors launched products based on the supposed revolutionary nature of SSDs when they first came out. All have failed. SSDs are just faster disks, that's all. Their ratio of random-access to sequential might be less than 4.0, but it's not 1.0. Heck, even RAM isn't 1.0. I'm also involved with the Redis project, which is an in-memory database. Even for a pure-RAM database, it turns out that just using linked lists and 100% random access is slower than accessing page images. I use SSDs for many PostgreSQL instances. They work great. No changes to PostgreSQL were required other than adjusting random_page_cost down to 2.0 (this number could use exhaustive testing, but seems to work pretty well right now). -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] create tablespace fails silently, or succeeds improperly
On 12/10/2010 06:01 PM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: +1 for src/port. ... At the moment, I'm not feeling hot to back-patch this. Yeah, that squares with my feelings. Will go do it that way, unless other people object. I think this is the sensible way to go. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] would hw acceleration help postgres (databases in general) ?
Hello hackers, I think i'm at the right place to ask this question. Based on your experience and the fact that you have written the Postgres code, can you tell what a rough break-down - in your opinion - is for the time the database spends time just fetching and writing stuff to memory and the actual computation. The reason i ask this is because off-late there has been a push to put reconfigurable hardware on processor cores. What this means is that database writers can possibly identify the compute-intensive portions of the code and write hardware accelerators and/or custom instructions and offload computation to these hardware accelerators which they would have programmed onto the FPGA. There is not much utility in doing this if there aren't considerable compute- intensive operations in the database (which i would be surprise if true ). I would suspect joins, complex queries etc may be very compute-intensive. Please correct me if i'm wrong. Moreover, if you were told that you have a reconfigurable hardware which can perform pretty complex computations 10x faster than the base, would you think about synthesizing it directly on an fpga and use it ? I'd be more than glad to hear your guesstimates. Thanks alot ! Hamza -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
On Fri, 2010-12-10 at 15:08 -0800, Josh Berkus wrote: I believe that PostgreSQL was been developed and optimized for sequential access. To get full advantage of SSDs its necessary to rewrite almost the whole project - there are so much code written with the sequential mechanism in mind. You can believe whatever you want, that doesn't make it true. Or more productively. Actually, the only (that I know of) optimized for sequential access code we have would be for the xlogs. All of the page writing within the cluster would be random, as would all logging outside of the WAL itself. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
On Fri, Dec 10, 2010 at 6:08 PM, Josh Berkus j...@agliodbs.com wrote: Heck, even RAM isn't 1.0. I'm also involved with the Redis project, which is an in-memory database. Even for a pure-RAM database, it turns out that just using linked lists and 100% random access is slower than accessing page images. That's a slightly different problem, though. Sequential vs. random access is about whether fetching pages n, n+1, n+2, ... is faster than skipping around, not whether accessing fewer pages is faster than more. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] would hw acceleration help postgres (databases in general) ?
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Hamza Bin Sohail Sent: Friday, December 10, 2010 3:10 PM To: pgsql-hackers@postgresql.org Subject: [HACKERS] would hw acceleration help postgres (databases in general) ? Hello hackers, I think i'm at the right place to ask this question. Based on your experience and the fact that you have written the Postgres code, can you tell what a rough break-down - in your opinion - is for the time the database spends time just fetching and writing stuff to memory and the actual computation. The reason i ask this is because off-late there has been a push to put reconfigurable hardware on processor cores. What this means is that database writers can possibly identify the compute-intensive portions of the code and write hardware accelerators and/or custom instructions and offload computation to these hardware accelerators which they would have programmed onto the FPGA. There is not much utility in doing this if there aren't considerable compute- intensive operations in the database (which i would be surprise if true ). I would suspect joins, complex queries etc may be very compute-intensive. Please correct me if i'm wrong. Moreover, if you were told that you have a reconfigurable hardware which can perform pretty complex computations 10x faster than the base, would you think about synthesizing it directly on an fpga and use it ? I'd be more than glad to hear your guesstimates. Here is a sample project: http://www.cs.virginia.edu/~skadron/Papers/bakkum_sqlite_gpgpu10.pdf And another: http://www.cs.cmu.edu/afs/cs.cmu.edu/Web/People/ngm/15-823/project/Final.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Dec 10, 2010, at 3:03 PM, Tom Lane wrote: Yeah, it should be *to* 1.12. FWIW, this is how Bricolage upgrade scripts are handled: version-string-named directories with the appropriate scripts to upgrade *to* the named version number. But you still have to know what you're upgrading *from*. Huh? It's in the pg_extension catalog. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
Heck, even RAM isn't 1.0. I'm also involved with the Redis project, which is an in-memory database. Even for a pure-RAM database, it turns out that just using linked lists and 100% random access is slower than accessing page images. That's a slightly different problem, though. Sequential vs. random access is about whether fetching pages n, n+1, n+2, ... is faster than skipping around, not whether accessing fewer pages is faster than more. It's not though. Redis stores stuff as lists and sets, so it actually does a lot of sequential access of data. Like if people are accessing an ordered set, they're usually pulling the whole thing. It turns out that *even in RAM* storing stuff in an ordered fashion on data pages is more efficient than just using pointers. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
On Fri, Dec 10, 2010 at 3:13 PM, Joshua D. Drake j...@commandprompt.com wrote: Actually, the only (that I know of) optimized for sequential access code we have would be for the xlogs. And even that is more of a book-keeping simplification, rather than an optimization. You have to know where to find the logically next (in a PG sense) record. If the logically next record is not right after (in a file system sense) the previous record, then where is it and how do you find it? If you really wanted to make it non-sequential, you could, with a substantial amount of work. But why would you want to? On spinning rust, you might want to try leap-frogging the platter, but that is never going to be generalizable to different work-loads, much less different hardware. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone for SSDs?
On 10.12.2010 21:21, Daniel Loureiro wrote: The fact that it's called md.c is a hangover from the '80s. These days, the logic that the Berkeley guys envisioned being at that code level is generally in kernel device drivers. md.c can drive anything that behaves as a block device + filesystem, which is pretty much everything of interest. I believe that PostgreSQL was been developed and optimized for sequential access. To get full advantage of SSDs its necessary to rewrite almost the whole project - there are so much code written with the sequential mechanism in mind. Nope, as a matter of fact postgreSQL was developed as a university project with flexibility and extensibility among top goals. Yes, magnetic disk is the only storage manager left in current code base, but the original design had more, most notably the WORM (Write Once Read Many) disks, one of the uses being for the old design of VACUUM which did not throw away deleted rows but moved them to WORM disks for historical queries. The WORM disks were the next big thing in storage a few tens of years ago. And as Josh Berkus notes in another replay, nowadays even RAM is not neutral to access patterns - pipeline stalls and cache flushes can have impact of several orders of magnitude on execution speeds. -- Hannu Krosing PostgreSQL Infinite Scalability and High Availability http://www.2ndquadrant.com/books/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] would hw acceleration help postgres (databases in general) ?
On 12/10/10 3:09 PM, Hamza Bin Sohail wrote: There is not much utility in doing this if there aren't considerable compute- intensive operations in the database (which i would be surprise if true ). I would suspect joins, complex queries etc may be very compute-intensive. Please correct me if i'm wrong. Moreover, if you were told that you have a reconfigurable hardware which can perform pretty complex computations 10x faster than the base, would you think about synthesizing it directly on an fpga and use it ? Databases are, in general, CPU-bound. Most activities are compute-intensive. Even things you might think would be I/O-bound ... like COPY ... end up being dominated by parsing and building data structures. So, take your pick. COPY might be a good place to start, actually, since the code is pretty isolated and it would be easy to do tests. Or am I using a different definition of compute-intensive than you are? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
David E. Wheeler da...@kineticode.com writes: On Dec 10, 2010, at 3:03 PM, Tom Lane wrote: Yeah, it should be *to* 1.12. FWIW, this is how Bricolage upgrade scripts are handled: version-string-named directories with the appropriate scripts to upgrade *to* the named version number. But you still have to know what you're upgrading *from*. Huh? It's in the pg_extension catalog. How do you select which upgrade script to apply? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Dec 10, 2010, at 4:15 PM, Tom Lane wrote: Huh? It's in the pg_extension catalog. How do you select which upgrade script to apply? You run all those that contain version numbers higher than the currently-installed one. This of course assumes that one can correctly tell that one version number is higher than another. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] would hw acceleration help postgres (databases in general) ?
On Fri, Dec 10, 2010 at 3:09 PM, Hamza Bin Sohail hsoh...@purdue.edu wrote: Hello hackers, I think i'm at the right place to ask this question. Based on your experience and the fact that you have written the Postgres code, can you tell what a rough break-down - in your opinion - is for the time the database spends time just fetching and writing stuff to memory and the actual computation. The database is a general purpose tool. Pick a bottleneck you wish to have, and probably someone uses it in a way that causes that bottleneck to occur. The reason i ask this is because off-late there has been a push to put reconfigurable hardware on processor cores. What this means is that database writers can possibly identify the compute-intensive portions of the code and write hardware accelerators and/or custom instructions and offload computation to these hardware accelerators which they would have programmed onto the FPGA. When people don't use prepared statements, parsing can become a bottleneck. If Bison's yyparse could be put on a FPGA in a transparent way, than anyone using Bison, including PG, might benefit. That's just one example, of course. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unlogged tables
2010/12/8 Kineticode Billing da...@kineticode.com: On Dec 8, 2010, at 10:37 AM, Chris Browne wrote: Other possibilities include TRANSIENT, EPHEMERAL, TRANSIENT, TENUOUS. EVANESCENT. UNSAFE ? -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unlogged tables
On Dec 10, 2010, at 4:34 PM, Cédric Villemain wrote: Other possibilities include TRANSIENT, EPHEMERAL, TRANSIENT, TENUOUS. EVANESCENT. UNSAFE ? LOLZ. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] would hw acceleration help postgres (databases in general) ?
Thanks alot for all the replies. Very helpful, really appreciate it. - Original Message - From: Jeff Janes jeff.ja...@gmail.com To: Hamza Bin Sohail hsoh...@purdue.edu Cc: pgsql-hackers@postgresql.org Sent: Friday, December 10, 2010 7:18 PM Subject: Re: [HACKERS] would hw acceleration help postgres (databases in general) ? On Fri, Dec 10, 2010 at 3:09 PM, Hamza Bin Sohail hsoh...@purdue.edu wrote: Hello hackers, I think i'm at the right place to ask this question. Based on your experience and the fact that you have written the Postgres code, can you tell what a rough break-down - in your opinion - is for the time the database spends time just fetching and writing stuff to memory and the actual computation. The database is a general purpose tool. Pick a bottleneck you wish to have, and probably someone uses it in a way that causes that bottleneck to occur. The reason i ask this is because off-late there has been a push to put reconfigurable hardware on processor cores. What this means is that database writers can possibly identify the compute-intensive portions of the code and write hardware accelerators and/or custom instructions and offload computation to these hardware accelerators which they would have programmed onto the FPGA. When people don't use prepared statements, parsing can become a bottleneck. If Bison's yyparse could be put on a FPGA in a transparent way, than anyone using Bison, including PG, might benefit. That's just one example, of course. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
David E. Wheeler da...@kineticode.com writes: On Dec 10, 2010, at 4:15 PM, Tom Lane wrote: How do you select which upgrade script to apply? You run all those that contain version numbers higher than the currently-installed one. This of course assumes that one can correctly tell that one version number is higher than another. This idea is not exactly free of disadvantages. 1. It assumes that the underlying .so supports not only the current version, but every intermediate version of the SQL objects. For example, say the previously installed version was 1.10, and we are trying to go to 1.12. With your proposal we must pass through the catalog state applicable to 1.11. What if that includes some SQL function whose underlying C function is no longer there? The CREATE FUNCTION command will fail, that's what, even though the next update file would have deleted it or more likely replaced it with a reference to some other underlying function. 2. It can't tell whether a missing update file means no work is required or no upgrade is possible; in fact, without quite a lot of assumptions about version numbers, it can't even tell that an intermediate version update file is missing at all. I assume you expect that the backend would treat a missing file as no work is required, but that carries a lot of risk of winding up in a bad state if a file fails to get installed or fails to get read for some reason. I'd much rather expect the extension author to explicitly support each pair of (from, to) version numbers that he's prepared to deal with. If he can build those update scripts as simple concatenations of single-step scripts, great; but let's not hard-wire the assumption that that approach MUST work. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
