[GENERAL] ERROR: SERVER CLOSED THE CONNECTION UNEXPECTEDLY

When i connect to the server on PostgreSQL i get this error: Server closed the connection unexpectedly. This probably means the server termitate abnormally before or while processing the request postgresql-2012-03-30_00.log

Re: [GENERAL] Using Variables in dblink_build_sql_insert

DOH! I figured it out. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Using-Variables-in-dblink-build-sql-insert-tp5750332p5750335.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postg

[GENERAL] Using Variables in dblink_build_sql_insert

Here is what I get from the documentation: The last two arguments are: text[] src_pk_att_vals_array, text[] tgt_pk_att_vals_array SELECT dblink_build_sql_insert('foo', '1 2', 2, '{"1", "a"}', '{"1", "b''a"}'); How do I replace the src and tgt with variables from within my function? I can easily c

[GENERAL] Is 'Peer authentication' supported on HPUX ?

Hi, Is 'Peer authentication' supported on HPUX ? I am using PG 9.2 , where i have set postgres1 postgres postgres --> in pg_ident.conf local all all ident map=postgresql1 --> in pg_hba.conf file ident_file='/home/Postgresql/9.2/data/pg_ident.conf' --> in postgresql.conf file server got res

[GENERAL] Is 'Peer authentication' supported on HPUX ?

Hi, Is 'Peer authentication' supported on HPUX ? I am using PG 9.2 , where i have set postgres1 postgres postgres --> in pg_ident.conf local all all ident map=postgresql1 --> in pg_hba.conf file ident_file='/home/Postgresql/9.2/data/pg_ident.conf' --> in postgresql.conf file server go

[GENERAL] High CPU usage of stats collector

On my db server I have noticed a very high CPU usage of process: *postgres: stats collector process* It is not below 50% of CPU. (Info from htop). What is best way to decrease CPU usage of this process? Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/High-CPU-

Re: [GENERAL] Upgrade from 9.1 to 9.2 fails due to unlogged table?

I repeated the entire process, and I have a few clarifications. When I said the db seemed fine after the restore, I was wrong. I could do a \d on an unlogged table, but when I selected count(*) from any, that resulted in an error like "could not open file "base/16388/15963587": No such file or

[GENERAL] Upgrade from 9.1 to 9.2 fails due to unlogged table?

