[GENERAL] backend process terminates

[Geoffrey Myers]

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.  The problem is that the output file is 324k, so I'm 
sticking it on a website so as not to send such a large file as a 
attachment.  We would appreciate any assistance folks might have in 
helping us determine what is going on here.  The following is the query 
run that generated this segfault:


select pcm_getmiles_s('sparta, nc', 'buffalo, ny', 0);

We are building pcm_getmiles_s() into the backend process.

This is Postgresql 7.4.17 on Red Hat Enterprise 4.

The output from the gdb batch process may be found here:

http://www.serioustechnology.com/gdbbatch.txt

Any help will be greatly appreciated.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

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

Re: [GENERAL] NOT NULL Issue

[Geoffrey Myers]

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 the standard.


If people would think of it in terms of an address it might make more 
sense to them.  An empty string has an address, so can a string, integer 
and so on.  When you think of NULL, think of it in the context of a NULL 
address.  It's not addressable, it's nothing, it's not set, it's not 
there.  I know it's not 100% accurate, but I think it helps folks 
understand the concept.


--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

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

[GENERAL] using between with dates

[Geoffrey Myers]

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 mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] searching for characters via the hexidecimal value

[Geoffrey Myers]

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 Jefferson

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

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

[Geoffrey Myers]

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: [archiver (db)] Error from TOC entry 5225; 0 16990 TABLE 
DATA cust postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence 
for encoding "UTF8": 0xb0
HINT:  This error can also happen if the byte sequence does not match 
the encoding expected by the server, which is controlled by 
"client_encoding".

CONTEXT:  COPY cust, line 778

Is there any easy way to figure out which record caused this error?

Thanks.

--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Geoffrey Myers]

Massa, Harald Armin wrote:

yes, there is.

select  from  where

 like '%'||chr(x'42'::int)||'%'


This does not work for me, but if I convert the hex value to octal this 
does work:


select comments from fax where comments ~* E'\231';



where '42' 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...@serioustechnology.com>> wrote:


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 Jefferson

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare



--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Geoffrey Myers]

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 following error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5225; 0 16990 TABLE
DATA cust postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence
for encoding "UTF8": 0xb0
HINT:  This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".
CONTEXT:  COPY cust, line 778
^^^ In the COPY command for that table. 


I picked up ont that, but the dump is binary, thus I can not view the 
actual code.





Is there any easy way to figure out which record caused this error?

Thanks.

--
Until later, Geoffrey








--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Geoffrey Myers]

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 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: [archiver (db)] Error from TOC entry 5225; 0 16990 TABLE
DATA cust postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence
for encoding "UTF8": 0xb0
HINT:  This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".
CONTEXT:  COPY cust, line 778

^^^ In the COPY command for that table.

I picked up ont that, but the dump is binary, thus I can not view the
actual code.


Actually you can :) I should have mentioned it before. You can have pg_restore 
restore to a file instead of a database by using the -f switch. When you do 
that it creates plain text output. You could restore the entire dump to the 
file or use the -t switch to get only the table you need.


Thanks for the suggestion.  As it stands, we are getting different 
errors for different hex characters, thus the solution we need is the 
ability to identify the characters that won't convert from SQL_ASCII to 
UTF8.  Is there a resource that would identify these characters?





Is there any easy way to figure out which record caused this error?

Thanks.

--
Until later, Geoffrey

--
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







--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Geoffrey Myers]

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 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: [archiver (db)] Error from TOC entry 5225; 0 16990 TABLE
DATA cust postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence
for encoding "UTF8": 0xb0
HINT:  This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".
CONTEXT:  COPY cust, line 778

^^^ In the COPY command for that table.

I picked up ont that, but the dump is binary, thus I can not view the
actual code.

Actually you can :) I should have mentioned it before. You can have
pg_restore restore to a file instead of a database by using the -f
switch. When you do that it creates plain text output. You could restore
the entire dump to the file or use the -t switch to get only the table
you need.

