[GENERAL] backend process terminates

We've been wrestling with a problem where the backend process terminates with a SEGSEGV. We are having a hard time tracking this thing down, so I decided to run a batch gdb process that single steps through the code until it crashes and post the output to the list for a request for assistance.

Re: [GENERAL] NOT NULL Issue

Tom Lane wrote: "Gustav Lindenberg" <[EMAIL PROTECTED]> writes: Why is '' not considered null is postgres (8.1.3) Because they're different. The SQL spec says that an empty string is different from NULL, and so does every database in the world except Oracle. Oracle, however, does not define

[GENERAL] pitr question

Excuse the ignorance, but I see the following in the docs: 'In any case the hardware architecture must be the same — shipping from, say, a 32-bit to a 64-bit system will not work.' Is this specific to the hardware? That is to say, can I use pitr wal shipping from 64 bit hardware to 64 bit ha

Re: [GENERAL] pitr question

On 10/13/2010 11:30 AM, zhong ming wu wrote: On Wed, Oct 13, 2010 at 11:17 AM, Geoffrey Myers mailto:li...@serioustechnology.com>> wrote: > Excuse the ignorance, but I see the following in the docs: > > 'In any case the hardware architecture must be the same — shipping fr

[GENERAL] Pitr

Set up wal shipping on postgresql 8.3.9 and rhel 5.5. When I start the postmaster on the primary, there is no reference to archiving in the log and files do not get shipped. Had this working earlier, but the ssh keys were accidentally over written breaking the shipping. Any clues? -- Later, Geo

Re: [GENERAL] Pitr

Patience is my friend. No transactions so no archiving. Waiting long enough produced results. Sorry for the noise. -- Later, Geoffrey Sent from my iPhone On Dec 27, 2010, at 3:18 PM, Geoffrey Myers wrote: > Set up wal shipping on postgresql 8.3.9 and rhel 5.5. When I start the > post

[GENERAL] searching for characters via the hexidecimal value

Is there a way to search for a character in the database by the hexidecimal value of that character? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jef

[GENERAL] error while trying to change the database encoding on a database

We need to change the database encoding on our databases as they were created with the wrong encoding. They were created as SQL_ASCII and we are changing them to UTF8. When testing this Friday, I received the following error: pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore:

Re: [GENERAL] searching for characters via the hexidecimal value

x27; is your hexadecimal character value. Be sure to read and understand everything you can find about encodings; and make sure the hexadecimal value you are searching for is from the same encoding. Best wishes, Harald On Mon, Jan 24, 2011 at 16:00, Geoffrey Myers mailto:li...@serioustechnolo

Re: [GENERAL] error while trying to change the database encoding on a database

Adrian Klaver wrote: On Monday 24 January 2011 6:38:55 am Geoffrey Myers wrote: We need to change the database encoding on our databases as they were created with the wrong encoding. They were created as SQL_ASCII and we are changing them to UTF8. When testing this Friday, I received the

Re: [GENERAL] error while trying to change the database encoding on a database

Adrian Klaver wrote: On Monday 24 January 2011 7:57:52 am Geoffrey Myers wrote: Adrian Klaver wrote: On Monday 24 January 2011 6:38:55 am Geoffrey Myers wrote: We need to change the database encoding on our databases as they were created with the wrong encoding. They were created as

Re: [GENERAL] error while trying to change the database encoding on a database

Adrian Klaver wrote: On Monday 24 January 2011 8:06:38 am Geoffrey Myers wrote: Adrian Klaver wrote: On Monday 24 January 2011 7:57:52 am Geoffrey Myers wrote: Adrian Klaver wrote: On Monday 24 January 2011 6:38:55 am Geoffrey Myers wrote: We need to change the database encoding on our

Re: [GENERAL] error while trying to change the database encoding on a database

Adrian Klaver wrote: On 01/24/2011 09:16 AM, Geoffrey Myers wrote: We hope to identify the characters and fix them in the existing database, then convert. It appears to be very limited, but it would help if there was some way to identify these characters outside of simply doing the reload of

[GENERAL] plsql question

I am trying to write a plsql routine that will delete a range of characters based on their octal or hexadecimal values. Something like the 'tr' shell command will do: cat file| tr -d ['\177'-'\377'] Can't seem to figure this one out. Pointers would be appreciated. -- Until later, Geoffrey

Re: [GENERAL] finding bogus UTF-8

Glenn Maynard wrote: On Thu, Feb 10, 2011 at 2:02 PM, Scott Ribe > wrote: I know that I have at least one instance of a varchar that is not valid UTF-8, imported from a source with errors (AMA CPT files, actually) before PG's checking was as string

Re: [GENERAL] finding bogus UTF-8

Vick Khera wrote: On Tue, Feb 15, 2011 at 11:09 AM, Geoffrey Myers wrote: comments would be appreciated. If all you're doing is filtering stdin to stdout and deleting a range of characters, it seems that tr would be a faster tool: cat foo.txt | tr -d '\000-\008\013-\037\177-\

