PL/LOLCODE [was Re: [HACKERS] [PATCH] \ef function in psql]
On Mon, Aug 04, 2008 at 10:31:10AM -0700, David Wheeler wrote: On Jul 31, 2008, at 00:07, Abhijit Menon-Sen wrote: I have attached two patches: - funcdef.diff implements pg_get_functiondef() - edit.diff implements \ef function in psql based on (1). Comments appreciated. +1 I like! The ability to easily edit a function on the fly in psql will be very welcome to DBAs I know. And I like the pg_get_functiondef() function, too, a that will simplify editing existing functions in other admin apps, like pgAdmin. I'm starting to get really excited for 8.4. I can haz cheezburger? You do understand you've just kicked off a discussion of shipping PL/LOLCODE by default. Oops, I mean, when does it ship? ;-P Christmas ;) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] NDirectFileRead and Write
Here is a patch to user NDirectFileRead/Write counters to get I/O counts in BufFile module. We can see the counters when log_statement_stats is on. The information is different from trace_sort; trace_sort shows used blocks in external sort, and log_statement_stats shows how many I/Os are submitted during sorts. I wrote: I'd like to use NDirectFileRead and NDirectFileWrite statistics counters for counting reads and writes in BufFile. They are defined, but not used now. BufFile is used for tuple sorting or materializing, so we could use NDirectFileRead/Write to retrieve how many I/Os are done in temp tablespace. =# SET client_min_messages = log; =# SET trace_sort = on; =# SET log_statement_stats = on; =# EXPLAIN ANALYZE SELECT * FROM generate_series(1, 100) AS i ORDER BY i; LOG: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f LOG: switching to external sort with 7 tapes: CPU 0.09s/0.26u sec elapsed 0.35 sec LOG: performsort starting: CPU 0.48s/1.68u sec elapsed 2.20 sec LOG: finished writing final run 1 to tape 0: CPU 0.48s/1.70u sec elapsed 2.21 sec LOG: performsort done: CPU 0.48s/1.70u sec elapsed 2.21 sec LOG: external sort ended, 2444 disk blocks used: CPU 0.79s/2.23u sec elapsed 3.06 sec LOG: QUERY STATISTICS DETAIL: ! system usage stats: ! 3.078000 elapsed 2.234375 user 0.812500 system sec ! [3.328125 user 1.281250 sys total] ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 5375 read, 5374 written QUERY PLAN Sort (cost=62.33..64.83 rows=1000 width=4) (actual time=2221.485..2743.831 rows=100 loops=1) Sort Key: i Sort Method: external sort Disk: 19552kB - Function Scan on generate_series i (cost=0.00..12.50 rows=1000 width=4) (actual time=349.065..892.907 rows=100 loops=1) Total runtime: 3087.305 ms (5 rows) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center NDirectFileReadWrite.patch Description: Binary data -- 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] Mini improvement: statement_cost_limit
Simon Riggs wrote: On Sun, 2008-08-03 at 22:09 +0200, Hans-Jürgen Schönig wrote: Another alternative would be to have a plugin that can examine the plan immediately after planner executes, so you can implement this yourself, plus some other possibilities. this would be really fancy. how could a plugin like that look like? Hmm...thinks: exactly like the existing planner_hook(). So, rewrite this as a planner hook and submit as a contrib module. Now that's a good idea! I personally don't think this feature is a good idea, for all the reasons others have mentioned, but as a pgfoundry project it can be downloaded by those who want it, and perhaps prove its usefulness for others as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Automatic Client Failover
On Mon, 2008-08-04 at 22:56 -0400, Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: I think the proposal was for an extremely simple works 75% of the time failover solution. While I can see the attraction of that, the consequences of having failover *not* work are pretty severe. Exactly. The point of failover (or any other HA feature) is to get several nines worth of reliability. It usually works is simply not playing in the right league. Why would you all presume that I haven't thought about the things you mention? Where did I say ...and this would be the only feature required for full and correct HA failover. The post is specifically about Client Failover, as the title clearly states. Your comments were illogical anyway, since if it was so bad a technique then it would not work for pgpool either, since it is also a client. If pgpool can do this, why can't another client? Why can't *all* clients? With correctly configured other components the primary will shut down if it is no longer the boss. The client will then be disconnected. If it switches to its secondary connection, we can have an option to read session_replication_role to ensure that this is set to origin. This covers the case where the client has lost connection with primary, though it is still up, yet can reach the standby which has not changed state. DB2, SQLServer and Oracle all provide this feature, BTW. We don't need to follow, but we should do that consciously. I'm comfortable with us deciding not to do it, if that is our considered judgement. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Reliability of CURRVAL in a RULE
Is the use of CURRVAL in this example reliable in heavy use?
CREATE TABLE users (
id SERIAL NOT NULL,
email VARCHAR(24) DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE users_with_email (
id INTEGER NOT NULL
);
CREATE RULE add_email AS ON INSERT TO users WHERE (NEW.email IS NULL)
DO INSERT INTO users_with_email (id) VALUES (CURRVAL('users_id_seq'));
I tried...
CREATE RULE add_email AS ON INSERT TO users WHERE (NEW.email IS NULL)
DO INSERT INTO users_with_email (id) VALUES (NEW.id);
which was incrementing the sequence twice. Should I be using a trigger
instead? This rule seems quite simple and easy enough... if reliable. -
Nick
--
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] Reliability of CURRVAL in a RULE
Nick wrote:
Is the use of CURRVAL in this example reliable in heavy use?
Nick - the hackers list is for people interested in working on the
code-base of PostgreSQL itself. This would have been better on the
general or sql lists.
CREATE RULE add_email AS ON INSERT TO users WHERE (NEW.email IS NULL)
DO INSERT INTO users_with_email (id) VALUES (CURRVAL('users_id_seq'));
Short answer no. Rules are like macros and you can end up with
unexpected multiple evaluations and strange order of execution. See the
mailing list archives for details and try inserting multiple users in
one go to see an example of a problem.
--
Richard Huxton
Archonet Ltd
--
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] Automatic Client Failover
Greg On 5-Aug-08, at 12:15 AM, Tom Lane [EMAIL PROTECTED] wrote: There is one really bad consequence of the oversimplified failover design that Simon proposes, which is that clients might try to fail over for reasons other than a primary server failure. (Think network partition.) You really want any such behavior to be managed centrally, IMHO. The alternative to a cwnrallu managed failover system is one based on a quorum system. At first glance it seems to me that would fit our use case better. But the point remains that we would be better off adopting a complete system than trying to reinvent one. -- 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] Automatic Client Failover
Le mardi 05 août 2008, Tom Lane a écrit : Huh? The problem case is that the primary server goes down, which would certainly mean that a pgbouncer instance on the same machine goes with it. So it seems to me that integrating pgbouncer is 100% backwards. With all due respect, it seems to me you're missing an important piece of the scheme here: I certainly failed to explain correctly. Of course, I'm not sure (by and large) that detailing what I have in mind will answer your concerns, but still... What I have in mind is having the pgbouncer listening process both at master and slave sites. So your clients can already connect to slave for normal operations, and the listener process simply connects them to the master, transparently. When we later provider RO slave, some queries could be processed locally instead of getting sent to the master. The point being that the client does not have to care itself whether it's connecting to a master or a slave, -core knows what it can handle for the client and handles it (proxying the connection). Now, that does not solve the client side automatic failover per-se, it's another way to think about it: - both master slave accept connection in any mode - master slave are able to speak to each other (life link) - when master knows it's crashing (elog(FATAL)), it can say so to the slave - when said so, slave can switch to master It obviously only catches some errors on master, the ones we're able to log about. So it does nothing on its own for allowing HA in case of master crash. But... Failover that actually works is not something we can provide with trivial changes to Postgres. It's really a major project in its own right: you need heartbeat detection, STONITH capability, IP address redirection, etc. I think we should be recommending external failover-management project(s) instead of offering a half-baked home-grown solution. Searching freshmeat for failover finds plenty of potential candidates, but not having used any of them I'm not sure which are worth closer investigation. We have worked here with heartbeat, and automating failover is hard. Not for technical reasons only, also because: - current PostgreSQL offers no sync replication, switching means trading or losing the D in ACID, - you do not want to lose any commited data. If 8.4 resolve this, failover implementation will be a lot easier. What I see my proposal fit is the ability to handle a part of the smartness in -core directly, so the hard part of the STONITH/failover/switchback could be implemented in cooperation with -core, not playing tricks against it. For example, switching back when master gets back online would only means for the master to tell the slave to now redirect the queries to him as soon as it's ready --- which still is the hard part, sync back data. Having clients able to blindly connect to master or any slave and having the current cluster topology smartness into -core would certainly help here, even if not fullfilling all HA goals. Of course, in the case of master hard crash, we still have to get sure it won't restart on its own, and we have to have an external way to get a chosen slave become the master. I'm even envisioning than -core could help STONITH projects with having sth like the recovery.conf file for the master to restart in not-up-to-date slave mode. Whether we implement resyncing to the new master in -core or from external scripts is another concern, but certainly -core could help here (even if not in 8.4, of course). I'm still thinking that this proposal has a place in the scheme of an integrated HA solution and offers interresting bits. Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Automatic Client Failover
On Tue, 2008-08-05 at 07:52 +0100, Simon Riggs wrote:
On Mon, 2008-08-04 at 22:56 -0400, Tom Lane wrote:
Josh Berkus [EMAIL PROTECTED] writes:
I think the proposal was for an extremely simple works 75% of the time
failover solution. While I can see the attraction of that, the
consequences of having failover *not* work are pretty severe.
Exactly. The point of failover (or any other HA feature) is to get
several nines worth of reliability. It usually works is simply
not playing in the right league.
Why would you all presume that I haven't thought about the things you
mention? Where did I say ...and this would be the only feature required
for full and correct HA failover. The post is specifically about Client
Failover, as the title clearly states.
I guess having the title Automatic Client Failover suggest to most
readers, that you are trying to solve the client side separately from
server.
Your comments were illogical anyway, since if it was so bad a technique
then it would not work for pgpool either, since it is also a client. If
pgpool can do this, why can't another client? Why can't *all* clients?
IIRC pgpool was itself a poor-mans replication solution, so it _is_ the
point of doing failover.
With correctly configured other components the primary will shut down if
it is no longer the boss. The client will then be disconnected. If it
switches to its secondary connection, we can have an option to read
session_replication_role to ensure that this is set to origin.
Probably this should not be an option, but a must.
maybe session_replication_role should be a DBA-defined function, so that
the same client failover mechanism can be applied to different
replication solutions, both server-built-in and external.
create function session_replication_role()
returns enum('master','ro-slave','please-wait-coming-online','...')
$$
...
This
covers the case where the client has lost connection with primary,
though it is still up, yet can reach the standby which has not changed
state.
DB2, SQLServer and Oracle all provide this feature, BTW. We don't need
to follow, but we should do that consciously. I'm comfortable with us
deciding not to do it, if that is our considered judgement.
The main argument seemed to be, that it can't be Automatic Client-ONLY
Failover.
--
Hannu
--
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] Automatic Client Failover
On Tue, 2008-08-05 at 11:50 +0300, Hannu Krosing wrote:
On Tue, 2008-08-05 at 07:52 +0100, Simon Riggs wrote:
On Mon, 2008-08-04 at 22:56 -0400, Tom Lane wrote:
Josh Berkus [EMAIL PROTECTED] writes:
I think the proposal was for an extremely simple works 75% of the
time
failover solution. While I can see the attraction of that, the
consequences of having failover *not* work are pretty severe.
Exactly. The point of failover (or any other HA feature) is to get
several nines worth of reliability. It usually works is simply
not playing in the right league.
Why would you all presume that I haven't thought about the things you
mention? Where did I say ...and this would be the only feature required
for full and correct HA failover. The post is specifically about Client
Failover, as the title clearly states.
I guess having the title Automatic Client Failover suggest to most
readers, that you are trying to solve the client side separately from
server.
Yes, that's right: separately. Why would anybody presume I meant and by
the way you can turn off all other HA measures not mentioned here? Not
mentioning a topic means no change or no impact in that area, at least
on all other hackers threads.
Your comments were illogical anyway, since if it was so bad a technique
then it would not work for pgpool either, since it is also a client. If
pgpool can do this, why can't another client? Why can't *all* clients?
IIRC pgpool was itself a poor-mans replication solution, so it _is_ the
point of doing failover.
Agreed.
With correctly configured other components the primary will shut down if
it is no longer the boss. The client will then be disconnected. If it
switches to its secondary connection, we can have an option to read
session_replication_role to ensure that this is set to origin.
Probably this should not be an option, but a must.
Perhaps, but some people doing read only queries don't really care which
one they are connected to.
maybe session_replication_role should be a DBA-defined function, so that
the same client failover mechanism can be applied to different
replication solutions, both server-built-in and external.
create function session_replication_role()
returns enum('master','ro-slave','please-wait-coming-online','...')
$$
...
Maybe, trouble is please wait coming online is the message a Hot
Standby would give also. Happy to list out all the states so we can make
this work for everyone.
This
covers the case where the client has lost connection with primary,
though it is still up, yet can reach the standby which has not changed
state.
DB2, SQLServer and Oracle all provide this feature, BTW. We don't need
to follow, but we should do that consciously. I'm comfortable with us
deciding not to do it, if that is our considered judgement.
The main argument seemed to be, that it can't be Automatic Client-ONLY
Failover.
No argument. Never was. It can't be.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
--
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] plan invalidation vs stored procedures
Hello try version 8.3. There lot of dependencies are solved. Regards Pavel Stehule 2008/8/5 Martin Pihlak [EMAIL PROTECTED]: Howdy, What is the status of plan invalidation vs stored procedures? From the initial design discussion I understand that function change handling was postponed to some time in the future. Is anybody already working on that or maybe some ideas of how to implement this? The business case for the feature is that most of our db logic is inside stored procedures and hence use cached plans. Every time a function is dropped and recreated we get a storm of cache lookup failed errors. If we are lucky, the DBA will detect it and apply appropriate workarounds. If not ... things get messy. We are considering of hacking up a proprietary solution to address our specific problems (e.g. invalidate every plan on pg_proc changes). But I think that this is something that would be useful to a wider audience and deserves a more general solution. How about it? regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] small improvement in buffread common
I attach patch which removes useless page header check when page is zeroed. It
is primary used by hash index.
Zdenek
--
Zdenek Kotala Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql
*** pgsql_page_api.7c9eff0cf439/src/backend/storage/buffer/bufmgr.c út srp 5 12:31:44 2008
--- pgsql_page_api/src/backend/storage/buffer/bufmgr.c út srp 5 12:25:01 2008
***
*** 352,379
if (zeroPage)
MemSet((char *) bufBlock, 0, BLCKSZ);
else
- smgrread(smgr, blockNum, (char *) bufBlock);
- /* check for garbage data */
- if (!PageHeaderIsValid((PageHeader) bufBlock))
{
! if (zero_damaged_pages)
{
! ereport(WARNING,
! (errcode(ERRCODE_DATA_CORRUPTED),
! errmsg(invalid page header in block %u of relation %u/%u/%u; zeroing out page,
! blockNum,
! smgr-smgr_rnode.spcNode,
! smgr-smgr_rnode.dbNode,
! smgr-smgr_rnode.relNode)));
! MemSet((char *) bufBlock, 0, BLCKSZ);
}
- else
- ereport(ERROR,
- (errcode(ERRCODE_DATA_CORRUPTED),
- errmsg(invalid page header in block %u of relation %u/%u/%u,
- blockNum, smgr-smgr_rnode.spcNode,
- smgr-smgr_rnode.dbNode,
- smgr-smgr_rnode.relNode)));
}
}
--- 352,382
if (zeroPage)
MemSet((char *) bufBlock, 0, BLCKSZ);
else
{
! smgrread(smgr, blockNum, (char *) bufBlock);
!
! /* check for garbage data */
! if (!PageHeaderIsValid((Page) bufBlock))
{
! if (zero_damaged_pages)
! {
! ereport(WARNING,
! (errcode(ERRCODE_DATA_CORRUPTED),
! errmsg(invalid page header in block %u of relation %u/%u/%u; zeroing out page,
! blockNum,
! smgr-smgr_rnode.spcNode,
! smgr-smgr_rnode.dbNode,
! smgr-smgr_rnode.relNode)));
! MemSet((char *) bufBlock, 0, BLCKSZ);
! }
! else
! ereport(ERROR,
! (errcode(ERRCODE_DATA_CORRUPTED),
! errmsg(invalid page header in block %u of relation %u/%u/%u,
! blockNum, smgr-smgr_rnode.spcNode,
! smgr-smgr_rnode.dbNode,
! smgr-smgr_rnode.relNode)));
}
}
}
--
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] Automatic Client Failover
Hi, Dimitri Fontaine wrote: If slave nodes were able to accept connection and redirect them to master, the client wouldn't need to care about connecting to master or slave, just to connect to a live node. I've thought about that as well, but think about it this way: to protect against N failing nodes, you need to forward *every* request through N living nodes, before actually hitting the node which processes the query. To me, that sounds like an awful lot of traffic within the cluster, which can easily be avoided with automatic client failover. (Why are you stating, that only slaves need to redirect? What is happening in case of a master failure?) So the proposal for Automatic Client Failover becomes much more simpler. I'm arguing it's the other way around: taking down a node of the cluster becomes much simpler with ACF, because clients automatically reconnect to another node themselves. The servers don't need to care. Regards Markus Wanner -- 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] Automatic Client Failover
Hi, Tom Lane wrote: Huh? The pgpool is on the server, not on the client side. Not necessarily. Having pgpool on the client side works just as well. There is one really bad consequence of the oversimplified failover design that Simon proposes, which is that clients might try to fail over for reasons other than a primary server failure. Why is that? It's just fine for a client to (re)connect to another server due to a fluky connection to the current server. I had something pretty similar in mind for Postgres-R. (Except that we should definitely allow to specify more than just a primary and a secondary server.) (Think network partition.) Uh... well, yeah, of course the servers themselves need to exchange their state and make sure they only accept clients if they are up and running (as seen by the cluster). That's what the 'view' of a GCS is all about. Or STONITH, for that matter. You really want any such behavior to be managed centrally, IMHO. Controlling that client behavior reliably would involve using multiple (at least N+1) connections to different servers, so you can control the client even if N of the servers fail. That's certainly more complex than what Simon proposed. Speaking in terms of orthogonality, client failover is orthogonal to the (cluster-wide) server state management. Which in turn is orthogonal to how the nodes replicate data. (Modulo some side effects like nodes lagging behind for async replication...) Regards Markus Wanner -- 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] Automatic Client Failover
Hi, Greg Stark wrote: a cwnrallu What is that? Regards Markus Wanner -- 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] CommitFest July Over
Hi, Josh Berkus wrote: 2) The number of patches is going to keep increasing with each commitfest. As such, the patch list is going to get harder to deal with. We now urgently need to start working on CF management software. Agreed. 3) Round Robin Reviewers didn't really work this time, aside from champion new reviewer Abhjit. For the most part, RRR who were assigned patches did not review them for 2 weeks. Two areas where this concept needs to be improved: a) we need to assign RRR to patches two days after the start of commitfest, not a week later; Maybe it's just me, but I don't quite understand the concept of RRR. If I get some spare cycles to review patches, I certainly want to choose mysqlf which patch I'm going to review. Why is the CF Manager doing any assignment of patches? Of course, the reviewers need to coordinate, it doesn't make much sense for seven people concurrently reviewing the same patch. But shouldn't the reviewer take care of 'tagging' a patch as being reviewed? Or do you think it's motivating to get nagged about accepting or rejecting a patch assignment? For my part, it's been the main reason I didn't sign up as an RRR: I didn't want to get into that situation. On the other hand, I must admit that I didn't review any of the outstanding patches either... Regards Markus Wanner -- 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] PL/PythonU
Hannu Krosing wrote: On Mon, 2008-08-04 at 13:08 -0400, David Blewett wrote: Hi All: This is an off-shoot of the Do we really want to migrate plproxy and citext into PG core distribution? thread. On the way home from PyOhio, I had a conversation with a few people that use Zope a lot. I happened to mention that Postgres doesn't have an untrusted version of pl/python and they were curious as to why. Personally I'm also constantly mentioning it :-) They directed me to Zope's Restricted Python implementation [1][2]. In doing some research, I found the Pl/Python -- current maintainer? [3] thread from 2006. I also found this [4] thread on the python-dev mailing list. Hannu: You had mentioned bringing pl/python up to the level of some of the other pl's. Have you thought any more about pl/pythonu? My recollection of old times (about python v. 1.6) was that the restricted sandboxes had some fatal flaws. I have not followed zope's RestrictedPython enough to have an opinion on its safety. Yes, the old sandbox (restricted execution and bastion) used a realatively naive approach of basically limiting only imports and iirc. some file access objects. That beeing not really bullet proof so these modules have been removed. This should not be confused with the different approach restricted python uses and which proofes to be successfull to date. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] CommitFest July Over
Robert Treat wrote: On Monday 04 August 2008 15:38:35 Josh Berkus wrote: Post-mortem things we've learned about the commitfest are: 1) It's hard to get anything done in June-July. True... vacations and conferences abound. September should be better in this regard I would think. Um. Looking at my calendar, the second half of september and all of october is packed solid with conferences. Unlike June, July August which were completely empty. Perhaps it's a US vs EU thing? (Vacations are July/August though, so that matches) 2) The number of patches is going to keep increasing with each commitfest. As such, the patch list is going to get harder to deal with. We now urgently need to start working on CF management software. 3) Round Robin Reviewers didn't really work this time, aside from champion new reviewer Abhjit. For the most part, RRR who were assigned patches did not review them for 2 weeks. Two areas where this concept needs to be improved: a) we need to assign RRR to patches two days after the start of commitfest, not a week later; This seems tricky, since you want people to volunteer to review patches ideally, will two days be enough? Should people interested in reviewing be signing up ahead of time? Looking at the next commitfest, it is going to start on a Monday... maybe auto-assigning reviewers on Wednesday is OK. Um, didn't they already sign up ahead of time? We can't very well hand out patches to someone who's not interested, can we? b) there needs to be the expectation that RRR will start reviewing or reject the assignment immediately. I wonder if too much time was spent on patches like the WITH patch, which seemed pretty early on it was not ready for commit... thoughts? I think that happens a lot. Once discussion takes off on a patch, it attracts more people to comment on it, etc. Plus the whole hey, i've added a git repo starts it's own thread :-P 4) We need to work better to train up new reviewers. Some major committer(s) should have worked with Abhjit, Thomas and Martin particularly on getting them to effectively review patches; instead, committers just handled stuff *for* them for the most part, which isn't growing our pool of reviewers. True. 5) Patch submitters need to understand that patch submission isn't fire-and-forget. They need to check back, and respond to queries from reviewers. Of course, a patch-tracker which automatically notified the submitter would help. Reviewers should be responding to the email on -hackers that is pointed to by the wiki, so patch submitters should be getting notified... right ? Well, there's really no way to easily do that. I mean, you can't hit reply once you find something in the archives. You'll need to manually put everybody back in the CC list, so it's much easier to just post to -hackers. Thus, I think requiring the submitters to check back on -hackers regularly is necessary, for now. 6) Overall, I took a low-nag-factor approach to the first time as commitfest manager. This does not seem to have been the best way; I'd suggest for september that the manager make more frequent nags. Yes, agreed. The manager role was fairly invisible this time around, I think we should at least try and see what happens. Finally: who wants to be CF Manager for September? I'm willing to do it again, but maybe someone else should get a turn. Why stop now when you've got the momentum? :-) Seriously though, I thought we were supposed to have 2 people working as CF Managers for each CF... is that not the case? Umm, IIRC we said one, but we'd rotate. That said, I think it'd be a good idea if Josh continued across the next one, given that this one was more or less a trial run for the CF Manager thingy. We can start switching once the role is a bit more defined. (This is all based on the fact that Josh says he's ok with doing it, of course :-P) //Magnus -- 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] Automatic Client Failover
Le mardi 05 août 2008, Markus Wanner a écrit : (Think network partition.) Uh... well, yeah, of course the servers themselves need to exchange their state and make sure they only accept clients if they are up and running (as seen by the cluster). That's what the 'view' of a GCS is all about. Or STONITH, for that matter. That's where I'm thinking that some -core smartness would makes this part simpler, hence the confusion (sorry about that) on the thread. If slave nodes were able to accept connection and redirect them to master, the client wouldn't need to care about connecting to master or slave, just to connect to a live node. So the proposal for Automatic Client Failover becomes much more simpler. -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Automatic Client Failover
Hi, Simon Riggs wrote: On Tue, 2008-08-05 at 11:50 +0300, Hannu Krosing wrote: I guess having the title Automatic Client Failover suggest to most readers, that you are trying to solve the client side separately from server. Yes, that's right: separately. Why would anybody presume I meant and by the way you can turn off all other HA measures not mentioned here? Not mentioning a topic means no change or no impact in that area, at least on all other hackers threads. I think the pgbouncer-in-core idea caused some confusion here. IMO the client failover method is very to what DNS round-robin setups do for webservers: even if clients might failover 'automatically', you still have to maintain the server states (which servers do you list in the DNS?) and care about 'replication' of your site to the webservers. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] plan invalidation vs stored procedures
Howdy, What is the status of plan invalidation vs stored procedures? From the initial design discussion I understand that function change handling was postponed to some time in the future. Is anybody already working on that or maybe some ideas of how to implement this? The business case for the feature is that most of our db logic is inside stored procedures and hence use cached plans. Every time a function is dropped and recreated we get a storm of cache lookup failed errors. If we are lucky, the DBA will detect it and apply appropriate workarounds. If not ... things get messy. We are considering of hacking up a proprietary solution to address our specific problems (e.g. invalidate every plan on pg_proc changes). But I think that this is something that would be useful to a wider audience and deserves a more general solution. How about it? regards, Martin -- 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] Location for pgstat.stat
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Attached is a patch that implements this. I went with the option of just storing it in a temporary directory that can be symlinked, and not bothering with a GUC for it. Comments? (documentation updates are also needed, but I'll wait with those until I hear patch comments :-P) Looks alright in a fast once-over (I didn't test it). That's what I was after. I tested it myself, obviously :-) Not promising zero bugs, but I was looking for the comment on the approach. So thanks! Two comments: Treating the directory as something to create in initdb means you'll need to bump catversion when you apply it. Yeah, i meant to do that as part of the commit. But thanks for the reminder anyway! I'm not sure where you are planning to document, but there should at least be a mention in the database physical layout chapter, since that's supposed to enumerate all the subdirectories of $PGDATA. I'm putting it under configuring the statistics collector. And I'll add a directory in that section - had missed that. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] searching bison guru - grouping sets implementation
Hello
I trying to implement GROUPING SETS feature. But there is basic
difference between PostgreSQL and ANSI. Pg allows expressions, ANSI
only column reference. I have syntax:
group_clause:
GROUP_P BY grouping_element_list
| /*EMPTY*/
;
grouping_element_list:
grouping_element
{
$$ = list_make1($1);
}
| grouping_element_list ',' grouping_element
{
$$ = lappend($1, $3);
}
;
grouping_element:
ordinary_grouping_set
{
}
| ROLLUP '(' ordinary_grouping_set_list ')'
{
}
| CUBE '(' ordinary_grouping_set_list ')'
{
}
| GROUPING SETS '(' grouping_element_list ')'
{
}
| '(' ')'
{
}
;
ordinary_grouping_set:
grouping_column_ref
{
}
| '(' grouping_ref_list ')'
{
}
;
grouping_ref_list:
grouping_column_ref
{
}
| grouping_ref_list ',' grouping_column_ref
{
}
;
ordinary_grouping_set_list:
ordinary_grouping_set
{
}
| ordinary_grouping_set_list ',' ordinary_grouping_set
{
}
;
grouping_column_ref:
columnref
{}
| Iconst
{}
;
;
this works well, but it is ANSI compliant not pg compliant
after change:
grouping_column_ref:
a_expr
{}
;
I getting
[EMAIL PROTECTED] parser]$ bison gram.y
gram.y: conflicts: 1 shift/reduce, 1 reduce/reduce
so I cannot find any way to remove shift/reduce.
any ideas?
*** ./gram.y.orig 2008-08-05 10:06:05.0 +0200
--- ./gram.y 2008-08-05 14:15:16.0 +0200
***
*** 362,367
--- 362,372
%type node xml_root_version opt_xml_root_standalone
%type ival document_or_content
%type boolean xml_whitespace_option
+ %type list grouping_element_list
+ %type node grouping_element
+ %type list grouping_ref_list
+ %type list ordinary_grouping_set ordinary_grouping_set_list
+ %type node grouping_column_ref
/*
***
*** 384,390
CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE CREATEDB
! CREATEROLE CREATEUSER CROSS CSV CURRENT_P CURRENT_DATE CURRENT_ROLE
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
--- 389,395
CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE CREATEDB
! CREATEROLE CREATEUSER CROSS CSV CUBE CURRENT_P CURRENT_DATE CURRENT_ROLE
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
***
*** 397,403
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
FREEZE FROM FULL FUNCTION
! GLOBAL GRANT GRANTED GREATEST GROUP_P
HANDLER HAVING HEADER_P HOLD HOUR_P
--- 402,408
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
FREEZE FROM FULL FUNCTION
! GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING
HANDLER HAVING HEADER_P HOLD HOUR_P
***
*** 431,440
READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE
! RIGHT ROLE ROLLBACK ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE
! SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
Re: [HACKERS] searching bison guru - grouping sets implementation
Pavel Stehule wrote:
I trying to implement GROUPING SETS feature. But there is basic
difference between PostgreSQL and ANSI. Pg allows expressions, ANSI
only column reference.
The conflict seems to arise from the parenthesis, between these two rules:
ordinary_grouping_set:
grouping_column_ref
{
}
*** | '(' grouping_ref_list ')'
{
}
;
and
grouping_column_ref:
a_expr
{}
;
where a_expr can be something like (foobar) as well, enclosed in
parenthesis. The grammar is ambiguous for something like
SELECT ... GROUP BY (foobar), bison doesn't know if that should be
interpreted as an '(' grouping_ref_list ')', whatever that is, or an
a_expr.
I don't know how that should be resolved, or if it's a genuine ambiguity
in the ANSI and PostgreSQL syntax or something that can be fixed with
some Bison magic.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
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] Automatic Client Failover
Le mardi 05 août 2008, Markus Wanner a écrit : I've thought about that as well, but think about it this way: to protect against N failing nodes, you need to forward *every* request through N living nodes, before actually hitting the node which processes the query. To me, that sounds like an awful lot of traffic within the cluster, which can easily be avoided with automatic client failover. (Why are you stating, that only slaves need to redirect? What is happening in case of a master failure?) I'm thinking in term of single master multiple slaves scenario... In single master case, each slave only needs to know who the current master is and if itself can process read-only queries (locally) or not. You seem to be thinking in term of multi-master, where the choosing of a master node is a different concern, as a failing master does not imply slave promotion. So the proposal for Automatic Client Failover becomes much more simpler. I'm arguing it's the other way around: taking down a node of the cluster becomes much simpler with ACF, because clients automatically reconnect to another node themselves. The servers don't need to care. Well, in the single master case I'm not sure to agree, but in the case of multi master configuration, it well seems that choosing some alive master is a client task. Now what about multi-master multi-slave case? Does such a configuration have sense? It this ever becomes possible (2 active/active masters servers, with some slaves for long running queries, e.g.), then you may want the ACF-enabled connection routine to choose to connect to any master or slave in the pool, and have the slave be itself an AFC client to target some alive master. Does this still makes sense? -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] plan invalidation vs stored procedures
Pavel Stehule wrote: Hello try version 8.3. There lot of dependencies are solved. Yes, 8.3 was the version I was testing with. Same results on the HEAD: $ psql -e -f test.sql select version(); version -- PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2) (1 row) create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 regards, Martin -- 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] plan invalidation vs stored procedures
Hi Sadly PostgreSQL inability to invalidate plan cache when function is dropped causes us downtime and costs money. ERROR: cache lookup failed for function 24865) This time our developers just rewrote function to use OUT parameters instead of return type. Currently i had to forbid dropping functions in our most critical databases but that makes developers unhappy. And as i understand it is not fixed in 8.3: Comment from code * Currently, we use only relcache invalidation events to invalidate plans. * This means that changes such as modification of a function definition do * not invalidate plans using the function. This is not 100% OK --- for * example, changing a SQL function that's been inlined really ought to * cause invalidation of the plan that it's been inlined into --- but the * cost of tracking additional types of object seems much higher than the * gain, so we're just ignoring them for now. So we will have to get it fixed and better would be to do it so that solution suits everybody. Our current workaround include updating pg_proc after release or letting pgBouncer to reconnect all connections but neither solution is good and cause us to lose valuable minutes in error flood when we miss some crucial drop function. Asko On Tue, Aug 5, 2008 at 1:40 PM, Pavel Stehule [EMAIL PROTECTED]wrote: Hello try version 8.3. There lot of dependencies are solved. Regards Pavel Stehule 2008/8/5 Martin Pihlak [EMAIL PROTECTED]: Howdy, What is the status of plan invalidation vs stored procedures? From the initial design discussion I understand that function change handling was postponed to some time in the future. Is anybody already working on that or maybe some ideas of how to implement this? The business case for the feature is that most of our db logic is inside stored procedures and hence use cached plans. Every time a function is dropped and recreated we get a storm of cache lookup failed errors. If we are lucky, the DBA will detect it and apply appropriate workarounds. If not ... things get messy. We are considering of hacking up a proprietary solution to address our specific problems (e.g. invalidate every plan on pg_proc changes). But I think that this is something that would be useful to a wider audience and deserves a more general solution. How about it? regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] plan invalidation vs stored procedures
2008/8/5 Martin Pihlak [EMAIL PROTECTED]: Pavel Stehule wrote: Hello try version 8.3. There lot of dependencies are solved. Yes, 8.3 was the version I was testing with. Same results on the HEAD: $ psql -e -f test.sql select version(); version -- PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2) (1 row) create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 regards, Martin use CREATE OR REPLACE FUNCTION syntax without DROP FUNCTION, CREATE FUNCTION .. Regards Pavel Stehule -- 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] plan invalidation vs stored procedures
Hi Thanks for pointing to another thing to fix :) postgres=# create type public.ret_status as ( status integer, status_text text); CREATE TYPE postgres=# create or replace function pavel ( i_param text ) returns public.ret_status as $$ select 200::int, 'ok'::text; $$ language sql; CREATE FUNCTION postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. Asko On Tue, Aug 5, 2008 at 4:00 PM, Pavel Stehule [EMAIL PROTECTED]wrote: 2008/8/5 Martin Pihlak [EMAIL PROTECTED]: Pavel Stehule wrote: Hello try version 8.3. There lot of dependencies are solved. Yes, 8.3 was the version I was testing with. Same results on the HEAD: $ psql -e -f test.sql select version(); version -- PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2) (1 row) create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 regards, Martin use CREATE OR REPLACE FUNCTION syntax without DROP FUNCTION, CREATE FUNCTION .. Regards Pavel Stehule -- 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] Parsing of pg_hba.conf and authentication inconsistencies
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: Seems a lot better to me to just train people to run the check-config code by hand before pulling the trigger to load the settings for real. I think it'd be reasonable to refuse starting if the config is *known broken* (such as containing lines that are unparseable, or that contain completely invalid tokens), whereas you'd start if they just contain things that are probably wrong. But picking from your previous examples of more advanced checks, there are lots of cases where things like overlapping CIDR address ranges are perfectly valid, so I don't think we could even throw a warning for that - unless there's a separate flag to enable/disable warnings for such a thing. There are cases that are sane, and there are cases that are not. You've got three possibilities: * two lines referencing the exact same address range (and other selectors such as user/database). Definitely a mistake, because the second one is unreachable. * two lines where the second's address range is a subset of the first (and other stuff is the same). Likewise a mistake. * two lines where the first's address range is a subset of the second's. This one is the only sane one. Yeah, certainly. But a very common one at that. (The nature of CIDR notation is that there are no partial overlaps, so it must be one of these three cases.) Right. We have in fact seen complaints from people who apparently missed the fact that pg_hba.conf entries are order-sensitive, so I think a test like this would be worth making. But it shouldn't be done by the postmaster. Agreed. Postmaster should verify things only to the point that it's a valid CIDR mask (say that the IP is actually numeric and not 1.2.foo.3/32). Any further context analysis does not belong there. Should I read this as you warming up slightly to the idea of having the postmaster do that? ;-) //Magnus -- 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] IN vs EXISTS equivalence
On Mon, Aug 4, 2008 at 6:48 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] writes: I'm adding some NOT EXISTS examples to the thread for completeness of what someone might want to address while working on it. For two queries which can easily be shown (to a human viewer, anyway) to return identical results, I see performance differences of over five orders of magnitude. Could we see EXPLAIN ANALYZE not just EXPLAIN for these? When people are complaining of bad planner behavior, I don't find bare EXPLAIN output to be very convincing. I'll give it a shot. I've never had the patience to let the one with the cost five or six orders of magnitude higher than the others run to completion, but I've started the lot of 'em. -Kevin -- 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] plan invalidation vs stored procedures
Martin Pihlak [EMAIL PROTECTED] writes: create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? regards, tom lane -- 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] Automatic Client Failover
Hi, Dimitri Fontaine wrote: I'm thinking in term of single master multiple slaves scenario... In single master case, each slave only needs to know who the current master is and if itself can process read-only queries (locally) or not. I don't think that's as trivial as you make it sound. I'd rather put it as: all nodes need to agree on exactly one master node at any given point in time. However, IMO that has nothing to do with automatic client failover. You seem to be thinking in term of multi-master, where the choosing of a master node is a different concern, as a failing master does not imply slave promotion. I'm thinking about the problem which AFC tries to solve: connection losses between the client and one of the servers (no matter if it's a master or a slave). As opposed to a traditional single-node database, there might be other servers available to connect to, once a client lost the current connection (and thus suspects the server behind that connection to have gone down). Redirecting writing transactions from slaves to the master node solves another problem. Being able to 'rescue' such forwarded connections in case of a failure of the master is just a nice side effect. But it doesn't solve the problem of connection losses between a client and the master. Well, in the single master case I'm not sure to agree, but in the case of multi master configuration, it well seems that choosing some alive master is a client task. Given a failure of the master server, how do you expect clients, which were connected to that master server, to failover? Some way or another, they need to be able to (re)connect to one of the slaves (which possibly turned into the new master by then). Of course, you can load that burden on the application, and simply let that try to connect to another server upon connection failures. AFAIU Simon is proposing to put that logic into libpq. I see merits in that for multiple replication solutions and don't think anything exclusively server-sided could solve the same issue (because the client currently only has one connection to one server, which might fail at any time). [ Please note that you still need the retry-loop in the application. It mainly saves having to care about the list of servers and server states in the app. ] Now what about multi-master multi-slave case? Does such a configuration have sense? Heh.. I'm glad you are asking. ;-) IMO the only reason for master-slave replication is ease of implementation. It's certainly not something a sane end-users is ever requesting by himself, because he needs that feature. After all, not being able to run writing queries on certain nodes is not a feature, but a bare limitation. In your question, you are implicitly assuming an existing multi-master implementation. Given my reasoning, this would make an additional master-slave replication pretty useless. Thus I'm claiming that such a configuration does not make sense. It this ever becomes possible (2 active/active masters servers, with some slaves for long running queries, e.g.), then you may want the ACF-enabled connection routine to choose to connect to any master or slave in the pool, You can do the same with multi-master replication, without any disadvantage. and have the slave be itself an AFC client to target some alive master. Huh? AFC for master-slave communication? That implies that slaves are connected to the master(s) via libpq, which I think is not such a good fit. Regards Markus Wanner -- 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] plan invalidation vs stored procedures
This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? I totally agree we should get this fixed first :) postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. On Tue, Aug 5, 2008 at 4:51 PM, Tom Lane [EMAIL PROTECTED] wrote: Martin Pihlak [EMAIL PROTECTED] writes: create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? regards, tom lane -- 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] plan invalidation vs stored procedures
postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; CREATE FUNCTION postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text, more_text OUT text ) returns record as $$ select 200::int, 'ok'::text, 'tom'::text; $$ language sql; ERROR: cannot change return type of existing function DETAIL: Row type defined by OUT parameters is different. HINT: Use DROP FUNCTION first. On Tue, Aug 5, 2008 at 5:04 PM, Asko Oja [EMAIL PROTECTED] wrote: This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? I totally agree we should get this fixed first :) postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. On Tue, Aug 5, 2008 at 4:51 PM, Tom Lane [EMAIL PROTECTED] wrote: Martin Pihlak [EMAIL PROTECTED] writes: create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? regards, tom lane -- 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] plan invalidation vs stored procedures
DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? Well, the test case was an illustration. The actual reason for DROP and CREATE is the inability to change function return type. In our case there are plpgsql OUT parameters involved, and there is no other way to add additional OUT parameters without dropping the function first. I'd be glad if this was fixed, but I still think that proper plan invalidation for function changes is needed (inlined functions, ALTER FUNCTION stuff etc.) regards, Martin -- 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] plan invalidation vs stored procedures
2008/8/5 Martin Pihlak [EMAIL PROTECTED]: DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? Well, the test case was an illustration. The actual reason for DROP and CREATE is the inability to change function return type. In our case there are plpgsql OUT parameters involved, and there is no other way to add additional OUT parameters without dropping the function first. I'd be glad if this was fixed, but I still think that proper plan invalidation for function changes is needed (inlined functions, ALTER FUNCTION stuff etc.) It isn't possible. Probably some wrong is in your database design. regards Pavel Stehule regards, Martin -- 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] Parsing of pg_hba.conf and authentication inconsistencies
Magnus Hagander [EMAIL PROTECTED] writes: Should I read this as you warming up slightly to the idea of having the postmaster do that? ;-) No ;-). I still think that a postgres --check-config feature would be far more complete and useful, as well as less likely to cause bugs in critical code paths. A point that I don't think has been made so far in the thread: the only place the postmaster could complain in event of problems is the postmaster log, which we know too well isn't watched by inexperienced DBAs. I guarantee you that we will get bug reports along the lines of I updated pg_hba.conf and did pg_ctl reload, but nothing changed! Postgres sucks! if we implement checking at load time. I think one of the main advantages of a --check-config approach is that whatever it had to say would come out on the user's terminal. regards, tom lane -- 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] plan invalidation vs stored procedures
2008/8/5 Asko Oja [EMAIL PROTECTED]: postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; CREATE FUNCTION postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text, more_text OUT text ) returns record as $$ select 200::int, 'ok'::text, 'tom'::text; $$ language sql; ERROR: cannot change return type of existing function DETAIL: Row type defined by OUT parameters is different. HINT: Use DROP FUNCTION first. On Tue, Aug 5, 2008 at 5:04 PM, Asko Oja [EMAIL PROTECTED] wrote: This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? I totally agree we should get this fixed first :) postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. you cannot change header of function. It's same as change C header of function without complete recompilation. On Tue, Aug 5, 2008 at 4:51 PM, Tom Lane [EMAIL PROTECTED] wrote: Martin Pihlak [EMAIL PROTECTED] writes: create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] searching bison guru - grouping sets implementation
Heikki Linnakangas [EMAIL PROTECTED] writes: I don't know how that should be resolved, or if it's a genuine ambiguity in the ANSI and PostgreSQL syntax or something that can be fixed with some Bison magic. Fwiw I looked into this once already and noted the same conflict: http://article.gmane.org/gmane.comp.db.postgresql.devel.general/83563/match=rollup Tom pointed out that there's more than one way to skin a cat: http://thread.gmane.org/gmane.comp.db.postgresql.devel.cvs/22326/focus=83563 (Oh look at that, he actually used precisely that phrase) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] searching bison guru - grouping sets implementation
[Oops sorry -- I used the wrong links for the previous message. Here are the correct links] Heikki Linnakangas [EMAIL PROTECTED] writes: I don't know how that should be resolved, or if it's a genuine ambiguity in the ANSI and PostgreSQL syntax or something that can be fixed with some Bison magic. Fwiw I looked into this once already and noted the same conflict: http://article.gmane.org/gmane.comp.db.postgresql.devel.general/83564 Tom pointed out that there's more than one way to skin a cat: http://article.gmane.org/gmane.comp.db.postgresql.devel.general/83578 (Oh look at that, he actually used precisely that phrase) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] Parsing of pg_hba.conf and authentication inconsistencies
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Should I read this as you warming up slightly to the idea of having the postmaster do that? ;-) No ;-). Bummer. Worth a shot though :-) I still think that a postgres --check-config feature would be far more complete and useful, as well as less likely to cause bugs in critical code paths. I still think we should have both :-) A point that I don't think has been made so far in the thread: the only place the postmaster could complain in event of problems is the postmaster log, which we know too well isn't watched by inexperienced DBAs. I guarantee you that we will get bug reports along the lines of I updated pg_hba.conf and did pg_ctl reload, but nothing changed! Postgres sucks! if we implement checking at load time. I think one of the main advantages of a --check-config approach is that whatever it had to say would come out on the user's terminal. How is this different from how we deal with postgresql.conf today? That one can only log errors there as well, no? (And has a lot more complex code to get there) Which would also be helped byu a --check-config approach, of course - I'm not saying we shouldn't have that, just that I want us to have both :-) //Magnus -- 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] Automatic Client Failover
Le mardi 05 août 2008, Markus Wanner a écrit : Dimitri Fontaine wrote: I'm thinking in term of single master multiple slaves scenario... In single master case, each slave only needs to know who the current master is and if itself can process read-only queries (locally) or not. I don't think that's as trivial as you make it sound. I'd rather put it as: all nodes need to agree on exactly one master node at any given point in time. However, IMO that has nothing to do with automatic client failover. Agreed, the idea is trying to help the AFC by reducing what I understood was its realm. It seems I'm misunderstanding the perimeter of the proposed change... And as for the apparent triviality, it resides only in the concept, and when you're confronted to nodes acting as master or slave depending on context (session_replication_role) it becomes more interresting. I'm thinking about the problem which AFC tries to solve: connection losses between the client and one of the servers (no matter if it's a master or a slave). As opposed to a traditional single-node database, there might be other servers available to connect to, once a client lost the current connection (and thus suspects the server behind that connection to have gone down). Redirecting writing transactions from slaves to the master node solves another problem. Being able to 'rescue' such forwarded connections in case of a failure of the master is just a nice side effect. But it doesn't solve the problem of connection losses between a client and the master. Agreed. It simply allows the ACF part not to bother with master(s) slave(s) topology, which still looks as a great win for me. Given a failure of the master server, how do you expect clients, which were connected to that master server, to failover? Some way or another, they need to be able to (re)connect to one of the slaves (which possibly turned into the new master by then). Yes, you still need ACF, I'm sure I never wanted to say anything against this. IMO the only reason for master-slave replication is ease of implementation. It's certainly not something a sane end-users is ever requesting by himself, because he needs that feature. After all, not being able to run writing queries on certain nodes is not a feature, but a bare limitation. I'm not agreeing here. I have replication needs where some data are only yo be edited by an admin backoffice, then replicated to servers. Those servers also write data (logs) which are to be sent to the main server (now a slave) which will compute stats on-the-fly (trigger based at replication receiving). Now, this configuration needs to be resistant to network failure of any node, central one included. So I don't want synchronous replication, thanks. And I don't want multi-master either, as I WANT to forbid central to edit data from the servers, and to forbid servers to edit data coming from the backoffice. Now, I certainly would appreciate having the central server not being a SPOF by having two masters both active at any time. Of course, if I want HA, whatever features and failure autodetection PostgreSQL gives me, I still need ACF. And if I get master/slave instead of master/master, I need STONITH and hearbeat or equivalent. I was just trying to propose ideas for having those external part as easy as possible to get right with whatever integrated solution comes from -core. In your question, you are implicitly assuming an existing multi-master implementation. Given my reasoning, this would make an additional master-slave replication pretty useless. Thus I'm claiming that such a configuration does not make sense. I disagree here, see above. Huh? AFC for master-slave communication? That implies that slaves are connected to the master(s) via libpq, which I think is not such a good fit. I'm using londiste (from Skytools), a master/slaves replication solution in python. I'm not sure whether the psycopg component is using libpq or implementing the fe protocol itself, but it seems to me in any case it would be a candidate to benefit from Simon's proposal. Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] CommitFest July Over
On Tuesday 05 August 2008 04:36:24 Magnus Hagander wrote:
Robert Treat wrote:
On Monday 04 August 2008 15:38:35 Josh Berkus wrote:
Post-mortem things we've learned about the commitfest are:
1) It's hard to get anything done in June-July.
True... vacations and conferences abound. September should be better in
this regard I would think.
Um. Looking at my calendar, the second half of september and all of
october is packed solid with conferences. Unlike June, July August
which were completely empty.
Perhaps it's a US vs EU thing?
(Vacations are July/August though, so that matches)
Hmm... Pg.br is the only thing I could think of in September, which I don't
think involves either of us, unless you're going on yet another world
adventure ;-)
I do agree that October looks packed, I'm glad we're not doing a commitfest
then.
2) The number of patches is going to keep increasing with each
commitfest. As such, the patch list is going to get harder to deal
with. We now urgently need to start working on CF management software.
3) Round Robin Reviewers didn't really work this time, aside from
champion new reviewer Abhjit. For the most part, RRR who were assigned
patches did not review them for 2 weeks. Two areas where this concept
needs to be improved:
a) we need to assign RRR to patches two days after the start of
commitfest, not a week later;
This seems tricky, since you want people to volunteer to review patches
ideally, will two days be enough? Should people interested in reviewing
be signing up ahead of time? Looking at the next commitfest, it is going
to start on a Monday... maybe auto-assigning reviewers on Wednesday is
OK.
Um, didn't they already sign up ahead of time? We can't very well hand
out patches to someone who's not interested, can we?
ISTR, and Josh's info above indicated, that after a week or so, patches with
no volunteers simply got assigned to someone. Josh, want to confirm.
b) there needs to be the expectation that RRR will start reviewing or
reject the assignment immediately.
I wonder if too much time was spent on patches like the WITH patch, which
seemed pretty early on it was not ready for commit... thoughts?
I think that happens a lot. Once discussion takes off on a patch, it
attracts more people to comment on it, etc.
Plus the whole hey, i've added a git repo starts it's own thread :-P
I have a git repo for ppa, want to do some hacking? :-)
4) We need to work better to train up new reviewers. Some major
committer(s) should have worked with Abhjit, Thomas and Martin
particularly on getting them to effectively review patches; instead,
committers just handled stuff *for* them for the most part, which isn't
growing our pool of reviewers.
True.
5) Patch submitters need to understand that patch submission isn't
fire-and-forget. They need to check back, and respond to queries from
reviewers. Of course, a patch-tracker which automatically notified the
submitter would help.
Reviewers should be responding to the email on -hackers that is pointed
to by the wiki, so patch submitters should be getting notified... right ?
Well, there's really no way to easily do that. I mean, you can't hit
reply once you find something in the archives. You'll need to manually
put everybody back in the CC list, so it's much easier to just post to
-hackers.
Ah, I keep a healthy backlog of email sent to hackers, so if I want to respond
to a patch, I find it in my email program and reply to that, with CC
list/threading intact.
Thus, I think requiring the submitters to check back on -hackers
regularly is necessary, for now.
Well, probably a good idea anyway, I certainly don't want to discourage it.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
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] Parsing of pg_hba.conf and authentication inconsistencies
Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: A point that I don't think has been made so far in the thread: the only place the postmaster could complain in event of problems is the postmaster log, which we know too well isn't watched by inexperienced DBAs. I guarantee you that we will get bug reports along the lines of I updated pg_hba.conf and did pg_ctl reload, but nothing changed! Postgres sucks! if we implement checking at load time. How is this different from how we deal with postgresql.conf today? It isn't, and I seem to recall we've had that scenario play out a couple times already for postgresql.conf changes. But pg_hba.conf is far more complex than variable = value ... regards, tom lane -- 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] Parsing of pg_hba.conf and authentication inconsistencies
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: A point that I don't think has been made so far in the thread: the only place the postmaster could complain in event of problems is the postmaster log, which we know too well isn't watched by inexperienced DBAs. I guarantee you that we will get bug reports along the lines of I updated pg_hba.conf and did pg_ctl reload, but nothing changed! Postgres sucks! if we implement checking at load time. How is this different from how we deal with postgresql.conf today? It isn't, and I seem to recall we've had that scenario play out a couple times already for postgresql.conf changes. But pg_hba.conf is far more complex than variable = value ... Ok, then I didn't misunderstand that part at least :-) Ah, well. I know that if others don't pipe in on my side of it, I'm implicitly out-voted ;), since I've stated my case by now... Thus, I won't put any time into working on it unless someone does. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Status of DISTINCT-by-hashing work
I've pretty much finished the project I got a bee in my bonnet about last week, which is to teach SELECT DISTINCT how to (optionally) use hashing for grouping in the same way that GROUP BY has been able to do for awhile. There are still two places in the system that hard-wire the use of sorting for duplicate elimination: * Set operations (UNION/INTERSECT/EXCEPT) * Aggregate functions with DISTINCT I'm thinking of trying to fix set operations before I leave this topic, but I'm not sure it's worth the trouble to change DISTINCT aggregates. They'd be a lot more work (since there's no executor infrastructure in place that could be used) and the return on investment seems low. Comments? regards, tom lane -- 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] plan invalidation vs stored procedures
On 8/5/08, Pavel Stehule [EMAIL PROTECTED] wrote: ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. you cannot change header of function. It's same as change C header of function without complete recompilation. Thats why plan invalidation for DROP+CREATE is needed. -- marko -- 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] small improvement in buffread common
Zdenek Kotala [EMAIL PROTECTED] writes: I attach patch which removes useless page header check when page is zeroed. It is primary used by hash index. Looks reasonable; applied. regards, tom lane -- 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] Status of DISTINCT-by-hashing work
Sounds very much like 80% 20% story. 80% that was easy to do is done and now 20% that is complex and progress is slow is left to be done. Sounds very familiar from the comment in plan cache invalidation :) On Tue, Aug 5, 2008 at 5:51 PM, Tom Lane [EMAIL PROTECTED] wrote: I've pretty much finished the project I got a bee in my bonnet about last week, which is to teach SELECT DISTINCT how to (optionally) use hashing for grouping in the same way that GROUP BY has been able to do for awhile. There are still two places in the system that hard-wire the use of sorting for duplicate elimination: * Set operations (UNION/INTERSECT/EXCEPT) * Aggregate functions with DISTINCT I'm thinking of trying to fix set operations before I leave this topic, but I'm not sure it's worth the trouble to change DISTINCT aggregates. They'd be a lot more work (since there's no executor infrastructure in place that could be used) and the return on investment seems low. Comments? regards, tom lane -- 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] Status of DISTINCT-by-hashing work
Tom Lane [EMAIL PROTECTED] writes: I've pretty much finished the project I got a bee in my bonnet about last week, which is to teach SELECT DISTINCT how to (optionally) use hashing for grouping in the same way that GROUP BY has been able to do for awhile. There are still two places in the system that hard-wire the use of sorting for duplicate elimination: * Set operations (UNION/INTERSECT/EXCEPT) Egads. Are you thinking to reimplement them more in line with the way other nodes work? Or just have them choose between hashing and sorting themselves? * Aggregate functions with DISTINCT I'm thinking of trying to fix set operations before I leave this topic, but I'm not sure it's worth the trouble to change DISTINCT aggregates. They'd be a lot more work (since there's no executor infrastructure in place that could be used) and the return on investment seems low. Comments? I recall being quite mystified by how distinct aggregates work when the sort didn't appear anywhere in EXPLAIN output. If we could manage to expose that info in the plan somehow it would be a great improvement even if we didn't actually improve the plans available. Any idea what would the needed executor infrastructure look like? Would it have anything in common with the OLAP window functions infrastructure? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] unnecessary code in_bt_split
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: I'm thinking we should split PageGetTempPage into two versions: PageGetTempPage: get a temp page the same size as the given page, but don't initialize its contents at all (so, just a thin wrapper for palloc). This could be used by _bt_split, as well as GinPageGetCopyPage and GistPageGetCopyPage. PageGetTempPageCopySpecial: get a temp page, PageInit it, and copy the special space from the given page. The only customer for this is gistplacetopage(), so maybe we don't even want it, rather than just doing the work right in gistplacetopage()? You could also make an argument for PageGetTempPageCopy() which'd just copy the source page verbatim, thus replacing GinPageGetCopyPage and GistPageGetCopyPage. Sounds good I will look on it. Zdenek -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- 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] Automatic Client Failover
Hi, Dimitri Fontaine wrote: Redirecting writing transactions from slaves to the master node solves another problem. Being able to 'rescue' such forwarded connections in case of a failure of the master is just a nice side effect. But it doesn't solve the problem of connection losses between a client and the master. Agreed. It simply allows the ACF part not to bother with master(s) slave(s) topology, which still looks as a great win for me. Hm.. yeah, for master-slave replication I'm slowly beginning to see merit in it. However, given the lacking use of master-slave... Yes, you still need ACF, I'm sure I never wanted to say anything against this. Ah, okay. I thought you were proposing an alternative. IMO the only reason for master-slave replication is ease of implementation. It's certainly not something a sane end-users is ever requesting by himself, because he needs that feature. After all, not being able to run writing queries on certain nodes is not a feature, but a bare limitation. I'm not agreeing here. Somehow, I just knew it.. ;-) I have replication needs where some data are only yo be edited by an admin backoffice, then replicated to servers. Those servers also write data (logs) which are to be sent to the main server (now a slave) which will compute stats on-the-fly (trigger based at replication receiving). Sure, you can currently do that because there exist master-slave replication solutions which can do that. And that's perfectly fine. Comparing that with concepts of an inexistent multi-master replication solution is not fair by definition. Now, this configuration needs to be resistant to network failure of any node, central one included. So I don't want synchronous replication, thanks. And I don't want multi-master either, as I WANT to forbid central to edit data from the servers, and to forbid servers to edit data coming from the backoffice. Now, I certainly would appreciate having the central server not being a SPOF by having two masters both active at any time. Of course, if I want HA, whatever features and failure autodetection PostgreSQL gives me, I still need ACF. And if I get master/slave instead of master/master, I need STONITH and hearbeat or equivalent. I was just trying to propose ideas for having those external part as easy as possible to get right with whatever integrated solution comes from -core. In your question, you are implicitly assuming an existing multi-master implementation. Given my reasoning, this would make an additional master-slave replication pretty useless. Thus I'm claiming that such a configuration does not make sense. I disagree here, see above. Huh? AFC for master-slave communication? That implies that slaves are connected to the master(s) via libpq, which I think is not such a good fit. I'm using londiste (from Skytools), a master/slaves replication solution in python. I'm not sure whether the psycopg component is using libpq or implementing the fe protocol itself, but it seems to me in any case it would be a candidate to benefit from Simon's proposal. Regards, -- 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] DROP DATABASE always seeing database in use
Gregory Stark wrote: It seems there's something wrong with CheckOtherDBBackends() but I haven't exactly figured out what. There are no other sessions but drop database keeps saying regression is being accessed by other users. I do see Autovacuum touching tables in regression but CheckOtherDBBackends() is supposed to send it a sigkill if it finds it and it doesn't seem to be doing so. I've been hacking on unrelated stuff in this database and have caused multiple core dumps and autovacuum is finding orphaned temp tables. It's possible some state is corrupted in some way here but I don't see what. Autovacuum does this as well. I know on 8.1, I've been bitten by it a number of times. I don't know for CVS or newer version than 8.1. But it's an option worth considering as autovac doesn't show up in pg_stat_activity. Regards Russell. -- 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] Status of DISTINCT-by-hashing work
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: There are still two places in the system that hard-wire the use of sorting for duplicate elimination: * Set operations (UNION/INTERSECT/EXCEPT) Egads. Are you thinking to reimplement them more in line with the way other nodes work? Or just have them choose between hashing and sorting themselves? Well, actually, after looking closer I'm realizing that it's harder than I thought. I had been thinking that we could just have the planner choose whether to generate grouping instead of sorting nodes, but that only works for plain UNION. For INTERSECT/EXCEPT (with or without ALL), you really need to maintain counters in each hashtable entry so you know how many matching rows you got from each side of the set operation. So it'd be necessary to either duplicate a large chunk of nodeAgg.c, or make that code handle hashed INTERSECT/EXCEPT along with all its existing duties. Neither of which seems particularly appealing :-(. I'm going to look at whether nodeAgg can be refactored to avoid this, but I'm feeling a bit discouraged about it at the moment. I recall being quite mystified by how distinct aggregates work when the sort didn't appear anywhere in EXPLAIN output. If we could manage to expose that info in the plan somehow it would be a great improvement even if we didn't actually improve the plans available. The problem is that each DISTINCT aggregate needs its own sort (or hash), which doesn't seem to fit into our plan tree structure. Any idea what would the needed executor infrastructure look like? Would it have anything in common with the OLAP window functions infrastructure? Possibly; I haven't paid much attention to the OLAP work yet. regards, tom lane -- 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] IN vs EXISTS equivalence
On Mon, Aug 4, 2008 at 6:48 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] writes: I'm adding some NOT EXISTS examples to the thread for completeness of what someone might want to address while working on it. For two queries which can easily be shown (to a human viewer, anyway) to return identical results, I see performance differences of over five orders of magnitude. Could we see EXPLAIN ANALYZE not just EXPLAIN for these? When people are complaining of bad planner behavior, I don't find bare EXPLAIN output to be very convincing. The other five queries have a cost to millisecond ratio of between 9.8 and 267. If the expensive one falls in the same range, it will run for 2.3 to 64 years. I know I have left it running for days before without completion. I don't think I can devote the resources to it. Attached are the EXPLAIN ANALYZE output for the other five. -Kevin not-exists-timings2.out Description: Binary data -- 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] Parsing of pg_hba.conf and authentication inconsistencies
* Magnus Hagander ([EMAIL PROTECTED]) wrote: Tom Lane wrote: It isn't, and I seem to recall we've had that scenario play out a couple times already for postgresql.conf changes. But pg_hba.conf is far more complex than variable = value ... Ok, then I didn't misunderstand that part at least :-) Ah, well. I know that if others don't pipe in on my side of it, I'm implicitly out-voted ;), since I've stated my case by now... Thus, I won't put any time into working on it unless someone does. Having one doesn't imply we don't have the other. I believe we should definitely have both the --check-config (to address Tom's concern, and to improve the user experience when doing an /etc/init.d/postgresql reload or similar) and the check done in the postmaster and have it only update the running config if the config file parsed correctly. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Automatic Client Failover
Hi, (sorry... I'm typing too fast and hitting the wrong keys... continuing the previous mail now...) Dimitri Fontaine wrote: Now, this configuration needs to be resistant to network failure of any node, Yeah, increasing availability is the primary purpose of doing replication. central one included. So I don't want synchronous replication, thanks. I do not understanding that reasoning. Synchronous replication is certainly *more* resilient to network failures, as it does *not* loose any data on failover. However, you are speaking about logs and stats. That certainly sounds like data you can afford to loose during a failover, because you can easily recreate it. And as asynchronous replication is faster, that's why you should prefer async replication here, IMO. And I don't want multi-master either, as I WANT to forbid central to edit data from the servers, and to forbid servers to edit data coming from the backoffice. Well, I'd say you are (ab)using replication as an access controlling method. That's not quite what it's made for, but you can certainly use it that way. As I understand master-slave replication, a slave should be able to take over from the master in case that one fails. In that case, the slave must suddenly become writable and your access controlling is void. In case you are preventing that, you are using replication only to transfer data and not to increase availability. That's fine, but it's quite a different use case. And something I admittedly haven't thought about. Thanks for pointing me to this use case of replication. We could probably combine Postgres-R (for multi-master replication) with londiste (to transfer selected data) asynchronously to other nodes. Of course, if I want HA, whatever features and failure autodetection PostgreSQL gives me, I still need ACF. Agreed. And if I get master/slave instead of master/master, I need STONITH and hearbeat or equivalent. A two-node setup with STONITH has the disadvantage, that you need manual intervention to bring up a crashed node again. (To remove the bullet from inside its head). I'm thus recommending to use at least three nodes for any kind of high-availability setup. Even if the third one only serves as a quorum and doesn't hold a replica of the data. It allows automation of node recovery, which does not only ease administration, but eliminates a possible source of errors. I was just trying to propose ideas for having those external part as easy as possible to get right with whatever integrated solution comes from -core. Yeah, that'd be great. However, ISTM that it's not quite clear, yet, what solution will get integrated into -core. Huh? AFC for master-slave communication? That implies that slaves are connected to the master(s) via libpq, which I think is not such a good fit. I'm using londiste (from Skytools), a master/slaves replication solution in python. I'm not sure whether the psycopg component is using libpq or implementing the fe protocol itself, but it seems to me in any case it would be a candidate to benefit from Simon's proposal. Hm.. yeah, that might be true. On the other hand, the servers in the cluster need to keep track of their state anyway, so there's not that much to be gained here. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Why we don't panic in PageGetExactFreeSpace
The PageGetExactFreeSpace function contains following code: 00486 space = (int) ((PageHeader) page)-pd_upper - 00487 (int) ((PageHeader) page)-pd_lower; 00488 00489 if (space 0) 00490 return 0; It seems to me that we should panic that data are overlaped instead of return zero. See PageHeaderIsValid for reference. Any comments? Zdenek -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] unable to build libpq on Win 2003 (32 bit)
Hello everybody, I am trying to build libpq.dll from the source on a WIN 2003 system, the make file is attached. I am using Microsoft Visual Studio 8 and below is the command and outcome I'm trying to perform: C:\src\PostgreSQL\postgresql-8.3.0\src\interfaces\libpqnmake /f win32.mak /I Microsoft (R) Program Maintenance Utility Version 8.00.50727.42 Copyright (C) Microsoft Corporation. All rights reserved. Building the Win32 static library... Using default OpenSSL Include directory: C:\OpenSSL\include Using default OpenSSL Library directory: C:\OpenSSL\lib\VC Using default Kerberos Include directory: C:\kfw-2.6.5\inc Using default Kerberos Library directory: C:\kfw-2.6.5\lib\i386 link.exe -lib @C:\DOCUME~1\MD_NSH~1\LOCALS~1\Temp\nm10D.tmp link.exe @C:\DOCUME~1\MD_NSH~1\LOCALS~1\Temp\nm10E.tmp mt -manifest .\Release\libpq.dll.manifest -outputresource:.\Release\libpq.dll;2 Microsoft (R) Manifest Tool version 5.2.3790.2014 Copyright (c) Microsoft Corporation 2005. All rights reserved. mt.exe : general error c10100b1: Failed to load file .\Release\libpq.dll. The system cannot find the file specified. What do you I need to change to make the build process successful? I'm looking forward to your reply. Thanks in advance. Nikolay Shevchenko. win32.mak Description: win32.mak -- 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] DROP DATABASE always seeing database in use
Maybe put the whole thing into the ERROR message instead of having a separate DETAIL line? ERROR: database %s is being accessed by %d session(s) -or- ERROR: database %s' is being accessed by %d prepared transaction(s) -or- ERROR: database %s' is being accessed by %d session(s) and %d prepared transaction(s) or possibly similar variants on the following, slightly more compact wording: ERROR: database %s' has %d open session(s) and %d prepared transaction(s) ...Robert On Tue, Aug 5, 2008 at 1:41 AM, Tom Lane [EMAIL PROTECTED] wrote: Jens-Wolfhard Schicke [EMAIL PROTECTED] writes: Tom Lane wrote: ERROR: database %s is being accessed by other users DETAIL: There are %d session(s) and %d prepared transaction(s) using the database. I'm aware that this phrasing might not translate very nicely ... anyone have a suggestion for better wording? I can only estimate translation effort into German, but how about: DETAIL: Active users of the database: %d session(s), %d prepared transaction(s) Hmmm ... what I ended up committing was code that special-cased the common cases where you only have one or the other, ie /* * We don't worry about singular versus plural here, since the English * rules for that don't translate very well. But we can at least avoid * the case of zero items. */ if (notherbackends 0 npreparedxacts 0) errdetail(There are %d other session(s) and %d prepared transaction(s) using the database., notherbackends, npreparedxacts); else if (notherbackends 0) errdetail(There are %d other session(s) using the database., notherbackends); else errdetail(There are %d prepared transaction(s) using the database., npreparedxacts); Your proposal seems fine for the first case but a bit stilted for the other two. Or maybe that's just me. Of course, we don't *have* to do it as above at all, if 0 prepared transactions doesn't bother people. Ideas anybody? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Why we don't panic in PageGetExactFreeSpace
Zdenek Kotala [EMAIL PROTECTED] writes: It seems to me that we should panic that data are overlaped instead of return zero. elog(PANIC) is almost never a good idea, and it certainly isn't a good response here. regards, tom lane -- 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] Why we don't panic in PageGetExactFreeSpace
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: It seems to me that we should panic that data are overlaped instead of return zero. elog(PANIC) is almost never a good idea, and it certainly isn't a good response here. Ok PANIC is too strong, but I guess FATAL should be relevant or is there any situation when lower upper? The same situation is in PageGetFreeSpace. Zdenek -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- 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] DROP DATABASE always seeing database in use
Russell Smith [EMAIL PROTECTED] writes: Gregory Stark wrote: It seems there's something wrong with CheckOtherDBBackends() but I haven't exactly figured out what. There are no other sessions but drop database keeps saying regression is being accessed by other users. I do see Autovacuum touching tables in regression but CheckOtherDBBackends() is supposed to send it a sigkill if it finds it and it doesn't seem to be doing so. I've been hacking on unrelated stuff in this database and have caused multiple core dumps and autovacuum is finding orphaned temp tables. It's possible some state is corrupted in some way here but I don't see what. Autovacuum does this as well. I know on 8.1, I've been bitten by it a number of times. I don't know for CVS or newer version than 8.1. But it's an option worth considering as autovac doesn't show up in pg_stat_activity. In 8.3 autovacuum politely steps out of the way if it's holding up traffic (actually anyone who gets stuck behind vacuum just rudely shoots it in the back). So this *shouldn't* happen any more which is why I was raising it. However it was solved earlier by someone else. It was a a prepared transaction. Which was precisely what my comment about some state is corrupted meant. In this case the server had core dumped after preparing a transaction and that prepared transaction was blocking the DROP DATABASE. 8.4 will now print a better message specifically pointing out the prepared transactions for the next hapless DBA to be caught in this situation. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] Parsing of pg_hba.conf and authentication inconsistencies
On Sun, 2008-08-03 at 10:36 +0200, Magnus Hagander wrote: Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: The good way to solve this would be to have independant command line utilities which check pg_hba.conf, pg_ident.conf and postgresql.conf for errors. Then DBAs could run a check *before* restarting the server. While clearly useful, it'd still leave the fairly large foot-gun that is editing the hba file and HUPing things which can leave you with a completely un-connectable database because of a small typo. That will *always* be possible, just because software is finite and human foolishness is not ;-). Certainly - been bitten by that more than once. But we can make it harder or easier to make the mistakes.. Yeah. I'm sure we've all done it. Would it be possible to have two config files? An old and a new? That way we could specify new file, but if an error is found we revert to the last known-good file? That would encourage the best practice of take-a-copy-then-edit. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] plan invalidation vs stored procedures
On Tue, Aug 5, 2008 at 10:12 AM, Martin Pihlak [EMAIL PROTECTED] wrote: DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? Well, the test case was an illustration. The actual reason for DROP and CREATE is the inability to change function return type. In our case there are plpgsql OUT parameters involved, and there is no other way to add additional OUT parameters without dropping the function first. I'd be glad if this was fixed, but I still think that proper plan invalidation for function changes is needed (inlined functions, ALTER FUNCTION stuff etc.) one workaround is to use a table based custom composite type: create table foo_output(a int, b text); create function foo() returns foo_output as ... alter table foo_output add column c int; create or replace foo() if necessary. This also works for 'in' variables. voila! :-) note you can't use standard composite type because there is no way to 'alter' it. merlin -- 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] Parsing of pg_hba.conf and authenticationinconsistencies
On Aug 5, 2008, at 4:07 PM, Simon Riggs wrote: On Sun, 2008-08-03 at 10:36 +0200, Magnus Hagander wrote: Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: The good way to solve this would be to have independant command line utilities which check pg_hba.conf, pg_ident.conf and postgresql.conf for errors. Then DBAs could run a check *before* restarting the server. While clearly useful, it'd still leave the fairly large foot-gun that is editing the hba file and HUPing things which can leave you with a completely un-connectable database because of a small typo. That will *always* be possible, just because software is finite and human foolishness is not ;-). Certainly - been bitten by that more than once. But we can make it harder or easier to make the mistakes.. Yeah. I'm sure we've all done it. Would it be possible to have two config files? An old and a new? That way we could specify new file, but if an error is found we revert to the last known-good file? That would encourage the best practice of take-a-copy-then-edit. Perhaps the --check-config option should take an (optional) file name? That would allow you to validate a config file without having to copy it into place first. postgres --check-config=myFilenameGoesHere -D $PGDATA -- Korry -- 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] Parsing of pg_hba.conf and authenticationinconsistencies
korry wrote: On Aug 5, 2008, at 4:07 PM, Simon Riggs wrote: On Sun, 2008-08-03 at 10:36 +0200, Magnus Hagander wrote: Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: The good way to solve this would be to have independant command line utilities which check pg_hba.conf, pg_ident.conf and postgresql.conf for errors. Then DBAs could run a check *before* restarting the server. While clearly useful, it'd still leave the fairly large foot-gun that is editing the hba file and HUPing things which can leave you with a completely un-connectable database because of a small typo. That will *always* be possible, just because software is finite and human foolishness is not ;-). Certainly - been bitten by that more than once. But we can make it harder or easier to make the mistakes.. Yeah. I'm sure we've all done it. Would it be possible to have two config files? An old and a new? That way we could specify new file, but if an error is found we revert to the last known-good file? That would encourage the best practice of take-a-copy-then-edit. Perhaps the --check-config option should take an (optional) file name? That would allow you to validate a config file without having to copy it into place first. postgres --check-config=myFilenameGoesHere -D $PGDATA If you're doing it that way, you need one for each type of file again. And you're still not helping the vast majority who will not bother with more than one file. They'll edit one file, and trust the system not to load a known broken file. That's kind of like every other daemon on the system works, so that's what people will be expecting. //Magnus -- 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] unable to build libpq on Win 2003 (32 bit)
Hi. Sorry late reaction.. VC++2008 are official and are not supported. However, it has Build(ed). Then, I did not reproduce a problem. http://winpg.jp/~saito/pg_work/pg8.3.3_nmake_VC++2008.txt It seems that there is some version difference. Please show dir Release. Regards, Hiroshi Saito - Original Message - From: Nikolae Shevchenco (md) To: pgsql-hackers@postgresql.org Sent: Wednesday, August 06, 2008 2:33 AM Subject: [HACKERS] unable to build libpq on Win 2003 (32 bit) Hello everybody, I am trying to build libpq.dll from the source on a WIN 2003 system, the make file is attached. I am using Microsoft Visual Studio 8 and below is the command and outcome I'm trying to perform: C:\src\PostgreSQL\postgresql-8.3.0\src\interfaces\libpqnmake /f win32.mak /I Microsoft (R) Program Maintenance Utility Version 8.00.50727.42 Copyright (C) Microsoft Corporation. All rights reserved. Building the Win32 static library... Using default OpenSSL Include directory: C:\OpenSSL\include Using default OpenSSL Library directory: C:\OpenSSL\lib\VC Using default Kerberos Include directory: C:\kfw-2.6.5\inc Using default Kerberos Library directory: C:\kfw-2.6.5\lib\i386 link.exe -lib @C:\DOCUME~1\MD_NSH~1\LOCALS~1\Temp\nm10D.tmp link.exe @C:\DOCUME~1\MD_NSH~1\LOCALS~1\Temp\nm10E.tmp mt -manifest .\Release\libpq.dll.manifest -outputresource:.\Release\libpq.dll;2 Microsoft (R) Manifest Tool version 5.2.3790.2014 Copyright (c) Microsoft Corporation 2005. All rights reserved. mt.exe : general error c10100b1: Failed to load file .\Release\libpq.dll. The system cannot find the file specified. What do you I need to change to make the build process successful? I'm looking forward to your reply. Thanks in advance. Nikolay Shevchenko. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] [patch] gsoc, improving hash index v2
Hi, hackers. Here is some test I run on a bigger set. Use a word list of 39916800 unique words The table size is 1529MB. Index BuildTimeIndexSize btree 874470.339ms 1027MB hash-patch 513026.381 ms 1024MB I use pgbench to test the time of a custom query script. There are 2000 statements in the script. It looks like this: select * from dict where word='123456789a0' ... The time of the two index is btree: 1/0.174700=5.00250125 hash-patch: 1/0.199900=5.724098 ---btree-- $ pgbench -n -f /tmp/query.sql dict transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 tps = 0.174694 (including connections establishing) tps = 0.174700 (excluding connections establishing) ---hash patch- $ pgbench -n -f /tmp/query.sql dict transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 tps = 0.199892 (including connections establishing) tps = 0.199900 (excluding connections establishing) -- Best Regards, Xiao Meng DKERC, Harbin Institute of Technology, China Gtalk: [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] http://xiaomeng.yo2.cn
Re: [HACKERS] [patch] gsoc, improving hash index v2
sorry, I made some mistake here. The time of the script on two indexes should be btree: 1/0.174700=5.724098s hash-patch: 1/0.199900=5.00250125s On Wed, Aug 6, 2008 at 9:33 AM, Xiao Meng [EMAIL PROTECTED] wrote: Hi, hackers. Here is some test I run on a bigger set. Use a word list of 39916800 unique words The table size is 1529MB. Index BuildTimeIndexSize btree 874470.339ms 1027MB hash-patch 513026.381 ms 1024MB I use pgbench to test the time of a custom query script. There are 2000 statements in the script. It looks like this: select * from dict where word='123456789a0' ... The time of the two index is btree: 1/0.174700=5.00250125 hash-patch: 1/0.199900=5.724098 ---btree-- $ pgbench -n -f /tmp/query.sql dict transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 tps = 0.174694 (including connections establishing) tps = 0.174700 (excluding connections establishing) ---hash patch- $ pgbench -n -f /tmp/query.sql dict transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 tps = 0.199892 (including connections establishing) tps = 0.199900 (excluding connections establishing) -- Best Regards, Xiao Meng DKERC, Harbin Institute of Technology, China Gtalk: [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] http://xiaomeng.yo2.cn -- Best Regards, Xiao Meng DKERC, Harbin Institute of Technology, China Gtalk: [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] http://xiaomeng.yo2.cn
Re: [HACKERS] [patch] gsoc, improving hash index v2
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Xiao Meng wrote: Hi, hackers. Here is some test I run on a bigger set. The time of the two index is btree: 1/0.174700=5.00250125 hash-patch: 1/0.199900=5.724098 Just to bring it to attention of everybody: btree: 1/0.174700=5.724098 hash-patch: 1/0.199900=5.00250125 Hence the hash _is_ actually faster. ---btree-- $ pgbench -n -f /tmp/query.sql dict ... tps = 0.174694 (including connections establishing) tps = 0.174700 (excluding connections establishing) ---hash patch- $ pgbench -n -f /tmp/query.sql dict transaction type: Custom query ... tps = 0.199892 (including connections establishing) tps = 0.199900 (excluding connections establishing) Jens -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFImQEZzhchXT4RR5ARAi2nAJ98ujYi+ZOHZybSQaOw11JFpkilIACg5DGu 0Mo+UPGsdd2ZFTGirMplFm4= =Qj5C -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
