[GENERAL] How to get recordset with CallableStatemente
Dear
sirs,
I am trying to get
recordset from PostgreSQL database (8.1.3) with java but I am some
problem.
The operation that I
would like to do is SELECT * FROM "Congressi" (the table has the first
letter in uppercase) (i.e. rs = st.executeQuery("select * from
\"Congressi\"");
It works with
Statement object (and query string declared directly) but does not with
CallableStatement (and thequery string "SELECT * FROM "Congressi"
inthe function find_congressi) called with the statement: cs =
conn.prepareCall("{find_congressi}");
Can somebody send me
a short fragment of code that call a function that returns a set of
records?
Thank
you.
Domenico
[GENERAL] ALTER SEQUENCE ... RESTART WITH [variable] problem
I need to generate a couple of dozen statements reseting my sequences so that they're next values are greater than the biggest existing ids. The problem is, I can't even form a statement to update one sequence. This is what I tried: CREATE OR REPLACE FUNCTION init_sequences() RETURNS void AS $BODY$ DECLARE next_id_table1 INTEGER; BEGIN SELECT INTO next_id_table1 MAX(id)+1 FROM table1; ALTER SEQUENCE pk_table1 RESTART next_id_table1; END; $BODY$ LANGUAGE 'plpgsql'; The problem seems to be the ALTER statement: ERROR: syntax error at or near $1 at character 36 QUERY: ALTER SEQUENCE pk_table1 RESTART $1 CONTEXT: SQL statement in PL/PgSQL function init_sequences near line 5 If I change the ALTER statement like this ALTER SEQUENCE pk_table1 RESTART 200; it works. But is obviously not what I wanted. Is there a way to get the ALTER SEQUENCE statement to use a value stored in a variable? t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ALTER SEQUENCE ... RESTART WITH [variable] problem
On Sat, May 20, 2006 at 09:52:29AM +0200, Tomi NA wrote:
I need to generate a couple of dozen statements reseting my sequences
so that they're next values are greater than the biggest existing ids.
The problem is, I can't even form a statement to update one sequence.
This is what I tried:
snip
The problem seems to be the ALTER statement:
ERROR: syntax error at or near $1 at character 36
QUERY: ALTER SEQUENCE pk_table1 RESTART $1
CONTEXT: SQL statement in PL/PgSQL function init_sequences near line 5
Seems you can't use a variable there. Your choices are to build a
string and use EXECUTE, or just do:
SELECT setval('sequence',value);
Have a nice day,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
From each according to his ability. To each according to his ability to
litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] ALTER SEQUENCE ... RESTART WITH [variable] problem
On 5/20/06, Martijn van Oosterhout kleptog@svana.org wrote:
Seems you can't use a variable there. Your choices are to build a
string and use EXECUTE, or just do:
SELECT setval('sequence',value);
The EXECUTE string solution did the job. Thank you very much, Martijn.
t.n.a.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] VACUUM FULL hangs on ordinary table
On 5/19/06, Tom Lane [EMAIL PROTECTED] wrote: Ivan Zolotukhin [EMAIL PROTECTED] writes: quite ordinary I think. When it hangs I see in `ps auxww` process with VACUUM waiting in its status. It's definitely waiting for a lock then. Yep, I checked that it waits for acquiring AccessExclusiveLock on the next table to vacuum after it finished education table. Below I pasted last lines concerning above table from VACUUM output (it stops after the last line): ... INFO: analyzing public.education INFO: education: scanned 674 of 674 pages, containing 40653 live rows and dead rows; 3000 rows in sample, 40653 estimated total rows If it hangs there then I'd venture that it's trying to get writer's lock (RowExclusiveLock) on pg_statistic so it can store the new statistic rows. Or possibly pg_class. You should be looking for locks on the system catalogs not locks on education itself. Actually, Joachim was closer to the truth: there was one not committed prepared transaction started several days ago (client disconnected at the beginning) that holded one RowExclusiveLock and 12 AccessShareLocks on several relations in that DB (that obviously interfered with vacuum trying to acquire AccessExclusiveLock on one of them). After ROLLBACK PREPARED vacuum works fine so thanks to your advices -- they helped me to solve the problem. Regards, Ivan Zolotukhin ---(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] another seemingly simple encoding question
this is a forward of my problem from April. I have this time gone all the way and re-inited a DB from scratch, created a new database, documented the import procedure, set the locale to match but I am still having problems. For example, look at this match count~ mod=# select count(*) from korean_english; count 205323 (1 row) mod=# mod=# select count(*) from korean_english where word='안녕'; count --- 40332 (1 row) mod=# \set VERSION = 'PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)' AUTOCOMMIT = 'on' VERBOSITY = 'default' DBNAME = 'mod' USER = 'postgres' PORT = '5432' ENCODING = 'UNICODE' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = ' ' HISTSIZE = '500' mod=# I documented the import procedure and put it at http://www.myowndictionary.com/design.htm if there is anybody out there who has any idea, i would be very grateful for help. I have to move my database to postgres from mysql, and it has this big problem with the encoding. thank you . joseph. Forwarded Message 보낸 사람: joseph [EMAIL PROTECTED] 받는 사람: pgsql-general@postgresql.org 제목: another seemingly simple encoding question 날짜: Fri, 24 Mar 2006 22:27:06 +0900 maybe a routine question here ... i hope i can understand the answer. [EMAIL PROTECTED] ~]$ pg_ctl --version pg_ctl (PostgreSQL) 8.0.0beta3 [EMAIL PROTECTED] ~]$ i have a problem matching a utf8 string with a field in a database encoded in utf8. i read the documentation, checked the following, and don't know where i went astray, trying to match ... 1) i am almost 100% sure the data is correctly utf8. i just dumped and loaded into postgres. 2) utf8db - \l List of databases Name | Owner | Encoding --+--+--- utf8db | postgres | UNICODE 3) postgresql.conf # These settings are initialized by initdb -- they might be changed lc_messages = 'en_US.utf8' # locale for system error message strings lc_monetary = 'en_US.utf8' # locale for monetary formatting lc_numeric = 'en_US.utf8' # locale for number formatting lc_time = 'en_US.utf8' # locale for time formatting # - Other Defaults - 4) set client encoding in client (psql or php, either one, both same mismatch) LOG: statement: select wordid,word from korean_english where word='기르 다' limit 10; LOG: statement: show client_encoding; LOG: statement: set client_encoding to 'utf8'; LOG: statement: select wordid,word from korean_english where word='기르 다' limit 10; LOG: statement: show client_encoding; 5) locale -a | grep en snip en_US.utf8 /snip ohhh, where is my mistake, please! -- my site a href=http://www.myowndictionary.com;myowndictionary/a was made to help students of many languages learn them faster. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] No stddev() for interval?
Hi all,I noticed a peculiarity in the default postgres aggregate functions. min(), max() and avg() support interval as an input type, but stddev() and variance() do not.Is there a rationale behind this, or is it just something that was never implemented? Regards,BJ
Re: [GENERAL] No stddev() for interval?
Brendan Jurd [EMAIL PROTECTED] writes: I noticed a peculiarity in the default postgres aggregate functions. min()= , max() and avg() support interval as an input type, but stddev() and variance() do not. Is there a rationale behind this, or is it just something that was never implemented? Is it sensible to calculate standard deviation on intervals? How would you handle the multiple components? I mean, you could certainly define *something*, but how sane/useful would the result be? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] No stddev() for interval?
On 5/20/06, Tom Lane [EMAIL PROTECTED] wrote: Brendan Jurd [EMAIL PROTECTED] writes: I noticed a peculiarity in the default postgres aggregate functions. min()= , max() and avg() support interval as an input type, but stddev() and variance() do not. Is there a rationale behind this, or is it just something that was never implemented? Is it sensible to calculate standard deviation on intervals? How would you handle the multiple components? I mean, you could certainly define *something*, but how sane/useful would the result be? Strictly speaking there's nothing bad in intervals. Physically standart deviation on interval can be very useful without any doubts. I can make a lot of examples on this. Say you want to know stat parameters of semi-regular periodical process (avg distance in time between maximums of some value and stddev of this quasiperiod -- why not?). Regards, Ivan Zolotukhin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] No stddev() for interval?
Ivan Zolotukhin [EMAIL PROTECTED] writes: On 5/20/06, Tom Lane [EMAIL PROTECTED] wrote: Is it sensible to calculate standard deviation on intervals? How would you handle the multiple components? I mean, you could certainly define *something*, but how sane/useful would the result be? Strictly speaking there's nothing bad in intervals. Physically standart deviation on interval can be very useful without any doubts. If the intervals are all expressed in seconds then sure, the calculation is straightforward and useful. I'm wondering what happens when nonzero values of days and months get in there. 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] No stddev() for interval?
If the intervals are all expressed in seconds then sure, the calculation
is straightforward and useful.I'm wondering what happens when nonzerovalues of days and months get in there.regards, tom laneThe existing logic used in avg(interval) can be seen in backend/utils/adt/timestamp.c, refer to functions interval_accum, interval_avg and interval_div.
interval_div(interval, double) is the most interesting for this discussion. There is a helpful comment that reads /* evaluate fractional months as 30 days */.So for example, interval_div('4 mons'::interval, 3) gives you '1 mon 10 days'.
It's not perfect, but doing arithmetic that involves converting between months and days never is. All in favour of deleting the month as unit of measurement of time say aye.Well that's not going to happen in my lifetime. How about we just extend this same logic over to stddev and variance? It's strange having avg but not the other two.
Regards,BJ
[GENERAL] Let's make CPgAN!
The idea that came up in the -hackers and -advocacy lists, and I think it should be brought up as a separate thread, in -general. Backgroud Once in a while someone comes and suggests adding some package to postgresql-contrib. Some other person asks for some feature and is directed at Pgfoundry. Yet again, someone submits a package and is usually directed to Pgfoundry to put it there. While it is a great place to keep development there, but not so obvious a place for random administrator/DBA/programmer... So the idea is to make something akin to: Comrehensive PostgreSQL Archive Network, or CPgAN I would like to provoke a discussion how should such a thing look like, and hopefully to make one. First, I think it should be pretty intergrated into PostgreSQL, and should by distribution agnostic. From user perspective it should be similar to createuser, createdb, createlang commands. ie. user would call a shell command, say: pg_package dbname install name_of_package and it would connect with cpgan, get list of packages, get sources, compile sources (if C-backed), maintain dependencies, etc. Or at least it should. It's better to talk with an example. Let's assume the issn/isbn datatype would be handled by that infrastructure. If DBA would want to install it from source, she would use this pg_package (or whatever the name) utility to download it, unpack, compile and install generated lib...so file. It would also install, in some common place a sql script which would install and uninstall the package. DBA should be able to specify if package is available to all users or not (grant its usage), but it is not essential right now. Command should be able to handle binary packages well. Think: Linux distributions etc -- they would put all files in place, then call pg_package to notify PostgreSQL about new additon. The second part is enabling the usage within given database. A simple running of bunch CREATE commands on given database. Plus a matching deinstall script. This raises couple of questions: 1. CREATE PACKAGE -- maybe its time to rethink such a command. :) 2. Where to store state information. A natural place would be a database itself. A dedicated cpgan db with all dependencies? A bit fragile solution, and much of managament will become cross-database (to install PL/R to template1 db you would need to connect both to cpgan and template1, but it may work. 3. There would be a need to automate the server side as possible. User should be able to use it to search for package she desires. And developers should be able to efficiently upload packages. 4. A tree of packages, PgFoundry's is too general I think. For instance I feel that PgAdmin and other DB Administration projects would not belong here, as they are rather interfaces to than extensions of. :) Say, a tree with such a look: pl/ pl/plr pl/plperlng replication/slony datatype/uri datatype/email index/ltree ...of course its not complete and not ideal. I wnat to know how would you organise such a tree. 5. A common documentation format. It would be great to be able to automatically merge in documentation of extensions into PostgreSQL's Manual as a separate Appendix. Right now its somewhere between Use The Source, Luke, README files and what-author-provided documentation format. What would this all give us? It would encourage development of simple extensions, and would greatly increase the ease of extending PostgreSQL by newbies. In the long run it would bring more developers, as PostgreSQL would be perceived not only as a SQL DB but also as a development platform, extensible and powerful. What do you think? Regards, Dawid ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Let's make CPgAN!
I think the implementation of postgresql installable packages (and package-space) should precede this idea. Then, any package management system can install the packages. On May 20, 2006, at 2:12 PM, Dawid Kuroczko wrote: Comrehensive PostgreSQL Archive Network, or CPgAN ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Let's make CPgAN!
I am not sure that Postgres needs CPAN. CPAN is particularly useful for handling dependencies. I doubt that there will be lots of dependencies in Postgres add ons. So having something like the current system where you download and build packages from source isn't going to be improved much with a CPAN like system. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best practice to grant all privileges on all bjects in database?
You can find some helpful grant scripts here:http://pgedit.com/tip/postgresql/access_control_functions
On 5/19/06, Joe Kramer [EMAIL PROTECTED] wrote:
Hello,I need to grant all privileges on all objects in database. Withoutusing SUPERUSER.It's strange that GRANT ALL PRIVILEGES ON DATABASE is useless, itdon't grant privileges on tables.I've found out this best practice, (more like ugly workaround):
select 'grant all on '||schemaname||'.'||tablename||' to\\\$USER\\\;' from pg_tables where schemaname in ('public');select 'grant all on '||schemaname||'.'||viewname||' to\\\$USER\\\;' from pg_views where schemaname in ('public');
and same for functions,sequences etc.Is there nicer, more friendly way? Maybe there is something likecontrib module or procedure that does that in user-friendly way?If not, anyone has a better version of above grant script?
Re: [GENERAL] No stddev() for interval?
On Sun, May 21, 2006 at 01:14:15AM +1000, Brendan Jurd wrote: Hi all, I noticed a peculiarity in the default postgres aggregate functions. min(), max() and avg() support interval as an input type, but stddev() and variance() do not. Is there a rationale behind this, or is it just something that was never implemented? That's because variance of foo is measured in foo^2 units. What is the square of an interval? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(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] No stddev() for interval?
That's because variance of foo is measured in foo^2 units.What isthe square of an interval? Cheers,DWell if you're willing to accept that for the purposes of computing the aggregates, an interval month is equal to 30 days (which is how avg(interval) already works), then an interval is reducable to a single quantity -- a number of seconds -- which can be squared. 30 days per month is pretty rough ... we could refine it to 30.4375, which is the average number of days per month over four years including one leap year.
Re: [GENERAL] No stddev() for interval?
First of all, stddev doesn't return square of smth - so, why should we worry about intermediate results? Furthermore, statistics work with any 'units' and doesn't worry about physical meaning of variance in any case (for example, what about variance for the set of lifetime values of people from town N in XX century? ;-) ). Second, SQL standard doesn't contain definition for STDDEV function, but it has STDDEV_POP and STDDEV_SAMP (but it doesn't really matter for this discussion). As for valid datatypes for these functions, I cannot find exact definition unfortunately, but I see folliwing: 'Without Feature T621, Enhanced numeric functions, conforming SQL language shall not contain a computational operation that immediately contains STDDEV_POP, STDDEV_SAMP, VAR_POP, or VAR_SAMP.' So, authors meant that these functions should accept only numeric values. Last but not least, ORA doesn't want to accept interval values for stddev() func: CREATE TABLE teststddev(id INTEGER PRIMARY KEY, val INTERVAL YEAR TO MONTH); INSERT INTO teststddev(id, val) VALUES(1, INTERVAL '300' MONTH(3)); INSERT INTO teststddev(id, val) VALUES(2, INTERVAL '2' YEAR(1)); INSERT INTO teststddev(id, val) VALUES(3, INTERVAL '-125' MONTH(3)); SELECT STDDEV(val) FROM teststddev; ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL YEAR TO MONTH As for me, I think that it's quite reasonable to expect stddev working with intervals... Why not? On 5/21/06, David Fetter [EMAIL PROTECTED] wrote: On Sun, May 21, 2006 at 01:14:15AM +1000, Brendan Jurd wrote: Hi all, I noticed a peculiarity in the default postgres aggregate functions. min(), max() and avg() support interval as an input type, but stddev() and variance() do not. Is there a rationale behind this, or is it just something that was never implemented? That's because variance of foo is measured in foo^2 units. What is the square of an interval? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(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 -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] No stddev() for interval?
On 5/20/06, Tom Lane [EMAIL PROTECTED] wrote: If the intervals are all expressed in seconds then sure, the calculation is straightforward and useful. I'm wondering what happens when nonzero values of days and months get in there. Ah! Maybe the reason for such thoughts lies in nature of postgres intervals. SQL:2003 standard paper says: 'There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precision that includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH.' So, the basic question is 'why Postgres allows to combine month and day?' Actually, is it good idea? If we have two separate interval types - we haven't the problem of '1 month VS 30 days' at all... And if we have no such a problem, we would work with intervals as with numbers (I don't see the strong reason for absense of stddev() and even variance() for INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, INTERVAL MINUTE TO SECOND and so on). -- Best regards, Nikolay ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Read Write
How can I determine if my tables are 'read only' or 'read/write'? Bob
Re: [GENERAL] No stddev() for interval?
On 5/21/06, Brendan Jurd [EMAIL PROTECTED] wrote: Well if you're willing to accept that for the purposes of computing the aggregates, an interval month is equal to 30 days (which is how avg(interval) already works), then an interval is reducable to a single quantity -- a number of seconds -- which can be squared. Let me make a correction. Internally, intervals are stored as separate values of months and days (and even seconds, for daylight saving purposes). So, in almost all cases everything is OK. But it isn't so when we should multiply or devide such 'hetorogenious intevals' (select interval '1 month 1 day'; gives us '15 days 12:00:00')... -- Best regards, Nikolay ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] No stddev() for interval?
On 5/21/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: So, in almost all cases everything is OK. But it isn't so when we should multiply or devide such 'hetorogenious intevals' (select interval '1 month 1 day'; gives us '15 days 12:00:00')... You obviously meant select interval '1 month 1 day' / 2; to get above weird result of 15 days and 12 hours. Regards, Ivan Zolotukhin ---(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] Read Write
\z 'table_name' Look under access privileges for r and w. For further information see- http://www.postgresql.org/docs/8.1/interactive/sql-grant.html On Saturday 20 May 2006 03:11 pm, Bob Pawley wrote: How can I determine if my tables are 'read only' or 'read/write'? Bob -- Adrian Klaver [EMAIL PROTECTED] ---(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] Read Write
Sorry meant to add this from the psql command line. On Saturday 20 May 2006 04:37 pm, Adrian Klaver wrote: \z 'table_name' Look under access privileges for r and w. For further information see- http://www.postgresql.org/docs/8.1/interactive/sql-grant.html On Saturday 20 May 2006 03:11 pm, Bob Pawley wrote: How can I determine if my tables are 'read only' or 'read/write'? Bob -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
