Re: [HACKERS] Notes on implementing URI syntax for libpq
It was proposed a while ago for libpq to support URI syntax for specifying the connection information: ... Now we're going to actually implement this. Do you know that we had this feature (more or less) in libpq for years but it was removed quite a while ago. It should still be there in the archive, not sure though if the old code fits the requirements for this feature completely. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at googlemail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, 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] Notes on implementing URI syntax for libpq
* Alexander Shulgin: This, in my opinion, is very similar to what we would like to achieve with the URI syntax, so the above could also be specified using a URI parameter like this: psql -d postgresql://example.net:5433/mydb How would you specifiy a local port/UNIX domain socket? Would it be possible to add something like psql -d postgresql+ssh://fweimer@db5/var/run/postgresql/.s.PGSQL.5432 similar to what Subversion supports? (This might have security implications when used from untrusted PHP scripts.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Not HOT enough
On Tue, Nov 22, 2011 at 7:25 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, Nov 22, 2011 at 11:40 PM, Simon Riggs si...@2ndquadrant.com wrote: I think this is unsafe for shared catalogs. I think so too. Thats why it uses IsMVCCSnapshot() to confirm when it is safe to do so. Ah, you mean access to shared catalogs using MVCC snapshots. Yeah. This change would have the disadvantage of disabling HOT cleanup for shared catalogs; I'm not sure whether that's a good decision. But now that you mention it, something seems funky about the other bit you mention, too: + /* MVCC snapshots ignore other databases */ + if (!allDbs + proc-databaseId != MyDatabaseId + proc-databaseId != 0) /* always include WalSender */ + continue; + It doesn't make sense for the RecentGlobalXmin calculation to depend on whether or not the current snapshot is an MVCC snapshot, because RecentGlobalXmin is a global variable not related to any particular snapshot. I don't believe it's safe to assume that RecentGlobalXmin will only ever be used in conjunction with the most-recently-taken snapshot. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Permissions checks for range-type support functions
On Tue, Nov 22, 2011 at 6:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: 2. The ANALYZE option is flat out dangerous, because it allows any function with the signature f(internal) returns bool to be called as though it's a typanalyze function. There are a couple of such functions in the catalogs already, and either of them will probably crash the backend if invoked as typanalyze on a range column. It's always seemed mildly insane to me that we don't distinguish between different flavors of internal. That seems like an accident waiting to happen. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Not HOT enough
On Wed, Nov 23, 2011 at 2:00 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Nov 22, 2011 at 7:25 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, Nov 22, 2011 at 11:40 PM, Simon Riggs si...@2ndquadrant.com wrote: I think this is unsafe for shared catalogs. I think so too. Thats why it uses IsMVCCSnapshot() to confirm when it is safe to do so. Ah, you mean access to shared catalogs using MVCC snapshots. Yeah. This change would have the disadvantage of disabling HOT cleanup for shared catalogs; I'm not sure whether that's a good decision. No, it disables cleanup when being read. They are still VACUUMed normally. Note that non-MVCC snapshots never did run HOT page-level cleanup, so this hardly changes anything. And it effects shared catalogs only, which are all low traffic anyway. But now that you mention it, something seems funky about the other bit you mention, too: + /* MVCC snapshots ignore other databases */ + if (!allDbs + proc-databaseId != MyDatabaseId + proc-databaseId != 0) /* always include WalSender */ + continue; + It doesn't make sense for the RecentGlobalXmin calculation to depend on whether or not the current snapshot is an MVCC snapshot, because RecentGlobalXmin is a global variable not related to any particular snapshot. I don't believe it's safe to assume that RecentGlobalXmin will only ever be used in conjunction with the most-recently-taken snapshot. Why would that matter exactly? RecentGlobalXmin is used in 4 places and this works with them all. This changes the meaning of that variable from what it was previously, but so what? It's backend local. The huge benefit is that we clean up data in normal tables much better than we did before in cases where people use multiple databases, which is a common case. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Inlining comparators as a performance optimisation
On Fri, Nov 18, 2011 at 2:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Fri, Nov 18, 2011 at 5:20 AM, Robert Haas robertmh...@gmail.com wrote: I think that we should really consider doing with this patch what Tom suggested upthread; namely, looking for a mechanism to allow individual datatypes to offer up a comparator function that doesn't require bouncing through FunctionCall2Coll(). I don't think its credible to implement that kind of generic improvement at this stage of the release cycle. Er, *what*? We're in mid development cycle, we are nowhere near release. When exactly would you have us make major changes? In any case, what I understood Robert to be proposing was an add-on feature that could be implemented in one datatype at a time. Not a global flag day. We couldn't really do the latter anyway without making life very unpleasant for authors of extension datatypes. Tom, whenever you think I've said something you really disagree with, just assume there's a misunderstanding. Like here. Of course it is OK to make such changes at this time. Given we have 2 months to the last CF of this release, inventing a generic infrastructure is unlikely to be finished and complete in this dev cycle, so requesting that isn't a practical suggestion, IMHO. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Not HOT enough
On Wed, Nov 23, 2011 at 9:25 AM, Simon Riggs si...@2ndquadrant.com wrote: Yeah. This change would have the disadvantage of disabling HOT cleanup for shared catalogs; I'm not sure whether that's a good decision. No, it disables cleanup when being read. They are still VACUUMed normally. Note that non-MVCC snapshots never did run HOT page-level cleanup, so this hardly changes anything. And it effects shared catalogs only, which are all low traffic anyway. I think low traffic is the key point. I understand that you're not changing the VACUUM behavior, but you are making heap_page_prune_opt() not do anything when a shared catalog is involved. That would be unacceptable if we expected shared catalogs to be updated frequently, either now or in the future, but I guess we don't expect that. I suppose we could compute two RecentGlobalXmin values, one for all databases and one for just the current database. But that would make GetSnapshotData() slower, which would almost certainly be a cure worse than the disease. But now that you mention it, something seems funky about the other bit you mention, too: + /* MVCC snapshots ignore other databases */ + if (!allDbs + proc-databaseId != MyDatabaseId + proc-databaseId != 0) /* always include WalSender */ + continue; + It doesn't make sense for the RecentGlobalXmin calculation to depend on whether or not the current snapshot is an MVCC snapshot, because RecentGlobalXmin is a global variable not related to any particular snapshot. I don't believe it's safe to assume that RecentGlobalXmin will only ever be used in conjunction with the most-recently-taken snapshot. Why would that matter exactly? RecentGlobalXmin is used in 4 places and this works with them all. This changes the meaning of that variable from what it was previously, but so what? It's backend local. I don't object to changing the meaning of the variable, but I don't understand how it can be correct to include backends from other databases in the RecentGlobalXmin calculation when using an MVCC snapshot, but not otherwise. Come to think of it, when does GetSnapshotData() get called with a non-MVCC snapshot anyway? The huge benefit is that we clean up data in normal tables much better than we did before in cases where people use multiple databases, which is a common case. I understand the benefit; I just want to make sure we're not going to break anything. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Not HOT enough
Excerpts from Robert Haas's message of mié nov 23 12:15:55 -0300 2011: And it effects shared catalogs only, which are all low traffic anyway. I think low traffic is the key point. I understand that you're not changing the VACUUM behavior, but you are making heap_page_prune_opt() not do anything when a shared catalog is involved. That would be unacceptable if we expected shared catalogs to be updated frequently, either now or in the future, but I guess we don't expect that. Maybe not pg_database or pg_tablespace and such, but I'm not so sure about pg_shdepend. (Do we record pg_shdepend entries for temp tables?) -- Á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] Permissions checks for range-type support functions
Excerpts from Robert Haas's message of mié nov 23 11:01:50 -0300 2011: On Tue, Nov 22, 2011 at 6:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: 2. The ANALYZE option is flat out dangerous, because it allows any function with the signature f(internal) returns bool to be called as though it's a typanalyze function. There are a couple of such functions in the catalogs already, and either of them will probably crash the backend if invoked as typanalyze on a range column. It's always seemed mildly insane to me that we don't distinguish between different flavors of internal. That seems like an accident waiting to happen. Well, before we had INTERNAL, there was only OPAQUE which conflated even more things that we now distinguish (at least trigger and cstring, not sure if there were others). -- Á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] Not HOT enough
On Wed, Nov 23, 2011 at 10:20 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of mié nov 23 12:15:55 -0300 2011: And it effects shared catalogs only, which are all low traffic anyway. I think low traffic is the key point. I understand that you're not changing the VACUUM behavior, but you are making heap_page_prune_opt() not do anything when a shared catalog is involved. That would be unacceptable if we expected shared catalogs to be updated frequently, either now or in the future, but I guess we don't expect that. Maybe not pg_database or pg_tablespace and such, but I'm not so sure about pg_shdepend. (Do we record pg_shdepend entries for temp tables?) Hmm, I'm not seeing any increase in the number of entries in pg_shdepend when I create either a temporary or permanent table: rhaas=# select sum(1) from pg_shdepend; sum - 2 (1 row) rhaas=# create temp table xyz (a int); CREATE TABLE rhaas=# select sum(1) from pg_shdepend; sum - 2 (1 row) rhaas=# create table abc (a int); CREATE TABLE rhaas=# select sum(1) from pg_shdepend; sum - 2 (1 row) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Not HOT enough
Excerpts from Robert Haas's message of mié nov 23 12:28:38 -0300 2011: Hmm, I'm not seeing any increase in the number of entries in pg_shdepend when I create either a temporary or permanent table: rhaas=# select sum(1) from pg_shdepend; sum - 2 (1 row) rhaas=# create temp table xyz (a int); CREATE TABLE rhaas=# select sum(1) from pg_shdepend; sum - 2 (1 row) That's because the owner is pinned (i.e. the bootstrap user). Try with a different user. I see new rows with both temp and non-temp tables. -- Á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] Not HOT enough
On Wed, Nov 23, 2011 at 10:35 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: That's because the owner is pinned (i.e. the bootstrap user). Try with a different user. I see new rows with both temp and non-temp tables. Oh, wow. I had no idea it worked like that. You learn something new every day. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Permissions checks for range-type support functions
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mié nov 23 11:01:50 -0300 2011: It's always seemed mildly insane to me that we don't distinguish between different flavors of internal. That seems like an accident waiting to happen. Well, before we had INTERNAL, there was only OPAQUE which conflated even more things that we now distinguish (at least trigger and cstring, not sure if there were others). Yeah, we previously subdivided OPAQUE to get rid of exactly this type of problem. Not sure if it's worth going further. The case that would be problematic would be if we had two different calling contexts that invoked internal-using functions, both accessible for untrusted users to set up which function gets called, and sharing identical function signatures. ATM I believe the only calling contexts that untrusted users can control are operator selectivity functions (restriction and join flavors) and encoding conversion functions; and those three cases have signatures that are distinct from each other and from all privileged cases. But this certainly is something that could accidentally get broken in future. I don't think we want to split INTERNAL into the number of distinct pseudotypes that would be required to cover everything, but it might be worth inventing a couple more for the selectivity cases, if we ever change those APIs again. 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] [JDBC] Optimize postgres protocol for fixed size arrays
On Tue, Nov 22, 2011 at 6:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oliver Jowett oli...@opencloud.com writes: On 23 November 2011 10:47, Mikko Tiihonen mikko.tiiho...@nitorcreations.com wrote: Here is a patch that adds a new flag to the protocol that is set when all elements of the array are of same fixed size. How does a client detect that this feature is supported? The only way that anything like this will go in is as part of a protocol version bump, so discoverability would reduce to knowing which protocol you're using. We should file this away as one of the things we might want to do whenever there's sufficient critical mass for a new wire protocol version. Note that COPY BINARY files would be affected too, and so we'd want to make sure that this sort of change is recognizable from a binary file's header. Wire format changes can only be made with a protocol version bump? Is this a new policy? In the past they were just made...for example the money type was bumped to 64 bits. In the past it was always buyer beware for binary format consumers. 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] Not HOT enough
On Wed, Nov 23, 2011 at 3:20 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of mié nov 23 12:15:55 -0300 2011: And it effects shared catalogs only, which are all low traffic anyway. I think low traffic is the key point. I understand that you're not changing the VACUUM behavior, but you are making heap_page_prune_opt() not do anything when a shared catalog is involved. That would be unacceptable if we expected shared catalogs to be updated frequently, either now or in the future, but I guess we don't expect that. Maybe not pg_database or pg_tablespace and such, but I'm not so sure about pg_shdepend. (Do we record pg_shdepend entries for temp tables?) Normal catalog access does not use HOT and never has. If catalogs need VACUUMing then autovacuum takes care of it. If we're saying that isn't enough and we actually depend on the occasional user inspecting the catalog then we are already hosed. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] [JDBC] Optimize postgres protocol for fixed size arrays
Merlin Moncure mmonc...@gmail.com writes: On Tue, Nov 22, 2011 at 6:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: The only way that anything like this will go in is as part of a protocol version bump, Wire format changes can only be made with a protocol version bump? Is this a new policy? In the past they were just made...for example the money type was bumped to 64 bits. In the past it was always buyer beware for binary format consumers. Well, (a) our standards have gone up over time, (b) binary protocol is getting more widely used (in part due to your own efforts), and (c) money is a third-class stepchild anyway. I don't think we can get away with changing the binary representation of such widely used types as int and float arrays, unless we have some pretty carefully thought through notion of how the client and server will negotiate what to do. Now it's possible we could do that without formally calling it a protocol version change, but I don't care at all for the idea of coming up with one-off hacks every time somebody decides that some feature is important enough that they have to have it Right Now instead of waiting for a sufficient accumulation of reasons to have a protocol flag day. I think but we made arrays a bit smaller! is a pretty lame response to have to give when somebody complains that Postgres 9.2 broke their client software. When we do it, I want to have a *long* list of good reasons. BTW, so far as the actual array format is concerned, I don't think the proposal is acceptable as-is: it renders the received array entirely unreadable unless the reader knows a-priori what the sender thought the typlen was. It would be a lot better if the fixed-length flag meant that the typlen is given once in the array header rather than once per element. I'm not thrilled by the no nulls restriction, either, though I admit I don't have a good idea about avoiding that offhand. 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] Not HOT enough
Excerpts from Simon Riggs's message of mié nov 23 13:14:04 -0300 2011: On Wed, Nov 23, 2011 at 3:20 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of mié nov 23 12:15:55 -0300 2011: And it effects shared catalogs only, which are all low traffic anyway. I think low traffic is the key point. I understand that you're not changing the VACUUM behavior, but you are making heap_page_prune_opt() not do anything when a shared catalog is involved. That would be unacceptable if we expected shared catalogs to be updated frequently, either now or in the future, but I guess we don't expect that. Maybe not pg_database or pg_tablespace and such, but I'm not so sure about pg_shdepend. (Do we record pg_shdepend entries for temp tables?) Normal catalog access does not use HOT and never has. Oh. If we're saying that isn't enough and we actually depend on the occasional user inspecting the catalog then we are already hosed. Probably not. I have heard of cases of pg_shdepend getting bloated though, so it'd be nice if it happened. -- Á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] Not HOT enough
Simon Riggs si...@2ndquadrant.com writes: On Wed, Nov 23, 2011 at 3:20 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Maybe not pg_database or pg_tablespace and such, but I'm not so sure about pg_shdepend. (Do we record pg_shdepend entries for temp tables?) Normal catalog access does not use HOT and never has. You are mistaken. 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] Not HOT enough
On Wed, Nov 23, 2011 at 9:44 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Nov 23, 2011 at 3:20 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of mié nov 23 12:15:55 -0300 2011: And it effects shared catalogs only, which are all low traffic anyway. I think low traffic is the key point. I understand that you're not changing the VACUUM behavior, but you are making heap_page_prune_opt() not do anything when a shared catalog is involved. That would be unacceptable if we expected shared catalogs to be updated frequently, either now or in the future, but I guess we don't expect that. Maybe not pg_database or pg_tablespace and such, but I'm not so sure about pg_shdepend. (Do we record pg_shdepend entries for temp tables?) Normal catalog access does not use HOT and never has. I don't understand that. We started with the simplified assumption that HOT can skip catalog tables, but later that was one of the pre-conditions Tom spelled out to accept HOT patch because his view was if this does not work for system tables, it probably does not work at all. Thanks, Pavan -- Pavan Deolasee 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] Not HOT enough
On Wed, Nov 23, 2011 at 5:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Wed, Nov 23, 2011 at 3:20 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Maybe not pg_database or pg_tablespace and such, but I'm not so sure about pg_shdepend. (Do we record pg_shdepend entries for temp tables?) Normal catalog access does not use HOT and never has. You are mistaken. Normal catalog access against shared catalogs via heap_scan does not use HOT cleanup, because it uses SnapshotNow. Page cleanup when reading a page only happens when scan-rs_pageatatime is set. scan-rs_pageatatime = IsMVCCSnapshot(snapshot); Index access does use HOT cleanup, which is probably normal. However, since we're talking about these tables only postgres=# select relname, pg_relation_size(oid) from pg_class where relisshared and relkind = 'r'; relname | pg_relation_size +-- pg_authid | 8192 pg_database| 8192 pg_tablespace | 8192 pg_pltemplate | 8192 pg_auth_members|0 pg_shdepend| 8192 pg_shdescription | 8192 pg_db_role_setting |0 (8 rows) then I think it's fair to say that they are seldom updated/deleted and so the effect of HOT cleanup is not important for those tables. The real question is do we favour HOT cleanup on those small 8 tables, or do we favour HOT cleanup of every other table? There are clearly pros and cons but the balance must surely be in favour of better cleaning of user tables since they are accessed millions of times more frequently than shared catalog tables. If we are concerned about those 8 tables then we can always set autovacuum more intensively. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Not HOT enough
Simon Riggs si...@2ndquadrant.com writes: On Wed, Nov 23, 2011 at 5:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: Normal catalog access does not use HOT and never has. You are mistaken. Normal catalog access against shared catalogs via heap_scan does not use HOT cleanup, because it uses SnapshotNow. Not sure what you are basing these statements on. Normal catalog access typically goes through indexam.c, which AFAICS will call heap_page_prune_opt on every heap page it visits, quite independently of what snapshot is used. There are no cases I know of where the system prefers heapscans on catalogs, except possibly pg_am which is known to be small. However, since we're talking about these tables only ... then I think it's fair to say that they are seldom updated/deleted and so the effect of HOT cleanup is not important for those tables. I agree with Alvaro that pg_shdepend is probably a bit too volatile to make such an assumption safe. The real question is do we favour HOT cleanup on those small 8 tables, or do we favour HOT cleanup of every other table? No, the real question is why not think a little harder and see if we can come up with a solution that doesn't involve making some cases worse to make others 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
Re: [HACKERS] Not HOT enough
Simon Riggs si...@2ndquadrant.com writes: On Tue, Nov 22, 2011 at 11:40 PM, Simon Riggs si...@2ndquadrant.com wrote: I think this is unsafe for shared catalogs. I think so too. Thats why it uses IsMVCCSnapshot() to confirm when it is safe to do so. Ah, you mean access to shared catalogs using MVCC snapshots. [ having now read the patch a bit more carefully ] I think the fundamental problem with this is that it's conflating what to do in shared catalogs with what to do when an MVCC snapshot is being used. HOT cleanup activity really ought not have anything at all to do with what snapshot is being used to scan the page. I'm also extremely uncomfortable with the fact that your proposed coding changes not only the RecentGlobalXmin output of GetSnapshotData, but the actual snapshot output --- you have not even made an argument why that is safe, and I doubt that it is. What I think might make more sense is to keep two variables, RecentGlobalXmin with its current meaning and RecentDatabaseWideXmin which considers only xmins of transactions in the current database. Then HOT cleanup could select the appropriate cutoff depending on whether it's working on a shared or non-shared relation. 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] plpython SPI cursors
On 20/11/11 19:14, Steve Singer wrote:
On 11-10-15 07:28 PM, Jan Urbański wrote:
Hi,
attached is a patch implementing the usage of SPI cursors in PL/Python.
Currently when trying to process a large table in PL/Python you have
slurp it all into memory (that's what plpy.execute does).
J
I found a few bugs (see my testing section below) that will need fixing
+ a few questions about the code
Responding now to all questions and attaching a revised patch based on
your comments.
Do we like the name plpy.cursor or would we rather call it something like
plpy.execute_cursor(...) or plpy.cursor_open(...) or
plpy.create_cursor(...)
Since we will be mostly stuck with the API once we release 9.2 this is
worth
some opinions on. I like cursor() but if anyone disagrees now is the time.
We use plpy.subtransaction() to create Subxact objects, so I though
plpy.cursor() would be most appropriate.
This patch does not provide a wrapper around SPI_cursor_move. The patch
is useful without that and I don't see anything that preculdes someone else
adding that later if they see a need.
My idea is to add keyword arguments to plpy.cursor() that will allow you
to decide whether you want a scrollable cursor and after that provide a
move() method.
The patch includes documentation updates that describes the new feature.
The Database Access page doesn't provide a API style list of database
access
functions like the plperl
http://www.postgresql.org/docs/9.1/interactive/plperl-builtins.html page
does. I think the organization of the perl page is
clearer than the python one and we should think about a doing some
documentaiton refactoring. That should be done as a seperate patch and
shouldn't be a barrier to committing this one.
Yeah, the PL/Python docs are a bit chaotic right now. I haven't yet
summoned force to overhaul them.
in PLy_cursor_plan line 4080
+ PG_TRY();
+ {
+ Portal portal;
+ char *volatile nulls;
+ volatile int j;
I am probably not seeing a code path or misunderstanding something
about the setjmp/longjump usages but I don't see why nulls and j need to be
volatile here.
It looked like you could drop volatile there (and in
PLy_spi_execute_plan, where this is copied from (did I mention there's
quite some code duplication in PL/Python?)) but digging in git I found
this commit:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2789b7278c11785750dd9d2837856510ffc67000
that added the original volatile qualification, so I guess there's a reason.
line 444
PLy_cursor(PyObject *self, PyObject *args)
+ {
+ char *query;
+ PyObject *plan;
+ PyObject *planargs = NULL;
+
+ if (PyArg_ParseTuple(args, s, query))
+ return PLy_cursor_query(query);
+
Should query be freed with PyMem_free()
No, PyArg_ParseTuple returns a string on the stack, I check that
repeatedly creating a cursor with a plan argument does not leak memory
and that adding PyMem_Free there promptly leads to a segfault.
I tested both python 2.6 and 3 on a Linux system
[test cases demonstrating bugs]
Turns out it's a really bad idea to store pointers to Portal structures,
because they get invalidated by the subtransaction abort hooks.
I switched to storing the cursor name and looking it up in the
appropriate hash table every time it's used. The examples you sent
(which I included as regression tests) now cause a ValueError to be
raised with a message stating that the cursor has been created in an
aborted subtransaction.
Not sure about the wording of the error message, though.
Thanks again for the review!
Cheers,
Jan
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index eda2bbf..d08c3d1 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -892,6 +892,15 @@ $$ LANGUAGE plpythonu;
/para
para
+Note that calling literalplpy.execute/literal will cause the entire
+result set to be read into memory. Only use that function when you are sure
+that the result set will be relatively small. If you don't want to risk
+excessive memory usage when fetching large results,
+use literalplpy.cursor/literal rather
+than literalplpy.execute/literal.
+ /para
+
+ para
For example:
programlisting
rv = plpy.execute(SELECT * FROM my_table, 5)
@@ -958,6 +967,77 @@ $$ LANGUAGE plpythonu;
/sect2
+ sect2
+titleAccessing data with cursors/title
+
+ para
+The literalplpy.cursor/literal function accepts the same arguments
+as literalplpy.execute/literal (except for literallimit/literal)
+and returns a cursor object, which allows you to process large result sets
+in smaller chunks. As with literalplpy.execute/literal, either a query
+string or a plan object along with a list of arguments can be used. The
+cursor object provides a literalfetch/literal method that accepts an
+integer paramter and returns a result object. Each time you
+call literalfetch/literal, the returned object will contain the next
+
Re: [HACKERS] Inlining comparators as a performance optimisation
On Tue, Nov 22, 2011 at 8:09 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: I wonder, is it worth qualifying that the Sort Method was a quicksort (fast path) sort within explain analyze output? This is a rather large improvement, so It might actually be something that people look out for, as it might be tricky to remember the exact circumstances under which the optimisation kicks in by the time we're done here. Well, right now the decision as to which mechanism should be used here gets made in tuplesort_performsort(), which has no good way of communicating with EXPLAIN anyway. Actually, I think that's a modularity violation; using the address of comparetup_heap as a flag value seems quite ugly. How about moving that logic up to tuplesort_begin_heap() and having it set some state inside the Tuplesort, maybe based on a flag in the opclass (or would it have to attach to the individual operator)? At least on my machine, your latest patch reliably crashes the regression tests in multiple places. The following test case also crashes them for me (perhaps for the same reason the regression tests crash): create table i4 (a int, b int); insert into i4 values (4, 1), (2, 1), (0, 1), (null, 1), (-2, 1), (-7, 1), (4, 2), (4, 3), (4, 4); select * from i4 order by 1, 2; TRAP: FailedAssertion(!(state-nKeys == 1), File: tuplesort.c, Line: 1261); The formatting of src/include/utils/template_qsort_arg.h is hard to read. At ts=8, the backslashes line up, but the code doesn't fit in 80 columns. If you set ts=4, then it fits in 80 columns, but the backslashes don't line up any more, and the variable declarations don't either. I believe ts=4 is project standard. I still think it would be a good idea to provide a mechanism to override heap_comparetup() with a type-specific function. I don't think that would take much extra code, and then any data type could get at least that much benefit out of this. It seems like it could be a good idea to do some per-assembler-instruction profiling of this code, and perhaps also of the original code. I'm curious where the time is being spent. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Not HOT enough
On Wed, Nov 23, 2011 at 6:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: The real question is do we favour HOT cleanup on those small 8 tables, or do we favour HOT cleanup of every other table? No, the real question is why not think a little harder and see if we can come up with a solution that doesn't involve making some cases worse to make others better. Slightly modified patch attached. When we access a shared relation and the page is prunable we re-derive the cutoff value using GetOldestXmin. That code path is rarely taken. In particular, pg_shdepend is only accessed during object creation/alter/drop, so this isn't a path we can't spare a small amount little extra on, just like the trade-off we've taken to make locking faster for DML while making DDL a little slower. If this is still unacceptable, then I'll have to look at reducing impact on pg_shdepend from temp tables - which is still a plan. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services fix_getsnapshotdata.v3.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] Not HOT enough
On Wed, Nov 23, 2011 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: What I think might make more sense is to keep two variables, RecentGlobalXmin with its current meaning and RecentDatabaseWideXmin which considers only xmins of transactions in the current database. Then HOT cleanup could select the appropriate cutoff depending on whether it's working on a shared or non-shared relation. Unfortunately, that would have the effect of lengthening the time for which ProcArrayLock is held, and as benchmark results from Pavan's patch in that area show, that makes a very big difference to total throughput on write-heavy workloads. On a related note, Simon's proposed change here would also complicate things for that patch, because databaseId would have to become part of PGXACT rather than PGPROC, and that would make the PGXACT act array larger and thus slower to scan. I have deep respect for the perils of not doing HOT cleanup quickly enough, but ProcArrayLock contention is nothing to sneeze at either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Not HOT enough
On Wed, Nov 23, 2011 at 7:57 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Nov 23, 2011 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: What I think might make more sense is to keep two variables, RecentGlobalXmin with its current meaning and RecentDatabaseWideXmin which considers only xmins of transactions in the current database. Then HOT cleanup could select the appropriate cutoff depending on whether it's working on a shared or non-shared relation. Unfortunately, that would have the effect of lengthening the time for which ProcArrayLock is held, and as benchmark results from Pavan's patch in that area show, that makes a very big difference to total throughput on write-heavy workloads. Agreed. The performance effect of doing that would be noticeable, and I quickly ruled out that solution without even mentioning it at version one. On a related note, Simon's proposed change here would also complicate things for that patch, because databaseId would have to become part of PGXACT rather than PGPROC, and that would make the PGXACT act array larger That is correct. and thus slower to scan. I have deep respect for the perils of not doing HOT cleanup quickly enough, but ProcArrayLock contention is nothing to sneeze at either. If you measure the difference we'll be able to see what difference adding 4 bytes onto every pgtran makes. I think it will be small. OTOH, the effect of database bloat is well documented and has a seriously negative effect on performance that easily outweighs the slight loss. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Not HOT enough
Robert Haas robertmh...@gmail.com writes: On Wed, Nov 23, 2011 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: What I think might make more sense is to keep two variables, RecentGlobalXmin with its current meaning and RecentDatabaseWideXmin which considers only xmins of transactions in the current database. Then HOT cleanup could select the appropriate cutoff depending on whether it's working on a shared or non-shared relation. Unfortunately, that would have the effect of lengthening the time for which ProcArrayLock is held, and as benchmark results from Pavan's patch in that area show, that makes a very big difference to total throughput on write-heavy workloads. [ shrug... ] Simon's patch already adds nearly as many cycles in the hot spot as would be required to do what I suggest. 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] Not HOT enough
On Wed, Nov 23, 2011 at 8:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Nov 23, 2011 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: What I think might make more sense is to keep two variables, RecentGlobalXmin with its current meaning and RecentDatabaseWideXmin which considers only xmins of transactions in the current database. Then HOT cleanup could select the appropriate cutoff depending on whether it's working on a shared or non-shared relation. Unfortunately, that would have the effect of lengthening the time for which ProcArrayLock is held, and as benchmark results from Pavan's patch in that area show, that makes a very big difference to total throughput on write-heavy workloads. [ shrug... ] Simon's patch already adds nearly as many cycles in the hot spot as would be required to do what I suggest. Well, its deeper than that. My patch actually skips xids that aren't in the user's database. That avoids other work in GetSnapshotData(), so will in many cases make it faster. The snapshots returned will be smaller, which also means more speed. As you point out upthread, that generates an MVCC snapshot that is not safe for user queries against shared catalog tables. Standard catalog access is safe, but user access isn't. The way to solve that problem is to make all scans against shared catalog tables use SnapshotNow, whatever the snapshot says. Which would be more useful since you'll see exactly what the DBMS sees. Given the infrequency of change to those tables and the infrequency of user access to those tables it seems like a very good thing. If we do as you suggest, snapshots would contain all xids from all databases, so no effort would be skipped, but we would pay the cost of deriving two values just in case we ever decide to read a shared catalog table, which is blue moon frequency, so a net loss. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Not HOT enough
On Wed, Nov 23, 2011 at 3:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Nov 23, 2011 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: What I think might make more sense is to keep two variables, RecentGlobalXmin with its current meaning and RecentDatabaseWideXmin which considers only xmins of transactions in the current database. Then HOT cleanup could select the appropriate cutoff depending on whether it's working on a shared or non-shared relation. Unfortunately, that would have the effect of lengthening the time for which ProcArrayLock is held, and as benchmark results from Pavan's patch in that area show, that makes a very big difference to total throughput on write-heavy workloads. [ shrug... ] Simon's patch already adds nearly as many cycles in the hot spot as would be required to do what I suggest. Well, that's a point in favor of your idea as compared with Simon's, I suppose, but it's not making me feel entirely sanguine about either approach. I've wondered a few times whether we could get rid of the RecentGlobalXmin computation from GetSnapshotData() altogether. We think that it's cheap to do it there because we already hold ProcArrayLock in exclusive mode, but Pavan's work suggests that it really isn't that cheap. Instead of updating RecentGlobalXmin every time we take a snapshot (which is likely to be a waste in many cases, since even in a busy system many snapshots are very short lived and therefore unlikely to trigger a HOT cleanup) maybe we should only update it on demand - e.g. if heap_page_prune_opt sees a page-prune-hint XID that is older than TransactionXmin and newer than the last-computed value of RecentGlobalXmin, there's hope that a recomputation might yield a new RecentGlobalXmin value new enough to allow a HOT cleanup, so if we haven't recomputed it lately, then we should. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] range_adjacent and discrete ranges
I wrote: Attached is a draft patch for this. It passes regression tests but I've not tried to exercise it with a canonical function that actually does something different. I hacked up int4range_canonical to produce []-style ranges, and confirmed that this version of range_adjacent seems to work with them. It's going to be a bit slower than Jeff's original, because it does not only range_cmp_bound_values but also a make_range cycle (in most cases). So I guess the question is how much we care about supporting canonical functions with non-default policies. Thoughts? I did a little bit of performance testing on an x86_64 machine (Fedora 14), and found that the time to execute a clause like WHERE int4range(1,2) -|- int4range(x, 1000) (x being an integer Var) grows from 0.37 us to 0.56 us if we adopt the patched version of range_adjacent. With float8 ranges it grows from 0.35 us to 0.54 us. So these are noticeable penalties but they don't seem like show-stoppers. Since the alternative is to document that the apparent freedom to choose a canonicalization policy is illusory, I'm inclined to think we should change 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] Not HOT enough
On Wed, Nov 23, 2011 at 8:45 PM, Robert Haas robertmh...@gmail.com wrote: I've wondered a few times whether we could get rid of the RecentGlobalXmin computation from GetSnapshotData() altogether. You have to calculate an xmin, so its unavoidable. My patch actually improves the speed of snapshots, rather than slowing them as Tom's would. We think that it's cheap to do it there because we already hold ProcArrayLock in exclusive mode, but Pavan's work suggests that it really isn't that cheap. Instead of updating RecentGlobalXmin every time we take a snapshot (which is likely to be a waste in many cases, since even in a busy system many snapshots are very short lived and therefore unlikely to trigger a HOT cleanup) maybe we should only update it on demand - e.g. if heap_page_prune_opt sees a page-prune-hint XID that is older than TransactionXmin and newer than the last-computed value of RecentGlobalXmin, there's hope that a recomputation might yield a new RecentGlobalXmin value new enough to allow a HOT cleanup, so if we haven't recomputed it lately, then we should. When we prune a page while running an UPDATE if we see that the page is left with less freespace than average row length for that relation AND page sees a RecentlyDead xid we could then re-derive a later db-local cutoff value and re-prune the page. That increases page lock time, but pages are locked for longer if we do non-HOT updates anyway, so it would still be a win. What % of non-HOT updates do you see in your recent benchmarks? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] range_adjacent and discrete ranges
I wrote:
I did a little bit of performance testing on an x86_64 machine (Fedora 14),
and found that the time to execute a clause like
WHERE int4range(1,2) -|- int4range(x, 1000)
(x being an integer Var) grows from 0.37 us to 0.56 us if we adopt the
patched version of range_adjacent. With float8 ranges it grows from
0.35 us to 0.54 us. So these are noticeable penalties but they don't
seem like show-stoppers. Since the alternative is to document that
the apparent freedom to choose a canonicalization policy is illusory,
I'm inclined to think we should change it.
It occurred to me that we can easily buy back the extra time for range
types that don't have a canonical function (ie, continuous ranges).
If there's no such function, it's impossible for B..C to normalize to
empty when B C, so we can skip the extra logic. The attached version
is no slower than the original code for continuous ranges, and doesn't
seem measurably different from my previous patch for discrete ranges.
regards, tom lane
*** /home/postgres/pgsql/src/backend/utils/adt/rangetypes.c Tue Nov 22 23:18:51 2011
--- new/rangetypes.c Wed Nov 23 16:29:20 2011
***
*** 699,704
--- 699,706
upper2;
bool empty1,
empty2;
+ RangeType *r3;
+ int cmp;
/* Different types should be prevented by ANYRANGE matching rules */
if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2))
***
*** 714,736
PG_RETURN_BOOL(false);
/*
! * For two ranges to be adjacent, the lower boundary of one range has to
! * match the upper boundary of the other. However, the inclusivity of
! * those two boundaries must also be different.
*
! * The semantics for range_cmp_bounds aren't quite what we need here, so
! * we do the comparison more directly.
*/
! if (lower1.inclusive != upper2.inclusive)
{
! if (range_cmp_bound_values(typcache, lower1, upper2) == 0)
! PG_RETURN_BOOL(true);
}
! if (upper1.inclusive != lower2.inclusive)
{
! if (range_cmp_bound_values(typcache, upper1, lower2) == 0)
! PG_RETURN_BOOL(true);
}
PG_RETURN_BOOL(false);
--- 716,774
PG_RETURN_BOOL(false);
/*
! * Given two ranges A..B and C..D, where B C, the ranges are adjacent
! * if and only if the range B..C is empty, where inclusivity of these two
! * bounds is inverted compared to the original bounds. For discrete
! * ranges, we have to rely on the canonicalization function to normalize
! * B..C to empty if it contains no elements of the subtype. (If there is
! * no canonicalization function, it's impossible for such a range to
! * normalize to empty, so we needn't bother to try.)
! *
! * If B == C, the ranges are adjacent only if these bounds have different
! * inclusive flags (i.e., exactly one of the ranges includes the common
! * boundary point).
*
! * And if B C then the ranges cannot be adjacent in this order, but we
! * must consider the other order (i.e., check D = A).
*/
! cmp = range_cmp_bound_values(typcache, upper1, lower2);
! if (cmp 0)
! {
! /* in a continuous subtype, there are assumed to be points between */
! if (!OidIsValid(typcache-rng_canonical_finfo.fn_oid))
! PG_RETURN_BOOL(false);
! /* flip the inclusion flags */
! upper1.inclusive = !upper1.inclusive;
! lower2.inclusive = !lower2.inclusive;
! /* change upper/lower labels to avoid Assert failures */
! upper1.lower = true;
! lower2.lower = false;
! r3 = make_range(typcache, upper1, lower2, false);
! PG_RETURN_BOOL(RangeIsEmpty(r3));
! }
! if (cmp == 0)
{
! PG_RETURN_BOOL(upper1.inclusive != lower2.inclusive);
}
! cmp = range_cmp_bound_values(typcache, upper2, lower1);
! if (cmp 0)
! {
! /* in a continuous subtype, there are assumed to be points between */
! if (!OidIsValid(typcache-rng_canonical_finfo.fn_oid))
! PG_RETURN_BOOL(false);
! /* flip the inclusion flags */
! upper2.inclusive = !upper2.inclusive;
! lower1.inclusive = !lower1.inclusive;
! /* change upper/lower labels to avoid Assert failures */
! upper2.lower = true;
! lower1.lower = false;
! r3 = make_range(typcache, upper2, lower1, false);
! PG_RETURN_BOOL(RangeIsEmpty(r3));
! }
! if (cmp == 0)
{
! PG_RETURN_BOOL(upper2.inclusive != lower1.inclusive);
}
PG_RETURN_BOOL(false);
--
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] Not HOT enough
Simon Riggs si...@2ndquadrant.com writes: My patch actually improves the speed of snapshots, rather than slowing them as Tom's would. It can be arbitrarily fast if it doesn't have to get the right answer. Unfortunately, you're not producing the right answer. You can not exclude XIDs in other databases from the snapshot, at least not unless you know that the snapshot will not be used for examining any shared catalogs ... and GetSnapshotData certainly cannot know that. I think that the idea of computing a different cutoff on the probably-rare occasions where we need to prune a shared catalog page has some merit, but the change you are currently proposing to GetSnapshotData flat out does not work. 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] [Review] Include detailed information about a row failing a CHECK constraint into the error message
On Mon, Nov 21, 2011 at 8:59 AM, Jan Kundrát j...@flaska.net wrote: What is the suggested way to go form here? Shall I update the unit tests? Yes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Snapshot build updates
The snapshots on the ftpsite have been down for a number of days, since hub.org upgraded the machine it used to be on and git stopped working there. Since we were planning to move it anyway, we didn't bother doing anything about it at the time. The snapshots are now auto-generated by buildfarm animal guaibasaurus, and automatically pushed to the ftpsite. So they're back alive, and you can check the generation schedule on that one. It will take some time after each build before they actually show up on the ftp site due to synchronization issues of course, but thwy will go there fairly soon after they're built. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Not HOT enough
On Wed, Nov 23, 2011 at 9:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: My patch actually improves the speed of snapshots, rather than slowing them as Tom's would. It can be arbitrarily fast if it doesn't have to get the right answer. (LOL) - laughing with you Unfortunately, you're not producing the right answer. You can not exclude XIDs in other databases from the snapshot, at least not unless you know that the snapshot will not be used for examining any shared catalogs ... and GetSnapshotData certainly cannot know that. I think that the idea of computing a different cutoff on the probably-rare occasions where we need to prune a shared catalog page has some merit, but the change you are currently proposing to GetSnapshotData flat out does not work. All true, but I already said that myself in a direct reply to you 2 hours ago. And I proposed a solution, which was to use SnapshotNow as an override for user queries against shared catalog tables. Computing two cutoffs is overkill for the rare event of pruning a shared catalog page. I did think of that already and its not a good solution. I'm tempted by the idea of getting rid of multiple databases altogether. The hassle of supporting that feature far outweighs the fairly low benefit. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Not HOT enough
On Wed, Nov 23, 2011 at 5:43 PM, Simon Riggs si...@2ndquadrant.com wrote: Computing two cutoffs is overkill for the rare event of pruning a shared catalog page. I did think of that already and its not a good solution. I'm tempted by the idea of getting rid of multiple databases altogether. The hassle of supporting that feature far outweighs the fairly low benefit. That seems a rather sudden U-turn. The point of your proposal is to improve life for people using multiple databases in a single cluster. If that's not an important use case, why bother with any of this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Not HOT enough
On Wed, Nov 23, 2011 at 10:47 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Nov 23, 2011 at 5:43 PM, Simon Riggs si...@2ndquadrant.com wrote: Computing two cutoffs is overkill for the rare event of pruning a shared catalog page. I did think of that already and its not a good solution. I'm tempted by the idea of getting rid of multiple databases altogether. The hassle of supporting that feature far outweighs the fairly low benefit. That seems a rather sudden U-turn. The point of your proposal is to improve life for people using multiple databases in a single cluster. If that's not an important use case, why bother with any of this? Where's the U-turn? I've not argued at any point that running multiple databases was a great idea. Offering multiple databases causes the problems I noted and have been trying to solve. If we didn't have databases we could probably chuck out tons of complexity and code that no longer need to exist now we have namespaces. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] FlexLocks
Kevin Grittner wrote: Robert Haas wrote: Updated patches attached. I have to admit I don't have my head around the extraWaits issue, so I can't personally vouch for that code, although I have no reason to doubt it, either. Everything else was something that I at least *think* I understand, and it looked good to me. I'm not changing the status until I get through the other patch file, which should be tomorrow. Most of the procarraylock-v1.patch file was pretty straightforward, but I have a few concerns. Why is it OK to drop these lines from the else condition in ProcArrayEndTransaction()?: /* must be cleared with xid/xmin: */ proc-vacuumFlags = ~PROC_VACUUM_STATE_MASK; The extraWaits code still looks like black magic to me, so unless someone can point me in the right direction to really understand that, I can't address whether it's OK. The need to modify flexlock_internals.h and flexlock.c seems to me to indicate a lack of desirable modularity here. The lower level object type shouldn't need to know about each and every implementation of a higher level type which uses it, particularly not compiled in like that. It would be really nice if each of the higher level types registered with flexlock at runtime, so that the areas modified at the flexlock level in this patch file could be generic. Among other things, this could allow extensions to use specialized types, which seems possibly useful. Does that (or some other technique to address the concern) seem feasible? -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] [JDBC] Optimize postgres protocol for fixed size arrays
On 24 November 2011 05:36, Tom Lane t...@sss.pgh.pa.us wrote: Now it's possible we could do that without formally calling it a protocol version change, but I don't care at all for the idea of coming up with one-off hacks every time somebody decides that some feature is important enough that they have to have it Right Now instead of waiting for a sufficient accumulation of reasons to have a protocol flag day. I think but we made arrays a bit smaller! is a pretty lame response to have to give when somebody complains that Postgres 9.2 broke their client software. When we do it, I want to have a *long* list of good reasons. Can we get a mechanism for minor protocol changes in this future version? Something as simple as exchanging a list of protocol features during the initial handshake (then use only features that are present on both sides) would be enough. The difficulty of making any protocol changes at the moment is a big stumbling block. (You could probably retrofit that to the current protocol version) Oliver -- 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] [JDBC] Optimize postgres protocol for fixed size arrays
Oliver Jowett wrote: Can we get a mechanism for minor protocol changes in this future version? Something as simple as exchanging a list of protocol features during the initial handshake (then use only features that are present on both sides) would be enough. The difficulty of making any protocol changes at the moment is a big stumbling block. I've been thinking the same thing. Any new protocol should include a way for each side to publish a list of what it can accept from the other during initial handshaking. (You could probably retrofit that to the current protocol version) Perhaps. It would be great if both sides could recognize the case where the feature negotiation was absent and use a default feature list for the protocol available on the other end. -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] PL/Python SQL error code pass-through
Hi all, Here's a little SQL snippet that exposes an apparent regression in the 9.1.x PL/Python behavior: ---clip--- # cat foo.sql \set VERBOSITY 'verbose' CREATE table bar (a INTEGER CONSTRAINT hello CHECK (a 1)); CREATE OR REPLACE FUNCTION foo () RETURNS integer AS $$ plpy.execute(INSERT INTO bar (a) VALUES (2)) plpy.execute(INSERT INTO bar (a) VALUES (1)) return 123 $$ LANGUAGE plpythonu; SELECT * FROM foo(); ---clip--- PostgreSQL 9.0 behavior: ---clip--- # psql foo.sql CREATE TABLE CREATE FUNCTION WARNING: 01000: PL/Python: plpy.SPIError: unrecognized error in PLy_spi_execute_query CONTEXT: PL/Python function foo LOCATION: PLy_elog, plpython.c:3532 ERROR: 23514: new row for relation bar violates check constraint hello CONTEXT: SQL statement INSERT INTO bar (a) VALUES (1) PL/Python function foo LOCATION: ExecConstraints, execMain.c:1330 ---clip--- Note the proper 23514 error code. PostgreSQL 9.1.1 behavior: ---clip--- # psql foo.sql ERROR: 42P07: relation bar already exists LOCATION: heap_create_with_catalog, heap.c:1011 CREATE FUNCTION ERROR: XX000: spiexceptions.CheckViolation: new row for relation bar violates check constraint hello CONTEXT: Traceback (most recent call last): PL/Python function foo, line 3, in module plpy.execute(INSERT INTO bar (a) VALUES (1)) PL/Python function foo LOCATION: PLy_elog, plpython.c:4502 ---clip--- In fact, all SQL error that occur within PL/Python seem to be returned with the XX000 error code. This is a bit of a problem for client-side logic that detects e.g. constraint violations based on the SQL error code. A small patch that includes passing thru the SQL error code is attached. Test run with PostgreSQL 9.1.1 + patch: ---clip--- # psql foo.sql ERROR: 42P07: relation bar already exists LOCATION: heap_create_with_catalog, heap.c:1011 CREATE FUNCTION ERROR: 23514: spiexceptions.CheckViolation: new row for relation bar violates check constraint hello CONTEXT: Traceback (most recent call last): PL/Python function foo, line 4, in module plpy.execute(INSERT INTO bar (a) VALUES (1)) PL/Python function foo LOCATION: PLy_elog, plpython.c:4504 ---clip--- Cheers! - Mika 0001-PL-Python-SQL-error-code-pass-through.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
[HACKERS] logging in high performance systems.
We have a need for logging in systems where it isn't feasible to log to disk as it negatively impacts performance. I'd like to be able to creatively solve this problem without modifying the core, but today I cannot. So... here's my first whack at solving this with some flexibility. The first thing I did was add hook points where immediate statement logging happens pre_exec and those that present duration post_exec. These should, with optimization turned on, have only a few instructions of impact when no hooks are registered (we could hoist the branch outside the function call if that were identified as an issue). https://github.com/postwait/postgres/commit/62bb9dfa2d373618f10e46678612720a3a01599a The second thing I did was write a sample use of those hooks to implement a completely non-blocking fifo logger. (if it would block, it drops the log line). The concept is that we could run this without risk of negative performance impact due to slow log reading (choosing to drop logs in lieu of pausing). And a simple process could be written to consume from the fifo. We use this method in other systems to log many 10s of thousands of log lines per second with negligible impact on performance. https://github.com/postwait/postgres/commit/c8f5a346c7b2c3eba9f72ea49077dc72f03a2679 Thoughts? Feedback? As can be seen, the patch is pretty tiny. -- Theo Schlossnagle http://omniti.com/is/theo-schlossnagle -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Obstacles to user-defined range canonicalization functions
I got religion this evening about the potential usefulness of user-defined canonicalization functions --- the example that did it for me was thinking about a range type over timestamp that quantizes boundaries to hours, or half hours, or 15 minutes, or any scheduling unit that is standard in a particular environment. In that sort of situation you really want a discrete range type, which the standard tsrange type is not. So how hard is it to build a user-defined canonicalization function to support such an application? The logic doesn't seem terribly difficult ... but *you have to write the darn thing in C*. There are two reasons why: * The underlying range_serialize function is only exposed at the C level. If you try to write something in, say, plpgsql then you are going to end up going through range_constructorN or range_in to produce your result value, and those call the type's canonical function. Infinite recursion, here we come. * The only way to create a canonicalization function in advance of declaring the range type is to declare it against a shell type. But the PL languages all reject creating PL functions that take or return a shell type. Maybe we could relax that, but it's nervous-making, and anyway the first problem still remains. Now you could argue that for performance reasons everybody should write their canonicalization functions in C anyway, but I'm not sure I buy that --- at the very least, it'd be nice to write the functions in something higher-level while prototyping. I have no immediate proposal for how to fix this, but I think it's something we ought to think about. One possibility that just came to me is to decree that every discrete range type has to be based on an underlying continuous range type (with all the same properties except no canonicalization function), and then the discrete range's canonicalization function could be declared to take and return the underlying range type instead of the discrete type itself. Haven't worked through the details 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] Obstacles to user-defined range canonicalization functions
On Nov24, 2011, at 04:33 , Tom Lane wrote: One possibility that just came to me is to decree that every discrete range type has to be based on an underlying continuous range type (with all the same properties except no canonicalization function), and then the discrete range's canonicalization function could be declared to take and return the underlying range type instead of the discrete type itself. Haven't worked through the details though. We could also make the canonicalization function receive the boundaries and boundary types as separate arguments, and return them in the same way. In plpgsql the signature could be canonicalize(inout lower base_type, inout upper base_type, inout lower_inclusive boolean, inout upper_inclusive boolean) Not exactly pretty, but it avoids the need for a second continuous range type... best regards, Florian Pflug -- 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] Obstacles to user-defined range canonicalization functions
On Nov 23, 2011, at 10:33 PM, Tom Lane wrote: Now you could argue that for performance reasons everybody should write their canonicalization functions in C anyway, but I'm not sure I buy that --- at the very least, it'd be nice to write the functions in something higher-level while prototyping. I would apply this argument to every single part of the system that requires code that extends the database to be written in C, including: * I/O functions (for custom data types) * tsearch parsers * use of RECORD arguments And probably many others. There are a *lot* of problems I’d love to be able to solve with prototypes written in PLs other than C, and in small databases (there are a lot of them out there), they may remain the production solutions. So I buy the argument in the case of creating range canonicalization functions, too, of course! Best, 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] logging in high performance systems.
On 11/23/2011 09:28 PM, Theo Schlossnagle wrote: The second thing I did was write a sample use of those hooks to implement a completely non-blocking fifo logger. (if it would block, it drops the log line). The concept is that we could run this without risk of negative performance impact due to slow log reading (choosing to drop logs in lieu of pausing). And a simple process could be written to consume from the fifo. This was one of the topics at the last developer's meeting you might not have seen go by: http://wiki.postgresql.org/wiki/PgCon_2011_Developer_Meeting#Improving_Logging There was a reference to a pipe-based implementation from Magnus that I haven't gotten a chance to track down yet. I think this area is going to start hitting a lot more people in the upcoming couple of years, since I'm seeing it increasingly at two customers I consider canary in a cole mine sentinels for performance issues. I'm now roughly considering three types of users here: -Don't care about the overhead of logging, but are sick of parsing text files. Would prefer the data be in a table instead. -Concerned enough about overhead that statement-level logging is impractical to log or table, but can cope with logging for other things. -Logging rate can burst high enough that messages must start being dropped instead no matter where they go. Before making a big change, log file vs. table needs to be carefully explored to figure which of the two approaches has more reasonable behavior/performance trade-offs. I've been trying to attack this starting at the middle, with the pg_stat_statements rework Peter here did for the current CommitFest. If you've already worked out a way to simulate heavy logging as part of what you've done here, I'd be quite interested to hear how capable you feel it is for the class of problem you're seeing. I've always assumed that pushing the most common queries into shared memory and only showing them on demand, rather than logging them line at a time, could be a big win for some places. We're still a bit light on benchmarks proving that is the case so far though. My assumption has been that eventually a lossy logger was going to be necessary for busier sites, I just haven't been suffering from one enough to hack on it yet. If it's possible to work this out in enough detail to figure out where the hooks go, and to prove they work with at least one consumer of them, I'd consider that a really useful thing to try and squeeze into 9.2. The processing parts can always be further improved later based on production feedback, going along with my recent them of letting extensions that poke and probe existing hooks be one place to brew next version features at. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support 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] pg_upgrade relation OID mismatches
Bruce Momjian wrote: OK, that is a heap table. My only guess is that the heap is being created without binary_upgrade_next_heap_pg_class_oid being set. Looking at the code, I can't see how the heap could be created without this happening. Another idea is that pg_dumpall isn't output the proper value, but again, how is this data type different from the others. I have reproduced the failure and found it was code I added to pg_dump back in 9.0. The code didn't set the index oid for exclusion constraint indexes. Once these were added to the regression tests for range types recently, pg_upgrade threw an error. My assumption is that anyone trying to use an exclusion constraint with pg_upgrade will get the same type of error. Patch attached. Should it be backpatched to 9.0 and 9.1? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c new file mode 100644 index 644637c..6dc3d40 *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *** dumpConstraint(Archive *fout, Constraint *** 12926,12932 exit_nicely(); } ! if (binary_upgrade !coninfo-condef) binary_upgrade_set_pg_class_oids(q, indxinfo-dobj.catId.oid, true); appendPQExpBuffer(q, ALTER TABLE ONLY %s\n, --- 12926,12932 exit_nicely(); } ! if (binary_upgrade) binary_upgrade_set_pg_class_oids(q, indxinfo-dobj.catId.oid, true); appendPQExpBuffer(q, ALTER TABLE ONLY %s\n, -- 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] Notes on implementing URI syntax for libpq
Excerpts from Florian Weimer's message of Wed Nov 23 13:04:47 +0200 2011: * Alexander Shulgin: This, in my opinion, is very similar to what we would like to achieve with the URI syntax, so the above could also be specified using a URI parameter like this: psql -d postgresql://example.net:5433/mydb How would you specifiy a local port/UNIX domain socket? Would it be possible to add something like psql -d postgresql+ssh://fweimer@db5/var/run/postgresql/.s.PGSQL.5432 similar to what Subversion supports? (This might have security implications when used from untrusted PHP scripts.) While it is really tempting to provide support for all that fancy stuff (or at least support user:password@host part instead of the ugly ?user=password=) this will make psql URIs backward-incompatible with the JDBC syntax, which is exactly what we want to avoid. The primary reason people even considering adding the syntax, IMO is compatibility and thus, it has to be compatible in both directions. If we support something that's more than JDBC provides, we're just adding to the soup of incompatible URI syntaxes out there. -- Alex -- 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] Notes on implementing URI syntax for libpq
Excerpts from Florian Weimer's message of Wed Nov 23 13:04:47 +0200 2011: * Alexander Shulgin: This, in my opinion, is very similar to what we would like to achieve with the URI syntax, so the above could also be specified using a URI parameter like this: psql -d postgresql://example.net:5433/mydb How would you specifiy a local port/UNIX domain socket? Missed that in my previous reply. If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. -- 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] Notes on implementing URI syntax for libpq
Hey Alexander, 2011/11/24 Alexander Shulgin a...@commandprompt.com Excerpts from Florian Weimer's message of Wed Nov 23 13:04:47 +0200 2011: * Alexander Shulgin: This, in my opinion, is very similar to what we would like to achieve with the URI syntax, so the above could also be specified using a URI parameter like this: psql -d postgresql://example.net:5433/mydb How would you specifiy a local port/UNIX domain socket? Missed that in my previous reply. If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. But what if the user wants to connect exactly via socket or TCP/IP ? And what if the user needs to specify a socket file name extension? -- // Dmitriy.
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Dmitriy Igrishin's message of Thu Nov 24 09:19:02 +0200 2011: If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. But what if the user wants to connect exactly via socket or TCP/IP ? And what if the user needs to specify a socket file name extension? How do you achieve that with the current psql set of command line options (and, possibly environment variables?) I would think the same method will work with URI, as with the proposed approach the URI is just decomposed into host, port and dbname parts and the rest of the code works like if you've had specified -h example.net -p 5433 -d mydb instead of the URI parameter. -- Alex -- 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] Notes on implementing URI syntax for libpq
On Thu, Nov 24, 2011 at 08:59:56AM +0200, Alexander Shulgin wrote: How would you specifiy a local port/UNIX domain socket? Missed that in my previous reply. If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. Uh, no it doesn't. -h localhost uses TCP/IP (try it). This is one piece of mysql magic we don't copy. If you want to use the socket you need to specify -h /tmp or wherever you keep it. Leaving out the -h parameter also uses UNIX domain sockets. Which does raise the valid question of how to represent that in URI syntax. SQLAlchemy (for example) doesn't try with it's URL syntax, to connect to a non-default UNIX socket, you need to create the URL object directly. How about the service option, that's a nice way of handling non-default socket options. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] Notes on implementing URI syntax for libpq
2011/11/24 Alexander Shulgin a...@commandprompt.com Excerpts from Dmitriy Igrishin's message of Thu Nov 24 09:19:02 +0200 2011: If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. But what if the user wants to connect exactly via socket or TCP/IP ? And what if the user needs to specify a socket file name extension? How do you achieve that with the current psql set of command line options (and, possibly environment variables?) For psql(1) see -h option and -p option http://www.postgresql.org/docs/9.1/static/app-psql.html For the libpq see host option and port option of PQconnectdbparams() http://www.postgresql.org/docs/9.1/static/libpq-connect.html In both cases: If the value of host begins with a slash, it is used as the directory for the Unix-domain socket. Port specifies the TCP port or the local Unix-domain socket file extension. I would think the same method will work with URI, as with the proposed approach the URI is just decomposed into host, port and dbname parts and the rest of the code works like if you've had specified -h example.net-p 5433 -d mydb instead of the URI parameter. Thats great, but see above. -- // Dmitriy.
