General Bits is a column loosely based on the PostgreSQL mailing list
pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see
www.PostgreSQL.org.

Twenty issues...

Editorial

07-Apr-2003

This is PostgreSQL General Bits issue number 20! There have been
120 items covering about that many threads in pgsql-general.
Those threads contained about 642 messages. But those numbers
do not count all of the messages read, tests run and
research done.

I'd like to thank the active contributors to pgsql-general as
well as the question askers for providing interesting and useful
topics and information to write about. I'd also like to thank the
people who have given me excellent feedback and suggestions.

With this issue, I would like to ask two things of you. First,
feedback is invaluable to me. There are some questions in
issue #10 if you are at a loss for what to
say, but all
feedback and comments help ensure the content of General Bits
is useful. Secondly, I recently added a
way for you to contribute to this small open source project
via voluntary subscriptions. This is a good
time to act on that and help insure that I have the time to continue
this column for another twenty issues.

PostgreSQL is so generous about formatting incoming dates that it
creates a bit of a hazard. Mistyped dates are rearranged until they
make "sense" when ever possible. In some cases, however, it is beneficial
for people alternating between mm-dd-yy and dd-mm-yy format.

The DATESTYLE option is used primarily for output. It is used on input
only when the interpretation could be ambiguous--but then it is not always
used. The DATESTYLE can be seen by using SHOW DATESTYLE; in psql.
The DATESTYLE used in the examples below
is ISO with US (NonEuropean) conventions

Here PostgreSQL assumes that 13 cannot possibly be a month, therefore,
it must be the day whether mm.dd.yy or dd.mm.yy is used as the
input format.

Trying another approach, using to_timestamp doesn't help. But it is
more fanciful in its interpretation. In this case, because month
is 13, then it is assume to mean (I am guessing) January of the
following year. Yikes!

Consistency is the key. If dates are to be input by people, it
is important to hint to them what format is expected. This
does not prevent typographical errors, but it helps.
The international format DD-Mon-YYYY, with or without your
favorite punctuation is a very good format to use because
it is easy to read and unambiguous.

However, to be absolutely sure about crucial dates, validate them
on the client side. This is lousy, but true.

A new host was added to pgsql.com with the ip of 64.117.224.149. If
you use an IP based whitelist filter to get the pgsql mailing lists,
you will want to add this IP to the whitelist.

An IP based spam filter is a program monitoring incoming email. If there
is a whitelist associated with it, those IPs on the whitelist are allowed.
Often a spam filter has both a whitelist (accept) and a blacklist (block).

Both plpgsql and plpython functions can be trigger functions.
The return values are a different for the
plpython functions in keeping with the plpython language.
This item describes plpgsql behavior only.

As of 7.3 trigger functions need to be created to return
type trigger. The previously defined return value,
opaque, has been deprecated.

The information value returned from plpgsql triggers is row data and an
indication of the disposition of the row. Even if NULL is returned,
as is legal in certain situations, it indicates (non-)data
and a disposition.

If the function is not returning NULL,
it is usually returning the row structure that was
made available for the function via variables NEW
and OLD. INSERTS only have a NEW row. UPDATES
have both NEW and OLD rows. DELETES only have an OLD
row. The value returned from the function
is usually NULL or NEW, depending on what the function
is supposed to do.

A BEFORE trigger is fired BEFORE the row is acted upon--
before it is inserted, updated or deleted. But only the BEFORE
UPDATE or BEFORE INSERT trigger can modify the row being
inserted or updated. The UPDATE or INSERT can be ignored
by returning NULL instead of NEW. This does not
abort the transaction.

An AFTER trigger occurs after the insertion, update or deletion
occurs. It cannot change the data or void the action on the
particular row.

In plpgsql trigger functions the row triggering
the function is defined in SQL variables so that
the values are available to the trigger functions.
NEW is the row about to be inserted or updated.
OLD is the row as it was before the update.
This table shows which statement types have
what variables available.

Statement

Variable

INSERT

NEW

UPDATE

NEW, OLD

DELETE

OLD

These column values of these variables are
accessed using the "dot" syntax like this:

NEW.id

BEFORE triggers on INSERT and UPDATE statements
allow you to update or change column values based
on your trigger's criteria. In UPDATE triggers,
this criteria can be based on what the row looked
like before the insert started.

If the task at hand is to change or assign column
values in the row in question, you simply make the
assignment to the NEW variable. No separate update
is required.

This is an example of a plpgsql trigger function
for BEFORE UPDATE of a salary table. If salary
is the field being updated, the previous
salary is saved in a separate column, oldsalary
and the approved by field is set to NULL so the
raise can be approved by some other mechanism.

In order to add a column with a default value, you must first
ALTER TABLE to add the column and then again to add the default.
When the default is added, it will affect any rows inserted after
the ALTER TABLE. It will not affect any data in existing
rows. This behavior corresponds to the SQL standard.
If you want to update existing rows with the new default you must
do this with a separate explicit update.

Henrik Steffen suggested that a feature be added that also updates
the existing rows, setting the column to the default when it was null.
The problem he was encountering was that the subsequent update was
very slow (affecting many rows).

However, even if the ALTER TABLE
performed the update, it would be doing it exactly the same way
one would do it separately. The speed of the query would not be
decreased. In fact it would probably cause a larger blockage because
ALTER TABLE requires an exclusive lock. This is usually not a
problem because the ALTER does not affect the data. But if
the update of rows were included in the operation, then it would
entail holding the exclusive lock much longer than would be
necessary by an ordinary ALTER followed by an update.

The implicit update of data is a little questionable. And the
fact that this behavior would not help the speed problem and
would not adhere to the SQL standard showed that this is not
really a feature to pursue.

Note that the syntax to accept ALTER TABLE ADD COLUMN ... SET DEFAULT
was available briefly in 7.1. It did not affect the row data.
It either split it into the two separate ALTER statements or it
"lost" the DEFAULT definition according to Stephan and Tom.
The syntax was removed in 7.2 because it did not correspond to the
SQL standards. Apparently DB2 does enable this syntax contrary
to the SQL standard and updates the rows in a fairly timely manner.