Re: [HACKERS] ECPG FETCH readahead

2010-06-24 Thread Böszörményi Zoltán

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 Thread Böszörményi Zoltán

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 Thread Böszörményi Zoltán

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

2006-08-25 Thread Böszörményi Zoltán
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

2006-08-25 Thread Böszörményi Zoltán
 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

2006-08-23 Thread Böszörményi Zoltán
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

2006-08-23 Thread Böszörményi Zoltán
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

2006-08-23 Thread Böszörményi Zoltán
 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

2006-08-23 Thread Böszörményi Zoltán
 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

2006-08-23 Thread Böszörményi Zoltán
 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

2006-08-17 Thread Böszörményi Zoltán
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

2006-06-12 Thread Böszörményi Zoltán
 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