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 dimi...@2ndquadrant.frwrote: Karl O. Pinc k...@meme.com 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
piyush.n...@enterprisedb.comwrote:
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 robertmh...@gmail.comwrote:
On Mon, Mar 21, 2011 at 12:39 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Mon, Mar 21, 2011 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us 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.
--
--
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
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 robertmh...@gmail.com wrote:
On Mon, Mar 21, 2011 at 12:39 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Mon, Mar 21, 2011 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us 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 t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Thu, Mar 17, 2011 at 9:46 AM, Alvaro Herrera
alvhe...@commandprompt.com 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,
[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);
break;
case
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 br...@momjian.us wrote:
Robert Haas wrote:
On Wed, Mar 16, 2011 at 5:52 PM, Bruce Momjian br...@momjian.us wrote:
Robert Haas wrote:
On Wed, Mar 16, 2011 at 8:21 AM, Piyush Newe
piyush.n...@enterprisedb.com 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 br...@momjian.ushttp://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] 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
