Re: [HACKERS] Improving speed of copy
On 20 Sep 2002 at 18:41, Nigel J. Andrews wrote: On Fri, 20 Sep 2002, Shridhar Daithankar wrote: In select test where approx. 15 rows where reported with query on index field, mysql took 14 sec. and psotgresql took 17.5 sec. Not bad but other issues eclipse the result.. I don't know about anyone else but I find this aspect strange. That's 1 second (approx.) per row retrieved. That is pretty dire for an index scan. The data/index must be very non unique. Sorry for late reply.. The numbers were scaled off.. Actually my fiend forgot to add units to those number.. The actual numbers are 140ms for mysql and 17 5ms for postgresql.. Further since result are obtained via 'time psql' higher overhead of postgres connection establishement is factored in.. Neck to neck I would say.. Bye Shridhar -- Steele's Law: There exist tasks which cannot be done by more than ten men or fewer than one hundred. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] regression test failure in CVS HEAD
On Fri, Sep 20, 2002 at 01:12:17PM -0400, Bruce Momjian wrote: Tom has fixed it. Sorry I didn't test earlier. Thanks. Neil Conway wrote: It seems the 'numeric' and 'int8' tests are failing in CVS HEAD. The culprit seems to be the recent to_char() change made by Karel, but I haven't verified that. The diff follows. You're right. Sorry. SELECT '' AS to_char_14, to_char(q2, 'FM.999') FROM INT8_TBL; to_char_14 | to_char ! + ! | 456. ! | 4567890123456789. ! | 123. ! | 4567890123456789. ! | -4567890123456789. The results like this are right. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] [PATCHES] to_char(FM9.9) bug fix
On Fri, Sep 20, 2002 at 09:24:00PM +0200, Peter Eisentraut wrote: Karel Zak writes: test=# select to_char(0,'FM9.9'); to_char - 0. (1 row) test=# select to_char(1,'FM9.9'); to_char - 1. (1 row) I find this highly bizzare. The FM modifier means to omit unnecessary In the code it's commented as terrible Ora format :-) trailing stuff. There is no reasonable business or scientific custom to leave a trailing point after a number. I think so. I don't know who can use format number like '1.' or '.0'. Can somebody explain why Oracle implement it, who use it? Or perhaps a more pragmatic question is, how would I print a number without the trailing point? Don't use FM or use FM9.0 Examples: 'SVRMGR' = Oracle8 Release 8.0.5.0.0 'test=#' = PostgreSQL 7.3b1 test=# select to_char(1, 'FM9.9'); to_char - 1. SVRMGR select to_char(1, 'FM9.9') from dual; TO_C 1. test=# select to_char(1, '9.9'); to_char - 1.0 SVRMGR select to_char(1, '9.9') from dual; TO_C 1.0 test=# select to_char(1, 'FM9.0'); to_char - 1.0 SVRMGR select to_char(1, 'FM9.0') from dual; TO_C 1.0 -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] DROP COLUMN misbehaviour with multiple inheritance
Tom Lane kirjutas P, 22.09.2002 kell 18:56: Alvaro Herrera [EMAIL PROTECTED] writes: Another interesting case is multiple inheritance. create table p1 (f1 int); create table p2 (f1 int); create table c () inherits(p1, p2); drop ONLY column p1.f1; drop column p2.f1; After this sequence, what is the state of c.f1? Is it still there? Should it be? Well, in this case the column is dropped. If the last drop is ONLY, the column will stay (regardless of what the first drop did). It seems to me that DROP ONLY should set attislocal true on each child for which it decrements the inherit count, whether the count reaches zero or not. This would not be what I e'd expect - if c inherited f1 twice and then one of the parents disinherits it, then it would still be inherited from the other parent This would cause the behavior in the above case to be that c.f1 stays around after the second drop (but can be dropped with a third drop of c.f1 itself). I'd vote for the way Alvaro describes it - keep the attislocal=false while there exist parents from which the column was inherited. I think this is correct, since the implication of DROP ONLY is that child columns are being cut loose from their parent's apron strings and now have independent existence. For me the implication is that ONLY this parent cuts loose the strings from its side, but should not mess with anything the child inherits from other parties. This is a minor tweak to your patch, and I'll make it work that way unless I hear squawks... I was disconnected for the weekend, I hope this is not too late to squawk ;) - Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Tom Lane kirjutas P, 22.09.2002 kell 18:56: Alvaro Herrera [EMAIL PROTECTED] writes: Another interesting case is multiple inheritance. create table p1 (f1 int); create table p2 (f1 int); create table c () inherits(p1, p2); drop ONLY column p1.f1; drop column p2.f1; After this sequence, what is the state of c.f1? Is it still there? Should it be? Well, in this case the column is dropped. If the last drop is ONLY, the column will stay (regardless of what the first drop did). It seems to me that DROP ONLY should set attislocal true on each child for which it decrements the inherit count, whether the count reaches zero or not. Would it then not produce a situation, which can't be reproduced using just CREATEs ? i.e. same column in bot parent (p2.f1) and child (c.f1) but _not_ inherited ?? Then there would be no way to move a field from one parent table to another and still have it as an inherited column in child. It also seems bogus considering when doing SELECT * FROM p2 -- How should the select behave regarding c.f1 - there is a field with the same name and type but not inherited . This would cause the behavior in the above case to be that c.f1 stays around after the second drop (but can be dropped with a third drop of c.f1 itself). What if you have a deeper hierarchy under c - will this make you traverse them all to drop f1 ? I think this is correct, since the implication of DROP ONLY is that child columns are being cut loose from their parent's apron strings and now have independent existence. From (this) parent's but not from (other) parents' ;) Like In real world one should only be allowed to disinherit what _he_ owns. -- Hannu ---(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] DROP COLUMN misbehaviour with multiple inheritance
Hannu Krosing dijo: Tom Lane kirjutas P, 22.09.2002 kell 18:56: It seems to me that DROP ONLY should set attislocal true on each child for which it decrements the inherit count, whether the count reaches zero or not. Would it then not produce a situation, which can't be reproduced using just CREATEs ? i.e. same column in bot parent (p2.f1) and child (c.f1) but _not_ inherited ?? No, you cannot do that. For example, create table p1 (f1 int, f2 int); create table p2 (f1 int, f3 int); create table c () inherits (p1, p2); alter table only p1 drop column f1; alter table only p2 drop column f1; In this case, f1 is kept on c, and this situation can be recreated as: create table p1 (f2 int); create table p2 (f3 int); create table c (f1 int) inherits (p2, p3); If you drop it on only one parent it is exactly the same. The next question is whether pg_dump knows how to do such things. The answer is that it doesn't know that it must locally define f1 on c if you drop the column on only one parent. Oddly enough, the following create table p (f1 int); create table c (f1 int not null); produces the right behavior in pg_dump, but create table p (f1 int); create table c () inherits (p); alter table c alter f1 set not null; produces exactly the same as the former. I don't know if it's right. Then there would be no way to move a field from one parent table to another and still have it as an inherited column in child. You cannot add a column to a table that is inherited by another table that has a column with the same name: inhtest=# alter table p1 add column f1 int; ERROR: ALTER TABLE: column name f1 already exists in table c inhtest=# alter table only p1 add column f1 int; ERROR: Attribute must be added to child tables too inhtest=# IOW: there's no way to move a column, unless you drop it in the whole inheritance tree first. Maybe this is a bug, and adding a column that exists in all childs (with the same name and type) should be allowed. It also seems bogus considering when doing SELECT * FROM p2 -- How should the select behave regarding c.f1 - there is a field with the same name and type but not inherited . I don't understand. Suppose table c has column f1. If I select from p2 and it has f1 also, f1 will show up. If p2 doesn't have f1, it won't: the inheritance status of the attribute doesn't matter. This would cause the behavior in the above case to be that c.f1 stays around after the second drop (but can be dropped with a third drop of c.f1 itself). What if you have a deeper hierarchy under c - will this make you traverse them all to drop f1 ? The recursion is always done in steps one level deep. If the column is inherited from somewhere else in the grandchild, it will stay. If not, it will disappear. If you want to drop in more than one level, but not all of them, you will have to drop it locally on each. This seems just natural, doesn't it? -- Alvaro Herrera (alvherre[a]atentus.com) Granting software the freedom to evolve guarantees only different results, not better ones. (Zygo Blaxell) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
En 23 Sep 2002 10:23:06 +0200 Hannu Krosing [EMAIL PROTECTED] escribió: Tom Lane kirjutas P, 22.09.2002 kell 18:56: It seems to me that DROP ONLY should set attislocal true on each child for which it decrements the inherit count, whether the count reaches zero or not. This would not be what I e'd expect - if c inherited f1 twice and then one of the parents disinherits it, then it would still be inherited from the other parent The problem with this is that two sequences of commands only differing in the ordering of two clauses give different result: create table p1 (f1 int, f2 int); create table p2 (f1 int, f2 int); create table c () inherits (p1, p2); alter table only p1 drop column f1; alter table p2 drop column f1; create table p1 (f1 int, f2 int); create table p2 (f1 int, f2 int); create table c () inherits (p1, p2); alter table p2 drop column f1; alter table only p1 drop column f1; The former drops f1 from c, while the latter does not. It's inconsistent. -- Alvaro Herrera (alvherre[a]atentus.com) La Primavera ha venido. Nadie sabe como ha sido (A. Machado) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Alvaro Herrera kirjutas E, 23.09.2002 kell 10:06: Hannu Krosing dijo: Tom Lane kirjutas P, 22.09.2002 kell 18:56: It seems to me that DROP ONLY should set attislocal true on each child for which it decrements the inherit count, whether the count reaches zero or not. Would it then not produce a situation, which can't be reproduced using just CREATEs ? i.e. same column in bot parent (p2.f1) and child (c.f1) but _not_ inherited ?? No, you cannot do that. For example, create table p1 (f1 int, f2 int); create table p2 (f1 int, f3 int); create table c () inherits (p1, p2); alter table only p1 drop column f1; alter table only p2 drop column f1; In this case, f1 is kept on c, and this situation can be recreated as: create table p1 (f2 int); create table p2 (f3 int); create table c (f1 int) inherits (p2, p3); If you drop it on only one parent it is exactly the same. I meant create table p1 (f1 int, f2 int); create table p2 (f1 int, f3 int); create table c () inherits (p1, p2); alter table only p1 drop column f1; If you now set c.f1.attislocal = 1 as suggested by Tom , it seems like you have a local p1.f1 _and_ local c.f1 , for which there is no way to create without DROP's. If I understand the meaning of attislocal correctly, the after the above, I could do ALTER TABLE c DROP COLUMN f1, which would break SELECT * FROM p2. The next question is whether pg_dump knows how to do such things. The answer is that it doesn't know that it must locally define f1 on c if you drop the column on only one parent. Oddly enough, the following create table p (f1 int); create table c (f1 int not null); Did you mean create table c (f1 int not null) inherits (p); ? produces the right behavior in pg_dump, but create table p (f1 int); create table c () inherits (p); alter table c alter f1 set not null; produces exactly the same as the former. I don't know if it's right. Then there would be no way to move a field from one parent table to another and still have it as an inherited column in child. You cannot add a column to a table that is inherited by another table that has a column with the same name: inhtest=# alter table p1 add column f1 int; ERROR: ALTER TABLE: column name f1 already exists in table c inhtest=# alter table only p1 add column f1 int; ERROR: Attribute must be added to child tables too inhtest=# IOW: there's no way to move a column, unless you drop it in the whole inheritance tree first. Maybe this is a bug, and adding a column that exists in all childs (with the same name and type) should be allowed. It should be symmetric to DROP behaviour. So we should first check, if there are no childs with columns with the same name but different type, then add it to all children where it is missing and just make it inherited, where it is already present. --- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Alvaro Herrera kirjutas E, 23.09.2002 kell 10:30: En 23 Sep 2002 10:23:06 +0200 Hannu Krosing [EMAIL PROTECTED] escribió: Tom Lane kirjutas P, 22.09.2002 kell 18:56: It seems to me that DROP ONLY should set attislocal true on each child for which it decrements the inherit count, whether the count reaches zero or not. This would not be what I e'd expect - if c inherited f1 twice and then one of the parents disinherits it, then it would still be inherited from the other parent The problem with this is that two sequences of commands only differing in the ordering of two clauses give different result: IMHO this is the correct behaviour create table p1 (f1 int, f2 int); create table p2 (f1 int, f2 int); create table c () inherits (p1, p2); alter table only p1 drop column f1; Here you get rid of f1 in p1 _only_, i.e you keep it in children. alter table p2 drop column f1; At this point c.f1 is inherited from only p2 and should be dropped create table p1 (f1 int, f2 int); create table p2 (f1 int, f2 int); create table c () inherits (p1, p2); alter table p2 drop column f1; Here c.f1 is still inherited from p1 and thus will not be dropped alter table only p1 drop column f1; If you say ONLY you _do_ mean don't drop from child tables. The former drops f1 from c, while the latter does not. It's inconsistent. But this is what _should_ happen. It is quite unreasonable to expect that order of commands makes no difference. Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PGXLOG variable worthwhile?
On Sun, 22 Sep 2002, Tom Lane wrote: It was pretty clear that Thomas' original patch lost the vote, or would have lost if we'd bothered to hold a formal vote. Hasn't there just been a formal vote on this? I don't see anyone arguing against the notion of making XLOG location more easily configurable --- it was just the notion of making it depend on environment variables that scared people. And it's obvious it was centred on the use of an environment variable from the subject line, it's still got PGXLOG in capitals in it. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGXLOG variable worthwhile?
Nigel J. Andrews wrote: snip And it's obvious it was centred on the use of an environment variable from the subject line, it's still got PGXLOG in capitals in it. Actually, to be really precise, my original email asked for an environment variable. But only because I'd thought about it from the point of view of us already having a PGDATA environment variable and hadn't considered alternatives nor seen Thomas's stuff. Personally, I don't care if it's a -X, or an environment variable, or a GUC option. I'm just extremely positive that we should have an alternative to using symlinks for this (they don't work properly on NT). After following the discussion for a while I'm inclined to think that we should indeed have the GUC version, and *maybe* have the environment variable or the -X. The only thing bad about the -X is it's ability to trash your data if you forget it or get it wrong, and it's really easy to do in a decent scale environment with many servers. Marc has already suggested we might as well have something about a particular pg_xlog directory that PostgreSQL can use to check it's validity upon startup, so that could solve the data damaging issue. So, this thread has migrated away from a PGXLOG environment variable to discuss PGXLOG in general (good or bad) and also has implementation points too (about which people have been arguing). Regards and best wishes, Justin Clift -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Problem on PG7.2.2
Hi All, When I try 2 or 3 consecutive select count(*) on my database I've the problem shown below. Here is a psql session log: [root@foradada root]# psql -d database 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 database=# select version(); version - PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) database=# select count(*) from detail; count 181661 (1 row) database=# select count(*) from detail; count 181660 (1 row) database=# select count(*) from detail; FATAL 2: open of /var/lib/pgsql/data/pg_clog/0303 failed: No such file or directo ry server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. database=# Roberto Fichera. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ECPG
On Sun, Sep 22, 2002 at 04:18:23PM -0400, Tom Lane wrote: I had a thought about what to do with the ECPG grammar-too-big problem: rather than depending on a beta release of bison, we could attack the problem directly by omitting some of the backend grammar from what ECPG supports. Surely there are not many people using ECPG to issue obscure utility commands like, for example, DROP OPERATOR CLASS. But then there may be one. And I'd prefer to not remove features that used to exist. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PGXLOG variable worthwhile?
On Sun, 22 Sep 2002, Marc G. Fournier wrote: Thomas implemented an option that he felt was useful, and that doesn't break anything inside of the code ... he provided 2 methods of being able to move the xlog's to another location (through command line and environment variable, both of which are standard methods for doing such in server software) ... but, because a small number of ppl voted that it should go away, it went away ... The option as he implemented it did make the system more fragile. You can't back up an environment variable, it's separated from other configuration information, and it's more easily changed without realizing it. We should be building systems that are as resilient to human failure as possible, not opening up more possibilities of failure. We already have a place for configuration information: the configuration file. If I created a patch to move a variable out of the configuration file and make it an environment variable instead, everybody would (rightly) think I was nuts, and the patch certainly would not be accepted. So why should the situation be different for new configuration information? cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Hannu Krosing [EMAIL PROTECTED] writes: Alvaro Herrera kirjutas E, 23.09.2002 kell 10:30: The former drops f1 from c, while the latter does not. It's inconsistent. But this is what _should_ happen. On what grounds do you claim that? I agree with Alvaro: it's inconsistent to have ONLY produce different effects depending on the order in which you issue the commands. It is quite unreasonable to expect that order of commands makes no difference. Why? I'll agree that it's not an overriding argument, but it is something to shoot for if we can. And I'm not seeing the argument on the other side. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Postgresql Automatic vacuum
Hello All, I have written a small daemon that can automatically vacuum PostgreSQL database, depending upon activity per table. It sits on top of postgres statistics collector. The postgres installation should have per row statistics collection enabled. Features are, * Vacuuming based on activity on the table * Per table vacuum. So only heavily updated tables are vacuumed. * multiple databases supported * Performs 'vacuum analyze' only, so it will not block the database The project location is http://gborg.postgresql.org/project/pgavd/projdisplay.php Let me know for bugs/improvements and comments.. I am sure real world postgres installations has some sort of scripts doing similar thing. This is an attempt to provide a generic interface to periodic vacuum. Bye Shridhar -- The Abrams' Principle: The shortest distance between two points is off the wall. ---(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] DROP COLUMN misbehaviour with multiple inheritance
Hannu Krosing [EMAIL PROTECTED] writes: I meant create table p1 (f1 int, f2 int); create table p2 (f1 int, f3 int); create table c () inherits (p1, p2); alter table only p1 drop column f1; If you now set c.f1.attislocal = 1 as suggested by Tom , it seems like you have a local p1.f1 _and_ local c.f1 , for which there is no way to create without DROP's. Uh, no, you don't have a p1.f1 at all. If I understand the meaning of attislocal correctly, the after the above, I could do ALTER TABLE c DROP COLUMN f1, which would break SELECT * FROM p2. No you could not, because c.f1 still has attinhcount = 1 due to the inheritance from p2. As long as c.f1.attinhcount 0, you won't be allowed to drop c.f1. attislocal does not override that. The next question is whether pg_dump knows how to do such things. The answer is that it doesn't know that it must locally define f1 on c if you drop the column on only one parent. That's a good point. It could be fixed easily though (pg_dump would just have to take attislocal into consideration when deciding whether to emit a column definition in the child table). ... produces the right behavior in pg_dump, but create table p (f1 int); create table c () inherits (p); alter table c alter f1 set not null; produces exactly the same as the former. I don't know if it's right. I think this is fine. Having done something to the field in c (and not recursively from p) means that you are attaching special new meaning to c.f1; I'm okay with equating this action to c is now locally defined. Maybe the backend should make that equation too, and actively set attislocal in the top level when doing an ALTER COLUMN. BTW, do we prohibit ALTER DROP NOT NULL on inherited columns? We probably should. You cannot add a column to a table that is inherited by another table that has a column with the same name: inhtest=# alter table p1 add column f1 int; ERROR: ALTER TABLE: column name f1 already exists in table c inhtest=# alter table only p1 add column f1 int; ERROR: Attribute must be added to child tables too inhtest=# IOW: there's no way to move a column, unless you drop it in the whole inheritance tree first. Maybe this is a bug, and adding a column that exists in all childs (with the same name and type) should be allowed. Yeah, this is an implementation shortcoming in ALTER ADD COLUMN: if it finds an existing column of the same name in a child table, it should test whether it's okay to merge the columns (same types, no conflict in constraints/defaults, cf CREATE's behavior); if so, it should increment the child column's attinhcount instead of failing. I had noticed that yesterday, and meant to ask Bruce to put it on TODO, but got distracted with other stuff. 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] Postgresql Automatic vacuum
On 23 Sep 2002 at 14:50, Lee Kindness wrote: Shridhar, Might be useful to add a .tag.gz to the downloads, so people do not have to use CVS to take a look. There is a development snapshot.. Bye Shridhar -- In most countries selling harmful things like drugs is punishable.Then howcome people can sell Microsoft software and go unpunished?(By [EMAIL PROTECTED], Hasse Skrifvars) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ECPG
Michael Meskes [EMAIL PROTECTED] writes: On Sun, Sep 22, 2002 at 04:18:23PM -0400, Tom Lane wrote: I had a thought about what to do with the ECPG grammar-too-big problem: rather than depending on a beta release of bison, we could attack the problem directly by omitting some of the backend grammar from what ECPG supports. Surely there are not many people using ECPG to issue obscure utility commands like, for example, DROP OPERATOR CLASS. But then there may be one. And I'd prefer to not remove features that used to exist. What about removing this feature that used to exist: being able to build ecpg with reasonably-standard tools? I think you should be setting more weight on that concern than on supporting obscure backend commands (some of which didn't even exist in 7.2, and therefore are certainly not depended on by any ecpg user...) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Hannu Krosing [EMAIL PROTECTED] writes: It seems to me that DROP ONLY should set attislocal true on each child for which it decrements the inherit count, whether the count reaches zero or not. Would it then not produce a situation, which can't be reproduced using just CREATEs ? i.e. same column in bot parent (p2.f1) and child (c.f1) but _not_ inherited ?? No, because the child will still have attinhcount 0 until you drop the last matching parent column. attislocal is independent of the value of attinhcount (that's why we need two fields). 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] Problem on PG7.2.2
Roberto Fichera [EMAIL PROTECTED] writes: database=# select count(*) from detail; count 181661 (1 row) database=# select count(*) from detail; count 181660 (1 row) database=# select count(*) from detail; FATAL 2: open of /var/lib/pgsql/data/pg_clog/0303 failed: No such file or directory [ blinks... ] That's with no one else modifying the table meanwhile? I think you've got *serious* hardware problems. Hard to tell if it's disk or memory, but get out those diagnostic programs now ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Problem on PG7.2.2
At 10.40 23/09/02 -0400, Tom Lane wrote: Roberto Fichera [EMAIL PROTECTED] writes: database=# select count(*) from detail; count 181661 (1 row) database=# select count(*) from detail; count 181660 (1 row) database=# select count(*) from detail; FATAL 2: open of /var/lib/pgsql/data/pg_clog/0303 failed: No such file or directory [ blinks... ] That's with no one else modifying the table meanwhile? This table is used to hold all the logs from our Radius servers, so we have only INSERT from the radiusd server. I think you've got *serious* hardware problems. Hard to tell if it's disk or memory, but get out those diagnostic programs now ... What diagnostic programs do you suggest ? Roberto Fichera. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Default privileges for 7.3
Bruce Momjian [EMAIL PROTECTED] writes: 7 . 3 O P E NI T E M S Loading 7.2 pg_dumps functions no longer public executable languages no longer public usable Although it's reasonably easy to fix no-privileges problems for functions after you load a dump, it occurs to me that the same does not hold for PL languages. If a newly created language doesn't have USAGE available to public, then any function definitions in your dump are going to fail, if they belong to non-superusers. I am thinking that the better course might be to have newly created languages default to USAGE PUBLIC, at least for a release or two. We might also consider letting newly created functions default to EXECUTE PUBLIC. I think this is less essential, but a case could still be made for it on backwards-compatibility grounds. If you don't want to hard-wire that behavior, what about a GUC variable that could be turned on while loading old dumps? Comments? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Default privileges for 7.3
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: 7 . 3 O P E NI T E M S Loading 7.2 pg_dumps functions no longer public executable languages no longer public usable Although it's reasonably easy to fix no-privileges problems for functions after you load a dump, it occurs to me that the same does not hold for PL languages. If a newly created language doesn't have USAGE available to public, then any function definitions in your dump are going to fail, if they belong to non-superusers. I am thinking that the better course might be to have newly created languages default to USAGE PUBLIC, at least for a release or two. We might also consider letting newly created functions default to EXECUTE PUBLIC. I think this is less essential, but a case could still be made for it on backwards-compatibility grounds. Yes, I am wondering if we should go one release with them open to give people a chance to adjust, but actually, I don't understand how we could do that effectively. Do we tell them to add GRANTs in 7.3 and tighten it down in 7.4, and if we do that, will the GRANTs be recorded in pg_dump properly? To me a table contains data, while a function usually just causes an action, and I don't see why an action has to be restricted (same with language). I realize we have some actions that must be limited, like clearing the stat collector, but the majority seem benign. Does the standard require us to restrict their executability? If you don't want to hard-wire that behavior, what about a GUC variable that could be turned on while loading old dumps? I think GUC is going to be confusing. Let's see if we can decide on a good course first. Well, we better decide something before we do beta2. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Temp tables and LRU-K caching
Hello. I'm just curious as to the 7.3 status of a couple of things: 1. Back in Feb. I wrote (in regards to Oracle behavior): Unlike normal queries where blocks are added to the MRU end of an LRU list, full table scans add the blocks to the LRU end of the LRU list. I was wondering, in the light of the discussion of using LRU-K, if PostgreSQL does, or if anyone has tried, this technique? Bruce wrote: Yes, someone from India has a project to test LRU-K and MRU for large table scans and report back the results. He will implement whichever is best. Did this make it into 7.3? 2. Gavin Sherry had worked up a patch so that temporary relations could be dropped automatically upon transaction commit. Did any of those patches it make it? I notice that whenever I create a temporary table in a transaction, my HD light blinks. Is this a forced fsync() causes by the fact that the SQL standard defines temporary relations as surviving across transactions? If so, I'd bet those of us who use transaction-local temporary tables could get few drops more of performance from an ON COMMIT drop patch w/o fsync. Any thoughts? Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Temp tables and LRU-K caching
Mike Mascari [EMAIL PROTECTED] writes: Bruce wrote: Yes, someone from India has a project to test LRU-K and MRU for large table scans and report back the results. He will implement whichever is best. Did this make it into 7.3? No, we never heard back from that guy. It is still a live topic though. One of the Red Hat people was looking at it over the summer, and I think Neil Conway is experimenting with LRU-2 code right now. 2. Gavin Sherry had worked up a patch so that temporary relations could be dropped automatically upon transaction commit. Did any of those patches it make it? No they didn't; I forget whether there was any objection to his last try or it was just too late to get reviewed before feature freeze. I notice that whenever I create a temporary table in a transaction, my HD light blinks. Is this a forced fsync() causes by the fact that the SQL standard defines temporary relations as surviving across transactions? A completely-in-memory temp table is not really practical in Postgres, for two reasons: one being that its schema information is stored in the definitely-not-temp system catalogs, and the other being that we request allocation of disk space for each page of the table, even if it's temp. It might be possible to work around the latter issue (at the cost of quite unfriendly behavior should you run out of disk space) but short of a really major rewrite there isn't any way to avoid keeping temp table catalog info in the regular catalogs. So you are certainly going to get a disk hit when you create or drop a temp table. 7.3 should be considerably better than 7.1 or 7.2 for temp table access because it doesn't WAL-log operations on the data within temp tables, though. Another thing I'd like to see in the near future is a configurable setting for the amount of memory space that can be used for temp-table buffers. The current setting is ridiculously small (64*8K IIRC), but there's not much point in increasing it until we also have a smarter management algorithm for the temp buffers. I've asked Neil to look at making the improved LRU-K buffer management algorithm apply to temp buffers as well as regular shared buffers. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Temp tables and LRU-K caching
Mike Mascari wrote: Hello. I'm just curious as to the 7.3 status of a couple of things: 1. Back in Feb. I wrote (in regards to Oracle behavior): Unlike normal queries where blocks are added to the MRU end of an LRU list, full table scans add the blocks to the LRU end of the LRU list. I was wondering, in the light of the discussion of using LRU-K, if PostgreSQL does, or if anyone has tried, this technique? Bruce wrote: Yes, someone from India has a project to test LRU-K and MRU for large table scans and report back the results. He will implement whichever is best. Did this make it into 7.3? That person stopped working on it. It is still on the TODO list. 2. Gavin Sherry had worked up a patch so that temporary relations could be dropped automatically upon transaction commit. Did any of those patches it make it? I notice that whenever I create a temporary table in a transaction, my HD light blinks. Is this a forced fsync() causes by the fact that the SQL standard defines temporary relations as surviving across transactions? If so, I'd bet those of us who use transaction-local temporary tables could get few drops more of performance from an ON COMMIT drop patch w/o fsync. This has me confused. There was an exchange with Gavin Auguest 27/28 which resulted in a patch: http://archives.postgresql.org/pgsql-patches/2002-08/msg00475.php and my adding it to the patches list: http://archives.postgresql.org/pgsql-patches/2002-08/msg00502.php However, it was never applied. I don't see any discussion refuting the patch or any email removing it from the queue. The only thing I can think of is that somehow I didn't apply it. My only guess is that I said I was putting in the queue, but didn't. I am concerned if there are any other patches I missed. I see the cube patch being added to the queue 40 seconds later, and I know that was in there because I see the message removing it from the queue. I must have made a mistake on that one. What do we do now? The author clearly got it in before beta, but we are in beta now. I think we should apply it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Temp tables and LRU-K caching
Tom Lane wrote: Another thing I'd like to see in the near future is a configurable setting for the amount of memory space that can be used for temp-table buffers. The current setting is ridiculously small (64*8K IIRC), but there's not much point in increasing it until we also have a smarter management algorithm for the temp buffers. I've asked Neil to look at making the improved LRU-K buffer management algorithm apply to temp buffers as well as regular shared buffers. Speaking of sizing, I wonder if we should query about the amount of RAM in the machine either during initdb or later and size based on that. In other words, if we add a GUC variable that shows the amount of RAM, we could size things based on that value. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Temp tables and LRU-K caching
Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: Bruce wrote: Yes, someone from India has a project to test LRU-K and MRU for large table scans and report back the results. He will implement whichever is best. Did this make it into 7.3? No, we never heard back from that guy. It is still a live topic though. One of the Red Hat people was looking at it over the summer, and I think Neil Conway is experimenting with LRU-2 code right now. 2. Gavin Sherry had worked up a patch so that temporary relations could be dropped automatically upon transaction commit. Did any of those patches it make it? No they didn't; I forget whether there was any objection to his last try or it was just too late to get reviewed before feature freeze. I see it going into the patch queue. Here is the full thread: http://groups.google.com/groups?hl=enlr=ie=UTF-8threadm=200208272124.g7RLO1L20172%40candle.pha.pa.usrnum=1prev=/groups%3Fq%3Dcreate%2Btemp%2Btable%2Bon%2Bcommit%2Bgroup:comp.databases.postgresql.*%26hl%3Den%26lr%3D%26ie%3DUTF-8%26scoring%3Dd%26selm%3D200208272124.g7RLO1L20172%2540candle.pha.pa.us%26rnum%3D1 I don't see why it wasn't applied. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Temp tables and LRU-K caching
Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: Bruce wrote: Yes, someone from India has a project to test LRU-K and MRU for large table scans and report back the results. He will implement whichever is best. Did this make it into 7.3? No, we never heard back from that guy. It is still a live topic though. One of the Red Hat people was looking at it over the summer, and I think Neil Conway is experimenting with LRU-2 code right now. Okay. 2. Gavin Sherry had worked up a patch so that temporary relations could be dropped automatically upon transaction commit. Did any of those patches it make it? No they didn't; I forget whether there was any objection to his last try or it was just too late to get reviewed before feature freeze. Nuts. Oh well. Hopefully for 7.4... I notice that whenever I create a temporary table in a transaction, my HD light blinks. Is this a forced fsync() causes by the fact that the SQL standard defines temporary relations as surviving across transactions? A completely-in-memory temp table is not really practical in Postgres, for two reasons: one being that its schema information is stored in the definitely-not-temp system catalogs, and the other being that we request allocation of disk space for each page of the table, even if it's temp. I knew what I was asking made no sense two seconds after clicking 'Send'. Unfortunately, there's no undo on my mail client ;-). Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Temp tables and LRU-K caching
Bruce Momjian [EMAIL PROTECTED] writes: What do we do now? The author clearly got it in before beta, but we are in beta now. I think we should apply it. No. It's a feature addition and we are in feature freeze. Moreover, it's an unreviewed feature addition (I certainly never had time to look at the last version of the patch). Hold it for 7.4. 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] Temp tables and LRU-K caching
Tom Lane [EMAIL PROTECTED] writes: No, we never heard back from that guy. It is still a live topic though. One of the Red Hat people was looking at it over the summer, and I think Neil Conway is experimenting with LRU-2 code right now. Just to confirm that, I'm working on this, and hope to have something ready for public consumption soon. Tom was kind enough to send me some old code of his that implemented an LRU-2 replacement scheme, and I've used that as the guide for my new implementation. I just got a really basic version working yesterday -- I'll post a patch once I get something I'm satisfied with. I also still need to look into the local buffer management stuff suggested by Tom. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Memory Errors...
Well, it looks like it was already taken to the mat. ;) Greg On Thu, 2002-09-19 at 16:58, Joe Conway wrote: Nigel J. Andrews wrote: On Thu, 19 Sep 2002, Joe Conway wrote: I can give it a shot, but probably not until the weekend. I haven't really followed this thread closely, and don't know tcl very well, so it would help if someone can send me a minimal tcl function which triggers the problem. I can probably take a look at this tomorrow, already started by looking at the pltcl_SPI_exec routine. I think a quick glance at ...init_unknown() also shows a lack of tuptable freeing. OK -- let me know if you can't find the time and I'll jump back in to it. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Temp tables and LRU-K caching
OK, I will save this for 7.4. Sorry, Gavin. I missed this one for 7.3. --- pgman wrote: Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: Bruce wrote: Yes, someone from India has a project to test LRU-K and MRU for large table scans and report back the results. He will implement whichever is best. Did this make it into 7.3? No, we never heard back from that guy. It is still a live topic though. One of the Red Hat people was looking at it over the summer, and I think Neil Conway is experimenting with LRU-2 code right now. 2. Gavin Sherry had worked up a patch so that temporary relations could be dropped automatically upon transaction commit. Did any of those patches it make it? No they didn't; I forget whether there was any objection to his last try or it was just too late to get reviewed before feature freeze. I see it going into the patch queue. Here is the full thread: http://groups.google.com/groups?hl=enlr=ie=UTF-8threadm=200208272124.g7RLO1L20172%40candle.pha.pa.usrnum=1prev=/groups%3Fq%3Dcreate%2Btemp%2Btable%2Bon%2Bcommit%2Bgroup:comp.databases.postgresql.*%26hl%3Den%26lr%3D%26ie%3DUTF-8%26scoring%3Dd%26selm%3D200208272124.g7RLO1L20172%2540candle.pha.pa.us%26rnum%3D1 I don't see why it wasn't applied. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] NUMERIC's transcendental functions
Tom Lane wrote: I have noticed a change in behavior following the recent changes for casting of numeric constants. In prior releases, we got regression=# select log(10.1); log -- 1.00432137378264 (1 row) CVS tip gives regression=# select log(10.1); log -- 1.0043213738 (1 row) The reason for the change is that 10.1 used to be implicitly typed as float8, but now it's typed as numeric, so this command invokes log(numeric) instead of log(float8). And log(numeric)'s idea of adequate output precision seems low. Similar problems occur with sqrt(), exp(), ln(), pow(). I realize that there's a certain amount of cuteness in being able to calculate these functions to arbitrary precision, but this is a database not a replacement for bc(1). ISTM the numeric datatype is intended for exact calculations, and so transcendental functions (which inherently have inexact results) don't belong. So proposal #1 is to rip out the numeric versions of these functions. If you're too attached to them, proposal #2 is to force them to calculate at least 16 digits of output, so that we aren't losing any accuracy compared to prior behavior. Comments? One problem is, that division already has an inherently inexact result. Do you intend to rip that out too while at it? (Just kidding) Proposal #2.667 would be to have a GUC variable for the default precision. Jan regards, tom lane -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] NUMERIC's transcendental functions
Jan Wieck [EMAIL PROTECTED] writes: One problem is, that division already has an inherently inexact result. Do you intend to rip that out too while at it? (Just kidding) No, but that too is now delivering less precision than it used to: regression=# select 10.1/7.0; ?column? -- 1.4428571429 (1 row) versus 1.44285714285714 in prior releases. Proposal #2.667 would be to have a GUC variable for the default precision. Perhaps, but I'd be satisfied if the default precision were at least 16 digits. Again, the point is not to have any apparent regression from 7.2. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Default privileges for 7.3
Tom Lane writes: I am thinking that the better course might be to have newly created languages default to USAGE PUBLIC, at least for a release or two. That seems reasonable. Since everyone is supposed to use createlang, that's the effective default anyway. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Default privileges for 7.3
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: I am thinking that the better course might be to have newly created languages default to USAGE PUBLIC, at least for a release or two. That seems reasonable. Since everyone is supposed to use createlang, that's the effective default anyway. Good point. I shall make it happen. How do you feel about allowing functions to default to EXECUTE PUBLIC? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Temp tables and LRU-K caching
On Mon, 23 Sep 2002, Bruce Momjian wrote: OK, I will save this for 7.4. Sorry, Gavin. I missed this one for 7.3. Such is life. Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
BETA2 HOLD: was Re: [HACKERS] NUMERIC's transcendental functions
Seems we need to resolve this before beta2. --- Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: One problem is, that division already has an inherently inexact result. Do you intend to rip that out too while at it? (Just kidding) No, but that too is now delivering less precision than it used to: regression=# select 10.1/7.0; ?column? -- 1.4428571429 (1 row) versus 1.44285714285714 in prior releases. Proposal #2.667 would be to have a GUC variable for the default precision. Perhaps, but I'd be satisfied if the default precision were at least 16 digits. Again, the point is not to have any apparent regression from 7.2. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: BETA2 HOLD: was Re: [HACKERS] NUMERIC's transcendental functions
Bruce Momjian wrote: Seems we need to resolve this before beta2. I'd go with making the NUMERIC default precision 16 for v7.3, so we are backwards compatible on this release (except that it is now a predictable 16 digit precision instead of an hardware implementation dependent one). For v7.4 we can discuss that a while. Jan --- Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: One problem is, that division already has an inherently inexact result. Do you intend to rip that out too while at it? (Just kidding) No, but that too is now delivering less precision than it used to: regression=# select 10.1/7.0; ?column? -- 1.4428571429 (1 row) versus 1.44285714285714 in prior releases. Proposal #2.667 would be to have a GUC variable for the default precision. Perhaps, but I'd be satisfied if the default precision were at least 16 digits. Again, the point is not to have any apparent regression from 7.2. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: BETA2 HOLD: was Re: [HACKERS] NUMERIC's transcendental functions
Bruce Momjian [EMAIL PROTECTED] writes: Seems we need to resolve this before beta2. Not really. It's just a bug; we have others. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: BETA2 HOLD: was Re: [HACKERS] NUMERIC's transcendental functions
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Seems we need to resolve this before beta2. Not really. It's just a bug; we have others. Oh, it doesn't effect initdb? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL 7.3: help on new CREATE TYPE
Illustra did a very nice job with composite types which correspond to these record types. The composite types were able to be used as a column type as jerome describes. The subcolumns were accessed with dots. This gave us schema.table.column.attribute where of course attribute could itself be a composite type Well, ok, it had some drawbacks, too. If we ever are serious about implementing this I would help with discussing and/or writing the specs. I can put together a nice spec. When I get a break on my book project, I might just write it up anyway. elein [EMAIL PROTECTED] PS: Everyone please forgive me for reading list mail late and out of order... I am in awe of anyone keeping up. On Tuesday 17 September 2002 07:22, Tom Lane wrote: Jerome Chochon [EMAIL PROTECTED] writes: Can I use this new type like other user-type ? CREATE TABLE person (his_name VARCHAR, his_adress adress); ...where adress is CREATE TYPE adress AS (number int, street text, country VARCHAR); Not at the moment, though that might be an interesting direction to pursue in future releases. At present, the only thing such a type is useful for is to define the argument or result type of a function that takes or returns records. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Web site
Hi all, It occurs to me that opening web page on www.postgresql.org, asking the user to select the mirror, is rather unprofessional. I am sure this has been discussed before but I thought I would bring it up again anyway. So, why not just redirect people to one of the mirrors listed? This could be done based on IP (yes it is inaccurate but it is close enough and has the same net effect: pushing people off the main web server) or it could be done by simply redirecting to a random mirror. From a quick look, there is nothing of any real size on the site (excluding developer.postgresql.org, which is not the issue) to warrant people wanting to access a geographically local server anyway. (Unlike the case of FTP, for which the list of mirrors is very useful). Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Hosed PostGreSQL Installation
Just following up on Tom Lane's email - A couple of things that I hadn't mentioned: After bringing up the machine, the first thing I did before mucking about with PostGreSQL was to tarball $PGDATA so that I'd have a second chance if I messed up. I then ran pg_resetlog -f the first time, as Tom surmised, with the unwanted results. That done, I sent out the email, and followed Tom's instructions (yay backups!) and did it properly. On Sat, Sep 21, 2002 at 11:13:44AM -0400, Tom Lane wrote: Pete St. Onge [EMAIL PROTECTED] writes: That should not have been a catastrophic mistake in any version = 7.1. I suspect you had disk problems or other problems. We did, but these were on a different disk according to the logs, AFAIK. These numbers are suspiciously small for an installation that's been in production awhile. I suspect you have not told us the whole story; in particular I suspect you already tried pg_resetxlog -f, which was probably not a good idea. *raises hand* Yep. Here's the contents of the pg_xlog directory. PGSQL has only been used here for approximately 4 months of fairly light use, so perhaps the numbers aren't as strange as they could be (this is from the backup). -rw---1 postgres postgres 16777216 Sep 19 22:09 0002007E Yeah, your xlog positions should be a great deal higher than they are, if segment 2/7E was previously in use. It is likely that you can recover (with some uncertainty about integrity of recent transactions) if you proceed as follows: 1. Get contrib/pg_resetxlog/pg_resetxlog.c from the 7.2.2 release ... [Chomp] The compile worked without a hitch after doing ./configure in the top-level directory. I just downloaded the src for both trees, made the changes manually, copied the file into the 7.1.3 tree and compiled it there. 2. Run the hacked-up pg_resetxlog like this: pg_resetxlog -l 2 127 -x 10 $PGDATA (the -l position is next beyond what we see in pg_xlog, the 1-billion XID is just a guess at something past where you were. Actually, can you give us the size of pg_log, ie, $PGDATA/global/1269? That would allow computing a correct next-XID to use. Figure 4 XIDs per byte, thus if pg_log is 1 million bytes you need -x at least 4 million.) -rw---1 postgres postgres 11870208 Sep 19 17:00 1269 This gives a min WAL starting location of 47480832. I used 4750. 3. The postmaster should start now. I had to use pg_resetxlog's force option, but yeah, it worked like you said it would. 4. *Immediately* attempt to do a pg_dumpall. Do not pass GO, do not collect $200, do not let in any interactive clients until you've done it. (I'd suggest tweaking pg_hba.conf to disable all logins but your own.) I did not pass go, I did not collect $200. I *did* do a pg_dumpall right there and then, and was able to dump everything I needed. One of the projects uses large objects - image files and html files (don't ask, I've already tried to dissuade the Powers-That-Be) - and these didn't come out. However, since this stuff is entered via script, the project leader was fine with re-running the scripts tomorrow. 5. If pg_dumpall succeeds and produces sane-looking output, then you've survived. initdb, reload the dump file, re-open for business, go have a beer. (Recommended: install 7.2.2 and reload into that, not 7.1.*.) You will probably still need to check for partially-applied recent transactions, but for the most part you should be OK. rpm -Uvh'ed the 7.2.2 RPMs, initdb'd and reloaded data into the new installation. Pretty painless. I've just sent out an email to folks here to let them know the situation, and we should know in the next day or so what is up. 6. If pg_dumpall fails then let us know what the symptoms are, and we'll see if we can figure out a workaround for whatever the corruption is. I've kept the tarball with the corrupted data. I'll hold onto it for a bit, in case, but will likely expunge it in the next week or so. If this can have a use for the project (whatever it may be), let me know and I can burn it to DVD. Of course, without your help, Tom, there would be a lot of Very Unhappy People here, me only being one of them. Many thanks for your help and advice! Cheers, Pete -- Pete St. Onge Research Associate, Computational Biologist, UNIX Admin Banting and Best Institute of Medical Research Program in Bioinformatics and Proteomics University of Toronto http://www.utoronto.ca/emililab/ ---(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] Web site
On Tue, 24 Sep 2002, Gavin Sherry wrote: Hi all, It occurs to me that opening web page on www.postgresql.org, asking the user to select the mirror, is rather unprofessional. I am sure this has been discussed before but I thought I would bring it up again anyway. Already being worked on ... So, why not just redirect people to one of the mirrors listed? This could be done based on IP (yes it is inaccurate but it is close enough and has the same net effect: pushing people off the main web server) or it could be done by simply redirecting to a random mirror. Have tried both in the past with disastrous results ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Web site
could be done based on IP (yes it is inaccurate but it is close enough and has the same net effect: pushing people off the main web server) or it could be done by simply redirecting to a random mirror. Have tried both in the past with disastrous results ... What method will be employed instead? Gavin ---(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] Postgresql Automatic vacuum
Just an FYI - this kind of thing would be a *great* feature addition to the generic PostgresSQL release. We at Lyris often hear that postgressql is very slow, and the files are getting larger and then wow! it's so much faster now that we're regularly vacuuming! after we let them know about this need (the RPM install of PostgresSQL is so easy that most people don't read any docs). Automatic maintenance of database tables is a Good Thing (tm) and would make more people we introduce to pgsql favorably disposed toward it. -john I have written a small daemon that can automatically vacuum PostgreSQL database, depending upon activity per table. It sits on top of postgres statistics collector. The postgres installation should have per row statistics collection enabled. Features are, * Vacuuming based on activity on the table * Per table vacuum. So only heavily updated tables are vacuumed. * multiple databases supported * Performs 'vacuum analyze' only, so it will not block the database The project location is http://gborg.postgresql.org/project/pgavd/projdisplay.php Let me know for bugs/improvements and comments.. I am sure real world postgres installations has some sort of scripts doing similar thing. This is an attempt to provide a generic interface to periodic vacuum. Bye Shridhar -- The Abrams' Principle:The shortest distance between two points is off the wall. ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] subselect bug (was Re: [GENERAL] DBLink: interesting issue)
Joe Conway wrote: Oleg Lebedev wrote: Ok, here are all the files. This dblink thread on GENERAL led me to a bug in the planner subselect code. Here is an example query that triggers it (independent of dblink and/or table functions): replica=# create table foo(f1 int); CREATE TABLE replica=# SELECT * FROM foo t WHERE NOT EXISTS (SELECT remoteid FROM (SELECT f1 as remoteid FROM foo WHERE f1 = t.f1) AS t1); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. It doesn't matter how foo is defined. I'm just starting to dig in to this, but was hoping for any thoughts or guidance I can get. Thanks, Joe p.s. Below is a backtrace: #3 0x081797a1 in ExceptionalCondition () at assert.c:46 #4 0x0810e102 in replace_var (var=0x82f73a8) at subselect.c:81 #5 0x0811293c in expression_tree_mutator (node=0x82f7438, mutator=0x810e96c replace_correlation_vars_mutator, context=0x0) at clauses.c:2314 #6 0x0810e9a5 in replace_correlation_vars_mutator (node=0x82f7438, context=0x0) at subselect.c:540 #7 0x08112718 in expression_tree_mutator (node=0x82f7454, mutator=0x810e96c replace_correlation_vars_mutator, context=0x0) at clauses.c:2179 #8 0x0810e9a5 in replace_correlation_vars_mutator (node=0x82f7454, context=0x0) at subselect.c:540 #9 0x0811293c in expression_tree_mutator (node=0x82f7480, mutator=0x810e96c replace_correlation_vars_mutator, context=0x0) at clauses.c:2314 #10 0x0810e9a5 in replace_correlation_vars_mutator (node=0x82f7480, context=0x0) at subselect.c:540 #11 0x0810e968 in SS_replace_correlation_vars (expr=0x82f7480) at subselect.c:525 #12 0x0810cef5 in preprocess_expression (parse=0x82f6830, expr=0x82f7064, kind=1) at planner.c:725 #13 0x0810cf7e in preprocess_qual_conditions (parse=0x82f6830, jtnode=0x82f6d70) at planner.c:775 #14 0x0810c75c in subquery_planner (parse=0x82f6830, tuple_fraction=1) at planner.c:168 #15 0x0810e260 in make_subplan (slink=0x82f6698) at subselect.c:185 #16 0x0811293c in expression_tree_mutator (node=0x82f6780, mutator=0x810e9bc process_sublinks_mutator, context=0x0) at clauses.c:2314 #17 0x0810ea35 in process_sublinks_mutator (node=0x82f6780, context=0x0) at subselect.c:586 #18 0x08112718 in expression_tree_mutator (node=0x82f6754, mutator=0x810e9bc process_sublinks_mutator, context=0x0) at clauses.c:2179 #19 0x0810ea35 in process_sublinks_mutator (node=0x82f6754, context=0x0) at subselect.c:586 #20 0x0811293c in expression_tree_mutator (node=0x82f679c, mutator=0x810e9bc process_sublinks_mutator, context=0x0) at clauses.c:2314 #21 0x0810ea35 in process_sublinks_mutator (node=0x82f679c, context=0x0) at subselect.c:586 #22 0x0810e9b8 in SS_process_sublinks (expr=0x82f679c) at subselect.c:553 #23 0x0810cede in preprocess_expression (parse=0x82f46d4, expr=0x82fc164, kind=1) at planner.c:721 #24 0x0810cf7e in preprocess_qual_conditions (parse=0x82f46d4, jtnode=0x82fc36c) at planner.c:775 #25 0x0810c75c in subquery_planner (parse=0x82f46d4, tuple_fraction=-1) at planner.c:168 #26 0x0810c68c in planner (parse=0x82f46d4) at planner.c:96 ---(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] Postgresql Automatic vacuum
On 23 Sep 2002 at 13:28, Matthew T. O'Connor wrote: On Monday 23 September 2002 09:43 am, Shridhar Daithankar wrote: Hello All, I have written a small daemon that can automatically vacuum PostgreSQL database, depending upon activity per table. Hello Shridhar, sorry I didn't respond to the email you sent me a while back. Anyway, I saw this post, and just started taking a look a it. I wasn't thinking of doing this as a totally separate executable / code base, but perhaps that has advantages I need to think more. A couple of quick questions, you are using C++, but all postgres source code is in C, do you want this to eventually be included as part of the postgres distribution? If so, I think that C might be a better choice. Well, I wrote it in C++ because I like it. I have lost habit of writing pure C code. Nothing else. As far as getting into base postgresql distro. I don't mind it rewriting but I have some reservations. 1) As it is postgresql source code is huge. Adding functions to it which directly taps into it's nervous system e.g. cache, would take far more time to perfect in all conditions. My application as it is is an external client app. It enjoys all the isolation provided by postgresql. Besides this is a low priority functionality at runtime, unlike real time replication. It would rarely matter it vacuum is triggered after 6 seconds instead of configuerd 5 seconds, for example. Less code, less bugs is my thinking. I wanted this functionality out fast. I didn't want to invest in learning postgresql source code because I didn't have time. So I wrote a separate app. Besides it would run on all previous postgresql versions which supports statistics collection. That's a huge plus if you ask me. 2) Consider this. No other database offers built in tool to clean the things. Is it that nobody needs it? No everybody needs it. And then you end up cleaning database by taking it down. If people take for granted that postgresql does not need manual cleaning, by deploying apps. like pgavd, vacuum will be a big feature of postgres. Clean the database without taking it down.. I will play with it more and give you some more feedback. Awaiting that. I am Cc'ing this to Hackers because I am sure some people might have same doubts. Bye Shridhar -- intoxicated, adj.: When you feel sophisticated without being able to pronounce it. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
