[GENERAL] Followup to week truncation thread
A few days ago there was a thread dealing with how a 'week' breaks.
I just had a need to run a transaction total by week and I wanted
a Sunday-Saturday week, not a Monday-Sunday week which is what the
date_trunc function gives.
Here was my solution:
select (date_trunc('week',mtrantime + interval '1 day')
- interval '1 day')::date as week,
count(*) as tot from trantable group by 1 order by 1;
--
Mike Nolan
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Duplicate Values or Not?!
> I don't know if it's guarenteed by spec, but it certainly seems silly > for strings to compare equal when they're not. Just because a locale > sorts ignoring case doesn't mean that "sun" and "Sun" are the same. The > only real sensible rule is that strcoll should return 0 only if strcmp > would also return zero... I disagree. Someone who wants true case independence (for whatever reason) needs all aspects of uniqueness such as selects, indexes and groups treating data the same way. This needs to be something the person who creates the instance or the database can control. -- Mike Nolan ---(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] Data Entry Tool for PostgreSQL
> We have a web application using PHP, Linux and PostgreSQL. We need a > simple data entry tool to allow non-IT people to edit data in our database > through a web-browser. Are there any free or commercial data management > tools out there, not database management tools like pgAdmin. I have been working on a general purpose table-driven web-based database query/update tool that I hope to release into the open source community some day. (I've been calling it PostBrowse, as far as I can tell nobody else is using that name yet.) It doesn't handle every data type in postgres, but it'll handle most of them, including limited support for arrays, and it will also support having data in the form of radio boxes, check boxes and pulldown lists. (It doesn't support style sheets yet, but I'm thinking about ways to add that.) It's been in use at a client's office since March of 2004, so it's pretty robust, though it needs some major cleanup work and doesn't have a lot of documentation yet. Contact me about your specific needs, if you're willing to be a pre-beta tester, I may be able to put something together for you. -- Mike Nolan Tailored Software Service, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Data Entry Tool for PostgreSQL
> I would have thought a php appliction would be able to this fairly > easily. My data entry scripts insert the new records with just a primary > key, then iterate through the various fields using an update sql for each > one which is not null. A generalized program to do this for (nearly) any table is not a trivial task. For a start you have to deal with knowing the difference between an insert and an update, perhaps provide some kind of record-locking scheme so it works in multi-user mode, do something about record keys (whether using OIDs or some other unique single field), and deal with quotes and other characters that cause problems for either web pages or SQL statements. Things like supporting a variety of search features, data type checking (eg, making sure that a date or an integer is valid BEFORE trying an insert/update), lookups on related data (for example, displaying the name from a customer record when the ID appears in an order record), user passwords, data access security levels, data formatting, etc. all add complexity. The main program I've been working on for about two years now is nearly 3200 lines long at this point. It has about 95% of the items on my original wish list of features. It's been in use at a client's office since March of 2004 and is used to maintain their database of over 600,000 members, among other things. Could I write separate PHP programs to handle each table? Yes, and in fact I've been doing that where I've needed to. But I can build a full-featured query tool (with search, insert, update and delete capabilities) for a new table in under 20 minutes, and it will have the same look and feel as a couple dozen other programs for other tables. That's saved me a BUNCH of time both in development and in training. -- Mike Nolan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Help with inventory control
> User1 starts order and takes the last two units. User2 starts order 1 minut= > e > after and checks inventory. He sees 2 units left and adds them to the his > order. User1 commits his order. Now User2 cannot finish his order because > the products are not available anymore. > > This is the problem I want to avoid. Therefore if User1 takes the product > but does not finish the order I want the inventory to still show that the > product is sold out to other users. > > Any suggestions on how to implemnt that? One common way to deal with it is to have a separate 'hold quantity' field (or table) for items in pending orders. You can commit to that field or table as each line item is entered, revised or deleted during order entry. When the order is finalized, you simultaneously release the hold and take the item out of inventory. The primary problem with this method is abandoned orders, because you want to release that inventory so someone else can order it. That's more of an issue if you are writing an application for your customers than if it's being used by a sales staff who will know to cancel an abandoned order. (However, you probably still need a 'cancel pending transaction' capability to deal with things like system crashes.) I once designed a web-based transaction system which kept a timestamp on each 'on hold' line item. It assumed that if the order wasn't completed within an hour the order had been abandoned, and at that point it released the hold on any items. (Actually it just checked the timestamp when adding up the 'on hold' quantity during an inventory check and ignored any timestamp that was more than an hour old.) There was also a one hour inactivity timeout on the web form, as I recall. You should be able to do most of this with trigger functions. -- Mike Nolan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle buys Innobase
> All of which seems to beg the question: why did not MySQL buy > Innobase themselves? As far as I've read, the terms of the > transaction were not disclosed. I guess it's possible that MySQL > didn't have the financial reach to pull off the deal. Maybe they didn't think it was necessary. In any event, they're far from the first (or last) company to underestmate the aggressive business tactics of Oracle, which isn't doing this out of the goodness of their hearts. My guess is that the people at Oracle looked at the number of ISPs who offer their customers MySQL database support and saw a market to tap. Oracle's tried to tap the 'small database server' market before, badly. If the folks at MySQL AB are smart, they may be considering selling out to Oracle too, before they get left out in the cold. Are there any lessons to be learned from this with regards to PostgreSQL? -- Mike Nolan ---(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: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
> Stupid question here ... if Oracle came at us with "the Software Patent > crap", is there any "reasonable time" provided to remove it? We've > already shown in the past that that isn't a big hurdle, with the ARC > stuff, so am just curiuos as to how big a thing the Patent stuff is, or > does even that fall under 'temporary setback / inconvience'? That may depend on what's been patented. In my opinions (and more importantly in the eyes of more than a few intellectual property attorneys) the patent office has granted some very dubious software patents, and a deep pockets patent holder would probably have the upper hand wielding them. -- Mike Nolan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Number of rows of a table
> Using psql how can I ask postgresql to show the actual number of rows of a > table? What do you mean by 'actual number of rows'? Is there a reason you can't just do: select count(*) from this_table: -- Mike Nolan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] update trigger not working
> I'm trying to set up a trigger that simply updates a > field's corresponding timestamp to now() whenever the > field is updated. But it's not working. Trying to > debug, I commented out the inner IF and END and the > log seemed to indicate infinite recursion occurred. My > next guess is that perhaps NULL's in OLD.stuff is > causing the IF to behave other than what I expect. Let me see if I have this right. You have an 'after-update' trigger on a table that does an update on that same table, and you're wondering why that creates an infinite loop? You need to do this in a 'before-update' trigger and set NEW.timestamp, then return NEW. -- Mike Nolan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Tablespaces and indexes
I'm trying to move over 50 tables (several over 500MB each) from a 7.4.5 database to 8.0.1 on a regular basis during system testing. (The 8.0.1 system will become the production system soon, probably next month.) I'd like to have the data table and its indexes built in separate tablespaces on separate physical drives. The 'default tablespace' parameter appears to apply equally to both the data table and any 'create index' commands. Editing the .dmp files to insert a tablespace clause in the CREATE INDEX commands in the .dmp file is impractical because of their size, any suggestions how to automate this? Related question: Once I switch the 8.0.1 system over to be the production, can I reverse the direction and restore .dmp files on the 7.4.5 system or are the tablespace terms in the dump files going to cause problems? Eventually the 7.4.5 system will be upgraded to version 8, but that may not happen for a couple of months. Are there any plans to have a separate 'default index tablespace' parameter? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Days in month query
> > What I want is SELECT statement that references no tables but returns
> > the days in a given month. I'm now thinking that I might be able to
> > come up with something using an IN clause and using EXTRACT, but
> > haven't figured it out yet.
I have a 'last_day' function (duplicating what the equivalent Oracle
function does), from that you can extract the number of days in the month.
Here's my 'last_day' function:
create or replace function public.last_day(date)
returns date as
'
DECLARE
this_day alias for $1;
declare wk_day date;
BEGIN
wk_day := date_trunc(''month'', this_day) + interval ''1 month''
- interval ''1 day'';
return wk_day;
END
' language 'plpgsql';
--
Mike Nolan
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL] Tuning queries inside a function
What's the best way to tune the queries inside a user function? I have a fairly complicated function that may make as many as 10 queries on several tables, some of which involve multiple joins. Further, in the PHP program that needs this function, it can be called as many as 400,000 times. The last time I ran the program in production mode, it took 35 hours to complete! Since then I've done some reworking to avoid the function calls about half of the time, that cut the run time down to about 16 hours, but that's still longer than I'd like. I need to find out if the function can be tuned further, but 'explain' doesn't really tell much about what's happening inside the function. Any ideas on how to tune a user function? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Tuning queries inside a function
> select * from foo('bar','debug')
But how do I do that inside a pl/pgsql function? 'select into' doesn't
seem to work properly.
> I would have to check be able to include a timestamp at the beginning
> of each notice.
You can do that from the config file, but it only gives the time to the
nearest second, which may not be a fine enough time interval.
--
Mike Nolan
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Tuning queries inside a function
> > Maybe you could return a refcursor pointing to the EXPLAIN ANALYZE of > > the query inside the function. > > The raw materials exist to do this: if you know which elements of a > query will be replaced by plpgsql variables, you can duplicate the > results via > > PREPARE foo(...) AS ... > EXPLAIN EXECUTE foo(...) > > Certainly there is a lot more that we can and must do about making > it easier to debug and tune plpgsql functions. But you can fix 'em > with a little determination even now... If I know which elements of a query will be replaced by variables, I can enter the query in psql, which I've done. (I can always output the variables to the log from inside the function.) But what I'd rather have is some way of getting and logging the 'explain' output for a series of function calls, which I can't seem to achieve inside a function. I think in the long run I may have to redefine the database to cut back on the number of queries inside the function. -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tuning queries inside a function
> Mike Nolan wrote:
> >>select * from foo('bar','debug')
> >
> >
> > But how do I do that inside a pl/pgsql function? 'select into' doesn't
> > seem to work properly.
>
>
> You would have to code it. For example:
>
> IF $2 = ''debug'' THEN:
That part I get, but I cannot seem to get an 'explain select' to return
the explain output inside a function.
--
Mike Nolan
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] getting the ranks of items
> > If I order a query by ascending age, the youngest person gets > > rank 1, the second youngest gets rank 2, the third youngest gets rank 3, > > and if the fourth and fifth tie, they both get 4, and the next one gets 6. > > > > You know, rank? :) > > You could use a plPerl function. To do it with ties, you'd need some way of passing the function the ranking criteria with persistence between calls, which might have some startup issues. Wouldn't that also cause problems with multiple users calling the function simultaneously? -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] need trigger help
> So how can I create a trigger to automatically update the hash fields > on updates and inserts? Something like the following works for me: create or replace function public.my_trigger() returns trigger as ' NEW.hashfield = hashfunction(NEW.data1,NEW.data2); RETURN NEW; END; ' language 'plpgsql'; create trigger my_trig before insert or update on my_tablename for each row execute procedure public.my_trigger(); The 'RETURN NEW' part is very important, without it your hash field won't get updated at all. -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] table synonyms
> > I hope anybody can help me on this subject. The problem is not to find > > a substitute for the Oracle synonyms, but a way to write queries > > which, reliably, can be used no matter the schema which owns the > > tables. > > Maybe you use views? Unless it changed in 8, you can't insert into or update a view. I don't know if rules will do the trick or not, to be honest I haven't figured out what they can and cannot do. As someone who used to use synonyms at the user/schema level in Oracle as a way to restrict access to a subset tables based on user-specific criteria (eg, restricting salesman 'X' to only his accounts in the customer master table), yes, synonyms would be nice. But if you really want them, become part of the development effort. -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] triggers/functions across databases
> If you have databases that are dependent on each others data you should > probably move those databases into a new schema within one database... That's a non-trivial task, especially if some of the tables in the two databases have the same name. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Generating random values.
> I have a table wich contains my users... I want to insert to each user > a random password, so I need a random function. Is there such function > in Postgres? I just found the RANDOM which generates values between > 0.0 and 1.0. If you multiply that random number by a large integer and then truncate or round the result, you will get a random integer between 0 and whatever you use as a multiplier. For example, 'select round(random() * 99)' will generate a six digit random integer. Whether that's a good password generator is a completely different subject, one for which there is no 'best' answer. The more arbitrary the password, the more likely the user is to write it down or have it saved in a password file on their computer, both of which tend to defeat the purpose of having passwords in the first place. I find some rather silly password 'standards' out there. For example, one company I've done business with requires that their passwords be EXACTLY six characters long, of which two must be UPPER CASE letters, two must be lower case letters and two must be numbers. I have a short PHP program which generates (IMHO) better random passwords, using several random numbers to select two short words (2-4 characters) from a dictionary file and adding in a number. Here are a few passwords generated by it just now: caps270nods egopegs326 mast659quip semi607it rots505hot I usually generate 3 or 4 passwords then let the user pick one. I often screen the output so that I don't get passwords like this one: pissbum560 -- Mike Nolan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Generating random values.
> Great! a simple, dumb program can generate all your passwords in very > quickly. My 2.4 Ghz Pentium 4 did it in under 10 minutes. A token set of > 16 characters, and a fixed length of 8 charachters just isnt a very big > search space. Your new password is 87&3jiwkjIJiwkjikmkq,^^2v12hqIwLbvCQQQi18152 Do not write it down or save it in a password manager, as doing so creates security problems. -- Mike Nolan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Generating random values.
> This way you can let users choose their own passwords :-) > > If you like you can put other checks in it to make sure you have any > three of uppercase/lowercase/numbers/other characters or whatever else > you like. Allowing users to choose their own permanent passwords does not make them any more secure, though it would hopefully make them easier to remember. Users tend to choose passwords that are easy to guess, and they tend to use the same password for multiple accounts. As I indicated in my original response, there is no best answer to the issue of password choices, though there are probably a few 'worst' answers. :-) Once someone has established a password scheme, either randomly generated or user selected, it should not be that difficult to write routines to generate acceptable passwords or to enforce standards for user-generated passwords. -- Mike Nolan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Is there such a thing as a 'background database job'?
In a recent discussion with an academician friend of mine regarding how to improve performance on a system, he came up with the idea of taking what is now a monthly purge/cleanup job that takes about 24 hours (and growing) and splitting it up into a series of smaller tasks. That part's fairly easy, but his next idea was to run those tasks more or less continuously in the background. As I understand most database back-ends, including Postgresql, there really isn't a way to run queries for an application in the background (ie, at a lower priority), especially for an application that does updates. I suppose I could 'nice' the program itself, but would that have any positive impact? -- Mike Nolan ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] TG_OP and undefined OLD values
I'm trying to write some code in a trigger that fires on both an insert and an update. At one point I need to update a column either on an insert or if the value of the column has changed. The following code fails because the OLD value is not defined: if TG_OP = ''INSERT'' or (TG_OP = ''UPDATE'' and NEW.column1 != coalesce(OLD.column1,''--'')) then column2 := ''CHANGED''; end if; Shouldn't OLD.column1 not even be evaluated when the other if statement in the group in parentheses is false or when the earlier if statement is true? Is there a way around this other than separating the code into two independent if statements, duplicating the action statements? -- Mike Nolan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Dumb question about 8.1 beta test
The notes on participating in the 8.1 beta suggest creating a dump using both an old and new copy of pg_dump. Does this mean we can't use pg_dumpall or that we have to restore both dumps? (Or is that just a way of testing what works and what doesn't between older dump files and the beta release?) -- Mike Nolan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] guaranteeing that a sequence never skips (fwd)
> On Sun, 2004-10-03 at 08:58, David Garamond wrote: > > Am I correct to assume that SERIAL does not guarantee that a sequence > > won't skip (e.g. one successful INSERT gets 32 and the next might be 34)? > > > > Sometimes a business requirement is that a serial sequence never skips, > > e.g. when generating invoice/ticket/formal letter numbers. Would an > > INSERT INTO t (id, ...) VALUES (SELECT MAX(col)+1 FROM t, ...) suffice, > > or must I install a trigger too to do additional checking? > > You will have to lock the whole table and your parallel performance will > be poor. Locking the table isn't sufficient to guarantee that a sequence value never skips. What if a transaction fails and has to be rolled back? I've written database systems that used pre-numbered checks, what's usually necessary is to postpone the check-numbering phase until the number of checks is finalized, so that there's not much chance of anything else causing a rollback. -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] guaranteeing that a sequence never skips (fwd)
> Then, every once in a while, a separate process would go in, see the > highest value on idfield < 250M, and rewrite the idfield on all of the > tuples where idfield > 250M. It would be efficient due to the partial > index. It limits the number of documents to 250M, but I'm sure that > can be alleviated when it turns into an issue... I think you'd be better off using two columns. Call the first one the 'work ticket' for the check request, and you don't really care if it has gaps in it or not, its primary purpose is to ensure that each check request has a unique document number of some kind, so a sequence works fine. One and only one program assigns the actual check numbers--in a separate column. That's the sort of thing that most commercial packages do, even though it seems clumsy and adds an extra step, and that's why they do it that way, too. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] two digit years in inserts
> its not my data i am stuck moving this data from a sad access database > designed by a moron. anyway it means i will have to do alot of work on > the script to make it fix that, or just install an old version of pgsql > on a box here and pg_dump the table which seems easier. I've ported a lot of data from legacy systems with 6 digit dates in them (mmddyy), not all of which were addressed for Y2K. For each system I usually wind up writing one or more to_date functions, so that I have a consistent set of rules being applied. Depending upon the specific application, those functions will have different switchover points between 1900 and 2000 as the base century. Sometimes I have to supply an additional parameter to help decide when to switch over. For example, if the data includes other age-based qualifications, such as whether someone is a child or a senior citizen, that offers another clue as to whether '02' in a birthdate is 2002 or 1902. Depending on how clean the data is in the first place, that might not fix all inconsistencies, though. Another reason for having my own date conversion function is consistency in dealing with bad dates, like 05/32/2004 or 11/31/2004. In general, I think that date conversion decisions are an application design issue, not a database system issue, and the fewer such decisions that are made by the database, the better. -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SSL connection between PHP4 & PostgreSQL ???
> >From PHP4, how can I get the pg_connect function to negotiate an SSL connection?
>
> I gather from researching the issue that pg_connect uses the same libraries as psql,
> so that
> this should be possible. But I've tried every syntax I can think of... the "options"
> parameter to
> pg_connect is not well documented.
>
> I've played with all varieties of "requiressl" or "ssl", alone or as a boolean,
> e.g.,
> "requiressl=true", etc...
First, are you sure that SSL is linked into PHP? Use phpinfo() if you're
not sure.
Second, what are you using for a connect statement?
Here's a sanitized version of one that works for me:
DB::connect("pgsql://foobar:[EMAIL PROTECTED]/dbnm?requiressl=true");
--
Mike Nolan
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] How do I recover from>> pg_xlog/0000000000000000 (log
> On Tue, Oct 19, 2004 at 03:49:04PM -0700, pw wrote: > > > I set up a cron job to pg_dump and gzip every hour and > > dump any backup gz files older than 1 week. > > Huh ... be sure to keep some older backup anyway! There was just > someone on a list (this one?) whose last two weeks of backups contained > no data (a guy with OpenACS or something). Also, if you don't routinely test your backups every now and then, how can you be sure they'll work when you NEED them to? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID's
> You are correct. nextval() is guaranteed never to give the same number > (unless setval() were used to reset the sequence value). Or unless the sequence wraps around. That's less likely (and less dangerous) than having the OID wrap around, but not impossible. I personally believe that there is value in a database-generated unique value like Oracle's ROWID. (Part of what I like about it is that since it is a system column it simplifies some application issues, since the app never has to worry about that column unless it chooses to.) Making the OID sufficiently large to avoid virtually all wraparound issues would probably mean going to a 64 bit field, which would certainly be a non-trivial task. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Important Info on comp.databases.postgresql.general
> Perhaps in parallel with the Usenet community voting whether they want > to receive posts from the mailing lists, we can have the mailing list > subscribers vote on whether they want to receive messages from the > Usenet or want to have their messages forwarded to the Usenet. That > might be interesting. If it isn't already in mailman, it would be an interesting option to add to mailman's web interface to give subscribers the option to include or exclude posts being gatewayed from USENET. (I could use that feature on some lists I run.) -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgresql website issues.
> It might be too obvious, but in my mind the only correct setting for the > body on any web site is "font-size: 100%". I tend to agree. Forcing a smaller font size is almost always an indication that you're trying to cram too much stuff on the page. The choice of font colors is also questionable IMHO. When combined with the reduced type size some things are so faint they're unreadable. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Data entry - forms design or other APIs etc. - what is there?
> There are many free GUI's built for database access (many of them > web-based using php); but most of them focus on database > administration. I think the reason for that is that database administration is easier to parameterize. There are so many different things that an application might (or should) do that writing a generalized application development tool is a huge task. Making it reasonably secure, multi-user aware and web-based adds extra levels of challenges. I've been playing around with writing a table-driven web-based database query/edit tool for the last year and a half. It works fairly well for some in-house applications and at one of my clients. Once I get through the major portion of the job for this client (around the end of April, I hope), I'm hoping to have time to look at what it would take to turn this into a project that can be released into the open source community. While it was written (in PHP) with PostgreSQL in mind, I've already used it with limited sucess with other database back ends, specifically MySQL and Oracle. I think it should be possible to make it work with any database for which there is a PEAR implementation in PHP. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] table name restiction
> I looked at the docs in the tutorial part in the beginning and in the > description of CREATE TABLE but could not find naming restriction info. > Could someone point me in the right direction? Try section 4.1.1: Identifiers and Key Words. In general PostgreSQL's SQL syntax is case-insensitive, ie, col_name and COL_NAME reference the same column. However, the default for data comparisons is case-sensitive, so a value of 'Abc' does not match 'ABC'. There are some case-insensitive operators, such as ilike, an extension to the SQL standard. The issue of case-sensitivity either at the syntax level or the data level seems to be one that brings out nearly religous ferver when 'discussed'. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Is there a peer-to-peer server solution with PG?
I have need to set up a 2nd database server for a client in their new offices in another state this month. We will be shutting down the old offices later this year but we really don't want to have 2-3 days of downtime while we physically transfer equipment 800 miles. We should have decent data connections between the two offices starting next week, but I was wonding if there is a good peer-to-peer option for PostgreSQL at this time. As I understand Slony, it is master-slave only. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Is there a peer-to-peer server solution with PG?
> Slony-1 is perfectly capable of replicating to a slave database, then > letting you decide to promote it to master, which is just what you'd > need. Why are you asking about multi-master? I am concerned that if I have to support the traffic to keep the slave unit in sync PLUS support general database use from the 'slaved' office to the master one, on the same comm line, I might start running into congestion issues. We will have people actively working the database in both office for a period of several weeks to several months, depending on how the final transfer plan unfolds. Master/Slave is probably an acceptable solution, I was just wondering if there was a multi-master one available yet. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Is there a peer-to-peer server solution with PG?
> If you have so much update load that one server cannot accomodate that > load, then you should wonder why you'd expect that causing every one > of these updates to be applied to (say) 3 servers would "diminish" > this burden. The update/query load isn't the real issue here, it's that these two servers will be 800 miles apart and there are some advantages in having each office connect to its local database rather than having one of them connect to the remote master. The Slony-1 approach will work, assuming I've got suffient network bandwidth to support it plus the traffic from the remote office plus exixting outside traffic from our public website. That's one of those things you just don't know will work until you have it built, so I'm looking for other options now while I have time to consider them. Once I get on-site in two weeks it'll a lot more hectic. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] checking SQL statement/subexpression validity
> I need to check whether a SQL subexpression (to be used in WHERE > clause), e.g.: I've never tested it from Perl, but could you use 'explain select' to see if it parses? It won't actually execute it if it does. -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Backslashes in data in version 8.1.2
When I moved up to 8.1.2 one of my PHP programs appears to be broken,
I am getting backslashes in my data that I don't want.
Investigating further, I have found some inconsistencies in how verion
8.1.2 handles data with backslashes in it:
uscf=> \d backtest;
Table "mikenolan.backtest"
Column | Type | Modifiers
+--+---
field | text |
uscf=> insert into backtest values ('ABCDEFG');
insert into backtest values ('ABCDEFG');
INSERT 417194901 1
uscf=> insert into backtest values (E'ABC\\DEFG');
insert into backtest values (E'ABC\\DEFG');
INSERT 417194902 1
uscf=> select * from backtest;
select * from backtest;
field
--
ABCDEFG
ABC\DEFG
(2 rows)
uscf=> select * from backtest where field like E'%\\%';
select * from backtest where field like E'%\\%';
field
---
(0 rows)
select * from backtest where field like E'%\\134%'
field
---
(0 rows)
uscf=> select * from backtest where field ~ E'\\';
select * from backtest where field ~ E'\\';
ERROR: invalid regular expression: invalid escape \ sequence
uscf=> select * from backtest where field ~ E'\\134';
select * from backtest where field ~ E'\\134';
field
--
ABC\DEFG
(1 row)
So far the only way I have found to change data with backslashes in it
is something like the following:
update backtest
set field = replace(field,'\\','')
where
field ~ E'\\134';
UPDATE 1
uscf=> select * from backtest;
select * from backtest;
field
-
ABCDEFG
ABCDEFG
(2 rows)
--
Mike Nolan
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Backslashes in data in version 8.1.2
> This has not changed from prior versions. It looks like you are > neglecting to allow for the fact that backslash is an escape character > both at the string-literal level and at the regex-pattern level. > Therefore you must write twice as many backslashes as you normally > would write in a regex pattern. In particular, '' to match a > literal backslash. Something must have changed, Tom, because neither of the following work on the system where I now have 8.1.2 but do work on another system running 7.4.5, and in both 8.0.2 and 8.1.2 on a third system: select * from backtest where field ~ ''; field --- (0 rows) select * from backtest where field like '%%'; field --- (0 rows) Could this be a locale issue? The one where it does not work uses the C locale, the others use the default locale, en_US.UTF-8. -- Mike Nolan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Backslashes in data in version 8.1.2
> Could this be a locale issue? The one where it does not work uses the C > locale, the others use the default locale, en_US.UTF-8. Nope, it's not a locale issue, it works on the test system using the C locale as well as the default locale. I though I had the backslash issue under control in my PHP app, whatever changed is apparently affecting both Postgres and PHP. Any ideas? Some kind of library issue maybe, such as readline? -- Mike Nolan ---(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] Any commercial shopping cart packages using postgresql?
I may need to move a web store to another hosting facility or rewrite it. I will need to be able to tie it to the in-house order entry system (which is/will be in Postgresql) for inventory status information. Are there any commercial web store/shopping cart packages or host sites that run under PostgreSQL? I found one web store package in the pgsql project archives, but it looks like it may need a lot of tinkering to get it working. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] tablespaces in 7.5?
> Ok, thats for the response. I take it a PG namespace = Oracle table space (or > namespace is simply the generic term). I can see some definite benefits > especially with disk i/o throughput though I thought database partitioning (I > think that is what its called) would provide the same thing. I could be wrong, but I think 'namespace' is an existing concept in PG that is a way of organizing objects into logical groups. As I recall, the group working on it decided to call it a 'directory' rather than a 'tablespace', because of concerns that the latter word might be proprietary to Oracle. I've lost touch with the rest of the members in that group, though, since the computer I was using for PG development purposes got zapped by lightning in August. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] hardware requirements under Redhat (fwd)
> >Does this config cover the above requirements very well. > >Does anybody know if RedHat 9 or Fedora can address 2 Gig > >of RAM out of the box? > > > Yes they can. The last time I built an RH system, one with 1GB, I had to recompile the kernel and change the 'High Memory Support' setting to get it to use the full 1GB. That was under RH 8, though. - Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] hardware requirements under Redhat
> > Use RAID 5 or 0+1... > I have had the Mirroring vs RAID 5 debate before. > You would go with RAID 5 to obtain the fault tolerance. > > That was my first choice but I was told I was wrong. I doubt there is a general rule as to which is better, it will depend upon the individual circumstances (including budget). Were you told why that was the wrong choice? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] between
> Damn, didn't find it in the docs, and just supposed it wasn't there. Try finding it in the online mysql docs. :-) Yes, it is there, but it took me far longer to wade through their docs to find it than in the postgresql docs. It took me less time with the Oracle SQL Language Reference Manual, but I cheated by looking it up in the index. There are index entries for 'BETWEEN' in "Practical PosgreSQL" and "Managing & Using MySQL", too. Sometimes books are still better than online docs. :-) -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] between
> Just FYI, there's an index entry for BETWEEN in the PG docs too: > http://www.postgresql.org/docs/7.4/static/bookindex.html > although it seems to mistakenly be lowercase instead of uppercase > as one would expect. I think I tried searching on 'between' but didn't find anything. > In general though I agree that the indexing of the docs is pretty > weak. Perhaps someone would care to step up and submit docs patches > to improve the situation? Adding index entries is no sweat if you > have even a moderate acquaintance with SGML or HTML ... we just need > someone willing to go through the files and add suitable entries ... I may look into it after the first of the year, though I'm likely to propose something more sweeping than that. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 'like' refuses to use an index???
> Mike Nolan <[EMAIL PROTECTED]> writes: > > However, I think RH always sets the LANG environmental variable, so > > that's going to be picked up by initdb, which means that the C locale > > will NOT be used unless specifically asked for. Other OS packages may > > also force the choice of a default LANG value. > > Yeah. There have been some discussions on pgsql-hackers about > defaulting to C locale instead of honoring LANG, but we haven't done > anything. Hmm. is the 'C' locale going to be faster than SQL_ASCII? Second dumb question: What is there about SQL_ASCII that a 'like' cannot use indexes? -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] GetLastInsertID ?
> Where do you get that impression? currval() refers to the last ID for the > session you are in. It's also FAQ question 4.15.3. Even better, if you > havn't used nextval() in your current session, currval() returns an error, > so you can't even get it wrong by accident. I stand corrected. I was doing some testing of a PHP module that is called from a web form a while back and got inconsistent results with currval, I probably had a script error of some kind. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] 7.4, 'group by' default ordering?
I notice that 7.4 doesn't do default ordering on a 'group by', so you have to throw in an 'order by' clause to get the output in ascending group order. Is this something that most RDB's have historically done (including PG prior to 7.4) but isn't really part of the SQL standard? On a mostly unrelated topic, does the SQL standard indicate whether NULL should sort to the front or the back? Is there a way to force it to one or the other independent of whether the order by clause uses ascending or descending order? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] order by is ambiguous
> Hmm but the first one has actually no name, it's just casted as datatype > time. I now realise that casted columns get assigned the datatype as > name. Should it not show ?column? as output just like you a "select > null;" would do? i think you're confusing what the front end uses as a default column heading with what the back end uses as a default column name. '?column?' would probably not meet SQL standards. -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Schemas not created on restore
> Yeah, this is an error in the 7.3 pg_dump logic for schemas. Use the > pg_dump from the 7.4 installation to dump the 7.3 server, or manually > edit the dump file ... When I try to build 7.4.1 on that system, I get the following error in the regression test: /home/postgres/src/postgresql-7.4.1/src/test/regress/./tmp_check/install//usr/lo cal/pgsql/bin/createdb: relocation error: /home/postgres/src/postgresql-7.4.1/sr c/test/regress/./tmp_check/install//usr/local/pgsql/bin/createdb: undefined symb ol: get_progname pg_regress: createdb failed Do I need to actually install 7.4, or can I just use pg_dump and pg_dumpall from it? (I get the same 'undefined symbol: get_progname' message when I try that.) -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Parsing bug?
In the following query the field 'memid' is varchar(8). Is the error message below a bug? select substr(memid,1,1) as memtp, substr(memid,2,4) as newx from memmast group by memtp, newx ERROR: column "memmast.memid" must appear in the GROUP BY clause or be used in an aggregate function -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Parsing bug?
> Doesn't look like a bug to me. As far as I know only aggregation functions > can occur in a select with group by for columns that are not in the group by > clause. I left out the 'count(*)' column, because the query fails with or without it. The reason I think it may be an error is that if I include either of the columns it works, but not if I include both of them. To recap, the first two queries below work, the third does not: OK:select substr(memid,1,1) as memtp, count(*) from memmast group by memtp OK:select substr(memid,2,4) as newx, count(*) from memmast group by newx FAIL: select substr(memid,1,1) as memtp, substr(memid,2,4) as newx, count(*) from memmast group by memtp, newx -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Parsing bug?
> Works for me in every branch back to 7.1 ... what version are you using? 7.4.1, but I figured out what I did wrong. The alias for the first column turns out to be the same as the name of another column in the table. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] psql, 7.4, and the \d command
> This has been discussed before. I think the reason for not doing it > has been the difficulty of coming up with a useful warning that explains > what will work and what won't. I think all that is necessary is to expand the startup banner to show what the back end is: Welcome to psql 7.4.1, the PostgreSQL interactive terminal. Connected to PostgreSQL 7.3.4 [EMAIL PROTECTED] This is similar to what some other front ends (ie, Oracle) do. it may not be all that helpful in terms of explaining what will and what won't work, but it unobtrusively identifies the back end for those who need or want that information. I would only display the host information if psql was called with -h. -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] CRM Academic Research Request
I received the following note on another database-oriented list. This may be something that pg users could help in, though I'll also pass on the caveat that the other list had, that I have no direct knowledge about the institution or the researchers. -- Mike Nolan -- At Henley Management College, in the UK, we are conducting an international academic not-for-profit research project on data quality in operational data warehouses supporting CRM processes. It is NOT required to have in production a CRM application (i.e. end-users might access customer data via traditional queries and/or business intelligence tools). The objectives of the research are to allow data warehouse practitioners to reach an accurate understanding of: - Their end-users' perceptions of the quality of customer relationship data in their firms' data warehouse - The impact of their data warehouse' customer relationship data on their end-users' ability understanding CRM problems, e.g., identification of the components of a customer relationship problem statement - The impact of their data warehouse customer relationship data on their end-users' information search behaviour, e.g., search strategies. Key Points: In my experience as a data warehouse practitioner key points are that - End-users' perceptions of the quality of their customer relationship data are a key factor in their satisfaction. Such perceptions might result in a gap with respect some metrics that IT executives might have (e.g. from data profiling tools). Remember: "Perception is reality" - Today's competitive pressure requires permanent justification of your business assets. Wouldn't be great to demonstrate that thanks to your data warehouse your end-users have improved (i) their ability understanding problems and (ii) their search behaviour? Invitation to Participate: Ron, I need qualified informants (i.e. practitioners working in campaigns, sales, or customer support with customer data stored in a data warehouse) willing to participate in this research. I kindly ask for (i) your participation filling out the survey (if you think that you qualify) and (ii) help forwarding the URL below to your Oracle Users Group. Value Proposition for Participants: By filling out the short survey (20 minutes) at the URL below, - Participants will be able to reuse this scientific questionnaire for assessing periodically their end-users' perceptions of the quality of their customer relationship data. Doing it, it will be able to track evolution and plan/implement corrective actions. - Participants will receive a summary of the results - (Optional) participants can benchmark their organization to the overall findings. Action to be Taken: - If you think that you qualify, please, fill out the short survey at the URL www.henleymc.ac.uk/quest/59365.htm <http://www.henleymc.ac.uk/quest/59365.htm> - Forward the URL www.henleymc.ac.uk/quest/59365.htm <http://www.henleymc.ac.uk/quest/59365.htm> to your user group kindly asking for their end-users (i.e. practitioners working in campaigns, sales, or customer support with customer data stored in a data warehouse) input. - In case someone wants to benchmark his/her organization, he/she should (i) ask his/her end-users for fulfilling the organization name with a coded name (e.g. "Bank of Joe"), an agreed acronym or the explicit true name and (ii) forward to my attention the e-mail address of the person designated for receiving the benchmark. Please, notice that identification (e.g. personal name, organization) is optional. Follow Up: - If we get a significant response rate from a given organization (i.e. +10 respondents), we could benchmark the results to the overall findings and send a report to the attention of the designated person. - We will send a summary of the results if this is requested in the questionnaire. - The questionnaire will be available for you in case you request it Ron, I hope you will find interesting my kind invitation to participate in this research. Otherwise, please, accept my apologies for disturbing you. If you have questions in regard to this research please contact me. If you require further confirmation of my status as Research Associate of Henley Management College, please, contact the Director of Studies, Doctoral Programme, Dr. David Price ([EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>). Sincerely, Raul M. Abril HMC, Research Associate Tel. USA: +1 760 233 08 29 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Differences between postgres and mysql
> the reason I ask is I have always used postgres at home and work, but my new > web host only has mysql :( Have you tried asking them to support postgreSQL? A client of mine had to switch ISP's rather abruptly last fall, in the middle of a lengthy project to convert them to postgreSQL from a legacy environment which includes some work in MySQL both internally and at the previous ISP. They agreed to add postgreSQL support when we need it, which will likely be in the next month or two. They were also very cooperative in providing other tools, like recompiling php to add features we needed, such as support for dBase files. > I want to know what sort of differences I will have in regards to > programming php/perl webpages using the database. The project I'm working on is using pg instead of MySQL because of the feature richness of pg, especially things like triggers. Here's a short list of things I've had to put up with in the MySQL part of that project: Difficulties in updating table A from table B because of the lack of subqueries or the 'update from' syntax in pg. Minor annoyances like needing to write 'substring' instead of 'substr'. Periodic corruption of indexes. Inconsistent handling of case sensitivity in SQL between windows and unix/linux implementations, which makes moving code from one platform to another more vexing. (On the other hand, MySQL appears to handle case insensitivity in data better than pg.) MySQL's poor implementation of the 'alter table' process. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Differences between postgres and mysql
> Interesting you should say that, because for years we were getting beat > up regularly about how poor our ALTER capabilities were compared to > MySQL's. Have we really passed them up in ALTER flexibility? Or is > there some other limitation you are thinking of? I wasn't commenting on the flexibility issue, more on performance. If you add or delete a column doesn't MySQL copy the table to a temp table then delete the original one and rename the copy? Try doing that on a table with 25 million rows and you can go to dinner and a movie while you wait for it to finish. Try it on a really big table and you can go to a performance of the Ring Cycle while you're waiting. (And I mean the Wagner Ring, not Tolkien.) I didn't comment on the interactive user interfaces. While there are a few things about psql that drive me nuts (like the fact that it always does the edit to a temporary file so it goes away immediately upon exit and some of the ways \o works, I'm used to using both features in Oracle to provide a historical trail of my work), mysql can't even repeat a command (\g) without first re-editing it. -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Trigger loop question
> Mike Nolan <[EMAIL PROTECTED]> writes: > > If I set up an on update trigger for table 'A' that updates the > > corresponding column in table 'B', and one for table 'B' that updates > > the corresponding column in table 'A', does that create an endless loop? > > Yes. > > You could break the loop perhaps by not issuing an UPDATE if the data is > already correct in the other table. The trigger on table 'A' is obviously going to see both the old value and the new value for the column. If it queries table 'B', it would see the current value there. However, if I update table 'B' and the 2nd trigger fires, that trigger will still see the OLD value if does a query on table 'A', since I think transaction atomic rules require that any updated values aren't made available to the outside world (including other triggers) until the transaction is complete. I tested this, and the 2nd trigger still sees the original value of the field from the first table, which I think is the proper result. -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Trigger loop question
> Actually, I wasn't thinking very clearly. The easiest way to break > the loop is to avoid updating the other table when OLD.x = NEW.x > in the trigger's arguments. The other way requires a rather-redundant > SELECT to see what is in the other table. If I have to update the other table for any other purpose as part of that trigger, or if some other trigger updates that table, couldn't that result in an infinite loop? It seems like the select-and-check method, even though it may be redundant most of the time, is the belt-and-suspenders way of avoiding an infinite loop. Here's a really weird question. If in the trigger for table A I have more than one statement that updates table B, or if more than one trigger procedure updates table B, does that cause multiple firings of either before or after update triggers on table B? -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] case statement as inline function?
Periodically I need to write a complex case statement that I'd like to be able to refer to in more than one place in a SQL command without having to make sure that each copy of the case statement remains the same as the query (to produce a mailing) is tailored. Is there any way to treat it like an inline function so that I could write something like the following (highly simplified): select case when A=1 then 1 when B=1 then 2 else null end as mailtype, memname from master where mailtype is not null; I could do it as a user function, though that would be less convenient during the specification phase, which may happen every few days. However, the columns referred to in the case statement can change too. Is there a way to pass the entire set of columns in a table to a function? -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] case statement as inline function?
> > Is there any way to treat it like an inline function so that I could write > > something like the following (highly simplified): > > I think 7.4 will inline simple SQL functions. Your CASE looks like a good > candidate. > > >select case when A=1 then 1 when B=1 then 2 else null end > >as mailtype, memname from master > >where mailtype is not null; I think it may inline it as an optimization step, I don't know of any way to shortcut how to write it. (I'm running 7.4.1.) -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PG vs MySQL
> Huh? Each database under PostgreSQL is kept under a seperate directory on > the server ... always has been that way .. Perhaps, but it isn't obvious which directory has which database. I'm not not sure which system catalogs provide that information, something that wasn't obvious from the online docs, either. > As to the ability to create/manage their own databases .. pls elaborate on > what issues you've had with this under PostgreSQL, as its a simple ALTER > command to provide a user with both CREATE USER and/or CREATE DATABASE > permisisons ... One of the big differences I see from the perspective of the DBA at an ISP is that MySQL has better user/customer isolation. This means that customer A should not be able to learn ANYTHING about customer B's database, not even that it exists. The \l command should only list databases that the current user is authorized for, the \du command should only list users authorized for the current database (and perhaps only superusers should get even that much information), etc. Perhaps it is possible to set PG to do this, but that should probably be the default. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PG vs MySQL
> > Perhaps, but it isn't obvious which directory has which database. I'm not > > not sure which system catalogs provide that information, something that > > wasn't obvious from the online docs, either. > > SELECT oid FROM pg_database WHERE datname = ''; Thanks. That should be easier to find in the documentation, perhaps it should be mentioned in the docs for the pg_database system catalog. >From an ISP's or DBA's point of view, it would be preferable if there was a way to determine which directory held which database without having to actually log into the database. I can envision circumstances under which postmaster might not be running when that information is needed. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PG vs MySQL
> Just curious ... restricting \l itself isn't too difficult ... but how > does MySQL restrict the ability to do something like: > > SELECT datname FROM pg_database; > > or does it not have an equivalent to that? I'm not much of an expert in MySQL, but on my ISP 'show databases' only shows MY databases. I find MySQL's security tables arcane and confusing, but it may be that I'm just more familiar with the way PG does it, because from the traffic on the pgsql-general list it seems like questions about how to set up the pg_hba.conf and pg_ident.conf are commonplace. I also wonder how well the pg_hba.conf method will scale. What happens if there are hundreds of client databases or thousands of entries in pg_hba.conf? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PG vs MySQL
> Now, that doesn't preclude clients from seeing the names of another > clients database using \l, but unless there is gross mis-management of the > pg_hba.conf, seeing the names of other databases doesn't give other > clients any benefits ... That rather depends upon what those clients are doing, doesn't it? I can see benefits from being able to completely isolate one client/database from another, even to the point of not giving them any hints that they're sharing the same database server. (Depending on how fanatical I am about it, there are other solutions, such as separate instances or completely separate physical systems, but those present a different set of administrative issues.) It may be more of a marketing issue than a technical one. If we want increased commercial acceptance, that may be one of the higher priority features from an ISP's (or his clients') point of view, if not from ours. -- Mike Nolan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Can we have time based triggers in Postgresql??
> Not that jobs built into the database are of no value, but shell scripts > seem to do everything you need and when there are other things to put into > posgtresql I wouldn't vote to waste time on jobs. I wonder if Oracle's time-based jobs feature came to be as a result of some other uses for that code, such as snapshots? Time-based jobs wouldn't make my top 10 wish list either, though if I had them, I'd probably use them. The security aspects of them could be important to some users or potential users. Using cron either forces one to have passwords out there in plaintext in the .pgpass file or to use a 'trusted' username that could also be a major security hole. Also, a script-based job can be changed or deleted by someone with the right file permissions even though they may not have database permissions, and vice versa. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Select/Group by/Order by question
> How about: > > select to_char(mtrantime,'mm-dd hh AM') as datetime, > to_char(mtrantime,'AM') as sort_field, > count(*) as tot from memtran > group by sort_field, datetime > order by sort_field, datetime; > > Then ignore the sort_field column? I usually don't like to send managers reports with data labeled 'ignore this column'. :-) With Tom's help, I found a solution. -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Data Encryption in PostgreSQL, and a Tutorial.6
> Has anyone created something like that for Postgresql? It would be > really handy to encrypt credit card numbers and other information so > it stays secure. Is there some reason you can't use contrib/pgcrypto? I use it for storing passwords in an MD5 encryption and credit card data using encrypt/decrypt, because I don't think it supports public/private key encryption. -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Data Encryption in PostgreSQL, and a Tutorial.
> True, but the original discussion, I believe, was on storing user > passwords etc... for which md5 is the preferred method... I thought the original question was what to use for storing credit cards, for which you want a decryptable method. (A public/private key method would be even better for credit card data IMHO, but I don't think pgcrypto includes one.) -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PL/SQL question
> In fact the problem seems to come from the "INSERT INTO". I delete > everything from the function and only keep the "INSERT INTO" and get the > same problem. Given that this is supposed to be a trigger function, what's your 'create trigger' statement look like? Part of the problem may be how your 'return null' is being handled, and that can be related to when the trigger fires. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] What is wrong here?
> I don't think so. I don't see why there should be a difference in > executing an insert statement direct, or trought a function. > You would still be simply executing an insert on a table, wich implies > that the user has to have sufficient rights on that table. Permissions problems can take a bit of detective work to nail down. Are you using schemas? If so, is that table in the public schema or in a user schema? Is the function in the public schema or in a user schema? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] security question
> i'm very new to postgres and have a fundamental question. > how do i make a pg-db most secure? > i feel, that pg_user e.g. ist something very dangerous - isn't it? if > someone hacks into a db, then he has lots of information at his/her > fingertips. is this so? > > what do i do to prevent my db from beeing hacked? If you want to make sure it is never hacked into, here are two suggestions: 1. Don't connect it to a computer network or phone line. 2. Don't turn it on. Seriously, most of the risks are NOT application or database-specific. In other words, if a hacker can get to your computer, it almost definitely isn't postgreSQL's fault, and there may not be much you can do about it from a database perspective once the hacker gets in. If the hacker can get dba, sysadmin or root access, you're fully compromised. PostgreSQL has a number of security features in it to control access privileges for both local and remote (networked) users, as do most major database platforms. If you are a DBA, you need to be aware of these, most of them are fairly well documented in the online manual on postgresql.org. Specifically read the sections on GRANT and on hba_conf. -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Listing databases
> Sorry for the newbie question, but how do you get PostgreSQL to list > the available databases? I know how to log into a certain database, but > not list all of them. I know this must be possible because the > phppgAdmin web site demonstrates it with their trial server. Use the \l command within psql. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Data change logs
> So, if anyone out there has an effective alternative, I would love to > hear about it. The way I do it is to create a copy of the table I want to track and add a text column for the user name and a timestamp column. I then set up an on update trigger on the original table that does the following: insert into _log select * from where keyfield = NEW.keyfield; I also set up an on insert trigger on the log table that adds the timestamp and the user ID (from session_user). The big negative is that if you add columns to the original table, you have to fiddle with the log table to make sure you keep the username and timestamp columns after all the columns in the original table and keep the columns in sync with the original table as to both size and order in which they appear, or you'll get errors. Pulling the data out of the log table can be a bit more, because it has the OLD data but not the NEW data. But you know what the values were, who changed them and when, and you can check the original table to see what the current value is. (If there are multiple changes, you have to check the next one in timestamp order, of course.) One of the nicer aspects is that because this is done at the trigger level, the user does NOT have to have any access to the log table, the trigger can use SECURITY DEFINER. That way you get full control over who can even look at the log. -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Data change logs
> > insert into _log select * from where keyfield = NEW.keyfield; Oops, that should be OLD.keyfield. -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Running Totals and other stuff....
> Adding a cheque number primary key would work tho' Depending on the specifics of the application, check number may not be a 'unique' field. Automatic bank checks come to mind, on my monthy statments they tend to always have the same check number or none at all. In this case I'd use a serial column. The best long term solution, IMHO, would be to change postgres so that it has a unique system column for each record, like Oracle does. -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [PERFORM] Trigger & Function
> My problem is I defined the "before" and "after" > fields in the audit table as TEXT and when I try to move NEW or OLD into > these fields I get the error "NEW used in query that is not in a rule". You're trying to insert record data into a text field, that doesn't work. OLD and NEW can be used as either record identifiers (as in RETURN OLD) or column qualifiers (as in OLD.colname), but you can't intermingle them. I don't think postgres (pl/pgsql) has row-to-variable and variable-to-row functions like serialize and unserialize, that's probably what you'd need. It would probably be necessary to write something like that in C, since at this point pl/perl cannot be used for trigger functions. I've not tried using pl/php yet, the announcement for it says it can be used for trigger functions. My first thought is that even if there was a serialize/unserialize capabiity you might be able to write something using it that creates the log entry but not anything that allows you to query the log for specific column or row entries. It would probably require a MAJOR extension of SQL to add it to pg, as there would need to be qualifiers that can be mapped to specific tables and columns. Even if we had that, storing values coming from multiple tables into a single audit table would present huge challenges. I've found only two ways to implement audit logs: 1. Have separate log tables that match the structure of the tables they are logging. 2. Write a trigger function that converts columns to something you can store in a common log table. (I've not found a way to do this without inserting one row for each column being logged, though.) -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Queries not always using index on timestamp search
Here are two queries (under 7.4.1): 'mytime' is a timestamp field that is indexed. select * from mytable where mytime > '2004-06-21' select * from mytable where mytime > current_date-3 Looking at an explain on these queries, the first one will use the index and the second one will not, even though 'current_date-3' produces the same date as the hard-coded one in the first search. Is there a way to get the second query to use the index? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Run a unix or perl command in a trigger
> I wonder to know if it is possible to launch a Perl program or a unix > comand in a trigger function. > I have tried to do that in a C trigger developed with the SPI function. > But my examples does not work and i don't have any error messages in the > postgres logfile. > So, i ask you your opinion and an example of code if your trigger works. I guess I'm not sure what you mean by 'launch'. Do you need to query the results? Here's a plperlu function that runs an external shell script. I've not tried it in a trigger, but it should work. This function needs to be created as a superuser since it uses 'untrusted' perl. It creates a security hole in that anyone who has write access to the postgres user home directory can run ANYTHING. -- Mike Nolan create or replace function submit_batch(varchar, varchar) returns varchar security invoker as ' # perl body goes here # parameters: user ID # batch id $command = "/home/postgres/submit_batch.job " . "$_[0]" . " " . "$_[1]" ; $x = system($command); return $x; ' language plperlu; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Trigger on Postgres for tables syncronization
> I want if my program inserted, updated, deleted the > record of "appointment" than the postgres does the > syncronization to the corresponded tables > (appointment0 or appointment1 or both). Is there a reason you aren't doing this with views? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Before/After trigger sequencing questiont
> Yeah, that would be my interpretation: the after trigger runs just > before the transaction commits, and your external PHP program can't > see the results since they haven't been committed yet. Your description > makes it sound like the trigger invokes the PHP code synchronously, > in which case it'd never work at all ... but if it's just asynchronously > sending a message to make the PHP code run a bit later, then it would > work almost all the time. Actually, the perl program executes a batch file that has the PHP program in it, so I can make it asynchronous by executing the PHP program as a batch job (&) and then have a sleep(5) in it. Yeah, it's not very secure, but since it executes as the postgres user anyone who can log in as the root user or the postgres user could mess with it anyway. > You might want to think about using LISTEN/NOTIFY somehow to trigger the > PHP run. A listener is guaranteed not to get the notification until > (and unless) the sending transaction commits. I haven't tried figuring out LISTEN/NOTIFY yet. I thought about using plperlu to generate the e-mail, but most of the system is written in PHP. Also, In addition to sending the e-mail, it uses curl to communicate with an external secure website, so it'd be a lot of work to change it to perl, including escaping all the single quotes so that it could be a PG function. When I get this system finished (probably in October/November), I really need to write it up for the website. IMHO it's a pretty sophisticated example of what PG can do. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] restoring a .dmp file to another table name
I've probably asked this question before, but what do people do when they need to restore a table that has been dumped but as a different table name? That doesn't appear to be an option in pg_restore. (Is that just a front end to psql?) I need both the old table and the live one in the same database so that I can compare a set of values to see what changed, and I don't have a spare system to do it on at the moment, nor can I rename the live file since it is in use most of the day. This is a fairly large table (2.8M rows, 500MB dump file) and I think it may have some data in it that would get messed up if I were to try to extract the DDL leading up to the COPY statement using head and tail statements to change the table name. -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Single Row Table?
> ISTM most natural to do this with a rule, e.g.: > > CREATE RULE my_insert_rule AS ON INSERT TO my_table DO > INSTEAD NOTHING; > > Which will cause all inserts to be silently dropped. This strikes me as bad programming practice. Errors should be reported, not silently ignored. If the application is doing an insert when it doesn't need to, then the application is flawed as well. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Single Row Table?
> But should you also prevent DELETE's from that table? Otherwise you could > wind up with no rows at all. I guess that would have to be done using a rule... Why not just revoke the delete privilege? -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] postgres "on in the internet"
> Does anyone out there have experience with this or recommended best > practices? We have been looking at either (a) tunnelling everything > over ssh, or (b) just making sure that users have "strong" passwords and > requiring "md5" authentication in pg_hba.conf. Have you considered using VPN routers to punch a hole through your firewall? Can you do a a combination of A and B? (Does that make much sense?) You should also consider blocking all IP addresses other than the client nodes at the firewall. That won't help much if the client node gets compromised. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] 'order by' in an insert into command
I have the following insert to populate a new table:
insert into pending_tnmt_sec
select tseceventid, tsecsecno,
nextval('sec_seq'),
tsecrtddt
from tnmtsec
order by tsecrtddt,tseceventid,tsecsecno;
I need to access this data in a particular order which may change over
time but the initial order I want is in the order by clause.
The problem is, I'm not getting the data into the right order based
on the sequence values being inserted:
tsecrtddttseceventid tsecsecno seq
2004-08-30 | 20040731910 | 1 | 356270### out of sequence
2004-07-08 | 20040531897 | 2 | 360792
2004-06-03 | 20040425023 | 1 | 354394
2004-04-23 | 20040320702 | 1 | 353557
2004-02-18 | 20040117178 | 2 | 359387### out of sequence
2004-01-10 | 20031213418 | 1 | 351315
I can't tell whether this is because the order by clause in the insert
is being ignored or because the sequence is incrememted before the sort
takes place. Is there a way to do this insert?
--
Mike Nolan
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Import an Excel table to a Postgresql one
> > How to import an Excel table into a Postgresql table in a simple way? Another way is to save the Excel table as a dBase file and import it using the dbf2pg utility in the contrib/dbase directory. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] dealing with invalid date
> can pgsql acceppt invalid date values? Sometimes it would be nice to > convert 2003-02-29 to 2003-03-01 or to 2003-02-28 automatically instead > of throwing back an error message. I guess the question is 'accept from where?' This isn't a database question as much as it is a data INPUT question. Take it from one who has spent 30 years dealing with user data, you DO NOT WANT 'bad' data in your database, you want to clean it up before it gets into the database, and you probably don't want the database back end making decisions about how to fix data problems, because what it does might not be what you want. What's the best corrected value for the date string '13/34/2004'? Beats me! Sometimes the best answer is "I don't know what you really mean here, try again." You can certainly define a clean_date function in pl/pgsql (among other choices) to take a string and fix whatever you want to fix before converting it to a date. You can also do that in perl or PHP or whatever it is you're writing the user interface in. Making the decision of when and how to do that is a large part what I consider my 'value added' role in designing a database system for a client. -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Accessing an array element from a function
This may be mostly a documention issue: I have a function with two parameters that returns an array: text[]. I want to access just the first element of that array within my SQL statement. This doesn't work: select myfunc(1,2)[1]; This does work: select (myfunc(1,2)[1]; If that's how it's supposed to work, is that documented somewhere? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
