Re: [HACKERS] Proposal: Multiversion page api (inplace upgrade)
Ron Mayer napsal(a): Tom Lane wrote: Another issue is that it might not be possible to update a page for lack of space. Are we prepared to assume that there will never be a transformation we need to apply that makes the data bigger? In such a situation an in-place update might be impossible, and that certainly takes it outside the bounds of what ReadBuffer can be expected to manage. Would a possible solution to this be that you could snip 2. Run some new maintenance command like vacuum expand or vacuum prepare_for_upgrade or something that would split any too-full pages, leaving only pages with enough space. It does not solve problems for example with TOAST tables. If chunks does not fit on a new page layout one of the chunk tuple have to be moved to free page. It means you get a lot of pages with ~2kB of free unused space. And if max chunk size is different between version you got another problem as well. There is also idea to change compression algorithm for 8.4 (or offer more varinats). It also mean that you need to understand old algorithm in a new version or you need to repack everything on old version. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_stat_statements
Hello, Postgres 8.4 has pg_stat_user_functions view to track number of calls of stored functions and time spent in them. Then, I'm thinking a sql statement version of similar view -- pg_stat_statements. Prepared statements and statements using extended protocol are grouped by their sql strings without parameters, that is the just same as pg_stat_user_functions. We could ignore simple queries with parameters because they have different expression for each execution. We can write sql statements in server logs and gather them using some tools (pgfouine and pqa) even now, but statement logging has unignorable overhead. Lightweight view is useful for typical users who are only interedted in aggregated results. One issue is how and where to store sql strings. We could use hash values of statement strings as short identifiers, but we need to store sql strings somewhere to compare the IDs and original statements. 1. Store SQLs in shared memory We need to allocate fixed region on starting servers. Should we have another memory setting into postgresql.conf? 2. Store SQLs in stats collector process's memory We can use dynamically allocated memory, but sending sql statements to stat collector process is probably slow and stat file will be large. I'm not sure which is better. It might have relevance to discussion of shared prepared statements. Another issue is that we could implement the feature as an add-on, not a core feature. We can use general hooks for this purpose; We store sql statement and their hash values in planner_hook, and record number of execution and time in new executor begin/end hooks or by adding a stop-watch executor node. Should this feature be in the core or not? For example, dynamic shared memory allocation might be need before we move the feature in the core. Comments and suggestions welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] a problem when poring from Oracle's PL/SQL to PLPGSQL
pgsql-hackers: The following is Oracle's PL/SQL if resTypeTableName is null then queryStr := 'select IntIID, Path FROM aaResourceData' || ' where ResType=''' || srcType || ''' and ResID=''' || srcID || ; else queryStr := 'select IntIID, Path FROM ' || resTypeTableName || ' where ResType=''' || srcType || ''' and ResID=''' || srcID || ; end if; open cursorSrc for queryStr; Here queryStr is a variable which type is TEXT OR VARCHAR or other string types. But in PLPGSQL, we can only open a cursor this way: open cursorSrc for select * from testtable; We cannot substitude select * from testtable with a variable. Is there another way to handle it? Thank you for your help. :-) billy [EMAIL PROTECTED] 2008-06-13 -- 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] a problem when poring from Oracle's PL/SQL to PLPGSQL
billy schrieb: pgsql-hackers: The following is Oracle's PL/SQL if resTypeTableName is null then queryStr := 'select IntIID, Path FROM aaResourceData' || ' where ResType=''' || srcType || ''' and ResID=''' || srcID || ; else queryStr := 'select IntIID, Path FROM ' || resTypeTableName || ' where ResType=''' || srcType || ''' and ResID=''' || srcID || ; end if; open cursorSrc for queryStr; Here queryStr is a variable which type is TEXT OR VARCHAR or other string types. But in PLPGSQL, we can only open a cursor this way: open cursorSrc for select * from testtable; We cannot substitude select * from testtable with a variable. Is there another way to handle it? Thank you for your help. :-) open cursorSrc for execute queryStr; should work fine Regards Mario Weilguni -- 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] Proposal: Multiversion page api (inplace upgrade)
Bruce Momjian napsal(a): Heikki Linnakangas wrote: Zdenek Kotala wrote: 4) Implementation The main point of implementation is to have several version of PageHeader structure (e.g. PageHeader_04, PageHeader_03 ...) and correct structure will be handled in special branch (see examples). (this won't come as a surprise as we talked about this in PGCon, but) I think we should rather convert the page structure to new format in ReadBuffer the first time a page is read in. That would keep the changes a lot more isolated. Note that you need to handle not only page header changes, but changes to internal representations of different data types, and changes like varvarlen and combocid. Those are things that have happened in the past; in the future, I'm foreseeing changes to the toast header, for example, as there's been a lot of ideas related to toast options compression. I understand the goal of having good modularity (not having ReadBuffer modify the page), but I am worried that doing multi-version page processing in a modular way is going to spread version-specific information all over the backend code, making is harder to understand. I don't think so. Page already contains page version information inside and currently we have macros like PageSetLSN. Caller needn't know nothing about PageHeader representation. It is responsibility of page API to correctly handle multi version. The same we can use for tuple access. It is more complicated but I think it is possible. Currently we several macros (e.g. HeapTupleGetOid) which works on TupleData structure. Only what we need is extend this API as well. I think in final we will get more readable code. Zdenek -- 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] keyword list/ecpg
[Sorry, just noticed that I didn't answer this email. ] On Wed, Jun 04, 2008 at 05:06:41PM +0100, Mike Aubury wrote: It might depend on the tokens.. Are =, ++ etc single tokens ? ... Wouldn't it work to just always insert a space between tokens, no matter whether there was one originally? There are a few cases where you must not enter a blank, but I'm not sure whethere these are all in ecpg specific rules anyway. One example that comes to my mind is the handling of :port in the connect statement. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] keyword list/ecpg
I took a quick look at this - would it be ok to add some small amounts of 'markup' to the gram.y ? eg : /* ECPGCOPYON */ /* ECPGCOPYOFF */ /* ECPGMODE=NOSPACE */ ... /* ECPGMODE=USESPACE */ etc ? On Friday 13 June 2008 10:47:55 Michael Meskes wrote: [Sorry, just noticed that I didn't answer this email. ] On Wed, Jun 04, 2008 at 05:06:41PM +0100, Mike Aubury wrote: It might depend on the tokens.. Are =, ++ etc single tokens ? ... Wouldn't it work to just always insert a space between tokens, no matter whether there was one originally? There are a few cases where you must not enter a blank, but I'm not sure whethere these are all in ecpg specific rules anyway. One example that comes to my mind is the handling of :port in the connect statement. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Adding more context to tuptoaster's elog messages
Tom Lane napsal(a): Reflecting on this thread: http://archives.postgresql.org/pgsql-general/2008-06/msg00344.php it strikes me that the elog messages in tuptoaster.c would be significantly more useful if they gave the name of the toast table containing the problem, which is readily available at the sites of the elog calls. Any objections? Should I back-patch that, or just do it in HEAD? +1 for back patching as well. Zdenek -- 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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
On Jun 12, 2008, at 12:25 PM, Bruce Momjian wrote: Dickson S. Guedes wrote: Hi all, There is a TODO Item to allow pg_hba.conf to specify host names along with IP addresses. I'd like to work on this feature, if nobody is working too and no objection exists. Please do --- I know of no one working on that. Oooh please do! -- 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] keyword list/ecpg
Mike Aubury [EMAIL PROTECTED] writes: I took a quick look at this - would it be ok to add some small amounts of 'markup' to the gram.y ? eg : /* ECPGCOPYON */ /* ECPGCOPYOFF */ /* ECPGMODE=NOSPACE */ ... /* ECPGMODE=USESPACE */ We're almost certainly going to need some kluges of that sort, so as long as they're not all over the place I won't object. But ... I've seen no evidence that those specific examples are needed. Why wouldn't we copy all the backend rules? And based on Michael's last comment it's unclear that we need to avoid adding spaces in the mechanically generated actions, either (which squares with my gut feeling about SQL syntax). You'll probably need to get into specific cases before finding out what kluges you need. 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] keyword list/ecpg
We're almost certainly going to need some kluges of that sort, so as long as they're not all over the place I won't object. But ... I've seen no evidence that those specific examples are needed. Why wouldn't we copy all the backend rules? And based on Michael's last comment it's unclear that we need to avoid adding spaces in the mechanically generated actions, either (which squares with my gut feeling about SQL syntax). You'll probably need to get into specific cases before finding out what kluges you need. I think this was more an 'in principle' - if thats route is ok, then I'll start hacking away properly... I was thinking about the copy on/copy off for more the header info (before the %%) - so we can have a really dumb script that just gets told what blocks to copy - and what to ignore.. There will also be some grammer in the original which we'll need to replace with some ecpg specifics - eg adding grammer for the variables etc. Might be easier to just turn 'off' the original rules and have some custom ecpg stuff appended to the generated code.. Theres also another thing that needs to be decided, which is if the generated ecpg grammer should be developer generated (ie. Michael Meskes runs a script and commits the output), or should be generated for each and every source based installation. I personally would stongly favour the script being a tool for ecpg tool developers and not used as part of a normal installation. -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SSL configure patch
Here is the SSL patch we discussed previously for 8.3.1. sslconfig.patch.8.3.1 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] keyword list/ecpg
Mike Aubury wrote: Theres also another thing that needs to be decided, which is if the generated ecpg grammer should be developer generated (ie. Michael Meskes runs a script and commits the output), or should be generated for each and every source based installation. I personally would stongly favour the script being a tool for ecpg tool developers and not used as part of a normal installation. What happens when a non-Michael developer changes the original gram.y? Is he expected to run the script before committing too? That sounds brittle to me. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] keyword list/ecpg
Alvaro Herrera [EMAIL PROTECTED] writes: Mike Aubury wrote: Theres also another thing that needs to be decided, which is if the generated ecpg grammer should be developer generated (ie. Michael Meskes runs a script and commits the output), or should be generated for each and every source based installation. I personally would stongly favour the script being a tool for ecpg tool developers and not used as part of a normal installation. What happens when a non-Michael developer changes the original gram.y? Is he expected to run the script before committing too? That sounds brittle to me. As long as the script is written in Perl and not exceedingly slow, I see no reason it shouldn't be required to run as part of a build from CVS. We already require Perl use elsewhere in the build if you're not working from a tarball. 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] keyword list/ecpg
The same as happens at the moment - nothing... The grammer for the ecpg needs to be re-generated when the grammer in the main parser is changed - whether its a manual or (mostly) automatic task is largely irrelevant. The only downside is that if its not regenerated then the change to gram.y simply wont be reflected in the grammer for ecpg. I personally think its down to the ecpg developers (of which I believe Michael is the main developer) to decide when to do this and to check that its worked. Its just otherwise - there could be a serious case for 'unintended consequences'... Just my 2 pence worth... On Friday 13 June 2008 15:39:48 Alvaro Herrera wrote: I personally would stongly favour the script being a tool for ecpg tool developers and not used as part of a normal installation. What happens when a non-Michael developer changes the original gram.y? Is he expected to run the script before committing too? That sounds brittle to me. -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ -- 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] keyword list/ecpg
Mike Aubury [EMAIL PROTECTED] writes: I was thinking about the copy on/copy off for more the header info (before the %%) - so we can have a really dumb script that just gets told what blocks to copy - and what to ignore.. I think you'll find that doesn't work very well; the script will need at least some understanding of the bison %-declarations, and it can just ignore everything else before %%. The reason is that (1) you're going to need to alter the %type declarations and (2) you're going to need to merge these declarations with ones from ecpg. It might be that the merging is best handled via directive-like comments, but I suspect they'll be in the ecpg-side file not in the main grammar: you'll have something indicating insert all the main %type declarations here, one for insert precedence declarations here, etc. But of course this is just speculation... 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] 8.3.3: uncovered Xml2 functionality
Hi,
in 8.3.3 Documentation / F.34.Xml2 the Deprecation notice claims:
..the core server now covers XML syntax checking and XPath queries,
which is what Xml2 does, and more.
I can't get boolean values out from function xpath. I think there is a
missing implementation of xpath syntax.
Try for example:
select xpath('false()',xml('root/'));
select xpath('1=1',xml('root/'));
select xpath('boolean(WHATEVERYOUWANT)',xml('root/'));
We have to keep in mind that xpath queries can return 4 value types:
integer, string, boolean and nodeset.
The previous Xml2 implementation was way more comfortable (functions:
xpath_number, xpath_string, xpath_bool and xpath_nodeset).
Thank you
--
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] keyword list/ecpg
Mike Aubury [EMAIL PROTECTED] writes: What happens when a non-Michael developer changes the original gram.y? The same as happens at the moment - nothing... Wrong answer. The entire point of this work is to get rid of manual work in updating the ecpg grammar when the main changes. The above seems about like saying that the build process shouldn't regenerate gram.c from gram.y --- you're supposed to do that manually. One of the things I want to have come out of this is that we find out right away if a main-grammar change breaks ecpg. Right now, any conflict is not discovered until Michael gets around to syncing the files, which is often weeks or months later. 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] keyword list/ecpg
On Fri, Jun 13, 2008 at 02:57:54PM +0100, Mike Aubury wrote: based installation. I personally would stongly favour the script being a tool for ecpg tool developers and not used as part of a normal installation. Why? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] keyword list/ecpg
On Fri, Jun 13, 2008 at 10:39:48AM -0400, Alvaro Herrera wrote: What happens when a non-Michael developer changes the original gram.y? Is he expected to run the script before committing too? That sounds brittle to me. The situation used to be that this only caused ecpg to be out-of-sync. No big deal. But nowadays it might break compilation. If you add a new keyword to keywords.c but not to preproc.y you'll get an undefined symbol during build process. To make sure stuff like this cannot happen in the future I'd prefer to have the script run automatically, albeit having a defined, but maybe non-functional, default. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_statements
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Postgres 8.4 has pg_stat_user_functions view to track number of calls of stored functions and time spent in them. Then, I'm thinking a sql statement version of similar view -- pg_stat_statements. We don't have any system-wide names for statements, so this seems pretty ill-defined and of questionable value. Showing the text of statements in a view also has security problems. 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] Overhauling GUCS
On Wednesday 11 June 2008 16:54:23 Bruce Momjian wrote:
Dave Page wrote:
On Wed, Jun 11, 2008 at 6:56 PM, Bruce Momjian [EMAIL PROTECTED] wrote:
Dave Page wrote:
On Mon, Jun 2, 2008 at 5:46 AM, Joshua D. Drake [EMAIL PROTECTED]
wrote:
pg_ctl -D data check?
I would +1 that.
I would also really like to see that - though I'd also like to see an
SQL interface so we can check a config before saving when editing via
pgAdmin or similar.
Should this be a TODO?
Yes please.
Added to TODO:
* Add pg_ctl option to do a syntax check of postgresql.conf
ISTM we need something that can run inside the db as well, i'm thinking
something like pg_check_conf() to go with pg_reload_conf(). Also, these
should probably check validity of the pg_hba.conf as well.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default client encoding in postgresql.conf
Andrew Dunstan [EMAIL PROTECTED] writes: Robert Treat wrote: This conversation is beginning to suggest to me that client_encoding shouldn't be listed in postgresql.conf at all. Yeah, that sure seems better than what we currently have. I should have thought there was a good argument for preventing its being set in postgresql.conf. No, I can think of cases where someone might legitimately want to do that, they're just pretty far out of mainstream usage. We already have some variables that are GUC_NOT_IN_SAMPLE but not GUC_DISALLOW_IN_FILE, so I don't see anything wrong with considering client_encoding the same way. (BTW, sometime we ought to get around to enforcing GUC_DISALLOW_IN_FILE...) 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] Proposal: Multiversion page api (inplace upgrade)
Zdenek Kotala [EMAIL PROTECTED] writes: It does not solve problems for example with TOAST tables. If chunks does not fit on a new page layout one of the chunk tuple have to be moved to free page. It means you get a lot of pages with ~2kB of free unused space. And if max chunk size is different between version you got another problem as well. There is also idea to change compression algorithm for 8.4 (or offer more varinats). It also mean that you need to understand old algorithm in a new version or you need to repack everything on old version. I don't have any problem at all with the idea that in-place update isn't going to support arbitrary changes of parameters, such as modifying the toast chunk size. In particular anything that is locked down by pg_control isn't a problem. 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] Overhauling GUCS
Robert Treat wrote: On Wednesday 11 June 2008 16:54:23 Bruce Momjian wrote: Dave Page wrote: On Wed, Jun 11, 2008 at 6:56 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Dave Page wrote: On Mon, Jun 2, 2008 at 5:46 AM, Joshua D. Drake [EMAIL PROTECTED] wrote: pg_ctl -D data check? I would +1 that. I would also really like to see that - though I'd also like to see an SQL interface so we can check a config before saving when editing via pgAdmin or similar. Should this be a TODO? Yes please. Added to TODO: * Add pg_ctl option to do a syntax check of postgresql.conf ISTM we need something that can run inside the db as well, i'm thinking something like pg_check_conf() to go with pg_reload_conf(). Also, these should probably check validity of the pg_hba.conf as well. Agreed, TODO updated: o Add functions to syntax check configuration files -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Options for protocol level cursors
James William Pye [EMAIL PROTECTED] writes: On Jun 12, 2008, at 4:45 PM, Tom Lane wrote: Huh? I don't see why... you might have such a limitation in a particular driver, but not in the protocol. Oh? I know when you bind a prepared statement you have the ability state the formats of each column, but I'm not aware of the protocol's capacity to reconfigure the formats of an already existing cursor; ie, a DECLARE'd cursor. I know you can use the Describe message to learn about the cursor's column types and formats You'd do it while Binding a FETCH command. 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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
Bruce Momjian wrote: Dickson S. Guedes wrote: Hi all, There is a TODO Item to allow pg_hba.conf to specify host names along with IP addresses. I'd like to work on this feature, if nobody is working too and no objection exists. Please do --- I know of no one working on that. The reason it wasn't done years ago was that there was disagreement on the way it should work. And the TODO actually lists several alternatives: Host name lookup could occur when the postmaster reads the pg_hba.conf file, or when the backend starts. Another solution would be to reverse lookup the connection IP and check that hostname against the host names in pg_hba.conf. We could also then check that the host name maps to the IP address. So before you start actually working on it, let's see if we have a better level of agreement on what it should do. Personally, I favor hostname lookup on backend start but none of the others. 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] Better default_statistics_target
On Jun 12, 2008, at 17:55, Greg Sabino Mullane wrote: Glad to hear that, although I think this is only in HEAD, not backpatched, right? Well at any rate, I withdraw my strong support for 100 and join in the quest for a good number. The anything but 10 campaign I vote for 11. That's one louda, in'it? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_statements
ITAGAKI Takahiro wrote: Hello, Postgres 8.4 has pg_stat_user_functions view to track number of calls of stored functions and time spent in them. Then, I'm thinking a sql statement version of similar view -- pg_stat_statements. I can see how this would be useful, but I can also see that it could be a huge performance burden when activated. So it couldn't be part of the standard statistics collection. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_statements
On Fri, 2008-06-13 at 17:33 +0900, ITAGAKI Takahiro wrote: We can write sql statements in server logs and gather them using some tools (pgfouine and pqa) even now, but statement logging has unignorable overhead. I would prefer to look at ways to reduce the current overhead rather than change to another system entirely. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
On Fri, Jun 13, 2008 at 12:47:22PM -0400, Andrew Dunstan wrote: The reason it wasn't done years ago was that there was disagreement on the way it should work. And the TODO actually lists several alternatives: Host name lookup could occur when the postmaster reads the pg_hba.conf file, or when the backend starts. Another solution would It needs to happen at authentication time. I'm not sure whether reads the pg_hba.conf or backend starts is the right way to say that, but it must happen only when you're actually authenticating the host entry. This is because DNS RRs have a TTL on them, so looking up the host at any moment other than when you're actually doing the authentication is prone to error. be to reverse lookup the connection IP and check that hostname against the host names in pg_hba.conf. We could also then check that the host name maps to the IP address. There is, curiously, an existing Internet Draft currently in WGLC at the dnsop working group at the IETF that warns explicitly against using hostname forward and reverse matching checks as a security mechanism, without having other options. So if the mechanism is going to force matching forward and reverse data, then I urge whoever implements this to make it possible to turn that matching check off, because it won't work reliably. The draft is available from http://tools.ietf.org/wg/dnsop/draft-ietf-dnsop-reverse-mapping-considerations/. By the way, in the context of DNSSEC, a matching check might not add anything, but a check for existing signed reverse data may. That is, if you have authenticated forward zone data and you have authenticated reverse zone data, you can be confident that you have the right hostname even if the forward and reverse hostnames don't match. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
Andrew Dunstan [EMAIL PROTECTED] writes: The reason it wasn't done years ago was that there was disagreement on the way it should work. And the TODO actually lists several alternatives: IIRC, the major reason there was disagreement was the prospect of unacceptable performance from any of the easy or obvious implementations. As Andrew S notes, you can't just do the lookups once at postmaster start; but resolving a pile of hostnames during each connection is pretty unpleasant, especially if the DNS server isn't local. (And then there are the effective-DOS implications if the DNS server is down altogether.) The attraction of the reverse-lookup approach is that you do only one lookup, on the actual connection IP, rather than having to resolve every hostname in the file to see if it matches. However that way had disadvantages of its own, which I don't recall at the moment. I think at least some of the issues had to do with security, ie how much can you trust an answer from a remote DNS server. Check the archives before you start implementing ... 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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
On Fri, 2008-06-13 at 13:26 -0400, Andrew Sullivan wrote: On Fri, Jun 13, 2008 at 12:47:22PM -0400, Andrew Dunstan wrote: The reason it wasn't done years ago was that there was disagreement on the way it should work. And the TODO actually lists several alternatives: Host name lookup could occur when the postmaster reads the pg_hba.conf file, or when the backend starts. Another solution would It needs to happen at authentication time. I'm not sure whether reads the pg_hba.conf or backend starts is the right way to say that, but it must happen only when you're actually authenticating the host entry. The best of both ideas would be to have an option inside pg_hab.conf to indicate when lookup occurs. Some parts of a network are static, others are not, so a global option would not be useful. The default should be at authentication time as Andrew Sullivan suggests, so that correctness is the default. If the user knows a portion of their network is static, then the lookups can be done ahead of connection time to reduce connection latency, as Andrew Dunstan suggests. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] pg_stat_statements
Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2008-06-13 at 17:33 +0900, ITAGAKI Takahiro wrote: We can write sql statements in server logs and gather them using some tools (pgfouine and pqa) even now, but statement logging has unignorable overhead. I would prefer to look at ways to reduce the current overhead rather than change to another system entirely. It's also the case that adding to the stats collector is hardly going to have ignorable overhead. (And you're dreaming if you think you can do it like pg_stat_activity, because this can't be a fixed-size array with a trivial indexing scheme.) 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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
On Fri, Jun 13, 2008 at 4:30 PM, Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: The reason it wasn't done years ago was that there was disagreement on the way it should work. And the TODO actually lists several alternatives: IIRC, the major reason there was disagreement was the prospect of unacceptable performance from any of the easy or obvious implementations. As Andrew S notes, you can't just do the lookups once at postmaster start; but resolving a pile of hostnames during each connection is pretty unpleasant, especially if the DNS server isn't local. (And then there are the effective-DOS implications if the DNS server is down altogether.) Yes, if DNS server is down during a init connection, or server startup, we can have problems. The attraction of the reverse-lookup approach is that you do only one lookup, on the actual connection IP, rather than having to resolve every hostname in the file to see if it matches. SSH uses an approach like that. However that way had disadvantages of its own, which I don't recall at the moment. I think at least some of the issues had to do with security, ie how much can you trust an answer from a remote DNS server. Check the archives before you start implementing ... I'm seeing alternatives and studing the code and the email replies, but not start coding yet. Thanks Tom. -- []s Dickson S. Guedes - Projeto Colmeia - Curitiba - PR (41) 3254-7130 ramal: 27 http://makeall.wordpress.com/ http://pgcon.postgresql.org.br/ http://planeta.postgresql.org.br/ -- 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] Change lock requirements for adding a trigger
On Wed, 2008-06-04 at 16:33 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We have * relhasindex (bool) set by CREATE INDEX but not unset by DROP INDEX * relhasrules (bool) * reltriggers (int2) set by CREATE and DROP, since its an integer Right. If CREATE INDEX can take a Share lock and can update pg_class, why would it not be theoretically possible for CREATE TRIGGER? It's (probably) theoretically possible, if we replace reltriggers with a bool that acts more like relhasindex, ie it's a hint to go look in pg_triggers. Looking at this area of locking, I've noticed that the locks held by CREATE TRIGGER are more of a problem than might be apparent. * Locks held by CREATE TRIGGER are an issue for trigger-based replication systems, where triggers are frequently added and removed to various tables. * ALTER TABLE .. ADD FOREIGN KEY holds an AccessExclusiveveLock on *both* referencing and referenced tables. It does this because we must add triggers to both tables. So reducing the lock strength required by CREATE TRIGGER would also allow a reduction in lock strength for adding FKs. So useful steps will be to * refactor pg_class code so that CREATE TRIGGER uses an identical approach to CREATE INDEX * reduce lock strength for CREATE TRIGGER and ALTER TABLE ... ADD FOREIGN KEY so that it takes a ShareLock during ATAddForeignKeyConstraint() * look at how we can reduce lock strength for other ALTER TABLE subcommands. Not sure how yet. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
Andrew Sullivan wrote: This is because DNS RRs have a TTL on them, so looking up the host at any moment other than when you're actually doing the authentication is prone to error. Perhaps the solution to this problem is to do the lookups and store the TTL of each answer. At the time of actually checking you need only get a new answer for those that expired. -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] Options for protocol level cursors
On Jun 13, 2008, at 9:24 AM, Tom Lane wrote: You'd do it while Binding a FETCH command. Indeed, that is true. It seems quite unfortunate that drivers have to jump through such hoops to provide a convenient programmer's interface to held and/or scrollable cursors; bearing in mind all that has been discussed about the--well, *my*--desire of equivalent capabilities wrt the usual protocol level Prepare, Describe, Bind, and Execute sequence. Well, perhaps it is better to say that it is *ideal* to be able to merely use the protocol mechanisms to achieve the desired effect, rather than using them to use the SQL command yielding the same or similar(Binding FETCH for different formats) effect. =\ [Obviously, I was looking to propose... ;] My thoughts for creating a HOLD and/or SCROLL cursor on Bind would be to add YA GUC stating the cursor options for Bind cursors. Something along the lines of default_bind_options=HOLD,SCROLL. Of course the actual default would be an empty string so as to preserve the existing functionality by default. I imagine there's a big fat No waiting for me for at least the following reasons[in no particular order]: 1. It's already possible to achieve the desired result and the proposed feature is, of course, not going to work with past versions. [Just put in the extra work to support past versions of PG.] 2. I'm the only one asking/looking for it. (I'm so lonely ;) It is, of course, ideal to be able to state these options in the Bind message, but I don't see how that would be a possibility without a new protocol version or doing something dangerous like embedding the options in the cursor's name. ain't happenin'. And, yeah, despite the first reason, I think I would prefer to use a new GUC. Certainly, with some bitterness. =( In any case, thanks for the discussion, Tom. -- 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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
Alvaro Herrera [EMAIL PROTECTED] writes: Perhaps the solution to this problem is to do the lookups and store the TTL of each answer. At the time of actually checking you need only get a new answer for those that expired. This is not behavior we'd want to put into the postmaster, though, and it's hard to see how to manage it otherwise. (Well, maybe a new postmaster child process just for this, but I find it hard to believe the feature is worth that.) 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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Perhaps the solution to this problem is to do the lookups and store the TTL of each answer. At the time of actually checking you need only get a new answer for those that expired. This is not behavior we'd want to put into the postmaster, though, and it's hard to see how to manage it otherwise. (Well, maybe a new postmaster child process just for this, but I find it hard to believe the feature is worth that.) Indeed. The only circumstance in which this feature should be used is probably where you have control over the zones involved. For remote connections I'd far rather have an open address specification and require SSL with client side certs. The potential for self-inflicted pain from this feature does worry me a bit. 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] Options for protocol level cursors
On Fri, 13 Jun 2008, James William Pye wrote: My thoughts for creating a HOLD and/or SCROLL cursor on Bind would be to add YA GUC stating the cursor options for Bind cursors. Something along the lines of default_bind_options=HOLD,SCROLL. 2. I'm the only one asking/looking for it. (I'm so lonely ;) The JDBC driver would also like this ability, but a GUC is a pretty ugly hack. Also, since you still have to go to the SQL level to issue the MOVE or FETCH BACKWARD, you're still not all the way there to a full protocol solution. Kris Jurka -- 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] Options for protocol level cursors
On Jun 13, 2008, at 4:40 PM, Kris Jurka wrote: The JDBC driver would also like this ability, but a GUC is a pretty ugly hack. I completely agree that it is an ugly hack. :) Also, since you still have to go to the SQL level to issue the MOVE or FETCH BACKWARD, you're still not all the way there to a full protocol solution. Completely true. However, this is, of course, only pertinent to SCROLL cursors. -- 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] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses
On Fri, Jun 13, 2008 at 08:51:19PM +0100, Simon Riggs wrote: The best of both ideas would be to have an option inside pg_hab.conf to indicate when lookup occurs. Some parts of a network are static, others are not, so a global option would not be useful. We would point and laugh at people who thought that something was static inside PostgreSQL, and depended on that for something critical without some pretty heavy-duty locks. Are we really proposing to offer an authentication mechanism that depends on something as flimsy as hostname lookups in the DNS, and then not insist that the bare minimum of integrity check (I checked this DNS lookup at connection time) is the rule? DNS is a distributed database. Surely the least we can demand is that the lookup happen when the naive think it will (i.e., at the time the connection from that hostname happens). If the user knows a portion of their network is static, If there were the slightest evidence that users historically believed in such knowledge correctly, then I might have some sympathy for this. The fact is that DNS (at least without DNSSEC) is one of the areas in which sysadmins have the worst record of trust to this day. I think we'd be fools to encourage such trust. If you don't look up at _least_ at connection time, this feature should be rejected on the grounds that it opens a new authentication hole a mile wide. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
