Re: [SQL] Best way to "and" from a one-to-many joined table?
I guess it depends on the optimiser and how clever it is. With the former the db will probably generate 2 sets of ids for the 2 joined tables (a, b) which only contain the values you require, these lists are probably much smaller than the total number of rows in the table therefore any merges and sorts on them have to operate on less rows and will be quicker. With the latter query it has to fetch all the rows regardless of the attribute and then do the restriction at the end, which results in more rows, bigger merges and sorts and takes longer... Obviously postgres may be clever enough to realise what you want and rearrange the query internally to a more efficient form. Generally to find out what it is doing stick "EXPLAIN (ANALYZE)" in front. This will show you the steps the db is taking to perform the query and in what order. If you include ANAYLZE then the db actually does the query (throwing away the results) and gives you accurate values, etc otherwise it shows you estimated values based on the various stats collected for the table. SELECT person_name FROM test_people p JOIN test_attributes a ON ((a.people_id = p.people_id) AND (a."attribute" = @firstAttr)) JOIN test_attributes b ON ((b."people_id" = p."people_id") AND (b."attribute" = @secondAttr)); Hi, I saw a few people post answers to this question and it raised another related question for me. What are the differences between the above query and this one. Are they semantically/functionally identical but might differ in performance? Or would they be optimized down to an identical query? Or am I misreading them and they are actually different? SELECT person_name FROM test_people p JOIN test_attributes a ON ((a.people_id = p.people_id) JOIN test_attributes b ON ((b."people_id" = p."people_id") WHERE (a."attribute" = @firstAttr)) AND (b."attribute" = @secondAttr)); Also, any suggestions about how to figure out this on my own without bugging the list in the future would be great. Thanks for any insight! Steve p.s. I posting in the same thread, but if you think I should have started a new thread let me know for the future. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Best way to restrict detail rows?
I have a "master-detail" kind of situation, as illustrated here: CREATE TABLE master( id SERIAL PRIMARY KEY, foo TEXT ); CREATE TABLE detail( id SERIAL PRIMARY KEY master BIGINT NOT NULL REFERENCES master(id), bar TEXT ); (this is a simplification, of course) I would like a way to restrict the addition of new detail records, but only after the initial detail records have been inserted into the system. Ideally, I'd like to start a transaction, enter the master record and its detail records, then commit the transaction, which would then lock these records so no more detail records can ever be added. As a concrete example, you might think of the master record as an Order, while the details are Line Items for an order. When initially creating the Order, I'd like to add as many Line Items as necessary, but once the Order is entered, I don't want anybody to be able to add more Line Items. Is there a way to do this straightforwardly? It seems like I might be able to use a BEFORE INSERT ROW trigger on the detail table to see if there are already any detail records for the master row. If there are no detail records, then I can go ahead and add. If there are detail records, then maybe I could use a pl/perl hash to carry information around as to whether or not those records were added in the current transaction, and then decide whether to insert based on that. That seems rather cumbersome and baroque, though. Thanks in advance for any suggestions, Chris -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Best way to restrict detail rows?
On Mon, Dec 8, 2008 at 1:56 PM, Christopher Maier <[EMAIL PROTECTED]> wrote: > I have a "master-detail" kind of situation, as illustrated here: > > CREATE TABLE master( >id SERIAL PRIMARY KEY, >foo TEXT > ); > > CREATE TABLE detail( >id SERIAL PRIMARY KEY >master BIGINT NOT NULL REFERENCES master(id), >bar TEXT > ); > > (this is a simplification, of course) > > I would like a way to restrict the addition of new detail records, but only > after the initial detail records have been inserted into the system. After you create the table do something like this: create rule detail_no_insert as on insert to detail do nothing; create rule detail_no_update as on update to detail do nothing; poof. no more updates or inserts work. Note that copy will still work, as it doesn't fire rules. So, you can update the data with copy, and otherwise not touch it. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Best way to restrict detail rows?
On Mon, Dec 8, 2008 at 2:28 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Mon, Dec 8, 2008 at 1:56 PM, Christopher Maier <[EMAIL PROTECTED]> wrote: >> I have a "master-detail" kind of situation, as illustrated here: >> >> CREATE TABLE master( >>id SERIAL PRIMARY KEY, >>foo TEXT >> ); >> >> CREATE TABLE detail( >>id SERIAL PRIMARY KEY >>master BIGINT NOT NULL REFERENCES master(id), >>bar TEXT >> ); >> >> (this is a simplification, of course) >> >> I would like a way to restrict the addition of new detail records, but only >> after the initial detail records have been inserted into the system. > > After you create the table do something like this: > > create rule detail_no_insert as on insert to detail do nothing; > create rule detail_no_update as on update to detail do nothing; > > poof. no more updates or inserts work. Note that copy will still > work, as it doesn't fire rules. So, you can update the data with > copy, and otherwise not touch it. > whoops! do INSTEAD nothing. -- When fascism comes to America, it will be draped in a flag and carrying a cross - Sinclair Lewis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Best way to restrict detail rows?
On Mon, Dec 8, 2008 at 1:28 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Mon, Dec 8, 2008 at 1:56 PM, Christopher Maier <[EMAIL PROTECTED]> wrote: >> I have a "master-detail" kind of situation, as illustrated here: >> >> CREATE TABLE master( >>id SERIAL PRIMARY KEY, >>foo TEXT >> ); >> >> CREATE TABLE detail( >>id SERIAL PRIMARY KEY >>master BIGINT NOT NULL REFERENCES master(id), >>bar TEXT >> ); >> >> (this is a simplification, of course) >> >> I would like a way to restrict the addition of new detail records, but only >> after the initial detail records have been inserted into the system. > > After you create the table do something like this: > > create rule detail_no_insert as on insert to detail do nothing; > create rule detail_no_update as on update to detail do nothing; > > poof. no more updates or inserts work. Note that copy will still > work, as it doesn't fire rules. So, you can update the data with > copy, and otherwise not touch it. One Idea that popped into my head that may-or-may-not work would be to add a constraint trigger that checks if all of the detail records have the same xmin as the order table record. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Best way to restrict detail rows?
On Mon, Dec 8, 2008 at 2:31 PM, Richard Broersma <[EMAIL PROTECTED]> wrote: > One Idea that popped into my head that may-or-may-not work would be to > add a constraint trigger that checks if all of the detail records have > the same xmin as the order table record. Yes, it's not as simple as I first thought when I read it. I'd look at using a udf that used a sec definer that only it had to do the row adds and do everything at once, inserting to both tables at the time of the creation of the order. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