I have a 9.1.3 instance (Redhat 5) with some unlogged tables. I did the following steps: 1. pg_basebackup to create a base.tar 2. Used the base.tar plus the WALs required by the backup to restore the db to another 9.1.3 server. This went fine, except at the end of the recovery I got this error (f

Re: [GENERAL] OID of type by name.

In what version of PG is the 'my_type'::regtype::oid syntax available? I want to introduce this to the JDBC driver which currently does the most ridiculous query that totally ignores search path. JDBC driver does this currently: SELECT oid FROM pg_catalog.pg_type WHERE typname = ? So if you h

Re: [GENERAL] Oracle to PostgreSQL transition?

On 4 April 2013 16:16, Roy Anderson wrote: > Hey all, > > We have a very robust Oracle and SQL Server presence at work but we're > looking to farm out some of the load to PostgreSQL to limit costs. I'm > curious if there are any DBAs out there who have gone down this route > before. Any tips, tri

Re: [GENERAL] Oracle to PostgreSQL transition?

On Thu, Apr 4, 2013 at 11:16 PM, Roy Anderson wrote: > Hey all, > > We have a very robust Oracle and SQL Server presence at work but we're > looking to farm out some of the load to PostgreSQL to limit costs. I'm > curious if there are any DBAs out there who have gone down this route > before. Any

Re: [GENERAL] OID of type by name.

2013/3/29 tahoe-gary > In what version of PG is the 'my_type'::regtype::oid syntax available? I > want to introduce this to the JDBC driver which currently does the most > ridiculous query that totally ignores search path. > > JDBC driver does this currently: SELECT oid FROM pg_catalog.pg_type

Re: [GENERAL] Need advice to avoid ORDER BY

On 2013-04-05 01:54, Merlin Moncure wrote: On Thu, Apr 4, 2013 at 5:15 PM, Tom Lane wrote: Merlin Moncure writes: problem is that you are looking for needles (valids = 0) in the haystack. the problem wasn't really the order, but the fact that you had to scan an arbitrary amount of rows be

Re: [GENERAL] High CPU usage of stats collector

On Sun, Mar 31, 2013 at 11:44 PM, komunca wrote: > On my db server I have noticed a very high CPU usage of process: > *postgres: stats collector process* > > It is not below 50% of CPU. (Info from htop). > > What is best way to decrease CPU usage of this process? Do you by any chance have a large

[GENERAL] Bug or feature? (The constraint of the domain of extension can be dropped...)

Hey hackers, According to http://www.postgresql.org/docs/9.2/static/extend-extensions.html "PostgreSQL will not let you drop an individual object contained in an extension, except by dropping the whole extension." But this rule does not apply to domain constraints, i.e. it is not possible to drop

Re: [GENERAL] High CPU usage of stats collector

On Fri, Apr 5, 2013 at 7:44 PM, Magnus Hagander wrote: > On Sun, Mar 31, 2013 at 11:44 PM, komunca wrote: > > On my db server I have noticed a very high CPU usage of process: > > *postgres: stats collector process* > > > > It is not below 50% of CPU. (Info from htop). > > > > What is best way to

[GENERAL] how to completely disable toasted table in postgresql and best practices to follow

Dear admin, please help me i want to completely disable Toasted tables in postgresql as per your suggestion i have used 9.0.13 but still toasted tables are getting created. also i want to know best practices and methods to clear such issues. kindly help Thanks & Regards, Zahid Quadri

Re: [GENERAL] ERROR: SERVER CLOSED THE CONNECTION UNEXPECTEDLY

Hello, What's the version? Regards 2013/4/3 jpui > When i connect to the server on PostgreSQL i get this error: > > Server closed the connection unexpectedly. > This probably means the server termitate abnormally before or > while processing the request > > > postgresql-2012-03-30_00.log

Re: [GENERAL] how to completely disable toasted table in postgresql and best practices to follow

On 2013-04-05 18:32:47 +0530, Zahid Quadri wrote: > Dear admin, > > please help me i want to completely disable Toasted tables in postgresql as > per your suggestion i have used 9.0.13 but still toasted tables are getting > created. > > also i want to know best practices and methods to clear

Re: [GENERAL] how to completely disable toasted table in postgresql and best practices to follow

On 5 April 2013 15:49, Andres Freund wrote: > On 2013-04-05 18:32:47 +0530, Zahid Quadri wrote: > > Dear admin, > > > > please help me i want to completely disable Toasted tables in postgresql > as per your suggestion i have used 9.0.13 but still toasted tables are > getting created. > > > > also

Re: [GENERAL] Is 'Peer authentication' supported on HPUX ?

tushar ahuja writes: > Is 'Peer authentication' supported on HPUX ? The fine manual says Peer authentication is only available on operating systems providing the getpeereid() function, the SO_PEERCRED socket parameter, or similar mechanisms. Currently that includes Linux, most flavors of BSD inc

Re: [GENERAL] how to completely disable toasted table in postgresql and best practices to follow

According to doc, YES. http://www.postgresql.org/docs/9.2/static/storage-toast.html MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit

Re: [GENERAL] how to completely disable toasted table in postgresql and best practices to follow

On 2013-04-05 15:53:40 +0200, Szymon Guz wrote: > On 5 April 2013 15:49, Andres Freund wrote: > > > On 2013-04-05 18:32:47 +0530, Zahid Quadri wrote: > > > Dear admin, > > > > > > please help me i want to completely disable Toasted tables in postgresql > > as per your suggestion i have used 9.0.1

Re: [GENERAL] Bug or feature? (The constraint of the domain of extension can be dropped...)

Dmitriy Igrishin writes: > According to > http://www.postgresql.org/docs/9.2/static/extend-extensions.html > "PostgreSQL will not let you drop an individual object contained in an > extension, except by dropping the whole extension." > But this rule does not apply to domain constraints, i.e. it is

Re: [GENERAL] Oracle to PostgreSQL transition?

On Fri, Apr 5, 2013 at 2:12 AM, Szymon Guz wrote: > On 4 April 2013 16:16, Roy Anderson wrote: >> >> Hey all, >> >> We have a very robust Oracle and SQL Server presence at work but we're >> looking to farm out some of the load to PostgreSQL to limit costs. I'm >> curious if there are any DBAs out

Re: [GENERAL] upgrading from V8.3.4 to V9.2.4

On Wed, Apr 3, 2013 at 10:02 PM, Adrian Klaver wrote: > On 04/03/2013 10:14 AM, Paul Tilles wrote: >> >> We are going to be upgrading our postgres version from 8.3.4 to 9.2.4 in >> the near future. >> >> Can anyone give me a short list of "gotchas" concerning problems we >> might step into? >> >>

Re: [GENERAL] Bug or feature? (The constraint of the domain of extension can be dropped...)

2013/4/5 Tom Lane > Dmitriy Igrishin writes: > > According to > > http://www.postgresql.org/docs/9.2/static/extend-extensions.html > > "PostgreSQL will not let you drop an individual object contained in an > > extension, except by dropping the whole extension." > > But this rule does not apply t

Re: [GENERAL] Oracle to PostgreSQL transition?

On 04/04/2013 09:16 AM, Roy Anderson wrote: We have a very robust Oracle and SQL Server presence at work but we're looking to farm out some of the load to PostgreSQL to limit costs. You can do what we did. EnterpriseDB has built-in Oracle compatibility through a series of plugins, patches, an

Re: [GENERAL] Oracle to PostgreSQL transition?

On 04/05/2013 08:03 AM, Robert Treat wrote: On Fri, Apr 5, 2013 at 2:12 AM, Szymon Guz wrote: On 4 April 2013 16:16, Roy Anderson wrote: Hey all, We have a very robust Oracle and SQL Server presence at work but we're looking to farm out some of the load to PostgreSQL to limit costs. I'm c

Re: [GENERAL] upgrading from V8.3.4 to V9.2.4

Robert Treat wrote: > Yeah, there were also some subtle breakage around keywords used > as variable naming when plpgsql was port to use the core lexer. One more: from a Java client access to bytea columns will break if you don't also use the latest JDBC driver jar. -- Kevin Grittner EnterpriseD

Re: [GENERAL] upgrading from V8.3.4 to V9.2.4

Beside all notes, i recommend you to use pg_upgrade, to avoid a complete backup/restore in your transition. http://www.postgresql.org/docs/9.2/static/pgupgrade.html On Fri, Apr 5, 2013 at 1:30 PM, Kevin Grittner wrote: > Robert Treat wrote: > > > Yeah, there were also some subtle breakage aro

Re: [GENERAL] Bug or feature? (The constraint of the domain of extension can be dropped...)

Dmitriy Igrishin writes: > 2013/4/5 Tom Lane >> We do not generally forbid ALTER on extension members. During >> development for instance a quick ALTER can be a whole lot more >> convenient than dropping and reloading the whole extension. > Debatable, because in practice during development it's

[GENERAL] BEFORE UPDATE trigger doesn't change column value

Hi, Sorry for this newbie-question, I am trying for quite some time now to get the following trigger-function to work properly: CREATE OR REPLACE FUNCTION update_synced_column() RETURNS trigger AS $BODY$ BEGIN IF NEW.synced IS NULL THEN NEW.synced := false; END IF; RETURN NEW; END

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

Clemens Eisserer wrote: > I am trying for quite some time now to get the following > trigger-function to work properly: > > CREATE OR REPLACE FUNCTION update_synced_column() >   RETURNS trigger AS > $BODY$ > BEGIN >    IF NEW.synced IS NULL THEN > NEW.synced :=  false; >    END IF; >    RETU

[GENERAL] Syntax problem with INDEX on expression

The following expression works fine in a query: sp_dev=# select (xpath('//rms:xsid/text()',udh,array[array['rms',' http://www.example.com']]))[1]::text from test; xpath -- 000125846 000125843 000125844 000125842 000125845 000125847 (10 rows)

Re: [GENERAL] Syntax problem with INDEX on expression

Mike Blackwell wrote: > sp_dev=# create index test_idx on test > ((xpath('//rms:xsid/text()',udh,array[array['rms','http://www.example.com']]))[1]::text); > ERROR:  syntax error at or near "[" > LINE 1: ...udh,array[array['rms','http://www.example.com']]))[1]::text)... > 

Re: [GENERAL] Syntax problem with INDEX on expression

So, one set to mark the parameter and one for the expression? It's starting to look like Lisp. ^_^ Thanks! __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

On 5/4/13 at 6:59 AM, Clemens Eisserer wrote: Sorry for this newbie-question, I am trying for quite some time now to get the following trigger-function to work properly: CREATE OR REPLACE FUNCTION update_synced_column() RETURNS trigger AS $BODY$ BEGIN IF NEW.synced IS NULL THEN NEW.synced := f

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

Hi, NEW reflects what the row will look like after the UPDATE. There > is no way to tell which columns were specified in the SET clause of > the UPDATE; a column which is omitted from that clause will look > exactly the same as a column which is set to the value it already > had. > Thanks a lot

[GENERAL] PostgreSQL Downgrades

Is it possible to downgrade PostgreSQL by a major version? If I want to upgrade a PG database from 8.x to 9.x, I have to dump the db, install the new binaries and import the dump file. This works fine going forward, but what if I need to rollback my changes? I cannot dump the 9.x database and

Re: [GENERAL] PostgreSQL Downgrades

On 4/5/2013 3:33 PM, Pete Wall wrote: Is there a supported way to downgrade PostgreSQL by major versions? no. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] ERROR: SERVER CLOSED THE CONNECTION UNEXPECTEDLY

