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.
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';In plsh, the functions would be similar, for example:
create or replace function do_shell (text) returns text as ' return ` $1 `; ' language 'plsh';
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.
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 -eThis dumps thistable in database1 as SQL and pipes it to psql which executes the commands, echoing the queries as it goes.
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.
select count(*) from mytableis s l o w. But sometimes you still need the count. As Tom Lane said, "If this were an easy problem, count() would do it for you ;-)". Here are two approaches you could take to have a faster count, depending on how you would use the number.
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;The functions simply increment or decrement the table's counter when each trigger is fired for each row. The tests show both single row inserts and multiple row inserts as well as multiple row deletes. The counter is accurate in all of these cases.
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.
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';where text is your comment and object is one of the following:
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_nameA second invocation of COMMENT on the same object will replace the comment.
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';
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009