Installing PostgreSQL

As per my previous blog on PostgreSQL, I'm going to start a journey to study this new area of interest. As the first step I'm going to install it on my local development/testing environment. I'm gong to follow articles from PostgreSQL Tutorial site and PostgreSQL course on W3 Schools site.

I have downloaded installation package for Window from Enterprise DB site -> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads. As of this article date, I'm using version 16.3 and it is about 375 MB.

I have followed the easy installation wizard.

Select the installation path

Select components. I have selected them all as I'm studying.

• PostgreSQL server -> the core database engine which you must install
• pgAdmin4 -> GUI to manage PostgreSQL server (like SSMS)
• Stack Builder -> Drivers and stuff for development (I guess)
• Command Line Tools

Select you data directory -> I choose default which is the "Data" sub folder in installation directory

Choose password for super user (administration/sa password)

Choose the port number for the server. I left it default, which is 5432.

Choose locale.

Check the installation summary and make sure all settings selected are looks ok.

Install.

Took about 5 minutes in my not so high end test machine and at the end it has asked me whether I want to launch the stack builder to install additional drivers and all. I choose yes.

At this point main installation is done and you can check the installation by launchign pgAdmin GUI from start menu.

Optionally you can continue with stack builder setup.

Stack Builder Setup

Once main PostgreSQL finishes Stack Builder setup starts with following screen:

You need to choose your PostgreSQL server from the drop down list (in case there are more than one installation).

Then you need to select categories for components to install.

•   Add-ons, tools and utilities
• EDB Language Pack v4.3-1 (programming language pack including python)
• pgAgent (64 bit) for PostgreSQL 16 v4.2.2-1 (Job agent)
• pgBouncer v1.22.1-1 (connection pooler)

•   Database Drivers
• Npgsql v3.2.6-3 (Microsoft .NET data provider)
• pgJDBC v42.7.2-1 (JDBC driver)
• psqlODBC (32 bit) v13.02.0000-1 (ODBC driver 32bit)
• psqlODBC (64 bit) v13.02.0000-1 (ODBC driver 64bit)

•   Database Server (additional version of Postgre SQL server, which I will not install)
• PostgreSQL (64 bit) v12.19-2
• PostgreSQL (64 bit) v13.15-2
• PostgreSQL (64 bit) v14.12-2
• PostgreSQL (64 bit) v15.7-2
• PostgreSQL (64 bit) v16.3-2 (installed)

• Registration-required and trial products
• • EnterpriseDB Tools -> No idea what these are, hopefully we learn about them in future)
  • Migration Toolkit v57.0-1
  • PEM SQL Profiler Plugin for PostgreSQL 16 (64 bit) v41.2
  • Postgres Enterprise Manager Agent v8.7.1-2
  • Postgres Enterprise Manager Agent v8.7.0-1
  • Postgres Enterprise Manager Server v8.7.0-1
  • Postgres Enterprise Manager Server v9.6.0-2
  • Replication Server v6.2.19 - 1
  • SQL/Protect for PostgreSQL (64bit) v16.0 - 2
  
  
• Spatial Extensions
• PostGIS 3.4 Bundle for PostgreSQL 16

•   Web Development
• PEM-HTTPD v2.4.59-1

Once you select your components, start the installation, which will download components.

This will download various components about 2Gb (depends on what you selected from above options).

Once all components are downloaded, you have options to setup them.

Please note that if you start this installation it will install all the components you choose one by one (each component have separate installation wizard). So if you choose 5 you will have to complete all 5 installation wizards.

APPLY Operator in T-SQL

APPLY operator was introduced with SQL Server 2005 edition and was great help to join records set with table value function or table value expression.

There are two APPLY operators.

1. CROSS APPLY

2. OUTER APPLY

In simplest terms, CROSS APPLY behave like INNER JOIN and OUTER APPLY behave like LEFT OUTER JOIN.

Let's consider a following example table structure

-- Employee Table

CREATE TABLE Employee (
    EmpId INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Age INT,
    BranchId INT
);

-- Branch Table

CREATE TABLE Branch (
    BranchId INT PRIMARY KEY,
    BranchName VARCHAR(100)
);

Here is my test data:

Branch Table

 

Employee Table

I have a table value function which brings branch id and average age of employees in that branch.

CREATE FUNCTION GetAverageEmployeeAgeByBranch
(
    @BranchId INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT BranchId, AVG(Age) AS AverageAge
    FROM Employee
    WHERE BranchId = @BranchId
    GROUP BY BranchId
);

