Обсуждение: Joining a result set from four (4) tables
Hi, Can anyone help me with the following? I am setting up a series of permissions of my own making in pgSQL 8.1.4. I have the following tables; resource -- a list of available resources actions -- the actions available to the user policies -- the actions that are allowed to be performed on individual resources permissions -- matches users with granted actions on any resource users -- no surprises here I have read the docs about joins but cannot get my head around the correct syntax. The following SQL returns all actions for every resource SELECT permission.serial_id, resource.name, actions.name, actions.classification, actions.display_group, FROM permission, policies, resource, actions WHERE permission.user_id = '11' AND permission.related_id = policies.serial_id AND policies.status = 'Active' AND permission.status= 'Active'AND actions.status = 'Active'AND resource.status = 'Active' I need a list of permissions back for each resource that a user is authorised to access (when they login to their GUI). I also need to check (at user login) if every record in the chain (e.g. resource, action, policy and permission) is "Active" before the permission record is considered valid. The list for a resource called 'Scenarios' would look something like: 11900;"Scenarios";"Publish";"Action";"B" 11900;"Scenarios";"Authorise";"Action";"B" 11900;"Scenarios";"Create";"Action";"C" 11900;"Scenarios";"Update";"Action";"C" I am guessing it should be an inner join? but by reference book does not show joins on this many tables. Thanks in advance for any help. Regards John T
On 7/31/06, John Tregea <john@debraneys.com> wrote:
Can you include the table create statements with primary and foreign keys? That would help a lot.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Hi,
Can anyone help me with the following?
I am setting up a series of permissions of my own making in pgSQL 8.1.4.
I have the following tables;
resource -- a list of available resources
actions -- the actions available to the user
policies -- the actions that are allowed to be performed on individual
resources
permissions -- matches users with granted actions on any resource
users -- no surprises here
I have read the docs about joins but cannot get my head around the
correct syntax. The following SQL returns all actions for every resource
SELECT
permission.serial_id,
resource.name,
actions.name ,
actions.classification,
actions.display_group,
FROM
permission, policies, resource, actions
WHERE
permission.user_id = '11' AND
permission.related_id = policies.serial_id AND
policies.status = 'Active' AND
permission.status = 'Active'AND
actions.status = 'Active'AND
resource.status = 'Active'
I need a list of permissions back for each resource that a user is
authorised to access (when they login to their GUI).
I also need to check (at user login) if every record in the chain (e.g.
resource, action, policy and permission) is "Active" before the
permission record is considered valid.
The list for a resource called 'Scenarios' would look something like:
11900;"Scenarios";"Publish";"Action";"B"
11900;"Scenarios";"Authorise";"Action";"B"
11900;"Scenarios";"Create";"Action";"C"
11900;"Scenarios";"Update";"Action";"C"
I am guessing it should be an inner join? but by reference book does not
show joins on this many tables.
Thanks in advance for any help.
Can you include the table create statements with primary and foreign keys? That would help a lot.
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Hi aaron, Here are the 'create table' statements. I have indicated what are the primary and foreign keys with trailing comments. Thanks John Aaron Bono wrote: > Can you include the table create statements with primary and foreign > keys? That would help a lot. CREATE TABLE resources ( serial_id numeric NOT NULL, -- << Primary Key related_id numeric, -- << Foreign Key host_id int4, created timestamptz DEFAULTnow(), modified timestamptz, valid_from timestamp, valid_to timestamp, schema_name varchar(32), grid_loc varchar(32),name varchar(32), status varchar(16), description text, comments text, sort_order int2, user_id int4 DEFAULT0, located text, classification varchar(32), sequence_id int4, ) CREATE TABLE actions ( serial_id numeric NOT NULL, -- primary key related_id numeric, -- foreign key on resources.serial_id host_id int4, createdtimestamptz DEFAULT now(), modified timestamptz, valid_from timestamp, valid_to timestamp, name varchar(32), statusvarchar(16) DEFAULT 'Active'::character varying, description text, comments text, sort_order int2 DEFAULT 0, user_idint4 DEFAULT 0, -- User_ID of the creator located text, classification varchar(32), sequence_id int4, in_box varchar(32),display_group varchar(2), ) CREATE TABLE policies ( serial_id numeric NOT NULL, -- primary key related_id numeric, -- foreign key on actions.serial_id resource_id numeric,-- foreign key on resources.serial_id owner_id numeric, authority_id int4, created timestamptz DEFAULT now(), modifiedtimestamptz, valid_from timestamp, valid_to timestamp, status varchar(16) DEFAULT 'Active'::character varying, descriptiontext, comments text, classification varchar(32), user_id int4, sequence_id int4, inheritance text, ) CREATE TABLE permissions ( serial_id numeric NOT NULL, -- primary key related_id numeric, -- foreign key on policies.serial_id user_id int4, -- foreignkey on users.serial_id owner_id int4, authority_id int4, resource_id int4, created timestamptz DEFAULT now(), modifiedtimestamptz, valid_from timestamp, valid_to timestamp, name varchar(32), acronym varchar(6), status varchar(16) DEFAULT'Active'::character varying, inheritance text, description text, comments text, sort_order int2, user_id int4 DEFAULT0, located text, classification varchar(32), sequence_id int4, ) CREATE TABLE users ( serial_id numeric NOT NULL, -- primary key created timestamptz DEFAULT now(), modified timestamptz, valid_from timestamp,valid_to timestamp, name varchar(64) NOT NULL, acronym varchar(6), status varchar(16), inheritance text, descriptiontext NOT NULL, comments text NOT NULL, sort_order int2 NOT NULL, clearance varchar(32) NOT NULL, administratorbool DEFAULT false, user_id int4 DEFAULT 0, next_serial_id int4 DEFAULT 1, classification varchar(32), )
Anybody have a clever way to quickly find whether there are any unused indexes in a PG DB? One way I have done is to take queries from the DB log, prepend an explain to each and grep the results, but I am wondering if there are either any index usage stats maintained somewhere inside Postgres or if there is a slicker/less cumbersome way of doing it. Also indexes used by functions are hard to simulate that way. George
check out pg_stat_user_indexes, you will need to turn on the stats collection in your postgresql.conf file first. Jim ---------- Original Message ----------- From: "George Pavlov" <gpavlov@mynewplace.com> To: <pgsql-sql@postgresql.org> Sent: Tue, 1 Aug 2006 09:05:34 -0700 Subject: [SQL] finding unused indexes? > Anybody have a clever way to quickly find whether there are any unused > indexes in a PG DB? One way I have done is to take queries from the DB > log, prepend an explain to each and grep the results, but I am wondering > if there are either any index usage stats maintained somewhere inside > Postgres or if there is a slicker/less cumbersome way of doing it. Also > indexes used by functions are hard to simulate that way. > > George > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend ------- End of Original Message -------
On Tue, 2006-08-01 at 09:05 -0700, George Pavlov wrote: > Anybody have a clever way to quickly find whether there are any unused > indexes in a PG DB? One way I have done is to take queries from the DB > log, prepend an explain to each and grep the results, but I am wondering > if there are either any index usage stats maintained somewhere inside > Postgres or if there is a slicker/less cumbersome way of doing it. Also > indexes used by functions are hard to simulate that way. > Check out pg_stat_user_indexes, pg_stat_sys_indexes and pg_statio_all_indexes You can very clearly see the index usage there. You might have to mess with the statistics collector section in the postgresql.conf file in order to collect the information. Brad.
On 7/31/06, John Tregea <john@debraneys.com> wrote:
First of all, I must say it is very unfortunate that all the tables have the same name for their primary key column. It made things confusing at first when reading through the tables (the foreign key names don't match the primary keys they map to). It also makes it more likely you will do a join improperly.
Worse, your foriegn key names are very ambiguous. The name related_id says nothing about what table it maps to. That means you need documentation or the foreign key definitions (are you using foreign key constraints?) to tell what is going on.
Anyway... on to solving your problem.
SELECT
permission.serial_id,
resource.name,
actions.name,
actions.classification,
actions.display_group
FROM permission
INNER JOIN policies ON (
policies.serial_id = permission.related_id
)
INNER JOIN actions ON (
actions.serial_id = policies.related_id
)
INNER JOIN resource ON (
-- This is tricky as policies maps to resources AND actions maps to resources
-- so the real question is which one do you do? I did both.
policies.resource_id = resources.serial_id
AND
actions.related_id = resources.serial_id
)
WHERE
permission.user_id = '11' AND
policies.status = 'Active' AND
permission.status = 'Active'AND
actions.status = 'Active'AND
resource.status = 'Active'
;
I always discourage listing more than one table in the FROM clause. Use INNER and OUTER JOINs - it is much easier to debug and it is somewhat self documenting. That way, when you or another developer look at this in the future, you understand right away how the tables are being put together.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Hi aaron,
Here are the 'create table' statements. I have indicated what are the
primary and foreign keys with trailing comments.
Thanks
John
Aaron Bono wrote:
> Can you include the table create statements with primary and foreign
> keys? That would help a lot.
CREATE TABLE resources
(
serial_id numeric NOT NULL, -- << Primary Key
related_id numeric, -- << Foreign Key
host_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
schema_name varchar(32),
grid_loc varchar(32),
name varchar(32),
status varchar(16),
description text,
comments text,
sort_order int2,
user_id int4 DEFAULT 0,
located text,
classification varchar(32),
sequence_id int4,
)
CREATE TABLE actions
(
serial_id numeric NOT NULL, -- primary key
related_id numeric, -- foreign key on resources.serial_id
host_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
name varchar(32),
status varchar(16) DEFAULT 'Active'::character varying,
description text,
comments text,
sort_order int2 DEFAULT 0,
user_id int4 DEFAULT 0, -- User_ID of the creator
located text,
classification varchar(32),
sequence_id int4,
in_box varchar(32),
display_group varchar(2),
)
CREATE TABLE policies
(
serial_id numeric NOT NULL, -- primary key
related_id numeric, -- foreign key on actions.serial_id
resource_id numeric, -- foreign key on resources.serial_id
owner_id numeric,
authority_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
status varchar(16) DEFAULT 'Active'::character varying,
description text,
comments text,
classification varchar(32),
user_id int4,
sequence_id int4,
inheritance text,
)
CREATE TABLE permissions
(
serial_id numeric NOT NULL, -- primary key
related_id numeric, -- foreign key on policies.serial_id
user_id int4, -- foreign key on users.serial_id
owner_id int4,
authority_id int4,
resource_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
name varchar(32),
acronym varchar(6),
status varchar(16) DEFAULT 'Active'::character varying,
inheritance text,
description text,
comments text,
sort_order int2,
user_id int4 DEFAULT 0,
located text,
classification varchar(32),
sequence_id int4,
)
CREATE TABLE users
(
serial_id numeric NOT NULL, -- primary key
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
name varchar(64) NOT NULL,
acronym varchar(6),
status varchar(16),
inheritance text,
description text NOT NULL,
comments text NOT NULL,
sort_order int2 NOT NULL,
clearance varchar(32) NOT NULL,
administrator bool DEFAULT false,
user_id int4 DEFAULT 0,
next_serial_id int4 DEFAULT 1,
classification varchar(32),
)
First of all, I must say it is very unfortunate that all the tables have the same name for their primary key column. It made things confusing at first when reading through the tables (the foreign key names don't match the primary keys they map to). It also makes it more likely you will do a join improperly.
Worse, your foriegn key names are very ambiguous. The name related_id says nothing about what table it maps to. That means you need documentation or the foreign key definitions (are you using foreign key constraints?) to tell what is going on.
Anyway... on to solving your problem.
SELECT
permission.serial_id,
resource.name,
actions.name,
actions.classification,
actions.display_group
FROM permission
INNER JOIN policies ON (
policies.serial_id = permission.related_id
)
INNER JOIN actions ON (
actions.serial_id = policies.related_id
)
INNER JOIN resource ON (
-- This is tricky as policies maps to resources AND actions maps to resources
-- so the real question is which one do you do? I did both.
policies.resource_id = resources.serial_id
AND
actions.related_id = resources.serial_id
)
WHERE
permission.user_id = '11' AND
policies.status = 'Active' AND
permission.status = 'Active'AND
actions.status = 'Active'AND
resource.status = 'Active'
;
I always discourage listing more than one table in the FROM clause. Use INNER and OUTER JOINs - it is much easier to debug and it is somewhat self documenting. That way, when you or another developer look at this in the future, you understand right away how the tables are being put together.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Hi Aaron, Thanks very much, I really appreciate both the solution and the advice about naming conventions. Your script worked fine and gives me an example of what to do for other situations as well. Because the logic structure of this software is in the front end application rather than the database there is a strong need to keep the naming of fields generic rather than specific, I am not using pre-defined foreign keys at all. If I was building the database with a purpose specific goal I would be doing what you say. I have found though that when I label elements at different levels of the back end for one purpose, they are less transportable in the long run. In this case the naming conventions are actually stored in another table and applied as aliases when needed. That way I can change the names and labels (for a new client or industry) while the underlying structure remains the same. I hope to increase interoperability in this way as well. Thanks again for your help. Kind regards John T. Aaron Bono wrote: > > First of all, I must say it is very unfortunate that all the tables > have the same name for their primary key column. It made things > confusing at first when reading through the tables (the foreign key > names don't match the primary keys they map to). It also makes it > more likely you will do a join improperly. > > Worse, your foriegn key names are very ambiguous. The name related_id > says nothing about what table it maps to. That means you need > documentation or the foreign key definitions (are you using foreign > key constraints?) to tell what is going on. > > Anyway... on to solving your problem. > > SELECT > permission.serial_id, > resource.name <http://resource.name/>, > actions.name <http://actions.name/>, > actions.classification, > actions.display_group > FROM permission > INNER JOIN policies ON ( > policies.serial_id = permission.related_id > ) > INNER JOIN actions ON ( > actions.serial_id = policies.related_id > ) > INNER JOIN resource ON ( > -- This is tricky as policies maps to resources AND actions maps to > resources > -- so the real question is which one do you do? I did both. > policies.resource_id = resources.serial_id > AND > actions.related_id = resources.serial_id > ) > WHERE > permission.user_id = '11' AND > policies.status = 'Active' AND > permission.status = 'Active'AND > actions.status = 'Active'AND > resource.status = 'Active' > ; > > I always discourage listing more than one table in the FROM clause. > Use INNER and OUTER JOINs - it is much easier to debug and it is > somewhat self documenting. That way, when you or another developer > look at this in the future, you understand right away how the tables > are being put together. > > ================================================================== > Aaron Bono > Aranya Software Technologies, Inc. > http://www.aranya.com > ==================================================================
On 8/1/06, John Tregea <john@debraneys.com> wrote:
We all find ourselves in different situations and because of that, what works for one person, doesn't work for another - so I understand. Good luck with the application and future queries. Maybe you can use the existing structure of your application to help create a query builder so you can have it write a lot of your joins for you. That should help avoid a lot of simply typos or mix-ups when hand writing your queries.
I am glad the queries worked for you.
-Aaron
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Because the logic structure of this software is in the front end
application rather than the database there is a strong need to keep the
naming of fields generic rather than specific, I am not using
pre-defined foreign keys at all. If I was building the database with a
purpose specific goal I would be doing what you say. I have found though
that when I label elements at different levels of the back end for one
purpose, they are less transportable in the long run. In this case the
naming conventions are actually stored in another table and applied as
aliases when needed. That way I can change the names and labels (for a
new client or industry) while the underlying structure remains the same.
I hope to increase interoperability in this way as well.
I am glad the queries worked for you.
-Aaron
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Hi Aaron, The query builder to generate joins that you mentioned is exactly what I am going to do doing this time. That is one of the reasons I have the generic field names for the primary and foreign key fields. Then I only have to pass the search criteria the table names and which direction I want to do the joins. Thanks again for your help. Regards John T Aaron Bono wrote: > We all find ourselves in different situations and because of that, > what works for one person, doesn't work for another - so I > understand. Good luck with the application and future queries. Maybe > you can use the existing structure of your application to help create > a query builder so you can have it write a lot of your joins for you. > That should help avoid a lot of simply typos or mix-ups when hand > writing your queries. > > I am glad the queries worked for you. > > -Aaron
resurrecting an old thread: so is it safe to say that an index that has pg_stat_user_indexes.idx_scan, pg_stat_user_indexes.idx_tup_read, and pg_stat_user_indexes.idx_tup_fetch all equal to 0 has not been used (since stats have been reset)? i have a bunch of those and all of them have pg_statio_user_indexes.idx_blks_read > 0 and most of those have pg_statio_user_indexes.idx_blks_hit > 0. when/why would that happen? i guess i don't entirely understand those two values so an explanation would be very welcome (maybe an example of when each of the five values gets incremented. thanks! george > -----Original Message----- > From: Brad Nicholson [mailto:bnichols@ca.afilias.info] > Sent: Tuesday, August 01, 2006 9:12 AM > To: George Pavlov > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] finding unused indexes? > > On Tue, 2006-08-01 at 09:05 -0700, George Pavlov wrote: > > Anybody have a clever way to quickly find whether there are > any unused > > indexes in a PG DB? One way I have done is to take queries > from the DB > > log, prepend an explain to each and grep the results, but I > am wondering > > if there are either any index usage stats maintained > somewhere inside > > Postgres or if there is a slicker/less cumbersome way of > doing it. Also > > indexes used by functions are hard to simulate that way. > > > Check out pg_stat_user_indexes, pg_stat_sys_indexes and > pg_statio_all_indexes > > You can very clearly see the index usage there. You might > have to mess > with the statistics collector section in the postgresql.conf file in > order to collect the information. > > Brad.
"George Pavlov" <gpavlov@mynewplace.com> writes: > so is it safe to say that an index that has > pg_stat_user_indexes.idx_scan, pg_stat_user_indexes.idx_tup_read, and > pg_stat_user_indexes.idx_tup_fetch all equal to 0 has not been used > (since stats have been reset)? > i have a bunch of those and all of them have > pg_statio_user_indexes.idx_blks_read > 0 and most of those have > pg_statio_user_indexes.idx_blks_hit > 0. when/why would that happen? You have stats_block_level turned on, but not stats_tuple_level? You have a lot of searches that find no rows? regards, tom lane
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Wednesday, October 10, 2007 6:36 AM > > "George Pavlov" <gpavlov@mynewplace.com> writes: > > so is it safe to say that an index that has > > pg_stat_user_indexes.idx_scan, > > pg_stat_user_indexes.idx_tup_read, and > > pg_stat_user_indexes.idx_tup_fetch all equal to 0 has not been used > > (since stats have been reset)? just want to make sure this question gets answered. i want to be certain that there are no uses of the index that do not get reflected in one of these three stats. > > i have a bunch of those and all of them have > > pg_statio_user_indexes.idx_blks_read > 0 and most of those have > > pg_statio_user_indexes.idx_blks_hit > 0. when/why would that happen? > > You have stats_block_level turned on, but not stats_tuple_level? i did confirm that both settings are on and anyway i have many indexes with non-0 tuple-level stats so i am collecting them.i was curious about why some indexes would have 0s in the tup_read/tup_fetch/scan stats, but still have positive blks_read/hit numbers? and it ties to my main question of how to identify indexes that are unused/candidates for removal. > You have a lot of searches that find no rows? wouldn't a search be reflected in the idx_scan number though? in this particular case it would be *all* searches in these indexes finding no rows? (tup_read/tup_fetch/scan are all 0, but blks_read/hit are both > 0.) george