Tag Info

New answers tagged

4

It's a totally pointless function that executes arbitrary SQL. It isn't SECURITY DEFINER so the only risk I think it can pose is if you allow users to run arbitrary SQL predicates or call arbitrary functions (in which case you're probably already stuffed) but try to block them from running any command they want. As you guessed, it just executes the SQL ...


1

To answer your question; WHY? You probably already know this by now since the post is 2 years old. But I'll respond just for the record. The reason #1 requires a commit and #2 doesn't is because the default database setting in Oracle is to commit a transaction when a session ends. If you are in sqlplus and run your code manually, it will not commit the ...


1

Looking at the original query SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE() GROUP BY DATE(timestamp) It appears that you are looking for counts for just today. In that instance, why use GROUP BY at all ? You should run SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE(); OK, let's get a little more ...


1

First create a Calendar table (a table of dates) as described in this answer; then extend it by adding and populating Year, MonthName, DayOfMonth, and Tomorrow columns in that table and add a unique index on it by (Year, MonthName and DayOfMonth), and another unique index on it by the base column (Date) - the Primary Key. Now you can generate your reports ...


0

This might work: select sum(N), the_date from ( SELECT COUNT(*) as N, timestamp, DATE(timestamp) as the_date FROM logs WHERE timestamp >= CURDATE() group by timestamp ) as A GROUP BY the_date -- or: GROUP BY DATE(timestamp) Your first problem is MySQL, for many reasons, here because function(column) shouldn't preclude the use of an ...


1

You could create an additional column, date, which stores the function date(timestamp) on insertion. This won't make the group by extremely efficient, but it can avoid the temporary table. The second problem is the range + GROUP BY, which would make an index on (timestamp, date) useless (BTREE limitations). You can create just an index on (date) or better, ...


2

If you have a function that does an UPDATE and you try to call that from a SELECT statement, you should get an Oracle error saying that you cannot do DML inside a query. So your SELECT statement should be throwing an error. My first guess would be that you have an exception handler somewhere that, intentionally or not, is catching and discarding the error. ...


1

Do the exclusion in the cron job definition. The handy flock shell command is intended for exactly this. To do it inside PostgreSQL you would have the function take a lock that it holds for the duration of its run. Lots of options - LOCK TABLE ... IN EXCLUSIVE MODE, do a SELECT ... FOR UPDATE of a particular row, or use pg_advisory_xact_lock. See the ...



Top 50 recent answers are included