Re: [HACKERS] [GENERAL] Google Summer of Code (Fix aggregate operators Implement features such as Data Cubes and Skyline)
On Saturday 15 April 2006 22:33, Benjamin Arai wrote:
Hi,
Myself and a friend are PhD students at the University of California,
Riverside. We would be interested in such a project if it were available.
We are both experienced developers previously interning at EA Games and
current system administrators for the CS department.
I agree with Tom that this seems ambitious, however if you started looking at
the code now (say for adding the simplest of the new functions you are
thinking about) and submitted a full proposal, we could probably drum up a
mentor to help push the project along.
Robert Treat
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Saturday, April 15, 2006 7:14 PM
To: Benjamin Arai
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Google Summer of Code (Fix aggregate
operators Implement features such as Data Cubes and Skyline)
Benjamin Arai [EMAIL PROTECTED] writes:
A good project for the Google summer of code would be to solve the
aggregate problems in PostgreSQL. In addition, add some of
the more
complex operators such as rollup and etc.
That seems a tad ambitious, unless the SOCcer has already
done some nontrivial backend work ...
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Control File
OK! I'll create a shell script to verify it. If you change your mind latter I apply for developing the patch. Best Regards, Bruno -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, April 13, 2006 1:57 AM To: Bruce Momjian Cc: Bruno Almeida do Lago; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Control File Bruce Momjian pgman@candle.pha.pa.us writes: Bruno Almeida do Lago wrote: After that night, I started to ask myself if PostgreSQL should not have a control file to check if expected datafiles are where they should be and JUST warn about missing ones? I don't think this happens frequently enough to add code for it. I think we saw it happen once to Joe Conway's DB. But I see no particular reason why Postgres needs a feature for this --- you can stick a test into your database start script if you need it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Control File
Tom, With the new tablespace scenario in mind, how do you see this check feature being implemented: as a script or inside the code? As I said before, I apply for the creation of this patch. We just need to decide the best way to implement it. Regards, Bruno Almeida do Lago -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Saturday, April 15, 2006 4:11 PM To: Jim C. Nasby Cc: Bruce Momjian; Bruno Almeida do Lago; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Control File Jim C. Nasby [EMAIL PROTECTED] writes: Perhaps an easy means would be to put a PG_VERSION file in each tablespace when it's created and then check all of them. Tablespaces arguably make it slightly easier to accidentally try and mount something from a different version... I believe we already do put a PG_VERSION file in each tablespace, but AFAIR there is no subsequent check on their contents. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] plpython improvements on patches
Hi Anyone interested in plpython, please check out/review http://archives.postgresql.org/pgsql-patches/2006-04/msg00087.php It fixes the most obvious shortcomings of plpython by making it possible to return both sets and records (and setof records) and to use named input parameters in addition to args[]. The patch is for pg 8.0.7, but batches for 8.1 and HEAD will also be made available soon. - Hannu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Google SoC--Idea Request
OpenMFG has done some work on getting PostgreSQL working with the Drupal CMS and the Mantis bugtracker (and also integrating those two, btw). We're in contact with the respective projects about getting our patches worked in, but if anyone's keeping a tally, just wanted you to be aware. Regards, Ned Jonah H. Harris wrote: Hey everyone, I know we started a discussion a month or so ago regarding ideas for SoC projects. However, after reading through the thread, I didn't see us nail down any actual items. As such, we need to quickly put together a list of oh, 15-20 midlevel project ideas. I'm sure we can pull some off the TODO list, but we should also look at project ideas for porting some of the most used third-party OSS software to PostgreSQL too (portals, CMS systems, accounting systems, etc.). All ideas welcome! -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Control File
Bruno Almeida do Lago [EMAIL PROTECTED] writes: With the new tablespace scenario in mind, how do you see this check feature being implemented: as a script or inside the code? I'd put it in the code, and share code somehow with the current check on $PGDATA/PG_VERSION (maybe factor that into a subroutine that can be pointed at different directories). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] A successor for PQgetssl
On Sun, Apr 16, 2006 at 05:29:04PM -0400, Tom Lane wrote: No, failing to provide that is the bad idea, because then you're buying into the notion that libpq will provide a universal API that will incorporate anything anyone could possibly want to do with the underlying SSL library. The above is *not* that, prima facie because it is read-only access. Even if this was a feasible goal, it would absorb The intention is not to provide access to everything. If people want to know more about the certificate, we simply export the certificate to them and they can do with it what they like, including sending it to another program. I wasn't expecting that list to grow much because there's not much to export. Besides, what's wrong with read-only access? What parameters were you expecting them to want to change? After a session is setup there are no parameters to change anymore. All you need is read and write. a lot of time on our part that could be better spent elsewhere, plus a lot of time on the part of app programmers rewriting existing OpenSSL-aware or GnuTLS-aware code to instead use whatever random API we tell them they ought to use. They have better things to do with their time, too. The whole point is that app writers should not be aware at all which library we're using. At the moment psqlODBC requires openssl because we force them to. They only use three OpenSSL functions, SSL_read, SSL_write, and SSL_get_error. If we provided a hook to allow people read/write directly, they wouldn't need to know about the SSL connection at all. I think that's a much better way to go than adding a new library specific function for every little feature we add. You objected to this on the grounds of a problem with the COPY functions, except I can't see any problem that's relevent. The problem with copy was that data didn't have a length. Given the user is sending their own packets, we always have a length. - This breaks psqlODBC when it uses libpq because it wants to use OpenSSL and when libpq is compiled with GnuTLS that obviously won't work. That alone is sufficient reason why we're not going down that path. If we expose a GnuTLS-handle-fetching API then it's up to the ODBC guys to extend their code to handle that SSL library when they feel like it. But telling them that we're simply going to break their code and not provide them a path to fix it is not happening. By going down this path you're saying that psql will never be able display the cipher of an SSL connection if the libpq was compiled with a different library. If we provide a read/write than psqlODBC can remove code and it will work with GnuTLS. Isn't that much better? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
Bruce Momjian pgman ( at ) candle ( dot ) pha ( dot ) pa ( dot ) us writes: I am thinking we should throw an error on pg_start_backup() and pg_stop_backup if full_page_writes is off. No, we'll just change the test in xlog.c so that fullPageWrites is ignored if XLogArchivingActive. Seems archive_command and full_page_writes can still be used if we are not in the process of doing a file system backup. Think harder: we are only safe if the first write to a given page after it's mis-copied by the archiver is a full page write. The requirement therefore continues after pg_stop_backup. Unless you want to add infrastructure to keep track for *every page* in the DB of whether it's been fully written since the last backup? I am confused. Since we checkpoint during pg_start_backup(), isn't any write to a file while the tar backup is going on going to be a full page write? And once we pg_stop_backup(), do we need full page writes? -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Google SoC--Idea Request
* Jonah H. Harris ([EMAIL PROTECTED]) wrote: I know we started a discussion a month or so ago regarding ideas for SoC projects. However, after reading through the thread, I didn't see us nail down any actual items. I got an email already for a good idea, actually, which is to work on having pg_hba.conf modifiable from SQL. The only problem with that is that it really needs to be done in an acceptable way which requires probably as much design work as actual programming. Another idea along those same lines would be having .k5login-style support for Kerberos. We'd need a conf-flag for that for backwards compatibility (once the .k5login-style support exists we should clean up our Kerberos credentials matching to, for example, not accept 'sfrost/root' for 'sfrost' or '[EMAIL PROTECTED]' for '[EMAIL PROTECTED]'). It'd also be nice to support SASL, and better hashes than md5. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] A successor for PQgetssl
Martijn van Oosterhout kleptog@svana.org writes: On Sun, Apr 16, 2006 at 05:29:04PM -0400, Tom Lane wrote: No, failing to provide that is the bad idea, because then you're buying into the notion that libpq will provide a universal API that will incorporate anything anyone could possibly want to do with the underlying SSL library. ... [ snip ] Besides, what's wrong with read-only access? Well, psqlODBC seems a sufficient counterexample. But the problem with this is that you're asking a bunch of non-SSL-experts to design, evaluate, and then maintain an API for an SSL library. The real answer to the above is I don't know, and I doubt you do either. This is the sort of problem that we should be avoiding, rather than going out of our way to get involved in. PQgetssl made it possible for us to stay out of the way for SSL-using applications, and I think we should continue to follow that philosophy for other SSL libraries. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] A successor for PQgetssl
* Martijn van Oosterhout (kleptog@svana.org) wrote: The intention is not to provide access to everything. If people want to know more about the certificate, we simply export the certificate to them and they can do with it what they like, including sending it to another program. I wasn't expecting that list to grow much because there's not much to export. Besides, what's wrong with read-only access? What parameters were you expecting them to want to change? After a session is setup there are no parameters to change anymore. All you need is read and write. I have to agree with this... Certificate handling isn't growing new things in leaps and bounds these days and as long as the certificates are available to the user then the other things are really just nicities for people who don't want to decode the certs themselves. a lot of time on our part that could be better spent elsewhere, plus a lot of time on the part of app programmers rewriting existing OpenSSL-aware or GnuTLS-aware code to instead use whatever random API we tell them they ought to use. They have better things to do with their time, too. The whole point is that app writers should not be aware at all which library we're using. At the moment psqlODBC requires openssl because we force them to. They only use three OpenSSL functions, SSL_read, SSL_write, and SSL_get_error. If we provided a hook to allow people read/write directly, they wouldn't need to know about the SSL connection at all. I think that's a much better way to go than adding a new library specific function for every little feature we add. I have to agree with Martijn here too. It's not all that expensive to provide read/write calls to abstract away the specific library being used (since psqlODBC, at least, couldn't care less which library is being used, really) rather than ask for application developers to write their apps to support multiple SSL libraries to handle the case where libpq is compiled with one library vs. compiled with another... - This breaks psqlODBC when it uses libpq because it wants to use OpenSSL and when libpq is compiled with GnuTLS that obviously won't work. That alone is sufficient reason why we're not going down that path. If we expose a GnuTLS-handle-fetching API then it's up to the ODBC guys to extend their code to handle that SSL library when they feel like it. But telling them that we're simply going to break their code and not provide them a path to fix it is not happening. By going down this path you're saying that psql will never be able display the cipher of an SSL connection if the libpq was compiled with a different library. If we provide a read/write than psqlODBC can remove code and it will work with GnuTLS. Isn't that much better? I didn't really understand Tom's point here either... In order to not break psqlODBC when libpq is compiled with GnuTLS we'd have to code up support for returning an SSL* from the GnuTLS library that would work for all the OpenSSL functions which just isn't going to happen... I agree that we probably shouldn't go out of our way to break psqlODBC when libpq is compiled with OpenSSL (and so we shouldn't really change PQgetssl but mark it obsolete and ask that people don't use it and provide some other way for psqlODBC to do what it wants, or not, perhaps return NULL when using GnuTLS or '0x1', or not define it when libpq is compiled with GnuTLS...). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] A successor for PQgetssl
Stephen Frost [EMAIL PROTECTED] writes: I have to agree with Martijn here too. It's not all that expensive to provide read/write calls to abstract away the specific library being used (since psqlODBC, at least, couldn't care less which library is being used, really) You're failing to consider async applications. AFAICS, the *minimum* API would be read write read ready? write ready? get socket so I can use it in select() (very possibly there's some stuff I missed, considering I haven't consumed any caffeine yet today...). And that's just considering the data transport aspect of it. I'm still concerned that SSL-using apps may wish to twiddle the SSL library in ways we don't even know about. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is full_page_writes=off safe in conjunction with
On Sun, Apr 16, 2006 at 04:44:50PM -0400, Tom Lane wrote: It seems that we should write an API to allow a backup device to ask for blocks from the database. I don't think we have the manpower or interest to develop and maintain our own backup tool --- or tools, actually, as you'd at least want a tar replacement and an rsync replacement. Oracle might be able to afford to throw programmers at that sort of thing, but where are you going to get volunteers for tasks as mind-numbing as maintaining a PG-specific tar replacement? Why would it have to replicate the functionality of tar or rsync? AFAICT we'd only need the ability to produce something that could be consummed by either a postgres backend or some other utility of our own creation. I also think it'd be fine to forgo the rsync capabilities, at least in an initial version. Come to think of it, someone not too long ago was proposing an API to allow a 'PITR slave' to subscribe to a master for WAL segments/changes; it seems logical to me for that API to also provide the ability to send relation data as well. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
Bruce Momjian pgman@candle.pha.pa.us writes: Think harder: we are only safe if the first write to a given page after it's mis-copied by the archiver is a full page write. The requirement therefore continues after pg_stop_backup. Unless you want to add infrastructure to keep track for *every page* in the DB of whether it's been fully written since the last backup? I am confused. Since we checkpoint during pg_start_backup(), isn't any write to a file while the tar backup is going on going to be a full page write? And once we pg_stop_backup(), do we need full page writes? Hm. The case I was concerned about was where a page is never written to while the backup occurs (thus not triggering any full-page WAL entry), and then the first post-backup write is partial. However, if the backup is guaranteed to have captured a non-torn copy of such a page then there shouldn't be any problem. So if we consider the initial checkpoint to be a *required part* of pg_start_backup (right now it is not) then maybe we can get away with this. It needs more eyeballs on it though ... after having been burnt once by full_page_writes, I'm pretty shy ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Think harder: we are only safe if the first write to a given page after it's mis-copied by the archiver is a full page write. The requirement therefore continues after pg_stop_backup. Unless you want to add infrastructure to keep track for *every page* in the DB of whether it's been fully written since the last backup? I am confused. Since we checkpoint during pg_start_backup(), isn't any write to a file while the tar backup is going on going to be a full page write? And once we pg_stop_backup(), do we need full page writes? Hm. The case I was concerned about was where a page is never written to while the backup occurs (thus not triggering any full-page WAL entry), and then the first post-backup write is partial. However, if the backup is guaranteed to have captured a non-torn copy of such a page then there shouldn't be any problem. So if we consider the initial checkpoint to be a *required part* of pg_start_backup (right now it is not) then maybe we can get away with this. It needs more eyeballs on it though ... after having been burnt once by full_page_writes, I'm pretty shy ... Right. The comment in pg_start_backup() has to be updated: /* * Force a CHECKPOINT. This is not strictly necessary, but it seems like * a good idea to minimize the amount of past WAL needed to use the * backup. Also, this guarantees that two successive backup runs will * have different checkpoint positions and hence different history file * names, even if nothing happened in between. */ RequestCheckpoint(true, false); This is a much simpler fix than people talking about writing their own backup programs. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] A successor for PQgetssl
On Mon, Apr 17, 2006 at 11:07:26AM -0400, Stephen Frost wrote: I didn't really understand Tom's point here either... In order to not break psqlODBC when libpq is compiled with GnuTLS we'd have to code up support for returning an SSL* from the GnuTLS library that would work for all the OpenSSL functions which just isn't going to happen... I agree that we probably shouldn't go out of our way to break psqlODBC when libpq is compiled with OpenSSL (and so we shouldn't really change PQgetssl but mark it obsolete and ask that people don't use it and provide some other way for psqlODBC to do what it wants, or not, perhaps return NULL when using GnuTLS or '0x1', or not define it when libpq is compiled with GnuTLS...). Just mark PQgetssl() obsolete. We have to return a NULL or a valid pointer, otherwise existing programs will just crash. The major reason for this PQgettlsinfo() function is so people can actually *know* which library is active. Consider stuff like this: PQgetssl() not NULL - using openssl PQgetgnutlsnot NULL - using gnutls else not using ssl OR using some unknown library That's not a way to design an interface. This PQgettlsinfo() would tell you, one way or the other, if there is a TLS library in use. Give the user the certificates and the cipher and if the user wants more info, they can use the SSL library of their choice to get the information they want. Note, some of the info there is not stuff you can actually get from the PQgetssl() function currently anyway. You can't ask the SSL library easily if we provided a certificate during authentication. libpq knows this easily. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Is full_page_writes=off safe in conjunction with
Come to think of it, someone not too long ago was proposing an API to allow a 'PITR slave' to subscribe to a master for WAL segments/changes; it seems logical to me for that API to also provide the ability to send relation data as well. Is that what replication is for? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] A successor for PQgetssl
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: I have to agree with Martijn here too. It's not all that expensive to provide read/write calls to abstract away the specific library being used (since psqlODBC, at least, couldn't care less which library is being used, really) You're failing to consider async applications. AFAICS, the *minimum* You're assuming I intended just 'read()' and 'write()', I suppose I should have been more explicit but I certainly understand there's more than just read() and write() involved, though not by much. I'm not sure I see a reason we wouldn't just use the existing PQsocket() for the same socket back to the user for select()-based systems (don't we do this for SSL-enabled connections too anyway?)... There's not a whole lot special when it comes to handling data transport, or much that has changed lately or is likely to change anytime soon. consumed any caffeine yet today...). And that's just considering the data transport aspect of it. I'm still concerned that SSL-using apps may wish to twiddle the SSL library in ways we don't even know about. SSL is all about the certificates, really. As long as we provide that back to the user in a library-agnostic way they'll be able to do whatever they like with it, such as compare it to a CRL or use OCSP to check the current status. Of course, these are usually things you're more concerned about on the *server* side, which we don't currently support anyway. With supporting multiple SSL libraries we'll have to deal with these issues on the *server* side, should we decide to implement them someday (which would be nice...) anyway so trying to play like we don't want to or don't care to know about them doesn't really work. Just saying we won't do CRLs or OCSP ever would just mean we don't get run in some environments. Of course, in either case we can play the if you want it, write the code card but I'd really expect to get complaints from admins who want to use X.509 but need CRL/OCSP support before getting complaints from application developers that they need libpq to provide something more for them... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] A successor for PQgetssl
On Mon, Apr 17, 2006 at 11:25:26AM -0400, Tom Lane wrote: You're failing to consider async applications. AFAICS, the *minimum* API would be read write read ready? write ready? get socket so I can use it in select() Actually, you only need two functions: read write The get socket already exists as PQsocket(). Both SSL libraries work perfectly fine if the user sets the connection to non-blocking via PQsetnonblocking(). They just return -EAGAIN. I was actually thinking of the two functions as follows: typedef PostgresPollingStatusType pq_read_func( PGconn *conn, void *buf, int *len); typedef PostgresPollingStatusType pq_write_func( PGconn *conn, const void *buf, int *len); The existing PostgresPollingStatusType seems to handle both blocking and non-blocking states just fine. (very possibly there's some stuff I missed, considering I haven't consumed any caffeine yet today...). And that's just considering the data transport aspect of it. I'm still concerned that SSL-using apps may wish to twiddle the SSL library in ways we don't even know about. Well, I checked a few libs like libcurl and libldap. They generally allow you to configure the files containing the certificates but that's about it. Seriously, if people want to do really sophisticated things with the SSL library, they should setup s_tunnel instead. If we wanted to let users control everything we'd allow Anonymous DH key transfers. The README.SSL lists the situations were SSL makes sense and if you fall outside of that you shouldn't be using SSL. All I'm asking for is that libpq be made SSL-library *agnostic* so that users like psqlODBC can just *use* the connection with having to jump through hoops. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] A successor for PQgetssl
* Martijn van Oosterhout (kleptog@svana.org) wrote: Seriously, if people want to do really sophisticated things with the SSL library, they should setup s_tunnel instead. If we wanted to let I certainly agree with all the rest but I'm just not sure I can agree with you here. While s_tunnel is nice it's not always an option and I think it *would* be nice to have Postgres support things like CRLs and OCSP but more from the server-side of things than the client-side. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
Bruce Momjian pgman@candle.pha.pa.us writes: This is a much simpler fix than people talking about writing their own backup programs. Well, it's still not exactly trivial. The hack that was being proposed involved having the admin manually do full_page_writes = ON (ie, edit config file and SIGHUP) pg_start_backup take backup dump pg_stop_backup full_page_writes = OFF (ie, edit config file and SIGHUP) with some additions to pg_start_backup/pg_stop_backup to complain if full_page_writes isn't ON. Aside from being a PITA, this isn't at all secure, first for the obvious reason that we're only checking full_page_writes at start/stop and not whether it was on for the whole interval, and second because SIGHUP is asynchronous. Backends respond to the signal when they feel like it (in practice, upon starting a new interactive command) and so it'd be quite possible for a long-running query to still be executing with full_page_writes off long after the pg_start_backup has occurred. If we were to do this, I'd want some more-bulletproof mechanism for forcing full_page_writes on during the backup. We could probably keep a backup in progress flag in shared memory, and examine that along with the GUC variable before deciding to omit a full-page write. I seem to recall that there were previous proposals for such a flag, which I resisted because I didn't want any macroscopic user-visible change in behavior during a backup. But forcing full-page WAL writes is something I could live with as a backup mode behavior. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Parser
Hi I was workin on the parser version 8.1.2 but wheneva simply update the date the compilation fails not even ne changes. but the same thing when done in 8.0.3 it works nicely. Pls help. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] A successor for PQgetssl
On Mon, Apr 17, 2006 at 12:24:40PM -0400, Stephen Frost wrote: * Martijn van Oosterhout (kleptog@svana.org) wrote: Seriously, if people want to do really sophisticated things with the SSL library, they should setup s_tunnel instead. If we wanted to let I certainly agree with all the rest but I'm just not sure I can agree with you here. While s_tunnel is nice it's not always an option and I think it *would* be nice to have Postgres support things like CRLs and OCSP but more from the server-side of things than the client-side. CRLs are easy, almost a one line change. I was actually surprised it wasn't done but I didn't add it because I figured someone had left it out for a reason. OCSP is something else. And in any case, you don't need a result of PQgetssl() to use it since it's a completely seperate part of the library. But neither of these are what I considered sophisticated. I don't think either of these require any API changes either. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Parser
On Mon, Apr 17, 2006 at 08:07:23AM -0700, [EMAIL PROTECTED] wrote: Hi I was workin on the parser version 8.1.2 but wheneva simply update the date the compilation fails not even ne changes. but the same thing when done in 8.0.3 it works nicely. What are you talking about? Please provide exact error messages and a description of what you are doing. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Parser
[EMAIL PROTECTED] wrote: I was workin on the parser version 8.1.2 but wheneva simply update the date the compilation fails not even ne changes. parse error -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: This is a much simpler fix than people talking about writing their own backup programs. Well, it's still not exactly trivial. The hack that was being proposed involved having the admin manually do full_page_writes = ON (ie, edit config file and SIGHUP) pg_start_backup take backup dump pg_stop_backup full_page_writes = OFF (ie, edit config file and SIGHUP) with some additions to pg_start_backup/pg_stop_backup to complain if full_page_writes isn't ON. Aside from being a PITA, this isn't at all secure, first for the obvious reason that we're only checking full_page_writes at start/stop and not whether it was on for the whole interval, and second because SIGHUP is asynchronous. Backends respond to the signal when they feel like it (in practice, upon starting a new interactive command) and so it'd be quite possible for a long-running query to still be executing with full_page_writes off long after the pg_start_backup has occurred. If we were to do this, I'd want some more-bulletproof mechanism for forcing full_page_writes on during the backup. We could probably keep a backup in progress flag in shared memory, and examine that along with the GUC variable before deciding to omit a full-page write. I seem to recall that there were previous proposals for such a flag, which I resisted because I didn't want any macroscopic user-visible change in behavior during a backup. But forcing full-page WAL writes is something I could live with as a backup mode behavior. Yes, good point. The setting has to be seen by all backends at the same time, so yea, a shared memory variable seems required. The manual method is clearly a loser. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] A successor for PQgetssl
* Martijn van Oosterhout (kleptog@svana.org) wrote: On Mon, Apr 17, 2006 at 12:24:40PM -0400, Stephen Frost wrote: I certainly agree with all the rest but I'm just not sure I can agree with you here. While s_tunnel is nice it's not always an option and I think it *would* be nice to have Postgres support things like CRLs and OCSP but more from the server-side of things than the client-side. CRLs are easy, almost a one line change. I was actually surprised it wasn't done but I didn't add it because I figured someone had left it out for a reason. I doubt there was a reason it was left out... OCSP is something else. And in any case, you don't need a result of PQgetssl() to use it since it's a completely seperate part of the library. Right, I mentioned you'd just need the certificate, and that I was talking about it on the server-side... But neither of these are what I considered sophisticated. I don't think either of these require any API changes either. Right, they don't require API changes (at least, not libpq) and wouldn't provided the certificate is available. What I was getting at is that they're more complicated SSL issues that the server *should* be able to deal with (imv) and would require additional configuration parameters and more of the SSL libraries than we're currently using. Mainly I was trying to point out that trying to claim that we don't do anything with SSL, it's all up to the application to decide what they want to do, and we give the application the SSL pointer so we don't have to worry about it doesn't really fly- we have an SSL-using application ourselves which is the postmaster and we really should be able to properly support it and be at least somewhat knowledgable about how it works because SSL is a Good Thing for us to have. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: If we were to do this, I'd want some more-bulletproof mechanism for forcing full_page_writes on during the backup. We could probably keep a backup in progress flag in shared memory, and examine that along with the GUC variable before deciding to omit a full-page write. Yes, good point. The setting has to be seen by all backends at the same time, so yea, a shared memory variable seems required. I've applied a patch for this. On reflection, the CHECKPOINT during pg_start_backup was actually necessary for torn-page safety even without full_page_writes off. The reason is that the torn-page risk occurs when we write a page from shared memory, not when we modify it in memory. Without a CHECKPOINT, a page modified just before pg_start_backup could be dumped during the backup and then be saved in a torn state, even though no WAL record for it is emitted anytime during the backup procedure. So that comment's been wrong all along. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: If we were to do this, I'd want some more-bulletproof mechanism for forcing full_page_writes on during the backup. We could probably keep a backup in progress flag in shared memory, and examine that along with the GUC variable before deciding to omit a full-page write. Yes, good point. The setting has to be seen by all backends at the same time, so yea, a shared memory variable seems required. I've applied a patch for this. On reflection, the CHECKPOINT during pg_start_backup was actually necessary for torn-page safety even without full_page_writes off. The reason is that the torn-page risk occurs when we write a page from shared memory, not when we modify it in memory. Without a CHECKPOINT, a page modified just before pg_start_backup could be dumped during the backup and then be saved in a torn state, even though no WAL record for it is emitted anytime during the backup procedure. So that comment's been wrong all along. Great, yea, checkpoing syncs up the dirty buffers with the file system, and it is true we need that to happen before the backup begins. The idea of creating functions to mark start/stop of backup has clearly been a win here. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] CVS tags
I finally got around to looking at adding $PostgreSQL$ tags to all files
in the repository. To see what I was looking at, I ran the following on
a clean checkout:
find . -type f ! -path \*/CVS\* ! -exec grep -q '$PostgreSQL: pgsql' {} \;
-print
The results are at http://jim.nasby.net/no_cvs_tag.txt
Since we're talking about 1100 files here, many of which might need the
tag added manually, I'm wondering if I should break this into chunks
(ie: src, contrib, everything else) rather than submitting one huge
patch. Anyone care one way or another?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CVS tags
Jim C. Nasby [EMAIL PROTECTED] writes: I finally got around to looking at adding $PostgreSQL$ tags to all files in the repository. Um, surely most of the ones that should have such tags already do. (contrib might be a glaring weak spot.) We have explicitly decided *not* to have tags in places such as the regression test files, because of the comparison problems that would create. Nor does it seem appropriate to add tags in files that are verbatim copies of other projects' upstream files (config.guess and the zic timezone files are examples here). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Corrupt data pages...
Tom Lane wrote: You should at least show the page you think is corrupt. I attempted to send this additional info to the list but I think the message got dropped on the floor by the mailing list software or by the spam filter. I'll put the files on a web server and post links to them here. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
On Mon, Apr 17, 2006 at 03:00:58PM -0400, Tom Lane wrote: I've applied a patch for this. On reflection, the CHECKPOINT during pg_start_backup was actually necessary for torn-page safety even without full_page_writes off. The reason is that the torn-page risk occurs when we write a page from shared memory, not when we modify it in memory. Without a CHECKPOINT, a page modified just before pg_start_backup could be dumped during the backup and then be saved in a torn state, even though no WAL record for it is emitted anytime during the backup procedure. So that comment's been wrong all along. Are you going to back-patch this? If I understand correctly current behavior could mean people using PITR may have invalid backups. In the meantime, perhaps we should send an email to -annouce recommending that folks issue a CHEKCPOINT; after pg_start_backup and before initiating the filesystem copy. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Corrupt data pages...
I wrote: I attempted to send this additional info to the list but I think the message got dropped on the floor by the mailing list software or by the spam filter. I'll put the files on a web server and post links to them here. You can find them here: https://gazebo.sysexperts.com/~kevin/postgresql The files are bad-page-info.txt and bad-page.txt. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Google SoC--Idea Request
On Sat, Apr 15, 2006 at 03:05:20PM -0400, Jonah H. Harris wrote: All ideas welcome! I know it's not directly PostgreSQL related, but I'd love to see the dbt* code improved. Items on my wish-list: - make it easy to run the test framework and clients on a seperate machine from the database server - keep results in a database - provide a front-end to allow users to schedule tests in a queue - add support for windows, at least for the database (theoretically possible to run that way now, but you have to do everything by hand) Another idea: afaik, spikesource is still offering a bounty for improvements to OSS test suites, something that'd fit well with SoC. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
Jim C. Nasby wrote: On Mon, Apr 17, 2006 at 03:00:58PM -0400, Tom Lane wrote: I've applied a patch for this. On reflection, the CHECKPOINT during pg_start_backup was actually necessary for torn-page safety even without full_page_writes off. The reason is that the torn-page risk occurs when we write a page from shared memory, not when we modify it in memory. Without a CHECKPOINT, a page modified just before pg_start_backup could be dumped during the backup and then be saved in a torn state, even though no WAL record for it is emitted anytime during the backup procedure. So that comment's been wrong all along. Are you going to back-patch this? If I understand correctly current behavior could mean people using PITR may have invalid backups. In the meantime, perhaps we should send an email to -annouce recommending that folks issue a CHEKCPOINT; after pg_start_backup and before initiating the filesystem copy. We are disabling full_page_writes for 8.1.4, so they should be fine. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?
Bruce Momjian wrote: Jim C. Nasby wrote: On Mon, Apr 17, 2006 at 03:00:58PM -0400, Tom Lane wrote: I've applied a patch for this. On reflection, the CHECKPOINT during pg_start_backup was actually necessary for torn-page safety even without full_page_writes off. The reason is that the torn-page risk occurs when we write a page from shared memory, not when we modify it in memory. Without a CHECKPOINT, a page modified just before pg_start_backup could be dumped during the backup and then be saved in a torn state, even though no WAL record for it is emitted anytime during the backup procedure. So that comment's been wrong all along. Are you going to back-patch this? If I understand correctly current behavior could mean people using PITR may have invalid backups. In the meantime, perhaps we should send an email to -annouce recommending that folks issue a CHEKCPOINT; after pg_start_backup and before initiating the filesystem copy. We are disabling full_page_writes for 8.1.4, so they should be fine. Just to clarify, 8.1.4 will remove control for turning off full_page_writes, but 8.2 will allow such control, and allow it can be used with PITR because we will automatically turn it on during file system backup. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Regrading TODO item alerting pg_hba.conf from SQL
Hello again :) It doesn't seem particularly complicated: inside the connection-startup transaction done by InitPostgres, you could check to make sure the selected user has the CONNECT privilege on the selected database. [ looks at code... ] Actually ReverifyMyDatabase is the right place, since it already has its hands on the pg_database row. You don't want this to cost an extra pg_database search during startup. I looked at the code as suggested. (so far many questions but I guess all in right time) Anyway... would it be correct to start any connection checking inside the if statement from line 186 if (IsUnderPostmaster ! IsAutoVacuumProcess()) in method ReverifyMyDatabase? Sorry for being too trivial, but I want to be sure. Regards, Gevik. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] question regarding pg_database.h and DatabaseRelationId
Hello all, Could someone explain the numbering logic in naming the relations? Why #define DatabaseRelationId 1262. I guess not being involved from the beginning has its disadvantages. but it is very exiting and fun trying to learn the internals by gdb and asking lots of questions :) Regards, Gevik. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] question regarding pg_database.h and DatabaseRelationId
Gevik Babakhani wrote: Could someone explain the numbering logic in naming the relations? There is none. The numbers are presumably assigned in some lowest-available manner, but over the years it has become pretty random. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Is full_page_writes=off safe in conjunction with
On Sun, 2006-04-16 at 16:44 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: It seems that we should write an API to allow a backup device to ask for blocks from the database. I don't think we have the manpower or interest to develop and maintain our own backup tool --- or tools, actually, as you'd at least want a tar replacement and an rsync replacement. Oracle might be able to afford to throw programmers at that sort of thing, but where are you going to get volunteers for tasks as mind-numbing as maintaining a PG-specific tar replacement? Agreed. The only reason to do that would be to combine it with an incremental backup solution also, so that some positive benefit also came from the work. I think an easier answer must be to make pg_start_backup() throw a checkpoint, then hold any database writes until pg_stop_backup() is called. (In the case of full_page_writes = off and fsync = on only). That way all the data is fsynced to disk and the physical backup is guaranteed to see whole blocks always, as we need it to. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Question on win32 semaphore simulation
Qingqing Zhou [EMAIL PROTECTED] wrote As I reviewed the win32/sema.c, there is some code that I am not clear, can anybody explain please? There is another problem related to concurrent operations on win32 sema. Say two processes are doing semop(+1) concurrently. Look at this code: /* Don't want the lock anymore */ sem_counts[sops[0].sem_num]++; ReleaseSemaphore(cur_handle, sops[0].sem_op, NULL); Except for the problem mentioned in the above thread that the first line should be: sem_counts[sops[0].sem_num] += sops[0].sem_op, the sem_counts[] are unprotected by anything, so we might lose an update. Maybe I totally misunderstand something? Regards, Qingqing ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] how can i get the binary format of timestamp?
Hello:
I find that the real timestamp format(got from the file which is produced by
copying binary to ) is different from what i find in timestamp_send func.
i do think that the binary format of a timestamp 2006-04-18 11:20:20 should
be 44 2B B0 6A 00 00 00 00 standing for secs:1143713898, usecs:0 but in
fact it is:41A7 7DBA D400
timestamp is defined as int64 or float8, when it is defined as int64, it looks
like timeval, first 32 bits for seconds,second 32 bits for usec.
and from the timestamp_send func, it just changes the first 32 bits to binary
then combines the next 32 bits
/* codes from timestamp_send
*/
Datum
timestamp_send(PG_FUNCTION_ARGS)
{
Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
StringInfoData buf;
pq_begintypsend(buf);
#ifdef HAVE_INT64_TIMESTAMP
pq_sendint64(buf, timestamp);
#else
pq_sendfloat8(buf, timestamp);
#endif
PG_RETURN_BYTEA_P(pq_endtypsend(buf));
}
/* codes from pq_sendint64
*/
void
pq_sendint64(StringInfo buf, int64 i)
{
uint32 n32;
/* High order half first, since we're doing MSB-first */
#ifdef INT64_IS_BUSTED
/* don't try a right shift of 32 on a 32-bit word */
n32 = (i 0) ? -1 : 0;
#else
n32 = (uint32) (i 32);
#endif
n32 = htonl(n32);
appendBinaryStringInfo(buf, (char *) n32, 4);
/* Now the low order half */
n32 = (uint32) i;
n32 = htonl(n32);
appendBinaryStringInfo(buf, (char *) n32, 4);
}
so i do think that the binary format of a timestamp 2006-04-18 11:20:20
should be 44 2B B0 6A 00 00 00 00 standing for secs:1143713898, usecs:0
but in fact it is: 41A7 7DBA D400 , i don't know why.
maybe, i have looked into a wrong func, then which is the right one?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
