In order to offset some of the costs of producing PostgreSQL General Bits, I am adding a voluntary subscription option. The fees will enable me to spend the time to write this column and will support my hardware costs.

This subscription option is completely voluntary. I will continue to provide this column to the whole PostgreSQL community as I have done since November 2002.

The button to the left saying "Support General Bits" takes you to PayPal. This seems to be the least intrusive method of requesting support back from the community. Ads have always bothered me. I hope you agree. I am suggesting $1 or $2 USD per issue you have read, paid quarterly or at your convenience. However, it is completely up to you what you can afford. You know how this column has contributed toward your staying in tune with the features of PostgreSQL.

Please feel free to comment on this change directly to me.
And thank you for your support.

Editor: elein at varlena.com

Shell commands from server functions [GENERAL] Shell Commands 06-Mar-2003

You can run shell commands in server functions via untrusted procedural languages such as perlpu and also plsh. In perlpu you could do the following:

	create or replace function do_readpipe(text) returns text as '
	        my $command = shift;
	        return `$command`;
	'
	language 'plperlu';
	
	create or replace function do_shell(text) returns int as '
	        my $command = shift;
	        return system $command;
	'
	language 'plperlu';

	create or replace function do_shell (text) returns text as '
		return ` $1 `;
	' language 'plsh';

There are a couple of very important points to notice here. Anything that is executed in functions like these will be executed as the owner of the PostgreSQL process--usually postgres. Responsibility for allowing or disallowing privileges for creating functions in any procedural language belongs to the person responsible for the administration of permissions in the postgreSQL installation. Enabling anyone to write or use these types of scripts compromise the security, not just of your database but of your entire computer/network.

An untrusted language is one that does not protect itself from executing code that can lead to data or process corruption. For example, the trusted language plperl, does not allow the system call, but the untrusted language plperlu does. Most untrusted functions are VOLATILE since they rely on accessing the operating system and their results could change from call to call.

Contributors: jjAntti Haapala antti.haapala at iki.fi, Carl Olivier carl at zero-one.co.za, Dennis Gearon gearond at cvc.net, "Gregory Wood" gregw at com-stock.com

Database to Database copy table [GENERAL] copying between Postgres 17-Mar-2003

The easiest way to copy data from one table in a database to the same table in another database is:

	pg_dump database1 -t thistable | psql database2 -e 

This method requires that the tables in both database be the same, and ideally, there should be no problems with duplicate keys on the table, triggers or other constraint problems on the target database. In real life, of course, stuff happens. Capture the output of psql and you will be able to easily sort out any problems that occurred.

If the table does not yet exist in database2, you can add the -c flag to pg_dump. This will create the table as well as copy in the data. If the second database is on a different host, you just need to specify the -h host option to psql.

Contributors: javier garcia - CEBAS rn001 at cebas.csic.es, Joshua Moore-Oliva josh at chatgris.com, Andrew Sullivan andrew at libertyrms.info, scott.marlowe scott.marlowe at ihs.com

Working around the slow count(*) [GENERAL] table row count 08-Feb-2003

The query

	select count(*) from mytable 

If you wanted an approximate count, you could use the count from the last vacuum. The accuracy would depend on your vacuum frequency, your table volatility and how soon after the vacuum you ran the query.

	select reltuples from pg_class where relname = 'mytable'; 

If you wanted up to the statement accuracy, you could rig your table with insert and delete triggers to manage a running count in another table. This will cause some overhead and so the benefit must be weighed against the cost. The overhead is one counter table for each table for which you want to count rows and a trigger and function execution and an update for each insert or delete into the counted table. This is what the trigger functions might look like for the ubiquitous table foo:

	drop table foo ;
	create table foo (pkey integer, data text);
	
	drop table foocounter;
	create table foocounter (
	   rcount   bigint
	);
	insert into foocounter values (0);
	
	drop function incarow();
	create function incarow() returns TRIGGER as '
	BEGIN
	update foocounter set rcount=rcount+1;
	return NEW;
	END;
	' language 'plpgsql';
	
	drop function incarow();
	create function incarow() returns TRIGGER as '
	BEGIN
	update foocounter set rcount=rcount+1;
	return NEW;
	END;
	' language 'plpgsql';
	
	drop function decarow();
	create function decarow() returns TRIGGER as '
	BEGIN
	update foocounter set rcount=rcount-1;
	return OLD;
	END;
	' language 'plpgsql';
	
	create trigger incarow AFTER INSERT on foo
	   FOR EACH ROW EXECUTE PROCEDURE incarow();
	create trigger decarow AFTER DELETE on foo
	   FOR EACH ROW EXECUTE PROCEDURE decarow();
	
	--
	-- TEST ROW TRIGGERS
	--
	insert into foo values (1, 'one');
	insert into foo values (2, 'two');
	select * from foocounter;
	
	insert into foo values (3, 'one');
	insert into foo values (4, 'two');
	select * from foocounter;
	
	insert into foo select * from foo;
	select * from foocounter;
	
	delete from foo where data = 'two';
	select * from foocounter;
	

