[HACKERS] DROP USER weirdness in 7.2.1
I have encountered unexpected behavior of DROP USER in 7.2.1.
One would normally expect, that when DROP USER someuser is issued, all
associated data structures will be readjusted, especially ownership and access
rights.
This however does not happen.
After droping an user, that had ownership of tables, pg_dump complains :
[...]
pg_dump: WARNING: owner of data type table_one appears to be invalid
pg_dump: WARNING: owner of table "some_seq" appears to be invalid
[...]
The access rights to those tables remain
database=# \z table_one
Access privileges for database "customer"
Table| Access privileges
-+--
table_one | {=,98=arwdRxt,maria=arwdRxt}
(1 row)
There is no way to remove rights of this 'user' 98 using REVOKE etc.
Perhaps full dump/reload will remove the rights, because that user will not be
found, but restore may fail due to the error conditions.
Any resolution for this?
Regards,
Daniel
---(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] Bug in PL/pgSQL GET DIAGNOSTICS?
Tom Lane <[EMAIL PROTECTED]> wrote: > "Michael Paesold" <[EMAIL PROTECTED]> writes: > > In a PL/pgSQL function I want to insert into a table and get the OID back. > > That usually works with > > GET DIAGNOSTICS last_oid = RESULT_OID; > > right after the insert statement. > > > But if the table that I insert to has a rule (or perhaps a trigger?) that > > updates another table, the RESULT_OID after the insert will be 0 (zero). > > As of CVS tip, this example produces the results I believe you want: > > regression=# SELECT pltestfunc(10); > NOTICE: RESULT_OID: 282229 > NOTICE: RESULT_OID: 282230 > NOTICE: RESULT_OID: 282231 ... [snip] That is very good news. I appreciate that you got it fixed for 7.3. I am sure I am only one of many who have use for that, but thanks anyway! Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] French version of the PostgreSQL "Advocacy and Marketing" site is ready
Hi everyone, Thanks to the French members of the PostgreSQL Community (mainly François Suter <[EMAIL PROTECTED]>), the French translation of the PostgreSQL "Advocacy and Marketing" site, is now complete and ready for public use: http://advocacy.postgresql.org/?lang=fr That's 4 completed languages at this point, with more coming along. Let's see how many more can be added... :) Regards and best wishes, Justin Clift -- "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
Re: [HACKERS] [GENERAL] Postgres-based system to run .org registry?
On Mon, Oct 14, 2002 at 12:42:37PM -0700, David De Graff wrote: > Is this the same group that recently asked for input on their proposal, > which specified Postgres as the registry database? Hi everyone, Yes, this is us. (Sorry I've been inactive the last week. I was on vacation.) What follows is a strictly personal set of remarks. I don't speak for Afilias, Liberty RMS, PIR, or ISOC. I'm just some guy. They don't even like me. ;-) I want to take the opportunity to thank everyone in the PostgreSQL community for the help they've offered, and for the fantastic software. When I ask for help, the support I get is just tremendous, both for marketing efforts and for general Postgres operation questions I have. This is a remarkable victory for PostgreSQL, by the way. As you can see if you check out the public forums and the various, publicly posted remarks by various bidders (everything I know about in the bidding is posted on the ICANN site), people were "gunning" for PostgreSQL. There were many suggestions that PostgreSQL wasn't up to the job. The Gartner Group, despite their natural tendency to be suspicious of a thing "nobody else" is using, concluded that PostgreSQL was not too big a risk. That may sound like damning with faint praise, when we all know that PostgreSQL can indeed handle this sort of task. But even such a hesitant endorsement from someone like Gartner means that PostgreSQL is now regarded by the usual commercial suspects as a "real" system. I can't believe that's a bad thing. I should note that we have had tremendous help from Geoff Davidson and the rest of the crew at PostgreSQL, Inc., and that Justic Clift has been totally indefatiguable in finding clever ways of promoting PostgreSQL. Thanks, guys. I am very hopeful that this provides Liberty and Afilias with an opportunity to make additional contributions to PostgreSQL. Naturally, though, I don't know anything. I'll keep lobbying. I'm very proud to be associated with this development in PostgreSQL's history. It's only possible because of the PostgreSQL community: the tireless efforts of the contributors, and the astounding support that participants on lists like these give one another. Thank you. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Disabling triggers (was Re: [HACKERS] pgsql 7.2.3 crash)
On Mon, 14 Oct 2002, Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > On Sat, 12 Oct 2002, Joe Conway wrote: > >> Tom Lane wrote: > >>> Hackers: we might reasonably fix this by doing a deep copy of the > >>> relcache's trigger info during initResultRelInfo(); or we could fix it > >>> by getting rid of ri_TrigDesc and re-fetching from the relcache every > >>> time. The former would imply that trigger state would remain unchanged > >>> throughout a query, the latter would try to track currently-committed > >>> trigger behavior. Either way has got pitfalls I think. > > >>> Any thoughts on which way to go? > > >> I'd say: > >> 1. go with the former > > > I agree. > > That's my leaning too, after further reflection. Will make it so. > > >> 2. we definitely should also have an ALTER command to allow > >> disable/enable of triggers > > > I thought this was worked on for 7.3? > > Unless I missed it, it's not in current sources. Here is an email I sent to pgsql-patches. --- BEGIN -- Forwarded message -- Date: Tue, 13 Aug 2002 15:38:50 +1000 (EST) From: Gavin Sherry <[EMAIL PROTECTED]> To: Tom Lane <[EMAIL PROTECTED]> Cc: Neil Conway <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Subject: Re: [PATCHES] Fix disabled triggers with deferred constraints On Tue, 13 Aug 2002, Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > ...The spec is a large one and I didn't look at all references to > > triggers since there are hundreds -- but I don't believe that there is any > > precedent for an implementation of DISABLE TRIGGER. > > Thanks for the dig. I was hoping we could get some guidance from the > spec, but it looks like not. How about other implementations --- does > Oracle support disabled triggers? DB2? etc? Oracle 8 (and I presume 9) allows you to disable/enable triggers through alter table and alter trigger. My 8.1.7 documentation is silent on the cases you mention below and I do not have an oracle installation handy to test. Anyone? > > > FWIW, i think that in the case of deferred triggers they should all be > > added to the queue and whether they are executed or not should be > > evaluated inside DeferredTriggerExecute() with: > > if(LocTriggerData.tg_trigger->tgenabled == false) > > return; > > So check the state at execution, not when the triggering event occurs. > I don't have any strong reason to object to that, but I have a gut > feeling that it still needs to be thought about... > > > FWIW, i think that in the case of deferred triggers they should all be > > added to the queue and whether they are executed or not should be > > evaluated inside DeferredTriggerExecute() with: > > if(LocTriggerData.tg_trigger->tgenabled == false) > > return; > > So check the state at execution, not when the triggering event occurs. > I don't have any strong reason to object to that, but I have a gut > feeling that it still needs to be thought about... > > Let's see, I guess there are several possible changes of state for a > deferred trigger between the triggering event and the end of > transaction: > > * Trigger deleted. Surely the trigger shouldn't be executed, but should > we raise an error or just silently ignore it? (I suspect right now we > crash :-() > > * Trigger created. In some ideal world we might think that such a > trigger should be fired, but in reality that ain't gonna happen; we're > not going to record every possible event on the speculation that some > trigger for it might be created later in the transaction. It doesn't need to be an ideal world. We're only talking about deferred triggers after all. Why couldn't CreateTrgger() just have a look through deftrig_events, check for its relid and if its in there, call deferredTriggerAddEvent(). > * Trigger disabled. Your proposal is to not fire it. Okay, comports > with the deleted case, if we make that behavior be silently-ignore. It doesn't need to be an ideal world. We're only talking about deferred triggers after all. Why couldn't CreateTrgger() just have a look through deftrig_events, check for its relid and if its in there, call deferredTriggerAddEvent(). > * Trigger disabled. Your proposal is to not fire it. Okay, comports > with the deleted case, if we make that behavior be silently-ignore. > > * Trigger enabled. Your proposal is to fire it. Seems not to comport > with the creation case --- does that bother anyone? > > * Trigger changed from not-deferred to deferred. If we already fired it > for the event, we surely shouldn't fire it again. I believe the code > gets this case right. Agreed. > * Trigger changed from deferred to not-deferred. As Neil was pointing > out recently, this really should cause the trigger to be fired for the > pending event immediately, but we don't get that right at the moment. > (I suppose a stricter interpretation would be to raise an error because > we can't do anything that really comports with the intended behavior > of either case.
Re: [HACKERS] Anyone want to assist with the translation of the Advocacy site?
On Tue, Oct 15, 2002 at 05:07:46AM +1000, Justin Clift wrote: > Hi Adrian, > > Wow. That's pretty cool. :) > > No-one has offered to do Romanian yet, so you're very welcome to. > > First things first: > > - What is the two letter language identifier most often used for > Romanian? i.e. fr = Franch, de = German, etc. ro? ro = Romanian > - What is the character set that should be used to send out Romanian > pages? i.e. for English, French, German it's iso-8859-1, for Turkish > it's iso-8859-9, Romanian = ? iso-8859-2 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Current CVS - something broken in contrib
In any contrib module 'make installcheck' runs infinite time... For example, contrib/ltree % gmake installcheck gmake -C ../../src/test/regress pg_regress gmake[1]: ÷ÈÏÄ × ËÁÔÁÌÏÇ `/spool/home/teodor/pgsql/src/test/regress' gmake[1]: `pg_regress' ÎÅ ÔÒÅÂÕÅÔ ÏÂÎÏ×ÌÅÎÉÑ. gmake[1]: ÷ÙÈÏÄ ÉÚ ËÁÔÁÌÏÇ `/spool/home/teodor/pgsql/src/test/regress' ../../src/test/regress/pg_regress ltree (using postmaster on Unix socket, default port) == dropping database "regression" == DROP DATABASE == creating database "regression" == CREATE DATABASE ALTER DATABASE == dropping regression test user accounts == == installing PL/pgSQL== == running regression test queries== test ltree... In this time in top: CPU states: 100% user, 0.0% nice, 0.0% system, 0.0% interrupt, 0.0% idle Mem: 116M Active, 33M Inact, 24M Wired, 11M Cache, 29M Buf, 1528K Free Swap: 510M Total, 32M Used, 477M Free, 6% Inuse kill PID USERNAME PRI NICE SIZERES STATETIME WCPUCPU COMMAND 18180 teodor64 0 1852K 1248K RUN 1:15 95.07% 93.65% psql postmaster doesn't take a CPU time... -- Teodor Sigaev [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] Current CVS - something broken in contrib
Teodor Sigaev <[EMAIL PROTECTED]> writes: > In any contrib module 'make installcheck' runs infinite time... Looks like my fault :-( ... will have it fixed in a few minutes (I seem to have broken psql for COPY FROM STDIN :-() 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] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: >>Good question. What is going to happen is that select() is going to be >>passed tv_sec = 1, and it is going to sleep for one second. Now, if >>select is interrupted, another time() call is going to be made. > > There is a very simple answer to this, which I think I suggested to Joe > originally, but it's not in the code now: the initial calculation of > finish_time = now() + timeout must add one. This ensures that any > roundoff error is in the conservative direction of timing out later, > rather than sooner. Yes, my bad, I guess. The thing was that with the extra +1, I was repeatedly getting a wall-clock time of 2 seconds with a timeout set to 1 second. It seemed odd to have my 1 second timeout automatically turned into 2 seconds every time. With the current code, I tried a timeout of 1 second at least a 100 times and it always took about 1 full wall-clock second. But I guess if there is some corner case that needs it... Joe ---(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] Current CVS - something broken in contrib
Teodor Sigaev <[EMAIL PROTECTED]> writes: > In any contrib module 'make installcheck' runs infinite time... Actually, I had managed to break \copy, not COPY --- it seems the main regression tests exercise COPY but not \copy. It might be a good idea to change copy2.sql to exercise both ... Anyway, fix committed. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] orderRules() now a bad idea?
Tom Lane wrote: > > I just noticed that rewriteHandler.c contains a subroutine orderRules() > that reorders the rules for a relation into the order > non-instead rules > qualified instead rules > unqualified instead rules > This conflicts with the feature we'd added to 7.3 to fire rules in > alphabetical order. (What will presently happen is they'll be fired > alphabetically in each of these categories.) > > I see that the logic in fireRules() assumes that rules are processed in > this order, but that would be fairly easy to fix. Is there any other > good reason for doing this reordering? I'd like to remove orderRules() > and implement straight alphabetical ordering. I don't see a strong reason why not doing it the way you propose. It's just that you need to keep a version of the parsetree before you applied an unqualified instead rule just for the case that you later need to apply one of the others. But this copy shall not make it into the final list of queries. Jan -- #==# # 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: [HACKERS] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Joe Conway <[EMAIL PROTECTED]> writes: > The thing was that with the extra +1, I was repeatedly getting a wall-clock > time of 2 seconds with a timeout set to 1 second. It seemed odd to have my 1 > second timeout automatically turned into 2 seconds every time. That is odd; seems like you should get between 1 and 2 seconds. How were you measuring the delay, exactly? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] oid2name and relfilenode
Bruce Momjian writes: > > Rename oid2name to relfilenode2name and install by default > > > > Actually, to be accurate, I think databases are stored based on their > oid and tables/indexes are stored based on their relfilenode. That is > pretty confusing. Do we still do the renaming? I don't think we should do either of these. Instead of giving people tools to dig around in the internals, let's give them tools to get the information they really want. Not sure what that is though. -- 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] orderRules() now a bad idea?
Bruce Momjian writes: > Unless Jan has an objection, I think alpha is best, because it matches > trigger rule odering. That original rule ordering isn't something > anyone is going to figure out on their own. But alphabetical? According to whose definition of the alphabet? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] DROP USER weirdness in 7.2.1
Daniel Kalchev writes: > One would normally expect, that when DROP USER someuser is issued, all > associated data structures will be readjusted, especially ownership and access > rights. Perhaps, but the documentation states otherwise. > There is no way to remove rights of this 'user' 98 using REVOKE etc. > > Perhaps full dump/reload will remove the rights, because that user will not be > found, but restore may fail due to the error conditions. > > Any resolution for this? Recreate the user with the given ID and drop the objects manually. -- Peter Eisentraut [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] foreign key problem with pg_dump under 7.3b2
I'm currently using 7.3b2 for test and development. I ran into a problem using a dumped schema from pg_dump. After importing the dumped schema, any delete or update involving a foreign key results in a relation 0 does not exist error. I noticed that all my foreign key declarations were moved from the table create to separate statements at the bottom of the dump file. Thanks in advance for any insight into this problem you can lend. -John ---(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] foreign key problem with pg_dump under 7.3b2
On 15 Oct 2002, John Halderman wrote: > I'm currently using 7.3b2 for test and development. I ran into a problem > using a dumped schema from pg_dump. After importing the dumped schema, > any delete or update involving a foreign key results in a relation 0 > does not exist error. I noticed that all my foreign key declarations > were moved from the table create to separate statements at the bottom of > the dump file. Thanks in advance for any insight into this problem you > can lend. If the data has moved from earlier versions (I think 7.0.x) there was a bug in an older pg_dump that dropped a piece of information (the related table) and the loss would be carried along. That information is now used rather than the name passed in the args so said dumps break on b2. Current sources should fill in the missing information whenever possible. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] foreign key problem with pg_dump under 7.3b2
On Tue, 2002-10-15 at 15:12, Stephan Szabo wrote: > On 15 Oct 2002, John Halderman wrote: > > > I'm currently using 7.3b2 for test and development. I ran into a problem > > using a dumped schema from pg_dump. After importing the dumped schema, > > any delete or update involving a foreign key results in a relation 0 > > does not exist error. I noticed that all my foreign key declarations > > were moved from the table create to separate statements at the bottom of > > the dump file. Thanks in advance for any insight into this problem you > > can lend. > > If the data has moved from earlier versions (I think 7.0.x) there was a > bug in an older pg_dump that dropped a piece of information (the > related table) and the loss would be carried along. That > information is now used rather than the name passed in the args > so said dumps break on b2. Current sources should fill in the missing > information whenever possible. > > Actually we are dumping from b2 to b2. Also the problem doesn't seem to be related to the data or missing data. I can infer this because I am doing a schema only dump. After I import this dump i create some test data and still run into the relation 0 does not exist error. I think it has something to do with the way the dump defines the foreign key constraints and triggers. Thanks again for the help. -john ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] COPY syntax
According to the syntax diagram in the documenation, I can write COPY table TO STDOUT WITH BINARY OIDS; Shouldn't the "binary", being an adjective, be attached to something? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] foreign key problem with pg_dump under 7.3b2
On Tue, 2002-10-15 at 15:38, Stephan Szabo wrote: > > On 15 Oct 2002, John Halderman wrote: > > > On Tue, 2002-10-15 at 15:12, Stephan Szabo wrote: > > > On 15 Oct 2002, John Halderman wrote: > > > > > > > I'm currently using 7.3b2 for test and development. I ran into a problem > > > > using a dumped schema from pg_dump. After importing the dumped schema, > > > > any delete or update involving a foreign key results in a relation 0 > > > > does not exist error. I noticed that all my foreign key declarations > > > > were moved from the table create to separate statements at the bottom of > > > > the dump file. Thanks in advance for any insight into this problem you > > > > can lend. > > > > > > If the data has moved from earlier versions (I think 7.0.x) there was a > > > bug in an older pg_dump that dropped a piece of information (the > > > related table) and the loss would be carried along. That > > > information is now used rather than the name passed in the args > > > so said dumps break on b2. Current sources should fill in the missing > > > information whenever possible. > > > > > > > > Actually we are dumping from b2 to b2. Also the problem doesn't seem to be > > related to the data or missing data. I can infer this because I am doing > > a schema only dump. After I import this dump i create some test data and > > still run into the relation 0 does not exist error. I think it has > > something to do with the way the dump defines the foreign key > > constraints and triggers. Thanks again for the help. > > Was your old b2 system loaded from a dump? If so, you'd be in the upgrade > portion of the problem. Old dumps were incorrect, and as soon as you > loaded from one of those dumps all future dumps became incorrect in the > same way. Current sources notice that the item is missing and attempts > to figure out what it should be. > > Interesting, that may be it. I'll do some testing to verify your theory. Thank you for your help. -john. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] oid2name and relfilenode
Peter Eisentraut wrote: > Bruce Momjian writes: > > > > Rename oid2name to relfilenode2name and install by default > > > > > > > Actually, to be accurate, I think databases are stored based on their > > oid and tables/indexes are stored based on their relfilenode. That is > > pretty confusing. Do we still do the renaming? > > I don't think we should do either of these. Instead of giving people > tools to dig around in the internals, let's give them tools to get the > information they really want. Not sure what that is though. That's the problem. People sometimes need to access those files from the file system level. There are just too many variations of why they need that I don't think there is any way to anticipate them. If someone comes up with a better idea, I am all ears, but seeing as nothing better has appeared since it was first written, I think we need to move forward. I will add these items to the TODO list, unless someone else votes. I think they are too important to have in /contrib (and we moved pg_resetxlog/pg_controldata from /contrib in 7.3) and I think relfilenode is better than OID so people start using that rather than OID for filename mapping. -- 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 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] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Tom Lane wrote: > Joe Conway <[EMAIL PROTECTED]> writes: > > The thing was that with the extra +1, I was repeatedly getting a wall-clock > > time of 2 seconds with a timeout set to 1 second. It seemed odd to have my 1 > > second timeout automatically turned into 2 seconds every time. > > That is odd; seems like you should get between 1 and 2 seconds. How > were you measuring the delay, exactly? Remember, that if you add 1, the select() is going to get tv_sec = 2, so yes, it will be two seconds. -- 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] orderRules() now a bad idea?
Peter Eisentraut <[EMAIL PROTECTED]> writes: > But alphabetical? According to whose definition of the alphabet? It looks like NAME comparison uses strcmp (actually strncmp). So it'll be numeric byte-code order. There's no particular reason we couldn't make that be strcoll instead, I suppose, except perhaps speed. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> That is odd; seems like you should get between 1 and 2 seconds. How >> were you measuring the delay, exactly? > Remember, that if you add 1, the select() is going to get tv_sec = 2, so > yes, it will be two seconds. Yeah, but only if the value isn't recalculated shortly later. Consider caller computes finish_time = time() + timeout; ... inside select-wait loop, compute max_delay = finish_time - time(); If the time() value has incremented by 1 second between these two lines of code, you have a problem with a 1-second timeout... 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] oid2name and relfilenode
Bruce Momjian <[EMAIL PROTECTED]> writes: > I will add these items to the TODO list, unless someone else votes. I was not thrilled with the idea of moving oid2name out of contrib either, but kept silent to see if someone else would complain first ... Basically I think that oid2name is a hacker's tool and not something users or DBAs really want as-is --- which I guess is another way of stating Peter's gripe that what it produces is not what the users want to know. The actual useful guts of it are nothing more than SELECT oid, datname FROM pg_database; SELECT relfilenode, relname FROM pg_class; which does not seem significant enough to justify the packaging and documentation overhead of having another command-line tool. The only actual use-case I've seen for it so far is as a vehicle for computing actual database sizes on-disk; which would be better served by a tool that did the whole job. What other uses do people have for oid2name? 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] oid2name and relfilenode
OK, removed from TODO. I figured it was as useful as pg_controldata but can see what you say that those give information that you can't get any other way, while oid2name info can be gotten another way. You can look at the oid2name README for examples of its usage. --- Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I will add these items to the TODO list, unless someone else votes. > > I was not thrilled with the idea of moving oid2name out of contrib > either, but kept silent to see if someone else would complain first ... > > Basically I think that oid2name is a hacker's tool and not something > users or DBAs really want as-is --- which I guess is another way of > stating Peter's gripe that what it produces is not what the users want > to know. The actual useful guts of it are nothing more than > SELECT oid, datname FROM pg_database; > SELECT relfilenode, relname FROM pg_class; > which does not seem significant enough to justify the packaging and > documentation overhead of having another command-line tool. > > The only actual use-case I've seen for it so far is as a vehicle for > computing actual database sizes on-disk; which would be better served > by a tool that did the whole job. What other uses do people have for > oid2name? > > regards, tom lane > -- 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] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> That is odd; seems like you should get between 1 and 2 seconds. How > >> were you measuring the delay, exactly? > > > Remember, that if you add 1, the select() is going to get tv_sec = 2, so > > yes, it will be two seconds. > > Yeah, but only if the value isn't recalculated shortly later. Consider > > caller computes finish_time = time() + timeout; > > ... > > inside select-wait loop, compute max_delay = finish_time - time(); > > If the time() value has incremented by 1 second between these two lines > of code, you have a problem with a 1-second timeout... Yep. If you track finish time, you get that 1 second rounding problem, and if you track just duration/timeout, you get into the problem of not knowing when the timeout has ended. I don't think these can be fixed except by overestimating (+1) or by tracking subseconds along with seconds so you really know when one second has elapsed. Perhaps we need to modify a timeout of 1 to be 2 and leave other values alone. -- 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 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] [GENERAL] Postgres-based system to run .org registry?
Andrew Sullivan wrote: > On Mon, Oct 14, 2002 at 12:42:37PM -0700, David De Graff wrote: > > > Is this the same group that recently asked for input on their proposal, > > which specified Postgres as the registry database? > > Hi everyone, > > Yes, this is us. (Sorry I've been inactive the last week. I was on > vacation.) You host .org now. No more vacations! ;-) > What follows is a strictly personal set of remarks. I don't speak > for Afilias, Liberty RMS, PIR, or ISOC. I'm just some guy. They > don't even like me. ;-) > > I want to take the opportunity to thank everyone in the PostgreSQL > community for the help they've offered, and for the fantastic > software. When I ask for help, the support I get is just tremendous, > both for marketing efforts and for general Postgres operation > questions I have. It is a huge win. I remember when Yahoo started using MySQL, and Slashdot uses it; well, this is the same, but bigger in many ways because it is so pervasive. You may not go to Yahoo, and most people (non-techies) don't go to slashdot, but everyone uses .org names, so it is the kind of universal calling card we need to take PostgreSQL to the next level. The past six months has seen huge improvements in PostgreSQL adoption; much more than I expected. The popularity of the software continues to amaze me, and its market growth seems unstoppable at this point. We are showing up in places I never expected: .org registry, tons of books, conventions, everywhere. It is just a wave that keeps getting bigger and bigger. I am starting to imagine what Linus felt seeing Linux take off; you just sit around and wonder how it is all happening, and of course, it is happening because you offer a unique value to users, and their sharing with others just makes it continue to grow. In one word: Amazing! -- 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] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Tom Lane wrote:
> Joe Conway <[EMAIL PROTECTED]> writes:
>
>> The thing was that with the extra +1, I was repeatedly getting a
>> wall-clock time of 2 seconds with a timeout set to 1 second. It seemed
>> odd to have my 1 second timeout automatically turned into 2 seconds every
>> time.
>
> That is odd; seems like you should get between 1 and 2 seconds. How were
> you measuring the delay, exactly?
OK. I got a little more scientific about my testing. I used a php script,
running on the same machine, to connect/disconnect in a tight loop and timed
successful and unsuccessful connection attempts using microtime().
Here are the results. First with current cvs code:
current cvs libpq code
---
good connect info, using unix socket, timeout = 1 second:
=
unsuccessful 69 times: sum 0.41736388206482: avg 0.0060487519139829
successful 9931 times: sum 68.798981308937: avg 0.0069276992557584
good connect info, using hostaddr, timeout = 1 second
=
unsuccessful 72 times: sum 0.37020063400269: avg 0.0051416754722595
successful 9928 times: sum 75.047878861427: avg 0.0075592142285886
current cvs libpq code - bad hostaadr, using hostaddr, timeout = 1 second
=
unsuccessful 100 times: sum 99.975758910179: avg 0.99975758910179
successful 0 times: sum 0: avg n/a
Clearly not good. The timeout code is causing connection failures about 0.7%
of the time. Next are the results using the attached patch. Per Bruce's
suggestion, it only adds 1 if the timeout is set to 1.
with patch libpq code
-
good connect info, using unix socket, timeout = 1 second
unsuccessful 0 times: sum 0: avg n/a
successful 1 times: sum 68.95981669426: avg 0.006895981669426
with patch libpq code - good connect info, using hostaddr, timeout = 1 second
=
unsuccessful 0 times: sum 0: avg n/a
successful 1 times: sum 73.500863552094: avg 0.0073500863552094
with patch libpq code - good connect info, using hostaddr, timeout = 2 seconds
==
unsuccessful 0 times: sum 0: avg n/a
successful 1 times: sum 73.354710936546: avg 0.0073354710936546
with patch libpq code - bad hostaadr, using hostaddr, timeout = 1 second
unsuccessful 100 times: sum 149.98181843758: avg 1.4998181843758
successful 0 times: sum 0: avg n/a
with patch libpq code - bad hostaadr, using hostaddr, timeout = 2 seconds
=
unsuccessful 100 times: sum 149.98445630074: avg 1.4998445630074
successful 0 times: sum 0: avg n/a
with patch libpq code - bad hostaadr, using hostaddr, timeout = 3 seconds
=
unsuccessful 20 times: sum 59.842629671097: avg 2.9921314835548
successful 0 times: sum 0: avg n/a
With the patch there were 0 failures on 3 attempts using good connect
information.
If there are no objections, please apply the attached. Otherwise let me know
if you'd like different tests or would like to try other approaches.
Thanks,
Joe
Index: src/interfaces/libpq/fe-connect.c
===
RCS file: /opt/src/cvs/pgsql-server/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.210
diff -c -r1.210 fe-connect.c
*** src/interfaces/libpq/fe-connect.c 15 Oct 2002 01:48:25 - 1.210
--- src/interfaces/libpq/fe-connect.c 15 Oct 2002 22:36:53 -
***
*** 1066,1071
--- 1066,1073
if (conn->connect_timeout != NULL)
{
remains.tv_sec = atoi(conn->connect_timeout);
+ if (remains.tv_sec == 1)
+ remains.tv_sec += 1;
if (!remains.tv_sec)
{
conn->status = CONNECTION_BAD;
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Vacuum improvement
Hi all, I'm thinking that there is an improvement to vacuum which could be made for 7.4. VACUUM FULLing large, heavily updated tables is a pain. There's very little an application can do to minimise dead-tuples, particularly if the table is randomly updated. Wouldn't it be beneficial if VACUUM could have a parameter which specified how much of the table is vacuumed. That is, you could specify: VACUUM FULL test 20 precent; Yes, terrible syntax but regardless: this would mean that we could spread the vacuum out and not, possibly, be backing up queues. ANALYZE could be modified, if necessary. Thoughts? Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY syntax
Peter Eisentraut wrote: > According to the syntax diagram in the documenation, I can write > > COPY table TO STDOUT WITH BINARY OIDS; > > Shouldn't the "binary", being an adjective, be attached to something? Uh, it is attached to WITH? Seriously, yea, it doesn't read well, but it follows the WITH format of parameters to a command. -- 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
Re: Changing Column Order (Was Re: [HACKERS] MySQL vs PostgreSQL.)
Bruce Momjian wrote: > > Alessio Bragadini wrote: > > On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote: > > > > > I cannot think of any reason why changing column order should be > > > implemented in Postgres. Seems like a waste of time/more code bloat for > > > something which is strictly asthetic. > > > > > > Regardless, I do have collegues/clients who ask when such a feature will > > > be implemented. Why is this useful? > > > > Has column ordering any effect on the physical tuple disposition? I've > > heard discussions about keeping fixed-size fields at the beginning of > > the tuple and similar. > > > > Sorry for the lame question. :-) > > Yes, column ordering matches physical column ordering in the file, and > yes, there is a small penalty for accessing any columns after the first > variable-length column (pg_type.typlen < 0). CHAR() used to be a fixed > length column, but with TOAST (large offline storage) it became variable > length too. I don't think there is much of a performance hit, though. When was char() fixed size? We had fixed size things like char, char2, char4 ... char16. But char() is internally bpchar() and has allways been variable-length. Jan -- #==# # 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: Changing Column Order (Was Re: [HACKERS] MySQL vs PostgreSQL.)
Jan Wieck wrote: > Bruce Momjian wrote: > > > > Alessio Bragadini wrote: > > > On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote: > > > > > > > I cannot think of any reason why changing column order should be > > > > implemented in Postgres. Seems like a waste of time/more code bloat for > > > > something which is strictly asthetic. > > > > > > > > Regardless, I do have collegues/clients who ask when such a feature will > > > > be implemented. Why is this useful? > > > > > > Has column ordering any effect on the physical tuple disposition? I've > > > heard discussions about keeping fixed-size fields at the beginning of > > > the tuple and similar. > > > > > > Sorry for the lame question. :-) > > > > Yes, column ordering matches physical column ordering in the file, and > > yes, there is a small penalty for accessing any columns after the first > > variable-length column (pg_type.typlen < 0). CHAR() used to be a fixed > > length column, but with TOAST (large offline storage) it became variable > > length too. I don't think there is much of a performance hit, though. > > When was char() fixed size? We had fixed size things like char, char2, > char4 ... char16. But char() is internally bpchar() and has allways been > variable-length. char() was fixed size only in that you could cache the column offsets for char() becuase it was always the same width on disk before TOAST. -- 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 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] Vacuum improvement
That a good idea. That way, if your database slows during specific windows in time, you can vacuum larger sizes, etc. Seemingly would help you better manage your vacuuming against system loading. Greg On Tue, 2002-10-15 at 19:22, Gavin Sherry wrote: > Hi all, > > I'm thinking that there is an improvement to vacuum which could be made > for 7.4. VACUUM FULLing large, heavily updated tables is a pain. There's > very little an application can do to minimise dead-tuples, particularly if > the table is randomly updated. Wouldn't it be beneficial if VACUUM could > have a parameter which specified how much of the table is vacuumed. That > is, you could specify: > > VACUUM FULL test 20 precent; > > Yes, terrible syntax but regardless: this would mean that we could > spread the vacuum out and not, possibly, be backing up queues. ANALYZE > could be modified, if necessary. > > Thoughts? > > Gavin > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster signature.asc Description: This is a digitally signed message part
Re: [HACKERS] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Joe Conway <[EMAIL PROTECTED]> writes:
> [ some convincing test cases that timeout=1 is not good ]
> remains.tv_sec = atoi(conn->connect_timeout);
> + if (remains.tv_sec == 1)
> + remains.tv_sec += 1;
> if (!remains.tv_sec)
> {
> conn->status = CONNECTION_BAD;
On pure-paranoia grounds, I'd suggest the logic
+ /* force a sane minimum delay */
+ if (remains.tv_sec < 2)
+ remains.tv_sec = 2;
whereupon you could remove the failure check just below.
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] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Tom Lane wrote:
> Joe Conway <[EMAIL PROTECTED]> writes:
> > [ some convincing test cases that timeout=1 is not good ]
>
> > remains.tv_sec = atoi(conn->connect_timeout);
> > + if (remains.tv_sec == 1)
> > + remains.tv_sec += 1;
> > if (!remains.tv_sec)
> > {
> > conn->status = CONNECTION_BAD;
>
> On pure-paranoia grounds, I'd suggest the logic
>
> + /* force a sane minimum delay */
> + if (remains.tv_sec < 2)
> + remains.tv_sec = 2;
>
> whereupon you could remove the failure check just below.
I think we should fail if they set the timeout to zero, rather than
cover it up by setting the delay to two.
Attached is a patch that implements most of what we have discussed:
use time()
get rid of timeval where not needed
allow restart of select() to properly compute remaining time
add 1 to timeout == 1
pass finish time to pqWaitTimed
Patch applied. I am applying it so it is in CVS and everyone can see
it. I will keep modifying it until everyone likes it. It is just
easier to do it that way when multiple people are reviewing it. They
can jump in and make changes too.
--
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
Index: src/interfaces/libpq/fe-connect.c
===
RCS file: /cvsroot/pgsql-server/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.210
diff -c -c -r1.210 fe-connect.c
*** src/interfaces/libpq/fe-connect.c 15 Oct 2002 01:48:25 - 1.210
--- src/interfaces/libpq/fe-connect.c 16 Oct 2002 02:48:07 -
***
*** 1052,1061
{
PostgresPollingStatusType flag = PGRES_POLLING_WRITING;
! time_t finish_time = 0,
! current_time;
! struct timeval remains,
! *rp = NULL;
if (conn == NULL || conn->status == CONNECTION_BAD)
return 0;
--- 1052,1058
{
PostgresPollingStatusType flag = PGRES_POLLING_WRITING;
! time_t finish_time = -1;
if (conn == NULL || conn->status == CONNECTION_BAD)
return 0;
***
*** 1065,1084
*/
if (conn->connect_timeout != NULL)
{
! remains.tv_sec = atoi(conn->connect_timeout);
! if (!remains.tv_sec)
{
conn->status = CONNECTION_BAD;
return 0;
}
! remains.tv_usec = 0;/* We don't use subsecond timing */
! rp = &remains;
!
/* calculate the finish time based on start + timeout */
! finish_time = time((time_t *) NULL) + remains.tv_sec;
}
! while (rp == NULL || remains.tv_sec > 0)
{
/*
* Wait, if necessary. Note that the initial state (just after
--- 1062,1082
*/
if (conn->connect_timeout != NULL)
{
! int timeout = atoi(conn->connect_timeout);
!
! if (timeout == 0)
{
conn->status = CONNECTION_BAD;
return 0;
}
! /* Rounding could cause connection to fail;we need at least 2 secs */
! if (timeout == 1)
! timeout++;
/* calculate the finish time based on start + timeout */
! finish_time = time(NULL) + timeout;
}
! while (finish_time == -1 || time(NULL) >= finish_time)
{
/*
* Wait, if necessary. Note that the initial state (just after
***
*** 1094,1100
return 1; /* success! */
case PGRES_POLLING_READING:
! if (pqWaitTimed(1, 0, conn, rp))
{
conn->status = CONNECTION_BAD;
return 0;
--- 1092,1098
return 1; /* success! */
case PGRES_POLLING_READING:
! if (pqWaitTimed(1, 0, conn, finish_time))
{
conn->status = CONNECTION_BAD;
return 0;
***
*** 1102,1108
break;
case PGRES_POLLING_WRITING:
! if (pqWaitTimed(0, 1, conn, rp))
Re: [HACKERS] [GENERAL] Postgres-based system to run .org registry?
En Tue, 15 Oct 2002 18:19:36 -0400 (EDT) Bruce Momjian <[EMAIL PROTECTED]> escribió: > We are showing up in places I never expected: .org registry, tons of > books, conventions, everywhere. It is just a wave that keeps getting > bigger and bigger. I am starting to imagine what Linus felt seeing > Linux take off; you just sit around and wonder how it is all happening, > and of course, it is happening because you offer a unique value to > users, and their sharing with others just makes it continue to grow. > > In one word: Amazing! This is not without good reason. PostgreSQL has shown repeatedly to be an excellent product, able to compete with most commercial products. I've just been asked to give a little administration course for people in an government organization, where Oracle has been disregarded for giving little gain over what PostgreSQL gives. I am pleased to have done such a good decision to stick with PostgreSQL a couple years ago. I will just continue with my crusade here, where everything appears to be only Oracle and just a little MySQL. Of course none of this would be possible without the excellent quality of the work people here does. If there is something to be amazed at is the people, their commitment and the quality of their work. One can only say a big "thanks!". -- Alvaro Herrera () Thou shalt study thy libraries and strive not to reinvent them without cause, that thy code may be short and readable and thy days pleasant and productive. (7th Commandment for C Programmers) ---(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: Changing Column Order (Was Re: [HACKERS] MySQL vs PostgreSQL.)
Bruce Momjian <[EMAIL PROTECTED]> writes: > Jan Wieck wrote: >> When was char() fixed size? > char() was fixed size only in that you could cache the column offsets > for char() becuase it was always the same width on disk before TOAST. But that was already broken by MULTIBYTE. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Changing Column Order (Was Re: [HACKERS] MySQL vs PostgreSQL.)
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Jan Wieck wrote: > >> When was char() fixed size? > > > char() was fixed size only in that you could cache the column offsets > > for char() becuase it was always the same width on disk before TOAST. > > But that was already broken by MULTIBYTE. Yes, I think there was conditional code that had the optimization only for non-multibyte servers. Of course, now multibyte is default. -- 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
Re: [HACKERS] Vacuum improvement
Gavin Sherry <[EMAIL PROTECTED]> writes: > have a parameter which specified how much of the table is vacuumed. That > is, you could specify: > VACUUM FULL test 20 precent; Erm ... but which 20 percent? In other words, how could you arrange for repeated applications of such a command to cover the whole table, and not just retrace an already-cleaned-out portion? I don't object to the idea in principle, but I am not sure how to implement it in practice. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vacuum improvement
On Tue, Oct 15, 2002 at 11:52:35PM -0400, Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > have a parameter which specified how much of the table is vacuumed. That > > is, you could specify: > > VACUUM FULL test 20 precent; > > Erm ... but which 20 percent? In other words, how could you arrange for > repeated applications of such a command to cover the whole table, and > not just retrace an already-cleaned-out portion? Maybe each relation block can have a last-vacuumed timestamp? Somewhere in the table there would have to be a linked list of least-recently vacuumed blocks so the vacuum cleaner does not have to read every block to know which one to clean. Or maybe some system table can provide information about activity in each block since last vacuum. This forces the use of the stat collector... -- Alvaro Herrera () "El sabio habla porque tiene algo que decir; el tonto, porque tiene que decir algo" (Platon). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Postgresql and multithreading
Is there any plans to make postgresql multithreading? Thanks in advance (and also for all who commented to my question regarding replication.) Anuradha NB: please don't open fire to declare war on whether multithreading is needed for PGSql or not. I am just expecting a black and white answer from the `authorities' ;) -- Debian GNU/Linux (kernel 2.4.18-xfs-1.1) If you can survive death, you can probably survive anything. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgresql and multithreading
Anuradha Ratnaweera wrote: > > Is there any plans to make postgresql multithreading? > > Thanks in advance (and also for all who commented to my question > regarding replication.) > > Anuradha > > NB: please don't open fire to declare war on whether multithreading is > needed for PGSql or not. I am just expecting a black and white answer > from the `authorities' ;) We don't think it is needed, except perhaps for Win32 and Solaris, which have slow process creation times. -- 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] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Bruce Momjian wrote: > Patch applied. I am applying it so it is in CVS and everyone can see > it. I will keep modifying it until everyone likes it. It is just > easier to do it that way when multiple people are reviewing it. They > can jump in and make changes too. I ran the same test as before, with the following results: current cvs --- good connect info, using hostaddr, timeout = 1 second = unsuccessful 0 times: avg n/a successful 5 times: avg 0.0087 bad connect info, using hostaddr, timeout = 1 second unsuccessful 100 times: avg 1.4998 successful 0 times: sum 0: avg n/a Seems to work well. But one slight concern: with previous 2 line patch -- good connect info, using hostaddr, timeout = 1 || 2 second(s) = unsuccessful 0 times: avg n/a successful 2 times: avg 0.0074 These tests were on the same, otherwise unloaded development box. Not sure if it is an artifact or not, but the average connection time has gone from 0.0074 to 0.0087, an increase of about 17%. Also worth noting is that there was very little deviation from connect-to-connect on both of the tests (eye-balled the total range at about 0.0003). I did not bother calculating standard deviation of the connect times, but I'm certain it would not be enough to account for the difference. Could anything in Bruce's patch account for this, or do you think it is normal variation due to something on my dev box? Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgresql and multithreading
On Wed, Oct 16, 2002 at 12:59:57AM -0400, Bruce Momjian wrote: > Anuradha Ratnaweera wrote: > > > > Is there any plans to make postgresql multithreading? > > We don't think it is needed, except perhaps for Win32 and Solaris, which > have slow process creation times. Thanks, Bruce. But what I want to know is whether multithreading is likely to get into in postgresql, say somewhere in 8.x, or even in 9.x? (as they did with Apache). Are there any plans to do so, or is postgres going to remain rather a multi-process application? Anuradha -- Debian GNU/Linux (kernel 2.4.18-xfs-1.1) One nice thing about egotists: they don't talk about other people. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Joe Conway wrote: > Seems to work well. But one slight concern: > > with previous 2 line patch > -- > good connect info, using hostaddr, timeout = 1 || 2 second(s) > = > unsuccessful 0 times: avg n/a > successful 2 times: avg 0.0074 > > These tests were on the same, otherwise unloaded development box. Not sure if > it is an artifact or not, but the average connection time has gone from 0.0074 > to 0.0087, an increase of about 17%. Also worth noting is that there was very > little deviation from connect-to-connect on both of the tests (eye-balled the > total range at about 0.0003). I did not bother calculating standard deviation > of the connect times, but I'm certain it would not be enough to account for > the difference. Could anything in Bruce's patch account for this, or do you > think it is normal variation due to something on my dev box? Yes, the new code has _three_ time() calls, rather than the old code that I think only had two. I was going to mention it but I figured time() was a pretty light system call, sort of like getpid(). I needed the additional time() calls so the computation of remaining time was more accurate, i.e. we are not resetting the timer on a select() EINTR anymore. Should I try to rework 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] Postgresql and multithreading
On Wed, 16 Oct 2002, Anuradha Ratnaweera wrote: > > Is there any plans to make postgresql multithreading? > > Thanks in advance (and also for all who commented to my question > regarding replication.) > > Anuradha > > NB: please don't open fire to declare war on whether multithreading is > needed for PGSql or not. I am just expecting a black and white answer > from the `authorities' ;) This has been discussed at length in the past. The answer has always been no. Consult the archives for extensive heated discussion :-). Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Postgresql and multithreading
Anuradha Ratnaweera wrote: > On Wed, Oct 16, 2002 at 12:59:57AM -0400, Bruce Momjian wrote: > > Anuradha Ratnaweera wrote: > > > > > > Is there any plans to make postgresql multithreading? > > > > We don't think it is needed, except perhaps for Win32 and Solaris, which > > have slow process creation times. > > Thanks, Bruce. But what I want to know is whether multithreading is > likely to get into in postgresql, say somewhere in 8.x, or even in 9.x? > (as they did with Apache). Are there any plans to do so, or is postgres > going to remain rather a multi-process application? It may be optional some day, most likely for Win32 at first, but we see little value to it on most other platforms; of course, we may be wrong. I am also not sure if it is a big win on Apache either; I think the jury is still out on that one, hence the slow adoption of 2.X, and we don't want to add threads and make a mess of the code or slow it down, which does often happen. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postgresql and multithreading
On Wed, Oct 16, 2002 at 01:25:23AM -0400, Bruce Momjian wrote: > Anuradha Ratnaweera wrote: > > > ... what I want to know is whether multithreading is likely to get > > into in postgresql, say somewhere in 8.x, or even in 9.x? > > It may be optional some day, most likely for Win32 at first, but we see > little value to it on most other platforms; of course, we may be wrong. In that case, I wonder if it is worth folking a new project to add threading support to the backend? Of course, keeping in sync with the original would be lot of work. In that way, one should be able to test the hypothesis (whether threads improve things, or the other way round - if one likes it it that way :)) without messing around with stable postgres code, as they did and do with postgresql-R. And a minor question is wheter it is legal to keep the _changes_ in such a project GPL? > I am also not sure if it is a big win on Apache either; I think the > jury is still out on that one, hence the slow adoption of 2.X, As far as we are concened, it is the stability, rather than speed which still keeps us in 1.3. > and we don't want to add threads and make a mess of the code or slow > it down, which does often happen. Fully agreed. Anuradha -- Debian GNU/Linux (kernel 2.4.18-xfs-1.1) Equality is not when a female Einstein gets promoted to assistant professor; equality is when a female schlemiel moves ahead as fast as a male schlemiel. -- Ewald Nyquist ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Bruce Momjian wrote: > Yes, the new code has _three_ time() calls, rather than the old code > that I think only had two. I was going to mention it but I figured > time() was a pretty light system call, sort of like getpid(). > > I needed the additional time() calls so the computation of remaining > time was more accurate, i.e. we are not resetting the timer on a > select() EINTR anymore. > > Should I try to rework it? > I tried two more runs of 1, and the average is pretty steady at 0.0087. However the total range is a fair bit wider than I originally reported. I added a forth time() call to see what the effect would be. It increased the average to 0.0089 (two runs of 1 connects each), so I don't think the time() call explains the entire difference. Not sure this is worth worrying about or not. I'd guess anyone serious about keeping connect time to a minimum uses some kind of connection pool or persistent connection anyway. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Postgresql and multithreading
Anuradha Ratnaweera wrote: > On Wed, Oct 16, 2002 at 01:25:23AM -0400, Bruce Momjian wrote: > > Anuradha Ratnaweera wrote: > > > > > ... what I want to know is whether multithreading is likely to get > > > into in postgresql, say somewhere in 8.x, or even in 9.x? > > > > It may be optional some day, most likely for Win32 at first, but we see > > little value to it on most other platforms; of course, we may be wrong. > > In that case, I wonder if it is worth folking a new project to add > threading support to the backend? Of course, keeping in sync with the > original would be lot of work. Probably not, but you can try. > In that way, one should be able to test the hypothesis (whether threads > improve things, or the other way round - if one likes it it that way :)) > without messing around with stable postgres code, as they did and do > with postgresql-R. I guess. > And a minor question is wheter it is legal to keep the _changes_ in such > a project GPL? We don't think we change the license, and we are happy with BSD. It certainly will never be merged in with a GPL, I can say that for sure. > > I am also not sure if it is a big win on Apache either; I think the > > jury is still out on that one, hence the slow adoption of 2.X, > > As far as we are concened, it is the stability, rather than speed which > still keeps us in 1.3. You could easily lose stability with threads -- don't think they are a free ride --- they aren't, and no, I don't feel like regurgitating what is already a 'thread' link on the TODO list. -- 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] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Joe Conway wrote: > Bruce Momjian wrote: > > Yes, the new code has _three_ time() calls, rather than the old code > > that I think only had two. I was going to mention it but I figured > > time() was a pretty light system call, sort of like getpid(). > > > > I needed the additional time() calls so the computation of remaining > > time was more accurate, i.e. we are not resetting the timer on a > > select() EINTR anymore. > > > > Should I try to rework it? > > > > I tried two more runs of 1, and the average is pretty steady at 0.0087. > However the total range is a fair bit wider than I originally reported. > > I added a forth time() call to see what the effect would be. It increased the > average to 0.0089 (two runs of 1 connects each), so I don't think the > time() call explains the entire difference. > > Not sure this is worth worrying about or not. I'd guess anyone serious about > keeping connect time to a minimum uses some kind of connection pool or > persistent connection anyway. Well, the fact you see a change of 0.0002 is significant. Let me add that in the old code there was only one time() call _in_ the loop, while now, there are two, so I can easily see there are several additional time() calls. Did you put your calls in the while loop? -- 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] Postgresql and multithreading
On Wed, 16 Oct 2002, Anuradha Ratnaweera wrote: > On Wed, Oct 16, 2002 at 01:25:23AM -0400, Bruce Momjian wrote: > > Anuradha Ratnaweera wrote: > > > > > ... what I want to know is whether multithreading is likely to get > > > into in postgresql, say somewhere in 8.x, or even in 9.x? > > > > It may be optional some day, most likely for Win32 at first, but we see > > little value to it on most other platforms; of course, we may be wrong. > > In that case, I wonder if it is worth folking a new project to add > threading support to the backend? Of course, keeping in sync with the > original would be lot of work. http://sourceforge.net/projects/mtpgsql > And a minor question is wheter it is legal to keep the _changes_ in such > a project GPL? Do you mean 'relicence the forked copy'? Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgresql and multithreading
On Wed, Oct 16, 2002 at 03:40:47PM +1000, Gavin Sherry wrote: > On Wed, 16 Oct 2002, Anuradha Ratnaweera wrote: > > > And a minor question is wheter it is legal to keep the _changes_ in such > > a project GPL? > > Do you mean 'relicence the forked copy'? Nope. To keep the `original' code licence as it is and to release the changes GPL? Is the question sane at first place? Anuradha -- Debian GNU/Linux (kernel 2.4.18-xfs-1.1) You got to be very careful if you don't know where you're going, because you might not get there. -- Yogi Berra ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postgresql and multithreading
Anuradha Ratnaweera wrote: > On Wed, Oct 16, 2002 at 03:40:47PM +1000, Gavin Sherry wrote: > > On Wed, 16 Oct 2002, Anuradha Ratnaweera wrote: > > > > > And a minor question is wheter it is legal to keep the _changes_ in such > > > a project GPL? > > > > Do you mean 'relicence the forked copy'? > > Nope. To keep the `original' code licence as it is and to release the > changes GPL? Is the question sane at first place? That would be a pretty big mess, I think. People would add your patch to our BSD code and it would be GPL. It could be done, of course. -- 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 and multithreading
Let me add one more thing on this "thread". This is one email in a long list of "Oh, gee, you aren't using that wizz-bang new sync/thread/aio/raid/raw feature" discussion where someone shows up and wants to know why. Does anyone know how to address these, efficiently? If we discuss it, it ends up causing a lot of effort on our part for the requestor to finally say, "Oh, gee, I didn't realize that." It isn't our job to explain that wizz-bang isn't always great. Maybe that should be our reply, "Wizz-bang isn't always great" and leave it at that. Of course, some will leave thinking we are just idiots, but then again, it takes one to know one. It is sort of like walking into a chess match and asking Bobby Fisher why he didn't move that pawn. :-) --- Anuradha Ratnaweera wrote: > On Wed, Oct 16, 2002 at 03:40:47PM +1000, Gavin Sherry wrote: > > On Wed, 16 Oct 2002, Anuradha Ratnaweera wrote: > > > > > And a minor question is wheter it is legal to keep the _changes_ in such > > > a project GPL? > > > > Do you mean 'relicence the forked copy'? > > Nope. To keep the `original' code licence as it is and to release the > changes GPL? Is the question sane at first place? > > Anuradha > > -- > > Debian GNU/Linux (kernel 2.4.18-xfs-1.1) > > You got to be very careful if you don't know where you're going, > because you might not get there. > -- Yogi Berra > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- 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 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] droped out precise time calculations in src/interfaces/libpq/fe-connect.c
Bruce Momjian wrote: > Well, the fact you see a change of 0.0002 is significant. Let me add > that in the old code there was only one time() call _in_ the loop, while > now, there are two, so I can easily see there are several additional > time() calls. Did you put your calls in the while loop? > Not the first time, but I moved it around (into the loop in connectDBComplete, and just before select() in pqWaitTimed) and it didn't seem to make any difference. Then I removed it entirely again, and still got 0.0089 seconds average. So, at the least, 0.0002 worth of variation seems to be related to the development machine itself. Joe p.s. The good news is that with tens of thousands more tests at 1 second timeout, still zero connection failures! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgresql and multithreading
On Wed, Oct 16, 2002 at 01:51:28AM -0400, Bruce Momjian wrote: > > Let me add one more thing on this "thread". This is one email in a > long list of "Oh, gee, you aren't using that wizz-bang new > sync/thread/aio/raid/raw feature" discussion where someone shows up > and wants to know why. Does anyone know how to address these, > efficiently? If somebody pops up asks such dumb questions without even looking at the FAQ, it is bad, if not idiotic, because it takes useful time away from the developers. But my question was not `why don't you implement this feature?`, but `do you have plans to implement this feature in the future?', and in the open source spirit of `if something is not there, go implement it yourself - without troubling developers' ;) Also, I have read the section 1.9 of the developers FAQ (Why don't we use threads in the backend?) long, long ago. > If we discuss it, it ends up causing a lot of effort on our part for > the requestor to finally say, "Oh, gee, I didn't realize that." Please don't. See the "NB" at end of my first mail of this thread. Anuradha -- Debian GNU/Linux (kernel 2.4.18-xfs-1.1) QOTD: "I'll listen to reason when it comes out on CD." ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vacuum improvement
On Wed, 2002-10-16 at 05:22, Gavin Sherry wrote: > Hi all, > > I'm thinking that there is an improvement to vacuum which could be made > for 7.4. VACUUM FULLing large, heavily updated tables is a pain. There's > very little an application can do to minimise dead-tuples, particularly if > the table is randomly updated. Wouldn't it be beneficial if VACUUM could > have a parameter which specified how much of the table is vacuumed. That > is, you could specify: > > VACUUM FULL test 20 precent; What about VACUUM FULL test WORK 5 SLEEP 50; meaning to VACUUM FULL the whole table, but to work in small chunks and relaese all locks and let others access the tables between these ? You could even fire up a separate thread to VACUUM [FULL] test WORK 5 SLEEP 50 CONTINUOUS; To keep vacuuming a heavily updated table. -- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Postgresql and multithreading
On 16 Oct 2002 at 1:25, Bruce Momjian wrote: > Anuradha Ratnaweera wrote: > > Thanks, Bruce. But what I want to know is whether multithreading is > > likely to get into in postgresql, say somewhere in 8.x, or even in 9.x? > > (as they did with Apache). Are there any plans to do so, or is postgres > > going to remain rather a multi-process application? > It may be optional some day, most likely for Win32 at first, but we see > little value to it on most other platforms; of course, we may be wrong. > I am also not sure if it is a big win on Apache either; I think the Well, I have done some stress testing on 1.3.26 and 2.0.39. Under same hardware and network setup and same test case, 1.3.26 maxed at 475-500 requests/sec and 2.0.39 gave flat 800 requests/sec. Yes, under light load, there is hardly any difference. But Apache2 series is definitely an improvement. > jury is still out on that one, hence the slow adoption of 2.X, and we > don't want to add threads and make a mess of the code or slow it down, > which does often happen. Well, slow adoption rate is attributed to 'apache 1.3.x is good enough for us' syndrome, as far as I can see from news. Once linux distros start shipping with apache 2.x series *only*, the upgrade cycle will start rolling, I guess. Bye Shridhar -- Programming Department: Mistakes made while you wait. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgresql and multithreading
On 16 Oct 2002 at 15:40, Gavin Sherry wrote: > > In that case, I wonder if it is worth folking a new project to add > > threading support to the backend? Of course, keeping in sync with the > > original would be lot of work. > > http://sourceforge.net/projects/mtpgsql Last discussion that happened for multithreading was not to add threads per connection like mysql, but to split tasks between threads so that IO blocking and efficiently using SMP abilities would be taken care off IIRC.. One thread per connection isn't going to change much, at least for mainstream postgresql. For embedded, it might be a necessity.. Bye Shridhar -- QOTD: "You're so dumb you don't even have wisdom teeth." ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Postgres-based system to run .org registry?
On 15 Oct 2002 at 18:19, Bruce Momjian wrote: > We are showing up in places I never expected: .org registry, tons of > books, conventions, everywhere. It is just a wave that keeps getting > bigger and bigger. I am starting to imagine what Linus felt seeing > Linux take off; you just sit around and wonder how it is all happening, > and of course, it is happening because you offer a unique value to > users, and their sharing with others just makes it continue to grow. Sigh.. I wish enough people could understand difference between cost and value and more importantly apply the understanding while making a judgement.. Bye Shridhar -- I'm a soldier, not a diplomat. I can only tell the truth. -- Kirk, "Errand of Mercy", stardate 3198.9 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