Thanks for the suggestion.  As it stands, we are getting different
errors for different hex characters, thus the solution we need is the
ability to identify the characters that won't convert from SQL_ASCII to
UTF8.  Is there a resource that would identify these characters?



Well the issue is that SQL_ASCII is not an encoding. From the docs:
http://www.postgresql.org/docs/9.0/interactive/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED
"Thus, this setting is not so much a declaration that a specific encoding is in 
use, as a declaration of ignorance about the encoding. In most cases, if you 
are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting 
because PostgreSQL will be unable to help you by converting or validating 
non-ASCII characters. "


What you need to do is determine what applications where putting data into the 
database and what encoding they are using. I ran into this a couple of years 
back with an app that was using WIN1252 for data being inserted into a couple 
of tables in a SQL_ASCII database . Once I knew the encoding I dumped the table 
schema only for those tables into a new UTF8 database. Using psql I set the 
client_encoding to WIN1252 and then used \i to pull in a plain text data only 
dump for each table.


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 the data and finding the errors.


Hence the reason I asked about a resource that might identify the 
characters.






--
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







--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Geoffrey Myers]

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 the data and finding the errors.

Hence the reason I asked about a resource that might identify the
characters.


The problem is that from the standpoint of the SQL_ASCII database there 
is nothing wrong with the characters per se. AFAIK there is no built in 
function to validate characters. The reason is that valid is determined 
by the encoding and if you know the encoding then you really don't need 
to determine validity. If you want to see one way others have tackled 
this, search on iconv in the mailing list archive. This requires working 
on an external copy of the data and knowing something about the 
encodings involved. The nearest I could ever find to an encoding 
detector is:


http://chardet.feedparser.org/

It is a Python program and the encodings it detects are limited but it 
might work for you.


Given all the above, when I was faced with the problem you are facing I 
found it easiest to make an educated guess as to the original encoding 
and then do test restores with client_encoding set to my guess.


Understood.  We had figured the problem to be small, and it appears it 
is and thus felt we could address it a character at a time.  Then get 
this error:


pg_restore: [archiver (db)] Error from TOC entry 5258; 0 17549 TABLE 
DATA fax postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence 
for encoding "UTF8": 0xe28053


That hex value doesn't translate to a single character.  I've dumped the 
data to a file as you suggested, but reviewing the identified line 
brings no joy.


--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] plsql question

[Geoffrey Myers]

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

"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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] finding bogus UTF-8

[Geoffrey Myers]

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 stringent as it is today. Can
anybody suggest a query to find such values?


I hit this problem too, if I remember correctly when trying to upgrade a 
database from 8.3 to 8.4.  I ended up aborting the upgrade, since the 
upgrade documentation made no mention of this and I didn't have time to 
dig into it at the time.  A tool to find all instances of this would be 
very helpful.


I'm about to pipe the ascii output of a database dump through a perl 
script that removes any unwanted characters.  To help define what 
'unwanted characters' are, compare the ord() values to decimal values at 
http://www.asciitable.com/


while (<>)
{
$_ =~ s/(.)/((ord($1) >= 0) && (ord($1) <= 8))
|| ((ord($1) >= 11) && (ord($1) <= 31))
|| ((ord($1) >= 127)) ?"": $1/egs;
print;
}

comments would be appreciated.

--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] finding bogus UTF-8

[Geoffrey Myers]

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-\377' > foo-cleaned.txt


I toyed with tr for a bit, but could not get it to work.  The above did 
not work for me either.  Not exactly sure what it's doing, but here's a 
couple of diff lines:



1619c1619
< days integer DEFAULT 28,
---
> days integer DEFAULT 2,


So it appears 'tr' is deleting the '8' character, rather then the octal 
value for 008.



--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] disable triggers using psql

[Geoffrey Myers]

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 the data integrity issue.


Is there a way to resolve this issue with the psql loading approach?

--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 I wasn't looking, the
constraints are actually implemented as triggers under the hood.  But
you're right that it'd be cleaner to drop the constraints and re-add
them than to fool with system triggers.


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.

We've got 15 databases we need to convert to UTF-8 and we are trying to 
get this done the fastest way possible.





