Re: [HACKERS] New Contrib Build?
./configure --with-perl --with-dblink --with-newsysviews This would make installing and upgrading BSD ports easier. David ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Can we get patents?
That depends; is the SFLC offering to pay for the patent applications? Last I checked, it was somewhere around $6000 per patent. Nolo press (www.nolo.com) sells a book on patents. Many people file their own patent applications successfully. The cost is less that $1000. David ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Call for porting reports
Postgresql-8.0.0rc1 hardware: HP Dual PPro os: Linux Slackware 10.0.0 kernel: 2.6.9-ac16 SMP gcc: 3.3.4 configure: ./configure --prefix=/usr/local/pgsql --with-tcl --with-perl --with-x --enable-syslog --with-openssl --with-pgport=5432 --with-odbc --enable-thread-safety 8.0.0beta4 was installed make check failed the first time with a cannot set locale error. I installed 8.0.0rc1, make check was successful == All 96 tests passed. == Thanks for a great database, David Walker ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [MLIST] Re: [HACKERS] location of the configuration files
In reference to determining what port postgres or any program is listening on
On my Redhat Linux machines
netstat --inet -nlp
when run as root
produces a nice list of all programs listening on the network with IP and port
number the process is listening on, the name of the process and the pid.
The environment used to start each of these postmasters can be found at
cat /proc/${POSTMASTER-PID}/environ | tr \000 \n
I'm not arguing one way or the other on your issue, just hope these tips make
the black magic a little easier to use.
On Friday 14 February 2003 04:58 am, Kevin Brown wrote:
Now let's repeat that scenario, except that instead of seeing one
postmaster process, you see five. And they all say
/usr/bin/postmaster in the ps listing. No arguments to clue you
in or anything, as before. You might be able to figure out where one
of them is going by looking at /etc/postgresql, but what about the
rest? Now you're stuck unless you want to do a find (time consuming
and I/O intensive -- a good way to slow the production database down a
bit), or you're knowledgeable enough to use 'lsof' or black magic like
digging into kernel memory to figure out where the config files and
data directories are, or you have enough knowledge to pore through the
startup scripts and understand what they're doing.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Bad download link
Trying to download 7.3.1 today I found a bad link on http://www.us.postgresql.org/sitess.html The ftp link goes to 7.3 and the http link returns a 404 not found. I did successfully download 7.3.1 via the HTTP access to the main repository link. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [MLIST] Re: [mail] Re: [HACKERS] Big 7.4 items - Replication
Another concern I have with multi-master systems is what happens if the network splits in 2 so that 2 master systems are taking commits for 2 separate sets of clients. It seems to me that to re-sync the 2 databases upon the network healing would be a very complex task or impossible task. On Sunday 15 December 2002 04:16 am, Al Sutton wrote: Many thanks for the explanation. Could you explain to me where the order or the writeset for the following scenario; If a tranasction takes 50ms to reach one database from another, for a specific data element (called X), the following timeline occurs at 0ms, T1(X) is written to system A. at 10ms, T2(X) is written to system B. Where T1(X) and T2(X) conflict. My concern is that if the Group Communication Daemon (gcd) is operating on each database, a successful result for T1(X) will returned to the client talking to database A because T2(X) has not reached it, and thus no conflict is known about, and a sucessful result is returned to the client submitting T2(X) to database B because it is not aware of T1(X). This would mean that the two clients beleive bothe T1(X) and T2(X) completed succesfully, yet they can not due to the conflict. Thanks, Al. - Original Message - From: Darren Johnson [EMAIL PROTECTED] To: Al Sutton [EMAIL PROTECTED] Cc: Bruce Momjian [EMAIL PROTECTED]; Jan Wieck [EMAIL PROTECTED]; [EMAIL PROTECTED]; PostgreSQL-development [EMAIL PROTECTED] Sent: Saturday, December 14, 2002 6:48 PM Subject: Re: [mail] Re: [HACKERS] Big 7.4 items - Replication b) The Group Communication blob will consist of a number of processes which need to talk to all of the others to interrogate them for changes which may conflict with the current write that being handled and then issue the transaction response. This is basically the two phase commit solution with phases moved into the group communication process. I can see the possibility of using solution b and having less group communication processes than databases as attempt to simplify things, but this would mean the loss of a number of databases if the machine running the group communication process for the set of databases is lost. The group communication system doesn't just run on one system. For postgres-r using spread there is actually a spread daemon that runs on each database server. It has nothing to do with detecting the conflicts. Its job is to deliver messages in a total order for writesets or simple order for commits, aborts, joins, etc. The detection of conflicts will be done at the database level, by a backend processes. The basic concept is if all databases get the writesets (changes) in the exact same order, apply them in a consistent order, avoid conflicts, then one copy serialization is achieved. (one copy of the database replicated across all databases in the replica) I hope that explains the group communication system's responsibility. Darren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] Boolean casting in 7.3 - changed?
Does this mean that in the future '342' may not be valid as an insert into a numeric field and that we should be using 342 instead? On Wednesday 27 November 2002 05:07 pm, (Via wrote: Ian Barwick writes: Casting integers to boolean (for example, 0::bool) is no longer allowed, use '0'::bool instead. This advice would probably only cause more confusion, because we are now moving into the direction that character strings are no longer acceptable as numeric data. Note that x 0 is also a perfectly good way to convert integers to booleans, and a more portable one at that. Finally, you can always create your own cast. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Turning the PLANNER off
Does PREPARE turn select * from mytable into select mytable.field1,mytable.field2 from mynamespace.mytable ? I was looking for this functionality for one of my projects so I'm curious. On Monday 28 October 2002 06:55 pm, (Via wrote: That is a good question. The planner does more than just analyse the query. It generates the Plan used by the executor, so that can't be removed. It is always a pain when the optimizer/planner takes longer than the executor. We do have PREPARE/EXECUTE in 7.3beta for you to use. --- Ben McMahan wrote: I'm looking at different ways of optimizing queries with a large number of joins. I write the same query in a number of different ways and compare the running times. Now the problem is I do not want the optimizer changing the queries. So I explicit state the order of the joins in the FROM clause. I also turn off everything I can except for one type of join (say hash join), and I've turned off geqo. But I find that the PLANNER still takes an enormous amount of time for some queries. It doesn't look like the Planner is actually optimizing (changing) anything, but just in case, I was wondering if there was a way to turn off the PLANNER. Note, when I say an enormous amount of time, I mean at least double the time the EXECUTOR takes to actually answer the query. Thanks for your help, Ben McMahan ps. here is a small example of what my queries look like (so you can see if there is something else it might be deciding on): SELECT DISTINCT c0.x1 , c1.x2 , c0.x3 , c0.x4 , c2.x5 FROM r1 c4 (x4,x2,x5) JOIN (r0 c3 (x2,x3,x5) JOIN (r2 c2 (x3,x1,x5) JOIN (r1 c1 (x4,x1,x2) JOIN r1 c0 (x1,x3,x4) ON ( c0.x4 = c1.x4 AND c0.x1 = c1.x1 )) ON ( c0.x3 = c2.x3 AND c0.x1 = c2.x1 )) ON ( c1.x2 = c3.x2 AND c0.x3 = c3.x3 AND c2.x5 = c3.x5 )) ON ( c0.x4 = c4.x4 AND c1.x2 = c4.x2 AND c2.x5 = c4.x5 ); A quick reminder, FROM r1 c4 (x4,x2,x5) just renames a table r1 into c4 where it also renames the columns to x4, x2, and x5 respectively. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vacuum improvement
Vacuum full locks the whole table currently. I was thinking if you used a similar to a hard drive defragment that only 2 rows would need to be locked at a time. When you're done vacuum/defragmenting you shorten the file to discard the dead tuples that are located after your useful data. There might be a need to lock the table for a little while at the end but it seems like you could reduce that time greatly. I had one table that is heavily updated and it grew to 760 MB even with regular vacuuming. A vacuum full reduced it to 1.1 MB. I am running 7.2.0 (all my vacuuming is done by superuser). On Wednesday 16 October 2002 09:30 am, (Via wrote: On Wed, 2002-10-16 at 02:29, Gavin Sherry wrote: On 16 Oct 2002, Hannu Krosing wrote: 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 ? Great idea. I think this could work as a complement to the idea I had. To answer Tom's question, how would we know what we've vacuumed, we could store the range of tids we've vacuumed in pg_class. Or, we could store the block offset of where we left off vacuuming before and using stats, run for another X% of the heap. Is this possible? Why couldn't you start your % from the first rotten/dead tuple? Just reading through trying to find the first tuple to start counting from wouldn't hold locks would it? That keeps you from having to track stats and ensures that X% of the tuples will be vacuumed. Greg ---(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] Virus Emails
That may be true with some variants. However my mail server has rejected the relay of several mails sent pretending to be from me (envelope sender) to other parties and I think these could be klez variants or another such virus. Since my server rejected them I cannot be sure of the contents. On Sunday 28 July 2002 04:06 am, Curt Sampson wrote: On Sat, 27 Jul 2002, Tom Lane wrote: One of the nastier aspects of the Klez virus However, even a trivial look at the detail mail headers (Received: etc) will convince you that the spam did not originate from the claimed From: address. If you care to post a few sets of complete headers, we can probably triangulate pretty quickly on the virus-infected loser who's originating these messages. It appears to me that the envelope sender is not forged by Klez.H, assuming that that's the virus I'm getting all the time. So you could check for the Return-Path: header, or maybe From (note: no colon) if you're using a Berkeley-mailbox style system, and find out the e-mail address of the real sender. cjs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Suggestions please: names for function cachability attributes
My 2 cents. Level 1. with (isCachableStatic) Level 2. with (isCachableDynamic) Level 3. default In my mind (isCachable) sounds like level 1 On Tuesday 02 April 2002 03:40 pm, Tom Lane wrote: Since I'm about to have to edit pg_proc.h to add a namespace column, I thought this would be a good time to revise the current proiscachable column into the three-way cachability distinction we've discussed before. But I need some names for the values, and I'm not satisfied with the ideas I've had so far. To refresh people's memory: what we want is to be able to distinguish between functions that are: 1. Strictly cachable (a/k/a constant-foldable): given fixed input values, the same result value will always be produced, for ever and ever, amen. Examples: addition operator, sin(x). Given a call of such a function with all-constant input values, the system is entitled to fold the function call to a constant on sight. 2. Cachable within a single command: given fixed input values, the result will not change if the function were to be repeatedly evaluated within a single SQL command; but the result could change over time. Examples: now(); datetime-related operations that depend on the current timezone (or other SET-able variables); any function that looks in database tables to determine its result. 3. Totally non-cachable: result may change from one call to the next, even within a single SQL command. Examples: nextval(), random(), timeofday(). (Yes, timeofday() and now() are in different categories. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-datet ime.html#FUNCTIONS-DATETIME-CURRENT) Currently the system can only distinguish cases 1 and 3, so functions that are really case 2 have to be labeled as case 3; this prevents a lot of useful optimizations. In particular, it is safe to use expressions involving only case-1 and case-2 functions as indexscan conditions, whereas case-3 functions cannot be optimized into an indexscan. So this is an important fix to make. BTW, because of MVCC semantics, case 2 covers more ground than you might think. We are interested in functions whose values cannot change during a single scan, ie, while the intra-transaction command counter does not increment. So functions that do SELECTs are actually guaranteed to be case 2, even if stuff outside the function is changing the table being looked at. My problem is picking names for the three categories of functions. Currently we use with (isCachable) to identify category 1, but it seems like this name might actually be more sensible for category 2. I'm having a hard time picking simple names that convey these meanings accurately, or even with a reasonable amount of suggestiveness. Comments, ideas? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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] How to give permission to others on data directory
Create a separate user and both of you use sudo to start the database. If you're insistent on keeping yourself owner of the data then use sudo to give permission to your project partner to start the database. On Sunday 31 March 2002 05:49 am, Amit Khare wrote: Hi Peter, Thank you very much for your reply . However the problem is that we don't want to create separate user for server. If initdb takes my login name and makes me owner of the data directory then how should I be able to give permission to other users in this case my project partner? Thanks again Regards Amit Khare - Original Message - From: Peter Eisentraut [EMAIL PROTECTED] To: Amit Khare [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, March 31, 2002 4:20 AM Subject: Re: [HACKERS] How to give permission to others on data directory Amit Khare writes: (1) Actually we are doing project on PostgreSQL in group of two. We installed individual copy of PostgreSQL into our group directory. (2) When I created data directory and ran initdb it makes me( takes my login name ) as the owner of data directory. (3) The problem is that now my partner cannot start the postmaster since he does not have right on the data directory. Further one cannot set right on the data directory more than 700 . (4) For time being we hacked the postmaster.c and commented the line starting from 318 which actually test the permission on data directory. Then my partner was able to run the postmaster since now I gave him rights(770) on the data directory(But changed rights on postgresql.conf file to 744). (5) Is there a clean way by which my partner can start postmaster on data directory created by me. Create a separate user for the server and give yourself and your partner access to it. -- 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 _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster --- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
