[HACKERS] Re: Outstanding patches
Tom Lane wrote: But it's not really tracking the variable; with Ian's proposed implementation, after create table foo(bar int4); create function fooey(foo.bar%type) ...; drop table foo; create table foo(bar int8); you would still have fooey declared as taking int4 not int8, because the type meant by %type is resolved and frozen immediately upon being seen. Ok, this is a more general point: in Oracle (which, as Ian points out, uses this feature extensively) if you recreate table foo, function fooey is tagged as 'dirty' and recompiled on the spot next time is used. This is also true for VIEWs and other objects, so you don't have the problem we have when a view breaks because you've updated the underlining table. -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 It is more complicated than you think -- The Eighth Networking Truth from RFC 1925 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] incorrect query result using complex structures (views?)
You're welcome ;-) Marvellous, it works! How much time did it take for you to find what have to be changed? Thank you very much. Regards, Zoltan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: Outstanding patches
Alessio Bragadini [EMAIL PROTECTED] writes: Tom Lane wrote: But it's not really tracking the variable; with Ian's proposed implementation, after create table foo(bar int4); create function fooey(foo.bar%type) ...; drop table foo; create table foo(bar int8); you would still have fooey declared as taking int4 not int8, because the type meant by %type is resolved and frozen immediately upon being seen. Ok, this is a more general point: in Oracle (which, as Ian points out, uses this feature extensively) if you recreate table foo, function fooey is tagged as 'dirty' and recompiled on the spot next time is used. This is also true for VIEWs and other objects, so you don't have the problem we have when a view breaks because you've updated the underlining table. Indeed, and we have plans to do something similar sometime soon. My real objection to this proposed feature is that there is no way to handle the update as a local matter within the function, because changing the function's input datatypes actually means it's a different function. This creates all sorts of problems at both the definitional and implementation levels... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Outstanding patches
Philip Warner [EMAIL PROTECTED] writes: Is anybody planning to fix the problem with ALTER TABLE ADD CONSTRAINT... in which the constraints are not applied to child tables? AFAIK no one is looking at it presently (although Stephan Szabo has probably thought about it). If you want to tackle it, step right up, but coordinate with Stephan. I was just in the vicinity of ALTER TABLE, and noted that that routine didn't have the same loop-over-children superstructure that most of the other ALTER code does. Should be a relatively simple matter to graft that logic onto it, unless there are semantic funnies that come up with propagating the new constraint. 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] Re: New Linux xfs/reiser file systems
[EMAIL PROTECTED] (Trond Eivind Glomsrød) writes: [EMAIL PROTECTED] (Trond Eivind Glomsrød) writes: Ken Hirsch [EMAIL PROTECTED] writes: I don't have a machine with XFS installed and it will be at least a week before I could get around to a build. Any volunteers? I think I could do that... any useful benchmarks to run? In lack of bigger benchmarks, I tried postgresql 7.1 on a Red Hat Linux 7.1 system with the SGI XFS modifications. The differences were very small. And here is the one for ReiserFS - same kernel, but recompiled to turn off debugging DB connection startup: 0.01user 0inputs+0outputs (208major+17minor)pagefaults 0swaps : 8192 INSERTs INTO SIMPLE (1 xact): real0m5.857s : user0m1.200s : sys 0m0.510s : 8192 INSERTs INTO SIMPLE (8192 xacts): real0m37.036s : user0m1.430s : sys 0m0.580s : Create INDEX on SIMPLE: 0.00user 0inputs+0outputs (209major+17minor)pagefaults 0swaps : 8192 INSERTs INTO SIMPLE with INDEX (1 xact): real0m7.017s : user0m1.180s : sys 0m0.420s : 8192 random INDEX scans on SIMPLE (1 xact): real0m12.761s : user0m1.670s : sys 0m0.820s : ORDER BY SIMPLE: 0.04user 0inputs+0outputs (214major+69minor)pagefaults 0swaps : When compared to the earlier ones (including XFS), you'll note that ReiserFS performance is rather poor in some of the tests - it takes 37 vs. 13 seconds for 8192 inserts, when the inserts are different transactions. -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] I still cannot force pg_dump to disable triggers
Date: Fri, 16 Mar 2001 22:58:42 +1100 From: Philip Warner [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: I cannot force pg_dump to disable triggers At 12:49 16/03/01 +0100, kovacsz wrote: I downloaded the current snapshot and realized that you changed the dumping behaviour about disabling and enabling triggers. Unfortunately I couldn't find the appropriate switches to get the same output before you made this change (I mean beta4). Could you please help? In beta4 I used -xacnDO for the desired result. Now I never get any lines in the output which contains -- Enable triggers or -- Disable triggers. I just tried: pg_dump -xacnDO pjw and got the enable/disable stuff. Can you check that you did a 'make distclean' in pg_dump before you ran the dump? The latest rule is that it only does an enable/disable if the dump (or restore) is data-only. Well, I stopped trying it in March but I'm in a need of changing to 7.1 (I should use Tom's patch). I did a 'make distclean' but no difference: there are no lines switching the triggers on/off. I'm using PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66. TIA, Zoltan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PostgreSQL 7.1 (current release) - frequent errno:55 (buffer space error)
Hello, I am running PostgreSQL on a FreeBSD machine with 1 Gig of ram, and dual P3-733mhz CPUs. This server also runs Apache and is a production/web server. I frequently run into the errno:55 on my site, if I simply click refresh it goes away. Anyone have any ideas what is causing this, or how to fix it ? my shared_buffers are set to 4000, and max_connections to 300. I run postmaster with this command line: ./postmaster -Si -o -F -D /usr/local/pgsql/data/ Any help would be MUCH appreciated. Thanks, Keith Bussey [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])
[HACKERS] Re: I still cannot force pg_dump to disable triggers
At 17:23 9/05/01 +0200, Kovacs Zoltan wrote: Well, I stopped trying it in March but I'm in a need of changing to 7.1 (I should use Tom's patch). I did a 'make distclean' but no difference: there are no lines switching the triggers on/off. I'm using PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66. It's because the data-only dump is (incorrectly) dumping the COMMENTs, and treats SEQUENCE SET entries as schema entries, not data entries. It will be fixed soon. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
AW: [HACKERS] Re: Outstanding patches
We need to discuss whether we like the %TYPE feature proposed by Ian OK, one idea is to throw a elog(NOTICE) when they use this feature, stating that it will not track column changes. Another option is to just forget about the feature entirely. Do we have people who like this feature? Speak up now. If not, we will drop it. I say drop it. (Never used it in Informix eighter, even though we have some heavy stored procedure writers). In Informix the varlen is also not part of the signature, it only states the longest accepted value. Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
AW: [HACKERS] NOCREATETABLE patch (was: Re: Please, help!(about Postgres))
The connect group would be granted these System Privileges: If we keep it like others (e.g. Informix) this System Privilege would be called resource. I like this name better, because it more describes the detailed priviledges. CREATE AGGREGATE privilege CREATE INDEX privilege CREATE FUNCTION privilege CREATE OPERATOR privilege CREATE RULE privilege CREATE SESSION privilege CREATE SYNONYM privilege CREATE TABLE privilege CREATE TRIGGER privilege CREATE TYPE privilege CREATE VIEW privilege The connect group would only have the priviledge to connect to the db [and create temp tables ?] and rights they where granted, or that were granted to public. They would not be allowed to create anything. Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
AW: [HACKERS] MULTIBYTE and SQL_ASCII (was Re: [JDBC] Re: A bug with pgsql 7.1/jdbc and non-ascii (8-bit) chars?)
Tom's suggestion does not sound reasonable to me. If PostgreSQL is not built with MULTIBYTE, then it means there would be no such idea encoding in PostgreSQL becuase there is no program to handle encodings. Thus it would be meaningless to assign an encoding to a database if MULTIBYTE is not enabled. Why? Without the MULTIBYTE code, the backend cannot perform character set translations --- but it's perfectly possible that someone might not need translations. A lot of European sites are probably very happy as long as the server gives them back the same 8-bit characters they stored. Yes, that is what we do (German language). Encoding is Latin1. Would it not be reasonable to return the machine LC_CTYPE in the non multibyte case ? Andreas ---(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] NOCREATETABLE patch (was: Re: Please, help!(about Postgres))
On Mon, May 07, 2001 at 02:48:11PM -0400, Bruce Momjian wrote: Can someone remind me what we are going to do with this? This patch add to 7.0.2 code NOCREATETABLE and NOLOCKTABLE feature: It's my old patch, it's usable and some people use it for 7.0.x. But it's really temporary solution and it was 1 day in official CVS :-) We remove it after discussion with Peter E. More correct will implement better privilege system. A privilege system is *very* important for real multiuser and sophisticated systems. For example if you compare PostgreSQL with Oracle, the PostgreSQL is really not winner in this part. Peter has some idea about it and Jan sent something about it too, but I not sure if somebody works on this and plannig it for some next release (or...? -- will good if I not right:-) Karel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Outstanding patches
At 09:36 9/05/01 -0400, Bruce Momjian wrote: Is anybody planning to fix the problem with ALTER TABLE ADD CONSTRAINT... in which the constraints are not applied to child tables? I thought we had not figured out how to inherit those, or at least certain constraints like UNIQUE. We do have on TODO: * Allow inherited tables to inherit index, UNIQUE constraint, and primary key [inheritance] aaa=# create table t1(f1 integer check(f10),primary key (f1)); aaa=# create table t1c() inherits (t1); aaa=# \d t1c Table t1c Attribute | Type | Modifier ---+-+-- f1| integer | not null Constraint: (f1 0) So PK is not inherited, but CHECK (and implied NOT NULL) seem to be. Whereas, aaa=# create table t1(f1 integer); aaa=# create table t1c() inherits (t1); aaa=# alter table t1 add constraint aaa check(f10); aaa=# \d t1c Table t1c Attribute | Type | Modifier ---+-+-- f1| integer | ie. The CHECK constraints inherit only at the time of table creation. I think this is a bug in ALTER TABLE for CHECK constraints. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Coping with huge deferred-trigger lists
I had a thought just now about how to deal with the TODO item about coping with deferred trigger lists that are so long as to overrun main memory. This might be a bit harebrained, but I offer it for consideration: What we need to do, at the end of a transaction in which deferred triggers were fired, is to find each tuple that was inserted or updated in the current transaction in each table that has such triggers. Well, we know where those tuples are: to a first approximation, they're all near the end of the table. Perhaps instead of storing each and every trigger-related tuple in memory, we only need to store one value per affected table: the lowest CTID of any tuple that we need to revisit for deferred-trigger purposes. At the end of the transaction, scan forward from that point to the end of the table, looking for tuples that were inserted by the current xact. Process each one using the table's list of deferred triggers. Instead of a list of all tuples subject to deferred triggers, we now need only a list of all tables subject to deferred triggers, which should pose no problems for memory consumption. It might be objected that this means more disk activity --- but in an xact that hasn't inserted very many tuples, most likely the disk blocks containing 'em are still in memory and won't need a physical re-read. Once we get to inserting so many tuples that that's not true, this approach should require less disk activity overall than the previous idea of writing (and re-reading) a separate disk file for the tuple list. I am not sure exactly what the triggered data change violation test does or is good for, but if we want to keep it, I *think* that in these terms we'd just need to signal error if we come across a tuple that was both inserted and deleted by the current xact. I'm a bit fuzzy on this though. An interesting property of this approach is that if the set of triggers for the table changes during the xact (which could only happen if this same xact created or deleted triggers; no other xact can, since changing triggers requires an exclusive lock on the table), the set of triggers applied to a tuple is the set that exists at the end of the xact, not the set that existed when the tuple was modified. Offhand I think this is a good change. Comments? 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] Re: Outstanding patches
Bruce Momjian [EMAIL PROTECTED] writes: Does this relate to allowing functions to be recreated with the same OID as the original function? I think we need that badly for 7.2. No, I don't think that's very related; that's a simple matter of implementing an ALTER FUNCTION command. The other thing will require figuring out how to do dependency tracking. 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] Re: Outstanding patches
Bruce Momjian [EMAIL PROTECTED] writes: No, I don't think that's very related; that's a simple matter of implementing an ALTER FUNCTION command. The other thing will require figuring out how to do dependency tracking. Got it. Let me ask, if they change the column type, would they use ALTER FUNCTION to then update to match the new column type. As I understand it, the problem is that this does not happen automatically, right? My vision of ALTER FUNCTION is that it would let you change the function body, and perhaps also the function language and attributes (isCachable, isStrict). It would NOT allow you to change the function's parameter types or return type, because that potentially breaks things that depend on the function. To do that, you should have to create a new function. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: New Linux xfs/reiser file systems
Makes it more fun :) Kinda like a lottery ticket: - reliable (cherry) - fast (cherry) - resource hog (lemon) -- Rod Taylor BarChord Entertainment Inc. - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Martín Marqués [EMAIL PROTECTED] Cc: Trond Eivind Glomsrød [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 1:24 PM Subject: Re: [HACKERS] Re: New Linux xfs/reiser file systems I'm concearned about this because we are going to switch our fist server to a Journaling FS (on Linux). Searching and asking I found out that for our short term work we need ReiserFS (it's for a proxy server). Put the interesting thing was that for large (very large) files, everybody recomends XFS. The drawback of XFS is that it's very, very slw when deleting files. Why do all these file systems seem to have one major negative? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Shared library versions
On Wed, 9 May 2001, Peter Eisentraut wrote: We did not bump the shared library versions before the 7.1 release. Maybe we should do this before 7.1.2 goes out. Ummm ... unless there are any changes that would require someone to recompile their apps between v7.1.1 and v7.1.2, I don't think so ... they we are just creating potential problems for those upgrading from v7.1/v7.1.1 to the latest stable, where there are no changes ... If we were to do it, it would have to be on the v7.x, not v7.x.y ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Shared library versions
Bruce Momjian [EMAIL PROTECTED] writes: We did not bump the shared library versions before the 7.1 release. Maybe we should do this before 7.1.2 goes out. I thought I did that long ago for 7.1, or I should have anyway. I don't see the commits either. Seems we can't do it in a minor release. I agree, too late now. Isn't there a checklist someplace of things to do while preparing a release? Check shared library version numbers should be on 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] Shared library versions
On Wed, 9 May 2001, Bruce Momjian wrote: We did not bump the shared library versions before the 7.1 release. Maybe we should do this before 7.1.2 goes out. I thought I did that long ago for 7.1, or I should have anyway. I don't see the commits either. Seems we can't do it in a minor release. Will have to wait for 7.2, but since there really wasn't much API change in 7.1, I think we are OK. Not sure if we should update them if there are no API changes, or were there? IMHO, it should only be changed if there are incompatibilities between releases ... we modify the API, mainly ... anything more then that, and we're making ppl recompile to pull in libraries that only unlying code has changed, but not the API ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Shared library versions
Peter Eisentraut [EMAIL PROTECTED] writes: The Hermit Hacker writes: IMHO, it should only be changed if there are incompatibilities between releases ... we modify the API, mainly ... anything more then that, and we're making ppl recompile to pull in libraries that only unlying code has changed, but not the API ... ISTM that you should read up on shared library versioning. I second that... if new functionality is added, bump the minor. If functionality changes or is removed, bump the major. -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Shared library versions
On Wed, 9 May 2001, Peter Eisentraut wrote: I'm talking about the minor number. The only thing that effects is that executables would pick up the new version if they have the old one in the path as well, no potential problems. Okay, but, what does that buy you? One overwrites the old library, the other creates one that will over-ride the old library ... either way, you are using the new library, no? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
