Re: [HACKERS] Re: AW: Plans for solving the VACUUM problem
As a rule of thumb, online applications that hold open transactions during user interaction are considered to be Broken By Design (tm). So I'd slap the programmer/design team with - let's use the server box since it doesn't contain anything useful. Many web applications use persistent database connections for performance reasons. Persistent connection is not the same as an OPEN transaction BTW. I suppose it's unlikely for webapps to update a row and then sit and wait a long time for a hit, so it shouldn't affect most of them. However if long running transactions are to be aborted automatically, it could possibly cause problems with some apps out there. Worse if long running transactions are _disconnected_ (not just aborted). -- Sincerely Yours, Denis Perchine -- E-Mail: [EMAIL PROTECTED] HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Feedback
Just thought that I'd tell you. I've been waiting (very patiently, I think) for a long time for outre joins, views w/ joins and not the least, functions that can handle NULL's in an orderly way. A little anxious I started implementing these elements in my projects, removing the workarounds and patches I've put in there, and - well, they just all work like a charm!! This is great. Working with Windows products, I'm more used to being disappointed whenever people promise that this or that function will be in the next release. But this time, with PostgreSQL, not :-) -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 14.00-18.00Web: www.suse.dk 2000 FrederiksbergLørdag 11.00-17.00 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
AW: AW: [HACKERS] Plans for solving the VACUUM problem
Todo: 1. Compact log files after checkpoint (save records of uncommitted transactions and remove/archive others). On the grounds that undo is not guaranteed anyway (concurrent heap access), why not simply forget it, since above sounds rather expensive ? The downside would only be, that long running txn's cannot [easily] rollback to savepoint. 2. Abort long running transactions. This is imho the big downside of UNDO, and should not simply be put on the TODO without thorow research. I think it would be better to forget UNDO for long running transactions before aborting them. Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] RE: Re: Functions returning sets
Why not like Interbase ? when you define a procedure in Interbase, you have the 'suspend' instruction, it suspend execution of the stored procedure and returns variables, then come back to the procedure. select * from myfunc('ba ba'); select mycol from myfunc('dada'); escuse my poor english :) Mike Mascari wrote: I see Tom Lane implemented the SQL92 feature of using subselects in FROM clauses: CREATE TABLE foo ( key integer not null, value text); SELECT * FROM (SELECT * FROM foo) AS bar WHERE bar.key = 1; Perhaps this is how functions returning sets should operate: SELECT titles.* FROM titles, (SELECT funct_set('blah blah')) AS bar WHERE titles.title = bar.title; FWIW, Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] select for update question....
Title: select for update question Hello, As I understand it: Issuing a select for update within a transaction will prevent other users from modifying data. (exclusive lock) Im contemplating using the DBI interface as a permanent client to postgres for a group of users, and have a few questions. Will the above procedure still exclusively lock the row, when using a browser to issue a select for update? Im confused about sth-finish; and will it finish the transaction, before the user re-submits the form data to update the row Can anyone help me or offer some howtos to read regarding postgres and DBI? thanks. Raoul.
[HACKERS] Re: I don't understand...
Hi All again, after I deleted the null row from carname: SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) +---+ | h_name| +---+ | DAEWOO-FSO| | DAEWOO-LUBLIN | | GAZ | | TATA | +---+ Query OK, 4 rows in set (0,13 sec) It's working now, but is it correct? Bye, Gabor. I think it's good, but SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) ++ | h_name | ++ ++ Query OK, 0 rows in set (0,10 sec) Why ? postgres-7.1 rpm on RedHat 7.0 Thanks, Gabor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] RE: Re: Functions returning sets
Why not like Interbase ? when you define a procedure in Interbase, you a the 'suspend' instruction, it suspend execution of the stored procedure and returns variables, then come back to the procedure. select * from myfunc('ba ba'); select mycol from myfunc('dada'); escuse my poor english :) Mike Mascari wrote: I see Tom Lane implemented the SQL92 feature of using subselects in FROM clauses: CREATE TABLE foo ( key integer not null, value text); SELECT * FROM (SELECT * FROM foo) AS bar WHERE bar.key = 1; Perhaps this is how functions returning sets should operate: SELECT titles.* FROM titles, (SELECT funct_set('blah blah')) AS bar WHERE titles.title = bar.title; FWIW, Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] possible DOMAIN implementation
Hi, Tom Lane wrote: John Reid [EMAIL PROTECTED] writes: Ugh. Don't overload pg_class with things that are not tables. I see no reason that either pg_class or pg_attribute should be involved in the definition of a domain. Make new system tables if you need to, but don't confuse the semantics of critical tables. This is required due to the way inheritance is currently handled? Not inheritance specifically. I'nm just looking at it on general design principles: all the rows of a table should be the same kind of thing. We shade that a little to allow views, sequences, etc, in pg_class, but at least they're all things that have columns and so forth. These could actually be defined in pg_type (or an inherited class pg_class_def)? From what I can remember inheritance works in postgresql at the class level. C.J. Date et al *strongly* argue that inheritance should be based on types, not relations/classes. This is still the case in 7.1? Postgres doesn't really distinguish between tables and composite types --- there's a one-for-one relationship between 'em. So we haven't had to think hard about that point. If we did allow composite types without associated tables, we probably would want tables to inherit from 'em (which would mean some rethinking of the inheritance representation). Yes. I had a superficial look at SQL99 abstract data types a while back, but didn't get very far. I didn't raise any of the issues I came across at the time as everyone was busy with the 7.1 release. My interest is primarily in GIS data storage, which is a bit more involved than most applications. Ability to define complex types without having to instantiate them (or else implement them as user defined type when they are really a class) would be especially handy for GIS schemas. Not quite sure what else yet ;-) IMHO, it is probably worth looking at this further - it seems to me that these issues will have a significant impact when dealing with implementation of the SQL99 standard, so probably easier to deal with them now/soon? FWIW, some *really sketchy* ideas from when I looked at this: 1) pg_inherits should point at pg_type 2) some (most?) of the functionality of pg_class should be moved into pg_type ((2a) maybe using inherited class pg_class_def?) 3) pg_class should purely contain relation specific stuff only (type, indexes, owner) another alternative would be introduce a new system table pg_relation for relations, making pg_class the equivalent of pg_type but used for handling complex types. Then again, this is effectively the same as (2a)? Might make sense to think about renaming the tables anyway, as to me pg_class seems to imply the class definition, rather than the instantiation. Then we would have pg_type pg_class inherits pg_type pg_relation I could forsee some real chicken or the egg problems in system initialization. How are these handled currently? cheers, John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Thank you
I know this is not an on-topic post, but I wanted my message to reach the right people. I want to thank all of you who have worked so hard to make Postgres such an excellent database. Since people tend to complain a lot, I thought it might be nice to share some good news... I have just finished a several month project migrating a backend from Access to Postgres. We went live with PG about a week ago and the results are far better than we ever hoped for (knock on wood). The database is comprised of 43 tables, consumes over 167 MB (du on data) and tends to have 5-10 simultaneous users. We have gone from several database corruptions per day to 24 hour uptime. Using ADO/ODBC, our average calc engine run-time was reduced by 50%! Live backups, significantly increased performance, scalability for additional users, elimination of corruption, and a path for web access to our system -- Postgres has greatly simplified my life! Thank you for all of your hard work! Sincerely, Brian E. Pangburn The Pangburn Company, Inc. www.nqadmin.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: PL/pgSQL CURSOR support
Definitely it's Oracle's syntax. Zeugswetter Andreas SB [EMAIL PROTECTED] ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: news:[EMAIL PROTECTED]. at... Explicit cursor can be declared as: DECLARE ... curname CURSOR [(argname type [, ...])] IS select_stmt; In esql you would have FOR instead of IS. DECLARE curname CURSOR ... FOR Thus the question, where is the syntax from ? There seems to be a standard for the SQL stored procedure language: Persistent Stored Module definition of the ANSI SQL99 standard (quote from DB/2) Anybody know this ? Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: I don't understand...
Gabor - Tri-valued logic strikes again. Remember, NULL represents don't know, which means could be anything. So, when you ask the system to return values that are guaranteed not to be in a list, and that list contains a NULL, the system returns nothing, since the NULL _could_ be equal to the whatever value you're comparing against: the system just doesn't know. The operational fixes are: 1) delete nulls where they're not appropriate or better 2) use NOT NULL constraints everywhere you can. and 3) use WHERE NOT NULL in your subselects, if NULL is appropriate in the underlying column Ross On Mon, May 21, 2001 at 01:09:09PM +0200, Gabor Csuri wrote: Hi All again, after I deleted the null row from carname: SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) +---+ | h_name| +---+ | DAEWOO-FSO| | DAEWOO-LUBLIN | | GAZ | | TATA | +---+ Query OK, 4 rows in set (0,13 sec) It's working now, but is it correct? Bye, Gabor. I think it's good, but SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) ++ | h_name | ++ ++ Query OK, 0 rows in set (0,10 sec) Why ? postgres-7.1 rpm on RedHat 7.0 Thanks, Gabor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] Re: I don't understand...
On Mon, 21 May 2001, Gabor Csuri wrote: Hi All again, after I deleted the null row from carname: SELECT DISTINCT h_name FROM haszon WHERE h_name NOT IN (SELECT cn_name FROM carname) +---+ | h_name| +---+ | DAEWOO-FSO| | DAEWOO-LUBLIN | | GAZ | | TATA | +---+ Query OK, 4 rows in set (0,13 sec) It's working now, but is it correct? Yep. :( SQLs NULLs give lots of pain and suffering. NULL is an unknown value, so you can know that there *IS* a matching row, but you never know with certainty that there *ISN'T* a matching row when a NULL is involved. Basically IN says, if row1=row2 is true for any row, return true; if row1=row2 is false for every row return false; otherwise return NULL. When it gets to the comparison with the NULL, row1=row2 gives a NULL not a false, so the IN returns NULL (which won't get through the where clause). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Not released yet, but could someone take a quick peak ...
ftp://ftp.postgresql.org/pub/source/v7.1.2 ... Just want a second opinion before I announce more publicly ... Thanks ... Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Configurating perl access to a separate Postgres Server
Hi you all: I´ve got some kind of a problem in the deploy of my perl client. My environment is the following Ihave a Solaris 7 web server in the internet, powered by apache and outside the local net. From it I can access, through a hole in the firewall to my PostgreSQL 7.0.2 (yes, I´d better migrate cause it´s illusionating) server (Solaris 8) in the local net, and the requests can only come from the local web and from the web server, so it works extremely fine with JSP, Java and so on (wow!). The problem starts when I want my Perl CGI's to work in this environment, because i do not want to install more copies of Postgres in my not-very-much-free-space web server. (that´s why I have my own DB-server). Installation procedure tells me that I have to specify where is my Postgres installation, but I´ve got no installation! Can anyone tell me what I have to do to configure a pure client with no server capabilities? or it´s unpossible? or takes so long effort that it´s not worth? Thank you. Manuel SEDANO CADIN~ANOS [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Not released yet, but could someone take a quick peak...
broken how? I just connected into it ... On Tue, 22 May 2001, Karl DeBisschop wrote: The Hermit Hacker wrote: ftp://ftp.postgresql.org/pub/source/v7.1.2 ... Just want a second opinion before I announce more publicly ... I'd check. But the postgresql ftp site appears to be broken for the past few days. -- Karl Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org ---(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] Updating system catalogs after a tuple deletion
(This machine still is having trouble with mx records :( ) On Mon, 21 May 2001, Christopher Kings-Lynne wrote: Actually this brings up a problem I'm having with ALTER TABLE ADD CONSTRAINT and since it mostly affects you with DROP CONSTRAINT, I'll bring it up here. If you have a table that has check constraints or is inherited from multiple tables, what's the correct way to name an added constraint that's being inherited? If it's $2 in the parent, but the child already has a $2 defined, what should be done? The reason this affects drop constraint is knowing what to drop in the child. If you drop $2 on the parent, what constraint(s) on the child get dropped? It occurs to me that there's a solution to this problem. All you need to do is in heap.c in the piece of code I modified earlier for generating constraint names and checking specified ones you simply make sure it is unique for the parent table and for ALL its children. This will stop people (1) adding named constraints that aren't unique across all children, noting that these new constraints need to be added to the children as well as the parent and (2) dynamically generated constraint names will be unique across all children and also can then be immediately propagated to inherited tables. With this enforced, surely there is a _guaranteed_ match between the name of a parent constraint and the same constraint in the inherited tables? The only problem, I guess, would be when you import data from old versions of PostgreSQL into a new version that has this assumption/restriction. Actually, I realized that in the face of multiple inheritance, dynamically generated constraint names still fail with our current default naming scheme. What happens when two tables both have a $1 and then you inherit from both of them, at this point it's pretty much too late to rename the constraint on one of the parents and I think right now the constraints get named $1 and $2. Either, we should punt, and make it so they both end up $1, or perhaps we should change $1 to something like table_$1 where table is the table name of the table on which the constraint was defined. So if you have table1 with an unnamed constraint, it and all of its children would see the constraint as table1_$1. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] ? potential bug in LockBuffer ?
Folks: As I study the source of LockBuffer in bufmgr.c I came across the following code snippet for the case of releasing a shared (read) lock: if (mode == BUFFER_LOCK_UNLOCK) { if (*buflock BL_R_LOCK) { Assert(buf-r_locks 0); Assert(!(buf-w_lock)); Assert(!(*buflock (BL_W_LOCK | BL_RI_LOCK))); (buf-r_locks)--; *buflock = ~BL_R_LOCK; This code resets BL_R_LOCK on the first release of a shared lock. I think it should check that the count of readers be zero: ( something like if (mode == BUFFER_LOCK_UNLOCK) { if (*buflock BL_R_LOCK) { Assert(buf-r_locks 0); Assert(!(buf-w_lock)); Assert(!(*buflock (BL_W_LOCK | BL_RI_LOCK))); (buf-r_locks)--; if (!buf-r_locks) *buflock = ~BL_R_LOCK; Or I am missing something... thanks regards Mauricio _ Get your FREE download of MSN Explorer at http://explorer.msn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [HACKERS] ? potential bug in LockBuffer ?
(buf-r_locks)--; if (!buf-r_locks) *buflock = ~BL_R_LOCK; Or I am missing something... buflock is per-backend flag, it's not in shmem. Backend is allowed only single lock per buffer. Vadim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS]
Has this been already fixed or reported? - Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit Using pager is off. test=# select version(); version - PostgreSQL 7.1.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) test=# \dz Did not find any relation named z. test-# \dz Did not find any relation named z. Segmentation fault (core dumped) -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] C++ Headers
On Tue, May 22, 2001 at 12:19:41AM -0400, Bruce Momjian wrote: This in fact has happened within ECPG. But since sizeof(bool) is passed to libecpg it was possible to figure out which 'bool' is requested. Another issue of C++ compatibility would be cleaning up the usage of 'const' declarations. C++ is really strict about 'const'ness. But I don't know whether postgres' internal headers would need such a cleanup. (I suspect that in ecpg there is an oddity left with respect to host variable declaration. I'll check that later) We have added more const-ness to libpq++ for 7.2. Breaking link compatibility without bumping the major version number on the library seems to me serious no-no. To const-ify member functions without breaking link compatibility, you have to add another, overloaded member that is const, and turn the non-const function into a wrapper. For example: void Foo::bar() { ... } // existing interface becomes void Foo::bar() { ((const Foo*)this)-bar(); } void Foo::bar() const { ... } Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] C++ Headers
On Tue, May 22, 2001 at 05:52:20PM -0400, Bruce Momjian wrote: On Tue, May 22, 2001 at 12:19:41AM -0400, Bruce Momjian wrote: This in fact has happened within ECPG. But since sizeof(bool) is passed to libecpg it was possible to figure out which 'bool' is requested. Another issue of C++ compatibility would be cleaning up the usage of 'const' declarations. C++ is really strict about 'const'ness. But I don't know whether postgres' internal headers would need such a cleanup. (I suspect that in ecpg there is an oddity left with respect to host variable declaration. I'll check that later) We have added more const-ness to libpq++ for 7.2. Breaking link compatibility without bumping the major version number on the library seems to me serious no-no. To const-ify member functions without breaking link compatibility, you have to add another, overloaded member that is const, and turn the non-const function into a wrapper. For example: void Foo::bar() { ... } // existing interface becomes void Foo::bar() { ((const Foo*)this)-bar(); } void Foo::bar() const { ... } Thanks. That was my problem, not knowing when I break link compatiblity in C++. Major updated. Wouldn't it be better to add the forwarding function and keep the same major number? It's quite disruptive to change the major number for what are really very minor changes. Otherwise you accumulate lots of near-copies of almost-identical libraries to be able to run old binaries. A major-number bump should usually be something planned for and scheduled. Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
AW: [HACKERS] Is stats update during COPY IN really a good idea?
We have a TODO item * Update reltuples in COPY I was just about to go do this when I realized that it may not be such a hot idea after all. Imho it is not a good idea at all. The statistics are a very sensitive area, that imho should only be calculated on request. I already don't like the statistics that are implicitly created during create index. Eighter you have online stats keeping or you don't. For me this is a definite all or nothing issue. Anything inbetween is only good for unpleasant surprises. I have very strong feelings about this, because of bad experience. I would be willing to go into detail. A syntactic extension to copy (with analyze) on the other hand would be a feature. Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
AW: AW: [HACKERS] Plans for solving the VACUUM problem
As a rule of thumb, online applications that hold open transactions during user interaction are considered to be Broken By Design (tm). So I'd slap the programmer/design team with - let's use the server box since it doesn't contain anything useful. We have a database system here, and not an OLTP helper app. A database system must support all sorts of mixed usage from simple OLTP to OLAP. Imho the usual separation on different servers gives more headaches than are necessary. Thus above statement can imho be true for one OLTP application, but not for all applications on one db server. Andreas ---(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
AW: AW: [HACKERS] Plans for solving the VACUUM problem
Correct me if I am wrong, but both cases do present a problem currently in 7.1. The WAL log will not remove any WAL files for transactions that are still open (even after a checkpoint occurs). Thus if you do a bulk insert of gigabyte size you will require a gigabyte sized WAL directory. Also if you have a simple OLTP transaction that the user started and walked away from for his one week vacation, then no WAL log files can be deleted until that user returns from his vacation and ends his transaction. I am not sure, it might be so implemented. But there is no technical reason to keep them beyond checkpoint without UNDO. Andreas ---(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
AW: [HACKERS] Plans for solving the VACUUM problem
REDO in oracle is done by something known as a 'rollback segment'. You are not seriously saying that you like the rollback segments in Oracle. They only cause trouble: 1. configuration (for every different workload you need a different config) 2. snapshot too old 3. tx abort because rollback segments are full 4. They use up huge amounts of space (e.g. 20 Gb rollback seg for a 120 Gb SAP) If I read the papers correctly Version 9 gets rid of Point 1 but the rest ... Andreas ---(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
AW: [HACKERS] PL/pgSQL CURSOR support
Explicit cursor can be declared as: DECLARE ... curname CURSOR [(argname type [, ...])] IS select_stmt; In esql you would have FOR instead of IS. DECLARE curname CURSOR ... FOR Thus the question, where is the syntax from ? There seems to be a standard for the SQL stored procedure language: Persistent Stored Module definition of the ANSI SQL99 standard (quote from DB/2) Anybody know this ? Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
AW: AW: [HACKERS] Is stats update during COPY IN really a good idea?
However, just remember that pg_class already has a row count that we force in there by default. I was just suggesting we make that accurate if we can, even if we can make it accurate only 80% of the time. Once we INSERT, it isn't accurate anymore anyway. This is just an estimate, and in my mind, it doesn't have to be accurate in all cases. Actually I think the accuracy of db stats is often over estimated. For installed OLTP applications the most important thing is, that query plans are predictable. They do not even need to be optimal, they only need to deliver an expected performance. I actually do get perfect query plans without any stats, because our indexes are perfectly matched to our statements, and in two cases we tuned the sql appropriately (2 of 200 statements with Informix optimizer hints). For such a condition you actually want a rule based optimizer. The current default values during create table are more or less chosen to give exactly this rule based behavior. The trouble is, that after the first implicitly created stats, the optimizer goes completely bananas, because now he thinks that one table has 1000 (the default) rows (it actually has 1000), but the other has 10 and the optimizer now knows that and chooses a different plan. And just because you copy a few rows ? Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [HACKERS] Plans for solving the VACUUM problem
And, I cannot say that I would implement UNDO because of 1. (cleanup) OR 2. (savepoints) OR 4. (pg_log management) but because of ALL of 1., 2., 4. OK, I understand your reasoning here, but I want to make a comment. Looking at the previous features you added, like subqueries, MVCC, or WAL, these were major features that greatly enhanced the system's capabilities. Now, looking at UNDO, I just don't see it in the same league as those other additions. Of course, you can work on whatever you want, but I was hoping to see another major feature addition for 7.2. We know we badly need auto-vacuum, improved replication, and point-in-time recover. I don't like auto-vacuum approach in long term, WAL-based BAR is too easy to do -:) (and you know that there is man who will do it, probably), bidirectional sync replication is good to work on, but I'm more interested in storage/transaction management now. And I'm not sure if I'll have enough time for another major feature in 7.2 anyway. It would be better to put work into one mechanism that would reuse all tuples. This is what we're discussing now -:) If community will not like UNDO then I'll probably try to implement dead space collector which will read log files and so on. Easy to #ifdef it in 7.2 to use in 7.3 (or so) with on-disk FSM. Also, I have to implement logging for non-btree indices (anyway required for UNDO, WAL-based BAR, WAL-based space reusing). Vadim ---(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] Detecting readline in configure
On Mon, 21 May 2001, Peter Eisentraut wrote: Tom Lane writes: checking for libreadline ... no checking for libedit ... no * * NOTICE: I couldn't find libreadline nor libedit. You will * not have history support in psql. * This may be useful as well, but it doesn't help those doing unattended builds, such as RPMs and *BSD ports. In that case you need to abort to notify the user that things didn't go the way the package maker had planned. *BSD ports/packages shouldn't have much of a problem. They can encode dependencies, both in the binary package and in the build-from-source process. So if the package maker did things right, the packaging system would have either squalked, or tried to install libreadline before running configure. Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] BSD gettext
Peter Eisentraut wrote: http://www.postgresql.org/~petere/gettext.html This is a compilation of the BSD-licensed gettext tools from NetBSD plus some of my own code, put into a (hopefully) portable package, intended to be evaluated for possible use in PostgreSQL. Give it a try if you're interested. I've already tried it on FreeBSD, Linux, and Unixware, so don't bother with those. # uname -a SunOS mage 5.8 Generic_108528-06 sun4u sparc SUNW,Ultra-5_10 # pwd /usr/local/src/3/bsd-gettext-0.0 # gmake install /dev/null 21 # echo $? 0 # LANGUAGE=sv /usr/local/bin/gettext /usr/local/bin/gettext: argument saknas # -- Rick Robino v. (503) 891-9283 Wave Division Consulting@. wavedivision.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [HACKERS] Updating system catalogs after a tuple deletion
On Wed, 23 May 2001, Christopher Kings-Lynne wrote: Actually, I realized that in the face of multiple inheritance, dynamically generated constraint names still fail with our current default naming scheme. What happens when two tables both have a $1 and then you inherit from both of them, at this point it's pretty much too late to rename the constraint on one of the parents and I think right now the constraints get named $1 and $2. Either, we should punt, and make it so they both end up $1, or perhaps we should change $1 to something like table_$1 where table is the table name of the table on which the constraint was defined. So if you have table1 with an unnamed constraint, it and all of its children would see the constraint as table1_$1. Even if we implemented this, it wouldn't fix the problem of duplicated user specified constraint names under multiple inheritance. It seems a many-many pg_constraint table it the only clean solution... I'm not sure that there is a workable solution for user specified names without going the constraint names should be unique throughout solution (which Tom doesn't want, and actually neither do I really even though I bring it up as a compliance issue). I think that users will have to be assumed to be smart enough not to screw themselves up with badly named constraints. We definately need better storage of our constraints. I liked the constraint is stored once with pointers from referencing tables idea. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] DROP CONSTRAINT patch
On Tue, 22 May 2001, Christopher Kings-Lynne wrote: Anyone looked at this yet? Also, if someone could tell me where I should attempt to add a regression test and what, exactly, I should be regression testing it would be helpful... At the risk of making it even longer, probably alter_table.sql. You probably want to try out various conceivable uses of the drop constraint, including error conditions. Some things like: create table with constraint try to insert valid row try to insert invalid row drop the constraint try to insert valid row try to insert row that was invalid create table with two equal named constraints insert valid to both insert valid to one but not two insert valid to two but not one insert valid to neither ... create table with two non-equal named constraints (do inserts) drop constraint one try to insert valid for both, valid for one but not two valid for two but not one valid for neither drop constraint two (do more inserts) ---(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