Oakies Blog Aggregator

Franck Pachot's picture

PostgreSQL transaction management in procedures

TL;DR: AUTOCOMMIT is required when calling a procedure which has some COMMIT inside.

In version 11 PostgreSQL has introduced the possibility to start, commit or rollback transactions in PL/pgSQL procedures (stored or anonymous). Most of the demos have been run from the psql default AUTOCOMMIT on, like 2ndQuadrant and dbi-services blogs. But Bryn Llewellyn (YugaByte) raised an issue when running without AUTOCOMMIT OFF (which, coming from Oracle, looks like the right choice). Here is my investigation on this.

You should read my last post Transaction management in PostgreSQL and what is different from Oracle if you are not totally familiar with PostgreSQL transaction management and auto-commit, as I wrote it as an introduction to this analysis.

Tracing with GDB

Here is what I run (I’m using the function from the previous post):

psql
\set PROMPT1 '\t\t\t\t\t\t>>>%`date +%H:%M:%S`<<<\n%/%R%# '
select pg_backend_pid();
-- attach gdb to backend and set breakpoint on exec_simple_query
\echo :AUTOCOMMIT
call my_inserts(1111,1111);
-- gdb stops on breakpoint and continue
\set AUTOCOMMIT off
call my_inserts(1112,1112);
-- gdb stops on breakpoint and continue

Here is my gdb session on a second terminal:

gdb -p $(pgrep -nl postgres)
define hook-stop
shell echo -e "\t\t\t\t\t\t>>>`date +%H:%M:%S`<<<"
end
print getpid()
break exec_simple_query
cont
## back to psql to call the procedure
print query
cont
## back to plsql to set AUTOCOMMIT off and run again

I have added timestamps in both prompts in order to show the sequence in one screenshot. Here is the result. The first call succeeded (in AUTOCOMMIT on) but the second call failed (with AUTOCOMMIT off) because psql has issued a BEGIN before the CALL:

I have 2 questions here:

  • Why does psql initiates a transaction before the call when it is not in AUTOCOMMIT?
  • Why does the procedure’s COMMIT fail when in a transaction opened outside of the procedure?

Why does the COMMIT fail when in a transaction opened outside?

From the previous step, I rollback (the transaction, initiated by the client when in AUTOCOMIT off, was aborted). And call the procedure again after having set the following breakpoints:

 break SPI_start_transaction
break SPI_commit
print _SPI_current->atomic
cont

I’ve set those and displayed “atomic” because the error message comes from the following PostgreSQL code:

https://github.com/YugaByte/yugabyte-db/blob/c02d4cb39c738991f93f45f4895f588a6d9ed716/src/postgres/src/backend/executor/spi.c#L226

 set pagination off
print _SPI_current->atomic
backtrace

I can see atomic=true in the call to the following function

ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver *dest)

and the comments in functioncmds.c explains the idea of “atomic commands” — those were transaction control commands are disallowed. Here is the postgres source code and the explanation:

YugaByte/yugabyte-db

* Inside a top-level CALL statement, transaction-terminating commands such as COMMIT or a PL-specific equivalent are allowed. The terminology in the SQL standard is that CALL establishes a non-atomic execution context. Most other commands establish an atomic execution context, in which transaction control actions are not allowed.

This makes sense. But I am in a “top-level CALL statement”, so why is atomic set to true? The parent in the stack is standard_ProcessUtility and here is how atomic is defined:

bool isAtomicContext = (!(context == PROCESS_UTILITY_TOPLEVEL || context == PROCESS_UTILITY_QUERY_NONATOMIC) || IsTransactionBlock());

Ok, I think I got it. There’s another reason to set atomic=true: I am in already in a transaction block. Just confirmed by running the same

with those additional breakpoints:

break standard_ProcessUtility
continue
print context
print IsTransactionBlock()
continue
print context
print IsTransactionBlock()

So, I am already in a transaction when executing the CALL and documentation says that:

If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements

Ok, documentation is perfect. Why did I need to gdb in order to get this? It’s more fun

Franck Pachot's picture

Is a datafile OMF or not?

TL;DR: there’s no flag (only the name tells it), but a function

You want to know which files are Oracle Managed Files or not? Maybe because you like OMF (like I do as I show no interest for file names when I have tablespaces) and you have non-OMF files that you want to move to OMF ones.

There’s no flag in V$DATABASE or DBA_DATA_FILES. Only the name of the file (starting with ‘o1_mf’, like Oracle Managed Files, and ending with ‘.dbf’, like Database File) makes it OMF or not. But I don’t like to rely on name convention so I searched how Oracle is doing it. There’s an isFileNameOMF procedure in the RMAN packages.

dbms_Backup_Restore.isFileNameOmf

Here is a procedure that displays which files are ASM, and which ones are regular user-managed ones:

declare
isomf boolean;
isasm boolean;
begin
for c in (select name from v$datafile)
loop
dbms_backup_restore.isfilenameomf(c.name,isomf,isasm);
if isasm then dbms_output.put('ASM '); else
if isomf then dbms_output.put('OMF '); else dbms_output.put('reg '); end if;
end if;
dbms_output.put_line(c.name);
end loop;
end;

Output on non-ASM with some OMF and some regular files

The procedure has another boolean which I guess is for controlfile autobackup names, but I don’t need it.

‘Rename’ OMF file names

In 12c, the procedure can be called from an in-line function in a SQL query. Here is how I generate all the ‘ALTER DATABASE MOVE DATAFILE’ commands for non-OMF files:

