Re: [HACKERS] Still a few flaws in configure's default CFLAGS selection
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Also, I thought Peter advocated adding -g a few releases back. I don't recall any such vote. The vote was whether -g should be used for a default compile. Here is the thread discussing the -g flag: http://archives.postgresql.org/pgsql-hackers/2002-04/msg00281.php What Peter was advocating in that thread was that we enable -g by default *when building with gcc*. I have no problem with that, since there is (allegedly) no performance penalty for -g with gcc. However, the actual present behavior of our configure script is to default to -g for every compiler, and I think that that is a big mistake. On most non-gcc compilers, -g disables optimizations, which is way too high a price to pay for production use. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Mapping Oracle types to PostgreSQL types
Dear friends, I would like to port Compiere CRM from Oracle to PostgreSQL (and release it for free). At first I would like to convert the data schema. This is not difficult as Compiere is written using portable types like NUMBER (i,d) which can be replaced by NUMERIC (i,d), etc... A series of Search/Replace is sufficiant. There are other solutions in Contrib to connect to Oracle and export the data (Bruce). Don't blame me to search in another (silly) direction... The point here is that I would like to use the CREATE TYPE or CREATE DOMAIN syntax to map Oracle types to PostgreSQL types. Therefore I can say Guys, Oracle is now mostly compatible with PostreSQL. In PostgreSQL, I used CREATE TYPE syntax to map Oracle nvarchar2 - PostgreSQL varchar (see code #1). The code seems to be the equivalent of CREATE DOMAIN nvarchar2 as varchar; Now I can create tables with nvarchar2 but not nvarchar2(lenght). Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght) in PostgreSQL 7.3? Are there plans to allow such mapping in the future using the CREATE DOMAIN syntax? Best regards, Jean-Michel Pouré ** Code #1 --DROP TYPE nvarchar2 CASCADE; CREATE OR REPLACE FUNCTION oracle_nvarchar2in(cstring, oid, int4) RETURNS nvarchar2 AS 'varcharin' LANGUAGE 'internal' IMMUTABLE STRICT; COMMENT ON FUNCTION oracle_nvarchar2in(cstring, oid, int4) IS '(internal)'; CREATE OR REPLACE FUNCTION oracle_nvarchar2out(nvarchar2) RETURNS cstring AS 'varcharout' LANGUAGE 'internal' IMMUTABLE STRICT; CREATE TYPE nvarchar2 (INPUT=oracle_nvarchar2in, OUTPUT=oracle_nvarchar2out, DEFAULT='', INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=EXTENDED); COMMENT ON TYPE nvarchar2 IS 'Oracle type nvarchar2(length) mapped to PostgreSQL type varchar(lenght)'; ---(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] Mapping Oracle types to PostgreSQL types
Jean-Michel POURE writes: Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght) in PostgreSQL 7.3? Are there plans to allow such mapping in the future using the CREATE DOMAIN syntax? No to both. Doing this would most likely require making the affected type names be reserved words in the grammar or sacrifice some other functionality, which seems a high price to pay for this cosmetic feature. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Some more information_schema issues
Tom Lane writes: The CHECK_CONSTRAINTS view should use pg_get_constraintdef() function rather than consrc, for the same reasons as psql should (I haven't fixed the latter yet, but will soon). True. Btw., is there a particular value in pg_get_constraintdef always printing double pairs of parentheses for CHECK constraints? There are several views that display pg_type.typname directly. I wonder whether any of these ought to be using format_type() instead. It won't matter for the views that only show domains, but several could potentially show standard types. Don't we want the output to be character rather than bpchar? typname is used in those contexts where the type name appears together with a schema name. In those cases you cannot use the result of format_type. It would be a small efficiency boost to use UNION ALL rather than UNION where possible. Good idea. READ COMMITED should be READ COMMITTED in sql_implementation_info. In sql_sizing, MAXIMUM COLUMNS IN SELECT should be 1664 (MaxTupleAttributeNumber). Several views get fixed pg_class OIDs like this: AND d.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class') This is unsafe OK. The ELEMENT_TYPES view doesn't work --- it returns zero rows. After some fooling around I think it's a simple typo: the line AND (n.nspname, x.objname, x.objtype, x.objtypeid) IN should be AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN OK. -- 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] Some thoughts about i/o priorities and throttling vacuum
Is it possible to have an optional delay in plain VACUUM for each invocation rather than database wide? Something along the line of an optional THROTTLE or DELAY parameter for the VACUUM command. The THROTTLE is ignored when FULL or FREEZE is selected. VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [THROTTLE] ANALYZE [ table [ (column [, ...] ) ] ] This way autovacuum can still throttle VACUUM as needed in future (either in contrib or backend) and administrators can decide to apply different delays for different tables depending on the usage. Regards, Stephen Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Bruce Momjian [EMAIL PROTECTED] writes: Of course, this makes VACUUM run longer, and if you are waiting for it to finish, it would be worse, like if you are running it at night or something. I think the delay has to take into account the number of active transactions or something. I was just thinking of a GUC parameter: wait N milliseconds between pages, where N defaults to zero probably. A user who wants to run his vacuum as a background process could set N larger than zero. I don't believe we are anywhere near being able to automatically adjust the delay based on load, and even if we could, this would ignore the point you make above --- the user's intent has to matter as much as anything else. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bison 1.875 for SuSE Linux 8.1?
Josh Berkus wrote: Peter, I can't seem to find a Bison 1.875 RPM for a SuSE 8.1 machine I can't afford to upgrade right now. Does such an animal exist? Help! Install it from source if you cannot find a package. Hmmm ... still getting the Bison Too Old warning message. How can I tell where Postgres is looking for Bison? I got the same problem when I upgraded my SuSE 8.1. Try which bison to locate it; usually, SuSE will install in /usr/bin, while packages installed from source will install to /usr/local/bin, so the older bison would take precedence. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Tom Lane wrote: I was just thinking of a GUC parameter: wait N milliseconds between pages, where N defaults to zero probably. A user who wants to run his vacuum as a background process could set N larger than zero. I don't believe we are anywhere near being able to automatically adjust the delay based on load, and even if we could, this would ignore the point you make above --- the user's intent has to matter as much as anything else. I am slightly confused here. IIRC pg_autovacuum never did a vacuum full. At the most it does vacuum /vacuum analyse, none of which chew disk bandwidth. And if pg_autovacuum is running along with postmaster all the time, with aggressive polling like 5 sec, the database should not accumulate any dead tuples nor it would suffer xid wraparound as there are vacuum happening constantly. What's left in above scenario? As long as all the requirements for pg_autovacuum are met, namely setting it up, setting it up aggressively and tuning postgresql.conf correctly, vacuum and related problems should be a thing in past, at least as far as 7.4 and onwards is considered. Of course RSM implementation for vacuum would still be much needed but right now, it does not affect disk IO directly(except for tossing buffer cache out of track that is). What am I missing? Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Mapping Oracle types to PostgreSQL types
Jean-Michel POURE wrote: Dear friends, I would like to port Compiere CRM from Oracle to PostgreSQL (and release it for free). At first I would like to convert the data schema. This is not difficult as Compiere is written using portable types like NUMBER (i,d) which can be replaced by NUMERIC (i,d), etc... A series of Search/Replace is sufficiant. There are other solutions in Contrib to connect to Oracle and export the data (Bruce). Don't blame me to search in another (silly) direction... Rather than declaring numeric, create them as integer/float of appropriate size and add appropriate constraints. Numeric can be slower for large data load w.r.t native integers. The point here is that I would like to use the CREATE TYPE or CREATE DOMAIN syntax to map Oracle types to PostgreSQL types. Therefore I can say Guys, Oracle is now mostly compatible with PostreSQL. You can create some sql scripts which can natively migrate from oracle to postgresql. Contrib could host them or gborg. So what postgresql would say is, create a database and run the scripts and many of the oracle migration gotchas will be automatically taken care of. Including such features in core postgresql is rather hard sell to postgresql developers. Especially when there is a rather simple workaround. HTH Shridhar ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Mapping Oracle types to PostgreSQL types
Shridhar Daithankar wrote: Jean-Michel POURE wrote: Dear friends, I would like to port Compiere CRM from Oracle to PostgreSQL (and release it for free). At first I would like to convert the data schema. This is not difficult as Compiere is written using portable types like NUMBER (i,d) which can be replaced by NUMERIC (i,d), etc... A series of Search/Replace is sufficiant. There are other solutions in Contrib to connect to Oracle and export the data (Bruce). Don't blame me to search in another (silly) direction... Rather than declaring numeric, create them as integer/float of appropriate size and add appropriate constraints. Numeric can be slower for large data load w.r.t native integers. float is *not* an alternative to numeric. While integer or int8 should be usable for NUMBER(i), the non-rounding precision of numeric is vital for such an application. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote: I am slightly confused here. IIRC pg_autovacuum never did a vacuum full. At the most it does vacuum /vacuum analyse, none of which chew disk bandwidth. The latter is false. VACUUM FULL certainly uses _more_ disk bandwidth than VACUUM, but it's just false that plain VACUUM doesn't contend for disk. And if you're already maxed, then that extra bandwidth you cannot afford. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Some more information_schema issues
Peter Eisentraut [EMAIL PROTECTED] writes: True. Btw., is there a particular value in pg_get_constraintdef always printing double pairs of parentheses for CHECK constraints? No, but it will require some restructuring of the code to get rid of it safely (where safely is defined as never omitting any parentheses that *are* necessary). For the moment I'm willing to live with the ugliness. You could consider pretty-printing (pass true to pg_get_constraintdef) if you think visual appeal is better than assured correctness. There are several views that display pg_type.typname directly. I wonder whether any of these ought to be using format_type() instead. typname is used in those contexts where the type name appears together with a schema name. In those cases you cannot use the result of format_type. Okay, fair enough. 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] Some thoughts about i/o priorities and throttling vacuum
Andrew Sullivan wrote: On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote: I am slightly confused here. IIRC pg_autovacuum never did a vacuum full. At the most it does vacuum /vacuum analyse, none of which chew disk bandwidth. The latter is false. VACUUM FULL certainly uses _more_ disk bandwidth than VACUUM, but it's just false that plain VACUUM doesn't contend for disk. And if you're already maxed, then that extra bandwidth you cannot afford. What part of plain vacuum takes disk bandwidth? WAL? Clog? Certainly not data files themselves, right? OK, I understand some system can be saturated enough to have additional WAL/Clog burdon, but genuinely curious, how much disk bandwidth is required for plain vacuum and what are the factors it depends upon? Shridhar ---(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] Some thoughts about i/o priorities and throttling vacuum
On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote: And if pg_autovacuum is running along with postmaster all the time, with aggressive polling like 5 sec, the database should not accumulate any dead tuples nor it would suffer xid wraparound as there are vacuum happening constantly. The database can suffer XID wraparound anyway if there's at least one table without updates, because the autovacuum daemon will never vacuum it (correct me if I'm wrong). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Tiene valor aquel que admite que es un cobarde (Fernandel) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Mapping Oracle types to PostgreSQL types
Peter Eisentraut [EMAIL PROTECTED] writes: Jean-Michel POURE writes: Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght) in PostgreSQL 7.3? Are there plans to allow such mapping in the future using the CREATE DOMAIN syntax? No to both. Doing this would most likely require making the affected type names be reserved words in the grammar Right. At the moment, *all* the type names that support parenthesized options are hard-wired into the grammar. I think this is probably unavoidable because otherwise there is a conflict between interpreting foo(3) as a type name and interpreting it as a function call. (But if anyone can think of a way around that, I'm all ears.) Since varchar(n) is SQL-standard syntax, can't you simply adopt the more standard name for both databases? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Alvaro Herrera wrote: On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote: And if pg_autovacuum is running along with postmaster all the time, with aggressive polling like 5 sec, the database should not accumulate any dead tuples nor it would suffer xid wraparound as there are vacuum happening constantly. The database can suffer XID wraparound anyway if there's at least one table without updates, because the autovacuum daemon will never vacuum it (correct me if I'm wrong). If a table is never updated and hence not vacuumed at all, why would it be involved in a transaction that would have xid wrap around? pg_autovacuum takes care of insert/updates/deletes. If a table never participates in above three and hence escape from pg_autovauum, it also escapes from xid wraparound, isn't it? Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
On Fri, Oct 17, 2003 at 07:41:38PM +0530, Shridhar Daithankar wrote: Alvaro Herrera wrote: On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote: The database can suffer XID wraparound anyway if there's at least one table without updates, because the autovacuum daemon will never vacuum it (correct me if I'm wrong). If a table is never updated and hence not vacuumed at all, why would it be involved in a transaction that would have xid wrap around? Because the tuples on it were involved in some insert operation at some time (else the table would not have any tuples). So it _has_ to be vacuumed, else you run the risk of losing the tuples when the wraparound happens. (Sorry, I don't know how to explain this better.) Maybe in this case it's best to do a VACUUM FREEZE; that'd ensure that the table would never ever need a vacuum again until it suffers an insert, delete or update. Perhaps the autovacuum daemon could detect the case where a table has only very old tuples and freeze it. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El número de instalaciones de UNIX se ha elevado a 10, y se espera que este número aumente (UPM, 1972) ---(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] Some thoughts about i/o priorities and throttling vacuum
Shridhar Daithankar [EMAIL PROTECTED] writes: What part of plain vacuum takes disk bandwidth? Reading (and possibly rewriting) all the pages. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Alvaro Herrera wrote: On Fri, Oct 17, 2003 at 07:41:38PM +0530, Shridhar Daithankar wrote: Alvaro Herrera wrote: On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote: The database can suffer XID wraparound anyway if there's at least one table without updates, because the autovacuum daemon will never vacuum it (correct me if I'm wrong). If a table is never updated and hence not vacuumed at all, why would it be involved in a transaction that would have xid wrap around? Because the tuples on it were involved in some insert operation at some time (else the table would not have any tuples). So it _has_ to be vacuumed, else you run the risk of losing the tuples when the wraparound happens. (Sorry, I don't know how to explain this better.) OK. So here is what I understand. I have a table which contains 100 rows which appeated there due to some insert operation. Then I vacuum it. And sit there for internity for rest of the database to approach the singularity(the xid wraparound..:-) Nice term, isn't it?). So this static table is vulnerable to xid wraparound? I doubt. Did I miss something? Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Tom Lane wrote: Shridhar Daithankar [EMAIL PROTECTED] writes: What part of plain vacuum takes disk bandwidth? Reading (and possibly rewriting) all the pages. I was under impression that was for shared memory pages only and not for disk pages. OK. I can see difference of understanding here. Plain Vacuum goes around the table/database and makes space, shared buffers and disks, reusable whenever possible but *does not* free any space. Would it be possible to have a vacuum variant that would just shuffle thr. shared buffers and not touch disk at all? pg_autovacuum could probably be ulra agressive with such a shared-buffers only scan? Is it possible or feasible? IMO that could be a clever solution rather than throttling IO for vacuum. For one thing, getting that throttiling right, would be extremely difficult and varying from site to site. If it is going to be tough to tune, then it will be underutilised and will lose it's value rather rapidly. Just a thought.. Shridhar ---(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] Mapping Oracle types to PostgreSQL types
On Fri, 2003-10-17 at 04:10, Jean-Michel POURE wrote: Dear friends, I would like to port Compiere CRM from Oracle to PostgreSQL (and release it for free). This would be wonderful. However, I believe the guys at Compiere tried to do this already and gave up on porting it to postgresql due too a couple of PostgreSQL limitations. I don't remember what they are exactly, I think it had to do with nested transactions, maybe savepoints, not sure exactly. You should be able to find some mention of this on their site. It sounded to me like they use a lot of Oracle features. Good Luck! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Shridhar Daithankar [EMAIL PROTECTED] writes: Would it be possible to have a vacuum variant that would just shuffle thr. shared buffers and not touch disk at all? What would be the use of that? You couldn't predict *anything* about the coverage. Maybe you find all the free space in a particular table, but most likely you don't. In any case an I/O-free vacuum is impossible since once you have decided to recycle a particular tuple, you don't have any option about removing the corresponding index entries first. So unless both the table and all its indexes are in RAM, you will be incurring I/O. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
On Fri, 2003-10-17 at 09:34, Shridhar Daithankar wrote: I am slightly confused here. IIRC pg_autovacuum never did a vacuum full. Correct. At the most it does vacuum /vacuum analyse, Incorrect, it either does vacuum analyse, or just analyse none of which chew disk bandwidth. Incorrect, vacuum can have lots of disk I/O, analyze has considerably less, but still some. And if pg_autovacuum is running along with postmaster all the time, with aggressive polling like 5 sec, the database should not accumulate any dead tuples True, however, I think such aggressive polling will be a net loss in efficiency. nor it would suffer xid wraparound as there are vacuum happening constantly. Wrong, pg_autovacuum typically just does vacuum [table name], which does not effect the xid wraparound issue, one has to issue a vacuum against an entire database to effect that. What's left in above scenario? As long as all the requirements for pg_autovacuum are met, namely setting it up, setting it up aggressively and tuning postgresql.conf correctly, vacuum and related problems should be a thing in past, at least as far as 7.4 and onwards is considered. Well it still remains to be seen if the client side implementation of pg_autovacuum is sufficient. Also, we will see if index bloat is handled (less an autovac issue, but semi-related). Ideally, autovac should make better decisions based on FSM and perhaps even the RSM (is that what it was called?) that people have talked about setting up. With all that said, hopefully pg_autovacuum proves to be a successful experiment, and if so, then it needs to be integrated into core somehow. Matthew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Mapping Oracle types to PostgreSQL types
On Fri, 17 Oct 2003, Tom Lane wrote: Since varchar(n) is SQL-standard syntax, can't you simply adopt the more standard name for both databases? A long time ago Oracle made the varchar type equivalent to char and once people complained about the excess space used by short entries they came out with varchar2 which they've maintained every since valuing backwards compatability more than the sql standard. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
On Fri, Oct 17, 2003 at 07:55:44PM +0530, Shridhar Daithankar wrote: OK. So here is what I understand. I have a table which contains 100 rows which appeated there due to some insert operation. Then I vacuum it. And sit there for internity for rest of the database to approach the singularity(the xid wraparound..:-) Nice term, isn't it?). So this static table is vulnerable to xid wraparound? I doubt. Did I miss something? You are missing the part when the XID that was formerly a committed transaction becomes an uncommitted transaction when the wraparound occurs... so the tuples will have creation XID by an uncommitted transaction, and current transactions will not see them. Voila, your table is empty. The trick to keep in mind is that the XID comparison functions use modulo operations, _but_ there are special frozen XIDs that are always committed -- that's why a VACUUM FREEZE would relieve the table forever from this problem. (At least this is how I understand it -- I could be totally wrong here) -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Los dioses no protegen a los insensatos. Éstos reciben protección de otros insensatos mejor dotados (Luis Wu, Mundo Anillo) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
On Fri, 2003-10-17 at 10:25, Shridhar Daithankar wrote: OK. So here is what I understand. I have a table which contains 100 rows which appeated there due to some insert operation. Then I vacuum it. And sit there for internity for rest of the database to approach the singularity(the xid wraparound..:-) Nice term, isn't it?). So this static table is vulnerable to xid wraparound? I doubt. No that table would probably be ok, because you did a vacuum on it after the inserts. The problem is that pg_autovacuum may choose not to do a vacuum if you didn't cross a threshold, or someone outside of pg_autovacuum may have done the vacuum and autovac doesn't know about it, so it can't guarantee that all tables in the database are safe from xid wraparound. One additional thing, some of this might be possible if pg_autovacuum saved its data between restarts. Right now it restarts with no memory of what happened before. ---(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] Mapping Oracle types to PostgreSQL types
Le Vendredi 17 Octobre 2003 16:32, Matthew T. O'Connor a crit : This would be wonderful. However, I believe the guys at Compiere tried to do this already and gave up on porting it to postgresql due too a couple of PostgreSQL limitations. I don't remember what they are exactly, I think it had to do with nested transactions, maybe savepoints, not sure exactly. You should be able to find some mention of this on their site. It sounded to me like they use a lot of Oracle features. There are only a few limitations in PostgreSQL like nested transaction, updatable cursors and Oracle PL error handling. Can we call these limitations? Most of us can live without them. These limitations are only a small portion of the code (sometimes a few lines like updatable cursors). Cheers, Jean-Michel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Alvaro Herrera [EMAIL PROTECTED] writes: Maybe in this case it's best to do a VACUUM FREEZE; that'd ensure that the table would never ever need a vacuum again until it suffers an insert, delete or update. But how would you keep track of that? Certainly an external autovacuum daemon couldn't know for sure that the table had never been modified since it was frozen. I suppose you could think about altering the backend to mark a table dirty whenever an insert/update/delete is done, but I'd have to think this would be a net waste of cycles in the vast majority of cases. How many people have tables that are *really* read-only over the long haul (billions of transactions)? I think the existing approach of forcing a database-wide vacuum every billion or so transactions is probably the most efficient way of dealing with the issue. It's almost certainly cheaper, net, than any scheme that adds even a tiny overhead to each individual insert/update/delete. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Matthew T. O'Connor wrote: On Fri, 2003-10-17 at 10:25, Shridhar Daithankar wrote: OK. So here is what I understand. I have a table which contains 100 rows which appeated there due to some insert operation. Then I vacuum it. And sit there for internity for rest of the database to approach the singularity(the xid wraparound..:-) Nice term, isn't it?). So this static table is vulnerable to xid wraparound? I doubt. No that table would probably be ok, because you did a vacuum on it after the inserts. The problem is that pg_autovacuum may choose not to do a vacuum if you didn't cross a threshold, or someone outside of pg_autovacuum may have done the vacuum and autovac doesn't know about it, so it can't guarantee that all tables in the database are safe from xid wraparound. One additional thing, some of this might be possible if pg_autovacuum saved its data between restarts. Right now it restarts with no memory of what happened before. Well, the unmaintened gborg version adopted approach of storing such info. in a table, so that it survives postgresql/pg_atuvacuum restart or both. That was considered a tablespace pollution back then. But personally I think, it should be ok. If ever it goes to catalogues, I would rather add few columns to pg_class for such a stat. But again, thats not my call to make. Shridhar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Tom Lane wrote: Shridhar Daithankar [EMAIL PROTECTED] writes: Would it be possible to have a vacuum variant that would just shuffle thr. shared buffers and not touch disk at all? What would be the use of that? You couldn't predict *anything* about the coverage. Maybe you find all the free space in a particular table, but most likely you don't. In any case an I/O-free vacuum is impossible since once you have decided to recycle a particular tuple, you don't have any option about removing the corresponding index entries first. So unless both the table and all its indexes are in RAM, you will be incurring I/O. I am just suggesting it as a variant and not a replacement for existing vacuum options. Knowing that it does not do any IO, it could be triggered lot more aggressively. Furthermore if we assume pg_autovacuum as integral part of database operation, right before from a single database object is created, I think it could cover many/most database usage patterns barring multiple indexes, for which normal vacuum variants could be used. Furthermore, when a tuple is updated, all the relevant indexes are updated, right? So if such a vacuum is aggressive enough, it could catch the index entries as well, in the RAM. Think of it like catching hens. Easier to do in a cage rather than over a farm. So catch as many of them in cage. If they escape or spill out of cage due to over-population, you have to tread the farm anyways... Just a thought. Shridhar ---(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] Some thoughts about i/o priorities and throttling vacuum
On Fri, Oct 17, 2003 at 07:25:13PM +0530, Shridhar Daithankar wrote: What part of plain vacuum takes disk bandwidth? WAL? Clog? Certainly not data files themselves, right? Sure, the data files. The data files still have to be completely read from beginning to end by VACUUM. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
On Fri, 2003-10-17 at 10:22, Tom Lane wrote: Shridhar Daithankar [EMAIL PROTECTED] writes: What part of plain vacuum takes disk bandwidth? Reading (and possibly rewriting) all the pages. Would it be possible for the backend to keep a list of the first N (N being a large number but not significant in memory usage) pages it has deleted tuples out of and a second list of N pages it has inserted tuples into. After the transaction has completed and there is an idle period (say 1/4 second between transaction) it can pass the insert information on a rollback and delete information on a commit to a separate backend. This 'vacuum' backend could then prioritize garbage collection for the pages it knows have been changed performing a single page vacuum when a specific page has seen a high level of reported activity. If this daemon could also get a hold of information about idleness of IO in general the decision about what to vacuum and when may be better (heavily hit pages during peak periods, all reports pages on medium load). When completely idle, run through the entire system to get back as much as possible. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Some more information_schema issues
True. Btw., is there a particular value in pg_get_constraintdef always printing double pairs of parentheses for CHECK constraints? No, but it will require some restructuring of the code to get rid of it safely (where safely is defined as never omitting any parentheses that *are* necessary). For the moment I'm willing to live with the ugliness. You could consider pretty-printing (pass true to pg_get_constraintdef) if you think visual appeal is better than assured correctness. We could check the first character of the definition, and if it isn't a left parenthesis, then we add parentheses. Chris ---(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] Some thoughts about i/o priorities and throttling vacuum
Rod Taylor wrote: On Fri, 2003-10-17 at 10:22, Tom Lane wrote: Shridhar Daithankar [EMAIL PROTECTED] writes: What part of plain vacuum takes disk bandwidth? Reading (and possibly rewriting) all the pages. Would it be possible for the backend to keep a list of the first N (N being a large number but not significant in memory usage) pages it has deleted tuples out of and a second list of N pages it has inserted tuples into. That is RSM, reclaimable space map. It is on TODO. After the transaction has completed and there is an idle period (say 1/4 second between transaction) it can pass the insert information on a rollback and delete information on a commit to a separate backend. This 'vacuum' backend could then prioritize garbage collection for the pages it knows have been changed performing a single page vacuum when a specific page has seen a high level of reported activity. If this daemon could also get a hold of information about idleness of IO in general the decision about what to vacuum and when may be better (heavily hit pages during peak periods, all reports pages on medium load). When completely idle, run through the entire system to get back as much as possible. I agree. This seems to be the best way of dealing with things. Of course, probably there are details we are missing here, but in general its good. Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Some more information_schema issues
Christopher Kings-Lynne [EMAIL PROTECTED] writes: We could check the first character of the definition, and if it isn't a left parenthesis, then we add parentheses. And we would be wrong. Consider (a 0) and (b 0) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Shridhar Daithankar [EMAIL PROTECTED] writes: I agree. This seems to be the best way of dealing with things. Of course, probably there are details we are missing here, but in general its good. Actually, this is all pure handwaving, because you are ignoring the need to remove index tuples. The existing VACUUM code amortizes index cleanup over as many tuples as it can. If you do partial vacuuming of tables then you are necessarily going to be expending more cycles (and I/O) per tuple, on average, to get rid of the index entries. It's not at all clear that there's any real win to be had in that direction. Perhaps it's a win, but you have no evidence on which to assert so. regards, tom lane ---(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] Some thoughts about i/o priorities and throttling vacuum
Alvaro Herrera [EMAIL PROTECTED] writes: The trick to keep in mind is that the XID comparison functions use modulo operations, _but_ there are special frozen XIDs that are always committed -- that's why a VACUUM FREEZE would relieve the table forever from this problem. (At least this is how I understand it -- I could be totally wrong here) No, that's exactly correct. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
On Fri, 2003-10-17 at 10:53, Shridhar Daithankar wrote: Matthew T. O'Connor wrote: One additional thing, some of this might be possible if pg_autovacuum saved its data between restarts. Right now it restarts with no memory of what happened before. Well, the unmaintened gborg version adopted approach of storing such info. in a table, so that it survives postgresql/pg_atuvacuum restart or both. That was considered a tablespace pollution back then. But personally I think, it should be ok. If ever it goes to catalogues, I would rather add few columns to pg_class for such a stat. But again, thats not my call to make. I still consider it tablespace pollution, when / if it gets integrated into the backend, and it uses system tables that is a different story, you are not modifying a users database. What should happen is that on exit pg_autovacuum writes it's data to a file that it rereads on startup, or something like that ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
[EMAIL PROTECTED] (Shridhar Daithankar) writes: Andrew Sullivan wrote: On Fri, Oct 17, 2003 at 07:04:45PM +0530, Shridhar Daithankar wrote: I am slightly confused here. IIRC pg_autovacuum never did a vacuum full. At the most it does vacuum /vacuum analyse, none of which chew disk bandwidth. The latter is false. VACUUM FULL certainly uses _more_ disk bandwidth than VACUUM, but it's just false that plain VACUUM doesn't contend for disk. And if you're already maxed, then that extra bandwidth you cannot afford. What part of plain vacuum takes disk bandwidth? WAL? Clog? Certainly not data files themselves, right? Certainly YES, the data files themselves. VACUUM has to read through the pages to assess what tuples are to expire. So if the data file is 8GB long, VACUUM has to read through 8GB of data. As compared to VACUUM FULL, it is certainly cheaper, as it is not rummaging around to reorder pages, but rather walking through, single page by single page. Thus, where VACUUM FULL might involve (in effect) reading through the file several times (as it shifts data between pages), VACUUM only reads through it once. That's (for the for instance) 8GB of reads. -- cbbrowne,@,libertyrms.info http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
[EMAIL PROTECTED] (Shridhar Daithankar) writes: Tom Lane wrote: I was just thinking of a GUC parameter: wait N milliseconds between pages, where N defaults to zero probably. A user who wants to run his vacuum as a background process could set N larger than zero. I don't believe we are anywhere near being able to automatically adjust the delay based on load, and even if we could, this would ignore the point you make above --- the user's intent has to matter as much as anything else. I am slightly confused here. IIRC pg_autovacuum never did a vacuum full. At the most it does vacuum /vacuum analyse, none of which chew disk bandwidth. [remainder elided; your second sentence is the vital bit...] What am I missing? You are missing that VACUUM most certainly _does_ chew up disk bandwidth, because it must load the pages of the table into memory. If the system is busy doing other I/O, then the other I/O has to compete with the I/O initiated by VACUUM. VACUUM FULL is certainly more expensive than VACUUM/VACUUM ANALYZE; the point is that even the latter is NOT free on big tables when there is a lot of traffic. VACUUM is like putting an extra few transport trucks onto the highway. It may only go from one highway junction to the next, and be fairly brief, if traffic is moving well. But if traffic is heavy, it adds to the congestion. (And that's as far as the analogy can go; I can't imagine a way of drawing the GUC parameter into this...) -- (format nil [EMAIL PROTECTED] cbbrowne libertyrms.info) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bison 1.875 for SuSE Linux 8.1?
Tom, Really? 'cause I got the warning from the Beta4 tarball. If you mean the configure warning, sure, but the build won't fail. Might I suggest changing the wording in the final release, then? The warning sure looked dangerous; I aborted the build and went looking for bison 1.875 binaries. We should let people know that the warning is non-fatal so they don't repeat my experience. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Bison 1.875 for SuSE Linux 8.1?
Josh Berkus writes: Might I suggest changing the wording in the final release, then? The warning sure looked dangerous; It only looks dangerous to those who don't actually read the full text of the message. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Christopher Browne [EMAIL PROTECTED] writes: VACUUM is like putting an extra few transport trucks onto the highway. It may only go from one highway junction to the next, and be fairly brief, if traffic is moving well. But if traffic is heavy, it adds to the congestion. (And that's as far as the analogy can go; I can't imagine a way of drawing the GUC parameter into this...) Ooh strained metaphors. This game is always fun. So I think of it the other way around. A busy database is like downtown traffic with everyone going every which way for short trips. Running vacuum is like having a few trucks driving through your city streets for through traffic. Having a parameter to slow down the through traffic is like, uh, having express lanes for local traffic. er, yeah, that's the ticket. Except who ever heard of having express lanes for local traffic. Hm. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
Greg Stark wrote: Christopher Browne [EMAIL PROTECTED] writes: VACUUM is like putting an extra few transport trucks onto the highway. It may only go from one highway junction to the next, and be fairly brief, if traffic is moving well. But if traffic is heavy, it adds to the congestion. (And that's as far as the analogy can go; I can't imagine a way of drawing the GUC parameter into this...) Ooh strained metaphors. This game is always fun. So I think of it the other way around. A busy database is like downtown traffic with everyone going every which way for short trips. Running vacuum is like having a few trucks driving through your city streets for through traffic. Having a parameter to slow down the through traffic is like, uh, having express lanes for local traffic. er, yeah, that's the ticket. Except who ever heard of having express lanes for local traffic. Hm. All I know is that Jan Wieck would have each car filled to the brim with spikes Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Error with views containing sub-queries with distinct
Last night I just upgraded to a nightly snapshot of 7.4 and noticed an error on queries that had previously worked (in version 7.3.x and previous 7.4's snapshots up to about a month old). I have a view that I can distill into a base case of: CREATE VIEW testing_v AS SELECT table_a.* FROM table_a WHERE (table_a.some_id IN ( SELECT DISTINCT table_b.some_id FROM table_b )); When I do : select * from testing_v; I get: JOIN qualification may not refer to other relations I have found that if I remove the distinct on the sub-query, it behaves as expected. Yes, I know the distinct is probably useless but we are in the process of porting it to postgres and have lots of cleanup left. We are migrating from a commercial database (and paid good money) and found postgres to be a joy (substantially fast, less gotchas, better at embeding business logic at db level). Thank you very much. I appreciate all the effort that has put into such a great product. --spt PS: Please cc me as I am not on list. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL on Novell Netware 6.5.
Hi, It's been quite a while ago that I did NetWare programming (3.11 NLM, communication protocol for a dbms), and that I had some contact with Netware (4.1), but if Novell didn't change the architecture completely (I doubt that) the native win32 port won't be of any help for They've done the job. Forget all about 3.x / 4.x and even 5.x. No more CLib, you have now LibC which is a POSIX like API. AFAIK, they're finishing some usefull APIs, which'll make the port much more easy. The only difference will be the threaded model instead of process based... NW6.x already have Apache, bash, gcc, mysql, openssh, vnc, etc... thanks to this new architecture. You can get the 7.2.4 on http://forge.novell.com Eduardo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Dreaming About Redesigning SQL
[EMAIL PROTECTED] (Seun Osewa) wrote in message news:[EMAIL PROTECTED]... Hi, This is for relational database theory experts on one hand and imlementers of real-world alications on the other hand. If there was a chance to start again and design SQL afresh, for best cleaness/power/performance what changes would you make? What would _your_ query language (and the underlying database concept) look like? Seun Osewa PS: I should want to post my ideas too for review but more experienced/qualified people should come first Some ideas, links, and complaints about SQL and remaking or replacing it: http://www.c2.com/cgi/wiki?SqlFlaws -T- ---(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] Dreaming About Redesigning SQL
In article [EMAIL PROTECTED], Gene Wirchenko [EMAIL PROTECTED] writes [EMAIL PROTECTED] (Seun Osewa) wrote: [snip] Sometimes I wonder why its so important to model data in the rela- tional way, to think of data in form of sets of tuples rather than tables or lists or whatever. I mean, though its elegant and based on mathematical principles I would like to know why its the _right_ model to follow in designing a DBMS (or database). The way my mind sees it, should we not rather be interested in what works? How do you know it works? Without the theory and model, you really do not. And don't other databases have both theory and model? It's just that all the academics have been brainwashed into thinking this is true only for relational, so that's what they teach to everyone else, and the end result is that all research is ploughed into a model that may be (I didn't say is) bankrupt. Just like the academics were brainwashed into thinking that microkernels were the be-all and end-all - until Linus showed them by practical example that they were all idiots :-) Cheers, Wol -- Anthony W. Youngman - wol at thewolery dot demon dot co dot uk Witches are curious by definition and inquisitive by nature. She moved in. Let me through. I'm a nosey person., she said, employing both elbows. Maskerade : (c) 1995 Terry Pratchett ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Vacuum thoughts
The more I think about this vacuum i/o problem, the more I think we have it wrong. The added i/o from vacuum really ought not be any worse than a single full table scan. And there are probably the occasional query doing full table scans already in those systems. For the folks having this issue, if you run select count(*) from bigtable is there as big a hit in transaction performance? On the other hand, does the vacuum performance hit kick in right away? Or only after it's been running for a bit? I think the other factor mentioned is actually the main problem: cache. The vacuum basically kills the kernel buffer cache by reading in every block of every table in the system. The difference between vacuum and a single select count(*) is that it does all the tables one after each other eventually overrunning the total cache available. If it's just a matter of all the read i/o from vacuum then we're best off sleeping for a few milliseconds every few kilobytes. If it's the cache then we're probably better off reading a few megabytes and then sleeping for several seconds to allow the other buffers to get touched and pushed back to the front of the LRU. Hm, I wonder if the amount of data to read between sleeps should be, something like 25% of the effective_cache_size, for example. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Vacuum thoughts
On Fri, 2003-10-17 at 16:22, Greg Stark wrote: If it's just a matter of all the read i/o from vacuum then we're best off sleeping for a few milliseconds every few kilobytes. If it's the cache then we're probably better off reading a few megabytes and then sleeping for several seconds to allow the other buffers to get touched and pushed back to the front of the LRU. Uh, no -- if it is the cache, we're better off fixing the buffer replacement policy, not trying to hack around it. Replacement policies that don't suffer from sequential flooding are well known. -Neil ---(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] Some thoughts about i/o priorities and throttling vacuum
On Fri, 2003-10-17 at 14:56, Mike Mascari wrote: Greg Stark wrote: Ooh strained metaphors. This game is always fun. So I think of it the other way around. A busy database is like downtown traffic with everyone going every which way for short trips. Running vacuum is like having a few trucks driving through your city streets for through traffic. Having a parameter to slow down the through traffic is like, uh, having express lanes for local traffic. er, yeah, that's the ticket. Except who ever heard of having express lanes for local traffic. Hm. All I know is that Jan Wieck would have each car filled to the brim with spikes ROTFLAMO ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Some thoughts about i/o priorities and throttling vacuum
[EMAIL PROTECTED] (Mike Mascari) writes: Greg Stark wrote: Christopher Browne [EMAIL PROTECTED] writes: VACUUM is like putting an extra few transport trucks onto the highway. It may only go from one highway junction to the next, and be fairly brief, if traffic is moving well. But if traffic is heavy, it adds to the congestion. (And that's as far as the analogy can go; I can't imagine a way of drawing the GUC parameter into this...) Ooh strained metaphors. This game is always fun. So I think of it the other way around. A busy database is like downtown traffic with everyone going every which way for short trips. Running vacuum is like having a few trucks driving through your city streets for through traffic. Having a parameter to slow down the through traffic is like, uh, having express lanes for local traffic. er, yeah, that's the ticket. Except who ever heard of having express lanes for local traffic. Hm. All I know is that Jan Wieck would have each car filled to the brim with spikes No, you just need _one_ spike. _One_ spike in the centre of the steering wheel. There would be _so_ much less tailgating if they had those spikes... -- cbbrowne,@,libertyrms.info http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Dreaming About Redesigning SQL
Quoth Anthony W. Youngman [EMAIL PROTECTED]: In article [EMAIL PROTECTED], Gene Wirchenko [EMAIL PROTECTED] writes [EMAIL PROTECTED] (Seun Osewa) wrote: [snip] Sometimes I wonder why its so important to model data in the rela- tional way, to think of data in form of sets of tuples rather than tables or lists or whatever. I mean, though its elegant and based on mathematical principles I would like to know why its the _right_ model to follow in designing a DBMS (or database). The way my mind sees it, should we not rather be interested in what works? How do you know it works? Without the theory and model, you really do not. And don't other databases have both theory and model? It's just that all the academics have been brainwashed into thinking this is true only for relational, so that's what they teach to everyone else, and the end result is that all research is ploughed into a model that may be (I didn't say is) bankrupt. Just like the academics were brainwashed into thinking that microkernels were the be-all and end-all - until Linus showed them by practical example that they were all idiots :-) In mathematics as well as in the analysis of computer algorithms, it is typical for someone who is trying to explain something new to try to do so in terms that allow the gentle reader to do as direct a comparison as possible between the things with which they are familiar (e.g. - in this case, relational database theory) and the things with which they are perhaps NOT familiar (e.g. - in this case, MV databases). Nobody seems to have been prepared to explain the MV model in adequate theoretical terms as to allow the gentle readers to compare the theory behind it with the other theories out there. I'm afraid that does not reflect very well on either those lauding MV or those trashing it. - Those lauding it have not made an attempt to show why the theory behind it would support it being preferable to the other models around. I hear some vague Oh, it's not about models; it's about language which doesn't get to the heart of anything. - And all we get from Bob Badour are dismissive sound-bites that _don't_ explain why he should be taken seriously. Indeed, the sharper and shorter he gets, the less credible that gets. There are no pointers to Michael Stonebraker on Why Pick Is Not My Favorite Database. Brian Kernighan felt the issues with Pascal were important enough that he wrote a nice, approachable paper that quite cogently describes the problems with Standard Pascal. http://www.lysator.liu.se/c/bwk-on-pascal.html He nicely summarizes it with 9 points that fit on a sheet of paper. If Bob wanted people to take him really seriously about this, and has done all the research to back up the points that are apparently so obvious to him, then it should surely be _easy_ to write up Nine Reasons Pick Isn't My Favorite Database System. And just as people have been pointing back to Kernighan's paper on Pascal for over 20 years, folks could point back to the Pick essay. But apparently it is much too difficult for anyone to present any _useful_ discourse on it. -- (reverse (concatenate 'string ac.notelrac.teneerf @ 454aa)) http://cbbrowne.com/info/nondbms.html For a good prime call: 391581 * 2^216193 - 1 -- [EMAIL PROTECTED] (Szymon Rusinkiewicz) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vacuum thoughts
Neil Conway [EMAIL PROTECTED] writes: Uh, no -- if it is the cache, we're better off fixing the buffer replacement policy, not trying to hack around it. If we can. As long as we are largely depending on the kernel's buffer cache, we may not be able to just fix it ... 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] Error with views containing sub-queries with distinct
Sean P. Thomas [EMAIL PROTECTED] writes: I get: JOIN qualification may not refer to other relations Problem confirmed here, will look into it. I have a feeling this is a bad side-effect of this patch: 2003-10-13 19:48 tgl * src/backend/optimizer/prep/prepjointree.c: pull_up_subqueries() should copy the subquery before starting to modify it. Not sure why I'd thought it would be a good idea to do differently way back when, but Greg Stark exposed the folly of doing so ... but I don't see why as yet. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Writers Wanted
Hello All: As the new Editor-N-Cheif of PostgreSQL.Org it is my job to make sure that people who want to write about PostgreSQL, can. I will be in constant communication with various publications and will be helping in the advocacy of PostgreSQL through wide spread dissemination http://dictionary.reference.com/search?r=2q=dissemination of the truth ;). I will also be requesting topics from writers from time to time. If you are, or would like to be a writer please contact me at [EMAIL PROTECTED] and include the following information: Name: Email: Experience: Topics you feel qualified to write about: Programming languages known: Willing to work for free: Y/N Lead time needed for average article: 1 week, 1 month etc... I will be in contact with all types of online and print media. Some will not be able to pay a fee but you will still get author credit which can add to your writer validity. Sincerely, Joshua D. Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
