varlena
varlena
PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

3-Mar-2003 Issue: 15

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

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.

Time interval "casting"
[GENERAL] type casting a subselect as an interval 21-Feb-2003

Some examples of time interval data include 6 days, 2 days 14 minutes, 5 hours, 16 years . Each requires an amount of time and an indicator of what time unit it represents. The interval function takes one string argument containing information made up of one or more pairs of an amount and a unit. interval is not an ordinary cast function. Cast functions usually require only one piece of information, not two or more and in this case it can be confusing because the two bits of information must be in a single text string.

The syntax for the interval function is:

	interval [(] textstring [)];
	or
	textstring::interval
where textstring is of the form:
	ascii_number units direction [textstring...]
units may be one of:
	second, minute, hour, day, week, month, year, decade, century, millennium
direction may empty or ago.

Construct the text string described above to create an interval data type. For example, if there is a project table containing an estimated duration in days stored as an integer, you may ask, "If we start now, what will the end date of the project be?" The duration data, an integer, must be fetched by selecting it from the project table as an interval of days and added to the current timestamp.

This is one wrong way. Subselects cannot be embedded into a string.

	SELECT current_timestamp + interval '(select no_days from projects ) days'
However, there are several right ways to do it.

Cast it in the subselect by creating the interval string there:

	SELECT current_timestamp + 
		(select (no_days::text || 'days')::interval from projects);
Another way is to rely on the flexibility of the implicit casts with multiplication. This method is more easily understood when read and is more efficient.
	SELECT current_timestamp + ( (select no_days from projects) * interval '1 day') ;
Contributors: Jean-Christian Imbeault jc at mega-bucks.co.jp, Karel Zak zakkr at zf.jcu.cz, Tom Lane tgl at sss.pgh.pa.us, Stephan Szabo sszabo at megazone23.bigpanda.com, elein at varlena.com
Accessing schema information
[GENERAL] Dealing with schema in psql utility? 20-Feb-2003

Now that schemas are working well in 7.3, there are some basic tasks that need to be handled. To find out what schemas are in the database using 7.4 you will be able to issue \dn in psql, but for now in 7.3 you can use:

	elein=# select nspname from pg_namespace;
  	nspname
	------------
 	pg_catalog
 	pg_toast
 	public
 	pg_temp_1
 	sinbin
 	foo
 	pg_temp_3
 	rr
 	ere
 	rugby
Ignore the schemas pg_toast and pg_temp_n. These are internally used schemas. In my test database, there are several schemas besides public and pg_catalog: foo, rr, ere, and rugby.

To alter a schema name with the ALTER statement you will also have to wait until 7.4. In the meanwhile you can issue this statement as superuser (postgres):

	UPDATE pg_namespace SET nspname='newfoo' WHERE nspname='foo';
Remember, however, that system catalog changes, even simple ones like this can go wrong easily. Be careful.

To list the tables in a schema, the simplest is to use the schema to qualify the name:

elein=# \dt rr.*
           List of relations
 Schema |     Name     | Type  | Owner
--------+--------------+-------+-------
 rr     | foo          | table | elein
 rr     | rr_appgroups | table | elein
 rr     | rr_prompts   | table | elein
 rr     | rr_reports   | table | elein
 rr     | rr_rprompts  | table | elein
 rr     | rr_types     | table | elein
(6 rows)
To select the contents of a table just qualify the table name with the prefix of the schema:
	select * from rr.foo; 

To access certain schemas by default, use the SQL variable search_path. By default, search_path is set to the schema with the user's name and public because it is assumed a user's default schema, like their default database name, will be their user name. You can reset search_path for your SQL session or you can put it into your .psqlrc file. For example, if I wanted to only see the schemas rr and rugby, I would use:

	SET search_path TO rr, rugby; 
Notice, however, that if the two schemas have tables of the same name, the table from the leftmost schema will be used. Once search_path is set, then you can do an unqualified \d to get a list of tables:
	elein=# \d
	               List of relations
	 Schema |        Name        |   Type   | Owner
	--------+--------------------+----------+-------
	 rr     | foo                | table    | elein
	 rr     | report_report      | view     | elein
	 rr     | rr_appgroups       | table    | elein
	 rr     | rr_prompts         | table    | elein
	 rr     | rr_reports         | table    | elein
	 rr     | rr_rprompts        | table    | elein
	 rr     | rr_types           | table    | elein
	 rugby  | areas              | table    | elein
	 rugby  | events             | table    | elein
	 rugby  | teams              | table    | elein
	 rugby  | teams_v            | view     | elein
	 rugby  | tmatches           | table    | elein
	 rugby  | tmatches_matid_seq | sequence | elein
	 rugby  | ttypes             | table    | elein
	 rugby  | videos             | table    | elein
	 rugby  | videos_vidid_seq   | sequence | elein
	(16 rows)
	

Contributors: Dan Delaney ddelaney at powercreative.com, Tariq Muhammad tmuhamma at libertyrms.com, Tom Lane tgl at sss.pgh.pa.us, elein at varlena.com
7.4 hopes
7.4 24-Feb-2003

There have been some questions regarding the status of 7.4 in order for people to plan upgrades. The general concensus seems to be that if you have not yet upgraded to 7.3, you should do so in order to facilitate conversion to 7.4.

The three key features that may be in 7.4 are:

  • Point in time recovery
  • Standard Replication
  • Native Windows Port