with function isomf( name v$datafile.name%type) return char as
isomf boolean;
isasm boolean;
begin
dbms_backup_restore.isfilenameomf(name,isomf,isasm);
if isomf then return 'Y'; else return 'N'; end if;
end;
select 'alter database move datafile '''||file_name||''';'
from dba_data_files
where isomf(file_name)='N';
/

Then, the files will go to the db_create_file_dest with an OMF name that you can change at session level.

Side comment about online “rename”

The previous script uses the 12c Online datafile move to rename them. But I’ve put “rename” in quotes because it copies the file even when it remains within the same filesystem. That’s different from the Linux “mv” which changes only the name when in the same filesystem. Here is an example:

host pkill -9 strace
connect / as sysdba
create tablespace franck datafile size 5M;
column spid new_value pid
select spid from v$process join v$session on v$session.paddr=v$process.addr where sid=sys_context('userenv','sid');
column spid clear
column file_name new_value file_name
select file_name from dba_data_files order by bytes fetch first 1 rows only;
column file_name clear
set escape on
host strace -fy -e trace=file,desc -p &pid 2>\&1 | grep '&file_name'\& :
alter database move datafile '&file_name';
disconnect
host grep dbf strace.log
drop tablespace franck including datafiles;

Update 02-AUG-2019

Thanks to @Mikhail Velikikh here is a function that is probably a better solution: DBMS_METADATA_UTIL.IS_OMF

dbmsmetu.sql

It actually calls the UTL_XML.ISNAMEOMF but has the advantage to be a function, returning 1 for OMF file names or 0 otherwise.

Jonathan Lewis's picture

LOB reads

This is a blog note that started life in September 2001 (which was before I started blogging, I think), got drafted as a blog in January 2014 because of a question on the Oracle-L list server, and has finally got to publication following a question on the ODC database forum. (But the comments in the blog don’t look as if they are relevant to the question.)

The question on Oracle-l was:  Why is the same CLOB datablock read multiple times?

The answer is basically: client programs tend to set a “sensible” fetch buffer size for LOBs and if the buffer size is less than the size of the LOB the block(s) the LOB is in have to be visited multiple times and for nocache LOBs that means the block(s) will be read multiple times.

This can be seen quite easily in SQL*Plus which has two helpful parameters (one dating back to at least v5):

set long N
set longchunksize M

The “long” setting tells SQL*Plus how much of the LOB to display on screen, the “longchunksize” tells SQL*Plus how much of the LOB to fetch from the database on each call. The default value is 80 for both settings. So let’s set up some data and do a few simple demonstrations using nothing more subtle than autotrace.


rem
rem     Script:         lob_fetch_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2012
rem     Purpose:        
rem
rem     Last tested 
rem             18.3.0.0
rem             12.1.0.2
rem             11.2.0.2
rem

create table t1 (
        id      number , 
        v1      varchar2(60),
        c1      clob, 
        c2      clob
)
lob(c1) store as (enable storage in row cache)
lob(c2) store as (enable storage in row cache)
;

begin
        for i in 1..1000 loop
                insert into t1 values(i, lpad(i,60), lpad(i,60), empty_clob());
        end loop;
        commit;
end;
/

execute dbms_stats.gather_table_stats(null,'t1')

I’ve got a table with two CLOB columns, both declared to enable storage in row and cache. I’ve also got a varchar2() column, and I’ve loaded one of the CLOB columns and the varchar2() column with a 60 character string, setting the other CLOB column explicitly to the empty_clob() value – which is not the same as setting it to NULL.

Now I’ll do 3 selects, fetching the id column and one other column, showing only the autotrace statistics:


set long 80
set longchunksize 80

set arraysize 5000

set autotrace traceonly statistics

prompt  ==============
prompt  Varchar2 fetch
prompt  ==============

select id, v1 from t1;

prompt  ================
prompt  Small CLOB fetch
prompt  ================

select id, c1 from t1;

prompt  ================
prompt  Empty CLOB fetch
prompt  ================

select id, c2 from t1;

set autotrace off

I’ve explicitly set long and longchunksize to 80 (the defaults) just to make it clear that that’s what they are for this test; and I’ve set the arraysize to 5000 so that I can get the 1,000 rows of data in the smallest number of fetches. Here are the three sets of autotrace statistics:


==============
Varchar2 fetch
==============

1000 rows selected.


Statistics
----------------------------------------------------------
         11  recursive calls
         11  db block gets
         75  consistent gets
          0  physical reads
       2040  redo size
      70545  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

================
Small CLOB fetch
================

1000 rows selected.


Statistics
----------------------------------------------------------
          4  recursive calls
          5  db block gets
       2036  consistent gets
          0  physical reads
        992  redo size
     707454  bytes sent via SQL*Net to client
     296624  bytes received via SQL*Net from client
       2002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

================
Enpty CLOB fetch
================

1000 rows selected.


Statistics
----------------------------------------------------------
          3  recursive calls
          5  db block gets
       2036  consistent gets
          0  physical reads
       1048  redo size
     585454  bytes sent via SQL*Net to client
     296624  bytes received via SQL*Net from client
       2002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Key points to note (ignoring minor details like the recursive calls to parse the statements):

  • The query for the the varchar2() column did two SQL*Net roundtrips and 75 consistent gets.
  • The query for the small, inline, CLOB did 2,002 SQL*Net roundtrips and 2,036 consistent gets
  • The query for the empty CLOB did exactly the same number of SQL*Net roundtrips and consistent gets as for the small CLOB

Because we were selecting a LOB column SQL*Plus switched from array fetches to single row fetches. In the first fetch of each pair of fetches it fetched the non-CLOB columns and the “LOB locator”; then in the second fetch of each pair it fetched the CLOB data – and it did this even when the LOB locator could (in principle) have told it that there was no data to fetch. (If you select both clob columns in the same query the number of SQL*Net roundtrips will go up to 3 per row in this test.)

Remember I said there was a difference between empty_clob() and NULL ? If you update the table to set c2 to null before running the query to select c2 you’ll see only 1,000 (and a few) SQL*Net roundtrips – you still get single row processing because you’re selecting a LOB column but when the NULL  arrives at SQL*Plus the code will know that there is no LOB data to retrieve.

Now try this:


truncate table t1;

begin
        for i in 1..1000 loop
                insert into t1 values(i, lpad(i,60), lpad(i,180), empty_clob());
        end loop;
        commit;
end;
/

execute dbms_stats.gather_table_stats(null,'t1')

set long 170
select id, c1 from t1;

Pause for thought as you work out what the stats will look like
.
.
.
.
.
The longchunksize (still at the default of 80) is now too small to collect all the data that should be displayed in one SQL*Net roundtrip – it’s going to take 3 roundtrips to get 170 bytes, so we might expect to see about 4,000 SQL*Net roundtrips and a similar number of consistent gets. This is what the stats look like:


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
       4030  consistent gets
          0  physical reads
          0  redo size
    1485454  bytes sent via SQL*Net to client
     866624  bytes received via SQL*Net from client
       4002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Sure enough we do single row fetches, one SQL*Net roundtrip for each row with its LOB locator, then three more roundtrips for each LOB for a total of 4,000 round trips and (approximately) 4,000 consistent gets.

One last test – let’s change the c1 clob to nocache and disable storage in row then repeat the last experiment where the long setting is larger than the longchunksize setting.


alter table t1 move lob(c1) store as (disable storage in row nocache);
execute dbms_stats.gather_table_stats(null,'t1')
select id, c1 from t1;

Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
       4001  consistent gets
       6000  physical reads
          0  redo size
    1485454  bytes sent via SQL*Net to client
     866624  bytes received via SQL*Net from client
       4002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Now there’s a surprise!  I was expecting to see 3000 physical reads as each “out of row, nocache” LOB was accessed three times to pick up the three pieces of longchunksize using direct path reads. So why have I got 6,000 physical reads? Time to enable extended tracing (event 10046, level 8) and repeat.

Here’s a sample from the trace file – this trace is from 18.3, but the LOBREAD lines are a feature that appeared some time around 11.2.0.2.

FETCH #140355181475400:c=58,e=57,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=3617692013,tim=5288719901
WAIT #140355181475400: nam='SQL*Net message from client' ela= 78 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720015

WAIT #0: nam='direct path read' ela= 12 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720198
WAIT #0: nam='direct path read' ela= 8 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720288
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720326
LOBREAD: type=PERSISTENT LOB,bytes=80,c=247,e=246,p=2,cr=1,cu=0,tim=5288720350

WAIT #0: nam='SQL*Net message from client' ela= 82 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720483
WAIT #0: nam='direct path read' ela= 12 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720733
WAIT #0: nam='direct path read' ela= 8 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288720836
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288720873
LOBREAD: type=PERSISTENT LOB,bytes=80,c=0,e=261,p=2,cr=1,cu=0,tim=5288720898

WAIT #0: nam='SQL*Net message from client' ela= 121 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721071
WAIT #0: nam='direct path read' ela= 11 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288721216
WAIT #0: nam='direct path read' ela= 8 file number=2 first dba=2867 block cnt=1 obj#=132008 tim=5288721300
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721335
LOBREAD: type=PERSISTENT LOB,bytes=20,c=0,e=236,p=2,cr=1,cu=0,tim=5288721359

WAIT #0: nam='SQL*Net message from client' ela= 73 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721506
WAIT #140355181475400: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721664
FETCH #140355181475400:c=51,e=51,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=3617692013,tim=5288721695
WAIT #140355181475400: nam='SQL*Net message from client' ela= 74 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288721801

WAIT #0: nam='direct path read' ela= 12 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288721939
WAIT #0: nam='direct path read' ela= 47 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288722065
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722122
LOBREAD: type=PERSISTENT LOB,bytes=80,c=357,e=357,p=2,cr=1,cu=0,tim=5288722204

WAIT #0: nam='SQL*Net message from client' ela= 81 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722351
WAIT #0: nam='direct path read' ela= 10 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288722489
WAIT #0: nam='direct path read' ela= 11 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288722671
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722720
LOBREAD: type=PERSISTENT LOB,bytes=80,c=0,e=349,p=2,cr=1,cu=0,tim=5288722746

WAIT #0: nam='SQL*Net message from client' ela= 76 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288722874
WAIT #0: nam='direct path read' ela= 11 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288723013
WAIT #0: nam='direct path read' ela= 10 file number=2 first dba=2868 block cnt=1 obj#=132008 tim=5288723160
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=132008 tim=5288723199
LOBREAD: type=PERSISTENT LOB,bytes=20,c=0,e=302,p=2,cr=1,cu=0,tim=5288723224

As you can see, the client seems to FETCH one row then do two direct path reads to read 80 bytes from a LOB, then another two direct path reads for the next 80 bytes, then another two direct path reads for the last 20 bytes. So the 6,000 reads is (strangely) doubling the 3,000 I was expecting to see and the trace file seems to say it’s really happening that way.

Unfortunately I decided to take snapshots of the session stats at this point – and that made things even worse (claiming, for example, 4,000 “consistent gets” but 7,000 “no work consistent gets”), the stats seem to be rather messed up and the whole thing looked very different when I ran this particular test on 12.1.0.2 (which claimed only one direct path read per CLOB rather than the three I was expecting).

Bottom line, though: notwithstanding any oddities of reporting, when your client progam is fetching LOBs from the database, you probably have to put up with:
single row fetches – that get the LOB locator then multiple fetches for each LOB, with the number of LOBREAD calls dependent on the buffer size the client allocates for pulling LOB data from the database.

So when handling LOBs make sure you know how the client can be configured to minimise SQL*Net roundtrips, and check a few simple examples looking at the the trace files, session stats and session events before you start loading data at production volumes.

 

dbakevlar's picture

Linux Scripting, Part IV- Scripting for Longevity

We’ve learned a lot about commands, utilities and how to create a script, but we need to discuss the importance of scripting for longevity.

What is Scripting for Longevity?  We have a tendency to focus on scripting to automate something WE might not want to perform manually, but avoid what we think might void our value.  We may try to ensure there is necessity for our role or our knowledge as we create scripts.  This can be built into the execution process, scheduling, arguments, pre-run or post-run steps.  This doesn’t make us an asset, but a liability and against what I call, “the Code of Conduct” when automating.

Questions

The questions you have to ask yourself as you script out solutions are:

  • Am I automating what users need automated or just what I find tedious?
  • Am I building out scripts that have hardcoded values in them vs. dynamically provided, ensuring ongoing support from me?
  • Will I need to update this script in 5 weeks, 6 months, 1 year?

I’m going to admit, that the reason I didn’t embrace Powershell at first, was most of the examples I found were of full of hardcoded values.  I found it incredibly obtuse, but I started to realize that it came from many sources who might not have the scripting history that those of other shells, (this was just my theory, not a lot of evidence to prove on this one, so keep that in mind…)  As Powershell scripts have matured, I’ve noticed how many are starting to build them with more dynamic values and advance scripting options, and with this, I’ve become more comfortable with Powershell.

I think the best way to learn is to see real examples, so let’s demonstrate.

Environment

When you set environment variables as part of the script user’s login, this can eliminate extra coding.  Added to the .bashrc or a .profile will ensure that these values are already known to the script and are available to it. Instead of entering paths to directories or setting up the environment, it can be part of the environment as soon as the user logs in. Example of .profile-  in our example, we’ll call the file .profile_sql, as we can create a couple different ones, then maintain those couple instead of tons of variables in the individual scripts:

export SQL_HOME=/opt/app/mssql
export SQLDB=finance1
export myip=$(curl http://ifconfig.me)
export subscriptionID=$(az account show | grep id |awk  '{print $2}'| tr -d \"\,)

When writing a script, you can then use this to set the values from the profile as part of your script and eliminating the need to set this in the script.  It also allows you to have environment profile to maintain.  In the above example, we’ve set the following:

  • The installation home for SQL Server
  • The name of the default database that will be used when logging in with SQLCMD
  • The IP Address for the user’s workstation
  • The Azure subscription ID for the User

The Script

In our shell script, we’ll begin by setting our shell, then executing the .profile_sql, setting everything we just discussed:

#/bin/bash
. ~/.profile_sql

The profile can have everything needed for this script and any others required for the support of other scripts, allowing for recycling, even if you only need a few for a unique script, this profile can support everything or you can break it up by database or application environment, creating a unique profile for each.

After this, we can then set any unique variables for the script that can’t be or shouldn’t be set at the profile level and begin scripting.  Notice that we can use the variables from the profile inside other variables:

set -euo pipefail
IFS=$'\n\t'
export DTA_DIR=$SQL_HOME/data/
export startip=$myip
export endip=$myip
export servername=$holname
export vnet=$servername"_vnet"l
export snet=$vnet"_snet"

The DTA_DIR value is partially hardcoded, so if we moved the data files, we’d need to update this script.  Think about how we could query the database to gather these values in the profile instead, (an enhancement).  There are often options to pull values dynamically, which will remove the need for ongoing support, allowing for scripts to automatically update as changes are made behind the scenes.

As an example, I capture my IP Address, then set this value in my script after calling the .profile_sql as part of the script, updating the IP Address beforehand.  You can set values, built up from other values-  Note the vnet is a combination of the server name with a naming extension and the subnet, (snet) is just the vnet name with an extension on top of this.  The user will need an identifying name-  if they create one, why would we not want to reuse it and append it to simply locking into the one?

Recycle, Generate, Reuse

This can also be done by using utilities/commands like awk/grep/sed to capture values or queries or commands to pull data that will populate values.  If the data comes from a command and populates to the screen in a way that isn’t easily manipulated, you can output the data to a file that’s easier to work with or can be captured multiple ways as you work through the steps in your script.

Here’s two examples:

az vm image list --offer Oracle --all --publisher Oracle --output table >db.lst

The above Azure command takes the list of images for Oracle offerings, ouptus it in a table formate to a file named db.lst.  Every time I rerun it, it rewrites over the file, recycling the file and ensuring only the latest information.

I can then use this data to fulfill different scripts in different ways.

For my Oracle VM creation, I can capture the fourth column in the table output and print it for options using the AWK utility:

cat db.lst | awk  '{print $4}'

You can also do this dynamically to capture values that you want to return to the screen for the customer to choose from so they always have the latest available:

az account list-locations | grep name | awk  '{print $2}'| tr -d \"\, | grep us | grep -v australia

The above Azure command lists all the location names, (grep name) printing the second word when it contains “us”, but not when it contains, “Australia”, (which also contains “us”.  I’ve also asked it to trim out the special characters from the output, (tr -d).

Notice again, each of the commands are separated by a “|”, allowing me to connect different commands and utilities.

Building this type of features into scripts create longevity and sustainability that hard coding and manual intervention can’t.  Always ask yourself:

  • Is there a way to dynamically generate this value?
  • Have I entered anything that will require me to revisit this script sooner than later?
  • Are there utilities that can replace what I’m trying to script out manually?
  • Should I be placing this automation in the specific script or have a wrapper script or profile handle the variable setting?

These are all questions you should be asking yourself as you write a script or if you’re enhancing one.  Scripting is not a one-shot deal.  You should be working to enhance and built out scripts to create more value for you, the user and the business ongoing.

 



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [Linux Scripting, Part IV- Scripting for Longevity], All Right Reserved. 2019.

Franck Pachot's picture

Oracle RMAN Backup Optimization ON and backup archivelog command.

TL;DR: BACKUP OPTIMIZATION ON acts as a NOT BACKED UP 1 TIMES for the BACKUP ARCHIVELOG ALL command.

I always mention “not backed up n times” in my “backup archive log” commands because I think it makes sense to precise exactly how many copies of archived log we want to have available to restore from each backup device

I often configure “backup optimization on” when thinking about the tablespaces I put read-only so that they are not backed up each time(given that there is no suppression of old backups that are unknown to RMAN).

But I’ve never realized that BACKUP OPTIMIZATION and NOT BACKED UP actually both work on datafiles, archivelogs, and backupsets. And then the following is redundant:

configure backup optimization on;
backup archivelog all not backed up 1 times;

It is not a problem at all and I prefer to keep this redundancy, especially because of the name: BACKUP OPTIMIZATION looks like something which concerns only the performance, but NOT BACKED UP 1 TIMES is explicit about the availability.

As I did a quick test to verify the behavior, I share it here. Of course, all this is documented in backup optimization and notBackedUpSpec.

I start to delete all archived logs, and backups of them, in a lab database, and set the default for BACKUP OPTIMIZATION:

set nocfau;
delete noprompt backup of archivelog all;
delete noprompt archivelog all;
configure backup optimization clear;
show backup optimization;

Note that I’ve disabled controlfile autobackup (with NOCFAU) to keep the screenshots small.

I run two backups of archivelog:

backup archivelog all;
backup archivelog all;

I have two copies of backups for sequence 280:

list backup of archivelog all;

This is not usually what we want. We try to backup archivelogs frequently, for availability reasons, but also want to keep backup storage low, for retention reasons.

Not Backed Up n Times

If I don’t want a 3rd copy:

backup archivelog all not backed up 2 times;

The sequence 280 has been skipped because “already backed up” 2 times. Sequence 281 has only one backup and 282 had no backup yet.

Usually, one backup is sufficient, and this is what I schedule:

backup archivelog all not backed up 1 times;


Backup Optimization

The point of this post is to show what happens when BACKUP OPTIMIZATION is ON and we do not specify “not backed up n times”

configure backup optimization on;
backup archivelog all;

Here, the sequences that already have been backed up one time have been skipped. Only the last one (generated by “backup archivelog all” which always archives the current log) is backed up.

Archivelog range

When I don’t want to archive the current log before, I run with an UNTIL TIME ’SYSDATE’:

backup archivelog until time 'sysdate';
backup archivelog until time 'sysdate';

In this case, even with BACKUP OPTIMIZATION to ON all archived logs are backed-up again. If I don’t want to, I need to explicitly mention it:

backup archivelog until time 'sysdate' not backed up 1 times;

This is because BACKUP OPTIMIZATION ON is applied to BACKUP ARCHIVELOG only when no range is provided (FROM/UNTIL/BETWEEN SCN/TIME/SEQUENCE). This only with ALL (or LIKE).

Richard Foote's picture

Announcement: “Oracle Performance Diagnostics and Tuning” Webinar – 20-23 August 2019.

I have just scheduled a new “Oracle Performance Diagnostics and Tuning” webinar to run between 20-23 August 2019, specifically for my friends in New Zealand, but is open to anyone. It will run between 7am-11am AEST each day, which is perfect for those in NZ, but also other parts of the world such as Western […]

connor_mc_d's picture

Statistics on Load

One of the talks I gave recently on the Groundbreaker Yatra tour was on the changes that arrive with the optimizer when you move to 12c and above. We talked about changes to histograms, all of the adaptive features etc, but also, one of the simplest but coolest optimizer improvements when it comes to loading data. When you perform a direct-path insert into an empty table, or a create-table-as-select (CTAS) operation, there is no need to perform an additional DBMS_STATS call to gather statistics at the conclusion of the load. The load process itself handles that for you. Here’s a simple example of that in action


SQL> create table t (id primary key, tag)
  2  as select rownum id, to_char(rownum) tag
  3  from dual connect by level <= 50000;

Table created.

SQL>
SQL> select num_rows from user_tables
  2  where table_name = 'T';

  NUM_ROWS
----------
     50000

1 row selected.

SQL>
SQL> select leaf_blocks
  2  from user_indexes
  3  where table_name = 'T';

LEAF_BLOCKS
-----------
        103

1 row selected.

Of course, many people are now aware of this functionality, so why does it warrant a blog post? Mainly to make readers aware of a boundary case. If the table you are creating or loading is an index-organized table (IOT), then you do not get the statistics gathered automatically.


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (id primary key, tag)
  2  organization index
  3  as select rownum id, to_char(rownum) tag
  4  from dual connect by level <= 50000;

Table created.

SQL>
SQL> select num_rows from user_tables
  2  where table_name = 'T';

  NUM_ROWS
----------


1 row selected.

SQL>
SQL> select leaf_blocks
  2  from user_indexes
  3  where table_name = 'T';

LEAF_BLOCKS
-----------


1 row selected.

Initially I suspected that the reason for this is that since the statistics that are being picked up on the fly are being collected on the source data, it only becomes possible for those statistics to be transposed if the target table structure is the same as the source. But if that were (strictly) the case, then other examples would exhibit a similar restriction, such as going from compressed data to uncompressed or vice versa. But you can see that the CTAS on load statistics are fine in this circumstance:


SQL> create table t1 (id primary key, tag)
  2  compress
  3  as select * from t;

Table created.

SQL>
SQL> select num_rows from user_tables
  2  where table_name = 'T1';

  NUM_ROWS
----------
     99999

SQL>
SQL> select leaf_blocks
  2  from user_indexes
  3  where table_name = 'T1';

LEAF_BLOCKS
-----------
        208

Similarly, even if I am copying from an identical index-organized table, the statistics will not be collected.


SQL> create table t2 (id primary key, tag)
  2  organization index
  3  as select * from t;

Table created.

SQL> select num_rows from user_tables
  2  where table_name = 'T2';

  NUM_ROWS
----------


SQL>
SQL> select leaf_blocks
  2  from user_indexes
  3  where table_name = 'T2';

LEAF_BLOCKS
-----------

Whatever the root cause is, just be aware, that if you are direct-path loading IOTs, then make sure you take the time to to also collect statistics on that table before you start querying it in earnest.

Richard Foote's picture

Oracle 19c Automatic Indexing: Configuration (All I Need)

In this post, I’ll discuss how to configure the new Automatic Indexing capability introduced in Oracle Database 19c. The intent of Oracle here is to make the configuration of Automatic Indexing as simplistic as possible, with as few levers for DBAs to potentially stuff up as possible. The ultimate goal would be to have a […]

Franck Pachot's picture

PostgreSQL “bind variable peeking”

custom vs. generic plans

With my Oracle database background, I know how important it is to balance the pros and cons of using bind variables (parameterized queries) vs. literals.

  • Bind variables have the advantage to share the query execution plan with several executions so that there’s no need to compile the SQL statement (parse and optimize) each time
  • Bind variables have the inconvenient to share the query execution plan with the risk that what is optimized for one value is bad for another set of values

This post is about PostgreSQL prepared statements that contain parameters, showing when the execution plan is optimized for those parameter values. For people with an Oracle experience, I start to quickly explain the Oracle database behavior in order to emphasize the difference. Basically, there are two possibilities. The plan can be compiled without any knowledge of the values for which it will be executed. Or the plan can be optimized for a chosen fixed value (called peeked bind in Oracle or sniffed parameter in SQL Server, or custom plan in PostgreSQL).

Oracle

Until Oracle 9iR2 the execution plan was optimized before any binding or execution. From 9iR2, except when “ _optim_peek_user_binds” is set false, the optimization is deferred to the first execution and the plan will be optimized for it. It is then expected to have better estimations, especially with histogram statistics. But the future executions of the same cursor will re-use the same plan, and that can be bad if the data distribution is different for those values. In 11g, Oracle tried to prevent this in some cases where the optimizer can decide to optimize it again with the new execution value (Adaptive Cursor Sharing). But that is about cursor sharing and the goal of this post is to compare with PostgreSQL which has no cursor sharing. The only way to share a cursor for multiple executions is by preparing it — and the limitation is that it can be shared in the same session only: there is no shared pool in PostgreSQL.

PostgreSQL

With PostgreSQL, there is nothing like Oracle cursor sharing to avoid the expensive compilation at each execution. Trying to avoid that requires to prepare the statements. But even there, there is no immediate re-use of the execution plan:

  • The first 5 executions will compile the query plan for each execution — aiming at an accurate plan as if the values were passed as literals.
  • From the 6th execution, a generic plan can be reused by all future executions of the same prepared statement (in the same session). But no ‘peeked bind’ is chosen. The optimizer chooses a plan optimized without the knowledge of the parameter values.

Here is the documentation about it:

PostgreSQL: Documentation: 11: PREPARE

I’ve run a little demo to show this in db<>fiddle:

Postgres 12 Beta | db fiddle

This runs with the following table created and analyzed:

create table DEMO as select 1 n from generate_series(1,11)
union all select 2 from generate_series(1,22)
union all select 3 from generate_series(1,33)
union all select 4 from generate_series(1,44)
union all select 5 from generate_series(1,55)
union all select 6 from generate_series(1,66)
union all select 7 from generate_series(1,77)
union all select 8 from generate_series(1,88)
;
select count(*),count(distinct n) from DEMO;
 count | count
-------+-------
396 | 8
analyze DEMO;
ANALYZE

I’ve created rows with skewed distributions: 11 rows with value ‘1’, 22 rows with value ‘2’,… up to 88 rows with value ‘8’. And I analyzed the table so that histograms are gathered to have the query planner aware of it:

\x
Expanded display is on.
select * from pg_stats where tablename = 'demo' and attname = 'n';
-[ RECORD 1 ]----------+------------------------------------------
schemaname | public
tablename | demo
attname | n
inherited | f
null_frac | 0
avg_width | 4
n_distinct | 8
most_common_vals | {8,7,6,5,4,3,2,1}
most_common_freqs | {0.2222,0.1944,0.1667,0.1389,0.1111,0.08333,0.05556,0.02778}
histogram_bounds |
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
\x
Expanded display is off.

No surprises. I have 396 rows, then the 88 rows with value ‘8’ have a density of 88/396=0.222 and so on.

I prepare my statement:

prepare myselect (int) as select count(*) from DEMO where n=$1;
PREPARE

And run this statement several times with different values, the goal being to look at the query planner estimation for the number of rows, for each execution:

postgres=# explain execute myselect(1);
QUERY PLAN
-----------------------------------------------------------
Aggregate (cost=6.98..6.99 rows=1 width=8)
-> Seq Scan on demo (cost=0.00..6.95 rows=11 width=0)
Filter: (n = 1)
postgres=# explain execute myselect(2);
QUERY PLAN
-----------------------------------------------------------
Aggregate (cost=7.00..7.01 rows=1 width=8)
-> Seq Scan on demo (cost=0.00..6.95 rows=22 width=0)
Filter: (n = 2)
postgres=# explain execute myselect(3);
QUERY PLAN
-----------------------------------------------------------
Aggregate (cost=7.03..7.04 rows=1 width=8)
-> Seq Scan on demo (cost=0.00..6.95 rows=33 width=0)
Filter: (n = 3)
postgres=# explain execute myselect(4);
QUERY PLAN
-----------------------------------------------------------
Aggregate (cost=7.06..7.07 rows=1 width=8)
-> Seq Scan on demo (cost=0.00..6.95 rows=44 width=0)
Filter: (n = 4)
postgres=# explain execute myselect(5);
QUERY PLAN
-----------------------------------------------------------
Aggregate (cost=7.09..7.10 rows=1 width=8)
-> Seq Scan on demo (cost=0.00..6.95 rows=55 width=0)
Filter: (n = 5)
postgres=# explain execute myselect(6);
QUERY PLAN
-----------------------------------------------------------
Aggregate (cost=7.08..7.08 rows=1 width=8)
-> Seq Scan on demo (cost=0.00..6.95 rows=50 width=0)
Filter: (n = $1)
postgres=# explain execute myselect(7);
QUERY PLAN
-----------------------------------------------------------
Aggregate (cost=7.08..7.08 rows=1 width=8)
-> Seq Scan on demo (cost=0.00..6.95 rows=50 width=0)
Filter: (n = $1)
postgres=# explain execute myselect(8);
QUERY PLAN
-----------------------------------------------------------
Aggregate (cost=7.08..7.08 rows=1 width=8)
-> Seq Scan on demo (cost=0.00..6.95 rows=50 width=0)
Filter: (n = $1)

As documented, the first 5 executions used a custom plan, getting the estimations from the parameter value. The estimation is exact (11,22,33,44,55) because I have frequency histograms for those values. But starting with the 6th execution, a generic plan was used to avoid to parse at each execution. This plan does not look at the parameter values (we see “$1” in the filter predicate) and does not re-use any of the previous plans. It is estimated with an average selectivity as if there were no histograms. Here the estimation (rows=50) comes from the average number of rows:

select count(n),count(distinct n),
count(n)::numeric/count(distinct n) "avg rows per value" from DEMO;
 count | count | avg rows per value
-------+-------+---------------------
396 | 8 | 49.5000000000000000

Cost decision for generic plan vs. custom plan

The documentation mentions that the cost of the generic plan is compared to the average of the custom plans.

https://doxygen.postgresql.org/plancache_8c.html

This is to avoid that a generic plan is more expensive, as it would be better to spend additional parse time for a custom plan. Besides the documentation, this is “arbitrary” 5 executions and the average custom cost are mentioned in plancache.c

In a previous tweet to explain this, I’ve put an example (3rd screenshot) for this cost decision to keep custom plans:

Here is another example with a partitioned table. Partition pruning can occur at parse time or execution time. But when the partition is not known at parse time, the query planner estimates the cost and cardinality without partition pruning. Here is the full example:

Postgres 12 Beta | db fiddle

set plan_cache_mode=auto

The query planner continues with the custom plan even after the 5th execution because the cost of the generic plan, estimated on the whole table, is higher than the custom plan, even including the parse time.

Forcing generic plan

In order to show the cost of the generic plan, I’ve run it on PostgreSQL 12 where we can control the custom vs. generic choice with PLAN_CACHE_MODE. The default is AUTO (the behavior described above) but we can also set it to FORCE_CUSTOM_PLAN or FORCE_GENERIC_PLAN.

Here, I have forced the generic plan:

Postgres 12 Beta | db fiddle

set plan_cache_mode=force_generic_plan

The filter is parameterized, but only DEMO7 has been read. The “Subplans Removed” is the execution-time partition pruning. However, the cost and cardinality did no estimations for this: (rows=396) is the total number of rows. And for this reason, the AUTO plan cache mode did not choose it because it is much higher than the average cost of the custom plans encountered before. I would have preferred that the query planner estimates an average cost for partition pruning rather than ignoring it for the generic plan cost estimation, because it knows, from the table definition, that only a single partition needs to be read, even if it doesn’t know which one yet.

Forcing custom plan

Back to the non-partitioned table from the first example in this post, where the generic plan was chosen after the 5th execution, I force the custom plan:

Postgres 12 Beta | db fiddle

show plan_cache_mode
set plan_cache_mode=force_custom_plan

This forces the custom plan even after the 5th execution.

Plan cache modes and Oracle equivalents

In PostgreSQL 12 which is still in beta, but will be released soon for production, we have now three choices:

  • Use a custom plan, like when using literals, but with the advantages of parameters (to avoid SQL injection for example). Perfect for ad-hoc queries. There is no easy equivalent in Oracle (I would love a hint to say: do not share this and find the best plan for these values).
  • Use a generic plan, for high throughput performance (avoid to parse each execution) and stability (the plan will be always the same). This is the equivalent of the Oracle “ _optim_peek_user_binds”=false setting that is used often in old OLTP applications, or like when no histograms are gathered.
  • The “auto” method (the only one before version 12) where a prepared statement starts with a custom plan but may switch to a generic one after several executions. Oracle has no equivalent for prepared statements which always use the plan determined by the first execution values.

Sharing execution plans across sessions, or not?

While there, I add a few thoughts about cursor sharing between sessions (although this post was about prepared statements within the same session).

  • Most commercial databases I know (Oracle, DB2, SQL Server) have a shared statement cache to avoid that one session re-parses a SQL statement already parsed by another session recently. Like C or Java programs that are compiled once, and then the binary code is executed.
  • And most open source databases I know (PostgreSQL, MySQL) do not share the execution plans across the sessions. Like Perl or Python, that are compiled when loaded, each time they are executed.

Both approaches have pros and cons:

  • More memory space and latch management issues when shared
  • More CPU time spent in parsing when all cursors are private

I think that there’s no bad or good approach, but it is related to their nature:

  • Commercial databases have many optimizer transformations which increase the parsing time, in order to get acceptable response time even for very bad designed software where SQL cannot be changed (because it is a commercial application). Vendors have to please the customer who says “I cannot change the application code”. Hard parses must be rare to keep this scalable. And people care about the CPU consumption as it is the most common metric for licensing
  • Open Source databases do not go to all those transformations. The answer to bad application design is to re-write the bad queries. That’s something an Open Source community can say, especially when the application itself is homemade or open source. There’s no commercial constraint to workaround this in the database, and parse time can be kept low. And on current hardware, there’s no problem to use more CPU when it avoids having to serialize the access to shared memory structures.

With my Oracle background, I tend to think that non-shared execution plans can be a limit for PostgreSQL evolution. We will see how the parse time will increase when dealing with complex joins, partitioning and parallel queries. But not sharing also solves many problems: my Oracle memories is full of fragmented shared pools, volatile data and bind peeking plan instabilities…

I’ll be happy to get feedback on that on Twitter:

Franck Pachot's picture

Oracle & Postgres JDBC Fetch Size

TL;DR — By default PostgreSQL ResultSet is not a server cursor but a client buffer. The whole result is fetched before the first next() call.

It is usually a good idea to start with default settings rather than trying to ‘tune’ any possible parameter. Defaults are often suited to the most common cases. However, there’s a big exception to it when using JDBC drivers: the default fetch size is probably not the right setting. Here is an example with the same Java code to show the fetching behavior in Oracle and PostgreSQL.

Java example

Here is my Java code which is exactly the same for Oracle and PostgresSQL

import java.io.*;
import java.sql.*;
import java.util.*;
import java.time.*;
import oracle.jdbc.*;
public class JDBC {
public static void println(String text){
System.out.println(Instant.now().toString() +":" + text);
}
public static void main(String[] args)
throws SQLException,InterruptedException {
try (Connection c = (Connection) DriverManager.getConnection(
args[2],args[0],args[1])
// url, user, password
) {
c.setAutoCommit(false);
try (PreparedStatement s = c.prepareStatement(
"select n,mytime(n) t from demo"
)
) {
//s.setFetchSize(5);
Thread.sleep(1000);
println(" PRS "+s);
try ( ResultSet rs = s.executeQuery() ) {
Thread.sleep(1000);
println(" EXE "+rs);
while ( rs.next()) {
Thread.sleep(1000);
println(" FCH "+rs.getLong("N")+" "+rs.getTimestamp("T"));
}
System.out.println("fetch size: "+rs.getFetchSize());
}
}
}
}
}

This is very simple. The user, password and JDBC url are passed as arguments. The DriverManager finds the right driver for it (ojdbc8.jar and postgresql-jdbc.jar are in my CLASSPATH).

I prepare a simple select which I execute and fetch the rows. The query calls a “mytime()” stored function that returns the execution time timestamp, and waits one second for each call. The goal is to simulate a long query and show when the rows are actually read and when they are fetched.

Oracle JDBC Fetch Size

In order to run it on Oracle I create the following table and function:

alter session set current_schema=demo;
create or replace function mytime(n number) return timestamp as
begin
dbms_session.sleep(1);
return current_timestamp;
end;
/
show errors
create table demo (n int);
insert into demo
select to_number(column_value) N from xmltable('1000 to 1020');
commit;

The function is non-deterministic, and then it is executed for each row.

$ORACLE_HOME/jdk/bin/java JDBC "demo" "demo" "jdbc:oracle:thin:@(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB1))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))"

Here is the result. Each fetch line shows the display timestamp first, and then the timestamp from the MYTIME() function which is the read time.

The Java program waits 1 second after each call: prepared at 18:33:10, executed at 18:33:21 — that’s a 10 second execution time. Then I fetched one row every second from 13:33:22 to 18:33:53:

With Oracle, the default JDBC fetch size is 10 rows, and the EXECUTE call also does the first fetch. Then here is the timing:

  • at 18:33:10 the prepareStatement is a very fast PARSE call as the actual optimization is deferred to the first execution.
  • at 18:33:22 the EXECUTE and first FETCH of the cursor occurs. Here it takes 10 seconds to get the first 10 rows. Those rows were read between 18:33:12 and 18:33:21, then fetched, then displayed between 18:33:22 and 18:33:31.
  • Then, the second FETCH call has read 10 more rows from 18:33:32 to 18:33:40 and displayed them from 18:33:42 to 18:33:51
  • Then one row remain: read, fetched and displayed.

PostgreSQL default JDBC Fetch Size

Now I’ll run the same on a PostgreSQL database. In PostgreSQL, current_timestamp is consistent with the query time, then I use clock_timestamp() to get the actual read time.

\connect demo postgres
create table demo(n int);
insert into demo
select generate_series from generate_series(1000,1020);
create function mytime(n int) returns timestamp as
$$
begin
perform pg_sleep(1);
return clock_timestamp();
end;
$$
language plpgsql;

Here is the run with the same Java code:

\! $ORACLE_HOME/jdk/bin/java JDBC "postgres" "demo" "jdbc:postgresql://localhost:5432/demo"

We don’t see any read-fetch pattern here and we have a clue with the fetch size that is set to zero by default. Rows were read from 18:36:07 to 18:36:27 and that matches with the executeQuery() time. All rows were read, and buffered, before displaying the first one. That’s the default behavior: the JDBC ResultSet is not a cursor but a buffer.

PostgreSQL JDBC set Fetch Size

In the Java code above, there’s a setFetchSize() in comments.

Here, I remove the comment to explicitly set the fetch size to 5 (for the demo, in real life you rather set 100 or 1000 to avoid too many roundtrips and context switches).

try (PreparedStatement s = c.prepareStatement(
"select n,mytime(n) t from demo"
)) {
s.setFetchSize(5);

Here is the execution:

$ORACLE_HOME/jdk/bin/java JDBC "postgres" "demo" "jdbc:postgresql://localhost:5432/demo"

There, we can see 5 rows read from 19:13:56 to 19:14:00 during the executeQuery() first FETCH, displayed from 19:14:02 to 19:14:06 and then again the same with 5 rows…

In summary

The behavior of the application can be really different.

The Oracle default has several advantages:

However, it has also some drawbacks:

  • we can get an ORA-1555 if we read slowly and the MVCC retention has expired
  • we waste time in roundtrip and context switch latencies if we forget to increase the fetch size
  • we can get an execution error after having already processed some rows

Note that if there is a blocking operation, like an ORDER BY that has to read the whole rows before returning the first one, the whole result is read and buffered on the server anyway.

PostgreSQL default seems to be more intuitive for today’s developers who are more used to lists than cursors. But this may change with the trend of streaming. The good thing is that you can choose. And anyway, in both case, the default (all rows for PostgreSQL, 10 rows for Oracle) is probably something to change in many cases.

Note that I’ve set AutoCommit to false, and this is required for the PostgreSQL cursor behavior when setting a non-zero fetch size, or it still buffers the whole rows. Anyway, that’s probably the worst JDBC default: never leave Auto Commit to true.

To prevent automated spam submissions leave this field empty.