Re: [HACKERS] Add socket dir to pg_config..?
2011/10/30 Martijn van Oosterhout klep...@svana.org: On Sat, Oct 29, 2011 at 08:28:57PM +, Mr. Aaron W. Swenson wrote: If /tmp is the only decent place where to put the socket file on Unix when security and other concerns are considered, then sure, making distro life difficult is a good thing to do. But then let's take it to the FHS that debian and ubuntu are implementing, AFAIUI. In Gentoo, we change the socket directory to /var/run/postgresql via pg_config_manual.h. However, I'm not too terribly interested in pg_config outputting the directory location. Frankly, I'm not seeing the difference between the socket directory and the listen_addresses option. When connecting you can specify the socket directory to use via the host option. It might even be more logical to be able to specify multiple directories. Given we support multiple listen sockets I can't imagine it would require much code. (And yes, just today I ran into the issue of hardcoded paths. If the directory it points to is not world writable then you've limited the users who can run the postgres server. Which is an unnecessary restriction imho). For Debian, the reason is : Description: Put server Unix sockets into /var/run/postgresql/ by default Forwarded: No, Debian specific configuration with postgresql-common Using /tmp for sockets allows everyone to spoof a PostgreSQL server. Thus use /var/run/postgresql/ for system clusters which run as 'postgres' (user clusters will still use /tmp). Since system cluster are by far the common case, set it as default. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- 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] unite recovery.conf and postgresql.conf
On Sat, Oct 29, 2011 at 7:54 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Sep 9, 2011 at 10:56 AM, Fujii Masao masao.fu...@gmail.com wrote: In previous discussion, we've reached the consensus that we should unite recovery.conf and postgresql.conf. The attached patch does that. The patch is WIP, I'll have to update the document, but if you notice something, please feel free to comment. My short summary of the thread is Thanks! In 9.1 we added pg_ctl promote as a better way of indicating failover/switchover. When we did that we kept the trigger_file parameter added in 9.0, which shows it is possible to add a new API without breaking backwards compatibility. We should add a pg_ctl standby command as a better way of indicating starting up (also described as triggering) standby mode. We keep standby_mode parameter. There is no difference here between file based and stream based replication: you can have file, stream or both file and stream (as intended). In this mode the recovery target parameters are *ignored* even if specified (explained below). http://developer.postgresql.org/pgdocs/postgres/recovery-target-settings.html Agreed to add pg_ctl standby. I think that this can be committed separately from the change of recovery.conf. In 9.2 the presence of recovery.conf can and therefore should continue to act as it does in 9.1. This means that recovery.conf is renamed to recovery.done at the end of recovery. IOW, all settings in recovery.conf are reset when recovery ends. Then if you run pg_ctl reload after recovery, you'll get something like the following error message and the reload will always fail; LOG: parameter standby_mode cannot be changed without restarting the server To resolve this issue, I think that we need to introduce new GUC flag indicating parameters are used only during recovery, and need to define recovery parameters with that flag. Whenever pg_ctl reload is executed, all the processes check whether recovery is in progress, and ignore silently the parameters with that flag if not. I'm not sure how easy we can implement this. In addition to introducing that flag, we might need to change some processes which cannot access to the shared memory so that they can. Otherwise, they cannot know whether recovery is in progress. Or we might need to change them so that they always ignore recovery parameters. Another simple but somewhat restricted approach is to read and set all parameters specified in recovery.conf by using PGC_S_OVERRIDE. If we do this, those parameters cannot be changed after startup even if recovery.conf is renamed. But the problem is that a user also cannot change their settings by reloading the configuration files. This is obviously a restriction. But it doesn't break any backward compatibility, I believe. No? If a user prefers new functionality (i.e., reload recovery parameters) rather than the backward compatibility, he/she can specify parameters in postgresql.conf. Thought? 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] Clarification on item on Todo List
I am interested in this item on the list and its exact meaning: (Under Data Types) - Fix data types where equality comparison is not intuitive, e.g. box Can you elaborate on this item, I particularly fail to see what is non-intuitive about box comparisons (and I suppose other types where equalities are non-intuitive). -Tristan
Re: [HACKERS] unite recovery.conf and postgresql.conf
On Mon, Oct 31, 2011 at 7:38 AM, Fujii Masao masao.fu...@gmail.com wrote: In 9.2 the presence of recovery.conf can and therefore should continue to act as it does in 9.1. This means that recovery.conf is renamed to recovery.done at the end of recovery. IOW, all settings in recovery.conf are reset when recovery ends. Then if you run pg_ctl reload after recovery, you'll get something like the following error message and the reload will always fail; LOG: parameter standby_mode cannot be changed without restarting the server To resolve this issue, This issue exists whether or not we have recovery.conf etc., so yes, we must solve the problem. I think that we need to introduce new GUC flag indicating parameters are used only during recovery, and need to define recovery parameters with that flag. Whenever pg_ctl reload is executed, all the processes check whether recovery is in progress, and ignore silently the parameters with that flag if not. I'm not sure how easy we can implement this. In addition to introducing that flag, we might need to change some processes which cannot access to the shared memory so that they can. Otherwise, they cannot know whether recovery is in progress. Or we might need to change them so that they always ignore recovery parameters. The postmaster knows whether its in recovery or not without checking shared memory. Various postmaster states describe this. If not postmaster, other backends can run recoveryinprogress() as normal. It makes sense to have a new flag and that is easily created and used. Another simple but somewhat restricted approach is to read and set all parameters specified in recovery.conf by using PGC_S_OVERRIDE. If we do this, those parameters cannot be changed after startup even if recovery.conf is renamed. But the problem is that a user also cannot change their settings by reloading the configuration files. This is obviously a restriction. But it doesn't break any backward compatibility, I believe. No? If a user prefers new functionality (i.e., reload recovery parameters) rather than the backward compatibility, he/she can specify parameters in postgresql.conf. Thought? No need to create problems. -- 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] Thoughts on SELECT * EXCLUDING (...) FROM ...?
On Sun, Oct 30, 2011 at 8:50 PM, Eric Ridge eeb...@gmail.com wrote: Well, it's a display thing as much as any SELECT statement (especially via psql) is a display thing. It's more like I want all 127 columns, except the giant ::xml column, and I'm too lazy to type each column name out by hand. How about an option for psql to truncate too long columns to X characters ? Greetings Marcin Mańk -- 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] pgsql_fdw, FDW for PostgreSQL server
2011/10/31 Shigeru Hanada shigeru.han...@gmail.com: (2011/10/30 11:34), Shigeru Hanada wrote: 2011/10/30 Tom Lanet...@sss.pgh.pa.us: I think we have to. Even if we estimate that a given scan will return only a few rows, what happens if we're wrong? We don't want to blow out memory on the local server by retrieving gigabytes in one go. Oh, I overlooked the possibility of wrong estimation. Old PostgreSQL uses 1000 as default estimation, so big table which has not been analyzed may crashes the backend. To ensure the data retrieving safe, we need to get actual amount of result, maybe by executing SELECT COUNT(*) in planning phase. It sounds too heavy to do for every scan, and it still lacks actual width. One possible idea is to change default value of min_cursur_rows option to 0 so that pgsql_fdw uses CURSOR by default, but it seems not enough. I'll drop simple SELECT mode from first version of pgsql_fdw for safety. I removed simple SELECT mode from pgsql_fdw, and consequently also removed min_cursor_rows FDW option. This fix avoids possible memory exhaustion due to wrong estimation gotten from remote side. Once libpq has had capability to retrieve arbitrary number of rows from remote portal at a time without server-side cursor in future, then we will be able to revive simple SELECT. Then it's enough safe even if we don't have actual data size, but (maybe) faster than cursor mode because we can reduce # of SQL commands. Though of course proof of performance advantage should be shown before such development. If you need a less SQL commands, then you can increase fetch_count parameter - default 1000 is maybe too small, maybe 1 lines as default (not more). For more complex queries can be interesting to set a cursor_tuple_fraction Pavel -- Shigeru Hanada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So, is COUNT(*) fast now?
On Sun, Oct 30, 2011 at 8:02 AM, Kääriäinen Anssi anssi.kaariai...@thl.fi wrote: Table size is around 600MB, index size is around 350MB and VM on-disk size is 16kB with default fillfactor. With fillfactor = 10, the VM size is 104 KB, and table size is around 6GB. The index size is the same. What I think you're probably measuring here (oprofile would tell us for sure) is that once the size of the table goes beyond about half a gigabyte, it will have more than one page in the visibility map. The index-only scan code keeps the most recently used visibility map page pinned to save on overhead, but if you're bouncing back and forth between data in the first ~500MB of the table and data in the last ~100MB, each switch will result in dropping the current pin and getting a new one, which figures to be fairly expensive. With the table is only a little over 500GB, you're probably only changing VM pages every couple of tuples, but with a 6GB table just about every tuple will switch to a new VM page. Now, maybe you're right and the CPU caches are the more significant effect. But I wouldn't like to bet on it without seeing how much the drop-and-get-new-pin operations are costing us. -- 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] Thoughts on SELECT * EXCLUDING (...) FROM ...?
On 10/30/2011 10:00 PM, Christopher Browne wrote: There is legitimate reason to reject this on the basis of nondeterminism. While we are surely obliged to hold our noses and support SELECT *, as: A) The SQL standard obliges us, and B) People already use it a lot, Neither of those factors hold true for the EXCLUDING notion. So all things are decidedly not equal. Surely it's only non-deterministic to the extent that '*' itself is non-deterministic. So your argument boils down to 'anything that enhances * is bad,' ISTM. By all means I find it an interesting feature, but that shouldn't be mistaken for necessarily being a desirable feature. I don't think I wish it. We're telling our developers not to use select *, and I don't think having select * except would change that policy, beyond requiring us to waste time explaining : No, we're not changing policy. The fact that PGDG added this to 9.2 does *not* imply our policy was wrong. That's fine, and it's a good policy. A good policy might well exclude use of a number of available features (e.g. one place I know bans doing joins with ',' instead of explicit join operators). But I don't think it helps us decide what to support. The fact is that if you have 100 columns and want 95 of them, it's very tedious to have to specify them all, especially for ad hoc queries where the house SQL standards really don't matter that much. It's made more tedious by the fact that there is no real help in constructing the query. This gets particularly bad with views, which developers often seem to stuff with every available column that might be needed by some query instead of creating views tailored to particular queries. Not long ago annoyance with this prompted my to write a little utility function that would give me a query with all the columns specified so I could cut and paste it, and delete the columns I didn't want. (Another advantage is that the result is guaranteed typo free, which my typing certainly is not.) See https://gist.github.com/818490. It's far from perfect, but I still find myself using it several times a month, mainly for the very purpose intended by this suggested feature. cheers andrew -- 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] So, is COUNT(*) fast now?
On 10/31/2011 02:44 PM, Robert Haas wrote: What I think you're probably measuring here (oprofile would tell us for sure) is that once the size of the table goes beyond about half a gigabyte, it will have more than one page in the visibility map. The index-only scan code keeps the most recently used visibility map page pinned to save on overhead, but if you're bouncing back and forth between data in the first ~500MB of the table and data in the last ~100MB, each switch will result in dropping the current pin and getting a new one, which figures to be fairly expensive. With the table is only a little over 500GB, you're probably only changing VM pages every couple of tuples, but with a 6GB table just about every tuple will switch to a new VM page. Now, maybe you're right and the CPU caches are the more significant effect. But I wouldn't like to bet on it without seeing how much the drop-and-get-new-pin operations are costing us. Maybe I should have left the analysis part out of the post, I don't know the internals, so my analysis is likely to be wrong. Now that I think of it, claiming that the cache effect is 50% of the runtime is likely a little wrong... However the part about clustering being important is still correct. According to the test, you can get 50% overhead because of random access to the VM. Stupid question, but why not keep the whole VM pinned? - Anssi -- 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] So, is COUNT(*) fast now?
On Mon, Oct 31, 2011 at 9:51 AM, Anssi Kääriäinen anssi.kaariai...@thl.fi wrote: Stupid question, but why not keep the whole VM pinned? It might be that keeping more than one VM page pinned is a good idea, but we'd have to think carefully about it. For example, if we pin too many pages in shared_buffers, other queries could start erroring out for failure to find an evictable buffer. -- 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] Clarification on item on Todo List
Tristan Wright twrigh...@earlham.edu writes: I am interested in this item on the list and its exact meaning: (Under Data Types) - Fix data types where equality comparison is not intuitive, e.g. box Can you elaborate on this item, I particularly fail to see what is non-intuitive about box comparisons (and I suppose other types where equalities are non-intuitive). box_eq compares the boxes' areas. The operator that most people would consider to be equality is box_same (~=). The reason this is on the TODO is that nobody's figured out a way to swap the two operator names without breaking applications. 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] Multiple queries in transit
We have a user interface which fetches and displays many small pieces of
distinct information from a PostgreSQL database.
* fetches are simple lookups across a diverse set of tables,
in response to events on another data source
* uses PQsendQuery() on a non-blocking socket
But data fetches visibly take some time -- libpq doesn't allow a second
query to be sent until the first has been fully processed. The
back-and-forth seems to give a bottleneck on the round-trip.
Instead, it would be preferable to send multiple requests (down the TCP
socket), and then receive multiple responses (in order).
This would allow the sending, processing and receiving response to be
interleaved much more reasonably, and reduce the delay.
Could libpq be reasonably modified to allow this?
Looking at the libpq code (fq-exec.c), it seems almost no state needs to
be stored until results are received, and so perhaps this limitation is
unnecessary. The result-accumulation state is reset on sending the query;
it could perhaps be done on receipt. Are there problems with this?
Below is a simple illustration.
Also, whilst tracing code through to pqsecure_write(), I also wondered if
some Nagle's algorithm on the socket is also introducing an additional
delay? I can't see special consideration in the code for this (eg.
TCP_NODELAY)
Thoughts and suggestions appreciated, many thanks.
--
Mark
#include stdio.h
#include libpq-fe.h
#define QUEUE 10
void qerror(const char *label, PGconn *db)
{
fprintf(stderr, %s: %s, label, PQerrorMessage(db));
}
int main(int argc, char *argv[])
{
unsigned int n;
PGconn *db;
db = PQconnectdb();
if (PQstatus(db) != CONNECTION_OK) {
qerror(PQconnectdb, db);
return -1;
}
/* Send queries. Important: this simple example does not cover
* the case of a full transmit buffer */
for (n = 0; n QUEUE; n++) {
fprintf(stderr, Sending query %u...\n, n);
if (PQsendQuery(db, SELECT random()) != 1) {
qerror(PQsendQuery, db);
return -1;
}
}
/* Receive responses */
for (n = 0; n QUEUE; n++) {
PGresult *r;
fprintf(stderr, Receiving response %u...\n, n);
r = PQgetResult(db);
if (r == NULL) {
qerror(PQgetResult, db);
return -1;
}
fprintf(stderr, Result is %s\n, PQgetvalue(r, 0, 0));
PQclear(r);
}
PQfinish(db);
return 0;
}
--
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] Multiple queries in transit
On 31.10.2011 17:44, Mark Hills wrote: We have a user interface which fetches and displays many small pieces of distinct information from a PostgreSQL database. * fetches are simple lookups across a diverse set of tables, in response to events on another data source * uses PQsendQuery() on a non-blocking socket But data fetches visibly take some time -- libpq doesn't allow a second query to be sent until the first has been fully processed. The back-and-forth seems to give a bottleneck on the round-trip. Instead, it would be preferable to send multiple requests (down the TCP socket), and then receive multiple responses (in order). This would allow the sending, processing and receiving response to be interleaved much more reasonably, and reduce the delay. Could libpq be reasonably modified to allow this? I believe it's doable in theory, no-one has just gotten around to it. Patches are welcome. Also, whilst tracing code through to pqsecure_write(), I also wondered if some Nagle's algorithm on the socket is also introducing an additional delay? I can't see special consideration in the code for this (eg. TCP_NODELAY) We do set TCP_NODELAY, see connectNoDelay() in fe-connect.c (http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/interfaces/libpq/fe-connect.c;h=ed9dce941e1d57cce51f2c21bf29769dfe2ee542;hb=HEAD#l960) -- 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] Multiple queries in transit
I have nothing of substance to add, but On Mon, Oct 31, 2011 at 17:44, Mark Hills mark.hi...@framestore.com wrote: Instead, it would be preferable to send multiple requests (down the TCP socket), and then receive multiple responses (in order). HTTP calls this pipelining. I think it's helpful to adopt this term since the concept is already familiar to many developers. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] warning in pg_upgrade
I just noticed a warning in pg_upgrade: option.c: In function *parseCommandLine*: option.c:96:8: warning: ignoring return value of *getcwd*, declared with attribute warn_unused_result It looks like it might be worth testing the return value here for NULL, which would indicate an error accessing the current working directory. Untested patch attached for purposes of discussion. http://www.kernel.org/doc/man-pages/online/pages/man3/getcwd.3.html -Kevin *** a/contrib/pg_upgrade/option.c --- b/contrib/pg_upgrade/option.c *** *** 56,61 parseCommandLine(int argc, char *argv[]) --- 56,62 int option; /* Command line option */ int optindex = 0; /* used by getopt_long */ int os_user_effective_id; + char*return_buf; user_opts.transfer_mode = TRANSFER_MODE_COPY; *** *** 93,99 parseCommandLine(int argc, char *argv[]) if (os_user_effective_id == 0) pg_log(PG_FATAL, %s: cannot be run as root\n, os_info.progname); ! getcwd(os_info.cwd, MAXPGPATH); while ((option = getopt_long(argc, argv, d:D:b:B:cgG:kl:o:O:p:P:u:v, long_options, optindex)) != -1) --- 94,102 if (os_user_effective_id == 0) pg_log(PG_FATAL, %s: cannot be run as root\n, os_info.progname); ! return_buf = getcwd(os_info.cwd, MAXPGPATH); ! if (return_buf == NULL) ! pg_log(PG_FATAL, Could not access current working directory: %s\n, getErrorText(errno)); while ((option = getopt_long(argc, argv, d:D:b:B:cgG:kl:o:O:p:P:u:v, long_options, optindex)) != -1) -- 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] Multiple queries in transit
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 31.10.2011 17:44, Mark Hills wrote: Could libpq be reasonably modified to allow this? I believe it's doable in theory, no-one has just gotten around to it. Patches are welcome. Can't you do that today with a multi-command string submitted to PQsendQuery, followed by multiple calls to PQgetResult? I'm hesitant to think about supporting the case more thoroughly than that, or with any different semantics than that, because I think that the error-case behavior will be entirely unintelligible/unmaintainable unless you abandon all queries-in-flight in toto when an error happens. Furthermore, in most apps it'd be a serious PITA to keep track of which reply is for which query, so I doubt that such a feature is of general usefulness. 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] Multiple queries in transit
On 31.10.2011 19:09, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: On 31.10.2011 17:44, Mark Hills wrote: Could libpq be reasonably modified to allow this? I believe it's doable in theory, no-one has just gotten around to it. Patches are welcome. Can't you do that today with a multi-command string submitted to PQsendQuery, followed by multiple calls to PQgetResult? Yes, true, although that only works with the simple query protocol. The extended protocol doesn't allow multi-command queries. I'm hesitant to think about supporting the case more thoroughly than that, or with any different semantics than that, because I think that the error-case behavior will be entirely unintelligible/unmaintainable unless you abandon all queries-in-flight in toto when an error happens. Abandoning all in-flight queries seems quite reasonable to me. You could send a Sync message between each query to make it easier to track which query errored. Furthermore, in most apps it'd be a serious PITA to keep track of which reply is for which query, so I doubt that such a feature is of general usefulness. I think a common use for this would be doing multiple inserts or updates on one go. Like, insert into a parent table, then more details into child tables. You don't care about getting the results back in that case, as long as you get an error on failure. Another typical use case would be something like an ORM that wants to fetch a row from one table, and details of the same object from other tables. If it's just 2-3 queries, it's not that difficult to remember in which order they were issued. Both of those use cases would be happy with just sending a multi-command string with PQsendQuery(), because you know the all queries in advance, but it would be nice to not be limited to simple query protocol... -- 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] Multiple queries in transit
On Mon, Oct 31, 2011 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 31.10.2011 17:44, Mark Hills wrote: Could libpq be reasonably modified to allow this? I believe it's doable in theory, no-one has just gotten around to it. Patches are welcome. Can't you do that today with a multi-command string submitted to PQsendQuery, followed by multiple calls to PQgetResult? Multi command string queries don't support parameterization. The way I do it is to keep an application managed stack of data (as an array of record types) to send that is accumulated when the last stack is in transit. Then when the last response comes in you repeat. Of course, if you could parameterize a multi command string statement, that might be a better way to go. 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] Multiple queries in transit
On Mon, 31 Oct 2011, Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 31.10.2011 17:44, Mark Hills wrote: Could libpq be reasonably modified to allow this? I believe it's doable in theory, no-one has just gotten around to it. Patches are welcome. Can't you do that today with a multi-command string submitted to PQsendQuery, followed by multiple calls to PQgetResult? I remember something about this; I think I concluded that it validated that receiving multiple results could be done this way. But this kind of batching can't be used with prepared queries. I'm hesitant to think about supporting the case more thoroughly than that, or with any different semantics than that, because I think that the error-case behavior will be entirely unintelligible/unmaintainable unless you abandon all queries-in-flight in toto when an error happens. Can you explain a bit more detail which errors are of most concern, do you mean full buffers on the client send? Because the content of the stream going to/from the server does not change, I wouldn't really expect the semantics to change. For example, the server cannot even see that the client is behaving in this way. Are there any 'send' functions that are heavily reliant on some kind of result/receive state? I don't disagree with the comments above though, any shift towards unintelligible behaviour would be very bad. Furthermore, in most apps it'd be a serious PITA to keep track of which reply is for which query, so I doubt that such a feature is of general usefulness. In our UI case, we already have a queue. Because libpq can't pipeline multiple queries, we have to make our own queue of them anyway. -- Mark -- 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] Multiple queries in transit
On Mon, Oct 31, 2011 at 12:49 PM, Mark Hills mark.hi...@framestore.com wrote: On Mon, 31 Oct 2011, Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 31.10.2011 17:44, Mark Hills wrote: Could libpq be reasonably modified to allow this? I believe it's doable in theory, no-one has just gotten around to it. Patches are welcome. Can't you do that today with a multi-command string submitted to PQsendQuery, followed by multiple calls to PQgetResult? I remember something about this; I think I concluded that it validated that receiving multiple results could be done this way. But this kind of batching can't be used with prepared queries. I'm hesitant to think about supporting the case more thoroughly than that, or with any different semantics than that, because I think that the error-case behavior will be entirely unintelligible/unmaintainable unless you abandon all queries-in-flight in toto when an error happens. Can you explain a bit more detail which errors are of most concern, do you mean full buffers on the client send? Because the content of the stream going to/from the server does not change, I wouldn't really expect the semantics to change. For example, the server cannot even see that the client is behaving in this way. Are there any 'send' functions that are heavily reliant on some kind of result/receive state? I don't disagree with the comments above though, any shift towards unintelligible behaviour would be very bad. Furthermore, in most apps it'd be a serious PITA to keep track of which reply is for which query, so I doubt that such a feature is of general usefulness. In our UI case, we already have a queue. Because libpq can't pipeline multiple queries, we have to make our own queue of them anyway. Note, nothing is keeping you from opening up a second connection and interleaving in that fashion, so 'libpq' is not the bottleneck, the connection object is :-). 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] Your review of pg_receivexlog/pg_basebackup
On Fri, Oct 28, 2011 at 08:46, Fujii Masao masao.fu...@gmail.com wrote:
On Thu, Oct 27, 2011 at 11:14 PM, Magnus Hagander mag...@hagander.net wrote:
Here's a version that does this. Turns out this requires a lot less
code than what was previously in there, which is always nice.
We still need to solve the other part which is how to deal with the
partial files on restore. But this is definitely a cleaner way from a
pure pg_receivexlog perspective.
Comments/reviews?
Looks good.
Minor comment:
the source code comment of FindStreamingStart() seems to need to be updated.
Here's an updated patch that both includes this update to the comment,
and also the functionality to pre-pad files to 16Mb. This also seems
to have simplified the code, which is a nice bonus.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
*** a/src/bin/pg_basebackup/pg_receivexlog.c
--- b/src/bin/pg_basebackup/pg_receivexlog.c
***
*** 71,104 usage(void)
static bool
segment_callback(XLogRecPtr segendpos, uint32 timeline)
{
- char fn[MAXPGPATH];
- struct stat statbuf;
-
if (verbose)
fprintf(stderr, _(%s: finished segment at %X/%X (timeline %u)\n),
progname, segendpos.xlogid, segendpos.xrecoff, timeline);
/*
- * Check if there is a partial file for the name we just finished, and if
- * there is, remove it under the assumption that we have now got all the
- * data we need.
- */
- segendpos.xrecoff /= XLOG_SEG_SIZE;
- PrevLogSeg(segendpos.xlogid, segendpos.xrecoff);
- snprintf(fn, sizeof(fn), %s/%08X%08X%08X.partial,
- basedir, timeline,
- segendpos.xlogid,
- segendpos.xrecoff);
- if (stat(fn, statbuf) == 0)
- {
- /* File existed, get rid of it */
- if (verbose)
- fprintf(stderr, _(%s: removing file \%s\\n),
- progname, fn);
- unlink(fn);
- }
-
- /*
* Never abort from this - we handle all aborting in continue_streaming()
*/
return false;
--- 71,81
***
*** 119,127 continue_streaming(void)
/*
* Determine starting location for streaming, based on:
* 1. If there are existing xlog segments, start at the end of the last one
! * 2. If the last one is a partial segment, rename it and start over, since
! * we don't sync after every write.
! * 3. If no existing xlog exists, start from the beginning of the current
* WAL segment.
*/
static XLogRecPtr
--- 96,103
/*
* Determine starting location for streaming, based on:
* 1. If there are existing xlog segments, start at the end of the last one
! *that is complete (size matches XLogSegSize)
! * 2. If no valid xlog exists, start from the beginning of the current
* WAL segment.
*/
static XLogRecPtr
***
*** 133,139 FindStreamingStart(XLogRecPtr currentpos, uint32 currenttimeline)
bool b;
uint32 high_log = 0;
uint32 high_seg = 0;
- bool partial = false;
dir = opendir(basedir);
if (dir == NULL)
--- 109,114
***
*** 195,201 FindStreamingStart(XLogRecPtr currentpos, uint32 currenttimeline)
disconnect_and_exit(1);
}
! if (statbuf.st_size == 16 * 1024 * 1024)
{
/* Completed segment */
if (log high_log ||
--- 170,176
disconnect_and_exit(1);
}
! if (statbuf.st_size == XLOG_SEG_SIZE)
{
/* Completed segment */
if (log high_log ||
***
*** 208,244 FindStreamingStart(XLogRecPtr currentpos, uint32 currenttimeline)
}
else
{
! /*
! * This is a partial file. Rename it out of the way.
! */
! char newfn[MAXPGPATH];
!
! fprintf(stderr, _(%s: renaming partial file \%s\ to \%s.partial\\n),
! progname, dirent-d_name, dirent-d_name);
!
! snprintf(newfn, sizeof(newfn), %s/%s.partial,
! basedir, dirent-d_name);
!
! if (stat(newfn, statbuf) == 0)
! {
! /*
! * XXX: perhaps we should only error out if the existing file
! * is larger?
! */
! fprintf(stderr, _(%s: file \%s\ already exists. Check and clean up manually.\n),
! progname, newfn);
! disconnect_and_exit(1);
! }
! if (rename(fullpath, newfn) != 0)
! {
! fprintf(stderr, _(%s: could not rename \%s\ to \%s\: %s\n),
! progname, fullpath, newfn, strerror(errno));
! disconnect_and_exit(1);
! }
!
! /* Don't continue looking for more, we assume this is the last */
! partial = true;
! break;
}
}
--- 183,191
}
else
{
! fprintf(stderr, _(%s: segment file '%s' is incorrect size %d, skipping\n),
! progname, dirent-d_name, (int) statbuf.st_size);
! continue;
}
}
***
*** 247,263 FindStreamingStart(XLogRecPtr currentpos, uint32 currenttimeline)
if (high_log 0 || high_seg 0)
{
XLogRecPtr high_ptr;
!
! if (!partial)
! {
! /*
! * If the segment was partial, the pointer is already at the right
! * location since we want to
Re: [HACKERS] Multiple queries in transit
On Mon, Oct 31, 2011 at 12:49 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Oct 31, 2011 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 31.10.2011 17:44, Mark Hills wrote: Could libpq be reasonably modified to allow this? I believe it's doable in theory, no-one has just gotten around to it. Patches are welcome. Can't you do that today with a multi-command string submitted to PQsendQuery, followed by multiple calls to PQgetResult? Multi command string queries don't support parameterization. The way I do it is to keep an application managed stack of data (as an array of record types) to send that is accumulated when the last stack is in transit. Then when the last response comes in you repeat. (offlist) in more detail, what I do here is to place action data into a composite type and parameterize it into an array. That array is passed directly to a receiving query or a function if what's happening in the server is complex. We wrote a library for that purpose: see here: http://libpqtypes.esilo.com/ and especially here: http://libpqtypes.esilo.com/man3/pqt-composites.html so that while the connection is busy, and data is coming in from the app, you continually PQputf() more records into the array that is going to be shipped off to the server when the connection becomes available. On the query that gets to the server, it can be as simple as: insert into foo select unnest(%foo[]) select work_on_data(%foo[]) libpqtypes sends all the data in native binary formats so is very fast. 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] Multiple queries in transit
On Mon, Oct 31, 2011 at 1:08 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Oct 31, 2011 at 12:49 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Oct 31, 2011 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 31.10.2011 17:44, Mark Hills wrote: Could libpq be reasonably modified to allow this? I believe it's doable in theory, no-one has just gotten around to it. Patches are welcome. Can't you do that today with a multi-command string submitted to PQsendQuery, followed by multiple calls to PQgetResult? Multi command string queries don't support parameterization. The way I do it is to keep an application managed stack of data (as an array of record types) to send that is accumulated when the last stack is in transit. Then when the last response comes in you repeat. (offlist) in more detail, what I do here is to place action data into a composite type and parameterize it into an array. That array is passed directly to a receiving query or a function if what's happening in the server is complex. We wrote a library for that purpose: see here: http://libpqtypes.esilo.com/ and especially here: http://libpqtypes.esilo.com/man3/pqt-composites.html so that while the connection is busy, and data is coming in from the app, you continually PQputf() more records into the array that is going to be shipped off to the server when the connection becomes available. On the query that gets to the server, it can be as simple as: insert into foo select unnest(%foo[]) select work_on_data(%foo[]) libpqtypes sends all the data in native binary formats so is very fast. heh, sorry for the noise here :-). 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] unite recovery.conf and postgresql.conf
Simon, Everybody agrees a neater way of invoking standby mode would be good. I don't think this goes far enough. The whole recovery.conf/recovery.done thing is a serious problem for automated management of servers and automated failover. So it's not just a neater way would be good but using recovery.conf as a trigger file is a broken idea and needs to be changed. These things are announced as deprecated and will be removed when we go to release 10.0 * trigger_file * standby_mode * recovery.conf indicates standby So you're idea is that people who don't want recovery.conf to be used as a trigger file would not have the file at all, but would have something like replication.conf instead? If it's possible to run a replica without having a recovery.conf file, then I'm fine with your solution. If it's not, then I find your solution not to be a solution at all. recovery.conf should continue to be required to perform a PITR. If we place the recovery_target parameters into postgresql.conf we will have no way to differentiate between (1) a recovery that has successfully completed then crashed and (2) a user-specified recovery, which was the original rationale for its use. This is OK, since we now encourage people to enter a recovery by creating recovery.conf and for entering a standby to use a new cleaner API without the confusing use of the word recovery. Sure. recovery.conf worked fine for PITR. We've just overextended it for other purposes. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] LDAP server docs
So once again I forgot about the fact that you can specify multiple LDAP server in our ldapserver parameter (because both openldap and winldap accept a space separated list). Any objections to just applying the attached docs patch? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index 5d543cb..3734586 100644 --- a/doc/src/sgml/client-auth.sgml +++ b/doc/src/sgml/client-auth.sgml @@ -1387,7 +1387,8 @@ omicron bryanh guest1 termliteralldapserver/literal/term listitem para -Name or IP of LDAP server to connect to. +Name or IP of LDAP server to connect to. You may specify a +blank separated list of hostnames to try to connect to. /para /listitem /varlistentry -- 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] unite recovery.conf and postgresql.conf
On Mon, Oct 31, 2011 at 7:05 PM, Josh Berkus j...@agliodbs.com wrote: If it's possible to run a replica without having a recovery.conf file, then I'm fine with your solution. If it's not, then I find your solution not to be a solution at all. Then you are fine with the solution - not mine alone, just the sum of everybody's inputs. So we can teach the new way, while supporting the old way a while longer. Sure. recovery.conf worked fine for PITR. We've just overextended it for other purposes. Agreed. -- 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] LDAP server docs
On Mon, Oct 31, 2011 at 3:11 PM, Magnus Hagander mag...@hagander.net wrote: So once again I forgot about the fact that you can specify multiple LDAP server in our ldapserver parameter (because both openldap and winldap accept a space separated list). Any objections to just applying the attached docs patch? I think the term space might be more clear than blank here. Perhaps: Names or IP addresses of LDAP servers to connect to. Multiple servers may be specified, separated by spaces. -- 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] myProcLocks initialization
On Sun, Oct 30, 2011 at 11:26 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Oct 30, 2011 at 11:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I'd like to propose the attached patch, which initializes each PGPROC's myProcLocks just once at postmaster startup, rather than every time the PGPROC is handed out to a backend. These lists should always be emptied before a backend shuts down, so a newly initialized backend will find the lists empty anyway. Not reinitializing them shaves a few cycles. In my testing, it saves about 1% of the cost of setting up and tearing down a connection, which is not a ton, but a cycle saved is a cycle earned. That's not really enough to excite me, and the prospect of problems in one session corrupting an unrelated later one is pretty scary from a debugging standpoint. How about at least an Assert that the lock is in a clean state? I can go for that. Revised patch attached. I think it would be useful to assert this both at process startup time and at process shutdown, since it would really be much nicer to have the process that didn't clean up fail the assertion, rather than the new one that innocently inherited its slot; so the attached patch takes that approach. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company init-myproclocks-once-v2.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] LDAP server docs
Magnus Hagander mag...@hagander.net writes: So once again I forgot about the fact that you can specify multiple LDAP server in our ldapserver parameter (because both openldap and winldap accept a space separated list). Any objections to just applying the attached docs patch? space-separated list is more in keeping with our usual terminology, I think, but otherwise please do. 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] LDAP server docs
On Mon, Oct 31, 2011 at 20:58, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: So once again I forgot about the fact that you can specify multiple LDAP server in our ldapserver parameter (because both openldap and winldap accept a space separated list). Any objections to just applying the attached docs patch? space-separated list is more in keeping with our usual terminology, I think, but otherwise please do. FWIW, the use of the word blank was just because I copied it off the ldap manpage. I agree space is better :-) -- 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] myProcLocks initialization
Robert Haas robertmh...@gmail.com writes: Revised patch attached. I think it would be useful to assert this both at process startup time and at process shutdown, since it would really be much nicer to have the process that didn't clean up fail the assertion, rather than the new one that innocently inherited its slot; so the attached patch takes that approach. +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] myProcLocks initialization
Simon Riggs si...@2ndquadrant.com writes: On Mon, Oct 31, 2011 at 7:54 PM, Robert Haas robertmh...@gmail.com wrote: Revised patch attached. I think it would be useful to assert this both at process startup time and at process shutdown, since it would really be much nicer to have the process that didn't clean up fail the assertion, rather than the new one that innocently inherited its slot; so the attached patch takes that approach. Something stronger than an assertion at shutdown? Run-time test? There's currently no evidence to suggest this will ever fire at all, especially not in non-development builds, so an assert seems enough to me. 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] myProcLocks initialization
On Mon, Oct 31, 2011 at 7:54 PM, Robert Haas robertmh...@gmail.com wrote: Revised patch attached. I think it would be useful to assert this both at process startup time and at process shutdown, since it would really be much nicer to have the process that didn't clean up fail the assertion, rather than the new one that innocently inherited its slot; so the attached patch takes that approach. Something stronger than an assertion at shutdown? Run-time test? -- 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
[HACKERS] Optimizing GetRunningTransactionLocks()
My list of things to do included optimising GetRunningTransactionLocks(), run once per checkpoint. I was thinking I needed to try harder to avoid acquiring LWlocks on all the lock partitions. ISTM that I don't need to do this - lwlocks on lock partitions are almost never contended now, so this should go much faster than before. Any thoughts? Do we think it would benefit from further tweaking? I'll assume not unless I hear from somebody with a different idea. -- 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] Optimizing GetRunningTransactionLocks()
Simon Riggs si...@2ndquadrant.com writes: My list of things to do included optimising GetRunningTransactionLocks(), run once per checkpoint. I was thinking I needed to try harder to avoid acquiring LWlocks on all the lock partitions. ISTM that I don't need to do this - lwlocks on lock partitions are almost never contended now, so this should go much faster than before. Any thoughts? Do we think it would benefit from further tweaking? I'll assume not unless I hear from somebody with a different idea. ISTM that some evidence of a problem should be acquired before expending sweat on a solution ... have you seen evidence that this creates any real issue? 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] IDLE in transaction introspection
Hey all, So, I'm dealing with a a big ol' java app that has multiple roads on the way to IDLE in transaction. We can reproduce the problem in a test environment, but the lead dev always asks can you just tell me the last query that it ran? So I wrote the attached patch, it just turns IDLE in transaction into: IDLE in transaction\n: Previous: last query executed. After seeing how quickly our dev's fixed the issue once they saw prepared statement XYZ, I'm thinking that I'd like to be able to have this in prod, and... maybe (with the frequency of IIT questions posted here) someone else would find this useful. Just wondering what ya'll thought. Any feedback (including a more efficient approach) is welcome. (Patch against release 9.1.1 tarball). Thanks! -- Scott Mead OpenSCG idleInTrans.911.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] Multiple queries in transit
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I think a common use for this would be doing multiple inserts or updates on one go. Like, insert into a parent table, then more details into child tables. You don't care about getting the results back in that case, as long as you get an error on failure. As of 9.1 you can use WITH to achieve that in many cases. wCTE and INSERT|UPDATE|DELETE … RETURNING are pretty cool combined :) Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] IDLE in transaction introspection
On Mon, Oct 31, 2011 at 22:37, Scott Mead sco...@openscg.com wrote: Hey all, So, I'm dealing with a a big ol' java app that has multiple roads on the way to IDLE in transaction. We can reproduce the problem in a test environment, but the lead dev always asks can you just tell me the last query that it ran? So I wrote the attached patch, it just turns IDLE in transaction into: IDLE in transaction\n: Previous: last query executed. After seeing how quickly our dev's fixed the issue once they saw prepared statement XYZ, I'm thinking that I'd like to be able to have this in prod, and... maybe (with the frequency of IIT questions posted here) someone else would find this useful. Just wondering what ya'll thought. Any feedback (including a more efficient approach) is welcome. (Patch against release 9.1.1 tarball). Thanks! I think the idea in general is pretty useful, but I'd like to extend on it. It would be even better to have a last query executed in the general IDLE state as well, not just idle in transaction. However, doing it the way you did it by adding it to the current query is going to break a lot of tools. I think it's a better idea to create a separate column called last query or something like that. Actually, for the future, it might be useful to have a state column, that holds the idle/in transaction/running status, instead of the tools having to parse the query text to get that information... -- 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] IDLE in transaction introspection
On Mon, Oct 31, 2011 at 4:45 PM, Magnus Hagander mag...@hagander.net wrote: Actually, for the future, it might be useful to have a state column, that holds the idle/in transaction/running status, instead of the tools having to parse the query text to get that information... if we are going to create the state column let's do it now and change current_query for last_query (so last query can be running, or it was the last before enter in idle state) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitació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] unite recovery.conf and postgresql.conf
On Mon, Oct 31, 2011 at 3:19 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Oct 31, 2011 at 7:05 PM, Josh Berkus j...@agliodbs.com wrote: If it's possible to run a replica without having a recovery.conf file, then I'm fine with your solution. If it's not, then I find your solution not to be a solution at all. Then you are fine with the solution - not mine alone, just the sum of everybody's inputs. So we can teach the new way, while supporting the old way a while longer. In most cases we either break backwards compatibility or require some type of switch to turn on backwards compatibility for those who want it. While the above plan tries to do one better, it leaves me feeling that the thing I don't like about this is that it sounds like you are forcing backwards compatibility on people who would much rather just do things the new way. Given that, I foresee a whole new generation of confused users who end up setting their configs one way only to have someone else set the same config in the other file, or some tool dump out some config file, overriding what was really intended. This will also make things *harder* for those tool providers you are trying to help, as they will be forced to support the behavior *both ways*. I'd much rather see some type of switch which turns on the old behavior for those who really want it, because while you can teach the new behavior, if you can't prevent the old behavior, you're creating operational headaches for yourself. Robert Treat conjecture: xzilla.net consulting: omniti.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] IDLE in transaction introspection
On Mon, Oct 31, 2011 at 5:45 PM, Magnus Hagander mag...@hagander.net wrote: Actually, for the future, it might be useful to have a state column, that holds the idle/in transaction/running status, instead of the tools having to parse the query text to get that information... +1 for doing it this way. Splitting current_query into query and state would be more elegant and easier to use all around. -- 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] Thoughts on SELECT * EXCLUDING (...) FROM ...?
On 10/31/2011 06:54 AM, Marcin Mańk wrote: On Sun, Oct 30, 2011 at 8:50 PM, Eric Ridgeeeb...@gmail.com wrote: Well, it's a display thing as much as any SELECT statement (especially via psql) is a display thing. It's more like I want all 127 columns, except the giant ::xml column, and I'm too lazy to type each column name out by hand. How about an option for psql to truncate too long columns to X characters ? I would use this option frequently... :-) It seems more to the point of what is trying to be accomplished, and doesn't even require a server change? :-) -- Mark Mielkem...@mielke.cc -- 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] IDLE in transaction introspection
On Mon, Oct 31, 2011 at 6:13 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 31, 2011 at 5:45 PM, Magnus Hagander mag...@hagander.net wrote: Actually, for the future, it might be useful to have a state column, that holds the idle/in transaction/running status, instead of the tools having to parse the query text to get that information... +1 for doing it this way. Splitting current_query into query and state would be more elegant and easier to use all around. I'm all for splitting it out actually. My concern was that I would break the 'ba-gillion' monitoring tools that already have support for pg_stat_activity if I dropped a column. What if we had: 'state' : idle | in transaction | running ( per Robert ) 'current_query' : the most recent query (either last / currently running) That may be a bit tougher to get across to people though (especially in the case where state='IDLE'). I'll rework this when I don't have trick-or-treaters coming to the front door :) -- Scott Mead OpenSCG http://www.openscg.com -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] IDLE in transaction introspection
On Mon, Oct 31, 2011 at 7:18 PM, Scott Mead sco...@openscg.com wrote: On Mon, Oct 31, 2011 at 6:13 PM, Robert Haas robertmh...@gmail.comwrote: On Mon, Oct 31, 2011 at 5:45 PM, Magnus Hagander mag...@hagander.net wrote: Actually, for the future, it might be useful to have a state column, that holds the idle/in transaction/running status, instead of the tools having to parse the query text to get that information... +1 for doing it this way. Splitting current_query into query and state would be more elegant and easier to use all around. I'm all for splitting it out actually. My concern was that I would break the 'ba-gillion' monitoring tools that already have support for pg_stat_activity if I dropped a column. What if we had: 'state' : idle | in transaction | running ( per Robert ) Sorry per Robert and Jaime 'current_query' : the most recent query (either last / currently running) That may be a bit tougher to get across to people though (especially in the case where state='IDLE'). I'll rework this when I don't have trick-or-treaters coming to the front door :) -- Scott Mead OpenSCG http://www.openscg.com -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] psql expanded auto
I wrote:
I have often found myself wanting that psql automatically switch between
normal and \x mode depending on the width of the output. Would others
find this useful?
Attached is a crude demo patch. Enable with \pset expanded auto.
Here is a finalized patch for this. The first hunk of the patch is the
documentation change, so you can see there how it's supposed to work.
Let me know what you think.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index d6941e0..e0f5ef4 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1876,15 +1876,19 @@ lo_import 152801
termliteralexpanded/literal (or literalx/literal)/term
listitem
para
- If replaceable class=parametervalue/replaceable is specified
- it must be either literalon/literal or literaloff/literal
- which will enable or disable expanded mode. If replaceable
- class=parametervalue/replaceable is omitted the command toggles
- between regular and expanded mode.
- When expanded mode is enabled, query results
- are displayed in two columns, with the column name on the left and
- the data on the right. This mode is useful if the data wouldn't fit
- on the screen in the normal quotehorizontal/quote mode.
+ If replaceable class=parametervalue/replaceable is specified it
+ must be either literalon/literal or literaloff/literal, which
+ will enable or disable expanded mode, or literalauto/literal.
+ If replaceable class=parametervalue/replaceable is omitted the
+ command toggles between the on and off settings. When expanded mode
+ is enabled, query results are displayed in two columns, with the
+ column name on the left and the data on the right. This mode is
+ useful if the data wouldn't fit on the screen in the
+ normal quotehorizontal/quote mode. In the auto setting, the
+ expanded mode is used whenever the query output is wider than the
+ screen, otherwise the regular mode is used. The auto setting is only
+ effective in the aligned and wrapped formats. In other formats, it
+ always behaves as if the expanded mode is off.
/para
/listitem
/varlistentry
@@ -2326,10 +2330,10 @@ lo_import 152801
varlistentry
-termliteral\x/literal/term
+termliteral\x [ replaceable class=parameteron/replaceable | replaceable class=parameteroff/replaceable | replaceable class=parameterauto/replaceable ]/literal/term
listitem
para
-Toggles expanded table formatting mode. As such it is equivalent to
+Sets or toggles expanded table formatting mode. As such it is equivalent to
literal\pset expanded/literal.
/para
/listitem
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 2c38902..5edeeb1 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1343,7 +1343,7 @@ exec_command(const char *cmd,
free(fname);
}
- /* \x -- toggle expanded table representation */
+ /* \x -- set or toggle expanded table representation */
else if (strcmp(cmd, x) == 0)
{
char *opt = psql_scan_slash_option(scan_state,
@@ -2177,14 +2177,21 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
/* set expanded/vertical mode */
else if (strcmp(param, x) == 0 || strcmp(param, expanded) == 0 || strcmp(param, vertical) == 0)
{
- if (value)
+ if (value pg_strcasecmp(value, auto) == 0)
+ popt-topt.expanded = 2;
+ else if (value)
popt-topt.expanded = ParseVariableBool(value);
else
popt-topt.expanded = !popt-topt.expanded;
if (!quiet)
- printf(popt-topt.expanded
- ? _(Expanded display is on.\n)
- : _(Expanded display is off.\n));
+ {
+ if (popt-topt.expanded == 1)
+printf(_(Expanded display is on.\n));
+ else if (popt-topt.expanded == 2)
+printf(_(Expanded display is used automatically.\n));
+ else
+printf(_(Expanded display is off.\n));
+ }
}
/* locale-aware numeric output */
diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
index 0d18665..c693040 100644
--- a/src/bin/psql/print.c
+++ b/src/bin/psql/print.c
@@ -122,9 +122,11 @@ const printTextFormat pg_utf8format =
/* Local functions */
static int strlen_max_width(unsigned char *str, int *target_width, int encoding);
-static void IsPagerNeeded(const printTableContent *cont, const int extra_lines,
+static void IsPagerNeeded(const printTableContent *cont, const int extra_lines, bool expanded,
FILE **fout, bool *is_pager);
+static void print_aligned_vertical(const printTableContent *cont, FILE *fout);
+
static void *
pg_local_malloc(size_t size)
@@ -713,6 +715,17 @@ print_aligned_text(const printTableContent *cont, FILE *fout)
}
}
+ /*
+ * If in expanded auto mode, we have now calculated the
Re: [HACKERS] Your review of pg_receivexlog/pg_basebackup
On Tue, Nov 1, 2011 at 3:08 AM, Magnus Hagander mag...@hagander.net wrote:
On Fri, Oct 28, 2011 at 08:46, Fujii Masao masao.fu...@gmail.com wrote:
On Thu, Oct 27, 2011 at 11:14 PM, Magnus Hagander mag...@hagander.net
wrote:
Here's a version that does this. Turns out this requires a lot less
code than what was previously in there, which is always nice.
We still need to solve the other part which is how to deal with the
partial files on restore. But this is definitely a cleaner way from a
pure pg_receivexlog perspective.
Comments/reviews?
Looks good.
Minor comment:
the source code comment of FindStreamingStart() seems to need to be updated.
Here's an updated patch that both includes this update to the comment,
and also the functionality to pre-pad files to 16Mb. This also seems
to have simplified the code, which is a nice bonus.
Here are the comments:
In open_walfile(), zerobuf needs to be free'd after use of it.
+ f = open(fn, O_WRONLY | O_CREAT | PG_BINARY, 0666);
We should use S_IRUSR | S_IWUSR instead of 0666 as a file access modes?
+ if (write(f, zerobuf, XLOG_BLCKSZ) != XLOG_BLCKSZ)
+ {
+ fprintf(stderr, _(%s: could not pad WAL segment %s:
%s\n),
+ progname, fn, strerror(errno));
+ close(f);
+ return -1;
+ }
When write() fails, we should delete the partial WAL file, like
XLogFileInit() does?
If not, subsequent pg_receivexlog always fails unless a user deletes
it manually.
Because open_walfile() always fails when it finds an existing partial WAL file.
When open_walfile() fails, pg_receivexlog exits without closing the connection.
I don't think this is good error handling. But this issue itself is
not what we're
trying to address now. So I think you can commit separately from current patch.
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] Multiple queries in transit
On 2011-11-01 00:53, Merlin Moncure wrote: On Mon, Oct 31, 2011 at 12:49 PM, Mark Hillsmark.hi...@framestore.com wrote: Furthermore, in most apps it'd be a serious PITA to keep track of which reply is for which query, so I doubt that such a feature is of general usefulness. In our UI case, we already have a queue. Because libpq can't pipeline multiple queries, we have to make our own queue of them anyway. In libpqxx (the C++ API) you do get support for this kind of pipelining. Look for the pipeline class. It uses the concatenate queries, retrieve multiple results trick. The pipeline also serves as an easy-to-manage interface for asynchronous querying: fire off your query, go do other things while the server is working, then ask for the result (at which point you'll block if necessary). Front page: http://pqxx.org/development/libpqxx/ Pipeline class: http://pqxx.org/devprojects/libpqxx/doc/stable/html/Reference/a00062.html Jeroen -- 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]
On Sat, 2011-10-29 at 21:12 +0200, Erik Rijkers wrote: Would it be possible to remove of the double quotes in the daterange display of BC dates? select '[0001-10-29 BC,2011-10-29)'::daterange; daterange -- [0001-10-29 BC,2011-10-29) (1 row) It accepts values without quotes, but on output it quotes them similar to a record type. Try: create table foo(d date); select '(0001-10-29 BC)'::foo; The spaces are the only reason it's being quoted there. I think it's best to be fairly consistent, and it was suggested that I model the input parsing after the record parsing. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