Postgres 8.3 The OS is windows server 2007 i think. Thank you for your help -- View this message in context: http://postgresql.1045698.n5.nabble.com/ERROR-SERVER-CLOSED-THE-CONNECTION-UNEXPECTEDLY-tp5750563p5750940.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. --

Re: [GENERAL] High CPU usage of stats collector

*288* -- View this message in context: http://postgresql.1045698.n5.nabble.com/High-CPU-usage-of-stats-collector-tp5750271p5750925.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

Re: [GENERAL] High CPU usage of stats collector

yes. maybe ~200 databases -- View this message in context: http://postgresql.1045698.n5.nabble.com/High-CPU-usage-of-stats-collector-tp5750271p5750918.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.

Re: [GENERAL] PostgreSQL Downgrades

On 04/05/2013 03:33 PM, Pete Wall wrote: Is it possible to downgrade PostgreSQL by a major version? If I want to upgrade a PG database from 8.x to 9.x, I have to dump the db, install the new binaries and import the dump file. This works fine going forward, but what if I need to rollback my chan

Re: [GENERAL] PostgreSQL Downgrades

On 04/05/2013 03:33 PM, Pete Wall wrote: Is it possible to downgrade PostgreSQL by a major version? If I want to upgrade a PG database from 8.x to 9.x, I have to dump the db, install the new binaries and import the dump file. This works fine going forward, but what if I need to rollback my ch

Re: [GENERAL] ERROR: SERVER CLOSED THE CONNECTION UNEXPECTEDLY

On 4/5/2013 6:58 AM, jpui wrote: Postgres 8.3 8.3 is past end of life, and is now considered a dead version. afaik, Windows Server went from 2003 to 2008, I've never heard of a 2007. -- john r pierce 37N 122W somewhere on the middle of the left coast

Re: [GENERAL] Oracle to PostgreSQL transition?

2013/4/5 Robert Treat : > > Yeah, it's worth reading through those links to get an idea of things; > you'll find much less literature (and tools) for MSSQL, but the > principals are mostly the same. One thing to decide on is if you are > going to port applications wholesale, or try to run some kind