[HACKERS] Passing tabular data around using python functions
Hi, I'm just trying to figure out what's possible with Postgresql and Python. One thing that's important for me, would be to pass result sets around to process them further. I have a table like this: create table fps ( docid integer, conceptid integer, rank float4 ) And the following function: create or replace function vectormatch(data fps[]) returns table(docid integer, weigth float4) as $$ plpy.notice(type(data)) plpy.notice(data) ... $$ language plpythonu; I call the function like this: select * from vectormatch(array(select (docid,conceptid,rank)::fps from fps where docid = 4205591)) and get the following output: NOTICE: type 'list' CONTEXT: PL/Python function vectormatch NOTICE: ['(4205591,1,1)', '(4205591,1219,1)', ...] CONTEXT: PL/Python function vectormatch I'm quite surprised that there are strings in the list and not tuples!? I tried my best, but I have no idea what I might be doing wrong. The main purpose of my sample/experiment is, to pass the results of a query to a function and to process it there. Any hint would be very appreciated. cheers, Achim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Hi guys, it is time to give another go to this patch. I would like to thank everyone for suggestions and ideas expressed through this list. We are happy that Part 0 of the patch has been committed (array_remove() and array_replace() functions), which will be useful in Part 2 (too early now to talk about it). Let's not rush though and focus on Part 1 of the patch. :) First, I would like to find a unique and general term for this feature. We started with Foreign keys with arrays and ended up with EACH foreign keys. Following Peter's suggestion, we will use the ELEMENT keyword (so that maybe in the future we can extend the usage). Our proposals are: * Array Foreign Key * Foreign Key Arrays * ELEMENT Foreign Keys * ... Which one is your favourite? Secondly, we have decided to split the patch we proposed back in March in two smaller patches. The most important goal of Part 1 is to find a generally accepted syntax. By removing ACTION handling from Part 1 (see limitations below), we believe that the community will be able to contribute more to driving future directions and requirements. Based on Peter's comments, we would like to propose the use of the ELEMENT keyword, rather than the EACH keyword proposed in March. You can find three examples at the bottom of this email. Finally, Part 1 of this patch will have these limitations: * Only one |ELEMENT| column allowed in a multi-column key (same as the proposed patch in March) * Supported actions|: * NO ACTION|| * RESTRICT| Cheers, Gabriele Example 1: inline usage CREATE TABLE drivers ( driver_id integer PRIMARY KEY, first_name text, last_name text, ... ); CREATE TABLE races ( race_id integer PRIMARY KEY, title text, race_day DATE, ... final_positions integer[] ELEMENT REFERENCES drivers ); Example 2: with FOREIGN KEY CREATE TABLE races ( race_id integer PRIMARY KEY, title text, race_day DATE, ... final_positions integer[], FOREIGN KEY (ELEMENT final_positions) REFERENCES drivers ); Example 3: with ALTER TABLE CREATE TABLE races ( race_id integer PRIMARY KEY, title text, race_day DATE, ... final_positions integer[] ); ALTER TABLE races ADD FOREIGN KEY (ELEMENT final_positions) REFERENCES drivers; -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
On 30 July 2012 16:12, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: * Array Foreign Key * Foreign Key Arrays * ELEMENT Foreign Keys * ... Which one is your favourite? Array Element Foreign Key -- 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] [PATCH] Support for foreign keys with arrays
On Mon, Jul 30, 2012 at 11:12 AM, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: Hi guys, it is time to give another go to this patch. I would like to thank everyone for suggestions and ideas expressed through this list. We are happy that Part 0 of the patch has been committed (array_remove() and array_replace() functions), which will be useful in Part 2 (too early now to talk about it). Let's not rush though and focus on Part 1 of the patch. :) First, I would like to find a unique and general term for this feature. We started with Foreign keys with arrays and ended up with EACH foreign keys. Following Peter's suggestion, we will use the ELEMENT keyword (so that maybe in the future we can extend the usage). Our proposals are: * Array Foreign Key * Foreign Key Arrays * ELEMENT Foreign Keys * ... Which one is your favourite? I think having the word element in there makes it a lot clearer. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] strange evaluation Window function and SRF functions?
Hello I seen nice trick based on window function http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql but isn't it example of wrong evaluation? Result of row_number is not correct Regards Pavel
Re: [HACKERS] strange evaluation Window function and SRF functions?
Pavel Stehule pavel.steh...@gmail.com writes: I seen nice trick based on window function http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql but isn't it example of wrong evaluation? Result of row_number is not correct Sure it is ... or at least, you won't find anything in the SQL spec that says it isn't. The result of a window function is only dependent on the state of the input, not on SRFs that might happen to be in sibling SELECT expressions. (This is one example of why SRFs in SELECT lists aren't terribly well defined.) A bigger problem with that query is that there's no guarantee it will preserve ordering of the elements of the arrays. 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] strange evaluation Window function and SRF functions?
On 30 July 2012 17:19, Pavel Stehule pavel.steh...@gmail.com wrote: Hello I seen nice trick based on window function http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql but isn't it example of wrong evaluation? Result of row_number is not correct Looks right to me. I guess the way to get the row_number they're after out of the result set would involve changing OVER () to OVER (ORDER BY unnest(myTextArrayColumn)) -- Thom
Re: [HACKERS] strange evaluation Window function and SRF functions?
On Jul 30, 2012, at 12:33, Thom Brown t...@linux.com wrote: On 30 July 2012 17:19, Pavel Stehule pavel.steh...@gmail.com wrote: Hello I seen nice trick based on window function http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql but isn't it example of wrong evaluation? Result of row_number is not correct Looks right to me. I guess the way to get the row_number they're after out of the result set would involve changing OVER () to OVER (ORDER BY unnest(myTextArrayColumn)) The better way would be to perform the unnest in a sub-select then attach the row number in the outer select. David J.
Re: [HACKERS] strange evaluation Window function and SRF functions?
2012/7/30 Thom Brown t...@linux.com On 30 July 2012 17:19, Pavel Stehule pavel.steh...@gmail.com wrote: Hello I seen nice trick based on window function http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql but isn't it example of wrong evaluation? Result of row_number is not correct Looks right to me. I guess the way to get the row_number they're after out of the result set would involve changing OVER () to OVER (ORDER BY unnest(myTextArrayColumn)) it looks like row_number is evaluated before SRF - this behave is absolutely undefined - for me - more native behave is different evaluation. Regards Pavel -- Thom
Re: [HACKERS] strange evaluation Window function and SRF functions?
it looks like row_number is evaluated before SRF - this behave is absolutely undefined - for me - more native behave is different evaluation. SRFs which return multiple rows in the SELECT clause have ALWAYS behaved oddly when it comes to row evaluation (LIMIT, COUNT(), etc.). This isn't necessarily desireable, but it is consistent with past releases, and it's not in any way limited to Windowing functions. In general, if you care about rows when calling such an SRF, you need to subselect it. It would be nice to clean that up, but you'd have to start with a comprehensive definition of what the behavior *should* be in all common cases. And then you'd be in for a big code overhaul. -- 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
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Excerpts from Simon Riggs's message of lun jul 30 11:21:46 -0400 2012: On 30 July 2012 16:12, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: * Array Foreign Key * Foreign Key Arrays * ELEMENT Foreign Keys * ... Which one is your favourite? Array Element Foreign Key I was going to say the same, except I had ELEMENT as a capitalized word in my mind (and in the docs it'd be within literal). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] strange evaluation Window function and SRF functions?
On Mon, Jul 30, 2012 at 11:47 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2012/7/30 Thom Brown t...@linux.com On 30 July 2012 17:19, Pavel Stehule pavel.steh...@gmail.com wrote: Hello I seen nice trick based on window function http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql but isn't it example of wrong evaluation? Result of row_number is not correct Looks right to me. I guess the way to get the row_number they're after out of the result set would involve changing OVER () to OVER (ORDER BY unnest(myTextArrayColumn)) it looks like row_number is evaluated before SRF - this behave is absolutely undefined - for me - more native behave is different evaluation. If it was me, I'd have expanded the array with generate_series (as with the undocumented information_schema._pg_expandarray) and stacked the array with array() not array_agg(). 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] [PATCH] Support for foreign keys with arrays
Il 30/07/12 19:11, Alvaro Herrera ha scritto: I was going to say the same, except I had ELEMENT as a capitalized word in my mind (and in the docs it'd be within literal). So: Array ELEMENT Foreign Key +1 for me And it can be also interchanged with Array element Foreign Key. -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- 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] strange evaluation Window function and SRF functions?
Josh Berkus j...@agliodbs.com writes: it looks like row_number is evaluated before SRF - this behave is absolutely undefined - for me - more native behave is different evaluation. SRFs which return multiple rows in the SELECT clause have ALWAYS behaved oddly when it comes to row evaluation (LIMIT, COUNT(), etc.). This isn't necessarily desireable, but it is consistent with past releases, and it's not in any way limited to Windowing functions. In general, if you care about rows when calling such an SRF, you need to subselect it. It would be nice to clean that up, but you'd have to start with a comprehensive definition of what the behavior *should* be in all common cases. And then you'd be in for a big code overhaul. And a lot of application code breakage, if you change the semantics at all. My feeling is that SRFs in targetlists are just fundamentally poorly defined, and the answer is to avoid them not try to make them cleaner. Most of the real use-cases for them could be handled in a better-defined, more standard way with LATERAL ... so what we ought to be spending time on is getting LATERAL done, not worrying about putting lipstick on tlist SRFs. 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] strange evaluation Window function and SRF functions?
On 07/30/2012 01:18 PM, Tom Lane wrote: My feeling is that SRFs in targetlists are just fundamentally poorly defined, and the answer is to avoid them not try to make them cleaner. Most of the real use-cases for them could be handled in a better-defined, more standard way with LATERAL ... so what we ought to be spending time on is getting LATERAL done, not worrying about putting lipstick on tlist SRFs. +1 LATERAL would be useful for all sorts of reasons anyway. 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] strange evaluation Window function and SRF functions?
2012/7/30 Tom Lane t...@sss.pgh.pa.us Josh Berkus j...@agliodbs.com writes: it looks like row_number is evaluated before SRF - this behave is absolutely undefined - for me - more native behave is different evaluation. SRFs which return multiple rows in the SELECT clause have ALWAYS behaved oddly when it comes to row evaluation (LIMIT, COUNT(), etc.). This isn't necessarily desireable, but it is consistent with past releases, and it's not in any way limited to Windowing functions. In general, if you care about rows when calling such an SRF, you need to subselect it. It would be nice to clean that up, but you'd have to start with a comprehensive definition of what the behavior *should* be in all common cases. And then you'd be in for a big code overhaul. And a lot of application code breakage, if you change the semantics at all. My feeling is that SRFs in targetlists are just fundamentally poorly defined, and the answer is to avoid them not try to make them cleaner. Most of the real use-cases for tihem could be handled in a better-defined, more standard way with LATERAL ... so what we ought to be spending time on is getting LATERAL done, not worrying about putting lipstick on tlist SRFs. I don't propose any changes - I would to show interesting/strange usage of SRF - this is a new use case of old issue - and I agree so we need LATERAL more and early. Regards Pavel 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] tzdata2012d
Folks, I just noticed that there's yet another change to time zones: http://www.iana.org/time-zones Can we slide this into the upcoming point release? When would that be? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] cataloguing NOT NULL constraints
Hello, Just over a year ago, I posted a patch (based on a previous patch by Bernd Helmle) that attempted to add pg_constraint rows for NOT NULL constraints. http://archives.postgresql.org/message-id/20110707213401.ga27...@alvh.no-ip.org That patch was rather long and complex, as it tried to handle all the hairy issues directly with a completely new 'contype' value for NOT NULL constraints; so the code had to deal with inheritance of constraints, pg_dump issues, and a lot of nitty-gritty. In the end it was killed by a simple realization of Peter Eisentraut's: Why not just transform these into the equivalent CHECK constraints instead? That ended up being discussing at length, and this patch, much smaller than the previous one, is an attempt to do things that way. This patch is not final yet, because there are some issues still open; but the interesting stuff already works. Simply declaring a column as NOT NULL creates a CHECK pg_constraint row; similarly, declaring a CHECK (foo IS NOT NULL) constraint sets the pg_attribute.attnotnull flag. If you create a child table, the NOT NULL constraint will be inherited. One thing that might be of interest is that we accumulate names of not-nullable columns during parse analysis if they can't be dealt with immediately; later, MergeAttributes is in charge of walking that list to determine which columns need to have is_not_null set. This is a bit ugly but necessary: consider the following: CREATE TABLE foo (CHECK (a IS NOT NULL), a INT); At the point where the CHECK is processed, there is not yet any ColumnDef node to set is_not_null to. Also CREATE TABLE foo (a INT); CREATE TABLE bar (CHECK (a IS NOT NULL)) INHERITS (foo); Same thing. We also handle this correctly: CREATE TABLE foo (a INT, b INT CHECK (a IS NOT NULL)); i.e. the NOT NULL check is declared on the wrong column (this last command is not actually standard SQL, because column constraints are supposed to apply only to the current column; but we take it anyway.) Another thing is that pg_dump now reads attnotnull as always false for 9.3 servers, hoping that there will be a corresponding CHECK constraint. I think this is okay, because a missing CHECK constraint means that somebody has been messing with the catalogs and so if it bombs out it's not our fault. But if somebody opines differently let me know. Another point to keep in mind is that I haven't touched syntax definitions. This means that ALTER TABLE / SET NOT NULL does not let you specify a constaint name, so you get an auto-generated name. I think this is okay; if you want a different name, use ALTER TABLE / ADD CONSTRAINT instead. If you do CREATE TABLE foo (a INT NOT NULL, CHECK (a IS NOT NULL)) you get two constraints. Some of the open items here: * declaring CHECK (foo IS NOT NULL) NO INHERIT doesn't work (i.e. the constraint is inherited) * declaring CHECK (foo IS NOT NUL) NOT VALID doesn't work (i.e. the constraint is tested on existing rows). * I've only handled raw_expr, not cooked_expr, in ColumnDef. I think this means that stuff such as CREATE TABLE AS and CREATE TABLE LIKE don't work. Haven't tested that yet. * the information_schema needs updating (mainly to remove some UNION branches, I think) * Haven't looked at domains. -- Álvaro Herrera alvhe...@commandprompt.com catalog-notnull-2.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] tzdata2012d
Excerpts from David Fetter's message of lun jul 30 17:27:46 -0400 2012: Folks, I just noticed that there's yet another change to time zones: http://www.iana.org/time-zones Can we slide this into the upcoming point release? When would that be? Usually, Tom installs the latest timezone data just before each point release. Updating it each time IANA releases a new one would just be unnecessary churn, I think. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tzdata2012d
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from David Fetter's message of lun jul 30 17:27:46 -0400 2012: Can we slide this into the upcoming point release? When would that be? Usually, Tom installs the latest timezone data just before each point release. It's part of the release checklist. In practice, people who need the latest TZ data shouldn't be relying on our copy anyway. I think pretty much all distros build with --with-system-tzdata so that the distros' own updates of tzdata get picked up automatically by Postgres. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [patch] libpq one-row-at-a-time API
Hey, this thread was pointed out to me just a few days ago, but I'll start by saying that I think this thread is on exactly the right track. I don't like the callback API, and think that PQsetSingleRowMode should be offered in place of it. But I do have one On Sat, Jun 16, 2012 at 10:22 AM, Marko Kreen mark...@gmail.com wrote: The function can be called only after PQsend* and before any rows have arrived. This guarantees there will be no surprises to PQexec* users who expect full resultset at once. Ok, I'm guessing you mean that before you call PQgetResult or PQgetRowData, or maybe before you call PQgetResult or PQgetRowData and it returns a result or partial result.Because it would be a race condition if you meant exactly what you said. (Though I don't understand how this could possibly be implemented without some source of concurrency, which libpq doesn't do.) Maybe this is a little overly pendantic, but I do want to confirm the intention here. One other possibility, Tom Lane fretted ever so slightly about the use of malloc/free per row... what about instead of PQsetSingleRowMode, you have PQsetChunkedRowMode that takes a chunkSize parameter. A chunkSize = 0 would be equivalent to what we have today, a chunkSize of 1 means you get what you have from PQsetSingleRowMode, and larger chunkSizes would wait until n rows have been received before returning them all in a single result. I don't know that this suggestion is all that important, but it seems like an obvious generalization that might possibly be useful. Best, Leon
Re: [HACKERS] [patch] libpq one-row-at-a-time API
On 7/30/2012 8:11 PM, Leon Smith wrote: One other possibility, Tom Lane fretted ever so slightly about the use of malloc/free per row... what about instead of PQsetSingleRowMode, you have PQsetChunkedRowMode that takes a chunkSize parameter. A chunkSize = 0 would be equivalent to what we have today, a chunkSize of 1 means you get what you have from PQsetSingleRowMode, and larger chunkSizes would wait until n rows have been received before returning them all in a single result. I don't know that this suggestion is all that important, but it seems like an obvious generalization that might possibly be useful. It is questionable if that actually adds any useful functionality. Any collecting of multiple rows will only run the risk to stall receiving the following rows while processing this batch. Processing each row as soon as it is available will ensure making most use network buffers. Collecting multiple rows, like in the FETCH command for cursors does, makes sense when each batch introduces a network round trip, like for the FETCH command. But does it make any sense for a true streaming mode, like what is discussed here? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [patch] libpq one-row-at-a-time API
On Mon, Jul 30, 2012 at 9:59 PM, Jan Wieck janwi...@yahoo.com wrote: On 7/30/2012 8:11 PM, Leon Smith wrote: One other possibility, Tom Lane fretted ever so slightly about the use of malloc/free per row... what about instead of PQsetSingleRowMode, you have PQsetChunkedRowMode that takes a chunkSize parameter. A chunkSize = 0 would be equivalent to what we have today, a chunkSize of 1 means you get what you have from PQsetSingleRowMode, and larger chunkSizes would wait until n rows have been received before returning them all in a single result. I don't know that this suggestion is all that important, but it seems like an obvious generalization that might possibly be useful. It is questionable if that actually adds any useful functionality. This is true, I'm not sure my suggestion is necessarily useful. I'm just throwing it out there. Any collecting of multiple rows will only run the risk to stall receiving the following rows while processing this batch. Processing each row as soon as it is available will ensure making most use network buffers. This is not necessarily true, on multiple levels. I mean, some of the programs I write are highly concurrent, and this form of batching would have almost no risk of stalling the network buffer.And the possible use case would be when you are dealing with very small rows, when there would typically be several rows inside a single network packet or network buffer. Collecting multiple rows, like in the FETCH command for cursors does, makes sense when each batch introduces a network round trip, like for the FETCH command. But does it make any sense for a true streaming mode, like what is discussed here? Maybe?I mean, I anticipate that there are (probably) still use cases for FETCH, even when the row-at-a-time interface is a viable option and the transport between postgres and the client has reasonable flow-control. Leon
Re: [HACKERS] [patch] libpq one-row-at-a-time API
On 7/30/2012 10:31 PM, Leon Smith wrote: This is not necessarily true, on multiple levels. I mean, some of the programs I write are highly concurrent, and this form of batching would have almost no risk of stalling the network buffer.And the possible use case would be when you are dealing with very small rows, when there would typically be several rows inside a single network packet or network buffer. With highly concurrent you mean multi-threaded? Like one thread reads the rows in batches and pushes them into a queue while another thread processes them from that queue? If that is the case, then you just added a useless layer of buffering and the need for thread/thread context switches to PQsetSingleRowMode. Libpq's receiver thread is the kernel itself. Libpq tries to never read partial kernel buffers already. It always makes sure that there are at least 8K of free space in the inBuffer. In the case you describe above, where several rows fit into a single packet, libpq will receive them with a single system call in one read(2), then the application can get them as fast as possible, without causing any further context switches because they are already in the inBuffer. I've written that sort of code myself in the past. Look at the Slony worker thread prior to 2.2. We switched to the COPY protocol instead of waiting for the single row mode and got rid of all that extra buffering already (and then some more). Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [patch] pg_stat_lwlocks view - lwlocks statistics
Hi,I was doing patch review for patch of pg_stat_lwlocks view - lwlocks statistics. https://commitfest.postgresql.org/action/patch_view?id=885 The mail for the patch work is at: http://archives.postgresql.org/pgsql-hackers/2012-06/msg01518.php Following the steps on http://wiki.postgresql.org/wiki/Reviewing_a_Patch, the review is below: 1. submission review The patch is in standard git diff format. The patch applies with git repo master branch, in commit 8a504a363925fc5c7af48cd723da3f7e4d7ba9e2. I applied back the patch with command patch -p1 pg_stat_lwlocks_20120626.diff. The patch applies back cleanly. There is no test file or doc patch, as I didn't find any. 2. Usability Review I run queries to select from pg_stat_lwlocks and function pg_stat_get_lwlocks(), and they return the same results. And after running select from pg_stat_reset_lwlocks(), all the data seems to reset. So the stats relation and functions are working. 3. Code format Looking at the patch file, the code format looks nice. Also some basic comments inside. About the code quality, performance review and further bebugging, I'm not sure how should I do the testing Any suggestion? Any comment?Thanks. Best RegardsHuang Qi VictorComputer Science of National University of Singapore pg_stat_lwlocks_20120626.diff 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