[GENERAL] disable triggers using psql

So, we have a text dump that we used to clean up our data, now we need to reload it into the new database. Problem is, we have some data integrity issues that cause records to fail to load. Before we ran into the data conversion issue we were using 'pg_restore disable_triggers' to get around

Re: [GENERAL] disable triggers using psql

Andrew Sullivan wrote: On Wed, Feb 16, 2011 at 10:08:53AM -0500, David Johnston wrote: I may be off-track here but triggers do not enforce referential integrity - constraints do. If you need to disable triggers you can do so via the ALTER TABLE command. Unless something very big changed when

Re: [GENERAL] disable triggers using psql

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 We were trying to accomplish this without having to hack the dump to much. We attempted adding: set local session_replication_role = replica; But that does not seem provide the expected relief. How exactly did

Re: [GENERAL] disable triggers using psql

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 set local session_replication_role = replica; But that does not seem provide the expected relief. How exactly did this fail? This should absolutely disable all triggers for you, unless you've mucked with the trigge

Re: [GENERAL] disable triggers using psql

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I thought we had tried this before, but with an embedded BEGIN. I get the same result, although I used the -1 switch instead of the BEGIN/COMMIT: psql:backup.txt:2077303: ERROR: insert or update on table "custome

Re: [GENERAL] disable triggers using psql

//biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1dpvQACgkQvJuQZxSWSsjvrgCgmiITSLnGyrBunVZTScc4HKvz Y3IAn1sYG4/BdM6XJpBAVMz6lU1WfUVH =XZcQ -END PGP SIGNATURE- -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- Sent via pgsql-general mailing list (pgsql-genera

Re: [GENERAL] disable triggers using psql

201102171551 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk1diwUACgkQvJuQZxSWSshcPgCdGuHIe4bINl/BmoKW89YmQscD IdAAnA8bwbzmMKssCga9G0dpSh1GopzD =khQx -END PGP SIGNATURE- -- Geoffrey Myers Myers Consulting Inc. 770.592

Re: [GENERAL] disable triggers using psql

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 psql:test.sql:11: ERROR: current transaction is aborted, commands ignored until end of transaction block Oops my bad, I forgot to tell you I have \set ON_ERROR_ROLLBACK on in my .psqlrc. So you'll need to add tha

Re: [GENERAL] disable triggers using psql

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 So I added the on_error_rollback to the script and I get this: ... psql:test.sql:12: ERROR: current transaction is aborted, commands ignored until end of transaction block That isn't right: are you sure you said

Re: [GENERAL] disable triggers using psql

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 cut and paste: set ON_ERROR_ROLLBACK; Should be \set ON_ERROR_ROLLBACK on You can also set this when calling psql like so: psql --set ON_ERROR_ROLLBACK=on But that's getting off-topic now, as we've got the pro

Re: [GENERAL] disable triggers using psql

SIGNATURE- -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- 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] disable triggers using psql

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I'm not sure how to address this. I'm not exactly sure where to place session_replication_role. It's very close to the top of the file: Is this a pg_dumpall? A \connect later on will reset the session_replicatio

Re: [GENERAL] disable triggers using psql

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I'm not sure how to address this. I'm not exactly sure where to place session_replication_role. It's very close to the top of the file: Is this a pg_dumpall? A \connect later on will reset the session_replicatio

Re: [GENERAL] disable triggers using psql

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 The saga continues. I've reverted to a multi-step process to try and figure this out. I create the initial database, then load it from the command line psql as follows: pro-# \set session_replication_role replic

Re: [GENERAL] Covert database from ASCII to UTF-8

friendly?' What about some sort of wal log shipping replication? WAL Log shipping won't help. Thanks & Regards, Vibhor Kumar -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

[GENERAL] error messages during restore

So we are in the process of converting our databases from SQL_ASCII to UTF8. If a particular row won't import because of the encoding issue we get an error like: pg_restore: [archiver (db)] Error from TOC entry 5317; 0 1266711 TABLE DATA logs postgres pg_restore: [archiver (db)] COPY failed:

Re: [GENERAL] error messages during restore

Tom Lane wrote: Geoffrey Myers writes: So we are in the process of converting our databases from SQL_ASCII to UTF8. If a particular row won't import because of the encoding issue we get an error like: pg_restore: [archiver (db)] Error from TOC entry 5317; 0 1266711 TABLE DATA

[GENERAL] UTF8 conversion revisited

So, we are still having an issue with this and I thought I'd throw this out to the list to see if I'm missing something. Basically, we have identified the tables/fields we need to convert. I'm running the following perl code against the fields and re-inserting the 'fixed' code into the field:

[GENERAL] converting databases form SQL_ASCII to UTF8

We are moving our databases to new hardware soon, so we felt it would be a good time to get the encoding correct. Our databases are currently SQL_ASCII and we plan to move them to UTF8. So, as previously noted, there are certain characters that won't load into a UTF8 database from a dump of t

