[BUGS] server stopped running abnormally
Things were crusing along just fine as I can tell, and then all the postgresql processes terminated. This is all I see in the logs: LOG: autovacuum: processing database "dew" LOG: autovacuum: processing database "postgres" LOG: autovacuum: processing database "template1" LOG: autovacuum: processing database "dew" LOG: autovacuum: processing database "postgres" LOG: autovacuum: processing database "template1" LOG: autovacuum: processing database "dew" LOG: received immediate shutdown request WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process [repeated many times to end of log] log output after restarting server: LOG: database system was interrupted at 2006-07-27 11:20:08 EDT LOG: checkpoint record is at 4/91F3A2CC LOG: redo record is at 4/91F3A2CC; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 520454; next OID: 1718140 LOG: next MultiXactId: 3; next MultiXactOffset: 6 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 4/91F3A310 LOG: record with zero length at 4/91F3A338 LOG: redo done at 4/91F3A310 LOG: database system is ready LOG: transaction ID wrap limit is 1074258945, limited by database "postgres" LOG: autovacuum: processing database "postgres" LOG: autovacuum: processing database "dew" LOG: autovacuum: processing database "postgres" LOG: autovacuum: processing database "dew" I have a nightly cron job that runs "vacuumdb --all --full --analyze --quiet" which failed a few days ago with this: vacuumdb: vacuuming of database "dew" failed: ERROR: buffer 126 is not owned by resource owner TopTransaction PANIC: cannot abort transaction 478973, it was already committed server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. I didn't do anything about it, and the job ran successfully the next night. Some system information: xserve:~ pfrost$ pg_config BINDIR = /usr/local/pgsql/bin DOCDIR = /usr/local/pgsql/doc INCLUDEDIR = /usr/local/pgsql/include PKGINCLUDEDIR = /usr/local/pgsql/include INCLUDEDIR-SERVER = /usr/local/pgsql/include/server LIBDIR = /usr/local/pgsql/lib PKGLIBDIR = /usr/local/pgsql/lib LOCALEDIR = MANDIR = /usr/local/pgsql/man SHAREDIR = /usr/local/pgsql/share SYSCONFDIR = /usr/local/pgsql/etc PGXS = /usr/local/pgsql/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--with-python' '--with-openssl' CC = gcc -no-cpp-precomp CPPFLAGS = CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing CFLAGS_SL = LDFLAGS = LDFLAGS_SL = LIBS = -lpgport -lssl -lcrypto -lz -lreadline -lresolv -ldl -lm VERSION = PostgreSQL 8.1.4 xserve:~ pfrost$ uname -a Darwin xserve..*** 8.6.0 Darwin Kernel Version 8.6.0: Tue Mar 7 16:58:48 PST 2006; root:xnu-792.6.70.obj~1/RELEASE_PPC Power Macintosh powerpc xserve:~ pfrost$ Not sure what can be done about this one; I don't see anything odd. I simply restarted the server about 15 minutes ago and things seem to be running normally. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2553: Outer join bug
The following bug has been logged online: Bug reference: 2553 Logged by: Steven Adams Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Red Hat Linux 3.2.3-42 Description:Outer join bug Details: Every time I use an outer join as the last one in a query and there are non-join conditions after it, those conditions are ignored. For example, if a left outer join is the last one in the query, all rows of the left table are returned, even if there is a condition that requires that table's primary key column to equal a certain value. If I add an inner self join after the outer join, the query returns only the row with the primary key value specified in the "AND" clause after the joins, as it should. The tables and query involved are as follows (with only the relevant columns shown): create table information_asset_categories( ID integer not null, internal boolean not null, constraint information_asset_categories_PK primary key(ID)); create table information_assets( ID integer not null, name varchar not null, category_ID integer, constraint information_assets_PK primary key(ID), constraint information_assets_categories_FK foreign key(category_ID) references information_asset_categories(ID)); select ia.name, iac.internal from information_assets as ia left outer join information_asset_categories as iac on(ia.category_id = iac.id) and ia.id = 21 This causes all rows in information_assets to be returned despite the "and" clause. Adding "join information_assets as ia2 on(ia.id = ia2.id)" after the outer join corrects this. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] Query returned unhandled type 16411
Hi, I have tried several installs of postgresql-8.1.3-1 and postgresql-8.1.4-1, with psqlodbc-08_02_0002.zip and PgOleDb-1.0.0.20.zip. After each uninstall I delete the entire contents of the C:\Program Files\PostgreSQL directory. I am using VB6 SP6b. My ‘Connection’ string is: Provider=PostgreSQL OLE DB Provider;Password=xx;User ID=postgres;Data Source=127.0.0.1;Location=spatial;Extended Properties="" When the following code runs against a table with a geometry column I get the following error: Query returned unhandled type 16411 The code generated the string : select the_geom from singleton limit 1; If I change the code to be: select asText(the_geom) from singleton limit 1; there is no error produced. If I run the query against any non-spatially enabled table like” select * from pg_stat_user_tables; there is no error produced. I.E. I am sure it relates to the geometry field: All my other queries work, including inserting geometry although I do use the ascii version for the geometry. Regards Roger Merritt PS: Here is the source code from my VB6 project: Set PGSConnection = New ADODB.Connection With PGSConnection Call .Open(GetPostgresConnectionString(txt_Server_IP, txt_Database, txt_PortNo, txt_LoginName, txt_Password), txt_LoginName, txt_Password) End With If PGSConnection.State <> 1 Then MsgBox "Connection String is: " + GetPostgresConnectionString(txt_Server_IP, txt_Database, txt_PortNo, txt_LoginName, txt_Password), vbOKOnly, AppName + " Database did not open?" Set PGSConnection = Nothing Exit Function End If Dim PGS_RecSet As ADODB.Recordset, RecCount As Long, Cntr As Long, PGSField As ADODB.field Set PGS_RecSet = New ADODB.Recordset With PGS_RecSet .ActiveConnection = PGSConnection .CursorLocation = adUseClient .CursorType = adOpenKeyset .LockType = adLockReadOnly .Open Source:="select the_geom from " + TableName + " limit 1;" '.Open Source:="select * from pg_stat_user_tables;" End With
Re: [BUGS] BUG #2553: Outer join bug
On Thu, 27 Jul 2006, Steven Adams wrote: > > The following bug has been logged online: > > Bug reference: 2553 > Logged by: Steven Adams > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1.4 > Operating system: Red Hat Linux 3.2.3-42 > Description:Outer join bug > Details: > > Every time I use an outer join as the last one in a query and there are > non-join conditions after it, those conditions are ignored. For example, if > a left outer join is the last one in the query, all rows of the left table > are returned, even if there is a condition that requires that table's > primary key column to equal a certain value. If I add an inner self join > after the outer join, the query returns only the row with the primary key > value specified in the "AND" clause after the joins, as it should. > > The tables and query involved are as follows (with only the relevant columns > shown): > > create table information_asset_categories( > ID integer not null, > internal boolean not null, > constraint information_asset_categories_PK primary key(ID)); > > create table information_assets( > ID integer not null, > name varchar not null, > category_ID integer, > constraint information_assets_PK primary key(ID), > constraint information_assets_categories_FK foreign key(category_ID) > references information_asset_categories(ID)); > > select ia.name, iac.internal > from information_assets as ia > left outer join information_asset_categories as iac on(ia.category_id = > iac.id) > and ia.id = 21 > > This causes all rows in information_assets to be returned despite the "and" > clause. AFAICT that's correct behavior. The ON condition in the LEFT JOIN case affects which rows are joined to actual rows on the right and which rows are extended with NULLs but does not filter the rows on left. ---(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] server stopped running abnormally
Phil Frost <[EMAIL PROTECTED]> writes: > Things were crusing along just fine as I can tell, and then all the > postgresql processes terminated. This is all I see in the logs: > LOG: received immediate shutdown request Something sent the postmaster a SIGQUIT signal. You need to look into what might have done that. > I have a nightly cron job that runs "vacuumdb --all --full --analyze > --quiet" which failed a few days ago with this: > vacuumdb: vacuuming of database "dew" failed: ERROR: buffer 126 is not owned > by resource owner TopTransaction > PANIC: cannot abort transaction 478973, it was already committed The PANIC is a known problem with VACUUM FULL: it commits its transaction before it's really done processing, and so if it gets an error after that point it's up the creek without a paddle. But I'm not sure what could have caused the "not owned by" error in the first place. Don't suppose you can reproduce the problem? regards, tom lane ---(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] BUG #2553: Outer join bug
"Steven Adams" <[EMAIL PROTECTED]> writes: > select ia.name, iac.internal > from information_assets as ia > left outer join information_asset_categories as iac on(ia.category_id = > iac.id) > and ia.id = 21 > This causes all rows in information_assets to be returned despite the "and" > clause. AFAICS that's correct behavior. I think you're confused about SQL syntax: the way you've written it, the "ia.id = 21" condition is part of the left join's ON clause, and therefore it cannot cause any rows from the left side of the join to be omitted. (Hint: those parentheses are just noise.) Perhaps you meant to write WHERE ia.id = 21? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2553: Outer join bug
On Thu, Jul 27, 2006 at 07:30:01PM +, Steven Adams wrote: > select ia.name, iac.internal > from information_assets as ia > left outer join information_asset_categories as iac on(ia.category_id = > iac.id) > and ia.id = 21 > > This causes all rows in information_assets to be returned despite the "and" > clause. Adding "join information_assets as ia2 on(ia.id = ia2.id)" after > the outer join corrects this. http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-JOIN LEFT OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table unconditionally has at least one row for each row in T1. The "and ia.id = 21" expression is part of the outer join condition that restricts rows from information_asset_categories (T2); it doesn't restrict rows from information_assets (T1). If you don't want all rows from information_assets then why are you using an outer join? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
