Re: [GENERAL] Initdb panic: invalid record offset at 0/0 creating
Hello Tom Do you know that this compiler generates trustworthy code with those options? The contents of the pg_control file are clearly good according to the dump from pg_controldata, and yet we have Perhaps we should use others options, could you tell us which are the write options with icc (options without problems running Postgresql), As you tell us about icc compiler bug, I take the latest release of Intel compiler (9.0), as there was an error, I have to add in the spec file the LD_LIBRARY_PATH for intel library "error while loading shared libraries: libimf.so.6: cannot open shared object file:" Next there no more error during the compilation, one error when trying to compress file when packaging the RPM : /usr/lib/rpm/redhat/brp-compress + /usr/lib/rpm/redhat/brp-strip /usr/bin/strip + /usr/lib/rpm/redhat/brp-strip-static-archive /usr/bin/strip /usr/bin/strip: there are no sections to be copied! /usr/lib/rpm/redhat/brp-strip-static-archive: line 11: 26613 Segmentation fault $STRIP -g $f so I had in the spec file : %define __spec_install_post : and now, we initialising lauching initdb, we have no more the error in creating the template database but another one error occurs: so I have execute the initdb as you tell us in previous mail : Do you have some explanation or/and tips on how to build a successfull rpm on ia64, with icc, perhaps we shoud not please tell us Thanks Tom hereafter the last run and error messages : [EMAIL PROTECTED] PGS]$ creating directory /home/PGS/V811/pg_subtrans ... ok creating directory /home/PGS/V811/pg_twophase ... ok creating directory /home/PGS/V811/pg_multixact/members ... ok creating directory /home/PGS/V811/pg_multixact/offsets ... ok creating directory /home/PGS/V811/base ... ok creating directory /home/PGS/V811/base/1 ... ok creating directory /home/PGS/V811/pg_tblspc ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 1000 creating configuration files ... ok creating template1 database in /home/PGS/V811/base/1 ... ok initializing pg_authid ... PANIC: invalid redo/undo record in shutdown checkpoint sh: line 1: 24229 Aborted "/opt/pg_811/PGHOME/bin/postgres" -F -O -c search_path=pg_catalog -c exit_on_error=true temp late1 >/dev/null child process exited with exit code 134 initdb: data directory "/home/PGS/V811" not removed at user's request [EMAIL PROTECTED] i-s]$ pg_controldata $PGDATA* pg_control version number:812 Catalog version number: 200510211 Database system identifier: 4887835219649830077 Database cluster state: shut down pg_control last modified: Mon 23 Jan 2006 06:37:01 PM CET Current log file ID: 0 Next log file segment:1 Latest checkpoint location: 0/98 Prior checkpoint location:0/20 Latest checkpoint's REDO location:0/98 Latest checkpoint's UNDO location:0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 3 Latest checkpoint's NextOID: 10284 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint:Mon 23 Jan 2006 06:37:01 PM CET Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Date/time type storage: floating-point numbers Maximum length of locale name:128 LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8 [EMAIL PROTECTED] i-s]$ [EMAIL PROTECTED] i-s]$ [EMAIL PROTECTED] i-s]$ od -x $PGDATA/pg_xlog/0001* 000 d05d 0002 0001 020 1cbd 43df 143c 43d5 0100 040 f3bd 3cfc 060 0050 0030 100 0020 0020 120 0001 0003 2710 0001 140 143c 43d5 160 96e3 44be 0020 0001 200 0024 0004 0030 220 4710 9a94 b046 240 0070 0050 0030 260 0098 300 0001 0003 320 282c 0001 340 143d 43d5 360 * 1 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Does this look ethical to you?
> George Pavlov wrote: > > they have the same kind of page setup for pg Admin: > > > > http://pgsql.navicat.com/PG_Admin/index.php > > > > this one renders... > > > > both pages seem to tell robots not to cache them, so can't view a > > cached view on google. > > > At least PG Admin is free software and doing that is not > really hurting anyone since (at least I don't think) no one > is making money off of PG Admin III. > > I do mention my product as a PG Admin III alternative but I > would never use Navicat or EMS PG Manager by name anywhere on > my site. I use words like "Other Companies" etc. If you don't use "other companies" by name, you shouldn't use pgAdmin by name either, IMHO. But as long as you list it as "alternative for ...", it should be just fine to list any product name, as long as any comparison is fair. (And if it's not fair, you should just take it off. Now, I think actually putting it in the *title* of your site is not a nice way to play. Again, IMHO of course. I don't buy the argument that it makes a difference wether people pay for the product or not. There are still people putting considerable amount of work into pgAdmin, as with most opensource products. I don't see why they should be treated differently from people putting work in and getting paid for it. > I would like to know how they get such good placment on all > the search engines, you do a search on "Postgresql GUI admin" > and all you get on the first page is their stuff. You mean like the way sticking "pgadmin" in the title of a page not related with it will get you traffic not "meant" to go there? It's not as "bad" as sticking it in the URL, but in my book it's certainly not far off. In this case, that looks like paid links. And "spamming" every download site they can find. Sort of like any search for "pgadmin alternative" brings up links like http://www.soft3k.com/kw/pgadmin-alternative.htm. The idea is to match the url and title to the keywords which will bring the results higher up in teh search. Your main point (per the title of the site) is "pgadmin III alternative". Theirs is, again per the title of the site, "the worlds best gui for windows, linux and macos x". (Your site comes back up on the ranking if you put in "postgresql gui admin client", because then it takes over from the pgadmin part, I guess) //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Installing Postgres 8.1 on Windows Server 2003 R2
Carl Conard wrote: I've successfully installed Postgres 8.1.2 on WS 2003 R2 on a Lenovo (IBM) ThinkPad. I used the default installation options and everything seems peachy keen for a single user (using localhost). However, when we started performance testing Postgres (vs. MySQL) using a 3rd party tool (I forget the name) running on the server, Postgres would drop connections after about the 10th or 11th virtual user (of 20) hit the DB. The remaining user processes would remain visible in Window's Task Manager and just "hang." How are you connecting? What do you mean by "drop connections"? What do you mean by "hang" - does the server have problems shutting them down if you tell it to? What do the logs show? What changes have you made to your configuration files? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.
Its not a macro.I meant that the code generated by AC_REPLACE_FUNCS([getaddrinfo]) by configure.in for "configure"does not have "#include ". Hence function is not detected(unresolved getaddrinfo).Hence I thought AC_TRY_LINK could give test program instead of AC_REPLACE_FUNCS taking one.$ diff -r configure.in configure.in.new918a919> AC_MSG_CHECKING([for getaddrinfo])920c921,926< AC_REPLACE_FUNCS([getaddrinfo])---> AC_TRY_LINK([#include #include ],> [char (*f)();f=getaddrinfo;],> ac_cv_func_getaddrinfo=yes, ac_cv_func_getaddrinfo=no)> if test x"$ac_cv_func_getaddrinfo" = xyes; then> AC_DEFINE(HAVE_GETADDRINFO,1,[Define if you have the getaddrinfo function])> fi923a930> AC_MSG_RESULT([$ac_cv_func_getaddrinfo])Regards, Rajesh R--This space intentionally left non-blank.-Original Message-From: Tom Lane [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 17, 2006 8:34 PMTo: R, Rajesh (STSD)Cc: pgsql-hackers@postgresql.orgSubject: Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function."R, Rajesh (STSD)" <[EMAIL PROTECTED]> writes:> But the bottomline is the default test does not include in> the test code.That's odd. Is getaddrinfo a macro on Tru64? If so, the appropriate patch would probably make the test look more like the tests for finite() and friends:dnl Cannot use AC_CHECK_FUNC because finite may be a macro AC_MSG_CHECKING(for finite) AC_TRY_LINK([ #include double glob_double; ], [return finite(glob_double) ? 0 : 1;], [AC_DEFINE(HAVE_FINITE, 1, [Define to 1 if you have finite().]) AC_MSG_RESULT(yes)], [AC_MSG_RESULT(no)]) regards, tom lane
Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.
On Tue, Jan 24, 2006 at 02:33:13PM +0530, R, Rajesh (STSD) wrote: > Its not a macro. > I meant that the code generated by AC_REPLACE_FUNCS([getaddrinfo]) by > configure.in for "configure" > does not have "#include ". Hence function is not > detected(unresolved getaddrinfo). > Hence I thought AC_TRY_LINK could give test program instead of > AC_REPLACE_FUNCS taking one. But if it isn't a macro, why do you need the header file? In C it's perfectly legal to declare the symbol yourself and try to link and it should work *unless* it's normally a macro. We're still missing some necessary understanding here... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] FATAL: terminating connection due to administrator
surabhi.ahuja wrote: The exact message i saw is this: LOG: received fast shutdown request LOG: aborting any active transactions FATAL: terminating connection due to administrator command so does this mean that someone is trying to stop postmaster by sending it a kill signal? Someone or something. It can be Linux's out-of-memory facility picking processes to kill. Google "oom killer" for discussion. It would need to be sent from the postgres user or root though (or a process running as them). i also have these questions: 1. many times i have seen two instances of postmaster running. how does that happen and how to prevent it from happening? Shouldn't (unless you have two installations of course). You shouldn't have to do anything to prevent it from happening. Try and capture details of the processes with "ps aux" and "pstree" if you can. 2. into the logfile (which i specify at the time of starting postmaster), i want to add timestamps, for each log/warning etc. How do i do that? See the manuals for details - run-time environment / error reporting and logging. http://www.postgresql.org/docs/ -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Quoted NULLs with COPY FROM
George Pavlov wrote: Did you try NULL AS ''? yes i did. that is the default and does not change the outcome (same errors about trying to insert an empty string into a numeric field. Well there's your problem. Quotes tend to imply a text field. Assuming you don't want to write a short Perl script to pre-process the file the simplest way would be to import into a temporary table with text columns rather than numeric, then copy from that into the real table (with CASE or two queries). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Does this look ethical to you?
> -Original Message- > From: Magnus Hagander [mailto:[EMAIL PROTECTED] > Sent: 24 January 2006 09:05 > To: Tony Caduto; George Pavlov > Cc: Bruce Momjian; pgsql-general@postgresql.org; Dave Page > Subject: RE: [GENERAL] Does this look ethical to you? > > > George Pavlov wrote: > > > they have the same kind of page setup for pg Admin: > > > > > > http://pgsql.navicat.com/PG_Admin/index.php > > > > > > this one renders... > > > > > > both pages seem to tell robots not to cache them, so can't view a > > > cached view on google. > > > > > > At least PG Admin is free software and doing that is not > > really hurting anyone since (at least I don't think) no one > > is making money off of PG Admin III. There most certainly are companies making money from pgAdmin, including at least 2 of the most well known large PostgreSQL companies. In addition to potentially hurting them, you are trading off our long established name, which to add insult to injury you haven't even spelt or formatted correctly (it is, and has always been pgAdmin)! > > I do mention my product as a PG Admin III alternative but I > > would never use Navicat or EMS PG Manager by name anywhere on > > my site. I use words like "Other Companies" etc. If it helps at all, we ask our supporters to follow this guideline when setting up keywords etc on software directory type sites: "These should reflect any aspect of what pgAdmin does, and what it works with. Under no circumstances should you include competitors company or product names in keywords. " I would appreciate it if you would hold yourself to a similar standard and stop using pgAdmin's name to promote your product. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Updating rows (automatically) that are selected
On Wed, Jan 18, 2006 at 14:26:28 -0700, Assad Jarrahian <[EMAIL PROTECTED]> wrote: > Hello, > I have a table called X > it contains 3 fields of importance(amongst others). > time (timestamp), snooze (int) , inbox (int) > > The time column sometimes has null values. > What I want is that every time a row is selected, if the time is a > null value, it is set to now() and snooze is set to 0 and inbox to 1 > > How would I go about doing this. I looked into Creating Rules, but I > am not sure if this will work. Are you talking about updating the underlying roles or just returning modified information? I don't think this is going to be easy to do in the case where you update the underlying rows. Normally you update data like that using triggers rather than rules, but you can't trigger on selects. Maybe you should be changing the data when you store it, rather than the first time you read it back? If you don't want to update the underlying data, then using a view with a CASE statement is probably the easiest soltuion. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.
sorry. It is a macro.so, would it be better to check for the macroas suggested by Tom or go with this patch$ diff -r configure.in configure.in.new918a919> AC_MSG_CHECKING([for getaddrinfo])920c921,926< AC_REPLACE_FUNCS([getaddrinfo])---> AC_TRY_LINK([#include #include ],> [char (*f)();f=getaddrinfo;],> ac_cv_func_getaddrinfo=yes, ac_cv_func_getaddrinfo=no)> if test x"$ac_cv_func_getaddrinfo" = xyes; then> AC_DEFINE(HAVE_GETADDRINFO,1,[Define if you have the getaddrinfo function])> fi923a930> AC_MSG_RESULT([$ac_cv_func_getaddrinfo]) I guess, instead of adding seperate code for macro checking as suggested by Tom, this might serve dual purpose.Thanks,Rajesh R--This space intentionally left non-blank.-Original Message-From: Martijn van Oosterhout [mailto:kleptog@svana.org]Sent: Tuesday, January 24, 2006 2:46 PMTo: R, Rajesh (STSD)Cc: Tom Lane; pgsql-hackers@postgresql.org; pgsql-general@postgresql.orgSubject: Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.On Tue, Jan 24, 2006 at 02:33:13PM +0530, R, Rajesh (STSD) wrote:> Its not a macro.> I meant that the code generated by AC_REPLACE_FUNCS([getaddrinfo]) by> configure.in for "configure"> does not have "#include ". Hence function is not> detected(unresolved getaddrinfo).> Hence I thought AC_TRY_LINK could give test program instead of> AC_REPLACE_FUNCS taking one.But if it isn't a macro, why do you need the header file? In C it's perfectly legal to declare the symbol yourself and try to link and it should work *unless* it's normally a macro.We're still missing some necessary understanding here...Have a nice day,--Martijn van Oosterhout http://svana.org/kleptog/> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is> a tool for doing 5% of the work and then sitting around waiting for> someone else to do the other 95% so you can sue them.
Re: [GENERAL] Isolation level in a function
On Thu, Jan 19, 2006 at 02:05:41 -0800, bgolda <[EMAIL PROTECTED]> wrote: > Hello, this is my first post, please don't shoot... > > I was just experimenting with transactions (PG 8.1), and there is > something which puzzles me. If i write 'SET TRANSACTION ISOLATION LEVEL > SERIALIZABLE;' in my function, it breaks. Error informs me, that it was > executed after some query, while it is a first command in the function > after declares and begin! Because there is already a transaction which the function is executing in and for which a query has been started (e.g. the one that calls the function). > However, it seems to work perfectly well if I change in the same place > the system variable, responsible for the transaction level. I used > set_config, if I remember well. The function is only for a DBA task, so > it seems to be all right to use set_config, isn't it? > > And my questions are: > 1) Why in the first case it is not possible? Is it an error or am I > just doing something wrong? Should have set something before? > 2) Are there any downsides of the mentioned solution (changing > transaction_isolation value), except of the fact that it is an > administration command and should not be used :)? > > Thanks, > > _-_-_-_ > Bart Golda > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] execution plan : Oracle vs PostgreSQL
hi, i have a database storing XML documents. The main table contains the nodes of the document, the other tables contain data for each node (depending on the node's type : ELE, Text, PI, ...) My test document has 115000 nodes. the export of the document(extracting all informations from database and writing XML file on disk) takes 30s with Oracle and 5mn with Postgresql. The tests have been made on different systems - Sun systems : - solaris8 : 16 cpu and 64Gb RAM - solaris8 : 2cpu and 8Gb RAM - Windows Systems : - WinNT : 1 cpu(PIV) and 1Gb RAM - WinXP : 1 cpu(centrino) and 512 RAM the times are always the same, except with the centrino for which it takes 1 min. So i don't understand such differences. here is my main query (on the main table for getting the children of a node) and the execution plan for PostgreSQL and Oracle : -Query : SELECT * FROM xdb_child c1 WHERE c1.doc_id = 100 AND c1.ele_id = 2589 AND c1.isremoved = 0 AND c1.evolution = (SELECT MAX (evolution) FROM xdb_child c2 WHERE c2.doc_id = c1.doc_id AND c2.ele_id = c1.ele_id AND c2.evolution <= 0 AND c2.child_id = c1.child_id AND c2.child_class = c1.child_class) ORDER BY c1.evolution, c1.indx -Postgresql plan : "Sort (cost=33.19..33.20 rows=1 width=28)" " Sort Key: evolution, indx" " -> Index Scan using index_xdb_child_1 on xdb_child c1 (cost=0.00..33.18 rows=1 width=28)" "Index Cond: ((ele_id = 2589) AND (doc_id = 100) AND (isremoved = 0))" "Filter: (evolution = (subplan))" "SubPlan" " -> Result (cost=4.34..4.35 rows=1 width=0)" "InitPlan" " -> Limit (cost=0.00..4.34 rows=1 width=4)" "-> Index Scan Backward using index_xdb_child_2 on xdb_child c2 (cost=0.00..4.34 rows=1 width=4)" " Index Cond: ((ele_id = $1) AND (doc_id = $0) AND (child_id = $2) AND (child_class = $3) AND (evolution <= 0))" " Filter: (evolution IS NOT NULL)" -Oracle plan (cost 14): Operation Object Name RowsBytes CostObject Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=CHOOSE 1 14 SORT ORDER BY 1 4 K 14 TABLE ACCESS BY INDEX ROWID XDB_CHILD 1 4 K 4 INDEX RANGE SCAN INDEX_XDB_CHILD_1 1 3 SORT AGGREGATE 1 65 FIRST ROW 1 65 3 INDEX RANGE SCAN (MIN/MAX) INDEX_XDB_CHILD_2 8 M 3 Regards William This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. Security Notice: all e-mail, sent to or from this address, may be accessed by someone other than the recipient, for system management and security reasons. This access is controlled under Regulation of Investigatory Powers Act 2000, Lawful Business Practises. ---(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] NOT HAVING clause?
This is sort of a feature request, I suppose. I solved my problem, but "NOT HAVING" seems to match better with the desired result or the way you phrase the question in your mind, if that makes any sense... I was hoping to write a query rather short by using a "NOT HAVING" clause. The documentation didn't specify that, and trying it resulted in a syntax error indeed... My data consists of a series of images related to an object. There should be at least one image per object with sort_order = 1. I want to find all objects that don't match this criterium. I have these tables (clipped a bit): CREATE TABLE image ( image_idSERIAL PRIMARY KEY, object_id INTEGER NOT NULL REFERENCES object MATCH FULL, sort_order SMALLINT NOT NULL DEFAULT 1 ); CREATE TABLE object ( object_id SERIAL PRIMARY KEY, nameTEXT NOT NULL ); This is what I want, but isn't a valid query: SELECT object_id FROM image GROUP BY object_id NOT HAVING sort_order = 1; It is wonderfully short, one of the reasons I like this. I could write this as: SELECT object_id FROM object WHERE NOT EXISTS ( SELECT object_id FROM image WHERE sort_order = 1 AND object_id = object.object_id ); Though this does give the right results, I would have liked to be able to use NOT HAVING. Or is there a way using HAVING that would give the same results? I'm quite sure HAVING sort_order <> 1 doesn't mean the same thing. What is the general opinion on this from the developers? Did I just have one of those wild and ridiculous ideas? :P Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] NOT HAVING clause?
On Jan 24, 2006, at 20:00 , Alban Hertroys wrote: Though this does give the right results, I would have liked to be able to use NOT HAVING. Or is there a way using HAVING that would give the same results? I'm quite sure HAVING sort_order <> 1 doesn't mean the same thing. Why are you so sure? It seems to me that NOT HAVING sort_order = 1 and HAVING sort_order <> 1 would mean semantically the same thing. Can you show that HAVING sort_order <> 1 gives incorrect results? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] NOT HAVING clause?
Hi, SELECT object_id FROM image GROUP BY object_id NOT HAVING sort_order = 1; After changing the "NOT HAVING" to "HAVING" the error message was "column "sort_order" must appear in the GROUP BY clause or be used in an aggregate function" The postgres document says "SELECT list and HAVING clause can only reference table columns from within aggregate functions" IMHO, I feel that it is not just postgres which cant do, but no other database because i dont see any meaningful way of fetching rows for the query given above. On 1/24/06, Alban Hertroys <[EMAIL PROTECTED]> wrote: > This is sort of a feature request, I suppose. I solved my problem, but > "NOT HAVING" seems to match better with the desired result or the way > you phrase the question in your mind, if that makes any sense... > > I was hoping to write a query rather short by using a "NOT HAVING" > clause. The documentation didn't specify that, and trying it resulted in > a syntax error indeed... > > My data consists of a series of images related to an object. There > should be at least one image per object with sort_order = 1. I want to > find all objects that don't match this criterium. > > I have these tables (clipped a bit): > CREATE TABLE image ( > image_idSERIAL PRIMARY KEY, > object_id INTEGER NOT NULL REFERENCES object MATCH FULL, > sort_order SMALLINT NOT NULL DEFAULT 1 > ); > > CREATE TABLE object ( > object_id SERIAL PRIMARY KEY, > nameTEXT NOT NULL > ); > > This is what I want, but isn't a valid query: > > SELECT object_id >FROM image > GROUP BY object_id > NOT HAVING sort_order = 1; > > It is wonderfully short, one of the reasons I like this. > > I could write this as: > > SELECT object_id >FROM object > WHERE NOT EXISTS ( > SELECT object_id > FROM image > WHERE sort_order = 1 >AND object_id = object.object_id >); > > Though this does give the right results, I would have liked to be able > to use NOT HAVING. Or is there a way using HAVING that would give the > same results? I'm quite sure HAVING sort_order <> 1 doesn't mean the > same thing. > > What is the general opinion on this from the developers? Did I just have > one of those wild and ridiculous ideas? :P > > Regards, > > -- > Alban Hertroys > [EMAIL PROTECTED] > > magproductions b.v. > > T: ++31(0)534346874 > F: ++31(0)534346876 > M: > I: www.magproductions.nl > A: Postbus 416 > 7500 AK Enschede > > //Showing your Vision to the World// > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] NOT HAVING clause?
Michael Glaesemann wrote: On Jan 24, 2006, at 20:00 , Alban Hertroys wrote: Though this does give the right results, I would have liked to be able to use NOT HAVING. Or is there a way using HAVING that would give the same results? I'm quite sure HAVING sort_order <> 1 doesn't mean the same thing. Why are you so sure? It seems to me that NOT HAVING sort_order = 1 and HAVING sort_order <> 1 would mean semantically the same thing. Can you show that HAVING sort_order <> 1 gives incorrect results? There's a difference in meaning. By NOT HAVING sort_order = 1 I mean there is no record in the grouped records that has sort_order = 1. In contrast HAVING sort_order <> 1 means there is a record in the group with a sort_order other than 1, even if there's also a sort_order = 1 in the grouped records. To illustrate, say we have sort_orders 1,2,3,4,5: - NOT HAVING sort_order = 1 would result false - HAVING sort_order <> 1 would result true If we'd have 2,3,4,5: - NOT HAVING sort_order = 1 would result true - HAVING sort_order <> 1 would result true If we'd have 1 only: - NOT HAVING sort_order = 1 would result false - HAVING sort_order <> 1 would result false But it seems HAVING can't be applied to columns not in the group by or an aggregate. No idea why that might be... -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Problems with pgsql 8.0.4 & freebsd 6: partially solved
I have been using a postgresql 8.0.4 server under freebsd 5.4 like a charm for a long time . Yesterday, I upgraded freebsd from 5.4 to 6 via the CD (the iso was downloaded from ftp.freebsd.org). Restating postgresql it happened: # /usr/local/etc/rc.d/010.pgsql.sh start --: not found FATAL: XX000: failed to initialize lc_messages to "" LOCATION: InitializeGUCOptions, guc.c:3699 # And the server was down After many attempts I discovered that the problem was connected to the "setenv LC_ALL it_IT.ISO_8859-1" I had introduced in /usr/local/pgsql/. cshrc which corrupted something in the system and, even removing the line, 010.pgsql.sh went on failing to start the server Having pg_dumped the data before the upgrading to 6, I have solved the problem by 1) eliminating the /usr/local/pgsql/data directory (BY THE WAY, remember to backup the pgsql *.conf files somewhere!!); 2) deinstalling the postgresql 8.0.4 server by means of "make deinstall"; 3) removing the pgsql user; 4) reinstalling the postgresql 8.0.4 server; 5) copying the *.conf files under the data directory 6) pg_restoring the data Now, of course I have left unmodified the .cshrc file (take notice that any attempt of modyfying the locale corrupts the postgresql server and you should start with a new reinstallation! I failed both adding a line "setenv LC_ALL it_IT.ISO_8859-1" and "setenv LC_ALL it_IT.ISO8859-1") Now it all works even though an inoffensive warning comes up # /usr/local/etc/rc.d/010.pgsql.sh start --: not found Is there anything else I could do? Ciao Vittorio ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] NOT HAVING clause?
Alban, what you want is to put the "sort_order <> 1" in the WHERE clause, not in the HAVING clause. Then it will do what you want. Cheers, Csaba. On Tue, 2006-01-24 at 13:51, Alban Hertroys wrote: > Michael Glaesemann wrote: > > > > On Jan 24, 2006, at 20:00 , Alban Hertroys wrote: > > > >> Though this does give the right results, I would have liked to be > >> able to use NOT HAVING. Or is there a way using HAVING that would > >> give the same results? I'm quite sure HAVING sort_order <> 1 doesn't > >> mean the same thing. > > > > Why are you so sure? It seems to me that NOT HAVING sort_order = 1 and > > HAVING sort_order <> 1 would mean semantically the same thing. Can you > > show that HAVING sort_order <> 1 gives incorrect results? > > There's a difference in meaning. By NOT HAVING sort_order = 1 I mean > there is no record in the grouped records that has sort_order = 1. In > contrast HAVING sort_order <> 1 means there is a record in the group > with a sort_order other than 1, even if there's also a sort_order = 1 in > the grouped records. > > To illustrate, say we have sort_orders 1,2,3,4,5: > - NOT HAVING sort_order = 1 would result false > - HAVING sort_order <> 1 would result true > > If we'd have 2,3,4,5: > - NOT HAVING sort_order = 1 would result true > - HAVING sort_order <> 1 would result true > > If we'd have 1 only: > - NOT HAVING sort_order = 1 would result false > - HAVING sort_order <> 1 would result false > > But it seems HAVING can't be applied to columns not in the group by or > an aggregate. No idea why that might be... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] NOT HAVING clause?
Alban Hertroys wrote: Michael Glaesemann wrote: On Jan 24, 2006, at 20:00 , Alban Hertroys wrote: Though this does give the right results, I would have liked to be able to use NOT HAVING. Or is there a way using HAVING that would give the same results? I'm quite sure HAVING sort_order <> 1 doesn't mean the same thing. Why are you so sure? It seems to me that NOT HAVING sort_order = 1 and HAVING sort_order <> 1 would mean semantically the same thing. Can you show that HAVING sort_order <> 1 gives incorrect results? There's a difference in meaning. By NOT HAVING sort_order = 1 I mean there is no record in the grouped records that has sort_order = 1. In contrast HAVING sort_order <> 1 means there is a record in the group with a sort_order other than 1, even if there's also a sort_order = 1 in the grouped records. No, you're wrong in both cases there (or would be if NOT HAVING was legal). You're mixing up WHERE and HAVING. The WHERE clause applies to the individual rows before GROUP BY. The HAVING applies to the output of the GROUP BY stage. So, you can refer to HAVING MAX(sort_order) > 10 for example, but not HAVING sort_order of anything (because you don't group by it or apply an aggregate function to it). But it seems HAVING can't be applied to columns not in the group by or an aggregate. No idea why that might be... See above. You're not the only person to be confused by HAVING. I'd have left it out altogether and relied on doing the aggregation in a sub-query and applying another WHERE to its output. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] NOT HAVING clause?
Richard Huxton wrote: Alban Hertroys wrote: You're mixing up WHERE and HAVING. The WHERE clause applies to the individual rows before GROUP BY. The HAVING applies to the output of the GROUP BY stage. Ah, of course, now it makes sense. Combined with Csaba's reply my original problem has vaporized. Thank you guys :) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Does this look ethical to you?
Dave Page wrote: There most certainly are companies making money from pgAdmin, including at least 2 of the most well known large PostgreSQL companies. In addition to potentially hurting them, you are trading off our long established name, which to add insult to injury you haven't even spelt or formatted correctly (it is, and has always been pgAdmin)! Hi Dave, Those companies simply bundle pgAdmin III, they don't sell it, there is a big difference. I know for a fact the ones you are talking about and the version they bundle have no changes over the stock one at all. I don't think mentioning a product as a alternative to pgAdmin III is wrong since pgAdmin III has such a big advantage being distributed with the Windows version of Postgresql. It almost has monopoly written on it since the user is not given any idea that there is anything else available. At least you are not forced to install it, but still a HUGE,HUGE advantage over any other competing product. pgAdmin does not play fair either, if you want to talk fair maybe a link should be placed in the windows pgAdmin installer informing users of other comercially available options. Thanks for the heads up on the spelling, I was not aware it was spelled like that. Later, Tony -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] NOT HAVING clause?
Alban Hertroys wrote: Michael Glaesemann wrote: On Jan 24, 2006, at 20:00 , Alban Hertroys wrote: Though this does give the right results, I would have liked to be able to use NOT HAVING. Or is there a way using HAVING that would give the same results? I'm quite sure HAVING sort_order <> 1 doesn't mean the same thing. Why are you so sure? It seems to me that NOT HAVING sort_order = 1 and HAVING sort_order <> 1 would mean semantically the same thing. Can you show that HAVING sort_order <> 1 gives incorrect results? There's a difference in meaning. By NOT HAVING sort_order = 1 I mean there is no record in the grouped records that has sort_order = 1. In contrast HAVING sort_order <> 1 means there is a record in the group with a sort_order other than 1, even if there's also a sort_order = 1 in the grouped records. To illustrate, say we have sort_orders 1,2,3,4,5: - NOT HAVING sort_order = 1 would result false - HAVING sort_order <> 1 would result true If we'd have 2,3,4,5: - NOT HAVING sort_order = 1 would result true - HAVING sort_order <> 1 would result true If we'd have 1 only: - NOT HAVING sort_order = 1 would result false - HAVING sort_order <> 1 would result false You might try: SELECT some_column FROM some_table GROUP BY some_column HAVING SUM(CASE WHEN sort_order=1 THEN 1 ELSE 0 END) = 0; That is, "get me values for some_column from some_table; grouping by some_column, include only groups where the number of grouped records having sort_order=1 is zero." --Will Glynn Freedom Healthcare ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] NOT HAVING clause?
Alban Hertroys wrote: Richard Huxton wrote: Alban Hertroys wrote: You're mixing up WHERE and HAVING. The WHERE clause applies to the individual rows before GROUP BY. The HAVING applies to the output of the GROUP BY stage. Ah, of course, now it makes sense. Combined with Csaba's reply my original problem has vaporized. Thank you guys :) Csaba's response is incorrect: Alban, what you want is to put the "sort_order <> 1" in the WHERE clause, not in the HAVING clause. Then it will do what you want. Cheers, Csaba. If you do that, the query reads "give me unique values for some_column from some_table, ignoring individual records that have sort_order=1". To illustrate, say we have sort_orders 2,3,4,5: - NOT HAVING sort_order = 1 would result true - HAVING sort_order <> 1 would result true - WHERE sort_order <> 1 would result true for all records If we'd have 1 only: - NOT HAVING sort_order = 1 would result false - HAVING sort_order <> 1 would result false - WHERE sort_order <> 1 would result false If we'd have 1,2,3,4,5: - NOT HAVING sort_order = 1 would result false - HAVING sort_order <> 1 would result true - WHERE sort_order <> 1 would result true for records 2,3,4,5, returning some_column anyway, which is not what you want This can be done with an aggregate, a sub-select, or a JOIN -- there's no way to do this using only a single-table WHERE. --Will Glynn Freedom Healthcare ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] NOT HAVING clause?
You're right, but only if there's no GROUP BY. As soon as you use a GROUP BY _and_ the mentioned WHERE clause, the result will be what the OP wanted... or you could use SELECT DISTINCT for what he wanted. On Tue, 2006-01-24 at 15:02, Will Glynn wrote: > Alban Hertroys wrote: > > > Richard Huxton wrote: > > > >> Alban Hertroys wrote: > >> You're mixing up WHERE and HAVING. The WHERE clause applies to the > >> individual rows before GROUP BY. The HAVING applies to the output of > >> the GROUP BY stage. > > > > > > Ah, of course, now it makes sense. Combined with Csaba's reply my > > original problem has vaporized. Thank you guys :) > > > Csaba's response is incorrect: > > >Alban, > > > >what you want is to put the "sort_order <> 1" in the WHERE clause, not > >in the HAVING clause. Then it will do what you want. > > > >Cheers, > >Csaba. > > > If you do that, the query reads "give me unique values for some_column > from some_table, ignoring individual records that have sort_order=1". > > To illustrate, say we have sort_orders 2,3,4,5: > - NOT HAVING sort_order = 1 would result true > - HAVING sort_order <> 1 would result true > - WHERE sort_order <> 1 would result true for all records > > If we'd have 1 only: > - NOT HAVING sort_order = 1 would result false > - HAVING sort_order <> 1 would result false > - WHERE sort_order <> 1 would result false > > If we'd have 1,2,3,4,5: > - NOT HAVING sort_order = 1 would result false > - HAVING sort_order <> 1 would result true > - WHERE sort_order <> 1 would result true for records 2,3,4,5, returning > some_column anyway, which is not what you want > > This can be done with an aggregate, a sub-select, or a JOIN -- there's > no way to do this using only a single-table WHERE. > > --Will Glynn > Freedom Healthcare > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] NOT HAVING clause?
OK, I really didn't think it through, GROUP BY or DISTINCT won't help here. Sorry for the noise. Cheers, Csaba. On Tue, 2006-01-24 at 15:11, Csaba Nagy wrote: > You're right, but only if there's no GROUP BY. As soon as you use a > GROUP BY _and_ the mentioned WHERE clause, the result will be what the > OP wanted... or you could use SELECT DISTINCT for what he wanted. > > On Tue, 2006-01-24 at 15:02, Will Glynn wrote: > > Alban Hertroys wrote: > > > > > Richard Huxton wrote: > > > > > >> Alban Hertroys wrote: > > >> You're mixing up WHERE and HAVING. The WHERE clause applies to the > > >> individual rows before GROUP BY. The HAVING applies to the output of > > >> the GROUP BY stage. > > > > > > > > > Ah, of course, now it makes sense. Combined with Csaba's reply my > > > original problem has vaporized. Thank you guys :) > > > > > > Csaba's response is incorrect: > > > > >Alban, > > > > > >what you want is to put the "sort_order <> 1" in the WHERE clause, not > > >in the HAVING clause. Then it will do what you want. > > > > > >Cheers, > > >Csaba. > > > > > If you do that, the query reads "give me unique values for some_column > > from some_table, ignoring individual records that have sort_order=1". > > > > To illustrate, say we have sort_orders 2,3,4,5: > > - NOT HAVING sort_order = 1 would result true > > - HAVING sort_order <> 1 would result true > > - WHERE sort_order <> 1 would result true for all records > > > > If we'd have 1 only: > > - NOT HAVING sort_order = 1 would result false > > - HAVING sort_order <> 1 would result false > > - WHERE sort_order <> 1 would result false > > > > If we'd have 1,2,3,4,5: > > - NOT HAVING sort_order = 1 would result false > > - HAVING sort_order <> 1 would result true > > - WHERE sort_order <> 1 would result true for records 2,3,4,5, returning > > some_column anyway, which is not what you want > > > > This can be done with an aggregate, a sub-select, or a JOIN -- there's > > no way to do this using only a single-table WHERE. > > > > --Will Glynn > > Freedom Healthcare > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend > > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Does this look ethical to you?
> -Original Message- > From: Tony Caduto [mailto:[EMAIL PROTECTED] > Sent: 24 January 2006 13:56 > To: Dave Page > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Does this look ethical to you? > > Those companies simply bundle pgAdmin III, they don't sell > it, there is > a big difference. I know for a fact the ones you are talking > about and > the version they bundle have no changes over the stock one at all. One of the two has extensively modified it. I don't know about the other. Whether or not the sell it seperately is not the point though - they do sell it as part of the product suite they offer. > I don't think mentioning a product as a alternative to pgAdmin III is > wrong since pgAdmin III has such a big advantage being > distributed with > the Windows version of Postgresql. It almost has monopoly > written on it > since the user is not given any idea that there is anything else > available. At least you are not forced to install it, but still a > HUGE,HUGE advantage over any other competing product. It's open source and is certainly the best option a present to bundle with the installer. If you want to open source PG Lightning Admin, and add the missing features that pgAdmin has such as Slony support, job scheduling, graphical explain and UI translations into a number of different languages, then I'm sure Magnus and I would be happy to look at including it. > pgAdmin does not play fair either, if you want to talk fair > maybe a link > should be placed in the windows pgAdmin installer informing users of > other comercially available options. By that token, every Linux distribution would have to tell users about MS Windows whilst it was installing. Does the fact that they don't mean they aren't playing fair? Should the PostgreSQL distributions also mention Oracle or DB2? Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does this look ethical to you?
> > There most certainly are companies making money from pgAdmin, > > including at least 2 of the most well known large PostgreSQL > > companies. In addition to potentially hurting them, you are trading > > off our long established name, which to add insult to injury you > > haven't even spelt or formatted correctly (it is, and has > always been pgAdmin)! > > Hi Dave, > Those companies simply bundle pgAdmin III, they don't sell > it, there is a big difference. I know for a fact the ones > you are talking about and the version they bundle have no > changes over the stock one at all. Then your facts are wrong. At least one of the ones Dave is talking about has done extensive modifications for it. And sell it as part of a commercial product. (I would assume that's what he's talking about. If not, this is a different company, but it's still there) > I don't think mentioning a product as a alternative to > pgAdmin III is wrong since pgAdmin III has such a big > advantage being distributed with the Windows version of > Postgresql. It almost has monopoly written on it since the > user is not given any idea that there is anything else > available. At least you are not forced to install it, but > still a HUGE,HUGE advantage over any other competing product. We (pginstaller hat goes on) don't know of any competing products. We will be happy to consider bundling any competing product, including PG Lightning Admin. One of the most important things in order to be distributed as part of an open source product is that the parts are open source. If PGLA (or a lite version if necessary) is available under an OSS license, we'll definitly consider bundling it. (We have considered bundling phpPgAdmin, but haven't found a good way to do it without dragging in a huge load of dependencies) If that's not acceptable, there is nothing preventing you from delivering a "Lightning PostgreSQL". All the parts that are in the installer are open source, it's just a matter of making the modification to put in pg lightning admin there. You might need to do something about the GPL stuff, don't know for sure. The BSD parts definitly permit it. But to be clear, I don't think it's wrong to say that PGLA is an alternative to pgAdmin. Because that's what it is. It's just a matter of *how* it's done. > pgAdmin does not play fair either, if you want to talk fair > maybe a link should be placed in the windows pgAdmin > installer informing users of other comercially available options. You're kidding, right? Should we also mention in the PostgreSQL installer that you should perhaps look at Oracle or DB2? And I don't see most Linux distributions informing the users that Windows might be an alternative. We *do* put up links to commercial management software on the website (PostgreSQL website, because that's where it applies), and we post news about it. IMHO, that's about as far as "the project" should go when it comes to "free marketing for commercial software". //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does this look ethical to you?
There most certainly are companies making money from pgAdmin, including at least 2 of the most well known large PostgreSQL companies. In addition to potentially hurting them, you are trading off our long established name, which to add insult to injury you haven't even spelt or formatted correctly (it is, and has always been pgAdmin)! here is my keywords: Postgresql,pg,admin,GUI,alternative,PSQL,pgsql,gui,postgres I removed the III. Not that it would ever have hurt pgAdmin III, but to show good faith I changed them in my shareware pad file and on my homepage. No how about getting a link or something in the win32 Postgresql installer back to the commercial products page: http://www.postgresql.org/download/commercial Thanks, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Does this look ethical to you?
> -Original Message- > From: Magnus Hagander [mailto:[EMAIL PROTECTED] > Sent: 24 January 2006 14:22 > To: Tony Caduto; Dave Page > Cc: pgsql-general@postgresql.org > Subject: RE: [GENERAL] Does this look ethical to you? > > > > pgAdmin does not play fair either, if you want to talk fair > > maybe a link should be placed in the windows pgAdmin > > installer informing users of other comercially available options. > > You're kidding, right? Should we also mention in the PostgreSQL > installer that you should perhaps look at Oracle or DB2? And > I don't see > most Linux distributions informing the users that Windows might be an > alternative. Deja-vu... > We *do* put up links to commercial management software on the website > (PostgreSQL website, because that's where it applies), and we > post news > about it. IMHO, that's about as far as "the project" should go when it > comes to "free marketing for commercial software". Agreed. And as I'm sure Tony will recall, not only do we offer that service, it was actually me that created the page on the site originally for Lightning Admin which was the first product listed! Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL best practices?
Hi, Is there any documentation or wiki on PostgreSQL best practices? For example: - run vacuum regularly and how to judge the frequency of running vacuum - when to use rule or trigger - etc. What I realized is that I joined PostgreSQL's mailing list and kept some of the advise that I feel might come in handy in my email client. However, sometimes it is very tedious search through my archived emails and read through all the emails in the thread. You can call me lazy, but I think it's good to have a set of best practices in using PostgreSQL, especially for the beginners (unless of course I don't know that there is an existing ones). Thanks :) Regards, Leonard Soetedjo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Does this look ethical to you?
Hi, On Tue, 2006-01-24 at 08:24 -0600, Tony Caduto wrote: > No how about getting a link or something in the win32 Postgresql > installer back to the commercial products page: > http://www.postgresql.org/download/commercial Are you kidding? -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Initdb panic: invalid record offset at 0/0 creating template1]
Agnes Bocchino <[EMAIL PROTECTED]> writes: > Do you have some explanation or/and tips on how to build a successfull > rpm on ia64, with icc, > perhaps we shoud not please tell us Perhaps you should use gcc? I don't personally have the time or interest to dig into this. Considering that PG works fine on several other 64-bit platforms, it seems unlikely (though not impossible of course) that this is our bug. What could be happening is that the RPM packaging involves tools that aren't compatible with icc. I think you already found that out with regard to brp-strip, and there may be some more-subtle problems too. You might try forgetting about RPM entirely and just building from the source tarball with "configure CC=icc" plus whatever other options you want. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Does this look ethical to you?
> -Original Message- > From: Tony Caduto [mailto:[EMAIL PROTECTED] > Sent: 24 January 2006 14:25 > To: Dave Page > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Does this look ethical to you? > > > > There most certainly are companies making money from > pgAdmin, including > > at least 2 of the most well known large PostgreSQL companies. In > > addition to potentially hurting them, you are trading off our long > > established name, which to add insult to injury you haven't > even spelt > > or formatted correctly (it is, and has always been pgAdmin)! > > here is my keywords: > > Postgresql,pg,admin,GUI,alternative,PSQL,pgsql,gui,postgres > > I removed the III. Thank you, however I'm more concerned with: "PGLA has many advanced features not found in pgAdmin III,". Aside from it being slightly misleading (not only are there not many 'advanced' things PGLA can do that pgAdmin can't, there are a similar number that pgAdmin can, that PGLA can't), it is still attempting to sell your product on our name. I would therefore ask that you either: - Not mention pgAdmin at all, or - Mention not only pgAdmin, but EDB Studio, Navicat, EMS PostgreSQL Manager and other comparable products *as well*. > No how about getting a link or something in the win32 Postgresql > installer back to the commercial products page: > http://www.postgresql.org/download/commercial Why? It is an Open Source package, distributed for free, at the personal expense of numerous people including myself. Why should we advertise your or anyone elses commercial products for free? Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] FATAL: terminating connection due to administrator command
Richard Huxton writes: > surabhi.ahuja wrote: >> so does this mean that someone is trying to stop postmaster by >> sending it a kill signal? > Someone or something. It can be Linux's out-of-memory facility picking > processes to kill. Google "oom killer" for discussion. No, because the OOM killer invariably uses "kill -9". "Fast shutdown" means that something sent the postmaster a SIGINT. If you launch the postmaster manually and are not careful to make it dissociate from your terminal, then typing ^C at some unrelated program later would be enough to make this happen ... >> 1. many times i have seen two instances of postmaster running. how >> does that happen and how to prevent it from happening? > Shouldn't (unless you have two installations of course). Perhaps he's not understanding the difference between the postmaster and its child processes? I don't believe he's actually got two postmasters running (unless maybe in separate directories with separate ports, which is hardly likely to be a setup one would create by accident). There are *very* extensive safety interlocks in place to prevent that. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Does this look ethical to you?
You're kidding, right? Should we also mention in the PostgreSQL installer that you should perhaps look at Oracle or DB2? No I am not kidding, it's more akin to MS bundling a web browser and a media player. That would be a more appropiate example. MS does it to destroy competitors. I understand that it is done with pgAdmin as a convenience thing, but it has the side effect of hurting the 3rd party market. A couple of years ago there was no 3rd party admin tools for Postgresql, pgAdmin was all that was available, but that has now changed and that change should be looked at. The fact of the matter is pgAdmin III is bundled in a manner that does not let the user know there are alternatives available plain and simple. Because of the way pgAdmin is bundled it is effectivly destroying the third party admin market which should be a good thing for Postgresql not viewed as a bad thing. Look at Mysql, they never had a bundled admin tool until recently and it is the most popular psuedo open source DB on the market. People are always going to use pgAdmin because it is free, you will never have a problem getting users, but it should not be at the expense of the third party market. I am just raising a valid concern, no need for flames. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] user defined function
I want to implement a UDF that can accept a parameter which is a tuple of any table, and returns the number of NULL attributes in this tuple. Different tables may have different schemas. How can I implement this function? Thanks. andrew
Re: [GENERAL] Does this look ethical to you?
> -Original Message- > From: Tony Caduto [mailto:[EMAIL PROTECTED] > Sent: 24 January 2006 14:57 > To: Dave Page > Cc: Magnus Hagander; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Does this look ethical to you? > > > No I am not kidding, it's more akin to MS bundling a web > browser and a > media player. That would be a more appropiate example. MS does it to > destroy competitors. People don't usually complain about their inclusion - they tend to complain about not being able to get rid of them. And most people that I know well actually would rather they were there to save the hassle of getting something else. pgAdmin is easily removed, or may not be installed in the first place if you prefer. > I understand that it is done with pgAdmin as a > convenience thing, but it has the side effect of hurting the > 3rd party > market. A couple of years ago there was no 3rd party admin tools for > Postgresql, pgAdmin was all that was available, but that has > now changed > and that change should be looked at. What's that got to do with including a link in the pgAdmin installer which is what you asked for? > The fact of the matter is pgAdmin III is bundled in a manner > that does > not let the user know there are alternatives available plain > and simple. > > Look at Mysql, they never had a bundled admin tool until > recently and it > is the most popular psuedo open source > DB on the market. MySQL are irrelevant. More relevant are the commercial databases that are comparable in functionality to PostgreSQL, such as Oracle, DB2 and SQL Server. All of which bundle admin tools, and none of which advertise third party tools to my knowledge, or have ever been accused of unfair practices by doing so. As I said before, if you want to produce an OSS version of PGLA, then we will happily consider including it in the installer. We *will not* be advertising or bundling commercial products in pgInstaller. > People are always going to use pgAdmin because it is free, you will > never have a problem getting users, but it should not be at > the expense > of the third party market. Maybe you should contact Marc about advertising on postgresql.org, or consider Google Ads. Either will get your far wider exposure on the PostgreSQL website that is almost certainly visited by the vast majority of PostgreSQL users. > I am just raising a valid concern, no need for flames. I don't flame, I blacklist. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Does this look ethical to you?
Thank you, however I'm more concerned with: "PGLA has many advanced features not found in pgAdmin III,". Aside from it being slightly misleading (not only are there not many 'advanced' things PGLA can do that pgAdmin can't, there are a similar number that pgAdmin can, that PGLA can't), it is still attempting to sell your product on our name. I would therefore ask that you either: - Not mention pgAdmin at all, or - Mention not only pgAdmin, but EDB Studio, Navicat, EMS PostgreSQL Manager and other comparable products *as well*. I changed it to say PGLA has many advanced GUI features not found in other admin tools. I play fair Dave, and expect the same from you. Because the installer is not letting it be known that there are alternatives available, I have had many people tell me they had no idea there where was anything else available. It does not matter that pgAdmin is open source, and letting users know about alternatives is not free advertising, free advertising would be you paying for my Google addwords account. You guys are doing the same thing as Microsoft did with Internet Explorer, let's include it so our browser/admin tool is all the user knows about or sees when they install the OS, or in this case the SQL server. A link or blurb should be mentioned that there are other admin tools available or pgAdmin should not be installed either. I am not saying you put specifics about mine or anyone elses product commercial or open source, but I think links back to the commercial and open source pages on the postgresql site would be a fair thing to do. You are not letting the user make a choice about which admin tool to use or even try... Just my opinion on the whole fair competition thing, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Does this look ethical to you?
> -Original Message- > From: Tony Caduto [mailto:[EMAIL PROTECTED] > Sent: 24 January 2006 15:24 > To: Dave Page > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Does this look ethical to you? > > I changed it to say PGLA has many advanced GUI features not found in > other admin tools. Thank you. Regards, Dave ---(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] user defined function
Yl Zhou <[EMAIL PROTECTED]> writes: > I want to implement a UDF that can accept a parameter which is a tuple of > any table, and returns the number of NULL attributes in this tuple. > Different tables may have different schemas. How can I implement this > function? Thanks. You could do that in C, but none of the available PLs support it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL]
UNSUBSCRIBE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] NOT HAVING clause?
On Tue, 24 Jan 2006, Alban Hertroys wrote: > This is sort of a feature request, I suppose. I solved my problem, but > "NOT HAVING" seems to match better with the desired result or the way > you phrase the question in your mind, if that makes any sense... One problem is that HAVING really works on entire groups at a time (including aggregated data for the group) not on pieces of the group. However, I think one might be able to fake it with an array accumulating aggregate like the one from http://www.postgresql.org/docs/current/static/xaggr.html and a query like: SELECT object_id FROM image GROUP BY object_id HAVING NOT(1 = ANY(array_accum(sort_order))). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problems with pgsql 8.0.4 & freebsd 6: partially solved
Vittorio <[EMAIL PROTECTED]> writes: > FATAL: XX000: failed to initialize lc_messages to "" > LOCATION: InitializeGUCOptions, guc.c:3699 We've seen that reported before, on more than one platform (I recall hearing about it on OS X, for instance). But no one's ever been able to explain what causes it. It's interesting to hear that a full database reinstall made it go away for you, but that was certainly overkill. Do you have the time to experiment with the locale settings and see if you can identify the difference between the cases where it happens and those where it doesn't? > After many attempts I discovered that the problem was connected to the > "setenv LC_ALL it_IT.ISO_8859-1" I had introduced in /usr/local/pgsql/.cshrc Do you have any other LC_xxx variables set? How about LANG? Is that string still a valid locale name on your updated platform? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] user defined function
check http://archives.postgresql.org/pgsql-novice/2005-02/msg00227.php ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Does this look ethical to you?
Hi, On Tue, 2006-01-24 at 07:56 -0600, Tony Caduto wrote: > I don't think mentioning a product as a alternative to pgAdmin III is > wrong since pgAdmin III has such a big advantage being distributed > with > the Windows version of Postgresql. It almost has monopoly written on > it > since the user is not given any idea that there is anything else > available. At least you are not forced to install it, but still a > HUGE,HUGE advantage over any other competing product. IMHO pgAdmin does not compete with other "products", since it is a community-driven GUI for PostgreSQL. Look what you wrote yesterday: On Mon, 2006-01-23 at 20:52 -0600, Tony Caduto wrote: > I am kind of ticked off that they are hijacking my product name this > way. PGLA is *your* *product*, but pgAdmin is community software. As stated before, I think many people will be glad if you distribute PGLA (or a part of it) as OSS. So pgAdmin is apart from many other GUIs since it is OSS and has excellent features like Slony, GE, etc. It deserves to be distributed with Windows Installer, unless another Open Source GUI which may beat pgAdmin with its features appears on the stage. Also it is available on Linux and some *nix systems, which I think is one of the best features. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] RAID 5 and postgresql
Hi, > How about software RAID? > > Linux software RAID appears to perform better than most RAID > controllers except perhaps those that can do read interleaving > for RAID1 (I believe some 3ware controllers can do it). Linux > RAID mirroring doesn't do read interleaving, only read > balancing, which may not be so good for a single sequential > read, but pretty good for concurrent sequential reads - each > drive in a mirror set can handle one sequential read. Don't forget the battery backed cache for write performance. And because the controller doesn't know about the RAID array booting can become a problem when your first drive breaks. > I find many of these RAID controllers fail significantly more > than basic SCSI controllers (which hardly ever fail). And the > support under Linux for such controllers can be a bit patchy > sometimes - you want to be able to easily know if a drive has > died. > > It just seems strange to pay a fair bit for something that > doesn't perform well and is less reliable. The Dell OpenManage tools can help you with that, and if the controller fails (never happened yet here) you can just call Dell support, and within 4 hours they bring you a new one. (I had some strange problems with a server, and Dell replaced the mainboard and memory very quickly) I still choose the Dell LSI-based PERC4/Di where possible. - Sander ---(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] Does this look ethical to you?
Tony Caduto wrote: Thank you, however I'm more concerned with: "PGLA has many advanced features not found in pgAdmin III,". Aside from it being slightly misleading (not only are there not many 'advanced' things PGLA can do that pgAdmin can't, there are a similar number that pgAdmin can, that PGLA can't), it is still attempting to sell your product on our name. I would therefore ask that you either: - Not mention pgAdmin at all, or - Mention not only pgAdmin, but EDB Studio, Navicat, EMS PostgreSQL Manager and other comparable products *as well*. I changed it to say PGLA has many advanced GUI features not found in other admin tools. I play fair Dave, and expect the same from you. Because the installer is not letting it be known that there are alternatives available, I have had many people tell me they had no idea there where was anything else available. It does not matter that pgAdmin is open source, and letting users know about alternatives is not free advertising, free advertising would be you paying for my Google addwords account. You guys are doing the same thing as Microsoft did with Internet Explorer, let's include it so our browser/admin tool is all the user knows about or sees when they install the OS, or in this case the SQL server. A link or blurb should be mentioned that there are other admin tools available or pgAdmin should not be installed either. I am not saying you put specifics about mine or anyone elses product commercial or open source, but I think links back to the commercial and open source pages on the postgresql site would be a fair thing to do. You are not letting the user make a choice about which admin tool to use or even try... Just my opinion on the whole fair competition thing, I'm not sure I understand some of these arguments, and I don't know the history, so as an uninformed third party who can't resist adding my tupennyw'th... a) pgAdmin happens to be an admin tool that undercuts other tools in terms of price (free) and for some features b) It has negotiated a distribution channel with partner organisations - something that any other organisation presumably is free to do. You just have to have the right proposition (OSS) to entice that partner to work with you. c) If you would like pgAdmin to mention that there are more expensive alternative products - would your product before completing a sale recommend that people go take a look at pgAdmin first and see whether that might be a better alternative? Just out of interest - which product came first? I've been aware of pgAdmin for a long time - if it was there first you'd have to look closely at whether there was a commercial business case for trying to get into that market with a broadly similar product. A valid business case would obviously include making sure there were suitable accessible channels to market and sufficient funds to finance those channels. OSS projects don't often have a cash generating base to fund those channels so they are always at a disadvantage to commercial ventures. Personally I'd be a little uneasy trying to build a commercial product that piggybacks on an OSS product simply because if it's something useful and important, as opposed to niche, then someone will add an OSS version and, if they do their work properly, destroy my market. Pete -- http://www.whitebeam.org http://www.yellowhawk.co.uk - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] user defined function
Tom Lane wrote: Yl Zhou <[EMAIL PROTECTED]> writes: I want to implement a UDF that can accept a parameter which is a tuple of any table, and returns the number of NULL attributes in this tuple. Different tables may have different schemas. How can I implement this function? Thanks. You could do that in C, but none of the available PLs support it. How would you define the signature for the function? One parameter of type anyelement? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL]
Every once in a while I've noticed the number of processes I've got running jumps up a little higher than normal. So I check it out and realize that I'm building up a bunch of processes that just aren't completing. About half of the one's not completing say "SELECT waiting". I'm not doing anything that I am aware of that could be blocking anything. I am only doing regular vacuums. I am not explicitly locking anything that I know of. Is there a way to figure out what each of these processes are waiting on? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Does this look ethical to you?
Magnus Hagander wrote: > We (pginstaller hat goes on) don't know of any competing products. We will be happy to consider bundling any competing product, including PG Lightning Admin. One of the most important things in order to be distributed as part of an open source product is that the parts are open source. If PGLA (or a lite version if necessary) is available under an OSS license, we'll definitly consider bundling it. (We have considered bundling phpPgAdmin, but haven't found a good way to do it without dragging in a huge load of dependencies) I don't wan't to be bundled, I just want it to be known that there are alternatives available. Postgresql is free, so what is the big deal about letting users know about alternative admin tools comercial or open source? It's not like it would put pgAdmin or Postgresql out of business(it could only help). It's a shame you don't see how the bundling of pgAdmin(in the current way) is hurting the 3rd party community. All that would be needed is a installer section at the end saying something like: In addition to pgAdmin III there are other opensource and commercial admin products available, you can get more information here to postgresql home page>. I don't see that as being detrimental to anyone and would certainly be in fair play. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Does this look ethical to you?
> the personal expense of numerous people including myself. Why > should we advertise your or anyone elses commercial products > for free?> > > Because the installer is not letting it be known that there > are alternatives available, I have had many people tell me > they had no idea there where was anything else available. > > It does not matter that pgAdmin is open source, Yes, it does. That is *exactly* what matters. If pgAdmin wasn't open source, it wouldn't be in there. > and letting > users know about alternatives is not free advertising, free > advertising would be you paying for my Google addwords account. There we definitly disagree. I would consider it free advertising. Not as direct, but still. > You guys are doing the same thing as Microsoft did with > Internet Explorer, let's include it so our browser/admin tool > is all the user knows about or sees when they install the OS, > or in this case the SQL server. I don't think we do, and frankly, I resent that comment. As an admin in a network that is a big user of Microsoft products, the bundling of IE and Media Player is a *good thing to me as a customer*. The same thing applies to me as an "end user at home". The thing that is bad is the inability to replace them with something else, and the inability to get rid of them if I want to. Neither of which applies to pgAdmin - it's trivial to get rid of it. > A link or blurb should be mentioned that there are other > admin tools available or pgAdmin should not be installed either. I'd find it reasonable to add a blurb somewhere about "note that there are other tools and addons available. See for example http://www.postgresql.org/download"; or something like that. That certainly provides some value to the end user, and there are also several other OSS projects that would like that exposure. (Exact wording is of course up for discussion) Does this fulfill your wish for a link/blurb? //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] user defined function
Richard Huxton writes: > Tom Lane wrote: >> You could do that in C, but none of the available PLs support it. > How would you define the signature for the function? One parameter of > type anyelement? Type RECORD would be a better choice --- ANYELEMENT allows scalar types which is not what you want here. (You could probably still do it with a function declared that way, but it'd have to take extra steps to defend itself against being passed, say, an integer.) If you're looking for a coding model, stripping down record_out() to just count nulls should get you there. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL]
Rick Gigger <[EMAIL PROTECTED]> writes: > Every once in a while I've noticed the number of processes I've got > running jumps up a little higher than normal. So I check it out and > realize that I'm building up a bunch of processes that just aren't > completing. About half of the one's not completing say "SELECT > waiting". I'm not doing anything that I am aware of that could be > blocking anything. I am only doing regular vacuums. I am not > explicitly locking anything that I know of. Is there a way to figure > out what each of these processes are waiting on? Look at the pg_locks view to see what locks they are waiting for and who's got conflicting locks. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Does this look ethical to you?
Yes, that is all I am asking. Sorry about the MS reference, but I needed to make a point :-) I'd find it reasonable to add a blurb somewhere about "note that there are other tools and addons available. See for example http://www.postgresql.org/download"; or something like that. That certainly provides some value to the end user, and there are also several other OSS projects that would like that exposure. (Exact wording is of course up for discussion) Does this fulfill your wish for a link/blurb? Thanks for hearing me out, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Does this look ethical to you?
Devrim GUNDUZ wrote: I am kind of ticked off that they are hijacking my product name this way. Yep, but they were using direct links, and I changed any verbage I had immediatly when Dave asked me to. If navicat had mentioned my product in their web page I really wouldn't care, but they had links like this: http://www.navicat.com/pg_lighting_admin/index.html That looks like they are selling PG Lighting Admin. Big difference. I also never stated that my product or any other should be bundled with the win32 installer, I just meant that it should be made known to the user that other options for administration are available. I also never stated I thought direct links should be made from the installer to any of my home pages. I am thinking more of the big picture and something that would benefit all third party tool vendors commercial or open source. Doing something like that in no way could hurt pgAdmin III, since there is nothing to hurt(it's free after all). I think we have beat this into the ground, lets drop it now. I think everyone is or will be happy :-) -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Access a temporary table of another session - is it possible ?
Hi all, As the subject says, I would like to access a temporary table of another session, while being the postgres superuser. Is this possible somehow ? The problem is that I lost the terminal of the other session and I really would like to see the results (even if partial) of some long running operations... pg_stats_activity still shows the query running against that table, so I still have a chance that the table actually exists. Thanks, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Does this look ethical to you?
On Tue, 24 Jan 2006, Tony Caduto wrote: You are not letting the user make a choice about which admin tool to use or even try... My understanding was that pgInstaller gave you the option to install pgAdmin III, it isn't a *requirement* to install it, like it is with your analogy of M$+IE ... Just my opinion on the whole fair competition thing, but, we aren't competing against anyone ... you are competing against us, which puts the onous on your to do the marketing, not us ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Does this look ethical to you?
I don't wan't to be bundled, I just want it to be known that there are alternatives available. I don't think it is reasonable to expect the OSS project within the OSS installer to mention commercial alternatives IF they are shipping an already available OSS product. What I mean is that the OSS Win32 Installer is shipping PgAdmin why should they mention your product? If I were to suggest that the Win32 Installer should mention Replicator there would be an uproar, rightfully so. Postgresql is free, so what is the big deal about letting users know about alternative admin tools comercial or open source? It's not like it would put pgAdmin or Postgresql out of business(it could only help). PostgreSQL is free and the PostgreSQL.Org project does let people know about other tools on the website. It's a shame you don't see how the bundling of pgAdmin(in the current way) is hurting the 3rd party community. You are welcome to create your own Win32 Installer. I don't see it as hurting the 3rd party community. I see it as helping the PostgreSQL community. I don't see that as being detrimental to anyone and would certainly be in fair play. Fair play? It could be argued that the fact that you don't open source your product means that you don't believe in fair play. I don't personally buy that argument but I assure you it will be made if this thread continues. The PostgreSQL community does a very good job of giving the commercial counter parts their due. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Quoted NULLs with COPY FROM (and pgAdmin export data options)
> Quotes tend to imply a text field. I think you meant to say "quotes imply a non-null text field". And, yes, I am quite aware of that. The point of the thread was to see if there is any way of avoiding/overriding that assumption. > Assuming > you don't want to write a short Perl script to pre-process > the file the > simplest way would be to import into a temporary table with > text columns > rather than numeric, then copy from that into the real table > (with CASE or two queries). Yes, both are valid alternatives. The point was to see if I could skip those steps. This is actually turning into a bit of a pgAdmin issue: pgAdmin lets you export data in a format that seems to be unimportable back into the same table. If in the "Export data to file" form you check "all columns" under "Quoting" you will get quotes around your numeric NULLs that you will be unable to import back using COPY without resorting to preprocessing of some sort (unless someone tells me how COPY can use quoted NULLs). George ---(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] user defined function
Do you mean this function? Seems I cannot get much information from it.../* * record_out - output routine for pseudo-type RECORD. */Datumrecord_out(PG_FUNCTION_ARGS){ elog(ERROR, "Cannot display a value of type %s", "RECORD"); PG_RETURN_VOID(); /* keep compiler quiet */}On 1/24/06, Tom Lane <[EMAIL PROTECTED] > wrote:Richard Huxtonwrites:> Tom Lane wrote:>> You could do that in C, but none of the available PLs support it.> How would you define the signature for the function? One parameter of> type anyelement? Type RECORD would be a better choice --- ANYELEMENT allows scalar typeswhich is not what you want here. (You could probably still do it witha function declared that way, but it'd have to take extra steps to defend itself against being passed, say, an integer.)If you're looking for a coding model, stripping down record_out() tojust count nulls should get you there.regards, tom lane
Re: [GENERAL] Access a temporary table of another session - is it possible ?
Csaba Nagy <[EMAIL PROTECTED]> writes: > As the subject says, I would like to access a temporary table of another > session, while being the postgres superuser. Is this possible somehow ? No. It is not a matter of permissions, it is a matter of physically not having access to the data: buffers for temp tables are private to individual backends, not shared, for performance reasons. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] user defined function
Yl Zhou <[EMAIL PROTECTED]> writes: > Do you mean this function? Seems I cannot get much information from it... That would appear to be Postgres 7.3 :-( You need a considerably newer version of Postgres if you want to do much of anything useful with unspecified-type records. 8.0 has most of that functionality but I think 8.1 added some things. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] user defined function
But I have to use 7.3 due to some limitations. Can I do it in 7.3? On 1/24/06, Tom Lane <[EMAIL PROTECTED]> wrote: Yl Zhou <[EMAIL PROTECTED]> writes: > Do you mean this function? Seems I cannot get much information from it...That would appear to be Postgres 7.3 :-(You need a considerably newer version of Postgres if you want to do muchof anything useful with unspecified-type records. 8.0 has most of thatfunctionality but I think 8.1 added some things.regards, tom lane
Re: [GENERAL] Backup and Restore mechanism in Postgres
On Tue, 20 Sep 2005, Lincoln Yeoh wrote: At 10:00 AM 9/20/2005 -0400, Vivek Khera wrote: On Sep 14, 2005, at 9:45 AM, vinita bansal wrote: I have a 4 proc. AMD Opteron machine with 32 GB RAM and ~400GB HDD Just curious what ever came of this? Also, were you reading the DB and writing the dump file from the same file system? Different partitions on the same disk? The 400GB is presumably a RAID1+0 or RAID5? If that's the case then, I would highly recommend having a separate physical drive/file system for writing backups to (from which your actual backup software should be pointed). Maybe even put it on a different channel on the same controller, or a different controller alltogether.. Obviously, the biggest thing missing from a lot of PostgreSQL documentation is practicle information for large deployments, including strategy and system design requirements. All in due-time I suppose. ~BAS Any reason why Postgresql would only get 2.8MB/sec for dumps or slower? Regards, Link. ---(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 l8* -lava x.25 - minix - bitnet - plan9 - 110 bps - ASR 33 - base8 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Constraint that compares and limits field values
I have a table that I am using to hold keys for M:M relationships. I have six fields that can hold the keys and I do this because I validate the key with a foreign key constraint. Fields evevid1, evevid2 hold keys from the event table, evreid1, evreid2 hold keys from the resource table, etc. The 0 works with the FK constraints because in each table being referenced I have a record with id = 0 that is empty. Each row should only have two foreign key values and the other key field values are 0. How do I put a constraint on the Insert / Update to make sure that only two fields out of the six have a value > 0 in them. I want to make sure that one of these three fields has a value > 0 and the other two are 0. evevid1 int4 evenid1 int4 evreid1 int4 Same here. I want to make sure that one of these three fields has a value > 0 and the other two are 0. evevid2 int4 evenid2 int4 evreid2 int4 Please reply to me as well as the list because I get the digest version and it might take a while to get the reply there. Thank you, *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message.
Re: [GENERAL] user defined function
For what it's worth, the next release of PL/Java has support for both RECORD parameters and SETOF RECORD return types. The adventurous can try out the current CVS HEAD. Regards, Thomas Hallgren Tom Lane wrote: Yl Zhou <[EMAIL PROTECTED]> writes: I want to implement a UDF that can accept a parameter which is a tuple of any table, and returns the number of NULL attributes in this tuple. Different tables may have different schemas. How can I implement this function? Thanks. You could do that in C, but none of the available PLs support it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] user defined function
Can anyone tell me whether 7.3 supports unspecified record types or not? On 1/24/06, Tom Lane <[EMAIL PROTECTED]> wrote: Yl Zhou <[EMAIL PROTECTED]> writes:> Do you mean this function? Seems I cannot get much information from it...That would appear to be Postgres 7.3 :-( You need a considerably newer version of Postgres if you want to do muchof anything useful with unspecified-type records. 8.0 has most of thatfunctionality but I think 8.1 added some things.regards, tom lane
Re: [GENERAL] Problems with pgsql 8.0.4 & freebsd 6: partially solved
On Jan 24, 2006, at 7:47 AM, Vittorio wrote: Yesterday, I upgraded freebsd from 5.4 to 6 via the CD (the iso was downloaded from ftp.freebsd.org). I've done this many times with no problems. However. You serve yourself very well by recompiling all your ports or reinstalling the packages to be the ones built against 6.0. This is especially necessary for libraries and programs that link against those libraries and those programs that dynamically load extensions (such as postgres). Otherwise you're forcing two copies of libc to load at the same time in some cases and that causes no end of confusion. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] user defined function
Yl Zhou <[EMAIL PROTECTED]> writes: > But I have to use 7.3 due to some limitations. Can I do it in 7.3? Probably, but I forget how (and I can guarantee that it will break when you do move to 8.0 or later, because we changed the internal representation of rowtype arguments). You'd be *much* better off to spend your time fixing whatever it is that's keeping you on 7.3. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] user defined function
On Tue, 2006-01-24 at 14:38, Tom Lane wrote: > Yl Zhou <[EMAIL PROTECTED]> writes: > > But I have to use 7.3 due to some limitations. Can I do it in 7.3? > > Probably, but I forget how (and I can guarantee that it will break > when you do move to 8.0 or later, because we changed the internal > representation of rowtype arguments). You'd be *much* better off to > spend your time fixing whatever it is that's keeping you on 7.3. For some reason I'm remember 7.4 as being the first version that let you do this. Not for certain. I didn't run 7.3 in production though, so I might have missed it if it could do this. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Interpreting pg_locks; looking for deadlock
I have a postgresql 7.4.8 database which has the same table declarations in several schemas. My application accesses each schema from a single thread; there is never more than one thread accessing a schema at a time. To try increasing concurrency, I've tried using multiple threads per schema. The application quickly locked up, and I suspect deadlock, but pg_locks doesn't seem to show deadlock. My pg_locks query is as follows: selectns.nspname as "schema", c.relname as "table", L.transaction, L.pid, L.mode, L.granted from pg_locks L, pg_class c, pg_namespace ns where L.relation = c.oid and c.relnamespace = ns.oid and ns.nspowner >= 100 The output looks something ilke this: schema | table | transaction | pid | mode | granted --+-+-+---+--+- schema_1 | idx_e | | 24058 | AccessShareLock | t schema_1 | d | | 24084 | AccessShareLock | t schema_1 | d | | 24084 | RowExclusiveLock | t schema_1 | e | | 24084 | AccessShareLock | t schema_1 | e | | 24084 | RowExclusiveLock | t schema_1 | e | | 24008 | AccessShareLock | t schema_1 | e | | 24008 | RowExclusiveLock | t schema_1 | idx_e | | 24081 | AccessShareLock | t schema_1 | m | | 24065 | ShareUpdateExclusiveLock | t schema_1 | idx_e | | 24091 | AccessShareLock | t schema_1 | m | | 24065 | ShareUpdateExclusiveLock | t schema_1 | idx_e | | 24008 | AccessShareLock | t schema_1 | idx_e | | 24059 | AccessShareLock | t schema_1 | idx_e | | 24071 | AccessShareLock | t schema_1 | idx_e | | 24037 | AccessShareLock | t d, e, and m are tables, idx_e is an index on the table e. What puzzles me is that there are no 'f' entries in the granted column. (This is partial output, but the complete output has no f entries.) If this is deadlock, then why don't I see granted = 'f'? And if it isn't deadlock, then why do so many backend processes appear to be stuck, e.g. (ps output): 24057 ?S< 0:01 postgres: risdba ris 127.0.0.1 UPDATE waiting 24058 ?S< 0:01 postgres: risdba ris 127.0.0.1 UPDATE waiting 24059 ?S< 0:01 postgres: risdba ris 127.0.0.1 UPDATE waiting Jack Orenstein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] FATAL: invalid frontend message type 47
After a commit I receive this message: "FATAL: invalid frontend message type 47", and rollback. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] FATAL: invalid frontend message type 47
On Tue, Jan 24, 2006 at 11:50:39PM -0500, Sterpu Victor wrote: > After a commit I receive this message: > "FATAL: invalid frontend message type 47", and rollback. The client seems to have violated the communications protocol. What client interface are you using? What version of PostgreSQL (both client and server versions if they're different)? What platform(s)? How are you connecting to the database (Unix socket, TCP socket, SSL over TCP, etc.)? How consistently do you get this error? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Are indexes used with LIKE?
On Mon, Jan 23, 2006 at 08:00:01PM +0100, Kovcs Pter wrote: > Are indexes on VARCHAR columns used with the LIKE operator, and if so, > how efficiently are they used? > > I can imagine that using indexes can be easy with the starting literal > characters up to the first percent sign such as in: > > LIKE 'ZOE%QQWE%' > > But, after the first % sign, things can get more difficult. The planner can use an index on the starting literal characters; how "difficult" the query becomes after that depends on how discriminating those initial characters are. If values matching the initial characters comprise a small fraction of the table then the query will probably use an index and be fast, but if they comprise a large fraction of the table, or if the search string starts with a wildcard, then you'll get a sequential scan, which might be slow. > The reason I am asking is that we are thinking about discriminating > between rows of a table based on a VARCHAR column containing various > one-character "flags". We could then use the LIKE operator for > formulating filter conditions. Have you considered putting each flag in a separate column and indexing those columns? If you're using 8.1 the planner would probably use bitmap index scans and come up with a fast plan regardless of which columns you restrict on. And performance issues aside, some people would consider that a better design. However, a disadvantage might be that your queries would be more complex. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Installing Postgres 8.1 on Windows Server 2003 R2
Connections are through localhost. We've also connected via a client machine through a router to insure it is not something on the server. By drop connections, I mean Task Manager is showing additional postgres.exe tasks after the completion of the test. Also, when we try to drop the DB to reset for another test, PGAdmin reports connections. Finally, of the 20 virtual users, any where from 2 to 12 or so will successfully complete the test (by adding information to the DB via our PHP app). The postmaster can be shut down manually with no issues after the test. However, upon rebooting the machine, I get IIS Helper Failed messages. I don't know if this is related or not. Logs don't really show anything. At least nothing I can find. The only changes to the config file was to enable the logging at verbose and info levels. I can send the file if you'd like to see it. I've noticed a number of issues about beta releases dealing with sockets and such. I haven't found anything indicating they were fixed or if there are work arounds. -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 3:09 AM To: Carl Conard Cc: Postgres User; Magnus Hagander; pgsql-general@postgresql.org Subject: Re: [GENERAL] Installing Postgres 8.1 on Windows Server 2003 R2 Carl Conard wrote: > I've successfully installed Postgres 8.1.2 on WS 2003 R2 on a Lenovo > (IBM) ThinkPad. I used the default installation options and everything > seems peachy keen for a single user (using localhost). > > However, when we started performance testing Postgres (vs. MySQL) using > a 3rd party tool (I forget the name) running on the server, Postgres > would drop connections after about the 10th or 11th virtual user (of 20) > hit the DB. The remaining user processes would remain visible in > Window's Task Manager and just "hang." How are you connecting? What do you mean by "drop connections"? What do you mean by "hang" - does the server have problems shutting them down if you tell it to? What do the logs show? What changes have you made to your configuration files? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] pgxml
I’ am looking for pgxml module for postgres 7.2.1, someone can help me to find it? Thanks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] NOT HAVING clause?
On 2006-01-24, Will Glynn <[EMAIL PROTECTED]> wrote: > You might try: > > SELECT some_column > FROM some_table > GROUP BY some_column > HAVING SUM(CASE WHEN sort_order=1 THEN 1 ELSE 0 END) = 0; SELECT some_column FROM some_table GROUP BY some_column HAVING every(sort_order <> 1); every() is in 8.1 at least (can't recall when it was introduced); it's the same as bool_and(), i.e. an aggregate that returns true only if all inputs are true. Why isn't there a corresponding any(), I wonder? (bool_or does exist) It should be possible to define bool_and() or every() in the usual way as a custom aggregate even on versions that don't have them builtin. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Setting expire date on insert/modify
I'm having a problem converting a simple date routing to PLPGSQL. I know the error has to be something stupid but I'm missing it. The error is in the "ExpireDate := (date 'StartDate' + integer 'NumOfDays');" line in the procedure below. I didn't understand why I had to convert the date to a string and back to a date but ok. Below is the error I'm getting a test and please tell me where I'm going wrong. Also I include the trigger procedure, trigger and insert. Please remember that I'm a MS-SQL guy migrating to PostgreSQL. ERROR: invalid input syntax for type date: "StartDate" CONTEXT: SQL statement "SELECT (date 'StartDate' + integer 'NumOfDays')" PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"() RETURNS "trigger" AS $BODY$ DECLARE ExpireDate timestamptz; -- Date the Banner Ad will expire. StartDate char(10); -- Date the Banner Ad was created or renewed NumOfDays char(10); -- Number of Dates the Ad will be in place. BEGIN StartDate := to_char(NEW.bannerad_creationdate, '-MM-DD'); NumOfDays := to_char(NEW.bannerad_term, '9'); ExpireDate := (date 'StartDate' + integer 'NumOfDays'); IF (TG_OP = 'UPDATE') THEN UPDATE public.bannerad_ads SET bannerad_expiredate = ExpireDate WHERE bannerad_id = OLD.bannerad_id; ELSIF (TG_OP = 'INSERT') THEN UPDATE public.bannerad_ads SET bannerad_expiredate = ExpireDate WHERE bannerad_id = NEW.bannerad_id; END IF; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER "Trg_BannerAd_Ads_InsMod" BEFORE INSERT OR UPDATE ON bannerad_ads FOR EACH ROW EXECUTE PROCEDURE public."TF_BannerAd_Ads_InsMod"(); insert into bannerad_ads (bannerad_href,bannerad_alttext,bannerad_filename,bannerad_creationdate, bannerad_term) values ('http://www.equilt.com','ElectricQuilt Southern Music','EQMBannerAd4.gif','2006-01-20 01:00:00-05',18250); -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Setting expire date on insert/modify
On Jan 25, 2006, at 9:45 , Foster, Stephen wrote: ERROR: invalid input syntax for type date: "StartDate" CONTEXT: SQL statement "SELECT (date 'StartDate' + integer 'NumOfDays')" PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment Why are you single-quoting StartDate and NumOfDays? Single-quotes are generally used for string literals (e.g., text and varchar types). If you are trying to preserve the case of the variable name, you need to (always) use double-quotes, including when you define them. Hope this helps a bit. I haven't looked through all of the code, so there may be other bugs lurking, but this is definitely one of the problems. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Setting expire date on insert/modify
Michael, I tried that line in the trigger procedure with double quotes, single quotes and without. The only way it would save was with single quotes and that is why you saw it that way. I know it has to be some sort of stupid syntax error but since I'm new to PostgreSQL (as far as this level of coding) I have no idea what my error is. If I try to save without which was what I did in the first place I receive this using "ExpireDate := (date StartDate + integer NumOfDays);" ERROR: syntax error at or near "$1" at character 16 QUERY: SELECT (date $1 + integer $2 ) CONTEXT: SQL statement in PL/PgSQL function "TF_BannerAd_Ads_InsMod" near line 8 Full procedure again: CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"() RETURNS "trigger" AS $BODY$ DECLARE ExpireDate timestamptz; -- Date the Banner Ad will expire. StartDate char(10); -- Date the Banner Ad was created or renewed NumOfDays char(10); -- Number of Dates the Ad will be in place. BEGIN StartDate := to_char(NEW.bannerad_creationdate, '-MM-DD'); NumOfDays := to_char(NEW.bannerad_term, '9'); ExpireDate := (date StartDate + integer NumOfDays); IF (TG_OP = 'UPDATE') THEN UPDATE public.bannerad_ads SET bannerad_expiredate = ExpireDate WHERE bannerad_id = OLD.bannerad_id; ELSIF (TG_OP = 'INSERT') THEN UPDATE public.bannerad_ads SET bannerad_expiredate = ExpireDate WHERE bannerad_id = NEW.bannerad_id; END IF; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; The hard bits I'm getting easily but I keep getting hung up over simple junk. Thanks for the help, Lee Foster/ -Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 6:58 PM To: Foster, Stephen Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Setting expire date on insert/modify On Jan 25, 2006, at 9:45 , Foster, Stephen wrote: > ERROR: invalid input syntax for type date: "StartDate" > CONTEXT: SQL statement "SELECT (date 'StartDate' + integer > 'NumOfDays')" > PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment Why are you single-quoting StartDate and NumOfDays? Single-quotes are generally used for string literals (e.g., text and varchar types). If you are trying to preserve the case of the variable name, you need to (always) use double-quotes, including when you define them. Hope this helps a bit. I haven't looked through all of the code, so there may be other bugs lurking, but this is definitely one of the problems. Michael Glaesemann grzm myrealbox com -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Postgresql Segfault in 8.1
I'm running PostgreSQL 8.1 on CentOS 4.2, Dual proc Athlon 64 w/4 GB RAM. I'm trying to get a PHP app to work, but the failure happens when the command is copy/pasted into pgsql. Trying to run a large insert statement, and I get: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. # in /var/log/messages, I see Jan 24 17:00:04 kepler kernel: postmaster[26185]: segfault at 2516d728 rip 0043c82c rsp 007fbfffddd0 error 4 The insert statement is long, but doesn't seem to violate anything strange - no weird characters, and all the fields have been properly escaped with pg_escape(). What information do you need to help figure this out? -Ben -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Setting expire date on insert/modify
On Jan 25, 2006, at 10:12 , Foster, Stephen wrote: If I try to save without which was what I did in the first place I receive this using "ExpireDate := (date StartDate + integer NumOfDays);" ERROR: syntax error at or near "$1" at character 16 QUERY: SELECT (date $1 + integer $2 ) CONTEXT: SQL statement in PL/PgSQL function "TF_BannerAd_Ads_InsMod" near line 8 The common ways of casting in PostgreSQL are some_value::some_type, e.g., "ExpireDate"::date, or using CAST, e.g., CAST ("ExpireDate" as date). In the general case, some_type some_value will not work. The docs can also be very helpful. I myself just looked up the syntax of the CAST expression (I generally use the double-colon method). See if that makes a difference. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Setting expire date on insert/modify
On Tue, 24 Jan 2006, Foster, Stephen wrote: > CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"() > RETURNS "trigger" AS > $BODY$ > DECLARE > ExpireDate timestamptz; -- Date the Banner Ad will expire. > StartDate char(10); -- Date the Banner Ad was created or > renewed > NumOfDays char(10); -- Number of Dates the Ad will be in > place. > BEGIN > StartDate := to_char(NEW.bannerad_creationdate, '-MM-DD'); > NumOfDays := to_char(NEW.bannerad_term, '9'); > ExpireDate := (date 'StartDate' + integer 'NumOfDays'); I think something like: ExpireDate := CAST(NEW.bannerad_creationdate as Date) + NEW.bannerad_term; may work for you. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Postgresql Segfault in 8.1
Benjamin Smith <[EMAIL PROTECTED]> writes: > in /var/log/messages, I see > > Jan 24 17:00:04 kepler kernel: postmaster[26185]: segfault at > 2516d728 > rip 0043c82c rsp 007fbfffddd0 error 4 > > The insert statement is long, but doesn't seem to violate anything strange - > no weird characters, and all the fields have been properly escaped with > pg_escape(). > > What information do you need to help figure this out? Reproduce it with gdb attached to the backend process and post the backtrace... You may need to recompile PG with debugging symbols to get the most info. -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Setting expire date on insert/modify
Thanks guys it finally worked correctly. Just in case someone else get hung on this type of thing here is the working trigger function. CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"() RETURNS "trigger" AS $BODY$ DECLARE ExpireDate timestamptz; -- Date the Banner Ad will expire. BEGIN NEW.bannerad_expiredate := (NEW.bannerad_creationdate::date + NEW.bannerad_term::int4); RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stephan Szabo Sent: Tuesday, January 24, 2006 7:18 PM To: Foster, Stephen Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Setting expire date on insert/modify On Tue, 24 Jan 2006, Foster, Stephen wrote: > CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"() > RETURNS "trigger" AS > $BODY$ > DECLARE > ExpireDate timestamptz; -- Date the Banner Ad will expire. > StartDate char(10); -- Date the Banner Ad was created or > renewed > NumOfDays char(10); -- Number of Dates the Ad will be in > place. > BEGIN > StartDate := to_char(NEW.bannerad_creationdate, '-MM-DD'); > NumOfDays := to_char(NEW.bannerad_term, '9'); > ExpireDate := (date 'StartDate' + integer 'NumOfDays'); I think something like: ExpireDate := CAST(NEW.bannerad_creationdate as Date) + NEW.bannerad_term; may work for you. ---(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 -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pgxml
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > Iâ am looking for pgxml module for postgres 7.2.1, someone can help me > to find it? *Please* tell me you are not still using PG 7.2.1. There are seven subsequent releases in the 7.2 series, each containing fixes for very serious bugs --- see http://developer.postgresql.org/docs/postgres/release-7-2-8.html and following pages. Furthermore, 7.2.* has officially been abandoned as unsupported: there are numerous known bugs in 7.2.8 that aren't going to be fixed, ever. Rather than looking for pgxml for 7.2, you desperately need to be spending your time on updating to some non-stone-age version of Postgres. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Setting expire date on insert/modify
"Foster, Stephen" <[EMAIL PROTECTED]> writes: > Michael, I tried that line in the trigger procedure with double quotes, > single quotes and without. The only way it would save was with single > quotes and that is why you saw it that way. Just for reference, the thing that was tripping you up (or one thing that was tripping you up at least) was trying to extrapolate the syntax typename 'literal value' to situations where you didn't mean an actual literal constant. This syntax is something that's forced on us by the SQL standard, but we do not support it for anything but literal constants (which is all that the standard defines it for, either). In the examples you gave, you wanted to coerce the value of a variable of one type to some other type, and so you should have written either CAST( variablename AS typename ) or variablename :: typename the former being the SQL-spec syntax for a runtime type conversion and the latter being a traditional Postgres abbreviation. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: hardware checks (was Re: [GENERAL] invalid memory alloc request
Greg Stark wrote: > Tom Lane <[EMAIL PROTECTED]> writes: > > > Janning Vygen <[EMAIL PROTECTED]> writes: > > > one more question: You mentioned standard disk and memory checks. Can you > > > point to some link where i can find more about it or which software do > > > you > > > mean? I guess i have to start looking at it. > > > > The stuff I've heard recommended is memtest86 for memory checks and > > badblocks for disk checks. But perhaps someone on the list has better > > ideas. > > I second memtest86, though even the author says memory errors can be tricksy > things. Sometimes a large compile finds memory errors that even memtest86 > doesn't find (the symptom is gcc crashing). > > However I fear using badblocks alone is pretty useless these days. Modern IDE > drives detect bad blocks and remap them to other locations. If you just use > badblocks you'll see mysterious errors that disappear or might not see any > errors at all. You need to use tools like smartctl to query the drive's SMART > firmware about errors. It's not easy to interpret but if you watch the numbers > for a while you can tell if a drive is going bad and continually remapping bad > blocks. badblocks is useful still as a way of ensuring that every block is > read and written to, but then you have to look at the SMART data to see what > happened. It is my experience the SCSI drive controllers will beep if they have a bad block that can't be read cleanly. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgresql Segfault in 8.1
Benjamin Smith <[EMAIL PROTECTED]> writes: > What information do you need to help figure this out? Best is to provide a recipe by which someone else can reproduce it from a standing start. You can find some useful hints at http://www.postgresql.org/docs/8.1/static/bug-reporting.html regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Does this look ethical to you?
The people who develop, package, and host pginstaller files are doing so to promote open source software for users, not to help you sell commercial software. For that, you are on your own. If you want to find volunteers to help you promote and sell your software, good luck. :-) --- Tony Caduto wrote: > Magnus Hagander wrote: > > We (pginstaller hat goes on) don't know of any competing products. We > > will be happy to consider bundling any competing product, including PG > > Lightning Admin. One of the most important things in order to be > > distributed as part of an open source product is that the parts are open > > source. If PGLA (or a lite version if necessary) is available under an > > OSS license, we'll definitly consider bundling it. > > (We have considered bundling phpPgAdmin, but haven't found a good way to > > do it without dragging in a huge load of dependencies) > > I don't wan't to be bundled, I just want it to be known that there are > alternatives available. > > Postgresql is free, so what is the big deal about letting users know > about alternative admin tools comercial or open source? It's not like > it would put pgAdmin or Postgresql out of business(it could only help). > > It's a shame you don't see how the bundling of pgAdmin(in the current > way) is hurting the 3rd party community. > > All that would be needed is a installer section at the end saying > something like: > In addition to pgAdmin III there are other opensource and commercial > admin products available, you can get more information here to postgresql home page>. > > I don't see that as being detrimental to anyone and would certainly be > in fair play. > > -- > Tony Caduto > AM Software Design > Home of PG Lightning Admin for Postgresql > http://www.amsoftwaredesign.com > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Postgresql Segfault in 8.1
Thanks, What's the best way to do this? Take PG down (normally started as a service) and run directly in a single-user mode? I've never reallly worked with gdb... -Ben On Tuesday 24 January 2006 17:27, you wrote: > > What information do you need to help figure this out? > > Reproduce it with gdb attached to the backend process and post the > backtrace... You may need to recompile PG with debugging symbols to > get the most info. -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] hi all......................!!
hello everybody i am new to this mailing list. this is my first mail to this group. i jussst want to confirm that whether is it possible to update a view or not?? i think you all help me in solving my queries in future...!! -- Thanks & Regards, Akhilesh DAV Institute of Management Faridabad(Haryana) GSM:-(+919891606064) (+911744293789) "FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
Re: [GENERAL] hi all......................!!
AKHILESH GUPTA schrieb: > hello everybody > i am new to this mailing list. this is my first mail to this group. > i jussst want to confirm that whether is it possible to update a view or > not?? > i think you all help me in solving my queries in future...!! Yes it is. All you have to do is to add a rule for updating in the way you want it to work. When you use pgadmin3 or such tools, you see that a view is basically a table with one rule for select. For Insert, update you can add a rule any time. HTH Tino Wildenhain ---(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