Problem with JDBC driver for Oracle 12c

Oracle has changed some of the behavior of its JDBC driver for 12c. If autocommit is enabled and an explicit commit is execute, it blows up.

java.sql.SQLException: Could not commit with auto-commit set on

OK, fair enough - it doesn't make sense to commit with auto-commit on. Comments I've read state that the JDBC spec demands this behavior, and Oracle is finally catching up. The earlier JDBC drivers don't  throw this error. I don't believe any other JDBC drivers (MS, etc.) throw this error!

Our application needs to be fixed now.

Silly little problem installing Oracle on EC2

My boss was attempting to install Oracle 12c on an EC2 instance running Linux but it was failing mysteriously, so he handed it off to me, the Database Guy (tm). The logs reported this error:

"ORA-21561: OID generation failed"

Wha'? After some head scratching and googling I learned this can happen if the host name you’re installing on is not resolvable. Boss said he did not enable DNS on the VPC (Virtual Private Cloud - an Amazon thing). I took a look at the Oracle install script (that was apparently generated by Amazon) and found that it’s getting the host name via a curl call, to retrieve meta-data Amazon provides for your EC2 instance: something like ORACLE_HOSTNAME=`curl -s http://xxx.xxx.xxx.xxx/latest/meta-data/local-hostname/`

One option would be to get rid of that call and set ORACLE_HOSTNAME to localhost. Another would be to add the EC2 host to /etc/hosts. I opted for the latter (I wasn’t convinced that was the only place that needed the host) then installation worked. (Yay!)

"In Which We Are Not Having Fun"

Speaking of Postgres materialized views...The guys at sparkfun (makers of Arduino as well other fine products) migrated from MySQL to Postgres for various reasons, and decided to use the newish materialized views. Apparently it was all fun and games until their number of orders increased by an order of magnitude, bringing MV refreshes to their knees. (These MVs keep track of their available stock, it seems.) 

MVs in Postgres are very basic -- no incremental refreshes like Oracle has, so they're crunching all the numbers from square one every single time you say "refresh."

I don't know anything about the sparkfun guys' particular requirements or infrastructure, but I probably would not try to keep Postgres MVs of any complexity constantly up to date in a transactional system. I'm imagining they're trying to update the MVs any time the dependent tables are modified, so too many orders kill them. What if they accepted some data staleness and set the MVs to refresh on a timer (every x minutes)? If the view of stock is out of date by a bit, that might be OK as long as there's a check in place at checkout time. Just kind of wondering out loud here.

This situation shows that testing to scale is necessary, by the way.

On a related note, their reasons for switching from MySQL to Postgres are pretty numerous.

EDIT years after the fact: maybe using incremental materialized views would help. but it's not a core Postgres feature at this time (August, 2025); rather, it is a feature provided by this extension.

 

MariaDB 10 Release

Saw this on Slashdot this morning, after driving through a March 31 snowstorm. Replication slaves don't crash now...What a feature! Guess that was a problem. I should check out Maria one of these days.

PostgreSQL Materialized View Example

As I mentioned in an earlier post, PostgreSQL finally supports some very basic materialized views as of version 9.3, and this has me more excited than it should! I finally got a chance to give it a try.

Overview of Materialized Views

But first, what is a materialized view? Very briefly, it’s a view whose query results are stored. If a view is a stored query that spits results at you on demand, a materialized view is a view that stores the query results and spits those same results out at you on demand. The ordinary view works hard to get your results every time you query it, while the materialized view has already done the work for you in the past. View=slower, zero disk space; materialized view=faster; potentially lots of disk space.

If the query results change over time, then at some point the materialized view results will be “stale”. You must tell Postgres to refresh your materialized view from time to time. In more advanced databases, materialized views may refresh automatically, either when a dependent table is updated, on a regular schedule, or on demand. Postgres only supplies “on demand”.

A Simple Example: Baseball Stats

Let’s get right into an example. I like baseball, so the goal of this example will be to come up with team batting averages based on the players’ individual plate appearances. My data is very simple, and a plate appearance can either be a hit or an out.  

So, we have these tables:

 /* each row represents a player on a team */  
 CREATE TABLE player (id SERIAL PRIMARY KEY, name VARCHAR, num SMALLINT, team VARCHAR(3));  
 /* each row represents a plate appearance where a player attempts to hit the ball. In our simplistic model, there are two possible outcomes: he either gets a hit or he is out */  
 CREATE TABLE pa (pa_id SERIAL PRIMARY KEY, player_id INT REFERENCES player(id), gamedate DATE, inning SMALLINT, result VARCHAR(1));  

I have populated them with dummy data. The teams I used are the MLB American League East teams: New York Yankees (NYY), Boston Red Sox (BOS), Tampa Bay Rays (TB), Toronto Blue Jays (TOR) and Baltimore Orioles (BAL). 25 players for each team, getting 4 plate appearances per day over 180 days. These are just numbers I made up. I’m not attempting total realism here.