The reason I think pg_restore works for you is because when a table is built
using pg_restore all the data is loaded into all tables BEFORE any
constraints are created.  I believe that if you did a data-only dump from
pg_dump you would have the same integrity problems.


Yes.

A




--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 this fail? This should absolutely disable all 
triggers for you, unless you've mucked with the triggers 
and set them to replica.


I received the following error:

ERROR:  insert or update on table "customer" violates foreign key 
constraint "$1"





- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102171053
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1dRKIACgkQvJuQZxSWSsir0wCfQZmZkDrYBYVffyYBGYoqA/RT
VRMAoLG497FaRU7gOkpM394UT7xksXzk
=f9co
-END PGP SIGNATURE-






--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 triggers 
and set them to replica.



I received the following error:

ERROR:  insert or update on table "customer" violates foreign key 
constraint "$1"


Try removing the 'local'; you may be spanning multiple transactions.
If this is a script you are feeding directly to psql, you can 
also add a BEGIN; at the top or just use the -1 argument.


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 "customer" 
violates foreign key constraint "$1"

DETAIL:  Key (country,state)=(US,GA) is not present in table "state".
psql:backup.txt:2077311: ERROR:  current transaction is aborted, 
commands ignored until end of transaction block

.
.

Just to clarify, I added this to the dump:

set session_replication_role = replica;

and ran the command:

psql -1 -p $TARGETPORT -f $BACKUP -d $DB



- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102171551
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1diwUACgkQvJuQZxSWSshcPgCdGuHIe4bINl/BmoKW89YmQscD
IdAAnA8bwbzmMKssCga9G0dpSh1GopzD
=khQx
-END PGP SIGNATURE-






--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 "customer" 
violates foreign key constraint "$1"


Hmm..are we running a modern Postgres?


8.3.13


Perhaps see if the following script works with a single error:

BEGIN;

CREATE TEMP TABLE abc (a INT PRIMARY KEY);

CREATE TEMP TABLE def (b INT NOT NULL REFERENCES abc(a));

SELECT 'Error:';

INSERT INTO def(b) VALUES (1);

SET session_replication_role = replica;

SELECT 'No error:';

INSERT INTO def(b) VALUES (2);

SELECT * FROM def;

ROLLBACK;


I get this:

BEGIN
psql:test.sql:3: NOTICE:  CREATE TABLE / PRIMARY KEY will create 
implicit index "abc_pkey" for table "abc"

CREATE TABLE
CREATE TABLE
 ?column?
--
 Error:
(1 row)

psql:test.sql:9: ERROR:  insert or update on table "def" violates 
foreign key constraint "def_b_fkey"

DETAIL:  Key (b)=(1) is not present in table "abc".
psql:test.sql:11: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block
psql:test.sql:13: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block
psql:test.sql:15: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block
psql:test.sql:17: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block

ROLLBACK

--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 "customer" 
violates foreign key constraint "$1"


Hmm..are we running a modern Postgres?


8.3.13

Perhaps see if the following 
script works with a single error:


BEGIN;

CREATE TEMP TABLE abc (a INT PRIMARY KEY);

CREATE TEMP TABLE def (b INT NOT NULL REFERENCES abc(a));

SELECT 'Error:';

INSERT INTO def(b) VALUES (1);

SET session_replication_role = replica;

SELECT 'No error:';

INSERT INTO def(b) VALUES (2);

SELECT * FROM def;

ROLLBACK;


I get this:

BEGIN
psql:test.sql:3: NOTICE:  CREATE TABLE / PRIMARY KEY will create 
implicit index "abc_pkey" for table "abc"

CREATE TABLE
CREATE TABLE
 ?column?
--
 Error:
(1 row)

psql:test.sql:9: ERROR:  insert or update on table "def" violates 
foreign key constraint "def_b_fkey"

DETAIL:  Key (b)=(1) is not present in table "abc".
psql:test.sql:11: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block
psql:test.sql:13: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block
psql:test.sql:15: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block
psql:test.sql:17: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block

