Re: [HACKERS] Some notes about the index-functions security vulnerability
On 1/8/08, Tom Lane [EMAIL PROTECTED] wrote: The other issue that ought to be on the TODO radar is that we've only plugged the hole for the very limited case of maintenance operations that are likely to be executed by superusers. If user A modifies user B's table (via INSERT/UPDATE/DELETE), there are a lot of bits of code that are controlled by B but will be executed with A's permissions; so A must trust B a whole lot. This general issue has been understood for quite some time, I think, but maybe it's time to make a serious push to solve it. High-level brain dump, does not cover all the use cases I'm sure... Given Invoker executing code created by Definer, there are basically 3 situations: 1) Definer does not trust Invoker, but Invoker trusts Definer. - use Invoker's permission set This is probably the case for most system/library generic functions, such as the various trigger templates They are typically owned by a superuser 2) Invoker does not trust Definer, but Definer trusts Invoker. - use Definer's permission set This case covers most triggers, since they are there to maintain Definer's data, and Invoker's input is inherently controlled. 3) Neither trusts the other. - use the intersection of Invoker's and Definer's permission sets This is essentially the case for any arbitrary functions floating around, where Invoker's input is not inherently controlled, and Definer is an unknown entity. Situation 1 is covered by SECURITY INVOKER, and 2 is covered by SECURITY DEFINER. Suppose another function option is added for situation 3, SECURITY INTERSECTION. Also suppose there is a new role option, TRUSTED (needs a better name). * A function is created with SECURITY INTERSECTION by default. * A function's owner can choose SECURITY DEFINER. * Only a role with TRUSTED can choose SECURITY INVOKER. * Only the superuser has TRUSTED by default. The idea here is that by default, neither Invoker nor Definer need to be terribly concerned. If Definer is creating the function specifically to operate on its own data, and is checking input appropriately, SECURITY DEFINER will allow it to work. If Definer is creating the function for generic use purposes, Invoker will want to apply it to its own data, and SECURITY INVOKER is appropriate for that. A Definer's trustworthiness for all Invokers is determined by the superuser via the TRUSTED role option. Offhand I can cite the following ways in which B could exploit A's privileges: * triggers Ideally Invoker's permission set would be replaced by the trigger owner's for the duration of the call. However it doesn't look like there actually is an owner concept for triggers, despite there being a TRIGGER permission for the associated table. The next appropriate option is to assign the table owner's permission set to Invoker. In the case of functions marked SECURITY INVOKER, this leaves a hole: a role that has TRIGGER permission on the table can elevate its permissions to that of the table owner's when calling that function. If the role with TRIGGER permission is not TRUSTED, it can only create new functions with SECURITY INTERSECTION, which will result in executing with its own permissions at best. This seems reasonable. * functions in indexes * functions in CHECK constraints * functions in DEFAULT expressions * functions in rules (including VIEW definitions) Replace the Invoker's permission set with the table owner's for the duration of the call. These all require you to be the owner of the associated object, so there is no potential hole as with triggers. The first three of these are probably not too difficult to solve: we could switch privilege state to the table owner before executing such functions, because the backend knows perfectly well when it's doing each of those things. But default expressions and rules get intertwined freely with query fragments supplied by the calling user, and it's not so easy to see how to know what to execute as which user. I'll just wave my hands wildly here and say functions in expressions supplied by the Invoker magically avoid being called with the object owner's permission set instead. I don't know how, they just do :) What this doesn't allow is actually executing things like VIEW expressions using the calling user's permission set. I don't have an actual use case for that, but I feel it's a problem somehow. I've also completely avoided things like CURRENT_USER by talking about permission sets only. ---(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] concurrency in psql
On 1/1/08, kenneth d'souza [EMAIL PROTECTED] wrote: I am trying to understand concurrency and mvcc with a small example in psql. Note that the big advantage to MVCC is that writers do not block readers. Since your example consists of all writers, MVCC isn't doing much for you. Isolation_level is read commited. There are 4 psql session by the same Role. I am executing the commands in the below sequence. Session 1: insert into kentab values ( 1,'A'); commit; begin; update kentab set name='Ad' where id=1; Transaction 1 has competed the UPDATE, but not committed yet. session 2: begin; update kentab set name='A2d' where id=1; Transaction 2 does not know how to do the update yet. Transaction 1 has already locked the row for changes, but because it has not committed yet, transaction 2 does not know what the current values of the row are. (In this example it doesn't really matter, but imagine if you were using where name = 'A': either transaction 1 will comit a change to the name, so transaction 2 must skip this row, or transaction 1 will roll back and transaction 2 must update.) Transaction 2 is waiting for transaction 1 to finish, so it knows whether to use the old or new version of the row. session 3: begin; update kentab set name='A3d' where id=1; Same problem as transaction 2. It is waiting for transaction 1 to finish. Session 1: commit; Transaction 1 has committed its changes, so all waiting transactions can use the new value of the row. Either transaction 2 or transaction 3 will continue now, and the other one will keep waiting. (Which one goes first is indeterminate.) session 4: begin; update kentab set name='A4d' where id=1; Same problem as before. It is waiting for transaction 2 or 3 to finish, and might have to wait for both. I want to now commit in Session 3. Firstly I don't see the command prompt. That means transaction 3 is still waiting. Transaction 2 probably continued with its UPDATE (in psql, it would say UPDATE 1 and give you a prompt), so transaction 3 is waiting for it now. If you repeat this test, transaction 3 may get to go before transaction 2. Morever, despite executing commit; it is not commiting and ending before session2 or session4. The COMMIT cannot be executed until the UPDATE is finished. The UPDATE is still waiting. I have tried Select for Update too but it is behaving the same. SELECT ... FOR UPDATE performs the same kind of lock as an UPDATE does, just without changing anything. ---(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] Spoofing as the postmaster
On 12/28/07, Andrew Sullivan [EMAIL PROTECTED] wrote: On Sat, Dec 29, 2007 at 02:09:23AM +1100, Naz Gassiep wrote: In the web world, it is the client's responsibility to ensure that they check the SSL cert and don't do their banking at www.bankofamerica.hax0r.ru and there is nothing that the real banking site can do to stop them using their malware infested PC to connect to the phishing site. The above security model is exactly how we got into the mess we're in: relying entirely on the good sense of a wide community of users is how compromises happen. Strong authentication authenticates both ways. For instance, the web world you describe is not the only one. Banks who take security seriously have multiple levels of authentication, have trained their users how to do this, and regularly provide scan tools to clients in an attempt (IMO possibly doomed) to reduce the chances of input-device sniffing. I don't follow. What are banks doing on the web now to force clients to authenticate them, and how is it any different from the model of training users to check the SSL certificate? There's a fundamental problem that you can't make someone else do authentication if they don't want to, and that's exactly the situation clients are in. I don't see how this can possibly be fixed anywhere other than the client. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spoofing as the postmaster
On 12/28/07, Tom Lane [EMAIL PROTECTED] wrote: Trevor Talbot [EMAIL PROTECTED] writes: There's a fundamental problem that you can't make someone else do authentication if they don't want to, and that's exactly the situation clients are in. I don't see how this can possibly be fixed anywhere other than the client. The point of requiring authentication from the server side is that it will get people to configure their client code properly. Then if a MITM attack is subsequently attempted, the client code will detect it. But this is essentially just an education/training issue; the security model itself is unchanged. Bank web sites are only going to accept clients via SSL, but if a client does not try to authenticate the site, whether it connects via SSL or not is rather irrelevant. I have no problem with the idea of encouraging clients to authenticate the server, but this configuration doesn't help with defaults. It's just available as a tool for site administrators to use. Also, getting people in the habit of setting up for mutual authentication does have value in that scenario too; it makes the new user perhaps a bit more likely to distrust a server that isn't presenting the right certificate. I see Naz's argument as addressing this goal. The problem with forcing authentication is that it's an all-or-nothing proposition: either the server and all the clients do it, or none of them do. That's fine when you control all the pieces and are willing to put in the work to configure them all, but not effective for encouraging default behavior. Instead, give the server credentials by default, but let clients choose whether to request them. That makes deployment easier in that all you have to do is configure clients as needed to get authentication of the server. Easier deployment means it's more likely to be used. IOW, put up both http and https out of the box. You might even want to have newer clients default to caching credentials on the first connect. That still doesn't change the security model, but should be more effective at getting clients to do something useful by default. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spoofing as the postmaster
On 12/23/07, Tomasz Ostrowski [EMAIL PROTECTED] wrote: On Sun, 23 Dec 2007, Magnus Hagander wrote: I'm just surprised that people are actually surprised by this. To me, it's just a natural fact that happens to pretty much all systems. And a good reason not to let arbitrary users run processes that can bind to something on your server. Not everybody works for Enterprise, where price does not matter. I cannot afford a dedicated servers for database, DNS, e-mail, antispam, firewall, file, WWW etc. Even administrative overhead would be too much for one person IT staff. I have to run all of this and much more on one machine, so I'm interested in limiting rights for a user for example running WWW, so when, god forbid, compromized, it'd limit damage. I am also not able to run sophisticated security frameworks, limiting every user rights to just what they need, as maintaining it would require a security full-timer. So I'm not very fond of this insecure by default, it's your problem to make it secure attitude. I'm the one who reported this. It's not that; it's the fact that if anyone can run a service on a computer, then anyone connecting to that computer won't necessarily know whose service they're connecting to, is a basic thing that should only take a moment's thought to recognize. I wouldn't knock anyone for not automatically realizing it can be a threat to security, but it's so very common it's hard to see why anyone would really be *surprised* by it. SSL and SSH both address the problem of the client wanting to verify the server, so usually being aware of either of those is enough to make someone aware of the issue in general. There is no default or automatic solution because the basic issue is one of trust, which requires an external procedure to address. (SSH generates a key on its own, but you are responsible for transferring the signature to the remote client in a secure manner so they can verify it. SSL typically has an external company generate your key after being paid to verify your identity, and presumably the remote client already trusts that company. You can also use the SSH approach with SSL.) There are various platform-specific security features that might be useful, like reserved port ranges and file permissions, but they are so specific to the scenario they're designed for that it's hard to create a generic solution that works well by default -- especially if you want to run without requiring administrative privileges in the first place. Having the adminstrator be responsible for organizing what they need is the only thing that seems to work in practice, since the requirements are so different for different environments. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pgwin32_open returning EINVAL
On 12/20/07, Magnus Hagander [EMAIL PROTECTED] wrote: ereport(WARNING, (errmsg(could not open file \%s\: %s violation, fileName, (GetLastError() == ERROR_SHARING_VIOLATION)?_(sharing):_(lock)), errdetail(Continuing to retry for 30 seconds.), errhint(You may have antivirus, backup or similar software interfering with the database.))); Without looking myself, is it possible for errhint() or errdetail() to do something that affects GetLastError()? It's like errno, checking it very far away from the call site makes me nervous. ---(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] pgwin32_open returning EINVAL
On 12/20/07, Magnus Hagander [EMAIL PROTECTED] wrote: On Thu, Dec 20, 2007 at 04:39:55AM -0800, Trevor Talbot wrote: On 12/20/07, Magnus Hagander [EMAIL PROTECTED] wrote: ereport(WARNING, (errmsg(could not open file \%s\: %s violation, fileName, (GetLastError() == ERROR_SHARING_VIOLATION)?_(sharing):_(lock)), errdetail(Continuing to retry for 30 seconds.), errhint(You may have antivirus, backup or similar software interfering with the database.))); Without looking myself, is it possible for errhint() or errdetail() to do something that affects GetLastError()? It's like errno, checking it very far away from the call site makes me nervous. I guess, but it shouldn't matter. We're giong to loop right back up and do a new CreateFile() after this, which will overwrite it again. I mean for the purposes of the report. I'm worried the message might say it's a lock violation when it's really a sharing violation. ---(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] pgwin32_open returning EINVAL
On 12/20/07, Magnus Hagander [EMAIL PROTECTED] wrote: On Thu, Dec 20, 2007 at 04:39:55AM -0800, Trevor Talbot wrote: On 12/20/07, Magnus Hagander [EMAIL PROTECTED] wrote: ereport(WARNING, (errmsg(could not open file \%s\: %s violation, fileName, (GetLastError() == ERROR_SHARING_VIOLATION)?_(sharing):_(lock)), errdetail(Continuing to retry for 30 seconds.), errhint(You may have antivirus, backup or similar software interfering with the database.))); Without looking myself, is it possible for errhint() or errdetail() to do something that affects GetLastError()? It's like errno, checking it very far away from the call site makes me nervous. Wouldn't we then have the same problem for every place that does a %i and report GetLastError() or errno? And we have a *lot* of those... I would've thought the framework thinks of that, but I haven't actually verified that. A function's arguments are evaluated before the call, so that's safe in general. What is implementation-specific is the order of evaluation of different arguments, and I don't know if the parentheses above override that. On 12/20/07, Magnus Hagander [EMAIL PROTECTED] wrote: On Thu, Dec 20, 2007 at 10:11:10AM -0500, Tom Lane wrote: Hmm ... the macro framework is designed so that the arguments get evaluated before anything very interesting happens, but it might be better to use a variable anyway --- for onm thing we could get rid of the redundant GetLastError calls in the test in front of this. Sounds like it was already thought of then. I'd expect the compiler to optimize away those, but I'll make it a var anyawy. It can't; it's an opaque callout to kernel32.dll, and there's nothing that tells the optimizer when you can expect to get the same result. That said, it's cheaper than it looks, since the error code is stored at a fixed location in thread-specific VM space. I guess it wasn't interesting enough to make an MSVC intrinsic instead of keeping it a system detail. ---(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] EXPLAIN ANALYZE printing logical and hardware I/O per-node
On 12/17/07, Decibel! [EMAIL PROTECTED] wrote: Also, has anyone looked into adding a class of system calls that would actually tell us if the kernel issued physical IO? I find it hard to believe that other RDBMSes wouldn't like to have that info... Non-blocking style interfaces can help here. On Windows, for instance, a read returns data at the call site if it was satisfied by cache, instead of invoking the asynchronous notification. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] WORM and Read Only Tables (v0.1)
On 12/11/07, Simon Riggs [EMAIL PROTECTED] wrote: Compressed Tablespaces Using a streaming library like zlib, it will be easy to read/write data files into a still-usable form but with much reduced size. Access to a compressed table only makes sense as a SeqScan. That would be handled by introducing tablespace-specific access costs, discussed below. Indexes on compressed tables would still be allowed, but would hardly ever be used. I've actually been wanting this lately, for a couple reasons. One is reduced disk footprint, but the other is reduced I/O, similar to how TOAST helps with large fields now. (In my particular scenario, TOAST can't help due to small field sizes.) It would be useful to have available even on read/write data. To that end, it would probably make more sense to use a block compression algorithm rather than a streaming one. Block-based algorithms can generally get better compression than streaming ones as well, at least when fed large enough blocks. I'm not familiar with the implementation issues, other than the obvious variable block sizes make the I/O subsystem look very different, so I don't know if there's a major tradeoff between the two strategies (even just for read-only). I'm open to arguments that we don't need this at all because filesystem utilities exist that do everything we need. You're experience will be good to hear about in regard to this feature. Some filesystems do support transparent compression, but they're not always available. It would be nice to have compression on unsophisticated systems with cheap hardware. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [EMAIL PROTECTED]: Re: [pgsql-es-ayuda] SLL error 100% cpu]
On 12/11/07, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I dunno anything about how to fix the real problem (what's winsock error 10004?), but I don't think he'd be seeing full speed log filling in 8.2.5. WSAEINTR, A blocking operation was interrupted by a call to WSACancelBlockingCall. Offhand I'd take it as either not entirely sane usage of a network API, or one of the so very many broken software firewalls / network security products. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Problem with ControlFileData structure being ABI dependent
On 12/7/07, Dave Page [EMAIL PROTECTED] wrote: Tom Lane wrote: AFAIK, time_t is a Unix-ism, so it's pretty unlikely to be used in the APIs of anything on Windows. Oh, it is. It's confined to the C Runtime libraries, not part of the Windows API proper. (Three exceptions: IP Helper uses the type, but the docs contain a warning; a DHCP API that came along after the change to 64bit; an obsoleted Wbem class library for C++.) The CRT has been causing compatibility problems in mixed-tools projects for years. I find Microsoft's incessant changes so irritating that I go out of my way to avoid using it in any project I intend to deploy. It's just one of those things you end up having to deal with somehow :( ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] String encoding during connection handshake
On 11/28/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Wed, Nov 28, 2007 at 05:54:05PM +0200, [EMAIL PROTECTED] wrote: Regarding the problem of One True Encoding, the answer seems obvious to me: use only one encoding per database cluster, either UTF-8 or UTF-16 or another Unicode-aware scheme, whichever yields a statistically smaller database for the languages employed by the users in their data. This encoding should be a one time choice! De facto, this is already happening now, because one cannot change collation rules after a cluster has been created. Umm, each database in a cluster can have a different encoding, so there is no such thing as the cluster's encoding. You can certainly argue that it should be a one time choice, but I doubt you'll get people to remove the possibilites we have now. If fact, if anything we'd probably go the otherway, allow you to select the collation on a per database/table/column level (SQL complaince requires this). To be clear, what sulfinu is really advocating is convergence on Unicode period, which is the direction most international projects are moving, when they can. PostgreSQL's problem is that it (and AFAICT POSIX) conflates encoding with locale, when the two are entirely separate concepts. I'm not entirely sure how that's supposed to solve the client authentication issue though. Demanding that clients present auth data in UTF-8 is no different than demanding they present it in the encoding it was entered in originally... ---(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] String encoding during connection handshake
On 11/28/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On Wednesday 28 November 2007, Trevor Talbot wrote: I'm not entirely sure how that's supposed to solve the client authentication issue though. Demanding that clients present auth data in UTF-8 is no different than demanding they present it in the encoding it was entered in originally... Oh no, it's a big difference: PREDICTABILITY! Why must I guess the encoding used by the administrator? What if he's Chinese? Instead, I know the cluster's encoding, just as I know the server name and the TCP port. And the connection handshake carries on without misunderstandings (read wrong encoding). What if the user and client program is Chinese too? Not everything is developed in an environment where UTF-8 support is easily available. Either way, it is a demand on the client, and not necessarily a simple one. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] String encoding during connection handshake
On 11/28/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Yes, you support (and worry about) encodings simply because of a C limitation dating from 1974, if I recall correctly... In Java, for example, a char is a very well defined datum, namely a Unicode point. While in C it can be some char or another (or an error!) depending on what encoding was used. The only definition that stands up is that a char is a byte. Its interpretation is unsure and unsafe (see my original problem). It's not really that simple. Java, for instance, does not actually support Unicode characters / codepoints at the base level; it merely deals in UTF-16 code units. (The critical difference is in surrogate pairs.) You're still stuck dealing with a specific encoding even in many modern languages. PostgreSQL's encoding support is not just about languages though, it's also about client convenience. It could simply choose a single encoding and parrot data to and from the client, but it also does on-the-fly conversion when a client requests it. It's a very useful feature, and many mature networked applications support similar things. An easy example is the World Wide Web itself. I implied that a cluster should have a single encoding that covers the whole Unicode set. That would certainly satisfy everybody. Note that it might not. Unicode does not encode *every* character, and in some cases there is no round-trip mapping between it and other character sets. The result could be a loss of semantic data. I suspect it actually would satisfy everyone in PostgreSQL's case, but it's not something you can assume without checking. This has nothing to do with C by the way. C has many features that allow you to work with different encodings. It just doesn't force you to use any particular one. Yes, my point exactly! C forces you to worry about encoding. I mean, if you're not an ASCII-only user ;) For a networked application, you're stuck worrying about the encoding regardless of language. UTF-8 is the most common Internet transport, for instance, but that's not the native internal encoding used by Java and most other Unicode processing platforms to date. That's fairly simple since it's still only a single character set, but if your application domain predates Unicode, you can't avoid dealing with the legacy encodings at some level anyway. As I implied earlier, I do think it would be worthwhile for PostgreSQL to move toward handling it better, so I'm not saying this is a bad idea. It's just that it's a much more complex topic than it might seem at first glance. I'm glad you got something working for you. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Simplifying Text Search
On 11/15/07, Peter Eisentraut [EMAIL PROTECTED] wrote: In practice, the search pattern will mostly be provided dynamically from some user input, so you could conceivably be able to modify the search patterns more readily than the entire queries in your application. Anyway, it's just an idea for those who need it. Ah, I see what you mean, like for a simple web forum that only knows LIKE searches now. It may be easier to adjust the DB to do the intended thing instead of trying to change the entire forum. I wasn't thinking of those narrow cases. ---(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] Spinlock backoff algorithm
On 11/14/07, Tom Lane [EMAIL PROTECTED] wrote: The other problem with using modulo is that it makes the result depend mostly on the low-order bits of the random() result, rather than mostly on the high-order bits; with lower-grade implementations of random(), the lower bits are materially less random than the higher. Now admittedly high-grade randomness is probably not too important for this specific context, but I dislike putting in poor coding practices that someone might see and copy without thinking... If there's a dependency on a particular quality of random() implementation, why not just include one? Mersenne Twister is easy, while not being cryptographic strength. http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/emt.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Simplifying Text Search
On 11/14/07, Peter Eisentraut [EMAIL PROTECTED] wrote:
I wrote:
What we'd need is a way to convert a LIKE pattern into a tsquery
('%foo%bar%' = 'foo bar'). Then you might even be able to sneak
index-optimized text search into existing applications. Might be worth a
try.
Here is how this could work:
CREATE FUNCTION likepattern_to_tsquery(text) RETURNS tsquery
[...]
But that coversion itself is fundamentally flawed, is the problem.
'foo bar'
'fooandbar'
'barfoo and foobar'
'%foo%bar%' matches all 3.
'foo bar' matches only the first.
If the application currently using LIKE actually wants a word-based
search, it should probably just convert to using tsearch wholesale,
since it doesn't work as intended now. If it actually wants wildcard
matching behavior, it can't use tsearch at all.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Simplifying Text Search
On 11/13/07, Peter Eisentraut [EMAIL PROTECTED] wrote:
Am Dienstag, 13. November 2007 schrieb Gregory Stark:
Peter Eisentraut [EMAIL PROTECTED] writes:
What we'd need is a way to convert a LIKE pattern into a tsquery
('%foo%bar%' = 'foo bar'). Then you might even be able to sneak
index-optimized text search into existing applications. Might be worth a
try.
I don't think that's the right direction to go. Notably %foo%bar% isn't
the same thing as foo bar. Also most tsearch queries can't be expressed
as LIKE patterns anyways.
The requirement is to express LIKE patterns as tsearch queries, not the other
way around.
How? LIKE queries are incapable of expressing word boundaries, do not
support substitution, and are implicitly ordered. tsearch queries
operate entirely on word boundaries, may substitute words, and are
unordered.
I don't see the two as even working in the same space, let alone be
convertable for optimization purposes. If the idea was just to use a
tsearch index as an initial filter, then running LIKE on the results,
dictionary-based substitution makes that unreliable.
---(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] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 11/12/07, Magnus Hagander [EMAIL PROTECTED] wrote: On Sat, Nov 10, 2007 at 03:17:13PM -0800, Trevor Talbot wrote: As for desktop heap, only 65KB of the service heap was allocated, or about 80 bytes per connection. No danger of hitting limits in the kernel memory pools either. As Dave said, it could be that the server version uses a lot less heap per process, which would be another good reason to use server rather than XP to run postgresql. But might there also be other differences, such as some third party (or non-core microsoft) product installed? The XP SP2 machine I tried 8.2.5 on was chewing up about 3.1KB per process, and it's not running anything invasive (AV or otherwise). I've been trying to find out exactly what's in the desktop heap, but I haven't had much luck so far. Apparently Microsoft changed the implementation after Win2000, and didn't bother teaching the public debugging tools about it. The details just don't seem to exist anymore :( ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Clarification reqeusted for select * from a huge table
On 11/12/07, Richard Huxton [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram wrote: I also noticed that it doesn't crash with psql, but it takes a long time to show the first set of records. It takes a long time, even to quit after i pressed 'q'. With oracle SQLPlus, it is quite instantaneous. Imagine, you need a large batch operation. In oracle we can fire the SQL and we can be sure that the client won't crash, but with postgres we have a region of uncertainity. Well, if your client doesn't know if it can handle 1 million rows, maybe it shouldn't ask for them? Isn't that exactly his point? He's talking about the default behavior of clients designed for postgres, one of which is psql. ---(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] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 11/12/07, Magnus Hagander [EMAIL PROTECTED] wrote: On Mon, Nov 12, 2007 at 04:00:04AM -0800, Trevor Talbot wrote: On 11/12/07, Magnus Hagander [EMAIL PROTECTED] wrote: On Sat, Nov 10, 2007 at 03:17:13PM -0800, Trevor Talbot wrote: As for desktop heap, only 65KB of the service heap was allocated, or about 80 bytes per connection. No danger of hitting limits in the kernel memory pools either. As Dave said, it could be that the server version uses a lot less heap per process, which would be another good reason to use server rather than XP to run postgresql. But might there also be other differences, such as some third party (or non-core microsoft) product installed? The XP SP2 machine I tried 8.2.5 on was chewing up about 3.1KB per process, and it's not running anything invasive (AV or otherwise). Then I think we can claim that Server is just better than Workstation in this regard. Maybe we should put that in the FAQ? I think it's safe to claim 2003 is better than XP, but I'm not sure that's enough to generalize into server vs workstation yet. It implies 2000 Server would be better than 2000 Pro, which might not be true. I'm also wondering whether 64bit XP behaves differently, since IIRC it's based on the 2003 kernel. Then there's Vista... Unfortunately I don't have access to any of these versions to test with at the moment. ---(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] Win32 shared memory speed
I've seen several comments about shared memory under Windows being slow, but I haven't had much luck finding info in the archives. What are the details of this? How was it determined and is there a straightforward test/benchmark? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/26/07, I wrote: On 10/26/07, Magnus Hagander [EMAIL PROTECTED] wrote: Can you try the attached patch? See how many backends you can get up to. This patch changes from using a single thread for each backend started to using the builtin threadpool functionality. It also replaces the pid/handle arrays with an i/o completion port. The net result is also, imho, much more readable code :-) The patch looks good; I'm not set up to build yet, but I should be able to test it sometime in the next week. Sorry about the long delay; I retested with the 8.3-beta2 installer, still Win2003 SP2 32bit. I stopped the test at 824 connections because I was about to run out of memory (1.25GB RAM + 3.75GB swap), but postmaster VM space usage was only 191MB. As for desktop heap, only 65KB of the service heap was allocated, or about 80 bytes per connection. No danger of hitting limits in the kernel memory pools either. Available RAM seems like a pretty reasonable limit to me ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] New tzdata available
On 11/8/07, Magnus Hagander [EMAIL PROTECTED] wrote: Andrew Dunstan wrote: Tom Lane wrote: Are Windows users accustomed to having up-to-the-minute timezone information? Maybe there's something I don't know about Microsoft's update practices, but I would have thought that the expectations on that platform would be pretty darn low. No, they push updates fairly aggressively. Of course, that's when they have fixes for the problems ... but I would normally expect them to be well on top of timezone changes. At least for common places. They certainly pushed out TZ updates for the US changes and the NZ changes recently through their Windows Update/Automatic Updates/WSUS service. Unfortunately, until Vista there was no architecture in place to track historical changes. On older versions, the latest zone calendar is the one that's in effect for all dates (so current US rules apply to last year's dates too, and we get inaccurate times for them). The OS services aren't suitable as a replacement for tzdata. I've been wondering lately why it isn't just stored in the database somewhere. ---(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] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/26/07, Magnus Hagander [EMAIL PROTECTED] wrote: Can you try the attached patch? See how many backends you can get up to. This patch changes from using a single thread for each backend started to using the builtin threadpool functionality. It also replaces the pid/handle arrays with an i/o completion port. The net result is also, imho, much more readable code :-) The patch looks good; I'm not set up to build yet, but I should be able to test it sometime in the next week. ---(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] [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/21/07, Magnus Hagander [EMAIL PROTECTED] wrote: I tried generating idle connections in an effort to reproduce Laurent's problem, but I ran into a local limit instead: for each backend, postmaster creates a thread and burns 4MB of its 2GB address space. It fails around 490. Oh, that's interesting. That's actually a sideeffect of us increasing the stack size for the postgres.exe executable in order to work on other things. By default, it burns 1MB/thread, but ours will do 4MB. Never really thought of the problem that it'll run out of address space. Unfortunately, that size can't be changed in the CreateThread() call - only the initially committed size can be changed there. There are two ways to get around it - one is not using a thread for each backend, but a single thread that handles them all and then some sync objects around it. We originally considered this but said we won't bother changing it because the current way is simpler, and the overhead of a thread is tiny compared to a process. I don't think anybody even thought about the fact that it'd run you out of address space... I'd probably take the approach of combining win32_waitpid() and threads. You'd end up with 1 thread per 64 backends; when something interesting happens the thread could push the info onto a queue, which the new win32_waitpid() would check. Use APCs to add new backends to threads with free slots. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/22/07, Magnus Hagander [EMAIL PROTECTED] wrote: Trevor Talbot wrote: I'd probably take the approach of combining win32_waitpid() and threads. You'd end up with 1 thread per 64 backends; when something interesting happens the thread could push the info onto a queue, which the new win32_waitpid() would check. Use APCs to add new backends to threads with free slots. I was planning to make it even easier and let Windows do the job for us, just using RegisterWaitForSingleObject(). Does the same - one thread per 64 backends, but we don't have to deal with the queueing ourselves. Oh, good call -- I keep forgetting the native thread pool exists. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/22/07, Tom Lane [EMAIL PROTECTED] wrote: Magnus Hagander [EMAIL PROTECTED] writes: I was planning to make it even easier and let Windows do the job for us, just using RegisterWaitForSingleObject(). Does the same - one thread per 64 backends, but we don't have to deal with the queueing ourselves. Should be rather trivial to do. How can that possibly work? Backends have to be able to run concurrently, and I don't see how they'll do that if they share a stack. This is about what postmaster does for its SIGCHLD wait equivalent on win32. The 64 comes from Windows' object/event mechanism, which lets you perform a blocking wait on up to that many handles in a single call. Currently postmaster is creating a new thread to wait on only one backend at a time, so it ends up with too many threads. ---(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] Including Snapshot Info with Indexes
On 10/14/07, Gokulakannan Somasundaram [EMAIL PROTECTED] wrote: http://www.databasecolumn.com/2007/09/one-size-fits-all.html The Vertica database(Monet is a open source version with the same principle) makes use of the very same principle. Use more disk space, since they are less costly and optimize the data warehousing. What i meant there was, it has duplicated storage of certain columns of the table. A table with more than one projection always needs more space, than a table with just one projection. By doing this they are reducing the number of disk operations. If they are duplicating columns of data to avoid reading un-necessary information, we are duplicating the snapshot information to avoid going to the table. Was this about Vertica or MonetDB? I saw that article a while ago, and I didn't see anything that suggested Vertica duplicated data, just that it organized it differently on disk. What are you seeing as being duplicated? (This is orthogonal to the current thread; I'm just curious.) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Locale + encoding combinations
On 10/12/07, Dave Page [EMAIL PROTECTED] wrote: Tom Lane wrote That still leaves us with the problem of how to tell whether a locale spec is bad on Windows. Judging by your example, Windows checks whether the code page is present but not whether it is sane for the base locale. What happens when there's a mismatch --- eg, what encoding do system messages come out in? I'm not sure how to test that specifically, but it seems that accented characters simply fall back to their undecorated equivalents if the encoding is not appropriate, eg: [EMAIL PROTECTED]:~$ ./setlc French_France.1252 Locale: French_France.1252 The date is: sam. 01 of août 2007 [EMAIL PROTECTED]:~$ ./setlc French_France.28597 Locale: French_France.28597 The date is: sam. 01 of aout 2007 (the encodings used there are WIN1252 and ISO8859-7 (Greek)). I'm happy to test further is you can suggest how I can figure out the encoding actually output. The encoding output is the one you specified. Keep in mind, underneath Windows is mostly working with Unicode, so all characters exist and the locale rules specify their behavior there. The encoding is just the byte stream it needs to force them all into after doing whatever it does to them. As you've seen, it uses some sort of best-fit mapping I don't know the details of. (It will drop accent marks and choose characters with similar shape where possible, by default.) I think it's a bit more complex for input/transform cases where you operate on the byte stream directly without intermediate conversion to Unicode, which is why UTF-8 doesn't work as a codepage, but again I don't have the details nearby. I can try to do more digging if needed. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone database changes
On 10/10/07, Tom Lane [EMAIL PROTECTED] wrote: Trevor Talbot [EMAIL PROTECTED] writes: Actually, what I meant at least (not sure if others meant it), is storing the value in the timezone it was entered, along with what zone that was. That makes the value stable with respect to the zone it belongs to, instead of being stable with respect to UTC. When DST rules change, the value is in effect reinterpreted as if it were input using the new rules. What happens if the rules change in a way that makes the value illegal or ambiguous (ie, it now falls into a DST gap)? That's a good question. I have a vague memory of something that absolutely needed to accept such values (as this would have to) choosing a reasonable way to interpret them. In the case of jumps forward, e.g. 1:59-3:00, a time of 2:15 is assumed to be on the previous scale, and thus interpreted as 3:15. For overlapping times, it picks one but I don't recall which. Unfortunately I don't remember where I picked that up. It might have been a semi-standard, or it might have been someone's personal theory. Your later example of midnight EDT + 3 months wanting to be midnight EST is a good one, so what I said earlier about internally converting to UTC is not something you want to do eagerly. I'd wondered why upthread Kevin mentioned using separate date and time types instead of just using timestamp; now I know. This point should go in any documentation enhancement too. But perhaps more to the point, please show use-cases demonstrating that this behavior is more useful than the pure-UTC behavior. For storage of actual time observations, I think pure-UTC is unquestionably the more useful. Peter's example of a future appointment time is a possible counterexample, but as observed upthread it's hardly clear which behavior is more desirable in such a case. Actually, it usually is, because a human picked one ahead of time. For example, if the appointment is set for 3pm in London, the London zone is the authoritative one, so that's what you store it in the DB as. If you're viewing it in NZ time, and the NZ DST rules change, so does what you see. If the London rules change, what you see in NZ still changes, but what you see in London does not. Choosing UTC in that scenario only works if the London DST rules don't change. Choosing the referencing timezone (London) when you store the value works if either one changes. If an organization is regularly scheduling such things, they might just settle on UTC anyway to avoid confusion, in which case you store values in UTC and get the same behavior as you do currently. I don't know what this person was doing, but I gather sticky timezones was preferable to them: http://archives.postgresql.org/pgsql-general/2007-08/msg00461.php Thinking that it might have had out of date zone rules brings up an interesting scenario though. Consider a closed (no networking or global interest) filing system in a local organization's office, where it's used to record the minutes of meetings and such via human input. It would seem that the correct time to record in that case is in fact the local time, not UTC. If that system is left alone for years, and does not receive any zone rule updates, it will likely begin storing the wrong UTC values. When the data is later transported out (upgrade, archive, whatever), it will be incorrect unless you use that particular snapshot of the zone rules. That situation might sound a bit contrived, but I think the real point is that even for some records of observed times, the local time is the authoritative one, not UTC. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Timezone database changes
On 10/11/07, Magne Mæhre [EMAIL PROTECTED] wrote: Trevor Talbot wrote: Thinking that it might have had out of date zone rules brings up an interesting scenario though. Consider a closed (no networking or global interest) filing system in a local organization's office, where it's used to record the minutes of meetings and such via human input. It would seem that the correct time to record in that case is in fact the local time, not UTC. If that system is left alone for years, and does not receive any zone rule updates, it will likely begin storing the wrong UTC values. When the data is later transported out (upgrade, archive, whatever), it will be incorrect unless you use that particular snapshot of the zone rules. That situation might sound a bit contrived, but I think the real point is that even for some records of observed times, the local time is the authoritative one, not UTC. ...and for that scenario you have TIMESTAMP WITHOUT TIME ZONE But that doesn't give you DST-sensitive display for free, which is tempting for application use, especially if the application is meant to be suitably generic. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Timezone database changes
On 10/11/07, Gregory Stark [EMAIL PROTECTED] wrote: Tom Lane [EMAIL PROTECTED] writes: Trevor Talbot [EMAIL PROTECTED] writes: On 10/11/07, Magne Mæhre [EMAIL PROTECTED] wrote: Trevor Talbot wrote: That situation might sound a bit contrived, but I think the real point is that even for some records of observed times, the local time is the authoritative one, not UTC. ...and for that scenario you have TIMESTAMP WITHOUT TIME ZONE But that doesn't give you DST-sensitive display for free, which is tempting for application use, especially if the application is meant to be suitably generic. If you are dealing only in local time, what do you need timezone for at all? October 29, 2006, 1:15am: PDT or PST? Even if you ignore overlap points like that, DST status is a piece of semantic information the human retrieving the data may want to know. It doesn't make much sense for an app to avoid the database's perfectly good knowledge of the local timezone to get it. Also note the possibility of coercing one type to the other on-the-fly for display, or using the AT TIME ZONE construct. Sure, but that's simply a workaround like tagging different zones yourself is. This single case isn't terribly important, it's just a non-future-appointment one where remembering the local zone makes sense. If we change it a bit so that it regularly transports data to a central office, you still want to know what time zone it belongs to. Right now, the local office's zone rules matter because you need it to convert to UTC properly. Instead, it should be the central office's zone rules that matter for temporary conversion and reporting, because you really don't want the original data changed at all. The original data is the legitimate record, not the conversion to UTC. This can all be done manually by applications today, of course. It would just be nice to take advantage of PostgreSQL's time zone knowledge more easily in these situations. 2) Specific moment in time (i.e. stored in UTC which is unaffected by time zone rules) 3) Specified time of day in specified time zone (equivalent to #2 except when the time zone rules change) Surely #2 is a must-have. There has to be a data type for representing a fixed moment in time unaffected by any time zone rules. Anything recording events -- which of course occurred at a specific moment in time -- needs it and there are a whole lot of databases which do just that. Actually in my experience most tables have one or sometimes more timestamps of that nature. While I agree that UTC storage is definitely a needed option, I was trying to point out in the scenario above that sometimes an event recorded at a specific moment in time *is* local time. Birth certificates aren't in UTC. Usually there's no practical difference, but there can be a semantic difference. The lack of #3 doesn't seem terribly pressing given how rarely the time zone rules change. Even with the latest shenanigans I don't think anyone's run into any unexpected problems. The link I posted upthread was someone who ran into something unexpected. There wasn't enough detail to figure out what, exactly, just that something related to zones changed and surprised them. And no, I don't think it's urgent either; the current behavior is known and fairly easy to understand. It's just that some applications need a different set of semantics. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone database changes
On 10/11/07, Gregory Stark [EMAIL PROTECTED] wrote: Trevor Talbot [EMAIL PROTECTED] writes: While I agree that UTC storage is definitely a needed option, I was trying to point out in the scenario above that sometimes an event recorded at a specific moment in time *is* local time. Birth certificates aren't in UTC. Usually there's no practical difference, but there can be a semantic difference. Thinking of it as UTC is the wrong way to think about it. A birth occurred at a specific moment in time. You want to record that precise moment, not what it happened to show on the clock at the time. If the clock turns out to have been in the wrong timezone the birth isn't going to move. Neither is the birth certificate. The recorded, legal time of the birth is the one that was written down. If it doesn't happen to match an international notion of current time, that's unfortunate, but it's not subject to arbitrary changes later. Even if it does match, it still belongs to a specific time zone. That's the key semantic point: regurgitating that time as anything other than exactly what it was entered as is simply not correct. Birth dates enter common usage with the time zone stripped. Your birthday doesn't change when you move across a date line, despite the fact that it's tied to the zone you were born in. And yet it's an observed and recorded event, not a predicted appointment. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Timezone database changes
On 10/11/07, Tom Lane [EMAIL PROTECTED] wrote: Trevor Talbot [EMAIL PROTECTED] writes: Neither is the birth certificate. The recorded, legal time of the birth is the one that was written down. If it doesn't happen to match an international notion of current time, that's unfortunate, but it's not subject to arbitrary changes later. Even if it does match, it still belongs to a specific time zone. That's the key semantic point: regurgitating that time as anything other than exactly what it was entered as is simply not correct. I'm not convinced about that. One consideration I think you are failing to account for is that there is a big difference between past and future times, at least in terms of what is likely to be the meaning of a change. The above reasoning might apply to a past time but I think it's bogus for a future time. If the TZ offset for a future time changes, it's likely because of a DST law change, and we are in Peter's what-time-is-the-appointment scenario. A TZ offset for a past time probably should not change, but if it does, it suggests a retroactive data correction. Surely you don't intend to prevent people from fixing bad data? No, but I am mixing some different issues together. The original question of this thread is what happens when the zone rules change for an already-entered time. I contend the answer to that is a symptom of the semantics of how it's treated, which boil down to whether a value is stable relative to a specific zone, or to UTC. Other symptoms include whether it accurately transports, can be retrieved in the same form it was entered in, etc. So the birth certificate argument is for past times, unlikely to have zone rules change, but does need to be tagged with a specific time zone so that it can be returned exactly the same way. The appointment argument is for future times, more likely to have zone rules change, and still needs to be tagged with a specific time zone. That includes transport, which implies that it should never be exposed in any other form. Same semantics really, it's just that one problem is less likely to happen in one of those situations. If something like a birth date is found to be incorrect, it would have to be corrected through official methods, which means some human involvement. The only reasonable thing a database can do is keep it exactly the same as entered until explicitly told otherwise; changing it automatically is equivalent to corruption. If the database is using zone rules that are out of date, and the stamps are stored as local value and zone, only dynamic calculations are affected. When the zone rules are updated, not changing the data is always the correct approach. I don't know if there have ever been retroactive changes to DST laws we could look at, but I could easily see a change like that affecting some things and not others. Individual organizations make their own calls, state entities make varying decisions after gigantic reviews, etc. It would not surprise me at all to see yearly permits retroactively change, lifetime certificates stay the same because they don't want to reprint stuff, except the modern computerized department that doesn't need to reprint much of anything, etc. The correct result is subjective, but since it's still a human call, you want to default to not mangling the data. People shouldn't be prevented from fixing bad data, but I don't see how the database can possibly determine it *is* bad. It seems similar to the server's clock being off while it's inserting data with NOW; there's just nothing you can do to automatically repair that after you fix the clock. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone database changes
On 10/10/07, Tom Lane [EMAIL PROTECTED] wrote: The arguments that have been made for storing a zone along with the UTC value seem to mostly boil down to it should present the value the same way I entered it, but if you accept that argument then why do we have DateStyle? If it's OK to regurgitate 11-12-2007 as 2007-12-11, I'm not clear on why adjusting timezone isn't OK. Actually, what I meant at least (not sure if others meant it), is storing the value in the timezone it was entered, along with what zone that was. That makes the value stable with respect to the zone it belongs to, instead of being stable with respect to UTC. When DST rules change, the value is in effect reinterpreted as if it were input using the new rules. To me that's also what the name of the type suggests it does. I imagine internally it would convert each value to UTC just before performing any calculations on it, and generally be irritating to work with. But the public interface would do the other right thing. Well, for political time zones anyway. I have no idea what that approach is supposed to do with numeric offsets, or the old PST8PDT type stuff. Anyway, getting back to documentation, I think it's just necessary to somehow point out the difference between these two behaviors in the section about the date and time types, and which type is more appropriate for which situation. I don't know if there's enough room to provide effective examples without getting too bogged down in details though. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone database changes
On 10/8/07, Bruce Momjian [EMAIL PROTECTED] wrote: I had a thought a week ago. If we update the time zone database for future dates, and you have a future date/time stored, doesn't the time change when the time zone database changes. For example if I schedule an appointment in New Zealand for 10:00a and we change the time zone database so that date is now daylight savings, doesn't the time change to display as 9 or 11am? That seems pretty bad. As a general rule, when you're doing planning or calendar type applications where times need to be treated in local time, you never store them in any other form (such as UTC). If you need to work with multiple zones, you also store the timezone and do explicit conversions on demand. In database terms, that means using timestamp without time zone and some other column for the zone. Put another way, when the authoritative reference is local time and not absolute time, you don't use absolute time :) I'm sure this trips up a lot of people, but it's S.O.P. for any environment. OS services have the same caveats, and I've seen desktop apps make this mistake and have to correct it later. (PostgreSQL actually provides better support for time zones than some environments. I've seen some use the current offset for conversions of all times, which utterly breaks in the face of DST; others take DST into account, but using the current year's DST rules only.) It might be worth trying to document for PostgreSQL-using people to find, but I don't see any need for behavior changes. Or anything practical that could be done, for that matter. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Timezone database changes
I wrote: On 10/8/07, Bruce Momjian [EMAIL PROTECTED] wrote: I had a thought a week ago. If we update the time zone database for future dates, and you have a future date/time stored, doesn't the time change when the time zone database changes. For example if I schedule an appointment in New Zealand for 10:00a and we change the time zone database so that date is now daylight savings, doesn't the time change to display as 9 or 11am? That seems pretty bad. As a general rule, when you're doing planning or calendar type applications where times need to be treated in local time, you never store them in any other form (such as UTC). If you need to work with multiple zones, you also store the timezone and do explicit conversions on demand. In database terms, that means using timestamp without time zone and some other column for the zone. Actually, I'm used to knowing how PostgreSQL does it, but looking at things again I remember some confusion I had when first encountering the timestamp types. I don't know what the SQL Standard says; is the implication that timestamp with time zone actually stores the literal time and the zone it is associated with? (Would make more sense, given the name.) If that's true, then the current behavior is a bug^H^H^Hdocumented limitation. I still don't know of anything practical that could be done now, but... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Timezone database changes
On 10/9/07, Peter Eisentraut [EMAIL PROTECTED] wrote: Independent of what any specification might say, however, the currently implemented behavior is clearly wrong in my mind and needs to be fixed. I don't think it's wrong, just a particular choice. As an example, consider an interval scheduling system that handles everything in absolute time (UTC), but uses local time as a convenience. Perhaps it presents a timestamp a few months from now to the user, and accepts any stamp back in the user's timezone. When the DST rules suddenly change a couple weeks before that timestamp occurs, you don't want the database changing its interpretation of what was entered months ago; the absolute time is already the correct time. That's simply a specific version of the general case of wanting the database to operate in absolute time, and present local time as a user convenience. Conveniently, PostgreSQL does exactly that now. If that behavior changes, making the above work anyway is easy: explicitly convert to UTC on input. But that's just a counterpoint to what I mentioned earlier in the thread, explicit conversion of local times. Either way, someone has to do some work to adapt to their specific usage, so which method the database naturally uses is just an arbitrary choice. FWIW, I am in favor of having it [behave as if it does] store the literal time and its associated zone. To me that seems smart, consistent, and more likely to fit what people need. I don't see it as fixing wrong behavior, though. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Latest ecpg patch broke MSVC build
Note that unless there's some tools issue, DllMain doesn't need to be exported to function properly. A DLL's initialization routine is marked as the entry point in the PE header, same as main() in classic C. It might be simpler to just get rid of the export. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On 9/6/07, apoc9009 [EMAIL PROTECTED] wrote: Backup 12/24/2008 Version 2 /pg/backup/12_24_2008/base/rcvry.rcv --- Basebackup /pg/backup/12_24_2008/changes/0001.chg --- Changed Data /changes/0002.chg --- Changed Data /changes/0003.chg --- Changed Data /changes/0010.chg --- Changed Data /changes/0001.rsf --- Recovery Stripeset File (10 MByte) addon of Basebackup delete *.chg if a Stripeset of 10 *.chg Files exist, they should be converted or merged to one greater Recovery Stripe File (*.RSF) Why? What does this actually do? ---(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] Win32 build Large Address Aware?
While reading one of the recent -perform threads, it occurred to me to check, and the 8.2.4 Win32 release binaries aren't marked large address aware. This means the process gets a 2GB VM space, which is normal for 32bit Windows. On x64, my understanding is that each 32 bit process can actually get 4GB if the appropriate flag is set in the binary. (I don't have the hardware to verify this.) The reason documented for this behavior is that 2GB VM space was the hard limit for a very long time, so some applications borrowed the high bit for themselves to use, and couldn't cope with addresses over 2GB. Essentially just a default for backwards compatibility. So with that in mind, is there a reason the Win32 binaries aren't marked that way? Unless there are problems with it, it might be worth doing until 64bit builds are supported. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch filenames unlikes special symbols and numbers
On 9/2/07, Gregory Stark [EMAIL PROTECTED] wrote: Right, traditionally the only characters forbidden in filenames in Unix are / and nul. If we want the files to play nice in Gnome etc then we should restrict them to ascii since we don't know what encoding the gui expects. Actually I think in Windows \ : and . are problems (not allowed more than one dot in dos). Reserved characters in Windows filenames are : / \ | ? * DOS limitations aren't relevant on the OS versions Postgres supports. ...but I thought this was about opening existing files, not creating them, in which case the only relevant limitation is path separators. Any other reserved characters are going to result in no open file, rather than a security hole. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] tsearch filenames unlikes special symbols and numbers
On 9/3/07, Mark Mielke [EMAIL PROTECTED] wrote: Tom Lane wrote: Also, says that Windows throws an error for : in the filename, which means we needn't. Windows doesn't fail - but it can do odd things. For example, try: C:\ echo hi foo:bar If one then checks the directory, one finds a foo. : is used for naming streams and attribute types in NTFS filenames. It's not very well-known functionality and tends to confuse people, but I'm not aware of any situation where it'd be a problem for read access. (Creation is not a security risk in the technical sense, but as most administrators aren't aware of alternate data streams and the shell does not expose them, it's effectively hidden data.) If any of you are familiar with MacOS HFS resource forks, NTFS basically supports an arbitrary number of named forks. A file is collection of one or more data streams, the single unnamed stream being default. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Undetected corruption of table files
On 8/27/07, Jonah H. Harris [EMAIL PROTECTED] wrote: On 8/27/07, Tom Lane [EMAIL PROTECTED] wrote: that and the lack of evidence that they'd actually gain anything I find it somewhat ironic that PostgreSQL strives to be fairly non-corruptable, yet has no way to detect a corrupted page. The only reason for not having CRCs is because it will slow down performance... which is exactly opposite of conventional PostgreSQL wisdom (no performance trade-off for durability). But how does detecting a corrupted data page gain you any durability? All it means is that the platform underneath screwed up, and you've already *lost* durability. What do you do then? It seems like the same idea as an application trying to detect RAM errors. ---(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] text search vs schemas
Tom Lane [EMAIL PROTECTED] wrote: Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Uh, no. Function names for example are subject to search-path confusion. Wait, are they? They are in PL languages but only because most languages store their source code as text just as is happening here. Hmmm ... if you look at the current solution for default expressions for serial columns, ie nextval() on a regclass constant, it's pretty schema-safe. So we could imagine inventing a regconfig datatype that is the same sort of wrapper-over-OID. Then make the 2-parameter form of to_tsvector take that type instead of text. Right, that's what I was getting at. I was confused about the trigger issues, sorry about that. That seems like it'd fix the problem for expression indexes on to_tsvector calls, but I don't see how it fixes the problem for triggers. We don't have any clear path for making trigger arguments be anything but a list of strings. Okay, trying to catch up here... For the simple case of handling a single column, we've got expression indexes as above. For handling two or more columns, expression indexes don't work that well, so that leaves triggers. There happens to be one utility function provided for that purpose, tsvector_update_trigger(). This trigger function needs its configuration as a (string) argument, and is also the only one with this problem. Is that correct? If so, then it seems the question should really be: is this situation of wanting to index multiple columns together, without even using different ranks for them, so common that this trigger function belongs in core? Maybe it shouldn't be there at all; instead have the docs walk through creating a specialized trigger function. It doesn't get rid of the schema-qualified names issue, but when you're writing PL functions you need to deal with that anyway, tsearch or not. And there's still contrib of course. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] text search vs schemas
On 8/17/07, Tom Lane [EMAIL PROTECTED] wrote: At the moment I feel our thoughts have to revolve not around adding complexity to tsearch, but taking stuff out. If we ship it with no schema support for TS objects in 8.3, we can always add that later, if there proves to be real demand for that (and I note that the contrib version has gotten along fine without it). But we cannot go in the other direction. Currently you can schema-qualify objects where you need to, to avoid issues with search_path subversion. If it's impossible to schema-qualify tsearch configs now, when schema support is later added it suddenly exposes everyone to risks that didn't exist before, and requires manual changes to fix. I'm for removing complexity, but per-schema support seems like a design decision that needs to be made up front, whichever way it goes. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch2 in PostgreSQL 8.3?
Digging through the simple vs advanced user discussion, I don't think
expression indexes are really the right idea. It seems a bit fragile,
you need a certain amount of knowledge about the optimizer to figure
out if your queries can even use the index, and it's just plain ugly.
It also seems like the choice is between either simple one-column
stuff, or triggers.
There are already several CREATE FULLTEXT items, so what if you take
it a bit farther:
CREATE TABLE posts (title text, body text);
CREATE FULLTEXT INDEX posts_fti ON posts (title WEIGHT A, body) CONFIG
english USING GIN;
..with searches looking something like..
... WHERE plainto_tsquery('...') @@ posts_fti ...
Okay, maybe that's not quite the right search abstraction (is it an
index or a column?), but you get the idea.
The point is that it would be fairly straightforward to do the common
things, and it works for people whose needs can be met with a full
text index rather than a multidimensional search for lexemes (or
whatever tsvector + index really is). The configuration is clearly
defined and stable, but queries can still use a GUC default.
Meanwhile all the current functions, types and operators are there for
use with triggers etc for advanced setups.
There's obviously a lot of detail missing, but if something like this
is the goal, then there doesn't need to be as much concern about
simple interfaces for 8.3, as long as the framework is ok. In
particular, expression indexes don't necessarily need special work
now.
It's a thought.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] tsearch2 in PostgreSQL 8.3?
On 8/18/07, Bruce Momjian [EMAIL PROTECTED] wrote: Remember an expression index can be a user-created function so you can embed whatever you want in your function and just index it's output, just like you would with a trigger creating a separate column. Well, you could create a function that returns a tsvector, but how do you get that to work with queries? I've been under the impression the expressions need to match (in the normal case, be the same function with the same arguments) in order to use the index. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch2 in PostgreSQL 8.3?
On 8/18/07, Bruce Momjian [EMAIL PROTECTED] wrote: Trevor Talbot wrote: Well, you could create a function that returns a tsvector, but how do you get that to work with queries? I've been under the impression the expressions need to match (in the normal case, be the same function with the same arguments) in order to use the index. Yes, so you create a function called complex_ts and create the index: CREATE INDEX ii on x USING GIT(complex_ts(col1, col2)) and in your WHERE clause you do: WHERE 'a b' @@ complex_ts(col1, col2) Oh, duh, of course. I kept thinking of the index as something abstract instead of reusing the expression, even when the examples were right in front of me... On 8/18/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Bruce Momjian wrote: CREATE INDEX ii on x USING GIT(complex_ts(col1, col2)) GIN? Freudian slip, that's what he thinks of me :D ---(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] text search vs schemas
On 8/18/07, Tom Lane [EMAIL PROTECTED] wrote: As my copy of the patch currently stands, there are two built-in trigger functions, tsvector_update_trigger and tsvector_update_trigger_column. The first expects trigger arguments name of tsvector col, name of tsconfig to use, name(s) of text col(s) and the second name of tsvector col, name of tsconfig col, name(s) of text col(s) that is, the tsconfig name is stored in a text column. We could fix the second form by changing it to expect the tsconfig column to be of type regconfig. The first form is a bit more problematic. I can see two approaches: either specify both the schema and the tsconfig name, as two separate arguments, or keep it one argument but insist that the content of the argument be an explicitly-qualified name. The second way seems a bit klugier when considered in isolation, but I think I like it better, because there would be a natural migration path to treating the argument as being of type regconfig when and if we get around to having real types for trigger arguments. (Which I think is a good idea, btw, just not for 8.3.) I like the second approach too. It may be slightly awkward for now, but IMHO it does the right thing. ---(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] text search vs schemas
On 8/16/07, Tom Lane [EMAIL PROTECTED] wrote:
Actually ... I'm suddenly not happy about the choice to put text search
configurations etc. into schemas at all. We've been sitting here and
assuming that to_tsvector('english', my_text_col) has a well defined
meaning --- but as the patch stands, *it does not*. The interpretation
of the config name could easily change depending on search_path.
It does not seem likely that a typical installation will have so many
text search configs that subdividing them into schemas will really be
useful. If I recall correctly, Teodor did that on my recommendation
that it'd be the cleanest way to distinguish built-in from non-built-in
objects for dump purposes. That is, pg_dump would ignore TS objects
that are in pg_catalog and dump everything else. But I'm having severe
second thoughts about that.
What seems the most attractive alternative at the moment is to have a
flat namespace for TS objects (no schemas) and introduce something like
a bool is_built_in column for pg_dump to consult in deciding whether
to dump 'em.
That assumes a database-oriented search config, instead of a case of
multiple users confined to invidual schemas doing their own thing. Is
the latter possible now, and do you want to remove that ability?
Something else that occurs to me though: the problem seems to be that
parts of tsearch take object names as strings. I thought one
advantage of having it in core is that they are now real database
objects, with owners etc. How many other database objects are passed
around as string labels?
Wouldn't treating them as actual objects remove this whole issue?
What happens now if you try to drop a configuration that's still used
in a trigger somewhere?
(I'm new to both tsearch2 and this list, so please excuse any
mistakes. Mostly keeping an eye on this for future use in my own
projects.)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
