Re: [BUGS] FW: Noticed a Bug with stored procedures
Please ignore my previous mail, there were few spelling mistakes. Now you can go through below mail and respond to it. When I send update query command from front end then PostGreSql is responding with number of rows affected. But when I send same update query which is embedded in stored procedure (as listed below) then PostGreSql respond with a value of -1. CREATE OR REPLACE FUNCTION samplepro5(deptid int) RETURNS void AS $BODY$ UPDATE EmailLoginUsers SET LoginID = 'a...@sample.com Where UserCheckedMailID = $1; $BODY$ LANGUAGE 'sql' VOLATILE COST 100; From front end, I have called above listed stored procedure by using below listed code: objNpgSQLCommand.CommandText = "samplepro5"; objNpgSQLCommand.CommandType = CommandType.StoredProcedure; NpgsqlParameter objParameter; objparameter.ParameterName = "deptid"; objparameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer; objparameter.Value = 1; objparameter.Direction = ParameterDirection.Input; objNpgSQLCommand.Parameters.Add(objParameter); int numberOfAffectedRows = objNpgSQLCommand.ExecuteNonQuery(); After excuting above code, the value of numberOfAffectedRows was -1 but database was updated with one record. Please tell me what's wrong. The reason that you don't see that number of affected rows is that your client application is *not* executing the UPDATE statement; your client application is executing a SELECT statement (which calls the samplepro5() function, which returns void). What would you expect to see if, for example, samplepro5() contained two or three UPDATE statements? Which "numberOfAffectedRows" would you expect to see? If you want to capture the number of rows affected by the UPDATE statement, modify samplepro5() so that it returns the row count (instead of returning void). You can get the row count with the GET DIAGNOSTICS statement: UPDATE ...; GET DIAGNOSTICS myRowCountVariable = ROW COUNT; RETURN myRowCountVariable; -- Korry ------- Korry Douglas Senior Database Dude EnterpriseDB Corporation The Enterprise Postgres Company Phone: (804)241-4301 Mobile: (620) EDB-NERD
Re: [BUGS] ecpg preprocessor regression in 9.0
On closer look, it's quite obvious: the code added to ECPGdump_a_type thinks that ECPGt_const is a variable type, and tries to look up the variable. The straightforward fix is this: ... But I wonder if there is a better way to identify variable-kind of ECPGttypes than list the ones that are not. There's some special ECPGttypes still missing from the above if-test, like ECPGt_NO_INDICATOR, but I'm not sure if they can ever be passed to ECPGdump_a_type. Seems a bit fragile anyway. I agree. How about adding a macro definition explicitely checking for the real variable types? Why not a function instead of a macro? You can set a breakpoint on a function if you need to debug. It seems unlikely that you would every see any measurable performance gain by writing a macro instead of a function. -- Korry -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] ecpg preprocessor regression in 9.0
On closer look, it's quite obvious: the code added to ECPGdump_a_type thinks that ECPGt_const is a variable type, and tries to look up the variable. The straightforward fix is this: ... But I wonder if there is a better way to identify variable-kind of ECPGttypes than list the ones that are not. There's some special ECPGttypes still missing from the above if-test, like ECPGt_NO_INDICATOR, but I'm not sure if they can ever be passed to ECPGdump_a_type. Seems a bit fragile anyway. I agree. How about adding a macro definition explicitely checking for the real variable types? You'd still have to list them all in the macro, but it would definitely be better. The list would then be closer to the enums, in the same header file, making it easier to remember to keep it up-to- date. (Korry's suggestion of making it a function instead of a macro would not have that advantage). Sure it would... use a switch statement that explicitly handles each type and include a default case that throws an error (or assertion). Of course, you have to run into the code to find out that you forgot to maintain the classification function. A good C compiler will even spot the problem if you forget to handle one or more enum values in the switch statement. A macro offers no assurances at all. -- Korry -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5753: Existing Functions No Longer Work
/* you cannot to fetch data from function that returns a refcursor. refcursor is varchar - name of some object (cursor). when you open cursor, then you can specify a name (it's refcursor), but when you fetch data, you must to use a direct sql identifier of cursor - not a reference. simply, you can't to write fetch all from function(parameter); */ Please explain to me then why my application works. You are speaking to me entirely in abstract, where I have provided you with several concrete examples of functions that work when called from the java application. I also provided you evidence that using the terms in your documentation do not work. What am I doing wrong? Again, I'm imploring you both: Use the data I provided to create a test table. Create the function I have created (test_proc and test_proc1) and run it or re-write it so that it works and please provide that back to me along with the syntax I should use to make it work. Please, prove your documentation applies to my data because as it stands from my perspective your documentation is fairly poor in explaining how to write and call functions, considering they are a fairly common tool for developers and DBAs. Why can I not Google for postgreSQL function examples? I submit it might be for this reason: perhaps people stay away from them because they have flaws, either in how they have been communicated or in how they are invoked I've worked extensively with SQL Server, IBM, Sybase and Oracle. I know how to write and test stored procedures. I'm telling you that something is different between 8.1 and 9.0 and all you are offering are conceptual excerpts from your own documentation. Please put it back to me using my data and you might get some insight into what could be tweaked with your documentation so that it can be more intuitive and functions used more often Vince - is there any chance that you were using a non-standard PostgreSQL distribution or driver before? Like something from EnterpriseDB? It may be that a third-party JDBC driver was doing some behind-the-curtains work on your behalf. -- Korry ----------- Korry Douglas Senior Database Dude EnterpriseDB Corporation The Enterprise Postgres Company Phone: (804)241-4301 Mobile: (620) EDB-NERD -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5816: index not used in function
> We may have different perceptions of something being a 'bug'. I always > have several simple ways of determining it. One of them is when a > work-around is in the proposal. Yours is one. It seems to me that the important question in this case is whether or not the query produced the correct result. You are complaining about a performance issue, not a correctness issue, right? Kevin's work-around is meant to help you *gain better performance*, not to obtain correct results when you are getting incorrect results. > There can be quite a number of ways of looking at the issue. First, it > is truly an implementation matter (making it in the true sense a bug). I > do not believe that the spec would in formal way say that 'well, there > are caveats where you have to do this and that to work around'. The "spec" (by which I assume you mean the SQL standard) says nothing about which execution plan will be selected the optimizer. > > If by 'kept from one execution to another' means that (the concept of) a > plan is implemented static, this can be a low level design issue, which > in general will still be regarded as implementation, thus a bug. The execution plan is not quite static - it is computed the first time you run the function (within a session) and is discarded when your session ends (or when the compiled function/execution plan becomes obsolete because of a change to a dependency). That is by design. If you want a dynamic plan that is re-computed each time you execute the query, you can get that behavior by using dynamic SQL, as Kevin suggested. -- Korry -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5816: index not used in function
Frank, thanks for educating me. -- Korry > -Original Message- > From: Korry Douglas [mailto:korry.doug...@enterprisedb.com] > Sent: Sunday, January 09, 2011 2:34 PM > To: frank > Cc: 'Kevin Grittner'; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5816: index not used in function > > > > > We may have different perceptions of something being a 'bug'. I always > > > have several simple ways of determining it. One of them is when a > > > work-around is in the proposal. Yours is one. > > > > It seems to me that the important question in this case is whether or not the > query produced the correct result. > > The important question by nature is not whether correct result is produce > eventually. In this case, the important thing is the inability to use the > index when in fact, if designed and implemented correctly, it could. > > > > You are complaining about a performance issue, not a correctness issue, right? > > No. I am pointing out a low-level-design/implementation defect. Poor > performance is the symptom. Poor performance due to sub-optimal execution of > the spec is a correctness issue. (See my comment on the nature of a function > below) > > > > Kevin's work-around is meant to help you *gain better performance*, not to > obtain correct results when you are getting incorrect results. > > If it is not a bug, why do we need a work-around? > > > > > There can be quite a number of ways of looking at the issue. First, it > > > is truly an implementation matter (making it in the true sense a bug). I > > > do not believe that the spec would in formal way say that 'well, there > > > are caveats where you have to do this and that to work around'. > > > > The "spec" (by which I assume you mean the SQL standard) says nothing about > which execution plan will be selected (by) the optimizer. > > No. Whatever the spec, it will never say that a function will not work as a > function, or something that works outside one will not work once moved inside. > > > > > > > > If by 'kept from one execution to another' means that (the concept of) a > > > plan is implemented static, this can be a low level design issue, which > > > in general will still be regarded as implementation, thus a bug. > > > > The execution plan is not quite static - it is computed the first time you > run the function (within a session) and is discarded when your session ends > (or when the compiled function/execution plan becomes obsolete because of a > change to a dependency). > > > > That is by design. > > Then the design is poor. > > > > If you want a dynamic plan that is re-computed each time you execute the > query, you can get that behavior by using dynamic SQL, as Kevin suggested. > > > > This seems far fetched and irrelevant. Whatever is truly static should be > implemented static; whatever is dynamic should be implemented dynamic; > whatever is partially static, the static part should be static and the > dynamic part should be dynamic. It is natural and correct treatment. > > > > Purely dynamic situation in which the final query can not be determined in > any fashion, will have to be constructed either outside of the function or > within, so EXECUTE is the only way to handle. What do you think of requiring > the caller to construct such a static statement as “select count(*) from > sometable” and use EXECUTE? > > > > By the same token, “select thiscolumn from thistable where upper(thiscolumn) > like $1” has to be treated statically for the static part. The only unknown > is the parameter, which can be, by the right design and implementation, > delayed till execution (runtime). The code to deal with this is what I > pointed out (via a conditional). If the plan is a piece of code, then the > conditional will be in it. If the plan is a piece of text to be further > interpreted for actual execution (why would one want to do it that way?), the > conditional could contain a text reference to two pieces of code (w/o the use > of the index). If it is not properly designed/implemented and such situation > results in the loss of the said ability, it is a defect to be addressed. > Whether one wants to address it is one issue. A defect is a defect. > > > > You seem to suggest that the plan was only built at (the first) execution. > That is poor design/implementation. > > > > Lastly, what is a function? One of the fundamental features of a f
Re: [BUGS] NO DATA error message in Frontend when querying large datasets
> thanks a lot for your response. > the frontend we are trying to use is a well known and spread laboratory > system. our consultant has experience with other databases (oracle, sql) that > are a lot bigger - with no problems. however i'm wondering that postgres is > not working with the lab system AND other common DB frontends using ODBC. i > guess there is no setting for: "use cursor instead" ? "Use Declare/Fetch" may be the setting that you want. That's a setting in the ODBC driver and you should be able to change that setting through the Windows ODBC Administrator. You may also want to change the "Server side prepare" setting. (Be sure to change one setting at a time and test your application between changes). -- Korry > > Am 26.01.2011 20:16, schrieb Tom Lane: >> Jens Kapp writes: >>> we have postgres 9.0.1 installed on a windows 2008 r2 server. >>> odbc is psqlodbc_09_00_0200.zip. >>> the database has currently a size of around 800mb. >>> it works as expected on the server (pgadmin), but if we try queries for >>> large datasets we get the error message: "No data" from our frontend or >>> any other database tools thats using the ODBC connection (e.g. anysql >>> maestro). >>> 1.) first we will make a query with only 1 column. we get data. >>> 2.) then make the same query but with all columns: it says "No data". >>> 3.) Afterwards we try the query from before again, but still getting "No >>> data". >> I wonder whether you're running out of memory for the query result in >> the client side, and this "no data" is coming from some code that is not >> written to cope with that happening. >> >> You might consider using a cursor to fetch large results a few rows at a >> time. >> >> regards, tom lane >> > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs --- Korry Douglas Senior Database Dude EnterpriseDB Corporation The Enterprise Postgres Company Phone: (804)241-4301 Mobile: (620) EDB-NERD -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5935: Log lotation not working for default log format
What would you expect the new log file to be named? Your log_filename is set to postgresql-%a.log. The %a part expands to the current day of the week. If it's Thursday and you already have a file for Thursday, what would the new file name be? -- Korry > The following bug has been logged online: > > Bug reference: 5935 > Logged by: Brad Nicholson > Email address: bnichol...@hp.com > PostgreSQL version: 9.0.3 > Operating system: CentOS Release 5.5 > Description:Log lotation not working for default log format > Details: > > PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) > 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit > CentOS release 5.5 (Final) > > (using the standard PG package) > > The logfile will not rotate if the log_filename is set to postgresql-%a.log, > which is the default setting after an initdb on my platform. > > > I have tested this by setting log_rotation_size and exceeding that threshold > and manually trying to rotate the logs with SELECT pg_rotate_logfile(). > Both fail to rotate the log file. > > > Manual Rotate: > > [postgres@pg1 pgdata]$ ls -al logs/ > total 24 > drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . > drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. > -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log > [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_filename;" postgres > log_filename > --- > postgresql-%a.log > (1 row) > > [postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()" > postgres > pg_rotate_logfile > --- > t > (1 row) > > [postgres@pg1 pgdata]$ ls -al logs/ > total 24 > drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . > drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. > -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log > > > > Size based automatic rotation: > > [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_rotation_size;" postgres > log_rotation_size > --- > 1MB > (1 row) > > > > [postgres@pg1 pgdata]$ ls -thl logs/ > total 3.2M > -rw--- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log > > > > If I switch the log_filename to something more specific like > postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates. > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5935: Log lotation not working for default log format
> You could appending something onto the logfile to differentiate it, but I can > understand why you wouldn't want to as that would change the filename format. > > If this is the case, the call to pg_rotate_logfile() should return false. pg_rotate_logfile() doesn't actually rotate the log file, it just schedules the rotation to happen as soon as possible. So it doesn't know whether or not the rotation will actually succeed. pg_rotate_logfile() only returns FALSE if you haven't started the log collector. > I'd also say that starting up and allowing a file size based rotation with a > log filename that does not allow it to be rotated is also broken. That filename pattern *does* permit rotation, just no more frequently than every 24 hours. Consider what would happen if you set log_filename to something like pg_log.%H (where %H is replaced with the hour) and your log size exceeded the log_rotation_size within a given hour - same problem. The key is to pick a filename pattern that *can* rotate given the value you set for log_rotation_size. It might be nice if the logfile naming mechanism supported a new escape sequence that meant "replace me with the next available sequence number... %n might translate to _1, _2, _3, ...). -- Korry P.S. I'm not disagreeing with you, just explaining the code as it exists today. > > Brad. > >> -Original Message- >> From: Korry Douglas [mailto:korry.doug...@enterprisedb.com] >> Sent: Thursday, March 17, 2011 4:00 PM >> To: Nicholson, Brad (Toronto, ON, CA) >> Cc: pgsql-bugs@postgresql.org >> Subject: Re: [BUGS] BUG #5935: Log lotation not working for default log >> format >> >> What would you expect the new log file to be named? Your log_filename >> is set to postgresql-%a.log. The %a part expands to the current day of >> the week. If it's Thursday and you already have a file for Thursday, >> what would the new file name be? >> >> -- Korry >> >>> The following bug has been logged online: >>> >>> Bug reference: 5935 >>> Logged by: Brad Nicholson >>> Email address: bnichol...@hp.com >>> PostgreSQL version: 9.0.3 >>> Operating system: CentOS Release 5.5 >>> Description:Log lotation not working for default log format >>> Details: >>> >>> PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc >> (GCC) >>> 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit >>> CentOS release 5.5 (Final) >>> >>> (using the standard PG package) >>> >>> The logfile will not rotate if the log_filename is set to postgresql- >> %a.log, >>> which is the default setting after an initdb on my platform. >>> >>> >>> I have tested this by setting log_rotation_size and exceeding that >> threshold >>> and manually trying to rotate the logs with SELECT >> pg_rotate_logfile(). >>> Both fail to rotate the log file. >>> >>> >>> Manual Rotate: >>> >>> [postgres@pg1 pgdata]$ ls -al logs/ >>> total 24 >>> drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . >>> drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. >>> -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log >>> [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_filename;" postgres >>> log_filename >>> --- >>> postgresql-%a.log >>> (1 row) >>> >>> [postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()" >>> postgres >>> pg_rotate_logfile >>> --- >>> t >>> (1 row) >>> >>> [postgres@pg1 pgdata]$ ls -al logs/ >>> total 24 >>> drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . >>> drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. >>> -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log >>> >>> >>> >>> Size based automatic rotation: >>> >>> [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_rotation_size;" >> postgres >>> log_rotation_size >>> --- >>> 1MB >>> (1 row) >>> >>> >>> >>> [postgres@pg1 pgdata]$ ls -thl logs/ >>> total 3.2M >>> -rw--- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log >>> >>> >>> >>> If I switch the log_filename to something more specific like >>> postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates. >>> >>> -- >>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-bugs > --- Korry Douglas Senior Database Dude EnterpriseDB Corporation The Enterprise PostgreSQL Company Phone: (804)241-4301 Mobile: (620) EDB-NERD -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