Now I will write a view to get the batting averages per team:

 CREATE OR REPLACE VIEW team_avg_v AS SELECT team, SUM(CASE WHEN result='H' THEN 1 ELSE 0 END)::float/COUNT(*)::float AS avg FROM pa JOIN player p ON pa.player_id=p.id GROUP BY team  

The result:

 mwrynn=# select * from team_avg_v;  
  team |    avg      
 ------+-------------------  
  BAL | 0.252296296296296  
  NYY | 0.244592592592593  
  TB  | 0.246666666666667  
  BOS | 0.250444444444444  
  TOR | 0.248444444444444  
 (5 rows)  
 Time: 248.679  

249 ms is good time, but imagine if we had data for *all* players for *all* teams, even for *all* years baseball was played! This simple view would possibly take hours to run, at least on my wimpy little laptop.

Enter the materialized view. All we have to do is the following to materialize this view: 

 mwrynn=# CREATE MATERIALIZED VIEW team_avg_mv AS SELECT * FROM team_avg_v;  
 SELECT 5  
 Time: 190.997 ms  

And now let’s try querying it:

 mwrynn=# select * from team_avg_mv;  
  team |    avg      
 ------+-------------------  
  BAL | 0.252296296296296  
  NYY | 0.244592592592593  
  TB  | 0.246666666666667  
  BOS | 0.250444444444444  
  TOR | 0.248444444444444  
 (5 rows)  
 Time: 0.755 ms  

We went from 249 ms to less than a ms. Not bad, huh? :)

Data Staleness and Refreshing

Probably the biggest downside to using a materialized view in this manner is it can get stale. In the context of our example, this means we add some more plate appearances for our players, and the averages change a bit. Let’s query the plain old view after adding more data:

 mwrynn=# select * from team_avg_v ;  
  team |    avg      
 ------+-------------------  
  BAL | 0.248888888888889  
  NYY | 0.24862962962963  
  TB  | 0.250592592592593  
  BOS | 0.25037037037037  
  TOR | 0.250074074074074  
 (5 rows)  
 Time: 351.739 ms  

Now let’s show that our materialized view is out of date:

 mwrynn=# select * from team_avg_mv ;  
  team |    avg      
 ------+-------------------  
  BAL | 0.252296296296296  
  NYY | 0.244592592592593  
  TB  | 0.246666666666667  
  BOS | 0.250444444444444  
  TOR | 0.248444444444444  
 (5 rows)  
 Time: 0.710 ms  

We need to refresh it to get it up to speed:

 mwrynn=# refresh materialized view team_avg_mv;  
 REFRESH MATERIALIZED VIEW  
 Time: 360.579 ms  

Our Exciting Conclusion

Note that the refresh took about the same amount of time as the plain old view took to query. That’s no coincidence - it’s doing the same work to get our query results (plus storing the data in the materialized view case, and if we had built any indexes on it, those would have to be updated accordingly). This could be a problem if the underlying data changes often, and it’s not acceptable to see slightly out-of-date data in our query results. So, like anything else, materialized views are a tool to use in the appropriate situation, not a one-size-fits-all magic bullet. Other databases such as Oracle have special incremental refresh modes that can speed up your refreshes - you can sort of let you have your cake and eat it too, but these advanced materialized views tend to get very complex. Maybe at some point I'll show you some of this fancy Oracle materialized view stuff. :)

Do you understand indexes?

Test Your SQL Performance Know-How in 3 Minutes - the author says 60% of those who take this little quiz fail! You can select a database of your choice and the quiz will grill you on knowing the basics of indexes and when a query can make good use of particular indexes.

I chose Postgres as my database of choice and passed with a 4 out of 5. I attribute my mistake to a misreading but whatever, I have to accept that I messed one up. :)

A simple but slightly weird Sybase issue

A QA guy came to pick my brain about a bug that was reported to him. A Sybase table had a VARCHAR(255). The bug was that when a too-large String was inserted via JDBC, the following exception was thrown:

com.sybase.jdbc3.jdbc.SybSQLException: Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not allowed.  Use the CONVERT function to run this query.

Mr. QA could not quite reproduce the problem. Whenever he tried to insert a large String, the data was truncated down to 255 bytes, but otherwise worked fine. The issue turned out to be when you  attempt to insert a String larger than the maximum size allowed for a VARCHAR (32767 or thereabouts) the Sybase JDBC driver assumes that it must be a TEXT. Sort of makes sense since that's too big for a VARCHAR, right? So, without any explicit conversion, the TEXT String cannot "fit" into a VARCHAR column. Exception thrown!

From the user's perspective it's kind of funny - a too-big String truncates, a too-too-big String explodes!

Unsolved Mystery: The datafile that wouldn't shrink

I had a problem the other day in reclaiming disk space on one of my Oracle development databases (10gR2).

Some background:

Made a tablespace delme with datafile delme.dbf.

Made a table mytable (which is very big) in tablespace delme, to use for just a little while, then it was to be dropped.

Done with table so: drop table mytable;

The problem:

Realizing I should have used "purge" in the DROP TABLE statement, I sigh and say OK, let's just run the Tom Kyte maxshrink script...There's absolutely nothing I want in this tablespace.