The feature freeze is targeted for April to be followed by a beta and ideally a release in September. These are very ambitious estimates. Just in case you missed that: These are very ambitious estimates. It is possible that one, two or all of these features may not make it to 7.4.

The ToDo list (as of 18-Feb-2003) targets the following features for 7.4:

* -Add GUC variables to control floating number output digits 
* -Make a transaction-safe TRUNCATE 
* -Add ALTER TABLE tab SET WITHOUT OIDS 
* -Allow CLUSTER to cluster all tables 
* -MOVE 0 should not move to end of cursor 
* -Make PL/PgSQL %TYPE schema-aware
* -Add schema, cast, and conversion backslash commands to psql 
* -Allow pg_dump to dump a specific schema 
* -Support statement-level triggers 
* -Add hash for evaluating GROUP BY aggregates 
* -Make IN/NOT IN have similar performance to EXISTS/NOT EXISTS 
* -Inline simple SQL functions to avoid overhead 
* -Get faster regex() code 
* -Add OpenBSD's getpeereid() call for local socket authentication

The full ToDo list is always available at http://developer.postgresql.org/todo.php. Bruce Momjian does a great job keeping it up to date.

Contributors: Dmitry Tkach dmitry at openratings.com, Dennis Gearon gearond at cvc.net, Bruno Wolff III bruno at wolff.to, Ericson Smith eric at did-it.com, Robert Treat xzilla at users.sourceforge.net, Joe Tomcat tomcat at mobile.mp, Ed L. pgsql at bluepolka.net, Neil Conway neilc at samurai.com, Peter Eisentraut peter_e at gmx.net, Andrew Sullivan andrew at libertyrms.info, Bruce Momjian pgman at candle.pha.pa.us
Filesystems for PostgreSQL
[GENERAL] What filesystem? 21-Feb-2003

This is what some folks had to say on the merits of running PostgreSQL on EXT2, EXT3, JFS, XFS, ReiserFS filesystems.

ext2:

  • ext2 is really not crash-safe. I think I'd think twice
  • EXT2 is the slowest but very very stable.
  • I'd be quite surprised if ext2 was any slower than ext3.
  • For large smaller directory structures, ext2 is pretty good, but fades as your fs grows.
EXT3:
  • EXT3 is good as long as you are running 2.4.20 + the source EXT3 patches.
reiserfs:
  • There have been some recent reports about corruption under reiserfs, but not much detail on it.
  • ReiserFS is good,
  • Since most PostgreSQL disk I/O involves large files, I wouldn't think ReiserFS would outperform ext2 either but it does ~30-60% on a single IDE drive. Apparently tree indexes in reiser helps it a lot. This is especially true in file systems with lots of small to medium files.
xfs:
  • bulletproof on IRIX
  • JFS and XFS are the most thoroughly tested.
  • As for XFS on linux, I'd guess it's probably pretty good, Demo on linux a few years ago was truly amazing.
Contributors: Steve Crawford scrawford at pinpointresearch.com, Andrew Sullivan andrew at libertyrms.info, Joshua Drake jd at commandprompt.com, Neil Conway neilc at samurai.com, Shridhar Daithankar shridhar_daithankar at persistent.co.in, scott.marlowe scott.marlowe at ihs.com
Securing your server properly
Hint by way of Christopher Kings-Lynne 23-Feb-2003

1. Edit $PGDATA/pg_hba.conf and change ALL authentication types to 'md5'. This means that not even the pgsql user (default postgres) running locally can access the database without a password.

2. However, now your vacuumdb cronjob and your backups won't work without a password and you cannot even start up postgres without a password.

3. The solution is this:

Put a .pgpass file in the pgsql user's (postgres') home directory.
In it put the proper values for each colon separated field:
host:port:database:username:password
Then chmod 600 the file. (Don't forget this step.)

Contributors: Christopher Kings-Lynne chriskl at familyhealth.com.au
Indexing Toast
[GENERAL] In 7.3.1, will I be able to reindex toast? 14-Feb-2003

In release 7.3 you can indeed reindex the toast tables directly. To do so use the following, where the schema is pg_toast and the table name is pg_toast_nnnnn.

	REINDEX TABLE pg_toast.pg_toast_840608; 
The table will be locked exclusively for the reindexing.

There was also some discussion about the reindexing of tables within the pg_catalog schema. The key issue about changing those tables is that a few of them are usually fully in the system cache and would need to be cleared from the cache in order to get the benefits of the reindexing.

Contributors: Jeffrey Baker jwbaker at acm.org, Bruce Momjian pgman at candle.pha.pa.us, Mario Weilguni mweilguni at sime.com, Tom Lane tgl at sss.pgh.pa.us, Hiroshi Inoue Inoue at tpf.co.jp
pgsql-announce and PostgreSQL Weekly News
[ANNOUNCE] PostgreSQL Weekly News 03-Mar-2003

I have recommended before that it is fairly important to subscribe to pgsql-announce in order to stay on top of events affecting PostgreSQL. But there is an added bonus for subscribing--you will also receive Robert Treat's PostgreSQL Weekly News which covers weekly HACKER progress as well as other timely events. It evens reminds you to read PostgreSQL General Bits :-) What a deal!

To subscribe to pgsql-announce or other pgsql mailing lists, go to the PostgreSQL Mailing List Subscription Page.

Editor: elein at varlena.com


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

Top
Google
Search General Bits & varlena.com Search WWW