[BUGS] JDBC-Interface - Behaviour on Update, Insert or Delete returning ResultSets - Inconsistency to Console & ODBC
Dear Ladies and Gentlemen, I do not know if this can even be considered a bug, but I would be pleased, if somebody could make a statement on this: environment: PostgreSQL 8.1 and 8.2 Redhat and WinXP While using views calling functions on INSERT, UPDATE and DELETE via according rules, I noticed an inconsistent behaviour of the JDBC-interface of postgres; an INSERT-, UPDATE- or DELETE-statement producing tuples as return-value (in our case through rules calling functions but although via the 'RETURNING'-clause of a single INSERT- or UPDATE-statement) is returning the expected number of tuples when called via the console (even through pgAdmin) or via ODBC BUT when called via JDBC only an 'UpdateCount' of 0 is returned; debugging to protocol-level showed, that the postgreSQL-server does not even differ between a 'simple' UPDATE or one returning tuples; a 'simple' UPDATE returning only an 'UpdateCount' produces the following sequence of commands (at protocol-level): 49 - 50 - 110 - 67 - 90 or in characters '1' - '2' - 'n' - 'C' - Z' exactly the same is returned on an UPDATE returning tuples; of course, as 'n' means that no data is available (according to class org.postgresql.core.v3.QueryExecutorImpl) no tuple will be available and the 'UpdateCount' will also be 0; ... so am I right guessing that there is no way to retrieve the resulting tuples via JDBC? (- in difference to the console and ODBC) For our needs we implemented a java.sql.Driver encapsulating the sun.jdbc.odbc.JdbcOdbcDriver that returns the expected values as a workaround, but that causes additional conversions that are not really necessary; in my opinion it would be a better solution to leave the decision, whether to return the tuples, an 'UpdateCount' or even throw an Exception up to the implementor of the driver and not to ignore the fact that tuples where produced already on server-side; I am looking forward to hearing your point of view! With best regards, Otto Weichselbaum -- SEW – Otto Weichselbaum DI (FH) Otto Weichselbaum A-4040 LINZ, Heindlstrasse 19/4 eMail: [EMAIL PROTECTED] fax: +43 (0) 732 925400 mobile: +43 (0) 664 825
Fwd: [BUGS] error while starting database
Hi, When i m using ps -ax |grep postgres [EMAIL PROTECTED] /root]# ps -ax |grep postgres 994 tty1 S 0:00 su - postgres when i am using netstat -a , it gives Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State tcp0 0 *:32768 *:* LISTEN tcp0 0 *:shell *:* LISTEN tcp0 0 *:time *:* LISTEN tcp0 0 *:8007 *:* LISTEN tcp0 0 *:netbios-ssn *:* LISTEN tcp0 0 *:pop3 *:* LISTEN tcp0 0 *:imap *:* LISTEN tcp0 0 *:finger*:* LISTEN tcp0 0 *:sunrpc*:* LISTEN tcp0 0 *:http *:* LISTEN tcp0 0 *:ftp *:* LISTEN tcp0 0 *:ssh *:* LISTEN tcp0 0 *:telnet*:* LISTEN tcp0 0 *:smtp *:* LISTEN tcp0 0 10.2.0.48:http 192.168.255.10:35046 TIME_WAIT tcp0138 10.2.0.48:telnet10.1.65.25:4468 ESTABLISHED udp0 0 *:32768 *:* udp0 0 10.2.0.48:netbios-ns*:* udp0 0 *:netbios-ns*:* udp0 0 10.2.0.48:netbios-dgm *:* udp0 0 *:netbios-dgm *:* udp0 0 *:680 *:* udp0 0 *:tftp *:* udp0 0 *:sunrpc*:* Active UNIX domain sockets (servers and established) Proto RefCnt Flags Type State I-Node Path unix 2 [ ACC ] STREAM LISTENING 1405 /dev/gpmctl unix 10 [ ] DGRAM776/dev/log unix 2 [ ] DGRAM9828 unix 2 [ ] DGRAM1556 unix 2 [ ] DGRAM1417 unix 2 [ ] DGRAM1386 unix 2 [ ] DGRAM1032 unix 2 [ ] DGRAM973 unix 2 [ ] DGRAM821 unix 2 [ ] DGRAM785 unix 2 [ ] STREAM CONNECTED 525 Please help as it is urgent. Regards: Nitin Saxena 91-985709 -- Forwarded message -- From: Nitin Saxena <[EMAIL PROTECTED]> Date: Aug 18, 2007 8:42 AM Subject: Re: [BUGS] error while starting database To: Douglas Toltzman <[EMAIL PROTECTED]>, pgsql-bugs@postgresql.org Hi All the command such asgives following output; 1: :ls -a/tmp: No such file or directory 2 : "rm -f/tmp/.s.PGSQL.5432" bash: rm -f/tmp/.s.PGSQL.5432: No such file or directory 3:"ps -ax|grep postgres" bash: ps -ax|grep postgres: command not found Does PID in postmaster.pid file is automatically created when we start the database. When i am giving command pg_ctl status at root, It is giving error message as pg_ctl: No database directory or environment variable $PGDATA is specified and When i am giving command pg_ctl status at bash it is giving postmaster is running [EMAIL PROTECTED] /root]# su - postgres bash-2.04$ pg_ctl status pg_ctl: postmaster is running (pid: 776) options are: /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data -B 64 -b /usr/bin/postgres -i -N 32 When i am giving command at root directory: [EMAIL PROTECTED] /root]# pg_ctl stop pg_ctl: No database directory or environment variable $PGDATA is specified [EMAIL PROTECTED] /root]# su - postgres bash-2.04$ pg_ctl stop /usr/bin/pg_ctl: kill: (776) - No such pid postmaster successfully shut down. when i am giving command bash-2.04$ pg_ctl start pg_ctl: It seems another postmaster is running. Try to start postmaster anyway. FATAL: StreamServerPort: bind() failed: No such file or directory Is another postmaster already running on that port? If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. pg_ctl: Cannot start postmaster. Is another postmaster is running? bash-2.04$ /usr/bin/postmaster: cannot create UNIX stream port when i am giving pg_ctl restart bash-2.04$ pg_ctl restart /usr/bin/pg_ctl: kill: (776) - No such pid Waiting for postmaster shutting down...pg_ctl: postmaster does not shut down Please tell how to start services in postgres when i m giving command [EMAIL PROTECTED] /root]# service postgresql restart Stopping postgresql service: [FAILED] Checking postgresql installation: [
Re: [BUGS] error while starting database
Hi
All the command such asgives following output;
1: :ls -a/tmp: No such file or directory
2 : "rm -f/tmp/.s.PGSQL.5432"
bash: rm -f/tmp/.s.PGSQL.5432: No such file or directory
3:"ps -ax|grep postgres"
bash: ps -ax|grep postgres: command not found
Does PID in postmaster.pid file is automatically created when we start the
database.
When i am giving command pg_ctl status at root, It is giving error message
as
pg_ctl: No database directory or environment variable $PGDATA is specified
and
When i am giving command pg_ctl status at bash it is giving postmaster is
running
[EMAIL PROTECTED] /root]# su - postgres
bash-2.04$ pg_ctl status
pg_ctl: postmaster is running (pid: 776)
options are:
/usr/bin/postmaster
-p 5432
-D /var/lib/pgsql/data
-B 64
-b /usr/bin/postgres
-i
-N 32
When i am giving command at root directory:
[EMAIL PROTECTED] /root]# pg_ctl stop
pg_ctl: No database directory or environment variable $PGDATA is specified
[EMAIL PROTECTED] /root]# su - postgres
bash-2.04$ pg_ctl stop
/usr/bin/pg_ctl: kill: (776) - No such pid
postmaster successfully shut down.
when i am giving command
bash-2.04$ pg_ctl start
pg_ctl: It seems another postmaster is running. Try to start postmaster
anyway.
FATAL: StreamServerPort: bind() failed: No such file or directory
Is another postmaster already running on that port?
If not, remove socket node (/tmp/.s.PGSQL.5432) and retry.
pg_ctl: Cannot start postmaster. Is another postmaster is running?
bash-2.04$ /usr/bin/postmaster: cannot create UNIX stream port
when i am giving pg_ctl restart
bash-2.04$ pg_ctl restart
/usr/bin/pg_ctl: kill: (776) - No such pid
Waiting for postmaster shutting
down...pg_ctl:
postmaster does not shut down
Please tell how to start services in postgres
when i m giving command
[EMAIL PROTECTED] /root]# service postgresql restart
Stopping postgresql service: [FAILED]
Checking postgresql installation: [ OK ]
Starting postgresql service: [FAILED]
Please help ASAP, as i have to up the application as soon as possible
Thanks & Regards:
Nitin Saxena
On 8/17/07, Douglas Toltzman <[EMAIL PROTECTED]> wrote:
>
> PostgreSQL version 7.0 is really, really out of date, but as far as I
> know, it should still work.
>
> I generally use "ps -ax | grep postges" to see if there is a postgres
> process running. Also, I use "netstat -a" to see open sockets and listening
> servers.
>
> Have you removed the socket file in /tmp and tried to start the server?
> You may also want to check the server log to see if there is any indication
> of why it crashed. Once you clean up the residue from the server that
> abended, you'll be able to start it again, unless there are serious problems
> with the database. I've only seen one case where I couldn't get the server
> restarted, though, and that was after a hard drive failure caused corruption
> in several files. Even then, I was able to recover everything.
>
> BTW: the socket file ("/tmp/.s.PGSQL.5432") will be a hidden file. You'll
> need to "ls -a /tmp" to see it. You can just "rm -f /tmp/.s.PGSQL.5432" as
> root and that should clean it up for you.
>
> p.s. This is not a bug, and even if it was, I'm sure version 7.0 is no
> longer supported. You may want to use a different list if you require
> additional assistance.
>
> On Aug 17, 2007, at 10:31 AM, Nitin Saxena wrote:
>
>
>
> I am using postgres 7.0 on linux platform.
> My java application was running fine ,but i got this message
>
> Connection refused. Check that the hostname and port is correct, and that
> the postmaster is r
> unning with the -i flag, which enables TCP/IP networking.
> at org.postgresql.Connection.openConnection(Connection.java:123)
> at org.postgresql.Driver.connect(Driver.java:116)
> at java.sql.DriverManager.getConnection(DriverManager.java:517)
> at java.sql.DriverManager.getConnection(DriverManager.java:177)
> at SendGrpSmpp.readSMPPtable(SendGrpSmpp.java:219)
> at SendGrpSmpp.run(SendGrpSmpp.java:96)
>
>
> When i am connecting as su via su - postgres
> at bash: when i give psql Database Name
>
> It is giving error:
>
> psql: connectDBStart() -- connect() failed: No such file or directory
> Is the postmaster running at 'localhost'
> and accepting connections on Unix socket '5432'?
>
> when i am using pg_ctl status command:
>
> it gives output: pg_ctl: postmaster is running (pid: 776)
> options are:
> /usr/bin/postmaster
> -p 5432
> -D /var/lib/pgsql/data
> -B 64
> -b /usr/bin/postgres
> -i
> -N 32
>
>
> when i am using pg_ctl stop it gives
>
> /usr/bin/pg_ctl: kill: (776) - No such pid
> postmaster successfully shut down.
>
>
> when i am using pg_ctl restart it gives
>
>
> bash-2.04$ pg_ctl restart
> /usr/bin/pg_ctl: kill: (776) - No such pid
> Waiting for postmaster shutting
> down...
Re: [BUGS] error while starting database
Since you do not have a /tmp folder in your filesystem, it may be that the server cannot create the socket file and so it will not start. You cannot telnet to port 5432, because no service is listening on that port. The PostgreSQL server is not started. I'm sorry I didn't answer you last night. It was 02:00 in the morning when I got home and sent you the previous message, I went straight to bed after checking my mail. In any case, the server specifically calls out an error creating "/tmp/.s.PGSQL.5432". I would start by creating a world-writable /tmp folder. If that allows you to start the service, then you can do a backup with pg_dumpall. On Aug 18, 2007, at 12:20 AM, Nitin Saxena wrote: Hi, When i m using ps -ax |grep postgres [EMAIL PROTECTED] /root]# ps -ax |grep postgres 994 tty1 S 0:00 su - postgres when i am using netstat -a , it gives Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State tcp0 0 *:32768 *:* LISTEN tcp0 0 *:shell *:* LISTEN tcp0 0 *:time *:* LISTEN tcp0 0 *:8007 *:* LISTEN tcp0 0 *:netbios-ssn *:* LISTEN tcp0 0 *:pop3 *:* LISTEN tcp0 0 *:imap *:* LISTEN tcp0 0 *:finger*:* LISTEN tcp0 0 *:sunrpc*:* LISTEN tcp0 0 *:http *:* LISTEN tcp0 0 *:ftp *:* LISTEN tcp0 0 *:ssh *:* LISTEN tcp0 0 *:telnet*:* LISTEN tcp0 0 *:smtp *:* LISTEN tcp0 0 10.2.0.48:http 192.168.255.10:35046 TIME_WAIT tcp0138 10.2.0.48:telnet10.1.65.25:4468 ESTABLISHED udp0 0 *:32768 *:* udp0 0 10.2.0.48:netbios-ns*:* udp0 0 *:netbios-ns*:* udp0 0 10.2.0.48:netbios-dgm *:* udp0 0 *:netbios-dgm *:* udp0 0 *:680 *:* udp0 0 *:tftp *:* udp0 0 *:sunrpc*:* Active UNIX domain sockets (servers and established) Proto RefCnt Flags Type State I-Node Path unix 2 [ ACC ] STREAM LISTENING 1405 /dev/gpmctl unix 10 [ ] DGRAM776/dev/log unix 2 [ ] DGRAM9828 unix 2 [ ] DGRAM1556 unix 2 [ ] DGRAM1417 unix 2 [ ] DGRAM1386 unix 2 [ ] DGRAM1032 unix 2 [ ] DGRAM973 unix 2 [ ] DGRAM821 unix 2 [ ] DGRAM785 unix 2 [ ] STREAM CONNECTED 525 Please help as it is urgent. Regards: Nitin Saxena 91-985709 -- Forwarded message -- From: Nitin Saxena <[EMAIL PROTECTED]> Date: Aug 18, 2007 8:42 AM Subject: Re: [BUGS] error while starting database To: Douglas Toltzman <[EMAIL PROTECTED]>, pgsql- [EMAIL PROTECTED] Hi All the command such asgives following output; 1: :ls -a/tmp: No such file or directory 2 : "rm -f/tmp/.s.PGSQL.5432" bash: rm -f/tmp/.s.PGSQL.5432: No such file or directory 3:"ps -ax|grep postgres" bash: ps -ax|grep postgres: command not found Does PID in postmaster.pid file is automatically created when we start the database. When i am giving command pg_ctl status at root, It is giving error message as pg_ctl: No database directory or environment variable $PGDATA is specified and When i am giving command pg_ctl status at bash it is giving postmaster is running [EMAIL PROTECTED] /root]# su - postgres bash-2.04$ pg_ctl status pg_ctl: postmaster is running (pid: 776) options are: /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data -B 64 -b /usr/bin/postgres -i -N 32 When i am giving command at root directory: [EMAIL PROTECTED] /root]# pg_ctl stop pg_ctl: No database directory or environment variable $PGDATA is specified [EMAIL PROTECTED] /root]# su - postgres bash-2.04$ pg_ctl stop /usr/bin/pg_ctl: kill: (776) - No such pid postmaster successfully shut down. when i am giving command bash-2.04$ pg_ctl start pg_ctl: It seems another postmaster is running. Try to start postmaster anyway. FATAL: StreamServerPort: bi
Re: [BUGS] error while starting database
"Douglas Toltzman" <[EMAIL PROTECTED]> writes: > In any case, the server specifically calls out an error creating > "/tmp/.s.PGSQL.5432". I would start by creating a world-writable /tmp > folder. FWIW the correct permissions for /tmp on Unix machines is 1777. It should look like this (note the "t" in place of the last "x"): $ ls -ld /tmp drwxrwxrwt 8 root root 12288 Aug 18 16:30 /tmp -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] error while starting database
"Nitin Saxena" <[EMAIL PROTECTED]> writes: > All the command such asgives following output; > 1: :ls -a/tmp: No such file or directory >2 : "rm -f/tmp/.s.PGSQL.5432" > bash: rm -f/tmp/.s.PGSQL.5432: No such file or directory The reason these commands aren't working for you is you're not putting any space between the switch and the filename. I suggest doing some basic studying of Unix command line stuff... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] error while starting database
All I can tell you, based on this information, Nitin, is that your postmaster is not started, and it is failing to start for some reason. Without any additional information, there is absolutely nothing I can tell you. Try listing the /var/lib/pgsql/data folder. Verify that it does exist and that it does contain the postgres configuration files, at least. I don't have time to teach you everything you need to know to administer a Un*x system or to be a DBA. If you were encountering a specific error, I'm sure someone could help you isolate it. However, this is the bugs list, as I pointed out before, it is not the correct list for determining how to configure your server. Once you get the server started, you should be able to dump your database and upgrade your software. On Aug 18, 2007, at 12:40 PM, Nitin Saxena wrote: Sir , plz reply regards: nitin On 8/18/07, Nitin Saxena <[EMAIL PROTECTED]> wrote: Dear Sir, please find the attached file.: This error is of boot.log file which i had attach with mail Aug 18 13:33:19 imap postgresql: Starting postgresql service: failed Aug 18 13:39:21 imap postgresql: postmaster shutdown failed Aug 18 13:39:23 imap postgresql: Checking postgresql installation: succeeded Aug 18 13:39:25 imap postgresql: Starting postgresql service: failed Aug 18 13:41:44 imap postgresql: postmaster shutdown failed Aug 18 13:41:46 imap postgresql: Checking postgresql installation: succeeded Aug 18 13:41:48 imap postgresql: Starting postgresql service: failed Aug 18 13:49:01 imap postgresql: postmaster shutdown failed Aug 18 13:49:03 imap postgresql: Checking postgresql installation: succeeded Aug 18 13:49:05 imap postgresql: Starting postgresql service: failed Aug 18 14:44:56 imap postgresql: postmaster shutdown failed Aug 18 14:44:58 imap postgresql: Checking postgresql installation: succeeded Aug 18 14:45:00 imap postgresql: Starting postgresql service: failed output of different commands [EMAIL PROTECTED] /]# pg_ctl status /usr/bin/pg_ctl: [: -lt: unary operator expected pg_ctl: postmaster is running (pid: ) options are: /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data -B 64 -b /usr/bin/postgres -i -N 32 [EMAIL PROTECTED] /]# pg_ctl stop /usr/bin/pg_ctl: [: -lt: unary operator expected kill: usage: kill [-s sigspec | -n signum | -sigspec] [pid | job]... or kill -l [sigspec] postmaster successfully shut down. [EMAIL PROTECTED] /]# pg_ctl start pg_ctl: It seems another postmaster is running. Try to start postmaster anyway. pg_ctl: Cannot start postmaster. Is another postmaster is running? I dont have postgresql.conf file in any of my directory I had only pg_hba.conf file. My application was working fine till yesterday. plzz help Regards: Nitin Saxena On 8/18/07, Douglas Toltzman <[EMAIL PROTECTED] > wrote: At this point, I'd have to have more information to be able to tell you what to fix. Does the server give any reason why it fails to start? There could be a configuration error or a permissions error on one of the database configuration files. Does the server give a detailed error message? Check your error logs, also, for clues. On Aug 18, 2007, at 11:45 AM, Nitin Saxena wrote: Hi, I have done it on my system drwxrwxrwt2 root root 4096 Aug 18 19:06 tmp still getting the same error. [EMAIL PROTECTED] /]# /etc/rc.d/init.d/postgresql restart Stopping postgresql service: FAILED] Checking postgresql installation: OK ] Starting postgresql service: FAILED] Now what to do sir? looking forward for your support. Regards Nitin On 8/18/07, Gregory Stark <[EMAIL PROTECTED]> wrote: "Douglas Toltzman" <[EMAIL PROTECTED]> writes: > In any case, the server specifically calls out an error creating > "/tmp/.s.PGSQL.5432". I would start by creating a world- writable /tmp > folder. FWIW the correct permissions for /tmp on Unix machines is 1777. It should look like this (note the "t" in place of the last "x"): $ ls -ld /tmp drwxrwxrwt 8 root root 12288 Aug 18 16:30 /tmp -- Gregory Stark EnterpriseDB http://www.enterprisedb.com -- NItin Saxena Douglas Toltzman [EMAIL PROTECTED] (910) 526-5938 -- NItin Saxena -- NItin Saxena Douglas Toltzman [EMAIL PROTECTED] (910) 526-5938
Re: [BUGS] JDBC-Interface - Behaviour on Update, Insert or Delete returning ResultSets - Inconsistency to Console & ODBC
On Sat, 18 Aug 2007, Otto Weichselbaum wrote: While using views calling functions on INSERT, UPDATE and DELETE via according rules, I noticed an inconsistent behaviour of the JDBC-interface of postgres; an INSERT-, UPDATE- or DELETE-statement producing tuples as return-value (in our case through rules calling functions but although via the 'RETURNING'-clause of a single INSERT- or UPDATE-statement) is returning the expected number of tuples when called via the console (even through pgAdmin) or via ODBC BUT when called via JDBC only an 'UpdateCount' of 0 is returned; debugging to protocol-level showed, that the postgreSQL-server does not even differ between a 'simple' UPDATE or one returning tuples; I believe this differing results you are seeing is based upon whether the query is executed using the simple or extended query protocol. The JDBC driver always uses extended query protocol when speaking to a V3 protocol capable server. Another workaround you can use is adding ?protocolVersion=2 to your URL to make it use the V2 protocol and simple query execution. It works with the simple query protocol because you send the server a query and then loop retrieving any number of results that are returned. The extended query protocol is designed around single query gives a single result, so it isn't really prepared to handle the update count and results at the same time. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] reindexdb in Windows
Hi, reindexdb.exe is not installed with PostgreSQL in Windows. Is it intentional or a bug? -- Euler Taveira de Oliveira http://www.timbira.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