I need to show branch id, branch name and average employee age for that branch in my query. First I have used CROSS apply in my query.

SELECT b.BranchId, BranchName, e.AverageAge

From Branch b CROSS APPLY dbo.GetAverageEmployeeAgeByBranch(BranchId) as e

ORDER BY BranchId

Result as follows:

Then I have used OUTER APPLY:

SELECT b.BranchId, BranchName, e.AverageAge

From Branch b OUTER APPLY dbo.GetAverageEmployeeAgeByBranch(BranchId) as e

ORDER BY BranchId

Result as follows:

As you can see in the first result set, SQL server only brings branches where there are employees on them (i.e. inner join), but on second result set it has bring all branches, where it cannot find employee age, it has put null on them (like LEFT OUTER join).

You can also use APPLY operator with table value expressions (i.e. sub queries).

SELECT b.BranchId, b.BranchName, e.EmployeeName

FROM Branch b CROSS APPLY 

(SELECT e.BranchId, 

STRING_AGG(EmployeeName, ';') WITHIN GROUP (ORDER BY e.EmployeeName) As EmployeeName 

FROM Employee e WHERE e.BranchId = b.BranchId GROUP BY e.BranchId) as e

Above query brings concatenated employee names for a given branch:

Shut It Down Correctly

My new (newish but used, provided by company) Windows 11 laptop keeps drains it's battery even when I shut it down from the Windows Shut Down menu (I turn the wall plug off so battery doesn't keep (trickle) charging over night).

I tried several power settings, but wasn't able to fix it. I wanted it to shut down properly and save battery. I didn't want it to startup quickly, I'm not in a hurry to start it up.

So I start searching for solutions.

That's when I found out Windows 11 hibernate mode and Fast Startup.

When hibernate mode is enabled, even when you shut down the computer from Shut Down menu, it goes to hibernate state, i.e. it will consume power to keep settings on the memory.

Fast Startup, which is another feature in Windows 11, will also make more battery to consume, even though you shut down your computer.

You need to turn off both these options to make it correctly shut down (which I wanted to do).

Turning of Fast Startup

1. Window Search bar (on start menu) type "Control Panel"

2. Control panel, choose "Hardware and Sound"

3. Under "Power Options", choose "What the power button does"

4. Click on "Change settings that are currently unavailable" link.

5. You will see a option called "Turn on Fast startup (recommended)" option. If it is ticked, un-tick it.

6. Save your settings.

Turn off Hibernate mode

1. Open command line (or terminal) under "Administrator"

2. Type following and press enter

         powercfg -h off

Turning off these two will make laptop to shut down properly.

While you are worry about battery, if you need to see very detail report about battery, you can do this by running following command under administrator command line (terminal):

        powercfg /batteryreport /output "C:\battery_report.html"

Note you can change the output path if you wish.

Rebuilding system databases in SQL Server

SQL Server has few system databases, which help it to manage the system. They are:

• master
• msdb
• model
• tempdb
• resource

tempdb is rebuilt every time your restart your SQL server, so there is not issues there.

In case of a corruption, how do you re-create all those system databases? One approach is restoring backup for those system databases. But if you don't backup system databases or backups also contains the corruption, you have no other option, but recreate them.

However, note that if you re-create them, you will lost any custom database object you created in those databases (i.e. it is like doing a factory reset) hence you will lost things like customized settings and agent jobs.

Prep

If you want current settings to be applied after restore/rebuild, you need to do following pre-requisite tasks:

        1. Note down current server configuration. You can fetch current server configuration by running following SQL (you probably want to extract this to a excel sheet or to a user database):

            SELECT * FROM sys.configurations;

        2.   If you hoping to restore to latest hotfixes, note down current hotfixes and current collation:

            SELECT

                    SERVERPROPERTY('ProductVersion ') AS ProductVersion,

                    SERVERPROPERTY('ProductLevel') AS ProductLevel,

                    SERVERPROPERTY('ResourceVersion') AS ResourceVersion,

                    SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,

                    SERVERPROPERTY('Collation') AS Collation;

        3. Following query shows current database file locations. Note this down if you are not using the default locations.

        SELECT name, physical_name AS current_file_location

        FROM sys.master_files

        WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

Rebuild

In order to rebuild, you need SQL Server installation media. There fore please mount or insert the installation media into the server you are rebuilding.

Then run following command on command line (make sure you are on the correct folder):

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName ]

There are many other parameters you can specify. For more detail please refer to Microsoft documentation - https://learn.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-ver16

Once rebuild completed, you can put your old settings back if necessary.

Creating Windows Task to Call URL

