[Spacewalk-devel] Suggestions for sysdate, for PostgreSQL?
On Fri, Jul 09, 2010 at 04:40:12PM +0200, Jan Pazdziora wrote: > > - ... and then it's just business as usual -- fix bugs that you see in > catalina.out and other logs. > - For example, we will need to do something about sysdate. ;-) Hello, what are our possibilities for handling sysdate in PostgreSQL. The word sysdate appears on more than 200 lines in our code base, not counting the schema definition itself where it's already been converted to current_timestamp. On these remaining lines, it is send as part of the SQL queries to the server. Are we able to create an object in PostgreSQL schema which would behave as current_timestamp but which would be called sysdate? Function seems to require parentheses upon invocation so that probably cannot be used. Another possibility is to modify the SQL on the fly, while it's being sent to the server, similar to what I did for the Python stack and the anonymous PL/SQL codes. Would it be reasonable to do a regexp replace on every command sent to the PostgreSQL server? Any performance implications? How would we do that for Java connections? Another option is to modify the sources not to use sysdate but (say) xsysdate(), or maybe now(), and define that xsysdate function both in Oracle and PostgreSQL to do the right thing. The problem with this approach is that it might have potential performance impact on the Oracle side, as having function where it had plain sysdate might prevent it to do optimizations that it was able to do before, thus slowing the operations down. Any other options? What do people think? -- Jan Pazdziora Principal Software Engineer, Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Suggestions for sysdate, for PostgreSQL?
Jan Pazdziora writes: > what are our possibilities for handling sysdate in PostgreSQL. Isn't s/sysdate/current_timestamp/g a good solution? That's standards compliant, unlike sysdate. regards, tom lane ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Suggestions for sysdate, for PostgreSQL?
On Wed, Jul 21, 2010 at 06:21:04PM -0400, Tom Lane wrote: > Jan Pazdziora writes: > > what are our possibilities for handling sysdate in PostgreSQL. > > Isn't s/sysdate/current_timestamp/g a good solution? That's standards > compliant, unlike sysdate. I wouldn't dare to do that en masse. It's a different type -- timestamp instead of date, so it has different precision, and I'm not sure if the arithmetic is exactly the same. But you are right, we could just change that one-by-one, verifying any side effect of the change, as we keep hitting the sysdate errors. -- Jan Pazdziora Principal Software Engineer, Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Suggestions for sysdate, for PostgreSQL?
Jan Pazdziora writes: > On Wed, Jul 21, 2010 at 06:21:04PM -0400, Tom Lane wrote: >> Isn't s/sysdate/current_timestamp/g a good solution? That's standards >> compliant, unlike sysdate. > I wouldn't dare to do that en masse. It's a different type -- > timestamp instead of date, so it has different precision, and I'm not > sure if the arithmetic is exactly the same. Hmm. If you only want the result to day precision, the standards-compliant way to spell that is current_date. But I am not sure whether Oracle treats that the same as sysdate, either. regards, tom lane ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
