Varlena, LLC | PostgreSQL General Bits Newsletter

PostgreSQL Training,
Consulting & Support

By A. Elein Mustain

24-Nov-2002 Issue: 1

Archives |

General Tidbits |

Google General Bits |

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.

UNION and UNION ALL
[GENERAL] UNION and ORDER BY	07-Nov-2002

When a UNION is performed, the data is sorted in order to eliminate duplicate rows in keeping with the proper definition of a UNION. If you want the concatenation of pre-sorted queries you can use UNION ALL.

Contributors: Cool Screen cool_screen_name90001 at yahoo.com, Tom Lane tgl at sss.pgh.pa.us, Dmitry Tkach dmitry at openratings.com

Mailing list problems
[GENERAL] OT: mailing list delays 07-Nov-2002

The issue was raised that people were not seeing their posts on pgsql-general immediately. Marc Fournier, the moderator, said that he reviews mail from unsubscribed addresses and has hundreds and sometimes more than 1000 messages to review before they get posted. Subscribed addresses are posted automatically and all reviewed messages with PostgreSQL content get posted eventually. It was suggested that Marc recruit some help and Richard Huxton volunteered to help.

Marc pointed out that people can subscribe to pgsql-general and set nomail and continue to read the list via the newsgroup. This would enable them to send in questions without having the posts stall for review and they also would not be flooded with pgsql-general email.

Mike Mascari and the rest of us thank Marc for doing this filtering.

Richard Huxton dev at archonet.com, Roj Niyogi niyogi at pghoster.com, Marc G. Fournier scrappy at hub.org, Tom Lane tgl at sss.pgh.pa.us, Robert Treat xzilla at users.sourceforge.net, Bruce Momjian pgman at candle.pha.pa.us, Mike Mascari mascarm at mascari.com

Inheritance and indexes
[GENERAL] Inheritance: problems with primary key 07-Nov-2002

Sub tables which inherit from a super table do not inherit primary keys or UNIQUE constraints. The reason for this is that the indexes on a table hierarchy are unique for each table. This is a feature, not a bug, but other people may disagree. The indexes enforce the primary keys and the UNIQUE constraint. There is a workaround if you want the table hierarchy to share a primary key. This was suggested to me earlier by Oliver Elphick olly at lfix.co.uk.

Create a separate table for the keys with the key table columns set as the primary key.

Put the key field as a primary key into the base table.

When inserting a new record into the sub table you need to first insert the keys in the key table and then into the target table. This can be done in a before trigger function on each target table. Note that you cannot define foreign keys to maintain this integrity because both records are inserted in the same transaction via the trigger and therefore are not visible to each other.

Camila Rocha camilarrocha at hotmail.com Alvaro Herrera alvherre at atentus.com elein at varlena.com

Changing NAMEDATALEN
[GENERAL] Changing max size of attribute names. 01-Nov-2002

To change the maximum size of an identifier in a PostgreSQL installation you must change the value of NAMEDATALEN and recompile the server. A database built with a server with a custom NAMEDATALEN value may not be used by other servers that were not compiled with the same NAMEDATALEN.

Prior to 7.3b, NAMEDATALEN is set to 32. With 7.3 NAMEDATALEN is set to 64. Using 7.3 will help most people with longer and unchangeable identifier names to use the server with no customization.

Contributors: Benjamin Scherrey postgres at proteus-tech.com, Doug McNaught doug at mcnaught.org

Permissions on Functions
[GENERAL] Permissions on functions 07-Nov-2002

Question: Can you set permissions on functions as is done in Oracle or MySQL?
Answer: Yes, in 7.3 which is currently in beta.

Contributors: Emmanuel Gravel e_gravel at yahoo.com, Bruce Momjian pgman at candle.pha.pa.us

Recursion
[GENERAL] Recursive call indicator/counter 07-Nov-2002

In a plpgsql function which recurses, you can pass a depth parameter to convey the information about whether the function is being called for the first or nth time. There are no "global" variables to use to tell you the same information.

You can also write the function using plpython instead of plpgsql. In plpython there is a statement duration dictionary called "SD" which should be accessible from all calls in the same SQL statement.

Contributors: Hadley Willan hadley.willan at deeper.co.nz, Mark Wilson mark at mediasculpt.com, elein at varlena.com

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!

Top