[SQL] loop query results
With the following function I expected to get an iteration through the results. But the loop seems to be performed two times instead. The function (postgres 7.2): create function echt_char_laenge () returns integer as ' declare char_auslese record; begin for char_auslese in select * from varlaenge loop raise notice ''%, %'', char_auslese.id_var, char_auslese.var_text; end loop; return 0; end; ' language 'plpgsql'; the result: select echt_char_laenge (); NOTICE: 1, text 1X NOTICE: 2, text 2X NOTICE: 3, text 3X NOTICE: 4, text 4X NOTICE: 5, text 5X NOTICE: 6, text 6X NOTICE: 1, text 1X NOTICE: 2, text 2X NOTICE: 3, text 3X NOTICE: 4, text 4X NOTICE: 5, text 5X NOTICE: 6, text 6X echt_char_laenge -- 0 (1 row) -- and the result as expected: select * from varlaenge; id_var | var_text +- 1 | text 1X 2 | text 2X 3 | text 3X 4 | text 4X 5 | text 5X 6 | text 6X (6 rows) Any ideas? Thanx Stefan __ Schneller als andere! Die Blitz-SMS von WEB.DE FreeMail! http://freemail.web.de/features/?mc=021167 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] loop query results
On Wed, 4 Dec 2002, Stefan Reuschke wrote: > With the following function I expected to get an iteration through the > results. But the loop seems to be performed two times instead. I tried you exact example in 7.2.1 and got every row only once. It doesn't help you find the error perhaps, but it doesn't hurt to know that it works as intended here. -- /Dennis ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How does postgres handle non literal string values
Vernon: Agreed. We use Struts (as our MVC framework), and then a data access layer (we call persistables) that uses the PreparedStatements. Our JSPs only get data to render *after* the business logic has decided that all logic has been performed successfully. The end-result is easily compartmentalized code (lots of code factoring) that makes for very robust applications. We are working on moving this framework to PostgreSQL (from Oracle) and we expect to have to touch the SQL statements (which are each in their own class, again for re-use) and perhaps two or three other classes to deal with any JDBC driver issues. When we make the transition successfully, I hope to be able to publicize the work and the value of PostgreSQL. Charlie Vernon Wu wrote: In general, it isn't a good idea to have SQL statements in JSP files. A good practise is using Mode 2. The Struts is a popular Mode 2 framework. If your application is very small and it won't grow into a big one, you can get around using Mode 1. In the situation, the SQL tags of JSTL will be a recommeded mechanism. 11/26/2002 8:05:27 AM, "Charles H. Woloszynski" <[EMAIL PROTECTED]> wrote: Actually, we use JDBC Prepared Statements for this type of work. You put a query with '?' in as placeholders and then add in the values and the library takes care of the encoding issues. This avoids the double encoding of (encode X as String, decode string and encode as SQL X on the line). There was a good article about a framework that did this in JavaReport about a 18 months ago. We have gleaned some ideas from that article to create a framework around using PreparedStatements as the primary interface to the database. I'd suggest looking at them. They really make your code much more robust. Charlie "')..." You *will* want to escape the username and password otherwise I'll be able to come along and insert any values I like into your database. I can't believe the JDBC classes don't provide 1. Some way to escape value strings 2. Some form of placeholders to deal with this -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem with view in PostgreSQl 7.3
I am sending a shema of used table. schema0.sql Description: Binary data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How does postgres handle non literal string values
When the MVC pattern is employed and EJB is absented, the DAO pattern shall be utilized. With this system design, the DB routines are isolated from the rest of system for the purpose of "robust" as you mentioned. In the future, you only need to write another set of DAO implemention if you decide to use another DB, say SAP DB for example. A DB connection pool also is desired, which shall take care of the JDBC driver look up. 12/4/2002 5:00:18 AM, "Charles H. Woloszynski" <[EMAIL PROTECTED]> wrote: >Vernon: > >Agreed. We use Struts (as our MVC framework), and then a data access >layer (we call persistables) that uses the PreparedStatements. Our JSPs >only get data to render *after* the business logic has decided that all >logic has been performed successfully. > >The end-result is easily compartmentalized code (lots of code factoring) >that makes for very robust applications. We are working on moving this >framework to PostgreSQL (from Oracle) and we expect to have to touch the >SQL statements (which are each in their own class, again for re-use) and >perhaps two or three other classes to deal with any JDBC driver issues. > When we make the transition successfully, I hope to be able to >publicize the work and the value of PostgreSQL. > >Charlie > >Vernon Wu wrote: > >>In general, it isn't a good idea to have SQL statements in JSP files. A good >practise is using Mode 2. The Struts is a >>popular Mode 2 framework. If your application is very small and it won't grow into a >big one, you can get around using >>Mode 1. In the situation, the SQL tags of JSTL will be a recommeded mechanism. >> >>11/26/2002 8:05:27 AM, "Charles H. Woloszynski" <[EMAIL PROTECTED]> wrote: >> >> >> >>>Actually, we use JDBC Prepared Statements for this type of work. You >>>put a query with '?' in as placeholders and then add in the values and >>>the library takes care of the encoding issues. This avoids the double >>>encoding of (encode X as String, decode string and encode as SQL X on >>>the line). There was a good article about a framework that did this in >>>JavaReport about a 18 months ago. >>> >>>We have gleaned some ideas from that article to create a framework >>>around using PreparedStatements as the primary interface to the >>>database. I'd suggest looking at them. They really make your code much >>>more robust. >>> >>>Charlie >>> >>> >>> >>> "')..." You *will* want to escape the username and password otherwise I'll be able to come along and insert any values I like into your database. I can't believe the JDBC classes don't provide 1. Some way to escape value strings 2. Some form of placeholders to deal with this >>>-- >>> >>> >>>Charles H. Woloszynski >>> >>>ClearMetrix, Inc. >>>115 Research Drive >>>Bethlehem, PA 18015 >>> >>>tel: 610-419-2210 x400 >>>fax: 240-371-3256 >>>web: www.clearmetrix.com >>> >>> >>> >>> >>> >>>---(end of broadcast)--- >>>TIP 5: Have you checked our extensive FAQ? >>> >>>http://www.postgresql.org/users-lounge/docs/faq.html >>> >>> >>> >> >> >> >> >>---(end of broadcast)--- >>TIP 6: Have you searched our list archives? >> >>http://archives.postgresql.org >> >> > >-- > > >Charles H. Woloszynski > >ClearMetrix, Inc. >115 Research Drive >Bethlehem, PA 18015 > >tel: 610-419-2210 x400 >fax: 240-371-3256 >web: www.clearmetrix.com > > > > > >---(end of broadcast)--- >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Query about table and catalog
Hi; I've got a table with several fields. Among others there are the fields 'soil1', 'soil2', and 'soil3' that are char type. A row can have an empty value in one of these fields, or the three fields can have valid values: cod_grass |suelo1|suelo2 | suelo3 ---+-+-+- 2590 | Xerosoles petrocalcicos| | 181 | Xerosoles calcicos | | 265 | Xerosoles petrocalcicos | | 593 | Zona urbana | | 1112 | Cambisoles calcicos | | 2 | Litosoles | | 3 | Xerosoles calcicos|| 4 | Litosoles | Rendsinas aridicas | 5 | Xerosoles petrocalcicos | | 6 | Litosoles | | 7 | Regosoles calcaricos | Xerosoles calcicos ... In other table I've got a catalog of posible soil types, assigning an integer value to each of possible soil types. tipo_suelo | cod_tipo_suelo -+ Arenosoles albicos | 1 Cambisoles calcicos | 2 Cambisoles eutricos | 3 Is it possible to prepare a query that show the contents of the table of soils and aditional columns after each of the soils fields, showing the corresponding numerical code for that soil, extracted from the catalog? I just know how to do this for one of the soils: SELECT cod_grass, suelo1,cod_tipo_suelo AS cod_suelo1 FROM suelos,suelos_catalogo WHERE suelo1=tipo_suelo ORDER BY cod_grass; But I would like to do the same for the three at a time. Thanks for your help and have a nice day. Javier . ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] problem with view in 7.3
"Alphasoft" <[EMAIL PROTECTED]> writes:
> In 7.3 when i am trying to recreate the view I received the folowing error :
> find_expr_references_walker: bogus varno 8
Found it --- thanks for the test case. The patch is attached, if you
want to apply it locally.
regards, tom lane
*** src/backend/catalog/dependency.c.orig Sat Sep 21 20:37:09 2002
--- src/backend/catalog/dependency.cWed Dec 4 14:45:58 2002
***
*** 789,794
--- 789,799
else if (rte->rtekind == RTE_JOIN)
{
/* Scan join output column to add references to join inputs */
+ List *save_rtables;
+
+ /* We must make the context appropriate for join's level */
+ save_rtables = context->rtables;
+ context->rtables = rtables;
if (var->varattno <= 0 ||
var->varattno > length(rte->joinaliasvars))
elog(ERROR, "find_expr_references_walker: bogus
varattno %d",
***
*** 796,801
--- 801,807
find_expr_references_walker((Node *) nth(var->varattno - 1,
rte->joinaliasvars),
context);
+ context->rtables = save_rtables;
}
return false;
}
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Ran out of connections
Hi all. Twice this week, I've come to work to find my Postgres server out of connections... effectively freezing my web server. Today, before I rebooted the entire system, I did a ps -auxw and kept the file to study. I didn't find too many clients running. But I did find a whole LOT of postgres processes running, idle. BTW, one of the postgres processes was doing a vacuum analyze. I'm running 7.2. Can anyone tell me how to fix this? The out put of the ps command can be seen at http://dominion.dyndns.org/~mdiehl/ps.txt Thanx in advance, -- Mike Diehl Network Tools Devl. SAIC at Sandia Labs (505) 284-3137 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Ran out of connections
On Wed, Dec 04, 2002 at 03:08:35PM -0700, Mike Diehl wrote: > Hi all. > > Twice this week, I've come to work to find my Postgres server out of > connections... effectively freezing my web server. > > Today, before I rebooted the entire system, I did a ps -auxw and kept the > file to study. I didn't find too many clients running. But I did find a > whole LOT of postgres processes running, idle. BTW, one of the postgres > processes was doing a vacuum analyze. I'm running 7.2. > > Can anyone tell me how to fix this? The out put of the ps command can be > seen at http://dominion.dyndns.org/~mdiehl/ps.txt Are you using PHP by chance? I've seen this behavior under Apache+PHP before. My "fix" (workaround rather) was to disable persistent connections. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + :) :D :O :( :[ ;) 8) B) :> |I :P =) :S :B :] :\ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Ran out of connections
You probably didn't need to reboot - I suspect you could have probably restarted PostgreSQL and Apache (quick version) or killed the extra postgres processes. I suspect you need to look carefully at your code and method of connecting (ie. are you using mod-perl, plain old cgi perl, PHP or what). A problem with Apache 1.x is that of connection pooling/persistence. If you don't use persistent connections you suffer the overhead of opening and closing the database connection as required by your web process. You can use mod-perl and keep your connections open but if your pool of web processes that have open connections exceeds the number of connections allowed by PostgreSQL you will have problems (pooling of database connections between processes is problematic so each Apache process has its own connection). Be sure that there isn't a bug causing a cgi to abort leaving a stranded connection. I don't have experience with local unix socket connections where the client has died but when I have windoze users reboot when they had an open connection it will wait till the TCP/IP connection times out (frequently for an hour) before the PostgreSQL connection is closed. If the timeout is similar for local unix sockets then a failure in the cgi could leave open connections and you will run out quickly. If you are doing lots of database backed work you may want to check out AOLserver (http://www.aolserver.com/). It has a multi-threaded architecture featuring connection pooling and persistence "out of the box." Oh, it's free/open-source as well. Of course you can also get pooling/persistence with enterprise Java solutions such as JBoss (www.jboss.org). Cheers, Steve On Wednesday 04 December 2002 2:08 pm, Mike Diehl wrote: > Hi all. > > Twice this week, I've come to work to find my Postgres server out of > connections... effectively freezing my web server. > > Today, before I rebooted the entire system, I did a ps -auxw and kept the > file to study. I didn't find too many clients running. But I did find a > whole LOT of postgres processes running, idle. BTW, one of the postgres > processes was doing a vacuum analyze. I'm running 7.2. > > Can anyone tell me how to fix this? The out put of the ps command can be > seen at http://dominion.dyndns.org/~mdiehl/ps.txt > > Thanx in advance, ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Ran out of connections
Once your done scoping other things out, you might also want to look at increasing the number of allowed connections (in postgresql.conf). The defaults can be low for high traffic systems. Robert Treat On Wed, 2002-12-04 at 17:29, Steve Crawford wrote: > You probably didn't need to reboot - I suspect you could have probably > restarted PostgreSQL and Apache (quick version) or killed the extra postgres > processes. > > > Cheers, > Steve > > > On Wednesday 04 December 2002 2:08 pm, Mike Diehl wrote: > > Hi all. > > > > Twice this week, I've come to work to find my Postgres server out of > > connections... effectively freezing my web server. > > > > > > Can anyone tell me how to fix this? The out put of the ps command can be > > seen at http://dominion.dyndns.org/~mdiehl/ps.txt > > > > Thanx in advance, > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Ran out of connections
On Wednesday 04 December 2002 03:25 pm, Roberto Mello wrote: > On Wed, Dec 04, 2002 at 03:08:35PM -0700, Mike Diehl wrote: > > Can anyone tell me how to fix this? The out put of the ps command > > can be seen at http://dominion.dyndns.org/~mdiehl/ps.txt > > Are you using PHP by chance? I've seen this behavior under Apache+PHP > before. My "fix" (workaround rather) was to disable persistent > connections. Nope. I'm using Perl and cgi. I've got some perl that runs via cron, and some more that runs via apache. I'm not even using ModPerl. It did occur to me that since some of my scripts communicate with other devices, that I may have some IO blocking, or zombies, but the ps output didn't indicate that. I can't see that many scripts running. Usually, I see one postgres process for each script/cgi that is running. Not in this case. -- Mike Diehl Network Tools Devl. SAIC at Sandia Labs (505) 284-3137 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Ran out of connections
On Wed, Dec 04, 2002 at 02:29:25PM -0800, Steve Crawford wrote: > > If you are doing lots of database backed work you may want to check out > AOLserver (http://www.aolserver.com/). It has a multi-threaded architecture > featuring connection pooling and persistence "out of the box." Oh, it's > free/open-source as well. I second that suggestion. Having used AOLserver for the past few years, it's a very nice application/web server with superb database support. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Itsdifficulttobeverycreativewithonlyfiftysevencharacters! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Ran out of connections
Doing anything unusual? Forking processes, opening multiple connections within a single CGI? Have you seen any evidence that a process that opens a connection is failing to complete normally? -Steve On Wednesday 04 December 2002 3:52 pm, Mike Diehl wrote: > On Wednesday 04 December 2002 03:25 pm, Roberto Mello wrote: > > On Wed, Dec 04, 2002 at 03:08:35PM -0700, Mike Diehl wrote: > > > Can anyone tell me how to fix this? The out put of the ps command > > > can be seen at http://dominion.dyndns.org/~mdiehl/ps.txt > > > > Are you using PHP by chance? I've seen this behavior under > > Apache+PHP before. My "fix" (workaround rather) was to disable > > persistent connections. > > Nope. I'm using Perl and cgi. I've got some perl that runs via cron, and > some more that runs via apache. I'm not even using ModPerl. > > It did occur to me that since some of my scripts communicate with other > devices, that I may have some IO blocking, or zombies, but the ps output > didn't indicate that. I can't see that many scripts running. Usually, I > see one postgres process for each script/cgi that is running. Not in this > case. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