ROLLBACK




- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102171745
http://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-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 triggers 
and set them to replica.



I received the following error:

ERROR:  insert or update on table "customer" violates foreign key 
constraint "$1"


Try removing the 'local'; you may be spanning multiple transactions.
If this is a script you are feeding directly to psql, you can 
also add a BEGIN; at the top or just use the -1 argument.


I actually manually wrapped the whole thing in a transaction, but I'll 
give your suggestion a shot.




- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 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.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

[Geoffrey Myers]

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 that to the top of 
the script. Or just comment out the first insert and 
see if the second one works. If it doesn't, something weird 
is going on with Postgres. If it does, something weird is 
going on with your script and I would recommend breaking your 
dump script down into smaller pieces to see what is happening.
Most likely session_replication_role is not getting set or 
is getting reset somewhere.


So I added the on_error_rollback to the script and I get this:

BEGIN
psql:test.sql:4: NOTICE:  CREATE TABLE / PRIMARY KEY will create 
implicit index "abc_pkey" for table "abc"

CREATE TABLE
CREATE TABLE
 ?column?
--
 Error:
(1 row)

psql:test.sql:10: ERROR:  insert or update on table "def" violates 
foreign key constraint "def_b_fkey"

DETAIL:  Key (b)=(1) is not present in table "abc".
psql:test.sql:12: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block
psql:test.sql:14: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block
psql:test.sql:16: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block
psql:test.sql:18: ERROR:  current transaction is aborted, commands 
ignored until end of transaction block

ROLLBACK



- -- 
Greg Sabino Mullane g...@endpoint.com  g...@turnstep.com

End Point Corporation 610-983-9073
PGP Key: 0x14964AC8 201102172155
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1d360ACgkQvJuQZxSWSsidCQCfTnQxp5w6psa3C9NREX0ecZ+j
Ft0An2JKofuxVJNwxhVkh4NBTJU3Xcom
=fLDa
-END PGP SIGNATURE-






--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 ON_ERROR_ROLLBACK? It's 
case-sensitive. Anyway, try this shortened version:


cut and paste:

set ON_ERROR_ROLLBACK;

When I try the below, I get:

BEGIN
psql:test.sql:3: NOTICE:  CREATE TABLE / PRIMARY KEY will create 
implicit index "abc_pkey" for table "abc"

CREATE TABLE
CREATE TABLE
SET
 ?column?
---
 No error:
(1 row)

INSERT 0 1
 b
---
 2
(1 row)

ROLLBACK




BEGIN;

CREATE TEMP TABLE abc (a INT PRIMARY KEY);

CREATE TEMP TABLE def (b INT NOT NULL REFERENCES abc(a));

SET session_replication_role = replica;

SELECT 'No error:';

INSERT INTO def(b) VALUES (2);

SELECT * FROM def;

ROLLBACK;

- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102180938
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1ehJEACgkQvJuQZxSWSsj/5gCgjsQa+nzZz26xQ7c70Bxl5Hs3
AuUAn1uD7MY2BtGR7usl45pC3Yv2pqVS
=mLCm
-END PGP SIGNATURE-






--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 problem narrowed:


INSERT 0 1


This shows the session_replication_role is working as it should. Double 
check where and how you are setting it; your foreign key problems 
will go away once it is set correctly.


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:


--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF-8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

set session_replication_role = replica;

I'm still getting the errors.  If it doesn't belong at the beginning of 
this process, I'm not exactly sure where it should go.


--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 problem narrowed:


INSERT 0 1


This shows the session_replication_role is working as it should. Double 
check where and how you are setting it; your foreign key problems 
will go away once it is set correctly.


Okay, thanks.



- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102181243
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1esCsACgkQvJuQZxSWSsh5JgCeK/Mk+e598LAhDsYvNmTCWM8E
F+sAoN9YX32TFKF/5YDp3CoNBwfnbnqb
=u8rB
-END PGP 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

[Geoffrey Myers]

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_replication_role. If so, add the SET right after 
the \connect. Alternatively, you could create a special user 
to invoke psql as, which has:


It is a pg_dump.  There is no \connect in the code.



ALTER USER dangerous_bob SET session_replication_role = replica;

Be *very* careful with that account though, as using it for 
anything other than this special case could be very bad.


- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102181408
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1ew9MACgkQvJuQZxSWSshETwCg2oEEicHhokORuQRl3sxkLkpj
ghIAnRe02LCuyyRlyzvKZ67QCYUyfPzC
=H9Wb
-END PGP SIGNATURE-






--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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_replication_role. If so, add the SET right after 
the \connect. Alternatively, you could create a special user 
to invoke psql as, which has:


ALTER USER dangerous_bob SET session_replication_role = replica;

Be *very* careful with that account though, as using it for 
anything other than this special case could be very bad.


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 replica;
pro-# \o db.out
pro-# \i dump.txt

I still get:

psql:dump.txt:2077301: ERROR:  insert or update on table "cust" violates 
foreign key constraint "$1"

DETAIL:  Key (country,state)=(US,GA) is not present in table "state".


So there's something in the dump that's changing the 
session_replication_role?




- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102181408
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1ew9MACgkQvJuQZxSWSshETwCg2oEEicHhokORuQRl3sxkLkpj
ghIAnRe02LCuyyRlyzvKZ67QCYUyfPzC
=H9Wb
-END PGP SIGNATURE-






--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

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 replica;
pro-# \o db.out
pro-# \i dump.txt


This is a database set, not a psql on, so you do not want the 
backslash before the "set". 


SET session_replication_role = replica;

I'd recommend adding a:

SHOW session_replication_role;

to the dump.txt as a sanity check.


For the sake of completeness, I've attempted the above, same result.  We 
have decided to take a different approach and attempt to clean up the 
data in the database, then convert.


I do appreciate all the time you've devoted to this.  There must be 
something in the dump that is causing these issues.


Thanks again Greg.  I'll certainly update the list once we have a 
working solution.




- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102211529
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1iy74ACgkQvJuQZxSWSsgWQACgrxKDvN/yCZD5GZJvlqFMyyIC
9mwAnjOMJ9QDRa3IoiBCvaS9mT5sMR6f
=JYCs
-END PGP SIGNATURE-






--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Geoffrey Myers]

Vibhor Kumar wrote:

On Feb 22, 2011, at 10:23 PM, Jeff Amiel wrote:


It's come time to bite the bullet and convert a half-terraybyte database from 
ASCII to UTF8.  Have gone through a bit of effort to track down the unclean 
ascii text and repair it but would like to avoid the outage of a many-many hour 
dump-restore.

Using Postgres 8.4.X.

I assume slony replication is an option.


Right! Replication would help. You can also try Bucardo.


I'm assuming you're saying you can replicate from an ASCII database to 
UTF8?  What happens to the data that is not UTF8 '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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] error messages during restore

[Geoffrey Myers]

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: ERROR:  invalid byte sequence 
for encoding "UTF8": 0x90
HINT:  This error can also happen if the byte sequence does not match 
the encoding expected by the server, which is controlled by 
"client_encoding".

CONTEXT:  COPY logs, line 590744

So as far as I can tell, this identifies the table by name, logs in this 
case, and then identifies the actula record by line.


Question is, it would be really nice if we could figure out the actual 
column name in that table.  Noting that I do get a line number that 
produced the error, but the fact that this is a binary dump makes it 
difficult to view that line.


Is there a way to view that data line without converting this dump to a 
text dump?


All I'd like to do is know which column in the table caused the problem 
so I could apply my fix to that particular column.


--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] error messages during restore

[Geoffrey Myers]

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 logs postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence 
for encoding "UTF8": 0x90
HINT:  This error can also happen if the byte sequence does not match 
the encoding expected by the server, which is controlled by 
"client_encoding".

CONTEXT:  COPY logs, line 590744


Question is, it would be really nice if we could figure out the actual 
column name in that table.


Sorry, no chance of that.  The line is converted to server encoding
before any attempt is made to split it into columns.  Since the column
delimiter is potentially encoding-specific, there's not really any
alternative to doing it that way.

