[HACKERS] number of attributes in page files?
Is it possible to get rid of the t_natts fields in the tuple header? Is this field only for alter table add/drop support? Then it might possible to get rid of it and put the t_natts field in the page header, not the tuple header, if it can be assured that when updating/inserting records only a compatible (a page file with the same number of attributes) page file is used. Especially master-detail tables would profit from this, reducing the tuple overhead by another 9%. Might this be possible? Regards, Mario Weilguni ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Suggestion: Helping the optimizer
I guess we had this discussion before but I have just gone through the
general list and I have encountered a problem I had a least VERY often
before.
Sometimes the planner does not find the best way through a query.
Looking at the problem of query optimization it is pretty obvious that
things like that can happen. The planner is a wonderful piece of
software and I have a high esteem of people working on it.
In some cases the planner fails because it is impossible to optimize
every query coming along - this is a natural thing.
In case of very complex SQL statements it would be wonderful to have a
command which allows the user to turn an INDEX on or off temporarily.
This would solve 90% of all problems people have with the planner.
People say that 10% of all queries cause 90% of the load. If we could
help those 10% we could gain A LOT of performance with very little effort.
Improving other things help of lot as well but in some cases the planner
decides whether a query can be done or not. YES/NO is a much bigger
problem than 5% faster or not.
Just have a look at a query like that:
$database-dbi_select(SELECT a.code, b.code, t_gruppe.id, t_strukturtyp.id,
t_struktur.id,t_struktur.oid
FROM t_master, t_struktur, t_strukturtyp, t_gruppenelement,
t_gruppe, t_text AS a, t_text AS b, t_betriebdetail,
t_strukturbetrieb
WHERE t_master.master_id = '$sportort'
AND t_master.slave_id = t_struktur.id
AND t_struktur.typid = t_strukturtyp.id
AND t_strukturtyp.kommentar = 'betrieb'
AND get_bezahlt(t_struktur.id) = 't'
AND t_strukturtyp.id = t_gruppenelement.suchid
AND t_gruppenelement.icode = 'strukturtyp'
AND t_gruppenelement.gruppeid = t_gruppe.id
AND a.suchid = t_gruppe.id
AND a.icode = 'gruppe'
AND a.sprache = $session{lang}
AND a.texttyp IS NULL
AND b.suchid = t_struktur.id
AND b.icode = 'struktur'
AND b.sprache = $session{lang}
AND b.texttyp IS NULL
AND t_gruppe.sortierung = getmin('basic')
AND t_gruppe.sortierung = getmax('basic')
AND t_struktur.id IN (
SELECT DISTINCT a.refid
FROM t_punkte AS a,t_text AS
b,t_struktur AS c
WHERE a.refid=b.suchid
AND a.icode='struktur'
AND b.icode='struktur'
AND a.refid=c.id
AND b.sprache=1
AND a.bildid='$picdata[0]'
AND b.texttyp IS NULL )
AND t_betriebdetail.von now()
AND t_betriebdetail.strukturbetriebid =
t_strukturbetrieb.betriebid
AND t_strukturbetrieb.strukturid = t_struktur.id
ORDER BY t_gruppe.sortierung, t_strukturtyp.sortierung,
t_betriebdetail.leistung , b.code);
This has been taken from a real world application I have written a few
weeks ago (unfortunately it is German).
In this case the planner does it absolutely right. There are subqueries
and functions and many other ugly things for the planner but it works.
What should I do if it doesn't work?
Well, I could turn seq scans off globally even if I knew that there is
just one table causing high execution times. People can easily imagine
that a bad execution plan can lead to really bad performance -
especially when there are millions of records around. By tweaking the
optimizer a little we could gain 100% percents of performance. (idx scan
vs. nested loop and seq scan or something like that).
I guess the patch for this tweaking stuff could be fairly easy.
Currently I am abusing system tables to get the problem fixed (which is
bad for other queries of course). Running VACUUM is not that funny if
the data in the system tables is mistreated.
Concern:
People might think this is ANSI: I know that this can be a problem but
is it better if people start abusing system tables or think that
PostgreSQL is bad or slow?
Take the time and fix the planner: I can fully understand this concern.
However, there is no way to fix the optimizer to do it right in every
case. The planner is really good but I am talking about 3% of all those
queries out there - unfortunately they cause 90% of the problems people
have.
I have taken this query so that people can see that the planner is doing
good work but people should also think of a situation where a query like
that can cause severe head ache ...
maybe this problem should be discussed from time to time.
Best regards,
Hans
http://kernel.cybertec.at
---(end
[HACKERS] MySQL vs PostgreSQL.
Check out: http://www.mysql.com/doc/en/MySQL-PostgreSQL_features.html MySQL AB compares MySQL with PostgreSQL. Quoted from one page Because we couldn't get vacuum() to work reliable with PostgreSQL 7.1.1, we haven't been able to generate a --fast version of the benchmarks yet (where we would have done a vacuum() at critical places in the benchmark to get better performance for PostgreSQL). We will do a new run of the benchmarks as soon as the PostgreSQL developers can point out what we have done wrong or have fixed vacuum() so that it works again. and from another. Drawbacks with PostgreSQL compared to MySQL Server: VACUUM makes PostgreSQL hard to use in a 24/7 environment. They also state that they have more sophisticated ALTER TABLE... Only usable feature in their ALTER TABLE that doesn't (yet) exist in PostgreSQL was changing column order (ok, the order by in table creation could be nice), and that's still almost purely cosmetic. Anyway, I could have used that command yesterday. Could this be added to pgsql. MySQL supports data compression between front and back ends. This could be easily implemented, or is it already supported? I think all the other statements were misleading in the sense, that they compared their newest product with PostgreSQL 7.1.1. There's also following line: PostgreSQL currently offers the following advantages over MySQL Server: After which there's only one empty line. Note that because we know the MySQL road map, we have included in the following table the version when MySQL Server should support this feature. Unfortunately we couldn't do this for previous comparisons, because we don't know the PostgreSQL roadmap. They could be provided one... ;-) Upgrading MySQL Server is painless. When you are upgrading MySQL Server, you don't need to dump/restore your data, as you have to do with most PostgreSQL upgrades. Ok... this is true, but not so hard - yesterday I installed 7.3b2 onto my linux box. Of course PostgreSQL isn't yet as fast as it could be. ;) -- Antti Haapala ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Peer to peer replication of Postgresql databases
On 11 Oct 2002 at 16:16, Anuradha Ratnaweera wrote: Hi all, I am trying to add some replication features to postgres (yes, I have already looked at ongoing work), in a peer to peer manner. The goal is to achive `nearly complete fault tolerence' by replicating data. Sounds a lot like usogres. You got it running. (I never had a chance.) I would like to hear how it compares against it. Can anybody comment how maintained usogres is. It covers an important area of replication but I am not sure how maintained that is. If it is not, I suggest we pick it up and finish it. HTH Bye Shridhar -- You go slow, be gentle. It's no one-way street -- you know how youfeel and that's all. It's how the girl feels too. Don't press. Ifthe girl feels anything for you at all, you'll know. -- Kirk, Charlie X, stardate 1535.8 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] number of attributes in page files?
Am Freitag, 11. Oktober 2002 14:12 schrieb Tom Lane: Mario Weilguni [EMAIL PROTECTED] writes: Is it possible to get rid of the t_natts fields in the tuple header? Is this field only for alter table add/drop support? Only? A lot of people consider that pretty important ... With only I mean it's an administrative task which requires operator intervenation anyways, and it's a seldom needed operation which may take longer, when queries become faster. But removing 2 bytes isn't going to save anything, on most machines, because of alignment considerations. ok, I did not consider alignment, but the question remains, is this easily doable? Especially because only one another byte has to be saved for real saving on many architectures, which is t_hoff. IMO t_hoff is not useful because it can be computed easily. This would give 20 byte headers instead of 23 (24) bytes as it's now. This is 17% saved, and if it's not too complicated it might be worth to consider. Best regards, Mario Weilguni ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MySQL vs PostgreSQL.
On 11 Oct 2002 at 16:20, Antti Haapala wrote: Check out: http://www.mysql.com/doc/en/MySQL-PostgreSQL_features.html Well, I guess there are many threads on this. You can dig around archives.. Upgrading MySQL Server is painless. When you are upgrading MySQL Server, you don't need to dump/restore your data, as you have to do with most PostgreSQL upgrades. Ok... this is true, but not so hard - yesterday I installed 7.3b2 onto my linux box. Well, that remains as a point. Imagine a 100GB database on a 150GB disk array. How do you dump and reload? In place conversion of data is an absolute necessary feature and it's already on TODO. Of course PostgreSQL isn't yet as fast as it could be. ;) Check few posts I have made in last three weeks. You will find that postgresql is fast enough to surpass mysql in what are considered as mysql strongholds. Of course it's not a handy win but for sure, postgresql is not slow. And for vacuum thing, I have written a autovacuum daemon that can automatically vacuum databases depending upon their activity. Check it at gborg.postgresql.org. (I can't imagine this as an advertisement of myself but looks like the one) Let thread be rested. Postgresql certaily needs some maketing hand but refuting claims in that article is not the best way to start it. I guess most hackers would agree with this.. Bye Shridhar -- Cat, n.:Lapwarmer with built-in buzzer. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Peer to peer replication of Postgresql databases
On 11 Oct 2002 at 8:30, Greg Copeland wrote: I'd be curious to hear in a little more detail what constitutes not good for postgres on a mosix cluster. On Fri, 2002-10-11 at 06:15, Anuradha Ratnaweera wrote: On Fri, Oct 11, 2002 at 04:29:53PM +0530, Shridhar Daithankar wrote: Have already tested postgres on a mosix cluster, and as expected results are not good. (although mosix does the correct thing in keeping all the database backend processes on one node). Well, I guess in kind of replication we are talking here, the performance will be enhanced only if separate instances of psotgresql runs on separate machine. Now if mosix kernel applies some AI and puts all of them on same machine, it isn't going to be any good for the purpose replication is deployed. I guess that's what she meant.. Bye Shridhar -- User n.:A programmer who will believe anything you tell him. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MySQL vs PostgreSQL.
On Fri, 2002-10-11 at 08:20, Antti Haapala wrote: Quoted from one page Because we couldn't get vacuum() to work reliable with PostgreSQL 7.1.1, I have little respect for the MySQL advocacy guys. They purposely spread misinformation. They always compare their leading edge alpha software against Postgres' year+ old stable versions. In some cases, I've seen them compare their alpha (4.x) software against 7.0. Very sad that these people can't even attempt to be honest. In the case above, since they are comparing 4.x, they should be comparing it to 7.x at least. It's also very sad that their testers don't seem to even understand something as simple as cron. If they can't understand something as simple as cron, I fear any conclusions they may arrive at throughout their testing (destined to be incorrect/invalid). MySQL supports data compression between front and back ends. This could be easily implemented, or is it already supported? Mammoth has such a feature...or at least it's been in development for a while. If I understood them correctly, it will be donated back to core sometime in the 7.5 or 7.7 series. Last I heard, their results were absolutely wonderful. I think all the other statements were misleading in the sense, that they compared their newest product with PostgreSQL 7.1.1. Ya, historically, they go out of their way to ensure unfair comparisons. I have no respect for them. They could be provided one... ;-) In other words, they need a list of features that they can one day hope to add to MySQL. Upgrading MySQL Server is painless. When you are upgrading MySQL Server, you don't need to dump/restore your data, as you have to do with most PostgreSQL upgrades. Ok... this is true, but not so hard - yesterday I installed 7.3b2 onto my linux box. Of course PostgreSQL isn't yet as fast as it could be. ;) I consider this par for the course. This is something I've had to do with Sybase, Oracle and MSSQL. Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Peer to peer replication of Postgresql databases
Well, not scalable doesn't have to mean not good. That's why I asked. Considering this is one of the problems with mosix clusters (process migration and associated restrictions) and the nature of PostgreSQL's implementation I'm not sure what other result may of been expected. Because of that, I wasn't sure if something else was being implied. Greg On Fri, 2002-10-11 at 08:40, Shridhar Daithankar wrote: On 11 Oct 2002 at 8:30, Greg Copeland wrote: I'd be curious to hear in a little more detail what constitutes not good for postgres on a mosix cluster. On Fri, 2002-10-11 at 06:15, Anuradha Ratnaweera wrote: On Fri, Oct 11, 2002 at 04:29:53PM +0530, Shridhar Daithankar wrote: Have already tested postgres on a mosix cluster, and as expected results are not good. (although mosix does the correct thing in keeping all the database backend processes on one node). Well, I guess in kind of replication we are talking here, the performance will be enhanced only if separate instances of psotgresql runs on separate machine. Now if mosix kernel applies some AI and puts all of them on same machine, it isn't going to be any good for the purpose replication is deployed. I guess that's what she meant.. Bye Shridhar -- User n.: A programmer who will believe anything you tell him. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] MySQL vs PostgreSQL.
Rod Taylor wrote: On Fri, 2002-10-11 at 09:20, Antti Haapala wrote: Check out: http://www.mysql.com/doc/en/MySQL-PostgreSQL_features.html MySQL AB compares MySQL with PostgreSQL. I wouldn't look too far into these at all. I've tried to get ' as identifier quote (ANSI SQL) ' corrected on the crash-me pages for us a couple of times (they say we don't support it for some reason). It's once again the typical MySQL propaganda. As usual they compare a future version of MySQL against an old release of PostgreSQL. And they just compare on buzzword level. Do their foreign keys have referential actions and deferrability? Is log based master slave replication all there can be? And surely do we have something that compares to *their* roadmap. That they cannot find it is because it's named HISTORY. 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Peer to peer replication of Postgresql databases
[ pgsql-patches removed from Cc: list ] Anuradha Ratnaweera [EMAIL PROTECTED] writes: I am trying to add some replication features to postgres (yes, I have already looked at ongoing work), in a peer to peer manner. Did you look at the research behind Postgres-R, and the pgreplication stuff? - When a frontend process sends a read query, each backend process does that from its own data area. Surely that's not correct -- a SELECT can be handled by *any one* node, not each and every one, right? - There are two types of write queries. Postmasters use seperate communication channels for each. One is the sequencial channel which carries writes whose order is important, and the non-sequencial channel carries write queries whose order is not important. How do you distinguish between these? Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(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
[HACKERS] move 0 behaviour
Currently there is a TODO list item to have move 0 not position to the end of the cursor. Moving to the end of the cursor is useful, can we keep the behaviour and change it to move end, or just leave it the way it is? Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [JDBC] Out of memory error on huge resultset
Hello, Does it mean that psql uses cursors ? regards Haris Peco On Friday 11 October 2002 05:58 pm, Dave Cramer wrote: This really is an artifact of the way that postgres gives us the data. When you query the backend you get *all* of the results in the query, and there is no indication of how many results you are going to get. In simple selects it would be possible to get some idea by using count(field), but this wouldn't work nearly enough times to make it useful. So that leaves us with using cursors, which still won't tell you how many rows you are getting back, but at least you won't have the memory problems. This approach is far from trivial which is why it hasn't been implemented as of yet, keep in mind that result sets support things like move(n), first(), last(), the last of which will be the trickiest. Not to mention updateable result sets. As it turns out there is a mechanism to get to the end move 0 in 'cursor', which currently is being considered a bug. Dave On Fri, 2002-10-11 at 11:44, Doug Fields wrote: At 08:27 AM 10/11/2002, snpe wrote: Barry, Is it true ? I create table with one column varchar(500) and enter 1 milion rows with length 10-20 character.JDBC query 'select * from a' get error 'out of memory', but psql not. I insert 8 milion rows and psql work fine yet (slow, but work) The way the code works in JDBC is, in my opinion, a little poor but possibly mandated by JDBC design specs. It reads the entire result set from the database backend and caches it in a horrible Vector (which should really be a List and which should at least make an attempt to get the # of rows ahead of time to avoid all the resizing problems). Then, it doles it out from memory as you go through the ResultSet with the next() method. I would have hoped (but was wrong) that it streamed - WITHOUT LOADING THE WHOLE THING - through the result set as each row is returned from the backend, thus ensuring that you never use much more memory than one line. EVEN IF you have to keep the connection locked. The latter is what I expected it to do. The former is what it does. So, it necessitates you creating EVERY SELECT query which you think has more than a few rows (or which you think COULD have more than a few rows, few being defined by our VM memory limits) into a cursor based query. Really klugy. I intend to write a class to do that for every SELECT query for me automatically. Cheers, Doug In C library is 'execute query' without fetch - in jdbc execute fetch all rows and this is problem - I think that executequery must prepare query and fetch (ResultSet.next or ...) must fetch only fetchSize rows. I am not sure, but I think that is problem with jdbc, not postgresql Hackers ? Does psql fetch all rows and if not how many ? Can I change fetch size in psql ? CURSOR , FETCH and MOVE isn't solution. If I use jdbc in third-party IDE, I can't force this solution regards On Thursday 10 October 2002 06:40 pm, Barry Lind wrote: Nick, This has been discussed before on this list many times. But the short answer is that that is how the postgres server handles queries. If you issue a query the server will return the entire result. (try the same query in psql and you will have the same problem). To work around this you can use explicit cursors (see the DECLARE CURSOR, FETCH, and MOVE sql commands for postgres). thanks, --Barry Nick Fankhauser wrote: I'm selecting a huge ResultSet from our database- about one million rows, with one of the fields being varchar(500). I get an out of memory error from java. If the whole ResultSet gets stashed in memory, this isn't really surprising, but I'm wondering why this happens (if it does), rather than a subset around the current record being cached and other rows being retrieved as needed. If it turns out that there are good reasons for it to all be in memory, then my question is whether there is a better approach that people typically use in this situation. For now, I'm simply breaking up the select into smaller chunks, but that approach won't be satisfactory in the long run. Thanks -Nick --- -- - Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko Co. Software Consulting Services http://www.ontko.com/ ---(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
[HACKERS] Client-side merge string sorting
Hello, I sometimes need to perform client-side merges, sometimes between two tables on the same database, sometimes between two different databases. When the merge key is numeric all goes well but, when the merge key is a string a problem arises: string comparison operators often behave differently between the database(s) and the client's language. Sometimes it is due to the locale settings, sometimes is the particular implementation of the operator, as a matter of facts, I cannot trust the strings comparison operators. Si, the question is how client-side merge should be done... - Perform the sorting locally... only one operator... maybe suboptimal sorting... etc - Compare the strings hex-encoded: overhead apart, I found myself unable to use encode(..) function on PostgreSQL since it accepts only BYTEA data and text isn't castable to bytea. - Invent a new operator whose behaviour would be always consistent, locale-indepentent... (like the very-first C's strcmp). Which do you think should be the correct approach ? Thanks in advance! Best regards! -- Daniele Orlandi Planet Srl ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Peer to peer replication of Postgresql databases
On Fri, Oct 11, 2002 at 04:04:29PM +0530, Shridhar Daithankar wrote: On 11 Oct 2002 at 16:29, Anuradha Ratnaweera wrote: On Fri, Oct 11, 2002 at 03:54:15PM +0530, Shridhar Daithankar wrote: I will look at it, too. Thanks for the link. In some cases, starting anew is faster than learning unmaintained existing code. Ok. Checked out what usogres is. It is not what I want. I don't want a static `main database'. It should simply a cluster of them - just like a set of Raid-0 disks, may be with a tempory controller for some tasks. Also, as a matter of fact, usogres is not unmaintained code. While that's true, usogres code is just few files. I wouldn't take more than half an hour to read up the things. And besides it contain postgresql protocol implementation necessary which would take some time to test and debug, Great. I will look into this over the weekend. And it's in C++. I like that..;-) And I DON'T like that ;) Anuradha -- Debian GNU/Linux (kernel 2.4.18-xfs-1.1) QOTD: I ain't broke, but I'm badly bent. ---(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] Peer to peer replication of Postgresql databases
On Fri, Oct 11, 2002 at 04:29:53PM +0530, Shridhar Daithankar wrote: Well, I don't think adding support for multiple slaves to usogres would be that problematic. Of course if you want to load balance your application queries, application has to be aware of that. I will not do sending requests to a mosix cluster anyway. Have already tested postgres on a mosix cluster, and as expected results are not good. (although mosix does the correct thing in keeping all the database backend processes on one node). Anuradha -- Debian GNU/Linux (kernel 2.4.18-xfs-1.1) Remember: Silly is a state of Mind, Stupid is a way of Life. -- Dave Butler ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Peer to peer replication of Postgresql databases
On 11 Oct 2002 at 16:39, Anuradha Ratnaweera wrote: On Fri, Oct 11, 2002 at 04:04:29PM +0530, Shridhar Daithankar wrote: On 11 Oct 2002 at 16:29, Anuradha Ratnaweera wrote: On Fri, Oct 11, 2002 at 03:54:15PM +0530, Shridhar Daithankar wrote: I will look at it, too. Thanks for the link. In some cases, starting anew is faster than learning unmaintained existing code. Ok. Checked out what usogres is. It is not what I want. I don't want a static `main database'. It should simply a cluster of them - just like a set of Raid-0 disks, may be with a tempory controller for some tasks. Well, I don't think adding support for multiple slaves to usogres would be that problematic. Of course if you want to load balance your application queries, application has to be aware of that. I will not do sending requests to a mosix cluster anyway. Also, as a matter of fact, usogres is not unmaintained code. Glad to know that. I wrote to author with some suggestion and never got a reply. Didn't bother joining mailing list though.. Regards, Shridhar --- Shridhar Daithankar LIMS CPE Team Member, PSPL. mailto:shridhar_daithankar;persistent.co.in Phone:- +91-20-5678900 Extn.270 Fax :- +91-20-5678901 --- ---(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] Peer to peer replication of Postgresql databases
On 11 Oct 2002 at 16:29, Anuradha Ratnaweera wrote: On Fri, Oct 11, 2002 at 03:54:15PM +0530, Shridhar Daithankar wrote: I will look at it, too. Thanks for the link. In some cases, starting anew is faster than learning unmaintained existing code. While that's true, usogres code is just few files. I wouldn't take more than half an hour to read up the things. And besides it contain postgresql protocol implementation necessary which would take some time to test and debug, And it's in C++. I like that..;-) My original mail would have been much shorter if it simply stated that I want to add `application level RAID-0' to postgres ;) :-) Bye Shridhar -- QOTD: Do you smell something burning or is it me? -- Joan of Arc ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Peer to peer replication of Postgresql databases
Hi all, I am trying to add some replication features to postgres (yes, I have already looked at ongoing work), in a peer to peer manner. The goal is to achive `nearly complete fault tolerence' by replicating data. The basic framework I have in mind is somewhat like this. - Postmasters are running on different computers on a networked cluster. Their data areas are identical at the beginning and recide on local storage devices. - Each postmaster is aware that they are a part of a cluster and they can communicate with each other, send multicast requests and look for each other's presence (like heartbeat in linux-ha project). - When a frontend process sends a read query, each backend process does that from its own data area. - There are two types of write queries. Postmasters use seperate communication channels for each. One is the sequencial channel which carries writes whose order is important, and the non-sequencial channel carries write queries whose order is not important. - When a frontend process sends non-sequencial write query to a backend, it is directly written to the local data area and a multicast is sent (preferably asynchronously) to the other postmasters who will also update their respective local areas. May be we can simply duplicate what goes to WAL into a TCP/IP socket (with some header info, of course). - When a sequencial-write query is requested, the corresponding postmaster informs a main-postmaster (more about in the next point), waits for his acknowledgement, and proceeds the same way as the non-sequencial write. - Each postmaster is assigned a priority. The one with the highest priority is doing some bookkeeping to handle concurrency issues etc. If he goes away, another one takes charge. Or maybe we can completely ignore the main-postmaster concept and let the clients broadcast a request to obtain locks etc. - When a new postmaster, hence a computer, joins the cluster, he will replicate the current database from one of the clients. Suggessions and critisisms are welcome. Anuradha -- Debian GNU/Linux (kernel 2.4.18-xfs-1.1) The best audience is intelligent, well-educated and a little drunk. -- Maurice Baring ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [Fwd: Re: [JDBC] Patch for handling autocommit=false in postgresql.conf]
Barry,
Never mind.
Patch with 'begin;set autocommit to on;commit' work fine for JDBC spec.
regards,
Haris Peco
On Friday 11 October 2002 02:57 am, Barry Lind wrote:
Did anything come of this discussion on whether SET initiates a
transaction or not?
In summary what is the right way to deal with setting autocommit in
clients?
thanks,
--Barry
Original Message
Subject: Re: [JDBC] Patch for handling autocommit=false in
postgresql.conf Date: Tue, 17 Sep 2002 10:26:14 -0400
From: Tom Lane [EMAIL PROTECTED]
To: snpe [EMAIL PROTECTED]
CC: pgsql-jdbc [EMAIL PROTECTED]
References: [EMAIL PROTECTED]
snpe [EMAIL PROTECTED] writes:
+ // handle autocommit=false in postgresql.conf
+if (haveMinimumServerVersion(7.3)) {
+ ExecSQL(set autocommit to on;
commit;); + }
The above will fill people's logs with
WARNING: COMMIT: no transaction in progress
if they don't have autocommit off.
Use
begin; set autocommit to on; commit;
instead.
I would recommend holding off on this patch altogether, actually,
until we decide whether SET will be a transaction-initiating
command or not. I would still like to persuade the hackers community
that it should not be.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] number of attributes in page files?
Mario Weilguni [EMAIL PROTECTED] writes: Is it possible to get rid of the t_natts fields in the tuple header? Is this field only for alter table add/drop support? Only? A lot of people consider that pretty important ... But removing 2 bytes isn't going to save anything, on most machines, because of alignment considerations. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Peer to peer replication of Postgresql databases
I'd be curious to hear in a little more detail what constitutes not good for postgres on a mosix cluster. Greg On Fri, 2002-10-11 at 06:15, Anuradha Ratnaweera wrote: On Fri, Oct 11, 2002 at 04:29:53PM +0530, Shridhar Daithankar wrote: Well, I don't think adding support for multiple slaves to usogres would be that problematic. Of course if you want to load balance your application queries, application has to be aware of that. I will not do sending requests to a mosix cluster anyway. Have already tested postgres on a mosix cluster, and as expected results are not good. (although mosix does the correct thing in keeping all the database backend processes on one node). Anuradha -- Debian GNU/Linux (kernel 2.4.18-xfs-1.1) Remember: Silly is a state of Mind, Stupid is a way of Life. -- Dave Butler ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [HACKERS] MySQL vs PostgreSQL.
On Fri, 2002-10-11 at 09:20, Antti Haapala wrote: Check out: http://www.mysql.com/doc/en/MySQL-PostgreSQL_features.html MySQL AB compares MySQL with PostgreSQL. I wouldn't look too far into these at all. I've tried to get ' as identifier quote (ANSI SQL) ' corrected on the crash-me pages for us a couple of times (they say we don't support it for some reason). I've not looked, but I thought 7.1 supported rename table as well. Anyway, max table row length was wrong with 7.1 wrong too unless I'm confused as to what a blob is (is text and varchar a blob -- what about your own 10Mb fixed length datatype -- how about a huge array of integers if the previous are considered blobs?) -- Rod Taylor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] MySQL vs PostgreSQL.
On Fri, 11 Oct 2002, Jeff Davis wrote: They also state that they have more sophisticated ALTER TABLE... Only usable feature in their ALTER TABLE that doesn't (yet) exist in PostgreSQL was changing column order (ok, the order by in table creation could be nice), and that's still almost purely cosmetic. Anyway, I could have used that command yesterday. Could this be added to pgsql. I agree with your message except for that statement. MySQL alter table provides the ability to change column types and cast the records automatically. I remember that feature as really the only thing from MySQL that I've ever missed. Of course, it's not that wonderful in theory. During development you can easily drop/recreate the tables and reload the test data; during production you don't change the data types of your attributes. But in practice, during development it's handy sometimes. I still remember a post from somebody on the phpbuilder site that had changed a field from varchar to date and all the dates he had got changed to -00-00. He most unimpressed, especially since he (being typical of a lot of MySQL users) didn't have a backup. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] MySQL vs PostgreSQL.
They also state that they have more sophisticated ALTER TABLE... Only usable feature in their ALTER TABLE that doesn't (yet) exist in PostgreSQL was changing column order (ok, the order by in table creation could be nice), and that's still almost purely cosmetic. Anyway, I could have used that command yesterday. Could this be added to pgsql. I agree with your message except for that statement. MySQL alter table provides the ability to change column types and cast the records automatically. I remember that feature as really the only thing from MySQL that I've ever missed. Of course, it's not that wonderful in theory. During development you can easily drop/recreate the tables and reload the test data; during production you don't change the data types of your attributes. But in practice, during development it's handy sometimes. Regards, Jeff ---(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] MySQL vs PostgreSQL.
I still remember a post from somebody on the phpbuilder site that had changed a field from varchar to date and all the dates he had got changed to -00-00. He most unimpressed, especially since he (being typical of a lot of MySQL users) didn't have a backup. Ah, yes. Classic. I was talking about a development scenario. Anyone who changes a huge amount of important data to a new form without a clearly defined algorithm is not making a wise choice. That's kind of like if you have a perl script operating on an important file: you don't want it to just kill all your data, so you do a few tests first. And it really is a minor matter of convenience. I end up dropping and recreating all my tables a lot in the early stages of development, which is mildly annoying. Certainly not as bad, I suppose, as if you're led to believe that a feature does something safely, and it kills all your data. So, you're right. It's probably better that it's never implemented. Regards, Jeff ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MySQL vs PostgreSQL.
On Fri, Oct 11, 2002 at 07:08:18PM -0700, Jeff Davis wrote: And it really is a minor matter of convenience. I end up dropping and recreating all my tables a lot in the early stages of development, which is mildly annoying. Certainly not as bad, I suppose, as if you're led to believe that a feature does something safely, and it kills all your data. Now that ALTER TABLE DROP COLUMN is implemented, there probably isn't any more the need to do such frequent drop/create of tables. And things just keep getting better and better. This is really amazing. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) We are who we choose to be, sang the goldfinch when the sun is high (Sandman) ---(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] MySQL vs PostgreSQL.
Oh yes, I agree. ALTER TABLE ... DROP COLUMN helps out a lot. I actually don't use that for much yet because 7.3 is still in beta. However, I certainly can't complain to the developers for it since it's already developed :) I am consistantly amazed by every minor version release. If postgres had a marketing team it would be at version 37.3 by now. In my last email I agreed with Scott Marlowe that postgres is better off without the casting of an entire column, since that's kind of a dangeous procedure and can be completed in a round-about (read: explicit) way by postgres anyway, that doesn't lose your data until after you've had a chance to look at the new stuff. Regards, Jeff On Friday 11 October 2002 07:16 pm, you wrote: On Fri, Oct 11, 2002 at 07:08:18PM -0700, Jeff Davis wrote: And it really is a minor matter of convenience. I end up dropping and recreating all my tables a lot in the early stages of development, which is mildly annoying. Certainly not as bad, I suppose, as if you're led to believe that a feature does something safely, and it kills all your data. Now that ALTER TABLE DROP COLUMN is implemented, there probably isn't any more the need to do such frequent drop/create of tables. And things just keep getting better and better. This is really amazing. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [JDBC] Out of memory error on huge resultset
Barry, Is it true ? I create table with one column varchar(500) and enter 1 milion rows with length 10-20 character.JDBC query 'select * from a' get error 'out of memory', but psql not. I insert 8 milion rows and psql work fine yet (slow, but work) In C library is 'execute query' without fetch - in jdbc execute fetch all rows and this is problem - I think that executequery must prepare query and fetch (ResultSet.next or ...) must fetch only fetchSize rows. I am not sure, but I think that is problem with jdbc, not postgresql Hackers ? Does psql fetch all rows and if not how many ? Can I change fetch size in psql ? CURSOR , FETCH and MOVE isn't solution. If I use jdbc in third-party IDE, I can't force this solution regards On Thursday 10 October 2002 06:40 pm, Barry Lind wrote: Nick, This has been discussed before on this list many times. But the short answer is that that is how the postgres server handles queries. If you issue a query the server will return the entire result. (try the same query in psql and you will have the same problem). To work around this you can use explicit cursors (see the DECLARE CURSOR, FETCH, and MOVE sql commands for postgres). thanks, --Barry Nick Fankhauser wrote: I'm selecting a huge ResultSet from our database- about one million rows, with one of the fields being varchar(500). I get an out of memory error from java. If the whole ResultSet gets stashed in memory, this isn't really surprising, but I'm wondering why this happens (if it does), rather than a subset around the current record being cached and other rows being retrieved as needed. If it turns out that there are good reasons for it to all be in memory, then my question is whether there is a better approach that people typically use in this situation. For now, I'm simply breaking up the select into smaller chunks, but that approach won't be satisfactory in the long run. Thanks -Nick - - Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko Co. Software Consulting Services http://www.ontko.com/ ---(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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [JDBC] Out of memory error on huge resultset
Can You do this : We save 1000 (or fetchSize rows) first from beginning If table have 1000 rows we save all rows, but if table have more rows and user request 1001 we fetch 1000 (again from begining, but skip 1000 rows or maybe continue fetching, if it possible) When user request last we fetch all rows, but save only last 1000 etc We save only fetchSize rows and seek from begining when user request backward (or maybe seek always when user request out our 'fetchSize' window) This is slow for large tables, but this is solution until developer get us better solution from backend.If table have fetchSize rows this is same current solution and we can fix minimal fetchSize for better performance with small tables. regards Haris Peco On Friday 11 October 2002 08:13 pm, Dave Cramer wrote: No, It doesn't have to store them, only display them Dave On Fri, 2002-10-11 at 12:48, snpe wrote: Hello, Does it mean that psql uses cursors ? regards Haris Peco On Friday 11 October 2002 05:58 pm, Dave Cramer wrote: This really is an artifact of the way that postgres gives us the data. When you query the backend you get *all* of the results in the query, and there is no indication of how many results you are going to get. In simple selects it would be possible to get some idea by using count(field), but this wouldn't work nearly enough times to make it useful. So that leaves us with using cursors, which still won't tell you how many rows you are getting back, but at least you won't have the memory problems. This approach is far from trivial which is why it hasn't been implemented as of yet, keep in mind that result sets support things like move(n), first(), last(), the last of which will be the trickiest. Not to mention updateable result sets. As it turns out there is a mechanism to get to the end move 0 in 'cursor', which currently is being considered a bug. Dave On Fri, 2002-10-11 at 11:44, Doug Fields wrote: At 08:27 AM 10/11/2002, snpe wrote: Barry, Is it true ? I create table with one column varchar(500) and enter 1 milion rows with length 10-20 character.JDBC query 'select * from a' get error 'out of memory', but psql not. I insert 8 milion rows and psql work fine yet (slow, but work) The way the code works in JDBC is, in my opinion, a little poor but possibly mandated by JDBC design specs. It reads the entire result set from the database backend and caches it in a horrible Vector (which should really be a List and which should at least make an attempt to get the # of rows ahead of time to avoid all the resizing problems). Then, it doles it out from memory as you go through the ResultSet with the next() method. I would have hoped (but was wrong) that it streamed - WITHOUT LOADING THE WHOLE THING - through the result set as each row is returned from the backend, thus ensuring that you never use much more memory than one line. EVEN IF you have to keep the connection locked. The latter is what I expected it to do. The former is what it does. So, it necessitates you creating EVERY SELECT query which you think has more than a few rows (or which you think COULD have more than a few rows, few being defined by our VM memory limits) into a cursor based query. Really klugy. I intend to write a class to do that for every SELECT query for me automatically. Cheers, Doug In C library is 'execute query' without fetch - in jdbc execute fetch all rows and this is problem - I think that executequery must prepare query and fetch (ResultSet.next or ...) must fetch only fetchSize rows. I am not sure, but I think that is problem with jdbc, not postgresql Hackers ? Does psql fetch all rows and if not how many ? Can I change fetch size in psql ? CURSOR , FETCH and MOVE isn't solution. If I use jdbc in third-party IDE, I can't force this solution regards On Thursday 10 October 2002 06:40 pm, Barry Lind wrote: Nick, This has been discussed before on this list many times. But the short answer is that that is how the postgres server handles queries. If you issue a query the server will return the entire result. (try the same query in psql and you will have the same problem). To work around this you can use explicit cursors (see the DECLARE CURSOR, FETCH, and MOVE sql commands for postgres). thanks, --Barry Nick Fankhauser wrote: I'm selecting a huge ResultSet from our database- about one million rows, with one of the fields being varchar(500). I get an out of memory error from java. If the whole ResultSet gets stashed in memory, this isn't really surprising, but I'm wondering why this happens (if it does),
Re: [HACKERS] MySQL vs PostgreSQL.
scott.marlowe wrote: On Fri, 11 Oct 2002, Jeff Davis wrote: I agree with your message except for that statement. MySQL alter table provides the ability to change column types and cast the records automatically. I remember that feature as really the only thing from MySQL that I've ever missed. Of course, it's not that wonderful in theory. During development you can easily drop/recreate the tables and reload the test data; during production you don't change the data types of your attributes. But in practice, during development it's handy sometimes. I still remember a post from somebody on the phpbuilder site that had changed a field from varchar to date and all the dates he had got changed to -00-00. He most unimpressed, especially since he (being typical of a lot of MySQL users) didn't have a backup. Couldn't he just do ROLLBACK? ;-) (for the humor impaired, that's a joke...) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] contrib/fixchar (Was: Large databases, performance)
Well, this is not related to postgresql exactly but to summerise the problem, with libc patch PHCO_19090 or compatible upwards, on HP-UX11, isspace does not work correctly if input value is 127. o isspace() and such are defined in the standards to operate on characters o for historic C reasons, 'char' is widened to 'int' in function calls o it is platform dependent whether 'char' is a signed or unsigned type If your platform has signed 'char' (as HP-UX does on PA-RISC) and you pass a value that is negative it will be sign extended when converted to 'int', and may be outside the range of values for which isspace() is defined. Portable code uses 'unsigned char' when using ctype.h features, even though for many platforms where 'char' is an unsigned type it's not necessary for correct functioning. I don't see any isspace() or similar in the code though, so I'm not sure why this issue is being raised? Regards, Giles ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
