Re: [HACKERS] TODO 9.0 done items removed
Tom Lane wrote: David E. Wheeler da...@kineticode.com writes: On Aug 9, 2010, at 5:45 PM, Bruce Momjian wrote: I figured it out; done: http://wiki.postgresql.org/wiki/TodoDone90 Jeepers. That's a long list! Uh, there seems to be quite a lot there that is *not* done in 9.0. In fact, none of the first page's worth are done ... I stopped reading at that point. Oh, I thought the wiki page was only a snapshot of the TODO list as of 9.0. I now see it is just completed items. I have updated the 9.0 TODO page accordingly: http://wiki.postgresql.org/wiki/TodoDone90 Now I see why the page is useful. :-) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] TODO 9.0 done items removed
Andrew Dunstan wrote: On 08/09/2010 09:36 PM, Tom Lane wrote: David E. Wheelerda...@kineticode.com writes: On Aug 9, 2010, at 5:45 PM, Bruce Momjian wrote: I figured it out; done: http://wiki.postgresql.org/wiki/TodoDone90 Jeepers. That's a long list! Uh, there seems to be quite a lot there that is *not* done in 9.0. In fact, none of the first page's worth are done ... I stopped reading at that point. yeah. It seems complete nonsense to me too. For example, this item is BS AFAIK: PL/Perl Incomplete item http://wiki.postgresql.org/wiki/Image:UntickedTick.svgAllow data to be passed in native language formats, rather than only text Thanks, removed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Explicit psqlrc
Kevin Grittner wrote: We should be giving authors as much leeway as possible, or they may not come back. One phenomenon I've noticed is that sometimes a patch is submitted because an end user has solved their own problem for themselves, but wishes to share the solution with the community. They're not always motivated to go to the lengths required to polish it up to the standard required for inclusion in core. In such cases, unless someone with the time to do so finds it interesting enough to pick up, it is just going to drop. I hope such authors feel comfortable submitting their next effort, as it might be something which interests a larger audience than the previous effort. We should do what we can to ensure that they understand the dynamics of that. This brings up the larger issue of whether incomplete/unapplied patches are recorded on the TODO list or just ignored. We never really came up with a plan for that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] MERGE Specification
On 10/08/10 06:03, Boxuan Zhai wrote: I have put everything in one patch, against the latest git repository. The program is tested on my machine. Thanks! I get a few compiler warnings: analyze.c: In function ‘transformMergeStmt’: analyze.c:2476: warning: unused variable ‘lastaction’ gram.y: In function ‘base_yyparse’: gram.y:7437: warning: assignment from incompatible pointer type gram.y:7441: warning: assignment from incompatible pointer type trigger.c: In function ‘ExecBSMergeTriggers’: trigger.c:2360: warning: assignment from incompatible pointer type trigger.c: In function ‘ExecASMergeTriggers’: trigger.c:2411: warning: assignment from incompatible pointer type planner.c: In function ‘merge_action_planner’: planner.c:681: warning: assignment from incompatible pointer type var.c: In function ‘push_up_merge_action_vars’: var.c:738: warning: passing argument 1 of ‘push_up_merge_action_vars_walker’ from incompatible pointer type var.c:96: note: expected ‘struct Node *’ but argument is of type ‘struct List *’ var.c:740: warning: passing argument 1 of ‘push_up_merge_action_vars_walker’ from incompatible pointer type var.c:96: note: expected ‘struct Node *’ but argument is of type ‘struct List *’ The merge.sgml file should be in doc/src/sgml/ref, not doc/src/sgml. After moving it there, I get a few errors from compiling the docs: openjade:ref/merge.sgml:128:55:X: reference to non-existent ID SQL-SELECT-TITLE openjade:ref/merge.sgml:129:55:X: reference to non-existent ID SQL-VALUES-TITLE openjade:ref/merge.sgml:185:42:X: reference to non-existent ID SQL-INSERT-TITLE openjade:ref/merge.sgml:170:42:X: reference to non-existent ID SQL-UPDATE-TITLE Those can be fixed by simply removing the endterm attributes from those lines, they're not needed. -- 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] Explicit psqlrc
Simon Riggs wrote: On Tue, 2010-07-20 at 09:05 -0400, Robert Haas wrote: On Tue, Jul 20, 2010 at 8:21 AM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-07-20 at 07:49 -0400, Robert Haas wrote: A further point is that it's very difficult to keep track of progress if the CF page reflects a whole bunch of supposedly Waiting on Author patches that are really quite thoroughly dead. True, but the point under discussion is what to do if no reply is received from an author. That is something entirely different from a patch hitting a brick wall. We gain nothing by moving early on author-delay situations, so I suggest we don't. No, we gain something quite specific and tangible, namely, the expectation that patch authors will stay on top of their patches if they want them reviewed by the community. If that expectation doesn't seem important to you, feel free to try running a CommitFest without it. If you can make it work, I'll happily sign on. I don't think so. We can assume people wrote a patch because they want it included in Postgres. Bumping them doesn't help them or us, since there is always an issue other than wish-to-complete. Not everybody is able to commit time in the way we do and we should respect that better. Authors frequently have to wait a long time for a review; why should reviewers not be as patient as authors must be? We should be giving authors as much leeway as possible, or they may not come back. By marking patches as 'returned with feedback' long before the end of the commit-fest, we show feedback of how close we are to completing the commit-fest. If we keep patches in limbo status, it is unclear how close we are to CF completion. And, of course, the author can reactivate the patch just by replying. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Measuring execution time
Hello all I want to measure the execution time spent running an SQL select query after the plan generation. So precisely I want to put my start timer before createQueryDesc() or ExecutorStart() and end timer after freeQueryDesc() or ExecutorEnd(). Right now I did so in spi.c, explain.c, pquery.c but they are not the default execution cases. Can someone tell me which file holds the default call to ExecutorStart(), because I also want to see the select query result unlike in the case of explain ? Thanks Vamsi
Re: [HACKERS] Universal B-tree
Daniel Oliveira wrote: There is a way to acess a index inside a c function without using a sql statement ? Yes, if you know the oid of the index you want to scan, you can use functions from backend/access/index/indexam.c. regards, Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_restore should accept multiple -t switches?
Hi, pg_dump allows us to select multiple target tables by using multiple -t switches, but pg_restore does not. So, when restoring multiple tables, we have to run pg_restore more than once as follows. This is a pain to me. $ pg_restore -t tbl1 db.dump $ pg_restore -t tbl2 db.dump Is it worth allowing pg_restore to accept multiple -t switches as well as pg_dump? $ pg_restore -t tbl1 -t tbl2 db.dump Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] knngist - 0.8
On Tue, Aug 10, 2010 at 1:35 AM, Euler Taveira de Oliveira eu...@timbira.com wrote: What do you mean by complex queries? You can always use the SET command. Sadly it doesn't work when you have different thresholds within distinct subqueries. (In pg_similarity I use this approach to set the function's thresholds). I mean exactly different thresholds in distinct subqueries. What I am investigating is a way to build an index with some user-defined parameters. (We already have some infra-structure in reloptions for that but it needs some work to support my idea). I have some half-baked patch that I'm planning to submit to some of the CFs. Unfortunately, I don't have time for it ATM. User-defined parameters for GiST would be a great feature. I'm performing some experiments with GiST and I'm really feeling the need of it. With best regards, Alexander Korotkov.
[HACKERS] MERGE command for inheritance
Hi, These days I am considering what else can be done for MERGE, And, I find inheritance tables in postgres is not supported by our MERGE command yet. Currently, MERGE command is only able to handle the target table itself, and its children tables are not involved in the process. I am not sure if inheritance of MERGE is needed by postgres. If we need, I may propose two methods for implementing it. An easy way to do it is use a rule-like strategy. We can generate new MERGE query statements with the children table as their target tables. Then the new query statement will be planned and executed in the normal way. This process can be put in the rewriter, before the queries are planned. This method is quite easy but seems not follow the tradition of inheritance in Postgres. The difficult way is to generate the plans for children table in planner, as the other commands like UPDATE and DELETE. However, because the structure of MERGE plan is much more complex than the ordinary ModifyTable plans, this job may not as simple as we expected. We need to adjust both the main plan and the merge actions to fit the children tables, which is not straight forward. I would like to know your opinions on this problem. PS: for my investigation on the inheritance actions, I find that although the children tables are modified by the UPDATE or DELETE commands on their ancestor tables, the rules defined on them are not activated during the query. Is this the case (I hope I am not asking a stupid question)? And, if so, I may ask why we want it to act like this. Boxuan
Re: [HACKERS] pg_restore should accept multiple -t switches?
On Tue, Aug 10, 2010 at 05:13:22PM +0900, Fujii Masao wrote: Hi, pg_dump allows us to select multiple target tables by using multiple -t switches, but pg_restore does not. So, when restoring multiple tables, we have to run pg_restore more than once as follows. This is a pain to me. $ pg_restore -t tbl1 db.dump $ pg_restore -t tbl2 db.dump Is it worth allowing pg_restore to accept multiple -t switches as well as pg_dump? $ pg_restore -t tbl1 -t tbl2 db.dump Regards, Yes. :) What other functionality in pg_dump does pg_restore not duplicate? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics 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] pg_restore should accept multiple -t switches?
On Tue, August 10, 2010 13:18, David Fetter wrote: On Tue, Aug 10, 2010 at 05:13:22PM +0900, Fujii Masao wrote: Is it worth allowing pg_restore to accept multiple -t switches as well as pg_dump? $ pg_restore -t tbl1 -t tbl2 db.dump Regards, Yes. :) What other functionality in pg_dump does pg_restore not duplicate? See also pg_restore -t table : can silently omit constraints: http://archives.postgresql.org/pgsql-bugs/2009-04/msg00269.php -- 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] host name support in pg_hba.conf
Peter Eisentraut wrote: On mån, 2010-08-09 at 13:56 -0500, Kevin Grittner wrote: Peter Eisentraut wrote: is reverse looked up, which results in a host name. Some IP addresses have several host names, including in reverse lookup; how is that handled? This is not possible, http://en.wikipedia.org/wiki/Reverse_DNS_lookup#Multiple_pointer_records or at least the C library APIs don't expose it. That may explain the prevalence of bugs in code dealing with it. -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] [BUGS] Measuring execution time
On Tue, Aug 10, 2010 at 3:46 AM, vamsi krishna vamsikrishna1...@gmail.com wrote: I want to measure the execution time spent running an SQL select query after the plan generation. So precisely I want to put my start timer before createQueryDesc() or ExecutorStart() and end timer after freeQueryDesc() or ExecutorEnd(). Right now I did so in spi.c, explain.c, pquery.c but they are not the default execution cases. Can someone tell me which file holds the default call to ExecutorStart(), because I also want to see the select query result unlike in the case of explain ? Well, you should be able to find all the calls to ExecutorStart() by using grep. But it sounds like you might be better off implementing this as an executor hook. Or perhaps one of the existing ones (auto_explain or pg_stat_statements) would give you what you need. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] pg_restore should accept multiple -t switches?
Fujii Masao masao.fu...@gmail.com writes: Is it worth allowing pg_restore to accept multiple -t switches as well as pg_dump? It's on the TODO list already, no? 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] MERGE command for inheritance
On tis, 2010-08-10 at 17:38 +0800, Boxuan Zhai wrote: I am not sure if inheritance of MERGE is needed by postgres. Yes, it is. PS: for my investigation on the inheritance actions, I find that although the children tables are modified by the UPDATE or DELETE commands on their ancestor tables, the rules defined on them are not activated during the query. Is this the case (I hope I am not asking a stupid question)? And, if so, I may ask why we want it to act like this. Your observation is correct. You could probably argue this way or that about how it should have been designed 20+ years ago, but this is how it is. In general, I wouldn't design new functionality on top of rules. Rules are pretty broken in many ways. -- 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] host name support in pg_hba.conf
On tis, 2010-08-10 at 07:32 -0500, Kevin Grittner wrote: http://en.wikipedia.org/wiki/Reverse_DNS_lookup#Multiple_pointer_records Yeah, you can configure all kinds of nonsense and sometimes even get away with it, but the basic assumption throughout is that a system has one host name and between 1 and many IP addresses. We must make our implementation robust again other setups, but we don't have to (or rather cannot) support them. -- 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] host name support in pg_hba.conf
On Tue, Aug 10, 2010 at 10:05 AM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2010-08-10 at 07:32 -0500, Kevin Grittner wrote: http://en.wikipedia.org/wiki/Reverse_DNS_lookup#Multiple_pointer_records Yeah, you can configure all kinds of nonsense and sometimes even get away with it, but the basic assumption throughout is that a system has one host name and between 1 and many IP addresses. These days, I think it's more common the other way around: one IP address, and many host names. We must make our implementation robust again other setups, but we don't have to (or rather cannot) support them. Cannot is a good argument for not supporting just about anything. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] MERGE command for inheritance
On 10/08/10 12:38, Boxuan Zhai wrote: The difficult way is to generate the plans for children table in planner, as the other commands like UPDATE and DELETE. However, because the structure of MERGE plan is much more complex than the ordinary ModifyTable plans, this job may not as simple as we expected. We need to adjust both the main plan and the merge actions to fit the children tables, which is not straight forward. This the approach you'll have to take. But actually, I'm surprised it doesn't happen to just work already. It should be opaque to the merge facility that the reference to the parent target table has inherited child tables - expanding the inherited table to scans of all the children should already be handled by the planner. -- 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] host name support in pg_hba.conf
Peter Eisentraut pete...@gmx.net wrote: Yeah, you can configure all kinds of nonsense and sometimes even get away with it, but the basic assumption throughout is that a system has one host name and between 1 and many IP addresses. It's hardly nonsense to have multiple names on a machine. While we usually avoid having multiple reverse lookup names, we have many in-house web applications and we neither want users to access them by IP address or have to worry about which web server is hosting which applications at the moment. So it's not unusual for one of our web servers to have 10 or 15 DNS names for forward lookup. If one machine becomes overloaded, we can move an application, change the DNS, and everyone's bookmark still works. This is precisely the sort of situation where using a hostname in pg_hba.conf would be most useful. We must make our implementation robust again other setups, but we don't have to (or rather cannot) support them. Without the logic to ensure that the hostname matches the reverse lookup, this might be useful for us. With that logic it is useless for us. I'm wondering how much you gain by having it in there. Why can't a forward lookup which matches the requesting IP be considered sufficient? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] trace_recovery_messages
Hi, The explanation of trace_recovery_messages in the document is inconsistent with the definition of it in guc.c. In the document, * trace_recovery_messages is categorized into DEVELOPER_OPTIONS * The default is WARNING * Parameter should be set in the postgresql.conf only But, in guc.c * trace_recovery_messages is categorized into LOGGING_WHEN * The default is DEBUG1 * The context is PGC_SUSET ISTM the right is * Categorized into DEVELOPER_OPTIONS * The default is DEBUG1 * The context is PGC_SIGHUP We should apply the attached patch which changes the document and guc.c as above? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center trace_recovery_messages_v1.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] MERGE Specification
On 10/08/10 12:08, Boxuan Zhai wrote: Thanks for your feedback. I fixed all the above waring bugs. Find the new patch in attachement. Thanks. I'm getting an assertion failure with this statement: CREATE TABLE foo (id int4); MERGE into foo t USING (select id FROM generate_series(1,5) id) AS s ON t.id = s.id WHEN NOT MATCHED THEN INSERT (id) VALUES (s.id); TRAP: FailedAssertion(!(ActiveSnapshotSet()), File: postgres.c, Line: 749) That's easily fixed - you need to add case T_MergeStmt to the list of optimizable command types in analyze_requires_snapshot() function. Unfortunately that doesn't get you far, the query then trips another assertion: TRAP: FailedAssertion(!(list_length(resultRelations) == list_length(subplans)), File: createplan.c, Line: 3929) -- 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] host name support in pg_hba.conf
On tis, 2010-08-10 at 10:11 -0400, Robert Haas wrote: These days, I think it's more common the other way around: one IP address, and many host names. Yes, that setup is very common, but it's actually only an illusion that DNS creates. The actual machine still has only one host name and some IP addresses, as far as the kernel is concerned. As you are surely aware, this situation creates all kinds of problems in practice. -- 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] host name support in pg_hba.conf
On tis, 2010-08-10 at 09:18 -0500, Kevin Grittner wrote: Without the logic to ensure that the hostname matches the reverse lookup, this might be useful for us. With that logic it is useless for us. I'm wondering how much you gain by having it in there. Why can't a forward lookup which matches the requesting IP be considered sufficient? For one thing, because people might like to add wildcard support. So I might be able to say host all all appserver*.example.com md5 -- 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] host name support in pg_hba.conf
Kevin Grittner kevin.gritt...@wicourts.gov writes: Without the logic to ensure that the hostname matches the reverse lookup, this might be useful for us. With that logic it is useless for us. I'm wondering how much you gain by having it in there. Why can't a forward lookup which matches the requesting IP be considered sufficient? I was about to complain about that same thing. ISTM the logic ought to be that you do a forward DNS lookup on the name presented in pg_hba.conf, and if any of the returned IP addresses match the connection's remote IP address, then you have a match. This business with doing a reverse lookup is at least twice as expensive, far more fragile, and it seems completely bogus from a security viewpoint. Why should I trust the RDNS server for an attacker's IP address? 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
[HACKERS] Session timeout on commitfest.postgresql.org
$SUBJECT seems to be less than 12 hours, which is annoyingly short. I don't see a good reason why I should have to log in again every morning. I could see expiring the cookie in a week or so, or tying it to a particular IP address, but this is just getting in the way. 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] host name support in pg_hba.conf
* Tom Lane t...@sss.pgh.pa.us [100810 10:39]: I was about to complain about that same thing. ISTM the logic ought to be that you do a forward DNS lookup on the name presented in pg_hba.conf, and if any of the returned IP addresses match the connection's remote IP address, then you have a match. This business with doing a reverse lookup is at least twice as expensive, far more fragile, and it seems completely bogus from a security viewpoint. Why should I trust the RDNS server for an attacker's IP address? Well, you don't trust the RDNS of the IP, you trust the normal lookup of the hostname returned by the RDNS. So if some other ip network is trying to give hostnames that should be authorized, you see that immediately when you resolve the authorized hostname and it doesn't give you that IP. The PTR query is a means to get the hostname to check against, so you d'nt have to pre-cache all thos possible results of all the hostnames. Pre-caching all the hostnames in pg_hba.conf is madness. How long do you cache them for? or do send out 1000 queries every connection? You can't support wildcards, or anythign usefull... AFAIK, every software I've used which allows hostnames as some connection control all do PTR-A/ lookups as Peter proposed. a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] host name support in pg_hba.conf
On tis, 2010-08-10 at 10:39 -0400, Tom Lane wrote: I was about to complain about that same thing. ISTM the logic ought to be that you do a forward DNS lookup on the name presented in pg_hba.conf, and if any of the returned IP addresses match the connection's remote IP address, then you have a match. This business with doing a reverse lookup is at least twice as expensive, far more fragile, and it seems completely bogus from a security viewpoint. If you have hundreds on lines in pg_hba.conf, then you need to do hundreds of DNS lookups per connection attempt (*), and each of those lookups could result in even more IP addresses, or could time out. So if some unrelated part of the system breaks (DNS down), it could take you hours to establish a connection. On the other hand, with the reverse DNS lookup, you would normally do about two DNS queries per successful connection attempt, and those would only be in relation to the machines actually involved in the connection. Also, if you are in a names-only environment, you might also like to turn on log_hostnames, in which case the reverse lookup is free (well, shared). (*) That could of course be addressed by your earlier idea of caching the resolved names when pg_hba.conf is read, but I don't think many people were on board with that idea. -- 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] host name support in pg_hba.conf
Peter Eisentraut pete...@gmx.net writes: On tis, 2010-08-10 at 09:18 -0500, Kevin Grittner wrote: Why can't a forward lookup which matches the requesting IP be considered sufficient? For one thing, because people might like to add wildcard support. So I might be able to say host all all appserver*.example.com md5 I don't think that the possibility that we might support that in future can justify using a slow and not-too-reliable method for ordinary non-wildcard names. 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] Session timeout on commitfest.postgresql.org
On Tue, Aug 10, 2010 at 10:48 AM, Tom Lane t...@sss.pgh.pa.us wrote: $SUBJECT seems to be less than 12 hours, which is annoyingly short. I don't see a good reason why I should have to log in again every morning. I could see expiring the cookie in a week or so, or tying it to a particular IP address, but this is just getting in the way. Hrm. It seems that there's not really any timeout at all (which probably needs to be fixed); rather, it just sets a cookie that lasts for the lifetime of your browser session. Let me see about doing something about this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Session timeout on commitfest.postgresql.org
Tom Lane t...@sss.pgh.pa.us wrote: $SUBJECT seems to be less than 12 hours, which is annoyingly short. I don't see a good reason why I should have to log in again every morning. I could see expiring the cookie in a week or so, or tying it to a particular IP address, but this is just getting in the way. Could it be a firewall doing that to you? I stay logged in to the CF app for weeks at a time. The Wiki seems to log me out on an annoyingly short timer, but not the CF app. -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] Session timeout on commitfest.postgresql.org
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 10, 2010 at 10:48 AM, Tom Lane t...@sss.pgh.pa.us wrote: $SUBJECT seems to be less than 12 hours, which is annoyingly short. I don't see a good reason why I should have to log in again every morning. I could see expiring the cookie in a week or so, or tying it to a particular IP address, but this is just getting in the way. Hrm. It seems that there's not really any timeout at all (which probably needs to be fixed); rather, it just sets a cookie that lasts for the lifetime of your browser session. Let me see about doing something about this. I haven't restarted Safari in quite a while, but I've been forced to log in again roughly daily for the past week. It appears to time out after 8 or 10 hours of non-access to the site. 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] Session timeout on commitfest.postgresql.org
Robert Haas robertmh...@gmail.com wrote: it just sets a cookie that lasts for the lifetime of your browser session. Ah, that's probably the difference -- I don't close the browser window with the CF app. I just lock my workstation. -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] host name support in pg_hba.conf
Peter Eisentraut pete...@gmx.net writes: On tis, 2010-08-10 at 10:39 -0400, Tom Lane wrote: I was about to complain about that same thing. ISTM the logic ought to be that you do a forward DNS lookup on the name presented in pg_hba.conf, and if any of the returned IP addresses match the connection's remote IP address, then you have a match. This business with doing a reverse lookup is at least twice as expensive, far more fragile, and it seems completely bogus from a security viewpoint. If you have hundreds on lines in pg_hba.conf, then you need to do hundreds of DNS lookups per connection attempt (*), and each of those lookups could result in even more IP addresses, or could time out. If you have a configuration that would actually require that, then you would have a case for using a wildcard. My complaint is that you're trying to force everyone to pay for that feature whether it's of use to them or not. I think it's at least as likely that typical setups would need exactly *one*, non wildcard, entry, to wit appserver.mycompany.com (which'd necessarily yield IPs for all the machines running your app server code). 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] Session timeout on commitfest.postgresql.org
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: $SUBJECT seems to be less than 12 hours, which is annoyingly short. I don't see a good reason why I should have to log in again every morning. I could see expiring the cookie in a week or so, or tying it to a particular IP address, but this is just getting in the way. Could it be a firewall doing that to you? Don't see how a firewall could affect cookies. Possibly this is a browser-specific issue, though. I'm using current-rev Safari on a Mac. I notice it shows the commitfest cookie as having no particular expiration time, which may mean that some Apple-specific expiration policy gets applied. But on the other hand, when I got prompted to log in this morning, I checked the cookie list and there was such a cookie there already --- so it wasn't that the browser had just dropped it. 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] patch for contrib/isn
On Aug 6, 2010, at 11:08 PM, Peter Eisentraut wrote: On ons, 2010-08-04 at 19:32 +0200, Jan Otto wrote: patch against HEAD is attached and validated against a lot of previously wrong and correct hyphenated isbn. I think this module could use a regression test. i'll take a look at this within next days. regards, jan -- 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] host name support in pg_hba.conf
* Peter Eisentraut (pete...@gmx.net) wrote: On mån, 2010-08-09 at 13:56 -0500, Kevin Grittner wrote: Some IP addresses have several host names, including in reverse lookup; how is that handled? This is not possible, or at least the C library APIs don't expose it. Compare the getnameinfo() and getaddrinfo() man pages, for example. Don't know how it happens at a technical level, but I've definitely seen it happen before.. Particularly with Windows domains where they don't have clean-up reverse DNS enabled. Manifests itself by having different host names show up on successive requests... Evil in any case. Stephen signature.asc Description: Digital signature
Re: [HACKERS] Session timeout on commitfest.postgresql.org
On Tue, Aug 10, 2010 at 11:22 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: $SUBJECT seems to be less than 12 hours, which is annoyingly short. I don't see a good reason why I should have to log in again every morning. I could see expiring the cookie in a week or so, or tying it to a particular IP address, but this is just getting in the way. Could it be a firewall doing that to you? Don't see how a firewall could affect cookies. Possibly this is a browser-specific issue, though. I'm using current-rev Safari on a Mac. I notice it shows the commitfest cookie as having no particular expiration time, which may mean that some Apple-specific expiration policy gets applied. But on the other hand, when I got prompted to log in this morning, I checked the cookie list and there was such a cookie there already --- so it wasn't that the browser had just dropped it. *scratches head* I don't see how that's possible, unless your browser is eating cookies for breakfast. There's no code anywhere in the application to (a) remove cookies from the database or (b) refuse to use cookies that are in the database based on the time they were issued. I can change the code to set an expires header (in fact, I'm working on that that now), but the symptoms you describe are inexplicable. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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: Add JSON datatype to PostgreSQL (GSoC, WIP)
Excerpts from Joseph Adams's message of mar ago 10 04:03:43 -0400 2010: An overview, along with my thoughts, of the utility functions: FN_EXTRA, FN_EXTRA_ALLOC, FN_MCXT macros * Useful-ometer: ()o TypeInfo structure and getTypeInfo function * Useful-ometer: ()---o getEnumLabelOids * Useful-ometer: ()---o I think this kind of thing could be stripped from the patch and submitted separately; they would presumably see a quick review and commit if they are small and useful (particularly if you can show a decrease of code verbosity by switching other uses in the existing code). The advantage is you don't have to keep arguing for their usefulness in the JSON patch; and if they turn out to be rejected, they won't cause the JSON patch to be rejected as a whole. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Session timeout on commitfest.postgresql.org
On 10 August 2010 16:26, Robert Haas robertmh...@gmail.com wrote: On Tue, Aug 10, 2010 at 11:22 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: $SUBJECT seems to be less than 12 hours, which is annoyingly short. I don't see a good reason why I should have to log in again every morning. I could see expiring the cookie in a week or so, or tying it to a particular IP address, but this is just getting in the way. Could it be a firewall doing that to you? Don't see how a firewall could affect cookies. Possibly this is a browser-specific issue, though. I'm using current-rev Safari on a Mac. I notice it shows the commitfest cookie as having no particular expiration time, which may mean that some Apple-specific expiration policy gets applied. But on the other hand, when I got prompted to log in this morning, I checked the cookie list and there was such a cookie there already --- so it wasn't that the browser had just dropped it. *scratches head* I don't see how that's possible, unless your browser is eating cookies for breakfast. There's no code anywhere in the application to (a) remove cookies from the database or (b) refuse to use cookies that are in the database based on the time they were issued. I can change the code to set an expires header (in fact, I'm working on that that now), but the symptoms you describe are inexplicable. -- Not anything to do with this?: http://hivelogic.com/articles/the-safari-cookie-issue-fixed -- Thom Brown Registered Linux user: #516935 -- 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] host name support in pg_hba.conf
* Aidan Van Dyk (ai...@highrise.ca) wrote: The PTR query is a means to get the hostname to check against, so you d'nt have to pre-cache all thos possible results of all the hostnames. Pre-caching all the hostnames in pg_hba.conf is madness. How long do you cache them for? or do send out 1000 queries every connection? You can't support wildcards, or anythign usefull... AFAIK, every software I've used which allows hostnames as some connection control all do PTR-A/ lookups as Peter proposed. Completely agreed. It's madness to precache all thse hostnames, but we need to figure out the hostname, thus, rDNS is used. The forward lookup is then to double-check that it matches. This is exactly how Kerberos works also. You certainly don't want to be repeatedly doing rDNS lookups to see if maybe that IP has other hosts. I also don't buy that there's an issue with setting up your rDNS to go to what you put in the pg_hba and then having the forward of that include the IP; again, it's how Kerberos works, and even if you don't believe in Kerberos, I hope you realize it's kind of popular. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] host name support in pg_hba.conf
* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote: It's hardly nonsense to have multiple names on a machine. While we usually avoid having multiple reverse lookup names, we have many in-house web applications and we neither want users to access them by IP address or have to worry about which web server is hosting which applications at the moment. So it's not unusual for one of our web servers to have 10 or 15 DNS names for forward lookup. If one machine becomes overloaded, we can move an application, change the DNS, and everyone's bookmark still works. This is precisely the sort of situation where using a hostname in pg_hba.conf would be most useful. We're talking about client systems connecting to PG here. Are you authenticating your web users by looking at their client addresses..? That strikes me as pretty unlikely. Your web servers will be connecting to your PG server from *one* address (whatever the main one is for that pariticular server), and that address just needs to have an rDNS entry that goes to a host whose forward DNS includes that IP. If you have multiple web servers that are connecting to the same PG database, then have multiple pg_hba entries, or make them all have the same hostname per reverse DNS (though I don't really see why you'd want to). We must make our implementation robust again other setups, but we don't have to (or rather cannot) support them. Without the logic to ensure that the hostname matches the reverse lookup, this might be useful for us. With that logic it is useless for us. I'm wondering how much you gain by having it in there. Why can't a forward lookup which matches the requesting IP be considered sufficient? Because you can't get there from here. You'd either have to cache all the entries in pg_hba (which is horrible), or do a look-up on each one on every connection (which is going to be a hell of alot slower than doing one more DNS lookup here). This isn't magic. What we have is a bunch of host names and a single IP (the connecting one). Figuring out which one goes with which is the issue. Stephen signature.asc Description: Digital signature
Re: [HACKERS] host name support in pg_hba.conf
On Aug 10, 2010, at 8:23 AM, Stephen Frost wrote: * Peter Eisentraut (pete...@gmx.net) wrote: On mån, 2010-08-09 at 13:56 -0500, Kevin Grittner wrote: Some IP addresses have several host names, including in reverse lookup; how is that handled? This is not possible, or at least the C library APIs don't expose it. Compare the getnameinfo() and getaddrinfo() man pages, for example. Don't know how it happens at a technical level, but I've definitely seen it happen before.. Particularly with Windows domains where they don't have clean-up reverse DNS enabled. Manifests itself by having different host names show up on successive requests... Evil in any case. Multiple hostnames for a given IP address are supported just fine by the DNS. Some C library APIs support this just fine, others (such as getnameinfo) have been simplified to make them more pleasant to use for the common case of displaying a text representation of an IP address in a friendly manner with simple code, at the expense of actually returning correct data. So getnameinfo() is not suitable for this particular usage. If an IP address has multiple hostnames then what getnameinfo() will return isn't well-defined (and I believe there's been a trickle of bugs in implementations such that sometimes they won't return any hostname if there are multiple ones configured in the DNS). Any approach to restrict based on hostnames will either need to just work with forward DNS resolution of hostnames configured in pg_hba.conf to create a list of IP addresses to compare against an incoming connection, or it'll need to use a more general interface to get the reverse DNS of an incoming connection (e.g. gethostbyaddr(), less elegant as that is) before checking forward DNS. The former approach won't work if we want to support wildcard hostnames (accept connections from *.example.com) - and that's the only useful functionality that adding hostname based ACLs provides, I think. If we want to do that, we need to use gethostbyaddr() to get all the claimed hostnames via reverse DNS, and for each of those that matches our ACL do a getaddrinfo() to check it resolves to the connecting IP. This is something that's pretty common to do in the email world, so stealing some robust code from there might be an idea. Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Libpq: PQftype, PQfsize
Hi, I have a test table with varchar(40) column. After executing the following query: select substr(fc,1,2) from test PQftype returns for the result column PG_TYPE_TEXT and PQfsize returns -1. Is it the expected behaviour? The most suprising for me is PQfsize. Tested on PostgreSQL 8.4, 32-bit Windows. Thank you in advance for explanations. Bozena -- 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] Session timeout on commitfest.postgresql.org
Thom Brown t...@linux.com writes: On 10 August 2010 16:26, Robert Haas robertmh...@gmail.com wrote: I don't see how that's possible, unless your browser is eating cookies for breakfast. There's no code anywhere in the application to (a) remove cookies from the database or (b) refuse to use cookies that are in the database based on the time they were issued. I can change the code to set an expires header (in fact, I'm working on that that now), but the symptoms you describe are inexplicable. Not anything to do with this?: http://hivelogic.com/articles/the-safari-cookie-issue-fixed Dunno, because that update was months ago. Robert's comments make the situation even odder, though, because I have *always* seen the commitfest app want me to log back in anytime I hadn't used it recently. I assumed that was policy. I only complained because the timeout seemed to have dropped to an irrationally short value during this fest. Anyway, maybe setting a normal expires date will make it work better. 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
[HACKERS] 8.3 to 8.4 Upgrade issues
We recently upgraded from 8.3 to 8.4 and have seen a performance degredation which we are trying to explain and I have been asked to get a second opinion on the cost of going from LATIN1 to UTF8 (Collation and CType) where the encoding remained SQL_ASCII.. Does anybody have experience on the cost, if any, of making this change? Pg 8.3: Encoding: SQL_ASCII LC_COLLATE: en_US LC_CTYPE: en_US Pg 8.4: Encoding: SQL_ASCII Collation: en_US.UTF-8 Ctype: en_US.UTF-8 -- 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: Add JSON datatype to PostgreSQL (GSoC, WIP)
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Joseph Adams's message of mar ago 10 04:03:43 -0400 2010: An overview, along with my thoughts, of the utility functions: FN_EXTRA, FN_EXTRA_ALLOC, FN_MCXT macros * Useful-ometer: ()o TypeInfo structure and getTypeInfo function * Useful-ometer: ()---o getEnumLabelOids * Useful-ometer: ()---o I think this kind of thing could be stripped from the patch and submitted separately; +1. It's easier all around if a patch does just one thing. Code refactoring and feature addition, in particular, are easier to review separately. 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] Libpq: PQftype, PQfsize
Bozena Potempa bozena.pote...@otc.pl writes: I have a test table with varchar(40) column. After executing the following query: select substr(fc,1,2) from test PQftype returns for the result column PG_TYPE_TEXT and PQfsize returns -1. Is it the expected behaviour? Yes. substr() returns text. But even if it returned varchar, you'd probably get -1 for the fsize. PG does not make any attempt to predict the result width of functions. 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] 8.3 to 8.4 Upgrade issues
Rod Taylor rod.tay...@gmail.com writes: Does anybody have experience on the cost, if any, of making this change? Pg 8.3: Encoding: SQL_ASCII LC_COLLATE: en_US LC_CTYPE: en_US Pg 8.4: Encoding: SQL_ASCII Collation: en_US.UTF-8 Ctype: en_US.UTF-8 Well, *both* of those settings collections are fundamentally wrong/bogus; any collation/ctype setting other than C is unsafe if you've got encoding set to SQL_ASCII. But without knowing what your platform thinks en_US means, it's difficult to speculate about what the difference between them is. I suppose that your libc's default assumption about encoding is not UTF-8, else these would be equivalent. If it had been assuming a single-byte encoding, then telling it UTF8 instead could lead to a significant slowdown in strcoll() speed ... but I would think that would mainly be a problem if you had a lot of non-ASCII data, and if you did, you'd be having a lot of problems other than just performance. Have you noticed any change in sorting behavior? 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] Session timeout on commitfest.postgresql.org
On Tue, Aug 10, 2010 at 1:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: On 10 August 2010 16:26, Robert Haas robertmh...@gmail.com wrote: I don't see how that's possible, unless your browser is eating cookies for breakfast. There's no code anywhere in the application to (a) remove cookies from the database or (b) refuse to use cookies that are in the database based on the time they were issued. I can change the code to set an expires header (in fact, I'm working on that that now), but the symptoms you describe are inexplicable. Not anything to do with this?: http://hivelogic.com/articles/the-safari-cookie-issue-fixed Dunno, because that update was months ago. Robert's comments make the situation even odder, though, because I have *always* seen the commitfest app want me to log back in anytime I hadn't used it recently. I assumed that was policy. I only complained because the timeout seemed to have dropped to an irrationally short value during this fest. Anyway, maybe setting a normal expires date will make it work better. Done. http://git.postgresql.org/gitweb?p=pgcommitfest.git;a=summary While I was at it, I implemented a feature I've been wanting for a while: I made the Status Summary line at the top of the CommitFest page have links to filter by status. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Session timeout on commitfest.postgresql.org
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 10, 2010 at 1:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Anyway, maybe setting a normal expires date will make it work better. Done. [ logs in again ... ] Hm, looks like you went for a one-week timeout? That'll be an improvement for me, I expect, but maybe not for other people. Should it be longer? 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] Session timeout on commitfest.postgresql.org
Tom Lane t...@sss.pgh.pa.us wrote: Hm, looks like you went for a one-week timeout? That'll be an improvement for me, I expect, but maybe not for other people. Should it be longer? The longer the setting, the more convenient for me, but I have a hard time getting work up over logging in once per week. :-) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] string_to_array with an empty input string
Looking through Pavel's string_to_array patch, I notice that the new version of string_to_array returns an empty (zero-element) array when the input string is of zero length, whereas the traditional version returned NULL instead. The patch fails to emulate the old behavior exactly, but rather than fix it, I wonder if we shouldn't make the two versions behave the same. (If we don't have them doing the same thing, we're going to have to document the difference...) The NULL result seems a bit peculiar to me, and the empty-array result saner. Given the general inconsistency surrounding empty arrays elsewhere, I think this wouldn't be a very problematic change for most users. Thoughts? 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] Session timeout on commitfest.postgresql.org
Robert Haas robertmh...@gmail.com wrote: While I was at it, I implemented a feature I've been wanting for a while: I made the Status Summary line at the top of the CommitFest page have links to filter by status. Very nice. I was going to ask to have Ready for Committer split out to its own section, but with this filtering, it's probably not worth the bother. This change will be very nice for CF managers. While we're on the topic of CF app enhancements, I often wished that the date of the last change to the Reviewers column would show underneath the name(s) where the value was not empty and the date was later than both the Last Activity date and the start of the CF. (Either that or count a non-NULL value set into this column as a reason to set the current date into Last Activity, but I like the extra date better.) It occasionally seems as though WiP patches are different enough that there should be a more systematic was to flag them and count them, but I can't think of any concrete way to do that which doesn't introduce more problems than it would fix. And I still think that a link back to the CommitFest Wiki page might prevent the occasional gaff by people new to the application, but that assumes they'd follow the link and read up on the process before jumping in with entries in the app. The two most common issues seem to be putting a URL in the Message-ID field, and putting a whole review into the comment text rather than a brief summary and a link to a post with the review. Occasionally people failed to set a new status when they should have done after linking in a new patch or review. -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] string_to_array with an empty input string
On 10 August 2010 19:41, Tom Lane t...@sss.pgh.pa.us wrote: Looking through Pavel's string_to_array patch, I notice that the new version of string_to_array returns an empty (zero-element) array when the input string is of zero length, whereas the traditional version returned NULL instead. The patch fails to emulate the old behavior exactly, but rather than fix it, I wonder if we shouldn't make the two versions behave the same. (If we don't have them doing the same thing, we're going to have to document the difference...) The NULL result seems a bit peculiar to me, and the empty-array result saner. Given the general inconsistency surrounding empty arrays elsewhere, I think this wouldn't be a very problematic change for most users. Thoughts? I, personally, would expect an empty array output given an empty input, and a null output for a null input. -- Thom Brown Registered Linux user: #516935 -- 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] string_to_array with an empty input string
On Aug 10, 2010, at 11:46 AM, Thom Brown wrote: I, personally, would expect an empty array output given an empty input, and a null output for a null input. +1 David -- 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] Session timeout on commitfest.postgresql.org
On Tue, Aug 10, 2010 at 2:43 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Very nice. I was going to ask to have Ready for Committer split out to its own section, but with this filtering, it's probably not worth the bother. This change will be very nice for CF managers. Glad you like. While we're on the topic of CF app enhancements, I often wished that the date of the last change to the Reviewers column would show underneath the name(s) where the value was not empty and the date was later than both the Last Activity date and the start of the CF. (Either that or count a non-NULL value set into this column as a reason to set the current date into Last Activity, but I like the extra date better.) That seems complex. It occasionally seems as though WiP patches are different enough that there should be a more systematic was to flag them and count them, but I can't think of any concrete way to do that which doesn't introduce more problems than it would fix. I agree that it occasionally seems that way, but it seems hard to get worked up about it. And I still think that a link back to the CommitFest Wiki page might prevent the occasional gaff by people new to the application, but that assumes they'd follow the link and read up on the process before jumping in with entries in the app. The two most common issues seem to be putting a URL in the Message-ID field, and putting a whole review into the comment text rather than a brief summary and a link to a post with the review. Oh, yeah, I forgot that you asked for this. It's probably a good idea to work that in somewhere. Occasionally people failed to set a new status when they should have done after linking in a new patch or review. I remain unconvinced that any tweaking of the system in this area comes out to a net plus. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Session timeout on commitfest.postgresql.org
Robert Haas robertmh...@gmail.com wrote: On Tue, Aug 10, 2010 at 2:43 PM, Kevin Grittner While we're on the topic of CF app enhancements, I often wished that the date of the last change to the Reviewers column would show underneath the name(s) where the value was not empty and the date was later than both the Last Activity date and the start of the CF. (Either that or count a non-NULL value set into this column as a reason to set the current date into Last Activity, but I like the extra date better.) That seems complex. Well, yeah, but I found myself doing this by hand when I was getting organized to send out off-list nag emails. Whenever I find myself growling Some day they'll invent a machine to do this while doing some tedious task, I consider it a candidate for automation. ;-) Occasionally people failed to set a new status when they should have done after linking in a new patch or review. I remain unconvinced that any tweaking of the system in this area comes out to a net plus. Agreed; that was just part of my list of things someone might get right more often if they had a handy link to the documentation to which they could refer while they were in making entries. -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] Surprising dead_tuple_count from pgstattuple
After much code reading, testing, and using the extremely handy pageinspect contrib to look at pages, here's what I believe is happening. I am not attempting to describe every possible scenario, only this one test path. Following my short test scenario above... - Inserted rows get line pointers with status 1 (LP_NORMAL) - When I do the 100 row delete, those rows stay status 1, but get their t_xmax set, indicating they were deleted. - When I do the next 200 row delete, those rows also get their t_xmax set. - As a side-effect to the deletion, the hot prune feature kicks in. The 2 pages in question were processed by a call to heap_page_prune, which set the line pointers of the previous 100 deleted rows to 3 (LP_DEAD). Now I have 100 LP_DEAD and 200 LP_NORMAL with xmax set. - When I do the vacuum, all 300 are vacuumed -- line pointer set to status 0 (LP_UNUSED). - Because my table has no indexes, lazy_scan_heap calls lazy_vacuum_page directly for each block, and reports the variable tups_vacuumed (removed 200 row versions in 2 pages). However, tups_vacuumed is computed without counting the 100 LP_DEAD tuples, because per the code comment, it thinks to do so would be double-counting. Perhaps the output should say something like: removed 300 row versions (200 were recently deleted and 100 were previously deleted). Whatever the phrasing, I don't know why 200 is the most significant number here, and 300 isn't mentioned at all. - If my table did have indexes, as in the original test case of this thread, then the removed xxx row version in yyy pages message comes from lazy_vacuum_heap instead. However, instead of using tups_vacuumed, this code reports the actual number of tuples actually set to status 0. I would like to respectfully suggest that the vacuum output line removed xx row versions in yy pages should show the same counts regardless of whether or not there's an index on the table. I would suggest that the value reported by lazy_vacuum_heap is correct, and is what I would expect to see. I think it would be fine if it also reports the breakdown of LP_DEAD vs LP_NORMAL tuples vacuumed, if that is deemed useful. Regarding the output of pgstattuple, via the call to HeapTupleSatisfiesVisibility, it appears that this simply returns true for these hot-pruned LP_DEAD tuples, skewing the counts accordingly. Does that make sense? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Surprising-dead-tuple-count-from-pgstattuple-tp2266955p2471232.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] patch: to_string, to_array functions
Brendan Jurd dire...@gmail.com writes: I have attached v4 of the patch against HEAD, and also an incremental patch showing just my changes against v3. I'll mark this as ready for committer. Applied, with the discussed changes and some code editing. 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] patch: to_string, to_array functions
2010/8/10 Tom Lane t...@sss.pgh.pa.us: Brendan Jurd dire...@gmail.com writes: I have attached v4 of the patch against HEAD, and also an incremental patch showing just my changes against v3. I'll mark this as ready for committer. Applied, with the discussed changes and some code editing. regards, tom lane Thank you very much 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] Initial review of xslt with no limits patch
Mike Fowler m...@mlfowler.com writes: On 06/08/10 17:50, Pavel Stehule wrote: attached updated patch with regression test Bravely ignoring the quotation/varidic/favourite_scheme_here conversations, I've taken a look at the patch as is. Thanks to Tom's input I can now correctly drive the function. I can also report that code is now behaving in the expected way. I've gone ahead and applied this patch, since the subsequent discussion seemed to be getting *extremely* far afield from the expressed intent of the patch, and nobody had pointed out a reason not to fix the number-of-parameters limitation. I think we have a few TODO items here: * Invent ... and document ... an API that permits safe assembly of a parameter list from non-constant (and perhaps untrustworthy) values. * Fix xslt_process' failure to report (some?) errors detected by libxslt. * Move the functionality to a less deprecated place. None of these are within the scope of the current patch though. 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] 8.3 to 8.4 Upgrade issues
On Tue, Aug 10, 2010 at 13:49, Tom Lane t...@sss.pgh.pa.us wrote: Rod Taylor rod.tay...@gmail.com writes: Does anybody have experience on the cost, if any, of making this change? Pg 8.3: Encoding: SQL_ASCII LC_COLLATE: en_US LC_CTYPE: en_US Pg 8.4: Encoding: SQL_ASCII Collation: en_US.UTF-8 Ctype: en_US.UTF-8 Well, *both* of those settings collections are fundamentally wrong/bogus; any collation/ctype setting other than C is unsafe if you've got encoding set to SQL_ASCII. But without knowing what your platform thinks en_US means, it's difficult to speculate about what the difference between them is. I suppose that your libc's default assumption about encoding is not UTF-8, else these would be equivalent. If it had been assuming a single-byte encoding, then telling it UTF8 instead could lead to a significant slowdown in strcoll() speed ... but I would think that would mainly be a problem if you had a lot of non-ASCII data, and if you did, you'd be having a lot of problems other than just performance. Have you noticed any change in sorting behavior? Agreed with it being an interesting choice of settings. Nearly all of the data is 7-bit ASCII and what isn't seems to be a mix of UTF8, LATIN1, and LATIN15. I'm pretty sure it interpreted en_US to be LATIN1. There haven't been any noticeable changes in sorting order that I know of. -- 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] string_to_array with an empty input string
On 10 August 2010 19:48, David E. Wheeler da...@kineticode.com wrote: On Aug 10, 2010, at 11:46 AM, Thom Brown wrote: I, personally, would expect an empty array output given an empty input, and a null output for a null input. +1 Agreed. After all, the result isn't indeterminate - it's an empty array. Some people might think that it's useful for the result to be NULL, but they'd probably also think that it's useful for an empty string to be NULL. -- Regards, Peter Geoghegan -- 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] 8.3 to 8.4 Upgrade issues
Rod Taylor rod.tay...@gmail.com writes: Agreed with it being an interesting choice of settings. Nearly all of the data is 7-bit ASCII and what isn't seems to be a mix of UTF8, LATIN1, and LATIN15. I'm pretty sure it interpreted en_US to be LATIN1. There haven't been any noticeable changes in sorting order that I know of. Well, if you've got non-ASCII data that you know is not UTF8, then setting a UTF8-dependent locale setting is a really really bad idea :-(. You are risking not just bad performance but seriously bad misbehavior. If you use a LATIN-n (or other single-byte-encoding) locale, the worst that data in other encodings can do to you is sort into odd positions. If you use a UTF8 locale and have data of other encodings, then strcoll() can tell that you are violating the encoding spec, and on many platforms it goes entirely berserk when you do that. glibc in particular does not play nice with that. You didn't say what platform this is, but if it's glibc based then you are sitting on a ticking time bomb, and you had better dump and reinitialize in a safer locale setting before your data gets eaten. 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] Surprising dead_tuple_count from pgstattuple
Gordon Shannon gordo...@gmail.com writes: - Because my table has no indexes, lazy_scan_heap calls lazy_vacuum_page directly for each block, and reports the variable tups_vacuumed (removed 200 row versions in 2 pages). However, tups_vacuumed is computed without counting the 100 LP_DEAD tuples, because per the code comment, it thinks to do so would be double-counting. Perhaps the output should say something like: removed 300 row versions (200 were recently deleted and 100 were previously deleted). Well, the problem is to tell which LP_DEAD rows are recently deleted. I agree the output leaves something to be desired, but I don't see how to improve it without tracking a lot more state than we do now. It's not clear that it's really worth much effort; how many people look at VACUUM VERBOSE output at all, let alone closely? Whatever the phrasing, I don't know why 200 is the most significant number here, and 300 isn't mentioned at all. IIRC, the reason for choosing to do it that way is that 200, and not 300, is the best indication of the amount of space reclaimed. LP_DEAD tuple headers don't take a lot of space. We're trying to give an idea of how many real tuples got reclaimed during vacuum. I would like to respectfully suggest that the vacuum output line removed xx row versions in yy pages should show the same counts regardless of whether or not there's an index on the table. But in fact the presence of an index does affect the behavior, above and beyond vacuum's counting or failure to count, because it changes what HOT updating and HOT pruning will do. So the initial state that vacuum is dealing with could well be different. 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] Measuring execution time
On 10/08/10 19:46, vamsi krishna wrote: Hello all I want to measure the execution time spent running an SQL select query after the plan generation. So precisely I want to put my start timer before createQueryDesc() or ExecutorStart() and end timer after freeQueryDesc() or ExecutorEnd(). Right now I did so in spi.c, explain.c, pquery.c but they are not the default execution cases. Can someone tell me which file holds the default call to ExecutorStart(), because I also want to see the select query result unlike in the case of explain ? See ProcessQuery in src/backend/tcop/query.c Cheers Mark
Re: [HACKERS] Measuring execution time
On 11/08/10 14:42, Mark Kirkwood wrote: On 10/08/10 19:46, vamsi krishna wrote: Hello all I want to measure the execution time spent running an SQL select query after the plan generation. So precisely I want to put my start timer before createQueryDesc() or ExecutorStart() and end timer after freeQueryDesc() or ExecutorEnd(). Right now I did so in spi.c, explain.c, pquery.c but they are not the default execution cases. Can someone tell me which file holds the default call to ExecutorStart(), because I also want to see the select query result unlike in the case of explain ? See ProcessQuery in src/backend/tcop/query.c Doh - sorry, I see you have that guy! Have a look at exec_simple_query in src/backend/tcop/postgres.c, by the time pg_plan_queries returns you have finished planning. The call to CreateQueryDesc is going to be from PortalStart in that case. Hopefully this will get you started (you may need to look at exec_*_message functions too). Mark
Re: [HACKERS] review: psql: edit function, show function commands patch
On Mon, Aug 9, 2010 at 7:40 AM, Pavel Stehule pavel.steh...@gmail.com wrote: updated patch attached I spent some time cleaning this up tonight. I think that the \e and \ef portions are now ready to commit, but I am not quite happy with the \sf stuff yet, so I've broken that out into a separate patch, which is also attached. Barring objections, I'll commit the \e and \ef portions of this in the morning after one final read-through. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company edit8-rmh.patch Description: Binary data sf.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] review: psql: edit function, show function commands patch
Robert Haas robertmh...@gmail.com writes: I spent some time cleaning this up tonight. I think that the \e and \ef portions are now ready to commit, but I am not quite happy with the \sf stuff yet, so I've broken that out into a separate patch, which is also attached. Barring objections, I'll commit the \e and \ef portions of this in the morning after one final read-through. The \e patch definitely needs another read-through. I noticed a number of comments that were still pretty poor English, and one --- /* skip header lines */ --- that seems just plain wrong. The actual intent of that next bit is to increase lineno to account for header lines, which is not well conveyed by skip. BTW, at least in the usage in that loop, get_functiondef_dollarquote_tag seems grossly overdesigned. It would be clearer, shorter, and faster if you just had a strncmp test for AS $function there. Also, the entire thing is subject to misbehavior in the case of \e (as opposed to \ef), which really cannot safely assert() that it's reading the output of pg_get_functiondef(). My inclination is to pull that part out of do_edit and put it into \ef-specific code. Also, there seemed to be some gratuitous inconsistency in the handling of tests on line number variables, eg some places lineno 0 and others lineno = 1. 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] MERGE Specification
On Tue, Aug 10, 2010 at 10:29 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 10/08/10 12:08, Boxuan Zhai wrote: Thanks for your feedback. I fixed all the above waring bugs. Find the new patch in attachement. Thanks. I'm getting an assertion failure with this statement: CREATE TABLE foo (id int4); MERGE into foo t USING (select id FROM generate_series(1,5) id) AS s ON t.id = s.id WHEN NOT MATCHED THEN INSERT (id) VALUES (s.id); The query works on my machine. TRAP: FailedAssertion(!(ActiveSnapshotSet()), File: postgres.c, Line: 749) That's easily fixed - you need to add case T_MergeStmt to the list of optimizable command types in analyze_requires_snapshot() function. Unfortunately that doesn't get you far, the query then trips another assertion: TRAP: FailedAssertion(!(list_length(resultRelations) == list_length(subplans)), File: createplan.c, Line: 3929) I just found that no Assert() works in my codes. I think it is because the assertion is no enabled. How to enable assertion. To define USE_ASSERT_CHECKING somewhere? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] MERGE Specification
Boxuan Zhai wrote: I just found that no Assert() works in my codes. I think it is because the assertion is no enabled. How to enable assertion. To define USE_ASSERT_CHECKING somewhere? When you run configure before make, use --enable-cassert. The normal trio for working on the PostgreSQL code is: ./configure --enable-depend --enable-cassert --enable-debug Generally the only reason to build as a developer without asserts on is to do performance testing. They will slow some portions of the code down significantly. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] MERGE Specification
On Wed, Aug 11, 2010 at 12:14 PM, Greg Smith g...@2ndquadrant.com wrote: Boxuan Zhai wrote: I just found that no Assert() works in my codes. I think it is because the assertion is no enabled. How to enable assertion. To define USE_ASSERT_CHECKING somewhere? When you run configure before make, use --enable-cassert. The normal trio for working on the PostgreSQL code is: ./configure --enable-depend --enable-cassert --enable-debug Generally the only reason to build as a developer without asserts on is to do performance testing. They will slow some portions of the code down significantly. Thanks. I will test MERGE under this new configuration. A new patch will be submitted once I fix all the asserting bugs. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us http://www.2ndquadrant.us/
Re: [HACKERS] Patch to show individual statement latencies in pgbench output
Florian Pflug wrote:
Attached is an updated version (v4).
I've attached a v5. No real code changes from Florian's version, just
some wording/style fixes and rework on the documentation. The user side
is now consistent about calling these statement latencies for example,
even though the internals still call them command latencies most places.
Since using this new feature will introduce a whole stack of new calls
to get the system time, I also added a warning about that impacting results:
Note that collecting the additional timing information needed for
detailed latency computation does add some overhead. This will slow
average execution speed and lower the computed TPS. The exact amount
of slowdown varies significantly based on platform and hardware.
Comparing average TPS values with and without latency reporting enabled
is a good way to measure if the timing overhead is significant.
I wasn't able to see any significant slowdown on my modern Linux systems
doing such a test:
$ ./pgbench -T 10 -S -c 8 -j 4 pgbench
tps = 6716.039813 (including connections establishing)
tps = 6720.238878 (excluding connections establishing)
$ ./pgbench -T 10 -S -c 8 -j 4 -r pgbench
tps = 6708.544618 (including connections establishing)
tps = 6712.728526 (excluding connections establishing)
But I know gettimeofday is fast here. Worth including a warning for
though I think.
I'm out of things to check here, marking this one ready for a committer
review. The patch hasn't had a committer assigned yet, so whoever wants
to claim it should mark the CF app.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index 53cc4b6..90d11f1 100644
*** a/contrib/pgbench/pgbench.c
--- b/contrib/pgbench/pgbench.c
*** int fillfactor = 100;
*** 133,138
--- 133,139
bool use_log; /* log transaction latencies to a file */
bool is_connect; /* establish connection for each transaction */
+ bool is_latencies; /* report per-command latencies */
int main_pid; /* main process id used in log filename */
char *pghost = ;
*** typedef struct
*** 171,177
int64 until; /* napping until (usec) */
Variable *variables; /* array of variable definitions */
int nvariables;
! instr_time txn_begin; /* used for measuring latencies */
int use_file; /* index in sql_files for this client */
bool prepared[MAX_FILES];
} CState;
--- 172,179
int64 until; /* napping until (usec) */
Variable *variables; /* array of variable definitions */
int nvariables;
! instr_time txn_begin; /* used for measuring transaction latencies */
! instr_time stmt_begin; /* used for measuring statement latencies */
int use_file; /* index in sql_files for this client */
bool prepared[MAX_FILES];
} CState;
*** typedef struct
*** 199,204
--- 201,207
/*
* queries read from files
*/
+ #define COMMENT_COMMAND 0
#define SQL_COMMAND 1
#define META_COMMAND 2
#define MAX_ARGS 10
*** static const char *QUERYMODE[] = {simpl
*** 216,224
--- 219,230
typedef struct
{
+ char *line; /* line containing the command */
int type; /* command type (SQL_COMMAND or META_COMMAND) */
int argc; /* number of commands */
char *argv[MAX_ARGS]; /* command list */
+ instr_time *exec_elapsed; /* time spent executing this command (per thread) */
+ int *exec_count; /* number of executions (per thread) */
} Command;
static Command **sql_files[MAX_FILES]; /* SQL script files */
*** usage(const char *progname)
*** 287,292
--- 293,299
define variable for use by custom script\n
-f FILENAME read transaction script from FILENAME\n
-j NUM number of threads (default: 1)\n
+ -r report average latency per command\n
-l write transaction times to log file\n
-M {simple|extended|prepared}\n
protocol for submitting queries to server (default: simple)\n
*** clientDone(CState *st, bool ok)
*** 740,746
/* return false iff client should be disconnected */
static bool
! doCustom(CState *st, instr_time *conn_time, FILE *logfile)
{
PGresult *res;
Command **commands;
--- 747,753
/* return false iff client should be disconnected */
static bool
! doCustom(TState* thread, CState *st, instr_time *conn_time, FILE *logfile)
{
PGresult *res;
Command **commands;
*** top:
*** 800,805
--- 807,824
#endif
}
+ /* Accumulate per-command execution times if per-command latencies
+ are requested */
+ if (is_latencies (commands[st-state]-type != COMMENT_COMMAND))
+ {
+ instr_time now;
+
+
Re: [HACKERS] pg_restore should accept multiple -t switches?
On Tue, Aug 10, 2010 at 11:00 PM, Tom Lane t...@sss.pgh.pa.us wrote: Fujii Masao masao.fu...@gmail.com writes: Is it worth allowing pg_restore to accept multiple -t switches as well as pg_dump? It's on the TODO list already, no? Thanks! I found it on the list and understood there are other many restrictions in pg_restore's -t switch. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Regression tests versus the buildfarm environment
There's an interesting buildfarm failure here: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=polecatdt=2010-08-10%2023:46:10 It appears to me that this was caused by the concurrent run of another buildfarm animal on the same physical machine, namely: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=colugosdt=2010-08-11%2000:02:58 Both animals are trying to test HEAD, which means that pg_regress defaults to the same postmaster port number in both builds: if (temp_install !port_specified_by_user) /* * To reduce chances of interference with parallel installations, use * a port number starting in the private range (49152-65535) * calculated from the version number. */ port = 0xC000 | (PG_VERSION_NUM 0x3FFF); We observe colugos successfully starting on that port: == starting postmaster== running on port 57332 with pid 47019 == creating database regression == CREATE DATABASE ALTER DATABASE ... etc etc ... polecat comes along what must be only moments later, and tries to use the same port for its temp install: == starting postmaster== running on port 57332 with pid 47022 == creating database regression == ERROR: duplicate key value violates unique constraint pg_database_datname_index DETAIL: Key (datname)=(regression) already exists. command failed: /usr/local/src/build-farm-3.2/builds/HEAD/pgsql.15278/src/test/regress/./tmp_check/install//usr/local/src/build-farm-3.2/builds/HEAD/inst/bin/psql -X -c CREATE DATABASE \regression\ TEMPLATE=template0 ENCODING='SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C' postgres pg_ctl: PID file /usr/local/src/build-farm-3.2/builds/HEAD/pgsql.15278/src/test/regress/./tmp_check/data/postmaster.pid does not exist Is server running? pg_regress: could not stop postmaster: exit code was 256 Now the postmaster log shows that the second postmaster correctly recognized that the port number was already in use, so it bailed out: == pgsql.15278/src/test/regress/log/postmaster.log === [4c61f2d2.b7ae:1] FATAL: lock file /tmp/.s.PGSQL.57332.lock already exists [4c61f2d2.b7ae:2] HINT: Is another postmaster (PID 47019) using socket file /tmp/.s.PGSQL.57332? However, pg_regress failed to have a clue about what had happened, and bulled ahead trying to run the regression tests (against the postmaster started by the other pg_regress instance). A look at the code shows that it is merely trying to run psql, and if psql reports that it can connect to the specified port, then pg_regress thinks the postmaster started OK. Of course, psql was really reporting that it could connect to the other instance's postmaster. I've seen similar multiple-postmaster-interference symptoms before in the buildfarm, but never really understood the cause. I am not sure if there's anything very good we can do about the problem of pg_regress misidentifying the postmaster it's managed to connect to. A real solution would probably be much more trouble than it's worth, anyway. However, it does seem like we ought to be able to do something about two buildfarm critters defaulting to the same choice of port number. The buildfarm infrastructure goes to great lengths to pick nonconflicting port numbers for the installed postmasters it runs; but we're ignoring all that effort and just using a hardwired port number for make check. This is dumb. pg_regress does have a --port argument that can be used to override that default. I don't know whether the buildfarm script calls pg_regress directly or does make check. If the latter, we'd need to twiddle the Makefiles to allow a port number to get passed in. But this seems well worthwhile to me. 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] Cost of AtEOXact_Buffers in --enable-cassert
Andres Freund wrote: The most prohibitively expensive part is the AtEOXact_Buffers check of running through all buffers and checking their pin count. And it makes $app's regression tests take thrice their time... Have you tried reducing shared_buffers from the default the system found by probing to make this overhead smaller? -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] RecordTransactionCommit() and SharedInvalidationMessages
On Tue, Aug 10, 2010 at 9:30 AM, Robert Haas robertmh...@gmail.com wrote: It appears to me that RecordTransactionCommit() only needs to WAL-log shared invalidation messages when wal_level is hot_standby, but I don't see a guard to prevent it from doing it in all cases. Perhaps right. During not hot standby, there is no backend which the startup process should send invalidation message to in the standby. So, ISTM we don't need to log invalidation message when wal_level is not hot_standby. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] assertions and constraint triggers
Thinking about SQL assertions (check constraints that are independent of one particular table), do you think it would be reasonable to implement those on top of constraint triggers? On creation you'd hook up a trigger to each of the affected tables. And the trigger function runs the respective check expression. Conceptually, this doesn't seem to be very far away from foreign key constraints after all. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
