[SQL] FOREIGN KEY and AccessExclusiveLock
Hi, all the below are for PostgreSQL 7.4.2. I noticed that during ALTER TABLE kid ADD CONSTRAINT "parcon" FOREIGN KEY (parid) on parent(id) pgsql tries to acquire an AccessExclusiveLock on *both* kid (which is pretty natural since it adheres with the docs, and it is an alter command) *and* parent. Whats the purpose of the AccessExclusiveLock on parent table? Is there a way this alter command will affect parent's data or schema? Lets see a simple example: dynacom=# CREATE TABLE parent(id int PRIMARY KEY); CREATE TABLE dynacom=# CREATE TABLE kid(parid int); CREATE TABLE dynacom=# Then in session 1: dynacom=# BEGIN ; BEGIN dynacom=# SELECT * from parent ; id (0 rows) dynacom=# In Session 2: dynacom=# BEGIN ; BEGIN dynacom=# ALTER TABLE kid ADD CONSTRAINT "parcon" FOREIGN KEY (parid) references parent(id); *here Session 2 is deadlocked* In Session 1: dynacom=# SELECT c.relname,l.mode,l.granted from pg_locks l,pg_class c where l.relation=c.oid; relname|mode | granted --+-+- kid | AccessExclusiveLock | t pg_locks | AccessShareLock | t pg_class | AccessShareLock | t parent | AccessExclusiveLock | f parent | AccessShareLock | t pg_namespace | AccessShareLock | t (6 rows) dynacom=# Again in Session 1: dynacom=# end; COMMIT dynacom=# In Session 2: ALTER TABLE dynacom=# Now imagine that session 2 is "called" by session 1, with commiting after session 2 is done, we have clearly a deadlock situation. The question is why an AccessExclusiveLock must be created for the FK table? Actually it puzzled me alot, since for me Session 1 is a java program "executing" XML in various forms, one of them being plain UNIX (exec()) commands, which in turn sometimes are psql commands. It was hard to imagine that an innocent select on the parent table in the java program and an alter table on a child table as a pgsql UNIX command would cause a deadlock situation. The natural workaround was to immediately commit in the java program after select and before UNIX command (psql) is executed. Thanx. -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Best way to know if there is a row
I have a bunch of queries in a system I'm finishing, and I bumped with a question on performace. Which is the best way to solve this: I need to know if there is at least one row in the relation that comes from a determinated query. Which is the best way to do this: (a) Build the query with "SELECT * ..." and after executing the query see if numRows()>0 (b) Build the query with "SELECT count(*) ...", fetch the row and see if count>0 I'm working with (a) because I see it better in performace, but I wanted to be sure the numRows() will actually give me the exact amount of rows (had some problems in the past with Informix). The aplication is written in PHP. -- 09:45:02 up 16 days, 3 min, 4 users, load average: 3.32, 2.69, 1.77 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Best way to know if there is a row
O Martin Marques έγραψε στις Sep 28, 2004 : > I have a bunch of queries in a system I'm finishing, and I bumped with a > question on performace. > Which is the best way to solve this: > > I need to know if there is at least one row in the relation that comes from a > determinated query. Which is the best way to do this: > > (a) Build the query with "SELECT * ..." and after executing the query see if > numRows()>0 > (b) Build the query with "SELECT count(*) ...", fetch the row and see if > count>0 You could also try (c) SELECT exists (select 1 ... limit 1) and test the boolean value in the one and only row returned, where "..." is your clause as in (a),(b). > > I'm working with (a) because I see it better in performace, but I wanted to be > sure the numRows() will actually give me the exact amount of rows (had some > problems in the past with Informix). > > The aplication is written in PHP. > > -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] CHECK col A not NULL if col B='x'
Hello, Is it possible to set up a table CHECK, which ensures that column A is NOT NULL if column B = 'x' ? -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] FOREIGN KEY and AccessExclusiveLock
Achilleus Mantzios <[EMAIL PROTECTED]> writes: > Whats the purpose of the AccessExclusiveLock on parent table? We're adding a trigger to it. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CHECK col A not NULL if col B='x'
El Mar 28 Sep 2004 11:02, T E Schmitz escribió: > Hello, > > Is it possible to set up a table CHECK, which ensures that column A is > NOT NULL if column B = 'x' ? CONSTRAINT constraint_name ] CHECK (expression) CHECK (expression) The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint may reference multiple columns. So I would say that it should be: CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL) (use a logical table to build the correct logical expression) -- 11:05:01 up 16 days, 1:23, 4 users, load average: 1.26, 0.70, 1.04 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CHECK col A not NULL if col B='x'
On Tue, Sep 28, 2004 at 03:02:02PM +0100, T E Schmitz wrote: > Hello, > > Is it possible to set up a table CHECK, which ensures that column A is > NOT NULL if column B = 'x' ? Sure. [EMAIL PROTECTED] CREATE TABLE test ( [EMAIL PROTECTED](# a integer check (case when b = 'x' then a is not null else true end), [EMAIL PROTECTED](# b text); CREATE TABLE [EMAIL PROTECTED] INSERT INTO test VALUES (null, '123'); INSERT 107538 1 [EMAIL PROTECTED] INSERT INTO test VALUES (null, 'x'); ERROR: new row for relation "test" violates check constraint "test_a" [EMAIL PROTECTED] INSERT INTO test VALUES (1, 'x'); INSERT 107539 1 -- Fduch M. Pravking ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] FOREIGN KEY and AccessExclusiveLock
O Tom Lane έγραψε στις Sep 28, 2004 : > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > Whats the purpose of the AccessExclusiveLock on parent table? > > We're adding a trigger to it. >From the docs: Acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly. Now is the lock in question created explicitly with LOCK TABLE? Since the docs dont say a thing about triggers acquiring locks. > > regards, tom lane > -- -Achilleus ---(end of broadcast)--- TIP 3: 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: [SQL] Best way to know if there is a row
Martin Marques <[EMAIL PROTECTED]> writes: > I need to know if there is at least one row in the relation that comes from a > determinated query. Which is the best way to do this: > (a) Build the query with "SELECT * ..." and after executing the query see if > numRows()>0 > (b) Build the query with "SELECT count(*) ...", fetch the row and see if > count>0 Either of these implies computing the entire query result set, which is much more computation than you want. Instead do SELECT * LIMIT 1 and then see if you got a row or not. Aside from not computing useless rows, the LIMIT will bias the optimizer in favor of fast-start plans. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] CHECK col A not NULL if col B='x'
Hola Martin! Martin Marques wrote: El Mar 28 Sep 2004 11:02, T E Schmitz escribió: Is it possible to set up a table CHECK, which ensures that column A is NOT NULL if column B = 'x' ? CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL) This is brilliant. Only detected this today. Don't know how I managed to overlook the CHECK constraints ;-) -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CHECK col A not NULL if col B='x'
Hello again, Martin Marques wrote: El Mar 28 Sep 2004 11:02, T E Schmitz escribió: Is it possible to set up a table CHECK, which ensures that column A is NOT NULL if column B = 'x' ? CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL) I noticed a table constraint can be added via ALTER TABLE. Is it correct that a column constraint cannot be added via the ALTER TABLE other than by dropping and adding the column? (I am using psql 7.4.2.) -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Doubt
On 22/09/2004 12:11 S.Sreejith wrote: Sir, I am new to PostgreSQL. I have a doubt. Now i am doing one project in .NET technology with postgres(linux) as database. I am using pgADMIN and pgManager as tools for database releted activities. Now i am able to create functions with 'sql' language. When i select 'pgsql' as language for creating query, an error poping up. How to create Query using 'pgsql' language. if any new tools need to be installed. Plz rectify. Mail me back at [EMAIL PROTECTED] I bet you haven't enabled pl/pgsql use createlang. Read the docs. -- Paul Thomas +--+---+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+---+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CHECK col A not NULL if col B='x'
T E Schmitz <[EMAIL PROTECTED]> writes: > I noticed a table constraint can be added via ALTER TABLE. Is it correct > that a column constraint cannot be added via the ALTER TABLE other than > by dropping and adding the column? (I am using psql 7.4.2.) There is no difference between table and column constraints in PG. Just write it as a table constraint in ALTER TABLE. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] FOREIGN KEY and AccessExclusiveLock
O Tom Lane έγραψε στις Sep 28, 2004 : > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > Whats the purpose of the AccessExclusiveLock on parent table? > > We're adding a trigger to it. >From the docs: Acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly. Now is the lock in question created explicitly with LOCK TABLE? Since the docs dont say a thing about triggers acquiring locks. > > regards, tom lane > -- -Achilleus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