Crap, it's still using the space:

SQL> alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 1m
  2  ;
alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 1m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 5m;
alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 5m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 100m;
alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 100m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


Hmmm....maybe the issue is the table wound up in the recycle bin. if I don't use "purge" the table gets put in the recycle bin. So let's find it there:
SQL> select object_name, original_name, type from recyclebin;

no rows selected

Oh...uh...ok...now what? Let's check if any objects might actually exist in the datafile:

select *
from (
select owner, segment_name, segment_type, block_id
from dba_extents
where file_id = ( select file_id
  from dba_data_files
where file_name = '/opt/oracle/oradata01/delme.dbf' )
  order by block_id desc
)
  2    3    4    5    6    7    8    9   10  ;

no rows selected

Hmm...

So now I'll just try dropping it, I guess. Here's how much space it took up before:

oracle@frd-db01:~/product/10.2/db_1> ll /opt/oracle/oradata01/delme.dbf
-rw-r----- 1 oracle oinstall 38722871296 2013-12-03 05:31 /opt/oracle/oradata01/delme.dbf

and now we drop tablespace:
SQL> drop tablespace delme including contents and datafiles;

Tablespace dropped.

oracle@frd-db01:~/product/10.2/db_1> df -h | grep /opt/oracle/oradata01
                      100G   56G   45G  56% /opt/oracle/oradata01

And yay, I have space again, but I just wish I had gotten to the bottom of the mystery. Unfortunately, I simply don't have the time for that right now. Does anyone in my vast readership know what might have happened? Comments are welcome!

Tool for generating random test data

Neat tool to generate semi-coherent random data: http://www.databasetestdata.com/

The Agony and the Ecstasy: Loading a 50G CSV file into an Oracle table Part I

Task: Load a 50+G CSV file into an Oracle database (running on Linux).

First problem: Only about 100G of free disk space was available on the Oracle server. Merely having a 50G CSV reside on the disk was taking up half! Is there a a way to import a compressed CSV (they are very-compressible)?

Enter named pipes.

Very briefly, named pipes allow for simple interprocess communication. I run one process and pipe output to the named pipe, which appears as a file in the filesystem. For my purpose, this allows me to unzip the huge CSV on the fly.

So how to set this up?
1) Upload mytable.csv.gz to the server running Oracle
2) Make a named pipe called mytable.csv
3) gunzip mytable.csv.gz and output to mytable.csv
4) From Oracle, load mytable.csv as if it were an ordinary CSV file. (I used an external table)

Now for some code so the above can be automated:

#!/bin/bash
#write_to_pipe.sh bash script:
#creates named pipe if non-existent and gunzips $1.gz to $1

pipe=$1

trap "rm -f $pipe" EXIT

if [[ ! -p $pipe ]]; then
    mkfifo $pipe
fi

gunzip -c $pipe.gz > $pipe

So, I run this script then query my external table that's hooked up to mytable.csv:

INSERT INTO mytable SELECT * FROM ext_mytable; --slightly simplified for illustrative purposes

...and voila! That’s it.

I hope someone else can enjoy this tip because I found it tremendously useful. By the way, the compressed CSV at 4.6G was less than 10% of the original CSV’s size! Nice…

I may write more of the hurdles and solutions involved in loading this big table into Oracle. But the named pipe solution was the neatest, coolest part, so I may leave it at that. :)

Postgres now supported on Amazon RDS

http://aws.amazon.com/rds/postgresql/

Finally!! Long story short time...

At work I had co-developed an application to run on Postgres - being the local database guy, I decided on Postgres as it is my preferred open source database. Fast forward about a year, and my boss said to me, "Mwrynn, it would really be nice to run your app on that RDS thing to make life easy for us when we go live. You know, RDS provides all those nice scalability, availability and management features." I agreed and converted the application to MySQL. This is not an automatic process just because we use a fancy ORM, and took a good deal of work. Then we went in production.

Now Postgres is available on RDS and I may have to convert back to Postgres again. The music running through my head: http://www.youtube.com/watch?v=1D5Sa2Yq-2g

New features in Postgres 9.3 (just released)

New features in PostgreSQL 9.3

I'm particularly excited about materialized views - a very useful feature I've used in Oracle for years. It looks like Postgres MVs will only have a fraction of the Oracle functionality, but hey it's a start!

Updatable views: also very nice. This feature can be useful when you have some tables that you want to make "private" and only expose a view based on them. An example off the top of my head... Maybe you have an archive_purchase table and current_purchase table - kind of like partitions - probably current_purchase is lean and mean and archive_purchase is old, huge and fat. You have some page in your web app that lets the user view and edit purchases from any time, so you make the view all_purchase that UNIONs the two. Now you simply query and update all_purchase without having to worry about where the underlying row comes from.

...And many more nice-looking new features.

100x faster Postgres performance by changing 1 line

From Datadog: 100x faster Postgres performance by changing 1 line

TL;DR: To check in a large list of values, use ANY(VALUES(...)) instead of ANY(ARRAY[...])

If this is a consistent issue, hopefully it's something Postgres can better optimize in the query planner.