[HACKERS] hub.org out of disk space
/home/projects/pgsql partition at hub.org is down to zero free space... 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] pg_depend
On Fri, 20 Jul 2001, Hiroshi Inoue wrote: > Bill Studenmund wrote: > > > > "How does postgres know that the new table a is sufficiently like the old > > table that it should be used?" > > > > By making the reattachment automatic, you are saying that once we make an > > object of a given name and make objects depend on it, we can never have > > another object of the same name but different. Because PG is going to try > > to re-attach the dependants for you. > > > > That's different than current behavior, and strikes me as the system being > > overly helpful (a class of behavior I personally find very annoying). > > > > Please understand I like the idea of being ABLE to do this reattachment. I > > can see a lot of places where it would be VERY useful. > > It doesn't seem preferable that the default(unadorned) DROP > allows reattachement after the DROP. The default(unadorned) DROP > should be the same as DROP RESTRICT(or CASCADE because the current > behabior is halfway CASCADE?). How about adding another keyword > to allow reattachment after the DROP ? Hmmm... My preference is for the subsequent CREATE to indicate if reattach should happen or not. But I'm not sure if that would leave dangling depend entries around. > All depende(a?)nt objects must be re-complied after the > reattachment and the re-compilation would fail if the new table > isn't sufficiently like the old one. > > Anyway my opinion seems in a minority as usual. Only partly. I think everyone likes the idea of being able to reattach later, an idea you came up with. :-) Take care, Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Could you use CTID instead of OID? > > > I am using both. > > TIDs for fast access and OIDs for identification. > > Unfortunately TIDs are transient and they aren't > > that reliable as for identification. > > Hmm ... within a transaction I think they'd be reliable enough, > but for long-term ID I agree they're not. What behavior do you > need exactly;do you need to be able to find the updated version > of a row you originally inserted? What I was about to do in the case e.g. UPDATE is the following. 1) UPDATE .. set .. where CTID = saved_ctid and OID = saved_oid; If one row was updated it's OK and return. 2) Otherwise something has changed and the update operation would fail. However the driver has to try to find the updated version of the row in case of keyset-driven cursors by the query SELECT CTID, .. from .. where CTID = currtid2(table_name, saved_ctid) and OID = saved_oid; If a row was found, the content of cursors' buffer is replaced and return. 3) If no row was found, the row may be deleted. Or we could issue another query SELECT CTID, .. from .. where OID = saved_oid; though the performance is doubtful. The OIDs are (mainly) to prevent updating the wrong records. > What would it take to use a > user-defined primary key instead of OID? Yes it could be. In fact M$ provides the ODBC cursor library in that way and we have used it(indirectly) for a long time. It's the reason why ODBC users don't complain about the non-existence of updatable cursors that often. Must I repeat the implementation ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
On Fri, Jul 20, 2001 at 08:45:05AM +0900, Hiroshi Inoue wrote: > > It doesn't seem preferable that the default(unadorned) DROP > allows reattachement after the DROP. The default(unadorned) DROP > should be the same as DROP RESTRICT(or CASCADE because the current > behabior is halfway CASCADE?). How about adding another keyword > to allow reattachment after the DROP ? > All depende(a?)nt objects must be re-complied after the > reattachment and the re-compilation would fail if the new table > isn't sufficiently like the old one. > > Anyway my opinion seems in a minority as usual. > How about making that functionality happen with ALTER REPLACE as Tom suggested? If I'm wanting to change an underlying table, how likely is it that I don't have the replacement ready right now? So, instead of: DROP WITH INTENT TO REPLACE CREATE it's just: ALTER REPLACE All nice and transactional: if the attempt to reattach one of the subordinate objects fails, you roll back to the old one. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_depend
Bill Studenmund wrote: > > On Thu, 19 Jul 2001, Hiroshi Inoue wrote: > > > > This step I disagree with. Well, I disagree with the automated aspect > of > > > the update. How does postgres know that the new table a is sufficiently > > > like the old table that it should be used? A way the DBA could say, "yeah, > > > restablish that," would be fine. > > > > > > > You could DROP a table with CASCADE or RESTRICT keyword if > > you hate the behavior. > > You didn't answer the question. :-) > > "How does postgres know that the new table a is sufficiently like the old > table that it should be used?" > > By making the reattachment automatic, you are saying that once we make an > object of a given name and make objects depend on it, we can never have > another object of the same name but different. Because PG is going to try > to re-attach the dependants for you. > > That's different than current behavior, and strikes me as the system being > overly helpful (a class of behavior I personally find very annoying). > > Please understand I like the idea of being ABLE to do this reattachment. I > can see a lot of places where it would be VERY useful. It doesn't seem preferable that the default(unadorned) DROP allows reattachement after the DROP. The default(unadorned) DROP should be the same as DROP RESTRICT(or CASCADE because the current behabior is halfway CASCADE?). How about adding another keyword to allow reattachment after the DROP ? All depende(a?)nt objects must be re-complied after the reattachment and the re-compilation would fail if the new table isn't sufficiently like the old one. Anyway my opinion seems in a minority as usual. regards, Hiroshi Inoue ---(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] Turning off revision tracking so vacuum never needs to be run
Mike Cianflone <[EMAIL PROTECTED]> writes: > is it possible to completely turn off the revision tracking feature so that > vacuum does not need to be run at all? No. Of course, if you never update or delete any rows, you don't need VACUUM ... but I suspect that's not what you had in mind. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_depend
On Thu, 19 Jul 2001, Hiroshi Inoue wrote: > > This step I disagree with. Well, I disagree with the automated aspect of > > the update. How does postgres know that the new table a is sufficiently > > like the old table that it should be used? A way the DBA could say, "yeah, > > restablish that," would be fine. > > > > You could DROP a table with CASCADE or RESTRICT keyword if > you hate the behavior. You didn't answer the question. :-) "How does postgres know that the new table a is sufficiently like the old table that it should be used?" By making the reattachment automatic, you are saying that once we make an object of a given name and make objects depend on it, we can never have another object of the same name but different. Because PG is going to try to re-attach the dependants for you. That's different than current behavior, and strikes me as the system being overly helpful (a class of behavior I personally find very annoying). Please understand I like the idea of being ABLE to do this reattachment. I can see a lot of places where it would be VERY useful. My vote though is to just make reattachment a seperate step or something you flag, like in the CREATE TABLE, say attach me to everything wanting a table of this name. Make it something you have to indicate you want. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Philip Warner <[EMAIL PROTECTED]> writes:
> At 00:00 19/07/01 -0400, Tom Lane wrote:
>> INSERT INTO foo ... RETURNING x,y,z,...
> That would have been me; at the time we also talked about
> UPDATE...RETURNING and Jan proposed allowing UPDATE...RETURNING
> {[Old.|New.]Attr,...}
Hm. I'm less excited about UPDATE ... RETURNING since it would seem
that SELECT FOR UPDATE followed by UPDATE would get that job done
in a somewhat-less-nonstandard manner. But anyway ---
Thinking about this some more, it seems that it's straightforward enough
for a plain INSERT, but I don't understand what's supposed to happen if
the INSERT is replaced by an ON INSERT DO INSTEAD rule. The rule might
not contain an INSERT at all, or it might contain several INSERTs into
various tables with no simple relationship to the original. What then?
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])
[HACKERS] RELAX! - or more to the point, how do I temporarily relax a trigger/constraint?
I need to programatically relax a constraint during db syncronization. I tried setting tgenabled to false in the pg_trigger table, but it didn't seem to make a difference. Thx, Howie ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Turning off revision tracking so vacuum never needs to be run
I've followed the threads of needing to run vacuum nightly, and even got an answer to a slow updating database regarding needing to vacuum. But I haven't see the question asked: is it possible to completely turn off the revision tracking feature so that vacuum does not need to be run at all? Thanks for any pointers, Mike ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [PATCHES] Re: [HACKERS] [PATCH] Re: Setuid functions
You are right; I have forgotten to create a "trusted" language. Mark Tom Lane wrote: > > Mark Volpe <[EMAIL PROTECTED]> writes: > > ERROR: Only users with Postgres superuser privilege are permitted to create a > > function in the 'plpgsql' language. > > > D'oh! So, if this is the case, then the last patch should be fine after all. > > No, evidently you broke something, or your plpgsql is installed wrong. > > regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Re: [HACKERS] [PATCH] Re: Setuid functions
Mark Volpe <[EMAIL PROTECTED]> writes: > ERROR: Only users with Postgres superuser privilege are permitted to create a > function in the 'plpgsql' language. > D'oh! So, if this is the case, then the last patch should be fine after all. No, evidently you broke something, or your plpgsql is installed wrong. 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: [PATCHES] Re: [HACKERS] [PATCH] Re: Setuid functions
OK, I finally got around to adding the superuser check to my patch. So I try to test it and... ERROR: Only users with Postgres superuser privilege are permitted to create a function in the 'plpgsql' language. D'oh! So, if this is the case, then the last patch should be fine after all. Mark Peter Eisentraut wrote: > > If you mean "only allow a superuser do define functions using this > mechanism", that could work. But it would probably make this feature a > lot less attractive, because any setuid function would have to run with > super powers. > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
J-P wrote: > > I need to create a new system table like pg_log to > > implement a replication scheme. The big problem is > how > > I could get an OID for it, a unique OID that is > > reserved for that table??? Hiroshi Inoue wrote: > > > Do you need the following ? > > visco=# select oid from pg_class where relname = > 'pg_log'; > oid > -- > 1269 > (1 row) > > I'm afraid of misunderstanding. Sorry my question was wrongly asked. What I need is a unique OID for my new system table that is reserved for that table? A new Id that is not used by anything else, and that will never be used. (The reference to pg_log was just to show the similarity of what I need). N.B. I can't just #select oid from pg_class and take one that is not there, since I don't know if the oid I choose will be used by something else in the system?? Thanks for your help, J-P ___ Do You Yahoo!? Get your free @yahoo.ca address at http://mail.yahoo.ca ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
> Yes, nowhere near, and yes. Sequence objects require disk I/O to > update; the OID counter essentially lives in shared memory, and can > be bumped for the price of a spinlock access. Sequences also cache values (32 afair) - ie one log record is required for 32 nextval-s. Sequence' data file is updated at checkpoint time, so - not so much IO. I really think that using sequences for system tables IDs would be good. Vadim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] MySQL Gemini code
> "Michael" == Michael Widenius <[EMAIL PROTECTED]> writes: Michael> Please note that we NEVER have asked NuSphere to sign Michael> over copyright of Gemini to us. We do it only for the Michael> core server, and this is actually not an uncommon thing Michael> among open source companies. For example QT (Trolltech) Michael> and Ximian (a lot of gnome applications) does the same Michael> thing. Assigning over the code is also something that Michael> FSF requires for all code contributions. If you Michael> criticize us at MySQL AB, you should also criticize the Michael> above. And Redhat (who are obviously pro Open Source) does this with Cygwin, Sincerely, Adrian Phillips -- Your mouse has moved. Windows NT must be restarted for the change to take effect. Reboot now? [OK] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Could you use CTID instead of OID? > I am using both. > TIDs for fast access and OIDs for identification. > Unfortunately TIDs are transient and they aren't > that reliable as for identification. Hmm ... within a transaction I think they'd be reliable enough, but for long-term ID I agree they're not. What behavior do you need exactly; do you need to be able to find the updated version of a row you originally inserted? What would it take to use a user-defined primary key instead of OID? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
At 12:37 18/07/01 -0400, Tom Lane wrote: >Philip Warner <[EMAIL PROTECTED]> writes: >> At 11:38 18/07/01 -0400, Tom Lane wrote: >>> I'd just make the dependency be from view_a to a and keep things >>> simple. What's so wrong with recompiling the view for *every* change >>> of the underlying table? > >> Not a problem for views, but when you get to constraints on large tables, >> re-evaluating all the constraints unnecessarily could be a nightmare, and >> especially frustrating when you just dropped an irrelevant attr. > >Huh? You seem to be thinking that we'd need to re-check the constraint >at each row of the table, but I don't see why we'd need to. I was just >envisioning re-parsing the constraint source text. I'm paranoid, but there could be a case for doing so, especially if we allow CHAR(n) to become CHAR(m) where m < n. Or any similar data-affecting field change. 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
Re: OID wraparound (was Re: [HACKERS] pg_depend)
>>>Bruce Momjian said: [...] > > No, we won't, because OID wrap is an issue already for any long-uptime > > installation. (64-bit XIDs are not a real practical answer either, > > btw.) > > Have we had a wraparound yet? Just for the record, I had an OID overflow on production database (most middleware crashed mysteriously but no severe data loss) about a month ago. This was on 7.0.2 which probably had some bug ... preventing real wrap to happen. No new allocations (INSERTs that used autoincrementing sequences) were possible in most tables. Anyway, I had to dump/restore the database - several hours downtime. The database is not very big in size (around 10 GB in the data directory), but contains many objects (logs) and many objects are inserted/deleted from the database - in my opinion at not very high rate. Many tables are also created/dropped during processing. What is worrying is that this database lived about half a year only... In my opinion, making OIDs optional would help things very much. In my case, I don't need OIDs for log databases. Perhaps it would additionally help if OIDs are separately increasing for each database - not single counter for the entire PostgreSQL installation. Regards, Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
