Re: [HACKERS] ECPG FETCH readahead
Hi, 2010-06-23 22:42 keltezéssel, Bruce Momjian írta: Boszormenyi Zoltan wrote: Hi, we improved ECPG quite a lot in 9.0 because we worked and still working with an Informix to PostgreSQL migration project. We came across a pretty big performance problem that can be seen in every naive application that uses only FETCH 1, FETCH RELATIVE or FETCH ABSOLUTE. These are almost the only FETCH variations usable in Informix, i.e. it doesn't have the grammar for fetching N rows at once. Instead, the Client SDK libraries do caching themselves behind the scenes to reduce network turnaround time. I assume our ecpg version supports1 fetch values, even in Informix mode. Does it make sense to add lots of code to our ecpg then? I think, yes, it does make sense. Because we are talking about porting a whole lot of COBOL applications. The ESQL/C or ECPG connector was already written the Informix quirks in mind, so it fetches only one record at a time passing it to the application. And similar performance is expected from ECPG - which excpectation is not fulfilled currently because libecpg doesn't do the same caching as ESQL/C does. And FYI, I haven't added a whole lot of code, most of the code changes in the patch is execute.c refactoring. ECPGdo() was split into several functions, the new parts are still doing the same things. I can make the test case much smaller, I only needed to test crossing the readahead window boundary. This would also make the patch much smaller. And this readahead is not on by default, it's only activated by ecpg -r fetch_readahead. Best regards, Zoltán Böszörményi -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG FETCH readahead
2010-06-24 11:04 keltezéssel, Heikki Linnakangas írta: On 24/06/10 10:27, Böszörményi Zoltán wrote: And this readahead is not on by default, it's only activated by ecpg -r fetch_readahead. Is there a reason not to enable it by default? I'm a bit worried that it will receive no testing if it's not always on. Because in the first step I wanted to minimize the impact on regression test stderr results. This is what I mentioned in the initial mail, I stuck to the original wording of ecpg_log() messages in the split-up parts of the original ECPGdo() and ecpg_execute() exactly for this reason. The usual policy for ecpg_log() is to report the function name where it was issued. I was also thinking about a new feature for pg_regress, to compare stdout results of two regression tests automatically so a difference can be reported as an error. It would be good for automated testing of features in ECPG that can be toggled, like auto-prepare and fetch readahead. It might come in handy in other subsystems, too. Best regards, Zoltán Böszörményi -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG FETCH readahead
2010-06-24 14:13 keltezéssel, Michael Meskes írta: I think, yes, it does make sense. Because we are talking about porting a whole lot of COBOL applications. COBOL??? Yes, OpenCOBOL... The ESQL/C or ECPG connector was already written the Informix quirks in mind, so it fetches only one record at a time passing it to the application. And similar performance is expected from ECPG - which excpectation is not fulfilled currently because libecpg doesn't do the same caching as ESQL/C does. Eh, you are talking about a program you wrote for your customer or they wrote themselves, right? I simply refuse to add this stuff only to fix this situation for that one customer of yours if it only hits them. Now the thing to discuss is how common is this situation. The OpenCOBOL database connector was written by them but the problem is more generic. There are many naive applications (elsewhere, too) using cursors but fetching one record at a time perhaps for portability reasons. This patch provides a big performance boost for those. Best regards, Zoltán Böszörményi -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Performance testing of COPY (SELECT) TO
Hi, we have a large export here, I made an in-house benchmark between Informix, plain PostgreSQL-8.1.4 and 8.2devel+COPY(SELECT) using the same data and query. Find the results below for the two PostgreSQL versions. With PostgreSQL 8.1.4, I used this: begin; select ... into temp myquery1; copy myquery1 to stdout csv delimiter '|'; rollback; With 8.2devel, I simple used copy (select ...) to stdout csv delimiter '|'; # of clients: 1* 3** 10** PostgreSQL 1:3310:58 55:46 PostgreSQL 8.2 1:194:5518:28 * - average of 4 runs, the first was with cold caches after reboot ** - 1 run, average of cliens' runtimes Performance between 8.1.4 and 8.2devel is interesting: 1 client: 15% 3 clients: 55.2% 10 clients: 66.9% The same machine was used for testing. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 1: 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] Performance testing of COPY (SELECT) TO
Böszörményi Zoltán [EMAIL PROTECTED] writes: With PostgreSQL 8.1.4, I used this: begin; select ... into temp myquery1; copy myquery1 to stdout csv delimiter '|'; rollback; The performance of this would doubtless vary a lot with the temp_buffers setting. Did you try different values? Yes, I did, but now checked back with 8.2CVS. The previously quoted result was achieved with temp_buffers = 1000 on both 8.1.4 and 8.2CVS. On 8.2CVS with temp_buffers = 4096, the 10 client case kills the machine with swapping, but the 3 client runtime with COPY(SELECT) went down to 2:41. The SELECT INTO TEMP case went down to 3:36. It'd also be interesting to time the same way (with a temp table) in devel. I don't remember whether we did any performance work on the COPY CSV data path in this cycle, or whether that was all present in 8.1. In any case it'd be worth proving that the COPY SELECT patch isn't degrading performance of the copy-a-relation case. I will report back with that, say on Monday. In the meantime, I documented the COPY (SELECT) case and modified parser/analyze.c and tcop/utility.c so neither of them calls anything from under another directory. I think it's cleaner now. Also, I tried to implement more closely what printtup() does. Please, review. Best regards, Zoltán Böszörményi pgsql-copyselect-7.patch.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] COPY view
Hi, Robert Treat [EMAIL PROTECTED] writes: On Tuesday 22 August 2006 16:10, Tom Lane wrote: As I see it, we've effectively got a patch that was rejected once, and Bruce wants to apply it anyway because no replacement has been forthcoming. Well, unless someone is going to commit to doing it the other way, it seems the guy who actually codes something offers a better solution than handwaving... people have also had plenty of time to come up with a replacement if that's what they really wanted. The patch submitter has neither provided an updated patch nor defended his original submission as being the right thing. If he doesn't take it seriously enough to have done any followup, why should the rest of us? At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. regards, tom lane I am willing to get it up to shape and support both COPY (select) TO and COPY view TO, the second is rewritten as SELECT * FROM view. In fact, I already started. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] COPY view
Hi, Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew It seems I was able to get it working for both the VIEW and SELECT cases. I still have one issue, the reference to the select is left open and it complains on closing the transaction. But basically works. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: Hi, Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew It seems I was able to get it working for both the VIEW and SELECT cases. I still have one issue, the reference to the select is left open and it complains on closing the transaction. But basically works. So when will you send in a revised patch? cheers andrew Soon. :-) Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 1: 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] [PATCHES] COPY view
Böszörményi Zoltán wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. I have to test it some more but I will send it. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: B?sz?rm?nyi Zolt?n wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. I have to test it some more but I will send it. I think Alvaro is saying we need it in a few days, not longer. Of course. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Question about GENERATED/IDENTITY
Hi, after some more reading, I am finally starting to grasp what Tom Lane meant with action at a distance. I outline below the information that I collected from the SQL2003 standard. Under section 11.5 default clause: Case: a) If the descriptor of S indicates that it represents a column of which some underlying column is an identity column or a generated column, then S is marked as unassigned. NOTE 250 #8212; The notion of a site being unassigned is only for definitional purposes in this International Standard. It is not a state that can persist so as to be visible in SQL-data. The treatment of unassigned sites is given in Subclause 14.19, Effect of inserting tables into base tables, and Subclause 14.22, Effect of replacing rows in base tables. I gathered this from those sections: - During both INSERT and UPDATE, the IDENTITY and GENERATED columns are evaluated after all base columns. [1] - Generated columns get their values based on the row's newly evaluated values, unlike base columns, that got their values based on the old row values. E.g. CREATE tab ( c1 GENERATED ALWAYS AS ( c2 * c2 ), c2 integer DEFAULT 1); INSERT INTO tab (c2) VALUES (3); -- (c1, c2) := (9, 3) UPDATE tab SET c1 = DEFAULT, c2 = c1 + 1; -- (c1, c2) := (100, 10) - If a column C is modified that the generated column GC depends on, effectively an , gc = DEFAULT is pulled in implicitely. Also, these have to be also implemented: - the expression in GENERATED ALWAYS AS (expr) can be pretty much everything just like with the CHECK constraint. Like CASE, column references from the same table, etc. No subselects. - If a column C is dropped and there is a generated column GC that depend on C, GC should also be dropped automatically. - Trigger definition cannot contain reference to any generated columns. [1] So, if I recall correctly what was said about NEXT VALUE FOR, if the above behaviour is implemented, NEXT VALUE FOR can be an alias of nextval(). It is still true, if I consider the following explicit definition CREATE SEQUENCE seq1; CREATE TABLE tab ( col1 integer DEFAULT NEXT VALUE FOR seq1, ...); Since col1 is a base column, not an identity, the value must be computed during the first evaluation pass, just like DEFAULT nextval(), e.g. SERIAL works currently. Did I misunderstood something? Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Extended SERIAL parsing
Zoltan Boszormenyi [EMAIL PROTECTED] writes:
after some experimentation, I came up with the attached patch,
which implements parsing the following SERIAL types:
As has been pointed out before, it would be a seriously bad idea to
implement the SQL syntax for identity columns without matching the
SQL semantics for them. That would leave us behind the eight-ball
when we wanted to implement the SQL semantics. Right now we have
a useful but non-standard semantics, and a useful but non-standard
syntax, and those two should stick together.
Well, I read all sections of 5WD-02-Foundation-2003-09.pdf
where identity appears, here are the list of changes that will
be needed for an identity column:
- Only one identity column can appear in the column list.
I have to check for this at CREATE, TABLE, ALTER TABLE ADD COLUMN
and ALTER TABLE ALTER COLUMN.
- ALTER TABLE ALTER COLUMN ... RESTART [WITH] or SET
alter the sequence on the column.
- If colname is SERIAL GENERATED ALWAYS, then
only UPDATE SER colname = default may occur.
Then there's the DROP default question: should PostgreSQL
allow it or not? What I found about this in the standard is this:
definitions of the DEFAULT clause, the identity column specification
and the generation clause are mutually exclusive, see 11.4.
So, if you cannot specify a DEFAULT for an identity column,
you must not be able to drop it, although this isn't expressed
in the standard, it's just my opinion.
Is there anything else? I haven't found any.
Or I can't read between the lines, which is a skill
that isn't required for reading standards. :-)
I'm not too happy with converting SERIAL4 and SERIAL8 into reserved
words, either, as I believe this patch does.
Not really, only IDENTITY is added to the list of reserved words,
serial/serial4/serial8/bigserial are just type names:
# create table serial8 (serial8 serial8 primary key);
NOTICE: CREATE TABLE will create implicit sequence serial8_serial8_seq
for serial column serial8.serial8
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
serial8_pkey for table serial8
CREATE TABLE
The others (AS, GENERATED) were added to the
non-reserved keyword list.
Some other things missing are documentation and pg_dump support.
I am working on that. The documentation is easier. :-)
Also note, that I misread the generated column syntax as part of the
identity column syntax. So, the parsing should only recognize
SERIAL [ GENERATED { ALWAYS | BY DEFAULT }
AS IDENTITY [ ( sequence_options ) ] ]
which I already fixed here and the sequence_options list
cannot be empty as with my previous attempt.
Best regards,
Zoltán Böszörményi
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
