One of the more interesting features in 7.4 is the information schema which describes the SQL standard definitions of a PostgreSQL database. It shows the elements of the database usually found using the pg_catalog schema or the slash commands in psql.
The information schema is the implementation of the SQL Information Schema as defined in ISO 9075-2:1999 chapter 20. It was added to 7.4 by Peter Eisentraut but it did not show up on the TODO list. The information schema implements an SQL standard and so it does not reflect all parts of the PostgreSQL specific features, for example, it does not display the different languages in which functions can be written.
The schema is installed by default in 7.4 databases and is named 'information_schema'. By default the schema is not on the search path and so it must be added to the search path in psql or accessed explicitly. For example:
SET search_path TO public, information_schema; or SELECT * FROM information_schema.tables;
This table shows the names of the views created within the information_schema. Some are self-explanatory and some are obscure in keeping with standard specification standards.
Every column in all of these views is one of the following four types:
Boolean (true/false) data is represented in the information schema by a column of type character_data that contains either YES or NO. (The information schema was invented before the type boolean was added to the SQL standard, so this convention is necessary to keep the information schema backward compatible.)
The information contained in the information schema can be fast and helpful or it could be both inadequate and overwhelming. All of the information available is also available in the pg_catalog schema, however, some people may find the information_schema easier to read, particularly with the expansion of the arrays stored in the pg_catalog.
The information_schema is very useful for those building tools to manipulate the database in standard SQL ways, but it does fall short of providing PostgreSQL specific information. The slash commands in psql provide PostgreSQL specific information fairly well, however, they are not necessarily available in views. Grabbing and translating the queries used to construct the information in slash commands in not always as easy as it should be and is the subject of many questions.
As you can tell by the pluses and minuses and the ambiguity, using the standard SQL information schema will be a matter of preference as is direct access to the pg_catalogs. Perusal of the information schema is highly recommended.
The question at hand was how to create an exclusive or (XOR) expression. Writing out the CASE statement is rather tedious if more than two operands are involved.
select case when $1 then (select case when $2 then 'f' else (select case when $3 then 'f' ...and so on... endThere is a binary XOR operator. If the boolean expressions could be reduced to 1 or 0 then it could be used. This unfortunately just adds an extra layer to a rather simple problem. If you find yourself converting data types to other data types in order to use their operators, that is a hint that you may need to create an operator for the original type or understand why there isn't one already.
The fun part of this thread was Peter Child's quick creation of a new boolean operator. It illustrates how easily a new operator can be created. The function xor(bool,bool) is created and bound to the operator sequence of "~|", a relatively arbitrary choice of an operator string. Binding a function and a left and a right argument type to a string is the basic procedure to create an operator.
CREATE FUNCTION xor(bool,bool) RETURNS bool AS ' SELECT ($1 AND NOT $2) OR (NOT $1 AND $2); ' LANGUAGE 'sql'; CREATE OPERATOR ~| (PROCEDURE='xor',LEFTARG=bool,RIGHTARG=bool);
Manfred Koizar then reeled us back to reality and beginning boolean algebra by reminding us that <> (not equals) on boolean arguments gives us exactly the same functionality as the desired XOR. Oh, yeah, I knew that.
A dump of a database with many tables caused the following error:
$ pg_dumpall >full.db pg_dump: WARNING: ShmemAlloc: out of memory pg_dump: Attempt to lock table "vs_dfa554862ac" failed. ERROR: LockAcquire: lock table 1 is out of memory pg_dumpall: pg_dump failed on bandwidth, exiting
The database had 3000 tables and each table has about 1500 rows containing date, timestamp and quantities. The size of the database ran from 25Gb to 9Gb.
The recommendation was to increase max_locks_per_transaction in the postgresql.conf file. A restart of the server is required for this change. It is generally known that this particular ShmemAlloc error is caused by insufficient max_locks_per_transaction. With very little prompting, Tom Lane volunteered to duck under the 7.4 Strings freeze and add this information to the error message.
The thread continued discussing the rationale behind creating 3000 tables instead of one large indexed one. With the new information and the fix for index bloat, the reasoning will be reexamined.
There were many changes to the postgresql.conf file between 7.3 and 7.4. Although many of the changes are documented in Josh and Shridhar's Annotated Postgresql.conf it is worth examining the specific differences now, just before the 7.4 release. This information should be used to update your postgresql.conf file for 7.4.
The change to datestyle is related to the change in the date input
and the others here are the result of many long discussions on
pgsql-hackers and pgsql-performance.
Developer level flags were removed. Most of these required
build changes in order to be useful.
Name Changes Several variables
had their names changed. None of the defaults were changed.
Feature and Miscellaneous Changes
The following options were added (or removed) as feature changes
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