Contributors: Martin Hurst martinh at ix.netcom.com, Tom Lane tgl at sss.pgh.pa.us, Bruno Wolff III bruno at wolff.to, elein at varlena.com

Rules or Triggers [GENERAL] rules problem 21-Mar-2003

The decision factor for rules vs. triggers is that rules are for rewriting the query and triggers are for changes executed on rows. The timing is different. Rules are invoked before a query gets executed and may affect the statement to be executed. The rule is only invoked at the start, but the ultimate statement may affect many rows.

A trigger, on the other hand, is a reaction to a change of data. During the statement execution, if there is a trigger PER ROW, each row affected will fire off the trigger. The triggers PER STATEMENT are not yet implemented so they are not considered here.

It is a common mistake to try to use a rule to track input and delete counts, for example. A rule which adds or subtracts a counter in some table will fire once, regardless of whether the statement affects one or many rows. In the example in the item above, if rules were used instead of triggers then when several rows were deleted by a single SQL statement the count would only have been decremented by one. Likewise, when we inserted multiple rows in one statement, the row count would have been incremented by one.

A good use of rules is to enable updates, inserts and deletes for views. These rules can redirect the data into logical statements operating on the underlying tables.

Contributors: Georgi Chorbadzhiyski gf at unixsol.org, Joshua Moore-Oliva josh at chatgris.com, Doug McNaught doug at mcnaught.org elein at varlena.com

Object Comments [GENERAL] where is comments located? 10-Mar-2003

You may have noticed that some of the items displayed by the slash functions, for example \do, have comments associated with them. PostgreSQL has the built-in ability to store comments on any row in a table or any column in a table. The system catalogs usually (but not always) have pre-defined comments. You can also use the same mechanism to comment your objects in your database.

To create comments, use the SQL:

	COMMENT ON object IS 'text'; 

	  TABLE object_name
	  COLUMN table_name.column_name
	  AGGREGATE agg_name (agg_type)
	  CONSTRAINT constraint_name ON table_name
	  DATABASE object_name
	  DOMAIN object_name
	  FUNCTION func_name (arg1_type, arg2_type, ...)
	  INDEX object_name
	  OPERATOR op (leftoperand_type, rightoperand_type)
	  RULE rule_name ON table_name
	  SCHEMA object_name
	  SEQUENCE object_name
	  TRIGGER trigger_name ON table_name
	  TYPE object_name
	  VIEW object_name

Retrieving the comments is not as simple as it could be. For all comments except column comments, you must pass the object's oid and the table name in which that oid resides. For example, for a table, you must get the table's oid from pg_class and then call the function obj_description with that oid and 'pg_class'. To retrieve a function's comment you must get the function's oid from pg_proc and pass it and 'pg_proc' to obj_description. To retrieve a column's comment, you need the table's oid and the column number. The column numbers start at 1.

	obj_description(objectOID, tablename);
	col_description(tableOID, columnnumber);

Comments, we know, are very good things and should be used if possible. They should add to what the code says and not repeat it and should be helpful to anyone who reads it including yourself in 6 months. However, the difficulty using the object description functions is a strong deterrent. Also the fact that most of psql's slash commands do not display the comment is a problem.

What good is a comment if you never see it? It is an exercise for the reader to write the functions that will enable you to see comments for each object. Below are a few examples to get you started.

	create or replace function get_tablecomment(text)
	returns text as
	'
	   select obj_description(oid, ''pg_class'') from pg_class where relname=$1;
	' language 'SQL' ;
	comment on function get_tablecomment(text) is 'Get comment from table name';
	
	create or replace function get_funccomment(text, text) 
	returns text as
	'
	   select obj_description(oid,''pg_proc'') from pg_proc 
	   where proname = $1 and oidvectortypes(proargtypes) = $2;
	' language 'SQL' ;
	comment on function get_funccomment(text, text) is
	'Get comment from function name and arguments';
	
	
	create or replace function get_aggcomment(text, text) 
	returns text as
	'
	   select obj_description(oid,''pg_aggregate'') from pg_proc 
	   where proname = $1 and oidvectortypes(proargtypes) = $2;
	' language 'SQL' ;
	comment on function get_aggcomment(text, text) is
	'Get comment from aggregate name and argument';

Contributors: Johnson, Shaunn SJohnson6 at bcbsm.com, Alvaro Herrera alvherre at dcc.uchile.cl, Bruce Momjian pgman at candle.pha.pa.us, elein at varlena.com