Re: [HACKERS] GROUPING SETS revisited
Hello 2010/8/3 Joshua Tolley eggyk...@gmail.com: In case anyone's interested, I've taken the CTE-based grouping sets patch from [1] and made it apply to 9.1, attached. I haven't yet done things like checked it for whitespace consistency, style conformity, or anything else, but (tuits permitting) hope to figure out how it works and get it closer to commitability in some upcoming commitfest. I mention it here so that if someone else is working on it, we can avoid duplicated effort, and to see if a CTE-based grouping sets implementation is really the way we think we want to go. I am plaing with it now :). I have a plan to replace CTE with similar but explicit executor node. The main issue of existing patch was using just transformation to CTE. I agree, so it isn't too much extensiable in future. Now I am cleaning identifiers little bit. Any colaboration is welcome. My plan: 1) clean CTE patch 2) replace CTE with explicit executor node, but still based on tuplestore 3) when will be possible parallel processing based on hash agg - then we don't need to use tuplestore comments?? Regards Pavel [1] http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkxXrggACgkQRiRfCGf1UMMlCQCglaIdtPj8Qe6G60V2LHn5pFNn kgIAniXRgIQEbVrK/eDVZnmKCzw33lT9 =XVVV -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] review: psql: edit function, show function commands patch
attached updated patch * don't use a default option for navigation in editor - user have to set this option explicitly * name for this psql variable is EDITOR_LINENUMBER_SWITCH - * updated comments, doc and some issues described by Robert Regards Pavel Stehule 2010/8/3 Robert Haas robertmh...@gmail.com: On Sun, Aug 1, 2010 at 11:48 PM, Robert Haas robertmh...@gmail.com wrote: b) more robust algorithm for header rows identification Have not gotten to this one yet. I notIce that on WIN32 the default editor is notepad.exe and the default editor navigation option is /. Does notepad.exe /lineno filename actually work on Windows? A quick Google search suggests that the answer is no, which seems somewhat unfortunate: it means we'd be shipping broken on Win32 out of the box. http://www.robvanderwoude.com/commandlineswitches.php#Notepad This is actually my biggest concern about this patch - that it may be just too much of a hassle to actually make it work for people. I just tried setting $EDITOR to MacOS's TextEdit program, and it turns out that TextEdit doesn't understand +. I'm afraid that we're going to end up with a situation where it only works for people using emacs or vi, and everyone else ends up with a broken install (and, possibly, no clear idea how to fix it). Perhaps it would be better to accept \sf and reject \sf+ and \ef func lineno. Assuming we get past that threshold issue, I'm also wondering whether the navigation option terminology is best; e.g. set PSQL_EDITOR_NAVIGATION_OPTION to configure it. It doesn't seem terrible, but it doesn't seem great, either. Anyone have a better idea? The docs are a little rough; they could benefit from some editing by a fluent English speaker. If anyone has time to work on this, it would be much appreciated. Instead of line number is unacceptable, I think you should write invalid line number. dollar should not be spelled dolar. function should not be spelled finction. This change looks suspiciously like magic. If it's actually safe, it needs a comment explaining why: - sys = pg_malloc(strlen(editorName) + strlen(fname) + 10 + 1); + sys = pg_malloc(strlen(editorName) + strlen(fname) + 20 + 1); Attempting to compile with this patch applied emits a warning on my machine; whether or not the warning is valid, you need to make it go away: command.c: In function ‘HandleSlashCmds’: command.c:1055: warning: ‘bsep’ may be used uninitialized in this function command.c:1055: note: ‘bsep’ was declared here Why does the \sf output have a trailing blank line? This seems weird, especially because \ef puts no such trailing blank line in the editor. Instead of: + /* skip useles whitespaces */ + while (c = func) + if (isblank(*c)) + c--; + else + break; ...wouldn't it be just as good to write: while (c = func isblank(*c)) c--; (and similarly elsewhere) In extract_separator, if you invert the sense of the first if-test, then you can avoid having to indent the entire function contents. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company *** ./doc/src/sgml/ref/psql-ref.sgml.orig 2010-08-03 09:00:48.384710383 +0200 --- ./doc/src/sgml/ref/psql-ref.sgml 2010-08-03 10:44:57.312835131 +0200 *** *** 1339,1345 varlistentry ! termliteral\edit/literal (or literal\e/literal) literaloptional replaceable class=parameterfilename/replaceable /optional/literal/term listitem para --- 1339,1345 varlistentry ! termliteral\edit/literal (or literal\e/literal) literaloptional replaceable class=parameterfilename/replaceable /optional optional linenumber /optional/literal/term listitem para *** *** 1369,1380 systems, filenamenotepad.exe/filename on Windows systems. /para /tip /listitem /varlistentry varlistentry ! termliteral\ef optional replaceable class=parameterfunction_description/replaceable /optional/literal/term listitem para --- 1369,1387 systems, filenamenotepad.exe/filename on Windows systems. /para /tip + + para + If replaceable class=parameterlinenumber/replaceable is + specified, then cursor is moved on this line after start of + editor (The psql's variable varnameEDITOR_LINENUMBER_SWITCH/varname + have to be filled). + /para /listitem /varlistentry varlistentry ! termliteral\ef optional replaceable class=parameterfunction_description/replaceable /optional optional linenumber /optional /literal/term listitem para *** *** 1397,1402 --- 1404,1417 If no function is specified, a
Re: [HACKERS] review: psql: edit function, show function commands patch
Hello 2010/8/3 Pavel Stehule pavel.steh...@gmail.com: attached updated patch * don't use a default option for navigation in editor - user have to set this option explicitly * name for this psql variable is EDITOR_LINENUMBER_SWITCH - * updated comments, doc and some issues described by Robert Regards Pavel Stehule I found a small bug - the last patch better handle parsing lineno after function descriptor Regards Pavel 2010/8/3 Robert Haas robertmh...@gmail.com: On Sun, Aug 1, 2010 at 11:48 PM, Robert Haas robertmh...@gmail.com wrote: b) more robust algorithm for header rows identification Have not gotten to this one yet. I notIce that on WIN32 the default editor is notepad.exe and the default editor navigation option is /. Does notepad.exe /lineno filename actually work on Windows? A quick Google search suggests that the answer is no, which seems somewhat unfortunate: it means we'd be shipping broken on Win32 out of the box. http://www.robvanderwoude.com/commandlineswitches.php#Notepad This is actually my biggest concern about this patch - that it may be just too much of a hassle to actually make it work for people. I just tried setting $EDITOR to MacOS's TextEdit program, and it turns out that TextEdit doesn't understand +. I'm afraid that we're going to end up with a situation where it only works for people using emacs or vi, and everyone else ends up with a broken install (and, possibly, no clear idea how to fix it). Perhaps it would be better to accept \sf and reject \sf+ and \ef func lineno. Assuming we get past that threshold issue, I'm also wondering whether the navigation option terminology is best; e.g. set PSQL_EDITOR_NAVIGATION_OPTION to configure it. It doesn't seem terrible, but it doesn't seem great, either. Anyone have a better idea? The docs are a little rough; they could benefit from some editing by a fluent English speaker. If anyone has time to work on this, it would be much appreciated. Instead of line number is unacceptable, I think you should write invalid line number. dollar should not be spelled dolar. function should not be spelled finction. This change looks suspiciously like magic. If it's actually safe, it needs a comment explaining why: - sys = pg_malloc(strlen(editorName) + strlen(fname) + 10 + 1); + sys = pg_malloc(strlen(editorName) + strlen(fname) + 20 + 1); Attempting to compile with this patch applied emits a warning on my machine; whether or not the warning is valid, you need to make it go away: command.c: In function ‘HandleSlashCmds’: command.c:1055: warning: ‘bsep’ may be used uninitialized in this function command.c:1055: note: ‘bsep’ was declared here Why does the \sf output have a trailing blank line? This seems weird, especially because \ef puts no such trailing blank line in the editor. Instead of: + /* skip useles whitespaces */ + while (c = func) + if (isblank(*c)) + c--; + else + break; ...wouldn't it be just as good to write: while (c = func isblank(*c)) c--; (and similarly elsewhere) In extract_separator, if you invert the sense of the first if-test, then you can avoid having to indent the entire function contents. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company *** ./doc/src/sgml/ref/psql-ref.sgml.orig 2010-08-03 09:00:48.384710383 +0200 --- ./doc/src/sgml/ref/psql-ref.sgml 2010-08-03 10:44:57.312835131 +0200 *** *** 1339,1345 varlistentry ! termliteral\edit/literal (or literal\e/literal) literaloptional replaceable class=parameterfilename/replaceable /optional/literal/term listitem para --- 1339,1345 varlistentry ! termliteral\edit/literal (or literal\e/literal) literaloptional replaceable class=parameterfilename/replaceable /optional optional linenumber /optional/literal/term listitem para *** *** 1369,1380 systems, filenamenotepad.exe/filename on Windows systems. /para /tip /listitem /varlistentry varlistentry ! termliteral\ef optional replaceable class=parameterfunction_description/replaceable /optional/literal/term listitem para --- 1369,1387 systems, filenamenotepad.exe/filename on Windows systems. /para /tip + + para + If replaceable class=parameterlinenumber/replaceable is + specified, then cursor is moved on this line after start of + editor (The psql's variable varnameEDITOR_LINENUMBER_SWITCH/varname + have to be filled). + /para /listitem /varlistentry varlistentry ! termliteral\ef optional replaceable class=parameterfunction_description/replaceable /optional
Re: [HACKERS] review: psql: edit function, show function commands patch
Hello I hope so I found and fixed last issue - the longer functions was showed directly - without a pager. Regards Pavel 2010/8/3 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/8/3 Pavel Stehule pavel.steh...@gmail.com: attached updated patch * don't use a default option for navigation in editor - user have to set this option explicitly * name for this psql variable is EDITOR_LINENUMBER_SWITCH - * updated comments, doc and some issues described by Robert Regards Pavel Stehule I found a small bug - the last patch better handle parsing lineno after function descriptor Regards Pavel 2010/8/3 Robert Haas robertmh...@gmail.com: On Sun, Aug 1, 2010 at 11:48 PM, Robert Haas robertmh...@gmail.com wrote: b) more robust algorithm for header rows identification Have not gotten to this one yet. I notIce that on WIN32 the default editor is notepad.exe and the default editor navigation option is /. Does notepad.exe /lineno filename actually work on Windows? A quick Google search suggests that the answer is no, which seems somewhat unfortunate: it means we'd be shipping broken on Win32 out of the box. http://www.robvanderwoude.com/commandlineswitches.php#Notepad This is actually my biggest concern about this patch - that it may be just too much of a hassle to actually make it work for people. I just tried setting $EDITOR to MacOS's TextEdit program, and it turns out that TextEdit doesn't understand +. I'm afraid that we're going to end up with a situation where it only works for people using emacs or vi, and everyone else ends up with a broken install (and, possibly, no clear idea how to fix it). Perhaps it would be better to accept \sf and reject \sf+ and \ef func lineno. Assuming we get past that threshold issue, I'm also wondering whether the navigation option terminology is best; e.g. set PSQL_EDITOR_NAVIGATION_OPTION to configure it. It doesn't seem terrible, but it doesn't seem great, either. Anyone have a better idea? The docs are a little rough; they could benefit from some editing by a fluent English speaker. If anyone has time to work on this, it would be much appreciated. Instead of line number is unacceptable, I think you should write invalid line number. dollar should not be spelled dolar. function should not be spelled finction. This change looks suspiciously like magic. If it's actually safe, it needs a comment explaining why: - sys = pg_malloc(strlen(editorName) + strlen(fname) + 10 + 1); + sys = pg_malloc(strlen(editorName) + strlen(fname) + 20 + 1); Attempting to compile with this patch applied emits a warning on my machine; whether or not the warning is valid, you need to make it go away: command.c: In function ‘HandleSlashCmds’: command.c:1055: warning: ‘bsep’ may be used uninitialized in this function command.c:1055: note: ‘bsep’ was declared here Why does the \sf output have a trailing blank line? This seems weird, especially because \ef puts no such trailing blank line in the editor. Instead of: + /* skip useles whitespaces */ + while (c = func) + if (isblank(*c)) + c--; + else + break; ...wouldn't it be just as good to write: while (c = func isblank(*c)) c--; (and similarly elsewhere) In extract_separator, if you invert the sense of the first if-test, then you can avoid having to indent the entire function contents. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company *** ./doc/src/sgml/ref/psql-ref.sgml.orig 2010-08-03 09:00:48.384710383 +0200 --- ./doc/src/sgml/ref/psql-ref.sgml 2010-08-03 10:44:57.312835131 +0200 *** *** 1339,1345 varlistentry ! termliteral\edit/literal (or literal\e/literal) literaloptional replaceable class=parameterfilename/replaceable /optional/literal/term listitem para --- 1339,1345 varlistentry ! termliteral\edit/literal (or literal\e/literal) literaloptional replaceable class=parameterfilename/replaceable /optional optional linenumber /optional/literal/term listitem para *** *** 1369,1380 systems, filenamenotepad.exe/filename on Windows systems. /para /tip /listitem /varlistentry varlistentry ! termliteral\ef optional replaceable class=parameterfunction_description/replaceable /optional/literal/term listitem para --- 1369,1387 systems, filenamenotepad.exe/filename on Windows systems. /para /tip + + para + If replaceable class=parameterlinenumber/replaceable is + specified, then cursor is moved on this line after start of + editor (The psql's variable varnameEDITOR_LINENUMBER_SWITCH/varname + have to be filled). +
Re: [HACKERS] GROUPING SETS revisited
2010/8/3 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/8/3 Joshua Tolley eggyk...@gmail.com: In case anyone's interested, I've taken the CTE-based grouping sets patch from [1] and made it apply to 9.1, attached. I haven't yet done things like checked it for whitespace consistency, style conformity, or anything else, but (tuits permitting) hope to figure out how it works and get it closer to commitability in some upcoming commitfest. I mention it here so that if someone else is working on it, we can avoid duplicated effort, and to see if a CTE-based grouping sets implementation is really the way we think we want to go. I am plaing with it now :). I have a plan to replace CTE with similar but explicit executor node. The main issue of existing patch was using just transformation to CTE. I agree, so it isn't too much extensiable in future. Now I am cleaning identifiers little bit. Any colaboration is welcome. My plan: 1) clean CTE patch 2) replace CTE with explicit executor node, but still based on tuplestore 3) when will be possible parallel processing based on hash agg - then we don't need to use tuplestore Couldn't you explain what exactly explicit executor node? I hope we can share your image to develop it further than only transformation to CTE. Regards, -- Hitoshi Harada -- 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] GROUPING SETS revisited
2010/8/3 Hitoshi Harada umi.tan...@gmail.com: 2010/8/3 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/8/3 Joshua Tolley eggyk...@gmail.com: In case anyone's interested, I've taken the CTE-based grouping sets patch from [1] and made it apply to 9.1, attached. I haven't yet done things like checked it for whitespace consistency, style conformity, or anything else, but (tuits permitting) hope to figure out how it works and get it closer to commitability in some upcoming commitfest. I mention it here so that if someone else is working on it, we can avoid duplicated effort, and to see if a CTE-based grouping sets implementation is really the way we think we want to go. I am plaing with it now :). I have a plan to replace CTE with similar but explicit executor node. The main issue of existing patch was using just transformation to CTE. I agree, so it isn't too much extensiable in future. Now I am cleaning identifiers little bit. Any colaboration is welcome. My plan: 1) clean CTE patch 2) replace CTE with explicit executor node, but still based on tuplestore 3) when will be possible parallel processing based on hash agg - then we don't need to use tuplestore Couldn't you explain what exactly explicit executor node? I hope we can share your image to develop it further than only transformation to CTE. I have a one reason Implementation based on CTE doesn't create space for possible optimalisations (I think now, maybe it isn't true). It is good for initial or referencial implementation - but it can be too complex, when we will try to append some optimalizations - like parallel hash agg processing, direct data reading without tuplestore. If you are, as CTE author, thinking so these features are possible in non recursive CTE too, I am not agains. I hope so this week I'll have a CTE based patch - and we can talk about next direction. I see as possible performance issue using a tuplestore - there are lot of cases where repeating of source query can be faster. If I remember well, Tom has a objection, so transformation to CTE is too early - in parser. So It will be first change. Executor node can be CTE. regards Pavel p.s. I am sure, so there are lot of task, that can be solved together with non recursive CTE. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Develop item from TODO list
Hello! We are 2 Students from the Technical University of Vienna. At our internship we would like to develop the item of the TODO list: Allow SET CONSTRAINTS to be qualified by schema/table name. Is anyone working on it? Our research at the SET CONTRAINTS function showed the following Error: *ERROR: cross-database references are not implemented: schema.table.constraint* *SQL state: 0A000* Is this the problem we could implement? Approximately where is the point to implement? As we have few experience with open-source programs, can someone recommend a good C-editor for Ubuntu? What do others use? Thanks in advance, Chris Viktor
Re: [HACKERS] Develop item from TODO list
On 3 August 2010 13:57, Viktor Valy vili0...@gmail.com wrote: Hello! We are 2 Students from the Technical University of Vienna. At our internship we would like to develop the item of the TODO list: Allow SET CONSTRAINTS to be qualified by schema/table name. Is anyone working on it? Our research at the SET CONTRAINTS function showed the following Error: ERROR: cross-database references are not implemented: schema.table.constraint SQL state: 0A000 Is this the problem we could implement? Approximately where is the point to implement? As we have few experience with open-source programs, can someone recommend a good C-editor for Ubuntu? What do others use? Thanks in advance, Chris Viktor I can't answer the main question, but you can try Anjuta as a C editor: http://www.anjuta.org/ -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Develop item from TODO list
Viktor Valy vili0...@gmail.com writes: We are 2 Students from the Technical University of Vienna. At our internship we would like to develop the item of the TODO list: Allow SET CONSTRAINTS to be qualified by schema/table name. Is anyone working on it? Uh, it was done years ago, AFAICS, unless the Todo entry means something non-obvious. regression=# create schema foo; CREATE SCHEMA regression=# create table foo.bar (f1 int unique deferrable); NOTICE: CREATE TABLE / UNIQUE will create implicit index bar_f1_key for table bar CREATE TABLE regression=# set constraints foo.bar_f1_key deferred; SET CONSTRAINTS regression=# set constraints foo.bar_f1_key immediate; SET CONSTRAINTS regression=# Bruce, do you remember what that entry was really about? 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] Develop item from TODO list
On Tue, Aug 3, 2010 at 9:01 AM, Thom Brown t...@linux.com wrote: I can't answer the main question, but you can try Anjuta as a C editor: http://www.anjuta.org/ Or vi. -- 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] review: xml_is_well_formed
On lör, 2010-07-31 at 13:40 -0400, Robert Haas wrote: Well-formedness should probably only allow XML documents. I think the point of this function is to determine whether a cast to xml will throw an error. The behavior should probably match exactly whatever test would be applied there. Maybe there should be xml_is_well_formed() xml_is_well_formed_document() xml_is_well_formed_content() I agree that consistency with SQL/XML is desirable, but for someone coming from the outside, the unqualified claim that 'foo' is well-formed XML might sound suspicious. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch for check constraints using multiple inheritance
Robert Haas wrote: On Mon, Aug 2, 2010 at 2:56 PM, Yeb Havinga yebhavi...@gmail.com wrote: Hence the ATOneLevelRecursion routing is usable in its current form for all callers during the prep stage, and not only ATPrepAddColumn. Well, only if they happen to want the visit each table only once behavior, which might not be true for every command type. It is actually visit each table only once for each distinct parent. Looking at the command types for ALTER TABLE, I see none where this behaviour would be incorrect. That put aside, the top1/top2 example is interesting in the sense that it reveals other problems besides the wrong attinhcount at the basement. For an example see the script below. The underlying cause is the failure of the code to recognize that if relation C inherits from both A and B, where A and B both have column x, that A.x 'is the same as' B.x, where the 'is the same as' relation is the same that holds for (A.x, C.x) and (B.x, C.x), which the code does a lot of trouble for to recognize. This means that if some definition is altered on A.x, only C.x is updated and B.x not touched. IMO this is wrong and either a multiple inheritance structure like this should be prohibited, since the user did not explicitly declare that A.x and B.x 'are the same' (by e.g. defining a relation D.x and have A and B inherit from that), or the code should update parents of relations when the childs are updated. The difficulty is in exactly specifying the 'is the same' as relation, i.e. under what conditions are columns A.x and B.x allowed to be merged to C.x. In the regression test there's only a small amount of tests, but one of them shows that the 'is the same' as relation does not mean everything is the same, as it shows that default values may differ. regards, Yeb Havinga DROP SCHEMA IF EXISTS test_inheritance CASCADE; CREATE SCHEMA test_inheritance; SET search_path TO test_inheritance; CREATE TABLE top1 (i int); CREATE TABLE top2 (i int); CREATE TABLE bottom () INHERITS (top1, top2); CREATE TABLE basement () INHERITS (bottom); ALTER TABLE top1 ADD COLUMN a_table_column INTEGER; ALTER TABLE top2 ADD COLUMN a_table_column INTEGER; SELECT t.oid, t.relname, a.attinhcount, a.attname FROM pg_class t JOIN pg_attribute a ON (a.attrelid = t.oid) JOIN pg_namespace n ON (t.relnamespace = n.oid) WHERE n.nspname = 'test_inheritance' AND a.attname LIKE '%table_column%' ORDER BY oid; ALTER TABLE top1 RENAME COLUMN a_table_column TO another_table_column; SELECT t.oid, t.relname, a.attinhcount, a.attname FROM pg_class t JOIN pg_attribute a ON (a.attrelid = t.oid) JOIN pg_namespace n ON (t.relnamespace = n.oid) WHERE n.nspname = 'test_inheritance' AND a.attname LIKE '%table_column%' ORDER BY oid; ALTER TABLE top2 RENAME COLUMN a_table_column TO another_table_column; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Too problems when using Postgresql8.3.7,Please help!
1.To add live HA to PG, I transfer WAL of a database instance(Primary node) to another database instance (standby node) at real time, and keep startup alive in standby node to recovery WAL online,so that standby node can be a hot standby.But I got some trouble. When standby node switch to primary mode to accept connections, of course after startup initilize the WAL and exit, the postgres process ereport(ERROR when mdread function enter ERROR branch, and I got message like this could not read block X of relation X/X/X: read only %d of %d bytes. I spent two days to figure out what happened, but it is too hard.Please help me. 2. When restore data from a LIVE backup , I got message like unexpected pageaddr %X/%X in log file %u, segment %u, offset %u WAL ends before end time of backup dump. It seems the WAL was corrupted. I found the LSN where the error occured contained the wrong pageaddr, the pageaddr was 8K before it's real address.What was wrong? -- Richard 2010-08-03 -- 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] Develop item from TODO list
On Tue, 2010-08-03 at 11:10 -0400, Robert Haas wrote: On Tue, Aug 3, 2010 at 9:01 AM, Thom Brown t...@linux.com wrote: I can't answer the main question, but you can try Anjuta as a C editor: http://www.anjuta.org/ Or vi. cough. The hint here would be: As we have few experience with open-source programs, can someone recommend a good C-editor for Ubuntu? I doubt they are unix/linux people at all. Putting them into Vi land is a bit torturous. There are quite a few editors out there but here are some that are going to be a bit more user friendly (in terms of learning curve): Bluefish Anjuta Kate If you want a full environment with projects, SCM integration etc... I would suggest Eclipse. That said, if you can get a handle on VI/VIM or (joe :P) you will probably be pleased with the efficiency. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Status report on writeable CTEs
2010/7/21 David Fetter da...@fetter.org: On Sat, Jul 17, 2010 at 01:15:22AM +0900, Hitoshi Harada wrote: 2010/7/17 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi: On 7/16/10 6:15 PM +0300, Hitoshi Harada wrote: 1. Use MaterialNode instead of adding DtScanNode. Since MaterialNode is exsiting one that work with single tuplestore, it might be sane to modify this so that it accepts tuplestore from Query instead of its child node. I thought about this, but I don't necessarily like the idea of overloading executor nodes. Neither do I have good shape for this solution. Maybe it's not good idea. But my concern is adding DtScanNode, which looks similar to MaterialNode. Of course each purpose is different, but quite big part will overlap each other, I think. Any ideas as to how to factor this out? Handiest ideas would be in the form of a patch ;) Yeah, that would be handiest, but I think I must wait for his first compilable patch to modify to try the idea. Current version looks quite messy and can't build. 2. Use temp table instead of tuplestore list. Since we agreed we need to execute each plan one by one starting and shutting down executor, it now looks very simple strategy. I didn't look at this because I thought using a tuplestore receiver in the portal logic was simple enough. Any thoughts on how this would work? It's just deconstructing queries like: WITH t AS (INSERT INTO x ... RETURING *) SELECT * FROM t; to CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *; SELECT * FROM t; While the second statement is not implemented yet, it will be so simpler. So CTAS would get expanded into CTA[row-returning query] ? Interesting. How much work would that part be? FWIW, this is getting interesting to me these days, and I think this can be separated from wCTE As hackers say, the first to try should be Marko's first design that use the list of tuplestore and DTScanNode. So if he has solid image to reach there, we can wait for him to complete his first compilable version. Then let's take it back and forth. Is it possible? And I concern we might not have concrete consensus about list of features in document form. Does it accept Recursive query? What if x refers to y that refers to x cyclicly? An external design sometimes fix the internal design, and it helps people to review the implementation. If I missed something please point me to the link. Regards, -- Hitoshi Harada -- 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] Per-column collation, proof of concept
On mån, 2010-08-02 at 01:43 -0500, Jaime Casanova wrote: nowadays, CREATE DATABASE has a lc_collate clause. is the new collate clause similar as the lc_collate? i mean, is lc_collate what we will use as a default? Yes, if you do not specify anything per column, the database default is used. How to integrate the per-database or per-cluster configuration with the new system is something to figure out in the future. if yes, then probably we need to use pg_collation there too because lc_collate and the new collate clause use different collation names. postgres=# create database test with lc_collate 'en_US.UTF-8'; CREATE DATABASE test=# create table t1 (col1 text collate en_US.UTF-8); ERROR: collation en_US.UTF-8 does not exist test=# create table t1 (col1 text collate en_US.utf8); CREATE TABLE This is something that libc does for you. The locale as listed by locale -a is called en_US.utf8, but apparently libc takes en_US.UTF-8 as well. also i got errors from regression tests when MULTIBYTE=UTF8 (attached). it seems i was trying to create locales that weren't defined on locales.txt (from were was fed that file?). i added a line to that file (for es_EC.utf8) then i create a table with a column using that collate and execute select * from t2 where col1 'n'; and i got this error: ERROR: could not create locale es_EC.utf8 (of course, that last part was me messing the things up, but it show we shouldn't be using a file locales.txt, i think) It might be that you don't have those locales installed in your system. locales.txt is created by using locale -a. Check what that gives you. i can attach a collate to a domain but i can't see where are we storing that info (actually it says it's not collatable): Domain support is not done yet. -- 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] Two problems when using Postgresql8.3.7, Please help me!
On Wed, 2010-08-04 at 00:34 +0800, Richard wrote: 1.To add live HA to PG, I transfer WAL of a database instance(Primary node) to another database instance (standby node) at real time, and keep startup alive in standby node to recovery WAL online,so that standby node can be a hot standby. But I got some trouble. When standby node switch to primary mode to accept connections, of course after startup initilize the WAL and exit, the postgres process ereportERROR when mdread function enter ERROR branch, and I got message like this could not read block X of relation X/X/X: read only Xof X bytes. I spent two days to figure out what happened, but it is too hard.Please help me. What are you using to transfer the WAL? Did you read this chapter? http://www.postgresql.org/docs/8.3/static/continuous-archiving.html Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Two problems when using Postgresql8.3.7, Please help me!
On Tue, Aug 3, 2010 at 12:34 PM, Richard husttrip...@vip.sina.com wrote: 1.To add live HA to PG, I transfer WAL of a database instance(Primary node) to another database instance (standby node) at real time, and keep startup alive in standby node to recovery WAL online,so that standby node can be a hot standby. But I got some trouble. When standby node switch to primary mode to accept connections, of course after startup initilize the WAL and exit, the postgres process ereportERROR when mdread function enter ERROR branch, and I got message like this could not read block X of relation X/X/X: read only Xof X bytes. I spent two days to figure out what happened, but it is too hard.Please help me. 2. When restore data from a LIVE backup , I got message like unexpected pageaddr %X/%X in log file %u, segment %u, offset %u WAL ends before end time of backup dump. It seems the WAL was corrupted. I found the LSN where the error occured contained the wrong pageaddr, the pageaddr was 8K before it's real address.What was wrong? Please see http://wiki.postgresql.org/wiki/Guide_to_reporting_problems I recently troubleshot a problem much like the one you describe in (2) and I believe in that case it was a bad disk subsystem that started failing writes and didn't tell PG. But it could also be caused by misconfiguration. Since you haven't given any details about your configuration or what steps you've tried to take to resolve the problem, it's pretty difficult to guess what's going on in your case. -- 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] reducing NUMERIC size for 9.1, take two
On 31 July 2010 07:58, Robert Haas robertmh...@gmail.com wrote: Here's a second version of the main patch, in which I have attempted to respond to Tom's concerns/suggestions. (There is still a small, side issue with numeric_maximum_size() which I plan to fix, but this patch is the good stuff.) Applies fine, compiles fine, passes regression tests, and demonstrates the same space reduction seen with the previous version of the patch. Marking Ready for Committer. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Hi, attached is a patch that adds the missing feature to use WHERE CURRENT OF :curname in UPDATE and DELETE statements via ECPG. I used the current CVS MAIN but also applies almost cleanly to 9.0beta4. I certainly feel that this should be applied to 9.0 as a bugfix. The execute.c changes were required because 1. The statement UPDATE table SET fld1 = :input1 WHERE CURRENT OF :curname RETURNING id + :input2; is transformed into UPDATE table SET fld1 = $1 WHERE CURRENT OF $0 RETURNING id + $2; and the $0 is past $1. The current code cannot deal with such a messed up order, and scanning the original query twice is needed, once for $0 substitution, once for mapping $1, etc. to the other input variables. 2. With such a statement and auto-prepare turned on, I always got SQL error: there is no parameter $0 on line X It turned out that the statement was prepared by the auto-prepare machinery before the $0 substitution. PostgreSQL allows PREPARE mystmt AS UPDATE ... WHERE CURRENT OF mycur even if mycur is currently unknown to the system. It's resolved upon executing the prepared query, so we should allow it in ECPG even with dynamic cursorname. The code survives make check and I also went through all the regression tests manually to check them with valgrind to see that there's no leak. As a result, another patch is attached that fixes two memory leaks in PGTYPESnumeric_from_asc() and PGTYPESnumeric_to_long() and quite some leaks in the regression tests themselves. Best regards, Zoltán Böszörményi diff -dcrpN pgsql.orig/src/backend/parser/gram.y pgsql-wherecurrentof/src/backend/parser/gram.y *** pgsql.orig/src/backend/parser/gram.y 2010-07-26 10:05:46.0 +0200 --- pgsql-wherecurrentof/src/backend/parser/gram.y 2010-08-03 10:45:41.0 +0200 *** where_clause: *** 8201,8207 /* variant for UPDATE and DELETE */ where_or_current_clause: WHERE a_expr { $$ = $2; } ! | WHERE CURRENT_P OF name { CurrentOfExpr *n = makeNode(CurrentOfExpr); /* cvarno is filled in by parse analysis */ --- 8201,8207 /* variant for UPDATE and DELETE */ where_or_current_clause: WHERE a_expr { $$ = $2; } ! | WHERE CURRENT_P OF cursor_name { CurrentOfExpr *n = makeNode(CurrentOfExpr); /* cvarno is filled in by parse analysis */ diff -dcrpN pgsql.orig/src/interfaces/ecpg/ecpglib/execute.c pgsql-wherecurrentof/src/interfaces/ecpg/ecpglib/execute.c *** pgsql.orig/src/interfaces/ecpg/ecpglib/execute.c 2010-07-11 11:15:00.0 +0200 --- pgsql-wherecurrentof/src/interfaces/ecpg/ecpglib/execute.c 2010-08-03 16:50:43.0 +0200 *** ecpg_store_input(const int lineno, const *** 1082,1099 return true; } ! static void ! free_params(const char **paramValues, int nParams, bool print, int lineno) { int n; ! for (n = 0; n nParams; n++) { if (print) ! ecpg_log(free_params on line %d: parameter %d = %s\n, lineno, n + 1, paramValues[n] ? paramValues[n] : null); ! ecpg_free((void *) (paramValues[n])); } ! ecpg_free(paramValues); } --- 1082,1109 return true; } ! void ! ecpg_free_params(struct statement *stmt, bool print, int lineno) { int n; ! for (n = 0; n stmt-nparams; n++) { if (print) ! ecpg_log(free_params on line %d: parameter %d = %s\n, lineno, n + 1, stmt-param_values[n] ? stmt-param_values[n] : null); ! ecpg_free((void *) (stmt-param_values[n])); } ! ecpg_free(stmt-param_values); ! ! stmt-nparams = 0; ! stmt-param_values = NULL; ! ! for (n = 0; n stmt-ndollarzero; n++) ! ecpg_free((void *) (stmt-dollarzero[n])); ! ecpg_free(stmt-dollarzero); ! ! stmt-ndollarzero = 0; ! stmt-dollarzero = NULL; } *** insert_tobeinserted(int position, int ph *** 1130,1135 --- 1140,1203 } static bool + ecpg_replace_inline_params(struct statement * stmt) + { + struct variable *var; + int position = 0; + + /* + * We have to check the $0 inline parameters first, they can appear + * after $1, e.g. in this example: + * EXEC SQL UPDATE table SET f1 = :in1 WHERE CURRENT OF :curname RETURNING id + :in2; + * transformed statement is: + * update table set f1 = $1 WHERE CURRENT OF $0 RETURNING id + $2 + */ + var = stmt-inlist; + while (var) + { + char *tobeinserted; + + tobeinserted = NULL; + + if ((position = next_insert(stmt-command, position, stmt-questionmarks) + 1) == 0) + break; + + /* + * if the placeholder is '$0' we have to replace it on the client side + * this is for places we want to support variables at that are not + * supported in the backend + */ + if (stmt-command[position] == '0') + { + const char **dollarzero; + + if (!ecpg_store_input(stmt-lineno, stmt-force_indicator, var, tobeinserted, false)) + return false; + + if (!(dollarzero = (const char **)
Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Boszormenyi Zoltan z...@cybertec.at wrote: attached is a patch that adds the missing feature I certainly feel that this should be applied to 9.0 as a bugfix. Those two statements seem to contradict one another. Is there some bug manifestation beyond an unimplemented feature this fixes? Without this, is there some regression going from 8.4 to 9.0? -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 dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Kevin Grittner írta: Boszormenyi Zoltan z...@cybertec.at wrote: attached is a patch that adds the missing feature I certainly feel that this should be applied to 9.0 as a bugfix. Those two statements seem to contradict one another. PostgreSQL 8.3 or so added WHERE CURRENT OF curname at the SQL level. 9.0 added ECPG's dynamic cursorname (a char variable carries the actual cursor name) but the WHERE CURRENT OF part was not converted, which was definitely an oversight. Whether this is a missing feature or a bugfix, it's only a difference in points of view. I think this patch belongs to 9.0. Is there some bug manifestation beyond an unimplemented feature this fixes? Without this, is there some regression going from 8.4 to 9.0? I haven't looked at 8.4's regression tests but pgtypeslib/numeric.c in 8.4.4 has the same problem, so the second patch (at least the numeric.c part) can be applied there as well, maybe in even older branches. 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] [COMMITTERS] pgsql: Add \conninfo command to psql, to show current connection info.
rh...@postgresql.org (Robert Haas) writes: Add \conninfo command to psql, to show current connection info. David Christensen. Reviewed by Steve Singer. Some further changes by me. Looking at the output from this command: regression=# \conninfo You are connected to database regression via local socket in /tmp at port 5432 as user postgres. This seems a bit awkwardly phrased. I think it would read better if the ordering was changed to You are connected to database regression as user postgres via local socket in /tmp at port 5432. I can't put my finger on the reason why the current ordering seems like awkward English, but I can give a couple of concrete arguments for changing it: 1. This way would match the argument ordering for \connect, which in case you've forgotten is dbname user host port. 2. At least to me, this seems closer to the relative importance of the items. Comments, objections? 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 \conninfo command to psql, to show current connection info.
Tom Lane t...@sss.pgh.pa.us wrote: You are connected to database regression as user postgres via local socket in /tmp at port 5432. +1 -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] Status report on writeable CTEs
On 8/3/2010 7:30 PM, Hitoshi Harada wrote: As hackers say, the first to try should be Marko's first design that use the list of tuplestore and DTScanNode. So if he has solid image to reach there, we can wait for him to complete his first compilable version. Then let's take it back and forth. Is it possible? I am currently working on a version that treats all WITH queries like wCTEs. My progress can be seen in my git repo [1], branch wcte. In its current form, the patch compiles and passes all applicable regression tests but it's still very messy. I'm going to send a cleaner WIP patch to the list the minute I have one, but anyone's free to look at the git repo (and even work on it if they want!). And I concern we might not have concrete consensus about list of features in document form. Does it accept Recursive query? What if x refers to y that refers to x cyclicly? An external design sometimes fix the internal design, and it helps people to review the implementation. If I missed something please point me to the link. A recursive query should be fine as long as 1) it's SELECT-only and 2) it doesn't loop forever. A wCTE can of course refer to the result of the recursive SELECT query with INSERT .. SELECT, UPDATE .. FROM or DELETE .. USING. Cyclic dependencies are out of the scope of this patch; I'm not planning on adding any new features to regular CTEs. [1] http://git.postgresql.org/gitweb?p=users/johto/postgres.git;a=summary Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] review: xml_is_well_formed
Hello 2010/8/3 Peter Eisentraut pete...@gmx.net: On lör, 2010-07-31 at 13:40 -0400, Robert Haas wrote: Well-formedness should probably only allow XML documents. I think the point of this function is to determine whether a cast to xml will throw an error. The behavior should probably match exactly whatever test would be applied there. Maybe there should be xml_is_well_formed() xml_is_well_formed_document() xml_is_well_formed_content() I agree that consistency with SQL/XML is desirable, but for someone coming from the outside, the unqualified claim that 'foo' is well-formed XML might sound suspicious. yes, it is little bit curious - but it can be just documented. Now, I don't think, so we need more functions. Regards Pavel -- 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 \conninfo command to psql, to show current connection info.
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: You are connected to database regression as user postgres via local socket in /tmp at port 5432. +1 Looking at the code, I notice another problem, which is that it's a rather egregious violation of the rule against assembling messages out of small phrases that would have to be translated separately. I realize that this is a pre-existing problem in \connect and the \conninfo patch just copied it, but that doesn't make it OK. The problem in \connect is that somebody made an arbitrary decision to print only the parameters that changed, which they weren't even too consistent about since the database name is always printed. Maintaining that behavior exactly would require quite a large number of variant messages. What I suggest we do in \connect is always print the database and user names, plus print all of the addressing info if any of it changed. This would mean three translatable messages there: You are now connected to database %s as user %s. You are now connected to database %s as user %s on host %s at port %s. You are now connected to database %s as user %s via local socket in %s at port %s. while \conninfo would have two translatable messages corresponding to the last two cases. BTW, the word local seems to be useless extra verbiage; any objections to making it just read via socket in? 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] Re: [COMMITTERS] pgsql: Add \conninfo command to psql, to show current connection info.
On Tue, Aug 3, 2010 at 1:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: 1. This way would match the argument ordering for \connect, which in case you've forgotten is dbname user host port. I noticed that discrepancy when reviewing, but felt it was a problem for another day. Today is fine, though. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch for check constraints using multiple inheritance
Yeb Havinga wrote: The underlying cause is the failure of the code to recognize that if relation C inherits from both A and B, where A and B both have column x, that A.x 'is the same as' B.x, where the 'is the same as' relation is the same that holds for (A.x, C.x) and (B.x, C.x), which the code does a lot of trouble for to recognize. This means that if some definition is altered on A.x, only C.x is updated and B.x not touched. IMO this is wrong and either a multiple inheritance structure like this should be prohibited, since the user did not explicitly declare that A.x and B.x 'are the same' (by e.g. defining a relation D.x and have A and B inherit from that), or the code should update parents of relations when the childs are updated. Thinking about this a bit more, the name 'is the same as' is a bit confusing, since that relation might not be commutative. C.x 'inherits properties from' A.x, or C.x 'is defined by' A.x are perhaps better names, that reflect that the converse might not hold. OTOH, what does C.x 'inherits (all) properties from' A.x mean? If it means that for all properties P, P(C.x) iff P(A.x), then C.x = A.x commutatively and by similar reasoning A.x = B.x. ALTER TABLE top1 RENAME COLUMN a_table_column TO another_table_column; When looking for previous discussions that was referred to upthread, the first thing I found was this recent thread about the exactly the same problem http://archives.postgresql.org/pgsql-hackers/2010-01/msg03117.php Sorry for the double post, however the previous discussion postponed work to .. now, so maybe there is some value in first trying to specify exactly what 'inherits' means, and derive consequences for code behaviour from that. regards, Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] (9.1) btree_gist support for searching on not equals
On Mon, Aug 2, 2010 at 11:16 PM, Jeff Davis pg...@j-davis.com wrote: On Mon, 2010-08-02 at 12:27 -0400, Robert Haas wrote: I was also wondering if it would be worth adding some additional regression testing to contrib/btree_gist exercising this new functionality. Thoughts? Sure. I attached two tests. Committed. I renamed the test to not_equals rather than mixed and added an EXPLAIN (COSTS OFF) in there to verify that the index is actually being used. (I might have to remove that if it turns out not to be stable between an index scan and a bitmap index scan, but let's see what the buildfarm says.) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to show individual statement latencies in pgbench output
Florian Pflug wrote: I created the patch to tune the wal_writer for the synchronous_commit=off case - the idea being that the COMMIT should be virtually instantaneous if the wal_writer writes old wal buffers out fast enough. As I was saying, being able to see the COMMIT times for purposes such as that is something I consider valuable about using this instrumentation that's not really targeted by pg_stat_statements, the other way built-in way people might try to get it. I haven't yet used pgbench's log output feature, so I can't judge how useful the additional of per-statement data to that log is, and what the format should be. However, if you think it's useful and can come up with a sensible format, I'd be happy to add that feature to the patch. Let's worry about that in the future. Maybe it's a good add-on, but it's more than I have time to get into during this CF personally. That was a leftover of the trimming and comment skipping logic, which my patch moves to process_command. I think there's still a trimming error here--line 195 of the new patch is now removing the declaration of i just before it sets it to zero? On the coding standard side, I noticed all your for loops are missing a space between the for and the (; that should get fixed. Finally, now that the rest of the patch is looking in good shape and is something I think is worth considering to commit, it's time to work on the documentation SGML. Also: when generating multiple versions of a patch like this, standard practice is to add something like -vX to the naming, so those of us trying to review can keep them straight. So next one would be pgbench_statementlatency_v3.patch or something like that. It's a good habit to get into from first version of a patch you submit. Presuming that's going to be the only version is optimistic for all but the smallest of patches, and sometimes not even them... -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tracking inherited columns (was: patch for check constraints using multiple inheritance)
On Tue, Aug 3, 2010 at 3:05 PM, Yeb Havinga yebhavi...@gmail.com wrote: Yeb Havinga wrote: The underlying cause is the failure of the code to recognize that if relation C inherits from both A and B, where A and B both have column x, that A.x 'is the same as' B.x, where the 'is the same as' relation is the same that holds for (A.x, C.x) and (B.x, C.x), which the code does a lot of trouble for to recognize. This means that if some definition is altered on A.x, only C.x is updated and B.x not touched. IMO this is wrong and either a multiple inheritance structure like this should be prohibited, since the user did not explicitly declare that A.x and B.x 'are the same' (by e.g. defining a relation D.x and have A and B inherit from that), or the code should update parents of relations when the childs are updated. Thinking about this a bit more, the name 'is the same as' is a bit confusing, since that relation might not be commutative. C.x 'inherits properties from' A.x, or C.x 'is defined by' A.x are perhaps better names, that reflect that the converse might not hold. OTOH, what does C.x 'inherits (all) properties from' A.x mean? If it means that for all properties P, P(C.x) iff P(A.x), then C.x = A.x commutatively and by similar reasoning A.x = B.x. ALTER TABLE top1 RENAME COLUMN a_table_column TO another_table_column; When looking for previous discussions that was referred to upthread, the first thing I found was this recent thread about the exactly the same problem http://archives.postgresql.org/pgsql-hackers/2010-01/msg03117.php Sorry for the double post, however the previous discussion postponed work to .. now, so maybe there is some value in first trying to specify exactly what 'inherits' means, and derive consequences for code behaviour from that. Yeah, I was thinking about that thread, too, on my drive home from Metuchen. I wouldn't get too bogged down in formal logic; it seems there are a couple of distinct cases here: 1. If you're changing properties of a column, you need to verify for each relation in the inheritance tree that the expected attinhcount and the actual attinhcount match. If, for any relation in the inheritance tree rooted at the named table, they don't, then they are doubly inherited there, from some other table outside the hierarchy rooted at the named table, and the operation must fail. We'd need similar logic for constraints, if we had support for renaming or otherwise modifying them, but right now we don't. 2. If you're adding a column, you need to propagate the new column to relations that don't have it yet, but if you find one that already has it than you adjust attinhcount and don't recurse to its chidlren. 3. If you're dropping a column, you essentially decrement the attinhcount of all your children; then you recurse into any that reach attincount = 0 and not attislocal and drop the column there as well. -- 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] (9.1) btree_gist support for searching on not equals
Robert Haas robertmh...@gmail.com writes: On Mon, Aug 2, 2010 at 11:16 PM, Jeff Davis pg...@j-davis.com wrote: Sure. I attached two tests. Committed. I see no sign of a commit from here ... 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] (9.1) btree_gist support for searching on not equals
On Tue, Aug 3, 2010 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Aug 2, 2010 at 11:16 PM, Jeff Davis pg...@j-davis.com wrote: Sure. I attached two tests. Committed. I see no sign of a commit from here ... Sigh. Forgot to exit my editor. -- 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] GROUPING SETS revisited
On Mon, Aug 02, 2010 at 11:50:00PM -0600, Josh Tolley wrote: In case anyone's interested, I've taken the CTE-based grouping sets patch from [1] and made it apply to 9.1, attached. I haven't yet done things like checked it for whitespace consistency, style conformity, or anything else, but (tuits permitting) hope to figure out how it works and get it closer to commitability in some upcoming commitfest. I mention it here so that if someone else is working on it, we can avoid duplicated effort, and to see if a CTE-based grouping sets implementation is really the way we think we want to go. [1] http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php I've added back one file in the patch enclosed here. I'm still getting compile fails from CC=ccache gcc ./configure --prefix=$PG_PREFIX --with-pgport=$PGPORT --with-perl --with-libxml --enable-debug --enable-cassert make Log from that also enclosed. 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 diff --git a/src/backend/parser/Makefile b/src/backend/parser/Makefile index a8f4c07..fb248a6 100644 --- a/src/backend/parser/Makefile +++ b/src/backend/parser/Makefile @@ -15,7 +15,7 @@ override CPPFLAGS := -I. -I$(srcdir) $(CPPFLAGS) OBJS= analyze.o gram.o keywords.o kwlookup.o parser.o \ parse_agg.o parse_clause.o parse_coerce.o parse_cte.o parse_expr.o \ parse_func.o parse_node.o parse_oper.o parse_param.o parse_relation.o \ - parse_target.o parse_type.o parse_utilcmd.o scansup.o + parse_target.o parse_type.o parse_utilcmd.o scansup.o parse_gsets.o FLEXFLAGS = -CF diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 6b99a10..1b579a8 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -34,6 +34,7 @@ #include parser/parse_clause.h #include parser/parse_coerce.h #include parser/parse_cte.h +#include parser/parse_gsets.h #include parser/parse_oper.h #include parser/parse_param.h #include parser/parse_relation.h @@ -150,6 +151,163 @@ parse_sub_analyze(Node *parseTree, ParseState *parentParseState, } /* + * process GROUPING SETS + */ +static SelectStmt * +makeSelectStmt(List *targetList, List *fromClause) +{ + SelectStmt *n = makeNode(SelectStmt); + n-distinctClause = NULL; + n-intoClause = NULL; + n-targetList = targetList; + n-fromClause = fromClause; + n-whereClause = NULL; + n-groupClause = NULL; + n-havingClause = NULL; + n-windowClause = NIL; + n-withClause = NULL; + n-valuesLists = NIL; + n-sortClause = NIL; + n-limitOffset = NULL; + n-limitCount = NULL; + n-lockingClause = NIL; + n-op = SETOP_NONE; + n-all = false; + n-larg = NULL; + n-rarg = NULL; + return n; +} + +static List * +makeStarTargetList(void) +{ + ResTarget *rt = makeNode(ResTarget); + + rt-name = NULL; + rt-indirection = NIL; + rt-val = (Node *) makeNode(ColumnRef); + ((ColumnRef *) rt-val)-fields = list_make1(makeNode(A_Star)); + rt-location = -1; + + return list_make1(rt); +} + +static SelectStmt * +transformGroupingSets(ParseState *pstate, SelectStmt *stmt) +{ + if (stmt-groupClause IsA(stmt-groupClause, GroupByClause)) + { + GroupingSetsSpec *gss = (GroupingSetsSpec *) expandGroupingSets(pstate, + (List *)((GroupByClause *)stmt-groupClause)-fields); + + if (pstate-p_hasGroupingSets) + { + CommonTableExpr *cte = makeNode(CommonTableExpr); + SelectStmt *cteedstmt; + int ngroupingsets = list_length(gss-set_list) + (gss-has_empty_set ? 1 : 0); + boolall = ((GroupByClause *) stmt-groupClause)-all; + + cteedstmt = makeSelectStmt(NIL, NIL); + cteedstmt-intoClause = stmt-intoClause; + cteedstmt-sortClause = stmt-sortClause; + cteedstmt-limitOffset = stmt-limitOffset; + cteedstmt-limitCount = stmt-limitCount; + cteedstmt-lockingClause = stmt-lockingClause; + + cte-ctename = **g**; + cte-ctequery = (Node *) stmt; + cte-location = -1; + + cteedstmt-withClause = makeNode(WithClause); + cteedstmt-withClause-ctes = list_make1(cte); + cteedstmt-withClause-recursive = false; +
Re: [HACKERS] GROUPING SETS revisited
On Tue, Aug 03, 2010 at 12:58:03PM -0700, David Fetter wrote: On Mon, Aug 02, 2010 at 11:50:00PM -0600, Josh Tolley wrote: In case anyone's interested, I've taken the CTE-based grouping sets patch from [1] and made it apply to 9.1, attached. I haven't yet done things like checked it for whitespace consistency, style conformity, or anything else, but (tuits permitting) hope to figure out how it works and get it closer to commitability in some upcoming commitfest. I mention it here so that if someone else is working on it, we can avoid duplicated effort, and to see if a CTE-based grouping sets implementation is really the way we think we want to go. [1] http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php I've added back one file in the patch enclosed here. I'm still getting compile fails from CC=ccache gcc ./configure --prefix=$PG_PREFIX --with-pgport=$PGPORT --with-perl --with-libxml --enable-debug --enable-cassert make Log from that also enclosed. Yeah, I seem to have done a poor job of producing the patch based on the repository I was working from. That said, it seems Pavel's working actively on a patch anyway, so perhaps my updating the old one isn't all that worthwhile. Pavel, is your code somewhere that we can get to it? -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] Compiling CVS HEAD with clang under OSX
On Mon, Aug 02, 2010 at 05:34:57PM +0100, Greg Stark wrote: Tom: Because that code is much more complex and prone to errors especially when you start getting into multiplication and other operations and it's also much slower than the code which allows overflow to happen and then checks that the result makes sense. I'm not entirely sure I agree. At least I haven't actually gone through all the arithmetic operations and I'm not sure how much more complex they get. If they were all at that level of complexity I think I would say we should go ahead and bite the performance bullet and do it the ultra-safe way. FWIW, here's a site with some gcc magic which will allow you to detect overflows during addition. Ofcourse, the fact that it's gcc specific makes it a lot less useful. http://www.fefe.de/intof.html Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [HACKERS] Review: Patch for phypot - Pygmy Hippotause
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: I think the patch is good in principle Since everyone seems to agree this is a good patch which needed minor tweaks, and we haven't heard from the author, I just went ahead and made the changes. Applied with a bit of further editing of the comments. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] reducing NUMERIC size for 9.1, take two
Robert Haas robertmh...@gmail.com writes: Here's a second version of the main patch, in which I have attempted to respond to Tom's concerns/suggestions. This version looks fine 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] merge command - GSoC progress
And, I have tested the running of MERGE command with different situations. I am sorry that I didn't create regression test files, because I am not sure how to add new files in the git package. But, I have written web pages in Postgres Wiki. I explain the details of my implementation and a set of testing examples. Can someone help Boxuan with how to write regression tests? -- -- 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] merge command - GSoC progress
https://wiki.postgresql.org/wiki/Implementation_detalis https://wiki.postgresql.org/wiki/Test_examples These pages were confusingly named, so I just moved them: https://wiki.postgresql.org/wiki/MergeTestExamples https://wiki.postgresql.org/wiki/MergeImplementationDetails -- -- 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] merge command - GSoC progress
On Tue, Aug 03, 2010 at 03:14:02PM -0700, Josh Berkus wrote: And, I have tested the running of MERGE command with different situations. I am sorry that I didn't create regression test files, because I am not sure how to add new files in the git package. But, I have written web pages in Postgres Wiki. I explain the details of my implementation and a set of testing examples. Can someone help Boxuan with how to write regression tests? Happy to. I'll start this evening PDT or tomorrow :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] reducing NUMERIC size for 9.1, take two
On Tue, Aug 3, 2010 at 6:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Here's a second version of the main patch, in which I have attempted to respond to Tom's concerns/suggestions. This version looks fine to me. Excellent. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] merge command - GSoC progress
Boxuan Zhai wrote: I think there are no redundant lines in this time's patch file. It is much better. There are still more blank likes around the new code you've added than are needed in many places, but that doesn't interfere with reading the patch. The main code formatting issue left you'll need to address eventually are all the really long comments in there. And, I have tested the running of MERGE command with different situations. I am sorry that I didn't create regression test files, because I am not sure how to add new files in the git package. git add filename ? The tests you've put in there are the right general sort of things to try out. The one example you gave does show an UPSERT being emulated by MERGE, which is the #1 thing people are looking for initially. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Stamp HEAD as 9.1devel.
On Fri, Jul 9, 2010 at 10:26 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jul 9, 2010 at 12:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: How long should I wait before I start breaking things? Did you have any particular breakage in mind? Well, you can see for yourself what I've submitted for the next CF. You might want to hold off on the get_whatever_oid patches for a bit, but the other stuff I see there looks pretty localized. No objection to pressing forward with CF work otherwise. I can hold off on those for a bit - I don't think there will be enough drift to matter very much, but if it makes you more comfortable, it's not a big deal. I checked on these patches today and there was only one, quite trivial conflict (and the relevant patch was not even something that was back-patched). So I think there is not much reason to hold off any longer on committing these. -- 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] reducing NUMERIC size for 9.1
On Fri, Jul 30, 2010 at 9:55 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jul 30, 2010 at 2:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Maybe something like this, obviously with a suitable comment which I haven't written yet: numeric_digits = (precision + 6) / 4; return (numeric_digits * sizeof(int16)) + NUMERIC_HDRSZ; This is OK for the base-10K case, but there's still code in there for the base-10 and base-100 cases. Can you express this logic in terms of DEC_DIGITS and sizeof(NumericDigit) ? I think you might find it was actually clearer that way, cf Polya. It appears to work out to: numeric_digits = (precision + 2 * (DEC_DIGITS - 1)) / DEC_DIGITS return (numeric_digits * sizeof(NumericDigits)) + NUMERIC_HDRSZ; The smallest value for precision which requires 2 numeric_digits is always 2; and the required number of numeric_digits increases by 1 each time the number of base-10 digits increases by DEC_DIGITS. And here is a patch implementing that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company numeric_maximum_size.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] Postgres as Historian
Hardik Belani wrote: For this i can create a table with number and time (may be time offset instead of timestamp) as columns. But still it will require me to store huge number of rows in the order of few millions. Data is read only and only inserts can happen. But I need to perform all kinds of aggregation to get various statistics. for example: daily avg, monthly avg etc.. You've unfortunately asked on the wrong list about this. pgsql-hackers is intended mainly for discussion related to the source code of PostgreSQL, so this is off-topic for it. The people who like to argue about the best way to implement aggregates and the like are on the pgsql-performance list. You'd be more likely to get detailed responses if you asked this question there. That group loves to talk about how to design things for other people. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GROUPING SETS revisited
2010/8/3 Joshua Tolley eggyk...@gmail.com: On Tue, Aug 03, 2010 at 12:58:03PM -0700, David Fetter wrote: On Mon, Aug 02, 2010 at 11:50:00PM -0600, Josh Tolley wrote: In case anyone's interested, I've taken the CTE-based grouping sets patch from [1] and made it apply to 9.1, attached. I haven't yet done things like checked it for whitespace consistency, style conformity, or anything else, but (tuits permitting) hope to figure out how it works and get it closer to commitability in some upcoming commitfest. I mention it here so that if someone else is working on it, we can avoid duplicated effort, and to see if a CTE-based grouping sets implementation is really the way we think we want to go. [1] http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php I've added back one file in the patch enclosed here. I'm still getting compile fails from CC=ccache gcc ./configure --prefix=$PG_PREFIX --with-pgport=$PGPORT --with-perl --with-libxml --enable-debug --enable-cassert make Log from that also enclosed. Yeah, I seem to have done a poor job of producing the patch based on the repository I was working from. That said, it seems Pavel's working actively on a patch anyway, so perhaps my updating the old one isn't all that worthwhile. Pavel, is your code somewhere that we can get to it? not now. please wait a week. Regards Pavel -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkxYeiQACgkQRiRfCGf1UMPlEQCff+I4sCGtR+lzUs6Wb5JKi7Uu 3qYAnjLHzHzyMSHHX55QsphkaBbEJ0Zf =uRqV -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] merge command - GSoC progress
2010/8/4 Greg Smith g...@2ndquadrant.com Boxuan Zhai wrote: I think there are no redundant lines in this time's patch file. It is much better. There are still more blank likes around the new code you've added than are needed in many places, but that doesn't interfere with reading the patch. Sorry, it is my personal habit of leaving blanks around codes. I will chage this if it doesn't follow the pgsql coding style. The main code formatting issue left you'll need to address eventually are all the really long comments in there. I will correct the long comments in the next patch. And, I have tested the running of MERGE command with different situations. I am sorry that I didn't create regression test files, because I am not sure how to add new files in the git package. git add filename ? The tests you've put in there are the right general sort of things to try out. The one example you gave does show an UPSERT being emulated by MERGE, which is the #1 thing people are looking for initially. In fact, I have created a merge.sql with simple merge example. I put it in the folder of /src/test/regress/sql/ and modified the serial_schedule file to add a line of : test: merge Is this correct? But, I don't know how to run regress to test this sql file. My make check fails when install the db. I think this is because I do it under a MinGW environment and some parameters are not matched with the default setting of postgres. I can configure, make install , initdb and do sql query in psql successfully in my machine. So the source code itself should be correct. I put my merge.sql in attachment, in case anyone want to have a look. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us http://www.2ndquadrant.us/ merge.sql 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