Re: [HACKERS] info about patch: using parametrised query in psql

2009-12-28 Thread Pavel Stehule
2009/12/28 Robert Haas robertmh...@gmail.com: On Fri, Dec 25, 2009 at 3:10 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Dec 25, 2009 at 2:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Works for me.  One small problem discussed upthread is that

Re: [HACKERS] [PATCH] bugfix for int2vectorin

2009-12-28 Thread Robert Haas
On Wed, Dec 2, 2009 at 2:59 PM, Caleb Welton cwel...@greenplum.com wrote: New patch attached: 1. Does not add a new error message (though the pg_atoi's error message is a little goofy looking). 2. Handles int2 overflow cases. 3. oidvectorin does NOT suffer from the same problems as

Re: [HACKERS] Application name patch - v3

2009-12-28 Thread Dave Page
On Sun, Dec 27, 2009 at 11:15 PM, Guillaume Lelarge guilla...@lelarge.info wrote: Le 13/11/2009 12:11, Dave Page a écrit : [...] What about pg_dump/psql setting fallback_application_name? Per Tom, I'm waiting on the possible new array-based libpq connect API which will make a conversion of

Re: [HACKERS] Small locking bugs in hs

2009-12-28 Thread Simon Riggs
On Mon, 2009-12-28 at 01:22 +0100, Andres Freund wrote: Btw, dont understand my questions as criticism or such. I didn't take them that way. Your questions and bug reports are welcome. It was important that HS was released in Alpha so that we can shake out bugs, issues and concerns early

[HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Boszormenyi Zoltan
Hi, attached is a small patch that makes it possible for clients to receive row count for SELECT ... INTO ... and CREATE TABLE ... AS ... Comments? Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is

Re: [HACKERS] Application name patch - v3

2009-12-28 Thread Guillaume Lelarge
Le 28/12/2009 10:07, Dave Page a écrit : On Sun, Dec 27, 2009 at 11:15 PM, Guillaume Lelarge guilla...@lelarge.info wrote: Le 13/11/2009 12:11, Dave Page a écrit : [...] What about pg_dump/psql setting fallback_application_name? Per Tom, I'm waiting on the possible new array-based libpq

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Pavel Stehule
2009/12/28 Boszormenyi Zoltan z...@cybertec.at: Hi, attached is a small patch that makes it possible for clients to receive row count for SELECT ... INTO ... and CREATE TABLE ... AS ... Comments? good idea +1 Pavel Best regards, Zoltán Böszörményi -- Bible has answers for

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Hans-Juergen Schoenig
hello ... just as a background info: this will have some positive side effects on embedded C programs which should be portable. informix, for instance, will also return a row count on those commands. regards, hans Pavel Stehule wrote: 2009/12/28 Boszormenyi Zoltan

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Boszormenyi Zoltan
Hans-Juergen Schoenig írta: hello ... just as a background info: this will have some positive side effects on embedded C programs which should be portable. Not just embedded C programs, every driver that's based on libpq and used PQcmdTuples() will automatically see the benefit. informix,

[HACKERS] Re: Streaming Rep - 2-phase backups and reducing time to full replication

2009-12-28 Thread Simon Riggs
On Fri, 2009-12-25 at 14:33 +0900, Fujii Masao wrote: On Thu, Dec 24, 2009 at 6:03 PM, Simon Riggs si...@2ndquadrant.com wrote: I see it would work like this: Add a new option to recovery.conf, perhaps two_phase_backup = on. Startup creates a file called backup_in_progress then waits. When

Re: [HACKERS] join ordering via Simulated Annealing

2009-12-28 Thread Jan Urbański
Andres Freund wrote: On Wednesday 23 December 2009 02:23:55 Jan Urbański wrote: Lastly, I'm lacking good testcases If you want to see some queries which are rather hard to plan with random search you can look at http://archives.postgresql.org/message-

Re: [HACKERS] updateMinRecoveryPoint bug?

2009-12-28 Thread Heikki Linnakangas
Fujii Masao wrote: In UpdateMinRecoveryPoint() and XLogNeedsFlush(), updateMinRecoveryPoint is used for us to short-circuit future checks only during a crash recovery. But it doesn't seem to play its role in a crash recovery that follows an archive recovery. Because such crash recovery always

Re: [HACKERS] Removing pg_migrator limitations

2009-12-28 Thread Bruce Momjian
Robert Haas wrote: On Sun, Dec 27, 2009 at 2:16 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Dec 27, 2009 at 9:53 AM, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: There are several pg_migrator limitations that appeared late in the 8.4 development cycle and were

Re: [HACKERS] Removing pg_migrator limitations

2009-12-28 Thread Robert Haas
On Mon, Dec 28, 2009 at 10:48 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Sun, Dec 27, 2009 at 2:16 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Dec 27, 2009 at 9:53 AM, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: There are several pg_migrator

Re: [HACKERS] Application name patch - v3

2009-12-28 Thread Tom Lane
Guillaume Lelarge guilla...@lelarge.info writes: Le 28/12/2009 10:07, Dave Page a écrit : Yes, still waiting on the new API. Is there something I can do to make this move forward? I think we were stalled on the question of whether to use one array or two parallel arrays. Do you want to try

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Tom Lane
Boszormenyi Zoltan z...@cybertec.at writes: Hans-Juergen Schoenig írta: just as a background info: this will have some positive side effects on embedded C programs which should be portable. Not just embedded C programs, every driver that's based on libpq and used PQcmdTuples() will

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Peter Eisentraut
On mån, 2009-12-28 at 11:08 -0500, Tom Lane wrote: Boszormenyi Zoltan z...@cybertec.at writes: Hans-Juergen Schoenig írta: just as a background info: this will have some positive side effects on embedded C programs which should be portable. Not just embedded C programs, every driver

Re: [HACKERS] updateMinRecoveryPoint bug?

2009-12-28 Thread Simon Riggs
On Mon, 2009-12-28 at 14:40 +0200, Heikki Linnakangas wrote: Fujii Masao wrote: How about always resetting ControlFile-minRecoveryPoint to {0, 0} at the beginning of a crash recovery, to fix the bug? Yeah, that would work. I think it would be better to clear it in CreateCheckPoint(),

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On mån, 2009-12-28 at 11:08 -0500, Tom Lane wrote: And, by the same token, the scope for possibly breaking clients is nearly unlimited ... Why is that? Are there programs out there that expect PQcmdTuples() to return something that is *not* the

Re: [HACKERS] Removing pg_migrator limitations

2009-12-28 Thread Bruce Momjian
Robert Haas wrote: On Mon, Dec 28, 2009 at 10:48 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Sun, Dec 27, 2009 at 2:16 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Dec 27, 2009 at 9:53 AM, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: There

Re: [HACKERS] New VACUUM FULL

2009-12-28 Thread Robert Haas
On Tue, Dec 22, 2009 at 7:29 AM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2009-12-22 at 19:45 +0900, Takahiro Itagaki wrote: I used VACUUM FULL because we were discussing to drop VFI completely, but I won't replace the behavior if hot-standby can support VFI. HS can't support VFI

[HACKERS] psql 8.4 \c repeats version banner

2009-12-28 Thread Peter Eisentraut
In 8.3, running \c from a file prints something like You are now connected to database postgres. In 8.4 it prints psql (8.4.1) You are now connected to database postgres. Is it intentional/sensible to repeat the startup banner every time the connection changes, or was this unintentionally

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Tom Lane
Boszormenyi Zoltan z...@cybertec.at writes: attached is a small patch that makes it possible for clients to receive row count for SELECT ... INTO ... and CREATE TABLE ... AS ... Comments? This doesn't look tremendously well thought out to me. 1. As given, the patch changes the result not

Re: [HACKERS] psql 8.4 \c repeats version banner

2009-12-28 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: In 8.3, running \c from a file prints something like You are now connected to database postgres. In 8.4 it prints psql (8.4.1) You are now connected to database postgres. Is it intentional/sensible to repeat the startup banner every time the

[HACKERS] Serializable implementation

2009-12-28 Thread Kevin Grittner
For perspective: Since our current application framework is about ten years old now, the Wisconsin State Courts System has put together a Long-Term Application Development Group to review all aspects of our development and production runtime environments. We started by reviewing various aspect

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Boszormenyi Zoltan
Tom Lane írta: Peter Eisentraut pete...@gmx.net writes: On mĂĽn, 2009-12-28 at 11:08 -0500, Tom Lane wrote: And, by the same token, the scope for possibly breaking clients is nearly unlimited ... Why is that? Are there programs out there that expect PQcmdTuples() to

[HACKERS] ExecTidReScan exprCtxt

2009-12-28 Thread Robert Haas
In ExecTidReScan, we have the following: /* If we are being passed an outer tuple, save it for runtime key calc */ if (exprCtxt != NULL) node-ss.ps.ps_ExprContext-ecxt_outertuple = exprCtxt-ecxt_outertuple; Is this dead code? I have been

Re: [HACKERS] ExecTidReScan exprCtxt

2009-12-28 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: In ExecTidReScan, we have the following: /* If we are being passed an outer tuple, save it for runtime key calc */ if (exprCtxt != NULL) node-ss.ps.ps_ExprContext-ecxt_outertuple =

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Tom Lane
Boszormenyi Zoltan z...@cybertec.at writes: Tom Lane írta: It's more the possibility of doing strcmp(tag, SELECT) on the command Actually it's strncmp(tag, SELECT , 7), so when you mix old server with new clients or new server with old client, it will just work as before, i.e. return . Are

[HACKERS] Admission Control Policy

2009-12-28 Thread Kevin Grittner
This paper has a brief but interesting discussion of Admission Control in section 2.4: Architecture of a Database System. (Joseph M. Hellerstein, Michael Stonebraker and James Hamilton). Foundations and Trends in Databases 1(2). http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf They

[HACKERS] Port for Microsoft Services for Unix (SFU) or SUA

2009-12-28 Thread Josh Rovero
Has anyone built postgresql (or just libpq.a) on Windows SFU/SUA? Would prefer to not reinvent any wheels We have a number of Unix/Linux applications that are also compiled under Microsoft Windows SFU 3.5. We need to have a SFU-compatible libpq.a, not a complete install. The regular

Re: [HACKERS] Application name patch - v3

2009-12-28 Thread Guillaume Lelarge
Le 28/12/2009 17:06, Tom Lane a écrit : Guillaume Lelarge guilla...@lelarge.info writes: Le 28/12/2009 10:07, Dave Page a écrit : Yes, still waiting on the new API. Is there something I can do to make this move forward? I think we were stalled on the question of whether to use one array

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Dimitri Fontaine
Hi, Le 28 déc. 2009 à 21:33, Kevin Grittner a écrit : We often see posts from people who have more active connections than is efficient. How would your proposal better solve the problem than using pgbouncer? mad proposal time I'd be in favor of considering how to get pgbouncer into -core, and

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Andres Freund
On Monday 28 December 2009 22:39:06 Dimitri Fontaine wrote: Hi, Le 28 déc. 2009 à 21:33, Kevin Grittner a écrit : We often see posts from people who have more active connections than is efficient. How would your proposal better solve the problem than using pgbouncer? mad proposal

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Dimitri Fontaine
Le 28 déc. 2009 à 22:46, Andres Freund a écrit : mad proposal time I'd be in favor of considering how to get pgbouncer into -core, and now that we have Hot Standby maybe implement a mode in which as soon as a real XID is needed, or maybe upon receiving start transaction read write command,

Re: [HACKERS] Application name patch - v3

2009-12-28 Thread Tom Lane
Guillaume Lelarge guilla...@lelarge.info writes: Le 28/12/2009 17:06, Tom Lane a écrit : I think we were stalled on the question of whether to use one array or two parallel arrays. Do you want to try coding up a sample usage of each possibility so we can see which one seems more useful? I'm

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Kevin Grittner
Dimitri Fontaine dfonta...@hi-media.com wrote: Le 28 déc. 2009 à 21:33, Kevin Grittner a écrit : We often see posts from people who have more active connections than is efficient. How would your proposal better solve the problem than using pgbouncer? With my current knowledge of pgbouncer

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Dimitri Fontaine
Le 28 déc. 2009 à 22:59, Kevin Grittner a écrit : With my current knowledge of pgbouncer I can't answer that definitively; but *if* pgbouncer, when configured for transaction pooling, can queue new transaction requests until a connection is free, then the differences would be: It does that,

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Kevin Grittner
Dimitri Fontaine dfonta...@hi-media.com wrote: That's why there's both transaction and session pooling. The benefit of session pooling is to avoid forking backends, reusing them instead, and you still get the pooling control. So the application would need to open and close a pgbouncer

[HACKERS] Stats for inheritance trees

2009-12-28 Thread Tom Lane
Following up on the discussion here http://archives.postgresql.org/message-id/4b3875c602250002d...@gw.wicourts.gov I'd like to propose making the following changes that would allow saner planning for queries involving inheritance: 1. Currently the primary key of pg_statistic is (starelid,

[HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Andres Freund
On Saturday 12 December 2009 21:38:41 Andres Freund wrote: On Saturday 12 December 2009 21:36:27 Michael Clemmons wrote: If ppl think its worth it I'll create a ticket Thanks, no need. I will post a patch tomorrow or so. Well. It was a long day... Anyway. In this patch I delay the fsync done

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Dimitri Fontaine
Le 28 déc. 2009 à 23:35, Kevin Grittner a écrit : So the application would need to open and close a pgbouncer connection for each database transaction in order to share the backend properly? No, in session pooling you get the same backend connection for the entire pgbouncer connection, it's a

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Kevin Grittner
Dimitri Fontaine dfonta...@hi-media.com wrote: Le 28 déc. 2009 à 22:59, Kevin Grittner a écrit : (3) With the ACP, the statements would be parsed and optimized before queuing, so they would be ready to execute as soon as a connection was freed. There's a pgfoundry project called

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Dimitri Fontaine
Le 28 déc. 2009 à 23:56, Kevin Grittner a écrit : http://preprepare.projects.postgresql.org/README.html I just reviewed the documentation for preprepare -- I can see a use case for that, but I really don't think it has a huge overlap with my point. The parsing and planning mentioned in my

Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Andres Freund
On Monday 28 December 2009 23:54:51 Andres Freund wrote: On Saturday 12 December 2009 21:38:41 Andres Freund wrote: On Saturday 12 December 2009 21:36:27 Michael Clemmons wrote: If ppl think its worth it I'll create a ticket Thanks, no need. I will post a patch tomorrow or so. Well.

Re: [HACKERS] Application name patch - v3

2009-12-28 Thread Guillaume Lelarge
Le 28/12/2009 22:59, Tom Lane a écrit : Guillaume Lelarge guilla...@lelarge.info writes: Le 28/12/2009 17:06, Tom Lane a écrit : I think we were stalled on the question of whether to use one array or two parallel arrays. Do you want to try coding up a sample usage of each possibility so we

Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Tom Lane
Andres Freund and...@anarazel.de writes: This speeds up CREATE DATABASE from ~9 seconds to something around 0.8s on my laptop. Still slower than with fsync off (~0.25) but quite a worthy improvement. I can't help wondering whether that's real or some kind of platform-specific artifact. I

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Kevin Grittner
Dimitri Fontaine dfonta...@hi-media.com wrote: No, in session pooling you get the same backend connection for the entire pgbouncer connection, it's a 1-1 mapping. Right -- so it doesn't allow more logical connections than that with a limit to how many are active at any one time, *unless* the

Re: [HACKERS] Serializable implementation

2009-12-28 Thread Jeff Davis
On Mon, 2009-12-28 at 11:54 -0600, Kevin Grittner wrote: Serializable transaction isolation is attractive for shops with active development by many programmers against a complex schema because it guarantees data integrity with very little staff time -- I would like to see true serializability

Re: [HACKERS] Serializable implementation

2009-12-28 Thread Florian G. Pflug
On 28.12.09 18:54 , Kevin Grittner wrote: To give some idea of the scope of development, Michael Cahill added SSI to InnoDB by modifying 250 lines of code and adding 450 lines of code; however, InnoDB already had the S2PL option and the prototype implementation isn't as sophisticated as I feel

Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Andres Freund
On Tuesday 29 December 2009 00:06:28 Tom Lane wrote: Andres Freund and...@anarazel.de writes: This speeds up CREATE DATABASE from ~9 seconds to something around 0.8s on my laptop. Still slower than with fsync off (~0.25) but quite a worthy improvement. I can't help wondering whether

Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Andres Freund
On Tuesday 29 December 2009 00:06:28 Tom Lane wrote: Andres Freund and...@anarazel.de writes: This speeds up CREATE DATABASE from ~9 seconds to something around 0.8s on my laptop. Still slower than with fsync off (~0.25) but quite a worthy improvement. I can't help wondering whether

[HACKERS] special cases of true serializability

2009-12-28 Thread Jeff Davis
I was thinking about true serializability, and started thinking that there are a lot of special cases where true serializability can be achieved without modification. For instance, the following is problematic: BEGIN; SELECT count(*) FROM mytable; ... because future insert/update/deletes

Re: [HACKERS] Serializable implementation

2009-12-28 Thread Kevin Grittner
Jeff Davis pg...@j-davis.com wrote: I don't know what you mean by get-a-new-snapshot strategy or how it is different from the current read committed behavior. Our current Read Committed level, if it blocks on a competing UPDATE or DELETE, can provide a view of data which is based on a mix of

Re: [HACKERS] special cases of true serializability

2009-12-28 Thread Kevin Grittner
Jeff Davis pg...@j-davis.com wrote: How hard would it be to reliably detect the transactions for which snapshot isolation already means true serializability? To answer that question, there's really no substitute for reading this: http://hdl.handle.net/2123/5353 -Kevin -- Sent via

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Robert Haas
On Mon, Dec 28, 2009 at 3:33 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: They describe a two-tier approach, where the first tier is already effectively implemented in PostgreSQL with the max_connections and superuser_reserved_connections GUCs.  The second tier is implemented to run

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: It seems like it might be helpful, before tackling what you're talking about here, to have some better tools for controlling resource utilization. Right now, the tools we have a pretty crude. You can't even nice/ionice a certain backend without

Re: [HACKERS] ExecTidReScan exprCtxt

2009-12-28 Thread Robert Haas
On Mon, Dec 28, 2009 at 2:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: In ExecTidReScan, we have the following:         /* If we are being passed an outer tuple, save it for runtime key calc */         if (exprCtxt != NULL)                

Re: [HACKERS] Serializable implementation

2009-12-28 Thread Greg Stark
On Mon, Dec 28, 2009 at 11:20 PM, Florian G. Pflug f...@phlo.org wrote: I believe the hard part of implementing true serializability is not the actual SSI or S2PL algorithm, but rather the necessary predicate locking strategy. So I think checking how InnoDB tackles that and how much of it's

[HACKERS] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Greg Stark
On Mon, Dec 28, 2009 at 10:54 PM, Andres Freund and...@anarazel.de wrote: fsync everything in that pass. Including the directory - which was not done before and actually might be necessary in some cases. Er. Yes. At least on ext4 this is pretty important. I wish it weren't, but it doesn't look

Re: [HACKERS] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Andres Freund
On Tuesday 29 December 2009 01:27:29 Greg Stark wrote: On Mon, Dec 28, 2009 at 10:54 PM, Andres Freund and...@anarazel.de wrote: fsync everything in that pass. Including the directory - which was not done before and actually might be necessary in some cases. Er. Yes. At least on ext4

[HACKERS] parse tree to XML format

2009-12-28 Thread matt
Is there some way to export the postgresql query parse tree in XML format? I can not locate the API/Tool etc to do that... thanks -Matt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] parse tree to XML format

2009-12-28 Thread Andres Freund
On Monday 28 December 2009 22:30:44 matt wrote: Is there some way to export the postgresql query parse tree in XML format? I can not locate the API/Tool etc to do that... Thats more of a -general question. There is no such possibility in 8.4 - the not yet released 8.5 contains such a

Re: [HACKERS] parse tree to XML format

2009-12-28 Thread Robert Haas
On Mon, Dec 28, 2009 at 7:32 PM, Andres Freund and...@anarazel.de wrote: On Monday 28 December 2009 22:30:44 matt wrote: Is there some way to export the postgresql query parse tree in XML format?  I can not locate the API/Tool etc to do that... Thats more of a -general question. There is no

Re: [HACKERS] Admission Control Policy

2009-12-28 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: The second tier is implemented to run after a plan is chosen, and may postpone execution of a query (or reduce the resources it is allowed) if starting it at that time might overload available

Re: [HACKERS] parse tree to XML format

2009-12-28 Thread Andres Freund
On Tuesday 29 December 2009 01:35:25 Robert Haas wrote: On Mon, Dec 28, 2009 at 7:32 PM, Andres Freund and...@anarazel.de wrote: On Monday 28 December 2009 22:30:44 matt wrote: Is there some way to export the postgresql query parse tree in XML format? I can not locate the API/Tool etc to do

Re: [HACKERS] Serializable implementation

2009-12-28 Thread Kevin Grittner
Greg Stark gsst...@mit.edu wrote: Talking about the details of the conflict resolution algorithms and the benefits of serializable mode to your development methodology is all fantasy as long as you don't have any approaches to solve actually being able to detect the conflicts in the first

[HACKERS] Re: [PERFORM] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Andres Freund
On Tuesday 29 December 2009 01:30:17 da...@lang.hm wrote: On Tue, 29 Dec 2009, Greg Stark wrote: On Mon, Dec 28, 2009 at 10:54 PM, Andres Freund and...@anarazel.de wrote: fsync everything in that pass. Including the directory - which was not done before and actually might be necessary in

[HACKERS] Re: [PERFORM] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread david
On Tue, 29 Dec 2009, Greg Stark wrote: On Mon, Dec 28, 2009 at 10:54 PM, Andres Freund and...@anarazel.de wrote: fsync everything in that pass. Including the directory - which was not done before and actually might be necessary in some cases. Er. Yes. At least on ext4 this is pretty

[HACKERS] Re: [PERFORM] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread david
On Tue, 29 Dec 2009, Andres Freund wrote: On Tuesday 29 December 2009 01:30:17 da...@lang.hm wrote: On Tue, 29 Dec 2009, Greg Stark wrote: On Mon, Dec 28, 2009 at 10:54 PM, Andres Freund and...@anarazel.de wrote: fsync everything in that pass. Including the directory - which was not done

Re: [PERFORM] [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Greg Smith
Andres Freund wrote: As I said the real benefit only occurred after adding posix_fadvise(.., FADV_DONTNEED) which is somewhat plausible, because i.e. the directory entries don't need to get scheduled for every file and because the kernel can reorder a whole directory nearly sequentially.

[HACKERS] exec_execute_message crush

2009-12-28 Thread Tatsuo Ishii
While inspecting a complain from a pgpool user, I found that PostgreSQL crushes with following statck trace: #0 0x0826436a in list_length (l=0xaabe4e28) at ../../../src/include/nodes/pg_list.h:94 #1 0x08262168 in IsTransactionStmtList (parseTrees=0xaabe4e28) at postgres.c:2429 #2

Re: [HACKERS] exec_execute_message crush

2009-12-28 Thread Tom Lane
Tatsuo Ishii is...@postgresql.org writes: It seems the source of the problem is, exec_execute_message tries to execute unamed portal which has unnamed statement which has already gone. Could we see an actual test case? regards, tom lane -- Sent via pgsql-hackers

Re: [HACKERS] exec_execute_message crush

2009-12-28 Thread Tatsuo Ishii
Tatsuo Ishii is...@postgresql.org writes: It seems the source of the problem is, exec_execute_message tries to execute unamed portal which has unnamed statement which has already gone. Could we see an actual test case? If you don't mind to use pgpool, it would be possible. If not, I

Re: [PERFORM] [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Andres Freund
On Tuesday 29 December 2009 01:46:21 Greg Smith wrote: Andres Freund wrote: As I said the real benefit only occurred after adding posix_fadvise(.., FADV_DONTNEED) which is somewhat plausible, because i.e. the directory entries don't need to get scheduled for every file and because the

Re: [HACKERS] exec_execute_message crush

2009-12-28 Thread Tom Lane
Tatsuo Ishii is...@postgresql.org writes: Could we see an actual test case? If you don't mind to use pgpool, it would be possible. If not, I need to write a small program which handles frontend/backend protocol directly. What shall I do? Hm, can't you get libpq to do it?

Re: [HACKERS] exec_execute_message crush

2009-12-28 Thread Tatsuo Ishii
If you don't mind to use pgpool, it would be possible. If not, I need to write a small program which handles frontend/backend protocol directly. What shall I do? Hm, can't you get libpq to do it? That depends on how libpq is intelligent:-) Let me try... Another idea is a packet

Re: [PERFORM] [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Michael Clemmons
Andres, Great job. Looking through the emails and thinking about why this works I think this patch should significantly speedup 8.4 on most any file system(obviously some more than others) unless the system has significantly reduced memory or a slow single core. On a Celeron with 256 memory I

Re: [PERFORM] [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Andres Freund
On Tuesday 29 December 2009 03:53:12 Michael Clemmons wrote: Andres, Great job. Looking through the emails and thinking about why this works I think this patch should significantly speedup 8.4 on most any file system(obviously some more than others) unless the system has significantly

Re: [PERFORM] [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Michael Clemmons
Maybe not crash out but in this situation. N=0 while(N=0): CREATE DATABASE new_db_N; Since the fsync is the part which takes the memory and time but is happening in the background want the fsyncs pile up in the background faster than can be run filling up the memory and stack. This is very

Re: [PERFORM] [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

2009-12-28 Thread Andres Freund
On Tuesday 29 December 2009 04:04:06 Michael Clemmons wrote: Maybe not crash out but in this situation. N=0 while(N=0): CREATE DATABASE new_db_N; Since the fsync is the part which takes the memory and time but is happening in the background want the fsyncs pile up in the background

Re: [HACKERS] Serializable implementation

2009-12-28 Thread Bruce Momjian
Kevin Grittner wrote: Jeff Davis pg...@j-davis.com wrote: I don't know what you mean by get-a-new-snapshot strategy or how it is different from the current read committed behavior. Our current Read Committed level, if it blocks on a competing UPDATE or DELETE, can provide a view of

Re: [HACKERS] exec_execute_message crush

2009-12-28 Thread Tatsuo Ishii
Hm, can't you get libpq to do it? That depends on how libpq is intelligent:-) Let me try... Another idea is a packet recorder, which could record packets from pgpool to PostgreSQL and replay them. I don't remember at present, but I vaguely recall something like that exists. It seems we

Re: [HACKERS] exec_execute_message crush

2009-12-28 Thread Tom Lane
Tatsuo Ishii is...@postgresql.org writes: Hm, can't you get libpq to do it? It seems we can't get libpq to do it. libpq does not provide a function which can execute bind alone. In my understanding PQexecPrepared does bind + execute. The event sequence you mentioned had bind followed by

Re: [HACKERS] exec_execute_message crush

2009-12-28 Thread Tatsuo Ishii
(In any case, some kind of quick lobotomy in libpq would be easier than writing a standalone test program, no?) Sounds nice idea. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] exec_execute_message crush

2009-12-28 Thread Kris Jurka
On Tue, 29 Dec 2009, Tatsuo Ishii wrote: parse bind describe execute normaly done parse invalid SQL thus abort a transaction bind (error) describe (error) execute (crush) Please note that without pgpool backend does not crush. This is because JDBC driver does not do execute() if prior parse,

Re: [HACKERS] exec_execute_message crush

2009-12-28 Thread Tatsuo Ishii
parse bind describe execute normaly done parse invalid SQL thus abort a transaction bind (error) describe (error) execute (crush) Please note that without pgpool backend does not crush. This is because JDBC driver does not do execute() if prior parse, bind etc. failed, I

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Boszormenyi Zoltan
Tom Lane írta: Boszormenyi Zoltan z...@cybertec.at writes: Tom Lane írta: It's more the possibility of doing strcmp(tag, SELECT) on the command Actually it's strncmp(tag, SELECT , 7), so when you mix old server with new clients or new server with old client, it will

Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs

2009-12-28 Thread Boszormenyi Zoltan
Tom Lane írta: Boszormenyi Zoltan z...@cybertec.at writes: attached is a small patch that makes it possible for clients to receive row count for SELECT ... INTO ... and CREATE TABLE ... AS ... Comments? This doesn't look tremendously well thought out to me. 1. As