regards, tom lane



Thanks for the follow up Tom.


--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] UTF8 conversion revisited

[Geoffrey Myers]

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:


data =~ s/(.)/((ord($1) >= 0) && (ord($1) <= 8))
|| (ord($1) == 11)
|| ((ord($1) >= 13) && (ord($1) <= 31))
|| ((ord($1) >= 127)) ?"": $1/egs;

This appears to be working as a large number of records are cleaned. 
Problem is, someone it's not fixing data that contains the hex value 
0xbd, as when I attempt to dump this database and create a new one with 
the UTF8 encoding I get the following error:


pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5246; 0 4978675 TABLE 
DATA cust postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence 
for encoding "UTF8": 0xbd


As I see it, the perl code above should catch this '0xbd' character, but 
somehow it is finding it's way through.


Any insights would be greatly appreciated.

--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] converting databases form SQL_ASCII to UTF8

[Geoffrey Myers]

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 the SQL_ASCII database.


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 this process attempts to push data from a SQL_ASCII 
database to a new UTF8 database and it has one of these characters 
mentioned above, the process fails.


So, now the question is, is this effort even worth our effort?

What is the harm in leaving our databases SQL_ASCII encoded?

Thanks for any insights.

--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] converting databases form SQL_ASCII to UTF8

[Geoffrey Myers]

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 this process attempts to push data from a SQL_ASCII
database to a new UTF8 database and it has one of these characters
mentioned above, the process fails.


The database's enforcement of the encoding should be the last layer that 
does so.  Your applications should be enforcing strict utf-8 encoding 
from start to finish.  Once this is done, and the old data already in 
the DB is properly encoded as utf-8, then there should be no problems 
switching on the utf-8 encoding in postgres to get that final layer of 
verification.


Totally agree.  Still, the question remains, why not leave it as SQL_ASCII?

--
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] converting databases form SQL_ASCII to UTF8

[Geoffrey Myers]

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 pushes data from one database to
another.  If this process attempts to push data from a SQL_ASCII
database to a new UTF8 database and it has one of these characters
mentioned above, the process fails.


The database's enforcement of the encoding should be the last layer that 
does so.  Your applications should be enforcing strict utf-8 encoding 
from start to finish.  Once this is done, and the old data already in 
the DB is properly encoded as utf-8, then there should be no problems 
switching on the utf-8 encoding in postgres to get that final layer of 
verification.

Totally agree.  Still, the question remains, why not leave it as SQL_ASCII?


perhaps you want sorted output in some locale other than 'C'?
or maybe want to take a substring in the database...

utf8 in SQL-ASCII is just a string of octets

utf8 in a utf8 database is a string of unicode characters.



We finally have a solution in place. A bug in my code was making the 
problem bigger then it really is.  Gotta love those bugs.



--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] proper regex_replace() syntax

[Geoffrey Myers]

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 
same character in multiple records using regex_replace() ?


In reality, we are trying to change characters like the 1/2 character to 
the three characters '1/2'.


Thanks for any assistance.


--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] out of memory error

[Geoffrey Myers]

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?

The databases are almost identical.  By that I mean, the database that 
the process completed properly is a dump of the database from the 
machine where it failed.  There is about a week's more data in the 
database where the process failed.  The whole database only takes up 
about 13 gig of disk space.


Any clues would be appreciated.

--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] out of memory error

[Geoffrey Myers]

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 following error:

out of memory for query result


You didn't mention what client you're using, but could it possibly be
the client that's running out of memory? The fact that it's happening
in the query result seems to point to the client.


Perl.


Another thing you might want to check: Does the second server have at
least as much shared memory configured in the kernel as the first
has?


I was thinking that might be the issue.  They have the same amount of 
share memory configured, but the server that had the error, has 8 
postmasters running, whereas the other server only has one.




Alban Hertroys

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


!DSPAM:1272,4e109ddd12091486111017!






--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] out of memory error

[Geoffrey Myers]

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 the error in the database log file.




