The question is how to create a unique index on two columns in a table where one of the columns, a boolean, should be TRUE. To create a simple two column unique index you would use:

	CREATE UNIQUE INDEX utab ON table(col1, col2);

You can add the condition in one of two ways. The first way creates the unique index only on rows where the second column is TRUE. The second way creates the unique index on the first column, regardless, but includes the second column only if it is TRUE.

	CREATE UNIQUE INDEX utab ON table(col1, col2) WHERE param2 IS TRUE;
	CREATE UNIQUE INDEX utab ON table(param1, (NULLIF(param2, FALSE)))

The NULLIF(value1, value2) returns NULL if both values are equal.

Contributors: tmpmac at mac.com Richard Huxton dev at archonet.com, Alban Hertroys alban at magproductions.nl, Scott Marlowe smarlowe at g2switchworks.com, Stuart Bishop stuart at stuartbishop.net

Show Previous Row Values in plperl Previous Values 17-Jun-2005

A long while ago, we addressed running counters and aggregates in plpythonu. This example shows how to use plperl to bring values from the previous row into the current row. This enables you to do simple tasks like time difference between rows and deltas between rows.

The technique to save data between rows in plperl uses the connection global hash %_SHARED. Data stored in this has stays around from definition to the close of the connection. This enables storing the previous row's data between selects.

The scope of the hash %_SHARED also causes problems if you call the perl function more than once during the connection. This example uses the brute force method of resetting the hash before use. This issue also prevents using this function more than once in any SQL statement.

Suppose we have a table like this:

 event  |         event_time
--------+----------------------------
 start  | 2005-06-17 17:53:57.17567
 reload | 2005-06-17 18:06:27.248593
 stop   | 2005-06-17 18:14:04.296951
 start  | 2005-06-17 18:18:39.345686

Then a function is created which can return the last value.

	create or replace function prev_time(event_time timestamp)
	returns timestamp AS
	$$
	   my $in_time = $_[0];
	   my $out_time = '0 seconds';
	   if ( defined $_SHARED{'event_time'} )
	      {
	         $out_time = $_SHARED{'event_time'} ;
	      }
	   else
	      {
	         $out_time = undef;
	      }
	   $_SHARED{'event_time'} = $in_time;

	   return $out_time;

	$$ language 'plperl';

	select event, event_time, prev_time(event_time) from events;

Before you call the function again, you need to reset the SHARED hash. This function will do it.

	create or replace function reset_event_times()
	returns void AS
	$$
	   $_SHARED{'event_time'} = undef;
	$$
	language 'plperl';

	select reset_event_times();
	select event, event_time, event_time - prev_time(event_time) from events;
	 event  |         event_time         |    ?column?
	--------+----------------------------+-----------------
	 start  | 2005-06-17 18:29:11.878583 |
	 reload | 2005-06-17 18:41:41.912348 | 00:12:30.033765
	 stop   | 2005-06-17 18:49:18.962434 | 00:07:37.050086
	 start  | 2005-06-17 18:53:53.976351 | 00:04:35.013917

Contributors: elein@varlena.com

Altering Dependent Row types Chickens and Eggs and Row types 20-Jun-2005

Let me start this article by saying that the following example of a logging system is not recommended specifically because of the difficulty and dependencies it creates. My preferred method is described in Issue #66 in plpython. That one uses a flat table paralleling the main table.

But this example is different. We've got a transaction table that we want to over audit. Whenever we make a change we want to save the old values and the new values into an audit table. We chose to compose the audit tables with new and old ROW values.

select * from transactions;
 xact_id |  account   | amount | crdb |   descr
---------+------------+--------+------+-----------
       2 | ABC123DEF0 |  25.45 | CR   | seed data
       4 | ZZYZYVA674 |  45.69 | DB   | seed data
       1 | ABC123DEF0 |  10.15 | DB   | seed data
       3 | ZZYZYVA674 |  99.78 | CR   | seed data
elect * from audit_transactions;
           atime            |            old_xact                   |            new_xact 
----------------------------+---------------------------------------+---------------------------------------
 2005-06-20 20:30:18.516902 | (,,,,)                                | (1,"ABC123DEF0",10.15,CR,"seed data")
 2005-06-20 20:30:18.636161 | (,,,,)                                | (2,"ABC123DEF0",25.45,CR,"seed data")
 2005-06-20 20:30:18.724672 | (,,,,)                                | (3,"ZZYZYVA674",13.72,CR,"seed data")
 2005-06-20 20:30:18.811641 | (,,,,)                                | (4,"ZZYZYVA674",45.69,DB,"seed data")
 2005-06-20 20:30:18.898525 | (1,"ABC123DEF0",10.15,CR,"seed data") | (1,"ABC123DEF0",10.15,DB,"seed data")

