Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
On Thu, Mar 10, 2005 at 12:44:50PM -0500, Tom Lane wrote: Would those of you with access to other DBMSes try this: DB2/LINUX 8.1.6 create table tab (col integer); select 1 from tab having 1=0; 1 --- 0 record(s) selected. select 1 from tab having 1=1; 1 --- 1 1 record(s) selected. insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; 1 --- 0 record(s) selected. select 1 from tab having 1=1; 1 --- 1 1 record(s) selected. -joh ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PostgreSQL vs. MySQL
On Fri, Jul 04, 2003 at 10:49:01AM -0400, Rod Taylor wrote: In my opinion the defaults should be set up for a typical database server machine. Ok.. thats fair. The first problem would be to define typical for current PostgreSQL installations, and typical for non-postgresql installations (the folks we want to convert). It's been a while since the last one of these discussions, so stop me if this has been suggested before, but... Do we actually want to have a default configuration file? Seriously, if we provide, say, 4 or 5 files based on various system assumptions (conf.MINIMAL, conf.AVERAGE, conf.MULTIDISK, or whatever), then we might be able to get away with not providing an actual default. Change the installation instructions to say PostgreSQL requires a configuration file, which it expects to be located in $DIR. Provided are several example configurations (in $DIR/eg/). If you're just starting with PostrgreSQL, we recommend reading through those and selecting one which most closely matches your machine. If you're in doubt as to which file to use, try $AVERAGE. If you're still having difficulty getting PostgreSQL to run, try $MINIMAL. $MINIMAL should work on every supported platform, but is not optimized for modern hardware -- PostgreSQL will not run well in this configuration. This makes the installation process slightly less simple, but only in the way that we want it to be. That is, it forces the end user to the realization that there actually is configuration to be done, and forces them into a minimally interactive way to deal with it. It also doesn't require any kernel-test coding, or really any development at all, so we should theoretically be able to get it finished and ready to go more quickly. Thoughts? -johnnn ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Physical Database Configuration
On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote: Has anyone looked at the syntaxes used by other databases to control tablespaces (Oracle, DB2, etc)? I have no strong desire to slavishly follow Oracle, but it would be a shame to miss out on any good ideas. DB2: CREATE TABLESPACE spacename ... ALTER TABLESPACE spacename ... RENAME TABLESPACE spacename TO newspacename CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN spacename] INDEX IN and LONG IN refer to the tablespace used to store the indices and the LOB values for that table, respectively. The create syntax revolves around nodegroups and such which are DB2 concepts i don't fully grok (i'm a programmer, not a DBA). But, yeah, those are really the only entrypoints. You can't create an index in a specific tablespace -- it will go wherever the table is set to put indices. I like the syntax (IN spacename), though. It's simple and straightforward. -johnn ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Physical Database Configuration
On Wed, Jun 25, 2003 at 10:30:31AM -0500, Andrew Dunstan wrote: DB2 looks good. I have horrid, horrid memories of wrestling with the Oracle extent madness. I do think that it's worth providing additional access points to tablespaces, though. That is, it would make sense to me to allow CREATE INDEX indexname IN spacename, instead of attaching an indexspace to a table. This is especially true with postgresql, since i've seen more than one proposal for multi-table indices. If we're spacing indices based on the table, it's unclear where a given multi-table index should go. It would also allow for other flexibilities, like putting join indices (on foreign keys) in one tablespace, with indices for aggregation or sorting in another tablespace. So, my vote, as a non-code-contributing member, would be for a DB2-style syntax, without the INDEX IN and LONG IN extensions, but with the ability to put indices explicitly into a tablespace. -johnn ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Upgrading the backend's error-message infrastructure
On Thu, Mar 13, 2003 at 03:51:00PM -0500, Tom Lane wrote: Wire-protocol changes - Error and Notice (maybe also Notify?) msgs will have this structure: E x string \0 x string \0 x string \0 \0 where the x's are single-character field identifiers. A frontend should simply ignore any unrecognized fields. Initially defined fields for Error and Notice are: ... S,C,M fields will always appear (at least in Error messages; perhaps Notices might omit C?). The rest are optional. It strikes me that this error response could be made slimmer by removing the text fields. It makes sense for P, F, L, and R to be returned when available, as they're specific to the instance of the error. C is clearly necessary, as well. S is questionable, though, depending on whether (for every C there is one, and only one S). But the others are going to be the same for every instance of a given C. It would seem to make more sense to me to provide a different function(s) which allows the lookup Messages, Details, and Hints based on the SQLSTATE. The benefits that i see would be: - Less clutter and wasted space on the wire. If we are concerned enough about space to reduce the SQLSTATE to an integer mapping, removing all the extra text should be a big win. Couple this with the libraries' ability to now do things like cache messages, or not bother to retrieve messages for certain SQLSTATEs, and the benefit gets larger. - Removal of localization from error/notice generation libraries. This should make that section of code simpler and more fault-tolerant. It also allows libraries to do potentially weird stuff like using multiple different locales per connection, so long as they can specify a locale for the lookup functions. Does that make sense, or am i missing something? -johnn ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Upgrading the backend's error-message infrastructure
On Fri, Mar 14, 2003 at 12:23:04PM -0500, Tom Lane wrote: It would seem to make more sense to me to provide a different function(s) which allows the lookup Messages, Details, and Hints based on the SQLSTATE. This would constrain us to have a different SQLSTATE for every error message, which we aren't going to do. That makes sense -- i was assuming a one-to-one mapping (or, at least, many-to-one in the other direction: many SQLSTATEs for the same Unknown error message). I'm not sure i follow the reasoning behind allowing multiple messages for a single SQLSTATE, though. I would think that having the machine-readable portion of the error be the most granular would make sense. I can't imagine the SQLSTATE space being too small for us at this point. If it's different enough to warrant a different message, then, in my mind, it's different enough to warrant a different SQLSTATE. It's also unclear how you insert parameters into error strings if you do this. That's valid, but there are other ways of dealing with it. The position in the SQL statement has been moved out to another item in the response, so why not move the table, column, index, or whatnot into another item(s) as well? - Removal of localization from error/notice generation libraries. This should make that section of code simpler and more fault-tolerant. And you put it where, instead? Sorry, i think i phrased that poorly. What i meant was that the functions which provide lookups would need to be aware of locale because they're referencing localized strings. The functions which are specifically generating and transmitting the errors, on the other hand, would be free of localized strings, so would not have to rely on any of the locale infrastructure at all. I'm not suggesting any change in the scheme for localization or anything like that, just saying that limiting the internal access points might make things cleaner. The usual other benefits should result as well: simpler unit tests, easier maintenance, etc. -joh ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL99 ARRAY support proposal
On Thu, Mar 13, 2003 at 09:53:15AM -0800, Joe Conway wrote: implode(text[], text) returns text - join array elements into a string using given string delimiter I'm open to opinions on implode() -- I only picked implode() because that's what it is called in PHP. Any suggestions? In both Perl and Python, that type of function is called join. -john ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [HACKERS] I feel the need for speed. What am I doing wrong?
On Tue, Jan 07, 2003 at 03:10:06PM -0800, Dann Corbit wrote: NOTICE: QUERY PLAN: SetOp Except (cost=202028537.97..202120623.90 rows=1227812 width=24) - Sort (cost=202028537.97..202028537.97 rows=12278124 width=24) - Append (cost=1.00..200225099.24 rows=12278124 width=24) - Subquery Scan *SELECT* 1 (cost=1.00..100112549.62 rows=6139062 width=24) - Seq Scan on CNX_DS_53_SIS_STU_OPT_FEE_TB a (cost=1.00..100112549.62 rows=6139062 width=24) - Subquery Scan *SELECT* 2 (cost=1.00..100112549.62 rows=6139062 width=24) - Seq Scan on CNX_DS2_53_SIS_STU_OPT_FEE_TB b (cost=1.00..100112549.62 rows=6139062 width=24) EXPLAIN Those big round numbers suggest that you haven't run vacuum analyze on all of your tables. Since PostgreSQL uses a cost-based optimizer, you do actually have to give it some idea of what things will cost before it can give you an appropriate plan. Reference for your version: http://www14.us.postgresql.org/users-lounge/docs/7.1/reference/sql-vacuum.html -john ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
