Thread: Foreign key deadlocks
> Hi all, quick question related to some scalability problems I have been seeing: do foreign key references acquire writelocks on the row they are referencing during an insert, and are these locks held until the end of the transaction? > > Thanks, > > Shawn >
On Sun, Sep 26, 2004 at 08:55:46PM -0400, Shawn Chisholm wrote: > Hi all, quick question related to some scalability problems I > have been seeing: do foreign key references acquire write locks on > the row they are referencing during an insert, and are these locks > held until the end of the transaction? This was recently brought up in pgsql-general -- see the original message and my followup: http://archives.postgresql.org/pgsql-general/2004-09/msg00405.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Dear Group, I am creating a temporary table from an existing table and I get the following error. I havent defined any relationship between these two tables. Althought I got the result what i wanted but I did not understand the NOTICE that compiler gives. => select DISTINCT *,*,*,*,*,*,*,* INTO temp_table FROM <existing table1> where * = table2.table_2_colName; NOTICE: adding missing FROM-clause entry for table "probe_set" Could any one please help me to understand this. Thank you. Kumar __________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail
Dear Group, I have a large mamoth table with many duplicated rows except one. Means, out of 10 columns 8 columns data is identical and 10th one is different (not for every row but for ~ 50 K rows 13 times). The 9th column is the unique data column. I parsed out the unique IDs and made it into a table (seq_ID table , number ~ 186K rows). Using SELECT DISTINCT I creted a temporary table by givin g a WHERE clause : WHERE mamoth.table.seq_id = small.table_seq_id; I expected to see the unique_IDs padded with rest of column data from mamoth table. However, even after using DISTINCT method I get duplicated entries with different primary_ids (generated using SERIAL attribute) E.g. Please look at the result. Key_ID Unique_Seq_id 8793 1000_at 132738 1000_at 8794 1001_at 132739 1001_at 8795 1002_f_at 132740 1002_f_at 8796 1003_s_at 132741 1003_s_at 8797 1004_at 132742 1004_at 8798 1005_at I made this mamoth table from 13 huge files into mamoth table by COPY command. A cursorial check shows me that the data from two tables is giving raise to duplicated entries. The rest columns data is identical (to say that they differ even in spelling mistakes). Can any one please, please help me. I am stuck with my project :-( Thank you very much. Kumar __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Mon, Sep 27, 2004 at 06:51:14PM -0700, Kumar S wrote: > I am creating a temporary table from an existing table > and I get the following error. I havent defined any > relationship between these two tables. Althought I got > the result what i wanted but I did not understand the > NOTICE that compiler gives. > > => select DISTINCT *,*,*,*,*,*,*,* INTO temp_table > FROM <existing table1> where * = > table2.table_2_colName; It would be helpful to see the actual SQL statements you're running instead of a simplified example such as the above, which forces the reader to make assumptions that might not be true. But in this case I think we get the idea. > NOTICE: adding missing FROM-clause entry for table > "probe_set" You've referenced the table probe_set (presumably what the example refers to as table2) but you didn't put it in the FROM clause, so PostgreSQL added it for you. Some people prefer this to raise an error instead of a warning, so they turn off the add_missing_from configuration parameter. http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-COMPATIBLE -- Michael Fuhr http://www.fuhr.org/~mfuhr/