Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?
On Fri, 2007-03-09 at 01:22 +, Bradley Kieser wrote: > I hope that someone has cracked this one because I have run into a brick > wall the entire week and after 3 all-nighters with bad installations, I > would appreciate hearing from others! > > I am looking for a decent OpenSource CRM system that will run with > Postgres. SugarCRM seems to be the most popular but it's MySQL-centric > and its opensource parts are very restricted. > > vTiger is also mySQL-centric. > > I thought that I had a corker of a system with "centricCRM" but when it > came to actually installing it, I am 48 hours down and hacking through > screen after screen of installation errors. Basically, it relies way too > much on ant and Java tools. Nothing against Java but my experience with > ant used for installing PG schemas is a dismal track record of error and > frustration. centric CRM is no exception. Frankly, it just doesn't work > and after trying to hack out the ant into a PG script I have decided to > give it up as a bad job. > > XRMS promises to run on PG but... it doesn't. The core system is fine, > but useless without the plugins. The Plugins are mySQL-specific again, I > spent several all-nighters previously hacking through installation > screens attempting to convert mysql to PG, making software patches... > you get the picture. > > XLSuite looks very promising. Awesome interface, looks great... only > it's just not ready yet. It is a year away from being at full PG > production level. > > Compiere doesn't support PG. > > OpenTAPS the demo won't even work. And it's US-centric whereas we are in > the UK. A pity that it's so very much tied to the US as it could be very > good. > > I have tried numerous other CRMs but all the same - either don't run on > PG, claim to but in reality don't or are simply pre-Alpha and not ready > for production use. > > So if anyone has actually cracked this, please let me know! I really > need a good CRM. > > It has to be OpenSource, not just out of principle, but we need to > integrate it into an existing business with established inhouse software > so we need to be able to customise the code. my experience with CRM stuff is that the general CRM application never does what you want and you are going to have to hack it no matter what. If you are comfortable with going PHP, you just download sugarcrm or vtiger or whatever comes closest to your vision of your needs and hack away from there. Myself, I am very much enthralled with Ruby on Rails and see it as an amazingly rapid development system and have been writing everything from scratch for our non-profit. Craig ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can psql show me where my script is broken?
"Stuart Cooper" <[EMAIL PROTECTED]> writes: >> I have an enormous sql script with an incorrect insert line somewhere. >> I can echo the commands to stdout as they are executed, but apparently >> errors go to stderr. How can I determine where the problem is? > from memory, if you invoke it from the shell with psql [connection > options] -f filename.sql > you'll get the line number of filename.sql that has the error. Any reasonably recent version of postgres will give you a fairly decent pointer to syntax errors. For example: $ cat bogus.sql select * from foo bar baz where 1=0; $ psql regression ... regression=# \i bogus.sql psql:bogus.sql:4: ERROR: syntax error at or near "baz" LINE 3: foo bar baz ^ regression=# If you feel you're being left in the dark, please provide some specifics about what the error is and what PG version you're using. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [GENERAL] security permissions for functions
"Ted Byers" <[EMAIL PROTECTED]> writes: > ... Can > I make a function as a part of a schema that is executable only by the owner > and other functions in the schema, and no-one else, and still have a > publically callable function in that schema invoke the "private" function? Certainly --- the point here is merely that that isn't the *default* behavior. We judged quite some time ago that allowing public execute access was the most useful default. Perhaps that was a bad choice, but I think we're unlikely to change it now ... > I mean the obvious statement, for the fine > tuning he appears to me to want to do, would be to follow the REVOKE > statement you show with a GRANT statement for a specific user. Check. Once you revoke the default public execute access, the function is useless (well, except to superusers) until you grant somebody the right to call it. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] OT: Canadian Tax Database
Omar Eljumaily <[EMAIL PROTECTED]> writes: > Thank God the DOI is inefficient. If they were good at what they do, > which is generally malicious, we'd all be in trouble. Guys, this was off-topic to start with ... if you'd like to argue politics please take it to some other list ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] security permissions for functions
Functions are controlled by the same ACL mechanism that tables and everything else follows. Thus you have the idea of "user id X may do Y with object Z" i.e. "user "barbara" may "execute" function "somefunction()". But there's no real way to alter those permissions outside of changing the user ID context. So, I should be able to have "user "barbara" "execute" function "somefunction()", but, though barbara must not have access of object alpha lets say for data security reasons (and user sarah does), I could have function somefunction invoke another function that stores information about barbara's action to object alpha by changing user context temporarily and without barbara's knowledge; basically saying within function "somefunction()" something like "execute function 'someotherfunction()' impersonating sarah and stop impersonating sarah once someotherfunction returns. Much like the way I can log in to Windows or Linux as one user and temporarily impersonate another while executing a particular program or administrative function (e,g, log into Linux as a mere mortal, start a bash shell providing credentials for an admin account, do my admin type stuff and then close the shell). Or have I misunderstood you here WRT user ID context? Ted ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Weird behaviour on a join with multiple keys
Charlie Clark <[EMAIL PROTECTED]> writes: > I'm getting unexpected results on a query which involves joining two > tables on two common variables (firstname and lastname). That looks like it should work. Given that you describe the columns as "names" I'm supposing they are of textual datatypes. Maybe you have a messed-up encoding or locale situation that is causing the sorts to not work properly? What PG version is this exactly, on what platform, and what do "show lc_collate" and "show server_encoding" say? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] On default privilege/access behavior and how to change it?
"Ing. Daniel Manrique" <[EMAIL PROTECTED]> writes: > I'd like to configure things so that: > a) users are unable to modify template1; If you're on a PG version where template1 isn't the default connection target, you could just disallow connections to it. But that might just mean that your problem moves over to the "postgres" database. > b) Users can't connect to databases other than the ones they created > (and perhaps the postgres database, for initial connection purposes). See CONNECT privilege (as of 8.2) and/or modify pg_hba.conf. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
HIPPA (was Re: [GENERAL] Anyone know ...)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/08/07 20:38, Kenneth Downs wrote: [snip] > Management and we are about to add the CRM to it so that the > scheduling/billing database also serves the doctor's public website, Is that wise? One bug and a cracker is poking around some very private stuff!! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF8N6/S9HxQb37XmcRAsgfAKCq5hSw1XpU+piaL2RBoihoPTMfZwCdG5D3 YndimzGriPXUM49P9b596og= =wU1C -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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: [GENERAL] Weird behaviour on a join with multiple keys
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/08/07 16:09, Charlie Clark wrote: > Hi, > > I'm getting unexpected results on a query which involves joining two > tables on two common variables (firstname and lastname). > > This is the basic query: > > SELECT table1.lastname, table1.firstname > FROM table1 > INNER JOIN table2 ON > (table2.name = table1.name > AND > table2.vorname = table1.vorname) > > This is returning many rows fewer than I expect and is ignoring a lot > where table1.firstname = table2.firstname AND table1.lastname = > table2.lastname. Huh? Why should you? You're not joining on firstname and lastname. What happens if you do it like this: SELECT T1.LASTNAME, T2.FIRSTNAME FROM TABLE1 T1, TABLE2 T2 WHERE T1.NAME = T2.NAME AND T1.VORNAME = T2.VORNAME; -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF8N1zS9HxQb37XmcRAp3wAKCRJ1kuoqbc8YPOZwx+53+JRqvD/ACfVvFy zK8u0+RYuMiBxEnURVc74Jc= =9oxj -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Query help
Richard Huxton wrote: Madison Kelly wrote: Hi all, I've got a query that looks through a table I use for my little search engine. It's something of a reverse-index but not quite, where a proper reverse index would have 'word | doc1, doc3, doc4, doc7' showing all the docs the keyword is in, mine has an entry for eac I've got a query like: SELECT sch_id, sch_for_table, sch_ref_id, sch_instances FROM search_index WHERE (sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%') AND sch_for_table!='client' AND ... (more restrictions) ORDER BY sch_instances DESC; This returns references to a data column (sch_ref_id) in a given table (sch_for_table) for each matched keyword. The problem I am having is that two keywords might reference the same table/column which would, in turn, give me two+ search results pointing to the same entry. What I would like to do is, when two or more results match the same 'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the 'sch_instances' column is the number of times the given keyword is found in the table/column. I'd like to add up the number in the duplicate results (to give it a higher accuracy and move it up the search results). You'll want something like: SELECT sch_id, sch_for_table, sch_ref_id, SUM(sch_instances) AS tot_instances ... GROUP BY sch_id, sch_for_table, sch_ref_id ORDER BY tot_instances DESC; The key word to search the manuals on is "aggregates" (sum(), count() etc). This is *exactly* the pointer I needed, thank you! Sad thing is that I even used "GROUP BY" before... had just forgotten about it. ^_^; Madison ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?
On Mar 8, 2007, at 6:26 PM, Brent Wood wrote: Bradley Kieser wrote: I hope that someone has cracked this one because I have run into a brick wall the entire week and after 3 all-nighters with bad installations, I would appreciate hearing from others! I am looking for a decent OpenSource CRM system that will run with Postgres. SugarCRM seems to be the most popular but it's MySQL- centric and its opensource parts are very restricted. Not a recommendation, coz I haven't actually used it, but you might try Drupal, if you haven't already. I've seen Drupal work OK, & it claims to support Postgres. It's not a CRM, though. Last time I looked, about a year ago, I came to the same conclusion as the OP. There are at least some excellent commercial CRMs that support postgresql, so there's no reason why the open source ones shouldn't, other than the whole open-source / PHP / MySQL hack mindset. That and the whole mysql installed base issue. Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?
Bradley Kieser wrote: I hope that someone has cracked this one because I have run into a brick wall the entire week and after 3 all-nighters with bad installations, I would appreciate hearing from others! I am looking for a decent OpenSource CRM system that will run with Postgres. SugarCRM seems to be the most popular but it's MySQL-centric and its opensource parts are very restricted. Bradley, I've got about 2.5 out of 3 of what you are looking for, perhaps it might work out for you. We have a GPL database application framework that we have used for a handful of CRM-style applications (links below). It runs on Postgres, is completely GPL, and is written in PHP. Now the bad news :) We have only crude CRM stuff, and you may end up having to put more into this area than you want to. As I said, we use it ourselves for some stuff, but our needs are simple in that area. Its primary purpose is high-powered business database apps. Now on the third hand, we have a pure business app for Medical Practice Management and we are about to add the CRM to it so that the scheduling/billing database also serves the doctor's public website, doing things like showing schedules, listing active insurances and other nifty stuff like that. And of course the doc can enter new articles. We think its really cool to be able to integrate CRM with business this way. The only other bad news is that it is Linux only. It has been installed on Mac but nobody here can support you with that. In principle it can run on Windows because Apache, PHP and Postgres run on windows, but again, you'd become the guy I send other people to once you get it going :) Here are three CRM sites running it. They are my company site, the project site itself, and a site for a rental home: www.secdat.com www.andromeda-project.org www.manisteeforestretreat.com The middle one is the actual project, its got some docs, some tutorials, and a link to the sourceforge download. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?
Bradley Kieser wrote: I hope that someone has cracked this one because I have run into a brick wall the entire week and after 3 all-nighters with bad installations, I would appreciate hearing from others! I am looking for a decent OpenSource CRM system that will run with Postgres. SugarCRM seems to be the most popular but it's MySQL-centric and its opensource parts are very restricted. Not a recommendation, coz I haven't actually used it, but you might try Drupal, if you haven't already. I've seen Drupal work OK, & it claims to support Postgres. Brent Wood vTiger is also mySQL-centric. I thought that I had a corker of a system with "centricCRM" but when it came to actually installing it, I am 48 hours down and hacking through screen after screen of installation errors. Basically, it relies way too much on ant and Java tools. Nothing against Java but my experience with ant used for installing PG schemas is a dismal track record of error and frustration. centric CRM is no exception. Frankly, it just doesn't work and after trying to hack out the ant into a PG script I have decided to give it up as a bad job. XRMS promises to run on PG but... it doesn't. The core system is fine, but useless without the plugins. The Plugins are mySQL-specific again, I spent several all-nighters previously hacking through installation screens attempting to convert mysql to PG, making software patches... you get the picture. XLSuite looks very promising. Awesome interface, looks great... only it's just not ready yet. It is a year away from being at full PG production level. Compiere doesn't support PG. OpenTAPS the demo won't even work. And it's US-centric whereas we are in the UK. A pity that it's so very much tied to the US as it could be very good. I have tried numerous other CRMs but all the same - either don't run on PG, claim to but in reality don't or are simply pre-Alpha and not ready for production use. So if anyone has actually cracked this, please let me know! I really need a good CRM. It has to be OpenSource, not just out of principle, but we need to integrate it into an existing business with established inhouse software so we need to be able to customise the code. Thanks, Brad ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] On default privilege/access behavior and how to change it?
Hey all! I have a postgresql 8.0 installation we're using to give database classes. By default we create a linux user and a postgresql user (with create database permissions) and use ident authentication. Users initially use psql to connect to the template1 database; from there they create their own database, connect to that and start working. The problem I'm having is, if the user forgets to connect to their own database, they stay on template1 and are able to create tables and generally mess the database around. Further, any user is able to connect to any other user's database and create his own tables there. So if user A connects to B's database he can create a table, and B can't remove it even though he owns the database. I'd like to configure things so that: a) users are unable to modify template1; for that matter, I'm planning on having them connect to the "postgres" database first, because if people are connected to template1, they can't create their own databases. The problem persists since the "postgres" database is similarly affected and anyone can create tables there. b) Users can't connect to databases other than the ones they created (and perhaps the postgres database, for initial connection purposes). At the very least, they should be able to connect and perhaps have read-only access, but *NOT* create new tables on databases they didn't create. Could anyone help me in doing this? if it's easier/better on a newer postgresql version, I'm willing to upgrade. Thanks in advance! - Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] make cascade the default?
Is there a way at to set the 'on delete' and 'on update' options at the database or table level, so that any new foreign keys default to, say 'CASCADE', rather than 'no action'? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PG periodic Error on W2K
Magnus Hagander wrote: On Thu, Mar 01, 2007 at 10:45:16AM -0500, Tom Lane wrote: Magnus Hagander <[EMAIL PROTECTED]> writes: On Thu, Mar 01, 2007 at 09:44:19AM +0900, Paul Lambert wrote: I am periodically getting errors pop up on the server console of the following nature: The File or directory D:\PostgresQL\Data\global\pgstat.stat is corrupt and unreadable. Please run the Chkdsk utility. They can *not* be caused by a bug in PostgreSQL - no more than a kernel oops in linux is the fault of PostgreSQL. Now, we do push the filesystem and disk layer in an unusual way with the pgstats writes, gievn that we rewrite the same file over and over and over and over again at very short intervals. But nothing says we're not allowed to do that :-) I'm wondering whether the message is coming from the kernel, or some sort of file-scanning utility that gets confused when a file is deleted while it's looking at it. That specific message comes from the kernel. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Still getting the errors, but I managed to convince the powers that be to order me a fancy new server so we'll see how that goes when it arrives. Thanks again to all for the info though. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?
I hope that someone has cracked this one because I have run into a brick wall the entire week and after 3 all-nighters with bad installations, I would appreciate hearing from others! I am looking for a decent OpenSource CRM system that will run with Postgres. SugarCRM seems to be the most popular but it's MySQL-centric and its opensource parts are very restricted. vTiger is also mySQL-centric. I thought that I had a corker of a system with "centricCRM" but when it came to actually installing it, I am 48 hours down and hacking through screen after screen of installation errors. Basically, it relies way too much on ant and Java tools. Nothing against Java but my experience with ant used for installing PG schemas is a dismal track record of error and frustration. centric CRM is no exception. Frankly, it just doesn't work and after trying to hack out the ant into a PG script I have decided to give it up as a bad job. XRMS promises to run on PG but... it doesn't. The core system is fine, but useless without the plugins. The Plugins are mySQL-specific again, I spent several all-nighters previously hacking through installation screens attempting to convert mysql to PG, making software patches... you get the picture. XLSuite looks very promising. Awesome interface, looks great... only it's just not ready yet. It is a year away from being at full PG production level. Compiere doesn't support PG. OpenTAPS the demo won't even work. And it's US-centric whereas we are in the UK. A pity that it's so very much tied to the US as it could be very good. I have tried numerous other CRMs but all the same - either don't run on PG, claim to but in reality don't or are simply pre-Alpha and not ready for production use. So if anyone has actually cracked this, please let me know! I really need a good CRM. It has to be OpenSource, not just out of principle, but we need to integrate it into an existing business with established inhouse software so we need to be able to customise the code. Thanks, Brad ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Weird behaviour on a join with multiple keys
What happens if you do an outer join instead of an inner join?
Charlie Clark wrote:
Hi,
I'm getting unexpected results on a query which involves joining two
tables on two common variables (firstname and lastname).
This is the basic query:
SELECT table1.lastname, table1.firstname
FROM table1
INNER JOIN table2 ON
(table2.name = table1.name
AND
table2.vorname = table1.vorname)
This is returning many rows fewer than I expect and is ignoring a lot
where table1.firstname = table2.firstname AND table1.lastname =
table2.lastname. In fact when I extend the query by a WHERE clause
such as WHERE table1.lastname IS LIKE 'Sch%' I get results which are
not returned by the original query.
I'm not very au fait with the inner workings of PostgreSQL but EXPLAIN
does not seem, to me at least, to provide an explanation for the
missing results.
"Merge Join (cost=1987.97..2121.24 rows=34 width=22)"
" Merge Cond: (("outer"."?column3?" = "inner"."?column3?") AND
("outer"."?column4?" = "inner"."?column4?"))"
" -> Sort (cost=364.97..375.99 rows=4409 width=22)"
"Sort Key: (table1.lastname)::text, (table1.firstname)::text"
"-> Seq Scan on table1 (cost=0.00..98.09 rows=4409 width=22)"
" -> Sort (cost=1623.00..1667.00 rows=17599 width=21)"
"Sort Key: (table2.lastname)::text, (table2.firstname)::text"
"-> Seq Scan on table2 (cost=0.00..381.99 rows=17599 width=21)"
Am I missing something big and obvious here?
Charlie
--
Charlie Clark
Helmholtzstr. 20
Düsseldorf
D- 40215
Tel: +49-211-938-5360
GSM: +49-178-782-6226
---(end of broadcast)---
TIP 1: 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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Weird behaviour on a join with multiple keys
Hi,
I'm getting unexpected results on a query which involves joining two
tables on two common variables (firstname and lastname).
This is the basic query:
SELECT table1.lastname, table1.firstname
FROM table1
INNER JOIN table2 ON
(table2.name = table1.name
AND
table2.vorname = table1.vorname)
This is returning many rows fewer than I expect and is ignoring a lot
where table1.firstname = table2.firstname AND table1.lastname =
table2.lastname. In fact when I extend the query by a WHERE clause
such as WHERE table1.lastname IS LIKE 'Sch%' I get results which are
not returned by the original query.
I'm not very au fait with the inner workings of PostgreSQL but
EXPLAIN does not seem, to me at least, to provide an explanation for
the missing results.
"Merge Join (cost=1987.97..2121.24 rows=34 width=22)"
" Merge Cond: (("outer"."?column3?" = "inner"."?column3?") AND
("outer"."?column4?" = "inner"."?column4?"))"
" -> Sort (cost=364.97..375.99 rows=4409 width=22)"
"Sort Key: (table1.lastname)::text, (table1.firstname)::text"
"-> Seq Scan on table1 (cost=0.00..98.09 rows=4409 width=22)"
" -> Sort (cost=1623.00..1667.00 rows=17599 width=21)"
"Sort Key: (table2.lastname)::text, (table2.firstname)::text"
"-> Seq Scan on table2 (cost=0.00..381.99 rows=17599 width=21)"
Am I missing something big and obvious here?
Charlie
--
Charlie Clark
Helmholtzstr. 20
Düsseldorf
D- 40215
Tel: +49-211-938-5360
GSM: +49-178-782-6226
---(end of broadcast)---
TIP 1: 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: [GENERAL] security permissions for functions
In response to "Ted Byers" <[EMAIL PROTECTED]>: > > - Original Message - > From: "Bill Moran" <[EMAIL PROTECTED]> > > > In response to Rikard Pavelic <[EMAIL PROTECTED]>: > > > >> Tom Lane wrote: > >> > No, it's operating as designed. Per the GRANT reference page: > >> > : Depending on the type of object, the initial default privileges may > >> > : include granting some privileges to PUBLIC. The default is no public > >> > : access for tables, schemas, and tablespaces; CONNECT privilege and > >> > TEMP > >> > : table creation privilege for databases; EXECUTE privilege for > >> > functions; > >> > : and USAGE privilege for languages. The object owner may of course > >> > revoke > >> > : these privileges. (For maximum security, issue the REVOKE in the same > >> > : transaction that creates the object; then there is no window in which > >> > : another user may use the object.) > >> > > > This seems clear enough. > > >> > You'll need to revoke the default public EXECUTE privilege on any > >> > functions you don't want to be callable. > > As does this. > >> > >> Hmm, so the answer to my question > >> "How can I assign execute permission to a role for a single function > >> inside schema." > >> is I can't? > > > > How did you interpret "do it like this" to mean "you can't do it"? > > > I too can not understand how he came to this conclusion, unless it wasn't > obvious to him how to grant certain permissions to roles. > > I am curious, though. I shape my understanding of this using a metaphore of > private, protected and public member functions in C++ classes. There is, of > course, no point in having a function that can't be called under any > circumstances, but it is often useful to have a variety of protected and > private functions (and other members) that can only be called by other > member functions or member frunctions of derived classes. Does the > permission granting procedure for functions work in a similar fashion? Can > I make a function as a part of a schema that is executable only by the owner > and other functions in the schema, and no-one else, and still have a > publically callable function in that schema invoke the "private" function? > Or is my C++ based understanding leading me astray here? No. At least not at the design level. Whether you might possibly be able to accomplish that in effect, I'm not sure. Functions are controlled by the same ACL mechanism that tables and everything else follows. Thus you have the idea of "user id X may do Y with object Z" i.e. "user "barbara" may "execute" function "somefunction()". But there's no real way to alter those permissions outside of changing the user ID context. ACLs have pretty much everything you'd want from ACLs, though. Think more like UNIX filesystem permissions than OO public/private/protected. You can take an executable on the filesystem and control what UIDs can execute it, and you can do the same thing with functions in Postgres. > > REVOKE ALL ON FROM PUBLIC; > > > >> So this basically means that I can't fine tune the permissions through > >> functions, but I > >> can through views and tables? > >> This looks like a bug in design to me ;( > > > > Relax. You (somehow) misunderstood Tom. > > > I wonder if he's worried about granting permissions to roles or groups > rather than to individual users. I mean the obvious statement, for the fine > tuning he appears to me to want to do, would be to follow the REVOKE > statement you show with a GRANT statement for a specific user.At least > that is what I'd infer from what you and Tom wrote. Did I misunderstand > either of you, or what Rikard is worried about? I think you understand. You can grant permissions by user or group, though, and best practice usually dictates allocating ACLs to groups, then adding users to groups where appropriate. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] OT: Canadian Tax Database
Thank God the DOI is inefficient. If they were good at what they do, which is generally malicious, we'd all be in trouble. Your story reminded me of a dear friend who works for the department of the interior here in the US who routinely was dressed down for writing functional, reliable software quickly and with a minimum of bugs and fuss. He made all the other people in his office feel bad. sigh. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Solaris and Ident
I've used Postgres on Linux for several years, and I have relied on the 'ident' authentication method for system administration and other tasks. Now I need to get a postgres database up and running on Solaris. The problem is that 'ident' authentication is not supported on Solaris. I can't figure out how to do simple things, such as running pg_dumpall from a cron job, because without 'ident' authentication the dump script prompts for a password. Does anybody have a work around for this problem? -- View this message in context: http://www.nabble.com/Solaris-and-Ident-tf3371429.html#a9381507 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 1: 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
[GENERAL] Tsearch2 / Create rule on select
Dear Friends, I am very impressed by TSearch2 and would like to thank Oleg and the team for their hard work. I would like to migrate a phpBB forum with more that 200.000 messages to TSeach. Full text searches have become a bottleneck. When there are more than 200 users, the server slow dramatically. I need TSearch! So ... I installed TSearch2, a French dictionnary, everything is okay. My database is PostgreSQL 8.2.3, UTF-8. My questions now: 1) Should I migrate phpBB to TSeach2 in PHP code, rewriting the queries, which is quite obvious or easy. OR 2) Is there a smart way to catch queries on the fly in PostgreSQL, for example using "CREATE RULE "_RETURN" AS ON SELECT TO t1 DO INSTEAD " or the like. The idea would be that when I search on a text field, PostgreSQL rule system would rewrite the query to seach using Tsearch2. Is there any tool in TSeach2 doing this query rewriting rule? Obviously, it does not seem to exist, but I would like to be sure. 3) Gin indexes Are Gin indexes recommended for large databases? How to calculate the size in memory of an index? Kind regards, Jean-Michel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Can psql show me where my script is broken?
I have an enormous sql script with an incorrect insert line somewhere. I can echo the commands to stdout as they are executed, but apparently errors go to stderr. How can I determine where the problem is? from memory, if you invoke it from the shell with psql [connection options] -f filename.sql you'll get the line number of filename.sql that has the error. Good luck, Stuart. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Can psql show me where my script is broken?
I have an enormous sql script with an incorrect insert line somewhere. I can echo the commands to stdout as they are executed, but apparently errors go to stderr. How can I determine where the problem is? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Setting week starting day
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Jorge Godoy escribió:
>
>> I mean, if I wanted to do the above but instead of Sunday or Monday as the
>> starting day I'd like using Fridays or Wednesdays...
>>
>> Is it possible? Writing a new function shouldn't be too hard -- it's a
>> matter
>> of truncating the week on a day and shifting the date forward or backward --,
>> but something like a "SET bow=5" (to make the API consistent with the 'dow'
>> that already exists) would be really great!
>
> Is it not just a matter of adding a constant and then taking modulo 7?
As I said, it is easy with a function. :-) I was just curious to see if we
had something like Oracle's NEXT_DAY function or something like what I
described (SET BOW=4; -- makes Thursday the first day of week):
NEXT_DAY
Syntax
Purpose
Returns the date of the first weekday named by char that is later than the
date d. The argument char must be a day of the week in the date language of
your session, either the full name or the abbreviation. The minimum number of
letters required is the number of letters in the abbreviated version. Any
characters immediately following the valid abbreviation are ignored. The
return value has the same hours, minutes, and seconds component as the
argument d.
Example
This example returns the date of the next Tuesday after March 15, 1998.
SELECT NEXT_DAY('15-MAR-98','TUESDAY') "NEXT DAY"
FROM DUAL;
NEXT DAY
-
16-MAR-98
So, I'd have something like: "SELECT NEXT_DAY(now()+'5 weeks'::INTERVAL,
'THURSDAY');" to give me the next Thursday 5 weeks from now.
Be seeing you,
--
Jorge Godoy <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] security permissions for functions
On 3/8/07, Rikard Pavelic <[EMAIL PROTECTED]> wrote: Bill Moran wrote: >> Hmm, so the answer to my question >> "How can I assign execute permission to a role for a single function >> inside schema." >> is I can't? >> > > How did you interpret "do it like this" to mean "you can't do it"? > > REVOKE ALL ON FROM PUBLIC; > > >> So this basically means that I can't fine tune the permissions through >> functions, but I >> can through views and tables? >> This looks like a bug in design to me ;( >> > > Relax. You (somehow) misunderstood Tom. > > Urgh, I didn't meant it like that ;( Sorry. Anyway, maybe I didn't make myself clear enough. Let's try again ;) Let's say I have two functions in schema example example.function1() example.function2() and two users user1 and user2 Is there a way to grant user1 permission to execute function example.function1(), but not example.function2() And for user2 to be able to execute example.function2() and not example.function1()? To grant user1 permission to execute example.function1() I need to do GRANT USAGE ON SCHEMA example to user1 But this permissions grants him also ability to execute example.function2 () GRANT EXECUTE ON FUNCTION example.function1() TO user1 or REVOKE EXECUTE ON FUNCTION example.function1() FROM user1 don't have any affect. Role PUBLIC don't have access to this schema so that is not an issue. I have a GRANT USAGE on schema "name" to PUBLIC and I have a GRANT EXECUTE on function1 to user1 and a GRANT EXECUTE on function2 to user 2 and I can't access function2 as user1. But when I created the functions I did a REVOKE EXECUTE on functionN from PUBLIC Exception: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for function function2 ... Regards, Rikard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings David
Re: [GENERAL] security permissions for functions
On 08/03/2007 22:21, Rikard Pavelic wrote: And for user2 to be able to execute example.function2() and not example.function1()? REVOKE EXECUTE ON FUNCTION example.function1() FROM user1 don't have any affect. Surely you mean - REVOKE EXECUTE ON FUNCTION example.function1() FROM user2; - in order to achieve what you described? Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] security permissions for functions
Shane Ambler wrote: You can but the default is to allow the execution of all functions. You need to revoke the existing permission of executing all functions before you can allow only a single function to be run. If you want a specific function to be accessed only by selected roles then you revoke public access when you create it and allow access to the one or more roles you want to allow access. Oh, I see Public access was being allowed by default when I created functions. Oh my ;( I'll go and hide somewhere now Thanks, Rikard ---(end of broadcast)--- TIP 1: 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: [GENERAL] security permissions for functions
Bill Moran wrote: Hmm, so the answer to my question "How can I assign execute permission to a role for a single function inside schema." is I can't? How did you interpret "do it like this" to mean "you can't do it"? REVOKE ALL ON FROM PUBLIC; So this basically means that I can't fine tune the permissions through functions, but I can through views and tables? This looks like a bug in design to me ;( Relax. You (somehow) misunderstood Tom. Urgh, I didn't meant it like that ;( Sorry. Anyway, maybe I didn't make myself clear enough. Let's try again ;) Let's say I have two functions in schema example example.function1() example.function2() and two users user1 and user2 Is there a way to grant user1 permission to execute function example.function1(), but not example.function2() And for user2 to be able to execute example.function2() and not example.function1()? To grant user1 permission to execute example.function1() I need to do GRANT USAGE ON SCHEMA example to user1 But this permissions grants him also ability to execute example.function2() GRANT EXECUTE ON FUNCTION example.function1() TO user1 or REVOKE EXECUTE ON FUNCTION example.function1() FROM user1 don't have any affect. Role PUBLIC don't have access to this schema so that is not an issue. Regards, Rikard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] security permissions for functions
Rikard Pavelic wrote: Tom Lane wrote: No, it's operating as designed. Per the GRANT reference page: : Depending on the type of object, the initial default privileges may : include granting some privileges to PUBLIC. The default is no public : access for tables, schemas, and tablespaces; CONNECT privilege and TEMP : table creation privilege for databases; EXECUTE privilege for functions; : and USAGE privilege for languages. The object owner may of course revoke : these privileges. (For maximum security, issue the REVOKE in the same : transaction that creates the object; then there is no window in which : another user may use the object.) You'll need to revoke the default public EXECUTE privilege on any functions you don't want to be callable. regards, tom lane Hmm, so the answer to my question "How can I assign execute permission to a role for a single function inside schema." is I can't? You can but the default is to allow the execution of all functions. You need to revoke the existing permission of executing all functions before you can allow only a single function to be run. If you want a specific function to be accessed only by selected roles then you revoke public access when you create it and allow access to the one or more roles you want to allow access. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Setting week starting day (was: Re: [GENERAL] Tabulate data incrementally)
Jorge Godoy escribió: > I mean, if I wanted to do the above but instead of Sunday or Monday as the > starting day I'd like using Fridays or Wednesdays... > > Is it possible? Writing a new function shouldn't be too hard -- it's a matter > of truncating the week on a day and shifting the date forward or backward --, > but something like a "SET bow=5" (to make the API consistent with the 'dow' > that already exists) would be really great! Is it not just a matter of adding a constant and then taking modulo 7? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] "oracle to postgresql" conversion
Hi, On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote: > NUMBER is Oracle's version of NUMERIC - Oracle will use both but > probably only Oracle will use NUMBER. Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT? /me looks at Oracle docs again. -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] OT: Canadian Tax Database
On Thu, 2007-03-08 at 10:15, Ted Byers wrote: > > I recall being told by one project manager I knew years ago who had an > opportunity to create a bid for an RFP issued by Transport Canada (long long > ago). He refuse, so his employer prepared the bid. He refused because the > RFP was a joke. There were absolutely no functional requirements, nor Your story reminded me of a dear friend who works for the department of the interior here in the US who routinely was dressed down for writing functional, reliable software quickly and with a minimum of bugs and fuss. He made all the other people in his office feel bad. sigh. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] "oracle to postgresql" conversion
On Tue, 2007-03-06 at 11:01, [EMAIL PROTECTED] wrote:
> I'm a newbie in Oracle and postgreSQL,
> i'm need to translate the following script (in Oracle) to postgreSQL :
>
> rem Autorisation des lignes vides :
> set sqlbl on
You don't need this, postgresql doesn't choke on extra lines.
> rem Initialisation du timer :
> set timing on
-- Change this to \timing if you're gonna use psql to run the script:
\timing
> rem Creation de la table :
becomes
-- Creation de la table :
Now, we need to use real SQL 99 types here, or specific postgresql
types. And don't quote unless you need to. PostgreSQL folds to lower
case, not upper case, so if you quote upper case here, you'll always
have to quote in the future. Better to just not quote, in my humble
opinion.
So, "LEPAPE" will become lepape
VARCHAR2(16) will become varchar(16)
NUMBER will become either
decimal or numeric
NOT NULL is still NOT NULL
and the check constraint will look the same too. again unless you
require upper case, leave the SYS_C009967 lower case, and better yet,
give it a useful name, like lepape_measure_check
CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,
CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Setting week starting day (was: Re: [GENERAL] Tabulate data incrementally)
Richard Huxton writes:
> Omar Eljumaily wrote:
>> I want to tabulate time data on a weekly basis, but my data is entered on a
>> daily basis.
>>
>> create table time_data
>> {
>>employee varchar(10),
>>_date date,
>>job varchar(10),
>>amount
>> }
>>
>> So I want to tabulate with a single sql command. Is that possible?
>
> Try one of these:
>
> => SELECT date_trunc('week',now());
>date_trunc
>
> 2007-03-05 00:00:00+00
>
> => SELECT extract(week from now());
> date_part
> ---
> 10
Hi!
I'm hijacking this thread a bit... Is it possible to specify dinamically the
day of the week when week starts?
I mean, if I wanted to do the above but instead of Sunday or Monday as the
starting day I'd like using Fridays or Wednesdays...
Is it possible? Writing a new function shouldn't be too hard -- it's a matter
of truncating the week on a day and shifting the date forward or backward --,
but something like a "SET bow=5" (to make the API consistent with the 'dow'
that already exists) would be really great!
Why doing that? Imagine an accounting office where all their activities
should be closed and values summed up every Wednesday. Or a company that
tracks the end of their activies weekly and consider the end of the week on
Thursdays (so that they can send invoices on Friday).
Being able to count "the first day of the 'week' 5 weeks from now" for the
above situations would make things easier to code. :-)
--
Jorge Godoy <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] security permissions for functions
- Original Message - From: "Bill Moran" <[EMAIL PROTECTED]> To: "Rikard Pavelic" <[EMAIL PROTECTED]> Cc: "Tom Lane" <[EMAIL PROTECTED]>; Sent: Thursday, March 08, 2007 3:18 PM Subject: Re: [GENERAL] security permissions for functions In response to Rikard Pavelic <[EMAIL PROTECTED]>: Tom Lane wrote: > No, it's operating as designed. Per the GRANT reference page: > : Depending on the type of object, the initial default privileges may > : include granting some privileges to PUBLIC. The default is no public > : access for tables, schemas, and tablespaces; CONNECT privilege and > TEMP > : table creation privilege for databases; EXECUTE privilege for > functions; > : and USAGE privilege for languages. The object owner may of course > revoke > : these privileges. (For maximum security, issue the REVOKE in the same > : transaction that creates the object; then there is no window in which > : another user may use the object.) > This seems clear enough. > You'll need to revoke the default public EXECUTE privilege on any > functions you don't want to be callable. As does this. Hmm, so the answer to my question "How can I assign execute permission to a role for a single function inside schema." is I can't? How did you interpret "do it like this" to mean "you can't do it"? I too can not understand how he came to this conclusion, unless it wasn't obvious to him how to grant certain permissions to roles. I am curious, though. I shape my understanding of this using a metaphore of private, protected and public member functions in C++ classes. There is, of course, no point in having a function that can't be called under any circumstances, but it is often useful to have a variety of protected and private functions (and other members) that can only be called by other member functions or member frunctions of derived classes. Does the permission granting procedure for functions work in a similar fashion? Can I make a function as a part of a schema that is executable only by the owner and other functions in the schema, and no-one else, and still have a publically callable function in that schema invoke the "private" function? Or is my C++ based understanding leading me astray here? REVOKE ALL ON FROM PUBLIC; So this basically means that I can't fine tune the permissions through functions, but I can through views and tables? This looks like a bug in design to me ;( Relax. You (somehow) misunderstood Tom. I wonder if he's worried about granting permissions to roles or groups rather than to individual users. I mean the obvious statement, for the fine tuning he appears to me to want to do, would be to follow the REVOKE statement you show with a GRANT statement for a specific user.At least that is what I'd infer from what you and Tom wrote. Did I misunderstand either of you, or what Rikard is worried about? Ted ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Determine users and roles
RPK <[EMAIL PROTECTED]> writes: > I want to determine the usernames, privileges and roles of the users that are > created for a particular database. I am using PGSQL 8.2.3 on Windows XP. I > want to run SQL query for this. In PostgreSQL users aren't created per database but per cluster. Run the equivalent of "psql -E" in your command prompt and issue a "\du". Be seeing you, -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] No buffer space available
"Nik" <[EMAIL PROTECTED]> writes: > Now I wonder why is it that I started getting WSANOBUFS errors from > Windows Socket System if this same setup with the same machines has > been operational for over a year? The query size did not change and > the number of connections to the server did not change. Is there any > good reason for this error to start showing up? Did your Windows change? (Updates, service packs, etc.) -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] security permissions for functions
In response to Rikard Pavelic <[EMAIL PROTECTED]>: > Tom Lane wrote: > > No, it's operating as designed. Per the GRANT reference page: > > : Depending on the type of object, the initial default privileges may > > : include granting some privileges to PUBLIC. The default is no public > > : access for tables, schemas, and tablespaces; CONNECT privilege and TEMP > > : table creation privilege for databases; EXECUTE privilege for functions; > > : and USAGE privilege for languages. The object owner may of course revoke > > : these privileges. (For maximum security, issue the REVOKE in the same > > : transaction that creates the object; then there is no window in which > > : another user may use the object.) > > > > You'll need to revoke the default public EXECUTE privilege on any > > functions you don't want to be callable. > > Hmm, so the answer to my question > "How can I assign execute permission to a role for a single function > inside schema." > is I can't? How did you interpret "do it like this" to mean "you can't do it"? REVOKE ALL ON FROM PUBLIC; > So this basically means that I can't fine tune the permissions through > functions, but I > can through views and tables? > This looks like a bug in design to me ;( Relax. You (somehow) misunderstood Tom. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] security permissions for functions
Tom Lane wrote: No, it's operating as designed. Per the GRANT reference page: : Depending on the type of object, the initial default privileges may : include granting some privileges to PUBLIC. The default is no public : access for tables, schemas, and tablespaces; CONNECT privilege and TEMP : table creation privilege for databases; EXECUTE privilege for functions; : and USAGE privilege for languages. The object owner may of course revoke : these privileges. (For maximum security, issue the REVOKE in the same : transaction that creates the object; then there is no window in which : another user may use the object.) You'll need to revoke the default public EXECUTE privilege on any functions you don't want to be callable. regards, tom lane Hmm, so the answer to my question "How can I assign execute permission to a role for a single function inside schema." is I can't? So this basically means that I can't fine tune the permissions through functions, but I can through views and tables? This looks like a bug in design to me ;( Regards, Rikard ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] which is more scalable for the database?
Timasmith wrote: Suppose I have a database table with 20 fields which are lookups to a single table. configtable(configtable_id, a_field, something_lookup_id, another_lookup_id, ...) lookup(lookup_id, value, description, ...) what is going to be faster to map the rows to an Object which needs the 'value' for every field ending in lookup_id How long is ball of string 'a' compared to ball of string 'b'? That will depend on a lot on how large the lookup table will be (100 rows or 10 million rows?) and on how long your descriptions are. Will you want all 20 descriptions for each query you run? If your descriptions are 200 characters each then maybe you can reduce some data transfer by caching these at the client instead of transferring them each time you retrieve a result. But then how will you keep your local cache up to date? I would suggest the view for a couple of reasons - first it will simplify your queries as you won't need all the join details in every query you run. Second the select for the query can be parsed and stored whereas separate selects will be parsed each time. A lot of this will depend on what you are doing and what you are using to do it. If you are using php then you have less options for caching than say a client program written in c. php may run pretty fast but it won't outrun a compiled c program. a) View select c.configtable_id, l1.value as something_lookup, l2.value as another_lookup from configtable c, lookup l1, lookup l2 where c.something_lookup_id = l1.lookup_id and c.another_lookup_id = l2.lookup_id foreach row map values to object end b) Cache all lookup values and populate select c.* from configtable foreach row map values to object if lookup_id find value from hashtable and map value to object endif end It seems that the latter *might* be better to scale outward better, as you could add application servers to do the caching/mapping and you only select from a single table? Maybe but then postgresql may do the lookups quicker than what you have available at the client end. The only way you will really know is to load up some sample data and test each method yourself. How far are you expecting to scale? The simplest method may just work fast enough that all the extra caching and setup/maintenance of this will outweigh any benefit. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres Mailing List management solution
Hi, On Tue, 2007-03-06 at 10:02 +, Desire Athow wrote: > I am looking for a email list management solution (can be a script, an > open source application etc), which uses Postgre SQL (AKA Postgres) as > database. I have done an Mailman installation some years ago that was storing all user info in PostgreSQL. We were dumping user data in mailman's format regulary (silent unsub/silent resub). I know it is a pain, but it was working (BTW, we were using our own subcription page). Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] OT: Canadian Tax Database
Since this thread has already degraded, I'll offer my two cents. The biggest screw ups in US history have been instigated by groups of privileged White men. I know my name may sound otherwise, but I'm a White American male, so I'm not pointing the finger at another group. Let's see, Enron, Arthur Anderson, the entire Bush Administration and its fiascos in Iraq, Katrina, foreign policy in general, etc. I've worked for large, major IT providers and I can tell you that incompetency shows no racial or ethnic boundaries. It tends to exist in large, politically connected, no bid contractors, not low bid contractors or ones who benefited from affirmative action. Ted Byers wrote: > Richard Huxton wrote: >> http://www.thestar.com/News/article/189175 >> >> "For instance, in some cases the field for the social insurance number >> was instead filled in with a birth date." >> >> Unbelievable. Sixty years of electronic computing, fifty years use in >> business and the "professionals" who built the tax system for a >> wealthy >> democratic country didn't use data types. > > This is Unbelievable? This is commonplace. > And due at least in part to government (and other institutions operated by damned fools) opting for the least expensive provider rather than paying for someone who actually knows what they're doing. Just as buying cheap junk always comes back to get you, hiring incompetent fools that don't know their ass from a hole in the ground will come back to get you too. Come on, they don't hire incompetent fools. The hire the people You CAN'T be serious! Have you ever dealt with them or with the consequences of their incompetence? they need to fill their quota regardless of how well trained and experienced they are. I am not saying that non white males are in any way less competent than white males, but by removing them from the pool does not make things better. The biggest problem with quotas is not hiring less qualified staff, it is that less qualified staff know why they were hired and know that they are very unlikely to be fired, so they have little incentive to work hard or attempt to do their best, they can always fail upwards. What does this have to do with anything? No one here, except you, has said anything about the profile of the people involved WRT race, gender, religion, &c. Nor has anyone said anything about "qualifications". The only thing that has been said is that, based on what is seen in the "work", the people responsible for that work must be incompetent. It is an inference based on what is seen in what has been done and has nothing to do with any of the prohibited grounds for discrimination used as excuses for affirmative action. And yes, I have seen cases where less qualified, even unqualified, people have been hired as a result of these affirmative action initiatives (and I have been told, by HR personelle in government, that certain favoured groups are deemed to be superior to white men, even if the favoured party has no education nor experience and the latter have earned doctorates and decades of experience), but no one has said anything about such people being employed on the projects to which I referred. But this is an aspect of our present society that is bound to degenerate into a flame war, launched by the politically correct, so we ought to say little, or even leave it alone. Those in power tend to be vicious, especially when there are no effective checks on their conduct and no consequences for what they do. Cheers Ted ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] group by and aggregate functions on regular expressions
select count(*), address where address ~* 'magil' or address ~* 'whitewater' etc group by address would that work? Rhys Stewart wrote: Hi all, i have a table with an address column. I wanted to count the number of rows with a given regex match. so i ended up with the following very verbose query: select address ~* 'magil' as Magil , address ~* 'whitewater' as whitewater, (address ~* 'inswood' or address ~* 'innswood') as innswood, (address ~* 'eltham' AND address ~* 'view') as eltham_view, (address ~* 'eltham' AND address ~* 'acre') as eltham_acres, (address ~* 'eltham' AND address ~* 'vista') as eltham_vista, count(prem) from prem_info where address ~* 'magil' or (address ~* 'eltham' AND address ~* 'view') or (address ~* 'eltham' AND address ~* 'acre') or (address ~* 'eltham' AND address ~* 'vista') or address ~* 'whitewater' or (address ~* 'inswood' or address ~* 'innswood') and parish = 'SpanishTown' group by Magil, whitewater, innswood, eltham_view, eltham_acres,eltham_vista and i got this: magilwhitewaterinnswoodeltham_vieweltham_acres eltham_vistacount ftffff650 tfffff361 ffffft181 fffftf462 ffftff542 fftfff686 useful but not in the format that would be nice. so the question: is there any way to rewrite this query or are there any existing functions that would give me a tabular output like so: communitycount magil361 whitewater650 inswood686 eltham_view 542 etc.. ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] "oracle to postgresql" conversion
Kevin Hunter wrote:
On 06 Mar 2007 at 9:01a -0800, g.c[ altudela ] wrote:
I'm a newbie in Oracle and postgreSQL,
i'm need to translate the following script (in Oracle) to postgreSQL :
rem Autorisation des lignes vides :
set sqlbl on
rem Initialisation du timer :
set timing on
rem Creation de la table :
I would say you can remove these. rem is just a comment Which can be
changed to -- . The two set lines are session settings that you won't miss.
You may want to consider starting with your CREATE DATABASE command and
make sure you have a suitable ENCODING setting.
Start by adding -
CREATE SCHEMA "LEPAPE";
You may want to add "LEPAPE" to your search path so you don't need to
have it before everything.
http://www.postgresql.org/docs/8.2/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH
can explain that further.
CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,
CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))
Change VARCHAR2 to VARCHAR
Change NUMBER to NUMERIC
Change CHECK(measure to CHECK("MEASURE"
VARCHAR2 is an Oracle type that replaces VARCHAR. Not sure why but my
first guess would be it works with multibyte character sets which is why
I suggest checking your ENCODING before you start.
NUMBER is Oracle's version of NUMERIC - Oracle will use both but
probably only Oracle will use NUMBER.
The CHECK change is to do with identifiers which I point you to later.
You may want to bookmark http://www.ss64.com/orasyntax/datatypes.html
and http://www.postgresql.org/docs/8.2/interactive/datatype.html
as references for future scripts that you may stumble on.
I believe rem translates to '-- ' (the extra space is important)
set translates to '\set'
I do not know what the setting 'sqlbl' does in Oracle.
I'm not entirely sure about the owner bit, specified by "LEPAPE"."..."
The various data types translate to (likely) more standards compliant
names, which you can peruse at
http://www.postgresql.org/docs/8.2/static/datatype.html
(Replace 8.2 with your major version of PostgreSQL.)
Someone may correct me, but I believe that Postgres is not case
sensitive in (terms of column and constraint names) unless you create
them with quotes. Thus, you could just as easily write
"EXP_ID" VARCHAR2(16) NOT NULL,
as
exp_id VARCHAR(16) NOT NULL
I would say remove all the double quotes, but this will depend on the
other scripts that have selects and such - it may be easier to leave
them as is than to update all the scripts you have. If you are typing
from a book then that won't matter.
I am guessing that this is an Oracle tutorial that you have and you want
to use postgresql to work through it. This would be a good time to get
familiar with naming conventions, start by reading -
http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
This should help you to understand why part of the above table
definition should be changed to CHECK("MEASURE" and you may have some
similar fixes throughout your scripts.
which would be my personal preference as I like to capitalize SQL
keywords and leave everything as lower case. (Makes for easier reading
later.)
Kevin
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Shane Ambler
[EMAIL PROTECTED]
Get Sheeky @ http://Sheeky.Biz
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] OT: Canadian Tax Database
> Richard Huxton wrote: >> http://www.thestar.com/News/article/189175 >> >> "For instance, in some cases the field for the social insurance number >> was instead filled in with a birth date." >> >> Unbelievable. Sixty years of electronic computing, fifty years use in >> business and the "professionals" who built the tax system for a >> wealthy >> democratic country didn't use data types. > > This is Unbelievable? This is commonplace. > And due at least in part to government (and other institutions operated by damned fools) opting for the least expensive provider rather than paying for someone who actually knows what they're doing. Just as buying cheap junk always comes back to get you, hiring incompetent fools that don't know their ass from a hole in the ground will come back to get you too. Come on, they don't hire incompetent fools. The hire the people You CAN'T be serious! Have you ever dealt with them or with the consequences of their incompetence? they need to fill their quota regardless of how well trained and experienced they are. I am not saying that non white males are in any way less competent than white males, but by removing them from the pool does not make things better. The biggest problem with quotas is not hiring less qualified staff, it is that less qualified staff know why they were hired and know that they are very unlikely to be fired, so they have little incentive to work hard or attempt to do their best, they can always fail upwards. What does this have to do with anything? No one here, except you, has said anything about the profile of the people involved WRT race, gender, religion, &c. Nor has anyone said anything about "qualifications". The only thing that has been said is that, based on what is seen in the "work", the people responsible for that work must be incompetent. It is an inference based on what is seen in what has been done and has nothing to do with any of the prohibited grounds for discrimination used as excuses for affirmative action. And yes, I have seen cases where less qualified, even unqualified, people have been hired as a result of these affirmative action initiatives (and I have been told, by HR personelle in government, that certain favoured groups are deemed to be superior to white men, even if the favoured party has no education nor experience and the latter have earned doctorates and decades of experience), but no one has said anything about such people being employed on the projects to which I referred. But this is an aspect of our present society that is bound to degenerate into a flame war, launched by the politically correct, so we ought to say little, or even leave it alone. Those in power tend to be vicious, especially when there are no effective checks on their conduct and no consequences for what they do. Cheers Ted ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to force planner to use GiST index?
On Wed, Mar 07, 2007 at 04:00:14PM -0800, [EMAIL PROTECTED] wrote:
> Hi,
>
> I have a GiST index on st_geometry type (a user defined type). It looks
> like index is not getting hit when I use some geometric operator. Here
> is the example of st_contains operator.
I don't know whether you noticed, but a function call can never use an
index like that. Index scans *only* work with operators, not with
functions.
> EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
> a
> Where st_contains(st_geometry('polygon ((6221958 1949440, 6349378
> 1949440,
> 6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
> a.shape) = 1;
So no matter what you do, this can never be an index scan, because
there's no operator postgres can apply to the index...
If you really want to use function names also, I beleive you can make
in inline SQL function to convert from function form to operator form.
The query planner will only look to the index if there's an operator.
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
[GENERAL] group by and aggregate functions on regular expressions
Hi all, i have a table with an address column. I wanted to count the number of rows with a given regex match. so i ended up with the following very verbose query: select address ~* 'magil' as Magil , address ~* 'whitewater' as whitewater, (address ~* 'inswood' or address ~* 'innswood') as innswood, (address ~* 'eltham' AND address ~* 'view') as eltham_view, (address ~* 'eltham' AND address ~* 'acre') as eltham_acres, (address ~* 'eltham' AND address ~* 'vista') as eltham_vista, count(prem) from prem_info where address ~* 'magil' or (address ~* 'eltham' AND address ~* 'view') or (address ~* 'eltham' AND address ~* 'acre') or (address ~* 'eltham' AND address ~* 'vista') or address ~* 'whitewater' or (address ~* 'inswood' or address ~* 'innswood') and parish = 'SpanishTown' group by Magil, whitewater, innswood, eltham_view, eltham_acres,eltham_vista and i got this: magil whitewater innswoodeltham_view eltham_acres eltham_vistacount f t f f f f 650 t f f f f f 361 f f f f f t 181 f f f f t f 462 f f f t f f 542 f f t f f f 686 useful but not in the format that would be nice. so the question: is there any way to rewrite this query or are there any existing functions that would give me a tabular output like so: communitycount magil361 whitewater650 inswood686 eltham_view 542 etc.. ---(end of broadcast)--- TIP 1: 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: [GENERAL] Database slowness -- my design, hardware, or both?
> LOG: statement: UPDATE Transactions > SET previous_value = previous_value(id) > WHERE new_value IS NOT NULL > AND new_value <> '' > AND node_id IN (SELECT node_id FROM NodeSegments) > LOG: duration: 16687993.067 ms I hope that I can presume some suggestions that I gleened after finishing my celko book. I don't know if the suggestions presented will help in your case. >From the reading WHERE conditions such as <> '' or IS NOT NULL can be >preformance killers as these may discourge the optimizer from using an index scan. The suggest was to replace this with: new_value > '' this WHERE conditions should only find non-NULL and non-empty strings. Also, the IN is also know as a killer so the suggestion was to reform the query like so: UPDATE Transactions SET previous_value = previous_value(id) FROM NodeSegments WHERE Transactions.Node_id = NodeSegments.Node_id AND Transactions.new_value > 'A'; --assuming A is a minimum value I hope this can help. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: 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: [GENERAL] vacuum error
On Wed, Mar 07, 2007 at 07:07:45PM -0700, Ed L. wrote: > How would I go about correctly creating the missing file? That > sounds appealing, as if it were something I could do without > taking downtime. Is it? Depends if it's because the file got deleted prematurly, or because it's the result of corruption in the table. If it's just that it got deleted prematurely, you should just be able to create the file with 256KB of zeros. > In reviewing the release notes between 8.1.2 and 8.1.8, there are > a number of vacuum fixes along with many others. It is unclear > if any of them are related. There definitly was a bug related to this and you should upgrade to the latest minor release anyway, because it will probably bite you again. > Given this error is occurring while doing a "VACUUM FREEZE" of > the template0 database, I wonder if that creates any options for > me? Frankly, I'd be happy to simply drop the template0 DB if I > could, since I'm not aware that we ever need it for anything. You can drop and recreate the template0 database, instructions are somewhere. I don't think you can do without because I beleive pg_dump needs it. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] OT: Canadian Tax Database
- Original Message - From: "Alan Hodgson" <[EMAIL PROTECTED]> To: Sent: Thursday, March 08, 2007 11:32 AM Subject: Re: [GENERAL] OT: Canadian Tax Database On Thursday 08 March 2007 08:15, "Ted Byers" <[EMAIL PROTECTED]> wrote: They would have satisfied the terms of their contract if, after a few years, and hundreds of man-years, they walked away without delivering anything. That tragedy cost Canada hundreds of millions, if not billions, of dollars It didn't happen to be a gun owners' registry, perhaps? (fellow Canadians will understand :) No. This predated that fiasco by more than ten years. In fact, had it been done right, it would have been a much much larger project than the registry. Ted ---(end of broadcast)--- TIP 1: 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: [GENERAL] Tabulate data incrementally
Thanks Alvaro. That's good to know. Actually I was spacing on the need
for this. The date_trunc function with group by actually works for me.
select sum(amount), date_trunc('week', period_end) as dt from time_data
group by dt;
Alvaro Herrera wrote:
Omar Eljumaily wrote:
Thanks Tom and Richard for the tip on date_trunc. Is it possible in an
sql select statement to create an iterator?
Yes, use the generate_series() function.
---(end of broadcast)---
TIP 1: 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: [GENERAL] "oracle to postgresql" conversion
On Thursday 08 March 2007 11:40:21 am Kevin Hunter wrote: > I do not know what the setting 'sqlbl' does in Oracle. SET SQLBLANKLINES ON makes sqlplus not choke on empty lines in your input. Don't get me started. jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] security permissions for functions
Rikard Pavelic <[EMAIL PROTECTED]> writes: > Is this a bug or something? No, it's operating as designed. Per the GRANT reference page: : Depending on the type of object, the initial default privileges may : include granting some privileges to PUBLIC. The default is no public : access for tables, schemas, and tablespaces; CONNECT privilege and TEMP : table creation privilege for databases; EXECUTE privilege for functions; : and USAGE privilege for languages. The object owner may of course revoke : these privileges. (For maximum security, issue the REVOKE in the same : transaction that creates the object; then there is no window in which : another user may use the object.) You'll need to revoke the default public EXECUTE privilege on any functions you don't want to be callable. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tabulate data incrementally
Omar Eljumaily wrote: > Thanks Tom and Richard for the tip on date_trunc. Is it possible in an > sql select statement to create an iterator? Yes, use the generate_series() function. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] security permissions for functions
Hi! How can I assign execute permission to a role for a single function inside schema. For example I create schema example; function example.simple_select() and user test_user; If I grant usage on schema example to user test_user as GRANT USAGE ON SCHEMA example TO test_user; I can do select * from example.simple_select(); But I don't want this user to be able to select from other functions, so I want something like GRANT EXECUTE ON FUNCTION example.simple_select() TO test_user; but unless the user has usage rights on schema example he can't select from this function; I'm using SECURITY DEFINER so that user can execute function without permissions for single commands. Is this a bug or something? because grant usage on schema doesn't assume select rights on views and tables, but it does on functions. Regards, Rikard ---(end of broadcast)--- TIP 1: 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: [GENERAL] "oracle to postgresql" conversion
On 06 Mar 2007 at 9:01a -0800, g.c[ altudela ] wrote:
I'm a newbie in Oracle and postgreSQL,
i'm need to translate the following script (in Oracle) to postgreSQL :
rem Autorisation des lignes vides :
set sqlbl on
rem Initialisation du timer :
set timing on
rem Creation de la table :
CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,
CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))
I believe rem translates to '-- ' (the extra space is important)
set translates to '\set'
I do not know what the setting 'sqlbl' does in Oracle.
I'm not entirely sure about the owner bit, specified by "LEPAPE"."..."
The various data types translate to (likely) more standards compliant
names, which you can peruse at
http://www.postgresql.org/docs/8.2/static/datatype.html
(Replace 8.2 with your major version of PostgreSQL.)
Someone may correct me, but I believe that Postgres is not case
sensitive in (terms of column and constraint names) unless you create
them with quotes. Thus, you could just as easily write
"EXP_ID" VARCHAR2(16) NOT NULL,
as
exp_id VARCHAR(16) NOT NULL
which would be my personal preference as I like to capitalize SQL
keywords and leave everything as lower case. (Makes for easier reading
later.)
Kevin
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Tabulate data incrementally
Thanks Tom and Richard for the tip on date_trunc. Is it possible in an
sql select statement to create an iterator?
For instance
select myItFunc(1,10);
would give 1,2,3,4,5,6,7,8,9,10
I'm a bit embarrassed that I don't know how to do this. My
understanding of sql functions is that not being object oriented, they
don't store state.
The reason I'm asking is that if I wanted to to use date_trunc, I think
I would need some sort of iterator to get multiple rows in one statement.
What I'm looking for is:
Employee Week Amount
John1/1 100
Mary1/1 0
Edward 1/2 100
etc
I'd also like to return zero or null values when the data doesn't
exist. Wouldn't I need an iterator to do that?
Thanks,
Omar
Tom Lane wrote:
Omar Eljumaily <[EMAIL PROTECTED]> writes:
I want to tabulate time data on a weekly basis, but my data is entered
on a daily basis.
Something involving GROUP BY date_trunc('week', _date) might work for
you, if your definition of week boundaries matches date_trunc's.
If not, you could probably make a custom function that breaks at the
boundaries you want.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] OT: Canadian Tax Database
On Thursday 08 March 2007 08:15, "Ted Byers" <[EMAIL PROTECTED]> wrote: > They would have satisfied the terms of their contract > if, after a few years, and hundreds of man-years, they walked away > without delivering anything. That tragedy cost Canada hundreds of > millions, if not billions, of dollars It didn't happen to be a gun owners' registry, perhaps? (fellow Canadians will understand :) -- Opportunity is missed by most people because it is dressed in overalls and looks like work. - Thomas Edison ---(end of broadcast)--- TIP 1: 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: [GENERAL] OT: Canadian Tax Database
On Thu, 2007-03-08 at 09:15 -0500, Ted Byers wrote: > - Original Message - > From: "Joshua D. Drake" <[EMAIL PROTECTED]> > To: "Richard Huxton" > Cc: > Sent: Thursday, March 08, 2007 8:00 AM > Subject: Re: [GENERAL] OT: Canadian Tax Database > > > > Richard Huxton wrote: > >> http://www.thestar.com/News/article/189175 > >> > >> "For instance, in some cases the field for the social insurance number > >> was instead filled in with a birth date." > >> > >> Unbelievable. Sixty years of electronic computing, fifty years use in > >> business and the "professionals" who built the tax system for a wealthy > >> democratic country didn't use data types. > > > > This is Unbelievable? This is commonplace. > > > And due at least in part to government (and other institutions operated by > damned fools) opting for the least expensive provider rather than paying for > someone who actually knows what they're doing. Just as buying cheap junk > always comes back to get you, hiring incompetent fools that don't know their > ass from a hole in the ground will come back to get you too. > Come on, they don't hire incompetent fools. The hire the people they need to fill their quota regardless of how well trained and experienced they are. I am not saying that non white males are in any way less competent than white males, but by removing them from the pool does not make things better. The biggest problem with quotas is not hiring less qualified staff, it is that less qualified staff know why they were hired and know that they are very unlikely to be fired, so they have little incentive to work hard or attempt to do their best, they can always fail upwards. ...snip... -- Guy Fraser Network Administrator The Internet Centre 1-888-450-6787 (780)450-6787 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] OT: Canadian Tax Database
And due at least in part to government (and other institutions operated by damned fools) opting for the least expensive provider rather than paying for someone who actually knows what they're doing. Just as buying cheap junk always comes back to get you, hiring incompetent fools that don't know their ass from a hole in the ground will come back to get you too. What you describe is a hundred times better than the reality... most of them actually get _expensive_ junk with some kick-back ;-) I concede. You're right. I recall being told by one project manager I knew years ago who had an opportunity to create a bid for an RFP issued by Transport Canada (long long ago). He refuse, so his employer prepared the bid. He refused because the RFP was a joke. There were absolutely no functional requirements, nor non-functional requirements, identified in the RFP. His company didn't get the contract, but being a bidder they did see the winning bid. It was just as ludicrous! It, too, failed to identify any requirements, and so it did not actually promise to deliver anything, working or not! They would have satisfied the terms of their contract if, after a few years, and hundreds of man-years, they walked away without delivering anything. That tragedy cost Canada hundreds of millions, if not billions, of dollars (I don't know if any final accounting was ever done - that would be opposed by the "civil servants" responsible lest they should be criticised for their incompetence), and ultimately nothing was delivered because the next elected government cancelled the project, refusing to through more money into their opposition's money pit - they prefer, of course, to through it into money pits created by their political supporters. The decisions to create the project, and to cancel it, were political, but the incompetence really responsible for it was lower done within the ranks of the civil service. The project could have delivered something good had the civil servants involved been competent! Similar nonsense happened with the firearms registry. For most of the early history of it, the software systems used where so bad, and inappropriate, that the clerks that had to use it could have been ten times more productive if they had the use of properly designed and implemented software. I can not understand how it became so outrageously expensive when the real needs for it were so simple (relative to products I have worked on). I'll bet real, genuinely capable, software engineers could have delivered a gold and platinum plated product for just a few million dollars; nothing really relative to what it ended up costing us. I know contractors that refuse to do business with the government because of this sort of nonsense, and I know, from discussions with ex-civil servants, that such incompetence is the norm in government. I know engineers who have been burned by government by investing a fortune in new products or services, and then educating relevant civil servants WRT to the new science or technology involved, only to find these same civil servants give contracts to provide the new product or service to incompetent bozos who didn't know the first thing about it. They just happened to be cheaper. Why waste time and money developing a product or service that is really relevant only to government when the risk of such unethical conduct by government is so high? I don't support anyone out there can describe a project or three where things were done right, to provide a cure for the depressing and discouraging nature of what this thread has turned out to be? Cheers Ted ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query ... returned 4 columns
Sorin Schwimmer wrote: Hi All, I'm trying to write a stored PLPG/SQL procedure: o:= * FROM expected_stuff WHERE packslip=$1; -- LIMIT 1; Is this valid syntax? I'm a little surprised, but I think I can see what's happening. Try something more like: SELECT * INTO o FROM expected_stuff... See if that makes a difference -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] which is more scalable for the database?
Timasmith wrote: Suppose I have a database table with 20 fields which are lookups to a single table. configtable(configtable_id, a_field, something_lookup_id, another_lookup_id, ...) lookup(lookup_id, value, description, ...) what is going to be faster to map the rows to an Object which needs the 'value' for every field ending in lookup_id [snip] b) Cache all lookup values and populate It seems that the latter *might* be better to scale outward better, as you could add application servers to do the caching/mapping and you only select from a single table? It *might* scale better and be faster. It might not. The only way to know is to test it. When you do compare, don't forget to allow for handling changes to the lookup table in the database. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] foreign key support for inheritance
Floyd Shackelford wrote: i would like to suggest the following modification to the foreign key syntax (and underlying implementation) to support table inheritance: The place to look for all these things is: http://www.postgresql.org/docs/faqs.TODO.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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: [GENERAL] DB Modeler
2007/3/8, Raymond O'Donnell <[EMAIL PROTECTED]>: On 08/03/2007 12:32, Hakan Kocaman wrote: > i work with Clay in Eclipse: > http://www.azzurri.jp/en/software/clay/ I use Clay also, from time to time. The only downside is that you can't print from the free version, and the commercial version isn't available outside Japan. Ditto. Cheers, t.n.a. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] query ... returned 4 columns
Sorin Schwimmer <[EMAIL PROTECTED]> writes: > DECLARE > o expected_stuff%ROWTYPE; > BEGIN > o:= * FROM expected_stuff WHERE packslip=$1; -- Use SELECT * INTO o FROM expected_stuff WHERE ... The assignment syntax is currently only supported for scalar values, I believe. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Database deadlock/hanging
On Wed, 07 Mar 2007 09:27:04 -0800 Reece Hart <[EMAIL PROTECTED]> wrote: > On Wed, 2007-03-07 at 10:08 -0600, John Gateley wrote: > > I'm guessing it's something related to table locks. > ... > > Any pointers on what I should be looking for to prevent this from > > happening again? What information I should be tracking to figure > > out what is exactly happening? > > Your inserts almost certainly have a table or index exclusively locked > and thereby causing a backlog of selects. Thanks. It turns out it was my nightly vacuuming of the database. I had the full option set, and I had added a large table, and I think it was just trying to finish the vacuum (it took about 15 minutes to do a vacuumdb -az, would several hours be reasonable for afz? Or maybe there was some deadlock with table access?) Moral - full isn't always better (and if I RTFM, I would have known that...) j ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Re: RFC tool to support development / operations work with slony replicated databases
Andrew Hammond wrote: > Hello All, > > I've been working on designing a tool to facilitate both developers > and operations staff working with slony replicated databases. I think > that the problem described below is a general problem for people > working with systems that are both in production and under on-going > development / maintenance. As a result I would like to both solicit > the input of the community and share the results. Documentation (which > is still somewhat drafty) follows. Andrew, I think this is a useful idea, that I would be interested in trying myself. One suggestion: It would be great if there was an option for the "downgrade" path to be determined automatically, or ignored as option. I think in some scenarios, it's just not very practical to "go back" without restoring from a dump file of the old state. Perhaps there could be some integration with a "diff" tool like APG? http://pgfoundry.org/projects/apgdiff Given two databases schemas, it could generate /both/ upgrade and downgrade paths. That would work for some simple cases, and manual changes could be used for more complex cases, which as were data needs to be changed and changed back as part of an upgrade/downgrade. Thanks again for your work on this tool! Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database slowness -- my design, hardware, or both?
Hi, Webb Sprague. You wrote: Do you have new \timings? Yup. It just finished executing a little while ago. With the explicitly interpolated array in place, I got the following: LOG: statement: UPDATE Transactions SET previous_value = previous_value(id) WHERE new_value IS NOT NULL AND new_value <> '' AND node_id IN (351, 169, 664, 240) LOG: duration: 16842710.469 ms The previous version, which included lots of calls to RAISE NOTICE and also used a subselect, had the following timing: LOG: statement: UPDATE Transactions SET previous_value = previous_value(id) WHERE new_value IS NOT NULL AND new_value <> '' AND node_id IN (SELECT node_id FROM NodeSegments) LOG: duration: 16687993.067 ms (I keep timing information in the logfile, rather than using \timing.) So it looks like this didn't make much of a timing difference at all. And what little difference there was, was negative. Bleah. What you or I think is a minor change isn't necessarily what the planner thinks is a minor change, especially when you change data from something that requires a query to something that is determinate. I would suggest changing your function to remove as many such queries as possible too (I am thinking of the order by limit 1). This would be a good move also in that you narrow down the amount of moving parts to diagnose and it just makes the whole thing cleaner. Good idea. I'll see if I can get the function to be a bit cleaner, although I'm not sure if it is, given the constraints of the problem. That's one of the reasons why I've been adding these "hints" to the database -- so that I can have many small queries, rather than one large one. I would also try amortizing the analysis with triggers, rather than building the table all at once; this may be better or worse, depending on the on-line character of the application (e.g., if they are waiting at an ATM, in might be a deal breaker to add two seconds to do an insert / update, but not if you are tailing off a log file that gets updated every minute or so.) The data that I'm dealing with is not changing over time. So I've been trying to build the transactions table (aka my data warehouse) slowly, adding one or two columns at a time with hints that will make extracting the data easier. Unfortunately, building those hints has proven to be very slow going. Reuven
[GENERAL] which is more scalable for the database?
Suppose I have a database table with 20 fields which are lookups to a single table. configtable(configtable_id, a_field, something_lookup_id, another_lookup_id, ...) lookup(lookup_id, value, description, ...) what is going to be faster to map the rows to an Object which needs the 'value' for every field ending in lookup_id a) View select c.configtable_id, l1.value as something_lookup, l2.value as another_lookup from configtable c, lookup l1, lookup l2 where c.something_lookup_id = l1.lookup_id and c.another_lookup_id = l2.lookup_id foreach row map values to object end b) Cache all lookup values and populate select c.* from configtable foreach row map values to object if lookup_id find value from hashtable and map value to object endif end It seems that the latter *might* be better to scale outward better, as you could add application servers to do the caching/mapping and you only select from a single table? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Determine users and roles
I want to determine the usernames, privileges and roles of the users that are created for a particular database. I am using PGSQL 8.2.3 on Windows XP. I want to run SQL query for this. -- View this message in context: http://www.nabble.com/Determine-users-and-roles-tf3360480.html#a9347822 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] No buffer space available
On Mar 6, 2:20 pm, [EMAIL PROTECTED] ("Andrej Ricnik-Bay")
wrote:
> On 3/7/07, andyk <[EMAIL PROTECTED]> wrote:
>
> > 1) try to use client software, which is not depends on libpq,
> > or
> > 2) divide your query into smaller parts,
> > or
> > 3) use software which parses long queries, separates them into single
> > SQL commands and then executes this commands one by one.
>
> 4) use Postgres on a Unix-type operating system...
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
Thanks for the suggestions. I chose to divide the query into two
different sets of inserts. This seems to work.
Now I wonder why is it that I started getting WSANOBUFS errors from
Windows Socket System if this same setup with the same machines has
been operational for over a year? The query size did not change and
the number of connections to the server did not change. Is there any
good reason for this error to start showing up?
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] java.sql.SQLException: The connection attempt failed
Hello, I'm using postgresql 8.0.8 and I get java.sql.SQLException: The connection attempt failed when I run the jar file. On my computer I am able to run the program from netbeans and the jar file in the dist folder. However, when I send the dist folder to another computer and run the jar file the error above is thrown. I've recently bought a certificate and installed it(before creating the jar file). Might this be the cause? But what I can't figure out is why I can run on my and not on another. Anyone have a clue? Thanks. vyang
[GENERAL] "oracle to postgresql" conversion
I'm a newbie in Oracle and postgreSQL,
i'm need to translate the following script (in Oracle) to postgreSQL :
rem Autorisation des lignes vides :
set sqlbl on
rem Initialisation du timer :
set timing on
rem Creation de la table :
CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,
CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))
Thanks!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] query ... returned 4 columns
Hi All,
I'm trying to write a stored PLPG/SQL procedure:
CREATE OR REPLACE FUNCTION
arch_expected_stuff(CHAR(12)) RETURNS VOID
AS $$
-- Archives expected_stuff
-- takes packing slip
DECLARE
o expected_stuff%ROWTYPE;
BEGIN
o:= * FROM expected_stuff WHERE packslip=$1; --
LIMIT 1;
INSERT INTO archive.expected_stuff VALUES (o);
DELETE FROM expected_stuff WHERE packslip=$1;
END;
$$ LANGUAGE PLPGSQL;
When I issue a
select arch_expected_stuff('246');
I receive the following error:ERROR: query "SELECT *
FROM expected_stuff WHERE packslip= $1 LIMIT 1"
returned 4 columns
CONTEXT: PL/pgSQL function "arch_expected_stuff" line
6 at assignment
Yes, both expected_stuff and archive.expected_stuff
have 4 columns. What is the error?
Thanks for your help,
Sorin
The fish are biting.
Get more visitors on your site using Yahoo! Search Marketing.
http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] DB Modeler
I use case studio Thorsten Kraus a écrit : Hi, which tools do you use for modelling your databases? I need a tool with a graphical interface where I can create tables and relations. The tool should also be able to create the DDL for Postgres. Thanks, Thorsten ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] foreign key support for inheritance
i would like to suggest the following modification to the foreign key syntax (and underlying implementation) to support table inheritance: alter table [only] foo add foreign key (a_field) references [only] bar (another_field) on delete restrict; the only changes from the current syntax are the "[only]" modifiers (pardon the pun) to the source and target tables. this modifier would behave the same was as the "[only]" modifier in the "from [only] foo_table" clause. i really, really need foreign keys to support table inheritance. right now, i have a script i run that generates 1000's of alter table commands because i have to take into account not only the target table's inheritance but the source table's inheritance too and all the combinations of inheriting tables between them. postgresql developers: please treat this as an enhancement request. i think this would be a very popular enhancement. -- Soli Deo gloria, Floyd Shackelford ---(end of broadcast)--- TIP 1: 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: [GENERAL] Importing *huge* mysql database into pgsql
In article <[EMAIL PROTECTED]>, ".ep" <[EMAIL PROTECTED]> writes: > Hello, > I would like to convert a mysql database with 5 million records and > growing, to a pgsql database. > All the stuff I have come across on the net has things like > "mysqldump" and "psql -f", which sounds like I will be sitting forever > getting this to work. > Is there anything else? If you really want to convert a *huge* MySQL database (and not your tiny 5M record thingie), I'd suggest "mysqldump -T". This creates for each table an .sql file containing just the DDL, and a .txt file containing the data. Then edit all .sql files: * Fix type and index definitions etc. * Append a "COPY thistbl FROM 'thispath/thistbl.txt';" Then run all .sql files with psql, in an order dictated by foreign keys. ---(end of broadcast)--- TIP 1: 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: [GENERAL] How to use pg_resetxlog if data directory is in c:/Program Files ?
Try using something like this --> "Program Files/PostgreSQL/data" (that is with quotes for using spaces) -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/8/07, Steven De Vriendt <[EMAIL PROTECTED]> wrote: Hi all, Got this kind of silly question. I'm trying to use pg_resetxlog. Problem is the path to c:/Program Files//data doesn't get reached, probably (space in between). How can I resolve this in order to run this command ? Regards, Steven ---(end of broadcast)--- TIP 1: 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: [GENERAL] Tabulate data incrementally
Omar Eljumaily wrote:
I want to tabulate time data on a weekly basis, but my data is entered
on a daily basis.
create table time_data
{
employee varchar(10),
_date date,
job varchar(10),
amount
}
So I want to tabulate with a single sql command. Is that possible?
Try one of these:
=> SELECT date_trunc('week',now());
date_trunc
2007-03-05 00:00:00+00
=> SELECT extract(week from now());
date_part
---
10
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tabulate data incrementally
Omar Eljumaily <[EMAIL PROTECTED]> writes:
> I want to tabulate time data on a weekly basis, but my data is entered
> on a daily basis.
Something involving GROUP BY date_trunc('week', _date) might work for
you, if your definition of week boundaries matches date_trunc's.
If not, you could probably make a custom function that breaks at the
boundaries you want.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] OT: Canadian Tax Database
> And due at least in part to government (and other institutions operated by > damned fools) opting for the least expensive provider rather than paying for > someone who actually knows what they're doing. Just as buying cheap junk > always comes back to get you, hiring incompetent fools that don't know their > ass from a hole in the ground will come back to get you too. What you describe is a hundred times better than the reality... most of them actually get _expensive_ junk with some kick-back ;-) Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Tabulate data incrementally
I want to tabulate time data on a weekly basis, but my data is entered
on a daily basis.
create table time_data
{
employee varchar(10),
_date date,
job varchar(10),
amount
}
So I want to tabulate with a single sql command. Is that possible?
If I had a separate week end table
create table week_ends
{
end_date date
}
I could do something like.
select *, (select sum(amount) from time_data where _date > end_date - 7
and _data <= end_date) from week_ends;
But the week_end table would be a pain to manage for a number of
reasons. Is it possible to do this without the week_end table?
Thanks.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] OT: Canadian Tax Database
- Original Message - From: "Joshua D. Drake" <[EMAIL PROTECTED]> To: "Richard Huxton" Cc: Sent: Thursday, March 08, 2007 8:00 AM Subject: Re: [GENERAL] OT: Canadian Tax Database Richard Huxton wrote: http://www.thestar.com/News/article/189175 "For instance, in some cases the field for the social insurance number was instead filled in with a birth date." Unbelievable. Sixty years of electronic computing, fifty years use in business and the "professionals" who built the tax system for a wealthy democratic country didn't use data types. This is Unbelievable? This is commonplace. And due at least in part to government (and other institutions operated by damned fools) opting for the least expensive provider rather than paying for someone who actually knows what they're doing. Just as buying cheap junk always comes back to get you, hiring incompetent fools that don't know their ass from a hole in the ground will come back to get you too. This time CRA is embarrassed, but they don't care because the people that suffer are the taxpayers who ultimately paid for such shoddy work in the first place. There's no consequences for the bureaucratic peons really responsible for it. They probably even get paid obscene sums in overtime for the time they spend fixing the problem. More annoying, for me, are the scurrilous scoundrels that pass themselves off as competent software consultants who take advantage of such incompetence in their clients' staff. I couldn't begin to document all the cases I have seen where either the wrong software was used (imagine a spreadsheet being used as an RDBMS) or the right software was grossly abused (imagine forcing a data entry clerk to enter the same data four times because the developer was too damned lazy or incompetent to develop a simple form to collect the data once and then submit it to the four externally owned databases that needed to be queried using it, and then having to manually collate the results returned from the queries). And then businesses operated by capable folk get burned by such incompetent and unethical scoundrels and swear off custom software because they'd rather have a COTS product that gives a 80% fit than try for a 100% fit with a custom product that in the end doesn't work at all. I have been told by some of these folk that they have found it virtually impossible to find capable software developers. This is because these scoundrels I mention outnumber capable developers by several orders of magnitude (and the current state of the curricula at colleges 'training' programmers doesn't help). It is s easy to get cynical, and very discouraged, when I think about this. :-( Maybe I should have myself lobotomized and become one of the mindless grunts at Canada post. Cheers Ted ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] OT: Canadian Tax Database
Joshua D. Drake wrote: Richard Huxton wrote: http://www.thestar.com/News/article/189175 "For instance, in some cases the field for the social insurance number was instead filled in with a birth date." Unbelievable. Sixty years of electronic computing, fifty years use in business and the "professionals" who built the tax system for a wealthy democratic country didn't use data types. This is Unbelievable? This is commonplace. I need to start adding 10% on my fees for things like "use of types". I would put some foreign-key constraints in my current project, but I don't think the client can afford it ;-) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] OT: Canadian Tax Database
Richard Huxton wrote: http://www.thestar.com/News/article/189175 "For instance, in some cases the field for the social insurance number was instead filled in with a birth date." Unbelievable. Sixty years of electronic computing, fifty years use in business and the "professionals" who built the tax system for a wealthy democratic country didn't use data types. This is Unbelievable? This is commonplace. Joshua D. Drake ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] DB Modeler
On 08/03/2007 12:32, Hakan Kocaman wrote: i work with Clay in Eclipse: http://www.azzurri.jp/en/software/clay/ I use Clay also, from time to time. The only downside is that you can't print from the free version, and the commercial version isn't available outside Japan. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] DB Modeler
Hi, > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Thorsten Kraus > Sent: Thursday, March 08, 2007 1:12 PM > To: postgres general > Subject: [GENERAL] DB Modeler > > > Hi, > > which tools do you use for modelling your databases? I need a > tool with > a graphical interface where I can create tables and > relations. The tool > should also be able to create the DDL for Postgres. > > Thanks, > Thorsten i work with Clay in Eclipse: http://www.azzurri.jp/en/software/clay/ Good Luck Hakan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] DB Modeler
Thorsten Kraus schrieb: Hi, which tools do you use for modelling your databases? I need a tool with a graphical interface where I can create tables and relations. The tool should also be able to create the DDL for Postgres. What do you mean by "also" what else should it create? ;) On Windows or with wine you could use casestudio, which is now bought by Quest Software but still support Postgresql along other databases. I also had success with dia and dia2sql tools. Regards Tino ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] DB Modeler
Hi, which tools do you use for modelling your databases? I need a tool with a graphical interface where I can create tables and relations. The tool should also be able to create the DDL for Postgres. Thanks, Thorsten ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] sql formatter/beautifier
If you use pgAdmin3 for example, it will format the definition in the database for you. This will exclude stored procedures, which are stored as-is. The formatter isn't too good however... There are better products on the market that will do the formatting significantly better. This one was nice: http://psti.equinoxbase.com/cgi-bin/handler.pl - Joris >-Original Message- >From: Aaron Bingham [mailto:[EMAIL PROTECTED] >Sent: donderdag 8 maart 2007 11:36 >To: Joris Dobbelsteen >Cc: Merlin Moncure; postgres general >Subject: Re: [GENERAL] sql formatter/beautifier > >Joris Dobbelsteen wrote: >> PostGreSQL (7.4 and onward) has such a thing build-in, but its not >> particulary good (simple case works, but once it gets >complex it makes >> a mess out of it). >Hi, > >Cleaning out my pgsql-general mail, I ran across your post. >How do I invoke PostgreSQL's built-in SQL beautifier? I >wasn't able to find a reference to this feature in the documentation. > >Thanks, > >-- > >Aaron Bingham >Senior Software Engineer >Cenix BioScience GmbH > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] sql formatter/beautifier
Joris Dobbelsteen wrote: PostGreSQL (7.4 and onward) has such a thing build-in, but its not particulary good (simple case works, but once it gets complex it makes a mess out of it). Hi, Cleaning out my pgsql-general mail, I ran across your post. How do I invoke PostgreSQL's built-in SQL beautifier? I wasn't able to find a reference to this feature in the documentation. Thanks, -- Aaron Bingham Senior Software Engineer Cenix BioScience GmbH ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] OT: Canadian Tax Database
http://www.thestar.com/News/article/189175 "For instance, in some cases the field for the social insurance number was instead filled in with a birth date." Unbelievable. Sixty years of electronic computing, fifty years use in business and the "professionals" who built the tax system for a wealthy democratic country didn't use data types. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Real world performance improvements in 8,2
CAJ CAJ wrote: Does anyone have benchmarks for performance gains by upgrading to 8.2.x from earlier postgres versions say 8.0.x/8.1.x? It will depend entirely on your usage patterns, I'm afraid. In general, every new version gets a little faster than the last, and there will be corner cases where you see rapid improvement. Have a look at the release notes and see if anything there looks like something you do a lot of: http://www.postgresql.org/docs/8.2/static/release-8-2.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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: [GENERAL] Query help
Madison Kelly wrote: Hi all, I've got a query that looks through a table I use for my little search engine. It's something of a reverse-index but not quite, where a proper reverse index would have 'word | doc1, doc3, doc4, doc7' showing all the docs the keyword is in, mine has an entry for eac I've got a query like: SELECT sch_id, sch_for_table, sch_ref_id, sch_instances FROM search_index WHERE (sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%') AND sch_for_table!='client' AND ... (more restrictions) ORDER BY sch_instances DESC; This returns references to a data column (sch_ref_id) in a given table (sch_for_table) for each matched keyword. The problem I am having is that two keywords might reference the same table/column which would, in turn, give me two+ search results pointing to the same entry. What I would like to do is, when two or more results match the same 'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the 'sch_instances' column is the number of times the given keyword is found in the table/column. I'd like to add up the number in the duplicate results (to give it a higher accuracy and move it up the search results). You'll want something like: SELECT sch_id, sch_for_table, sch_ref_id, SUM(sch_instances) AS tot_instances ... GROUP BY sch_id, sch_for_table, sch_ref_id ORDER BY tot_instances DESC; The key word to search the manuals on is "aggregates" (sum(), count() etc). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] GIST index on geometry+integer
Oleg Bartunov a écrit : Try install contrib/btree_gist Thanks a lot ! Regards -- Arnaud ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] GIST index on geometry+integer
Try install contrib/btree_gist On Thu, 8 Mar 2007, Arnaud Lesauvage wrote: Hi list ! I would like to test clustering on a multicolumn GIST index. The first column is a PostGIS-geometry field, the second column is a smallint field. When I try to create the index, I have an error about GIST not being available for smallint datatype. How can I create this operator class ? Thanks for your help ! Regards -- Arnaud ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] GIST index on geometry+integer
Hi list ! I would like to test clustering on a multicolumn GIST index. The first column is a PostGIS-geometry field, the second column is a smallint field. When I try to create the index, I have an error about GIST not being available for smallint datatype. How can I create this operator class ? Thanks for your help ! Regards -- Arnaud ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Real world performance improvements in 8,2
Does anyone have benchmarks for performance gains by upgrading to 8.2.x from earlier postgres versions say 8.0.x/8.1.x? Thanks!