There are many ways you can call a URL from a windows schedule task. Old time we used vbscript to call a URL. We can create a small console app on .Net (Visual Studio) to call a URL and then schedule the console app to run periodically.

But what is the best and most easiest way these days. I wanted to find out recently as I wanted to call a URL from a website periodically to keep the website live (not going to sleep).

Answer I got from several GPTs were to use Powershell scritp.

This is the powershell command to use:

-ExecutionPolicy unrestricted 

    -Command "(New-Object Net.WebClient).DownloadString('YOUR-URL')"

To call this using a Windows Task scheduler do the following steps:

1. Launch Task Scheduler and create a task (don't select basic task)

2. Give an appropriate name and fill out the properties as suited for you

3. Create a trigger to run the task. You can schedule it to run periodically

4. In the "Actions" tab, click new and type "powershell" on "Program/script" box

5. In the "Arguments" box, type the above text (replace the url with your URL).

6. Click ok and save the task.

Make sure to run the script with a user who have appropriate permission to run powershell and commands.

Getting into PostgreSQL

PostgreSQL is open source database management system which can compete with more established DBMSs like SQL server and MySQL. In recent years PostgreSQL has grown popularity exponentially. Specially, we can see lot of SQL server and MySQL database experts are turning their attention to it. This is why novice like me also should have a look at what's the fuzz.

There are many theories why PostgreSQL has suddenly become very popular. But I agree that one of the main factors to it is that MySQL community is beginning to distance them self from MySQL, because it is now owned by Oracle (vendor lock). 

In addition to that, I see SQL server new features are becoming less and less enthusiastic people who use it. For example SQL Server 2022, release was disaster to Microsoft, because few of the feature didn't work the way they expected. Plus we are now in middle of the 2024, but there is no sign of the next version of SQL server yet (bit odd comparing Microsoft previous release cycles). So SQL server experts are getting little agitated and they are looking for alternative ways to keep the future bright. When expert do that rest of the community also follows.

From PostgreSQL side also it has become more and more robust over the years to compete with main players in the field. Features like non relational query support and object relational support gives the main advantages.

Considering above, I have also started looking into PostgreSQL basics. In future blogs I will be blogging about my journey with PostgreSQL.

Check() Constraint in SQL Server

CHECK() Constraint in SQL server is a data validation technique. Using CHECK() constraints, we can minimise data issues that could cause by bad application code or by incorrect queries by user at the database level.

Consider a table like below:

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year) )

Let's say we need users (or applications) to insert year as 4 digit year and year should be value between 2000 and 3000. Then we can define a check constraint like below.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year),

    CHECK(course_year >= 2000 and course_year <= 3000))

You can give a name to the constraint. This is really helpful when debugging issue. So you know which table and which column it happened.

E.g.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year),

    CONSTRAINT my_constraint1 CHECK(course_year >= 2000 and course_year <= 3000))

Check constraint can be declared in the line of the column, if it only applied to that column

E.g.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL CHECK(course_year >= 2000 AND course_year <= 3000), 

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year))

But if it reference multiple columns, it has to be on its own line.

E.g.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year),

    CONSTRAINT my_constraint2 CHECK(course_start_date <= course_end_date))

If you want to drop a check constraint:

ALTER TABLE CourseSchedule 

DROP CONSTRAINT my_constraint2

Temporary Stored Procedures

I have worked with stored procedures in SQL Server for more than decade now, but only now I got to know the existence of temporary stored procedures. Weird!

Temporary stored procedures works exactly the same way as temporary tables in SQL server.

So their name starts with # for local temporary stored procedures and ## for global temporary stored procedures.

E.g. #thisIsMyLocalTempSp

        ##thisIsMyGlobalTempSp

Same as temporary tables, local temporary stored procedures only available in current session and global temporary stored procedures is available to all sessions, but dropped when session that created it closes.

That make me think, will SQL server allow temporary functions and views too? Unfortunately (or fortunately) my research shows - No.

Note that temporary stored procedures are created on tempdb, so all the issues with tempdb will arise with them (just like temp tables).

