Alexander Kuznetsovhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/default.aspxenCommunityServer 2.1 SP2 (Build: 61129.1)Cars, Databases, and Benchmarkshttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2014/04/22/cars-databases-and-benchmarks.aspxWed, 23 Apr 2014 00:45:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53687Alexander Kuznetsov2http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/53687.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=53687Over the last few months we have migrated some functionality from Sql Server to PostgreSql, and developed a couple of new systems powered by PostgreSql. So far I do not see that either of these two products is better than another - they are different....(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2014/04/22/cars-databases-and-benchmarks.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=53687" width="1" height="1">PostgreSqlsql-serverdatabase-migrationagile-developmentBook Review: Tribal SQL,Performance Tuning With SQL Trace and Extended Eventshttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2014/02/18/book-review-tribal-sql-performance-tuning-with-sql-trace-and-extended-events.aspxTue, 18 Feb 2014 14:51:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:52876Alexander Kuznetsov3http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/52876.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=52876Tara Kizer wrote this chapter, and it is relevant for us developers. Tara briefly explains how to use the GUI, the Profiler, and states that it can heavily impact the performance on the server. Then she explains how to set up a server-side trace which...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2014/02/18/book-review-tribal-sql-performance-tuning-with-sql-trace-and-extended-events.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=52876" width="1" height="1">Book ReviewTribal SQLBook Review: Tribal SQL, Chapter 1.http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2014/02/08/book-review-tribal-sql-chapter-1.aspxSat, 08 Feb 2014 22:49:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:52760Alexander Kuznetsov2http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/52760.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=52760"Tribal SQL" is an interesting book, so I will be reviewing some, although not all, of its chapters. Quoting from the introduction, "This is a book for DBAs, for things you think they really ought to know". As an agile developer, I clearly do not belong...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2014/02/08/book-review-tribal-sql-chapter-1.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=52760" width="1" height="1">Book ReviewTribal SQLLearning postgreSql: serialization failures with SERIALIZABLEhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2014/01/06/learning-postgresql-serialization-failures-with-serializable.aspxMon, 06 Jan 2014 17:38:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:52346Alexander Kuznetsov0http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/52346.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=52346We shall reproduce a serialization failure and see SERIALIZABLE isolation level enforces data integrity. Prerequisites We shall need the following test data: CREATE TABLE Carpools ( Car_Name VARCHAR NOT NULL, Passenger VARCHAR NOT NULL ); INSERT INTO...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2014/01/06/learning-postgresql-serialization-failures-with-serializable.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=52346" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: Fun with REPEATABLE READhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/12/19/learning-postgresql-fun-with-repeatable-read.aspxThu, 19 Dec 2013 15:11:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:52180Alexander Kuznetsov0http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/52180.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=52180In this post we shall run some examples under REPEATABLE READ, and see how they behave differently. Setting up test data On SQL Server, run the following: CREATE TABLE Tickets ( ID INT NOT NULL, Problem VARCHAR ( 100 ) NOT NULL, SpaceFiller CHAR ( 200...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/12/19/learning-postgresql-fun-with-repeatable-read.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=52180" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: READ COMMITTED and Data Integrityhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/12/02/learning-postgresql-read-committed-and-data-integrity.aspxMon, 02 Dec 2013 17:34:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51952Alexander Kuznetsov6http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51952.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51952As we have just discussed, READ COMMITTED isolation level behaves very much like Sql Server's READ_COMMITTED_SNAPSHOT. As such, we need to be very careful with data integrity - lots of code that just works on Sql Server under its default isolation level,...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/12/02/learning-postgresql-read-committed-and-data-integrity.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51952" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: different behavior with READ UNCOMMITTED/COMMITTEDhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/27/learning-postgresql-different-behavior-with-read-uncommitted-committed.aspxWed, 27 Nov 2013 20:25:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51933Alexander Kuznetsov0http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51933.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51933Because of multi-valued concurrency control aka MVCC, there are many differences in queries' behavior with different isolation levels. Before running examples, let us set up test data. Setting up test data The following script sets up the data we shall...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/27/learning-postgresql-different-behavior-with-read-uncommitted-committed.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51933" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: XACT_ABORT is Always ONhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/26/learning-postgresql-xact-abort-is-always-on.aspxTue, 26 Nov 2013 21:20:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51926Alexander Kuznetsov0http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51926.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51926PostgreSql has simple and consistent error handling, which can be roughly explained in Sql Server terms as follows: XACT_ABORT is Always ON. In other words, error handling in PostgreSql has substantially less features, but it does have all the features...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/26/learning-postgresql-xact-abort-is-always-on.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51926" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: No Nested Transactions, No Transactions in PL/pgSQLhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/25/learning-postgresql-no-nested-transactions-no-transactions-in-pl-pgsql.aspxMon, 25 Nov 2013 15:58:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51888Alexander Kuznetsov3http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51888.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51888Transactions and error handling in PostgreSql are substantially different. We shall discuss only the features that we needed to learn to complete our project. It will take us more than one post to describe them all. TL;DR; Transactions and error handling...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/25/learning-postgresql-no-nested-transactions-no-transactions-in-pl-pgsql.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51888" width="1" height="1">Learning PostgreSql: UPDATE...FROM works differentlyhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/20/learning-postgresql-update-from-works-differently.aspxWed, 20 Nov 2013 17:33:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51845Alexander Kuznetsov11http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51845.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51845UPDATE...FROM command on PostgreSql may raise no errors, but produce completely different results. Later I shall provide a repro, but first let us briefly refresh how UPDATE...FROM works on Sql Server. UPDATE...FROM on SQL Server ignores ambiguity We...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/20/learning-postgresql-update-from-works-differently.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51845" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: replacing TOP and APPLY with LIMIT and LATERALhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/19/learning-postgresql-replacing-top-and-apply-with-limit-and-lateral.aspxTue, 19 Nov 2013 17:06:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51812Alexander Kuznetsov3http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51812.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51812All SQL Server queries using TOP and/or APPLY need to be changed - PostgreSql uses completely different syntax. Replacing TOP with LIMIT The following script shows how to do that: CREATE TEMP TABLE Runs ( State_Code VARCHAR , Run_Date DATE , Distance...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/19/learning-postgresql-replacing-top-and-apply-with-limit-and-lateral.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51812" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: differences in implementation of constraintshttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/15/learning-postgresql-differences-in-implementation-of-constraints.aspxFri, 15 Nov 2013 17:33:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51774Alexander Kuznetsov0http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51774.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51774Constraints in PostgreSql are implemented somewhat differently. To use them efficiently, there are quite a few details we need to be aware of. NULLs and uniqueness In PostgreSql, unique constraints allow multiple NULLs. This behavior is ANSI standard....(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/15/learning-postgresql-differences-in-implementation-of-constraints.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51774" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: Embracing Change With Copying Types and VARCHAR(NO_SIZE_NEEDED)http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/12/learning-postgresql-embracing-change-with-copying-types-and-varchar-no-size-needed.aspxTue, 12 Nov 2013 16:56:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51708Alexander Kuznetsov4http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51708.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51708PostgreSql 9.3 allows us to declare parameter types to match column types, aka Copying Types. Also it allows us to omit the length of VARCHAR fields, without any performance penalty. These two features make PostgreSql a great back end for agile development,...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/12/learning-postgresql-embracing-change-with-copying-types-and-varchar-no-size-needed.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51708" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: old versions of rows are stored right in the tablehttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/07/learning-postgresql-old-versions-of-rows-are-stored-right-in-the-table.aspxThu, 07 Nov 2013 20:26:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51644Alexander Kuznetsov6http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51644.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51644PostgreSql features multi-version concurrency control aka MVCC. To implement MVCC, old versions of rows are stored right in the same table, and this is very different from what SQL Server does, and it leads to some very interesting consequences. Let us...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/07/learning-postgresql-old-versions-of-rows-are-stored-right-in-the-table.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51644" width="1" height="1">Agile-learningPostgreSqlLearning PostgreSql: overloadinghttp://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/05/learning-postgresql-polymorphism.aspxTue, 05 Nov 2013 21:17:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51624Alexander Kuznetsov4http://www2.sqlblog.com/blogs/alexander_kuznetsov/comments/51624.aspxhttp://www2.sqlblog.com/blogs/alexander_kuznetsov/commentrss.aspx?PostID=51624Functions in PL/PgSql can be overloaded, which is very different from T-SQL. Demonstrating overloading For example, the second CREATE FUNCTION in the following script does not replace the first function - it creates a second one: CREATE OR REPLACE FUNCTION...(<a href="http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/05/learning-postgresql-polymorphism.aspx">read more</a>)<img src="http://www2.sqlblog.com/aggbug.aspx?PostID=51624" width="1" height="1">Agile-learningPostgreSql