Обсуждение: to_char incompatibility
On Oracle:
SQL> select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss') from
dual;
TO_DATE('
---------
31-DEC-07
On PostgreSQL:
select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy 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/
> -----Original Message-----
> On Oracle:
>
> SQL> select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss') from
> dual;
>
> TO_DATE('
> ---------
> 31-DEC-07
>
> On PostgreSQL:
>
> select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy 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-yyyy') from dual;
TO_DATE('
---------
31-DEC-07
SQL> select to_date('31-DEC-2007', 'dd - mon - yyyy') 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 - yyyy');
ERROR: invalid value for MON/Mon/mon
# select to_date('31 -dec-2007', 'dd-mon-yyyy');
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-yyyy'); fn_to_date
---------------------2007-12-31 00:00:00
(1 row)
# select fn_to_date('31-dec-2007', 'dd- mon-yyyy'); 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-yyyy hh24:mi:ss');
fn_to_date
---------------------2007-12-31 00:00:00
(1 row)
Jon
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 $$ BEGINRETURN 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
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:pavel.stehule@gmail.com] > 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
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
> > 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
"Roberts, Jon" <Jon.Roberts@asurion.com> 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
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
> > 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
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
Josh Berkus <josh@agliodbs.com> 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
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Josh Berkus <josh@agliodbs.com> 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!
Am Donnerstag, 10. Januar 2008 schrieb Roberts, Jon:
> > On PostgreSQL:
> >
> > select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy 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 YYYY, 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', 'YYYY-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/
Peter Eisentraut <peter_e@gmx.net> 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
Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> 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
Andrew Dunstan wrote: > > > Tom Lane wrote: > > Peter Eisentraut <peter_e@gmx.net> 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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Peter Eisentraut wrote:
> Am Donnerstag, 10. Januar 2008 schrieb Roberts, Jon:
> > > On PostgreSQL:
> > >
> > > select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy 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 YYYY, why does it create a six-digit year?
>
If we didn't print +4 digits for YYYY we would need new patterns for >4
digit years, like 5 and 6-digit years. Our documentation is at least
clear:
<entry><literal>YYYY</literal></entry> <entry>year (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', 'YYYY-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 <bruce@momjian.us> http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Jan 17, 2008 8:22 AM, Bruce Momjian <bruce@momjian.us> wrote: > Andrew Dunstan wrote: > > Tom Lane wrote: > > > Peter Eisentraut <peter_e@gmx.net> 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