[HACKERS] Postgres config file: autocommit = off
I'm not sure that this is the right list to ask - but after having googled a while it seems that the only ones that might be able to answer this question is the developers. Therefor, here we go: As far as I have understood, postgres is autocommiting each typed statement UNLESS the user remembers to write "BEGIN" which then disables the autocommit behavior for this single transaction. -Unfortunately it's easy to forget the BEGIN and it might be quite troublesome if one has to do it a lot. Therefor: Are there any plans to give the administrator an OPTION to turn the behavior off through a parameter "autocommit = " in the config file? Eventually the default behavior could be the autoccomit = on, as it is now. Eventually an option per database could override the config file setting, so that the default value was to disable the autocommit behavior but a subset of the databases had autocommit enabled. _ Få MSN Hotmail på mobilen http://www.msn.dk/mobile ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] default locale considered harmful? (was Re: [GENERAL]
Tom Lane writes: > Peter has provided a hack whereby one can create a LIKE-supporting index > in a non-C locale. But a *default* index in a non-C locale is still not > going to support LIKE ... and the hacked index will not support ordinary > comparison or ordering operators. So I think there's still a lot left > to be desired here. I don't understand why you call this a hack. Pattern matching and string comparison simply work differently, so the proper solution is to use different operator classes. After all, that's what operator classes exist for. What is left to be desired? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] REMOVE
Re: [HACKERS] Testing the return value of fclose() in the backend
On Fri, 30 May 2003, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Gavin Sherry wrote: > >> There are various places in the backend, such as FreeFile(), where the > >> return value of fclose() is not tested. > > > We are not checking fclose, probably because fclose failures are quite > > rare. Should we be concerned? > > Probably. Closing a valid file descriptor in itself can't provoke any > error that I can imagine, but fclose() also implies fflush() --- so if > you have written data that hasn't yet been forced out of the stdio > buffers then out-of-disk-space is certainly a foreseeable failure. Yes. I think I brought that up in my original email. Heap access/WAL routines 'should not' suffer an fclose() problem because of fsync() calls. But this isn't necessarily the case for COPY. > > fclose failure on an open-for-read-only file seems like Assert() > material; it "can't happen". Right. If this generates an error, there are probably more serious issues. Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] A few notes
Hello. I thought I'd just toss out a few thoughts: 1) Should a link to the release changes for 7.3.3 be on the website? I had to look into the web-interface of CVS to see what was actually changed. 2) It would be nice if some regular performance tests could be done upon every release on some stock machine whose configuration never changes to give some numerical hints as to the value of an upgrade. 3) I got bit by using the explicit join syntax just like Thomas Lockhart had predicted. I then removed the syntax to let the planner do its job. Queries which took around 10 seconds took 5 minutes. I then disabled GEQO and the queries ran in around a second. I noticed that the explicit join syntax will no longer confine planning choices in 7.4, but is it possible the GEQO threshold, as a default, is too low? Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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] default locale considered harmful? (was Re: [GENERAL]
Bruce Momjian <[EMAIL PROTECTED]> writes: > Has the single-byte LIKE penalty been eliminated, so we don't need to > consider using C as the default locale for initdb, right? I'm still of the opinion that we should make C the default locale. But I'm not sure where the consensus is, so I've not made the change. > If fixed, how was it done? Peter has provided a hack whereby one can create a LIKE-supporting index in a non-C locale. But a *default* index in a non-C locale is still not going to support LIKE ... and the hacked index will not support ordinary comparison or ordering operators. So I think there's still a lot left to be desired here. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] default locale considered harmful? (was Re: [GENERAL]
Has the single-byte LIKE penalty been eliminated, so we don't need to consider using C as the default locale for initdb, right? If fixed, how was it done? --- Peter Eisentraut wrote: > Tom Lane writes: > > > I recall someone floating a proposal that initdb should by default > > initialize the database in C locale, not whatever-it-finds-in-the- > > environment. To get a non-C locale you'd have to give an explicit > > command-line switch --- essentially, reversing the sense of the present > > "initdb --no-locale" option. > > If you're concerned about speed, let's think about fixing the real > problems, not about disabling the feature altogether. A while ago I > proposed an easy solution that made LIKE use an index based on strxfrm > order instead. It was rejected on the grounds that it would prevent a > future enhancement of the LIKE mechanism to use the locale-enabled > collation order, but no one seems to be seriously interested in > implementing that. I still have the patch; we can reconsider it if you > like. > > (Btw., LIKE using the locale-enabled collation sequence is hardly going to > work, because most locales compare strings backwards from the end to the > start in the second pass, so something like LIKE 'foo%' can easily give > inconsistent results, since you don't know what the end of the string > really is. It's better to think of pattern matching as > character-by-character matching.) > > -- > Peter Eisentraut [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Testing the return value of fclose() in the backend
Bruce Momjian <[EMAIL PROTECTED]> writes: > Gavin Sherry wrote: >> There are various places in the backend, such as FreeFile(), where the >> return value of fclose() is not tested. > We are not checking fclose, probably because fclose failures are quite > rare. Should we be concerned? Probably. Closing a valid file descriptor in itself can't provoke any error that I can imagine, but fclose() also implies fflush() --- so if you have written data that hasn't yet been forced out of the stdio buffers then out-of-disk-space is certainly a foreseeable failure. fclose failure on an open-for-read-only file seems like Assert() material; it "can't happen". regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Compressing Fields?
You are going to love the answer to this question --- it already does
compression of any long fields when it is stored in the TOAST table.
In fact, you have to turn off compression if you don't want it using
ALTER TABLE ... SET STORAGE.
---
Christopher Browne wrote:
> I was wondering if anyone has had occasion to hook up a compression scheme
> (Huffman/gzip/zlib/whatever) as a PostgreSQL function.
>
> I've got a case where there is a need to store fairly large chunks of XML in a
> database. There seems little reason to parse it beforehand, as many seem wont
> to do. In fact, there will often be no need to look at it at all. Typically,
> it will be ignored, only to be looked at if a human specifically asks for it.
>
> Ergo, it would be a slick idea to cut a 1700 byte field down to 253 by
> [somehow compressing it] before sticking it into a "bytea" field. That will
> save on disk space, pack more records into pages, and generally lead to
> queries being a bit cheaper.
>
> insert into log_table (id, txn_date, metadata, xml) values
> (4271324, '2003-07-01', 'Useless Data',
> compress(' This is worthless data
> FooAnd 8K of futile murmurings ');
>
> 90% of the time, we need only:
>
> select id, txn_date, metadata from log_table;
>
> And the other 10%, we do
> select id, txn_date, metadata, uncompress(xml) from logtable where id in
> (871009, 873281, 8321947);
>
> It would surely be possible for the client software to do the compression, but
> it seems an interesting thought to do it on the server, thereby making it
> "language-neutral" such that I could write client software in Perl, Python, or
> even use Pierre Mai's binding to CMU/CL without having to worry about whether
> or not there's a binding of the compression algorithm to whatever client
> language I might imagine using.
>
> I don't see anything in contrib for this. pgcrypto obviously does something
> similar for cryptographic functions, but I don't see compression on the list.
>
> Presumably something could be constructed using zlib; if anyone has done this
> already, it would be nice to know of...
> --
> output = reverse("moc.enworbbc" "@" "enworbbc")
> http://www.ntlug.org/~cbbrowne/advocacy.html
> "Bureaucracies interpret communication as damage and route around it"
> -- Jamie Zawinski
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Testing the return value of fclose() in the backend
On Fri, 30 May 2003, Bruce Momjian wrote: > Gavin Sherry wrote: > > Hi all, > > > > There are various places in the backend, such as FreeFile(), where the > > return value of fclose() is not tested. Whilst we would often notice any > > problems with writing to data files due to testing on fsync(), it could > > affect things like COPY ... TO, CreateOptsFile() and more. > > > > Are we catching these somewhere else I'm not seeing? > > We are not checking fclose, probably because fclose failures are quite > rare. Should we be concerned? fsync() errors are probably just as rare. The problem with long running daemons not testing for fclose() failure is the problem caused by file descriptor leakage. I recall that squid (another long running daemon) had this problem due to the large number of file systems interactions they undertake. The question is, of course, what to do in postgres if fclose() returns an error? elog(WARNING)? Another fclose() call? Not sure what squid did. Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Testing the return value of fclose() in the backend
Gavin Sherry wrote: > Hi all, > > There are various places in the backend, such as FreeFile(), where the > return value of fclose() is not tested. Whilst we would often notice any > problems with writing to data files due to testing on fsync(), it could > affect things like COPY ... TO, CreateOptsFile() and more. > > Are we catching these somewhere else I'm not seeing? We are not checking fclose, probably because fclose failures are quite rare. Should we be concerned? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Compressing Fields?
I was wondering if anyone has had occasion to hook up a compression scheme
(Huffman/gzip/zlib/whatever) as a PostgreSQL function.
I've got a case where there is a need to store fairly large chunks of XML in a
database. There seems little reason to parse it beforehand, as many seem wont
to do. In fact, there will often be no need to look at it at all. Typically,
it will be ignored, only to be looked at if a human specifically asks for it.
Ergo, it would be a slick idea to cut a 1700 byte field down to 253 by
[somehow compressing it] before sticking it into a "bytea" field. That will
save on disk space, pack more records into pages, and generally lead to
queries being a bit cheaper.
insert into log_table (id, txn_date, metadata, xml) values
(4271324, '2003-07-01', 'Useless Data',
compress(' This is worthless data
FooAnd 8K of futile murmurings ');
90% of the time, we need only:
select id, txn_date, metadata from log_table;
And the other 10%, we do
select id, txn_date, metadata, uncompress(xml) from logtable where id in
(871009, 873281, 8321947);
It would surely be possible for the client software to do the compression, but
it seems an interesting thought to do it on the server, thereby making it
"language-neutral" such that I could write client software in Perl, Python, or
even use Pierre Mai's binding to CMU/CL without having to worry about whether
or not there's a binding of the compression algorithm to whatever client
language I might imagine using.
I don't see anything in contrib for this. pgcrypto obviously does something
similar for cryptographic functions, but I don't see compression on the list.
Presumably something could be constructed using zlib; if anyone has done this
already, it would be nice to know of...
--
output = reverse("moc.enworbbc" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/advocacy.html
"Bureaucracies interpret communication as damage and route around it"
-- Jamie Zawinski
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Practical sets of SQLSTATE values?
>> Anyone have lists of implementation-defined SQLSTATEs for >> the big commercial DBs? This points to the Oracle docs. http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a87540/ch2.htm Table 2-2 SQLSTATE Status Codes ---(end of broadcast)--- TIP 3: 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] index suggestion for 7.4
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> I went back and reread the stuff on NEGATOR and found it only applies
> to operators that return boolean types. I had thought it was different
> and would let you make the deduction a > b <=> -a <= -b, but that isn't
> the case. Instead it lets you make the deduction that a > b <=> NOT (a <= b).
Right, the reason NEGATOR exists is to let prepqual.c flatten out NOTs
where possible (this is the same part of the code that applies
DeMorgan's Laws and other boolean algebra to try to bring a qual
condition into the simplest possible form).
To do something useful with "-" and descending order, we'd need some way
of explicitly associating "-" operators with btree opclasses. I'm not
convinced that it's worth the trouble, especially when it'd really only
apply to the numeric datatypes ("-" on text is a pretty unappealing
concept...). Stephan's suggestion of providing standard reverse-order
opclasses seems more attractive to me. Even if people didn't want to
put them into the mainstream, they could be consed up as a contrib
module with not a lot of effort.
regards, tom lane
---(end of broadcast)---
TIP 3: 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] RBLs ... I'm tired of spam ...
I know there are alot of people that really appreciate PG and all the work that so many people have contributed. I am also sure that there are alot of people who would like to contribute to PG but don't feel they have any means except in the evangelism arena. This is something you could probably dish out to a few trusted individuals. Not to accept/post to the list but maybe to just check in and go through and delete all the spams. If that is possible to set up you can count me in as a volunteer to check in and do some spam clearing a couple times a day. Best Regards, Carl Garland _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Practical sets of SQLSTATE values?
Peter Eisentraut <[EMAIL PROTECTED]> writes: > In my mind, distinct error codes are only useful if the application can > react differently to the condition. Agreed, we do not want to divide the error codes too finely. However, we had a request on the lists just today for an error-code-based way to detect whether the server failed because of running out of disk space, and that wasn't by any means the first such request. So "out of disk space" definitely deserves its own SQLSTATE, IMHO. A nice property of the SQLSTATE design is that even if an application doesn't recognize the exact code, it probably can recognize the category (the first two characters), and the category is usually enough to give it an idea of whether it can do anything useful or not. So for example, as long as "no such function" is under the 42xxx (syntax error or access rule violation) category, it shouldn't be a big problem for applications to understand it well enough for their purposes. This is specifically intended by the spec writers, I think, in view of this note in SQL99: NOTE 356 - One consequence of this is that an SQL-implementation may, but is not required by ISO/IEC 9075 to, provide subcodes for exception condition syntax error or access rule violation that distinguish between the syntax error and access rule violation cases. We should probably expend more care on making sure we have the categories right than on worrying about which errors deserve their own subcodes. I also wonder whether we shouldn't explicitly document someplace "if you don't recognize an XXYYY SQLSTATE, you may treat it as XX000 instead". regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] is it possible to enlarge the TopMemoryContext?
=?iso-8859-1?q?Alice=20Lottini?= <[EMAIL PROTECTED]> writes: > As far as we know, this could be due to the limited > size of the TopMemoryContext in which the dynamically > loadable modules work. TopMemoryContext is just as expansible as any other context. I'm not sure what your problem is, but I am quite sure that you've mis-analyzed it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Practical sets of SQLSTATE values?
Tom Lane writes: > I've been starting to look at assigning SQLSTATE values to all the > backend elog() calls, and have realized that the set of values defined > by the spec is very, how you say, uneven. They have conditions as > specific as "data exception/invalid time zone displacement value" > (22009) and yet nothing for cases as obvious as "no such function" > or "out of disk space". We're going to need a lot of implementation- > defined SQLSTATE codes if we want the facility to be as useful as it > should be. In my mind, distinct error codes are only useful if the application can react differently to the condition. Hence, "no such function" can be equated to "no such " or a general "syntax error", because the action of the application in all those cases is likely the same (perhaps show error text to user and make him fix the command). Similarly, "out of disk space" can be put into a general "internal server error" class because in all those cases the action is the same (show error text to administrator and make him fix the problem). How this extends to "invalid time zone displacement value" is a little beyond my reach right now, but in general we should be able to get away with relatively few distinct error codes. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] index suggestion for 7.4
On Fri, May 30, 2003 at 11:31:23 -0700, Stephan Szabo <[EMAIL PROTECTED]> wrote: > On Fri, 30 May 2003, Bruno Wolff III wrote: > > > I was hoping the new stuff Tom added would make doing this easier. The issue > > has come up before and at least at that time it didn't get changed so I > > expected it wasn't easy to do. > > > > I thought maybe there was information for the - operator > > that would allow you to know that you could use an index on -col > > to go in the reverse direction safely. > > Not really. I think that if you were to do that, you'd probably need to > provide an additional thing to the opclass to let it know. Otherwise it'd > be unsafe for user defined types/user defined - operators and doesn't help > on things where - isn't the correct way to do it. I went back and reread the stuff on NEGATOR and found it only applies to operators that return boolean types. I had thought it was different and would let you make the deduction a > b <=> -a <= -b, but that isn't the case. Instead it lets you make the deduction that a > b <=> NOT (a <= b). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] index suggestion for 7.4
On Fri, 30 May 2003, Bruno Wolff III wrote: > On Fri, May 30, 2003 at 10:42:24 -0700, > Stephan Szabo <[EMAIL PROTECTED]> wrote: > > On Fri, 30 May 2003, Bruno Wolff III wrote: > > > > > Now that expressions can be used in indexes in 7.4 you can have multicolumn > > > indexes that are ordered in different directions. However the planner > > > doesn't seem to understand that order by -col asc is the same as order by > > > col desc (for at least the normal -) so you have to be careful how you > > > write queries when doing this. > > > > I think it'd be better to make it easier to make indexes where some > > columns are reversed. I'm not sure that making a reverse opclass for > > btree (one that goes >, >=, =, <=, < I guess) is a complete solution > > even for btree but if it is, we could provide them. I think this would > > also have the advantage of not requiring wacky queries to use the index > > for multicolumn lookups as well. > > I was hoping the new stuff Tom added would make doing this easier. The issue > has come up before and at least at that time it didn't get changed so I > expected it wasn't easy to do. > > I thought maybe there was information for the - operator > that would allow you to know that you could use an index on -col > to go in the reverse direction safely. Not really. I think that if you were to do that, you'd probably need to provide an additional thing to the opclass to let it know. Otherwise it'd be unsafe for user defined types/user defined - operators and doesn't help on things where - isn't the correct way to do it. > The new stuff still is easier to use then creating a new opclass which was > the old solution. It might make sense to provide descending opclasses as part of the base install, _desc_ops or something for the types that have btree opclasses. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] XML and postgres
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Is there interest to storing and indexed access methods for xml in postgresql? There is definitely interest. See all the chatter created by my psql-xml patch on patches/hackers for example. If I get some free time this summer I am going to look into this; hopefully someone will have started something before then. If so, count me in as willing to help as well. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200305301423 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+16GyvJuQZxSWSsgRApzHAKCF49JekB9f6b4AVvmDBoAdQcskgwCeL0Ak atdj5PIv0us85zivZ4omXzQ= =RqHZ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: 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] is it possible to enlarge the TopMemoryContext?
Hello everybody, we've developed a function which reads a huge amount of data from postgres and, being recursive, does several memory-intensive elaborations and writes the results back on two postgres tables. No memory context switch has been done in our function. Now we have to compare this function with another one which performs the same elaborations but reads the data from a binary file and stores the results on another file. Both of them work exactly in the same way (as we've simply ported our postgres module to work in memory) but we've noticed a rather different memory usage in the two cases. The in-memory function seems to have a lot more of memory to work on, while the postgres one stops for memory exhausted as soon as the data size increases over a certain limit. As far as we know, this could be due to the limited size of the TopMemoryContext in which the dynamically loadable modules work. Is there a way to expand the size of memory available to our function? Thanks a lot! alice and lorena __ Yahoo! Mail: 6MB di spazio gratuito, 30MB per i tuoi allegati, l'antivirus, il filtro Anti-spam http://it.yahoo.com/mail_it/foot/?http://it.mail.yahoo.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] index suggestion for 7.4
On Fri, May 30, 2003 at 10:42:24 -0700, Stephan Szabo <[EMAIL PROTECTED]> wrote: > On Fri, 30 May 2003, Bruno Wolff III wrote: > > > Now that expressions can be used in indexes in 7.4 you can have multicolumn > > indexes that are ordered in different directions. However the planner > > doesn't seem to understand that order by -col asc is the same as order by > > col desc (for at least the normal -) so you have to be careful how you > > write queries when doing this. > > I think it'd be better to make it easier to make indexes where some > columns are reversed. I'm not sure that making a reverse opclass for > btree (one that goes >, >=, =, <=, < I guess) is a complete solution > even for btree but if it is, we could provide them. I think this would > also have the advantage of not requiring wacky queries to use the index > for multicolumn lookups as well. I was hoping the new stuff Tom added would make doing this easier. The issue has come up before and at least at that time it didn't get changed so I expected it wasn't easy to do. I thought maybe there was information for the - operator that would allow you to know that you could use an index on -col to go in the reverse direction safely. The new stuff still is easier to use then creating a new opclass which was the old solution. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] "Vastly under-hyped..."
http://www.infoworld.com/article/03/05/23/21OPconnection_1.html?platforms My favorite line from the article is, "In last week's column, I provided an obligatory tease for PostgresSQL in my discussion of MySQL. A deeper examination of PostgresSQL suggests that it could be vastly under-hyped. Historically, PostgresSQL has been consistently ^^ ^^^ ahead of MySQL in enterprise database features with support of transactions and stored procedures." heh, understatement of the year++ for an understated project. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] index suggestion for 7.4
On Fri, 30 May 2003, Bruno Wolff III wrote: > Now that expressions can be used in indexes in 7.4 you can have multicolumn > indexes that are ordered in different directions. However the planner > doesn't seem to understand that order by -col asc is the same as order by > col desc (for at least the normal -) so you have to be careful how you > write queries when doing this. I think it'd be better to make it easier to make indexes where some columns are reversed. I'm not sure that making a reverse opclass for btree (one that goes >, >=, =, <=, < I guess) is a complete solution even for btree but if it is, we could provide them. I think this would also have the advantage of not requiring wacky queries to use the index for multicolumn lookups as well. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] index suggestion for 7.4
Bruno Wolff III <[EMAIL PROTECTED]> writes: > Now that expressions can be used in indexes in 7.4 you can have multicolumn > indexes that are ordered in different directions. However the planner > doesn't seem to understand that order by -col asc is the same as order by > col desc (for at least the normal -) I don't think it should; that's an extremely datatype-dependent bit of analysis, and the planner does not have any means of ascertaining whether the equivalency holds for a particular "-" operator and index opclass. The correct way to set up this sort of thing would be to build a "backwards ordering" operator class, not to use an index on "-col". regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Practical sets of SQLSTATE values?
On Fri, 30 May 2003, Tom Lane wrote: > What do other DBMSes do about this? Seems like it would make sense to > borrow as many SQLSTATE codes as we can from Oracle or DB2 or some other > big player ... especially if there's any commonality in their > extensions. Anyone have lists of implementation-defined SQLSTATEs for > the big commercial DBs? > On informix SQLSTATE is mostly for getting the oh.. lets call it the "genre" of the error. They use a separate error code which contains the specific error. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] XML and postgres
Hello, Is there interest to storing and indexed access methods for xml in postgresql ? While I don't use xml in my applications but I see possible directions to develop contrib module with indexed access methods to xml-like data type. We have already contrib/ltree for tree-like structures and recently we developed (not released yet) hstore module, which implements hash data type like in perl with indexed AM to keys, values. Motivation for this modules is need to store data with weak structure (semi-structured data), i.e. we have several obligatory fields and a bunch of optional data. Obligatory fields could be stored as usual, while for optional columns we use special data type - hstore, which serves as a storage of (key,value) pairs. There are could be many (key,value) pairs and hstore provides AM to them. We've realized that combination of ltree, hstore could be used for xml. We have no spare time to elaborate this, so if someone could work on this, we could provide hstore module and help with developing. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] index suggestion for 7.4
Now that expressions can be used in indexes in 7.4 you can have multicolumn indexes that are ordered in different directions. However the planner doesn't seem to understand that order by -col asc is the same as order by col desc (for at least the normal -) so you have to be careful how you write queries when doing this. For example: bruno=> \d test Table "public.test" Column | Type | Modifiers +-+--- col1 | integer | col2 | integer | Indexes: "test1" btree (col1, ((- col2))) bruno=> explain select col1, col2 from test order by col1 asc, col2 desc; QUERY PLAN Sort (cost=814.39..839.39 rows=1 width=8) Sort Key: col1, col2 -> Seq Scan on test (cost=0.00..150.00 rows=1 width=8) (3 rows) bruno=> explain select col1, col2 from test order by col1 asc, -col2 asc; QUERY PLAN Index Scan using test1 on test (cost=0.00..337.50 rows=1 width=8) (1 row) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Practical sets of SQLSTATE values?
Joe Conway <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Anyone have lists of implementation-defined SQLSTATEs for >> the big commercial DBs? > Does this help? > http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a97269/pc_09err.htm#3174 Some, but the mapping table is mostly pretty vague --- for instance, it's quite unclear whether they provide subclasses in the 42xxx series errors, or if they all come out as 42000. If there are subclasses, which subclass codes correspond to which ORA-foo codes? A more complete mapping table would help. The same site has a copy of the complete Oracle 9i error message book: http://www.csis.gvsu.edu/GeneralInfo/Oracle/server.920/a96525/toc.htm but I couldn't find any mention at all of SQLSTATE codes in it. It's pretty clear that Oracle regards SQLSTATE as an ugly stepchild. (Which might be a fair assessment ;-), but it's at least somewhat standard ...) We might do better following DB2's lead. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Practical sets of SQLSTATE values?
Tom Lane wrote: What do other DBMSes do about this? Seems like it would make sense to borrow as many SQLSTATE codes as we can from Oracle or DB2 or some other big player ... especially if there's any commonality in their extensions. Anyone have lists of implementation-defined SQLSTATEs for the big commercial DBs? Does this help? http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a97269/pc_09err.htm#3174 Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Practical sets of SQLSTATE values?
> extensions. Anyone have lists of implementation-defined SQLSTATEs for > the big commercial DBs? http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2m0db2m002.htm#ToC Chapter 12 has SQLState information. It's very short in most cases (aside from 'Warning'). DB2 seems to use an SQLCode (Chapter 11) which catalogues all of the error messages, the error code, and SQLState applied to it. The entire book has to do with DB2 messages and their meaning. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[HACKERS] Practical sets of SQLSTATE values?
I've been starting to look at assigning SQLSTATE values to all the backend elog() calls, and have realized that the set of values defined by the spec is very, how you say, uneven. They have conditions as specific as "data exception/invalid time zone displacement value" (22009) and yet nothing for cases as obvious as "no such function" or "out of disk space". We're going to need a lot of implementation- defined SQLSTATE codes if we want the facility to be as useful as it should be. What do other DBMSes do about this? Seems like it would make sense to borrow as many SQLSTATE codes as we can from Oracle or DB2 or some other big player ... especially if there's any commonality in their extensions. Anyone have lists of implementation-defined SQLSTATEs for the big commercial DBs? regards, tom lane ---(end of broadcast)--- TIP 3: 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
