[BUGS] BUG #1239: Stale postmaster.pid prevents server start
The following bug has been logged online: Bug reference: 1239 Logged by: Martin Pollard Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: Win32 Description:Stale postmaster.pid prevents server start Details: A stale or invalid postmaster.pid file in the postgres data directory will prevent postgresql from starting and not return an error to the service controller. Example shown below. C:\Program Files\PostgreSQL\8.0-beta1\data>net start "postgresql database server 8.0-beta1" The PostgreSQL Database Server 8.0-beta1 service is starting. The PostgreSQL Database Server 8.0-beta1 service was started successfully. C:\Program Files\PostgreSQL\8.0-beta1\data>net stop "postgresql database server 8.0-beta1" The PostgreSQL Database Server 8.0-beta1 service is stopping. The PostgreSQL Database Server 8.0-beta1 service was stopped successfully. C:\Program Files\PostgreSQL\8.0-beta1\data>type CON > postmaster.pid 123455 fish 4433 C:\Program Files\PostgreSQL\8.0-beta1\data>net start "postgresql database server 8.0-beta1" The PostgreSQL Database Server 8.0-beta1 service is starting. The PostgreSQL Database Server 8.0-beta1 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. C:\Program Files\PostgreSQL\8.0-beta1\data>del postmaster.pid C:\Program Files\PostgreSQL\8.0-beta1\data>net start "postgresql database server 8.0-beta1" The PostgreSQL Database Server 8.0-beta1 service is starting. The PostgreSQL Database Server 8.0-beta1 service was started successfully. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] transaction block: server closed the connection unexpectedly
On Mon, Sep 06, 2004 at 04:41:32PM +1000, Koju Iijima wrote: Hi, > template1=# BEGIN; > BEGIN > template1=# CREATE TABLE FOO ( a int unique); > NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_a_key" for table "foo" > CREATE TABLE > template1=# INSERT INTO FOO VALUES ( 0 ); > INSERT 17232 1 > template1=# INSERT INTO FOO VALUES ( 0 ); > ERROR: duplicate key violates unique constraint "foo_a_key" > FATAL: block 1 of 1663/1/17230 is still referenced (private 1, global 1) The problem is that there's a pin on an index page when the smgr tries to drop its buffers. This patch corrects this problem, by having resowner cleanup before smgr. It also passes regression tests. Not sure if it's the correct solution though, but it seems the natural thing to me. Thanks for the report. I wonder how we managed to miss this. -- Alvaro Herrera () "Estoy de acuerdo contigo en que la verdad absoluta no existe... El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama) Index: src/backend/access/transam/xact.c === RCS file: /home/alvherre/cvs/pgsql-server/src/backend/access/transam/xact.c,v retrieving revision 1.185 diff -c -r1.185 xact.c *** src/backend/access/transam/xact.c 30 Aug 2004 19:00:03 - 1.185 --- src/backend/access/transam/xact.c 6 Sep 2004 12:57:25 - *** *** 1481,1494 * ordering. */ - smgrDoPendingDeletes(false); - smgrabort(); CallXactCallbacks(XACT_EVENT_ABORT, InvalidTransactionId); ResourceOwnerRelease(TopTransactionResourceOwner, RESOURCE_RELEASE_BEFORE_LOCKS, false, true); AtEOXact_Inval(false); ResourceOwnerRelease(TopTransactionResourceOwner, RESOURCE_RELEASE_LOCKS, --- 1481,1494 * ordering. */ CallXactCallbacks(XACT_EVENT_ABORT, InvalidTransactionId); ResourceOwnerRelease(TopTransactionResourceOwner, RESOURCE_RELEASE_BEFORE_LOCKS, false, true); + smgrDoPendingDeletes(false); + smgrabort(); AtEOXact_Inval(false); ResourceOwnerRelease(TopTransactionResourceOwner, RESOURCE_RELEASE_LOCKS, Index: src/test/regress/expected/transactions.out === RCS file: /home/alvherre/cvs/pgsql-server/src/test/regress/expected/transactions.out,v retrieving revision 1.8 diff -c -r1.8 transactions.out *** src/test/regress/expected/transactions.out 12 Aug 2004 19:12:21 - 1.8 --- src/test/regress/expected/transactions.out 6 Sep 2004 13:08:27 - *** *** 374,379 --- 374,387 FETCH 10 FROM c; ERROR: portal "c" cannot be run COMMIT; + -- make sure dropping an index that's being scanned works + BEGIN; + CREATE TABLE koju (a INT UNIQUE); + NOTICE: CREATE TABLE / UNIQUE will create implicit index "koju_a_key" for table "koju" + INSERT INTO koju VALUES (1); + INSERT INTO koju VALUES (1); + ERROR: duplicate key violates unique constraint "koju_a_key" + ROLLBACK; DROP TABLE foo; DROP TABLE baz; DROP TABLE barbaz; Index: src/test/regress/sql/transactions.sql === RCS file: /home/alvherre/cvs/pgsql-server/src/test/regress/sql/transactions.sql,v retrieving revision 1.8 diff -c -r1.8 transactions.sql *** src/test/regress/sql/transactions.sql 12 Aug 2004 19:12:21 - 1.8 --- src/test/regress/sql/transactions.sql 6 Sep 2004 13:05:30 - *** *** 231,236 --- 231,243 FETCH 10 FROM c; COMMIT; + -- make sure dropping an index that's being scanned works + BEGIN; + CREATE TABLE koju (a INT UNIQUE); + INSERT INTO koju VALUES (1); + INSERT INTO koju VALUES (1); + ROLLBACK; + DROP TABLE foo; DROP TABLE baz; DROP TABLE barbaz; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1240: memory leak in JDBC driver build 215
The following bug has been logged online:
Bug reference: 1240
Logged by: Roland Walter
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.3
Operating system: SuSE Linux 9.0, Windows CYGWIN, J2SDK 1.4.2_x
Description:memory leak in JDBC driver build 215
Details:
Hello!
I used the JDBC driver build 215 that was given by postgresql 7.4.5. (The
webinterface for bug reports
has not the version 7.4.5 yet.)
I wanted to execute the following statement with JDBC on a database table
named transaction with 3.945.773 rows:
SELECT * FROM transaction WHERE transaction_date >=
to_timestamp('01.01.2002', 'DD.MM.') AND transaction_date <
to_timestamp('01.01.2003', 'DD.MM.')
But I got the following error message from the JVM:
java.lang.OutOfMemoryError
Here the java-code:
stmt = con.createStatement();
log.debug("executing SQL-Stmt: " + stmtString);
rs = stmt.executeQuery(stmtString);
log.debug("getting metadata");
ResultSetMetaData rsMeta = rs.getMetaData();
And here the output I got:
328 [main] DEBUG com.mosaicag.rwa.dbutil.standard.DefaultCsvExport -
executing SQL-Stmt: SELECT * FROM transaction WHERE transaction_date >=
to_timestamp('01.01.2002', 'DD.MM.') AND transaction_date <
to_timestamp('01.01.2003', 'DD.MM.')
java.lang.OutOfMemoryError
Exception in thread "main"
As you can see, the log.debug("getting metadata"); was
never reached. A count with the same where-clause gave 387.665 rows, that
should have been iterated with a
while(rs.next()) loop. The JVM used the standard heap size, there were no
big memory allocations in the previous code.
The table definition is as follows:
create table transaction (
transaction_id bigint not null,
bc_id bigint,
recipient_iln varchar(20) not null,
transaction_date timestamp,
transaction_type varchar(20),
transaction_state varchar(20),
productive char(2),
remark varchar(255),
origin char(2),
cc_trans_id numeric(38),
constraint xpktransaction primary key (transaction_id));
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] BUG #1240: memory leak in JDBC driver build 215
328 [main] DEBUG com.mosaicag.rwa.dbutil.standard.DefaultCsvExport -
executing SQL-Stmt: SELECT * FROM transaction WHERE transaction_date >=
to_timestamp('01.01.2002', 'DD.MM.') AND transaction_date <
to_timestamp('01.01.2003', 'DD.MM.')
java.lang.OutOfMemoryError
Exception in thread "main"
Maybe the JDBC drivier tries to allocate the whole result of the query?
If so, it is not a memory leak, it's a big memory need;-)
You might try using a cursor manually (well, if it is the problem, then it
just shows that jdbc should do it by default). Something like:
DECLARE CURSOR foo FOR your-select-query...;
FETCH FORWARD 10 FROM foo;
FETCH FORWARD 10 FROM foo;
FETCH FORWARD 10 FROM foo;
...
CLOSE foo;
Hope this help.
--
Fabien Coelho - [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] error in simple sql function breaks connection
Dear bug-hunters,
With a nearly current cvs head version I have the following:
psql> SELECT VERSION();
...
PostgreSQL 8.0.0beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.4 (Debian 1:3.3.4-6sarge1)
psql> CREATE TABLE foo(id SERIAL PRIMARY KEY, data TEXT);
psql> CREATE FUNCTION add_data(TEXT) RETURNS boolean LANGUAGE SQL AS
'BEGIN; INSERT INTO foo(data) VALUES($1); COMMIT; SELECT TRUE;';
psql> SELECT add_data('hello');
FATAL: EndTransactionBlock: unexpected state BEGIN
CONTEXT: SQL function "add_data" statement 3
server closed the connection unexpectedly
in log:
Sep 6 18:09:23 sablons postgres[21271]: [4-1] FATAL: EndTransactionBlock: unexpected
state BEGIN
Sep 6 18:09:23 sablons postgres[21271]: [4-2] CONTEXT: SQL function "add_data"
statement 3
Sep 6 18:09:23 sablons postgres[21271]: [5-1] LOG: disconnection: session time:
0:02:49.02 user=coelho database=coelho host=[local] port=
Sep 6 18:09:23 sablons postgres[21271]: [5-2] CONTEXT: SQL function "add_data"
statement 3
Although the "BEGIN" might not be welcome (well, it was ok, possibly
ignored, with version 7.4), shuting down the connection does not seem to
be the appropriate action anyway.
Have a nice day,
--
Fabien.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] BUG #1239: Stale postmaster.pid prevents server start
> Operating system: Win32 > Description:Stale postmaster.pid prevents server start I believe this is fixed as of CVS tip. But thanks for the report! regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] transaction block: server closed the connection unexpectedly
Alvaro Herrera <[EMAIL PROTECTED]> writes: > The problem is that there's a pin on an index page when the smgr tries > to drop its buffers. This patch corrects this problem, by having > resowner cleanup before smgr. Good diagnosis, not very good fix. You didn't do anything about adjusting the comments to match the code, and you missed the identical bug occurring during subtransaction abort. One of the most powerful programming techniques I know is, once you've identified a bug, to ask yourself "where else might I (or others) have made this same error?". That would have led you to the subtransaction case, even if we hadn't already agreed that the order of operations during xact/subxact commit/abort should be kept the same as much as possible. Patch as-applied is attached. regards, tom lane *** src/backend/access/transam/xact.c.orig Mon Aug 30 15:00:03 2004 --- src/backend/access/transam/xact.c Mon Sep 6 13:52:42 2004 *** *** 1333,1341 * backend-wide state. */ - smgrDoPendingDeletes(true); - /* smgrcommit already done */ - CallXactCallbacks(XACT_EVENT_COMMIT, InvalidTransactionId); ResourceOwnerRelease(TopTransactionResourceOwner, --- 1333,1338 *** *** 1352,1357 --- 1349,1362 */ AtEOXact_Inval(true); + /* +* Likewise, dropping of files deleted during the transaction is best done +* after releasing relcache and buffer pins. (This is not strictly +* necessary during commit, since such pins should have been released +* already, but this ordering is definitely critical during abort.) +*/ + smgrDoPendingDeletes(true); + ResourceOwnerRelease(TopTransactionResourceOwner, RESOURCE_RELEASE_LOCKS, true, true); *** *** 1363,1368 --- 1368,1374 AtEOXact_SPI(true); AtEOXact_on_commit_actions(true, s->transactionIdData); AtEOXact_Namespace(true); + /* smgrcommit already done */ AtEOXact_Files(); pgstat_count_xact_commit(); *** *** 1481,1495 * ordering. */ - smgrDoPendingDeletes(false); - smgrabort(); - CallXactCallbacks(XACT_EVENT_ABORT, InvalidTransactionId); ResourceOwnerRelease(TopTransactionResourceOwner, RESOURCE_RELEASE_BEFORE_LOCKS, false, true); AtEOXact_Inval(false); ResourceOwnerRelease(TopTransactionResourceOwner, RESOURCE_RELEASE_LOCKS, false, true); --- 1487,1499 * ordering. */ CallXactCallbacks(XACT_EVENT_ABORT, InvalidTransactionId); ResourceOwnerRelease(TopTransactionResourceOwner, RESOURCE_RELEASE_BEFORE_LOCKS, false, true); AtEOXact_Inval(false); + smgrDoPendingDeletes(false); ResourceOwnerRelease(TopTransactionResourceOwner, RESOURCE_RELEASE_LOCKS, false, true); *** *** 1501,1506 --- 1505,1511 AtEOXact_SPI(false); AtEOXact_on_commit_actions(false, s->transactionIdData); AtEOXact_Namespace(false); + smgrabort(); AtEOXact_Files(); pgstat_count_xact_rollback(); *** *** 3014,3020 AtSubCommit_Notify(); AtEOSubXact_UpdatePasswordFile(true, s->transactionIdData, s->parent->transactionIdData); - AtSubCommit_smgr(); CallXactCallbacks(XACT_EVENT_COMMIT_SUB, s->parent->transactionIdData); --- 3019,3024 *** *** 3024,3029 --- 3028,3034 AtEOSubXact_RelationCache(true, s->transactionIdData, s->parent->transactionIdData); AtEOSubXact_Inval(true); + AtSubCommit_smgr(); ResourceOwnerRelease(s->curTransactionOwner, RESOURCE_RELEASE_LOCKS, true, false); *** *** 3109,3116 RecordSubTransactionAbort(); /* Post-abort cleanup */ - AtSubAbort_smgr(); - CallXactCallbacks(XACT_EVENT_ABORT_SUB, s->parent->transactionIdData); ResourceOwnerRelease(s->curTransactionOwner, --- 3114,3119 *** *** 3119,3124 --- 3122,3128 AtEOSubXact_RelationCache(false, s->transactionIdData,
Re: [BUGS] error in simple sql function breaks connection
Fabien COELHO <[EMAIL PROTECTED]> writes:
> psql> CREATE FUNCTION add_data(TEXT) RETURNS boolean LANGUAGE SQL AS
>'BEGIN; INSERT INTO foo(data) VALUES($1); COMMIT; SELECT TRUE;';
> psql> SELECT add_data('hello');
> FATAL: EndTransactionBlock: unexpected state BEGIN
> CONTEXT: SQL function "add_data" statement 3
> server closed the connection unexpectedly
> Although the "BEGIN" might not be welcome (well, it was ok, possibly
> ignored, with version 7.4),
I'm surprised the COMMIT didn't dump core on you in older versions.
This could never have worked, because if it actually did try to COMMIT
then the function's execution context would have been yanked out from
underneath it.
Anyway, now it says
regression=# SELECT add_data('hello');
ERROR: cannot begin/end transactions in SQL functions
CONTEXT: SQL function "add_data" statement 1
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1241: returns different result for the same result with differnt plans.
The following bug has been logged online: Bug reference: 1241 Logged by: Xiaoyu Wang Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.3 Operating system: Mandrake Linux 9.2 3.3.1-2mdk Description:returns different result for the same result with differnt plans. Details: database: TPC-H with scale factor=1.0 query: 13.sql (TPC-H) select c_count, count(*) as custdist from ( select c_custkey, count(o_orderkey) from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%special%requests' group by c_custkey ) as c_orders (c_custkey, c_count) group by c_count order by custdist desc, c_count desc; I ran the query with enable_mergejoin set to on/off, the results are different. Postgres chose Merge Left Join when enable_mergejoin is on and Hash Left Join when it is off. I dumped the results to two files, merge.data and hash.data. Here is the result when I do a diff: diff merge.data hash.data 3d2 <0 |50004 41a41 >0 |4 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1241: returns different result for the same result with differnt plans.
"PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes: > Description:returns different result for the same result with > differnt plans. > database: TPC-H with scale factor=1.0 > query: 13.sql (TPC-H) This is not *nearly* enough information to let someone else reproduce the problem. (Heck, I can't even tell which answer is wrong.) Could you boil it down to a self-contained test script? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [pgsql-hackers-win32] [BUGS] Win32 deadlock detection not working for Postgres8beta1
"Magnus Hagander" <[EMAIL PROTECTED]> writes: > [ fix broken CHECK_FOR_INTERRUPTS macro ] Applied. I see how this might change detection of statement_timeout, but I do not actually see what it's got to do with deadlock detection. In the deadlock situation the process that needs to wake up is going to be blocked on a semaphore, and so it's not going to be executing CHECK_FOR_INTERRUPTS at all. How does this fix that case? 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: [BUGS] Permissions problem with sequences
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> Given that pg_dump does put out GRANT/REVOKE operations on the sequence, >> it's certainly aware that the sequence exists. I suspect this is just a >> fixable bug (ie, suppression of output of the sequence CREATE command is >> being done at the wrong place). > I'm trying to think of the solution here. One way is to allow the ArchiveEntry to be created (ie, suppress the discrimination against owned sequences at pg_dump.c:7306) and instead discriminate at the point of emitting the CREATE or DROP from the ArchiveEntry ... but not when emitting an ALTER OWNER from it. That does seem a bit ugly though. What about emitting only an ACL ArchiveEntry instead of a full ArchiveEntry for an owned sequence? Actually ... given that pg_dump.c:7306 is suppressing the ArchiveEntry ... where the heck are the GRANT/REVOKE outputs coming from? I thought those were generated off an ArchiveEntry but apparently not. It's too late at night here for me to feel like tracking this down, but it seems an important part of the puzzle. Ultimately I think this one comes down to taste --- do what seems least ugly ... > Also, are there any other objects that are going to have this problem? > Off the top of my head it's only serial sequences. Can't think of any others ATM. If more come up, we'll need to invent some infrastructure to support it --- more fields in an ArchiveEntry, say. That is also a possible solution right now, but I'm not sure it's worth the trouble as long as there's only one use-case. Again it boils down to your design taste ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