Do you have a ulimit in place that applies to postgresql? You can check 
by examining the resource limits of a running postgresql backend as 
shown in /proc/$PG_PID where $PG_PID is the process ID of the backend of 
interest.


Check your work_mem in postgresql.conf, too.


work_mem is commented out on both machines, so I suspect that it is then 
using the default value?  What would be the default value?




--
Craig Ringer




--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] out of memory error

[Geoffrey Myers]

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 them."
- Thomas Jefferson

--
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] out of memory error

[Geoffrey Myers]

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

How is this possible?

The databases are almost identical.  By that I mean, the database that 
the process completed properly is a dump of the database from the 
machine where it failed.  There is about a week's more data in the 
database where the process failed.  The whole database only takes up 
about 13 gig of disk space.


Any clues would be appreciated.



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?



--
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] out of memory error

[Geoffrey Myers]

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 the error in the database log file.


Do you have a ulimit in place that applies to postgresql? You can check 
by examining the resource limits of a running postgresql backend as 
shown in /proc/$PG_PID where $PG_PID is the process ID of the backend of 
interest.


Check your work_mem in postgresql.conf, too.


work_mem is commented out on both machines, so I suspect that it is then 
using the default value?  What would be the default value?




--
Craig Ringer




--
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] out of memory error

[Geoffrey Myers]

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-side error.

(Which a quick grep through the PG source code says it is ...)

regards, tom lane


Wanted to add more specifics. Here is the actual code that generated the 
error:


my $result = $conn->exec($select);

if ($result->resultStatus != PGRES_TUPLES_OK)
{
$error = $conn->errorMessage;
die "Error: <$error> Failed: <$select>";
}

So you're saying this select request failing would not be logged to the 
postgres database log?



--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] running out of oids

[Geoffrey Myers]

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."
- Thomas Jefferson

--
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] running out of oids

[Geoffrey Myers]

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 discouraged.  The right way to deal with this problem is not
to use oids at all.

merlin


Thanks.

--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Is max connections in a table somewhere?

[Geoffrey Myers]

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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] is max connections in a database table somewhere

[Geoffrey Myers]

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?

[Geoffrey Myers]

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/postgresql/8.x/main for the default cluster.  It's in
/var/lib/pgsql/data for RHEL 5.  Not sure about other distros.


Yeah, I knew it was in the postgresql.conf file, but since I've got a 
piece of code that's already connected to the database, I figured I'd 
get it from the database, rather then open the file and read it from there.


--
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?

[Geoffrey Myers]

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.  Obviously, there's more then one 
way to skin this cat.



--
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 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 database table somewhere

[Geoffrey Myers]

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(setting AS integer) FROM pg_settings WHERE 
name='max_connections';


The setting comes back as a text field when using current_setting on the 
pg_settings view (which isn't a real table, under the hood it's calling 
a system function)


Actually, just pulling it out of the database to display it in a report.


--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] weird initdb output

[Geoffrey Myers]

I wrote a script that creates a new database from an existing backup. 
Works great on my machine.  Another user tries to use it and sees the 
following output from initdb:


could not change directory to "/root"
The files belonging to this database system will be owned by user 
"postgres".

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pitr question

[Geoffrey Myers]

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 hardware, where one machine is 
running a 32 bit OS and the other is running a 64 bit OS?


Further:

Say 32 bit hardware and 64 bit hardware, where both are running a 32 bit OS?

Specifically speaking of RHEL.

Thanks.


--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pitr question

[Geoffrey Myers]

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 from,
 > say, a 32-bit to a 64-bit system will not work.'

postgres only sees the OS. It does not care what hardware you got. So
you must use the same type of OS: both 32 bit or both 64 bit.


As I expected, thus the document should possibly be rephrased to 
indicate it's the OS not the physical hardware that is the issue.



--
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 list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Pitr

[Geoffrey Myers]

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, Geoffrey
Sent from my iPhone
-- 
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] Pitr

[Geoffrey Myers]

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 
> 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, Geoffrey
> Sent from my iPhone
> -- 
> 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