Re: [HACKERS] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
On Wed, 2010-06-23 at 21:54 +, Robert Haas wrote: Log Message: --- Add TCP keepalive support to libpq. I misunderstood the earlier discussion on this and didn't realise you were considering committing in this way. For me, this is two patches, not one. I object to one and like the other. This adds four additional connection parameters to libpq: keepalives, keepalives_idle, keepalives_count, and keepalives_interval. keepalives default to on, per discussion, but can be turned off by specifying keepalives=0. The remaining parameters, where supported, can be used to adjust how often keepalives are sent and how many can be lost before the connection is broken. There isn't any need at at all for this. We can already add options on the libpq connection line. options = '-o tcp_keepalives_idle=X tcp_keepalives_interval=Y tcp_keepalives_count=Z' I see zero need to further complicate the libpq interface. If it changes frequently between releases then supporting PostgreSQL programs becomes much harder and leads to software not working correctly with Postgres. Connecting to Postgres is already too complex. At most it needs an example in the manual to show how to do this the existing way. I'm sorry to express this opinion now, again because I misunderstood. The immediate motivation for this patch is to make sure that walreceiver will eventually notice if the master reboots without closing the connection cleanly, but it should be helpful in other cases as well. My understanding is that the motivation for this is that the above options parameter would not have worked? So I regard making that work as a bug fix, so agree with some parts of this patch, I think. -- Simon Riggs www.2ndQuadrant.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] TOAST issue on custom index access method
Hi *,
I have a strange issue using a custom built index structure. My index access
method support document type composed of words (as tsvector) and points
(1-dimensional arrays of them). For internal reasons, I have to save the
documents as a whole inside my structure (for proper reorganisations).
So, I form the tuples using index_form_tuple with the proper description.
Everything works fine, as long as the documents are quite small. However, if
the tsvector becomes too large, I run into a problem of not being able to store
the documents, because (obviously) the tsvector is too large for one page.
Everything works fine, as long as I do insert ...(select...) statement, i.e. I
have some table that feeds my table, where the index is set up, with data
using, e.g.:
insert into documents(words, points) (select words, points from docs);
This query works in every case, because the words that I receive in my index
are properly toasted in advance and the size of the documents is quite limited
then. The document is constructed as CREATE TYPE document AS (words tsvector,
points _point) .
However, I tried to insert a document containing a large words vector directly
(the tsvector is of length 770, actually the points don't matter here too much).
I tried this using:
INSERT INTO documents (words, points) VALUES (to_tsvector('english',
'extremely long string constant.'), 'points array');
If I perform the statement, mentioned above (I can also extract data of my
feeding table 'docs' and use them as input), i.e. I insert the large vector
directly, without pulling it out of some feeding table, I get a problem, when I
call index_form_tuple, resulting in the following output:
ERROR: index row requires 12320 bytes, maximum size is 8191
So, creating my tuple using index_form_tuple fails because of the tsvector not
being toasted in prior to inserting it to my index.
What I tried to solve this issue here, is to extract the words from the
document (in my index) and calling 'Datum toast_compress_datum(Datum value)'in
order to compress the tsvector into a proper toast table. Unfortunately, this
function always returned NULL, which meant that the vector could not be
compressed, resulting in the same error.
Actually, currently, I don't know, how to proceed (of course, there would be
the possibilty of stopping the direct insertion, creating a table which
triggers the insertion into the real data table, containing the index and
inserting the row there after the insertion, which I don't assume to be a good
solution).
Does anybody know about this issue of getting untoasted values if the values
are inserted directly and toasted values, if copying the data from another
table?
I hope that somebody could help me, here in order that my access method is able
to run, properly.
Thanks in advance
Best regards
Carsten Kropf
P.S.: The version I use to program is 8.4.2
Re: [HACKERS] ECPG FETCH readahead
Hi, 2010-06-23 22:42 keltezéssel, Bruce Momjian írta: Boszormenyi Zoltan wrote: Hi, we improved ECPG quite a lot in 9.0 because we worked and still working with an Informix to PostgreSQL migration project. We came across a pretty big performance problem that can be seen in every naive application that uses only FETCH 1, FETCH RELATIVE or FETCH ABSOLUTE. These are almost the only FETCH variations usable in Informix, i.e. it doesn't have the grammar for fetching N rows at once. Instead, the Client SDK libraries do caching themselves behind the scenes to reduce network turnaround time. I assume our ecpg version supports1 fetch values, even in Informix mode. Does it make sense to add lots of code to our ecpg then? I think, yes, it does make sense. Because we are talking about porting a whole lot of COBOL applications. The ESQL/C or ECPG connector was already written the Informix quirks in mind, so it fetches only one record at a time passing it to the application. And similar performance is expected from ECPG - which excpectation is not fulfilled currently because libecpg doesn't do the same caching as ESQL/C does. And FYI, I haven't added a whole lot of code, most of the code changes in the patch is execute.c refactoring. ECPGdo() was split into several functions, the new parts are still doing the same things. I can make the test case much smaller, I only needed to test crossing the readahead window boundary. This would also make the patch much smaller. And this readahead is not on by default, it's only activated by ecpg -r fetch_readahead. Best regards, Zoltán Böszörményi -- 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] ECPG FETCH readahead
On 24/06/10 10:27, Böszörményi Zoltán wrote: And this readahead is not on by default, it's only activated by ecpg -r fetch_readahead. Is there a reason not to enable it by default? I'm a bit worried that it will receive no testing if it's not always on. -- 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] ECPG FETCH readahead
2010-06-24 11:04 keltezéssel, Heikki Linnakangas írta: On 24/06/10 10:27, Böszörményi Zoltán wrote: And this readahead is not on by default, it's only activated by ecpg -r fetch_readahead. Is there a reason not to enable it by default? I'm a bit worried that it will receive no testing if it's not always on. Because in the first step I wanted to minimize the impact on regression test stderr results. This is what I mentioned in the initial mail, I stuck to the original wording of ecpg_log() messages in the split-up parts of the original ECPGdo() and ecpg_execute() exactly for this reason. The usual policy for ecpg_log() is to report the function name where it was issued. I was also thinking about a new feature for pg_regress, to compare stdout results of two regression tests automatically so a difference can be reported as an error. It would be good for automated testing of features in ECPG that can be toggled, like auto-prepare and fetch readahead. It might come in handy in other subsystems, too. Best regards, Zoltán Böszörményi -- 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] TCP keepalive support for libpq
On Thu, Jun 24, 2010 at 03:14, Robert Haas robertmh...@gmail.com wrote: On Tue, Jun 22, 2010 at 12:32 PM, Magnus Hagander mag...@hagander.net wrote: I looked around quickly earlier when we chatted about this, and I think I found an API call to change them for a socket as well - but a Windows specific one, not the ones you'd find on Unix... Magnus - or anyone who knows Windows - Now that I've committed this patch, any chance you want to add a few lines of code to make this work on Windows also? I can probably look at that, yes. But definitely not until next week, and I can't promise I'll make it next week either. So if somebody else knows what to do, please go ahead and do so - I can definitely commit to *reviewing* it next week :-) -- 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
[HACKERS] EOL is when?
Our versioning policy (http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy) says that we EOL 7.4 and 8.0 July 2010. Does that mean July 1st or July 31st? -- 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] TCP keepalive support for libpq
On Tue, Jun 22, 2010 at 6:04 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: What does bother me is the fact that we are engineering a critical aspect of our system reliability around vendor-specific implementation details of the TCP stack, and that if any version of any operating system that we support (or ever wish to support in the future) fails to have a reliable implementation of this feature AND configurable knobs that we can tune to suit our needs, then we're screwed. Does anyone want to argue that this is NOT a house of cards? [/me raises hand] TCP keepalive has been available and a useful part of my reliability solutions since I had so find a way to clean up zombie database connections caused by clients powering down their workstations without closing their apps -- that was in OS/2 circa 1990. I think the problem is that the above is precisely what TCP keepalives were designed for -- to prevent connections that are definitely dead from living on forever. Even then they're controversial and mean sacrificing a feature that's quite desirable for TCP -- namely that idle connections don't die unnecessarily in the face of transient failures and can function fine when the link returns. The proposed use is for detecting connections which aren't responding quickly enough for our tastes which might be much more quickly than TCP timeouts. Because we have a backup plan the conservative option in our case is to kill the connection as soon as there's any doubt about it's validity so we can try a new connection. That's just not how TCP is designed -- the conservative option is assumed to be to keep the connection open until there's no doubt the connection is dead. I think it's going to be an uphill battle convincing TCP that we know better than the TCP spec about how aggressive it should be about throwing errors and killing connections. Once we have TCP keepalives set low enough -- assuming the OS will allow it to be set much lower -- we'll find that other timeouts are longer than we expect too. TCP Keepalives won't come into it at all if there is any unacked data pending -- TCP *will* detect that case but it might take longer than you want too and you won't be able to lower it. -- greg -- 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] ECPG FETCH readahead
On Wed, Jun 23, 2010 at 04:42:37PM -0400, Bruce Momjian wrote: I assume our ecpg version supports 1 fetch values, even in Informix mode. Does it make sense to add lots of code to our ecpg then? Yes, it does. The big question that zoltan and I haven't figured out yet, is whether it makes sense to add all this even for native ecpg mode. 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 ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org 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] ECPG FETCH readahead
I think, yes, it does make sense. Because we are talking about porting a whole lot of COBOL applications. COBOL??? The ESQL/C or ECPG connector was already written the Informix quirks in mind, so it fetches only one record at a time passing it to the application. And similar performance is expected from ECPG - which excpectation is not fulfilled currently because libecpg doesn't do the same caching as ESQL/C does. Eh, you are talking about a program you wrote for your customer or they wrote themselves, right? I simply refuse to add this stuff only to fix this situation for that one customer of yours if it only hits them. Now the thing to discuss is how common is this situation. 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 ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org 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] ECPG FETCH readahead
On Thu, Jun 24, 2010 at 12:04:30PM +0300, Heikki Linnakangas wrote: Is there a reason not to enable it by default? I'm a bit worried that it will receive no testing if it's not always on. Yes, there is a reason, namely that you don't need it in native mode at all. ECPG can read as many records as you want in one go, something ESQL/C apparently cannot do. This patch is a workaround for that restriction. I still do not really see that this feature gives us an advantage in native mode though. 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 ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org 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] TCP keepalive support for libpq
Greg Stark wrote: we'll find that other timeouts are longer than we expect too. TCP Keepalives won't come into it at all if there is any unacked data pending -- TCP *will* detect that case but it might take longer than you want too and you won't be able to lower it. If memory servers after twenty years, and the standard hasn't changed, if you add up all the delays, it can take about nine minutes maximum for a connection to break due to a wait for unacked data. That's longer than the values Robert showed (which I think was between one and two minutes -- can't fine the post at the moment), but quite a bit less than the two hours and ten minutes you get with the defaults for keepalive. -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] ECPG FETCH readahead
On Jun 24, 2010, at 2:13 PM, Michael Meskes wrote: I think, yes, it does make sense. Because we are talking about porting a whole lot of COBOL applications. COBOL??? yes, COBOL :). it is much more common than people think. it is not the first COBOL request for PostgreSQL hitting my desk. in our concrete example we are using a C module written with ECPG which is magically attached to tons of COBOL code ... The ESQL/C or ECPG connector was already written the Informix quirks in mind, so it fetches only one record at a time passing it to the application. And similar performance is expected from ECPG - which excpectation is not fulfilled currently because libecpg doesn't do the same caching as ESQL/C does. Eh, you are talking about a program you wrote for your customer or they wrote themselves, right? I simply refuse to add this stuff only to fix this situation for that one customer of yours if it only hits them. Now the thing to discuss is how common is this situation. Michael i think that this cursor issue is a pretty common thing for many codes. people are usually not aware of the fact that network round trips and parsing which are naturally related to FETCH 1 are a lot more expensive than fetching one row somewhere deep inside the DB engine. out there there are many applications which fetch data row by row. if an app fetches data row by row in PostgreSQL it will be A LOT slower than in, say, Informix because most commercial database clients will cache data inside a cursor behind the scenes to avoid the problem we try to solve. currently we are talking about a performance penalty of factor 5 or so. so - it is not a small thing; it is a big difference. regards, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- 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] ECPG FETCH readahead
2010-06-24 14:13 keltezéssel, Michael Meskes írta: I think, yes, it does make sense. Because we are talking about porting a whole lot of COBOL applications. COBOL??? Yes, OpenCOBOL... The ESQL/C or ECPG connector was already written the Informix quirks in mind, so it fetches only one record at a time passing it to the application. And similar performance is expected from ECPG - which excpectation is not fulfilled currently because libecpg doesn't do the same caching as ESQL/C does. Eh, you are talking about a program you wrote for your customer or they wrote themselves, right? I simply refuse to add this stuff only to fix this situation for that one customer of yours if it only hits them. Now the thing to discuss is how common is this situation. The OpenCOBOL database connector was written by them but the problem is more generic. There are many naive applications (elsewhere, too) using cursors but fetching one record at a time perhaps for portability reasons. This patch provides a big performance boost for those. Best regards, Zoltán Böszörményi -- 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] EOL is when?
On 24/06/10 14:41, Magnus Hagander wrote: Our versioning policy (http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy) says that we EOL 7.4 and 8.0 July 2010. Does that mean July 1st or July 31st? Probably means one more minor release, and that's it. -- 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] EOL is when?
On Thu, Jun 24, 2010 at 15:39, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 24/06/10 14:41, Magnus Hagander wrote: Our versioning policy (http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy) says that we EOL 7.4 and 8.0 July 2010. Does that mean July 1st or July 31st? Probably means one more minor release, and that's it. Yes, but I care about what actual date we mean. A month is a lot of time :-) -- 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] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
Simon Riggs si...@2ndquadrant.com writes: On Wed, 2010-06-23 at 21:54 +, Robert Haas wrote: This adds four additional connection parameters to libpq: keepalives, keepalives_idle, keepalives_count, and keepalives_interval. keepalives default to on, per discussion, but can be turned off by specifying keepalives=0. The remaining parameters, where supported, can be used to adjust how often keepalives are sent and how many can be lost before the connection is broken. There isn't any need at at all for this. We can already add options on the libpq connection line. options = '-o tcp_keepalives_idle=X tcp_keepalives_interval=Y tcp_keepalives_count=Z' Huh? The above is 100% fanciful; there was no code in libpq or anywhere else that would have processed such a thing. The bigger picture is that this patch is needed, not only for walreceiver but for many other purposes --- the feature has been requested repeatedly over the years and was already in the 9.1 commitfest queue. We moved it up because it seemed fairly important for walreceiver's purposes, but it would have gotten done in the very near future anyway. 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] TOAST issue on custom index access method
Carsten Kropf ckro...@fh-hof.de writes: I have a strange issue using a custom built index structure. My index access method support document type composed of words (as tsvector) and points (1-dimensional arrays of them). For internal reasons, I have to save the documents as a whole inside my structure (for proper reorganisations). So, I form the tuples using index_form_tuple with the proper description. Everything works fine, as long as the documents are quite small. However, if the tsvector becomes too large, I run into a problem of not being able to store the documents, because (obviously) the tsvector is too large for one page. Well, of course. I think this is a fundamentally bad index design. You didn't say exactly what sort of searches you want this index type to accelerate, but perhaps you need a design closer to GIN, in which you'd make index entries for individual words not whole documents. What I tried to solve this issue here, is to extract the words from the document (in my index) and calling 'Datum toast_compress_datum(Datum value)'in order to compress the tsvector into a proper toast table. Indexes don't have toast tables. 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] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
On Thu, Jun 24, 2010 at 10:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Wed, 2010-06-23 at 21:54 +, Robert Haas wrote: This adds four additional connection parameters to libpq: keepalives, keepalives_idle, keepalives_count, and keepalives_interval. keepalives default to on, per discussion, but can be turned off by specifying keepalives=0. The remaining parameters, where supported, can be used to adjust how often keepalives are sent and how many can be lost before the connection is broken. There isn't any need at at all for this. We can already add options on the libpq connection line. options = '-o tcp_keepalives_idle=X tcp_keepalives_interval=Y tcp_keepalives_count=Z' Huh? The above is 100% fanciful; there was no code in libpq or anywhere else that would have processed such a thing. You can do this: psql host=127.0.0.1 options='-c tcp_keepalives_idle=1' ...but it doesn't do the same thing as this patch. It lets you set the TCP keepalive parameters on the server side, whereas what this patch does is let you set them on the client side. Only setting them on the client side will allow the client to notice when the server has gone away. There is still an open question in my mind as to whether this is really an adequate solution to the walrecevier problem, but as you say, if it turns out not to be, it's got other value. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] EOL is when?
Magnus Hagander mag...@hagander.net writes: On Thu, Jun 24, 2010 at 15:39, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 24/06/10 14:41, Magnus Hagander wrote: Our versioning policy (http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy) says that we EOL 7.4 and 8.0 July 2010. Does that mean July 1st or July 31st? Probably means one more minor release, and that's it. Yes, but I care about what actual date we mean. A month is a lot of time :-) There is no actual date as yet. Whenever the next set of minor releases come out will be the last one for 7.4 and 8.0. We'll keep back-patching those branches as appropriate up till then. 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] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
On Jun 24, 2010, at 16:30 , Robert Haas wrote: On Thu, Jun 24, 2010 at 10:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Wed, 2010-06-23 at 21:54 +, Robert Haas wrote: This adds four additional connection parameters to libpq: keepalives, keepalives_idle, keepalives_count, and keepalives_interval. keepalives default to on, per discussion, but can be turned off by specifying keepalives=0. The remaining parameters, where supported, can be used to adjust how often keepalives are sent and how many can be lost before the connection is broken. There isn't any need at at all for this. We can already add options on the libpq connection line. options = '-o tcp_keepalives_idle=X tcp_keepalives_interval=Y tcp_keepalives_count=Z' Huh? The above is 100% fanciful; there was no code in libpq or anywhere else that would have processed such a thing. You can do this: psql host=127.0.0.1 options='-c tcp_keepalives_idle=1' Hm, seems a bit error-prone though. The difference between the above psql host=127.0.0.1 keepalives=1 isn't immediately obvious I'd say. Should we maybe rename the libpq-side parameters to tcp_client_keepalives, tcp_client_keepalives_idle, tcp_client_keepalives_count and tcp_client_keepalives_interval? Or do we expect people who fiddle with those parameters to understand the subtle difference? 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] TCP keepalive support for libpq
Greg Stark gsst...@mit.edu writes: I think it's going to be an uphill battle convincing TCP that we know better than the TCP spec about how aggressive it should be about throwing errors and killing connections. Once we have TCP keepalives set low enough -- assuming the OS will allow it to be set much lower -- we'll find that other timeouts are longer than we expect too. TCP Keepalives won't come into it at all if there is any unacked data pending -- TCP *will* detect that case but it might take longer than you want too and you won't be able to lower it. So it's a good thing that walreceiver never has to send anything after the initial handshake ... 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] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
On Thu, Jun 24, 2010 at 10:40 AM, Florian Pflug f...@phlo.org wrote: On Jun 24, 2010, at 16:30 , Robert Haas wrote: On Thu, Jun 24, 2010 at 10:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: There isn't any need at at all for this. We can already add options on the libpq connection line. options = '-o tcp_keepalives_idle=X tcp_keepalives_interval=Y tcp_keepalives_count=Z' Huh? The above is 100% fanciful; there was no code in libpq or anywhere else that would have processed such a thing. You can do this: psql host=127.0.0.1 options='-c tcp_keepalives_idle=1' Hm, seems a bit error-prone though. The difference between the above psql host=127.0.0.1 keepalives=1 isn't immediately obvious I'd say. Should we maybe rename the libpq-side parameters to tcp_client_keepalives, tcp_client_keepalives_idle, tcp_client_keepalives_count and tcp_client_keepalives_interval? Or do we expect people who fiddle with those parameters to understand the subtle difference? I think the existing names are fine - people should understand that options means server-side options and that anything else is a client-side option. However, if there's a strong consensus the other way and someone feels like working up a patch, that's fine too. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TOAST issue on custom index access method
Oh, thanks. I didn't know about index tables not having access to associated toast values. The index access method is (at least in the textual part) similar to GIN, however, I needed to do some changes to it. Saving the whole document is actually only important for vacuum. I think, I will find some workaround to solve this issue. However, it is a little bit strange, that I get toasted values (when inserting from another table) and untoasted values, if I insert items directly. Could anybody please explain this to me? Best regards Carsten Kropf Am 24.06.2010 um 16:20 schrieb Tom Lane: Carsten Kropf ckro...@fh-hof.de writes: I have a strange issue using a custom built index structure. My index access method support document type composed of words (as tsvector) and points (1-dimensional arrays of them). For internal reasons, I have to save the documents as a whole inside my structure (for proper reorganisations). So, I form the tuples using index_form_tuple with the proper description. Everything works fine, as long as the documents are quite small. However, if the tsvector becomes too large, I run into a problem of not being able to store the documents, because (obviously) the tsvector is too large for one page. Well, of course. I think this is a fundamentally bad index design. You didn't say exactly what sort of searches you want this index type to accelerate, but perhaps you need a design closer to GIN, in which you'd make index entries for individual words not whole documents. What I tried to solve this issue here, is to extract the words from the document (in my index) and calling 'Datum toast_compress_datum(Datum value)'in order to compress the tsvector into a proper toast table. Indexes don't have toast tables. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TOAST issue on custom index access method
Carsten Kropf ckro...@fh-hof.de writes: However, it is a little bit strange, that I get toasted values (when inserting from another table) and untoasted values, if I insert items directly. Could anybody please explain this to me? Huh? An index will never ever get passed an externally-toasted value. See the TOAST_INDEX_HACK code in indextuple.c. 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] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
Florian Pflug f...@phlo.org writes: On Jun 24, 2010, at 16:45 , Robert Haas wrote: I think the existing names are fine - people should understand that options means server-side options and that anything else is a client-side option. However, if there's a strong consensus the other way and someone feels like working up a patch, that's fine too. I'd volunteer to create the patch if people think renaming the libpq options is a good idea. I'm with Robert: the names are fine as-is. We've not had complaints about the libpq SSL parameters being confusingly like server-side SSL parameters, for instance. It might be a good idea to add a sentence to the documentation, though, just pointing out that these control client-side keepalive probes rather than server-side. 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] TOAST issue on custom index access method
Hmm, k, then I don't know exactly what happens. I have some values in my composite type. The size of the index tuple (if coming from one table and being copied to the target table) is about 15x bytes. Otherwise, if I insert the same tuple directly, it is about 12kb. So, I don't know exactly what happens here, unfortunately. Best regards Carsten Kropf Am 24.06.2010 um 17:08 schrieb Tom Lane: Carsten Kropf ckro...@fh-hof.de writes: However, it is a little bit strange, that I get toasted values (when inserting from another table) and untoasted values, if I insert items directly. Could anybody please explain this to me? Huh? An index will never ever get passed an externally-toasted value. See the TOAST_INDEX_HACK code in indextuple.c. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] EOL for 7.4 and 8.0
Hello, We need to make sure and send out multiple announcements of this. At least 2 during the month of July. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- 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] testing plpython3u on 9.0beta2
On Wed, Jun 23, 2010 at 10:49 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 23, 2010 at 10:30 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I can reproduce this, here. The problem seems to be that plpython only build either plpython2.so or plython3.so, but both languages expect a call handler called plython_call_handler. So once we load the shared library for one language, the other language just grabs the same call handler. We had better fix that --- I can definitely foresee installations wanting to use both plpython2 and plpython3. This'd require a change in the default contents of pg_pltemplate, though. Does it seem important enough to bump catversion for? Yeah, I think so. As for using both plpython2 and plpython3, it looks to me like right now you can only use one or the other. I think if you somehow manage to install both, you're really just getting the same one twice (I have not tested this, however). So, what's the right thing to do here? Should we just fix it so that creating the second language always fails, or should we try to make it possible for both of them to coexist (which is probably a lot more work)? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] EOL for 7.4 and 8.0
On 06/24/2010 09:04 AM, Joshua D. Drake wrote: Hello, We need to make sure and send out multiple announcements of this. At least 2 during the month of July. Ach. I drafted an annoucement ... did I ever send it out? If not, will do so immediately. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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
Re: [HACKERS] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
On Thu, 2010-06-24 at 11:15 -0400, Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Jun 24, 2010, at 16:45 , Robert Haas wrote: I think the existing names are fine - people should understand that options means server-side options and that anything else is a client-side option. However, if there's a strong consensus the other way and someone feels like working up a patch, that's fine too. I'd volunteer to create the patch if people think renaming the libpq options is a good idea. I'm with Robert: the names are fine as-is. We've not had complaints about the libpq SSL parameters being confusingly like server-side SSL parameters, for instance. It might be a good idea to add a sentence to the documentation, though, just pointing out that these control client-side keepalive probes rather than server-side. Yes please. I was confused; I think others will be also. -- Simon Riggs www.2ndQuadrant.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] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
On Thu, 2010-06-24 at 10:30 -0400, Robert Haas wrote: It lets you set the TCP keepalive parameters on the server side, whereas what this patch does is let you set them on the client side. Only setting them on the client side will allow the client to notice when the server has gone away. Thank you for explaining. Now I understand. It sort of begs the question: why need they be different? -- Simon Riggs www.2ndQuadrant.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] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
On Thu, Jun 24, 2010 at 1:40 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-06-24 at 10:30 -0400, Robert Haas wrote: It lets you set the TCP keepalive parameters on the server side, whereas what this patch does is let you set them on the client side. Only setting them on the client side will allow the client to notice when the server has gone away. Thank you for explaining. Now I understand. It sort of begs the question: why need they be different? In most cases, they probably don't; in fact, I suspect most people don't bother adjusting these parameters at all. Essentially, they just control the amount of time that can pass before you decide that the other guy is dead. And if the client thinks it's right to declare the server dead after X minutes, it's probably reasonable for the server to declare the client dead after X minutes, too. On the other hand, the client and server may be under different administrative control. What the DBA wants the database to do to avoid running out of connection slots might not be the same as what the application writer wants to do to detect when the database has crashed. For that exact reason, it's actually slightly strange that we make these parameters PGC_USERSET on the server side - but not strange enough that I can get excited about changing it. If an application writer wants to make trouble for the DBA, he certainly has better ways to do it than this... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] EOL for 7.4 and 8.0
On Thu, 2010-06-24 at 10:34 -0700, Josh Berkus wrote: On 06/24/2010 09:04 AM, Joshua D. Drake wrote: Hello, We need to make sure and send out multiple announcements of this. At least 2 during the month of July. Ach. I drafted an annoucement ... did I ever send it out? Not sure actually. I was just thinking that since it is only a month... You might want to mention the EOL of 8.1 in Nov too. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- 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] Adding XMLEXISTS to the grammar
Robert Haas wrote:
I usually troubleshoot things like this by setting a breakpoint in
elog_start or elog_finish. Then you can see where it's blowing up.
Off the top of my head, I would guess you've added a node type whose
structure definition doesn't begin with NodeTag, or else you've got a
memory clobber.
Thanks Robert, I've managed to resolve this make making a type cast
inside gram.y. However, it now seems that the function itself can not be
found. I've made an entry in pg_proc.h, but when running psql I'm
getting the following:
xmltest=# SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers'
PASSING BY REF data);
ERROR: function pg_catalog.xml_exists(text, xml) does not exist
LINE 1: SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' ...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
In gram.y I've got:
FuncCall *n = makeNode(FuncCall);
n-funcname = SystemFuncName(xml_exists);
(also tried SystemFuncName(xmlexists);)
In xml.h:
extern bool xml_exists(text *xpath_expr_text, xmltype *data);
I've also tried
bool xml_exists(PG_FUNCTION_ARGS) {
and finally in pg_proc.h I have:
DATA(insert OID = 3037 ( xmlexists PGNSP PGUID 12 1 0 0 f f f t f i
3 0 16 25 142 _null_ _null_ _null_ _null_ xml_exists _null_ _null_
_null_ ));
DESCR(evaluate XPath expression in a boolean context);
(also tried ( xml_exists PGNSP))
After each attempt, I've blown away the installation, made clean and
installed, initialised a fresh database and restored my sample database.
I've had a grep around using position and it's target function textpos
as examples but I fail to see any other file that they live in other
than their implementation. As far as I can tell, I'm not doing anything
different from position. Any thoughts?
Regards,
--
Mike Fowler
Registered Linux user: 379787
--
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] EOL for 7.4 and 8.0
On 6/24/10 11:03 AM, Joshua D. Drake wrote: On Thu, 2010-06-24 at 10:34 -0700, Josh Berkus wrote: On 06/24/2010 09:04 AM, Joshua D. Drake wrote: Hello, We need to make sure and send out multiple announcements of this. At least 2 during the month of July. Ach. I drafted an annoucement ... did I ever send it out? Not sure actually. I was just thinking that since it is only a month... You might want to mention the EOL of 8.1 in Nov too. Darn, looks like I didn't. Announcing now. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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
Re: [HACKERS] Adding XMLEXISTS to the grammar
On Thu, Jun 24, 2010 at 2:37 PM, Mike Fowler m...@mlfowler.com wrote:
Thanks Robert, I've managed to resolve this make making a type cast inside
gram.y. However, it now seems that the function itself can not be found.
I've made an entry in pg_proc.h, but when running psql I'm getting the
following:
xmltest=# SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers'
PASSING BY REF data);
ERROR: function pg_catalog.xml_exists(text, xml) does not exist
LINE 1: SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' ...
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
In gram.y I've got:
FuncCall *n = makeNode(FuncCall);
n-funcname = SystemFuncName(xml_exists);
(also tried SystemFuncName(xmlexists);)
In xml.h:
extern bool xml_exists(text *xpath_expr_text, xmltype *data);
I've also tried
bool xml_exists(PG_FUNCTION_ARGS) {
and finally in pg_proc.h I have:
DATA(insert OID = 3037 ( xmlexists PGNSP PGUID 12 1 0 0 f f f t f i 3 0
16 25 142 _null_ _null_ _null_ _null_ xml_exists _null_ _null_ _null_ ));
DESCR(evaluate XPath expression in a boolean context);
(also tried ( xml_exists PGNSP))
After each attempt, I've blown away the installation, made clean and
installed, initialised a fresh database and restored my sample database.
I've had a grep around using position and it's target function textpos as
examples but I fail to see any other file that they live in other than their
implementation. As far as I can tell, I'm not doing anything different from
position. Any thoughts?
It looks like the pg_proc entry is creating an SQL function called
xmlexists referencing a C function called xml_exists, and the gram.y
changes want there to be an SQL function called xml_exists. I think
you should rip out all the catalog and parser changes for starters,
and just try to get it working as a regular old function. Once you
have that working, you can add the syntax support back in. I'd
suggest making the C and SQL function names the same as each other,
but different from the keyword you're planning to use (xmlexists).
As for declaring the function, I believe you want this:
Datum
your_function_name(PG_FUNCTION_ARGS)
{
}
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
On Thu, Jun 24, 2010 at 1:38 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-06-24 at 11:15 -0400, Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Jun 24, 2010, at 16:45 , Robert Haas wrote: I think the existing names are fine - people should understand that options means server-side options and that anything else is a client-side option. However, if there's a strong consensus the other way and someone feels like working up a patch, that's fine too. I'd volunteer to create the patch if people think renaming the libpq options is a good idea. I'm with Robert: the names are fine as-is. We've not had complaints about the libpq SSL parameters being confusingly like server-side SSL parameters, for instance. It might be a good idea to add a sentence to the documentation, though, just pointing out that these control client-side keepalive probes rather than server-side. Yes please. I was confused; I think others will be also. Do either of you have any thoughts about where would be the best place to add such a sentence? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add TCP keepalive support to libpq.
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 24, 2010 at 1:38 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-06-24 at 11:15 -0400, Tom Lane wrote: It might be a good idea to add a sentence to the documentation, though, just pointing out that these control client-side keepalive probes rather than server-side. Yes please. I was confused; I think others will be also. Do either of you have any thoughts about where would be the best place to add such a sentence? In the hunk you added to libpq.sgml would be fine by 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] testing plpython3u on 9.0beta2
On ons, 2010-06-23 at 07:17 -0400, Robert Haas wrote: I can reproduce this, here. The problem seems to be that plpython only build either plpython2.so or plython3.so, but both languages expect a call handler called plython_call_handler. So once we load the shared library for one language, the other language just grabs the same call handler. The problem is apparently that when CREATE LANGUAGE creates a language from a pg_pltemplate entry, it creates the proname from the tmplhandler name, and if it finds a fitting proname entry already, it used that one. So when you create plpython2 first and plpython3 second, the pg_language entries of the latter point to the pg_proc entries of the former. If you fix that up manually (create additional pg_proc entries and fix the pg_language entries to point there), it works better. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
