Re: [HACKERS] to_char incompatibility
On Jan 17, 2008 8:22 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Andrew Dunstan wrote: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: A further example shows that to_date seems to have little error checking altogether: TODO list item? We have something on hold for 8.4: http://momjian.us/mhonarc/patches_hold/msg00319.html Yeah, the date/time formatting code is rife with these kinds of issues. I've been poking around in there for a while. The patch Bruce mentioned was my attempt to refactor some of the code, making it easier to work with. Depending on how the review of that patch goes, I'll then be putting in some effort to make to_date actually check that the formatting pattern is legal (see thread at http://archives.postgresql.org/pgsql-hackers/2007-07/msg00513.php). So, I am keen to improve this area, but it's slow going. I don't have a great deal of experience with Postgres, C isn't my native tongue and the review cycle is ponderous. Cheers, BJ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] to_char incompatibility
Am Donnerstag, 10. Januar 2008 schrieb Roberts, Jon:
On PostgreSQL:
select to_date('31-DEC-200700:00:00', 'dd-mon- hh24:mi:ss');
to_date
--
200700-12-31
Oracle removes all white spaces in the date you pass in and the date
format.
I don't have a strong opinion on the whitespace handling, but then I wonder
1. If I put four , why does it create a six-digit year?
2. If it does create a six digit year, the rest of the pattern doesn't match
anymore, so it should error.
A further example shows that to_date seems to have little error checking
altogether:
select to_date('17.12.1978', '-MM-DD');
to_date
0017-12-19
That can't possibly be a good idea, in the interest of the robustness of
applications built on this.
select to_date('whatever', 'foobar');
to_date
---
0001-01-01 BC
Yah.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] to_char incompatibility
Peter Eisentraut [EMAIL PROTECTED] writes: A further example shows that to_date seems to have little error checking altogether: Yeah, that's been one of the main knocks on that code since day one. Somebody needs to spend a whole lot of time on it, and the original author has left the project ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] to_char incompatibility
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: A further example shows that to_date seems to have little error checking altogether: Yeah, that's been one of the main knocks on that code since day one. Somebody needs to spend a whole lot of time on it, and the original author has left the project ... TODO list item? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] to_char incompatibility
Andrew Dunstan wrote: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: A further example shows that to_date seems to have little error checking altogether: Yeah, that's been one of the main knocks on that code since day one. Somebody needs to spend a whole lot of time on it, and the original author has left the project ... TODO list item? We have something on hold for 8.4: http://momjian.us/mhonarc/patches_hold/msg00319.html -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] to_char incompatibility
Peter Eisentraut wrote:
Am Donnerstag, 10. Januar 2008 schrieb Roberts, Jon:
On PostgreSQL:
select to_date('31-DEC-200700:00:00', 'dd-mon- hh24:mi:ss');
to_date
--
200700-12-31
Oracle removes all white spaces in the date you pass in and the date
format.
I don't have a strong opinion on the whitespace handling, but then I wonder
1. If I put four , why does it create a six-digit year?
If we didn't print +4 digits for we would need new patterns for 4
digit years, like 5 and 6-digit years. Our documentation is at least
clear:
entryliteral/literal/entry
entryyear (4 and more digits)/entry
2. If it does create a six digit year, the rest of the pattern doesn't match
anymore, so it should error.
A further example shows that to_date seems to have little error checking
altogether:
select to_date('17.12.1978', '-MM-DD');
to_date
0017-12-19
Yea, I can't find any way to suppress those leading zeros, except by
using the proper number of Y's.
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] to_char incompatibility
You'll have to explain to Oracle and their customers that Oracle's security model is not a great idea then. I'd love to, and in fact *do* whenever I'm given the chance. In fact, Oracle's security model is pretty bad; the reason why Oracle advertises Unbreakable so hard is that they have a terrible record of security exploits, making them nearly as bad as MySQL. Heck, these days you're better off using MSSQL than Oracle to protect your data. LOL! I'm not going to trade jabs with you on which product has more exploits because that is just stupid. I'm stating that the *model* for Oracle security is very similar to the non-default behavior of PostgreSQL of using security definer. I prefer this model. I think it is a great idea and I mention Oracle because it is highly reputable database company that uses this model. For instance, if I want to allow a user to insert data, I most likely want them to ONLY do it through my method. That means creating a function with security definer set and granting the user execute on the function. I don't want the user to select my sequence or inserting data directly to the table. Also, there is no need to argue this because we can have it both ways. Security definer is an option and I recommend to always use it over the default. If you don't want to use it, don't. Jon ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] to_char incompatibility
Jon, You'll have to explain to Oracle and their customers that Oracle's security model is not a great idea then. I'd love to, and in fact *do* whenever I'm given the chance. In fact, Oracle's security model is pretty bad; the reason why Oracle advertises Unbreakable so hard is that they have a terrible record of security exploits, making them nearly as bad as MySQL. Heck, these days you're better off using MSSQL than Oracle to protect your data. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] to_char incompatibility
Jon, Also, there is no need to argue this because we can have it both ways. Security definer is an option and I recommend to always use it over the default. If you don't want to use it, don't. Security Definer has ramifications in PostgreSQL which I don't think it does in Oracle. Particularly, see: http://www.postgresql.org/docs/techdocs.77 -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] to_char incompatibility
Josh Berkus [EMAIL PROTECTED] writes: Security Definer has ramifications in PostgreSQL which I don't think it does in Oracle. Particularly, see: http://www.postgresql.org/docs/techdocs.77 BTW, that article needs to be updated to show the (much easier) way to do it as of 8.3. I concur that make all your functions security definer by default is unlikely to make a system more secure overall --- it'll just move the problems around. Especially if it's applied blindly by someone who stopped reading at that point. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] to_char incompatibility
Tom Lane [EMAIL PROTECTED] writes: Josh Berkus [EMAIL PROTECTED] writes: Security Definer has ramifications in PostgreSQL which I don't think it does in Oracle. Particularly, see: http://www.postgresql.org/docs/techdocs.77 BTW, that article needs to be updated to show the (much easier) way to do it as of 8.3. I concur that make all your functions security definer by default is unlikely to make a system more secure overall --- it'll just move the problems around. Especially if it's applied blindly by someone who stopped reading at that point. I think the reason Oracle DBAs are accustomed to using security definer for everything is that it has some further effects aside from selecting the privileges to use. Remember that in Oracle the current role also controls what we call the search_path. So selecting security definer is effectively selecting lexical scoping over dynamic scoping. It nails down all the references in the package or function at compile time. That does have more robust security implications. It's also supposed to perform better. And experience shows lexical scoping makes it easier to build large complex systems without getting bogged down in lots of action-at-a-distance. In Postgres the performance consequence is reversed. We have a performance *hit* for security definer. And the pl interpreters don't behave any differently as far as when they do their lookups. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] to_char incompatibility
On Oracle:
SQL select to_date('31-DEC-200700:00:00', 'dd-mon- hh24:mi:ss') from
dual;
TO_DATE('
-
31-DEC-07
On PostgreSQL:
select to_date('31-DEC-200700:00:00', 'dd-mon- hh24:mi:ss');
to_date
--
200700-12-31
Now the input value is probably a mistake. But according to the theory
described in the PostgreSQL documentation that to_char more or less ignores
whitespace unless FX is used, I think the Oracle behavior is more correct. In
addition, even if it wants to take 6 digits for the year in spite of only 4
Y's, the rest of the format wouldn't match anymore.
Is anyone an Oracle format code expert who can comment on this?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] to_char incompatibility
-Original Message-
On Oracle:
SQL select to_date('31-DEC-200700:00:00', 'dd-mon- hh24:mi:ss') from
dual;
TO_DATE('
-
31-DEC-07
On PostgreSQL:
select to_date('31-DEC-200700:00:00', 'dd-mon- hh24:mi:ss');
to_date
--
200700-12-31
Now the input value is probably a mistake. But according to the theory
described in the PostgreSQL documentation that to_char more or less
ignores
whitespace unless FX is used, I think the Oracle behavior is more correct.
In
addition, even if it wants to take 6 digits for the year in spite of only
4
Y's, the rest of the format wouldn't match anymore.
Is anyone an Oracle format code expert who can comment on this?
Oracle removes all white spaces in the date you pass in and the date format.
SQL select to_date('31 - DEC - 2007', 'dd-mon-') from dual;
TO_DATE('
-
31-DEC-07
SQL select to_date('31-DEC-2007', 'dd - mon - ') from dual;
TO_DATE('
-
31-DEC-07
And then in PostgreSQL with to_timestamp or to_date:
# select to_date('31-dec-2007', 'dd -mon - ');
ERROR: invalid value for MON/Mon/mon
# select to_date('31 -dec-2007', 'dd-mon-');
ERROR: invalid value for MON/Mon/mon
I've used Oracle for years but I think PostgreSQL is actually more accurate.
I put together this function very quickly that will make it behave like
Oracle:
create or replace function fn_to_date(p_date varchar, p_format varchar)
returns timestamp as
$$
declare
v_date varchar;
v_format varchar;
v_timestamp timestamp;
begin
v_date := replace(p_date, ' ', '');
v_format := replace(p_format, ' ', '');
v_timestamp := to_timestamp(v_date, v_format);
return v_timestamp;
exception
when others then
raise exception '%', sqlerrm;
end;
$$
language 'plpgsql' security definer;
# select fn_to_date('31 -dec-2007', 'dd-mon-');
fn_to_date
-
2007-12-31 00:00:00
(1 row)
# select fn_to_date('31-dec-2007', 'dd-mon-');
fn_to_date
-
2007-12-31 00:00:00
(1 row)
Or with your exact example:
# select fn_to_date('31-DEC-200700:00:00', 'dd-mon- hh24:mi:ss');
fn_to_date
-
2007-12-31 00:00:00
(1 row)
Jon
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] to_char incompatibility
small non important note: your function is very expensive exactly same but faster is: CREATE OR REPLACE FUNCTION fn_to_date(p_date varchar, p_format varchar) RETURNS timestamp AS $$ SELECT to_timestamp(replace($1, ' ', ''), replace($2, ' ', '')); $$ LANGUAGE SQL STRICT IMMUTABLE; or CREATE OR REPLACE FUNCTION fn_to_date(p_date varchar, p_format varchar) RETURNS timestamp AS $$ BEGIN RETURN to_timestamp(replace(p_date, ' ', ''), replace(p_format, ' ', '')); END$$ LANGUAGE SQL STRICT IMMUTABLE; there isn't any reason for using security definer and you forgot IMMUTABLE, Regards Pavel Stehule ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] to_char incompatibility
I always put security definer as I really think that should be the default behavior. Anyway, your function should run faster. Jon -Original Message- From: Pavel Stehule [mailto:[EMAIL PROTECTED] Sent: Thursday, January 10, 2008 8:47 AM To: Roberts, Jon Cc: Peter Eisentraut; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] to_char incompatibility small non important note: your function is very expensive exactly same but faster is: CREATE OR REPLACE FUNCTION fn_to_date(p_date varchar, p_format varchar) RETURNS timestamp AS $$ SELECT to_timestamp(replace($1, ' ', ''), replace($2, ' ', '')); $$ LANGUAGE SQL STRICT IMMUTABLE; or CREATE OR REPLACE FUNCTION fn_to_date(p_date varchar, p_format varchar) RETURNS timestamp AS $$ BEGIN RETURN to_timestamp(replace(p_date, ' ', ''), replace(p_format, ' ', '')); END$$ LANGUAGE SQL STRICT IMMUTABLE; there isn't any reason for using security definer and you forgot IMMUTABLE, Regards Pavel Stehule ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] to_char incompatibility
Jon, I always put security definer as I really think that should be the default behavior. Anyway, your function should run faster. That's not a real good idea. A security definer function is like an SUID shell script; only to be used with great care. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] to_char incompatibility
Jon, I always put security definer as I really think that should be the default behavior. Anyway, your function should run faster. That's not a real good idea. A security definer function is like an SUID shell script; only to be used with great care. You'll have to explain to Oracle and their customers that Oracle's security model is not a great idea then. soapbox Executing a function should never require privileges on the underlying objects referenced in it. The function should always run with the rights of the owner of the function, not the user executing it. /soapbox Jon ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] to_char incompatibility
Roberts, Jon [EMAIL PROTECTED] writes: soapbox Executing a function should never require privileges on the underlying objects referenced in it. The function should always run with the rights of the owner of the function, not the user executing it. /soapbox You might want to climb off that soapbox for long enough to read the various security-related threads that have been in this mailing list over the past year or so. Security-definer functions are seriously at risk from trojan-horse exploits; particularly in an extensible system such as Postgres. Certainly there are cases where you want a function to change privilege levels as sketched above. But I'd argue that there are a huge number of cases where a function is just providing convenient shorthand for something the caller could do for himself --- and when that's the case, making it have more/different privileges from the caller is simply taking a risk for no reward. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
