Re: [GENERAL] Timestamp vs timestamptz
But watch out! This is mentioned in the docs but it bit me when I used timestamp with time zone so: timestamp with time zone does not record the timezone you inserted it with- it simply stores the GMT version and converts to whatever timezone you like on demand. If the timezone is important data, you will need an extra column for it. For example, flight arrival information should probably include the timezone of the destination. -M On Jul 13, 2006, at 8:04 PM, David Fetter wrote: On Thu, Jul 13, 2006 at 04:35:20PM -0700, Antimon wrote: Hi, I'm working on a web project with pgsql, i did use mysql before and stored epoch in database so i'm not familiar with these datatypes. What i wanna ask is, if i don't need to display timestamps in different timezones, Not this week, but who knows about next week? shall i use timestamptz anyway? Yes. Timestamptz is the one. :) ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Server Crash using plPHP or PL/Perl
Are you certain that it is the trigger that is crashing the process? If that is true, then there may be a bug in plperl. To debug, you could use gdb, but try this first: Use the strict pragma. To do this in plperl (instead of plperlu), use: BEGIN { strict-import(); } or set strict mode to on in postgresql.conf [I don't understand why this isn't the default.] You will need to declare all your variables using my $var. [You are already half-way there because you declare a lot of empty strings.] You already pepper your code with elog(NOTICE,) so you can tell us how far the code gets right? You can use more elogs to hone in on the line that crashes. Are you aware that your code will be very costly to execute? On Jul 11, 2006, at 5:43 PM, Carl M. Nasal II wrote: We are writing a multi-master replication process for our Electronic Medical Records product. We have written triggers in plPHP and then in PL/Perl to keep an audit trail of the changes as well as flags so the data can be replicated. We started with plPHP, but then server started crashing, which reset all connections to the database (requiring our application to be restarted). We then tried to rewrite the code using PL/Perl, but the same problem has occurred. The code for the triggers are available at: http://medical.bmaenterprises.com/audit.plphp http://medical.bmaenterprises.com/audit.plperl We create the triggers by running the follow SQL statement for each table: CREATE TRIGGER config_audit AFTER INSERT OR UPDATE OR DELETE ON config FOR EACH ROW EXECUTE PROCEDURE audit(); Any ideas of what is causing the server to crash will be helpful. Below are the lines from the PostgreSQL serverlog file when the crash occurs: --- - LOG: server process (PID 29153) exited with exit code 255 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2006-07-11 16:01:32 EDT LOG: checkpoint record is at 1/F413F26C LOG: redo record is at 1/F413F26C; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 7628670; next OID: 693120 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 1/F413F2B0 LOG: record with zero length at 1/F4186D3C LOG: redo done at 1/F4186D14 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database postgres Thank you, Carl M. Nasal II BMA Enterprises, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Long term database archival
Will postgresql be a viable database in 20 years? Will SQL be used anywhere in 20 years? Are you sure 20 years is your ideal backup duration? Very few media even last 5 years. The good thing about open source and open standards is that regardless of the answers to those questions, there is no proprietary element to prevent you from accessing that data- simply decide what it will be and update your backups along the way. Whether such data will be relevant/ useful to anyone in 20 years is a question you have to answer yourself. Good luck. -M On Jul 6, 2006, at 2:57 PM, Karl O. Pinc wrote: Hi, What is the best pg_dump format for long-term database archival? That is, what format is most likely to be able to be restored into a future PostgreSQL cluster. Mostly, we're interested in dumps done with --data-only, and have preferred the default (-F c) format. But this form is somewhat more opaque than a plain text SQL dump, which is bound to be supported forever out of the box. Should we want to restore a 20 year old backup nobody's going to want to be messing around with decoding a custom format dump if it does not just load all by itself. Is the answer different if we're dumping the schema as well as the data? Thanks. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Long term database archival
I am not to sure of the relevance, but I periodically worked as a sub-contractor for an Oil-producing Company in California. They were carrying 35 years of data on an Alpha Server running Ca-Ingres. The really bad part is that hundreds and hundreds of reporting tables were created on top of the functioning system for reporting over the years. Now nobody know which tables are relevant and with are redundant and or deprecated. Also year after year, new custom text file reports were created with procedural scrips. The load on the server was such that the daily reporting was taking near taking 23 hours to complete. And the requests for new reports was getting the IT department very worried. But the data from 35 years ago wasn't stored in Ingres and, if it's important, it won't stay in Ingres. The data shifts from format to format as technology progresses. It seemed to me that the OP wanted some format that would be readable in 20 years. No one can guarantee anything like that. -M ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] different sort order in windows and linux version
On Jul 2, 2006, at 6:13 AM, Martijn van Oosterhout wrote: But I don't think anyone is actually considering importing ICU into the postgres source tree, are they? Why not? Size - I'm not sure this is relevent since I don't think we want to incorporate it into postgres itself, just let people use it if they have it. In any case though, the default dataset is 8MB. This includes support for every locale and charset it knows about. If you drop the conversion stuff (because postgres already has that) you're down to about 4MB. Why would you drop the ICU transcoding support instead of the existing postgres functions? Why the duplicated effort? Well, the Japanese think that UTF8 is not the solution to all their worries, so they won't be happy with a UTF8-only solution. Likewise, those of us who only need single-byte character sets won't be very happy with being forced to accept multi-byte processing overhead. I've not quite understood the japenese problem with Unicode. My understanding is that it was primarily due to widespread use of broken converters. Certain Japanese characters cannot make a reliable round-trip through Unicode. ICU uses UTF-16 as its store, so the Japanese folks won't be happy with an ICU-only solution. However, it would still be of great benefit to allow ICU to handle as much as possible, leaving the string encodings to the encoding experts. At the very least, it would be great to have ICU to handle encoding on a per-column basis (perhaps extending the text datatype with encoding info). Perhaps this would be a decent stopgap solution? The backend protocol would also need a version bump- currently, it converts all strings to a single encoding. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Default directory for postgres user?
The shell is probably /bin/false right? That indicates that the postgres user won't log in to an active session. If that's an issue, then you should change that to whatever you like (probably /usr/local/pgsql/). On Jul 2, 2006, at 12:29 PM, Victor Escobar wrote: What should the default directory for the postgres user be? I'm using OSX 10.4. Right now, the default directory is set to /dev/null. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
Well, the Date argument against NULLs (and he never endorsed them, or so he claims) is that they are not data- they represent the absence of data- so why put non-data in a _data_base. If you are asking yourself the question how you can have support multiple meanings in a column, normalize. Then, baldness is just another value and you don't have to guess if the hair color is unknown, undefined, or missing. On Jun 8, 2006, at 10:10 PM, Christopher Browne wrote: A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Trent Shipley) wrote: On Thursday 2006-06-08 15:14, David Fetter wrote: On Thu, Jun 08, 2006 at 05:21:07AM -0700, [EMAIL PROTECTED] wrote: on bag theory[1] and 3-value logic[2]. Until they come up with a testable system, or Hell freezes over, whichever comes first, Pascal's book will make a good companion on your shelf to books on Phlogiston[3] theory, or a decent doorstop, whichever you prefer. I have encountered at least two commercial database products that declared every column NOT NULL. I have always assumed that this was defensive, preventing stupid programmer mistakes. I recall reading somewhere that Codd proposed multiple flavors of nullity. Are there theoretical proposals for databases with logical systems having more than three values? Darwen did a paper where he described how you'd cope with not having any nulls. It amounted to having a whole bunch of views that would have a whole host of special indicator values to replace the multiple meanings of NULL... -- let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];; http://linuxdatabases.info/info/lisp.html Including a destination in the CC list that will cause the recipients' mailer to blow out is a good way to stifle dissent. -- from the Symbolics Guidelines for Sending Mail ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
On Jun 8, 2006, at 9:32 PM, David Fetter wrote: On Thu, Jun 08, 2006 at 06:09:21PM -0700, Trent Shipley wrote: On Thursday 2006-06-08 15:14, David Fetter wrote: On Thu, Jun 08, 2006 at 05:21:07AM -0700, [EMAIL PROTECTED] wrote: on bag theory[1] and 3-value logic[2]. Until they come up with a testable system, or Hell freezes over, whichever comes first, Pascal's book will make a good companion on your shelf to books on Phlogiston[3] theory, or a decent doorstop, whichever you prefer. I have encountered at least two commercial database products that declared every column NOT NULL. I have always assumed that this was defensive, preventing stupid programmer mistakes. It's not that simple. If there are no NULLs allowed anywhere, that means that you can't even have them as the output of a SELECT statement, which means no OUTER JOINs. No repetitions means none anywhere, which means that they can't be the output of a query either, and makes it complicated at best to do aggregates. The whole thing is just ridiculous on its face. Yes, no repetition. The point is that all functions should return actual relations, so no ordering either. This makes it trivial to chain the operations which is painful to do in SQL because you have to pay attention to return types *and* whether or not the subquery returns one or more rows or just one value. The OUTER JOIN stuff is a limitation of SQL. In the relational model, there is no reason a column type cannot be a relation itself, however SQL can't cope well with that. Aggregates can return relations as header types so all the applicable tuples are returned. I have to do that in PostgreSQL with arrays but there is no trivial way to convert between arrays and a fake view or table or a VALUES() construct :-( ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
To balance the discussion, I would like to say that I thoroughly enjoyed Date's latest Database In Depth. It gave me a strong foundation in relational theory and I can say that I think more about my schema designs thanks to the advice in the text. Just because SQL may allow something, doesn't make it good. If you don't use NULL, then you don't come across 3-valued logic--problem solved. Some Tutorial D notions really make sense; I would love to be able to rely on every function returning a relation. Everything is OK until he starts peddling TransRelational™ software... On Jun 8, 2006, at 6:14 PM, David Fetter wrote: On Thu, Jun 08, 2006 at 05:21:07AM -0700, [EMAIL PROTECTED] wrote: I'm reading, and enjoying immensely, Fabial Pascal's book Practical Issues in Database Management. Be aware that Pascal, along with Date and Darwen, are...how do I put this gently...cranks. They've been getting more strident and irrational as the decades go by. Pascal, Date, and Darwen have been alleging for years, with increasing shrillness, that DBMSs should be based on set theory and 2-value logic. I say alleging because they have not backed up this idea with any actual software that others could test. SQL DBMSs are based on bag theory[1] and 3-value logic[2]. Until they come up with a testable system, or Hell freezes over, whichever comes first, Pascal's book will make a good companion on your shelf to books on Phlogiston[3] theory, or a decent doorstop, whichever you prefer. Cheers, D ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Let's make CPgAN!
I think the implementation of postgresql installable packages (and package-space) should precede this idea. Then, any package management system can install the packages. On May 20, 2006, at 2:12 PM, Dawid Kuroczko wrote: Comrehensive PostgreSQL Archive Network, or CPgAN ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] [CHALLENGE] return column by ordinal number
I came across a guy that wanted to get rows from a table by specifying the table name and column ordinal number and nothing more. [Yes, this is useless and violates relational model and SQL priniciples.] My initial thoughts centered on using an array to snag each row and pull out the column number I want, but I couldn't figure out how to concatenate all the columns together without specifying them individually. Then, I whipped up some plpgsql: CREATE OR REPLACE FUNCTION columnx(tablename text,columnindex integer) RETURNS SETOF RECORD AS $$ DECLARE r RECORD; colname TEXT; BEGIN SELECT INTO colname isc.column_name FROM information_schema.columns AS isc WHERE tablename LIKE table_schema || '.' || table_name AND columnindex=isc.ordinal_position; RAISE NOTICE '%',colname; FOR r IN EXECUTE 'SELECT ' || colname || ' FROM ' || tablename || ';' LOOP RETURN NEXT r; END LOOP; END; $$ LANGUAGE 'plpgsql'; But running this gets me: agentm=# select * from columnx('public.test',2); ERROR: a column definition list is required for functions returning record agentm=# select * from columnx('public.test',2) as ret(a anyelement); ERROR: column a has pseudo-type anyelement agentm=# select * from columnx('public.test',2) as ret(a text); NOTICE: b ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function columnx line 8 at return next agentm=# select * from columnx('public.test',2) as ret(a integer); NOTICE: b a --- 2 (1 row) In the function, I don't know until I get to the information schema what types I will be returning and I can't declare a variable then. Making it explicit (as I do in the last command) is cheating because I would want it to return whatever type that column is without manually figuring that out. Can this be done without resorting to an external SQL generation programr? Does anyone have a good hack to share? -M ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] sudo-like behavior
On Apr 22, 2006, at 1:13 PM, Florian G. Pflug wrote: Why don't you just use SET SESSION AUTHORIZATION somerole, and then scan the to-be-executel sql scripts for any occurence of reset session authorization, and ignore the script it matches. Of course you'd need to be a bit carefull to catch all syntactially valid variations (like ReSeT SeSsIoN AuToRiZaTiOn), but that should be doable. If you design your matched carefully, the only way to defeat that protection would be to wrap the reset session authorization command in a function, which I believe is not possible. Unfortunately, it is possible: agentm=# CREATE OR REPLACE FUNCTION testacl() RETURNS void AS $$ RESET SESSION AUTHORIZATION; $$ LANGUAGE SQL; CREATE FUNCTION agentm=# select current_user; current_user -- agentm (1 row) agentm=# set session authorization test; SET agentm= select current_user; current_user -- test (1 row) agentm= select testacl(); testacl - (1 row) agentm=# select current_user; current_user -- agentm (1 row) So, currently, there is a security limitation in postgresql which effectively prohibits switching roles midstream unless you can control the statements of that role, i.e. there is no sandbox feature available. (Such a feature would also be great for pooled connections, but that has already been discussed as well.) -M ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] sudo-like behavior
Sorry, but you misunderstand- nowhere am I interested in the role's password. My previous suggestion was to add a password to set session authorization itself so that if the authorization were to be reset, it would need to be done with that password; the password itself could be machine-generated. It it would merely allow a secure sandbox to be established between: SET SESSION AUTHORIZATION somerole WITH PASSWORD 'abc'; --arbitrary SQL run as somerole RESET SESSION AUTHORIZATION; --fails- requires password RESET SESSION AUTHORIZATION WITH PASSWORD 'pass'; --fails RESET SESSION AUTHORIZATION WITH PASSWORD 'abc'; --succeeds- we are done with this role The password ensures that the session authorization initiator is the only one that can terminate it as well. -M On Apr 20, 2006, at 10:44 PM, Tom Lane wrote: Agent M [EMAIL PROTECTED] writes: I really haven't provided enough details- my fault. What I want to accomplish is a general-purpose timer facility for postgresql. I'm not really sure why you think it'd be a good idea for such a thing to operate as an unprivileged user that gets around its lack of privilege by storing copies of everyone else's passwords. I can think of several reasonable ways to design the privilege handling for a cron-like facility, but giving it cleartext copies of everyone's passwords is not one of them. regards, tom lane ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] sudo-like behavior
I really haven't provided enough details- my fault. What I want to accomplish is a general-purpose timer facility for postgresql. Ideally, arbitrary roles provide statements to run at certain intervals. The benefit here is that the user connections can go away and only a single timer connection is maintained (waiting on notifications to update). Examples of where this could be useful: 1) simulated materialized views 2) daily tasks such as cache cleanup/refresh/updates 3) expensive tasks which run regularly Arbitrary statements could be executed on a timed basis without needing local access for crontab or persistent remote access. Anyway, here is the table: CREATE TABLE pgtimer._timer ( id SERIAL PRIMARY KEY, repeats INTEGER NOT NULL, --repeats X times as countdown lastfired TIMESTAMP, waitinterval INTERVAL, --OR specialeventid INTEGER REFERENCES pgtimer.specialevent, --various special events such as startup, autovacuum, or notifications detail TEXT, --stores notification event name if applicable statement TEXT NOT NULL, asrole TEXT NOT NULL ); A separate view with rules handles insert/update capabilities and throws a notification so that the daemon is notified to refresh its countdown to the next event. The actual statement execution is all I have left to do. I could force users to define security definer functions but then vacuuming capability is lost (autovacuum can't handle everything). If there is an architecture change I could make to rectify this, I am all ears. Thanks! -M On Apr 20, 2006, at 5:03 PM, Tom Lane wrote: A.M. [EMAIL PROTECTED] writes: On Thu, April 20, 2006 4:21 pm, Tom Lane wrote: I think the correct way to do what you want is via a SECURITY DEFINER function. Perhaps I can't wrap my head around it- I have the SQL as a string in a table. Well, the simplest thing would be create function exec(text) returns void as $$ begin execute $1; end$$ language plpgsql strict security definer; revoke execute on exec(text) from public; grant execute on exec(text) to whoever-you-trust; although personally I'd try to restrict what the function can be used for a bit more than that. If the allowed commands are in a table, you could perhaps pass the table's key to exec() and let it pull the string from the table for itself. What about commands that can't be run from within transactions? There aren't that many of those. Do you really need this for them? For that matter, do you really need this at all? Have you considered granting role membership as an alternative solution path? The SQL permissions mechanism is quite powerful as of 8.1, and if it won't do what you want, maybe you have not thought hard enough. regards, tom lane ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match