Thread: Re: [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 nickf@ontko.com 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
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
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
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.. ByeShridhar -- Cat, n.: Lapwarmer with built-in buzzer.
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
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. # #================================================== JanWieck@Yahoo.com #
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
> 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
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 0000-00-00. He most unimpressed, especially since he (being typical of a lot of MySQL users) didn't have a backup.
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 0000-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 mascarm@mascari.com
> > 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 0000-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
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)
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.
Alvaro Herrera kirjutas L, 12.10.2002 kell 04:16: > 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. Did attlognum's (for changing column order) get implemented for 7.2 ? ------------ Hannu
On 12 Oct 2002, Hannu Krosing wrote: > Alvaro Herrera kirjutas L, 12.10.2002 kell 04:16: > > 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. > > Did attlognum's (for changing column order) get implemented for 7.2 ? 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? Gavin
> 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. What about copy? AFAIK, copy doesn't allow column names being specified, so it's not purely aesthetic...
> > > > Did attlognum's (for changing column order) get implemented for 7.2 ? > > 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? > I think even "asthetic" might go too far. It seems mostly irrelevent except for people who are obsessive compulsive and operate in interactive psql a lot. It's marginally simpler to get the columns ordered the way you want so that you can just do "SELECT * ..." rather than "SELECT att0,att1,... ..." at the interactive psql prompt, and still get the columns in your favorite order. As far as I can tell, the order the attributes are returned makes no difference in a client application, unless you're referencing attributes by number. All applications that I've made or seen all use the name instead, and I've never heard otherwise, or heard any advantage to using numbers to reference columns. When someone asks, ask them "why?". I'd be interested to know if they have some other reason. I would think that if they absolutely wanted to fine-tune the order of columns they'd use a view (seems a little easier than continually changing order around by individual SQL statements). Regards,Jeff
On 12 Oct 2002 at 2:54, Jeff Davis wrote: > As far as I can tell, the order the attributes are returned makes no > difference in a client application, unless you're referencing attributes by > number. All applications that I've made or seen all use the name instead, and > I've never heard otherwise, or heard any advantage to using numbers to > reference columns. Even in that case you can obtain field number for a given name and vise versa.. > When someone asks, ask them "why?". I'd be interested to know if they have > some other reason. I would think that if they absolutely wanted to fine-tune > the order of columns they'd use a view (seems a little easier than > continually changing order around by individual SQL statements). Sounds fine but what is about that "continually changing"? A view needs a change only if it alters fields selected/tables to select from/selection criteria. Field order does not figure in there.. ByeShridhar -- QOTD: "A child of 5 could understand this! Fetch me a child of 5."
Hannu Krosing wrote: > Alvaro Herrera kirjutas L, 12.10.2002 kell 04:16: > > 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. > > Did attlognum's (for changing column order) get implemented for 7.2 ? No, changing column order isn't even on the TODO list. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Sat, Oct 12, 2002 at 12:43:37 +0300, Antti Haapala <antti.haapala@iki.fi> 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. > > What about copy? AFAIK, copy doesn't allow column names being specified, > so it's not purely aesthetic... The SQL COPY command does (at least in 7.3). The \copy psql command doesn't seem to allow this though.
Bruno Wolff III <bruno@wolff.to> writes:
> On Sat, Oct 12, 2002 at 12:43:37 +0300,
> Antti Haapala <antti.haapala@iki.fi> wrote:
>> What about copy? AFAIK, copy doesn't allow column names being specified,
>> so it's not purely aesthetic...
> The SQL COPY command does (at least in 7.3). The \copy psql command
> doesn't seem to allow this though.
That's an oversight; \copy should have been fixed for 7.3.
Do we want to look at this as a bug (okay to fix for 7.3) or a new
feature (wait for 7.4)?
I see something that I think is a must-fix omission in the same code:
it should allow a schema-qualified table name. So I'm inclined to fix
both problems now.
regards, tom lane
Tom Lane wrote: > Bruno Wolff III <bruno@wolff.to> writes: > > On Sat, Oct 12, 2002 at 12:43:37 +0300, > > Antti Haapala <antti.haapala@iki.fi> wrote: > >> What about copy? AFAIK, copy doesn't allow column names being specified, > >> so it's not purely aesthetic... > > > The SQL COPY command does (at least in 7.3). The \copy psql command > > doesn't seem to allow this though. > > That's an oversight; \copy should have been fixed for 7.3. > > Do we want to look at this as a bug (okay to fix for 7.3) or a new > feature (wait for 7.4)? > > I see something that I think is a must-fix omission in the same code: > it should allow a schema-qualified table name. So I'm inclined to fix > both problems now. I don't think we can say \copy missing columns is a bug; we never had it in previous release. Seems like a missing feature. The COPY schema names seems valid. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Do we want to look at this as a bug (okay to fix for 7.3) or a new
>> feature (wait for 7.4)?
> I don't think we can say \copy missing columns is a bug; we never had
> it in previous release. Seems like a missing feature. The COPY schema
> names seems valid.
Well, we never had schema names in previous releases either. So I'm not
sure that I see a bright line between these items. The real issue is
that psql's \copy has failed to track the capabilities of backend COPY.
I think we should just fix it.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> Do we want to look at this as a bug (okay to fix for 7.3) or a new > >> feature (wait for 7.4)? > > > I don't think we can say \copy missing columns is a bug; we never had > > it in previous release. Seems like a missing feature. The COPY schema > > names seems valid. > > Well, we never had schema names in previous releases either. So I'm not > sure that I see a bright line between these items. The real issue is > that psql's \copy has failed to track the capabilities of backend COPY. > I think we should just fix it. OK, I added it to the open items list. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
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. :-) -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-22-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
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. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Mon, Oct 14, 2002 at 11:04:07AM -0400, 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. > > > > 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. > > 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). And note that if column ordering was to be implemented through the use of attlognum or something similar, the physical ordering would not be affected. The only way to physically reoder the columns would be to completely rebuild the table. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Aprende a avergonzarte mas ante ti que ante los demas" (Democrito)
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. # #================================================== JanWieck@Yahoo.com #
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 pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> 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
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> 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 pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Dave Cramer wrote: > 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? I did some research on this. It turns out the parser uses 0 for ALL, so when you do a FETCH ALL it is passing zero. Now, when you do MOVE 0, you are really asking for FETCH ALL and all the tuples are thrown away because of the MOVE. So, that is why MOVE 0 goes to the end of the cursor. One idea would be for MOVE 0 to actually move nothing, but jdbc and others need the ability to move the end of the cursor, perhaps to then back up a certain amount and read from there. Seems MOVE 0 is the logical way to do that. (I can't think of another reasonable value). I have the following patch which just documents the fact that MOVE 0 goes to the end of the cursor. It does not change any behavior, just document it. If/when I apply the patch, I will remove the TODO item. Another idea would be to require MOVE END to move to the end. Comments? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/ref/move.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/move.sgml,v retrieving revision 1.13 diff -c -c -r1.13 move.sgml *** doc/src/sgml/ref/move.sgml 21 Apr 2002 19:02:39 -0000 1.13 --- doc/src/sgml/ref/move.sgml 26 Oct 2002 20:01:15 -0000 *************** *** 37,44 **** <command>MOVE</command> allows a user to move cursor position a specified number of rows. <command>MOVE</command> works like the <command>FETCH</command> command, ! but only positions the cursor and does ! not return rows. </para> <para> Refer to --- 37,44 ---- <command>MOVE</command> allows a user to move cursor position a specified number of rows. <command>MOVE</command> works like the <command>FETCH</command> command, ! but only positions the cursor and does not return rows. The special ! direction <literal>0</> moves to the end of the cursor. </para> <para> Refer to Index: src/backend/executor/execMain.c =================================================================== RCS file: /cvsroot/pgsql-server/src/backend/executor/execMain.c,v retrieving revision 1.180 diff -c -c -r1.180 execMain.c *** src/backend/executor/execMain.c 14 Oct 2002 16:51:30 -0000 1.180 --- src/backend/executor/execMain.c 26 Oct 2002 20:01:20 -0000 *************** *** 1119,1125 **** /* * check our tuple count.. if we've processed the proper number ! * then quit, else loop again and process more tuples.. */ current_tuple_count++; if (numberTuples == current_tuple_count) --- 1119,1127 ---- /* * check our tuple count.. if we've processed the proper number ! * then quit, else loop again and process more tuples. ! * If numberTuples is zero, it means we have done MOVE 0 ! * or FETCH ALL and we want to go to the end of the portal. */ current_tuple_count++; if (numberTuples == current_tuple_count) Index: src/backend/tcop/utility.c =================================================================== RCS file: /cvsroot/pgsql-server/src/backend/tcop/utility.c,v retrieving revision 1.180 diff -c -c -r1.180 utility.c *** src/backend/tcop/utility.c 21 Oct 2002 20:31:52 -0000 1.180 --- src/backend/tcop/utility.c 26 Oct 2002 20:01:29 -0000 *************** *** 263,270 **** /* * parser ensures that count is >= 0 and 'fetch ALL' -> 0 */ - count = stmt->howMany; PerformPortalFetch(portalName, forward, count, (stmt->ismove) ? None : dest, --- 263,270 ---- /* * parser ensures that count is >= 0 and 'fetch ALL' -> 0 + * MOVE 0 is equivalent to fetch ALL with no returned tuples. */ count = stmt->howMany; PerformPortalFetch(portalName, forward, count, (stmt->ismove) ? None : dest,
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I did some research on this. It turns out the parser uses 0 for ALL, so
> when you do a FETCH ALL it is passing zero. Now, when you do MOVE 0,
> you are really asking for FETCH ALL and all the tuples are thrown away
> because of the MOVE.
Yeah. I think this is a bug and "MOVE 0" ought to be a no-op ... but
changing it requires a different parsetree representation for MOVE ALL,
which is tedious enough that it hasn't gotten done yet.
> I have the following patch which just documents the fact that MOVE 0
> goes to the end of the cursor. It does not change any behavior, just
> document it.
It should be documented as behavior that is likely to change. Also,
I believe FETCH 0 has the same issue.
regards, tom lane
Bruce Momjian writes: > So, that is why MOVE 0 goes to the end of the cursor. One idea would be > for MOVE 0 to actually move nothing, but jdbc and others need the > ability to move the end of the cursor, perhaps to then back up a certain > amount and read from there. Seems MOVE 0 is the logical way to do that. > (I can't think of another reasonable value). It would seem more logical and reasonable for MOVE 0 to do nothing and have some special syntax such as MOVE LAST to move to the end. (MOVE LAST would actually be consistent with the standard syntax FETCH LAST.) -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote: > Bruce Momjian writes: > > > So, that is why MOVE 0 goes to the end of the cursor. One idea would be > > for MOVE 0 to actually move nothing, but jdbc and others need the > > ability to move the end of the cursor, perhaps to then back up a certain > > amount and read from there. Seems MOVE 0 is the logical way to do that. > > (I can't think of another reasonable value). > > It would seem more logical and reasonable for MOVE 0 to do nothing and > have some special syntax such as MOVE LAST to move to the end. (MOVE LAST > would actually be consistent with the standard syntax FETCH LAST.) Yea, I started thinking and we need to get MOVE/FETCH to make sense. The following patch makes FETCH/MOVE 0 do nothing, and FETCH LAST move to the end. I was going to use the word END, but if LAST is more standard, we will use that. It uses INT_MAX in the grammar for FETCH ALL/MOVE LAST, but maps that to zero so it is consistent in the /executor code. I will keep this patch for 7.4. JDBC folks, I realize you need this. Seems you will have to use MOVE 0 for 7,3 and MOVE LAST for 7.4. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/ref/move.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/move.sgml,v retrieving revision 1.13 diff -c -c -r1.13 move.sgml *** doc/src/sgml/ref/move.sgml 21 Apr 2002 19:02:39 -0000 1.13 --- doc/src/sgml/ref/move.sgml 31 Oct 2002 01:15:42 -0000 *************** *** 21,27 **** <date>1999-07-20</date> </refsynopsisdivinfo> <synopsis> ! MOVE [ <replaceable class="PARAMETER">direction</replaceable> ] [ <replaceable class="PARAMETER">count</replaceable> ] { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable> </synopsis> </refsynopsisdiv> --- 21,28 ---- <date>1999-07-20</date> </refsynopsisdivinfo> <synopsis> ! MOVE [ <replaceable class="PARAMETER">direction</replaceable> ] ! {<replaceable class="PARAMETER">count</replaceable> | LAST } { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable> </synopsis> </refsynopsisdiv> Index: src/backend/commands/portalcmds.c =================================================================== RCS file: /cvsroot/pgsql-server/src/backend/commands/portalcmds.c,v retrieving revision 1.3 diff -c -c -r1.3 portalcmds.c *** src/backend/commands/portalcmds.c 4 Sep 2002 20:31:15 -0000 1.3 --- src/backend/commands/portalcmds.c 31 Oct 2002 01:15:44 -0000 *************** *** 15,20 **** --- 15,22 ---- #include "postgres.h" + #include <limits.h> + #include "commands/portalcmds.h" #include "executor/executor.h" *************** *** 55,61 **** * * name: name of portal * forward: forward or backward fetch? ! * count: # of tuples to fetch (0 implies all) * dest: where to send results * completionTag: points to a buffer of size COMPLETION_TAG_BUFSIZE * in which to store a command completion status string. --- 57,63 ---- * * name: name of portal * forward: forward or backward fetch? ! * count: # of tuples to fetch * dest: where to send results * completionTag: points to a buffer of size COMPLETION_TAG_BUFSIZE * in which to store a command completion status string. *************** *** 100,105 **** --- 102,115 ---- return; } + /* If zero count, we are done */ + if (count == 0) + return; + + /* Internally, zero count processes all portal rows */ + if (count == INT_MAX) + count = 0; + /* * switch into the portal context */ Index: src/backend/executor/execMain.c =================================================================== RCS file: /cvsroot/pgsql-server/src/backend/executor/execMain.c,v retrieving revision 1.180 diff -c -c -r1.180 execMain.c *** src/backend/executor/execMain.c 14 Oct 2002 16:51:30 -0000 1.180 --- src/backend/executor/execMain.c 31 Oct 2002 01:15:50 -0000 *************** *** 1119,1125 **** /* * check our tuple count.. if we've processed the proper number ! * then quit, else loop again and process more tuples.. */ current_tuple_count++; if (numberTuples == current_tuple_count) --- 1119,1126 ---- /* * check our tuple count.. if we've processed the proper number ! * then quit, else loop again and process more tuples. Zero ! * number_tuples means no limit. */ current_tuple_count++; if (numberTuples == current_tuple_count) Index: src/backend/parser/gram.y =================================================================== RCS file: /cvsroot/pgsql-server/src/backend/parser/gram.y,v retrieving revision 2.370 diff -c -c -r2.370 gram.y *** src/backend/parser/gram.y 22 Sep 2002 21:44:43 -0000 2.370 --- src/backend/parser/gram.y 31 Oct 2002 01:16:14 -0000 *************** *** 49,54 **** --- 49,55 ---- #include "postgres.h" #include <ctype.h> + #include <limits.h> #include "access/htup.h" #include "catalog/index.h" *************** *** 357,363 **** JOIN KEY ! LANCOMPILER LANGUAGE LEADING LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P --- 358,364 ---- JOIN KEY ! LANCOMPILER LANGUAGE LAST LEADING LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P *************** *** 2644,2650 **** if ($3 < 0) { $3 = -$3; ! $2 = (($2 == FORWARD)? BACKWARD: FORWARD); } n->direction = $2; n->howMany = $3; --- 2645,2651 ---- if ($3 < 0) { $3 = -$3; ! $2 = (($2 == FORWARD) ? BACKWARD: FORWARD); } n->direction = $2; n->howMany = $3; *************** *** 2712,2719 **** fetch_how_many: Iconst { $$ = $1; } | '-' Iconst { $$ = - $2; } ! /* 0 means fetch all tuples*/ ! | ALL { $$ = 0; } | NEXT { $$ = 1; } | PRIOR { $$ = -1; } ; --- 2713,2720 ---- fetch_how_many: Iconst { $$ = $1; } | '-' Iconst { $$ = - $2; } ! | ALL { $$ = INT_MAX; } ! | LAST { $$ = INT_MAX; } | NEXT { $$ = 1; } | PRIOR { $$ = -1; } ; *************** *** 7098,7103 **** --- 7099,7105 ---- | KEY | LANGUAGE | LANCOMPILER + | LAST | LEVEL | LISTEN | LOAD Index: src/backend/parser/keywords.c =================================================================== RCS file: /cvsroot/pgsql-server/src/backend/parser/keywords.c,v retrieving revision 1.127 diff -c -c -r1.127 keywords.c *** src/backend/parser/keywords.c 18 Sep 2002 21:35:22 -0000 1.127 --- src/backend/parser/keywords.c 31 Oct 2002 01:16:15 -0000 *************** *** 172,177 **** --- 172,178 ---- {"key", KEY}, {"lancompiler", LANCOMPILER}, {"language", LANGUAGE}, + {"last", LAST}, {"leading", LEADING}, {"left", LEFT}, {"level", LEVEL}, Index: src/backend/tcop/utility.c =================================================================== RCS file: /cvsroot/pgsql-server/src/backend/tcop/utility.c,v retrieving revision 1.180 diff -c -c -r1.180 utility.c *** src/backend/tcop/utility.c 21 Oct 2002 20:31:52 -0000 1.180 --- src/backend/tcop/utility.c 31 Oct 2002 01:16:18 -0000 *************** *** 262,270 **** forward = (bool) (stmt->direction == FORWARD); /* ! * parser ensures that count is >= 0 and 'fetch ALL' -> 0 */ - count = stmt->howMany; PerformPortalFetch(portalName, forward, count, (stmt->ismove) ? None : dest, --- 262,269 ---- forward = (bool) (stmt->direction == FORWARD); /* ! * parser ensures that count is >= 0 */ count = stmt->howMany; PerformPortalFetch(portalName, forward, count, (stmt->ismove) ? None : dest,
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> The following patch makes FETCH/MOVE 0 do nothing, and FETCH LAST move
> to the end.
Do not hack up PerformPortalFetch; put the special case for INT_MAX in
utility.c's FetchStmt code, instead. As-is, you probably broke other
callers of PerformPortalFetch.
BTW, there's a comment in parsenodes.h that needs to be fixed too:
int howMany; /* amount to fetch ("ALL" --> 0) */
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > The following patch makes FETCH/MOVE 0 do nothing, and FETCH LAST move
> > to the end.
>
> Do not hack up PerformPortalFetch; put the special case for INT_MAX in
> utility.c's FetchStmt code, instead. As-is, you probably broke other
> callers of PerformPortalFetch.
I thought about that, but I need to fail if the cursor name is invalid.
Those tests are done in PerformPortalFetch(). The good news is that no
one else call it. Other ideas?
> BTW, there's a comment in parsenodes.h that needs to be fixed too:
>
> int howMany; /* amount to fetch ("ALL" --> 0) */
Done.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Do not hack up PerformPortalFetch; put the special case for INT_MAX in
>> utility.c's FetchStmt code, instead. As-is, you probably broke other
>> callers of PerformPortalFetch.
> I thought about that, but I need to fail if the cursor name is invalid.
What has that got to do with it?
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Do not hack up PerformPortalFetch; put the special case for INT_MAX in
> >> utility.c's FetchStmt code, instead. As-is, you probably broke other
> >> callers of PerformPortalFetch.
>
> > I thought about that, but I need to fail if the cursor name is invalid.
>
> What has that got to do with it?
If I put the 'return' for 0 MOVE/FETCH in utility.c's FetchStmt code, I
will not get the checks for invalid cursor names, and I will not get the
proper return tag. I don't see how to do anything in utility.c. I
assume this is the code you want to move to utility.c:+ /* If zero count, we are done */+ if (count == 0)+
return;+ + /* Internally, zero count processes all portal rows */+ if (count == INT_MAX)+
count = 0;+
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> I thought about that, but I need to fail if the cursor name is invalid.
>>
>> What has that got to do with it?
> If I put the 'return' for 0 MOVE/FETCH in utility.c's FetchStmt code, I
> will not get the checks for invalid cursor names, and I will not get the
> proper return tag.
Oh, I see. Yeah, you're probably right, we have to change the calling
convention for PerformPortalFetch.
BTW, portalcmds.h also contains a comment that would need to be fixed.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> Bruce Momjian <pgman@candle.pha.pa.us> writes: > >>> I thought about that, but I need to fail if the cursor name is invalid. > >> > >> What has that got to do with it? > > > If I put the 'return' for 0 MOVE/FETCH in utility.c's FetchStmt code, I > > will not get the checks for invalid cursor names, and I will not get the > > proper return tag. > > Oh, I see. Yeah, you're probably right, we have to change the calling > convention for PerformPortalFetch. > > BTW, portalcmds.h also contains a comment that would need to be fixed. Updated. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073