Re: [GENERAL] converting databases form SQL_ASCII to UTF8

Vick Khera wrote: On Fri, Apr 22, 2011 at 11:00 AM, Geoffrey Myers mailto:li...@serioustechnology.com>> wrote: Here's our problem. We planned on moving databases a few at a time. Problem is, there is a process that pushes data from one database to another. If t

Re: [GENERAL] converting databases form SQL_ASCII to UTF8

Jasen Betts wrote: On 2011-04-22, Geoffrey Myers wrote: Vick Khera wrote: On Fri, Apr 22, 2011 at 11:00 AM, Geoffrey Myers mailto:li...@serioustechnology.com>> wrote: Here's our problem. We planned on moving databases a few at a time. Problem is, there is a process that

[GENERAL] proper regex_replace() syntax

I want to use regex_replace() to replace characters in multiple records. What I would like to do is this: select regex_replace((select fname from table), 'z', 'Z')); The problem is, the subquery returns more then one row. So, is there a way to do what I'm trying to do? That is, replace the

[GENERAL] out of memory error

We have a process that we successfully ran on virtually identical databases. The process completed fine on a machine with 8 gig of memory. The process fails when run on another machine that has 16 gig of memory with the following error: out of memory for query result How is this possible?

Re: [GENERAL] out of memory error

Alban Hertroys wrote: On 3 Jul 2011, at 12:00, Geoffrey Myers wrote: We have a process that we successfully ran on virtually identical databases. The process completed fine on a machine with 8 gig of memory. The process fails when run on another machine that has 16 gig of memory with the

Re: [GENERAL] out of memory error

Craig Ringer wrote: On 3/07/2011 6:00 PM, Geoffrey Myers wrote: out of memory for query result How is this possible? Resource limits? Could this message be generated because of shared memory issues? The odd thing is the error was generated by a user process, but there is no reference

Re: [GENERAL] out of memory error

One other note, there is no error in the postgres log for this database. I would have expected to find an error there. -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of

Re: [GENERAL] out of memory error

Geoffrey Myers wrote: We have a process that we successfully ran on virtually identical databases. The process completed fine on a machine with 8 gig of memory. The process fails when run on another machine that has 16 gig of memory with the following error: out of memory for query result

Re: [GENERAL] out of memory error

Craig Ringer wrote: On 3/07/2011 6:00 PM, Geoffrey Myers wrote: out of memory for query result How is this possible? Resource limits? Could this message be generated because of shared memory issues? The odd thing is the error was generated by a user process, but there is no reference to

Re: [GENERAL] out of memory error

Tom Lane wrote: Geoffrey Myers writes: Geoffrey Myers wrote: out of memory for query result One other note that is bothering me. There is no reference in the log regarding the out of memory error. Should that not also show up in the associated database log? Not if it's a client

[GENERAL] running out of oids

Am I correct in assuming that the 'running out of oids' issue was resolved with a design change within Postgresql? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them.

Re: [GENERAL] running out of oids

Merlin Moncure wrote: On Wed, Aug 3, 2011 at 2:41 PM, Geoffrey Myers wrote: Am I correct in assuming that the 'running out of oids' issue was resolved with a design change within Postgresql? not exactly -- for quite some time now the use of oids in user tables has been discour

[GENERAL] Is max connections in a table somewhere?

Is max connections in any table in the database I can access? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing

[GENERAL] is max connections in a database table somewhere

Is the max connections value in a system table somewhere? Thanks. -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- 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] Is max connections in a table somewhere?

Scott Marlowe wrote: On Wed, Aug 10, 2011 at 12:47 PM, Geoffrey Myers wrote: Is max connections in any table in the database I can access? No it's in the postgresql.conf file, which is in various places depending on how pg was installed. for debian / ubuntu it's in /etc/postgresq

Re: [GENERAL] Is max connections in a table somewhere?

Adrian Klaver wrote: On Wednesday, August 10, 2011 11:47:25 am Geoffrey Myers wrote: Is max connections in any table in the database I can access? SELECT current_setting('max_connections'); current_setting - 100 Thanks for all the responses folks. Obviousl

Re: [GENERAL] is max connections in a database table somewhere

Greg Smith wrote: On 08/10/2011 02:46 PM, Geoffrey Myers wrote: Is the max connections value in a system table somewhere? If you intend to do anything with the value you probably want one of these forms: SELECT CAST(current_setting('max_connections') AS integer); SELECT CAST(

[GENERAL] using between with dates

I'm trying the following: ship_date between '04/30/2010' AND '04/30/2010' + 14 But this returns: ERROR: invalid input syntax for integer: "04/30/2010" Can I use between with dates? -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- Sent via pgsql-

[GENERAL] weird initdb output

ostgres". This user must also own the server process. . . Why is it trying to change directory to /root??? Running as the postgres user. Any assistance would be appreciated. -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- Sent via pgsql-general mailing list (pgsql-general@pos