Re: [BUGS] BUG #3748: Postmaster Service
A couple things: On Thu, Nov 15, 2007 at 12:49:40PM +, Sudarshan V Tantry wrote: > PostgreSQL version: 7.8.2 There is no PostgreSQL version numbered that. > We are supporting a messaging system in which there are two application > servers with cluster services.Cluster service does the job of shifting the > Application and Database services from one server to another if one of the > Application servers goes down. So you have two application servers and two database servers? Two servers with the application and database on each? I don't really understand. Also, > services,by manually stopping cluster service on one server using the > command "service clumanager stop" the Application and Database services get > shifted to another server,but Application services would be down due to the > fact that it cannot connect to the Database services since the postmaster > service is down.We check the status of Postmaster service with the command In what sense do they get shifted to another server, if the postmaster doesn't start up? > Also postmaster services do not start even when the server is restarted. Is there a start up script for postgres? > > DBDATA is the database service that is mounted on the application > server. I don't understand this sentence either. But. . . > That is getting mounted but postmaster would be found stopped. . . .mounting a directory does not entail starting a database server. These are separate problems. You have to have the PostgreSQL data area mounted, and _then_ start PostgreSQL. Perhaps your cluster thingy isn't doing it in the right order. In any case, > Please let me a solution for this problem and also tell me why this problem > occurs. this doesn't seem to be a bug with PostgreSQL at all. If anything, it has something to do with this cluster thing you're using. That's in general a dangerous thing to do, and requires plenty of care. See the high availability parts of the manual to read general discussions of this, but if you're having specific problems, I think you need to talk to the people who make the cluster software. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3748: Postmaster Service
The following bug has been logged online: Bug reference: 3748 Logged by: Sudarshan V Tantry Email address: [EMAIL PROTECTED] PostgreSQL version: 7.8.2 Operating system: Redhat Linux Description:Postmaster Service Details: We are supporting a messaging system in which there are two application servers with cluster services.Cluster service does the job of shifting the Application and Database services from one server to another if one of the Application servers goes down. Now,the problem is when we were testing the automatic shift over of services,by manually stopping cluster service on one server using the command "service clumanager stop" the Application and Database services get shifted to another server,but Application services would be down due to the fact that it cannot connect to the Database services since the postmaster service is down.We check the status of Postmaster service with the command "pg_ctl status".Even if we start the Postmaster service manually it goes down whenever we stop the cluster service to shift the services back to the first server. Also postmaster services do not start even when the server is restarted. We checked the postgresql.log in which we could find this: Unable to connect to database Cannot find /var/lib/pgsql/DBDATA/data/postmaster.pid DBDATA is the database service that is mounted on the application server.That is getting mounted but postmaster would be found stopped. Please let me a solution for this problem and also tell me why this problem occurs. ---(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: [BUGS] Strange problem with an index.
"Blanco, Jose" <[EMAIL PROTECTED]> writes: > Well, this was running very slowly, so I droped the index and recreated > it, and then it ran quickly. Sounds like you had a bloated index. > I'm running versin 7.3 Consider switching to something more modern --- 7.3 is vastly slower than current releases. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3750: Invalid frontend message type 112
The following bug has been logged online: Bug reference: 3750 Logged by: Christian Ullrich Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3-beta2 Operating system: Windows Server 2003 Description:Invalid frontend message type 112 Details: I'm running 8.3-beta2 (on WS2003, the client is XP Prof.) to experiment with SSPI auth against AD. There is a persistent problem when using SSPI: Authentication appears to work, but the connection fails at the first query. psql tells me about an "invalid frontend message type 112". Most of the time, that error comes as the first line of output from psql (above the "Welcome to" line), sometimes it is displayed later: Welcome to psql 8.3beta2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=> test=> select 1; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: FATAL: invalid frontend message type 112 Succeeded. When I use another authentication method, all is well. I can provide a network trace of the connection on request. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #3751: Conversion error using PreparedStatement.setObject()
The following bug has been logged online:
Bug reference: 3751
Logged by: Lance Andersen
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.x
Operating system: N/A
Description:Conversion error using PreparedStatement.setObject()
Details:
A PreparedStatement.setObject(1, "1", Types.Bit) will fail with the
following Exception:
org.postgresql.util.PSQLException: ERROR: column "max_val" is of type bit
but expression is of type integer
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:337)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2State
ment.java:283)
at com.sun.jdbc.samples.BitTest.testSetObject48(BitTest.java:108)
at com.sun.jdbc.samples.BitTest.runTest(BitTest.java:61)
at com.sun.jdbc.samples.BitTest.main(BitTest.java:16)
Here is a simple repro:
package com.sun.jdbc.samples;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.math.BigDecimal;
import java.math.*;
import java.util.StringTokenizer;
public class BitTest {
List drivers;
public static void main(String args[]){
BitTest test = new BitTest();
test.runTest();
}
public BitTest() {
drivers = new ArrayList();
// Postgresql Drivers
drivers.add(new Drivers("org.postgresql.Driver",
"jdbc:postgresql://jsepc18.east:5432/CTS5", "cts1", "cts1" ));
}
public void runTest() {
Connection conn = null;
for(Drivers driver : drivers){
try {
Class.forName(driver.getDriver());
conn = DriverManager.getConnection(driver.getUrl(),
driver.getUser(), driver.getPassword());
DatabaseMetaData dbmeta = conn.getMetaData();
dumpVersionInfo(dbmeta);
testSetObject48(conn);
conn.close();
}catch( Exception e ) {
e.printStackTrace();
}
}
}
public static void dumpVersionInfo(DatabaseMetaData dbmeta) {
try {
System.out.println("\n***");
String productName = dbmeta.getDatabaseProductName();
String productVersion = dbmeta.getDatabaseProductVersion();
String driverName = dbmeta.getDriverName();
String driverVersion = dbmeta.getDriverVersion();
System.out.println("productName: " + productName);
System.out.println("productVersion: " + productVersion);
System.out.println("driverName: " + driverName);
System.out.println("Version: " + driverVersion);
System.out.println("***");
} catch( Exception e ) {
e.printStackTrace();
}
}
private static void testSetObject48(Connection conn) {
ResultSet rs;
String Min_Val_Query= "SELECT MIN_VAL from Bit_Tab";
String sMaxBooleanVal = "1";
//sMaxBooleanVal = "true";
Boolean bool = Boolean.valueOf("true");
String Min_Insert= "insert into Bit_Tab values(1,0,null)";
//System.out.println("Value to insert=" +
extractVal(Min_Insert,1));
CallableStatement cstmt;
try {
Statement stmt = conn.createStatement();
stmt.executeUpdate("delete from Bit_Tab");
stmt.executeUpdate(Min_Insert);
cstmt = conn.prepareCall("{call Bit_In_Min(?)}");
cstmt.setObject(1,sMaxBooleanVal,java.sql.Types.BIT);
//cstmt.setObject(1,bool,java.sql.Types.BIT);
cstmt.executeUpdate();
rs = stmt.executeQuery(Min_Val_Query);
while(rs.next()){
System.out.println("Expected value=" + sMaxBooleanVal + ",
Returned value as Boolean= " +rs.getBoolean(1)
+ ", as String=" + rs.getString(1));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3751: Conversion error using PreparedStatement.setObject()
Lance J. Andersen wrote: Thank you for your time, but i think there is still a driver issue here: If i use the same types as i sent in the email and execute select * from bit_in_min(1::bit) I have no problems and the table is correctly updated. This would lead me to believe that the driver has a problem with correctly mapping the setObect() of the String to a BIT which is required conversion by the JDBC spec. According to our reading of the JDBC spec java.sql.Types.BIT and BOOLEAN are equivalent. So it doesn't make sense to map BIT to one server type and BOOLEAN to another. When thinking about Types.BIT it's easy to see a server type named "bit" and assume it's a match, but for the semantics of Types.BIT, we think boolean is a better match. http://archives.postgresql.org/pgsql-jdbc/2004-04/msg00107.php Just because "bit" can be made to work doesn't make it the best option. Particularly our concern arises from what to do when returning meta data. Consider a table that has columns "a bit(1), b bit(2)". While a does have boolean semantics because it's limited to a length of 1, b is not. Returning b as Types.BIT would be a mistake. Driving this decision solely on the length attribute is not good because the server will not give you the length information in certain circumstances and all you'll have is the raw "bit" type name. This is why we've chose to use boolean as the server type for Types.BIT + BOOLEAN. Kris Jurka ---(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] BUG #3751: Conversion error using PreparedStatement.setObject()
On Thu, 15 Nov 2007, Lance Andersen wrote: The following bug has been logged online: Bug reference: 3751 PostgreSQL version: 8.2.x Description:Conversion error using PreparedStatement.setObject() Details: A PreparedStatement.setObject(1, "1", Types.Bit) will fail with the following Exception: This is not a great test case. 1) It doesn't contain the definition of the Drivers class so it doesn't compile. The whole drivers class and dumping metadata is needless complication for a simple test case. 2) It doesn't contain the definition of the bit_tab table, so it doesn't run. 3) The error is actually coming from "stmt.executeUpdate(Min_Insert)", not a PreparedStatement. So where does that leave us? 1) The raw insert fails. INSERT INTO bit_tab (1,0,null) fails because 1 gets typed as an integer and there are no implicit or assignment casts from integer to bit. You would need to say, '1' so it comes in untyped and converted to bit, or be explicit about the type with a cast, saying 1::bit or CAST(1 AS bit). 2) There might be a problem with bit conversion in prepared statements, but we didn't get that far. Other notes: In PG the bit type is really for multiple bits, not a single bit. Consider SELECT 77::bit(8) results in "01001101". It's more likely that you want to use boolean as the type instead although it doesn't have any casts that will help you out in this situation either. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3751: Conversion error using PreparedStatement.setObject()
The test runs for me when I change all of the underlying types from bit
to boolean:
create table Bit_Tab (MAX_VAL boolean, MIN_VAL boolean, NULL_VAL boolean
NULL) ;
CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM boolean) returns void as
'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language 'plpgsql' ;
Kris Jurka
Lance J. Andersen wrote:
Sorry Bad, Cut and paste. This test is a strip down of much larger
test. The reason the metadata is there as this gets run from a
framework which exercises JDBC drivers from all of the major vendors
which is also the reason for the Drivers class.
As far as the INSERT, i did not look at the postgresql docs in enough
detail probably given that it works against all of the other vendors
who support BIT data types, so my mistake.
Here is the the entire scenario:
The table is created as
create table Bit_Tab (MAX_VAL bit(1), MIN_VAL bit(1), NULL_VAL bit(1)
NULL) ;
and the stored procedure via
CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM bit(1)) returns void as
'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language 'plpgsql' ;
even if i change the insert as you suggest, to
insert into Bit_Tab values('1', '0', null )
it still fails
org.postgresql.util.PSQLException: ERROR: column "min_val" is of type
bit but expression is of type boolean
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
at com.sun.jdbc.samples.BitTest.testSetObject48(BitTest.java:93)
at com.sun.jdbc.samples.BitTest.runTest(BitTest.java:41)
at com.sun.jdbc.samples.BitTest.main(BitTest.java:16)
BUILD SUCCESSFUL (total time: 2 seconds)
The failure now is on cstmt.executeUpdate() which i would infer either
the driver is not doing the proper conversion or the function is having
issues.
The test is validating that a String can be sent as a BIT and returned
as a Boolean per the JDBC specifcation.
-lance
Kris Jurka wrote:
On Thu, 15 Nov 2007, Lance Andersen wrote:
The following bug has been logged online:
Bug reference: 3751
PostgreSQL version: 8.2.x
Description:Conversion error using PreparedStatement.setObject()
Details:
A PreparedStatement.setObject(1, "1", Types.Bit) will fail with the
following Exception:
This is not a great test case.
1) It doesn't contain the definition of the Drivers class so it
doesn't compile. The whole drivers class and dumping metadata is
needless complication for a simple test case.
2) It doesn't contain the definition of the bit_tab table, so it
doesn't run.
3) The error is actually coming from "stmt.executeUpdate(Min_Insert)",
not a PreparedStatement.
So where does that leave us?
1) The raw insert fails. INSERT INTO bit_tab (1,0,null) fails because
1 gets typed as an integer and there are no implicit or assignment
casts from integer to bit. You would need to say, '1' so it comes in
untyped and converted to bit, or be explicit about the type with a
cast, saying
1::bit or CAST(1 AS bit).
2) There might be a problem with bit conversion in prepared
statements, but we didn't get that far.
Other notes:
In PG the bit type is really for multiple bits, not a single bit.
Consider SELECT 77::bit(8) results in "01001101". It's more likely
that you want to use boolean as the type instead although it doesn't
have any casts that will help you out in this situation either.
Kris Jurka
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[BUGS] B-tree crash recovery error in 8.3 beta 2
Hi, I've found that B-tree crash recovery in 8.3 beta2 could make some tuples invisible through B-tree. They're visible if we read using but Seq-Scan. This happens in 8.3 beta2, but not in 8.2.4. Here's how it happens. 1. Create b-tree for a text type column. 2. Make B-tree three-story, that is, root-intermediate-leaf. Insert tuples sufficient to construct such B-tree. 3. No checkpoint should occur during 2. 4. Kill postmaster. 5. Restart postmaster. Crash recovery will be done. 6. Tuples with column values less than HIKEY becomes invisible through Idx-scan, still visible through Seq-scan. >From the dump of B-tree, it seems that HIKEY value is cleared (only tuple header is left). No problem was found in the case of integer or numeric type columns. Attached is the shell script, postgresql.conf (almost the default one) to reproduce the problem, and the log of the problem reproduction. -- Koichi Suzuki reproduce_text2_en.sh Description: application/shellscript DROP TABLE CREATE TABLE CREATE INDEX INSERT 0 27249 magic | version | root | level | fastroot | fastlevel +-+--+---+--+--- 340322 | 2 |3 | 1 |3 | 1 (1 row) CHECKPOINT INSERT 0 1 magic | version | root | level | fastroot | fastlevel +-+--+---+--+--- 340322 | 2 | 231 | 2 | 231 | 2 (1 row) itemoffset | ctid | itemlen | nulls | vars | data +-+-+---+--+- 1 | (71,1) | 32 | f | t| 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 31 39 30 36 36 00 00 2 | (1,1) | 8 | f | f| 3 | (229,1) | 32 | f | t| 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 31 30 36 00 00 4 | (228,1) | 32 | f | t| 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 32 32 36 00 00 5 | (227,1) | 32 | f | t| 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 33 34 36 00 00 6 | (226,1) | 32 | f | t| 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 34 36 36 00 00 7 | (225,1) | 32 | f | t| 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 35 38 36 00 00 8 | (224,1) | 32 | f | t| 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 37 30 36 00 00 9 | (223,1) | 32 | f | t| 2d 50 6f 73 74 67 72 65 53 51 4c 2d 30 30 30 30 30 30 30 38 32 36 00 00 (9 rows) -- Visible tuples (name = HIKEY) explain select * from test where name = 'PostgreSQL-000147'; QUERY PLAN Bitmap Heap Scan on test (cost=9.14..173.19 rows=114 width=32) Recheck Cond: (name = 'PostgreSQL-000147'::text) -> Bitmap Index Scan on id_test (cost=0.00..9.11 rows=114 width=0) Index Cond: (name = 'PostgreSQL-000147'::text) (4 rows) select * from test where name = 'PostgreSQL-000147'; name --- PostgreSQL-000147 (1 row) -- Still visible tuples (name = HIKEY) explain select * from test where name = 'PostgreSQL-000146'; QUERY PLAN Bitmap Heap Scan on test (cost=9.14..173.19 rows=114 width=32) Recheck Cond: (name = 'PostgreSQL-000146'::text) -> Bitmap Index Scan on id_test (cost=0.00..9.11 rows=114 width=0) Index Cond: (name = 'PostgreSQL-000146'::text) (4 rows) select * from test where name = 'PostgreSQL-000146'; name --- PostgreSQL-000146 (1 row) -- They are vislble when no indexes are used explain select * from test where name like '%PostgreSQL-000146'; QUERY PLAN --- Seq Scan on test (cost=0.00..458.93 rows=1 width=32) Filter: (name ~~ '%PostgreSQL-000146'::text) (2 rows) select * from test where name like '%PostgreSQL-000146'; name --- PostgreSQL-000146 (1 row) ### # Server crashed ### pg_ctl: another server might be running; trying to start server anyway waiting for server to start... done server started magic | version | root | level | fastroot | fastlevel +-+--+---+--+--- 340322 | 2 | 231 | 2 | 231 | 2 (1 row) itemoffset | ctid | itemlen | nulls | vars | data
