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.
Opinions, experiences and information about SQL and the databases that process the language, from a guy who's been at it for over 20 years.
Tuesday, September 10, 2013
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.
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.
Monday, August 12, 2013
Some good SQL tips
Two very nice articles on SQL tips for Java developers, or anyone who uses SQL, really:
10 Common Mistakes Java Developers Make When Writing SQL
10 More Common Mistakes Java Developers Make When Writing SQL
In Part 2, one tip is always to use ANSI joins. I tend to agree, but I just want to note that I have run into situations where the "old style" was required by Oracle. Maybe someone can correct me if this doesn't apply anymore, but back about 7 years ago when I really dug into materialized views, I found that query rewrite required the older syntax. Always thought that was a bit odd.
10 Common Mistakes Java Developers Make When Writing SQL
10 More Common Mistakes Java Developers Make When Writing SQL
In Part 2, one tip is always to use ANSI joins. I tend to agree, but I just want to note that I have run into situations where the "old style" was required by Oracle. Maybe someone can correct me if this doesn't apply anymore, but back about 7 years ago when I really dug into materialized views, I found that query rewrite required the older syntax. Always thought that was a bit odd.
Tuesday, August 06, 2013
The world's most [x] open source database.
I'm working with both Postgres and MySQL at the moment, so I have documentation pages open for each. I just randomly noticed how similar their respective slogans are.
Postgres: "The world's most advanced open source database."
MySQL: "The world's most popular open source database."
Telling, isn't it? :)
Postgres: "The world's most advanced open source database."
MySQL: "The world's most popular open source database."
Telling, isn't it? :)
Thursday, April 25, 2013
Schema versioning in Postgres
Edit long after writing this post (4/2016): These days I use Liquibase! But let's keep the post below for posterity....
For a web application our team has been working on, I wrote a set of scripts that facilitate keeping our Postgres database schema up to date. Each developer has their own database instance, but I'm the guy in charge of managing schema changes. So, whenever the schema is changed, it's nice to have a version number for each so we can tell if an upgrade is required. (A similar approach could easily be used for databases other than Postgres, by the way.)
How it works:
We have a set of .sql scripts that are named like this:
upgrade_[a.b]_to_[c.d].sql
upgrade_[c.d]_to_[e.f].sql
upgrade_[e.f]_to_[g.h].sql
Each script contains the necessary changes, typically ALTER TABLE statements and such. At the very end of each, the schema version is set in the form of two function definitions: get_schema_version_major() and get_schema_version_minor(). Each simply returns an int. I increment the major number in each sql script that makes a change that, without which, the application would break. An example might be a dropped column. The minor version is incremented when a non-application-breaking change is made, such as a new constraint or index.
For each sql script there is a corresponding bash shell script as well as a Windows cmd script. Example: upgrade_[a.b]_to_[c.d].sh. This wraps up the call to psql so that the less-database-inclined can run it without hassle.
After a while of my coworkers using these upgrade scripts, there were complaints about how so many scripts had to be run! For example, maybe schema version 10.3 needed to be upgraded to 15.0 via several links in between. So I cooked up a new shell script that would intelligently traverse the chain of version numbers and automagically do it all for you. just run upgrade.sh and voila! Here's what the output looks like:
Beginning schema upgrades, starting with version 10.3...
Upgrading from 10.3 to 11.0...
Upgrade successful.
Upgrading from 11.0 to 12.0...
Upgrade successful.
Upgrading from 12.0 to 13.0...
Upgrade successful.
Upgrading from 13.0 to 14.0...
Upgrade successful.
Upgrading from 14.0 to 15.0...
Upgrade successful.
Number of successful upgrades: 5. Version is now 15.0.
It won’t do anything it shouldn’t if I run it again…
mwrynn@mwrynnix:~$ ./upgrade.sh
Beginning schema upgrades, starting with version 15.0...
Number of successful upgrades: 0. Version is now 15.0.
Here’s an error case. I started over from 10.3 and purposely added a syntax error to the 13.0_to_14.0 script just for the purpose of this test…
mwrynn@mwrynnix:~$ ./upgrade.sh
Beginning schema upgrades, starting with version 10.3...
Upgrading from 10.3 to 11.0...
Upgrade successful.
Upgrading from 11.0 to 12.0...
Upgrade successful.
Upgrading from 12.0 to 13.0...
Upgrade successful.
Upgrading from 13.0 to 14.0...
psql:sbo_upgrade_13.0_to_14.0.
LINE 15: ...subscription ADD COLUMNBLAH pending_status_change INT REFERE...
Upgrade failed. Aborting.
Number of successful upgrades: 3. Version is now 13.0.
Finally, I’ve fixed that error and I’m resuming from where we were. Since each upgrade is an all-or-nothing transaction, it doesn’t matter that some of the statements in that bad script worked before the error occurred…
mwrynn@mwrynnix:~$ ./upgrade.sh
Beginning schema upgrades, starting with version 13.0...
Upgrading from 13.0 to 14.0...
Upgrade successful.
Upgrading from 14.0 to 15.0...
Upgrade successful.
Number of successful upgrades: 2. Version is now 15.0.
For a web application our team has been working on, I wrote a set of scripts that facilitate keeping our Postgres database schema up to date. Each developer has their own database instance, but I'm the guy in charge of managing schema changes. So, whenever the schema is changed, it's nice to have a version number for each so we can tell if an upgrade is required. (A similar approach could easily be used for databases other than Postgres, by the way.)
How it works:
We have a set of .sql scripts that are named like this:
upgrade_[a.b]_to_[c.d].sql
upgrade_[c.d]_to_[e.f].sql
upgrade_[e.f]_to_[g.h].sql
Each script contains the necessary changes, typically ALTER TABLE statements and such. At the very end of each, the schema version is set in the form of two function definitions: get_schema_version_major() and get_schema_version_minor(). Each simply returns an int. I increment the major number in each sql script that makes a change that, without which, the application would break. An example might be a dropped column. The minor version is incremented when a non-application-breaking change is made, such as a new constraint or index.
For each sql script there is a corresponding bash shell script as well as a Windows cmd script. Example: upgrade_[a.b]_to_[c.d].sh. This wraps up the call to psql so that the less-database-inclined can run it without hassle.
After a while of my coworkers using these upgrade scripts, there were complaints about how so many scripts had to be run! For example, maybe schema version 10.3 needed to be upgraded to 15.0 via several links in between. So I cooked up a new shell script that would intelligently traverse the chain of version numbers and automagically do it all for you. just run upgrade.sh and voila! Here's what the output looks like:
Beginning schema upgrades, starting with version 10.3...
Upgrading from 10.3 to 11.0...
Upgrade successful.
Upgrading from 11.0 to 12.0...
Upgrade successful.
Upgrading from 12.0 to 13.0...
Upgrade successful.
Upgrading from 13.0 to 14.0...
Upgrade successful.
Upgrading from 14.0 to 15.0...
Upgrade successful.
Number of successful upgrades: 5. Version is now 15.0.
It won’t do anything it shouldn’t if I run it again…
mwrynn@mwrynnix:~$ ./upgrade.sh
Beginning schema upgrades, starting with version 15.0...
Number of successful upgrades: 0. Version is now 15.0.
Here’s an error case. I started over from 10.3 and purposely added a syntax error to the 13.0_to_14.0 script just for the purpose of this test…
mwrynn@mwrynnix:~$ ./upgrade.sh
Beginning schema upgrades, starting with version 10.3...
Upgrading from 10.3 to 11.0...
Upgrade successful.
Upgrading from 11.0 to 12.0...
Upgrade successful.
Upgrading from 12.0 to 13.0...
Upgrade successful.
Upgrading from 13.0 to 14.0...
psql:sbo_upgrade_13.0_to_14.0.
LINE 15: ...subscription ADD COLUMNBLAH pending_status_change INT REFERE...
Upgrade failed. Aborting.
Number of successful upgrades: 3. Version is now 13.0.
Finally, I’ve fixed that error and I’m resuming from where we were. Since each upgrade is an all-or-nothing transaction, it doesn’t matter that some of the statements in that bad script worked before the error occurred…
mwrynn@mwrynnix:~$ ./upgrade.sh
Beginning schema upgrades, starting with version 13.0...
Upgrading from 13.0 to 14.0...
Upgrade successful.
Upgrading from 14.0 to 15.0...
Upgrade successful.
Number of successful upgrades: 2. Version is now 15.0.
Friday, March 15, 2013
No ORDER BY = No guarantee of order, period.
People won't seem to take Tom Kyte's word (and the Oracle docs' word) that if you don't use ORDER BY, results are not guaranteed to be returned in order. It's almost funny...
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6257473400346237629
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6257473400346237629
Thursday, March 14, 2013
OpenJPA headaches
I'm met with some incredulity every time I comment that fiddling with/working around the problems with O/R Mapper xyz wastes more of my time than it would take for me to handcode all my SQL. Here's another experience that makes me say just that.
Once upon a time, our Spring application used OpenJPA 2.1. The database was Postgres. Everything was happy until We encountered this bug which broke some of our app's queries: https://issues.apache.org/jira/browse/OPENJPA-2056 ...So, we upgraded to OpenJPA 2.2 which includes this fix.
Along with that fix, we got a brand new wonderful feature that we didn't ask for, that can't be disabled! This feature is ID caching, via a new allocationSize parameter (to the @SequenceGenerator annotation), which caches [allocationSize] number of IDs on the application side.
OpenJPA sends ALTER SEQUENCE commands to the database in order to update the sequence appropriately - this is necessary for its caching scheme to work. They decided this new parameter should default to 50, so that's what we were using. You see when we upgraded to OpenJPA 2.2, we neglected to account for new features that might attempt to screw around with database objects behind our backs. The problem is that ALTER SEQUENCE can only be run by the sequence's owner, the application user is not the owner, and so an error is thrown. The OpenJPA docs say you can set the value of the allocationSize parameter to 1 to avoid using this feature, but when we tried that, it instead sent a command with a completely invalid syntax: "ALTER SEQUENCE mytable_id_seq"!
This invalid statement is fixed in OpenJPA 2.3, which has not been released at this time. We tested a SNAPSHOT build of 2.3, and found while it does correct the invalid syntax, it's still a problem. It sends an ALTER SEQUENCE statement when we don't want it to send any. Can't we just turn this thing off? Well, OpenJPA now has this feature that when this particular ALTER SEQUENCE statement fails, OpenJPA will just ignore it and log a warning. That's great and all but:
1) Now I have unnecessary "ERROR"s in my Postgres log. I monitor the log for "ERROR" and now this junk is cluttering it
2) One of my tables requires a special lock before modifying, therefore before we persist on the Java side, we begin a new transaction. So, this failed ALTER SEQUENCE statement winds up in the same transaction as the UPDATE/INSERT. Now, Postgres doesn't like when there's an error in your transaction - it forces all subsequent statements to also throw errors, and the only option is to rollback. The end result: we can't modify the data at all. It doesn't matter that the application pretends the first statement wasn't an error.
We worked around the issue by writing our own custom class for sequences, that subclasses AbstractJDBCSeq. We tell it to us a favor and not cache IDs in a way that won't work for us.
These ORMs tend to operate with the assumption that:
1) The ORM has total control over every database object
2) I actually want it to have total control over every database object. You know in the past I've worked with database clusters, each node replicated, where the databases need control over the sequences. For example, imagine such a cluster with three nodes. In order to avoid ID collisions, Node 1 might use IDs 1, 4, 7, ... Node 2 uses 2, 5, 8, ... and finally Node 3 uses 3, 6, 9, ... (This kind of configuration is not the norm, but it isn't all that uncommon either.) Now if you decide it's OK to start adding numbers to these sequences without my permission, well there goes the neighborhood.
If you're going to introduce a feature that requires a new set of database privileges, or does anything non-standard with database objects, at least make it optional!
Sigh!
Once upon a time, our Spring application used OpenJPA 2.1. The database was Postgres. Everything was happy until We encountered this bug which broke some of our app's queries: https://issues.apache.org/jira/browse/OPENJPA-2056 ...So, we upgraded to OpenJPA 2.2 which includes this fix.
Along with that fix, we got a brand new wonderful feature that we didn't ask for, that can't be disabled! This feature is ID caching, via a new allocationSize parameter (to the @SequenceGenerator annotation), which caches [allocationSize] number of IDs on the application side.
OpenJPA sends ALTER SEQUENCE commands to the database in order to update the sequence appropriately - this is necessary for its caching scheme to work. They decided this new parameter should default to 50, so that's what we were using. You see when we upgraded to OpenJPA 2.2, we neglected to account for new features that might attempt to screw around with database objects behind our backs. The problem is that ALTER SEQUENCE can only be run by the sequence's owner, the application user is not the owner, and so an error is thrown. The OpenJPA docs say you can set the value of the allocationSize parameter to 1 to avoid using this feature, but when we tried that, it instead sent a command with a completely invalid syntax: "ALTER SEQUENCE mytable_id_seq"!
This invalid statement is fixed in OpenJPA 2.3, which has not been released at this time. We tested a SNAPSHOT build of 2.3, and found while it does correct the invalid syntax, it's still a problem. It sends an ALTER SEQUENCE statement when we don't want it to send any. Can't we just turn this thing off? Well, OpenJPA now has this feature that when this particular ALTER SEQUENCE statement fails, OpenJPA will just ignore it and log a warning. That's great and all but:
1) Now I have unnecessary "ERROR"s in my Postgres log. I monitor the log for "ERROR" and now this junk is cluttering it
2) One of my tables requires a special lock before modifying, therefore before we persist on the Java side, we begin a new transaction. So, this failed ALTER SEQUENCE statement winds up in the same transaction as the UPDATE/INSERT. Now, Postgres doesn't like when there's an error in your transaction - it forces all subsequent statements to also throw errors, and the only option is to rollback. The end result: we can't modify the data at all. It doesn't matter that the application pretends the first statement wasn't an error.
We worked around the issue by writing our own custom class for sequences, that subclasses AbstractJDBCSeq. We tell it to us a favor and not cache IDs in a way that won't work for us.
These ORMs tend to operate with the assumption that:
1) The ORM has total control over every database object
2) I actually want it to have total control over every database object. You know in the past I've worked with database clusters, each node replicated, where the databases need control over the sequences. For example, imagine such a cluster with three nodes. In order to avoid ID collisions, Node 1 might use IDs 1, 4, 7, ... Node 2 uses 2, 5, 8, ... and finally Node 3 uses 3, 6, 9, ... (This kind of configuration is not the norm, but it isn't all that uncommon either.) Now if you decide it's OK to start adding numbers to these sequences without my permission, well there goes the neighborhood.
If you're going to introduce a feature that requires a new set of database privileges, or does anything non-standard with database objects, at least make it optional!
Sigh!
Oracle 12c New Features
http://sqlandplsql.com/2013/01/08/oracle-12c-new-features/
The least fancy of these features are the ones I'm most excited to see. I should say, I'm most relieved to see. These deal with some annoyances that felt absurd. For example I'd find myself screaming, "Why in this day and age do I still have to create a trigger for every ID?!?"
Here they are (these are quotes from the page I linked to):
2. VARCHAR2 length up to 32767
This one will be one of the best feature for developers who always struggle
to manage large chunk of data. Current version of databases allows only
up to 4000 bytes in a single varchar2 cell. So developers has to either use
CLOB or XML data types which are comparatively slower that varchar2
processing.
3. Default value can reference sequences
This is also for developers who struggle to maintain unique values in
Primary Key columns. While creating a table default column can be
referenced by sequence.nextval.
8. Boolean in SQL
As of Oracle 11g Boolean is not a supported data type in SQL and 12c you can
enjoy this feature.
The least fancy of these features are the ones I'm most excited to see. I should say, I'm most relieved to see. These deal with some annoyances that felt absurd. For example I'd find myself screaming, "Why in this day and age do I still have to create a trigger for every ID?!?"
Here they are (these are quotes from the page I linked to):
2. VARCHAR2 length up to 32767
This one will be one of the best feature for developers who always struggle
to manage large chunk of data. Current version of databases allows only
up to 4000 bytes in a single varchar2 cell. So developers has to either use
CLOB or XML data types which are comparatively slower that varchar2
processing.
3. Default value can reference sequences
This is also for developers who struggle to maintain unique values in
Primary Key columns. While creating a table default column can be
referenced by sequence.nextval.
8. Boolean in SQL
As of Oracle 11g Boolean is not a supported data type in SQL and 12c you can
enjoy this feature.
Friday, August 17, 2012
Mention of new Pgpool-II HA feature
"...I will explain how to set up "watchdog", which is also a new feature of pgpool-II 3.2. By using this, you can avoid SPOF(single point of failure) problem of pgpool-II itself without using extra HA software."
http://pgsqlpgpool.blogspot.com/2012/08/pgpool-ii-talk-at-postgresql-conference.html?utm_source=dlvr.it&utm_medium=twitter
http://pgsqlpgpool.blogspot.com/2012/08/pgpool-ii-talk-at-postgresql-conference.html?utm_source=dlvr.it&utm_medium=twitter
Wednesday, February 08, 2012
Exporting a SQL Server database directly to Oracle
I was faced with the task of importing a SQL Server schema, data and all, over to an Oracle database. Initially I started to go the route of exporting to flat files, then I realized I apparently had to do this one by one for all 30-something tables in the SQL Server database! I looked into a better way...
Assuming an Oracle database is running on one machine, and MS SQL Server with Management Studio on another...
1) Obtain Oracle Data Access Components (ODAC) - http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html - you may need a different link depending on whether you need 32 or 64 bit (or different version?)
2) Install ODAC on the Windows server running SQL Server according to the README.
3) a) Open SQL Server Management Studio. I'm using 2005, so the dialogs may be different for newer versions.
b) Right-click the database you'd like to export, then click Tasks, Export Data.
4) Choose data source FROM which to copy data - probably SQL Native Cilent with SQL Server Authentication...The database selected should be the one you right-clicked earlier.
5) Choose destination. Oracle Provider for OLE DB worked for me. Microsoft OLE DB Provider for Oracle did NOT work. Upon executing the export, this provider immediately spat out a mysterious "Data type is not supported" error.
6) Click Properties. Data Link Properties dialog will come up. There might be multiple way to define "Data Source", but what worked for me was [IP]/[oracle SID]" - for example 10.1.2.3/mydb ...then enter the oracle user/pw and hit Test Connection. When the test is successful, make sure you check off "Allow saving password" because if you don't it will try to log in with empty password and fail. Click OK.
7) Pretty obvious from here. (Hit next a few times, select the tables you want, etc.)
Assuming an Oracle database is running on one machine, and MS SQL Server with Management Studio on another...
1) Obtain Oracle Data Access Components (ODAC) - http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html - you may need a different link depending on whether you need 32 or 64 bit (or different version?)
2) Install ODAC on the Windows server running SQL Server according to the README.
3) a) Open SQL Server Management Studio. I'm using 2005, so the dialogs may be different for newer versions.
b) Right-click the database you'd like to export, then click Tasks, Export Data.
4) Choose data source FROM which to copy data - probably SQL Native Cilent with SQL Server Authentication...The database selected should be the one you right-clicked earlier.
5) Choose destination. Oracle Provider for OLE DB worked for me. Microsoft OLE DB Provider for Oracle did NOT work. Upon executing the export, this provider immediately spat out a mysterious "Data type is not supported" error.
6) Click Properties. Data Link Properties dialog will come up. There might be multiple way to define "Data Source", but what worked for me was [IP]/[oracle SID]" - for example 10.1.2.3/mydb ...then enter the oracle user/pw and hit Test Connection. When the test is successful, make sure you check off "Allow saving password" because if you don't it will try to log in with empty password and fail. Click OK.
7) Pretty obvious from here. (Hit next a few times, select the tables you want, etc.)
Thursday, January 26, 2012
PL/SQL Elisp file
The Elisp file for PL/SQL can be found here: http://www.emacswiki.org/emacs/download/plsql.el
I'm trying to byte-compile it as mentioned in the script's comments, but I'm getting this warning: plsql.el:154:1:Warning: defgroup for `plsql' fails to specify containing group.
Not sure if this is a show stopper?
Edit:
changed
(defgroup plsql nil "")
to
(defgroup plsql nil "plsql mode" :group 'SQL)
Reason for this: can't put things into root config in later versions of emacs, so I had to put plsql under the group SQL.
I'm trying to byte-compile it as mentioned in the script's comments, but I'm getting this warning: plsql.el:154:1:Warning: defgroup for `plsql' fails to specify containing group.
Not sure if this is a show stopper?
Edit:
changed
(defgroup plsql nil "")
to
(defgroup plsql nil "plsql mode" :group 'SQL)
Reason for this: can't put things into root config in later versions of emacs, so I had to put plsql under the group SQL.
Tuesday, January 10, 2012
Oracle SQL Scripts: optional parameters
I used this technique successfully. Basically if you want &1, &2 (etc.) to be optional and avoid the dreaded forced user interaction if it's not specified: http://eriksekeris.blogspot.com/2010/09/nvl-for-sqlplus-commandline-parameters.html
Wednesday, December 07, 2011
Cloning an Oracle Schema
An Oracle schema can be "cloned" by using the expdp and impdp utilities.
1) Create a directory on the file system and let Oracle know about it.
a) [bash assumed]: mkdir /home/oracle/data_dmp
b) [in sqlplus]: create directory data_dmp as '/home/oracle/data_dmp';
grant read,write on directory DATA_DMP to myschema;
2) Export schema to file:
expdp myschema/**** dumpfile=myschema.dmp logfile=myschema.exp schemas=myschema directory=data_dmp
3) Create a user for your new schema. I am cloning this schema simply to run a test of some scripts that will modify the schema. I like to call my temporary schemas "delme" so I remember to delete them.
[I created user in SQL Developer and granted all because I'm lazy and want it done fast.]
Crossed out because Step 4 below will actually create the user for you!
4) Import!
impdp myschema/**** directory=data_dmp dumpfile=myschema.dmp logfile=myschema.imp remap_schema='MYSCHEMA':'DELME'
1) Create a directory on the file system and let Oracle know about it.
a) [bash assumed]: mkdir /home/oracle/data_dmp
b) [in sqlplus]: create directory data_dmp as '/home/oracle/data_dmp';
grant read,write on directory DATA_DMP to myschema;
2) Export schema to file:
expdp myschema/**** dumpfile=myschema.dmp logfile=myschema.exp schemas=myschema directory=data_dmp
[I created user in SQL Developer and granted all because I'm lazy and want it done fast.]
Crossed out because Step 4 below will actually create the user for you!
4) Import!
impdp myschema/**** directory=data_dmp dumpfile=myschema.dmp logfile=myschema.imp remap_schema='MYSCHEMA':'DELME'
Subscribe to:
Posts (Atom)