This is the trigger function and trigger that created the audit table. Notice that we check TG_OP to find out if it is an INSERT or an UPDATE trigger. Also notice that the row data is enclosed using ROW().

	--Triggers populate the audit table
	CREATE OR REPLACE FUNCTION updins_xact()
	RETURNS TRIGGER AS
	$$
	BEGIN
	   IF TG_OP = 'INSERT' THEN
	      INSERT INTO audit_transactions (atime, new_xact) values (now(),
	         ROW(NEW.xact_id, NEW.account, NEW.amount, NEW.crdb, NEW.descr));
	   ELSE
	      INSERT INTO audit_transactions (atime, new_xact, old_xact) values (now(),
	         ROW(NEW.xact_id, NEW.account, NEW.amount, NEW.crdb, NEW.descr),
	         ROW(OLD.xact_id, OLD.account, OLD.amount, OLD.crdb, OLD.descr));
	   END IF;
	   RETURN NEW;
	END;
	$$ LANGUAGE 'plpgsql';
	
	CREATE TRIGGER updins_xact BEFORE INSERT OR UPDATE  ON transactions
	FOR EACH ROW EXECUTE PROCEDURE updins_xact();

Now the problem that came up is that the account number field was declared as char(10) and it really needs to be char(17). Uh oh. Well, we will just alter the transaction table, right?

alter table transactions
   alter column account TYPE char(17);
ERROR:  cannot alter table "transactions" because column "audit_transactions"."new_xact" uses its row type

PostgreSQL knows how to ALTER a table instance of a type of the same name, but it does not know how to cascade the changes throughout all uses of that type. I hope this will be remedied at some time, but it could be argued that it may best be left for the db developer to remedy. The developer remedy is what I will show next.

We need to save the existing audit data somewhere somehow that doesn't depend on transactions. Then we can drop the audit table, alter the transaction table and then try to put the audit table back. And recreate the trigger function. The transaction type is dependent on

• transaction table -- instance of the transaction type
• audit table 2 columns -- 2 instances of the transaction type
• trigger function -- pre-compiled reference to transaction type.
That's the goal.

Saving the audit data off can be done in several ways. We are going to store it in a table in the same format as the original table except that the row types will be a row type that is equivalent but not the same as the transactions type. An alternative way is to just flatten the row data and distinguish between old and new by column names. Note: I strongly suggest doing the rest of this in a block transaction and make good use of rollback for typos, etc. The syntax with the parentheses can be a little trying.

	BEGIN;
	create type temp_xact as (
	   xact_id  integer,
	   account  char(10),
	   amount   numeric,
	   crdb     char(2),
	   descr    text
	);
	create table temp_audit as
	   select atime, ROW((old_xact).xact_id,(old_xact).account,(old_xact).amount,
	      (old_xact).crdb, (old_xact).descr)::temp_xact as old_xact,
	    ROW((new_xact).xact_id,(new_xact).account,(new_xact).amount, (new_xact).crdb,
	      (new_xact).descr)::temp_xact as new_xact
	   from audit_transactions;

Notice the references to the attributes of the column row types are enclosed in parentheses and the whole list is enclosed as a ROW() and cast to the new type name. Try a select to make sure you have what you think you should have.

Now, still within the block transaction, we can drop the original alter table and alter the transaction table which will implicitly alter the transactions type. While we are at it, lets create the new audit table.

drop table audit_transactions;
alter table transactions
   alter column account TYPE char(17);

create table audit_transactions (
   atime timestamp,
   old_xact transactions,
   new_xact transactions
);

Next we must populate the audit_transactions table with the data from the temp_audit table. This is tricky. It isn't possible (AFAIK) to just assign the row types to each other. It was necessary to lay them out as follows:

insert into audit_transactions
   select atime,
   ROW( (old_xact).xact_id, (old_xact).account, (old_xact).amount,
      (old_xact).crdb, (old_xact).descr)::transactions,
   ROW( (new_xact).xact_id, (new_xact).account, (new_xact).amount,
      (new_xact).crdb, (new_xact).descr)::transactions
from temp_audit;

Notice the syntax is the same as we used to save the data.

Next we have to recreate the trigger function. We need to do this because the pre-compiled function referenced the old audit_transactions table and we need it to reference the new one. If we did not do this then we would get a runtime error about a OID not found in relations.

If all went well, you should be able to commit. Select and check the data.

The final test is to update a row in the transactions table and see its result in the audit table.

One issue not yet addressed is the timing of these changes. You may have problems if there are updates to the transactions table during the block transaction when you are altering data. If you can halt these inserts and updates that would be best. In the worst case you audit will be missing some rows that were inserted or updated. For this reason, make the execution of your block transaction which changes things to be as quick and correct as possible. Practice it with a rollback first and when it is all correct, run it as a script with commit.

Contributors: david at fetter.org, elein at varlena.com