Re: [HACKERS] creating index names automatically?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, Dec 25, 2009 at 05:27:44PM -0500, Robert Haas wrote: > On Fri, Dec 25, 2009 at 4:13 PM, Tom Lane wrote: > > Robert Haas writes: [...] > >> I think what we should learn from this case, as well as the recent > >> changes to EXPLAIN, COPY, and VACUUM syntax, is that adding options to > >> commands by creating keywords is not very scalable, and that putting > >> the modifier immediately after the command name is an especially poor > >> positioning. > > > > Perhaps. The original VACUUM syntax is a pretty bad piece of design, [...] > I wasn't intending to engage in pointless bellyaching. What I was > trying to do was point out that there are some common problems in all > of these cases, and trying to extract a design principle. I'm not > really sure why CREATE INDEX [CONCURRENTLY] is any different from > VACUUM [FULL] [FREEZE] [ANALYZE]. In both cases, the command and its > modifiers are immediately followed by a name, without any intervening > keyword or punctuation. In retrospect, that doesn't seem like a good > choice, at least to me, so, it might be something to look out for in > the future. YMMV, of course. I have to concur with Robert here. There will be always a need to add (PostgreSQL-specific, non-standard) modifiers. Having a "syntactical place" where to put them without forcing us to introduce new (non-standard) keywords or semi-keywords seems like a Good Thing. Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFLNawdBcgs9XrR2kYRAnigAJ99c6dMhgk30hYK29ci0+WyXXCKzgCfV+c2 HLCy7BEvQYwWySMVI5n6LE0= =PpCd -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] keywords on .pgpass
On Fri, Dec 25, 2009 at 9:56 PM, Andrew Dunstan wrote: > I don't see the use case for it - .pgpass is for single users, not a whole > cluster. And it does support wildcards, which takes care of the 'all' case. > In the case of pg_hba.conf we don't know in advance who will actually be > connecting. But in the case of .pgpass we do, so the extra utility of > 'sameuser', 'samerole' and 'samegroup' in this case is not apparent to me. OK, I might need to focus only on the use of replication, without being avaricious. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Fri, Dec 25, 2009 at 4:13 PM, Tom Lane wrote: > Robert Haas writes: >> Having said all this, I don't really object to the alternate proposal >> of creating a set of words that are reserved as relation names but not >> as column names, either, especially if it would allow us to make some >> other existing keywords less-reserved. But I don't really understand >> the justification for thinking that CONCURRENTLY is OK to make more >> reserved, but, say, EXPLAIN would not be OK. > > You're attacking a straw man --- no such comparison was made or > implied. In practice, if we were up against a situation where we seemed > to need to make EXPLAIN more reserved, we'd consider that and the > alternatives on their own merits, not by reference to whether it should > be more reserved than CONCURRENTLY. IMO these are always going to be > one-of-a-kind decisions; I feel no desire to propose a hard and fast > rule about them. The particular case of EXPLAIN was discussed previously. Although I think we have other ways to work around the problems discussed on that thread, I came away from that discussion with the impression that you were categorically opposed to adding any more non-standard reserved words. Evidently that's not the case. > The basic problem I've got with kluges such as you proposed is that it's > impossible to explain them to users. "CONCURRENTLY is unreserved, > except that in the context of a CREATE INDEX target it'll be interpreted > as an option not an index name"? Ugh. If we make a separate keyword > category for it, at least we can document that in a reasonably > straightforward fashion: "unreserved (cannot be table name)". That's a valid concern. I admitted it was gross right from the start - I just thought it might be better than having a non-standard reserved word, especially for such a minor feature. If it isn't, it isn't. >> I think what we should learn from this case, as well as the recent >> changes to EXPLAIN, COPY, and VACUUM syntax, is that adding options to >> commands by creating keywords is not very scalable, and that putting >> the modifier immediately after the command name is an especially poor >> positioning. > > Perhaps. The original VACUUM syntax is a pretty bad piece of design, > dating from a time when we didn't even have a clear notion of which > keywords were reserved and which weren't; if it were proposed today > I'm confident we'd notice the problem and reject the syntax. It's less > obvious that CREATE INDEX CONCURRENTLY was a bad idea. We did consider > alternative syntaxes and rejected them on (IIRC) the grounds that they > didn't read well. Even now, the only thing you can really say against > it is that it got in the way of making the index name optional, but > every syntax choice forecloses some other choices. Complaining because > we didn't have the 20-20 foresight needed to realize that we'd want to > make the index name optional later on isn't very useful. I wasn't intending to engage in pointless bellyaching. What I was trying to do was point out that there are some common problems in all of these cases, and trying to extract a design principle. I'm not really sure why CREATE INDEX [CONCURRENTLY] is any different from VACUUM [FULL] [FREEZE] [ANALYZE]. In both cases, the command and its modifiers are immediately followed by a name, without any intervening keyword or punctuation. In retrospect, that doesn't seem like a good choice, at least to me, so, it might be something to look out for in the future. YMMV, of course. ...Robert -- 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 for 8.5, listagg aggregate function, WIP patch
2009/12/25 Tom Lane :
> Pavel Stehule writes:
>> I propose a new aggregate function - listagg. This function
>> concatenate values to string. If this function is used with two
>> parameters, then second parameter is used as delimiter. NULL input
>> values are ignored like other aggregates. If all values are NULL, then
>> result is NULL. When delimiter is omitted, then values are
>> concatenated without any delimiter.
>
> The main objection I have to this proposal is the name: listagg seems
> pretty horrid. It's got nothing to do with lists, and it doesn't even
> attempt to be consistent with other existing function names.
It is list - on application level - without arrays - was used list of
values separated by comma. I don't have a experience with Oracle. But
I know this term in this meaning from MSSQL.
>
> I can see a couple of different approaches that might make sense
> for choosing a better name. One is "something_to_string", though
> I'm not sure what "something" should be --- maybe "rows" or "set"?
> The other approach is to name it something based on concat() on the
> grounds that it's a form of concatenation, and we do have "concat"
> in the standard in the guise of XMLCONCAT. For instance there's some
> case for concat_agg() by analogy to array_agg(); though personally
> I think array_agg() is a horrid name too and not one of the SQL
> committee's better efforts.
I don't know, who though up named this function in Oracle. This
functionality is known as "listagg" (Oracle) or "group_concat"
(MySQL). I don't thing we need a third name for it. group_concat has
enhanced syntax:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
there are keyword SEPARATOR. Oracle's syntax is nearer to standard
PostgreSQL's syntax
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm
But Oracle has own syntax too. After some experience today, I see some
advantage Oracle's syntax over standard :).
This function isn't standardised now, so we can use any name. I vote
name used in Oracle db.
Regards
Pavel Stehule
>
> 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 for 8.5, listagg aggregate function, WIP patch
Pavel Stehule writes: > I propose a new aggregate function - listagg. This function > concatenate values to string. If this function is used with two > parameters, then second parameter is used as delimiter. NULL input > values are ignored like other aggregates. If all values are NULL, then > result is NULL. When delimiter is omitted, then values are > concatenated without any delimiter. The main objection I have to this proposal is the name: listagg seems pretty horrid. It's got nothing to do with lists, and it doesn't even attempt to be consistent with other existing function names. I can see a couple of different approaches that might make sense for choosing a better name. One is "something_to_string", though I'm not sure what "something" should be --- maybe "rows" or "set"? The other approach is to name it something based on concat() on the grounds that it's a form of concatenation, and we do have "concat" in the standard in the guise of XMLCONCAT. For instance there's some case for concat_agg() by analogy to array_agg(); though personally I think array_agg() is a horrid name too and not one of the SQL committee's better efforts. 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] PHP and PostgreSQL 8.5 compatibility
Hi everyone, I'm glad to announce that the pgsql and PDO PHP extensions test suites are now passing when used with 8.5-cvs. Mostly it was just a matter of updating the tests themselves, but a bug in PDO_PgSQL dealing with the new hex format affecting only the 5.2.x branch was fixed during the process[1]. This means that most of the PHP applications should work fine with 8.5 when running recent enough PHP versions. The few that are using both PDO and bytea fields will require a switch to 5.3 (or 5.2.13 whenever it comes out). [1] http://bugs.php.net/50575 Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.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] creating index names automatically?
Robert Haas writes: > Having said all this, I don't really object to the alternate proposal > of creating a set of words that are reserved as relation names but not > as column names, either, especially if it would allow us to make some > other existing keywords less-reserved. But I don't really understand > the justification for thinking that CONCURRENTLY is OK to make more > reserved, but, say, EXPLAIN would not be OK. You're attacking a straw man --- no such comparison was made or implied. In practice, if we were up against a situation where we seemed to need to make EXPLAIN more reserved, we'd consider that and the alternatives on their own merits, not by reference to whether it should be more reserved than CONCURRENTLY. IMO these are always going to be one-of-a-kind decisions; I feel no desire to propose a hard and fast rule about them. The basic problem I've got with kluges such as you proposed is that it's impossible to explain them to users. "CONCURRENTLY is unreserved, except that in the context of a CREATE INDEX target it'll be interpreted as an option not an index name"? Ugh. If we make a separate keyword category for it, at least we can document that in a reasonably straightforward fashion: "unreserved (cannot be table name)". > I think what we should learn from this case, as well as the recent > changes to EXPLAIN, COPY, and VACUUM syntax, is that adding options to > commands by creating keywords is not very scalable, and that putting > the modifier immediately after the command name is an especially poor > positioning. Perhaps. The original VACUUM syntax is a pretty bad piece of design, dating from a time when we didn't even have a clear notion of which keywords were reserved and which weren't; if it were proposed today I'm confident we'd notice the problem and reject the syntax. It's less obvious that CREATE INDEX CONCURRENTLY was a bad idea. We did consider alternative syntaxes and rejected them on (IIRC) the grounds that they didn't read well. Even now, the only thing you can really say against it is that it got in the way of making the index name optional, but every syntax choice forecloses some other choices. Complaining because we didn't have the 20-20 foresight needed to realize that we'd want to make the index name optional later on isn't very useful. 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] Initial refactoring of plperl.c - rebased [PATCH]
Tim Bunce wrote:
On Fri, Dec 25, 2009 at 12:54:13PM -0500, Andrew Dunstan wrote:
Tim Bunce wrote:
I've attached an update of my previous refactoring of plperl.c.
It's been rebased over the current (git) HEAD and has a few
very minor additions.
[snip]
+ -- Test compilation of unicode regex
+ --
+ CREATE OR REPLACE FUNCTION perl_unicode_regex(text) RETURNS INTEGER AS $$
+ # see http://rt.perl.org/rt3/Ticket/Display.html?id=47576
+ return ($_[0] =~ /\x{263A}|happy/i) ? 1 : 0; # unicode smiley
+ $$ LANGUAGE plperl;
This test is failing on my setup at least when the target db is not UTF8
encoded.
Maybe that's a bug we need to fix?
Yes. I believe the test is highlighting an existing problem: that plperl
function in non-PG_UTF8 databases can't use regular expressions that
require unicode character meta-data.
Either the (GetDatabaseEncoding() == PG_UTF8) test in plperl_safe_init()
should be removed, so the utf8fix function is always called, or the
test should be removed (or hacked to only apply to PG_UTF8 databases).
I tried forcing the test, but it doesn't seem to work, possibly because
in the case that the db is not utf8 we aren't forcing argument strings
to UTF8 :-(
I think we might need to remove the test from the patch.
p.s. There may be other problems using unicode in non-PG_UTF8 databases,
but I believe this patch doesn't change the behaviour for better or worse.
Right.
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] Update ppport.h in plperl
On Thu, Dec 24, 2009 at 11:08:49AM -0500, Andrew Dunstan wrote: > Tim Bunce wrote: >> I'm about ready to post the next draft of my plperl feature patch. >> >> When I looked at the diff I saw ~7800 lines of it were just due to >> updating ppport.h. So I've broken that out into this large but totally >> trivial patch. > > I'm going to apply this pretty soon to get some buildfarm coverage on it. > We had a little trouble last time we messed with ppport.h, ISTR, so we need > to make sure nothing breaks. It'll probably work fine over alpha3, but was meant to be applied after the plperl refactoring patch. Tim. -- 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] Initial refactoring of plperl.c - rebased [PATCH]
On Fri, Dec 25, 2009 at 12:54:13PM -0500, Andrew Dunstan wrote:
> Tim Bunce wrote:
>> I've attached an update of my previous refactoring of plperl.c.
>> It's been rebased over the current (git) HEAD and has a few
>> very minor additions.
>>
> [snip]
>> + -- Test compilation of unicode regex
>> + --
>> + CREATE OR REPLACE FUNCTION perl_unicode_regex(text) RETURNS INTEGER AS $$
>> + # see http://rt.perl.org/rt3/Ticket/Display.html?id=47576
>> + return ($_[0] =~ /\x{263A}|happy/i) ? 1 : 0; # unicode smiley
>> + $$ LANGUAGE plperl;
>
> This test is failing on my setup at least when the target db is not UTF8
> encoded.
>
> Maybe that's a bug we need to fix?
Yes. I believe the test is highlighting an existing problem: that plperl
function in non-PG_UTF8 databases can't use regular expressions that
require unicode character meta-data.
Either the (GetDatabaseEncoding() == PG_UTF8) test in plperl_safe_init()
should be removed, so the utf8fix function is always called, or the
test should be removed (or hacked to only apply to PG_UTF8 databases).
Tim.
p.s. There may be other problems using unicode in non-PG_UTF8 databases,
but I believe this patch doesn't change the behaviour for better or worse.
--
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] info about patch: using parametrised query in psql
On Fri, Dec 25, 2009 at 2:30 PM, Tom Lane wrote: > Robert Haas writes: >> Works for me. One small problem discussed upthread is that there >> currently doesn't appear to be a libpq function that does >> ident-quoting. I'm thinking that we will need to add one to make this >> work - is that going to be a problem? > > The rules for ident-quoting are simple and haven't changed over the > years, so we don't really *need* a libpq function for it. OTOH you > could argue it's inconsistent that we have one and not the other. Yeah. Plus it seems like a useful thing to have, anyway. >> I'm thinking that since we're >> just adding a function it won't force an uncomfortable major-version >> bump on libpq. > > Yeah, we have taken the position in the past that adding new functions > doesn't require a soname bump. Good. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PostgreSQL 8.5 Open Items
I'm not sure whether we ever posted this schedule anywhere official - if so, I can't find it - but my understanding is that we have consensus on the release schedule described here: http://archives.postgresql.org/pgsql-hackers/2009-09/msg01251.php Under this schedule, the last CommitFest for the 8.5 release will start 3 weeks from today. As I think many of us remember, the last CommitFest for 8.4 was extremely long and was followed by some amount of further delay before we were able to go to beta. I believe that we have made a lot of progress in how we manage CommitFests this release cycle, and I am fairly confident that we will be able to wrap up the last CommitFest in a timely fashion. I am personally committed to doing whatever I can to make sure that happens and hereby volunteer to manage the last CommitFest, unless someone else would like to take a crack at it, in which case by all means feel free. The open items list is a source of somewhat more concern for me, because while patches can be bounced if we run out of time, open items don't just go away. I've added two items I know about here: http://wiki.postgresql.org/wiki/PostgreSQL_8.5_Open_Items I would encourage anyone else who is aware of open items to begin adding them to this list, adding sections as necessary. Last time around, we ended up assembling our initial list from a dump of one of Bruce's mail folders. I'm not sure whether that's the best way to go about it, but if we're going to do it that way, we should do it SOON, so that we start to get an idea what the must-fix issues are before we get down to the wire. We have a little time on any issues that are must-fix for the final release, but the scheduled gap between alpha4 and beta1 is quite short, so anything that is must-fix for beta is something we need to start thinking about soon. We can't do that, though, until we first have a list of items and a decision about which ones fall into that category. Merry Christmas, ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Reg postgresSQL source code
Dear Sir, I am a 3rd year B.Tech. (Computer Science Engineering) student at IIIT, Hyderabad (India). For the past 5 months I've been trying to implement a module in postgresSQL and I am stuck at a point. I'll be highly grateful to you if you could help me out. The module is about constructing a new plan without calling the optimizer. The new plan should be built using some old available plans and the parsetree of the new query. It is assumed that the new plan and old plan have similar structure. Doubt: I am not able to figure out the meaning of 'targetlist' in the 'plan' node. Earlier I thought it was a list of TargetEntry nodes that contains the information about the column names in the SELECT clause. But the subplans (i.e., 'lefttree' and 'righttree' nodes) again contain the targetlist and I want to know about them. Could you please also let me know if there is a way in which I can construct these targetlists using just the parsetree of the query and not any other information like "context" or "outPlan". Thank You Manan Nayak B.Tech. (CSE) IIIT, Hyderabad -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- 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] info about patch: using parametrised query in psql
Robert Haas writes: > Works for me. One small problem discussed upthread is that there > currently doesn't appear to be a libpq function that does > ident-quoting. I'm thinking that we will need to add one to make this > work - is that going to be a problem? The rules for ident-quoting are simple and haven't changed over the years, so we don't really *need* a libpq function for it. OTOH you could argue it's inconsistent that we have one and not the other. > I'm thinking that since we're > just adding a function it won't force an uncomfortable major-version > bump on libpq. Yeah, we have taken the position in the past that adding new functions doesn't require a soname bump. 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] ORDER BY clause in aggregate doesn't work well with multi argument aggregates
Pavel Stehule writes: > 2009/12/25 Tom Lane : >> No, because you could have more than one ORDER BY item. > hmm - this isn't bullet-prof design :( I notice that ORDER BY 'x' draws an error at the query level but not within aggregates or window functions. I wonder if we should have the "SQL99" ORDER BY code throw an error for a simple literal ORDER BY item. There isn't any visible use for ordering by a constant, and this would catch some possible misunderstandings. For instance, it would have complained about Pavel's original example in this thread, and we could word the error message to make it clear that the system thinks that's an ORDER BY value not an aggregate argument. 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] info about patch: using parametrised query in psql
On Fri, Dec 25, 2009 at 2:12 PM, Tom Lane wrote: > Robert Haas writes: >> If we do want to go >> with a single flag character, maybe it should just be a single or >> double quote: > >> :'foo - quote as a literal >> :"foo - quote as an ident > > I would've proposed that myself if I thought it would work, but I'm > afraid that it will wreak complete chaos from a parsing standpoint. > Half the tools in the world will think this is an incomplete literal, > and I'm not even very sure you could keep psql itself from getting > confused. > > Hmm ... actually, though, what about combining the ideas: > > :'foo' - quote as a literal > :"foo" - quote as an ident > > This leaves us with nothing much as far as extensibility, but from > a mnemonic standpoint it's a large win. Works for me. One small problem discussed upthread is that there currently doesn't appear to be a libpq function that does ident-quoting. I'm thinking that we will need to add one to make this work - is that going to be a problem? I'm thinking that since we're just adding a function it won't force an uncomfortable major-version bump on libpq. I guess the other thing that is bad about this is that someone might be forgiven for thinking that quotation marks were the way to include a space in the variable name. But that may be a downside that we can just live with. ...Robert -- 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] info about patch: using parametrised query in psql
2009/12/25 Tom Lane : > Robert Haas writes: >> If we do want to go >> with a single flag character, maybe it should just be a single or >> double quote: > >> :'foo - quote as a literal >> :"foo - quote as an ident > > I would've proposed that myself if I thought it would work, but I'm > afraid that it will wreak complete chaos from a parsing standpoint. > Half the tools in the world will think this is an incomplete literal, > and I'm not even very sure you could keep psql itself from getting > confused. > I though about it too. > Hmm ... actually, though, what about combining the ideas: > > :'foo' - quote as a literal > :"foo" - quote as an ident > > This leaves us with nothing much as far as extensibility, but from > a mnemonic standpoint it's a large win. +1 Pavel > > regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ORDER BY clause in aggregate doesn't work well with multi argument aggregates
2009/12/25 Tom Lane : > Pavel Stehule writes: >> I was wrong. But it should be syntax error no? > >> SELECT foo(expr ORDER BY expr, .) > >> currently it quietly ignore arguments over ORDER BY clause. > > No, because you could have more than one ORDER BY item. > I see it now. hmm - this isn't bullet-prof design :( ok, thank you Pavel > regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] info about patch: using parametrised query in psql
Robert Haas writes: > If we do want to go > with a single flag character, maybe it should just be a single or > double quote: > :'foo - quote as a literal > :"foo - quote as an ident I would've proposed that myself if I thought it would work, but I'm afraid that it will wreak complete chaos from a parsing standpoint. Half the tools in the world will think this is an incomplete literal, and I'm not even very sure you could keep psql itself from getting confused. Hmm ... actually, though, what about combining the ideas: :'foo' - quote as a literal :"foo" - quote as an ident This leaves us with nothing much as far as extensibility, but from a mnemonic standpoint it's a large win. 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] info about patch: using parametrised query in psql
2009/12/25 Robert Haas :
> On Fri, Dec 25, 2009 at 1:41 PM, Tom Lane wrote:
>> Pavel Stehule writes:
>>> there are two quoting styles, so we need two syntax. I proposed
>>
>>> :[var] and :{var} - for ident quoting and literal quoting.
>>> Theoretically we could to use :(var) for bytea escaping.
>>
>> And if you need a fourth style, you're at a dead end. I don't think
>> this is really an improvement over the single-flag-character approach.
>> Neither one has got any mnemonic value whatever, unfortunately, but
>> at least the flag character method is fairly extensible.
>
> The lack of mnemonic value kind of sucks, but I don't see that Pavel's
> style is any more or less extensible than your proposed flag
> character. Basically, he's saying that the flag characters will be [
> and { and adding a closing delimeter to match. If we do want to go
> with a single flag character, maybe it should just be a single or
> double quote:
>
> :'foo - quote as a literal
> :"foo - quote as an ident
I could to live with
:'foo' and :"foo" although ' and " characters are not the best for
readability. But the mnemonic is clear.
Pavel
>
> I dunno what to do about bytea-escaping under this framework, though.
>
> ...Robert
>
--
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] ORDER BY clause in aggregate doesn't work well with multi argument aggregates
Pavel Stehule writes: > I was wrong. But it should be syntax error no? > SELECT foo(expr ORDER BY expr, .) > currently it quietly ignore arguments over ORDER BY clause. No, because you could have more than one ORDER BY item. 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] info about patch: using parametrised query in psql
On Fri, Dec 25, 2009 at 1:41 PM, Tom Lane wrote:
> Pavel Stehule writes:
>> there are two quoting styles, so we need two syntax. I proposed
>
>> :[var] and :{var} - for ident quoting and literal quoting.
>> Theoretically we could to use :(var) for bytea escaping.
>
> And if you need a fourth style, you're at a dead end. I don't think
> this is really an improvement over the single-flag-character approach.
> Neither one has got any mnemonic value whatever, unfortunately, but
> at least the flag character method is fairly extensible.
The lack of mnemonic value kind of sucks, but I don't see that Pavel's
style is any more or less extensible than your proposed flag
character. Basically, he's saying that the flag characters will be [
and { and adding a closing delimeter to match. If we do want to go
with a single flag character, maybe it should just be a single or
double quote:
:'foo - quote as a literal
:"foo - quote as an ident
I dunno what to do about bytea-escaping under this framework, though.
...Robert
--
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] ORDER BY clause in aggregate doesn't work well with multi argument aggregates
2009/12/25 Tom Lane : > Pavel Stehule writes: >> It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. > > I think you don't understand the syntax. Put the aggregate arguments > first, then the ORDER BY. > I was wrong. But it should be syntax error no? SELECT foo(expr ORDER BY expr, .) currently it quietly ignore arguments over ORDER BY clause. Pavel > regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ORDER BY clause in aggregate doesn't work well with multi argument aggregates
2009/12/25 Pavel Stehule : > 2009/12/25 Tom Lane : >> Pavel Stehule writes: >>> It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. >> >> I think you don't understand the syntax. Put the aggregate arguments >> first, then the ORDER BY. >> > > Sorry, I don't see it. Please, could you be more descriptive. > again sory, I see it. all is ok Thank You Pavel Stehule >> 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] ORDER BY clause in aggregate doesn't work well with multi argument aggregates
2009/12/25 Tom Lane : > Pavel Stehule writes: >> It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. > > I think you don't understand the syntax. Put the aggregate arguments > first, then the ORDER BY. > Sorry, I don't see it. Please, could you be more descriptive. > 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] info about patch: using parametrised query in psql
2009/12/25 Tom Lane :
> Pavel Stehule writes:
>> there are two quoting styles, so we need two syntax. I proposed
>
>> :[var] and :{var} - for ident quoting and literal quoting.
>> Theoretically we could to use :(var) for bytea escaping.
>
> And if you need a fourth style, you're at a dead end. I don't think
> this is really an improvement over the single-flag-character approach.
> Neither one has got any mnemonic value whatever, unfortunately, but
> at least the flag character method is fairly extensible.
I thing so not.
what:
:'variable'
:"variable"
we could to use any non identifier char without ":"
for me - flag characters looks little bit strange - maybe I have a
quoting joined with some symmetric. Maybe it looks too much like unary
operator
Regards
Pavel
>
> regards, tom lane
>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ORDER BY clause in aggregate doesn't work well with multi argument aggregates
Pavel Stehule writes: > It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. I think you don't understand the syntax. Put the aggregate arguments first, then the ORDER BY. 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] info about patch: using parametrised query in psql
Pavel Stehule writes:
> there are two quoting styles, so we need two syntax. I proposed
> :[var] and :{var} - for ident quoting and literal quoting.
> Theoretically we could to use :(var) for bytea escaping.
And if you need a fourth style, you're at a dead end. I don't think
this is really an improvement over the single-flag-character approach.
Neither one has got any mnemonic value whatever, unfortunately, but
at least the flag character method is fairly extensible.
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] ORDER BY clause in aggregate doesn't work well with multi argument aggregates
Hello It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. create aggregate la(text, text) (SFUNC=listagg2_transfn, STYPE=internal, FINALFUNC=listagg_finalfn); postgres=# select la(town,',') from country; la --- Prague,Brno,Bratislava,Kosice (1 row) but when I add ORDER BY clause postgres=# select la(town order by town,',') from country; ERROR: function la(character varying) does not exist LINE 1: select la(town order by town,',') from country; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. postgres=# Probably it don't use correct count of parameters, because when I add one parameter aggregate: postgres=# create aggregate la(text) (SFUNC=listagg1_transfn, STYPE=internal, FINALFUNC=listagg_finalfn); CREATE AGGREGATE postgres=# select la(town order by town,',') from country; la BratislavaBrnoKosicePrague (1 row) Then it working, but it call wrong aggregates. Regards Pavel Stehule -- 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] PQescapeByteaConn and the new hex encoding
Matteo Beccati wrote: Il 25/12/2009 18:54, Tom Lane ha scritto: Matteo Beccati writes: However, before taking a look at the actual code and understanding its behaviour, I tried using "SET bytea_output = 'escape'" and I was expecting PQescapeByteaConn to honour it. Why? PQescapeByteaConn's charter is to produce something that will work on the given connection, no more and no less. Makes complete sense :) I was just trying to find a way to get the PHP function pg_escape_bytea (which uses PQescapeByteaConn if available) to generate a backwards compatible escaped string. It's probably just a corner case though and it can be dealt with at the client side, if necessary. There is plenty of reason to use hex format wherever possible - we expect it to be much more efficient to process on the server side. 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
[HACKERS] proposal for 8.5, listagg aggregate function, WIP patch
Hello
I am returning to discussion
http://archives.postgresql.org/pgsql-hackers/2009-12/msg01378.php
I propose a new aggregate function - listagg. This function
concatenate values to string. If this function is used with two
parameters, then second parameter is used as delimiter. NULL input
values are ignored like other aggregates. If all values are NULL, then
result is NULL. When delimiter is omitted, then values are
concatenated without any delimiter.
This function could be replaced with
array_to_string(array_agg(),delimiter). It has same functionality, but
different implementation. listagg should be (and it is) faster,
because hasn't array overhead. In my tests - listagg is about 25%
faster. Mainly, listagg is shorter. Because we cannot well wrap
aggregates, I propose integrate this function. There are precedent -
function generate_sequences. It should be replaced by
generate_series(array_lower(), array_upper()), but it hasn't same
effectiveness.
Using:
postgres=# select * from country ;
town| state
+---
Prague | cs
Brno | cs
Bratislava | sk
Kosice | sk
(4 rows)
postgres=# select listagg(town,',') from country group by state;
listagg
---
Bratislava,Kosice
Prague,Brno
(2 rows)
Comments?
Regards
Pavel Stehule
*** ./doc/src/sgml/func.sgml.orig 2009-12-19 18:49:50.0 +0100
--- ./doc/src/sgml/func.sgml 2009-12-25 18:01:13.281367152 +0100
***
*** 1789,1794
--- 1789,1798
+
+ See also about the aggregate
+ function listagg.
+
Built-in Conversions
***
*** 9789,9794
--- 9793,9816
+
+
+ listagg
+
+
+ listagg(expression
+ [, expression ] )
+
+
+text
+
+
+text
+
+ input values concatenated into an string
+
+
+
max(expression)
any array, numeric, string, or date/time type
same as argument type
*** ./src/backend/utils/adt/varchar.c.orig 2009-07-11 23:15:32.0 +0200
--- ./src/backend/utils/adt/varchar.c 2009-12-25 15:41:42.928370326 +0100
***
*** 18,27
--- 18,40
#include "access/hash.h"
#include "access/tuptoaster.h"
#include "libpq/pqformat.h"
+ #include "lib/stringinfo.h"
+ #include "nodes/execnodes.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "mb/pg_wchar.h"
+ /* type for state data of listagg function */
+ typedef struct
+ {
+ StringInfo strInfo;
+ char delimiter[1]; /* separator string - one or more chars */
+ } ListAggState;
+
+ static ListAggState *accumStringResult(ListAggState *state,
+ text *elem,
+ text *delimiter,
+ MemoryContext aggcontext);
/* common code for bpchartypmodin and varchartypmodin */
static int32
***
*** 995,997
--- 1008,1163
PG_RETURN_INT32(result);
}
+
+ /
+ * listagg
+ *
+ * Concates values and returns string.
+ *
+ * Syntax:
+ * FUNCTION listagg(string varchar, delimiter varchar = '')
+ * RETURNS varchar;
+ *
+ * Note: any NULL value is ignored.
+ *
+ /
+ static ListAggState *
+ accumStringResult(ListAggState *state, text *elem, text *delimiter,
+ MemoryContext aggcontext)
+ {
+ MemoryContext oldcontext;
+
+ /*
+ * when state is NULL, create new state value.
+ */
+ if (state == NULL)
+ {
+ if (delimiter != NULL)
+ {
+ char *dstr = text_to_cstring(delimiter);
+ int len = strlen(dstr);
+
+ oldcontext = MemoryContextSwitchTo(aggcontext);
+ state = palloc(sizeof(ListAggState) + len);
+
+ /* copy delimiter to state var */
+ memcpy(&state->delimiter, dstr, len + 1);
+ }
+ else
+ {
+ oldcontext = MemoryContextSwitchTo(aggcontext);
+ state = palloc(sizeof(ListAggState));
+ state->delimiter[0] = '\0';
+ }
+
+ /* Initialise StringInfo */
+ state->strInfo = NULL;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* only when element isn't null */
+ if (elem != NULL)
+ {
+ char *value = text_to_cstring(elem);
+
+ oldcontext = MemoryContextSwitchTo(aggcontext);
+ if (state->strInfo != NULL)
+ appendStringInfoString(state->strInfo, state->delimiter);
+ else
+ state->strInfo = makeStringInfo();
+
+ appendStringInfoString(state->strInfo, value);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ return state;
+ }
+
+ Datum
+ listagg1_transfn(PG_FUNCTION_ARGS)
+ {
+ MemoryContext aggcontext;
+ ListAggState *state = NULL;
+ text *elem;
+
+ if (fcinfo->context && IsA(fcinfo->context, AggState))
+ aggcontext = ((AggState *) fcinfo->context)->aggcontext;
+ else if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
+ aggcontext = ((WindowAggState *) fcinfo->context)->wincont
Re: [HACKERS] PQescapeByteaConn and the new hex encoding
Il 25/12/2009 18:54, Tom Lane ha scritto: Matteo Beccati writes: However, before taking a look at the actual code and understanding its behaviour, I tried using "SET bytea_output = 'escape'" and I was expecting PQescapeByteaConn to honour it. Why? PQescapeByteaConn's charter is to produce something that will work on the given connection, no more and no less. Makes complete sense :) I was just trying to find a way to get the PHP function pg_escape_bytea (which uses PQescapeByteaConn if available) to generate a backwards compatible escaped string. It's probably just a corner case though and it can be dealt with at the client side, if necessary. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.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] info about patch: using parametrised query in psql
2009/12/25 Tom Lane :
> Robert Haas writes:
>> I think maybe what we need here is a piece of syntax to indicate that a
>> specific parameter should be substituted after first being passed
>> through PQescapeStringConn.
>
> I agree that a global flag that changes the behavior of :foo is a
> seriously bad idea. Alternate syntax would be much better, but how
> exactly can we shoehorn that in? Maybe something like
> :!foo
there are two quoting styles, so we need two syntax. I proposed
:[var] and :{var} - for ident quoting and literal quoting.
Theoretically we could to use :(var) for bytea escaping. :!foo isn't
good idea. It is related to negation operator. Bracket or parenthesis
are good readable and far to some custom pg operators.
Regards
Pavel Stehule
> ie put some non-letter flags between the : and the variable name.
> It would obviously not work to use ::foo, but I think many other
> punctuation characters would be safe (would not conflict with any
> likely SQL usage). We could have a couple of different flags to
> signal whether you want single or double quoting of the variable
> value.
>
> 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] PQescapeByteaConn and the new hex encoding
Matteo Beccati writes: > However, before taking a look at the actual code and understanding its > behaviour, I tried using "SET bytea_output = 'escape'" and I was > expecting PQescapeByteaConn to honour it. Why? PQescapeByteaConn's charter is to produce something that will work on the given connection, no more and no less. 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] Initial refactoring of plperl.c - rebased [PATCH]
Tim Bunce wrote:
I've attached an update of my previous refactoring of plperl.c.
It's been rebased over the current (git) HEAD and has a few
very minor additions.
[snip]
+ -- Test compilation of unicode regex
+ --
+ CREATE OR REPLACE FUNCTION perl_unicode_regex(text) RETURNS INTEGER AS $$
+ # see http://rt.perl.org/rt3/Ticket/Display.html?id=47576
+ return ($_[0] =~ /\x{263A}|happy/i) ? 1 : 0; # unicode smiley
+ $$ LANGUAGE plperl;
This test is failing on my setup at least when the target db is not UTF8
encoded.
Maybe that's a bug we need to fix?
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] info about patch: using parametrised query in psql
Robert Haas writes: > I think maybe what we need here is a piece of syntax to indicate that a > specific parameter should be substituted after first being passed > through PQescapeStringConn. I agree that a global flag that changes the behavior of :foo is a seriously bad idea. Alternate syntax would be much better, but how exactly can we shoehorn that in? Maybe something like :!foo ie put some non-letter flags between the : and the variable name. It would obviously not work to use ::foo, but I think many other punctuation characters would be safe (would not conflict with any likely SQL usage). We could have a couple of different flags to signal whether you want single or double quoting of the variable value. 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] PQescapeByteaConn and the new hex encoding
Hi everyone, I've been playing with the 8.5alpha3 in the last few days. Among other things, I'm making sure that the pgsql PHP extensions still work correctly with the new version. It would seems so, as all the errors in the standard pgsql extension test suite come from the fact that PQescapeByteaConn now defaults to the new "hex" format when connected to a 8.5+ server, which is cool. It's just a matter of updating the tests. However, before taking a look at the actual code and understanding its behaviour, I tried using "SET bytea_output = 'escape'" and I was expecting PQescapeByteaConn to honour it. Not sure if changing the current behaviour is at all possible, desirable and really worth it, but I'm going to hold the patches to the php test suite until I get some feedback here. Thoughts? Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.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] unicode questions
On Thu, Dec 24, 2009 at 5:40 PM, Andrew Dunstan wrote: >> 1) If I set my database and connection encoding to UTF-8, does pg (and >> future versions of it) guarantee that unicode code points are stored >> unmodified? or could it be that pg does some unicode >> normalization/manipulation with them before storing a string, or when >> retrieving a string? >> >> The reason why I'm asking is, I've built a little program that reads >> in and stores text and explicilty analyzes the text at a later point >> in time, also regarding things like if the text is in NFC, NFD or >> neither. and since I want to store them in the database, it is very >> imporant for PG not to fiddle around with the normalization unless my >> program explicitly told PG to do that. > > We don't do any normalization. If the client gives us UTF8 then we store > exactly what it gives us, and return exactly that. OK. > > (This question is not really a -hackers question. The correct forum is > pgsql-general. Please make sure you use the correct forum in future.) Are you sure? The description for -hackers says: "Discussion of current development issues, problems and bugs, and proposed new features.", which seems to be exactly where you'd ask my 2nd question, which is still unanswered. >> >> 2) How far is normalization support in PG? When I checked a long time >> ago, there was no such support. Now that the SQL standard mandates a >> NORMALIZE function that may have changed. Any updates? >> Kind regards. -- 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] Removing pg_migrator limitations
Bruce Momjian wrote: > Bruce Momjian wrote: > > > Well, we might eventually allow addition of values to enums too; the > > > fact that it's not implemented outside pg_migrator right now doesn't > > > mean we won't ever think of a solution. In any case I'm not persuaded > > > that a zero-element enum is totally without value. Think of it like a > > > domain with a "must be null" constraint. > > > > OK, but that is going to expand the my patch. I will probably implement > > zero-element enums first and then go ahead and do the binary upgrade > > part. Zero-element enums will simplify the pg_dump code. > > I have implemented the zero-value option to CREATE TYPE ENUM with the > attached patch. pg_dump also needs a minor edit for this, which will appear in the oid assignment patch. -- Bruce Momjian 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] keywords on .pgpass
Fujii Masao wrote: Hi, pg_hba.conf accepts some keywords like 'all', 'sameuser' and so on. Likewise, I'd like to support the same keywords for .pgpass file. Thought? I don't see the use case for it - .pgpass is for single users, not a whole cluster. And it does support wildcards, which takes care of the 'all' case. In the case of pg_hba.conf we don't know in advance who will actually be connecting. But in the case of .pgpass we do, so the extra utility of 'sameuser', 'samerole' and 'samegroup' in this case is not apparent to me. 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
[HACKERS] keywords on .pgpass
Hi, pg_hba.conf accepts some keywords like 'all', 'sameuser' and so on. Likewise, I'd like to support the same keywords for .pgpass file. Thought? In Streaming Replication, new keyword 'replication' has been introduced into pg_hba.conf to authenticate the standby server. If my proposal will have been accepted, I'll also add the 'replication' keyword into .pgpass file. This keyword is required to specify the password which the standby uses since the connection for replication doesn't correspond to the specific database. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] info about patch: using parametrised query in psql
>
> This makes sense now that you've explained it. Personally, I would
> not choose to use psql as a scripting language, and I think there has
The scripting are not realised directly in psql - psql missing some
basic features still. Usually is used in combination with bash (any
shell) - like starter stored procedures or source of data.
for x in `echo "sql" | psql params db
do
...
done
this combination is relative very strong.
> been some controversy on that point in the past, though I don't
> remember the details. In spite of that, though, it seems to me that
> it does make some sense to provide a mechanism for escaping the value
> stored in a psql variable, since - if nothing else - someone might
> easily want to do the sort of thing you're describing here in an
> interactive session.
>
> However, I think the approach you've taken in this patch is a
> non-starter. You've basically added a global flag that will cause ALL
> variables to be passed in a way that removes the need for them to be
> escaped. That seems pretty inconvenient and awkward. What happens if
> someone wants to do "INSERT INTO :foo VALUES (:bar)"? They're out of
Using a global flags is typical for psql. There are nothing else. I am
thinking about stacked states for epsql, but it isn't some for psql.
psql uses global flags, it uses global variables. I aware of
disadvantages - but I thing so it is in agreement with psql design "do
things simple".
If somebody use variable on wrong place, then result will be a syntax
error. But better fail then be not secure. For full functionality it
needs some explicit syntax for quote_ident - so correct and secure
statement will be:
INSERT INTO :[foo] VALUES (:bar)
There are two ways (three) - both are possible and well, and probably
it is +/- personal preferences who prefer one or second:
a) using parametrised queries - it simple way - bulletproof with limit
- cannot use variable as identifier
b) using some quoting mechanism - it little bit more complex -
PostgreSQL uses two different quoting styles, for somebody isn't
bulletproof, but it could be used everywhere. There are big advantage
- no new global flag - so using should be simpler for beginners.
c) combination
a) INSERT INTO :foo VALUES(:bar) -- isn't possible
b) INSERT INTO :[foo] VALUES(:{bar}) -- I used syntax from epsql fpr
this moment - could be different
c) INSERT INTO :[foo] VALUES(:bar)
I didn't need to (b) or (c), personally I prefer (a), maybe (b). It is
only my personal preference - and I have a good knowledge of
parametrised queries. Typical user can thing different. I am not
strong in it. I'll be satisfied if any form will be supported. I
tested all variants.
> luck. Futhermore, if a psql script that expects the pexec flag to be
> set one way is run with it set the other way, it may either work fine,
> work OK but with a potential security hole, or fail spectacularly. I
> think maybe what we need here is a piece of syntax to indicate that a
> specific parameter should be substituted after first being passed
> through PQescapeStringConn.
PQescapeStringConn is good, but it isn't helper for INSERT INTO :foo.
It is analogy for quote_literal function, not for quote_ident. So we
need enhance PQ function sets. Escaping is little bit slower, but it
isn't important in this case. I agree, potential escaping needs
explicit syntax.
?
Regards
Pavel
>
> Other thoughts?
>
> ...Robert
>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
