to_char incompatibility

-----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 $$
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

I always put security definer as I really think that should be the default
behavior. Anyway, your function should run faster.

Jon

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