Re: [GENERAL] cursor already in use error
On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote: PostGreSQL 8.0beta1 That's pretty old. Have you tried 8.0.1? I have a function that uses a cursor and it is giving me the error: cursor crsr already in use when the parameters I pass in come from another table. The function works fine when I call it by itself, such as select PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1) but when I try select PartNeedsReschedule(PartID,1) from Parts then I get the error. Could you post a simple, self-contained example that demonstrates the problem? Please show definitions of Parts and PartNeedsReschedule and some sample data -- enough that somebody could copy what you post into a test database and duplicate the problem. It'll be easier to help if we can see exactly what you're doing. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] cursor already in use error
I haven't tried 8.0.1 yet. I am planning on making the upgrade soon, but since I haven't seen this issue discussed I did not think it had been fixed just because I am using an older version. I did search the Internet and found one person who had the same issue and found a workaround that won't apply to my situation. Here is an example of my problem: create table test( TestID int primary key, name varchar); insert into test(TestID,name) values(1,'Bob'); insert into test(testid,name) values(2,'Mark'); insert into test(testid,name) values(3,'Tom'); create table test2(Test2ID serial primary key, TestID int, comments varchar); insert into test2(TestID,Comments) values(1,'Comment 1'); insert into test2(TestID,Comments) values(1,'Comment 2'); insert into test2(TestID,Comments) values(1,'Comment 3'); insert into test2(TestID,Comments) values(2,'Comment 1'); create or replace function testcursor(thistestid int) returns varchar as $$ declare crs Cursor for select comments from test a join test2 b on a.testid=b.testid where a.TestID=thistestid; thiscomment varchar; totalstr varchar; begin open crs; fetch crs into thiscomment; totalstr:=''; while found loop totalstr:= totalstr || '-' || thiscomment; fetch crs into thiscomment; end loop; return totalstr; end; $$language 'plpgsql'; select name,testcursor(testid) from test; --doesn't work select name,testcursor(testid) from test where testid=1; -- works (as does testid=2 or 3) Obviously in this example I could use a for loop which would remove the problem, but in my real life complicated example, I cannot use a for loop and I am stuck using a cursor. My real life example uses the cursor inside of a for loop such that it opens the cursor before the loop starts and then based on criteria inside the loop sometimes it reads the values and fetches the next value and sometimes it doesn't. Thank You Sim Michael Fuhr [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote: PostGreSQL 8.0beta1 That's pretty old. Have you tried 8.0.1? I have a function that uses a cursor and it is giving me the error: cursor crsr already in use when the parameters I pass in come from another table. The function works fine when I call it by itself, such as select PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1) but when I try select PartNeedsReschedule(PartID,1) from Parts then I get the error. Could you post a simple, self-contained example that demonstrates the problem? Please show definitions of Parts and PartNeedsReschedule and some sample data -- enough that somebody could copy what you post into a test database and duplicate the problem. It'll be easier to help if we can see exactly what you're doing. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Replication from other SQL Server
You might want to take a look at http://www.daffodildb.com/replicator.html It claims to be able to replicate between different databases, including Sql server and postgres. If you try it pleases share your experience with us, I haven't seen yet a review involving replication between different DB server to postgres using daffodil DB in a real world application. Cheers, Csaba. On Wed, 2005-03-02 at 06:15, Keith Tsao wrote: Hi, I am new to postgresql! We have a M$SQL server and would like to do a replication from this server to postgresql. Would this be possible? If so, what would be the appropiate method. Any suggestion? Thx! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] cursor already in use error
Sim Zacks wrote: create or replace function testcursor(thistestid int) returns varchar as $$ declare crs Cursor for select comments from test a join test2 b on a.testid=b.testid where a.TestID=thistestid; thiscomment varchar; totalstr varchar; begin open crs; fetch crs into thiscomment; totalstr:=''; while found loop totalstr:= totalstr || '-' || thiscomment; fetch crs into thiscomment; end loop; close crs; return totalstr; end; $$language 'plpgsql'; select name,testcursor(testid) from test; --doesn't work select name,testcursor(testid) from test where testid=1; -- works (as does testid=2 or 3) The second query works because you fetch only one record; You don't call the SP a second time with the cursor still open, while you do with the first query. Always close your cursors. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] cursor already in use error
If I add close crs; before the function returns, I get this: SELECT name, testcursor(testid) FROM test; name | testcursor --+ Bob | -Comment 1-Comment 2-Comment 3 Mark | -Comment 1 Tom | (3 rows) You got it. I was closing the cursor at the end of the function, but there were other possible returns in the middle. I changed the code to add close before each return and it works like a charm. I rushed together the example, or I should have caught that. Much thanks. Sim ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] cursor already in use error
Always close your cursors. Thanks. In my real example I had multiple return points and did not close the cursor before each one. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] cursor already in use error
On Wed, Mar 02, 2005 at 11:15:52AM +0200, Sim Zacks wrote: select name,testcursor(testid) from test; --doesn't work select name,testcursor(testid) from test where testid=1; -- works (as does testid=2 or 3) If I add close crs; before the function returns, I get this: SELECT name, testcursor(testid) FROM test; name | testcursor --+ Bob | -Comment 1-Comment 2-Comment 3 Mark | -Comment 1 Tom | (3 rows) Is that what you're after? If so, then the problem might simply be that you're not closing the cursor when you're done with it. Will closing it work in the real code? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] to_char bug?
If the number is negative there needs to be room for the minus sign... On Tue, Mar 01, 2005 at 09:25:02AM -, Ben Trewern wrote: From the docs: FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width It works now but for one I don't understand why the space is added in the firs place and two I think the docs don't tell the whole story ie leading blanks and I assume trailing zeros if applicable. -- Martijn van Oosterhout kleptog@svana.org 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. pgp5cFrqsC8HC.pgp Description: PGP signature
[GENERAL] pgadmin3 / postgresql newbie question
hi all, running amd64 fedora core 3 w/ default postgresql 7.4.7-3. did asu - , su postgres and createdb mydb as explained in the postgresql tutorial. installed the latest pgadmin3 and am trying to connect to this database. as i wasn't sure what the FC3 default password was for postgres, i changed it to something i could remember. i can't seem to connect to the new database using pgadmin3. i have the correct IP address of the local computer, default port 5432, mydb as the initaldb, postgres as the username, and my new password as the password. i keep getting the error Error connecting to the server: could not connect to server: Connection refused Is the server running on host 192.168.1.24 and accepting TCP/IP connections on port 5432? i also verified the postgresql service is running, and that i've added the following to /etc/raddb/postgresql.conf: login = postgres password = mynewpassword and right underneath it: tcpip = true i've also disabled my local firewall and SELINUX just for kicks. and yes, i did a reboot. so...anyone know what else i can look at? many thanks! jonathan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pgadmin3 / postgresql newbie question
Did you start the postmaster using '-i' to allow tcp/ip connections? Sean On Mar 2, 2005, at 6:15 AM, Jonathan Schreiter wrote: hi all, running amd64 fedora core 3 w/ default postgresql 7.4.7-3. did asu - , su postgres and createdb mydb as explained in the postgresql tutorial. installed the latest pgadmin3 and am trying to connect to this database. as i wasn't sure what the FC3 default password was for postgres, i changed it to something i could remember. i can't seem to connect to the new database using pgadmin3. i have the correct IP address of the local computer, default port 5432, mydb as the initaldb, postgres as the username, and my new password as the password. i keep getting the error Error connecting to the server: could not connect to server: Connection refused Is the server running on host 192.168.1.24 and accepting TCP/IP connections on port 5432? i also verified the postgresql service is running, and that i've added the following to /etc/raddb/postgresql.conf: login = postgres password = mynewpassword and right underneath it: tcpip = true i've also disabled my local firewall and SELINUX just for kicks. and yes, i did a reboot. so...anyone know what else i can look at? many thanks! jonathan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pgadmin3 / postgresql newbie question
On Wed, 2 Mar 2005, Jonathan Schreiter wrote: hi all, running amd64 fedora core 3 w/ default postgresql 7.4.7-3. did asu - , su postgres and createdb mydb as explained in the postgresql tutorial. installed the latest pgadmin3 and am trying to connect to this database. as i wasn't sure what the FC3 default password was for postgres, i changed it to something i could remember. i can't seem to connect to the new database using pgadmin3. i have the correct IP address of the local computer, default port 5432, mydb as the initaldb, postgres as the username, and my new password as the password. i keep getting the error Error connecting to the server: could not connect to server: Connection refused Is the server running on host 192.168.1.24 and accepting TCP/IP connections on port 5432? i also verified the postgresql service is running, and that i've added the following to /etc/raddb/postgresql.conf: This file is part of the freeradius package, and despite the name, has nothing to do with your PostgreSQL configuration. The default path for the real PostgreSQL configuration file is: /var/lib/pgsql/data/postgresql.conf I don't know if running TCP/IP is a requirement for pgadmin3, but if you need to access the _local_ PostgreSQL server, most clients would do w/o configuring TCP/IP support at all. login = postgres password = mynewpassword and right underneath it: tcpip = true These do not belong to PostgreSQL server configurarion. It's RADIUS stuff. i've also disabled my local firewall and SELINUX just for kicks. and yes, i did a reboot. so...anyone know what else i can look at? 1) make sure postgresql is running (use ps - look for a postmaster process) 2) if it's not there, run following command as root: service postgresql start 3) if you want it to run automatically at boot, and it doesn't, run the following command as root: chkconfig postgresql on this won't start the process if it's not running. It just sets a flag for the next boot. 4) i don't get what you mean for changing postgres password. To switch to the postgres user, I usually switch to root first, and then to postgres. Root can switch to any user w/o password. Actually, it's good security practice not to assign any password to system pseudo-accounts (postgres is one of them) and leave them locked. If you need a different access method, I strongly suggest to look at the PostgreSQL way to authenticate users and stop using the 'ident' method (see pg_hba.conf), which forces you to run clients with a certain Unix user id. 5) try and access to the db with the psql client first. Use the same connection method you're using with pgadmin3, and run it under the same user you run pgadmin3 with. E.g.: psql -h localhost -p 5432 -U postgres mydb see psql manual for details. If you successfully get to 5), it's likely it's a pgadmin3 problem. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pgadmin3 / postgresql newbie question
Did you set up the pg_hba.conf file with the proper security rights? Jonathan Schreiter [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] hi all, running amd64 fedora core 3 w/ default postgresql 7.4.7-3. did asu - , su postgres and createdb mydb as explained in the postgresql tutorial. installed the latest pgadmin3 and am trying to connect to this database. as i wasn't sure what the FC3 default password was for postgres, i changed it to something i could remember. i can't seem to connect to the new database using pgadmin3. i have the correct IP address of the local computer, default port 5432, mydb as the initaldb, postgres as the username, and my new password as the password. i keep getting the error Error connecting to the server: could not connect to server: Connection refused Is the server running on host 192.168.1.24 and accepting TCP/IP connections on port 5432? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [ADMIN] Database Name
On Tue, 2005-03-01 at 09:44 +1300, Envbop wrote: Hi I've just inherited a PostgreSQL database, for which I do not have any details of, like database name or the users. This used to be a library database which was managed via a web page written in php. Its running on a Linux box. The front end was also written in php. The original writers of this application is no longer. I've just started to get familiarisd with postgresql in the last week. and have installed PostgreSQL v8.0.1 on a Windows server. And I am trying to connect to the linux box via the phppgadmin tool. as well as trying an odbc connection. Can someone tell me where I can find the database names. If you look at the php source code, you will find the database name, user name and password that the php scripts use to connect to the database. That should be a good start. If php uses odbc to connect, you might also check /etc/odbc.ini on the linux box. Good luck! Bernt ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] [Auth] ident method and LDAP user accounts
I manage a Debian/Linux machine which runs PostgreSQL 7.4.7. All the user accounts, including mine, are in a LDAP database. Thanks to NSS (Name Service Switch) all applications have access to the LDAP accounts (getpwuid(3) and getpwnam(3) use LDAP). But not PostgreSQL. When I connect locally (Linux as SO_PEERCRED so the ident daemon is not used) with the ident method, I get rejected. If I create an ident map to map the numeric UID to my name, it works: # MAPNAME IDENT-USERNAMEPG-USERNAME ldapuser 1000 bortzmeyer It is of course very inconvenient to duplicate my LDAP database into pg_ident.conf. Is there a better way? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Error: catalog is missing 8 attribute(s) for relid 16683
Hello.. I've got this error and I don't know how to fix it. Since it's an production database , I can't drop recreate it.. Here are some infos below.. [EMAIL PROTECTED] gateway]# psql -U postgres -h 127.0.0.1 template1Welcome to psql 7.4.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quittemplate1=# \dtERROR: catalog is missing 8 attribute(s) for relid 16683template1=# VACUUM FULL;ERROR: catalog is missing 3 attribute(s) for relid 16656 There is some data in pg_attribute : template1=# select * from pg_attribute LIMIT 3;attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount--+--+--+---+++--+-+---+--++--+--++---+--++- 1247 | typname | 19 | -1 | 64 | 1 | 0 | -1 | -1 | f | p | f | i | t | f | f | t | 0 1247 | typnamespace | 26 | -1 | 4 | 2 | 0 | -1 | -1 | t | p | f | i | t | f | f | t | 0 1247 | typowner | 23 | -1 | 4 | 3 | 0 | -1 | -1 | t | p | f | i | t | f | f | t | 0(3 rows) but non for relid 16683 template1=# select * from pg_attribute where attrelid=16683;attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount--+-+--+---+++--+-+---+--++--+--++---+--++-(0 rows) i've tried an pg_dump: [EMAIL PROTECTED] gateway]# pg_dump -Umydata -h 127.0.0.1mydata data.sqlpg_dump: SQL command failedpg_dump: Error message from server: ERROR: catalog is missing 8 attribute(s) for relid 16683pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database WHERE datname = 'mydata' The table affected is pg_user: [EMAIL PROTECTED] root]# oid2name -H 127.0.0.1 -U mydata -dmydata -o 16683Tablename of oid 16683 from database "mydata":-16683 = pg_user[EMAIL PROTECTED] root]# oid2name -H 127.0.0.1 -U mydata -d mydata -o 16656Tablename of oid 16656 from database "mydata":-16656 = pg_toast_16384[EMAIL PROTECTED] root]# oid2name -H 127.0.0.1 -U mydata -d mydata -o 16384Tablename of oid 16384 from database "mydata":-16384 = pg_attrdef Any select from pg_user fails.. template1=# select * from pg_user;ERROR: catalog is missing 8 attribute(s) for relid 16683 Even after REINDEX template1=# reindex table pg_attribute;REINDEX template1=# REINDEX INDEX pg_attribute_relid_attnum_index;REINDEX template1=# select * from pg_user;ERROR: catalog is missing 8 attribute(s) for relid 16683 Any ideeas ? Thanks Alex
[GENERAL] PL/Perl trusted throws error on example function
Hi,
I tried to test plperl and got the following error with the very first
example from chapter 37:
CREATE OR REPLACE FUNCTION perlmax(integer,integer)
RETURNS integer AS $$
if ($_[0] $_[1]) { return $_[0]; } return $_[1];
$$ LANGUAGE plperl;
--
error from Perl function: trusted Perl functions disabled - please
upgrade Perl Safe module to version 2.09 or later at (eval 4) line 1.
--
PostgreSQL is 8.0.1, System is SuSE 9.2 (64 bit) on AMD64. Perl Safe
module says, it__s version 2.11, which seems to be the latest release on
CPAN.
plperl is installed as trusted. If I install it as plperlu
untrusted, the example above works correctly.
What is so bad with this function that I only can run it untrusted?
Regards, Frank
--
Frank Finner
Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606Mail: [EMAIL PROTECTED]
Telefax: 0271 231 8608Web: http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651
pgpVhA0LHkrXR.pgp
Description: PGP signature
Re: [GENERAL] Vacuum time degrading
On 2/28/05 6:53 PM, Tom Lane [EMAIL PROTECTED] wrote: Again, VACUUM VERBOSE info would be informative (it's sufficient to look at your larger tables for this). It took 5.2 hours again tonight to do the vacuum. I don't see anything out of the ordinary - no explanation for the non-linear increases in vacuum time. This is what shows up at the end: INFO: free space map: 93 relations, 282 pages stored; 1712 total pages needed DETAIL: Allocated FSM size: 1000 relations + 100 pages = 5920 kB shared memory. The tables all show something like: INFO: blah: found 0 removable, 366326534 nonremovable row versions in 3241829 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. Wes ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [ADMIN] pg_shadow passwd decrypt
Hemapriya wrote: Hi, Does postgres support any system functions that decode/decrypt the user passwords stored in pg_shadow system catalog. Is there a way to retrieve the passwords, if forgotten. No. They are MD5 and are designed to be unencryptable. -- 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: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] pg_shadow passwd decrypt
Hi, Does postgres support any system functions that decode/decrypt the user passwords stored in pg_shadow system catalog. Is there a way to retrieve the passwords, if forgotten. Thanks in Advance. Priya __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Database Name
Envbop [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi I've just inherited a PostgreSQL database, for which I do not have any details of, like database name or the users. This used to be a library database which was managed via a web page written in php. Its running on a Linux box. The front end was also written in php. The original writers of this application is no longer. I've just started to get familiarisd with postgresql in the last week. and have installed PostgreSQL v8.0.1 on a Windows server. And I am trying to connect to the linux box via the phppgadmin tool. as well as trying an odbc connection. Can someone tell me where I can find the database names. FWIW: phppgAdmin is a good interface if you ONLY have web-based access to the database if you need a real functionality -- I recommend pgAdmin III -- much more functional and feature rich with a better interface and the ability to extract SQL the constructs used to create the tables, etc ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pgadmin3 / postgresql newbie question
That particular error message is typically associated with a missing pg_hba.conf entry that tells the postgreSQL server which remote machines to allow connections FROM # If you want to allow non-local connections, you need to add more # host records. Also, remember TCP/IP connections are only enabled # if you enable tcpip_socket in postgresql.conf. # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD local all all trust # IPv4-style local connections: hostall all 127.0.0.1 255.255.255.255 trust # IPv6-style local connections: hostall all ::1 :::::::trust host all all 10.1.10.23 255.255.255.255 trust Regards Jonathan Schreiter [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] hi all, running amd64 fedora core 3 w/ default postgresql 7.4.7-3. did asu - , su postgres and createdb mydb as explained in the postgresql tutorial. installed the latest pgadmin3 and am trying to connect to this database. as i wasn't sure what the FC3 default password was for postgres, i changed it to something i could remember. i can't seem to connect to the new database using pgadmin3. i have the correct IP address of the local computer, default port 5432, mydb as the initaldb, postgres as the username, and my new password as the password. i keep getting the error Error connecting to the server: could not connect to server: Connection refused Is the server running on host 192.168.1.24 and accepting TCP/IP connections on port 5432? i also verified the postgresql service is running, and that i've added the following to /etc/raddb/postgresql.conf: login = postgres password = mynewpassword and right underneath it: tcpip = true i've also disabled my local firewall and SELINUX just for kicks. and yes, i did a reboot. so...anyone know what else i can look at? many thanks! jonathan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] aggregate functions on massive number of rows
I have an aggregate function setup (attached) that I'm calling over a
massive amount of data and am running into:
ERROR: cannot have more than 2^32-1 commands in a transaction
CONTEXT: PL/pgSQL function float8_jitter_add line 16 at if
error. Since I'm able to do count() and avg() over the same values
without this aggregate function, it's theoretically possible.
Something was making me think that it was the extract() that was doing
it (I used to have 'select extract(milliseconds from v_rtt_in) into
v_rtt' and something leaded me to believe the error was with that), but
I don't recall what that is, so it looks like it's just the if like it
says.
Since I'm not really doing anything transaction-oriented in
float8_jitter_add, is there a way to get around this? Or is this a
limitation in pl/pgsql?
thanks in advance,
-Todd
---snip---
create or replace function float8_jitter_add(float8[], interval)
returns float8[] as '
declare
v_old_state ALIAS FOR $1;
v_rtt_in ALIAS FOR $2;
v_state float8[];
v_rtt float8;
BEGIN
v_state := v_old_state;
v_rtt := extract(milliseconds from v_rtt_in);
IF v_old_state is NULL THEN
v_state := ''{0,0,0,0}'';
v_state[1] = 0;
v_state[2] = 0;
v_state[3] = v_rtt;
v_state[4] = 1;
ELSIF v_rtt IS NOT NULL THEN
if v_old_state[4] = 1 THEN
v_state[1] := v_old_state[2] + (v_old_state[3] - v_rtt);
v_state[2] := v_old_state[2] + 1;
END IF;
v_state[3] := v_rtt;
v_state[4] := 1;
ELSE
v_state[4] := 0;
END IF;
return v_state;
END;
' language 'plpgsql';
create or replace function float8_jitter_sum(float8[])
returns float8 as '
declare
v_state ALIAS FOR $1;
v_avg float8;
BEGIN
v_avg := NULL;
if v_state[1] != 0 THEN
v_avg := v_state[1] / v_state[2];
END IF;
return v_avg;
END;
' language 'plpgsql';
create aggregate jitter (
basetype = interval,
sfunc = float8_jitter_add,
stype = float8[],
finalfunc = float8_jitter_sum
);
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] aggregate functions on massive number of rows
On Wed, Mar 02, 2005 at 12:23:45PM -0500, Todd Kover wrote: I have an aggregate function setup (attached) that I'm calling over a massive amount of data and am running into: ERROR: cannot have more than 2^32-1 commands in a transaction CONTEXT: PL/pgSQL function float8_jitter_add line 16 at if error. Since I'm able to do count() and avg() over the same values without this aggregate function, it's theoretically possible. Something was making me think that it was the extract() that was doing it (I used to have 'select extract(milliseconds from v_rtt_in) into v_rtt' and something leaded me to believe the error was with that), but I don't recall what that is, so it looks like it's just the if like it says. Note the following from the PL/pgSQL Expressions documentation: All expressions used in PL/pgSQL statements are processed using the server's regular SQL executor. In effect, a query like SELECT expression is executed using the SPI manager. I'd guess that you are indeed hitting the command limit. You might have more luck with one of the other procedural languages (PL/Perl, PL/Tcl, PL/Python, etc.), but I'd consider coding something like this in C if I were using it with so much data. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] sql join question
Got it. Thanks! Scott On Mar 1, 2005, at 10:52 PM, Ragnar Hafstað wrote: On Tue, 2005-03-01 at 16:51 -0800, Scott Frankel wrote: Sweet! And not so sweet. The natural join worked beautifully with my test schema; but it failed to yield any rows with my real-world schema. I think I've tracked down why: duplicate column names. i.e.: ... CREATE TABLE palettes (palette_pkey SERIAL PRIMARY KEY, palette_name text UNIQUE DEFAULT NULL, qwe text); CREATE TABLE tones(tone_pkey SERIAL PRIMARY KEY, tone_name text UNIQUE DEFAULT NULL, palette_pkey integer REFERENCES palettes, qwe text); Are the 'qwe' columns in both tables clobbering each other and preventing the join from succeeding? the docs really explain this better than I can, but a table1 NATURAL JOIN table2 is shorthand fo a table1 JOIN table2 USING (list_of_common_keys) so: select color_name from palettes join tones USING (palette_pkey) join colors USING (tone_pkey) where palette_name='plt1'; see: http://www.postgresql.org/docs/8.0/interactive/sql-select.html gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Vacuum time degrading
On 3/2/05 12:16 PM, Tom Lane [EMAIL PROTECTED] wrote: Would you post the complete VACUUM VERBOSE log? The CPU/elapsed time lines would help us identify where the time is going. I'll send it to you directly - its rather long. DETAIL: Allocated FSM size: 1000 relations + 100 pages = 5920 kB shared memory. Well, you don't have a problem with FSM being too small anyway ;-) Nope... Preparation for when deletes start kicking in down the road. If I can only do a vacuum once a week, I've got to have lots of space. Wes ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Vacuum time degrading
Wes [EMAIL PROTECTED] writes: It took 5.2 hours again tonight to do the vacuum. I don't see anything out of the ordinary - no explanation for the non-linear increases in vacuum time. Would you post the complete VACUUM VERBOSE log? The CPU/elapsed time lines would help us identify where the time is going. This is what shows up at the end: INFO: free space map: 93 relations, 282 pages stored; 1712 total pages needed DETAIL: Allocated FSM size: 1000 relations + 100 pages = 5920 kB shared memory. Well, you don't have a problem with FSM being too small anyway ;-) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Vacuum time degrading
On 3/2/05 12:16 PM, Tom Lane [EMAIL PROTECTED] wrote: Would you post the complete VACUUM VERBOSE log? The CPU/elapsed time lines would help us identify where the time is going. Mailed. I do see stats like: CPU 518.88s/25.17u sec elapsed 10825.33 sec. CPU 884.96s/64.35u sec elapsed 13793.13 sec. CPU 132.46s/6.66u sec elapsed 2435.42 sec. CPU 49.25s/4.15u sec elapsed 414.71 sec. This is a dual CPU hyperthreaded (which probably makes little difference here) 2.4Ghz RedHat 3.0. The database is on an 8-disk SCSI hardware RAID 5 with 10k rpm disks. Pg_xlog is on a separate volume. I thought it was a 2.6 kernel, but it looks like it is 2.4.20. I need to monitor the system when the vacuum is running to see if sar/top show anything. I wonder if it's hitting the kswapd thrashing problem? Wes ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pgadmin3 / postgresql newbie question
pgadmin3 does not seem to want to connect to the server using UNIX sockets, it only does it through TCP. Set up the Postgres server to use TCP (as well as UNIX sockets) - i.e. - starting postmaster with the -i option, sorting it pg_hba.conf etc. Eddy Jonathan Schreiter wrote: hi all, running amd64 fedora core 3 w/ default postgresql 7.4.7-3. did asu - , su postgres and createdb mydb as explained in the postgresql tutorial. installed the latest pgadmin3 and am trying to connect to this database. as i wasn't sure what the FC3 default password was for postgres, i changed it to something i could remember. i can't seem to connect to the new database using pgadmin3. i have the correct IP address of the local computer, default port 5432, mydb as the initaldb, postgres as the username, and my new password as the password. i keep getting the error Error connecting to the server: could not connect to server: Connection refused Is the server running on host 192.168.1.24 and accepting TCP/IP connections on port 5432? i also verified the postgresql service is running, and that i've added the following to /etc/raddb/postgresql.conf: login = postgres password = mynewpassword and right underneath it: tcpip = true i've also disabled my local firewall and SELINUX just for kicks. and yes, i did a reboot. so...anyone know what else i can look at? many thanks! jonathan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Error: catalog is missing 8 attribute(s) for relid
Alexandru Coseru wrote: Hello.. snip content=Error Details/ Any ideeas ? Fraid so and it is not good. I am no expert in the inner-inner workings of Postgres, but my guess is that your catalogue is, or has been, corrupt. If this is the case it is unrecoverable. It is a matter of rescuing what data you can (using pg_dump), and re-creating the database (or even the entire cluster) from scratch. Eddy Thanks Alex ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [Auth] ident method and LDAP user accounts
Stephane Bortzmeyer [EMAIL PROTECTED] writes: It is of course very inconvenient to duplicate my LDAP database into pg_ident.conf. Is there a better way? Perhaps you can find a PAM plugin that talks to LDAP, and configure Postgres to use that. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Error: catalog is missing 8 attribute(s) for relid 16683
Alexandru Coseru [EMAIL PROTECTED] writes: [EMAIL PROTECTED] gateway]# psql -U postgres -h 127.0.0.1 template1 Welcome to psql 7.4.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=3D# \dt ERROR: catalog is missing 8 attribute(s) for relid 16683 template1=3D# VACUUM FULL; ERROR: catalog is missing 3 attribute(s) for relid 16656 Something very bad has happened to pg_attribute. I don't think there's any useful way to recover that database; however, if it's only template1 that is corrupted, you could drop template1 and recreate it from template0 (see techdocs.postgresql.org for detailed instructions). The rest of your message suggests that the same corruption has occurred in both template1 and your mydata database. That's really odd. Maybe template1 was already broken when you cloned it to make mydata? But I think you'd have noticed before getting very far. It's barely possible that you could get to a state where pg_dump would succeed by dropping and recreating the pg_user view --- since it's only a view, there's no data to lose. I expect that DROP VIEW would not work but you could simply delete the pg_class row (DELETE FROM pg_class WHERE oid = 16683) and then make a new view using the same definition you see in the initdb script: CREATE VIEW pg_catalog.pg_user AS SELECT usename, usesysid, usecreatedb, usesuper, usecatupd, ''::text as passwd, valuntil, useconfig FROM pg_shadow; regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Index size
On Tue, 1 Mar 2005, Tom Lane wrote: Tatsuo Ishii [EMAIL PROTECTED] writes: So it seems Ioannis' number was not taken immediately after a CREATE INDEX operation? I would guess not, but it's up to him to say. If it is a number derived after some period of normal operation, then his result agrees with the theory that says 70% is the steady-state figure ... yes, my number was taken after a large amount of inserts. Your comments about the block usage in case of b-tree indexes are absolutely interesting. Where can i find a documentation with technical analysis for all (if possible) of components of postgres? All documentations that i have found are very general and refer to simple users. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Index size
Ioannis Theoharis [EMAIL PROTECTED] writes: Where can i find a documentation with technical analysis for all (if possible) of components of postgres? Read the source code. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Index size
On Wed, 2 Mar 2005, Tatsuo Ishii wrote: An other question: Is there any way to prevent duplicates on btree index attribute, PERMITTING them on table? I can't think of any usefull usage for such an index. Can you explain why you need it? I have a relation like this: (att0 varchar(1000), att1 int4) i create a b-tree index on att1 () i cluster my raltion according to index now i have a query select * formtc20 where att1=9 and att1=0 ; As far as i can see from explain analyze an index scan is used: Index Scan using inst_id_idx on tc20 Index Cond: ((att1 = 9) AND (att1 = 0)) If for each entry in table, an entry in index is beeing held, then the index size is populated too fast. I guess, that postgres uses index to find the first entry satisfying the index conition, after find the last one and then do a sequential scan on the appropriate fraction of the table (to take advantage of physical clustering). In my case, discrete values on att1 are orders of magnitude less than number of table raws. Thus, the big index size is useless for me. I want to avoid the overhead of scanning such a big index, just permitting ONLY the discrete values to entry in index. In such a way the whole scenario i presented before for how i guess, that postgres evaluates my query, is still in use. I think there must be a way to change the way of index_usage to alter it to what i 'm looking for. -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fwd: Re: [GENERAL] [ADMIN] pg_shadow passwd decrypt
One can, however, simply delete the forgotten password and create another for temporary usage until the user in question can create a new one. (At least one could in 7.4, I have not tried it in 8.0.) Jeff --- Bruce Momjian pgman@candle.pha.pa.us wrote: From: Bruce Momjian pgman@candle.pha.pa.us Subject: Re: [GENERAL] [ADMIN] pg_shadow passwd decrypt To: Hemapriya [EMAIL PROTECTED] Date: Wed, 2 Mar 2005 11:16:19 -0500 (EST) CC: pgsql-admin@postgresql.org, pgsql-general@postgresql.org Hemapriya wrote: Hi, Does postgres support any system functions that decode/decrypt the user passwords stored in pg_shadow system catalog. Is there a way to retrieve the passwords, if forgotten. No. They are MD5 and are designed to be unencryptable. -- 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: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Index size
On Wed, Mar 02, 2005 at 10:08:58PM +0200, Ioannis Theoharis wrote: I have a relation like this: (att0 varchar(1000), att1 int4) i create a b-tree index on att1 () i cluster my raltion according to index now i have a query select* form tc20 where att1=9 and att1=0 ; As far as i can see from explain analyze an index scan is used: Index Scan using inst_id_idx on tc20 Index Cond: ((att1 = 9) AND (att1 = 0)) If for each entry in table, an entry in index is beeing held, then the index size is populated too fast. I guess, that postgres uses index to find the first entry satisfying the index conition, after find the last one and then do a sequential scan on the appropriate fraction of the table (to take advantage of physical clustering). What makes you think that? Clustering is nice, but postgresql needs to get the right answer and that the table in clustered is not something postgresql can rely on. It uses the index to find *every* row you're looking for, there's no shortcut here. In my case, discrete values on att1 are orders of magnitude less than number of table raws. Thus, the big index size is useless for me. I want to avoid the overhead of scanning such a big index, just permitting ONLY the discrete values to entry in index. In such a way the whole scenario i presented before for how i guess, that postgres evaluates my query, is still in use. There's no special relationship between two rows with the same att1. Either you find the rows by using an index for each row, or scanning the whole table. There's no inbetween. The only thing clustering acheives is that due to values being together, the chance that succeeding indexes entries will already have been loaded is higher, thus reducing the overall cost. -- Martijn van Oosterhout kleptog@svana.org 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. pgpIpJtaXYEbh.pgp Description: PGP signature
Re: [GENERAL] pgpool
Brian Maguire wrote: Is anyone currently using pgpool with production high volume use? I am interested in your success and challenges. There isn't much special about high volume use. Just make sure you configure the pool size appropriately for your application and configure a corresponding number of connection slots in the PostgreSQL server. Note that pgpool has not authentication mechanism of its own, so you need to firewall it or confine it to the local machine. It may also be worthwhile to include both pgpool and the PostgreSQL server in some sort of monitoring application, say, Nagios. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Vacuum time degrading
Watching the system as vacuum is running, I can see that we are encountering the kswapd/kscand problem in the 2.4.20 kernel. This could very well account for the non-linear increase in vacuum time. This problem is fixed in the 2.6 kernel, but we can't upgrade because DELL is dragging their feet in releasing hardware monitoring compatible with 2.6 kernel. So, we're going to try a 2.4.29 kernel and hope that the problem is fixed there. With any luck, by Friday I'll know if the kswapd problem is fixed in 2.4.29 and if that solves the excessive vacuum times. Wes ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Vacuum time degrading
Wes [EMAIL PROTECTED] writes: Watching the system as vacuum is running, I can see that we are encountering the kswapd/kscand problem in the 2.4.20 kernel. This could very well account for the non-linear increase in vacuum time. Hmm. Looking at the vacuum verbose output you sent me, it's clear that the bulk of the time is going into scanning a couple of the larger indexes. On an index that's been growing for awhile, this involves a highly nonsequential access pattern (it wants to visit the index leaf pages in sort order, which will not look much like physical order after a lot of page splits have occurred). I don't know whether that would tend to set off the kswapd/kscand problems, but just in terms of physical I/O time it might be annoying. I was going to suggest REINDEXing those indexes to see if that cuts the vacuum time at all. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Index size
On Wed, Mar 02, 2005 at 11:30:58PM +0200, Ioannis Theoharis wrote: On Wed, 2 Mar 2005, Martijn van Oosterhout wrote: What makes you think that? Clustering is nice, but postgresql needs to get the right answer and that the table in clustered is not something postgresql can rely on. If postgresql doesn't rely on it, it' s postgresql's technical decision (and i don't know the reason) and not a default decision between rdbms's. But if you know exactly the reason, it would be a great help for me to know it. Easy, if you CLUSTER a table, it's CLUSTERed then. But it doesn't stay that way. As soon as you insert a new row, or update an old one, it gets added to the end (the only place with space) and now it's not clustered anymore. It's almost clustered and from a caching point of view it's fine. But postgresql can't assume at any point a table will stay clustered, an insert could happen in the middle of your processing. Logically you can't magically add space in the middle of a file, you have to move everything else up. If you know an efficient way to keep a table clustered while handling arbitrary inserts and updates, I'd be curious to know... -- Martijn van Oosterhout kleptog@svana.org 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. pgpr0qhchYYud.pgp Description: PGP signature
Re: [GENERAL] pgadmin3 / postgresql newbie question
Jonathan Schreiter wrote: i can't seem to connect to the new database using pgadmin3. i have the correct IP address of the local computer, default port 5432, mydb as the initaldb, postgres as the username, and my new password as the password. i keep getting the error Error connecting to the server: could not connect to server: Connection refused Is the server running on host 192.168.1.24 and accepting TCP/IP connections on port 5432? Make sure to include the above address (or most likely the hostname equivalent) to your list of listen_addresses in postgresql.conf. -- Guy Rouillier ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Vacuum time degrading
Wes Palmer [EMAIL PROTECTED] writes: Any chance of change that behavior to scan in physical storage order? It wouldn't be easy --- there are some locking considerations that say btbulkdelete needs to scan the index in the same order that an ordinary scan would do. See the nbtree README for details. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] does postgres supports syslog-ng package for loging rather than using standart syslog package
Dear All, I facing problem in my log rotation for postgres, it is not rotation properly. I am using syslog-ng package for loging. My logs for postgres looks like this, -rw-r--r--1 root root 10M Feb 28 23:24 postgres.log.20 -rw-r--r--1 root root0 Feb 28 20:16 postgres.log.21 -rw-r--r--1 root root 10M Feb 28 20:15 postgres.log.22 -rw-r--r--1 root root0 Feb 28 17:04 postgres.log.23 -rw-r--r--1 root root 10M Feb 28 17:03 postgres.log.24 -rw-r--r--1 root root0 Feb 28 13:52 postgres.log.25 -rw-r--r--1 root root 10M Feb 28 13:51 postgres.log.26 -rw-r--r--1 root root0 Feb 28 10:56 postgres.log.27 -rw-r--r--1 root root 10M Feb 28 10:55 postgres.log.28 -rw-r--r--1 root root0 Feb 28 08:40 postgres.log.29 -rw-r--r--1 root root0 Mar 2 00:12 postgres.log.3 That's after rotation it generates two files one with 0MB size and another with is of 10MB, so, why it is generating 0MB size log files?? Does it supports syslogng package for loging? If yes then what is the difference is its configuration from normal syslog package. Any help appreciated. -jack __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
