[GENERAL] Is this a bug?
The following sql statement fails because the column "user_id" does not exist in the users table. =# select user_id from users WHERE username = 'blah'; ERROR: column "user_id" does not exist LINE 1: select user_id from users WHERE username = 'blah.. ^ The following shows a valid statement where I want to delete one user entry from the "map_users_roles" table =# delete from map_users_roles where user_id = (select id from users WHERE username = 'blah'); DELETE 2 If I made a mistake and changed the "id" column to "user_id" then from some the statement executes. =# delete from map_users_roles where user_id = (select user_id from users WHERE username = 'blah'); DELETE 33631 I would have thought that the last statement would fail. Instead it removed all the entries from the table. This is happening because I did not qualify the column names as follows... =# delete from map_users_roles where user_id = (select u.user_id from users as u WHERE username = 'blah'); ERROR: column u.user_id does not exist LINE 1: ...lete from map_users_roles where user_id = (select u.user_id ... Still, this was quite a suprise to me and I would consider this a bug. Thoughts? -- Harry http://www.uklug.co.uk http://www.hjackson.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] verifying database integrity - fsck for pg?
On 1/31/06, Rich Doughty <[EMAIL PROTECTED]> wrote: > We are currently migrating a cluster between hosts. I'd like to > verify that the new database has been transferred reliably and > that the datafiles are in tact. > > What's the recommended way to do this? We're using > pg_start/stop_backup so an md5 check is out of the question. > > pg version 8.0 I would doubt if it is a full test but in the past when I have had corruptions in the database its been a full vacuum that has spotted them. -- Harry http://www.hjackson.org http://www.uklug.co.uk ---(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] A tale of two similar databases
On 17 Jan 2006 01:22:20 -0800, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi All, > > I am using PostgreSQL 7.3.2 on a server running Red Hat Linux 9.0. > > I have two databases ( identical schema and similar data ). > > One database, D1 contains the actual data of a Production Application. > The other D2 contains dummy data which is used during development and > testing of the application. > > D2 actually contains a recent snapshot of D1 and hence contain almost > the same data. > > The strange thing is that D1 is extremely fast whereas D2 is relatively > slow for any given query. > (Thank God, it isnt the other way round :) ) > > Eg, > An extensive Statistics query returns in a matter of seconds on D1 but > takes close to a minute on D2 > > I would like to know if anybody can answer why it is so. I am making the assumption that you have checked your query plan on both databases to make sure that they are the same i.e. on DB1 explain "big query"; on DB2 explain "big query" If these are not almost identical then you need to investigate the reasons for the difference ie bad stats on the dev database or missing index's etc. Have you vacuum analyzed D2? One other possible reason is that D1 is mostly in cache and D2 isn't. If you run the query twice on D2 immediately after each other is the second query much faster. If this is the case what you might be seeing is D1 being in constant use is forcing the D2 data back onto the disk and out of the cache. -- Harry http://www.hjackson.org http://www.uklug.co.uk ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Plans for 8.2?
On 1/12/06, Jeff Trout <[EMAIL PROTECTED]> wrote: > > On Jan 12, 2006, at 1:36 PM, Joshua D. Drake wrote: > > >>> "Built In" Failover/Clustering > >>> This won't happen. The community stance, which is a good one is > >>> that no single replication solutions fits everyone's needs and > >>> therefore we rely out the outside > >>> sources. Slony-I, Mammoth Replicator and pgpool being the most > >>> popular. > >> Too bad - I think that will keep a lot of potential users from > >> evaluating Pg as a serious alternative. Good or bad, decide for > >> yourself :) > > > > Isn't the [expensive db name here]'s replication/failover just an > expensive addon? > As in if you don't pay for it you don't get it. > > So we're basically in the same boat as them.. just an add on. we just > offer more variety. Not really. The entire company of [expensive DB name here] is at the end of the phone[0]. Taking Oracle as an example. I am not aware of Oracle etc having a seperate company that sells replication on top of their database although I could be wrong. The other thing is that Oracle is supported by various platforms etc and that support will include their replication or clustering offering. Sun has offered to support PostgreSQL just recently but have they offered to support any of the replication offerings? I would hardly say we are in the same boat just because we have bolt on replication. -- Harry http://www.hjackson.org http://www.uklug.co.uk [0] I am not suggesting that this improves support although it does improve the appearance of support. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] regarding triggers
On 1/10/06, John McCawley <[EMAIL PROTECTED]> wrote: > Regarding the usefulness of triggers...I tend to stay away from them. I > like to keep my data in my database and my logic in my application. I > try to relegate triggers to very simple things like timestamping > records. i.e. things that I won't later wonder "What in the hell is > going on???" I always try to get all the relationships from the data into the database using whatever the database can do ie triggers, foriegn keys, check constraints etc. I find that leaving all the logic to the application is a disaster waiting to happen particularly when the application is being developed by lots of people. If you insist in having all the logic in the application then surely you could use triggers to make sure that if the application makes a cock up then the integrity of the data won't be compromised. I know you can use "begin; commit;" from the application but when working with other developers there are no guarantees that they will always be using them or more likely a mistake will be made by me or someone else and I want the database to handle it. -- Harry http://www.hjackson.org http://www.uklug.co.uk ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Forum Software
On 12/30/05, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > On 12/30/05, Raymond O'Donnell <[EMAIL PROTECTED]> wrote: > > QUOTE: > I used it once (2004) because it supported Postgres. It got hacked in > under a month. I admit that this was a one off but having searched > around the Internet for various bulletin board software there seem to > be no end of problems with phpbb with regards security. I have even > come across articles claiming that the phpbb team try not to publish > all their exploits but rather blame PHIP [0] itself and they have a > tendency to ignore certain exploits in any releases that are not > current. > UNQUOTE: > > That's hardly fair. PostgreSQL also ignores security issues on older > versions. If you're running 8.0.0 and a security fix came out in 8.0.1, > it's your fault, not the PGDG folks. Actually a security hole being found is not really anyones fault [0] it just happens and then something has to be done by the user who has the software on his system. Would the people on here ignore requests for help regardless of version. I am sure if the case was stong enough someone would give you a hand, perhaps they wouldn't but I am not reading on blogs how the PostgreSQL community ignores security issues or that PostgreSQL has a particular problem with security. In fact searching for Postgres exploit returnred 206000 results on google which considering PostgreSQL is a great deal older than phpbb is not bad now is it. > Also, as a big proponent of PHP, I have to admit that it's quite easy to > write insecure software with it. Its quite easy to write insecure software period. Choice of language with regards security is an almost pointless discussion. See point [0]. Its the ability of the surgeon in the majority of cases that makes for a successful operation not his choice of scalpel [1]. > I've had nothing but good luck with PHPBB. And I am truly happy for you. I would have loved phpBB to have been my silver bullet. I may yet need to use it again because I can find nothing else that will do the job. For all its faults its most certainly filling a gap in the market. I don't want to use phpBB and I will need to be dragged kicking and screaming to drink from that well again but were needs must, better the devil you know. -- Harry http://www.hjackson.org http://www.uklug.co.uk [0] Actually we could blame the software developers for the bugs but that would be like blaming a surgeon for stitches. However, this does not give the surgeon immunnity if he performs the operation with as little apptitude as a drunk. [1] Although choosing a chain saw for open heart surgery may put him in the "limited ability" category. ---(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] Forum Software
On 12/30/05, Raymond O'Donnell <[EMAIL PROTECTED]> wrote: > On 30 Dec 2005 at 9:36, Harry Jackson wrote: > > > PHPBB. I don't want to use PHPBB due to its complete lack of anything > > resembling security. > > Just curious - where do you get your info re PHPBB's "complete lack > of anythng resembling security"? I've been considering using that > software, and would like a balanced opinion of its godd & bad points. I used it once (2004) because it supported Postgres. It got hacked in under a month. I admit that this was a one off but having searched around the Internet for various bulletin board software there seem to be no end of problems with phpbb with regards security. I have even come across articles claiming that the phpbb team try not to publish all their exploits but rather blame PHIP [0] itself and they have a tendency to ignore certain exploits in any releases that are not current. The whole thing does not inspire any confidence in me and having been stung by the software once I think it would be foolhardy to give it annother shot. Perhaps everything I am reading is true perhaps its all just bad luck. Just out of interest try searching google for phpbb exploit I get a "WERE SORRY" page from google which is an attempt by google to prevent the proliferation of a particular worm, its bad when google step in ;) If you get results the first time then try the search a few times in succession. If you are lucky enough to get some search results you will notice that there are 821,000 pages in the search results. Compared to exploit vBulletin 330,000 exploit yabb 26000 exploit bbboard exploit 631 I know its hardly scientific and that phpbb and vbulleting are a lot more popular than the other two boards but I really cannot afford the time or the money that getting cracked costs and try to avoid it at all costs. Friendly Advice: If you do decided to run phpbb then make sure you chroot Apache properly, which is something you should be doing anyway particularly if you run any third part software. This will save you time and money in the long run if someone gets in[1]. Its also easier to backup a chrooted env so you can roll over [2] the cracked site after/if you catch them in the act. -- Harry http://www.hjackson.org http://www.uklug.co.uk [0] If PHP is so problematic with regards security then this would still cast some doubt as as to the teams ability since they have chosen an implimentation langauage that is severely flawed. [1] This is assuming its a typical remote command execution and not some other nefarious hack involving your database which may be outside the chroot or cross site scripting or .. the list is endless [2] After fixing the hole. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Forum Software
What would people recommendation be for Bulleting Board software. I am after something that uses PostgreSQL and has similar features of PHPBB. I don't want to use PHPBB due to its complete lack of anything resembling security. -- Harry http://www.hjackson.org http://www.uklug.co.uk ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] outdated (bad) information in pg_stat_activity
On 12/17/05, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote: > must have missed that one. thanks - we are moving to 8.1 anyway (50-150% > increase in performance for some specific tasks!). For which tasks in particular are you seeing this king of gain? Harry -- http://www.hjackson.org http://www.uklug.co.uk ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] libpq and PQexecPrepared
I have been trying to use PQexecPrepared but so far have been having little success. I have looked at the docs and there is very little in there by way of examples. I am not really a native C programmer hence my reason for assuming its my own fault and not a bug in PG but I have noticed some things I just cannot explain. Version == (PostgreSQL) 7.4.7 The following function has been used by me for some time from Perl CREATE FUNCTION insert_index(varchar, integer, integer) RETURNS INTEGER AS ' DECLARE var_keywordalias for $1; var_job_id alias for $2; var_term_freq alias for $3; var_exists int4; BEGIN SELECT into var_exists job_id FROM job_search_index WHERE keyword = var_keyword AND job_id = var_job_id; IF var_exists is null THEN insert into job_search_index ( keyword , job_id, term_frequency ) values ( var_keyword, var_job_id, var_term_freq); return 1; else update job_search_index set term_frequency = var_term_freq where keyword = var_keyword and job_id = var_job_id; return 2; END IF; RETURN 0; END; ' LANGUAGE 'plpgsql'; Its straight forward enough. However, when I use the following from libpq prepare = PQexec(conn, "prepare insert_indx (varchar, integer, integer) as select harry.insert_index($1, $2, $3)"); result = PQexecPrepared(conn, "insert_indx", 3, (const char* const *)paramValues, paramLengths, NULL, 1); If I log the statements called I get the following [5-1] LOG: statement: prepare insert_indx (varchar, integer, integer) as select harry.insert_index($1, $2, $3) [6-1] LOG: statement: BEGIN [7-1] LOG: statement: SELECT job_id FROM job_search_index WHERE keyword = $1 AND job_id = $2 [7-2] CONTEXT: PL/pgSQL function "insert_index" line 7 at select into variables [8-1] LOG: statement: SELECT $1 is null [8-2] CONTEXT: PL/pgSQL function "insert_index" line 12 at if [9-1] LOG: statement: update job_search_index set term_frequency = $1 where keyword = $2 and job_id = $3 [9-2] CONTEXT: PL/pgSQL function "insert_index" line 16 at SQL statement [10-1] LOG: statement: SELECT 1 [10-2] CONTEXT: PL/pgSQL function "insert_index" line 22 at return The params are correct for the entry in the logs at [7-1] but if you look at [9-1] the params are in the wrong order. This looks odd to me and I was wondering if someone could explain this? I am assuming this could be an error in the way I am using libpq because so far I have been unable to get libpq working when using stored procs and prepared statements As an aside are there any decent examples on using libpq online. The ones in the docs are minimal at best and assume text parameters which make things a bit too easy. I have also greped through the contrib directories and most of the src in there seems to be using PQexec. I would like to see a working example using PQexecPrepared with mixed params if possible. Regards, Harry ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Any real known bugs about wrong selects?
David Teran wrote: The real question was: are there open known bugs where a select statement does not return a correct result, meaning a wrong number of rows? Were there a lot of errors like this? We are asking this because this makes trouble with the database we are currently using. Of course we know that there is no guarantee that pgsql works 100% bugfree but we are only asking for user experience. An error of this nature in any database is a very serious problem and you should really take it up with the vendor. If you can reproduce it I am sure they would try and supply a patch as soon as feasibly possible. I have seen databases return the wrong number of rows to queries loads of times or at least that is what it appears to be. On further investigation these apparent bugs turn out to be user errors in complicated sql statements. To date I have never seen a database return the anything other than what I have asked it for and Postgres is in this list. h ---(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
