[SQL] Foreign key - Indexing & Deadlocking.
Hi, I'm porting some Oracle software to PostgreSQL, & have a question about best practices with foreign keys. In the Oracle environment, you're basically obliged to index all foreign keys (more specifically foreign key columns should appear on the leading edge of an index) due to the way that it manages locks. Failure to index foreign keys in this way in Oracle often causes deadlock situations. Although I could no find a mention of anything similar in the postgreSQL documentation, I just wanted to check whether or not the same was true of PostgreSQL to be on the safe side. Thanks, Andrew. _ Use Hotmail to send and receive mail from your different email accounts http://clk.atdmt.com/UKM/go/186394592/direct/01/
Re: [SQL] Using || operator to fold multiple columns into one
Craig Ringer wrote: On 24/12/2009 5:04 AM, Rosser Schwarz wrote: On Wed, Dec 23, 2009 at 1:44 AM, Craig Ringer wrote: Your invocation of COALESCE is incorrect -- it is n-ary, but it returns its first non-NULL argument. Yes. That was the point. I think we're assuming the OP wants different things. You're assuming they're trying to concatenate all fields, where more than one field in a given record may be non-null. I'm assuming that all but one field in each record will be null, and they want to show the "value" of the record - in other words, they're using the record as a sort of union type. It looks like that from the example they gave. Craig is correct in the OP attempt. All but one field is intended to be null, and the union is simply to get a more compact output at the psql prompt (without invoking "/pset format=wrapped" ). The union will be used only at the command prompt. Craig's example works with one typo fix, thanks!: select context_key,keyname,COALESCE( t_number::text, t_string::text, t_date::text, t_boolean::text) AS value from context_keyvals; This table was designed to allow arbitrary key value data, allowing postgres type operations on the data, yet still allowing clustering on the index: # (select context_key from context_keyvals where keyname='BOGUS' and t_number > 5); # SELECT contexts.context_key FROM contexts JOIN context_keyvals ON (context_keyvals.context_key=contexts.context_key) WHERE contexts.site_key = 4130 AND (context_keyvals.t_string LIKE 'T%' AND context_keyvals.keyname='SHORT_TITLE'); # \d context_keyvals; Table "public.context_keyvals" Column|Type | Modifiers -+-+--- context_key | integer | not null keyname | text| t_number| integer | t_string| text| t_boolean | boolean | t_date | timestamp without time zone | Indexes: "context_keyvals_ck" btree (context_key) CLUSTER Foreign-key constraints: "context_keyvals_context_key_fkey" FOREIGN KEY (context_key) REFERENCES contexts(context_key) ON DELETE CASCADE Duplicate rows, or data in multiple columns, would wreck havoc on the scheme. If there is a better way, I am all eyes. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Foreign key - Indexing & Deadlocking.
On 30/12/2009 6:59 AM, Andrew Hall wrote: Hi, I'm porting some Oracle software to PostgreSQL, & have a question about best practices with foreign keys. In the Oracle environment, you're basically obliged to index all foreign keys (more specifically foreign key columns should appear on the leading edge of an index) due to the way that it manages locks. Failure to index foreign keys in this way in Oracle often causes deadlock situations. Although I could no find a mention of anything similar in the postgreSQL documentation, I just wanted to check whether or not the same was true of PostgreSQL to be on the safe side. It is not the case. It's quite common in PostgreSQL to leave foreign keys without indexes. Doing so does mean that referential integrity checks being made on DELETE from the tablew with the referenced primary key will require a sequential scan of the referencing table(s) to check integrity, though. PostgreSQL isn't smart enough (yet) to group up such checks into a single pass down the target table. So if you delete 100 rows from a table in a query, and that table is referenced by another table via a foreign key without an index, the referencing table will be sequentially scanned 100 times. Needless to say, you want to add indexes to your foreign keys if you expect to delete from the parent, or (for some reason) update the primary key value for rows in the parent table. -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] DataBase Problem
On 30/12/2009 9:49 AM, Premila Devi wrote: Caused by: _java.sql.SQLException_: Couldn't perform the operation rollback: You can't perform any operations on this connection. It has been automatically closed by Proxool for some reason (see logs). "see logs" Look at your proxool logs and see why the connection was closed. The error message its self gives you the next step. I suggest reading this: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems for some hints in case you need to ask a more detailed follow-up. -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
