do i need a view or procedure? - Mailing list pgsql-general
From | Fran Fabrizio |
---|---|
Subject | do i need a view or procedure? |
Date | |
Msg-id | 3B5345CB.7ACF7D54@mmrd.com Whole thread Raw |
List | pgsql-general |
Hello, I am trying to get my head around a complex problem I've been tasked with... We have a set of servers spread throughout the country (100s to 1000s of them) and I need to build a patch-distributing postgresql database. The actual patches would be distributed via rpm's which we'd like to store in the database (we pretty much have that part figured out). However, there are various server configurations and only some patches are going to be applicable to any one server. The decision process from an out-in-the-field server's point of view is something like this: - Connect to the central patch database - What patches are available? - Of these, which are intended for my OS? - Of these, which are intended for the major version of the software which I am running? - Of these, which are not already installed here? So that whittles down the number of eligible patches quite a bit. But then there's more... - Am I authorized to install patches designated for beta site or limited release? - Is this patch authorized for installation in my geographic region? - Are there any patches that are targeted uniquely to me as a site-specific patch? - I'm running version 1.2 of the software. Is this patch numbered 1.0.7 intended specifically only for 1.0 installs or is it ok for 1.x installs (we have both cases)? etc... As you can see, it becomes quite the tangled web. The database has a few tables...a host table with an entry for each field server, what their privileges are (beta site, limited release site, etc...), what region they are in, what OS they are running, what version of the software, etc...there's also a patches table, which contain both the actual patch binary and all the various info about the patch...what OS it's for...what version of the software it's for...if it's for general use or for a specific host, etc... the idea is that the client will be as dumb as possible. In other words, we hope it can be as simple as "select * from patchview where serverid=xxx" or "select availablepatches(xxx)" where xxx is the serverid. So, my task is to figure out which host is asking for which patches are available, and using that piece of information, build a result set that includes only the patches that pass all the criteria for that host. I see two approaches. The first is a view...drawbacks of the view approach are that the view cannot tailor which rows it's presenting based on a variable (i.e. a view can not present a different view of the table depending on who connected can it?) I think I would need one view for each server which quickly becomes unwieldy. Plus, I think the view is going to need some crazy unions and where clauses to get the full set and nothing but the set of eligible patches. The best we could come up with is a view that has a 1-to-1 mapping of serverid to patchid. So if you have 10 patches, and 10 servers, this view would have as many as 100 entries, if all 10 patches were applicable to all 10 servers. This gets fairly large when you start looking at real world projections of how many patches and servers we're going to have (something like maybe 100 patches x 10000 servers) and we're still not even sure if a view can encompass all of the logic we need. Another, more elegant possibility would be to use a procedure which could build the result set piecewise (i.e. first get all the ones that are marked specifically for this host, store them off to the side, then go and get all the other ones that meet criteria B, add them to the pile, then go get those that meet C and add them, etc....) and at the end of the procedure, return a big result set to the client. However, I don't see anything where the procedural languages can return a result set to the client, i'm not even sure if they can always return even a single row or not. A third option is that neither of us tasked with this are database gurus - we're mostly comfortable with them but it's not our bread and butter. So maybe we're taking the wrong approach completely. So, I'm just having a lot of trouble figuring out how to even approach this, so any feedback at all would be greatly appreciated! Thanks for reading this far :-) Sincerely, Fran
pgsql-general by date: