Re: [GENERAL] Please help! Server process was terminated by signal 11: Segmentation fault

[Adrian Klaver]

On 01/04/2016 06:07 PM, John Hong wrote:

Hello Everyone,

On 2015/12/30 17:51:01, Transaction was failed on our system.

I checked the pg_log, it states as below:

2015-12-30 17:51:01 CST [11260]: [42-1] LOG:  0: server process (PID
14272) was terminated by signal 11: Segmentation fault






On my case, Please give me the instruction in steps on how to figure out
the

reason why did this “signal 11” happen and how to solve “signal 11:
Segmentation fault” issue.


The part that confuses me is:

"
2015-12-30 17:50:52 CST [18954]: [5195-1] LOG:  0: duration: 0.111 ms

2015-12-30 17:50:52 CST [18954]: [5196-1] LOCATION:  exec_simple_query, 
postgres.c:1364


★2015-12-30 17:51:02 CST [14276]: [1-1] LOG:  0: database system was 
interrupted; last known up at 2015-12-30 17:50:21 CST★


★2015-12-30 17:51:02 CST [14276]: [31-1] LOG:  0: last completed 
transaction was at log time 2015-12-30 17:50:52.793516+08★


2015-12-30 17:51:01 CST [14270]: [1-1] LOG:  0: connection received: 
host=[local]


"

Is the above a cut and paste error or did the server really go backwards 
in time?


At any rate this:
"
2015-12-30 17:51:01 CST [14270]: [9-1] LOG:  0: statement: select 
current_timestamp,pid,current_timestamp - query_start as 
runtime,datname,usename,client_addr,query


2015-12-30 17:51:01 CST [14270]: [10-1] LOCATION:  exec_simple_query, 
postgres.c:1082


2015-12-30 17:51:01 CST [14272]: [3-1] LOG:  0: connection 
authorized: user=enterprisedb database=edb


2015-12-30 17:51:01 CST [14272]: [4-1] LOCATION:  PerformAuthentication, 
postinit.c:265


2015-12-30 17:51:01 CST [14271]: [5-1] LOG:  0: statement: select 
CURRENT_TIMESTAMP(0), client_addr, count(client_addr) from 
pg_stat_activity group by client_addr order by client_addr;


2015-12-30 17:51:01 CST [14271]: [6-1] LOCATION:  exec_simple_query, 
postgres.c:1082


2015-12-30 17:51:01 CST [14272]: [5-1] LOG:  0: statement: select now();

2015-12-30 17:51:01 CST [14272]: [6-1] LOCATION:  exec_simple_query, 
postgres.c:1082


2015-12-30 17:51:01 CST [14272]: [7-1] LOG:  0: duration: 0.997 ms

2015-12-30 17:51:01 CST [14272]: [8-1] LOCATION:  exec_simple_query, 
postgres.c:1364


2015-12-30 17:51:01 CST [14272]: [9-1] LOG:  0: statement: SELECT 
bl.pid AS blocked_pid,


"

which includes the statement that was at fault:

"★2015-12-30 17:51:01 CST [11260]: [42-1] LOG:  0: server process 
(PID 14272) was terminated by signal 11: Segmentation fault★


★2015-12-30 17:51:01 CST [11260]: [43-1] DETAIL:  Failed process was 
running: SELECT bl.pid AS blocked_pid,★

"

looks like something coming from a monitoring/logging process, if that 
helps.




Thank you in advance.

John







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help! Server process was terminated by signal 11: Segmentation fault

[Michael Paquier]

On Tue, Jan 5, 2016 at 12:36 PM, John R Pierce  wrote:
> On 1/4/2016 6:07 PM, John Hong wrote:
>
> EnterpriseDB 9.4.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
>
>
>
> you probably should contact EnterpriseDB technical support.   This email
> list is for the community open source version of PostgreSQL.

If you actually come up with a self-contained test case that fails
with the community version, then here would be fine.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help! Server process was terminated by signal 11: Segmentation fault

[John R Pierce]

On 1/4/2016 6:07 PM, John Hong wrote:
EnterpriseDB 9.4.1.3 on x86_64-unknown-linux-gnu, compiled by gcc 
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit



you probably should contact EnterpriseDB technical support.   This email 
list is for the community open source version of PostgreSQL.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Please help me regarding the WITH RECURSIVE query

[BladeOfLight16]

On Mon, Aug 26, 2013 at 3:17 AM, gajendra s v  wrote:

> Please explain me why it is ?
>

A good place to start would be removing all the parts here that don't seem
to matter. Your problem seems to be with the recursive query (since that is
the part you're changing). Cut off everything else and compare the results
of the recursive queries, and if you still can't figure it out, come back
here with your findings (and the isolated recursive queries). You'll be
much more likely to get responses if you narrow down the problem you're
having instead of asking people on this list to do it.

You might find this a good read: http://sscce.org/. And it wouldn't hurt if
you could create a SQL Fiddle  that demonstrates
your problem; the simpler and more trimmed down the better.

Re: [GENERAL] Please help

[coutinhoviola]

Thank you Sr., 

I'll try to see who can help me. I can't access postgres server and create data 
base without  stack builder application installed.

Thank you again for your support 
Thodi Viola 


--Original Message--
From: Adrian Klaver
To: Thodi Viola
Cc: pgsql-general@postgresql.org
Cc: thodi.vi...@hotmail.com
Subject: Re: [GENERAL] Please help
Sent: Aug 13, 2013 10:00 PM

On 08/13/2013 10:29 AM, Thodi Viola wrote:
> The error mentioned is  template1: FATAL:  password authentication failed for user "postgres">
> orcan you see the attachment.

Well either no password or the wrong password supplied for the postgres 
user. I do not use StackBuilder, so someone else will need to help out 
with the proper place/method to supply the password.

>
> Thodi Viola

-- 
Adrian Klaver
adrian.kla...@gmail.com

Sent from my BlackBerry® smartphone
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help

[Adrian Klaver]

On 08/14/2013 05:50 AM, coutinhovi...@gmail.com wrote:

Thank you Sr.,

I'll try to see who can help me. I can't access postgres server and create data 
base without  stack builder application installed.


You might want to try the EnterpriseDB forums:

http://forums.enterprisedb.com/forums/list.page



Thank you again for your support
Thodi Viola





--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help

[Adrian Klaver]

On 08/13/2013 10:29 AM, Thodi Viola wrote:

The error mentioned is 
orcan you see the attachment.


Well either no password or the wrong password supplied for the postgres 
user. I do not use StackBuilder, so someone else will need to help out 
with the proper place/method to supply the password.




Thodi Viola


--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help

[Thodi Viola]

The error mentioned is  orcan you see
the attachment.

Thodi Viola



2013/8/13 Adrian Klaver 

> On 08/13/2013 08:57 AM, Thodi Viola wrote:
>
>> Dear Srs
>>
>> Could you please help me to fix the error shown in attachment, i'm use
>> windows 7.
>>
>>
>>
> What is in the error log mentioned in the pop up?
>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>



-- 
---
Melhores cumprimentos / Best regards

*Thodi Viola*
Geólogo de Pesquisa / Exploration Geologist
Membro / Member of: AGMM
**
*Contactos:*
+258 821 217 160 / 845 237 792
*Skype*: thodi.viola
Maputo / Tete - Moçambique
"descansar não significa parar de trabalhar mas mudar de actividade...tente
e veja o resultado / rest does not mean stop working but changing activity
... try and see the result"
createdb: could not connect to database template1: FATAL:  password 
authentication failed for user "postgres"

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help

[Adrian Klaver]

On 08/13/2013 08:57 AM, Thodi Viola wrote:

Dear Srs

Could you please help me to fix the error shown in attachment, i'm use
windows 7.




What is in the error log mentioned in the pop up?


--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me to take a look of the erros in my functions. Thanks.

[Bartosz Dmytrak]

2012/4/3 Alban Hertroys 

> On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:
>
> > That is right, there is no sense to use cursors here...
>
> I think you're wrong there: The OP is querying a system table for tables
> of a certain name, which I expect can contain multiple rows for tables of
> the same name in different schema's.
>
> Of course, that may not be applicable to the her situation.
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
hmm...

 if tablename variable contains schema name this function will never work,
because:
quote_ident ('aa.aaa') gives "aa.aaa" what is not proper fully qualified
name,  should be "aa"."aaa".
So, my assumption is tablename variable contains only table name. If this
is only table name, without schema name then postgre will try to truncate
table only in schema where this table could be found (according to
search_path parameter). It is not possible to have more then one table with
the same name in the same schema.

Grace wrote:
*"I tried to create function to truncate table"*
this drives me to think about one table not all of them in database, but
cursor statement could be misleading.

I think it is not a good idea to truncate all tables with the same name in
all schemas (maybe this is Grace's intention - don't know).

BTW, *tablename *column of *pg_catalog.pg_tables* view contains only table
name without schema, so this statement will NOT truncate all tables with
the same name accross all schemas because of search_path.
http://www.postgresql.org/docs/9.1/static/view-pg-tables.html



Regards,
Bartek

Re: [GENERAL] Please help me to take a look of the erros in my functions. Thanks.

[Alban Hertroys]

On 2 Apr 2012, at 22:02, leaf_yxj wrote:

> CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void AS
> $$ 
> DECLARE 
> stmt RECORD; 
> statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables; 
> BEGIN 
> IF stmt IN statements then 
> EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || '

I think you meant to use a FOR LOOP there, not IF. IF does not know to fetch a 
record from a CURSOR (hmm... should it perhaps?).

http://www.postgresql.org/docs/9.1/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP

> CASCADE;'; 
> ELSE 
> The tablename doesn't exist.doesn 
> END IF ; 
> END;   
> $$ LANGUAGE 'plpgsql' security definer; 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me to take a look of the erros in my functions. Thanks.

[Alban Hertroys]

On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:

> That is right, there is no sense to use cursors here...

I think you're wrong there: The OP is querying a system table for tables of a 
certain name, which I expect can contain multiple rows for tables of the same 
name in different schema's.

Of course, that may not be applicable to the her situation.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me to take a look of the erros in my functions. Thanks.

[Bartosz Dmytrak]

That is right, there is no sense to use cursors here...

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
 EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
 WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

this works fine for me.
Regards,
Bartek

2012/4/2 Pavel Stehule 

> Hello
>
> " IF stmt IN statements then " is nonsense.
>
> use trapping exceptions instead
>
> BEGIN
>  EXECUTE 'TRUNCATE TABLE ' || quote_ident(_tablename) || ' CASCADE';
> EXCEPTION WHEN  undefined_table THEN
>  RAISE EXCEPTION 'your own exception, when you like';
> END;
>
> Regards
>
> Pavel
>
>
> 2012/4/2 leaf_yxj :
> > I tried to create function to truncate table
> > 1) when the user call the function just specify the tablename
> > 2) the user can use the function owner privilege to execute the function.
> >
> > But I got the errors as follows. Please help me to take a look.
> >
> > Thanks.
> >
> > Regards.
> >
> > Grace
> > -- function :
> >
> > CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void
> AS
> > $$
> >  DECLARE
> > stmt RECORD;
> > statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables;
> >  BEGIN
> > IF stmt IN statements then
> > EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || '
> > CASCADE;';
> >  ELSE
> > The tablename doesn't exist.doesn
> > END IF ;
> >  END;
> >  $$ LANGUAGE 'plpgsql' security definer;
> >
> >  errors.
> > ERROR:  syntax error at or near "$2"
> > LINE 1: SELECT   $1  IN  $2
> > ^
> > QUERY:  SELECT   $1  IN  $2
> > CONTEXT:  SQL statement in PL/PgSQL function "truncate_t" near line 6
> >
> > --
> > View this message in context:
> http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5613507.html
> > Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Please help me to take a look of the erros in my functions. Thanks.

[Pavel Stehule]

Hello

" IF stmt IN statements then " is nonsense.

use trapping exceptions instead

BEGIN
  EXECUTE 'TRUNCATE TABLE ' || quote_ident(_tablename) || ' CASCADE';
EXCEPTION WHEN  undefined_table THEN
  RAISE EXCEPTION 'your own exception, when you like';
END;

Regards

Pavel


2012/4/2 leaf_yxj :
> I tried to create function to truncate table
> 1) when the user call the function just specify the tablename
> 2) the user can use the function owner privilege to execute the function.
>
> But I got the errors as follows. Please help me to take a look.
>
> Thanks.
>
> Regards.
>
> Grace
> -- function :
>
> CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void AS
> $$
>  DECLARE
>     stmt RECORD;
>     statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables;
>  BEGIN
>     IF stmt IN statements then
>         EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || '
> CASCADE;';
>  ELSE
>     The tablename doesn't exist.doesn
>     END IF ;
>  END;
>  $$ LANGUAGE 'plpgsql' security definer;
>
>  errors.
> ERROR:  syntax error at or near "$2"
> LINE 1: SELECT   $1  IN  $2
>                         ^
> QUERY:  SELECT   $1  IN  $2
> CONTEXT:  SQL statement in PL/PgSQL function "truncate_t" near line 6
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5613507.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] please help understand freeing shared buffers

[Filip Rembiałkowski]

2012/1/6 Tom Lane :
> =?UTF-8?Q?Filip_Rembia=C5=82kowski?=  writes:
>> Among following queries, only THREE runs fast enough for me.
>> I can't understand the logic behind this.
>
> I'm not sure why you'd expect real answers when you haven't shown us
> what the query is doing,

it is an UDF, encapsulating a single SELECT where a=$1 and b=$2 and c=$3

> but my first thought is that the discrepancy
> comes from additional buffer touches in the first execution of a query
> in a given backend; which is not exactly surprising because that backend
> has to load up its system catalog caches.  IOW, the excess touches
> represent accesses to system catalogs not user tables.
>
> In general, if you're annoyed by query execution times measured in
> milliseconds, you'd be best advised not to start a fresh connection
> for each one.  A new connection not only involves a process launch
> but a fair amount of loading of local caches, and a large part of
> the latter work happens during the first few queries it processes.


thank you, that explains a lot.

I misinterpreted the number of buffer hits as true buffer reads.

sure, using persistent connections is what I will do (we have pgbouncer here)


Filip

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] please help understand freeing shared buffers

[Tom Lane]

=?UTF-8?Q?Filip_Rembia=C5=82kowski?=  writes:
> Among following queries, only THREE runs fast enough for me.
> I can't understand the logic behind this.

I'm not sure why you'd expect real answers when you haven't shown us
what the query is doing, but my first thought is that the discrepancy
comes from additional buffer touches in the first execution of a query
in a given backend; which is not exactly surprising because that backend
has to load up its system catalog caches.  IOW, the excess touches
represent accesses to system catalogs not user tables.

In general, if you're annoyed by query execution times measured in
milliseconds, you'd be best advised not to start a fresh connection
for each one.  A new connection not only involves a process launch
but a fair amount of loading of local caches, and a large part of
the latter work happens during the first few queries it processes.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help -- Postgres stopped responding ....

[John R Pierce]

On 02/26/11 7:04 AM, savio rodriges wrote:

Hello Team,

I am new to Postgresql. Postgresql suddenly stoped responding. Below is what I 
checked.

[root@server]$/etc/init.d/postgres status
dead but pid file exists


that probably means the server aborted for some reason without cleaning 
up.   this could happen if the system reset or power cycled without 
being shutdown properly, or if the server crashed.


as others said, check the log file.  if thats RHEL/CentOS/Fedora, odds 
are its in /var/lib/pgsql/data/pg_log/postgresql-${DATE}_${SEQUENCE}.log 
... look at the latest file.   if you're running an older RHEL/etc stock 
build of postgres, its possible its /var/lib/pgsql/server.log


to restart postgres, you'll need to delete the 'pid' file, likely 
/var/lib/pgsql/data/postmaster.pid  ...  probably a good idea to verify 
there aren't any postmaster processes still running detached,


   $ ps -FU postgres
   postgres  2707 1  0 38026  5696   1 Jan05 ?00:18:50
   /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
   postgres  3921  2707  0  3281  1060   0 Jan05 ?00:00:19
   postgres: logger process
   postgres  4524  2707  0 38058 112828  2 Jan05 ?00:01:24
   postgres: writer process
   postgres  4525  2707  0 38060  1304   1 Jan05 ?00:00:05
   postgres: wal writer process
   postgres  4526  2707  0 38353  2352   1 Jan05 ?00:55:39
   postgres: autovacuum launcher process
   postgres  4527  2707  0  3569  1648   0 Jan05 ?02:35:08
   postgres: stats collector process

(showing a normally running system)



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help -- Postgres stopped responding ....

[Raymond O'Donnell]

On 26/02/2011 16:00, savio rodriges wrote:


Please let me know how to configure "ERROR REPORTING AND LOGGING"
section of postgres.conf to get all necessary postgres information in
logfile for debuging/administration purpose.


I think you need to look at log_min_messages and log_error_verbosity 
anyway Full details here:


http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help -- Postgres stopped responding ....

[savio rodriges]

Hello Raymond,

FYI, attach is "ERROR REPORTING AND LOGGING" section from postgres.conf.

Following information is from /var/log

-bash-3.2$ cd /var/log

-bash-3.2$ ls -l mess*
-rw--- 1 root root 242 Feb 25 23:14 messages
-rw--- 1 root root 110 Feb 16 22:11 messages.1
-rw--- 1 root root 344 Feb 11 12:14 messages.2
-rw--- 1 root root 110 Jan 31 19:36 messages.3
-rw--- 1 root root 110 Jan 25 19:50 messages.4

[root@erp log]# cat messages.4
Jan 23 04:02:35 erp syslogd 1.4.1: restart.
Jan 25 19:50:38 erp auditd[2660]: Audit daemon rotating log files

[root@erp log]# cat messages
Feb 20 04:02:39 erp syslogd 1.4.1: restart.
Feb 21 01:54:24 erp auditd[2660]: Audit daemon rotating log files
Feb 22 10:00:01 erp auditd[2660]: Audit daemon rotating log files
Feb 25 23:14:29 erp auditd[2660]: Audit daemon rotating log files

Following is postgres log information,

-bash-3.2$ pwd
/var/lib/pgsql/data/pg_log

-bash-3.2$ ls -ltr
total 354000
-rw--- 1 postgres postgres 53001301 Feb 20 23:59 postgresql-Sun.log
-rw--- 1 postgres postgres 47584965 Feb 21 23:59 postgresql-Mon.log
-rw--- 1 postgres postgres 39886690 Feb 22 23:59 postgresql-Tue.log
-rw--- 1 postgres postgres 45764569 Feb 23 23:59 postgresql-Wed.log
-rw--- 1 postgres postgres 47464819 Feb 24 23:59 postgresql-Thu.log
-rw--- 1 postgres postgres 39972476 Feb 25 23:59 postgresql-Fri.log
-rw--- 1 postgres postgres 88413622 Feb 26 21:20 postgresql-Sat.log

-bash-3.2$ cat postgresql-Sat.log

LOG:  duration: 0.074 ms
LOG:  duration: 0.518 ms
LOG:  duration: 0.041 ms
LOG:  duration: 0.321 ms
LOG:  duration: 0.022 ms

As you can see, there is not much information available for debuging purpose.

Please let me know how to configure "ERROR REPORTING AND LOGGING" section of 
postgres.conf to get all necessary postgres information in logfile for 
debuging/administration purpose.

Thanks for your help.

Savio

--- On Sat, 2/26/11, Raymond O'Donnell  wrote:

> From: Raymond O'Donnell 
> Subject: Re: [GENERAL] Please help  -- Postgres stopped responding 
> To: "savio rodriges" 
> Cc: pgsql-general@postgresql.org
> Date: Saturday, February 26, 2011, 7:21 AM
> On 26/02/2011 15:04, savio rodriges
> wrote:
> > Hello Team,
> >
> > I am new to Postgresql. Postgresql suddenly stoped
> responding. Below
> > is what I checked.
> >
> > [root@server]$/etc/init.d/postgres status dead but pid
> file exists
> >
> > [root@server]$ uptime 20:33:26 up 63 days,
> 22:57,  4 users,  load
> > average: 1.73, 2.26, 2.43
> >
> > My questions are,
> >
> > 1. Which postgresql database logfile needs to checked
> for information
> > on why postgres stoped responding?
> 
> The latest one. :-)
> 
> Seriously, check in postgresql.conf where the logs are set
> to go, and 
> look there for the most recent file.
> 
> > 2. What else besides logfile can be checked ?
> 
> What happens when you try to restart it? - Again, check the
> logs.
> 
> Maybe look in syslog to see if anything else happened at
> that time?
> 
> Ray.
> 
> -- 
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>


  #--
# ERROR REPORTING AND LOGGING
#--

# - Where to Log -

log_destination = 'stderr'  # Valid values are combinations of
# stderr, csvlog, syslog and eventlog,
# depending on platform.  csvlog
# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on  # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'# directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log'  # log file name pattern,
# can include strftime() escapes
log_truncate_on_rotation = on   # If on, an existing log file of the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
  

Re: [GENERAL] Please help -- Postgres stopped responding ....

[Raymond O'Donnell]

On 26/02/2011 15:04, savio rodriges wrote:

Hello Team,

I am new to Postgresql. Postgresql suddenly stoped responding. Below
is what I checked.

[root@server]$/etc/init.d/postgres status dead but pid file exists

[root@server]$ uptime 20:33:26 up 63 days, 22:57,  4 users,  load
average: 1.73, 2.26, 2.43

My questions are,

1. Which postgresql database logfile needs to checked for information
on why postgres stoped responding?


The latest one. :-)

Seriously, check in postgresql.conf where the logs are set to go, and 
look there for the most recent file.



2. What else besides logfile can be checked ?


What happens when you try to restart it? - Again, check the logs.

Maybe look in syslog to see if anything else happened at that time?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please Help...

[Craig Ringer]

On 04/11/10 16:58, Gavin Burrows wrote:

> I have literally been researching this for weeks! Now I feel I have
> reached a point where this has become an impossible task so I need help
> from the experts please :]

It sounds like you have Group Policy restrictions on password strength
in place.

You need to be more specific. PostgreSQL version, where you got it from,
Windows version, is it on a Windows domain or standalone, etc. If you're
installing on Windows 2008 server than there are password strength
requirements in place by default.


-- 
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please Help...

[Satoshi Nagayasu]

Hi Gavin,

On 2010/11/04, at 17:58, Gavin Burrows  wrote:
> I'm sure you have had this question many times before but I feel as though I 
> have genuinely exhausted all of my option and followed all the advise I can 
> find online. 
> 
> During installation of Postgresql at the time when it tries to create the 
> account I get the message ' user account postgress cannot be created because 
> the password is too short or not complex enough'  at which point the install 
> is terminated. This is driving me nuts as it even says this when it chooses 
> the password for you which just seems bizarre.

Which tool are you using to install PostgreSQL?
Are you trying to install from source or some binary package?

And which platform are you using? Windows?

I guess I need to know which component produces the message.

Regards,
-- 
NAGAYASU Satoshi 

Re: [GENERAL] Please Help...

[Dave Page]

On Thu, Nov 4, 2010 at 1:58 AM, Gavin Burrows  wrote:
> Hi
>
> I'm sure you have had this question many times before but I feel as though I
> have genuinely exhausted all of my option and followed all the advise I can
> find online.
>
> During installation of Postgresql at the time when it tries to create the
> account I get the message ' user account postgress cannot be created because
> the password is too short or not complex enough'  at which point the install
> is terminated. This is driving me nuts as it even says this when it chooses
> the password for you which just seems bizarre.

If it's generating the password for you, I assume you're using the
older MSI installers which are largely obsolete now?

In any case, the issue is that for some reason, Windows is refusing to
allow the user account to be created. That's normally because it's not
in compliance with a security policy on the system - almost always
related to password complexity, or age/reuse. You could try creating
the account manually in advance.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please Help...

[Andrej]

Now if you told people the OS, and the version of Postgres maybe ... ?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] please help me. I can't pg_dumg DB

[Bill Moran]

In response to peeratat tungsungnern :
> 
> My HDD has bad sector and i can change new HDD. I have a ploblem can't back 
> up database and display massage is:
> 
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  cache lookup failed for function 
> 137832813
> pg_dump: The command was: SELECT tableoid, oid, adnum, 
> pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc FROM pg_catalog.pg_attrdef 
> WHERE adrelid = '146944117'::pg_catalog.oid

When asking for help, it's generally worthwhile to provide _at_least_ the
version of PostgreSQL that you're using.

However, on a guess, I'm guessing it's a slightly older version that has
that limitation where cached names don't get cleared when the objects are
deleted.  Try restarting PostgreSQL and see if that fixes the problem.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me write a query

[Tim Landscheidt]

Nikolas Everett  wrote:

> Sorry.  Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT
> NOT NULL, timestamp TIMESTAMP);
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
> '12 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - interval
> '11 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
> '10 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - interval
> '9 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
> '8 hours');


> I want to write a query that spits out:
>  state1 | timestamp
> +
>   1 | now() - interval '12 hours'
>   2 | now() - interval '9 hours'
>   1 | now() - interval '8 hours'

> Standard grouping destroys the third row so that's out.  No grouping at all
> gives repeats of state1.  Is this what partitioning is for?

Partitioning usually means splitting data across several
tables for faster access which is probably not what you want
here.

  A simple solution would be to use LAG() and discard rows
where the current value is equal to the preceding value:

| SELECT state1, timestamp
|   FROM
| (SELECT id,
| state1,
| state2,
| LAG(state1) OVER (ORDER BY timestamp) AS prevstate1,
| timestamp FROM test) AS SubQuery
|   WHERE state1 IS DISTINCT FROM prevstate1
|   ORDER BY timestamp;

Tim


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me write a query

[Nikolas Everett]

Got it:
SELECT state1, timestamp
   FROM (SELECT state1, timestamp, lag(state1) OVER (ORDER BY timestamp)
FROM test) as foo
 WHERE state1 != lag OR lag IS NULL
ORDER BY timestamp;
 state1 | timestamp
+
  1 | now() - interval '12 hours'
  2 | now() - interval '9 hours'
  1 | now() - interval '8 hours'

Without lag IS NULL I miss the first row.

On Thu, May 27, 2010 at 11:44 AM, Nikolas Everett  wrote:

> The 10 and 11 hour interval are being skipped because I'm only interested
> in the transitions of state 1. State 1 only transitioned three times at now
> - 12, now - 9 and now - 8.
>
> The table has both transitions in it because I frequently care about them
> both together.  I just don't in this case.
>
>
> On Thu, May 27, 2010 at 12:36 PM, Justin Graf  wrote:
>
>>  On 5/27/2010 9:45 AM, Nikolas Everett wrote:
>>
>> Sorry.  Here is the setup:
>> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2
>> INT NOT NULL, timestamp TIMESTAMP);
>>  INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
>> interval '12 hours');
>>   INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() -
>> interval '11 hours');
>>   INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
>> interval '10 hours');
>>  INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() -
>> interval '9 hours');
>>  INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
>> interval '8 hours');
>>
>>
>>  I want to write a query that spits out:
>>   state1 | timestamp
>> +
>>   1 | now() - interval '12 hours'
>>   2 | now() - interval '9 hours'
>>   1 | now() - interval '8 hours'
>>
>>
>>  Have a question what makes  these values different other than the
>> timestamp???
>>
>>
>> 1, 1, now() - interval '12 hours'
>> *1, 1, now() - interval '10 hours'*
>>
>> The reason i ask, is because you show *1, 1, now() - interval '8 hours'*
>> in the desired output.   What logic keeps the 8 hour and 12 hour but not the
>> 10hour interval???
>>
>> Its kinda hard to understand why the 10hour interval is being skipped???
>>
>>
>> All legitimate Magwerks Corporation quotations are sent in a .PDF file
>> attachment with a unique ID number generated by our proprietary quotation
>> system. Quotations received via any other form of communication will not be
>> honored.
>>
>> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
>> legally privileged, confidential or other information proprietary to
>> Magwerks Corporation and is intended solely for the use of the individual to
>> whom it addresses. If the reader of this e-mail is not the intended
>> recipient or authorized agent, the reader is hereby notified that any
>> unauthorized viewing, dissemination, distribution or copying of this e-mail
>> is strictly prohibited. If you have received this e-mail in error, please
>> notify the sender by replying to this message and destroy all occurrences of
>> this e-mail immediately.
>> Thank you.
>>
>
>

Re: [GENERAL] Please help me write a query

[Nikolas Everett]

The 10 and 11 hour interval are being skipped because I'm only interested in
the transitions of state 1. State 1 only transitioned three times at now -
12, now - 9 and now - 8.

The table has both transitions in it because I frequently care about them
both together.  I just don't in this case.

On Thu, May 27, 2010 at 12:36 PM, Justin Graf  wrote:

>  On 5/27/2010 9:45 AM, Nikolas Everett wrote:
>
> Sorry.  Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2
> INT NOT NULL, timestamp TIMESTAMP);
>  INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
> interval '12 hours');
>   INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() -
> interval '11 hours');
>   INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
> interval '10 hours');
>  INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() -
> interval '9 hours');
>  INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
> interval '8 hours');
>
>
>  I want to write a query that spits out:
>   state1 | timestamp
> +
>   1 | now() - interval '12 hours'
>   2 | now() - interval '9 hours'
>   1 | now() - interval '8 hours'
>
>
>  Have a question what makes  these values different other than the
> timestamp???
>
>
> 1, 1, now() - interval '12 hours'
> *1, 1, now() - interval '10 hours'*
>
> The reason i ask, is because you show *1, 1, now() - interval '8 hours'*
> in the desired output.   What logic keeps the 8 hour and 12 hour but not the
> 10hour interval???
>
> Its kinda hard to understand why the 10hour interval is being skipped???
>
>
> All legitimate Magwerks Corporation quotations are sent in a .PDF file
> attachment with a unique ID number generated by our proprietary quotation
> system. Quotations received via any other form of communication will not be
> honored.
>
> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
> legally privileged, confidential or other information proprietary to
> Magwerks Corporation and is intended solely for the use of the individual to
> whom it addresses. If the reader of this e-mail is not the intended
> recipient or authorized agent, the reader is hereby notified that any
> unauthorized viewing, dissemination, distribution or copying of this e-mail
> is strictly prohibited. If you have received this e-mail in error, please
> notify the sender by replying to this message and destroy all occurrences of
> this e-mail immediately.
> Thank you.
>

Re: [GENERAL] Please help me write a query

[Justin Graf]

On 5/27/2010 9:45 AM, Nikolas Everett wrote:
> Sorry.  Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, 
> state2 INT NOT NULL, timestamp TIMESTAMP);
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - 
> interval '12 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - 
> interval '11 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - 
> interval '10 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - 
> interval '9 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - 
> interval '8 hours');
>
>
> I want to write a query that spits out:
>  state1 | timestamp
> +
>   1 | now() - interval '12 hours'
>   2 | now() - interval '9 hours'
>   1 | now() - interval '8 hours'
>
>
Have a question what makes  these values different other than the 
timestamp???

1, 1, now() - interval '12 hours'
*1, 1, now() - interval '10 hours'*

The reason i ask, is because you show *1, 1, now() - interval '8 
hours'*  in the desired output.   What logic keeps the 8 hour and 12 
hour but not the 10hour interval???

Its kinda hard to understand why the 10hour interval is being skipped???


All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.
<>
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me write a query

[Justin Graf]

On 5/27/2010 9:04 AM, Nikolas Everett wrote:
> Say I have a table that stores state transitions over time like so:
> id, transitionable_id, state1, state2, timestamp
>
> I'm trying to write a query that coalesces changes in state2 away to
> produce just a list of transitions of state1.  I guess it would look
> something like
>
> SELECT state1, FIRST(timestamp)
> FROM table
>
> but I have no idea how to aggregate just the repeated state1 rows.

if i understand what your  after

Select distinct transitinable_id, state1,  min(timestamp) from table 
group by  transitinable_id, state1















All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me write a query

[Nikolas Everett]

Sorry.  Here is the setup:
CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT
NOT NULL, timestamp TIMESTAMP);
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
'12 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - interval
'11 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
'10 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - interval
'9 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
'8 hours');


I want to write a query that spits out:
 state1 | timestamp
+
  1 | now() - interval '12 hours'
  2 | now() - interval '9 hours'
  1 | now() - interval '8 hours'

Standard grouping destroys the third row so that's out.  No grouping at all
gives repeats of state1.  Is this what partitioning is for?

Nik

On Thu, May 27, 2010 at 10:20 AM, Ozz Nixon  wrote:

> Lost me a bit, do you mean DISTINCT?
>
> select distinct state1, first(timestamp) from table
>
> On May 27, 2010, at 10:04 AM, Nikolas Everett wrote:
>
> > Say I have a table that stores state transitions over time like so:
> > id, transitionable_id, state1, state2, timestamp
> >
> > I'm trying to write a query that coalesces changes in state2 away to
> produce just a list of transitions of state1.  I guess it would look
> something like
> >
> > SELECT state1, FIRST(timestamp)
> > FROM table
> >
> > but I have no idea how to aggregate just the repeated state1 rows.
>
>

Re: [GENERAL] Please help me write a query

[Ozz Nixon]

Lost me a bit, do you mean DISTINCT?

select distinct state1, first(timestamp) from table

On May 27, 2010, at 10:04 AM, Nikolas Everett wrote:

> Say I have a table that stores state transitions over time like so:
> id, transitionable_id, state1, state2, timestamp
> 
> I'm trying to write a query that coalesces changes in state2 away to produce 
> just a list of transitions of state1.  I guess it would look something like
> 
> SELECT state1, FIRST(timestamp)
> FROM table
> 
> but I have no idea how to aggregate just the repeated state1 rows.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[pgsql]

Tom Lane wrote:

pgsql  writes:

Tom Lane wrote:

Um, that's not too helpful, we want to see the string it's pointing at.


Sorry about that. All statements are calling one of two pl/pgsql 
functions. While that information already helps me a lot, it'll take me 
a while to step through the code. Those functions are outer wrappers 
calling many other procedures.


Well, the stack trace you showed previously indicates that the crash is
happening in the outermost plpgsql function (ie, one called directly
from a client SQL command).  However it's certainly true that the crash
might be a consequence of something that had been done a bit earlier in
another function called by this one.


Sorry for the late reply. The only DDL performed is indeed in the outer 
function and it's a TRUNCATE, immediately followed by an INSERT SELECT 
to repopulate the truncated table.


As mentioned, I build 8.3.10 from source using --enable-debug 
--enable-cassert. I had issues with this version causing protection 
faults. Also the backtrace I got from that still doesn't include line 
numbers and arguments. So I assume I missed something important when 
doing the build?


postgres[7172] general protection rip:44abd8 rsp:7fff9195a060 error:0

