Re: [HACKERS] PostgreSQL 8.0.6 crash
On Fri, Feb 10, 2006 at 12:16:04PM -0500, Mark Woodward wrote: > > And even when PostgreSQL has the server all to itself, having a hashagg > > spill to disk is *way* better than pushing the machine into a swap > > storm. At least if you spill the hashagg you only have one backend > > running at a snail's pace; a swap storm means next to nothing gets done. > > > >> This was/is an example of where the behavior of PostgreSQL is clearly > >> unacceptable. OK, yes, this problem goes away with an ANALYZE, but it > >> isn't clear how anyone could have known this, and unexpected behavior is > >> bad in any product. > > > > Care to submit a documentation patch before releases are bundled (I > > think on Sunday?) At least then people would be aware that work_mem is > > just a suggestion to hash_aggs. I'd do a patch myself but I doubt I'll > > have time before the release. :( > > I would be glad too. What's the process? Well, find the appropriate file in doc/src/sgml, make a copy, edit the file, generate a diff with diff -u, and email that diff/patch to pgsql-patches. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Upcoming re-releases
* Tom Lane: > Actually, it's "because it's certain to be there and be accessible to > unprivileged users". Isn't this a bit problematic because any local user can impersonate a PostgreSQL backend which has been shut down? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Upcoming re-releases
On Sat, Feb 11, 2006 at 03:04:00PM +0100, Florian Weimer wrote: > * Tom Lane: > > > Actually, it's "because it's certain to be there and be accessible to > > unprivileged users". > > Isn't this a bit problematic because any local user can impersonate a > PostgreSQL backend which has been shut down? Well, I guess it's an issue. At least it's not suceptable to the standard symlink attacks. There is in general no way of knowing if the server you are connecting to is what you think it is (except via SSL maybe?). The good thing is that if you're using md5 auth they can't grab your password. The bad thing is that the server decides the authentication protocol :(. Man-in-the-middle attacks would only be feasable for attacker that have the same UID as the postmaster (deleting the socket and creating a new one over the top). In those cases there's little you can do anyway. Putting the socket in a directory owned by the postgres user does stop other users impersonating the server. Currently, if two local users both compile a postgres server, they may end up connecting to eachothers servers :). These no real way around this. The only real option would be moving to a home directory but that would require knowing the username the server is running under... Have a nice day, -- Martijn van Oosterhout 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] [COMMITTERS] pgsql: Enable pg_ctl to give up admin privileges when starting the
Stephen Frost <[EMAIL PROTECTED]> writes: > * Tom Lane ([EMAIL PROTECTED]) wrote: >> Enable pg_ctl to give up admin privileges when starting the server under >> Windows (if newer than NT4, else works same as before). > I don't suppose we could consider doing this for Unix-based systems too? > I think it'd certainly be nice. It's also how quite a few other Unix > daemons operate. Why should we try to duplicate the functionality of "su"? And we would have to largely duplicate it, not just change process UID. I don't see the need for it anyway. The only reason this patch went in at all is to compensate for the general level of ignorance of Windows users... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [HACKERS] What do the Windows pg hackers out there like for dev
Mark Kirkwood <[EMAIL PROTECTED]> writes: > I was doing exactly this about a year ago and used Mingw. The only > annoyance was that I could compile everything on Linux in about 3 > minutes (P4 2.8Ghz), but had to wait about 60-90 minutes for the same > thing on Windows 2003 Server! (also a P4 2.8Ghz...). So I used to build > a 'go for coffee' task into the build and test cycle. Youch! That seems unbelievably bad, even for Microsloth. Did you ever identify what was the bottleneck? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Enable pg_ctl to give up admin privileges
Tom Lane wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > * Tom Lane ([EMAIL PROTECTED]) wrote: > >> Enable pg_ctl to give up admin privileges when starting the server under > >> Windows (if newer than NT4, else works same as before). > > > I don't suppose we could consider doing this for Unix-based systems too? > > I think it'd certainly be nice. It's also how quite a few other Unix > > daemons operate. > > Why should we try to duplicate the functionality of "su"? And we would > have to largely duplicate it, not just change process UID. > > I don't see the need for it anyway. The only reason this patch went in > at all is to compensate for the general level of ignorance of Windows > users... But other Unix daemons do it, so why not us? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] What do the Windows pg hackers out there like for dev
> Mark Kirkwood <[EMAIL PROTECTED]> writes: > > I was doing exactly this about a year ago and used Mingw. The only > > annoyance was that I could compile everything on Linux in about 3 > > minutes (P4 2.8Ghz), but had to wait about 60-90 minutes > for the same > > thing on Windows 2003 Server! (also a P4 2.8Ghz...). So I used to > > build a 'go for coffee' task into the build and test cycle. > > Youch! That seems unbelievably bad, even for Microsloth. > Did you ever identify what was the bottleneck? The mingw gcc compiler is horribly slow. It has nothing to do with Microsoft this time. I haven't seen times quite that bad, but it's much slower than gcc on Linux. (As a comparison, completely rebuilding pgAdmin3 with Visual C++ on my slow laptop takes maybe 5-6 minutes, whereas it takes 20+ minutes on a Athlon64 3200+, with a much faster SATA disk and twice the memory. And it's almost as slow on a dual-CPU server with high-speed SCSI disks. So Visual C++ certainly doesn't have this problem.) //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upcoming re-releases
Martijn van Oosterhout writes: > These no real way around this. The only real option would be moving to > a home directory but that would require knowing the username the server > is running under... And the problem would still exist, with even less chance of solution, for TCP connections which are probably the majority of real-world usage. If you're concerned about this sort of attack I think it has to be solved in the protocol, not by reliance on socket placement. I'm not sure whether our current SSL support does a good job of this --- I think it only tries to check whether the server presents a valid certificate, not which cert it is. Possibly Kerberos does more, but I dunno a thing about that... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Upcoming re-releases
> I'm not sure whether our current SSL support does a good job of this > --- I think it only tries to check whether the server > presents a valid certificate, not which cert it is. Possibly > Kerberos does more, but I dunno a thing about that... If you stick a root certificate (root.crt in ~/.postgresql) for it to validate against, it will be validated against that root. I'm not sure if it validates the common name of the cert though - that would be an issue if you're using a global CA. If you're using a local enterprise CA, that's a much smaller issue (because you yourself have total control over who gets certificates issued by the CA). The way our Kerberos implementation is done, it does *not* validate the server, just the client. If you want server verification, you must use a combination of both Kerberos and SSL. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Upcoming re-releases
"Magnus Hagander" <[EMAIL PROTECTED]> writes: > If you stick a root certificate (root.crt in ~/.postgresql) for it to > validate against, it will be validated against that root. I'm not sure > if it validates the common name of the cert though - that would be an > issue if you're using a global CA. If you're using a local enterprise > CA, that's a much smaller issue (because you yourself have total control > over who gets certificates issued by the CA). But in either case, it would only be checking that the cert had been issued by that CA, no? Unless you set up a CA that only ever issues certificates to your PG server, someone else with a cert from the CA could still impersonate. Or am I mistaken about that? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 8.0.6 crash
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > That's why merely allocating tons of swap doesn't necessarily protect you. > > It's still possible for a process (or several processes if you allocate more > > swap than you have address space) to mmap gigabytes of memory without > > touching > > it and then start touching those pages. > > So? If the swap exists to back that memory, there's no problem. It > might be slow, but it will not fail. Sure, but there's no way to know how much swap you need. No matter how much swap you allocate these processes can allocate more pages of untouched RAM and then blow up. Of course realistically allocating 4G of swap is enough to deal with something like Postgres where you're not being maliciously attacked. One process on ia32 can't accidentally allocate more than 4G of ram. I was just trying to clarify the situation since someone made some comment about it having to do with memory being swapped out and then finding nowhere to swap in when needed. That's not exactly what's happening. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Upcoming re-releases
* Martijn van Oosterhout: > Well, I guess it's an issue. At least it's not suceptable to the > standard symlink attacks. There is in general no way of knowing if the > server you are connecting to is what you think it is (except via SSL > maybe?). For local (i.e. UNIX domain socket) connections, there is -- just use a hard-coded path where each directory is only writable by root or by the PostgreSQL superuser (/var/run in Debian is not world-writable, for instance). > The good thing is that if you're using md5 auth they can't grab your > password. The password is probably of little concern if you use UNIX domain sockets. But feeding wrong data to the application might trigger interesting things. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upcoming re-releases
> > If you stick a root certificate (root.crt in ~/.postgresql) > for it to > > validate against, it will be validated against that root. > I'm not sure > > if it validates the common name of the cert though - that > would be an > > issue if you're using a global CA. If you're using a local > enterprise > > CA, that's a much smaller issue (because you yourself have total > > control over who gets certificates issued by the CA). > > But in either case, it would only be checking that the cert > had been issued by that CA, no? Unless you set up a CA that > only ever issues certificates to your PG server, someone else > with a cert from the CA could still impersonate. Or am I > mistaken about that? Correct. But if you run your own enterprise CA, that's exactly the kind of thing you can make sure - that nobody else has a certificate from that CA. But no, it wouldn't be bad if there was a way to specify exactly which cert is used. Or at least validate the common name of it agains the hostname of the server. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL 8.0.6 crash
Greg Stark wrote: > > Tom Lane <[EMAIL PROTECTED]> writes: > > > Greg Stark <[EMAIL PROTECTED]> writes: > > > That's why merely allocating tons of swap doesn't necessarily protect you. > > > It's still possible for a process (or several processes if you allocate > > > more > > > swap than you have address space) to mmap gigabytes of memory without > > > touching > > > it and then start touching those pages. > > > > So? If the swap exists to back that memory, there's no problem. It > > might be slow, but it will not fail. > > Sure, but there's no way to know how much swap you need. No matter how much > swap you allocate these processes can allocate more pages of untouched RAM and > then blow up. > > Of course realistically allocating 4G of swap is enough to deal with something > like Postgres where you're not being maliciously attacked. One process on ia32 > can't accidentally allocate more than 4G of ram. > > I was just trying to clarify the situation since someone made some comment > about it having to do with memory being swapped out and then finding nowhere > to swap in when needed. That's not exactly what's happening. I guess the fundamental issue is whether Linux requires all mmap()'ed file contents to be in memory, or whether it pushes data to disk and unmaps it as it runs low on memory. I don't know the answer to that. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 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] PostgreSQL 8.0.6 crash
Greg Stark <[EMAIL PROTECTED]> writes: > I was just trying to clarify the situation since someone made some comment > about it having to do with memory being swapped out and then finding nowhere > to swap in when needed. That's not exactly what's happening. No. I believe the case that is actually hard for the kernel to predict comes from copy-on-write: when a process forks, you could potentially need twice its current memory image, but in reality you probably won't ever need that much since many of the shared pages won't ever be written by either process. However, a non-overcommitting kernel must assume that worst case, and hence fail the fork() if it doesn't have enough swap space to cover both processes. If it does not, then the crunch comes when one process does touch a shared page. If there is no available swap space at that time, kill -9 is the only recourse, because there is no way in the Unix API to fail a write to valid memory. The reason for having a lot more swap space than you really need is just to cover the potential demand from copy-on-write of pages that are currently shared. But given the price of disk these days, it's pretty cheap insurance. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Scrollable cursors and Sort performance
On Fri, Feb 10, 2006 at 07:16:41PM +, Simon Riggs wrote: > On Fri, 2006-02-10 at 19:14 +0100, Martijn van Oosterhout wrote: > > On Fri, Feb 10, 2006 at 04:48:42PM +, Simon Riggs wrote: > > > If a cursor is defined NO SCROLL, which is the SQL Standard implicit > > > default, then we are safe to assume there will be no rewinds or backward > > > scans. The PostgreSQL current implicit default is SCROLL, which means > > > that no part of the executor can currently make useful assumptions about > > > scan direction, so this is a wider issue than just sorts. > > > > Umm, the documentation says: PostgreSQL will allow backward fetches > > without SCROLL, if the cursor's query plan is simple enough that no > > extra overhead is needed to support it. > > > > So if the default is SCROLL someone needs to fix the docs because > > that's not what it says. It says that *some plans* can be fetched > > backwards even if you don't say scroll. The documentation clearly says > > we don't need to support backwards searches without scroll if it > > causes problems. > > Changing the docs is not the problem here. I don't understand the point > you are making and how it effects the issue. > > The problem is knowing before the sort is executed whether the sort > result will ever be used in the future by a backward scan. We can only > do this by definition, restricting the future use of a FETCH. I think the point that Martijn was trying to make was that per our docs it would be perfectly acceptable for us to make any cursor NO SCROLL implicitly if it means less work for the optimizer. Whether that's a good thing is a different story... ISTM doing so would be very confusing for users. Since this affects all queries with sorts I would definately be in favor of exposing an option to change this ASAP, even if the default was to maintain the current behavior for compatability. Disk sorts are hugely expensive, and anything to reduce that expense would be very welcome. -- 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 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] Scrollable cursors and Sort performance
On Sat, Feb 11, 2006 at 11:32:02AM -0600, Jim C. Nasby wrote: > I think the point that Martijn was trying to make was that per our docs > it would be perfectly acceptable for us to make any cursor NO SCROLL > implicitly if it means less work for the optimizer. Ok, I take that back. The actual quote[1] is: "Depending upon the complexity of the query's execution plan, specifying SCROLL may impose a performance penalty on the query's execution time." Clearly that says it can affect execution time, not that we're free to alter the default behavior at will. But speaking of documentation, it doesn't actually say what the default is. Care update that, or should I formally submit a patch? [1] http://www.postgresql.org/docs/8.1/interactive/sql-declare.html -- 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] Scrollable cursors and Sort performance
Jim C. Nasby wrote: > On Sat, Feb 11, 2006 at 11:32:02AM -0600, Jim C. Nasby wrote: > > I think the point that Martijn was trying to make was that per our docs > > it would be perfectly acceptable for us to make any cursor NO SCROLL > > implicitly if it means less work for the optimizer. > > Ok, I take that back. The actual quote[1] is: > > "Depending upon the complexity of the query's execution plan, specifying > SCROLL may impose a performance penalty on the query's execution time." > > Clearly that says it can affect execution time, not that we're free to > alter the default behavior at will. > > But speaking of documentation, it doesn't actually say what the default > is. Care update that, or should I formally submit a patch? Patch please. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Upcoming re-releases
* Magnus Hagander: > But no, it wouldn't be bad if there was a way to specify exactly which > cert is used. Or at least validate the common name of it agains the > hostname of the server. SSH-like "leap of faith" authentication would be even better. Store the certificate on the first connection (together with the domain name), and refuse subsequent connections if the certificate changes. ---(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] Analyze and vacuum, they are sort of mandatory....
I was think about how forgetting to run analyze while developing a table loader program caused PostgreSQL to run away and use up all the memory. Is there some way that postges or psql can know that it substantially altered the database and run analyze? I know this is a kind of stupid question, but postgresql does not behave well when the system changes in a major way without at least an analyze. There must be something that can be done to protect the casual user (or busy sometimes absent minded developer) from these dangerous edge conditions? ---(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] Analyze and vacuum, they are sort of mandatory....
Mark Woodward wrote: > I know this is a kind of stupid question, but postgresql does not > behave well when the system changes in a major way without at least > an analyze. There must be something that can be done to protect the > casual user (or busy sometimes absent minded developer) from these > dangerous edge conditions? autovacuum -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Lock Functions
I don't know if this the correct list to ask about implementation details. If not, let me know. Anyway, are there functions that return which processes or transactions are holding locks in relations, tuples, etc ? Best regards, Alfranio ---(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] Locks
Are there some sort of functions that I could use to know which process has an exclusive lock on relations, pages, tuples, transactions, etc... ? Best regards, Alfranio Junior ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Locks
On Sat, 11 Feb 2006, Alfranio Correia Junior wrote: Are there some sort of functions that I could use to know which process has an exclusive lock on relations, pages, tuples, transactions, etc... ? SELECT * FROM pg_locks; - Heikki ---(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
[HACKERS] Raising the Pl/Perl required version
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This was raised before, but I can't find the exact thread. I'd like to re-open the idea of boosting the minimum Perl version for PL/Perl to 5.6. My primary motivation is to provide use of "our" for the %TD hash, as mentioned before (cannot find the email right now). Being as 5.6 was released nearly six years ago, in March of 2000, I'm hoping that this won't meet too many objections. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200602111428 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFD7jsQvJuQZxSWSsgRAkH8AJ9cf9uCjVKNBUZwtUT/q5ODtZZrfQCgtaVW n43hYpQqHObl5eIRKijFGUM= =ko1M -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Enable pg_ctl to give up admin
Bruce Momjian wrote: Tom Lane wrote: Stephen Frost <[EMAIL PROTECTED]> writes: * Tom Lane ([EMAIL PROTECTED]) wrote: Enable pg_ctl to give up admin privileges when starting the server under Windows (if newer than NT4, else works same as before). I don't suppose we could consider doing this for Unix-based systems too? I think it'd certainly be nice. It's also how quite a few other Unix daemons operate. Why should we try to duplicate the functionality of "su"? And we would have to largely duplicate it, not just change process UID. I don't see the need for it anyway. The only reason this patch went in at all is to compensate for the general level of ignorance of Windows users... But other Unix daemons do it, so why not us? Other Unix daemons typically do it when they need to do something at startup like open a privileged port to listen on, or if they need to be able to become other users. Postgres doesn't have anything that requires root privilege in normal operation. How many times have security holes been inadvertantly opened because of this sort of privilege escalation? Windows is different in a number of ways, including the high inconvenience factor involved in running as a non-power user, and the fact that Windows servers are typically single-purpose. cheers andrew ---(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] Raising the Pl/Perl required version
Greg Sabino Mullane wrote: > This was raised before, but I can't find the exact thread. I'd > like to re-open the idea of boosting the minimum Perl version > for PL/Perl to 5.6. I don't think this is unreasonable. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Scrollable cursors and Sort performance
On Sat, 2006-02-11 at 11:44 -0600, Jim C. Nasby wrote: > On Sat, Feb 11, 2006 at 11:32:02AM -0600, Jim C. Nasby wrote: > > I think the point that Martijn was trying to make was that per our docs > > it would be perfectly acceptable for us to make any cursor NO SCROLL > > implicitly if it means less work for the optimizer. > > Ok, I take that back. The actual quote[1] is: > > "Depending upon the complexity of the query's execution plan, specifying > SCROLL may impose a performance penalty on the query's execution time." > > Clearly that says it can affect execution time, not that we're free to > alter the default behavior at will. > > But speaking of documentation, it doesn't actually say what the default > is. Care update that, or should I formally submit a patch? > > [1] http://www.postgresql.org/docs/8.1/interactive/sql-declare.html Why do that ahead of me making the suggested changes? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Locks
I mean, any C function. ---(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] Scrollable cursors and Sort performance
On Sat, Feb 11, 2006 at 07:47:32PM +, Simon Riggs wrote: > On Sat, 2006-02-11 at 11:44 -0600, Jim C. Nasby wrote: > > On Sat, Feb 11, 2006 at 11:32:02AM -0600, Jim C. Nasby wrote: > > > I think the point that Martijn was trying to make was that per our docs > > > it would be perfectly acceptable for us to make any cursor NO SCROLL > > > implicitly if it means less work for the optimizer. > > > > Ok, I take that back. The actual quote[1] is: > > > > "Depending upon the complexity of the query's execution plan, specifying > > SCROLL may impose a performance penalty on the query's execution time." > > > > Clearly that says it can affect execution time, not that we're free to > > alter the default behavior at will. > > > > But speaking of documentation, it doesn't actually say what the default > > is. Care update that, or should I formally submit a patch? > > > > [1] http://www.postgresql.org/docs/8.1/interactive/sql-declare.html > > Why do that ahead of me making the suggested changes? Because right now it doesn't say what the actual default is. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] SpeedComparison
Has anyone here seen this one before? Do the values appear realistic? http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upcoming re-releases
* Tom Lane ([EMAIL PROTECTED]) wrote: > Martijn van Oosterhout writes: > > These no real way around this. The only real option would be moving to > > a home directory but that would require knowing the username the server > > is running under... > > And the problem would still exist, with even less chance of solution, > for TCP connections which are probably the majority of real-world usage. > If you're concerned about this sort of attack I think it has to be > solved in the protocol, not by reliance on socket placement. > > I'm not sure whether our current SSL support does a good job of this > --- I think it only tries to check whether the server presents a > valid certificate, not which cert it is. Possibly Kerberos does more, > but I dunno a thing about that... With AP_OPTS_MUTUAL_REQUIRED (which we and most other Kerberos client/server setups use), the user and the server authenticate to each other. The server has to prove it has access to the same key the KDC has on file for the server, and the client has to do the same. We really should support the various options for SSL checking. Options to define trusted CAs, checking CN against what the IP address of the server resolves to, mapping of DN to username (perhaps regexp based), explicitly certificate -> username mapping, etc... Of course, it'd be nice to get SASL support and move to GSSAPI instead of the Kerberos API... :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Upcoming re-releases
* Magnus Hagander ([EMAIL PROTECTED]) wrote: > The way our Kerberos implementation is done, it does *not* validate the > server, just the client. If you want server verification, you must use a > combination of both Kerberos and SSL. Eh? We use mutual authentication in Kerberos... Stephen signature.asc Description: Digital signature
Re: [HACKERS] Raising the Pl/Perl required version
See here: http://archives.postgresql.org/pgsql-hackers/2005-10/msg00438.php I don't mind requiring 5.6, but I do want to think carefully about the implications of changing the declaration of $_TD from "my" to "our", especially if multiple triggers fire. Is there a danger we might clobber one? cheers andrew Greg Sabino Mullane wrote: This was raised before, but I can't find the exact thread. I'd like to re-open the idea of boosting the minimum Perl version for PL/Perl to 5.6. My primary motivation is to provide use of "our" for the %TD hash, as mentioned before (cannot find the email right now). Being as 5.6 was released nearly six years ago, in March of 2000, I'm hoping that this won't meet too many objections. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SpeedComparison
On 2/11/06, Andrej Ricnik-Bay wrote: > Has anyone here seen this one before? Do the values > appear realistic? > > http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison The values appear to originate from an intrsinsically flawed test setup. Just take the first test. The database has to do 1000 commits. That means 1000 I/O operations. There is no way that a 7200 RPM disk can do that in the time that that test says it took. It is reasonable to say that a disk can do 1 I/O operation per rotation, which means that any test result below 9 seconds is untrustworthy. Jochem ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Upcoming re-releases
> > The way our Kerberos implementation is done, it does *not* validate > > the server, just the client. If you want server > verification, you must > > use a combination of both Kerberos and SSL. > > Eh? We use mutual authentication in Kerberos... We do? That's good then :-) I was told by someone that we don't. Never really checked into it, since all my installations already use SSL for that. So, I'll retract my comment ;) //Magnus ---(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] SpeedComparison
Andrej Ricnik-Bay wrote: > Has anyone here seen this one before? Do the values > appear realistic? > > http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison Some of the particularly bad test results for PostgreSQL may be related to using the default memory configuration and never having run ANALYZE. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Upcoming re-releases
* Magnus Hagander ([EMAIL PROTECTED]) wrote: > > > The way our Kerberos implementation is done, it does *not* validate > > > the server, just the client. If you want server > > verification, you must > > > use a combination of both Kerberos and SSL. > > > > Eh? We use mutual authentication in Kerberos... > > We do? That's good then :-) I was told by someone that we don't. Never > really checked into it, since all my installations already use SSL for > that. So, I'll retract my comment ;) We pass in 'MUTUAL_REQUIRED' to krb5_sendauth and check the return value of it correctly... I'd be really curious why 'someone' felt we weren't doing mutual authentication... I don't see anything obvious.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Getting the length of varlength data using PG_DETOAST_DATUM_SLICE
Jeremy Drake wrote:
It looks like pg_column_size gives you the actual size on disk, ie after
compression.
What looks interesting for you would be byteaoctetlen or the function it
wraps, toast_raw_datum_size. See src/backend/access/heap/tuptoaster.c.
pg_column_size calls toast_datum_size, while byteaoctetlen/textoctetlen
calls toast_raw_datum_size.
On Sat, 11 Feb 2006, Bruce Momjian wrote:
Have you looked at the 8.1.X buildin function pg_column_size()?
---
Mark Dilger wrote:
Hello, could anyone tell me, for a user contributed variable length data type,
how can you access the length of the data without pulling the entire thing from
disk? Is there a function or macro for this?
As a first cut, I tried using the PG_DETOAST_DATUM_SLICE macro, but to no avail.
grep'ing through the release source for version 8.1.2, I find very little
usage of the PG_GETARG_*_SLICE and PG_DETOAST_DATUM_SLICE macros (and hence
little clue how they are intended to be used.) The only files where I find them
referenced are:
doc/src/sgml/xfunc.sgml
src/backend/utils/adt/varlena.c
src/include/fmgr.h
I am writing a variable length data type and trying to optimize the disk usage
in certain functions. There are cases where the return value of the function
can be determined from the length of the data and a prefix of the data without
fetching the whole data from disk. (The prefix alone is insufficient -- I need
to also know the length for the optimization to work.)
The first field of the data type is the length, as follows:
typedef struct datatype_foo {
int32 length;
char data[];
} datatype_foo;
But when I fetch the function arguments using
datatype_foo * a = (datatype_foo *)
PG_DETOAST_DATUM_SLICE(PG_GETARG_DATUM(0),0,BLCKSZ);
the length field is set to the length of the fetched slice, not the length of
the data as it exists on disk. Is there some other function that gets the length
without pulling more than the first block?
Thanks for any insight,
--Mark
---(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
Ok, for anyone following the thread, this code works for me:
int true_size_arg_zero = toast_raw_datum_size(PG_GETARG_DATUM(0));
int true_size_arg_one = toast_raw_datum_size(PG_GETARG_DATUM(1));
Be sure to #include "access/tuptoaster.h"
Thanks Jeremy!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Scrollable cursors and Sort performance
> On Sat, 2006-02-11 at 11:44 -0600, Jim C. Nasby wrote: >> But speaking of documentation, it doesn't actually say what the default >> is. Care update that, or should I formally submit a patch? >> >> [1] http://www.postgresql.org/docs/8.1/interactive/sql-declare.html Actually, if you submit a patch that says either "SCROLL is the default" or "NO SCROLL is the default", it will be rejected as incorrect. The reason is that the default behavior is different from either of these, as is explained in the NOTES section. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SpeedComparison
Jochem van Dieten <[EMAIL PROTECTED]> writes: > On 2/11/06, Andrej Ricnik-Bay wrote: >> http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison > The values appear to originate from an intrsinsically flawed test setup. > Just take the first test. The database has to do 1000 commits. That > means 1000 I/O operations. There is no way that a 7200 RPM disk can do > that in the time that that test says it took. It is reasonable to say > that a disk can do 1 I/O operation per rotation, which means that any > test result below 9 seconds is untrustworthy. Disk lying about write-complete, no doubt. Of course that probably affects all the databases about the same. The fact that it's on Windows is probably handicapping us noticeably, considering that that port still isn't well optimized. Test 8's problem is most likely lack of an ANALYZE --- although when I tried to duplicate it, I got a bitmap index scan, which shouldn't be horrendously slow. There's something fishy about that one. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [HACKERS] What do the Windows pg hackers out there like
Tom Lane wrote: Mark Kirkwood <[EMAIL PROTECTED]> writes: I was doing exactly this about a year ago and used Mingw. The only annoyance was that I could compile everything on Linux in about 3 minutes (P4 2.8Ghz), but had to wait about 60-90 minutes for the same thing on Windows 2003 Server! (also a P4 2.8Ghz...). So I used to build a 'go for coffee' task into the build and test cycle. Youch! That seems unbelievably bad, even for Microsloth. Did you ever identify what was the bottleneck? No - I was connecting using an RDB client from a Linux box (over a LAN), so was never sure how much that was hurting things... but (as noted by Magnus) the compiler itself is noticeablely slower (easily observed during the 'configure' step). cheers Mark ---(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] to_char and i18n
Patch applied. Thanks. --- Euler Taveira de Oliveira wrote: > --- Euler Taveira de Oliveira <[EMAIL PROTECTED]> escreveu: > > > I have a patch like this. But this was for 7.4.x. I have to take a > > look > > at it. > > > The patch is attached. It implements day and month i18n. I fixed a few > misspelling comments. Docs is attached too. > > template1=# select to_char(now(), 'Day, DD Month '); >to_char > -- > Sunday , 25 December 2005 > (1 registro) > > template1=# select to_char(now(), 'TMDay, DD TMMonth '); > to_char > --- > Domingo, 25 Dezembro 2005 > (1 registro) > > template1=# > > > Comments? > > Euler Taveira de Oliveira > euler[at]yahoo_com_br > > > > > > > > > ___ > Yahoo! doce lar. Fa?a do Yahoo! sua homepage. > http://br.yahoo.com/homepageset.html Content-Description: 1242239392-i18n-date.diff [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Scrollable cursors and Sort performance
On Sat, Feb 11, 2006 at 07:50:22PM -0500, Tom Lane wrote: > > On Sat, 2006-02-11 at 11:44 -0600, Jim C. Nasby wrote: > >> But speaking of documentation, it doesn't actually say what the default > >> is. Care update that, or should I formally submit a patch? > >> > >> [1] http://www.postgresql.org/docs/8.1/interactive/sql-declare.html > > Actually, if you submit a patch that says either "SCROLL is the default" > or "NO SCROLL is the default", it will be rejected as incorrect. The > reason is that the default behavior is different from either of these, > as is explained in the NOTES section. Ok, so *that's* where the bit about the query plan being simple enough. Based on that, ISTM that it should be premissable for us to decide that a cursor requiring a sort isn't "simple enough" to support SCROLL. In any case, here's a patch that makes the non-standard behavior easier for people to find. -- 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 Index: doc/src/sgml/ref/declare.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/declare.sgml,v retrieving revision 1.34 diff -u -r1.34 declare.sgml --- doc/src/sgml/ref/declare.sgml 18 Jan 2006 06:49:26 - 1.34 +++ doc/src/sgml/ref/declare.sgml 12 Feb 2006 05:21:30 - @@ -129,7 +129,9 @@ execution plan, specifying SCROLL may impose a performance penalty on the query's execution time. NO SCROLL specifies that the cursor cannot be - used to retrieve rows in a nonsequential fashion. + used to retrieve rows in a nonsequential fashion. The default is to + allow scrolling, but this is not the same as specifying + SCROLL. See for more details. @@ -198,7 +200,7 @@ - + Notes ---(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] SpeedComparison
On Sat, Feb 11, 2006 at 08:06:56PM -0500, Tom Lane wrote: > Jochem van Dieten <[EMAIL PROTECTED]> writes: > > On 2/11/06, Andrej Ricnik-Bay wrote: > >> http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison > > > The values appear to originate from an intrsinsically flawed test setup. > > > Just take the first test. The database has to do 1000 commits. That > > means 1000 I/O operations. There is no way that a 7200 RPM disk can do > > that in the time that that test says it took. It is reasonable to say > > that a disk can do 1 I/O operation per rotation, which means that any > > test result below 9 seconds is untrustworthy. > > Disk lying about write-complete, no doubt. Of course that probably > affects all the databases about the same. > > The fact that it's on Windows is probably handicapping us noticeably, > considering that that port still isn't well optimized. > > Test 8's problem is most likely lack of an ANALYZE --- although when > I tried to duplicate it, I got a bitmap index scan, which shouldn't be > horrendously slow. There's something fishy about that one. FWIW, here's a thread about this test: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg12945.html Part of the problem seems to be related to psql; he was able to run test 8 in about 5 seconds using pgAdmin: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg12955.html -- 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 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] FW: PGBuildfarm member snake Branch HEAD Status changed
How do people feel about having tabs displayed as 0x09? Should they be a literal tab? --- Teodor Sigaev wrote: > *** > *** 2463,2469 >http://www.google.com/foo.bar.html"; target="_blank">YES > ff-bg > > ! document.write(15); > > > > --- 2463,2469 > http://www.google.com/foo.bar.html"; target="_blank">YES > > ff-bg > > ! \x09document.write(15); > > > > > > \x09 is a '\t'. Is it now prohibited(non-printable) symbol? > > > > -- > Teodor Sigaev E-mail: [EMAIL PROTECTED] > WWW: http://www.sigaev.ru/ > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed
Bruce Momjian writes: > How do people feel about having tabs displayed as 0x09? Should they be > a literal tab? If they're a literal tab they'll mess up the formatting that we just so painstakingly put in. I'd personally vote for \t rather than \x09, but other than that I agree with doing something like this in formatted psql output. OTOH ... there's a rather nasty problem with this whole concept, which is that you can't readily tell whether the data was a tab or the actual string "\x09". The only way to make it unambiguous would be to start doubling backslashes, which I think is a complete nonstarter on backwards compatibility grounds :-( 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