So what is the use of temporary stored procedures? Main advantage is I think code re-use. For example if you have a very long stored proc, that uses piece of code over and over again (but you really don't want to create it as a permanent stored proc), you can create a temp stored proc and use it within permanent stored procedure.

Setting up SMTP Email Service with smtp2go

With so many Email service providers it is hard to pick a one that provide good email delivering service with reliability and no down time.

I recently had a task to evaluate email service providers to integrate our software with. There are few SMTP service providers, such as "SendGrid", "Amazon SES", "Mailgun". But to my surprise small smtp service provider called "smtp2go" topped my evaluation, because of their responsiveness to customer matters and simplicity.

Here are quick guide on how to setup smtp service with smtp2go provider.

Create an account

First you need to create a account with smtp2go website. They provide "Free Plan", which gives you 1000 email per month. I suggest you use that to evaluate and test the service before you actually commit to paid plan - https://www.smtp2go.com/pricing/

You need a work email to sign up with. You will not be able to sign up with gmail/outlook/yahoo email account.

Verify Sender

After signup, before sending any emails, you need to verify sending email addresses. There are two ways to do:

1. Verify Single Email address

2. Verify sending email domain

Verify Single email address is simple, once you add an email address to verify, smtp2go send a verification link to the email address specified. Then you can click on it to verify it.

However, single email address doesn't provide much protection for spamming and reliable email delivery. It is only good for sending occasional notification email from a software application.

Recommended method is verify domain. When domain is verified, you can send email from any email address on that domain.

In order to verify domain, you will need to add 3 CNAM entries to to your domain's DNS record. These settings are provided by smtp2go domain verification wizard, so it is easy to handle this. If you don't have access to your domain's DNS, you will need to contact who ever manage that (e.g. if you purchased your domain from GoDaddy, you can configure these setting by your self by logging into GoDaddy control panel).

SMTP User / API Key

Once domain is verified, you have two ways to use the SMTP relay. 

First one is calling their API method and send email using their API. You will require to create API key to do this. Generating API key is easy and can be done on smtp2go control panel very easily.

Second approach is to create a SMTP user. You can add as many SMTP users as you like from control panel (if you have paid plan). You need to give a user name and password will be generated for you (you can overtype it though).

SMTP Settings

SMTP settings are same for everyone using smtp2go and clearly showed on the control panel.

Use above SMTP settings and SMTP user credentials you have created to send emails from your application.

There are very comprehensive set of support articles can be found on smtp2go site -> https://support.smtp2go.com/hc/en-gb

There are few dashboard reports which shows you the status of your email delivery. You can track which email addresses are getting bounce and rejected.

You can chat with their support team from the control panel it self. For me it was very responsive.

Overall I'm pretty satisfied with their service so far.

Prevent App Pool recycle when web config changes

We all know when we need to recycle and get web app restarted we can edit the web.config file. Sometime we(developers) even do dummy change to force it restart.

But what if you don't want to recycle it, but want to update the web.config file anyway. For example I had a situation where I want to update the web.config file, but didn't wanted to recycle/restart the web app immediately, because users were using it heavily. I just needed to do the web.config update now and restart the web app later (out of working hours).

That's when I heard the setting called - "Disable Recycling for Configuration Changes".

This setting can be found in "Advanced Settings" section of an app pool. So this only effective to one app pool at a time.

Default value for this is "False".

Setting this to true will cause app pool to NOT to restarts/recycle when web.config changes.

 

Query Hints -> OPTIMIZE VS RECOMPILE

I have known query hint OPTION(RECOMPILE) for while and most I've heard was it is bad and should be used with care.

As per my understanding when OPTION(RECOMPILE) is used within a stored procedure, plan for that stored procedure get compiled every time it runs (instead of caching the plan in plan cache for future use). Compiling plan is known to be very CPU expensive as it involves many steps, specially for large complex queries. So we should avoid it as much as possible. Using OPTION(RECOMPILE) is a trade off between plan compiling CPU time vs using bad/mediocre plan for set of values.

Recently I came across another related query hint - OPTION(OPTIMIZE FOR ...)

OPTIMIZE FOR hint can be used two ways.

OPTIMIZE FOR (@myparam1 = 1, @myparam2 = 2) -> i.e. specify set of parameters and values to optimize for. So SQL server compile and cache the plan that uses value 1 and 2 for above two parameters. It doesn't use what value came first at run time (like it normally does). By changing the values run time you can have multiple plans cached for same stored procedure.

Other approach is OPTIMIZE FOR UNKNOWN -> In this case SQL server tries to get best plan for each parameter set passed in, BUT it might decided in some cases compiling for new plan will cost me lot so I will use something similar I already have in cache. In other words it try to do the trade off and try to come up with plan that cost same every time. This great article by Kendra Little on BrentOzar website explains it much more details.

Again, as we come across regularly, in performance tuning world of SQL Server, there is nothing fixed, you always need to try and and see what is best for each case.

P.S. I'm not a SQL server expert (yet), I'm still learning, so please be kind enough to comment if something wrong. These notes are my own reference mostly.