Core was generated by `postgres:   (51'.
Program terminated with signal 11, Segmentation fault.
[New process 7172]
#0  0x0044abd8 in index_form_tuple ()
(gdb) bt
#0  0x0044abd8 in index_form_tuple ()
#1  0x0001 in ?? ()
#2  0x0153d968 in ?? ()
#3  0x00670634 in tuplesort_performsort ()
#4  0x in ?? ()

I decided to strip the debug options (as they somehow seem to cause 
issue on that system) and instead apply the patch you pointed out. No 
crashes since then anymore.


Thanks for your support.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[Tom Lane]

pgsql  writes:
> Tom Lane wrote:
>> Um, that's not too helpful, we want to see the string it's pointing at.

> Sorry about that. All statements are calling one of two pl/pgsql 
> functions. While that information already helps me a lot, it'll take me 
> a while to step through the code. Those functions are outer wrappers 
> calling many other procedures.

Well, the stack trace you showed previously indicates that the crash is
happening in the outermost plpgsql function (ie, one called directly
from a client SQL command).  However it's certainly true that the crash
might be a consequence of something that had been done a bit earlier in
another function called by this one.

Keep in mind that the crash very likely doesn't happen when you simply
step through the function in isolation.  If it is the same as or
related to the previously-fixed bug, it would only happen if a cache
flush event happened at just the wrong time (as a consequence of
sufficiently many concurrent catalog updates issued by other processes).

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[pgsql]

Tom Lane wrote:

pgsql  writes:

Tom Lane wrote:

Looking at debug_query_string in the core dumps would
at least show what SQL command is calling the function(s) --- and I
wouldn't be surprised if there's exactly one function involved here.



Content of debug_query_string:



core.21207
$1 = 63106368


Um, that's not too helpful, we want to see the string it's pointing at.


Sorry about that. All statements are calling one of two pl/pgsql 
functions. While that information already helps me a lot, it'll take me 
a while to step through the code. Those functions are outer wrappers 
calling many other procedures.


Thank you very much.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[Tom Lane]

pgsql  writes:
> Tom Lane wrote:
>> Looking at debug_query_string in the core dumps would
>> at least show what SQL command is calling the function(s) --- and I
>> wouldn't be surprised if there's exactly one function involved here.

> Content of debug_query_string:

> core.21207
> $1 = 63106368

Um, that's not too helpful, we want to see the string it's pointing at.
In a non-debug build you probably need to say
p (char *) debug_query_string
or something like that.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[pgsql]

Tom Lane wrote:

pgsql  writes:
Looking at debug_query_string in the core dumps would
at least show what SQL command is calling the function(s) --- and I
wouldn't be surprised if there's exactly one function involved here.


Content of debug_query_string:

core.21207
$1 = 63106368

core.20832
$1 = 292449712

core.25421
$1 = 292450320

core.23631
$1 = 29245

core.9419
$1 = 284979152

core.16801
$1 = 284978992

core.32242
$1 = 284971248

core.10776
$1 = 284978832



As per Alvaro's suggestion, installing postgresql-debuginfo would
make the stack traces a lot more useful, too.


Build from source (without the relcache patch) with debug enabled; 
waiting for the next crash.



Thank you

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[pgsql]

Alvaro Herrera wrote:

pgsql wrote:

Hi,

one of our pgsql instances recently started to segfault multiple times a
week. I tried a couple of things to pin it down to a certain query
or job but failed to find any pattern. All I can offer is some notes
and a set of similar looking back traces.


Please install the debuginfo package(s).  Have you got some external
module installed?


..for whatever reason I cannot get the postmaster provided by 
postgresql-debuginfo-8.3.10-2PGDG.el5.x86_64.rpm to run; it immediately 
causes a segfault:


ld-linux-x86-64[4286] general protection rip:3b2ca06471 rsp:7fff9a5077c0 
error:0


However I just build 8.3.10 from source with debug enabled. As soon as 
it crashes I'll post the new back trace.


Thanks!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[Craig Ringer]

On 5/05/2010 5:27 AM, Alvaro Herrera wrote:

pgsql wrote:

Hi,

one of our pgsql instances recently started to segfault multiple times a
week. I tried a couple of things to pin it down to a certain query
or job but failed to find any pattern. All I can offer is some notes
and a set of similar looking back traces.


Please install the debuginfo package(s).


See:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#Installing_External_symbols


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[Tom Lane]

pgsql  writes:
> one of our pgsql instances recently started to segfault multiple times a
> week. I tried a couple of things to pin it down to a certain query
> or job but failed to find any pattern. All I can offer is some notes
> and a set of similar looking back traces.

BTW, there is a post-8.3.10 patch for a problem that could easily match
what you are showing.  Dunno if you are up for applying patches
yourself, but if so try adding this one:
http://archives.postgresql.org/pgsql-committers/2010-04/msg00122.php

Now, the fact that that's fixing a problem introduced in January might
mean it's not what you're seeing.  However, the January patch was fixing
a different issue in the same general area.  It could be that your app
was managing to tickle both the old bug (in 8.3.7) and the new one (in
8.3.10).

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[Tom Lane]

pgsql  writes:
> one of our pgsql instances recently started to segfault multiple times a
> week. I tried a couple of things to pin it down to a certain query
> or job but failed to find any pattern. All I can offer is some notes
> and a set of similar looking back traces.

All of those traces seem to be within plpgsql functions that are doing
some sort of schema update on a table.  Sure you can't pin it down a
little better?  Looking at debug_query_string in the core dumps would
at least show what SQL command is calling the function(s) --- and I
wouldn't be surprised if there's exactly one function involved here.

As per Alvaro's suggestion, installing postgresql-debuginfo would
make the stack traces a lot more useful, too.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me debug regular segfaults on 8.3.10

[Alvaro Herrera]

pgsql wrote:
> Hi,
> 
> one of our pgsql instances recently started to segfault multiple times a
> week. I tried a couple of things to pin it down to a certain query
> or job but failed to find any pattern. All I can offer is some notes
> and a set of similar looking back traces.

Please install the debuginfo package(s).  Have you got some external
module installed?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] please help

[Bill Moran]

In response to Shu Ho :
> 
> I have postgres installed in mac and want to connect it from window XP, I get 
> error, can you please help what is needed to get connected to mac server 
> postgres ?
> 
> Server [localhost]: 10.1.1.9
> Database [postgres]: viewods
> Port [5432]: 5433
> Username [postgres]: sho
> psql: FATAL:  no pg_hba.conf entry for host "10.1.1.86", user "sho", database 
> "v
> iewods", SSL off

http://www.postgresql.org/docs/8.4/interactive/auth-pg-hba-conf.html

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] please help

[John R Pierce]

Shu Ho wrote:

Dear sir,
I need to install postgresql in unix solaris, can you please help with 
the config file set up and more detail set up instructions.


Solaris 10 comes with several versions of PostgreSQL.  if you have a 
reasonably recent release of sol10, you should have 8.3.x, and you need 
merely...


   # cat /etc/release
 Solaris 10 10/08 s10s_u6wos_07b SPARC
  Copyright 2008 Sun Microsystems, Inc.  All Rights Reserved.
   Use is subject to license terms.
   Assembled 27 October 2008

   # svcadm enable svc:/application/database/postgresql_83:default_64bit

to start it (this also autostarts it on future reboots).   initdb is run 
automatically on the first start, this can take a minute or so, use


   # svcs -xv

to check on the status (if its listed as disabled or maintenance, then 
you need to debug why it didn't start)


The configuration files and database files are created by default in 
/var/postgres/8.3/data_64
if you wish to put it somewhere else, before starting it for the first 
time, use...


   # svccfg -s svc:/application/database/postgresql_83:default_64bit 
setprop postgresql_83/data /path/to/new/data


(making sure this path is owned by and writable by user postgres (90:90)

to re-load the config files, use...

   # svcadm refresh svc:/application/database/postgresql_83:default_64bit

to restart it (stop/start), use...

   # svcadm restart svc:/application/database/postgresql_83:default_64bit

Put /usr/postgres/8.3/bin/64/ first in the path of any user who is going 
to run postgres software via ~/.profile.   link your postgres client 
programs with /usr/postgres/8.3/lib/64/libpq.so




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] please help

[Adrian Klaver]

On Saturday 02 January 2010 11:35:42 am Shu Ho wrote:
> Dear sir,
>
> I need to install postgresql in unix solaris, can you please help with the
> config file set up and more detail set up instructions.
>
>
>
> thanks
>
> Sue
>
> _
> Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
> http://clk.atdmt.com/GBL/go/171222985/direct/01/


Good places to start:
http://www.postgresql.org/docs/8.4/interactive/installation-platform-notes.html#INSTALLATION-NOTES-SOLARIS
http://wiki.postgresql.org/wiki/Detailed_installation_guides#Solaris

-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] please help

[Raymond O'Donnell]

On 02/01/2010 19:35, Shu Ho wrote:
> Dear sir,
> I need to install postgresql in unix solaris, can you please help with
> the config file set up and more detail set up instructions.

The first place to start is with the documentation - there are very
detailed instructions on installation and the configuration options.

  http://www.postgresql.org/docs/8.4/interactive/installation.html
  http://www.postgresql.org/docs/8.4/interactive/runtime-config.html

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] please help

[Rilindo Foster]

Funny thing, a workmate ask me about this same type of question. I found this 
book to be useful.

http://www.amazon.com/Beginning-Databases-PostgreSQL-Novice-Professional/dp/1590594789/ref=sr_1_2?ie=UTF8&s=books&qid=1262365492&sr=8-2

I will second Craig's recommendation, though, since  good PostgreSQL books are 
hard to find, for some reason.

 - Rilindo


On Jan 1, 2010, at 6:54 AM, Craig Ringer wrote:

> Shu Ho wrote:
>> Dear sir,
>> I work as a new DBA in postgresql, please recommend some good books,
>> website for tutorial.
> 
> The best starting point is the PostgreSQL documentation:
> 
>  http://postgresql.org/docs
> 
> Additionally, remain subscribed to this mailing list, and read the
> discussions that happen here. You will learn a LOT that way.
> 
> --
> Craig Ringer
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] please help

[Craig Ringer]

Shu Ho wrote:
> Dear sir,
> I work as a new DBA in postgresql, please recommend some good books,
> website for tutorial.

The best starting point is the PostgreSQL documentation:

  http://postgresql.org/docs

Additionally, remain subscribed to this mailing list, and read the
discussions that happen here. You will learn a LOT that way.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help

[Craig Ringer]

On Thu, 2009-07-16 at 04:47 -0700, Roseller A. Romanos wrote:
> Please help me with this. I really need your advice as to how to
> retrieve the data in my postgresql database.
> 
> I have postgresql installed in Windows XP platform five months ago.
> Just yesterday my OS bugged down and saying NTDLR is missing.  What I
> did was I re-installed my OS. When I finished my installation I found
> out that I cannot access anymore my postgresql using PGAdmin III. 
> 
> What should I do to access my database and retrieve the important
> records in it?   

Back up your data directory NOW. This is the directory containing the
PG_VERSION file, the pg_xlog directory, etc. You must back up the WHOLE
directory EXACTLY as it is.

Unfortunately I have the feeling the Windows installer wants to initdb
even if there's already data in the data directory (someone please
correct me if that's not so), so you might have to:

- rename your data directory before installing PostgreSQL.

- Install PostgreSQL. Make sure you install the same major
  version, eg install 8.3.7 if you had some 8.3.x version
  installed originally. If you are unsure, open the PG_VERSION
  file in a text editor to see what it says. (DO NOT save
  changes).

- Stop the PostgreSQL service. Use "Start -> Run -> services.msc",
  find the PostgreSQL service, and stop it.

- Rename the data directory the installer created to something else.

- Rename your old data directory to the one the installer created.

- Make sure the security settings on the old data directory will allow
  PostgreSQL to read and write to it. Setting the ownership to be the 
  same as that of the new data directory should do the trick. Use the
  "security" tab of the properties window on the folder to do this.

- Start the PostgreSQL service.

With luck you can now connect. If so, IMMEDIATELY back up your database,
and start doing so regularly...

-- 
Craig Ringer


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help

[Yuriy Rusinov]

Hello, Roseller !

>
> What should I do to access my database and retrieve the important records in
> it?

Possible you should keep files in database cluster, recreate db
cluster and put your old files into newly created cluster.

-- 
Best regards,
Sincerely yours,
Yuriy Rusinov.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please Help! Was: Multithread problem: Error in transaction processing

[Leif Jensen]

Hi,

I will have to try again. I know that I was probably not specific enough in 
my first attempt.

The systems on which we have tested and are getting the error:
- PostgreSQL 8.2.4 (also tried 8.2.6 without db upgrade) configured with 
'enable_thread_safety'.
- Linux Slackware 10.2.0 both kernel 2.4.31 and kernel 2.6.15, and Linux 
Slackware 9.1.0 with kernel 2.4.14.

The client (our daemon) has both been compiled based on 8.2.4 and 8.2.6 but 
seems not to make any difference.

Our daemon creates a couple of threads doing database access on each their 
(named) connection, where one of these threads will receive UDP packages rather 
often with data to the database Therefore we open the database at the thread 
start and keep it open all through the execution time (which can be months). 
When we get a package it results in a database update (1 row updated for each 
package), and here we often gets the -401 error. Another thread is a 'timer' 
thread running every 30 seconds checking the database for certain changes (read 
only). This also gets the error once in a while. I have not made any statistics 
about how often, but it's more like in 5% of the attempts than 0.1%.

In which situations is it possible to get this error? I have of course 
looked in the documentation, but it is not very specific, just that you can get 
it during Start Transaction (which is where we see it), Rollback, or Commit. 
What can be the basic cause of getting the error? How do I dig into getting it 
resolved? Can someone please point me to some 
documentation/description/examples on how to access the database in multiple 
threads. (We have of course looked in the PostgreSQL manual and searched the 
net. That is why we are using one connection per thread.)

As a workaround we close the database and reopen it when we get this error. 
Is this a normal/bug situation ? 

Any suggestions will be most welcome,

 Leif



- Original Message -
From: "Leif Jensen" <[EMAIL PROTECTED]>
To: "Leif Jensen" <[EMAIL PROTECTED]>
Sent: Friday, March 14, 2008 9:44:27 AM GMT +01:00 Amsterdam / Berlin / Bern / 
Rome / Stockholm / Vienna
Subject: [GENERAL] Multithread problem: Error in transaction processing


Hi Guys,

We have a system with a multithreaded daemon running on Linux using
PostgreSQL 8.2.4 Embedded SQL in C. Most of the threads are doing database
access and they have each their own conection which is opened when the
thread is created (as one of the first things within the thread).

We pretty often gets the error:

  -401:08007 Error in transaction processing in line 200

which is where there is an EXEC SQL AT :xxx START TRANSACTION ...

   I have checked and re-checked the code for non- coomit/rollback and
added lots of test output to verify that every transaction is either
committed or rolled back. I have also made sure that there is no
cross-thread use of connection, especially looking at the test output.

Any ideas ?

  Leif


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please Help ! Error with Access 2003

[Bill Moran]

In response to hengky liwandouw <[EMAIL PROTECTED]>:

> Dear all,
>
>   I have another problem with Access 2003 as front end with Postgre 8.1, ODBC 
> version 8.02.02 Unicode.
>
>   I just upgrade the Point of Sales Program to Access 2003 MDE, all station 
> running XP Home wth Access 2003 on P4 3.0, 512MB Memory, 40GB HDD. Server run 
> Windows Server 2003 on P4 3.0, 1GB Memory, 72GB UWSCSI HDD.
>
>   Sometimes In POS Application, while user input sales transaction, this 
> error message window appear :
>
>   "The information you were working might be lost. microsoft office Access 
> can by you compact and repair."
>
>   with option to send or don't send this error report to Microsoft.
>
>   What caused this error ? Please help !!! :(

If you actually believe that this problem _is_ with Postgres, grab the error
message in the Postgres server logs that corresponds with the MS Access error.

Based on that error, MS Access really has no idea what's going on.

-- 
Bill Moran
Collaborative Fusion Inc.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Please Help ! Error with Access 2003

[Magnus Hagander]

On Thu, Mar 01, 2007 at 02:31:12AM -0800, hengky liwandouw wrote:
> Dear all,
>
>   I have another problem with Access 2003 as front end with Postgre 8.1, ODBC 
> version 8.02.02 Unicode.
>
>   I just upgrade the Point of Sales Program to Access 2003 MDE, all station 
> running XP Home wth Access 2003 on P4 3.0, 512MB Memory, 40GB HDD. Server run 
> Windows Server 2003 on P4 3.0, 1GB Memory, 72GB UWSCSI HDD.
>
>   Sometimes In POS Application, while user input sales transaction, this 
> error message window appear :
>
>   "The information you were working might be lost. microsoft office Access 
> can by you compact and repair."
>
>   with option to send or don't send this error report to Microsoft.
>
>   What caused this error ? Please help !!! :(

This isa problem in MS Access, and has nothing to do with PostgreSQL.
You will have to post your question in a MS Access forum.

(BTW, it's PostgreSQL or Postgres, not Postgre)

//Magnus

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Please help.-- unable to load xxid.so

[Ashish Karalkar]

I am using Redhat Linux 4
Now the problem is solved.I installed Postgres 8.1.5
from source code previously it was installed using rpm
on slave machine.

but then i am having one problem :
I am left with following details
I am having two machine

1)Master machine :
Redhat linux
PostgreSQL 8.1.5 -- installed using RPM
Slony 1.2.1---installed using Source code

1)Slave machine :
Redhat linux
PostgreSQL 8.1.5 -- installed using source code
Slony 1.2.1---installed using Source code.

Its running fine now. but will there be any problem in
future  as the master and slave PostgreSQL server are
installed using diffrent pacakage i.e. master with RPM
and slave with  source code?

I know this is not the solution but for time being its
working fine.

also could you please suggest me if I installed
PostgreSQL with RPM on redhat Linux then which object
was that xxid.so (failed to map segemnt from shared
object -permission denied)function was refering ?

with regards
Ashish Karalkar



--- Chris <[EMAIL PROTECTED]> wrote:

> Ashish Karalkar wrote:
> > Thanks Chris ,
> > I ahve checked the permissions on the xxid.so file
> and
> > it ids having all permission i.e. 0777
> > I think its not getting the permission from the
> object
> > which xxid.so is refering, any idea which object
> it is
> > refering
> 
> Are you running SeLinux by any chance? That could be
> an issue..
> 
> or maybe try:
> 
> ldd xxid.so
> 
> to see what xxid.so is trying to use.
> 
> -- 
> Postgresql & php tutorials
> http://www.designmagick.com/
> 



 

Want to start your own business?
Learn how on Yahoo! Small Business.
http://smallbusiness.yahoo.com/r-index

---(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: [GENERAL] Please help.-- unable to load xxid.so

[Chris]

Ashish Karalkar wrote:

Thanks Chris ,
I ahve checked the permissions on the xxid.so file and
it ids having all permission i.e. 0777
I think its not getting the permission from the object
which xxid.so is refering, any idea which object it is
refering


Are you running SeLinux by any chance? That could be an issue..

or maybe try:

ldd xxid.so

to see what xxid.so is trying to use.

--
Postgresql & php tutorials
http://www.designmagick.com/

---(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: [GENERAL] Please help.-- unable to load xxid.so

[Ashish Karalkar]

Thanks Chris ,
I ahve checked the permissions on the xxid.so file and
it ids having all permission i.e. 0777
I think its not getting the permission from the object
which xxid.so is refering, any idea which object it is
refering

With Regards
Ashish Karalkar

--- Chris <[EMAIL PROTECTED]> wrote:

> Ashish Karalkar wrote:
> > I am having PostgreSQL 8.1.5 binary loaded on
> linux 
> > machine.
> > I am using Slony 1.2.1 for replication
> database.Bothe
> > master and slave will be on localhost, while
> > configuring the database I am getting following
> error
> > massage.
> >  could not load library "/usr/lib/pgsql/xxid.so":
> > /usr/lib/pgsql/xxid.so: failed to map segment from
> > shared object: Permission denied
> > 
> > Function xxid.so is there in the /usr/lib/pgsql
> > directory stil its unable to load.
> > 
> > Can any one please help me which shared object it
> is
> > refering to...
> 
> Check permissions on the file. The postgres user
> will need to be able to 
> read it (at least) - not sure whether it needs to
> own the file but it 
> can't hurt..
> 
> -- 
> Postgresql & php tutorials
> http://www.designmagick.com/
> 



 

Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Please help.-- unable to load xxid.so

[Chris]

Ashish Karalkar wrote:
I am having PostgreSQL 8.1.5 binary loaded on linux 
machine.

I am using Slony 1.2.1 for replication database.Bothe
master and slave will be on localhost, while
configuring the database I am getting following error
massage.
 could not load library "/usr/lib/pgsql/xxid.so":
/usr/lib/pgsql/xxid.so: failed to map segment from
shared object: Permission denied

Function xxid.so is there in the /usr/lib/pgsql
directory stil its unable to load.

Can any one please help me which shared object it is
refering to...


Check permissions on the file. The postgres user will need to be able to 
read it (at least) - not sure whether it needs to own the file but it 
can't hurt..


--
Postgresql & php tutorials
http://www.designmagick.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: [GENERAL] Please, help! About database cluster and adding to

[Devrim GUNDUZ]

Hi,

On Wed, 2006-01-25 at 12:40 +0300, Sergey Karin wrote:
> We create database cluster (via initdb) on some disk. Then we create
> and use a database that can be very large. In some moment the database
> occupy all disk space allicated to cluster. 
> The question is: are there any abilities in PostgreSQL to use for
> created cluster additional disk space in new other disk? Similar
> ability exist in Informix...

You should either use LVM or tablespaces for this.

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Please, help! About database cluster and adding to

[Richard Huxton]

Sergey Karin wrote:

Hi, List!

Lets suppose the next situation...

We create database cluster (via initdb) on some disk. Then we create and use
a database that can be very large. In some moment the database occupy all
disk space allicated to cluster.
The question is: are there any abilities in PostgreSQL to use for created
cluster additional disk space in new other disk? Similar ability exist in
Informix...


Well, there are various filesystem add-ons that allow you to have 
virtual partitions spread over one or more physical disks. I'm guessing 
you're not using any of those.


You can move the WAL to a different disk and use table-spaces to 
relocate tables and indexes. See the manuals for tablespace setup and 
check the mailing-list archives for how to do it the hard way with 
symbolic links if you're running an old version of PG.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Please, help! About database cluster and adding to it additional disk space

[Martijn van Oosterhout]

On Wed, Jan 25, 2006 at 12:40:36PM +0300, Sergey Karin wrote:
> Hi, List!
> 
> Lets suppose the next situation...
> 
> We create database cluster (via initdb) on some disk. Then we create and use
> a database that can be very large. In some moment the database occupy all
> disk space allicated to cluster.
> The question is: are there any abilities in PostgreSQL to use for created
> cluster additional disk space in new other disk? Similar ability exist in
> Informix...

You didn't say which version you are running, but recent versions have
tablespaces which do what you want. Alternativly, if you're running LVM
somewhere, you could make the disk postgres is no bigger...

Have a nice day,
-- 
Martijn van Oosterhout  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.


signature.asc
Description: Digital signature

Re: [GENERAL] Please HELP - URGENT - transaction wraparound error

[Martijn van Oosterhout]

On Mon, Oct 31, 2005 at 05:27:15PM -, John Sidney-Woollett wrote:
> > Wonder if it would be a good idea for the error messages to identify which
> > databases might have lost data.
> >
> > However if you have a fair number of databases involved you might get a
> > fair number of log messages. Still, I think I wouldn't mind 100 lines in
> > the logs if I had 100 databases at risk...

FWIW, the 8.1beta has the following code:

ereport(WARNING,
   (errmsg("database \"%s\" must be vacuumed within %u 
transactions",
   NameStr(oldest_datname),
   (MaxTransactionId >> 1) - age),
errhint("To avoid a database shutdown, execute a 
full-database VACUUM in \"%s\".",
NameStr(oldest_datname;


Should be enough I think...

-- 
Martijn van Oosterhout  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.


pgpgiw2fWqXN6.pgp
Description: PGP signature

Re: [GENERAL] Please HELP - URGENT - transaction wraparound error

[John Sidney-Woollett]

Lincoln Yeoh said:
> At 07:48 PM 10/30/2005 +, John Sidney-Woollett wrote:
>
>>"Panic" - that's my middle name. ;)
>>
>>Had I known how to identify the database at fault, and that it would have
>>had no effect on the other databases, then I would have handled this
>>episode differently.
>
> Wonder if it would be a good idea for the error messages to identify which
> databases might have lost data.
>
> However if you have a fair number of databases involved you might get a
> fair number of log messages. Still, I think I wouldn't mind 100 lines in
> the logs if I had 100 databases at risk...
>

Agreed!

John


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Please HELP - URGENT - transaction wraparound

[Lincoln Yeoh]

At 07:48 PM 10/30/2005 +, John Sidney-Woollett wrote:


"Panic" - that's my middle name. ;)

Had I known how to identify the database at fault, and that it would have 
had no effect on the other databases, then I would have handled this 
episode differently.


Wonder if it would be a good idea for the error messages to identify which 
databases might have lost data.


However if you have a fair number of databases involved you might get a 
fair number of log messages. Still, I think I wouldn't mind 100 lines in 
the logs if I had 100 databases at risk...


:)

Link.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Please HELP - URGENT - transaction wraparound error

[John Sidney-Woollett]

"Panic" - that's my middle name. ;)

Had I known how to identify the database at fault, and that it would 
have had no effect on the other databases, then I would have handled 
this episode differently.


In the event, things seem to be OK. Our old slave db is now acting as 
master and the old master rebuilt as the new slave ... courtesy of slon.


I'd like to move to 8.1 but I'm waiting for a quiet period when there's 
less development/fire fighting so that I can test all the java 
components of our webapp and then manage the upgrade properly.


Maybe suppressing other vacuums once a month, and running the "vacuumdb 
-a" option instead wouldn't be a bad idea...


Many thanks for all your support and advice - you've been great help 
(and comfort).


John


Tom Lane wrote:

John Sidney-Woollett <[EMAIL PROTECTED]> writes:

Just out of curiousity would the wraparound error (for mail_lxtreme) 
actually have affected data in bp_live?
Could I just have deleted mail_lxtreme and then continued to use bp_live 
as though nothing had happened?



No, and yes, which is why panicking was not warranted ;-)

Martijn's advice to be using "vacuumdb -a" every so often is well given,
though.

You could also consider switching over to autovacuum, particularly as of
8.1.  (I'm not sure how much I trust the contrib version that exists in
8.0, and 7.4's is definitely pretty buggy, but I believe 8.1's can be
relied on to prevent this sort of thing.)

regards, tom lane


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Please HELP - URGENT - transaction wraparound error

[John Sidney-Woollett]

Martijn

Thanks for the answers/thoughts...

Vacuumuming the databases hammers the server so the vacuums are spread 
out at different times during the night/morning. Plus template1 is 
vacuumed once a week.


I guess I was unlucky to have missed the vacuum on that unused database 
(due to my misunderstanding), and not to have been scanning the 
serverlog more frequently (if at all recently!).


My solution is to create a nagios script that scans entries in serverlog 
loking for WARN or ERROR messages in the past xx minutes.


With this in place, I would have caught this error weeks ago before it 
bit me in the ass!


Stressful day, but learnt a lot...

Thanks for everyone for their input - great product and great support!

John

Martijn van Oosterhout wrote:

On Sun, Oct 30, 2005 at 06:41:45PM +, John Sidney-Woollett wrote:

Hmm. I'm pretty sure that database mail_lxtreme was unused (no 
connections/activity) - I didn't think that it would need to be vacuumed 
at all...



A database that is never used still needs to be vacuumed. The only
exception is if you VACUUM FREEZE which puts the entire database in a
frozen state which will never need vacuuming. This is how template0 is
configured. Ofcourse, once you make changes...


Just out of curiousity would the wraparound error (for mail_lxtreme) 
actually have affected data in bp_live?



I doubt it but (thinking shared tables) I'll have to defer to someone
more knowledgable.


Could I just have deleted mail_lxtreme and then continued to use bp_live 
as though nothing had happened?


Or had database bp_live already been damaged by the wraparound?



Everything would probably have been fine.

BTW, I would have thought this message would have been appearing the
last billion transactions or so, didn't anyone notice?

To solve this forever, setup a cronjob for once a month:

vacuumdb -a

This will vacuum every database, even if you don't know the names or
where they came from. AIUI when you vacuum a database whose
transactions are over billion transactions old it automatically puts it
in "frozen" state. If someone had happened to run "vacuumdb -a" anytime
in the last few months, you might never have noticed the wraparound...

Hope this helps,


---(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: [GENERAL] Please HELP - URGENT - transaction wraparound error

[Tom Lane]

John Sidney-Woollett <[EMAIL PROTECTED]> writes:
> Just out of curiousity would the wraparound error (for mail_lxtreme) 
> actually have affected data in bp_live?
> Could I just have deleted mail_lxtreme and then continued to use bp_live 
> as though nothing had happened?

No, and yes, which is why panicking was not warranted ;-)

Martijn's advice to be using "vacuumdb -a" every so often is well given,
though.

You could also consider switching over to autovacuum, particularly as of
8.1.  (I'm not sure how much I trust the contrib version that exists in
8.0, and 7.4's is definitely pretty buggy, but I believe 8.1's can be
relied on to prevent this sort of thing.)

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Please HELP - URGENT - transaction wraparound error

[Martijn van Oosterhout]

On Sun, Oct 30, 2005 at 06:41:45PM +, John Sidney-Woollett wrote:
> Hmm. I'm pretty sure that database mail_lxtreme was unused (no 
> connections/activity) - I didn't think that it would need to be vacuumed 
> at all...

A database that is never used still needs to be vacuumed. The only
exception is if you VACUUM FREEZE which puts the entire database in a
frozen state which will never need vacuuming. This is how template0 is
configured. Ofcourse, once you make changes...

> Just out of curiousity would the wraparound error (for mail_lxtreme) 
> actually have affected data in bp_live?

I doubt it but (thinking shared tables) I'll have to defer to someone
more knowledgable.

> Could I just have deleted mail_lxtreme and then continued to use bp_live 
> as though nothing had happened?
> 
> Or had database bp_live already been damaged by the wraparound?

Everything would probably have been fine.

BTW, I would have thought this message would have been appearing the
last billion transactions or so, didn't anyone notice?

To solve this forever, setup a cronjob for once a month:

vacuumdb -a

This will vacuum every database, even if you don't know the names or
where they came from. AIUI when you vacuum a database whose
transactions are over billion transactions old it automatically puts it
in "frozen" state. If someone had happened to run "vacuumdb -a" anytime
in the last few months, you might never have noticed the wraparound...

Hope this helps,
-- 
Martijn van Oosterhout  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.


pgpAOqhwOGLIr.pgp
Description: PGP signature

Re: [GENERAL] Please HELP - URGENT - transaction wraparound error

[John Sidney-Woollett]

Hmm. I'm pretty sure that database mail_lxtreme was unused (no 
connections/activity) - I didn't think that it would need to be vacuumed 
at all...


Just out of curiousity would the wraparound error (for mail_lxtreme) 
actually have affected data in bp_live?


Could I just have deleted mail_lxtreme and then continued to use bp_live 
as though nothing had happened?


Or had database bp_live already been damaged by the wraparound?

Thanks for your great help/advice - it's much appreciated.

John

Tom Lane wrote:

John Sidney-Woollett <[EMAIL PROTECTED]> writes:


OK, I restored the pgsql/data to another server and started up postgres
and this is what I got:




 SELECT datname, age(datfrozenxid) FROM pg_database;
   datname| age
--+-
 mail_lxtreme | -2074187459
 bp_live  |  1079895636
 template1|  1076578064
 template0| -2074187459
(4 rows)



mail_lxtreme is a test mail db and I don't care about it. So it could 
have been deleted without any worries...




Which databases are a problem? Is it template0 or bp_live and template1?



mail_lxtreme is exactly the problem.  You weren't vacuuming it...

(template0 is a special case and can be ignored.)

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: [GENERAL] Please HELP - URGENT - transaction wraparound error

[Tom Lane]

John Sidney-Woollett <[EMAIL PROTECTED]> writes:
> OK, I restored the pgsql/data to another server and started up postgres
> and this is what I got:

>   SELECT datname, age(datfrozenxid) FROM pg_database;
> datname| age
> --+-
>   mail_lxtreme | -2074187459
>   bp_live  |  1079895636
>   template1|  1076578064
>   template0| -2074187459
> (4 rows)

> mail_lxtreme is a test mail db and I don't care about it. So it could 
> have been deleted without any worries...

> Which databases are a problem? Is it template0 or bp_live and template1?

mail_lxtreme is exactly the problem.  You weren't vacuuming it...

(template0 is a special case and can be ignored.)

regards, tom lane

---(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: [GENERAL] Please HELP - URGENT - transaction wraparound error

[John Sidney-Woollett]

OK, I restored the pgsql/data to another server and started up postgres

and this is what I got:

 SELECT datname, age(datfrozenxid) FROM pg_database;
   datname| age
--+-
 mail_lxtreme | -2074187459
 bp_live  |  1079895636
 template1|  1076578064
 template0| -2074187459
(4 rows)

mail_lxtreme is a test mail db and I don't care about it. So it could 
have been deleted without any worries...


Which databases are a problem? Is it template0 or bp_live and template1?

Thanks

John

Tom Lane wrote:

John Sidney-Woollett <[EMAIL PROTECTED]> writes:

I can restore the file system backup of pgsql/data to another database 
server and then get the info from pg_database. Or I can import a dump 
file from 15 minutes before I re-inited the database...



Importing a dump will tell you nothing at all, as all the data will be
freshly loaded.



What exactly am I looking for though?



SELECT datname, age(datfrozenxid) FROM pg_database;

where the second column approaches 2 billion.

Alternatively, wait a few weeks and note which entries in your live
database are increasing rather than staying near 1 billion.

regards, tom lane


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Please HELP - URGENT - transaction wraparound error

[Tom Lane]

John Sidney-Woollett <[EMAIL PROTECTED]> writes:
> I can restore the file system backup of pgsql/data to another database 
> server and then get the info from pg_database. Or I can import a dump 
> file from 15 minutes before I re-inited the database...

Importing a dump will tell you nothing at all, as all the data will be
freshly loaded.

> What exactly am I looking for though?

SELECT datname, age(datfrozenxid) FROM pg_database;

where the second column approaches 2 billion.

Alternatively, wait a few weeks and note which entries in your live
database are increasing rather than staying near 1 billion.

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: [GENERAL] Please HELP - URGENT - transaction wraparound error

[John Sidney-Woollett]

Hi Tom

You're not wrong about panicking! This is the worst Sunday I've had in a 
while... No sunday lunch or time with the kids... :(


This database supports a (normally 24/7) website and we couldn't 
tolerate any possibility of data corruption. I had to make a judgement 
call on preventing any/further data loss or corruption, and switching 
over to the slave seemed the safest thing to do (based on my ignorance 
of the wraparound problem).


I can restore the file system backup of pgsql/data to another database 
server and then get the info from pg_database. Or I can import a dump 
file from 15 minutes before I re-inited the database...


What exactly am I looking for though?

We don't use OIDs when creating tables...

Could Slon 1.1.0 be causing a problem for us? It must be creating and 
deleting bucket loads of records as part of its regular activity...


What am I likely to have missed in my vacuuming? Because whatever I did 
wrong is going to break our current live database at some point soon.


Thanks

John



Tom Lane wrote:

John Sidney-Woollett <[EMAIL PROTECTED]> writes:

I decided to switch over to the slave which is now our live database. 
the old master with the problem has already been re-inited (although I 
have a cold backup of the data dir), plus dump files that I can restore 
from.



You panicked much too quickly and destroyed the evidence ... unless by
"cold backup" you mean a filesystem backup, in which case what you
should do is restore that and take a look at what's in its pg_database.
I think there's no question that there is some omission in your vacuuming
procedures, and you need to find out what it is.

regards, tom lane


---(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: [GENERAL] Please HELP - URGENT - transaction wraparound error

[Tom Lane]

John Sidney-Woollett <[EMAIL PROTECTED]> writes:
> I decided to switch over to the slave which is now our live database. 
> the old master with the problem has already been re-inited (although I 
> have a cold backup of the data dir), plus dump files that I can restore 
> from.

You panicked much too quickly and destroyed the evidence ... unless by
"cold backup" you mean a filesystem backup, in which case what you
should do is restore that and take a look at what's in its pg_database.
I think there's no question that there is some omission in your vacuuming
procedures, and you need to find out what it is.

regards, tom lane

---(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: [GENERAL] Please HELP - URGENT - transaction wraparound error

[John Sidney-Woollett]

Martin, thanks for the feedback.

I had a look around and couldn't see any data loss (but wasn't really 
sure where to start looking).


I decided to switch over to the slave which is now our live database. 
the old master with the problem has already been re-inited (although I 
have a cold backup of the data dir), plus dump files that I can restore 
from.


I checked pg_database (on the new master) and I don't really understand 
what it is saying. Is the datvacuumxid vs 3553547043 the significant 
information? I see in our new database:


   datname| datdba | encoding | datistemplate
--++--+---
 bp_live  |  1 |6 | f
 bp_test  |  1 |6 | f
 template1|  1 |0 | t
 template0|  1 |0 | t


   datname| datallowconn | datlastsysoid | datvacuumxid
--+--+---+--
 bp_live  | t| 17140 |332321570
 bp_test  | t| 17140 |332265474
 template1| t| 17140 |332241177
 template0| f| 17140 |  464


   datname| datfrozenxid | datpath | datconfig
--+--+-+---
 bp_live  |   3553547043 | |
 bp_test  |   3553490947 | |
 template1|   3553466653 | |
 template0|  464 | |


   datname|  datacl
--+--
 bp_live  |
 bp_test  |
 template1| {postgres=C*T*/postgres}
 template0| {postgres=C*T*/postgres}


Are we going to get the same problem with this database?

What's also worrying me is that the warning message is in fact 
misleading!!??


2005-10-28 05:56:58 WARNING:  some databases have not been vacuumed in 
over 2 billion transactions

DETAIL:  You may have already suffered transaction-wraparound data loss.

And I'm wondering if I have in fact destroyed a perfectly good database 
and data set...


I read the link you gave (before) but found it hard to work out what you 
actually need to do to protect yourself.


We DID vacuum the databases nightly, and template1 once a week. So I 
still don't understand why we got this error. Can someone explain in 
simple language?


Can someone also give me a detailed "you need to do this, and this and 
this..." explanation to prevent this happening again (either on our 
master or slave databases).


For example, must you do a vacuum full instead of a vacuum analyze on a 
7.4.x database to prevent wraparound issues?


BTW, for those not using **Slony** - you should check it out. It has 
saved my bacon three times this year! Due to:


1) server failure - hardware crash, needed BIOS flash, complete OS 
reinstall etc

2) disk full - corrupted pg data
3) oid wraparound (today's problem)

Any further help that anyone can give is much appreciated.

Thanks

John

Martijn van Oosterhout wrote:

On Sun, Oct 30, 2005 at 08:50:18AM +, John Sidney-Woollett wrote:


Oh my god!

DB is pg 7.4.6 on linux



Firstly, check pg_database, it should tell you which databases need to
be vacuumed. Any database you regularly vacuumed is fine so maybe the
corruption is in some other database you don't remember?


1) Why do have we data corruption? I thought we were doing everything we 
needed to stop any wraparound... Are the pg docs inadequate, or did I 
misunderstand what needed to be done?



You *may* have corruption. Anything you vacuumed recently should be
fine.



2) What can I do to recover the data?



Check whether anything is lost first.



How do I stop this EVER happening again??!!!



Have you read this:

http://www.postgresql.org/docs/7.4/static/maintenance.html#VACUUM-FOR-WRAPAROUND

Hope this helps,


---(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: [GENERAL] Please HELP - URGENT - transaction wraparound error

[Martijn van Oosterhout]

On Sun, Oct 30, 2005 at 08:50:18AM +, John Sidney-Woollett wrote:
> Oh my god!
> 
> DB is pg 7.4.6 on linux

Firstly, check pg_database, it should tell you which databases need to
be vacuumed. Any database you regularly vacuumed is fine so maybe the
corruption is in some other database you don't remember?

> 1) Why do have we data corruption? I thought we were doing everything we 
> needed to stop any wraparound... Are the pg docs inadequate, or did I 
> misunderstand what needed to be done?

You *may* have corruption. Anything you vacuumed recently should be
fine.

> 2) What can I do to recover the data?

Check whether anything is lost first.

> How do I stop this EVER happening again??!!!

Have you read this:

http://www.postgresql.org/docs/7.4/static/maintenance.html#VACUUM-FOR-WRAPAROUND

Hope this helps,
-- 
Martijn van Oosterhout  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.


pgpfnTkNQOBJ3.pgp
Description: PGP signature

Re: [GENERAL] PLEASE HELP ME

[John DeSoi]

On Apr 21, 2005, at 1:22 AM, amit kumar awasthi wrote:
 # Using sockets credentials for improved security. Not available 
everywhere,
 # but works on Linux, *BSD (and probably some others)
 local  all    all            ident  sameuser
 #local  all    all            127.0.0.1  sameuser
 only this entry i have made



local is for unix domain sockets. You need a setting for host to enable 
TCP/IP connections via 127.0.0.1. Something like:

hostall all 127.0.0.1 255.255.255.255   
ident

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Please help to speed up UPDATE statement

[Bob Henkel]

On Apr 12, 2005 9:40 AM, Andrus <[EMAIL PROTECTED]> wrote:
The following statement runs VERY slowly on large tables.Any idea how to speed it up ?UPDATE rid SET dokumnr=NULL WHERE  dokumnr NOT IN (SELECT dokumnr FROM dok);Tables:CREATE TABLE dok ( dokumnr INTEGER,   CONSTRAINT dok_pkey PRIMARY KEY (dokumnr) );CREATE TABLE rid ( dokumnr INTEGER  );CREATE INDEX rid_dokumnr_idx ON rid (dokumnr);Andrus---(end of broadcast)---TIP 5: Have you checked our extensive FAQ?  http://www.postgresql.org/docs/faqPlease send your explain plan everyone will want to see it to tell what is going on.

Re: [GENERAL] Please help

[Ang Chin Han]

CY wrote:
I wanted a auto-increasement function, similar to sequence, to increase
a field.  I cannot use CREATE SEQUENCE because it cd_line_no will start
again with a new coursedetail.
CREATE TABLE coursedetail
(
cd_cf_id   char(30),
cd_line_no  smallint default auto_increment by 1,
cd_name  char(40),
cd_status  char(2),
CONSTRAINT cd_pkey PRIMARY KEY (cd_cf_id, cd_line_no),
FOREIGN KEY (cd_cf_id) REFERENCES course (cf_id)
);
Don't really understand why you can't use CREATE SEQUENCE... are you 
saying you need a unique, unused smallint for cd_line_no, for a given 
cd_cf_id (noting that (cd_cf_id, cd_line_no) is the primary key)?

This is probably a bad design, but you could try
CREATE FUNCTION cd_nextval(CHAR(30)) RETURNS smallint LANGUAGE sql AS
'SELECT coalesce(max(cd_line_no), -1) + 1 FROM coursedetail WHERE 
cd_cf_id = $1';

Need to change some of your application code to handle it, and your 
coursedetail table definition a bit, but, hey, can't do all your 
homework for you ;)



pgp0.pgp
Description: PGP signature

RE: [GENERAL] Please Help JDBC Error!!

[Dave Cramer]

Hi,

First of call can you make sure you are using the latest source. There
are binaries at http://jdbc.fastcrypt.com

Second, my instincts are telling me that there is some sort of overflow
occurring with 6k records. Is there some way to reduce the size of the
resultset?

Dave

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Binoy
Sent: July 30, 2001 11:48 PM
To: [EMAIL PROTECTED]
Subject: [GENERAL] Please Help JDBC Error!!


Hi
I have been  using postgres database since last 7 months working on a
involving JBoss server as an application server and postgres database at
backend lately.. i noticed as the database started growing larger..
today encountered some error while firing this query

select firstname , last name from mycustomers where firstname between
'A%' and 'Z%'

the above query returns 6000 + records ;-)

I am using PostgreSQL 7.1.2 and JBoss2.2.2 with jetty 3.1

The error stack trace is :
[Jetty] An I/O error occured while reading from backend - Exception:
java.net.SocketException: Connection reset by peer: JVM_recv in socket
input stream read Stack Trace:

java.net.SocketException: Connection reset by peer: JVM_recv in socket
input stream read  at java.net.SocketInputStream.socketRead(Native
Method)  at java.net.SocketInputStream.read(Unknown Source)  at
java.io.BufferedInputStream.fill(Unknown Source)  at
java.io.BufferedInputStream.read(Unknown Source)  at
org.postgresql.PG_Stream.ReceiveChar(PG_Stream.java:181)
 at org.postgresql.Connection.ExecSQL(Connection.java:473)
 at org.postgresql.jdbc2.Statement.execute(Statement.java:294)
 at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:59)
 at
org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.ja
va:1
08)
 at
org.opentools.minerva.jdbc.PreparedStatementInPool.executeQuery(Prepared
Stat
ementInPool.java:71)
 at
org.jboss.security.auth.spi.DatabaseServerLoginModule.getUsersPassword(D
atab
aseServerLoginModule.java:94)
 at
org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePa
sswo
rdLoginModule.java:82)
 at java.lang.reflect.Method.invoke(Native Method)
 at javax.security.auth.login.LoginContext.invoke(LoginContext.java:595)
 at
javax.security.auth.login.LoginContext.access$000(LoginContext.java:125)
 at javax.security.auth.login.LoginContext$3.run(LoginContext.java:531)
 at java.security.AccessController.doPrivileged(Native Method)  at
javax.security.auth.login.LoginContext.invokeModule(LoginContext.java:52
8)
 at javax.security.auth.login.LoginContext.login(LoginContext.java:449)
 at
org.jboss.security.plugins.JaasSecurityManager.defaultLogin(JaasSecurity
Mana
ger.java:333)
 at
org.jboss.security.plugins.JaasSecurityManager.authenticate(JaasSecurity
Mana
ger.java:301)
 at
org.jboss.security.plugins.JaasSecurityManager.isValid(JaasSecurityManag
er.j
ava:193)
 at
org.jboss.jetty.JBossUserRealm$User.authenticate(JBossUserRealm.java:46)
 at
com.mortbay.HTTP.Handler.SecurityHandler.basicAuthenticated(SecurityHand
ler.
java:259)
 at
com.mortbay.HTTP.Handler.SecurityHandler.authenticatedInRole(SecurityHan
dler
.java:192)
 at
com.mortbay.HTTP.Handler.SecurityHandler.handle(SecurityHandler.java:163
)
 at com.mortbay.HTTP.HandlerContext.handle(HandlerContext.java:956)
 at com.mortbay.HTTP.HandlerContext.handle(HandlerContext.java:913)
 at com.mortbay.HTTP.HttpServer.service(HttpServer.java:703)
 at com.mortbay.HTTP.HttpConnection.service(HttpConnection.java:508)
 at com.mortbay.HTTP.HttpConnection.handle(HttpConnection.java:347)
 at
com.mortbay.HTTP.SocketListener.handleConnection(SocketListener.java:107
)
 at com.mortbay.Util.ThreadedServer.handle(ThreadedServer.java:294)
 at
com.mortbay.Util.ThreadPool$PoolThreadRunnable.run(ThreadPool.java:613)
 at java.lang.Thread.run(Unknown Source)
End of Stack Trace

[Jetty]  at org.postgresql.PG_Stream.ReceiveChar(PG_Stream.java:184)
[Jetty]  at org.postgresql.Connection.ExecSQL(Connection.java:473)
[Jetty]  at org.postgresql.jdbc2.Statement.execute(Statement.java:294)
[Jetty]  at
org.postgresql.jdbc2.Statement.executeQuery(Statement.java:59)
[Jetty]  at
org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.ja
va:1
08)
[Jetty]  at
org.opentools.minerva.jdbc.PreparedStatementInPool.executeQuery(Prepared
Stat
ementInPool.java:71)
[Jetty]  at
org.jboss.security.auth.spi.DatabaseServerLoginModule.getUsersPassword(D
atab
aseServerLoginModule.java:94)
[Jetty]  at
org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePa
sswo
rdLoginModule.java:82)
[Jetty]  at java.lang.reflect.Method.invoke(Native Method) [Jetty]  at
javax.security.auth.login.LoginContext.invoke(LoginContext.java:595)
[Jetty]  at
javax.security.auth.login.LoginContext.access$000(LoginContext.java:125)
[Jetty]  at
javax.security.auth.login.LoginContext$3.run(LoginContext.java:531)
[Jetty]  at java.security.AccessController.doPrivileged(Native Method)
[Jetty]  at
javax.security.auth.login.LoginContext.invokeModule(LoginContext.java:5

Re: [GENERAL] Please help!

[Richard Huxton]

From: "Vijayan" <[EMAIL PROTECTED]>

>  I have a registration database. I need to know number of
> registrations / month in a report format. How will I get a report like
> this ?
>
> In Oracle I can give the query like this:-
> "select to_char(reg_date, 'Mon '), count(*) from
> register group by to_char(reg_date, 'Mon ') order by
> to_date(to_char(reg_date, 'Mon '), 'Mon ');"
>
> When I tried in psql, it gave this error. "No such function
> 'to_char' with the specified attributes".  But in postgre there is a
> function called to_char(). Then why am I getting this error? My postgre
> version is "PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc
> egcs-2.91.66".

richardh=> select to_char(now(), 'Mon ');
 to_char
--
 Jul 2001
(1 row)

richardh=> select version();
   version
-
 PostgreSQL 7.1.1 on i586-pc-linux-gnu, compiled by GCC 2.96
(1 row)

Try "\df to_char" to see how/if to_char is defined in 6.5.3 - if you have
one with timestamp,text try something like:

to_char(reg_date, 'Mon '::text)

and see if that helps. Perhaps have a look at the changes list (in the
administrators manual) and see if there's been any work on it.

Failing that, an upgrade might be in order.

- Richard Huxton


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [GENERAL] Please help recover data - lost pg_control

[Tom Lane]

"Alok K. Dhir" <[EMAIL PROTECTED]> writes:
>   ERROR:  readDatum: ']' expected, length = 12

Hm, are you sure that was 7.1b3, and not something even older?  Looking
at the CVS logs, the last change in stored rule format was between beta1
and beta2, so I'd expect this sort of failure if you tried to run 7.1
release against a 7.1b1 database (noting that you clobbered the normal
DB version crosscheck by running pg_resetxlog).

You might be able to build a hacked version of 7.1 release that will
read your 7.1b1 database by undoing the CONST node dump format change of
2001-01-07 --- see rev 1.103 at
http://www.ca.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/nodes/readfuncs.c
but note that most of the diffs don't need to be undone, you just need
to change back the order of the fields that _readConst is expecting.

I'm not sure that's the only thing that'll bite you, but it's worth a
try.  If you get a clean dump, initdb with a non-hacked copy of 7.1.2
before restoring.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [GENERAL] Please help! Functions passing records between them

[Thalis A. Kalfigopoulos]

I had asked something simular a week back.
This should help you :-)

http://fts.postgresql.org/db/mw/msg.html?mid=121203

cheers,
thalis

ps did someone play too much with the majordomo? it screwes up the list addresses in 
Cc: when I reply to a message


On 12 Jun 2001, Alla wrote:

> Guys;
> 
> I am begging for your help again.
> 
> I can't find a solution to my problem.
> 
> I am porting a complex system from Oracle to PostgreSQL and I need to
> implement the following:
> 
> function 1 does some processing and returns a record (I can declare it
> as a row in a view)
> function 2 uses func1 to get that record and does some more processing
> 
> My problem is that even if I can return a record from my function 1,
> function 2 does not read it properly
> 
> Here is an example:
> create view my_view
>   as select null as type, null as value, null as timestamp;  -- this
> is how I "declare" the user-defined data structure (I could not find
> any other way)
> 
> create function func1()
> returns my_view as '
> declare
>my_record   my_view%rowtype;
> begin
>.
>.
>my_record.type := ''AAA'';
>my_record.value := 25;
>my_record.timestamp := now();   -- this is for simplicity
>   
>return my_record;
> end;
> ' LANGUAGE 'plpgsql';
> 
> create function func2()
> returns varchar as '
> declare
>my_record   my_view%rowtype;
> begin
>select func1() into my_record;
> 
>return my_record.type;
> end;
> ' LANGUAGE 'plpgsql';
> 
>
> It compiles and runs fine, except that it does not return what it's
> supposed to. It gives me some strange huge number, which I assume is
> some kind of OID
> 
> 
> I know that there are quite a few gurus of PostgreSQL out there -
> please help me solve this problem. May be my whole approach is wrong,
> but I need to be able to accomplist this: pass some kind of
> user-defined structures between function
> 
> Thank you so much for your help
> 
> Alla Gribov
> 
> ---(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 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [GENERAL] please help me with arrays

[Peter Eisentraut]

Bo Berkhaut writes:

> CREATE TABLE example (name text, slots text[]);
>
> I want to be able to select, for example, all rows such that an element
> of example.slots exists equal to example.name.

There are some operators for this in contrib/array.

However, I'd say that if you're trying to do this you better redesign your
database schema to make "slots" a separate table.

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/

Re: [GENERAL] please help me recover from duplicate key in unique index

[Adriaan Joubert]

> > Please help me recover our database from what I think
> > is a duplicate key in unique index problem.
>
> This may not help, and forgive my asking the obvious, but have
> you done a SELECT on the table and actually *seen* duplicate 'id'
> values in the SERIAL column?  That would surprise me because I
> didn't think it was possible to create duplicates in the scenario
> you describe.  The SERIAL type is really just a sequence, and its
> values are unique across all transactions (I hope!).  IIRC there
> is some opportunity for wierdness if the sequence cache setting
> has been "adjusted" incorrectly
> (http://www.postgresql.org/docs/postgres/sql-createsequence.htm).
>
> > When I try to vacuum, I get this:
> >
> > ERROR:  Cannot insert a duplicate key into a unique
> > index
> >

Try dropping all indexes on the table, do a vacuum (if it will let you).
Dump the table out with pg_dump -t  if it will let you, sort it
in emacs or with perl. Easiest thing would then be to write a little
perl script that puts all duplicate rows into a separate file. Dropt the
table and re-create it. Load the first lot up (with given sequence
number!), fix your sequences (drop, create ..start ) and then
handle the duplicate rows (i.e. insert them with perl/DBI or something
so that they get new sequence numbers assigned).

I think you should be able to dump once you have dropped all indexes
(probably one of them is _pkey). Your sequence may be called
__seq if memeory serves me right. It is still a
normal sequences and you can drop and recreate it safely.

Good luck,

Adriaan

RE: [GENERAL] Please help with performance tuning on Postgres

[Greg Youngblood]

Yes, you assume correctly. I have read the FAQ. The only problem is the
FAQ's answer is nice, short, and sweet, but does not have any good real
world examples or real details.. The only details it mentions is that
setting shared memory buffers too high can cause it to crash, and so forth.
What is too high? Is there a formula or rule of thumb that you can use?
Possibly based on amount of available memory or memory your willing to
'dedicate' to the process, the number of back end processes, etc?

What I am looking for is some help from people who have tuned their
databases and can offer some insight. For example, the same performance
gains may be possible with multiple parameter settings, however, one group
of settings may be less 'expensive' than another. Or, you may be able to get
almost the same performance gain from a less expensive change than you do
with changes that consume a lot more resources.

That kind of information is obtained from people who have gone through this
process already, and have either learned the hard way, or picked up tricks
from others. If these people can share their experience, then others such as
myself can avoid reinventing the wheel. That is what I am searching for.

Thanks for the directions to the FAQ. It is also in the 'doc' directory of
the pgsql distribution.

Greg

Gregory S. Youngblood
ext 2164
 
 


-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Saturday, October 03, 1998 5:55 PM
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Please help with performance tuning on Postgres


I assume you have read the FAQ item on performance.  It is on the web
site.


> I have a database running fine, however, it is purely stock configuration.
I
> would like to fine tune it for better performance and to control memory
> usage.
> 
> I have seen people refer to restricting the number of back end processes,
> and fine tuning maximum memory consumption as well as other items.
> 
> I've read the man pages, and a few options look promising, such as -B on
> postmaster and -S on postgres. However, I can't find anything that
explains
> what exactly those options will do, and the ramifications of changing them
> away from defaults.
> 
> I also can't find any mention of restricting the number of simultaneous
> connections. Or, restricting the number of back end processes that can be
> run.
> 
> The database will have a lot of information, but has no need to run more
> than maybe 4 to 12 backend processes. I would estimate that it wouldn't
need
> more than 6 unless special circumstances arose.
> 
> This database is going to sit behind a web based front end. There won't be
a
> lot of simultaneous queries on the system, in normal operations. The web
> will be used as a query tool. Data modifications and updates will probably
> be via Access and ODBC, but that may change to some web based
modifications
> and updates at a later time.
> 
> Does anyone have any pointers to fine tuning memory and performance
details
> of Postgres? I would really like to find something that documents not only
> what things do, but what ramifications of certain operations are. I want
to
> understand the performance tuning, not just copy someone's prewritten
> instructions. 
> 
> Thanks.
> Greg
> 
> Gregory S. Youngblood
> ext 2164
>  
>  
> 
> 
> 
> 
> 


-- 
  Bruce Momjian|  http://www.op.net/~candle
  [EMAIL PROTECTED]|  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026