Re: [HACKERS] gset updated patch
Just another thought ! When we are setting up the variable using \gset, I feel their should be a provision to drop a particular variable. Internally, all the variables are set into "VariableSpace" linked-list. We should provide a command to Drop a particular variable, because in some cases unnecessary the variable count is increasing & consuming a VariableSpace. We might use two different variables for two different queries, but if we are not going to use the first variable in further execution, then unnecessary we are consuming a space for 1st variable in the "VariableSpace". In such cases, user will drop the 1st variable. This particular feature/mechanism is useful for a queries which returns a single row. So user will be using such technique for multiple queries. In such cases, user might need to create multiple variables. Hence I thoughts so. Let me know if such mechanism is already exists & I am missing the same. On Mon, Nov 19, 2012 at 9:42 PM, Dimitri Fontaine wrote: > "Karl O. Pinc" writes: > > Yes. I'm wrong. For some reason I thought you could use DO to make > > an anonymous code block that would act as a SETOF function, > > allowing RETURN NEXT expr (et-al) to be used in the > > plpgsql code, allowing DO to return table results. > > (Or, perhaps, instead, be used in place of a table in a SELECT > > statement.) Oh well. > > My key for remembering about that point is that DO is a utility command, > not a query. Now, the proposal I pushed last time we opened that very > can of worms was to have inline functions rather than anonymous code > blocks: > >WITH FUNCTION foo(integer) returns bigint language SQL AS $$ > SELECT $1 + 1; >$$, > > Not sure how much that relates to $topic, but still something that > raises in my mind with enough presence that I need to write about it so > that it stops calling for attention :) > > Regards, > -- > Dimitri Fontaine > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
Re: [HACKERS] Rectifying wrong Date outputs
Any inputs further ? On Tue, Mar 22, 2011 at 2:37 PM, Piyush Newe wrote: > Thanks Heikki, Tom & Robert for your valuable inputs. > > According to the code, PG is behaving what is mentioned below. > > 1. Format = Y > 0 ... 9 = 2000 ... 2009 (we are always adding 2000 to the year) > > 2. Format = YY > 00 ... 69 = 2000 ... 2069 (we are adding 2000 to the year) > 70 ... 99 = 1970 ... 1999 (we are adding 1900 to the year) > > 3. Format = YYY > 100 ... 999 = 1100 ... 1999 (we are adding 1000 to the year) > 000 ... 099 = 2000 ... 2099 (we are adding 2000 to the year) > > 4. Format = > Any number -4712 to > > If we closely look at the code, we can say that, if the given INPUT value > is not in the range of the DESIRED format (i.e. Y/YY/YYY/), then it > results some weired YEAR. > > e.g. > 1. TO_DATE('01-jan-2010', 'DD-MON-Y') > Here it falls in the 1st format case i.e. "Y". As per the code, we are > blindly adding 2000 in the year value, hence the result is returned as > 4010. > > 2. TO_DATE('01-jan-2010', 'DD-MON-YYY') > Here it falls in the 3rd case i.e. "YYY". As per the code, without checking > the input we are adding 1000 to the value & hence it results 3010. > > IMHO, before deciding the century we should check what is the INPUT. This > check is missing in the code. As Heikki said, we really don't have such > document anywhere. We need to atleast document what we are doing. However, > if we are doing something vague then we need to decide what we are really > going to follow. We need to follow one standard, then it might be either > Oracle or something else. > > It might happened that the given input contains the Century. In this case, > we should intelligent enough and decide not to add anything to the year. To > avoid this situation, I still feel we have to follow the format given by the > user. i.e. "if the number of digits specified exceeds the number of Y", > lets throw an error. This will make our life easier & relatively easier to > fix. > > Robert, > If we follow the standard what Oracle is using, we will not break any > case. > > One more observation in Oracle > > SQL> SELECT to_char(TO_DATE('01-jan-0001', 'DD-MON-YY'), 'DD-MON-') > from dual; > > TO_CHAR(TO_DATE('01- > > 01-JAN-0001 > > SQL> SELECT to_char(TO_DATE('01-jan-1', 'DD-MON-YY'), 'DD-MON-') from > dual; > > TO_CHAR(TO_DATE('01- > > 01-JAN-2001 > > If we observe this closely, in the later case, century is not given in the > input, hence Oracle is taking the current century. But the first case is bit > puzzling. Because if we convert the '0001' to number it is still interprets > as 1, then why it is not adding current century their ? I think it is > checking the INPUT & accordingly taking the decision. > > Anyways, will it work if we check the input before deciding the century ? > > > On Mon, Mar 21, 2011 at 10:35 PM, Robert Haas wrote: > >> On Mon, Mar 21, 2011 at 12:39 PM, Tom Lane wrote: >> > Robert Haas writes: >> >> On Mon, Mar 21, 2011 at 10:18 AM, Tom Lane wrote: >> >>> As far as I can see, that would completely destroy the use-case of >> >>> trying to parse a string where there's not non-digit delimiters and >> >>> so you have to take exactly the specified number of digits, not more. >> > >> >> Yeah, I thought about that, but it seems that use case is already >> >> hopelessly broken in both PostgreSQL and Oracle, so I'm disinclined to >> >> worry about it. >> > >> > How so? >> > >> > regression=# select to_date('20110321', 'MMDD'); >> > to_date >> > >> > 2011-03-21 >> > (1 row) >> > >> > regression=# select to_date('110321', 'YYMMDD'); >> > to_date >> > >> > 2011-03-21 >> > (1 row) >> > >> > If you break the latter case, I am sure the villagers will be on your >> > doorstep shortly. >> >> Oh, dear. No wonder this code is so hard to get right. >> >> -- >> Robert Haas >> EnterpriseDB: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> > > > > -- > -- > Piyush S Newe > Principal Engineer > EnterpriseDB > office: +91 20 3058 9500 > www.enterprisedb.com > > Website: www.enterprisedb.com > E
Re: [HACKERS] Rectifying wrong Date outputs
Thanks Heikki, Tom & Robert for your valuable inputs. According to the code, PG is behaving what is mentioned below. 1. Format = Y 0 ... 9 = 2000 ... 2009 (we are always adding 2000 to the year) 2. Format = YY 00 ... 69 = 2000 ... 2069 (we are adding 2000 to the year) 70 ... 99 = 1970 ... 1999 (we are adding 1900 to the year) 3. Format = YYY 100 ... 999 = 1100 ... 1999 (we are adding 1000 to the year) 000 ... 099 = 2000 ... 2099 (we are adding 2000 to the year) 4. Format = Any number -4712 to If we closely look at the code, we can say that, if the given INPUT value is not in the range of the DESIRED format (i.e. Y/YY/YYY/), then it results some weired YEAR. e.g. 1. TO_DATE('01-jan-2010', 'DD-MON-Y') Here it falls in the 1st format case i.e. "Y". As per the code, we are blindly adding 2000 in the year value, hence the result is returned as 4010. 2. TO_DATE('01-jan-2010', 'DD-MON-YYY') Here it falls in the 3rd case i.e. "YYY". As per the code, without checking the input we are adding 1000 to the value & hence it results 3010. IMHO, before deciding the century we should check what is the INPUT. This check is missing in the code. As Heikki said, we really don't have such document anywhere. We need to atleast document what we are doing. However, if we are doing something vague then we need to decide what we are really going to follow. We need to follow one standard, then it might be either Oracle or something else. It might happened that the given input contains the Century. In this case, we should intelligent enough and decide not to add anything to the year. To avoid this situation, I still feel we have to follow the format given by the user. i.e. "if the number of digits specified exceeds the number of Y", lets throw an error. This will make our life easier & relatively easier to fix. Robert, If we follow the standard what Oracle is using, we will not break any case. One more observation in Oracle SQL> SELECT to_char(TO_DATE('01-jan-0001', 'DD-MON-YY'), 'DD-MON-') from dual; TO_CHAR(TO_DATE('01- 01-JAN-0001 SQL> SELECT to_char(TO_DATE('01-jan-1', 'DD-MON-YY'), 'DD-MON-') from dual; TO_CHAR(TO_DATE('01- 01-JAN-2001 If we observe this closely, in the later case, century is not given in the input, hence Oracle is taking the current century. But the first case is bit puzzling. Because if we convert the '0001' to number it is still interprets as 1, then why it is not adding current century their ? I think it is checking the INPUT & accordingly taking the decision. Anyways, will it work if we check the input before deciding the century ? On Mon, Mar 21, 2011 at 10:35 PM, Robert Haas wrote: > On Mon, Mar 21, 2011 at 12:39 PM, Tom Lane wrote: > > Robert Haas writes: > >> On Mon, Mar 21, 2011 at 10:18 AM, Tom Lane wrote: > >>> As far as I can see, that would completely destroy the use-case of > >>> trying to parse a string where there's not non-digit delimiters and > >>> so you have to take exactly the specified number of digits, not more. > > > >> Yeah, I thought about that, but it seems that use case is already > >> hopelessly broken in both PostgreSQL and Oracle, so I'm disinclined to > >> worry about it. > > > > How so? > > > > regression=# select to_date('20110321', 'MMDD'); > > to_date > > > > 2011-03-21 > > (1 row) > > > > regression=# select to_date('110321', 'YYMMDD'); > > to_date > > > > 2011-03-21 > > (1 row) > > > > If you break the latter case, I am sure the villagers will be on your > > doorstep shortly. > > Oh, dear. No wonder this code is so hard to get right. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
Re: [HACKERS] Rectifying wrong Date outputs
On Thu, Mar 17, 2011 at 7:56 PM, Tom Lane wrote: > Robert Haas writes: > > On Thu, Mar 17, 2011 at 9:46 AM, Alvaro Herrera > > wrote: > >> Keep in mind that the datetime stuff was abandoned by the maintainer > >> some years ago with quite some rough edges. Some of it has been fixed, > >> but a lot of bugs remain. Looks like this is one of those places and it > >> seems appropriate to spend some time fixing it. Since it would involve > >> a behavior change, it should only go to 9.2, of course. > > > I wouldn't object to fixing the problem with # of digits > # of Ys in > > 9.1, if the fix is simple and clear-cut. I think we are still > > accepting patches to make minor tweaks, like the tab-completion patch > > I committed yesterday. It also doesn't bother me tremendously if we > > push it off, but I don't think that anyone's going to be too sad if > > TO_DATE('01-jan-2010', 'DD-MON-YYY') starts returning something more > > sensible than 3010-01-01. > > Agreed, it's certainly not too late for bug fixes in 9.1. I agree > that this isn't something we would want to tweak in released branches, > but 9.1 isn't there yet. > > I feel the patch for the same would be easier and was attached in the initial mail of this mail thread. For your ready reference, I am attaching the same patch here again. > Having said that, it's not entirely clear to me what sane behavior is > here. Personally I would expect that an n-Ys format spec would consume > at most n digits from the input. Otherwise how are you going to use > to_date to pick apart strings that don't have any separators? So > I think the problem is actually upstream of the behavior complained of > here. However, what we should first do is see what Oracle does in such > cases, because the main driving factor for these functions is Oracle > compatibility not what might seem sane in a vacuum. > > Following is the extended chart which is comparing the behavior of Oracle, PG & EDBAS. *Data Format Oracle PostgreSQL EDBAS* TO_DATE('01-jan-1', 'DD-MON-Y') 01-JAN-2011 01-JAN-2001 01-JAN-2001 TO_DATE('01-jan-1', 'DD-MON-YY') 01-JAN-2001 01-JAN-2001 01-JAN-2001 TO_DATE('01-jan-1', 'DD-MON-YYY') 01-JAN-2001 01-JAN-2001 01-JAN-2001 TO_DATE('01-jan-1', 'DD-MON-') 01-JAN-0001 01-JAN-0001 01-JAN-0001 In this case, all the cases are in sync except the 1st one. I didn't understand why Oracle is interpreting year '1' as '2011'. *Data Format Oracle PostgreSQL EDBAS* TO_DATE('01-jan-10', 'DD-MON-Y') Error 01-JAN-2010 Error TO_DATE('01-jan-10', 'DD-MON-YY') 01-JAN-2010 01-JAN-2010 01-JAN-2010 TO_DATE('01-jan-10', 'DD-MON-YYY') 01-JAN-2010 01-JAN-2010 01-JAN-2010 TO_DATE('01-jan-10', 'DD-MON-') 01-JAN-0010 01-JAN-0010 01-JAN-0010 In this case, it seems in last 3 cases PostgreSQL is behaving correctly. Oracle is throwing error in 1st case since the Format ('Y') is lesser than the actual value ('10'). But PostgreSQL is ignoring this case and throwing whatever is input. The output is might not be the same was user is expecting. *Data Format Oracle PostgreSQL EDBAS* TO_DATE('01-jan-067', 'DD-MON-Y') Error 01-JAN-2067 Error TO_DATE('01-jan-111', 'DD-MON-YY') 01-JAN-0111 01-JAN-2011 Error TO_DATE('01-jan-678', 'DD-MON-YYY') 01-JAN-2678 01-JAN-1678 01-JAN-2678 TO_DATE('01-jan-001', 'DD-MON-') 01-JAN-0001 01-JAN-0001 01-JAN-0001 In this case, just last case was correct in PG. Rest other cases are not in sync with Oracle, rather the output is vague. *Data Format Oracle PostgreSQL EDBAS* TO_DATE('01-jan-2010', 'DD-MON-Y') Error 01-JAN-4010 Error TO_DATE('01-jan-2010', 'DD-MON-YY') 01-JAN-2010 01-JAN-3910 Error TO_DATE('01-jan-2010', 'DD-MON-YYY') Error 01-JAN-3010 Error TO_DATE('01-jan-2010', 'DD-MON-') 01-JAN-2010 01-JAN-2010 01-JAN-2010 In this case, PG is giving wrong output in first 3 cases. Those need to get rectified. Oracle is throwing error in 1st and 3rd case and the reason is, the format is lesser than the actual value. It seems this rule is not applicable for 2nd case in Oracle. In all above mentioned cases, the observation is, If the # Ys are lesser than the # of digits,, then it should throw an error. Only in case of 'YY', its not correct, unless the year is later than . In this way, we can fix the wrong outputs in PG. >regards, tom lane > -- -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving,
Re: [HACKERS] Rectifying wrong Date outputs
Sorry for creating the confusion. The table drawn was PostgreSQL vs EDB Advanced Server. Thanks Burce for clarification. For the 1-digit, 2-digit & 3-digit Year inputs, as I said, I didn't see any document in PG which will explain what would be the century considered if it is not given. If I missed out it somewhere please let me know. I refer to following link which explains the behavior of Oracle. http://forums.oracle.com/forums/thread.jspa?threadID=312239 Now, if 1. # of digits given is greater than the # of Ys i.e. postgres=# select to_date('01-jan-111', 'DD-MON-Y'); to_date 2111-01-01 (1 row) What we should do ? Either we should throw an error or we should give what user has provided. IMHO, we should follow what format is given by user. However, even if the 'format' gets wrong rather invalid, it is not throwing any error. e.g. postgres=# select to_date('01-jan-111', 'DD-MON-Y POSTGRES'); <<<< Look at this to_date 2111-01-01 (1 row) 2. # of digits given is less than the # of Ys Consider following case, postgres=# select to_date('01-jan-6', 'DD-MON-Y'), to_date('01-jan-6', 'DD-MON-'); to_date | to_date + 2006-01-01 | 0006-01-01 Why this behaviour not predictable ? I think we are always considering the current century, if it is not provided. If I missed out any related document, please share. And yes, postgres=# select to_date('01-jan-1761', 'DD-MON-Y'); to_date 3761-01-01<<<- Look at this. (1 row) Definitely, their is a bug in this case. Am I missing something ? -Piyush On Thu, Mar 17, 2011 at 3:30 AM, Bruce Momjian wrote: > Robert Haas wrote: > > On Wed, Mar 16, 2011 at 5:52 PM, Bruce Momjian wrote: > > > Robert Haas wrote: > > >> On Wed, Mar 16, 2011 at 8:21 AM, Piyush Newe > > >> wrote: > > >> > Data Format ?? ? ? ? ? ? ? ?PostgreSQL EDBAS > > >> > TO_DATE('01-jan-10', ?'DD-MON-Y') ?? ? ? ?2010-01-01 Error > > >> > TO_DATE('01-jan-10', ?'DD-MON-YY') ?? ? ? ?2010-01-01 01-JAN-2010 > > >> > TO_DATE('01-jan-10', ?'DD-MON-YYY') 2010-01-01 01-JAN-2010 > > >> > TO_DATE('01-jan-10', ?'DD-MON-') 0010-01-01 01-JAN-0010 > > >> > In this case, it seems in last 3 cases PG is behaving correctly. > Whereas in > > >> > 1st case the output is not correct since the Format ('Y') is lesser > than the > > >> > actual input ('10'). But PG is ignoring this condition and throwing > whatever > > >> > is input. The output year is might not be the year, what user is > expecting. > > >> > Hence PG should throw an error. > > >> > > >> I can't get worked up about this. ?If there's a consensus that > > >> throwing an error here is better, fine, but on first blush the PG > > >> behavior doesn't look unreasonable to me. > > >> > > >> > Data Format ?? ? ? ? ? ? ? ?PostgreSQL EDBAS > > > > > > To clarify, the user is reporting EDB Advanced Server, though the > > > community PG has the same issues, or at least similar; ?with git HEAD: > > > > > > ? ? ? ?test=> SELECT TO_DATE('01-jan-2010', ?'DD-MON-YY'); > > > ? ? ? ? ?to_date > > > ? ? ? ? > > > ? ? ? ? 3910-01-01 > > > ? ? ? ?(1 row) > > > > Actually, I think he's comparing PostgreSQL to Advanced Server. > > Oh, I understand now. I was confused that the headings didn't line up > with the values. I see now the first value is community PG and the > second is EDBAS. > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > -- -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
[HACKERS] Rectifying wrong Date outputs
Hi, I was randomly testing some date related stuff on PG & observed that the outputs were wrong. e.g. postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YY'); to_date 3910-01-01 <- Look at this (1 row) postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-'); to_date 2010-01-01 (1 row) User can provide the year in any format i.e. 1-digit to 4-digit, in this case the internal representation of century should be logically correct. Considering this situation, I am drawing the table below & logging my observation here. This might help us to demonstrate where we are lacking in PG. *Data Format PostgreSQL EDBAS* *TO_DATE('01-jan-1', 'DD-MON-Y') 2001-01-01 01-JAN-2001* *TO_DATE('01-jan-1', 'DD-MON-YY') 2001-01-01 01-JAN-2001* *TO_DATE('01-jan-1', 'DD-MON-YYY') 2001-01-01 01-JAN-2001* *TO_DATE('01-jan-1', 'DD-MON-') 0001-01-01 01-JAN-0001* In this case, all the cases seems correct. Also the YEAR part in the output is seems logical. *Data Format PostgreSQL EDBAS* *TO_DATE('01-jan-10', 'DD-MON-Y') 2010-01-01 Error * *TO_DATE('01-jan-10', 'DD-MON-YY') 2010-01-01 01-JAN-2010* *TO_DATE('01-jan-10', 'DD-MON-YYY') 2010-01-01 01-JAN-2010* *TO_DATE('01-jan-10', 'DD-MON-') 0010-01-01 01-JAN-0010* In this case, it seems in last 3 cases PG is behaving correctly. Whereas in 1st case the output is not correct since the Format ('Y') is lesser than the actual input ('10'). But PG is ignoring this condition and throwing whatever is input. The output year is might not be the year, what user is expecting. Hence PG should throw an error. *Data Format PostgreSQL EDBAS* *TO_DATE('01-jan-067', 'DD-MON-Y') 2067-01-01 Error* *TO_DATE('01-jan-111', 'DD-MON-YY') 2011-01-01 Error* *TO_DATE('01-jan-678', 'DD-MON-YYY') 1678-01-01 01-JAN-2678* *TO_DATE('01-jan-001', 'DD-MON-') 0001-01-01 01-JAN-0001* In this case, only last case seems correct in PG. Rest other cases are might not be logical, rather the output is vague. In PG, I haven't seen any document which is saying something like this, if year is 111...999 then the century would be 2000 and 001...099 then then century would be 1000. However, the 1st and 2nd case should throw an error since the output format ('Y' & 'YY') are really not matching with the Input ('067' & '111'), respectively. *Data Format PostgreSQL EDBAS* *TO_DATE('01-jan-2010', 'DD-MON-Y') 4010-01-01 Error* *TO_DATE('01-jan-2010', 'DD-MON-YY') 3910-01-01 Error * *TO_DATE('01-jan-2010', 'DD-MON-YYY') 3010-01-01 Error * *TO_DATE('01-jan-2010', 'DD-MON-') 2010-01-01 01-JAN-2010* In this case, PG is giving wrong output in first 3 cases. Those need to get rectified. Again it should throw error in these cases, because Output Format is not matching with Input Data. The exception here is the 2nd case, where century is well-defined. After observing the all above cases, the summary would be, if the output format is lesser than the actual input value, then it should throw an error. Considering this thumb rule, we can fix the wrong outputs in PG. I have made necessary changes to the code & attaching the patch with this email. In the patch, I have written one centralize function which will decide the century depends upon the given Input format. Thoughts ? Any other ideas on this ? -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index aba1145..ad42126 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -989,6 +989,7 @@ static DCHCacheEntry *DCH_cache_getnew(char *str); static NUMCacheEntry *NUM_cache_search(char *str); static NUMCacheEntry *NUM_cache_getnew(char *str); static void NUM_cache_remove(NUMCacheEntry *ent); +static int DecideCentury(int *in, int len); /* -- @@ -2733,21 +2734,14 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out) case DCH_IYYY: from_char_parse_int(&out->year, &s, n); out->yysz = 4; +DecideCentury(&out->year, out->yysz); s += SKIP_THth(n->suffix); bre
[HACKERS] Issue with ReRaise in PG
Hi, Please consider the following test case > CREATE OR REPLACE FUNCTION raisetest() returns void AS $$ BEGIN BEGIN RAISE syntax_error; EXCEPTION WHEN syntax_error THEN BEGIN raise notice 'exception thrown in inner block, reraising'; RAISE; EXCEPTION WHEN OTHERS THEN raise notice 'RIGHT - exception caught in innermost block'; END; END; EXCEPTION WHEN OTHERS THEN raise notice 'WRONG - exception caught in outer block'; END; $$ LANGUAGE plpgsql; > select raisetest(); NOTICE: exception thrown in inner block, reraising NOTICE: WRONG - exception caught in outer block block raisetest --- (1 row) The output of the above function seems to be wrong. Ideally the Exception should have caught in the inner most block instead of the outer block. Below I am sharing my obsevation while debuging this issue. When we give RAISE without the exception name statement, it is internally returning PLPGSQL_RC_RERAISE instead of jumping to the EXCEPTION block of the current Begin-End Block. This will force engine to eliminate/skip the current block's EXCEPTION block. This is the reason its got caught in the next exception block. To fix this, instead of returning PLPGSQL_RC_RERAISE from the function, we will rethrow the exception if their is no EXCEPTION name given to the RAISE statement. When their is RAISE (without exception name) statement, it is been assume that their must be some exception already raised earlier. We are now storing the 'errordata' into the 'estate' structure, while raising the exception. Now since we are not returning PLPGSQL_RC_RERAISE statement, I have also removed the related redundunt code in the pl_exec.c. The testcase mentioned above is behaving correctly like postgres=# select raisetest(); NOTICE: exception thrown in inner block, reraising NOTICE: RIGHT - exception caught in innermost block raisetest --- (1 row) Please find attached patch generated on the current branch to fix the problem. -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. Index: src/pl/plpgsql/src/pl_exec.c === RCS file: /repositories/postgreshome/cvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v retrieving revision 1.257 diff -c -p -r1.257 pl_exec.c *** src/pl/plpgsql/src/pl_exec.c 14 Apr 2010 23:52:10 - 1.257 --- src/pl/plpgsql/src/pl_exec.c 23 Apr 2010 09:51:08 - *** plpgsql_exec_function(PLpgSQL_function * *** 327,336 ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("CONTINUE cannot be used outside a loop"))); - else if (rc == PLPGSQL_RC_RERAISE) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("RAISE without parameters cannot be used outside an exception handler"))); else ereport(ERROR, (errcode(ERRCODE_S_R_E_FUNCTION_EXECUTED_NO_RETURN_STATEMENT), --- 327,332 *** plpgsql_exec_trigger(PLpgSQL_function *f *** 695,704 ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("CONTINUE cannot be used outside a loop"))); - else if (rc == PLPGSQL_RC_RERAISE) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("RAISE without parameters cannot be used outside an exception handler"))); else ereport(ERROR, (errcode(ERRCODE_S_R_E_FUNCTION_EXECUTED_NO_RETURN_STATEMENT), --- 691,696 *** exec_stmt_block(PLpgSQL_execstate *estat *** 1132,1138 --- 1124,1136 estate->err_text = NULL; + /* + * Set last_caught_error for the duration of the + * exception handler, so that "RAISE;" can rethrow it. + */ + estate->last_caught_error = edata; rc = exec_stmts(estate, exception->action); + estate->last_caught_error = NULL; free_var(state_var); state_var->value = (Datum) 0; *** exec_stmt_block(PLpgSQL_execstate *estat *** 1141,1150 errm_var->value = (Datum) 0; errm_var->isnull = true; - /* re-throw error if requested by handler */ - if (rc == PLPGSQL_RC_RERAISE)
[HACKERS] Repetition of warning message while REVOKE
Hi, Description: === Repetition of warning message with revoke. How to reproduce : == > create table tbl(col int); > create user usr; > grant select on tbl to usr; > \c postgres usr; > REVOKE SELECT on tbl from usr; Actual output: WARNING: no privileges could be revoked for "tbl" WARNING: no privileges could be revoked for "tbl" WARNING: no privileges could be revoked for "tbl" WARNING: no privileges could be revoked for "tbl" WARNING: no privileges could be revoked for "tbl" WARNING: no privileges could be revoked for "tbl" WARNING: no privileges could be revoked for "tbl" WARNING: no privileges could be revoked for "tbl" REVOKE expected output: === Shouldn't print repetitive warnings. -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
[HACKERS] Correcting Error message
Hi, Consider following testcase, CREATE TABLE footable(id int4, name varchar2(10)); CREATE FUNCTION foofunc(a footable, b integer DEFAULT 10) RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL; CREATE FUNCTION foofunc(a footable, b numeric DEFAULT 10) RETURNS integer AS $$ SELECT 123; $$ LANGUAGE SQL; SELECT (footable.*).foofunc FROM footable; ERROR: column footable.foofunc does not exist LINE 1: SELECT (footable.*).foofunc FROM footable; ^ The error message thrown is seems to be wrong. When I dig into the code, I found in function ParseFuncOrColumn(), if we just add small condition it will throw correct error message. i.e. " function foofunc(footable) is not unique". I have made a slight change in code, which is throwing the correct error message now. The code changes are attached in the patch. -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 39123a7..0536b58 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -265,7 +265,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, * If we are dealing with the attribute notation rel.function, let the * caller handle failure. */ - if (is_column) + if (is_column && fdresult != FUNCDETAIL_MULTIPLE) return NULL; /* -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers